Re: problems with access into system catalogs

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