Thread: Stored Procedure and Trigger they puzzle me
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi all,
I just started to write my first stored procedure in plpgsql and
installed a trigger for it.
The two Tables are:
CREATE TABLE ltlocation (
"id" integer DEFAULT nextval('ltlocation_id_seq'::text) NOT NULL,
name varchar(30) NOT NULL default '',
"description" varchar(254) NOT NULL default '',
"parent" int4,
type int2 NOT NULL default '0',
PRIMARY KEY (id)
) ;
just to hold a tree Structure and the second one is:
CREATE TABLE ltlocationpath (
"ltlocation_id" int4 NOT NULL default '0',
"ltlocancester_id" int4 NOT NULL default '0',
PRIMARY KEY (ltlocation_id,ltlocancester_id)
) ;
where the second one holds a materialized path view of the first one.
These constraints are defined:
ALTER TABLE ONLY ltlocation ADD CONSTRAINT parent_fkey
FOREIGN KEY (parent) REFERENCES ltlocation(id) ON UPDATE CASCADE ON
DELETE CASCADE;
ALTER TABLE ONLY ltlocationpath ADD CONSTRAINT ltlocation_fkey
FOREIGN KEY (ltlocation_id) REFERENCES ltlocation(id) ON UPDATE
CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY ltlocationpath ADD CONSTRAINT ltlocancester_fkey
FOREIGN KEY (ltlocancester_id) REFERENCES ltlocation(id) ON UPDATE
CASCADE ON DELETE CASCADE;
The Stored Procedure is:
CREATE OR REPLACE FUNCTION populatelocationpath() RETURNS trigger AS $$
DECLARE
workid integer := 0;
BEGIN
IF tg_op = 'UPDATE' THEN
DELETE FROM ltlocationpath WHERE ltlocation_id = old.id;
END IF;
workid := new.id;
WHILE workid > 0 LOOP
BEGIN
EXECUTE 'INSERT INTO ltlocationpath (ltlocation_id,
ltlocancester_id) '
|| 'VALUES (' || new.id || ', ' || workid || ')';
EXCEPTION WHEN unique_violation THEN
-- do nothing
END;
SELECT INTO workid parent FROM ltlocation WHERE id = workid;
END LOOP;
RETURN new;
END;
$$ LANGUAGE plpgsql;
And the Trigger is defined as:
CREATE TRIGGER ltlocationpathtrigger AFTER INSERT OR UPDATE ON
ltlocation FOR EACH ROW EXECUTE PROCEDURE populatelocationpath();
The strange thing is:
insert is OK (materialized path gets populated)
update of parent column is OK old values get delete and new ones get
inserted
but if the exception handling of the unique_violation exception is
removed an update on the id column fails, with
an duplicate pkey violation an the self reference in the materialized
path eg for the values (25, 25)
It works OK with ignoring the exception but why is the exception
thrown in the first place.
The postgresql version 8.1.5
- --
Viele Grüße,
Lars Heidieker
lars@heidieker.de
http://paradoxon.info
- ------------------------------------
Mystische Erklärungen.
Die mystischen Erklärungen gelten für tief;
die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind.
-- Friedrich Nietzsche
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (Darwin)
iD4DBQFFgbkKcxuYqjT7GRYRArhdAJ9s9uGGJX34mD2hGXgZxF78ZbBXIgCY6RvE
jhAObk1zUpvAZ4gGnFAk5w==
=qyV9
-----END PGP SIGNATURE-----
> The strange thing is: > insert is OK (materialized path gets populated) > update of parent column is OK old values get delete and new ones get > inserted > but if the exception handling of the unique_violation exception is > removed an update on the id column fails, with > an duplicate pkey violation an the self reference in the > materialized > path eg for the values (25, 25) > > It works OK with ignoring the exception but why is the exception > thrown in the first place. Could you provide a sequence of INSERT and UPDATE statements that produce the problem you describe? Yours, Laurenz Albe
Lars Heidieker wrote:
> Hi all,
>
> I just started to write my first stored procedure in plpgsql and
> installed a trigger for it.
>
> The two Tables are:
>
> CREATE TABLE ltlocation (
> "id" integer DEFAULT nextval('ltlocation_id_seq'::text) NOT NULL,
> name varchar(30) NOT NULL default '',
> "description" varchar(254) NOT NULL default '',
> "parent" int4,
I think you can do without this column; it's already defined by your
location path and it constrains your hierarchy to single parent nodes.
If you're sure single parent nodes are sufficient, you're probably
better off using the ltree contrib package. You'll still have to handle
tree integrity yourself, but you'll have an optimized index and
functions to navigate the tree.
> type int2 NOT NULL default '0',
Why the typecast? A string isn't an integer, you know...
> PRIMARY KEY (id)
> ) ;
>
> just to hold a tree Structure and the second one is:
>
> CREATE TABLE ltlocationpath (
> "ltlocation_id" int4 NOT NULL default '0',
> "ltlocancester_id" int4 NOT NULL default '0',
And you're "forcing" your column names to lower case here; whether you
need to is up to you, of course.
> PRIMARY KEY (ltlocation_id,ltlocancester_id)
> ) ;
> The Stored Procedure is:
>
> CREATE OR REPLACE FUNCTION populatelocationpath() RETURNS trigger AS $$
> DECLARE
> workid integer := 0;
> BEGIN
> IF tg_op = 'UPDATE' THEN
> DELETE FROM ltlocationpath WHERE ltlocation_id = old.id;
> END IF;
>
> workid := new.id;
Are you sure you want locations to reference themselves? That may also
be where your unique constraint violation originates.
I think I'd use something along the lines of:
workid := new.parent;
LOOP
INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id)
VALUES (new.id, workid);
SELECT INTO workid ...
-- Assuming the top nodes have NULL parents
EXIT WHEN parent IS NULL;
END LOOP;
> WHILE workid > 0 LOOP
> BEGIN
> EXECUTE 'INSERT INTO ltlocationpath (ltlocation_id,
> ltlocancester_id) '
> || 'VALUES (' || new.id || ', ' || workid || ')';
I don't think you need a dynamic query here (see my example).
> EXCEPTION WHEN unique_violation THEN
> -- do nothing
> END;
>
> SELECT INTO workid parent FROM ltlocation WHERE id = workid;
> END LOOP;
> RETURN new;
> END;
> $$ LANGUAGE plpgsql;
>
> And the Trigger is defined as:
>
> CREATE TRIGGER ltlocationpathtrigger AFTER INSERT OR UPDATE ON
> ltlocation FOR EACH ROW EXECUTE PROCEDURE populatelocationpath();
>
> The strange thing is:
> insert is OK (materialized path gets populated)
> update of parent column is OK old values get delete and new ones get
> inserted
> but if the exception handling of the unique_violation exception is
> removed an update on the id column fails, with
> an duplicate pkey violation an the self reference in the materialized
> path eg for the values (25, 25)
I think that is because your workid will be back at 25 in the next
iteration when that happens, because of the self-reference.
>
> It works OK with ignoring the exception but why is the exception thrown
> in the first place.
>
> The postgresql version 8.1.5
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 18 Dec 2006, at 09:26, Albe Laurenz wrote:
>> The strange thing is:
>> insert is OK (materialized path gets populated)
>> update of parent column is OK old values get delete and new ones get
>> inserted
>> but if the exception handling of the unique_violation exception is
>> removed an update on the id column fails, with
>> an duplicate pkey violation an the self reference in the
>> materialized
>> path eg for the values (25, 25)
>>
>> It works OK with ignoring the exception but why is the exception
>> thrown in the first place.
>
> Could you provide a sequence of INSERT and UPDATE statements
> that produce the problem you describe?
>
>
Currently I can only reproduce the following error if the exception
handling is removed.
ERROR: insert or update on table "ltlocationpath" violates foreign
key constraint "ltlocancester_fkey"
DETAIL: Key (ltlocancester_id)=(18999) is not present in table
"ltlocation".
CONTEXT: SQL statement "UPDATE ONLY "public"."ltlocationpath" SET
"ltlocation_id" = $1 WHERE "ltlocation_id" = $2"
The table ltlocation is filled with:
id | name | description | parent | type
- ----+-----------------+---------------+--------+------
1 | <i>location</i> | root location | | 0
2 | Images | | 1 | 0
ltlocationpath:
ltlocation_id | ltlocancester_id
- ---------------+------------------
1 | 1
2 | 1
2 | 2
INSERT INTO ltlocation (id, parent, name, description, type) VALUES
(18999, 2, 'test', '', 0);
UPDATE ltlocation SET id = 45555 WHERE id = 18999;
Should produce it.
I am just trying out a few thing Alban Hertroys suggested to get an
better understanding of what is going on.
- --
Viele Grüße,
Lars Heidieker
lars@heidieker.de
http://paradoxon.info
- ------------------------------------
Mystische Erklärungen.
Die mystischen Erklärungen gelten für tief;
die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind.
-- Friedrich Nietzsche
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (Darwin)
iD8DBQFFhxhNcxuYqjT7GRYRAlp5AKCnlzAXOCIWbWn7uUd6AUxVb9VAugCg05Kd
kb8Z12MrU2c6q9AB3z9Fzh8=
=y4Av
-----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 18 Dec 2006, at 09:37, Alban Hertroys wrote:
> Lars Heidieker wrote:
>> Hi all,
>>
>> I just started to write my first stored procedure in plpgsql and
>> installed a trigger for it.
>>
>> The two Tables are:
>>
>> CREATE TABLE ltlocation (
>> "id" integer DEFAULT nextval('ltlocation_id_seq'::text) NOT NULL,
>> name varchar(30) NOT NULL default '',
>> "description" varchar(254) NOT NULL default '',
>> "parent" int4,
>
> I think you can do without this column; it's already defined by your
> location path and it constrains your hierarchy to single parent nodes.
>
> If you're sure single parent nodes are sufficient, you're probably
> better off using the ltree contrib package. You'll still have to
> handle
> tree integrity yourself, but you'll have an optimized index and
> functions to navigate the tree.
>
Yes, that is an option (and I am thinking about it) I only need
single parent nodes, I'll have a look at the ltree package,
the database is just quickly ported from another DBMS and I tried to
get my hands into the stored procedures.
>> type int2 NOT NULL default '0',
>
> Why the typecast? A string isn't an integer, you know...
True the create script was automatically created and I haven changed
it much.....
(Sure it needs a rewrite)
>
>> PRIMARY KEY (id)
>> ) ;
>>
>> just to hold a tree Structure and the second one is:
>>
>> CREATE TABLE ltlocationpath (
>> "ltlocation_id" int4 NOT NULL default '0',
>> "ltlocancester_id" int4 NOT NULL default '0',
>
> And you're "forcing" your column names to lower case here; whether you
> need to is up to you, of course.
>
That's a left over from the automatic conversion as well.
>> PRIMARY KEY (ltlocation_id,ltlocancester_id)
>> ) ;
>
>
>> The Stored Procedure is:
>>
>> CREATE OR REPLACE FUNCTION populatelocationpath() RETURNS trigger
>> AS $$
>> DECLARE
>> workid integer := 0;
>> BEGIN
>> IF tg_op = 'UPDATE' THEN
>> DELETE FROM ltlocationpath WHERE ltlocation_id = old.id;
>> END IF;
>>
>> workid := new.id;
>
> Are you sure you want locations to reference themselves? That may also
> be where your unique constraint violation originates.
>
> I think I'd use something along the lines of:
>
> workid := new.parent;
> LOOP
> INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id)
> VALUES (new.id, workid);
>
> SELECT INTO workid ...
>
> -- Assuming the top nodes have NULL parents
> EXIT WHEN parent IS NULL;
> END LOOP;
>
That's good to hear that it can be written more easy (no dynamic SQL
necessary in this case)
In your example I don't get the self reference I have to check if I
need to rewrite some other queries for that,
but sure the self reference is actually useless data.
>> WHILE workid > 0 LOOP
>> BEGIN
>> EXECUTE 'INSERT INTO ltlocationpath (ltlocation_id,
>> ltlocancester_id) '
>> || 'VALUES (' || new.id || ', ' || workid || ')';
>
> I don't think you need a dynamic query here (see my example).
>
>> EXCEPTION WHEN unique_violation THEN
>> -- do nothing
>> END;
>>
>> SELECT INTO workid parent FROM ltlocation WHERE id = workid;
>> END LOOP;
>> RETURN new;
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> And the Trigger is defined as:
>>
>> CREATE TRIGGER ltlocationpathtrigger AFTER INSERT OR UPDATE ON
>> ltlocation FOR EACH ROW EXECUTE PROCEDURE populatelocationpath();
>>
>> The strange thing is:
>> insert is OK (materialized path gets populated)
>> update of parent column is OK old values get delete and new ones get
>> inserted
>> but if the exception handling of the unique_violation exception is
>> removed an update on the id column fails, with
>> an duplicate pkey violation an the self reference in the materialized
>> path eg for the values (25, 25)
>
> I think that is because your workid will be back at 25 in the next
> iteration when that happens, because of the self-reference.
>
Not sure as I deleted them before, but currently I cant reproduce it.
I just get the following now:
ERROR: insert or update on table "ltlocationpath" violates foreign
key constraint "ltlocancester_fkey"
DETAIL: Key (ltlocancester_id)=(18999) is not present in table
"ltlocation".
CONTEXT: SQL statement "UPDATE ONLY "public"."ltlocationpath" SET
"ltlocation_id" = $1 WHERE "ltlocation_id" = $2"
on: UPDATE ltlocation SET id = 45555 WHERE id = 18999;
which I don't get if:
IF tg_op = 'UPDATE' THEN
DELETE FROM ltlocationpath WHERE ltlocation_id = old.id;
END IF;
is executed.
Probably I am running in some bad interaction between triggers and
foreign key constraints (cascading)
I'll just continue to play around to get a better understanding.
- --
Viele Grüße,
Lars Heidieker
lars@heidieker.de
http://paradoxon.info
- ------------------------------------
Mystische Erklärungen.
Die mystischen Erklärungen gelten für tief;
die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind.
-- Friedrich Nietzsche
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (Darwin)
iD8DBQFFhxxKcxuYqjT7GRYRAgzpAJ9A74MnEFgu7huobM/U6aCK9Y/PlACgxRwW
UHI7RENIUipoarw3UY+Zn9w=
=hJSD
-----END PGP SIGNATURE-----
> CREATE TABLE ltlocation (
> "id" integer DEFAULT nextval('ltlocation_id_seq'::text) NOT NULL,
> name varchar(30) NOT NULL default '',
> "description" varchar(254) NOT NULL default '',
> "parent" int4,
> type int2 NOT NULL default '0',
> PRIMARY KEY (id)
> ) ;
>
> just to hold a tree Structure and the second one is:
>
> CREATE TABLE ltlocationpath (
> "ltlocation_id" int4 NOT NULL default '0',
> "ltlocancester_id" int4 NOT NULL default '0',
> PRIMARY KEY (ltlocation_id,ltlocancester_id)
> ) ;
>
> where the second one holds a materialized path view of the first one.
> These constraints are defined:
>
> ALTER TABLE ONLY ltlocation ADD CONSTRAINT parent_fkey
> FOREIGN KEY (parent) REFERENCES ltlocation(id)
> ON UPDATE CASCADE ON DELETE CASCADE;
> ALTER TABLE ONLY ltlocationpath ADD CONSTRAINT ltlocation_fkey
> FOREIGN KEY (ltlocation_id) REFERENCES
> ltlocation(id) ON UPDATE CASCADE ON DELETE CASCADE;
> ALTER TABLE ONLY ltlocationpath ADD CONSTRAINT ltlocancester_fkey
> FOREIGN KEY (ltlocancester_id) REFERENCES
> ltlocation(id) ON UPDATE CASCADE ON DELETE CASCADE;
>
> The Stored Procedure is:
>
> CREATE OR REPLACE FUNCTION populatelocationpath() RETURNS
> trigger AS $$
> DECLARE
> workid integer := 0;
> BEGIN
> IF tg_op = 'UPDATE' THEN
> DELETE FROM ltlocationpath WHERE ltlocation_id = old.id;
> END IF;
>
> workid := new.id;
> WHILE workid > 0 LOOP
> BEGIN
> EXECUTE 'INSERT INTO ltlocationpath
(ltlocation_id, ltlocancester_id) '
> || 'VALUES (' || new.id || ', ' ||
workid || ')';
> EXCEPTION WHEN unique_violation THEN
> -- do nothing
I added here:
RAISE NOTICE 'An exception! new.id = %, workid = %', new.id, workid;
> END;
>
> SELECT INTO workid parent FROM ltlocation WHERE id =
workid;
> END LOOP;
> RETURN new;
> END;
> $$ LANGUAGE plpgsql;
>
> And the Trigger is defined as:
>
> CREATE TRIGGER ltlocationpathtrigger AFTER INSERT OR UPDATE ON
> ltlocation FOR EACH ROW EXECUTE PROCEDURE populatelocationpath();
>
> The strange thing is:
> insert is OK (materialized path gets populated)
> update of parent column is OK old values get delete and new ones get
inserted
> but if the exception handling of the unique_violation exception is
> removed an update on the id column fails, with
> an duplicate pkey violation an the self reference in the materialized
> path eg for the values (25, 25)
>
> It works OK with ignoring the exception but why is the exception
> thrown in the first place.
With your examples I can reproduce the error and explain what is going
on.
test=> select * from ltlocation;
id | name | description | parent | type
-------+-----------------+---------------+--------+------
1 | <i>location</i> | root location | | 0
2 | Images | | 1 | 0
18999 | test | | 2 | 0
test=> select * from ltlocationpath;
ltlocation_id | ltlocancester_id
---------------+------------------
1 | 1
2 | 2
2 | 1
18999 | 18999
18999 | 2
18999 | 1
(6 rows)
test=> UPDATE ltlocation SET id = 45555 WHERE id = 18999;
NOTICE: An exception! new.id = 45555, workid = 45555
NOTICE: An exception! new.id = 45555, workid = 2
NOTICE: An exception! new.id = 45555, workid = 1
UPDATE 1
Here is a chronological description of what takes place when you
do the update:
- 'id' in ltlocation is changed from 18999 to 45555.
- The foreign key ltlocancester_fkey, which is defined as
ON UPDATE CASCADE changes 'ltlocancester_id' in table
ltlocationpath from 18999 to 45555 in one record.
- The foreign key ltlocation_fkey, which is defined as
ON UPDATE CASCADE changes 'ltlocation_id' in table
ltlocationpath from 18999 to 45555 in three record.
- Trigger 'ltlocationpathtrigger' fires and does the following:
- DELETE FROM ltlocationpath WHERE ltlocation_id = 18999
This does not match any record in ltlocationpath, 0 records
are deleted.
- INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id)
VALUES (45555, 45555)
This violates the primary key on ltlocationpath since there
is also such a record.
- INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id)
VALUES (45555, 2)
This violates the primary key on ltlocationpath since there
is also such a record.
- INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id)
VALUES (45555, 1)
This violates the primary key on ltlocationpath since there
is also such a record.
Essentially, you're doing the same thing twice, once through the
foreign key constraint, and once in the trigger function.
Yours,
Laurenz Albe
Lars Heidieker wrote: >>>> The strange thing is: >>>> insert is OK (materialized path gets populated) >>>> update of parent column is OK old values get delete and new ones get >>>> inserted >>>> but if the exception handling of the unique_violation exception is >>>> removed an update on the id column fails, with >>>> an duplicate pkey violation an the self reference in the materialized >>>> path eg for the values (25, 25) >>> >>> I think that is because your workid will be back at 25 in the next >>> iteration when that happens, because of the self-reference. >>> > > Not sure as I deleted them before, but currently I cant reproduce it. > I just get the following now: > ERROR: insert or update on table "ltlocationpath" violates foreign key > constraint "ltlocancester_fkey" > DETAIL: Key (ltlocancester_id)=(18999) is not present in table > "ltlocation". > CONTEXT: SQL statement "UPDATE ONLY "public"."ltlocationpath" SET > "ltlocation_id" = $1 WHERE "ltlocation_id" = $2" Looks like a locationpath being inserted with an invalid ltlocancestorid; probably the first or the last record inserted is wrong. > on: UPDATE ltlocation SET id = 45555 WHERE id = 18999; Not something that will actually happen in your application, I bet (what's the point of modifying an artificial key?); no reason it shouldn't work, though. > which I don't get if: > IF tg_op = 'UPDATE' THEN > DELETE FROM ltlocationpath WHERE ltlocation_id = old.id; > END IF; > is executed. I didn't suggest to remove that block, though it can probably be handled more elegantly (fe. only if a column referenced by ltlocationpath changed). I left it out because it wasn't part of what I tried to explain. > Probably I am running in some bad interaction between triggers and > foreign key constraints (cascading) > I'll just continue to play around to get a better understanding. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 19 Dec 2006, at 08:45, Albe Laurenz wrote: > > With your examples I can reproduce the error and explain what is going > on. > > test=> select * from ltlocation; > id | name | description | parent | type > -------+-----------------+---------------+--------+------ > 1 | <i>location</i> | root location | | 0 > 2 | Images | | 1 | 0 > 18999 | test | | 2 | 0 > > test=> select * from ltlocationpath; > ltlocation_id | ltlocancester_id > ---------------+------------------ > 1 | 1 > 2 | 2 > 2 | 1 > 18999 | 18999 > 18999 | 2 > 18999 | 1 > (6 rows) > > test=> UPDATE ltlocation SET id = 45555 WHERE id = 18999; > NOTICE: An exception! new.id = 45555, workid = 45555 > NOTICE: An exception! new.id = 45555, workid = 2 > NOTICE: An exception! new.id = 45555, workid = 1 > UPDATE 1 > > Here is a chronological description of what takes place when you > do the update: > > - 'id' in ltlocation is changed from 18999 to 45555. > - The foreign key ltlocancester_fkey, which is defined as > ON UPDATE CASCADE changes 'ltlocancester_id' in table > ltlocationpath from 18999 to 45555 in one record. > - The foreign key ltlocation_fkey, which is defined as > ON UPDATE CASCADE changes 'ltlocation_id' in table > ltlocationpath from 18999 to 45555 in three record. > - Trigger 'ltlocationpathtrigger' fires and does the following: > - DELETE FROM ltlocationpath WHERE ltlocation_id = 18999 > This does not match any record in ltlocationpath, 0 records > are deleted. > - INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id) > VALUES (45555, 45555) > This violates the primary key on ltlocationpath since there > is also such a record. > - INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id) > VALUES (45555, 2) > This violates the primary key on ltlocationpath since there > is also such a record. > - INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id) > VALUES (45555, 1) > This violates the primary key on ltlocationpath since there > is also such a record. > > Essentially, you're doing the same thing twice, once through the > foreign key constraint, and once in the trigger function. > > Yours, > Laurenz Albe > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > Thanks, this explains what is going on. I had the thought that it might be some bad interaction between the trigger and foreign key constraint. So it makes sense to rewrite the trigger to take only care of those cases that aren't handled by the cascading foreign keys. - -- Viele Grüße, Lars Heidieker lars@heidieker.de http://paradoxon.info - ------------------------------------ Mystische Erklärungen. Die mystischen Erklärungen gelten für tief; die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind. -- Friedrich Nietzsche -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (Darwin) iD8DBQFFh71ScxuYqjT7GRYRAit9AKCXIVHx28D1V1VURBuqCWdKzcXSQQCgy6yq ne2AYpvhd6CPAfPfP8Ll1qw= =/w5f -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 19 Dec 2006, at 08:56, Alban Hertroys wrote: >> >> Not sure as I deleted them before, but currently I cant reproduce it. >> I just get the following now: >> ERROR: insert or update on table "ltlocationpath" violates >> foreign key >> constraint "ltlocancester_fkey" >> DETAIL: Key (ltlocancester_id)=(18999) is not present in table >> "ltlocation". >> CONTEXT: SQL statement "UPDATE ONLY "public"."ltlocationpath" SET >> "ltlocation_id" = $1 WHERE "ltlocation_id" = $2" > > Looks like a locationpath being inserted with an invalid > ltlocancestorid; probably the first or the last record inserted is > wrong. > >> on: UPDATE ltlocation SET id = 45555 WHERE id = 18999; > > Not something that will actually happen in your application, I bet > (what's the point of modifying an artificial key?); no reason it > shouldn't work, though. > >> which I don't get if: >> IF tg_op = 'UPDATE' THEN >> DELETE FROM ltlocationpath WHERE ltlocation_id = old.id; >> END IF; >> is executed. > > I didn't suggest to remove that block, though it can probably be > handled > more elegantly (fe. only if a column referenced by ltlocationpath > changed). I left it out because it wasn't part of what I tried to > explain. > >> Probably I am running in some bad interaction between triggers and >> foreign key constraints (cascading) >> I'll just continue to play around to get a better understanding. > Thanks, yes the ltlocancester_id is invalid as it was changed by the foreign key constraint before, in the end it turned out to be some bad interaction between the trigger and the foreign key constraint, as Albe Laurenz found out. That's where I got confused. (Yes, updating the primary key doesn't happen, but I thought the trigger should be able to handle that.) - -- Viele Grüße, Lars Heidieker lars@heidieker.de http://paradoxon.info - ------------------------------------ Mystische Erklärungen. Die mystischen Erklärungen gelten für tief; die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind. -- Friedrich Nietzsche -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (Darwin) iD8DBQFFh79UcxuYqjT7GRYRAu5WAKCXKY0GzAbSV5fxuBH6ANCddGVIWwCeJLcE 0vXHy8xr/Y54A9AYz95Aaqk= =zRtO -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 19 Dec 2006, at 10:30, Lars Heidieker wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > On 19 Dec 2006, at 08:56, Alban Hertroys wrote: > >>> >>> Not sure as I deleted them before, but currently I cant reproduce >>> it. >>> I just get the following now: >>> ERROR: insert or update on table "ltlocationpath" violates >>> foreign key >>> constraint "ltlocancester_fkey" >>> DETAIL: Key (ltlocancester_id)=(18999) is not present in table >>> "ltlocation". >>> CONTEXT: SQL statement "UPDATE ONLY "public"."ltlocationpath" SET >>> "ltlocation_id" = $1 WHERE "ltlocation_id" = $2" >> >> Looks like a locationpath being inserted with an invalid >> ltlocancestorid; probably the first or the last record inserted is >> wrong. >> >>> on: UPDATE ltlocation SET id = 45555 WHERE id = 18999; >> >> Not something that will actually happen in your application, I bet >> (what's the point of modifying an artificial key?); no reason it >> shouldn't work, though. >> >>> which I don't get if: >>> IF tg_op = 'UPDATE' THEN >>> DELETE FROM ltlocationpath WHERE ltlocation_id = old.id; >>> END IF; >>> is executed. >> >> I didn't suggest to remove that block, though it can probably be >> handled >> more elegantly (fe. only if a column referenced by ltlocationpath >> changed). I left it out because it wasn't part of what I tried to >> explain. >> >>> Probably I am running in some bad interaction between triggers and >>> foreign key constraints (cascading) >>> I'll just continue to play around to get a better understanding. >> > > Thanks, yes the ltlocancester_id is invalid as it was changed by > the foreign key constraint before, > in the end it turned out to be some bad interaction between the > trigger and the foreign key constraint, > as Albe Laurenz found out. > That's where I got confused. > > (Yes, updating the primary key doesn't happen, but I thought the > trigger should be able to handle that.) > Actually I just figured out, that is is not the trigger but the two cascade on update triggers collide. It happens also without the trigger: lt=# UPDATE ltlocation SET id = 45555 WHERE id = 18999; ERROR: insert or update on table "ltlocationpath" violates foreign key constraint "ltlocancester_fkey" DETAIL: Key (ltlocancester_id)=(18999) is not present in table "ltlocation". CONTEXT: SQL statement "UPDATE ONLY "public"."ltlocationpath" SET "ltlocation_id" = $1 WHERE "ltlocation_id" = $2" I think the two foreign key constraints together make it impossible to change the primary key (which isn't needed), as they would have to run "as one" which they can't... - -- Viele Grüße, Lars Heidieker lars@heidieker.de http://paradoxon.info - ------------------------------------ Mystische Erklärungen. Die mystischen Erklärungen gelten für tief; die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind. -- Friedrich Nietzsche -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (Darwin) iD8DBQFFh8shcxuYqjT7GRYRAtz+AJ42TizNIN13rOyGpKFjaXitxR3AdQCeI2RP oFbKgeuD4vCDDBQAxxz4L/8= =orBT -----END PGP SIGNATURE-----
> Actually I just figured out, that is is not the trigger but the two > cascade on update triggers collide. > It happens also without the trigger: > > lt=# UPDATE ltlocation SET id = 45555 WHERE id = 18999; > ERROR: insert or update on table "ltlocationpath" violates foreign > key constraint "ltlocancester_fkey" > DETAIL: Key (ltlocancester_id)=(18999) is not present in table > "ltlocation". > CONTEXT: SQL statement "UPDATE ONLY "public"."ltlocationpath" SET > "ltlocation_id" = $1 WHERE "ltlocation_id" = $2" > > I think the two foreign key constraints together make it impossible > to change the primary key (which isn't needed), > as they would have to run "as one" which they can't... The two foreign key constraints worked fine when I tried them, the only problem was the trigger. I don't get what you describe. Have you changed anything in the definitions? If yes, post table, key, and trigger definitions as you have them now. Yours, Laurenz Albe
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 19 Dec 2006, at 11:44, Albe Laurenz wrote:
>> Actually I just figured out, that is is not the trigger but the two
>> cascade on update triggers collide.
>> It happens also without the trigger:
>>
>> lt=# UPDATE ltlocation SET id = 45555 WHERE id = 18999;
>> ERROR: insert or update on table "ltlocationpath" violates foreign
>> key constraint "ltlocancester_fkey"
>> DETAIL: Key (ltlocancester_id)=(18999) is not present in table
>> "ltlocation".
>> CONTEXT: SQL statement "UPDATE ONLY "public"."ltlocationpath" SET
>> "ltlocation_id" = $1 WHERE "ltlocation_id" = $2"
>>
>> I think the two foreign key constraints together make it impossible
>> to change the primary key (which isn't needed),
>> as they would have to run "as one" which they can't...
>
> The two foreign key constraints worked fine when I tried them,
> the only problem was the trigger.
>
> I don't get what you describe.
>
> Have you changed anything in the definitions?
>
> If yes, post table, key, and trigger definitions as you have them now.
>
Yes you are right, I must have messed something up when I tried that,
the foreign keys work properly.
Nevertheless I changed the Trigger Function to the following:
CREATE OR REPLACE FUNCTION populatelocationpath() RETURNS trigger AS $$
DECLARE
workid integer := 0;
BEGIN
IF tg_op = 'UPDATE' THEN
IF old.parent <> new.parent THEN
DELETE FROM ltlocationpath WHERE ltlocation_id = new.id;
workid := new.id;
WHILE workid > 0 LOOP
INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id)
VALUES (new.id, workid);
SELECT INTO workid parent FROM ltlocation WHERE id = workid;
END LOOP;
END IF;
END IF;
IF tg_op = 'INSERT' then
workid := new.id;
WHILE workid > 0 LOOP
INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id)
VALUES (new.id, workid);
SELECT INTO workid parent FROM ltlocation WHERE id = workid;
END LOOP;
END IF;
RETURN new;
END;
$$ LANGUAGE plpgsql;
So it handles only the cases the foreign keys can't and now it works!
- --
Viele Grüße,
Lars Heidieker
lars@heidieker.de
http://paradoxon.info
- ------------------------------------
Mystische Erklärungen.
Die mystischen Erklärungen gelten für tief;
die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind.
-- Friedrich Nietzsche
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (Darwin)
iD8DBQFFiStycxuYqjT7GRYRAn3QAKDCkDL1DZy0xi7t04XeZTl/4Ng3+wCgyOSe
dhd3fFsifDjtY3BGpCP/5rY=
=5IBW
-----END PGP SIGNATURE-----