Обсуждение: Orphan Temp Table After PITR

Dear All,

We encountered this for the 3rd time after completing a PITR.

The database starts up as usual and we could carry out the read and write transactions. After a while the following errors start appearing in the log. So we tried running vacuumdb with options All,Freeze,Analyze to see if that can clear the logs. That is why we get the entries "checkpoints are occurring too frequently (11 seconds apart)" and so on. However during and after the vacuumdb is completed the "orphan temp table" entry keeps on printing in the log. Anyone has any idea why this is happening?

2011-04-19 12:12:31 MYT LOG:  connection received: host=[local]
2011-04-19 12:12:31 MYT LOG:  connection authorized: user=postgres database=np
2011-04-19 12:12:46 MYT LOG:  checkpoints are occurring too frequently (11 seconds apart)
2011-04-19 12:12:46 MYT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2011-04-19 12:12:48 MYT LOG:  checkpoints are occurring too frequently (2 seconds apart)
2011-04-19 12:12:48 MYT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2011-04-19 12:12:49 MYT LOG:  checkpoints are occurring too frequently (1 second apart)
2011-04-19 12:12:49 MYT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_108"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_19"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_35"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_18"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_21"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_6"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_42"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_12"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_30"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_17"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_8"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_44"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_46"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_25"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_5"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_22"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_120"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_36"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_47"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_13"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_14"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_15"."tmp_swsv" in database "np"
2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table "pg_temp_69"."session_id" in database "np"
2011-04-19 12:13:05 MYT LOG:  checkpoints are occurring too frequently (16 seconds apart)
2011-04-19 12:13:05 MYT HINT:  Consider increasing the configuration parameter "checkpoint_segments".

Hi all,

I am attempting to set up slony-i and have run into a minor
glitch...apparently whoever designed the database I have inherited
didn't consistently build indexes to go along with Primary Keys, or at
least that's the error message I have been getting.  I am far from
confident in my own sqlfu in the catalog tables.  Does anyone have a
script for identifying tables without indexes that correspond to their
PKs?  I'm just trying to avoid re-inventing the wheel if I can help it.

Thanks,

John


John P Weatherman
Sr Database Administrator
Centerstone

Selva manickaraja <mavles78@gmail.com> wrote:

> We encountered this for the 3rd time after completing a PITR.

> 2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp table
> "pg_temp_108"."session_id" in database "np"
> [for many different temp tables]

> Anyone has any idea why this is happening?

There were a lot of temp tables open on the master when you made the
base backup?  It would seem autovacuum is cleaning them up when it
comes across them.

Do you have some reason to believe that these "LOC" level entries
represent some problem?

-Kevin

John P Weatherman <jweatherman91@alumni.wfu.edu> writes:
> I am attempting to set up slony-i and have run into a minor
> glitch...apparently whoever designed the database I have inherited
> didn't consistently build indexes to go along with Primary Keys, or at
> least that's the error message I have been getting.  I am far from
> confident in my own sqlfu in the catalog tables.  Does anyone have a
> script for identifying tables without indexes that correspond to their
> PKs?  I'm just trying to avoid re-inventing the wheel if I can help it.

At least in Postgres, there is no such animal as a PK (or unique)
constraint without an underlying index.  Maybe you'd better be more
clear about what problem you're having.

            regards, tom lane

John P Weatherman <jweatherman91@alumni.wfu.edu> writes:

> Hi all,
>
> I am attempting to set up slony-i and have run into a minor
> glitch...apparently whoever designed the database I have inherited
> didn't consistently build indexes to go along with Primary Keys, or at
> least that's the error message I have been getting.  I am far from
> confident in my own sqlfu in the catalog tables.  Does anyone have a
> script for identifying tables without indexes that correspond to their
> PKs?  I'm just trying to avoid re-inventing the wheel if I can help it.

Here's an example for you...

begin;

create schema foo;
set search_path to foo;

create table haspk (a int primary key);
create table missingpk (a int);

select relname
from pg_class c
join pg_namespace n on c.relnamespace = n.oid
where nspname = 'foo'
and relkind = 'r'
and c.oid not in (
    select conrelid
    from pg_constraint
    where contype = 'p'
);

abort;

HTH
>
> Thanks,
>
> John
>
>
> John P Weatherman
> Sr Database Administrator
> Centerstone

--
Jerry Sievers
Postgres DBA/Development Consulting
e: gsievers19@comcast.net
p: 305.321.1144

--- On Tue, 4/19/11, Jerry Sievers <gsievers19@comcast.net> wrote:

> From: Jerry Sievers <gsievers19@comcast.net>
> Subject: Re: [ADMIN] PKs without indexes
> To: jweatherman91@alumni.wfu.edu
> Cc: pgsql-admin@postgresql.org
> Date: Tuesday, April 19, 2011, 11:19 AM
> John P Weatherman <jweatherman91@alumni.wfu.edu>
> writes:
>
> > Hi all,
> >
> > I am attempting to set up slony-i and have run into a
> minor
> > glitch...apparently whoever designed the database I
> have inherited
> > didn't consistently build indexes to go along with
> Primary Keys, or at
> > least that's the error message I have been
> getting.  I am far from
> > confident in my own sqlfu in the catalog tables. 
> Does anyone have a
> > script for identifying tables without indexes that
> correspond to their
> > PKs?  I'm just trying to avoid re-inventing the
> wheel if I can help it.
>
> Here's an example for you...
>
> begin;
>
> create schema foo;
> set search_path to foo;
>
> create table haspk (a int primary key);
> create table missingpk (a int);
>
> select relname
> from pg_class c
> join pg_namespace n on c.relnamespace = n.oid
> where nspname = 'foo'
> and relkind = 'r'
> and c.oid not in (
>     select conrelid
>     from pg_constraint
>     where contype = 'p'
> );
>
> abort;
>
> HTH

Slony will use any unique index on a table for replication purposes, so the list of tables should come from:

select relname
  from pg_class c
  join pg_namespace n on c.relnamespace = n.oid
 where nspname = current_schema()
   and relkind = 'r'
   and c.oid not in (
       select indrelid
         from pg_index
        where indisprimary or indisunique
)
 order by 1;

Bob Lunney

Awesome!  Thanks Bob.

On Tue, 2011-04-19 at 12:51 -0700, Bob Lunney wrote:
>
> Slony will use any unique index on a table for replication purposes, so the list of tables should come from:
>
> select relname
>   from pg_class c
>   join pg_namespace n on c.relnamespace = n.oid
>  where nspname = current_schema()
>    and relkind = 'r'
>    and c.oid not in (
>        select indrelid
>          from pg_index
>         where indisprimary or indisunique
> )
>  order by 1;
>
> Bob Lunney
>
>

The problem is, the DB keeps on printing these entries the whole day and bloats the log file. It's 11AM now and already 14,700 entries of this message in the log and it keeps on increasing. What needs to be done to counter this unnecessary logging? This machine will be the production secondary DB and nominated as the primary to fail-over. So we need to ensure it is clear of any issue.

Thank you.

Regards,

Selvam

[Rearranging to eliminate top-posting.  List style in embedded
responses.]

Selva manickaraja <  mavles78@gmail.com  > wrote:
> Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
>> Selva manickaraja <mavles78@gmail.com> wrote:
>>
>>> We encountered this for the 3rd time after completing a PITR.
>>
>>> 2011-04-19 12:12:59 MYT LOG:  autovacuum: found orphan temp
>>> table "pg_temp_108"."session_id" in database "np"
>>> [for many different temp tables]
>>
>>> Anyone has any idea why this is happening?
>>
>> There were a lot of temp tables open on the master when you made
>> the base backup?  It would seem autovacuum is cleaning them up
>> when it comes across them.
>>
>> Do you have some reason to believe that these "LOC" level entries
>> represent some problem?

> The problem is, the DB keeps on printing these entries the whole
> day and bloats the log file. It's 11AM now and already 14,700
> entries of this message in the log and it keeps on increasing.

I'm assuming the 1 MB or so of disk space used by those lines isn't a
problem.  If they're getting in the way of spotting log entries you
need to notice, you might consider grep -v as a workaround, but the
big question is why you're getting so many.  To that end:

(1)  In previous posts you didn't show any duplicates.  Are the
14,700 entries for 14,700 different files, or are files showing up
more than once?

(2)  If you look under your production server's data directory, how
many such files can you find?

(3)  If you still have a copy of the base backup, how many such
files were in it?

(4)  Check the replica to see how many such files are still there.
Get a list.  Do any log entries show up after that for files which
weren't there?

(5)  Could you show us your configuration?  The easiest way is to
run the query on this page:

http://wiki.postgresql.org/wiki/Server_Configuration

> What needs to be done to counter this unnecessary logging?

I'm not sure I would want my server to delete such files without
mentioning it.  It is just LOG level, not even WARNING.  Rather than
considering the logging to be the problem, I'd first be concerned
that it might be providing a valuable clue to something that's a
problem with current operations on the master.

-Kevin