From: Craig Ringer <ringerc@ringerc.id.au>
To: Thomas Markus <t.markus@proventis.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: problems with access into system catalogs
Date: Thu, 26 Jul 2012 17:30:33 +0800
Message-ID: <50110E39.1040601@ringerc.id.au> (raw)
In-Reply-To: <5011025B.7050003@proventis.net>
References: <5010F555.9020209@proventis.net>
<5010FEF2.3050901@ringerc.id.au>
<5011025B.7050003@proventis.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ringerc@ringerc.id.au, t.markus@proventis.net
Subject: Re: problems with access into system catalogs
In-Reply-To: <50110E39.1040601@ringerc.id.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox