More on inheritance and foreign keys

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 locking
problems)

Of course then we would need
REFERENCES 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