Postgres hangs during delete query

Question

I am using psql 8.4.7. I am trying to delete from a log table with over 4 million rows. Every time the process starts, it hangs and I stop it. I tried deleting 1 row, using the id to select it, but no luck. No error, just hangs. I do have free space on my disk. The log table has a child table with almost 2 mill rows, and ON DELETE CASCADE is in place. Other tables with more dependents and row delete just fine.

Here is the create statement:

  CREATE TABLE log(
  id serial NOT NULL,
  userid integer,
  itemid integer NOT NULL,
  itemtype integer NOT NULL,
  date timestamp with time zone NOT NULL DEFAULT now(),
  actiontype integer NOT NULL,
  log_detail text,
  dtype integer,
  created timestamp with time zone DEFAULT now(),
  modified timestamp with time zone DEFAULT now(),
  CONSTRAINT log_pkey PRIMARY KEY (id),
  CONSTRAINT fk_log_user_id FOREIGN KEY (userid)
  REFERENCES appuser (id) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE SET NULL
)
CREATE INDEX log_itemid_index
 ON log
 USING btree
 (itemid);

CREATE INDEX log_itemtype_index
  ON log
  USING btree
  (itemtype);

CREATE INDEX log_userid_index
  ON log
  USING btree
  (userid);

Any idea what could be wrong?

EDIT: The table definition for the child is:

CREATE TABLE log_snapshot ( 
id serial NOT NULL, 
log_id integer, 
item_field character varying(255) DEFAULT NULL::character varying, 
item_value character varying(255) DEFAULT NULL::character varying, 
created timestamp with time zone DEFAULT now(),
modified timestamp with time zone DEFAULT now(), 
CONSTRAINT fk_log_snapshot FOREIGN KEY () REFERENCES log () MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ).

Does your table child have an index on the foreign key column? This would be crucial for performance. Also: we need the complete table definition of child as well. Triggers are another suspect. And, Postgres 8.4 is reaching EOL soon, consider upgrading to a current version. While you are stuck with 8.4, you absolutely need to upgrade to the latest point release, currently 8.4.18, not 8.4.7. Many bug and security fixes since then. This may well be (part of) the problem. — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 18, 2013 at 20:31
Sorry, this is the comment edited properly ;) : @Erwin: This is a client's server, so upgrading is not an option right now. The table definition for the child is: CREATE TABLE log_snapshot ( id serial NOT NULL, log_id integer, item_field character varying(255) DEFAULT NULL::character varying, item_value character varying(255) DEFAULT NULL::character varying, created timestamp with time zone DEFAULT now(), modified timestamp with time zone DEFAULT now(), CONSTRAINT fk_log_snapshot FOREIGN KEY () REFERENCES log () MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ). — fertech
– fertech, Commented Nov 20, 2013 at 18:56
@fertech: Please edit the question (left under the question). Don't put basic information in comments. — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 20, 2013 at 19:26

Jayadevan · Accepted Answer · 2013-11-20 04:27:58Z

3

Someone else already mentioned. But try creating an index on log_snapshot(log_id) I tried it (inserted 10000000 records into log and log_snapshot). See the execution time difference.

test=# delete from log where id = 196;
DELETE 1
Time: 1232.772 ms
test=# create index myindex on log_snapshot(log_id);
CREATE INDEX
Time: 10143.934 ms
test=# delete from log where id = 496;
DELETE 1
Time: 65.293 ms

answered Nov 20, 2013 at 4:27

Jayadevan

1,4342 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

fertech Over a year ago

Yes, the index did the trick. I tried it in a test DB, but hopefully will work fine on production as well. Thanks to @erwin as well.

mantri · Accepted Answer · 2017-06-14 04:33:00Z

1

I faced this similar issue and I later found that my server was turned on on my local machine and my table might be somehow locked. The issue resolved when I stopped the server.

answered Jun 14, 2017 at 4:33

mantri

3,2214 gold badges18 silver badges21 bronze badges

Collectives™ on Stack Overflow

Postgres hangs during delete query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related