From: Peter Hinse <loco@d0pefish.de>
To: pgsql-admin@postgresql.org
Subject: hanging INSERT statements with subselect
Date: Fri, 31 Oct 2008 22:27:56 +0100
Message-ID: <490B785C.2040003@d0pefish.de> (raw)
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
Attachments:
signature.asc (application/pgp-signature, 256B) download
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-admin@postgresql.org
Cc: loco@d0pefish.de
Subject: Re: hanging INSERT statements with subselect
In-Reply-To: <490B785C.2040003@d0pefish.de>
* 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