pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLong-running DELETE
5+ messages / 3 participants
[nested] [flat]
* Long-running DELETE
@ 2002-01-15 19:02 Jeff Boes <jboes@nexcerpt.com>
2002-01-16 05:13 ` Re: Long-running DELETE Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-01-16 05:42 ` Re: Long-running DELETE Tom Lane <tgl@sss.pgh.pa.us>
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-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 ` Re: Long-running DELETE 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-15 19:02 Long-running DELETE Jeff Boes <jboes@nexcerpt.com>
2002-01-16 05:13 ` Re: Long-running DELETE Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-01-16 13:38 ` Jeff Boes <jboes@nexcerpt.com>
2002-01-16 15:14 ` Re: Long-running DELETE 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-15 19:02 Long-running DELETE Jeff Boes <jboes@nexcerpt.com>
2002-01-16 05:13 ` Re: Long-running DELETE Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-01-16 13:38 ` Re: Long-running DELETE Jeff Boes <jboes@nexcerpt.com>
@ 2002-01-16 15:14 ` Stephan Szabo <sszabo@megazone23.bigpanda.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
* Re: Long-running DELETE
2002-01-15 19:02 Long-running DELETE Jeff Boes <jboes@nexcerpt.com>
@ 2002-01-16 05:42 ` Tom Lane <tgl@sss.pgh.pa.us>
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
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