Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Maxim Boguk <maxim.boguk@xxxxxxxxx>]

On Tue, May 22, 2018 at 9:47 PM, Andres Freund <andres@xxxxxxxxxxx> 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 relfrozenxid 248712603
>
> select ctid, xmin, xmax, cmin, cmax from pg_catalog.pg_authid where
> xmin::text::bigint=2894889518;
>   ctid  |    xmin    | xmax | cmin | cmax
> --------+------------+------+------+------
>  (1,26) | 2894889518 |    0 |    0 |    0
>
>
> postgres=# SELECT * FROM heap_page_items(get_raw_page('pg_authid', 1))
> where t_ctid::text='(1,26)';
> -[ RECORD 1
> ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> lp          | 26
> lp_off      | 4656
> lp_flags    | 1
> lp_len      | 144
> t_xmin      | 2894889518
> t_xmax      | 0
> t_field3    | 0
> t_ctid      | (1,26)
> t_infomask2 | 32779
> t_infomask  | 10507
> t_hoff      | 32
> t_bits      | 1111111111000000
> t_oid       | 189787727
>

So this row has, if I didn't screw up decoding the following infomask
bits set::
HEAP_HASNULL
HEAP_HASVARWIDTH
HEAP_HASOID
HEAP_XMIN_COMMITTED
HEAP_XMAX_INVALID
HEAP_UPDATED

So it's not been previously frozen, which I was wondering about.


> Which indeed makes it
> Any new role created in DB instantly affected by this issue.

What's txid_current()?


>
> In the same time:
>
>  select relfrozenxid from pg_class where relname='pg_authid';
>  relfrozenxid
> --------------
>    2863429136
>
> So it's interesting where value of " from before relfrozenxid 248712603"
> come from.

Hm. That's indeed odd.  Could you get a backtrace of the error with "bt
full" of the error?

Greetings,

Andres Freund

​select txid_current();
 txid_current
--------------
  41995913769
​

​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.​

--

Maxim Boguk
Senior Postgresql DBA
http://dataegret.com/

Phone RU: +7  985 433 0000
Phone UA: +380 99 143 0000
Phone AU: +61  45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk

"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно когда я так делаю ещё раз?"