hanging INSERT statements with subselect

[Peter Hinse <loco(at)d0pefish(dot)de>]

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

Attachment: signature.asc
Description: OpenPGP digital signature