Re: Stored Procedure and Trigger they puzzle me - Mailing list pgsql-general

-----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-----