Re: [GENERAL] Cascades Failing

From: Darcy Buskermolen <darcy(at)wavefire(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: [GENERAL] Cascades Failing
Date: 2005-08-17 15:53:28
Message-ID: 200508170853.29172.darcy@wavefire.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

On Tuesday 16 August 2005 09:17, Stephan Szabo wrote:
> On Tue, 16 Aug 2005, Tom Lane wrote:
> > [ redirected to -hackers ]
> >
> > I wrote:
> > > This suggests that we need a way to prevent immediate execution of
> > > freshly queued triggers at the end of a command fired by an FK trigger.
> > > If we could move them to the end of the trigger queue that the FK
> > > operation itself is in, things would work reasonably well I think.
> >
> > After a quick look through the code, it seems like the way to do this
> > is to add an extra bool parameter "nest_triggers" to _SPI_pquery, which
> > when false would simply suppress its calls to AfterTriggerBeginQuery
> > and AfterTriggerEndQuery --- thus causing any queued triggers to be
> > queued in the same trigger list the FK is in. We'd then expose this
> > parameter (only) via SPI_execute_snapshot, which is intended only for
> > RI trigger use anyway.
>
> This seems right to me. I'd thought that SQL wanted the user triggers to
> be run after the updating directly, but reading it again, SQL03 at least
> seems to just talk about adding state changes for after triggers to the
> current trigger context AFAICS which means that the above seems to be what
> is requested by the spec in general.
>
> > I think this would take some generalization of afterTriggerInvokeEvents,
> > which now might or might not find the target rel in the EState it's
> > passed, but otherwise it doesn't seem too invasive. Thoughts?
>
> That doesn't seem too bad really, looking at afterTriggerInvokeEvents it
> doesn't look like it'd be that much work to change it to handle that case.
> I can put a patch together to see what it looks like.

I have a realworld test case of delete cascade (approx 90 cascaded tables,
some more than 8 levels deep) failing on 8.0.3 (and 8.1) , this is one of a
few issues that is preventing me from upgrading a couple of 7.4 boxen to 8.x,
if you need testers for this patch, please let me know and I'll be glad to
try it out and see if it solves the cascade problems I am experiencing.

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--
Darcy Buskermolen
Wavefire Technologies Corp.

http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruno Wolff III 2005-08-17 15:57:34 Re: Finding nearest numeric value
Previous Message Mario Guenterberg 2005-08-17 15:50:04 Re: Adding contrib modules

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2005-08-17 16:01:58 Re: pthread stack on FreeBSD WAS: HEAD doesn't cope with libraries in non-default
Previous Message Jim C. Nasby 2005-08-17 14:51:49 Re: pthread stack on FreeBSD WAS: HEAD doesn't cope with libraries in non-default