Skip site navigation (1) Skip section navigation (2)

Re: problems with access into system catalogs

From: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
To: Thomas Markus <t(dot)markus(at)proventis(dot)net>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: problems with access into system catalogs
Date: 2012-07-26 09:30:33
Message-ID: 50110E39.1040601@ringerc.id.au (view raw or flat)
Thread:
Lists: pgsql-admin
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

In response to

Responses

pgsql-admin by date

Next:From: Thomas MarkusDate: 2012-07-26 11:12:15
Subject: Re: problems with access into system catalogs
Previous:From: Alexey KlyukinDate: 2012-07-26 09:24:04
Subject: standby with a fixed lag behind the master

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group