Re: Backends stuck in LISTEN

On 1/8/14 4:55 PM, Tom Lane wrote:
> Marko Tiikkaja <marko(at)joh(dot)to> writes:
>>> In the meanwhile I'll be happy to provide more information if someone
>>> has any ideas.
>
> Oh, after further thought: this trace says the process was trying to look
> up the status of XID 1124964531 (see the TransactionIdGetStatus and
> TransactionLogFetch calls). Do all the backtraces show this same XID?

I only got two, and the other one wants the status of 1133447326.

> If I did the math right, this corresponds to pg_clog file 010C. Do you
> have such a file? Does it look to have the right size and permissions?
> What file names are present in pg_clog?

No, it's not there. The files look like this:

-rw------- 1 postgres postgres 256K Nov 3 15:55 0390
-rw------- 1 postgres postgres 256K Nov 3 22:26 0391
..
-rw------- 1 postgres postgres 256K Jan 8 16:43 044D
-rw------- 1 postgres postgres 24K Jan 8 17:28 044E

> It'd also be interesting to see the current XID counter according to
> pg_controldata, especially if you have some idea how far it's advanced
> since the problem happened.

Latest checkpoint's NextXID: 0/1155614649

(I hope that's the number you were looking for.)

Judging from a few days old base backup that number would have been
(very roughly) around 1148225540 when this incident happened.

Regards,
Marko Tiikkaja