Proposal / proof of concept: Triggers on VIEWs
in [PgSql]
|
Prev: Patch to show individual statement latencies in pgbench output
Next: tracking inherited columns (was: patch for check constraintsusing multiple inheritance)
From: Dean Rasheed on 6 Aug 2010 03:49 On 4 August 2010 15:08, Marko Tiikkaja <marko.tiikkaja(a)cs.helsinki.fi> wrote: > On 8/4/10 5:03 PM +0300, Dean Rasheed wrote: >> >> On 4 August 2010 14:43, Marko Tiikkaja<marko.tiikkaja(a)cs.helsinki.fi> >> �wrote: >>> >>> I'm not sure I understand. �RETURNING in DELETE on a table fetches the >>> old >>> value after it was DELETEd, so it really is what the tuple was before the >>> DLETE, not what is seen by the snapshot. �In a BEFORE DELETE trigger, the >>> row is always locked so it can't change after the trigger is fired. >>> >> >> Ah, I think I mis-understood. If I understand what you're saying >> correctly, you're worried that the row might have been modified in the >> same query, prior to being deleted, and you want RETURNING to return >> the updated value, as it was when it was deleted. > > I'm mainly concerned about concurrently running transactions. > Sorry for the delay replying. Once again, I think I mis-understood your point. I think that the database can't really lock anything before firing the trigger because the view might contain grouping/aggregates or even not be based on any real tables at all, so it would be impossible to work out what to lock. Thus it would be up to the trigger function to get this right. In the simplest case, for a DELETE, this might look something like: CREATE OR REPLACE FUNCTION instead_of_delete_trig_fn() RETURNS trigger AS $$ BEGIN DELETE FROM base_table WHERE pk = OLD.pk; IF NOT FOUND THEN RETURN NULL; END IF; RETURN OLD; END; $$ LANGUAGE plpgsql; If 2 users try to delete the same row, the second would block until the first user's transaction finished, and if the first user committed, the second user's trigger would return NULL, which the database would signal as no rows deleted. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Naveed Alam on 6 Aug 2010 03:56
aoa how can i connect my postgres database to manifold? Regards? On Fri, Aug 6, 2010 at 12:49 PM, Dean Rasheed <dean.a.rasheed(a)gmail.com>wrote: > On 4 August 2010 15:08, Marko Tiikkaja <marko.tiikkaja(a)cs.helsinki.fi> > wrote: > > On 8/4/10 5:03 PM +0300, Dean Rasheed wrote: > >> > >> On 4 August 2010 14:43, Marko Tiikkaja<marko.tiikkaja(a)cs.helsinki.fi> > >> wrote: > >>> > >>> I'm not sure I understand. RETURNING in DELETE on a table fetches the > >>> old > >>> value after it was DELETEd, so it really is what the tuple was before > the > >>> DLETE, not what is seen by the snapshot. In a BEFORE DELETE trigger, > the > >>> row is always locked so it can't change after the trigger is fired. > >>> > >> > >> Ah, I think I mis-understood. If I understand what you're saying > >> correctly, you're worried that the row might have been modified in the > >> same query, prior to being deleted, and you want RETURNING to return > >> the updated value, as it was when it was deleted. > > > > I'm mainly concerned about concurrently running transactions. > > > > Sorry for the delay replying. > > Once again, I think I mis-understood your point. I think that the > database can't really lock anything before firing the trigger because > the view might contain grouping/aggregates or even not be based on any > real tables at all, so it would be impossible to work out what to > lock. Thus it would be up to the trigger function to get this right. > In the simplest case, for a DELETE, this might look something like: > > CREATE OR REPLACE FUNCTION instead_of_delete_trig_fn() > RETURNS trigger AS > $$ > BEGIN > DELETE FROM base_table WHERE pk = OLD.pk; > IF NOT FOUND THEN RETURN NULL; END IF; > > RETURN OLD; > END; > $$ > LANGUAGE plpgsql; > > If 2 users try to delete the same row, the second would block until > the first user's transaction finished, and if the first user > committed, the second user's trigger would return NULL, which the > database would signal as no rows deleted. > > Regards, > Dean > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > |