Message-ID: <50110E39.1040601@ringerc.id.au>
Date: Thu, 26 Jul 2012 17:30:33 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: Thomas Markus <t.markus@proventis.net>
CC: pgsql-admin@postgresql.org
Subject: Re: problems with access into system catalogs
References: <5010F555.9020209@proventis.net> <5010FEF2.3050901@ringerc.id.au>
	<5011025B.7050003@proventis.net>
In-Reply-To: <5011025B.7050003@proventis.net>
Content-Type: multipart/alternative;
	boundary="------------000501050802040005080700"

This is a multi-part message in MIME format.
--------------000501050802040005080700
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------000501050802040005080700
Content-Type: text/html; charset=ISO-8859-15
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-15"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 07/26/2012 04:39 PM, Thomas Markus
      wrote:<br>
    </div>
    <blockquote cite="mid:5011025B.7050003@proventis.net" type="cite">Hi,
      <br>
      <br>
      see below
      <br>
      <br>
      Am 26.07.2012 10:25, schrieb Craig Ringer:<br>
      <blockquote type="cite">- Do you have any uncommitted two phase
        transactions?  Run:
        <br>
          SELECT * from pg_prepared_xacts ;
        <br>
      </blockquote>
      hm yes, i stopped all applications this morning but this query
      shows:
      <br>
       transaction | gid                                              |
      prepared            | owner  | database
      <br>
-------------+----------------------------------------------------------------------------------------------+-------------------------------+--------+-----------
      <br>
          49737548 |
      131075_MS03ZjAwMDAwMTpjZmVlOjRlZDg3MTk2OjY4NGJlN2I=_N2YwMDAwMDE6Y2ZlZTo0ZWQ4NzE5Njo2ODRiZTdm
      | 2012-01-05 07:49:30.78583+01  | xxx | db1
      <br>
          49737549 |
      131075_MS03ZjAwMDAwMTpjZmVlOjRlZDg3MTk2OjY4NGJlN2I=_N2YwMDAwMDE6Y2ZlZTo0ZWQ4NzE5Njo2ODRiZTg0
      | 2012-01-05 07:49:30.789382+01 | xxx | db2
      <br>
      <br>
      system time is valid (Thu Jul 26 10:38:12 CEST 2012). so may 1st
      is really old
      <br>
      Should I restart the instance?
      <br>
    </blockquote>
    <br>
    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:<br>
    <br>
     
    <meta http-equiv="content-type" content="text/html;
      charset=ISO-8859-15">
    <a
href="http://www.postgresql.org/docs/9.1/static/sql-prepare-transaction.html">http://www.postgresql.org/docs/9.1/static/sql-prepare-transaction.html</a><br>
    <br>
    I cannot advise you on what to do without knowing what created those
    transactions and why.<br>
    <br>
    <blockquote cite="mid:5011025B.7050003@proventis.net" type="cite">
      <blockquote type="cite">Do you very frequently create and drop
        tables, indexes, etc? Say, using a database unit testing
        framework?
        <br>
      </blockquote>
      no, its a live system with normal olap access
      <br>
    </blockquote>
    <br>
    Weird, then I don't know how the catalogs would get so fat.<br>
    <br>
    I don't think temporary tables create writes to the catalog heap,
    but I can't think what else it'd be.<br>
    <br>
    --<br>
    Craig Ringer<br>
  </body>
</html>

--------------000501050802040005080700--