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

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