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