Re: More than one pg_database entry for database

Tom Lane wrote:
> "James Wilford" <jwilford(at)mistral(dot)net> writes:
> > It's a redhat version of 7.3 - rh-postgresql-server-7.3.8-2 is the
> > package.
>
> That was obsoleted by Red Hat well over two years ago; you are overdue
> for an update.
>
> > xmin | cmin | xmax | cmax | oid | ctid | datname
> > ----------+------------+------------+------+----------+--------+--------
> > 59254726 | 0 | 0 | 0 | 31238435 | (0,28) | misp
> > 2 | 2430588419 | 2430588419 | 0 | 6790290 | (0,37) | misp
>
> It definitely looks to me like you have a problem with XID wraparound :-(
> The (0,37) row was apparently deleted long ago, but never vacuumed away,
> and now its xmax has wrapped around past the 2-billion-transaction event
> horizon, causing it to appear visible to MVCC-using queries. So there's
> something broken about your routine vacuuming procedures; you'd better
> take a look at that.
>
> As far as getting out of the situation, the only really good answer is a
> dump and reload. I can't think of any simple way of getting rid of the
> bogus row, but what you should be able to do to let pg_dump work is to
> rename misp to something else. You can rename it back after getting
> through the dump/reload, of course.

Or roll the XID counter back, vacuum the table, and restore the XID to
the original value. This is done with pg_resetxlog, though I am not
sure if we shipped it in 7.3.

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"The West won the world not by the superiority of its ideas or values
or religion but rather by its superiority in applying organized violence.
Westerners often forget this fact, non-Westerners never do."
(Samuel P. Huntington)