Re: Stored Procedure and Trigger they puzzle me

-----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(at)heidieker(dot)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-----