Обсуждение: plpython triggers TD["new"] = None

I have postgres 7.4.6 installed on 2 machines one debian and one
freebsd. Both are the most recent installs of each OS. On both I have
the plpython module and both are having the same issue. Essentially when
a function is called from a trigger the TD tuple get's populated with
all the standard data except new and old have no value (None). Here is
the function code I am working with:

CREATE OR REPLACE FUNCTION public.test_trigger() RETURNS trigger AS
'
plpy.error(TD)

return "OK"
' LANGUAGE 'plpythonu';

The actual trigger is defined as follows:
CREATE TRIGGER trig
  BEFORE INSERT OR UPDATE
  ON public.test
  FOR EACH STATEMENT
  EXECUTE PROCEDURE public.test_trigger();

The following error is generated on both machines when an insert occurs:
ERROR:  plpython: function "test_trigger" failed
DETAIL:  plpy.Error: ({'relid': '17246', 'old': None, 'name': 'trig',
'level': 'STATEMENT', 'args': None, 'when': 'BEFORE', 'new': None,
'event': 'INSERT'},)

Any help would be appreciated. Most likely I've done something wrong
somewhere.

Lee

On Thu, Jan 27, 2005 at 03:13:27PM -0700, Lee Jensen wrote:

> I have postgres 7.4.6 installed on 2 machines one debian and one
> freebsd. Both are the most recent installs of each OS. On both I have
> the plpython module and both are having the same issue. Essentially when
> a function is called from a trigger the TD tuple get's populated with
> all the standard data except new and old have no value (None). Here is
> the function code I am working with:

[snip]

> CREATE TRIGGER trig
>  BEFORE INSERT OR UPDATE
>  ON public.test
>  FOR EACH STATEMENT
            ^^^^^^^^^
>  EXECUTE PROCEDURE public.test_trigger();

OLD and NEW don't make sense in statement-level triggers because
the statement could affect many rows.  Use FOR EACH ROW if you need
to access the row values.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Thu, Jan 27, 2005 at 03:13:27PM -0700, Lee Jensen wrote:
> I have postgres 7.4.6 installed on 2 machines one debian and one
> freebsd. Both are the most recent installs of each OS. On both I have
> the plpython module and both are having the same issue. Essentially when
> a function is called from a
... [FOR EACH STATEMENT] ...
> trigger the TD tuple get's populated with
> all the standard data except new and old have no value (None). Here is
> the function code I am working with:

Statement level triggers don't have access to NEW nor OLD.  Only row
level triggers do ...

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"I suspect most samba developers are already technically insane...
Of course, since many of them are Australians, you can't tell." (L. Torvalds)

On Thu, Jan 27, 2005 at 03:34:55PM -0700, Michael Fuhr wrote:

> OLD and NEW don't make sense in statement-level triggers because
> the statement could affect many rows.  Use FOR EACH ROW if you need
> to access the row values.

IMHO they do make sense.  It's just that they haven't been implemented.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"El miedo atento y previsor es la madre de la seguridad" (E. Burke)

On Thu, Jan 27, 2005 at 07:46:54PM -0300, Alvaro Herrera wrote:
> On Thu, Jan 27, 2005 at 03:34:55PM -0700, Michael Fuhr wrote:
>
> > OLD and NEW don't make sense in statement-level triggers because
> > the statement could affect many rows.  Use FOR EACH ROW if you need
> > to access the row values.
>
> IMHO they do make sense.  It's just that they haven't been implemented.

What do you have in mind?  What would OLD and NEW refer to in
statements that affect multiple rows?  Are you thinking of a way
to refer to all of the old and new rows?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Thu, Jan 27, 2005 at 04:01:32PM -0700, Michael Fuhr wrote:
> On Thu, Jan 27, 2005 at 07:46:54PM -0300, Alvaro Herrera wrote:
> > On Thu, Jan 27, 2005 at 03:34:55PM -0700, Michael Fuhr wrote:
> >
> > > OLD and NEW don't make sense in statement-level triggers because
> > > the statement could affect many rows.  Use FOR EACH ROW if you need
> > > to access the row values.
> >
> > IMHO they do make sense.  It's just that they haven't been implemented.
>
> What do you have in mind?  What would OLD and NEW refer to in
> statements that affect multiple rows?  Are you thinking of a way
> to refer to all of the old and new rows?

Yes, exactly that.  They would be arrays of tuples (or whatever they are
called in Python -- I'm not thinking specifically in Python.)

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Escucha y olvidarás; ve y recordarás; haz y entenderás" (Confucio)

On Thu, Jan 27, 2005 at 08:11:50PM -0300, Alvaro Herrera wrote:
> On Thu, Jan 27, 2005 at 04:01:32PM -0700, Michael Fuhr wrote:
> >
> > What do you have in mind?  What would OLD and NEW refer to in
> > statements that affect multiple rows?  Are you thinking of a way
> > to refer to all of the old and new rows?
>
> Yes, exactly that.  They would be arrays of tuples (or whatever they are
> called in Python -- I'm not thinking specifically in Python.)

Right...when I saw your first response I started digging around one
of the standards and saw references to old and new transition tables
for statement-level triggers.  That would be handy if it could be
implemented without scalability problems.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/