Thread: Long-running DELETE
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.
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).
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
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
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.