Old/New

"Bob Pawley" <rjpawley@shaw.ca> writes:

I am getting a strange result when using the following -

Select fluid_id into fluidid
from p_id.processes
where new.pump1 = 'True'
and old.pump1 = 'False'
or old.pump1 is null;

The fluid_id return is fine when there is a single row. However with two rows, and updating only one of the rows, I quite often get the fluid_id for the other row.

That WHERE condition isn't constraining the SELECT at all; you're
getting the result from the first row in the table. I think you have
some fundamental confusion about how to work with OLD and NEW in
triggers. They're just rowtype variables, you do not need to select
from the table to examine them.

regards, tom lane

Following is the format with which I have had great success using "New" in
After Insert triggers.

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
select (p_id.processes.p_id_id), (p_id.processes.process_id),
(p_id.processes.fluid_id), ('Pump #1'), ('11'), ('Pump')
from p_id.processes
where new.pump1 = 'True';

However when the above is used on an After Update trigger on a table with
two rows, I get both rows inserted.

(For a while I thought it was a quirk in the actions of the interface, but
it happens even when I update pump1 using only PostgreSQL.

This is the reason that I have been avoiding Update triggers until now, so,
if anyone can help my understanding of what is happening I would appreciate
it.

Bob

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Thursday, January 21, 2010 3:16 PM
Subject: Re: [GENERAL] Old/New

"Bob Pawley" <rjpawley@shaw.ca> writes:

Following is the format with which I have had great success using "New" in
After Insert triggers.

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
select (p_id.processes.p_id_id), (p_id.processes.process_id),
(p_id.processes.fluid_id), ('Pump #1'), ('11'), ('Pump')
from p_id.processes
where new.pump1 = 'True';

Hmm, maybe for small values of "great success". new.pump1 is simply a
local variable in the plpgsql function. That means that the above
command will have one of two behaviors:

* if new.pump1 has the value 'True', every row in p_id.processes will be
copied into p_id.devices, because the WHERE condition succeeds at
every row;
* if new.pump1 has any other value, nothing gets copied, because the
WHERE condition succeeds nowhere.

Maybe that's actually what you intended, but I rather doubt it. It
seems more likely to me that what you want is something like

if new.pump1 = 'True' then
Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #1', '11', 'Pump');
end if;

which would have the effect of inserting based on the contents of NEW.*
and nothing else.

regards, tom lane

I haven't found any documentation on how the underlying structure of
PostgreSQL actually operates. So I have had to extrapolate.

I think what you are saying is that on an update of a field the whole row
which includes that field is affected to the extent that the whole row falls
under the rules of New/Old.

Is that a fair statement?

However the present problem is that I get two or multiple rows returned when
I update the pump1 field to 'True' - even when there is only a single row in
the table.

The complete After Update trigger follows -

Begin

If new.pump1 = 'True'

then

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #1', '11',
'Pump');

End if;

If new.pump2 = 'True'

then

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #2', '11',
'Pump');

End if ;

RETURN NULL;

END;

Bob

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Friday, January 22, 2010 9:56 AM
Subject: Re: [GENERAL] Old/New

"Bob Pawley" <rjpawley@shaw.ca> writes:

I haven't found any documentation on how the underlying structure of
PostgreSQL actually operates. So I have had to extrapolate.

I think what you are saying is that on an update of a field the whole row
which includes that field is affected to the extent that the whole row falls
under the rules of New/Old.

In triggers, NEW and OLD are just rowtype variables that contain the
entire new and old versions of whatever row the ON UPDATE trigger was
fired for. You do not have to look into the table to find out anything
else about the update event, and doing so is generally a bad idea
because it'll be much slower than just looking at NEW/OLD.

You might be confusing this with the use of NEW/OLD in rules, where they
have a rather different meaning.

regards, tom lane

On 01/22/2010 11:20 AM, Bob Pawley wrote:

I haven't found any documentation on how the underlying structure of
PostgreSQL actually operates. So I have had to extrapolate.

I think what you are saying is that on an update of a field the whole
row which includes that field is affected to the extent that the whole
row falls under the rules of New/Old.

Is that a fair statement?

Maybe an example is in order.

Existing row
id desc pump1
1 test f

UPDATE foo set pump1 ='t';

OLD row
id desc pump1
1 test f

NEW row
id desc pump1
1 test t

At the point the AFTER UPDATE trigger is fired it has access to both the
OLD and NEW rows via the OLD.* and NEW.* variables per Toms explanation.

However the present problem is that I get two or multiple rows returned
when I update the pump1 field to 'True' - even when there is only a
single row in the table.

The complete After Update trigger follows -

Begin

If new.pump1 = 'True'

then

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #1', '11',
'Pump');

End if;

If new.pump2 = 'True'

then

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #2', '11',
'Pump');

End if ;

RETURN NULL;

END;

Bob

This is different from what you originally posted. Is the above still
causing problems?

--
Adrian Klaver
adrian.klaver@gmail.com

Begin

If new.pump1 = 'True'

then

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #1', '11',
'Pump');

End if;

If new.pump2 = 'True'

then

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #2', '11',
'Pump');

End if ;

RETURN NULL;

END;

Bob

This is different from what you originally posted. Is the above still
causing problems?

Yes. The above inserts two versions of the same row.

Bob
----- Original Message -----
From: "Adrian Klaver" <adrian.klaver@gmail.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Friday, January 22, 2010 12:34 PM
Subject: Re: [GENERAL] Old/New

On 01/22/2010 01:05 PM, Bob Pawley wrote:

Begin

If new.pump1 = 'True'

then

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #1', '11',
'Pump');

End if;

If new.pump2 = 'True'

then

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #2', '11',
'Pump');

End if ;

RETURN NULL;

END;

Bob

This is different from what you originally posted. Is the above still
causing problems?

Yes. The above inserts two versions of the same row.

Some detail is in order. Two versions of what row? Also what is your
update statement?
--
Adrian Klaver
adrian.klaver@gmail.com

I have a single row that is being duplicated on insert.

Update statement -
update p_id.processes
set pump1 = 'True'
where p_id.processes.fluid_id = '3501' ;

The proper field is updated.

Bob

----- Original Message -----
From: "Adrian Klaver" <adrian.klaver@gmail.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Friday, January 22, 2010 1:11 PM
Subject: Re: [GENERAL] Old/New

On 01/22/2010 01:16 PM, Bob Pawley wrote:

I have a single row that is being duplicated on insert.

Update statement -
update p_id.processes
set pump1 = 'True'
where p_id.processes.fluid_id = '3501' ;

The proper field is updated.

Bob

This is insufficient detail. What is the row? What are the two versions?
Is the insert you are talking about being done on the table with the
trigger or the table referred to in the trigger?

--
Adrian Klaver
adrian.klaver@gmail.com

I have a table labeled p_id.processes. One row is inserted into it by
another source containing fluid_id 3501 and other fields.

The update statement -
update p_id.processes
set pump1 = 'True'
where p_id.processes.fluid_id = '3501' ;
updates the field pump1 to 'True'.

The After Update trigger has an insert statement that inserts the
p_id.processes row into the table p_id.devices. For pump1 I want only one
row inserted into p_id.devices containing fluid_id = '3501 and 'Pump #1'.

Insert statement -
Begin

If new.pump1 = 'True'

then

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #1', '11',
'Pump');

End if;

Instead I get two identical rows inserted containing the fluid_id = '3501'
and 'Pump #1'.

Bob

----- Original Message -----
From: "Adrian Klaver" <adrian.klaver@gmail.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Friday, January 22, 2010 1:24 PM
Subject: Re: [GENERAL] Old/New

"Bob Pawley" <rjpawley@shaw.ca> writes:

Instead I get two identical rows inserted containing the fluid_id = '3501'
and 'Pump #1'.

Seems like the only way that's possible with the INSERT .. VALUES
formulation is if the trigger function gets executed twice. Maybe you
accidentally created two instances of the trigger? psql's \d on the
table should list the triggers for you.

regards, tom lane

On Friday 22 January 2010 2:05:02 pm Tom Lane wrote:

"Bob Pawley" <rjpawley@shaw.ca> writes:

Instead I get two identical rows inserted containing the fluid_id =
'3501' and 'Pump #1'.

Seems like the only way that's possible with the INSERT .. VALUES
formulation is if the trigger function gets executed twice. Maybe you
accidentally created two instances of the trigger? psql's \d on the
table should list the triggers for you.

regards, tom lane

In addition to the above, is there more to the trigger function then what you
have shown so far?

--
Adrian Klaver
adrian.klaver@gmail.com

This is the whole trigger

Begin

If new.pump1 = 'True'

then

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #1', '11',
'Pump');

End if;

If new.pump2 = 'True'

then

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #2', '11',
'Pump');

End if ;

RETURN NULL;

END;

Bob
----- Original Message -----
From: "Adrian Klaver" <adrian.klaver@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Bob Pawley" <rjpawley@shaw.ca>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Friday, January 22, 2010 3:01 PM
Subject: Re: [GENERAL] Old/New

On Friday 22 January 2010 3:05:54 pm Bob Pawley wrote:

This is the whole trigger

Begin

If new.pump1 = 'True'

then

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #1', '11',
'Pump');

End if;

If new.pump2 = 'True'

then

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #2', '11',
'Pump');

End if ;

RETURN NULL;

END;

Bob

The update statement -
update p_id.processes
   set pump1 = 'True'
   where p_id.processes.fluid_id = '3501' ;
updates the field pump1 to 'True'.

Is there more than one row in p_id.processes with p_id.processes.fluid_id
= '3501' ?

--
Adrian Klaver
adrian.klaver@gmail.com

No

The table p_id.processes is the start of the fluid_id ident and that column
is serial.

Bob

----- Original Message -----
From: "Adrian Klaver" <adrian.klaver@gmail.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Friday, January 22, 2010 3:19 PM
Subject: Re: [GENERAL] Old/New

On Friday 22 January 2010 3:05:54 pm Bob Pawley wrote:

This is the whole trigger

Begin

If new.pump1 = 'True'

then

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #1', '11',
'Pump');

End if;

If new.pump2 = 'True'

then

Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #2', '11',
'Pump');

End if ;

RETURN NULL;

END;

Bob

The update statement -
update p_id.processes
set pump1 = 'True'
where p_id.processes.fluid_id = '3501' ;
updates the field pump1 to 'True'.

Is there more than one row in p_id.processes with p_id.processes.fluid_id
= '3501' ?

--
Adrian Klaver
adrian.klaver@gmail.com

On Friday 22 January 2010 3:25:34 pm Bob Pawley wrote:

No

The table p_id.processes is the start of the fluid_id ident and that column
is serial.

Bob

Per Tom's suggestion can we see \d for p_id.processes and for good measure
p_id.devices ?

--
Adrian Klaver
adrian.klaver@gmail.com

I havn't been able to find documentation on how to use \d. When I open the
psql interface (through either port ) it asks for a password but doesn't
allow any entry of a password.

However, after my last e-mail to you, I came across something interesting -
at least to me.

I use pg_admin scripts to modify triggers.

Looking through pg_admin at all of the triggers on the p_id.processes table
I just happened to click on the trigger we have been discussing and then
clicked its refresh button.

Using the same update statement and the same After Update trigger that
inserted two rows into p_id.devices it now inserted only one row and that
row was the correct row.

I don't know enough about the interaction between the unrefreshed copy of a
trigger held by pg_admin and the updated trigger installed in the server to
comment - however there does seem to be a connection of which I wasn't
aware.

Bob

----- Original Message -----
From: "Adrian Klaver" <adrian.klaver@gmail.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Friday, January 22, 2010 3:37 PM
Subject: Re: [GENERAL] Old/New

On 23/01/2010 15:51, Bob Pawley wrote:

I havn't been able to find documentation on how to use \d. When I open

Hi Bob,

In brief:

\dt lists all the tables in the current schema
\d <tablename> gives the structure of the named table

.. and loads of others. The docs are here:

http://www.postgresql.org/docs/8.4/static/app-psql.html

See the section entitled "meta-commands", a good distance down the page.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie