Re: "too many trigger records found for relation xyz"

On Tue, Apr 8, 2008 at 2:49 PM, Zdenek Kotala <Zdenek(dot)Kotala(at)sun(dot)com> wrote:

> Mikko Partio napsal(a):
>
> On Tue, Apr 8, 2008 at 1:45 PM, Zdenek Kotala <Zdenek(dot)Kotala(at)sun(dot)com>
> > wrote:
> >
> > Mikko Partio napsal(a):
> > >
> > > Should I try remove one of the duplicate rows from pg_class?
> > > Try it with caution. You should use ctid column to refer to exact row.
> > >
> > > Try before:
> > >
> > > select oid, ctid, xmin, xmax, cmin, cmax, relname from pg_class where
> > > oid
> > > = 294397;
> > >
> > > If both row are identical or not.
> > >
> > >
> >
> > # select oid, ctid, xmin, xmax, cmin, cmax, relname from pg_class where
> > oid
> > = 294397;
> > oid | ctid | xmin | xmax | cmin | cmax | relname
> >
> > --------+-----------+------------+------------+------+------+--------------
> > 294397 | (1125,36) | 3944654557 | 0 | 35 | 35 | abc
> > 294397 | (1124,55) | 3943984380 | 3943984642 | 3 | 3 | abc
> > (2 rows)
> >
> > Which one should I remove? What is the worst case scenario for the
> > delete?
> > The database is 1,5TB so I would rather not restore it from backups :-)
> >
>
> (1124,55) is deleted one. This row should not be visible. But it seems a
> problem with visibility - transaction wraparound. Do you have any warning
> message in the log about vacuuming? Or clog corruption when transaction
> 3943984642 is marked as rollbacked.
>

There are no such messages in the logfile, but autovacuum is working hard
to prevent xid wraparound (we have an import process which consums huge
amounts of xids).

# select max(age(relfrozenxid)) from pg_class where relkind = 'r' ;
max
------------
204500712
(1 row)

How can I tell if there is clog corruption?

Thanks a lot for your help.

Regards

Mikko