Thread: Old/New
Hi
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.
I am using an After Update trigger.
Bob
"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
quiteoften 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:
>> 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
"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:
>> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
"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 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
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: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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
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 > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
"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 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
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 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
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
On Saturday 23 January 2010 7:51:28 am Bob Pawley wrote: > 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. That would depend on the settings in pg_hba.conf, whether you have passwords enabled for the connection or not. I am not sure what you either port? Are you referring to the port address and the local socket? > > 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. Can't help you there I don't use pgAdmin. > > Bob > -- Adrian Klaver adrian.klaver@gmail.com
The suggestions received have worked well for one update in the row. However, if I make any other update on the same row the trigger fires and more inserts are generated. However. I have found that the 8.5 alpha version has this addition - http://developer.postgresql.org/pgdocs/postgres/release-8-5.html "Add a WHEN clause to CREATE TRIGGER, allowing a boolean expression to be checked to determine whether the trigger should be fired." Would this change solve my problem, or is there another solution around? 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: Saturday, January 23, 2010 10:06 AM Subject: Re: [GENERAL] Old/New > On Saturday 23 January 2010 7:51:28 am Bob Pawley wrote: >> 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. > > That would depend on the settings in pg_hba.conf, whether you have > passwords > enabled for the connection or not. I am not sure what you either port? Are > you > referring to the port address and the local socket? > >> >> 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. > > Can't help you there I don't use pgAdmin. > >> >> Bob >> > > > > -- > Adrian Klaver > adrian.klaver@gmail.com
On 01/25/2010 10:24 AM, Bob Pawley wrote: > The suggestions received have worked well for one update in the row. > > However, if I make any other update on the same row the trigger fires > and more inserts are generated. > > However. I have found that the 8.5 alpha version has this addition - > > http://developer.postgresql.org/pgdocs/postgres/release-8-5.html > > "Add a WHEN clause to CREATE TRIGGER, allowing a boolean expression to > be checked to determine whether the trigger should be fired." > > Would this change solve my problem, or is there another solution around? > > Bob From your previous thread on this problem: http://archives.postgresql.org/pgsql-general/2010-01/msg00777.php I suggested this: Create an INSERT, UPDATE trigger on table1. Have the trigger inspect the value of pump1. You will need to guard against double entry on updates. So rough flow is: if TG_OP = 'INSERT' and NEW.pump1 = 't' INSERT row second table if TG_OP = 'UPDATE' and NEW.pump1='t' if OLD.pump1 = 'f' or OLD.pump1 is NULL INSERT row second table You need to verify whether the pump1='t' is actually a change or just carry over from the previous version of the row. As it stands now your trigger functions sees NEW.pump1='t' and issues an INSERT regardless of the previous state of pump1. -- Adrian Klaver adrian.klaver@gmail.com