Thread: 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/