pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: jboes@nexcerpt.com
To: pgsql-sql@postgresql.org
Subject: Long-running DELETE
Date: 15 Jan 2002 11:02:22 -0800
Message-ID: <d40a65a1.0201151102.218752c@posting.google.com> (raw)

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.



view thread (5+ messages)  latest in thread

Message-ID: <d40a65a1.0201151102.218752c@posting.google.com>
Permalink:  ../d40a65a1.0201151102.218752c@posting.google.com/
Also on:    postgresql.org/message-id/d40a65a1.0201151102.218752c@posting.google.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jboes@nexcerpt.com
  Subject: Re: Long-running DELETE
  In-Reply-To: <d40a65a1.0201151102.218752c@posting.google.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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