Обсуждение: 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