Thread: Re: [GENERAL] Foreign Key
--- Howie <caffeine@toodarkpark.org> wrote:
> On Wed, 6 Oct 1999, Herouth Maoz wrote:
>
> > At 01:10 +0200 on 06/10/1999, Howie wrote:
> >
> >
> > > for now, refint ( $PGSQL_SRC_ROOT/contrib/spi/ )
> is what one should be
> > > using for foreign keys. requires two triggers,
> one on the parent and one
> > > on the child. works nicely.
> >
> > Does it? I was under the impression that it
> supported cascading deletes but
> > not cascading updates.
>
> CREATE SEQUENCE employee_seq START 1 INCREMENT 1;
> CREATE SEQUENCE expense_seq START 1 INCREMENT 1;
>
> CREATE TABLE employee
> (
> emp_id int4 not null default
> nextval('employee_seq'),
> emp_name varchar(30) NOT NULL,
> PRIMARY KEY (emp_id)
> );
>
> CREATE TABLE emp_expense
> (
> emp_id int4 not null,
> expense_id int4 not null default
> nextval('expense_seq'),
> descr varchar(100) NOT NULL,
> ondate date not null,
> primary key (expense_id)
> );
>
> CREATE TRIGGER expense_empid_fk
> BEFORE INSERT OR UPDATE ON emp_expense
> FOR EACH ROW
> EXECUTE PROCEDURE check_primary_key('emp_id',
> 'employee', 'emp_id');
>
> CREATE TRIGGER employee_empid_propk
> AFTER DELETE OR UPDATE ON employee
> FOR EACH ROW
> EXECUTE PROCEDURE check_foreign_key( '1',
> 'cascade', 'emp_id',
> 'emp_expense', 'emp_id');
>
> ----
>
> caffeine=> select * from employee;
> emp_id|emp_name
> ------+--------
> 2|Myself
> (1 row)
>
> caffeine=> select * from emp_expense;
> emp_id|expense_id|descr | ondate
> ------+----------+-------+----------
> 2| 1|Test |10-06-1999
> 2| 2|Test #2|10-06-1999
> (2 rows)
>
> caffeine=> update employee set emp_id=5;
> UPDATE 1
> caffeine=> select * from emp_expense;
> emp_id|expense_id|descr | ondate
> ------+----------+-------+----------
> 5| 1|Test |10-06-1999
> 5| 2|Test #2|10-06-1999
> (2 rows)
>
> caffeine=> select version();
> version
>
>
--------------------------------------------------------
> PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by
> egcc
> (1 row)
Now do another update (in the same session):
update employee set emp_id=6;
Followed by a select:
select * from emp_expense;
What's the emp_id value?
In the version of refint.c which was release with
6.5.0, there was an error because after the first
cascading update trigger was executed, the plan was
saved (which includes the value of the foreign key
to be updated), since the cascading update code was
simply the cascading delete code. Saving the SPI
plan for deletes is fine, but for updates it can
cause either (a) the wrong value to updated or
(b) an insertion of new rows.
Perhaps this has been fixed, but I doubt it.
Mike Mascari
(mascarim@yahoo.com)
=====
__________________________________________________
Do You Yahoo!?
Bid and sell for free at http://auctions.yahoo.com
On Wed, 6 Oct 1999, Mike Mascari wrote: > [SNIP] > > Now do another update (in the same session): > > update employee set emp_id=6; > > Followed by a select: > > select * from emp_expense; > > What's the emp_id value? doesnt even get that far: caffeine=> update employee set emp_id=6; UPDATE 1 (emp_expense updated accordingly) caffeine=> update employee set emp_id=3; ERROR: expense_empid_fk: tuple references non-existing key in employee (emp_expense not updated; fki breaks, whole countries are washed away) > In the version of refint.c which was release with > 6.5.0, there was an error because after the first > cascading update trigger was executed, the plan was > saved (which includes the value of the foreign key > to be updated), since the cascading update code was > simply the cascading delete code. Saving the SPI > plan for deletes is fine, but for updates it can > cause either (a) the wrong value to updated or > (b) an insertion of new rows. > > Perhaps this has been fixed, but I doubt it. i havent had a chance to grab 6.5.2 ( latest, iirc ) yet and test this out. actually, i suppose that after check_foreign_key() completes it should discard its plan, yesno ? if so, ( and i havent done any SPI stuff just yet ), the 'fix' would be to insert the proper function call inside of check_foreign_key's "* Ok, execute prepared plan(s)." loop. a quick lookie at programmer/spi-spisaveplan.htm ( grin ) doesnt show any SPI_forgetPlan() functions, though. and SPI_saveplan() "saves a passed plan (prepared by SPI_prepare) in memory protected from freeing by SPI_finish...", which is bad in this situation. so the fix is to wait for 6.6, which hopefully wont have this problem since refint's functionality should be inside of the backend proper, or dont do two cascading updates at the same time, beating users on the head with large books and/or unused machines if they do so. --- Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org "Just think how much deeper the ocean would be if sponges didn't live there."
Does cascade delete work for you if there are more than one tables referencing the primary key, because it throws up an error for me when I delete an entry in the primary key table. -- Anand Howie wrote: > On Wed, 6 Oct 1999, Mike Mascari wrote: > > > [SNIP] > > > > Now do another update (in the same session): > > > > update employee set emp_id=6; > > > > Followed by a select: > > > > select * from emp_expense; > > > > What's the emp_id value? > > doesnt even get that far: > > caffeine=> update employee set emp_id=6; > UPDATE 1 > (emp_expense updated accordingly) > caffeine=> update employee set emp_id=3; > ERROR: expense_empid_fk: tuple references non-existing key in employee > (emp_expense not updated; fki breaks, whole countries are washed away) > > > In the version of refint.c which was release with > > 6.5.0, there was an error because after the first > > cascading update trigger was executed, the plan was > > saved (which includes the value of the foreign key > > to be updated), since the cascading update code was > > simply the cascading delete code. Saving the SPI > > plan for deletes is fine, but for updates it can > > cause either (a) the wrong value to updated or > > (b) an insertion of new rows. > > > > Perhaps this has been fixed, but I doubt it. > > i havent had a chance to grab 6.5.2 ( latest, iirc ) yet and test this > out. actually, i suppose that after check_foreign_key() > completes it should discard its plan, yesno ? if so, ( and i havent done > any SPI stuff just yet ), the 'fix' would be to insert the proper function > call inside of check_foreign_key's "* Ok, execute prepared plan(s)." loop. > > a quick lookie at programmer/spi-spisaveplan.htm ( grin ) doesnt show any > SPI_forgetPlan() functions, though. and SPI_saveplan() "saves a passed > plan (prepared by SPI_prepare) in memory protected from freeing by > SPI_finish...", which is bad in this situation. > > so the fix is to wait for 6.6, which hopefully wont have this problem > since refint's functionality should be inside of the backend proper, or > dont do two cascading updates at the same time, beating users on the head > with large books and/or unused machines if they do so. > > --- > Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org > "Just think how much deeper the ocean would be if sponges didn't live there." > > ************