Re: problems with access into system catalogs

On 07/26/2012 04:39 PM, Thomas Markus wrote:
> Hi,
>
> see below
>
> Am 26.07.2012 10:25, schrieb Craig Ringer:
>> - Do you have any uncommitted two phase transactions? Run:
>> SELECT * from pg_prepared_xacts ;
> hm yes, i stopped all applications this morning but this query shows:
> transaction | gid |
> prepared | owner | database
> -------------+----------------------------------------------------------------------------------------------+-------------------------------+--------+-----------
>
> 49737548 |
> 131075_MS03ZjAwMDAwMTpjZmVlOjRlZDg3MTk2OjY4NGJlN2I=_N2YwMDAwMDE6Y2ZlZTo0ZWQ4NzE5Njo2ODRiZTdm
> | 2012-01-05 07:49:30.78583+01 | xxx | db1
> 49737549 |
> 131075_MS03ZjAwMDAwMTpjZmVlOjRlZDg3MTk2OjY4NGJlN2I=_N2YwMDAwMDE6Y2ZlZTo0ZWQ4NzE5Njo2ODRiZTg0
> | 2012-01-05 07:49:30.789382+01 | xxx | db2
>
> system time is valid (Thu Jul 26 10:38:12 CEST 2012). so may 1st is
> really old
> Should I restart the instance?

Nope, and it wouldn't help anyway. Prepared but uncommitted two phase
transactions are a permanent and persistent part of the database. They
only go away when a COMMIT PREPARED or ROLLBACK PREPARED is issued. See:

http://www.postgresql.org/docs/9.1/static/sql-prepare-transaction.html

I cannot advise you on what to do without knowing what created those
transactions and why.

>> Do you very frequently create and drop tables, indexes, etc? Say,
>> using a database unit testing framework?
> no, its a live system with normal olap access

Weird, then I don't know how the catalogs would get so fat.

I don't think temporary tables create writes to the catalog heap, but I
can't think what else it'd be.

--
Craig Ringer