Timeline for Unique constraint on different columns
Current License: CC BY-SA 3.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Oct 6, 2017 at 10:15 | history | edited | Vérace | CC BY-SA 3.0 |
added 42 characters in body
|
| Oct 6, 2017 at 10:14 | comment | added | Vérace | @a_horse_with_no_name I have removed the reference to performance - on reflection, it probably wouldn't be faster. | |
| Oct 6, 2017 at 10:14 | comment | added | Vérace | @ypercubeᵀᴹ edited to deal with your concerns! Hadn't thought of that! | |
| Oct 6, 2017 at 7:34 | comment | added | ypercubeᵀᴹ |
Besides the other reasons - already mentioned by Evan and a_horse_with_no_name - this has an issue with negative ids as well. It doesn't allow both (-5,1) and (-1,5) for example.
|
|
| Oct 6, 2017 at 6:13 | history | edited | Vérace | CC BY-SA 3.0 |
deleted 129 characters in body
|
| Oct 6, 2017 at 4:34 | comment | added | Evan Carroll |
I think you're missing what I'm saying. In a normal serial column, you can handle 2^31-1 rows and the primary key can handle 2^32 rows. Both of those are usually far more rows than needed. In your index, you can only handle 46340 rows. Why? Because your algorithm is p*c assuming p=c, you can exhaust that range in 46,341. I find it possible to have more than 46,341 edges. So the difference between your range and my range is (2^31-1) - sqrt(2^31-1) you have to value the performance difference, if anything, that much; if you can only support 46,341 rows - does it matter?
|
|
| Oct 6, 2017 at 3:28 | comment | added | Vérace |
Won't PostgreSQL just CAST anyway? I'm not sure any system would cope with 2^32 edges?
|
|
| Oct 6, 2017 at 3:23 | comment | added | Evan Carroll |
I'm not sure it's faster -- and I'm not sure the speed matters on this kind of operation, but it won't work on more than 100k parent/child rows without growing to bigint. For instance, run your create DDL and then run INSERT INTO edge(parent,child) VALUES (1e5, 1e5); My example works up until 2,147,483,647 (>1e9) which is the full range of ints.
|
|
| Oct 6, 2017 at 2:58 | history | answered | Vérace | CC BY-SA 3.0 |