Thread: More on inheritance and foreign keys
Hi,after starting this thread http://archives.postgresql.org/pgsql-hackers/2006-05/msg00222.php, I thought I'd finally go for making foreign keys my own way instead of trying to patch PostgreSQL. However, I've realized that managing foreign keys with my own PL/SQL or C function isn't possible as I need DEFERRED checks which are currently only available for foreign keys. The solution to the foreign key problem seems easy if I modify PostgreSQL implementation and take off the ONLY word from the SELECT query, but it's not an option for me, as I'm developing a library, not an application. I'd like many people could use it. Would it be acceptable if one could add the INHERIT word when creating foreign keys, in order to change the SELECT behaviour? Are there other solutions to this problem? Thanks.
On Jun 8, 2006, at 15:38 , Albert Cervera Areny wrote: > However, I've realized that managing foreign keys with my own > PL/SQL or C function isn't possible as I need DEFERRED checks which > are > currently only available for foreign keys. I don't know enough about your situation to be sure if this will work or not, but perhaps you want to look at CREATE CONSTRAINT TRIGGER. They're deferred to the end of the transaction. http://www.postgresql.org/docs/8.1/interactive/sql-createconstraint.html In the hope that this helps, Michael Glaesemann grzm seespotcode net
> The solution to the foreign key problem seems easy if I > modify PostgreSQL implementation and take off the ONLY word > from the SELECT query, but it's not an option for me, as I'm I think that the ONLY was wrong from day one :-( The default in other areas is table including childs. (Not like in old pg where you had to use tab* to include childs) (iirc leaving off ONLY is not sufficient because of lockingproblems) Of course then we would needREFERENCES tenk ONLY (unique1) to allow current behavior. Andreas
Ühel kenal päeval, N, 2006-06-08 kell 08:38, kirjutas Albert Cervera Areny: > Hi, > after starting this thread > http://archives.postgresql.org/pgsql-hackers/2006-05/msg00222.php, I thought > I'd finally go for making foreign keys my own way instead of trying to patch > PostgreSQL. However, I've realized that managing foreign keys with my own > PL/SQL or C function isn't possible as I need DEFERRED checks which are > currently only available for foreign keys. remember that you must manage both ends of foreign key. and you have to lock the other table while changing values at either end. -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com
"Zeugswetter Andreas DCP SD" <ZeugswetterA@spardat.at> writes:
>> The solution to the foreign key problem seems easy if I
>> modify PostgreSQL implementation and take off the ONLY word
>> from the SELECT query, but it's not an option for me, as I'm
> I think that the ONLY was wrong from day one :-(
Well, sure, but until we have an implementation that actually *works*
across multiple tables, it has to be there so that we can at least
consistently support the current single-table semantics. Until we
have some form of cross-table unique constraint (index or whatever)
we can't support multi-table foreign keys --- taking off the ONLY
is not a fix.
> Of course then we would need
> REFERENCES tenk ONLY (unique1)
> to allow current behavior.
When we do have the support I'd be inclined to just change the
semantics. I don't think we need to be backward compatible with
what everyone agrees is a bug. (Also, your proposal would cover
having a non-inheritable referenced table, but what of inheritance
on the referencing side?)
regards, tom lane
Tom Lane wrote: > "Zeugswetter Andreas DCP SD" <ZeugswetterA@spardat.at> writes: > >>>The solution to the foreign key problem seems easy if I >>>modify PostgreSQL implementation and take off the ONLY word >>>from the SELECT query, but it's not an option for me, as I'm > > >>I think that the ONLY was wrong from day one :-( > > > Well, sure, but until we have an implementation that actually *works* > across multiple tables, it has to be there so that we can at least > consistently support the current single-table semantics. Until we > have some form of cross-table unique constraint (index or whatever) I managed uniqueness using normal indexes and ins/upd triggers on all child tables: CREATE OR REPLACE FUNCTION checkchildsunique RETURNS trigger AS $BODY$BEGIN IF EXISTS (SELECT 1 FROM foo Master WHERE Master.primaryKeyCol = NEW.primaryKeyCol) THEN RAISE EXCEPTION 'Primary Key violation in table % on %',TG_RELNAME, TG_OP; END IF; RETURN NEW; END;$BODY$ LANGUAGE 'plpgsql' Shouldn't be too complicated to implement it as internal function. Regards, Andreas
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> I managed uniqueness using normal indexes and ins/upd triggers on all
> child tables:
Do I need to point out the race-condition problems in this?
regards, tom lane
> > I think that the ONLY was wrong from day one :-( > > Well, sure, but until we have an implementation that actually > *works* across multiple tables, it has to be there so that we > can at least consistently support the current single-table > semantics. Until we have some form of cross-table unique > constraint (index or whatever) we can't support multi-table > foreign keys > --- taking off the ONLY is not a fix. Um, I think it would work for a special case, where the unique constraint includes the partitioning column[s], and the partitions (check constraints) don't overlap. In this case you can create simple unique indexes on the subtables. When looking at other db's this is not such an exceptional requirement for unique indexes that share the same partitioning scheme as the table. And imho the "all indexes sharing the table partitioning scheme" is the most important use case. Andreas