[SQL] Order of trigger execution: AFTER DELETE ON EACH ROW doesn't appear to be working properly
I've got a series of triggers that are responsible for maintaining
deduced data in a table (background for this can be found in an
earlier mailing list posting: http://archives.postgresql.org/pgsql-sql/2008-10/msg00041.php)
.
This problem concerns the order in which my triggers are executed.
After each row in the table is deleted, I have a trigger set up to
then delete any rows that can be logically deduced from the row that
was just deleted. I expect that when deleting a row, my before row
triggers would fire in alphabetical order, the row would be deleted,
and then my after row triggers would fire in alphabetical order. If I
delete rows one at a time (e.g. "DELETE FROM foo WHERE ID = 1"), this
is what happens. However, when I do a bulk delete (e.g., "DELETE FROM
foo"), this is not what happens. Based on debugging messages I added
to the triggers, it looks like the before row triggers fire for ALL
the rows first, THEN all the after row triggers fire. Here's some
sample code that illustrates this:
CREATE TABLE foo (
id serial primary key,
message text
);
insert into foo(message) values
('Hello'),
('Hi'),
('Howdy')
;
CREATE OR REPLACE FUNCTION debug_insert_or_update_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Trigger % on table % is firing % % for record %',
TG_NAME, TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, TG_WHEN, TG_OP,
NEW.id;
RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION debug_delete_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Trigger % on table % is firing % % for record %',
TG_NAME, TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, TG_WHEN, TG_OP,
OLD.id;
RETURN OLD;
END;
$$;
CREATE TRIGGER a_debug_before_insert_or_update
BEFORE UPDATE OR INSERT
ON foo
FOR EACH ROW
EXECUTE PROCEDURE debug_insert_or_update_trigger();
;
CREATE TRIGGER a_debug_after_insert_or_update
AFTER UPDATE OR INSERT
ON foo
FOR EACH ROW
EXECUTE PROCEDURE debug_insert_or_update_trigger();
;
CREATE TRIGGER a_debug_before_delete
BEFORE DELETE
ON foo
FOR EACH ROW
EXECUTE PROCEDURE debug_delete_trigger();
;
CREATE TRIGGER a_debug_after_delete
AFTER DELETE
ON foo
FOR EACH ROW
EXECUTE PROCEDURE debug_delete_trigger();
;
SELECT * FROM FOO;
id | message
+-
1 | Hello
2 | Hi
3 | Howdy
(3 rows)
SAVEPOINT S1;
DELETE FROM FOO WHERE id = 1;
NOTICE: Trigger a_debug_before_delete on table public.foo is firing
BEFORE DELETE for record 1
NOTICE: Trigger a_debug_after_delete on table public.foo is firing
AFTER DELETE for record 1
DELETE 1
DELETE FROM FOO WHERE id = 2;
NOTICE: Trigger a_debug_before_delete on table public.foo is firing
BEFORE DELETE for record 2
NOTICE: Trigger a_debug_after_delete on table public.foo is firing
AFTER DELETE for record 2
DELETE 1
DELETE FROM FOO WHERE id = 3;
NOTICE: Trigger a_debug_before_delete on table public.foo is firing
BEFORE DELETE for record 3
NOTICE: Trigger a_debug_after_delete on table public.foo is firing
AFTER DELETE for record 3
DELETE 1
ROLLBACK TO SAVEPOINT S1;
SELECT * FROM FOO;
id | message
+-
1 | Hello
2 | Hi
3 | Howdy
DELETE FROM FOO;
NOTICE: Trigger a_debug_before_delete on table public.foo is firing
BEFORE DELETE for record 1
NOTICE: Trigger a_debug_before_delete on table public.foo is firing
BEFORE DELETE for record 2
NOTICE: Trigger a_debug_before_delete on table public.foo is firing
BEFORE DELETE for record 3
NOTICE: Trigger a_debug_after_delete on table public.foo is firing
AFTER DELETE for record 1
NOTICE: Trigger a_debug_after_delete on table public.foo is firing
AFTER DELETE for record 2
NOTICE: Trigger a_debug_after_delete on table public.foo is firing
AFTER DELETE for record 3
DELETE 3
As you can see, doing a row-by-row delete works fine, but when doing a
bulk delete, all the before triggers are grouped together, as are all
the after triggers.
Why are these calls not interleaved (e.g. BEFORE DELETE for record 1,
AFTER DELETE for record 1, BEFORE DELETE for record 2, AFTER DELETE
for record 2, etc.). Is there any way I can get them to be
interleaved? This causes problems for my application because if
dependent rows are not deleted immediately after the row that supports
it, then I can get "orphaned" rows left in the table that have no
supporting rows.
Thanks,
Chris
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with delete trigger: how to allow only triggers to delete a row?
On Oct 10, 2008, at 5:16 PM, Christopher Maier wrote: On Oct 10, 2008, at 4:53 PM, Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> writes: Looks like you should revoke DELETE privilege from plain users, and have your delete trigger be a security definer function. There would be another security definer function to delete non-deduced rows which users can call directly. That seems overly complicated to use. If the triggers that are privileged to delete deduced rows run as a special user, couldn't the validation triggers look at CURRENT_USER to see whether to allow the delete of a deduced row or not? regards, tom lane That sounds like the best approach, Tom. I've already implemented Alvaro's suggestion, which works nicely. It should be a simple matter to add in the current_user check. I'll give that a whirl and see how it goes. Thanks for all the great suggestions, everyone. Chris Just for completeness, and for posterity, this solution (checking for CURRENT_USER) works great. I don't need to revoke DELETE privileges from anyone; simply define all my triggers that kick off a DELETE operation as SECURITY DEFINER (created by my privileged user role), and then have a BEFORE DELETE trigger that compares the value of CURRENT_USER to this privileged user's name. Works great, and is very easy to implement. Thanks for the help! --Chris -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Order of trigger execution: AFTER DELETE ON EACH ROW doesn't appear to be working properly
"Christopher Maier" <[EMAIL PROTECTED]> writes: > ... Based on debugging messages I added > to the triggers, it looks like the before row triggers fire for ALL > the rows first, THEN all the after row triggers fire. That's correct. After triggers are queued up and fired at end of statement. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Order of trigger execution: AFTER DELETE ON EACH ROW doesn't appear to be working properly
-- Original message -- From: "Christopher Maier" <[EMAIL PROTECTED]> > > As you can see, doing a row-by-row delete works fine, but when doing a > bulk delete, all the before triggers are grouped together, as are all > the after triggers. > > Why are these calls not interleaved (e.g. BEFORE DELETE for record 1, > AFTER DELETE for record 1, BEFORE DELETE for record 2, AFTER DELETE > for record 2, etc.). Is there any way I can get them to be > interleaved? This causes problems for my application because if > dependent rows are not deleted immediately after the row that supports > it, then I can get "orphaned" rows left in the table that have no > supporting rows. > > Thanks, > Chris > Is not possible to delete the dependent rows from within the BEFORE trigger? -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Updating Query of 1 table from data in another
Hello all, I'm still new to postgres If I have 2 tables with the following data structure Agentno and agentname (along with many other fields) this table is called agent_master And I have another table with agentno and agentname. Table called updatetable When I add data in the updatetable, I want to write a query that will enter the agentname field in the corresponding agent_master.agentname field based on agent_master.agentno = updatetable.agentno Thanks in advance
Re: [SQL] Updating Query of 1 table from data in another
On Mon, Oct 13, 2008 at 1:23 PM, Chris Preston <[EMAIL PROTECTED]> wrote: > Hello all, I'm still new to postgres > > If I have 2 tables with the following data structure > > Agentno and agentname (along with many other fields) this table is called > agent_master > > And I have another table with agentno and agentname. Table called > updatetable > > When I add data in the updatetable, I want to write a query that will enter > the agentname field in the corresponding agent_master.agentname field based > on agent_master.agentno = updatetable.agentno Would an on update cascade foreign key work for you? create table a (id int primary key, nam text not null); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a" CREATE TABLE create table b (aid int, nam text, foreign key (aid,nam) references a(id,nam) on update cascade); CREATE TABLE insert into b (aid, nam) values (1,'steve'); ERROR: insert or update on table "b" violates foreign key constraint "b_aid_fkey" DETAIL: Key (aid,nam)=(1,steve) is not present in table "a". insert into a (id, nam) values (1,'steve'); INSERT 0 1 insert into b (aid, nam) values (1,'steve'); INSERT 0 1 select * from a join b on a.id=b.aid; id | nam | aid | nam +---+-+--- 1 | steve | 1 | steve update a set nam='scott' where id=1; UPDATE 1 select * from a join b on a.id=b.aid; id | nam | aid | nam +---+-+--- 1 | scott | 1 | scott tada... hope that makes sense. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Order of trigger execution: AFTER DELETE ON EACH ROW doesn't appear to be working properly
On Oct 13, 2008, at 3:08 PM, Adrian Klaver wrote: -- Original message -- From: "Christopher Maier" <[EMAIL PROTECTED]> As you can see, doing a row-by-row delete works fine, but when doing a bulk delete, all the before triggers are grouped together, as are all the after triggers. Why are these calls not interleaved (e.g. BEFORE DELETE for record 1, AFTER DELETE for record 1, BEFORE DELETE for record 2, AFTER DELETE for record 2, etc.). Is there any way I can get them to be interleaved? This causes problems for my application because if dependent rows are not deleted immediately after the row that supports it, then I can get "orphaned" rows left in the table that have no supporting rows. Thanks, Chris Is not possible to delete the dependent rows from within the BEFORE trigger? -- Adrian Klaver [EMAIL PROTECTED] After playing around with it, yes, it is possible. I've reworked my code to delete dependent rows before the row itself is deleted, and it works as desired. Thanks, Chris -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
