Re: Proposal / proof of concept: Triggers on VIEWs

aoa how can i connect my postgres database to manifold?<br /><br />Regards?<br /><br /><br /><br /><div
class="gmail_quote">OnFri, Aug 6, 2010 at 12:49 PM, Dean Rasheed <span dir="ltr"><<a
href="mailto:dean.a.rasheed@gmail.com">dean.a.rasheed@gmail.com</a>></span>wrote:<br /><blockquote
class="gmail_quote"style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">On
4August 2010 15:08, Marko Tiikkaja <<a
href="mailto:marko.tiikkaja@cs.helsinki.fi">marko.tiikkaja@cs.helsinki.fi</a>>wrote:<br /> > On 8/4/10 5:03 PM
+0300,Dean Rasheed wrote:<br /> >><br /> >> On 4 August 2010 14:43, Marko Tiikkaja<<a
href="mailto:marko.tiikkaja@cs.helsinki.fi">marko.tiikkaja@cs.helsinki.fi</a>><br/> >>  wrote:<br />
>>><br/> >>> I'm not sure I understand.  RETURNING in DELETE on a table fetches the<br />
>>>old<br /> >>> value after it was DELETEd, so it really is what the tuple was before the<br />
>>>DLETE, not what is seen by the snapshot.  In a BEFORE DELETE trigger, the<br /> >>> row is always
lockedso it can't change after the trigger is fired.<br /> >>><br /> >><br /> >> Ah, I think I
mis-understood.If I understand what you're saying<br /> >> correctly, you're worried that the row might have been
modifiedin the<br /> >> same query, prior to being deleted, and you want RETURNING to return<br /> >> the
updatedvalue, as it was when it was deleted.<br /> ><br /> > I'm mainly concerned about concurrently running
transactions.<br/> ><br /><br /> Sorry for the delay replying.<br /><br /> Once again, I think I mis-understood your
point.I think that the<br /> database can't really lock anything before firing the trigger because<br /> the view might
containgrouping/aggregates or even not be based on any<br /> real tables at all, so it would be impossible to work out
whatto<br /> lock. Thus it would be up to the trigger function to get this right.<br /> In the simplest case, for a
DELETE,this might look something like:<br /><br /> CREATE OR REPLACE FUNCTION instead_of_delete_trig_fn()<br /> RETURNS
triggerAS<br /> $$<br /> BEGIN<br />  DELETE FROM base_table WHERE pk = OLD.pk;<br />  IF NOT FOUND THEN RETURN NULL;
ENDIF;<br /><br />  RETURN OLD;<br /> END;<br /> $$<br /> LANGUAGE plpgsql;<br /><br /> If 2 users try to delete the
samerow, the second would block until<br /> the first user's transaction finished, and if the first user<br />
committed,the second user's trigger would return NULL, which the<br /> database would signal as no rows deleted.<br
/><br/> Regards,<br /> Dean<br /><font color="#888888"><br /> --<br /> Sent via pgsql-hackers mailing list (<a
href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/> To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-hackers"
target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></font></blockquote></div><br />