pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Long-running DELETE
5+ messages / 3 participants
[nested] [flat]

* Long-running DELETE
@ 2002-01-15 19:02  Jeff Boes <jboes@nexcerpt.com>
  0 siblings, 2 replies; 5+ messages in thread

From: jboes@nexcerpt.com @ 2002-01-15 19:02 UTC (permalink / raw)
  To: pgsql-sql

Okay, this is really starting to fry me:

I'm trying to delete 1000 rows from a table which is referenced by
several different other tables as a foreign key.  I've turned on
debug_print_query to help track this down.  When I do deletes from
some tables, I can delete hundreds of rows per second.  When I delete
from the main table, it's about 1 row per second.  I've turned off
every trigger in the database:

update pg_triggers set tgenabled=false;

I read that such a change might require restarting the backend, so
I've gone so far as to bounce the database after this step.  What I
see in the log is

DEBUG:  query:  delete from a where id = 49;
DEBUG:  query: SELECT oid FROM ONLY "a_r" WHERE "id" = $1 FOR UPDATE
OF "a_r"
DEBUG:  query: SELECT oid FROM ONLY "a_c" WHERE "id" = $1 FOR UPDATE
OF "a_c"
DEBUG:  query: SELECT oid FROM ONLY "j" WHERE "id" = $1 FOR UPDATE OF
"j"
DEBUG:  query: SELECT oid FROM ONLY "a_r" WHERE "id" = $1 FOR UPDATE
OF "a_r"
DEBUG:  query: SELECT oid FROM ONLY "a_c" WHERE "id" = $1 FOR UPDATE
OF "a_c"
DEBUG:  query: SELECT oid FROM ONLY "j" WHERE "id" = $1 FOR UPDATE OF
"j"
DEBUG:  query: SELECT oid FROM ONLY "a_r" WHERE "id" = $1 FOR UPDATE
OF "a_r"
DEBUG:  query: SELECT oid FROM ONLY "a_c" WHERE "id" = $1 FOR UPDATE
OF "a_c"
DEBUG:  query: SELECT oid FROM ONLY "j" WHERE "id" = $1 FOR UPDATE OF
"j"
DEBUG:  query:  delete from a where id = 50;
DEBUG:  query:  delete from a where id = 53;
etc.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Long-running DELETE
@ 2002-01-16 05:13  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Jeff Boes <jboes@nexcerpt.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Stephan Szabo @ 2002-01-16 05:13 UTC (permalink / raw)
  To: Jeff Boes <jboes@nexcerpt.com>; +Cc: pgsql-sql


On 15 Jan 2002, Jeff Boes wrote:

> Okay, this is really starting to fry me:
>
> I'm trying to delete 1000 rows from a table which is referenced by
> several different other tables as a foreign key.  I've turned on
> debug_print_query to help track this down.  When I do deletes from
> some tables, I can delete hundreds of rows per second.  When I delete
> from the main table, it's about 1 row per second.  I've turned off
> every trigger in the database:
>
> update pg_triggers set tgenabled=false;

I don't think tgenabled works.  You'll probably need to set reltriggers to
0 on the pg_class row for the table (and then reset it to the correct
value afterwards).





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Long-running DELETE
@ 2002-01-16 05:42  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jeff Boes <jboes@nexcerpt.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Tom Lane @ 2002-01-16 05:42 UTC (permalink / raw)
  To: jboes@nexcerpt.com; +Cc: pgsql-sql

jboes@nexcerpt.com (Jeff Boes) writes:
> I've turned off every trigger in the database:

> update pg_triggers set tgenabled=false;

AFAIR, tgenabled isn't really supported; it's not tested for AFTER
triggers, which is what foreign keys use.  Your debug trace certainly
shows that the FK triggers are still live.

pg_dump knows how to disable triggers for real; I think it involves
setting pg_class.reltriggers to zero.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Long-running DELETE
@ 2002-01-16 13:38  Jeff Boes <jboes@nexcerpt.com>
  parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Jeff Boes @ 2002-01-16 13:38 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql

On Tue, 15 Jan 2002 21:13:49 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:

> 
> On 15 Jan 2002, Jeff Boes wrote:
> 
> > update pg_triggers set tgenabled=false;
> 
> I don't think tgenabled works.  You'll probably need to set reltriggers to
> 0 on the pg_class row for the table (and then reset it to the correct
> value afterwards).
> 

I can't confirm that one, but I did find that things got a lot faster
when I also did

  update pg_trigger 
  set tgenabled=false,
    tgdeferrable=true,
    tginitdeferred=true

for each of the associated triggers.

-- 
Jeff Boes                                             vox 616.226.9550
Database Engineer                                     fax 616.349.9076
Nexcerpt, Inc.                                      jboes@nexcerpt.com



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Long-running DELETE
@ 2002-01-16 15:14  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Jeff Boes <jboes@nexcerpt.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Stephan Szabo @ 2002-01-16 15:14 UTC (permalink / raw)
  To: Jeff Boes <jboes@nexcerpt.com>; +Cc: pgsql-sql

On Wed, 16 Jan 2002, Jeff Boes wrote:

> On Tue, 15 Jan 2002 21:13:49 -0800 (PST)
> Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
>
> >
> > On 15 Jan 2002, Jeff Boes wrote:
> >
> > > update pg_triggers set tgenabled=false;
> >
> > I don't think tgenabled works.  You'll probably need to set reltriggers to
> > 0 on the pg_class row for the table (and then reset it to the correct
> > value afterwards).
> >
>
> I can't confirm that one, but I did find that things got a lot faster
> when I also did
>
>   update pg_trigger
>   set tgenabled=false,
>     tgdeferrable=true,
>     tginitdeferred=true
>
> for each of the associated triggers.

Hmm, that'd make the actual trigger calls wait till end of transaction.
If you were running the deletes in a transaction, that'd make the delete
parts go faster, but the commit should take a while.





^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2002-01-16 15:14 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-15 19:02 Long-running DELETE Jeff Boes <jboes@nexcerpt.com>
2002-01-16 05:13 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-01-16 13:38   ` Jeff Boes <jboes@nexcerpt.com>
2002-01-16 15:14     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-01-16 05:42 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox