Skip site navigation (1) Skip section navigation (2)

hanging INSERT statements with subselect

From: Peter Hinse <loco(at)d0pefish(dot)de>
To: pgsql-admin(at)postgresql(dot)org
Subject: hanging INSERT statements with subselect
Date: 2008-10-31 21:27:56
Message-ID: 490B785C.2040003@d0pefish.de (view raw or flat)
Thread:
Lists: pgsql-admin
Hi *,

we have a postgresql 8.2.10 database running on a centos 4.6 x86_64 host
which runs some aggregation jobs each night.

We often see the INSERT statement (which gets its data from some rather
large subselects) not terminating. Using strace <pid> we see something
like this - for ages. If we do not terminate the process, this runs for
ages:

lseek(59, 0, SEEK_END)                  = 0
lseek(61, 0, SEEK_END)                  = 8192
lseek(68, 0, SEEK_END)                  = 0
lseek(75, 0, SEEK_END)                  = 0
lseek(82, 0, SEEK_END)                  = 0
lseek(89, 0, SEEK_END)                  = 0
lseek(96, 0, SEEK_END)                  = 0
lseek(103, 0, SEEK_END)                 = 0
lseek(110, 0, SEEK_END)                 = 0
lseek(117, 0, SEEK_END)                 = 0
lseek(124, 0, SEEK_END)                 = 0
lseek(131, 0, SEEK_END)                 = 0
lseek(138, 0, SEEK_END)                 = 0
lseek(145, 0, SEEK_END)                 = 0
lseek(215, 0, SEEK_END)                 = 0
lseek(222, 0, SEEK_END)                 = 0
lseek(229, 0, SEEK_END)                 = 0
lseek(236, 0, SEEK_END)                 = 0
lseek(243, 0, SEEK_END)                 = 0
lseek(250, 0, SEEK_END)                 = 0
lseek(257, 0, SEEK_END)                 = 0
lseek(264, 0, SEEK_END)                 = 0
lseek(271, 0, SEEK_END)                 = 0
lseek(278, 0, SEEK_END)                 = 0
lseek(285, 0, SEEK_END)                 = 0
lseek(292, 0, SEEK_END)                 = 0
lseek(299, 0, SEEK_END)                 = 0
lseek(306, 0, SEEK_END)                 = 0
lseek(313, 0, SEEK_END)                 = 0

While reviewing our config, we stopped the problem for some time by
resetting the following variables:

default_statistics_target = 100
constraint_exclusion = off

Any idea what might be our problem? If more information is required,
please ask.

Regards,

	Peter

pgsql-admin by date

Next:From: Alexander StrotmannDate: 2008-11-02 10:18:26
Subject: autovacuum questions v2
Previous:From: Scott MarloweDate: 2008-10-31 18:31:56
Subject: Re: autovacuum questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group