Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Fri, May 25, 2018 at 1:38 PM, Andres Freund wrote: >> BTW I think the definition of HeapTupleHeaderXminFrozen is seriously >> confusing, by failing to return true if the xmin is numerically >> FrozenXid (which it'll be if the database was pg_upgraded). I wonder >> about

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-05-24 16:46:24 -0400, Alvaro Herrera wrote: > On 2018-May-24, Andres Freund wrote: > > > On 2018-05-24 13:08:53 -0400, Alvaro Herrera wrote: > > > Hmm .. surely > > > > xid = HeapTupleHeaderGetXmin(tuple); > > > xmin_frozen = ((xid == FrozenTransactionId) || > > >

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-05-24 17:13:11 -0400, Alvaro Herrera wrote: > On 2018-May-24, Andres Freund wrote: > > > On 2018-05-24 16:49:40 -0400, Alvaro Herrera wrote: > > > BTW is it just a coincidence or are all the affected tables pg_authid? > > > Maybe the problem is shared relations ..? Maybe the fact that

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-May-24, Andres Freund wrote: > On 2018-05-24 16:49:40 -0400, Alvaro Herrera wrote: > > BTW is it just a coincidence or are all the affected tables pg_authid? > > Maybe the problem is shared relations ..? Maybe the fact that they have > > separate relfrozenxid (!?) in different databases?

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-05-24 16:49:40 -0400, Alvaro Herrera wrote: > BTW is it just a coincidence or are all the affected tables pg_authid? > Maybe the problem is shared relations ..? Maybe the fact that they have > separate relfrozenxid (!?) in different databases? Yes, that appears to be part of the problem.

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

> > BTW is it just a coincidence or are all the affected tables pg_authid? > Maybe the problem is shared relations ..? Maybe the fact that they have > separate relfrozenxid (!?) in different databases? > > -- > Álvaro Herrerahttps://www.2ndQuadrant.com/ > PostgreSQL Development,

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-May-24, Andres Freund wrote: > On 2018-05-24 13:08:53 -0400, Alvaro Herrera wrote: > > Hmm .. surely > > xid = HeapTupleHeaderGetXmin(tuple); > > xmin_frozen = ((xid == FrozenTransactionId) || > >HeapTupleHeaderXminFrozen(tuple)); > > - if

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-May-24, Andres Freund wrote: > FWIW, even if that weren't the case: a) there'd be a lot more wrong with > this routine imo. b) some of the tuples affected clearly weren't > frozen... Right. BTW is it just a coincidence or are all the affected tables pg_authid? Maybe the problem is

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-05-24 13:30:54 -0700, Andres Freund wrote: > On 2018-05-24 13:08:53 -0400, Alvaro Herrera wrote: > > Hmm .. surely > > > > diff --git a/src/backend/access/heap/heapam.c > > b/src/backend/access/heap/heapam.c > > index 5016181fd7..5d7fa1fb45 100644 > > ---

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-05-24 13:08:53 -0400, Alvaro Herrera wrote: > Hmm .. surely > > diff --git a/src/backend/access/heap/heapam.c > b/src/backend/access/heap/heapam.c > index 5016181fd7..5d7fa1fb45 100644 > --- a/src/backend/access/heap/heapam.c > +++ b/src/backend/access/heap/heapam.c > @@ -6690,7 +6690,7

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

Hmm .. surely diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index 5016181fd7..5d7fa1fb45 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -6690,7 +6690,7 @@ heap_prepare_freeze_tuple(HeapTupleHeader tuple, xid =

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Thu, May 24, 2018 at 12:38 PM, Maxim Boguk wrote: > > >> >> > ​About gdb bt - it's tricky because it is mission critical master db of >> > huge project. >> > I'll will try promote backup replica and check is issue persist there >> and >> > if yes - we will have our

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

> > > > ​About gdb bt - it's tricky because it is mission critical master db of > > huge project. > > I'll will try promote backup replica and check is issue persist there and > > if yes - we will have our playground for a while, but it will require > > sometime to arrange.​ > > You should be ok

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Tue, May 22, 2018 at 2:42 PM, Maxim Boguk wrote: > > > On Tue, May 22, 2018 at 10:30 PM, Andres Freund > wrote: > >> Hi, >> >> On 2018-05-22 22:18:15 +0300, Maxim Boguk wrote: >> > On Tue, May 22, 2018 at 9:47 PM, Andres Freund

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

Hi, On 2018-05-22 22:18:15 +0300, Maxim Boguk wrote: > On Tue, May 22, 2018 at 9:47 PM, Andres Freund wrote: > > > select relfrozenxid from pg_class where relname='pg_authid'; > > > relfrozenxid > > > -- > > >2863429136 > ​select txid_current(); >

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Tue, May 22, 2018 at 9:47 PM, Andres Freund <and...@anarazel.de> wrote: > Hi, > > On 2018-05-22 21:30:43 +0300, Maxim Boguk wrote: > > ​For sample: > > > > postgres=# vacuum pg_catalog.pg_authid; > > ERROR: found xmin 2894889518 from before relfrozen

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

Hi, On 2018-05-22 21:30:43 +0300, Maxim Boguk wrote: > ​For sample: > > postgres=# vacuum pg_catalog.pg_authid; > ERROR: found xmin 2894889518 from before relfrozenxid 248712603 > > select ctid, xmin, xmax, cmin, cmax from pg_catalog.pg_authid where > xmin::text::bigint

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

? > > Greetings, > > Andres Freund > ​For sample: postgres=# vacuum pg_catalog.pg_authid; ERROR: found xmin 2894889518 from before relfrozenxid 248712603 select ctid, xmin, xmax, cmin, cmax from pg_catalog.pg_authid where xmin::text::bigint=2894889518;

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-05-15 11:06:38 +0200, Maxim Boguk wrote: > ​Hi everyone, > > I just got the same issue on 9.6.8: > > 2018-05-15 11:52:01 MSK 33558 @ from [vxid:317/92895305 txid:0] [] ERROR: > found xmin 2808837517 from before relfrozenxid 248712603 > 2018-05-15 11:52:01 MSK 33558 @ from

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

​Hi everyone, I just got the same issue on 9.6.8: 2018-05-15 11:52:01 MSK 33558 @ from [vxid:317/92895305 txid:0] [] ERROR: found xmin 2808837517 from before relfrozenxid 248712603 2018-05-15 11:52:01 MSK 33558 @ from [vxid:317/92895305 txid:0] [] CONTEXT: automatic vacuum of table

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Thu, Mar 22, 2018 at 2:24 PM, Jeremy Finzel wrote: > I am running this on a san snapshot of our production system. I assume that > this will give me a valid check for file-system-level corruption. I am > going to kick it off and see if I find anything interesting. It

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Thu, Mar 22, 2018 at 3:20 PM, Peter Geoghegan wrote: > On Thu, Mar 22, 2018 at 12:27 PM, Jeremy Finzel wrote: > > Thank you for the recommendation. I ran both amcheck functions on all 4 > > indexes of those 2 tables with heapallindexed = true, but no issues

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

I admit I'm pretty surprised by this whole episode. I have no useful advice to offer here. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Wed, Mar 21, 2018 at 4:29 PM, Peter Geoghegan wrote: > On Wed, Mar 21, 2018 at 1:38 PM, Jeremy Finzel wrote: > > A server restart and upgrade to 9.5.12 (at the same time), as expected, > made > > the issue go away. Still doesn't give us any answers as to

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Wed, Mar 21, 2018 at 1:38 PM, Jeremy Finzel wrote: > A server restart and upgrade to 9.5.12 (at the same time), as expected, made > the issue go away. Still doesn't give us any answers as to what happened or > if it would happen again! Thanks for the feeback. You may

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Tue, Mar 20, 2018 at 11:19 AM, Jeremy Finzel wrote: > > > On Mon, Mar 19, 2018 at 3:55 PM, Jeremy Finzel wrote: > >> >> >> On Mon, Mar 19, 2018 at 3:53 PM, Peter Geoghegan wrote: >> >>> On Mon, Mar 19, 2018 at 1:01 PM, Jeremy Finzel

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 3:55 PM, Jeremy Finzel wrote: > > > On Mon, Mar 19, 2018 at 3:53 PM, Peter Geoghegan wrote: > >> On Mon, Mar 19, 2018 at 1:01 PM, Jeremy Finzel wrote: >> > SELECT heap_page_items(get_raw_page('pg_authid', 7)); >> >>

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 4:12 PM, Peter Geoghegan wrote: > On Mon, Mar 19, 2018 at 1:55 PM, Jeremy Finzel wrote: > > @Peter : > > > > staging=# SELECT * FROM page_header(get_raw_page('pg_authid', 7)); > > lsn | checksum | flags | lower | upper |

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 1:55 PM, Jeremy Finzel wrote: > @Peter : > > staging=# SELECT * FROM page_header(get_raw_page('pg_authid', 7)); > lsn | checksum | flags | lower | upper | special | pagesize | > version | prune_xid >

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 3:53 PM, Peter Geoghegan wrote: > On Mon, Mar 19, 2018 at 1:01 PM, Jeremy Finzel wrote: > > SELECT heap_page_items(get_raw_page('pg_authid', 7)); > > Can you post this? > > SELECT * FROM page_header(get_raw_page('pg_authid', 7)); > > -- >

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 1:01 PM, Jeremy Finzel wrote: > SELECT heap_page_items(get_raw_page('pg_authid', 7)); Can you post this? SELECT * FROM page_header(get_raw_page('pg_authid', 7)); -- Peter Geoghegan

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

Hi, On 2018-03-19 15:37:51 -0500, Jeremy Finzel wrote: > Does the fact that a snapshot does not have this issue suggest it could be > memory-related corruption and a db restart could clear it up? Could you show the page from the snapshot? I suspect it might just be a problem that's temporarily

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 3:01 PM, Jeremy Finzel wrote: > > > On Mon, Mar 19, 2018 at 2:56 PM, Andres Freund wrote: > >> Hi, >> >> On 2018-03-19 14:53:58 -0500, Jeremy Finzel wrote: >> > FWIW, if I remove the last filter, I get these rows and I believe row

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 2:56 PM, Andres Freund wrote: > Hi, > > On 2018-03-19 14:53:58 -0500, Jeremy Finzel wrote: > > FWIW, if I remove the last filter, I get these rows and I believe row > 7/57/ > > 2906288382 is the one generating error: > > Oh, yea, that makes sense. It's

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

Hi, On 2018-03-19 14:53:58 -0500, Jeremy Finzel wrote: > FWIW, if I remove the last filter, I get these rows and I believe row 7/57/ > 2906288382 is the one generating error: Oh, yea, that makes sense. It's wrapped around and looks like it's from the future. > SELECT * FROM

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 2:41 PM, Andres Freund wrote: > On 2018-03-19 14:37:24 -0500, Jeremy Finzel wrote: > > We upgraded to 9.5.5, and today we are running 9.5.11. And actually we > > upgraded from 9.3, not 9.4. We are still trying to figure out which > point > > release

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-03-19 14:37:24 -0500, Jeremy Finzel wrote: > We upgraded to 9.5.5, and today we are running 9.5.11. And actually we > upgraded from 9.3, not 9.4. We are still trying to figure out which point > release we were on at 9.3. Ok. IIRC there used to be a bug a few years back that sometimes

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 1:17 PM, Andres Freund wrote: > Hi Jeremy, Alvaro, > > On 2018-03-19 13:00:13 -0500, Jeremy Finzel wrote: > > On Mon, Mar 19, 2018 at 12:46 PM, Alvaro Herrera < > alvhe...@alvh.no-ip.org> > > wrote: > > > > > Jeremy Finzel wrote: > > > > Getting some

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

Hi Jeremy, Alvaro, On 2018-03-19 13:00:13 -0500, Jeremy Finzel wrote: > On Mon, Mar 19, 2018 at 12:46 PM, Alvaro Herrera > wrote: > > > Jeremy Finzel wrote: > > > Getting some concerning errors in one of our databases that is on 9.5.11, > > > on autovacuum from

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 12:46 PM, Alvaro Herrera wrote: > Jeremy Finzel wrote: > > Getting some concerning errors in one of our databases that is on 9.5.11, > > on autovacuum from template0 database pg_authid and pg_auth_members. I > > only saw some notes on the list

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

pg_control version number:942 Catalog version number: 201510051 Database system identifier: 6351536019599012028 Database cluster state: in production pg_control last modified: Mon 19 Mar 2018 12:56:10 PM CDT Latest checkpoint location:

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

Jeremy Finzel wrote: > Getting some concerning errors in one of our databases that is on 9.5.11, > on autovacuum from template0 database pg_authid and pg_auth_members. I > only saw some notes on the list about this error related to materialized > views. FWIW, we did use pg_upgrade to upgrade