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

Re: problems with access into system catalogs

From: Thomas Markus <t(dot)markus(at)proventis(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: problems with access into system catalogs
Date: 2012-07-26 08:39:55
Message-ID: 5011025B.7050003@proventis.net (view raw or flat)
Thread:
Lists: pgsql-admin
Hi,

see below

Am 26.07.2012 10:25, schrieb Craig Ringer:
> First, thank-you for an excellent complete question with versions, 
> EXPLAIN ANALYZE, and exact messages.
;)
>
> - 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?

>
> - Do you have any long-lived 'IDLE IN TRANSACTION' connections ? Try:
>   SELECT * FROM pg_stat_activity WHERE current_query = '<IDLE> in 
> transaction' AND xact_start > current_timestamp - '1 minute'::interval;
none, and there was a disconnect from all clients this morning
>
> Either of those can prevent vacuum from cleaning things up.
>
> 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



Thomas

In response to

Responses

pgsql-admin by date

Next:From: Craig RingerDate: 2012-07-26 08:46:35
Subject: Re: "Data import from Mysql to MS Sql Server"
Previous:From: Bèrto ëd SèraDate: 2012-07-26 08:26:05
Subject: Re: check_postgresql.pl for zabbix

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