Thread: trigger functions broken?
Hi, Trigger functions are supposed to be able to be called only as triggers, but apparently the check is not working in CVS HEAD: alvherre=# create or replace function foo () returns trigger as $$ begin perform 1; return new; end; $$ language plpgsql; CREATE FUNCTION alvherre=# select foo();foo ----- (1 fila) -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
I get the same thing on 8.2.9. ...Robert On Wed, Oct 8, 2008 at 2:29 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Hi, > > Trigger functions are supposed to be able to be called only as triggers, > but apparently the check is not working in CVS HEAD: > > alvherre=# create or replace function foo () returns trigger as $$ begin perform 1; return new; end; $$ language plpgsql; > CREATE FUNCTION > alvherre=# select foo(); > foo > ----- > > (1 fila) > > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
am Wed, dem 08.10.2008, um 14:29:23 -0400 mailte Alvaro Herrera folgendes: > Hi, > > Trigger functions are supposed to be able to be called only as triggers, > but apparently the check is not working in CVS HEAD: > > alvherre=# create or replace function foo () returns trigger as $$ begin perform 1; return new; end; $$ language plpgsql; > CREATE FUNCTION > alvherre=# select foo(); > foo > ----- > > (1 fila) And? The function returns a TRIGGER, not a value. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Wed, Oct 8, 2008 at 3:56 PM, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote: > am Wed, dem 08.10.2008, um 14:29:23 -0400 mailte Alvaro Herrera folgendes: >> Hi, >> >> Trigger functions are supposed to be able to be called only as triggers, >> but apparently the check is not working in CVS HEAD: >> >> alvherre=# create or replace function foo () returns trigger as $$ begin perform 1; return new; end; $$ language plpgsql; >> CREATE FUNCTION >> alvherre=# select foo(); >> foo >> ----- >> >> (1 fila) > > > And? > > The function returns a TRIGGER, not a value. > actually, that means that you can return undefined values for NEW and OLD...and worse you can update other tables based on undefined NEW/OLD values? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
A. Kretschmer wrote:
> am Wed, dem 08.10.2008, um 14:29:23 -0400 mailte Alvaro Herrera folgendes:
> > Hi,
> >
> > Trigger functions are supposed to be able to be called only as triggers,
> > but apparently the check is not working in CVS HEAD:
> >
> > alvherre=# create or replace function foo () returns trigger as $$ begin perform 1; return new; end; $$ language
plpgsql;
> > CREATE FUNCTION
> > alvherre=# select foo();
> > foo
> > -----
> >
> > (1 fila)
>
> And?
And the source says that this is not allowed:
/* Disallow pseudotype result, except VOID or RECORD */ /* (note we already replaced polymorphic
types)*/ if (typeStruct->typtype == TYPTYPE_PSEUDO) { if (rettypeid == VOIDOID ||
rettypeid == RECORDOID) /* okay */ ; else if (rettypeid == TRIGGEROID)
ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("trigger functions can only be called as triggers"))); else ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("plpgsql functions
cannotreturn type %s", format_type_be(rettypeid)))); }
> The function returns a TRIGGER, not a value.
Precisely.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Trigger functions are supposed to be able to be called only as triggers,
> but apparently the check is not working in CVS HEAD:
Hmm, some experimentation shows that 7.4 is the only active branch that
throws an error for that. Did we change it intentionally?
regards, tom lane
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Trigger functions are supposed to be able to be called only as triggers,
> but apparently the check is not working in CVS HEAD:
I traced through this, and what is happening is that the validator's
trial compilation of the function doesn't complain (as indeed it
shouldn't) but then it produces a function cache entry that successfully
matches the non-trigger call later. Since the error check is made while
compiling, it doesn't happen during that call. So the proximate cause
is that compute_function_hashkey() is failing to ensure that the hash
keys are distinct in the two cases.
You do get an error when you try to call the function in a session other
than the one that defined it.
I wonder whether we should allow the validator to produce a persistent
cache entry at all. I guess in simple cases (not trigger, not
polymorphic) the validator's compilation is perfectly fine, but it
seems like trouble waiting to happen.
regards, tom lane
On Wed, 2008-10-08 at 20:56 +0200, A. Kretschmer wrote: > am Wed, dem 08.10.2008, um 14:29:23 -0400 mailte Alvaro Herrera folgendes: > > Hi, > > > > Trigger functions are supposed to be able to be called only as triggers, > > but apparently the check is not working in CVS HEAD: > > > > alvherre=# create or replace function foo () returns trigger as $$ begin perform 1; return new; end; $$ language plpgsql; > > CREATE FUNCTION > > alvherre=# select foo(); > > foo > > ----- > > > > (1 fila) > > > And? > > The function returns a TRIGGER, not a value. Can you do anything with this TRIGGER value ? pl/python's approach seems saner to me: hannu=# create or replace function foo () returns trigger as $$ return $$ language plpythonu; CREATE FUNCTION hannu=# select foo (); ERROR: trigger functions can only be called as triggers ------------------- Hannu