Kynn Jones <kyn...@gmail.com> wrote: > I want to implement something akin to OO inheritance among DB > tables. The idea is to define some "superclass" table, e.g.: > > CREATE TABLE super ( > super_id INT PRIMARY KEY, > ... > -- other columns > > ); > > > CREATE TABLE sub_1 ( > > super_id INT PRIMARY KEY, > FOREIGN KEY (super_id) REFERENCES super(super_id), > ... > > -- other columns > > ); > > > CREATE TABLE sub_2 ( > > super_id INT PRIMARY KEY, > FOREIGN KEY (super_id) REFERENCES super(super_id), > ... > > -- other columns > ); > > ... > > > CREATE TABLE sub_n ( > > super_id INT PRIMARY KEY, > FOREIGN KEY (super_id) REFERENCES super(super_id), > ... > > -- other columns > ); > > > it would be possible for multiple "sub" records (each from a > different "sub_k" table) to refer to the same "super" record, and > this may not be consistent with the semantics of some > applications. > > > Does PostgreSQL have a good way to enforce the uniqueness of > super_id values across multiple tables?
This goes beyond the capabilities of declarative constraints to enforce. You can enforce it using triggers, but you need to handle race conditions, which is not easy with MVCC behavior (where reads don't block anything and writes don't block reads). There are basically two ways to cover that: (1) You can introduce blocking. This can be done with LOCK TABLE statements, but that tends to be a pretty crude tool for this. You might be able to make clever use of transactional advisory locks. Or you could have a child_count column in the super table which is maintained by "AFTER EACH ROW" triggers for INSERT and DELETE. (2) You can ensure that all transactions which could affect this invariant use the SERIALIZABLE transaction isolation level. The triggers can then check that there is not a matching row in more than one "sub" table without worrying about the race conditions (beyond automatically retrying a transaction which throws a serialization failure). An example of handling something vaguely similar using SERIALIZABLE transactions is here: http://wiki.postgresql.org/wiki/SSI#FK-Like_Constraints You should probably review this entire chapter in the documentation: http://www.postgresql.org/docs/current/interactive/mvcc.html By the way, I saw exactly this pattern in a financial accounting system for courts. The super table had common information for all financial transactions, and there were separate sub tables for assessments, receipts, checks, etc. We had a "transaction type" code column in the super table to specify *which* of the sub tables should be populated for each row in the super table. I don't know whether you have anything like that, but I think the issues are similar either way -- perhaps a little simpler with such a code than without. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
