Sequences are guaranteed to produce unique values. Just manually configure each child table to use the same sequence for the primary key column. Yes, uniqueness won't be enforced on the parent table, but the values will still be unique (unless you manually update them in your queries)
Re:PostgreSQL partitioning explained
by trippy_biscuits on Sunday May 04, 2014 @04:26AM
(#7693707)
Until the sequence rolls over. Why design a schema with built in failure (an integer PK that overflows)?
Sequences won't roll over by default. nextval() fails if you're out of values. You can create a sequence which is allowed to roll over (by specifying cycle), but it's not the default.
Yes. It's not optimal and you can shoot yourself in the foot, but it works in its default configuration. I just gave a workable workaround for a problem, I wasn't saying it's the perfect solution (a working unique constraint on the parent table would be)
Re:PostgreSQL partitioning explained
by Negitivefrags on Sunday May 04, 2014 @06:41AM
(#7693853)
The default sequence size is 64 bit. Even if you are getting 100,000 ids a second from it you wont run out for over 5 million years.
Re:PostgreSQL partitioning explained
by drewcrawford on Sunday May 04, 2014 @02:20AM
(#7693500)
I'm no database expert, but I don't think the features you want are possible, really. To get a unique constraint over multiple tables you'd have to lock them all, and if you lock them all you have lost the performance benefit of partitioning tables.
There are things you could do, like key on a GUID, that would ensure each insert has a unique ID in a lock-free way. But the semantics of that are going to be very different from a traditional autoincrementing ID.
Well, the locks would only be necessary for updates (or INSERTS where the constrained column doesn't come from the sequence), and that's a price I'd be willing to pay (hint, how often do you update the primary key column?)
Re:PostgreSQL partitioning explained
by keithgabryelski on Sunday May 04, 2014 @01:55PM
(#7695090)
it's simply not a problem in practice -- even for very very large tables. Use bigserial if you must.
you can also modify the child table to use a different sequence for each table.
If this constraint was lifted, table inheritance would allow some really cool things, but currently I'm too conservative to use it in production.