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