Re: ERROR: uncommitted xmin 347341220 from before xid cutoff 967029200 needs to be frozen

On Mon, Dec 9, 2019 at 11:21 AM rajesh kumar <vallarapurajesh(at)gmail(dot)com> wrote:
> Thanks for your reply.
> So, i have this question. I have seen a patch on similar issue with shared catalog tables and it is fixed in PostgreSQL 9.6.10.
> We are currently using 9.6.10.
> Do you think we hit another bug ?
> Is this because of some synchronization issue ?
>
> Or is there something i should do to avoid this issue in the future ?

I mean, you haven't really provided any useful details that would
enable me or anyone to guess how the problem happened in the first
place. It could be a bug, but you've just given a very high-level
summary of what happened, so who knows? Note that this list is for
development of PostgreSQL, not technical support.

One thing to keep in mind is that the error is just a symptom of
corruption that happened earlier and was, in effect, detected by
VACUUM. And those error checks were not there originally; those were
back-patched into some relatively recent minor version. So it could be
that you were running an older version that had a bug and the problem
got created, and then when you upgraded to a newer version after that
the older corruption got detected by the new checks.

If you dump and restore, and if there's nothing in your environment
that can cause database corruption (bad hardware, bad kernel, bad
filesystem, more PostgreSQL bugs, bad backup-and-restore procedure,
fsync=off, ...) then you shouldn't have any more corruption after
that. If you do, then there's a problem someplace, and a PostgreSQL
bug is a likely but not certain culprit. However, if that's the case,
you'd need to provide lots of details about how to reproduce the
problem, or about how the problem happened, in order for somebody to
be able to fix it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company