Horton

Thread

  1. plpgsql lacks generic identifier for record in triggers...

    Sean Chittenden <sean@chittenden.org> — 2004-11-25T01:06:11Z

    Now that pgmemcache is getting more use, I've heard a couple of groans 
regarding the need to have two functions with exactly the same code 
body.  This is necessary because there is no generic way of handling 
NEW/OLD.  For example:

db=# CREATE FUNCTION schma.tbl_ins_upd() RETURNS TRIGGER AS 'BEGIN
         EXECUTE public.mc_init();
         EXECUTE public.mc_delete(''mc_key'');
         RETURN NEW;
END;' LANGUAGE 'plpgsql';
db=# CREATE FUNCTION schma.tbl_del() RETURNS TRIGGER AS 'BEGIN
         EXECUTE public.mc_init();
         EXECUTE public.mc_delete(''mc_key'');
         RETURN OLD;
END;' LANGUAGE 'plpgsql';
db=# CREATE TRIGGER tbl_ins_upd_trg AFTER INSERT OR UPDATE ON schma.tbl 
FOR EACH ROW EXECUTE PROCEDURE schma.tbl_ins_upd();
db=# CREATE TRIGGER tbl_del_trg AFTER DELETE ON schma.tbl FOR EACH ROW 
EXECUTE PROCEDURE schma.tbl_del();

It's be nice if there was a generic return type so that one could 
collapse those two functions and trigger creation statements into one 
function and one trigger.  Something like:

db=# CREATE FUNCTION schma.tbl_inval() RETURNS TRIGGER AS 'BEGIN
         EXECUTE public.mc_init();
         EXECUTE public.mc_delete(''mc_key'');
         RETURN ROW;
END;' LANGUAGE 'plpgsql';
db=# CREATE TRIGGER tbl_inval_trg AFTER INSERT OR UPDATE OR DELETE ON 
schma.tbl FOR EACH ROW EXECUTE PROCEDURE schma.tbl_inval();


pgmemcache has pushed this to the surface as a problem that otherwise 
wouldn't exist.  That said, plpgsql's semantics are clearly the issue 
here as it's a syntax problem.  ROW being an alias for NEW in the 
INSERT and UPDATE case, and OLD in the DELETE case.  Thoughts?  Would a 
patch be accepted that modified plpgsql's behavior to include a new 
predefined alias?  Better yet, could TRIGGER functions be allowed to 
return nothing (ala VOID)?  For example:

db=# CREATE FUNCTION schma.tbl_inval() RETURNS TRIGGER AS 'BEGIN
         EXECUTE public.mc_init();
         EXECUTE public.mc_delete(''mc_key'');
         RETURN;
END;' LANGUAGE 'plpgsql';
db=# CREATE TRIGGER tbl_inval_trg AFTER INSERT OR UPDATE OR DELETE ON 
schma.tbl FOR EACH ROW EXECUTE PROCEDURE schma.tbl_inval();

Which would tell the backend to assume that the row wasn't changed and 
proceed with its handling.  This is the preferred approach, IMHO... but 
I think is the hardest to achieve (I haven't looked to see what'd be 
involved yet).

Enjoy your T-Day commute if you haven't yet.  -sc

-- 
Sean Chittenden

  2. Re: plpgsql lacks generic identifier for record in triggers...

    Sean Chittenden <sean@chittenden.org> — 2004-11-25T01:23:06Z

    > Now that pgmemcache is getting more use, I've heard a couple of groans 
> regarding the need to have two functions with exactly the same code 
> body.  This is necessary because there is no generic way of handling 
> NEW/OLD.  For example:

[snip]  Err... wait, this is a classic case of send first then 
finishing to pondering the gripe.

> db=# CREATE FUNCTION schma.tbl_inval() RETURNS TRIGGER AS 'BEGIN
>         EXECUTE public.mc_init();
>         EXECUTE public.mc_delete(''mc_key'');
>         RETURN ROW;
> END;' LANGUAGE 'plpgsql';
> db=# CREATE TRIGGER tbl_inval_trg AFTER INSERT OR UPDATE OR DELETE ON 
> schma.tbl FOR EACH ROW EXECUTE PROCEDURE schma.tbl_inval();

A statement trigger should be used instead since the return value is 
ignored (and NULL can be used to satisfy the need for return to 
actually return something).  When updating dynamic keys, you always 
need to be explicit regarding NEW/OLD to get the data version, but for 
static keys, statement triggers are the way to go.  Ex:

> db=# CREATE FUNCTION schma.tbl_inval() RETURNS TRIGGER AS 'BEGIN
>         EXECUTE public.mc_init();
>         EXECUTE public.mc_delete(''mc_key'');
>         RETURN NULL;
> END;' LANGUAGE 'plpgsql';
> db=# CREATE TRIGGER tbl_inval_trg AFTER INSERT OR UPDATE OR DELETE ON 
> schma.tbl FOR EACH STATEMENT EXECUTE PROCEDURE schma.tbl_inval();

Very nice.  -sc

-- 
Sean Chittenden

  3. Stack not being popped correctly (was: Re: [HACKERS] plpgsql lacks generic identifier for record in triggers...)

    Sean Chittenden <sean@chittenden.org> — 2004-11-25T01:42:36Z

    > [snip]  Err... wait, this is a classic case of send first then 
> finishing to pondering the gripe.

And sending a reply to ones self without actually testing my suggestion.

>> db=# CREATE FUNCTION schma.tbl_inval() RETURNS TRIGGER AS 'BEGIN
>>         EXECUTE public.mc_init();
>>         EXECUTE public.mc_delete(''mc_key'');
>>         RETURN NULL;
>> END;' LANGUAGE 'plpgsql';
>> db=# CREATE TRIGGER tbl_inval_trg AFTER INSERT OR UPDATE OR DELETE ON 
>> schma.tbl FOR EACH STATEMENT EXECUTE PROCEDURE schma.tbl_inval();

Which, doesn't work as expected as it seems as though there's something 
left behind on the stack that shouldn't be.  Here's the case to 
reproduce (doesn't involve pgmemcache):

test=# CREATE TABLE t5 (i int);
Time: 35.294 ms
test=# CREATE FUNCTION t5_func() RETURNS TRIGGER AS 'BEGIN EXECUTE 
TRUE; RETURN NULL; END;' LANGUAGE 'plpgsql';
Time: 101.701 ms
test=# CREATE TRIGGER t5_func_trg AFTER INSERT ON t5 FOR EACH STATEMENT 
EXECUTE PROCEDURE t5_func();
Time: 62.345 ms
test=# INSERT INTO t5 VALUES (1);
ERROR:  syntax error at or near "t" at character 1
QUERY:  t
CONTEXT:  PL/pgSQL function "t5_func" line 1 at execute statement
LINE 1: t
         ^
Doh!  Can someone with plpgsql foo look into this?  -sc

-- 
Sean Chittenden

  4. Re: plpgsql lacks generic identifier for record in triggers...

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-11-25T03:24:26Z

    Sean Chittenden <sean@chittenden.org> writes:
> ... Better yet, could TRIGGER functions be allowed to 
> return nothing (ala VOID)?
> Which would tell the backend to assume that the row wasn't changed and 
> proceed with its handling.  This is the preferred approach, IMHO... but 
> I think is the hardest to achieve (I haven't looked to see what'd be 
> involved yet).

plperl is doing it that way, so I don't see why plpgsql couldn't allow
it.

			regards, tom lane

  5. Re: Stack not being popped correctly (was: Re: [HACKERS] plpgsql lacks generic identifier for record in triggers...)

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-11-25T03:36:35Z

    Sean Chittenden <sean@chittenden.org> writes:
> Which, doesn't work as expected as it seems as though there's something 
> left behind on the stack that shouldn't be.  Here's the case to 
> reproduce (doesn't involve pgmemcache):

> test=# CREATE FUNCTION t5_func() RETURNS TRIGGER AS 'BEGIN EXECUTE 
> TRUE; RETURN NULL; END;' LANGUAGE 'plpgsql';

What are you expecting "execute true" to do?  The argument of EXECUTE
is a string, not a boolean, and it's supposed to be a string that looks
like a SQL command.  The result

> ERROR:  syntax error at or near "t" at character 1
> QUERY:  t
> CONTEXT:  PL/pgSQL function "t5_func" line 1 at execute statement
> LINE 1: t
>          ^

is pretty much what I'd expect considering that plpgsql will do whatever
it takes to coerce the expression result to text.

			regards, tom lane

  6. Re: Stack not being popped correctly (was: Re: [HACKERS] plpgsql lacks generic identifier for record in triggers...)

    Sean Chittenden <sean@chittenden.org> — 2004-11-25T04:53:11Z

    >> Which, doesn't work as expected as it seems as though there's 
>> something
>> left behind on the stack that shouldn't be.  Here's the case to
>> reproduce (doesn't involve pgmemcache):
>
>> test=# CREATE FUNCTION t5_func() RETURNS TRIGGER AS 'BEGIN EXECUTE
>> TRUE; RETURN NULL; END;' LANGUAGE 'plpgsql';
>
> What are you expecting "execute true" to do?

Behave the same as PERFORM?  Ugh, how humiliating.  When writing my 
pgmemcache presentation, I erroneously wrote EXECUTE instead of 
PERFORM.  When sending off that little flurry of emails, I was updating 
my pgmemcache presentation and subconsciously propagated the error w/o 
even thinking about it.  :-/  Thanks, I'll take that pumpkin pie on my 
face.  -sc

-- 
Sean Chittenden

  7. Re: plpgsql lacks generic identifier for record in triggers...

    Weiping <laser@qmail.zhengmai.net.cn> — 2004-11-25T10:32:27Z

    >
> db=# CREATE FUNCTION schma.tbl_ins_upd() RETURNS TRIGGER AS 'BEGIN
>         EXECUTE public.mc_init();
>         EXECUTE public.mc_delete(''mc_key'');
>         RETURN NEW;
> END;' LANGUAGE 'plpgsql';
> db=# CREATE FUNCTION schma.tbl_del() RETURNS TRIGGER AS 'BEGIN
>         EXECUTE public.mc_init();
>         EXECUTE public.mc_delete(''mc_key'');
>         RETURN OLD;
> END;' LANGUAGE 'plpgsql';

could this be used?

CREATE FUNCTION schma.tbl_ins_upd() RETURNS TRIGGER AS 'BEGIN

        EXECUTE public.mc_init();
        EXECUTE public.mc_delete(''mc_key'');
    if TG_OP = ''INSERT'' or TG_OP = ''UPDATE'' then
        RETURN NEW;
    else
        RETURN OLD;
    end if;   
END;' LANGUAGE 'plpgsql';


regards

Laser