Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 26B45650259 for ; Fri, 31 Oct 2008 18:36:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 41871-03 for ; Fri, 31 Oct 2008 18:36:36 -0300 (ADT) X-Greylist: delayed 00:08:28.095675 by SQLgrey-1.7.6 Received: from mail.pommesbude.org (mail.pommesbude.org [88.198.164.122]) by postgresql.org (Postfix) with ESMTP id 270526501AC for ; Fri, 31 Oct 2008 18:36:36 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mail.pommesbude.org (Postfix) with ESMTP id F36B8CFD9E7 for ; Fri, 31 Oct 2008 22:28:04 +0100 (CET) X-Virus-Scanned: amavisd-new 2.6.1 (20080629) at pommesbude.org Received: from mail.pommesbude.org ([88.198.164.122]) by localhost (mail.pommesbude.org [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id ekVIYCcJjnBT for ; Fri, 31 Oct 2008 22:28:04 +0100 (CET) Received: from peter-hinses-macbook.local (xdsl-84-44-228-37.netcologne.de [84.44.228.37]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.pommesbude.org (Postfix) with ESMTPSA id A9999CFD9D7 for ; Fri, 31 Oct 2008 22:28:04 +0100 (CET) Message-ID: <490B785C.2040003@d0pefish.de> Date: Fri, 31 Oct 2008 22:27:56 +0100 From: Peter Hinse User-Agent: Thunderbird 2.0.0.17 (Macintosh/20080914) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: hanging INSERT statements with subselect X-Enigmail-Version: 0.95.7 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig52F77CADFB34077A7190929B" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200810/198 X-Sequence-Number: 30654 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig52F77CADFB34077A7190929B Content-Type: text/plain; charset=ISO-8859-15 Content-Transfer-Encoding: quoted-printable 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 we see something like this - for ages. If we do not terminate the process, this runs for ages: lseek(59, 0, SEEK_END) =3D 0 lseek(61, 0, SEEK_END) =3D 8192 lseek(68, 0, SEEK_END) =3D 0 lseek(75, 0, SEEK_END) =3D 0 lseek(82, 0, SEEK_END) =3D 0 lseek(89, 0, SEEK_END) =3D 0 lseek(96, 0, SEEK_END) =3D 0 lseek(103, 0, SEEK_END) =3D 0 lseek(110, 0, SEEK_END) =3D 0 lseek(117, 0, SEEK_END) =3D 0 lseek(124, 0, SEEK_END) =3D 0 lseek(131, 0, SEEK_END) =3D 0 lseek(138, 0, SEEK_END) =3D 0 lseek(145, 0, SEEK_END) =3D 0 lseek(215, 0, SEEK_END) =3D 0 lseek(222, 0, SEEK_END) =3D 0 lseek(229, 0, SEEK_END) =3D 0 lseek(236, 0, SEEK_END) =3D 0 lseek(243, 0, SEEK_END) =3D 0 lseek(250, 0, SEEK_END) =3D 0 lseek(257, 0, SEEK_END) =3D 0 lseek(264, 0, SEEK_END) =3D 0 lseek(271, 0, SEEK_END) =3D 0 lseek(278, 0, SEEK_END) =3D 0 lseek(285, 0, SEEK_END) =3D 0 lseek(292, 0, SEEK_END) =3D 0 lseek(299, 0, SEEK_END) =3D 0 lseek(306, 0, SEEK_END) =3D 0 lseek(313, 0, SEEK_END) =3D 0 While reviewing our config, we stopped the problem for some time by resetting the following variables: default_statistics_target =3D 100 constraint_exclusion =3D off Any idea what might be our problem? If more information is required, please ask. Regards, Peter --------------enig52F77CADFB34077A7190929B Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (Darwin) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkkLeGMACgkQlpV6nMOBgiffEwCeMKz3KRfu74OmrvyZNaZ1783J KWoAoKhv0ah1ofExVejSHr4p0lGOWHXz =M3jC -----END PGP SIGNATURE----- --------------enig52F77CADFB34077A7190929B--