Re: Proposal / proof of concept: Triggers on VIEWs

aoa how can i connect my postgres database to manifold?

Regards?

On Fri, Aug 6, 2010 at 12:49 PM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>wrote:

> On 4 August 2010 15:08, Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>
> wrote:
> > On 8/4/10 5:03 PM +0300, Dean Rasheed wrote:
> >>
> >> On 4 August 2010 14:43, Marko Tiikkaja<marko(dot)tiikkaja(at)cs(dot)helsinki(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>