Обсуждение: ERROR: uncommitted xmin 347341220 from before xid cutoff 967029200needs to be frozen

On Mon, Dec 9, 2019 at 4:52 AM rajesh kumar <vallarapurajesh@gmail.com> wrote:
> We recently started seeing an error “ERROR:  uncommitted xmin 347341220 from before xid cutoff 967029200 needs to be
frozen”on our user tables. 
> I’m unable to do ‘vacuum’, ‘vacuum freeze’ or ‘vacuum full’  on the affected tables.
>
> From what I read, this was a bug couple of years ago on System tables and it was fixed long back.
> However, we are seeing these errors on two of our User tables now.
>
> After some Google search, I found the fix but, they seem to be temporary.
>
> These are the solutions I found :
> 1. Truncate the table and restore the dump
>
> 2. remove ‘pg_internal.init’ from global directory
>
>
> I’m not yet sure about removing  the file ‘pg_internal.init’. So, I would go ahead with table rebuilt for now.
>
> Anyways, I would like to know if there is any permanent solution for this issue as I did not find a proper solution.

I think that the best thing to do would be to dump all of your data
using pg_dump, create a whole new cluster using initdb, restore the
data into the new cluster, and delete the old one.

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

On Mon, Dec 9, 2019 at 11:21 AM rajesh kumar <vallarapurajesh@gmail.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
PostgreSQL9.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