3 ms·

Somehow http://www.postgresql.org/docs/current/static/ddl-inherit.html#DDL-INHERIT-CAVEATS http://www.postgresql.org/docs/current/static/ddl-inherit.ht... has a

Somehow http://www.postgresql.org/docs/current/static/ddl-inherit.html#DDL-INHERIT-CAVEATS http://www.postgresql.org/docs/current/static/ddl-inherit.ht... has always stopped me from using table inheritance; you don't get unique constraints over the table and its child tables, so for example you can't have an autoincrement primary key that works safely across them all. (Disclaimer: I haven't run into large enough tables yet that I'd need partitioning). If this constraint was lifted, table inheritance would allow some really cool things, but currently I'm too conservative to use it in production.

pilif 13y ago
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)
- trippy_biscuits 13y ago
  Until the sequence rolls over. Why design a schema with built in failure (an integer PK that overflows)?
  - pilif 13y ago
    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)
  - Negitivefrags 13y ago
    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.
drewcrawford 13y ago
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.
- perlgeek 13y ago
  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?)
keithgabryelski 13y ago
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.

pilif 13y ago

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)

trippy_biscuits 13y ago
Until the sequence rolls over. Why design a schema with built in failure (an integer PK that overflows)?
- pilif 13y ago
  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)
- Negitivefrags 13y ago
  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.

drewcrawford 13y ago

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.

perlgeek 13y ago
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?)

keithgabryelski 13y ago

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.