Re: [HACKERS] ANALYZE getting dead tuple count hopelessly wrong

Pavan Deolasee wrote:
> On Fri, Apr 4, 2008 at 11:10 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>
>> The
>> policy of this project is that we only put nontrivial bug fixes into
>> back branches, and I don't think this item qualifies ...
>>
>>
>
> Got it. I will submit a patch for HEAD.
>
> Thanks,
As I mentioned earlier, I patched 8.3.1 with Pavan's patch and have been
running tests. After a few days I have got postgres to lock up - not
sure if it is related. Below is a ps from my system (NetBSD 3).

TEST> ps -ax | grep post
1952 ? IW<s 13:52.24 postgres: writer process
2113 ? S<s 0:03.04 postgres: logger process
2157 ? S<s 0:03.12 postgres: autovacuum launcher process
2199 ? I<s 0:00.04 postgres: metauser metadb [local] SELECT
2472 ? DW<s 814:23.50 postgres: metauser metadb localhost(65524)
COMMIT
2661 ? DW<s 0:11.27 postgres: metauser metadb localhost(65525) idle
2680 ? S<s 1:18.75 postgres: stats collector process
3156 ? S<s 0:45.12 postgres: wal writer process
24362 ? IW<s 0:00.00 postgres: autovacuum worker process
25024 ? IW<s 0:00.00 postgres: autovacuum worker process
25134 ? IW<s 0:00.00 postgres: autovacuum worker process
3289 ttyp5 I< 0:01.96 /usr/local/pgsql/bin/postgres -D ../data/metadb

and I was disconnected in my client app with the following message:

[WARN] PSQL:exec - failed in command <SELECT
relname,n_tup_ins,n_live_tup,n_dead_tup,pg_total_relation_size('s8_0000.'
|| relname)*10/(1024*1024),last_autovacuum FROM pg_stat_user_tables
WHERE schemaname='s8_0000' ORDER BY n_tup_ins DESC>
[WARN] error = 'server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.'
[WARN] ConnectionNB: PQconsumeInput failed with error 'server closed the
connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.'

The server is still running but I can't access it. A top yields:

load averages: 0.23, 0.23, 0.21 09:53:58
110 processes: 109 sleeping, 1 on processor

Memory: 513M Act, 256M Inact, 1336K Wired, 75M Exec, 557M File, 2776K Free
Swap: 600M Total, 600M Free

PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
463 root 2 0 6132K 14M select 0:06 0.05% 0.05% kdeinit
2472 postgres -22 -2 4580K 4K mclpl 814:23 0.00% 0.00% <postgres>
2631 root -22 0 644K 4K mclpl 606:25 0.00% 0.00% <test_writer
1622 root 2 0 8456K 14M select 19:05 0.00% 0.00% kdeinit
1952 postgres 2 -2 3544K 4K netlck 13:52 0.00% 0.00% <postgres>
233 root 2 0 24M 31M select 4:47 0.00% 0.00% XFree86
451 root 2 0 3544K 15M select 4:45 0.00% 0.00% kdeinit
16 root 18 0 0K 182M syncer 3:51 0.00% 0.00% [ioflush]
17 root -18 0 0K 182M aiodoned 1:46 0.00% 0.00% [aiodoned]
15 root -18 0 0K 182M pgdaemon 1:30 0.00% 0.00% [pagedaemon]
1301 root -22 0 4092K 4K mclpl 1:23 0.00% 0.00% <kdeinit>
2680 postgres 2 -2 3560K 1588K poll 1:18 0.00% 0.00% postgres
1493 root 2 0 3488K 17M select 1:09 0.00% 0.00% korgac
461 root 2 0 3748K 16M select 0:57 0.00% 0.00% kdeinit
3156 postgres 2 -2 3448K 1792K select 0:45 0.00% 0.00% postgres
1174 root 2 0 2608K 2928K select 0:40 0.00% 0.00% profiler
1428 root 2 0 3376K 13M select 0:26 0.00% 0.00% kdeinit
2661 postgres -22 -2 4896K 4K mclpl 0:11 0.00% 0.00% <postgres>

I'm not convinced this is a postgresql bug (state=mclpl concerns me),
but it's the first time I've seen it. I suppose it could be:
http://www.netbsd.org/cgi-bin/query-pr-single.pl?number=35224.

Anything I can do which might help isolating the problem?

Regards
Stuart