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