Re: Stored Procedure and Trigger they puzzle me

> 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