Horton

Thread

Commits

  1. Fix unlogged sequence corruption after standby promotion

  2. doc: Clarify pg_get_sequence_data() privileges and NULL results

  3. Fix misreporting of publisher sequence permissions during sync

  4. Introduce pg_sequence_read_tuple().

  1. Fix publisher-side sequence permission reporting

    Fujii Masao <masao.fujii@gmail.com> — 2026-06-18T15:36:06Z

    Hi,

While testing logical replication sequence synchronization, I found
that a publisher-side permission problem can be reported as a
misleading "missing sequence on publisher" warning.

The issue can be reproduced as follows:

1. On the publisher:

   CREATE SEQUENCE myseq;
   CREATE PUBLICATION mypub FOR ALL SEQUENCES;
   CREATE ROLE foo LOGIN REPLICATION NOSUPERUSER;

2. On the subscriber:

   CREATE SEQUENCE myseq;
   CREATE SUBSCRIPTION mysub
     CONNECTION 'user=foo dbname=postgres ...'
     PUBLICATION mypub;

The subscriber currently emits:

   WARNING:  missing sequence on publisher ("public.myseq")

even though the sequence still exists on the publisher. The real
problem is that the replication connection lacks SELECT privilege to
read the sequence data.

The cause is that; sequence synchronization obtains sequence data using
pg_get_sequence_data(). When the user lacks SELECT privilege on
the sequence, pg_get_sequence_data() returns a row containing all NULL
values. Sequence synchronization currently treats that the same as
a missing sequence, so publisher-side permission failures and
genuinely missing sequences are not distinguished, leading to
the misleading warning.

Patch 0001 fixes this by distinguishing the two cases during sequence
synchronization. It checks whether the replication connection has the
required privilege for each published sequence and reports
publisher-side permission failures separately.

While working on this, I also noticed that the documented privilege
requirement for pg_get_sequence_data() does not match the
implementation. The documentation says that USAGE or SELECT privilege
is sufficient, but the implementation requires SELECT.

Patch 0002 updates the documentation to match the current behavior.

I chose to update the documentation rather than broaden the
implementation for two reasons.

First, commit c8b06bb969b, which introduced the predecessor of
pg_get_sequence_data(), described it as a substitute for SELECT
from a sequence, and its implementation has always required
SELECT privilege.

Second, the logical replication documentation already states that
replicating sequence data requires SELECT privilege.

Patches attached.

Regards,

-- 
Fujii Masao

  2. Re: Fix publisher-side sequence permission reporting

    Tristan Partin <tristan@partin.io> — 2026-06-18T23:11:48Z

    Hi Masao-san,

On Thu Jun 18, 2026 at 10:36 AM CDT, Fujii Masao wrote:
> Hi,
>
> While testing logical replication sequence synchronization, I found
> that a publisher-side permission problem can be reported as a
> misleading "missing sequence on publisher" warning.
>
> The issue can be reproduced as follows:
>
> 1. On the publisher:
>
>    CREATE SEQUENCE myseq;
>    CREATE PUBLICATION mypub FOR ALL SEQUENCES;
>    CREATE ROLE foo LOGIN REPLICATION NOSUPERUSER;
>
> 2. On the subscriber:
>
>    CREATE SEQUENCE myseq;
>    CREATE SUBSCRIPTION mysub
>      CONNECTION 'user=foo dbname=postgres ...'
>      PUBLICATION mypub;
>
> The subscriber currently emits:
>
>    WARNING:  missing sequence on publisher ("public.myseq")
>
> even though the sequence still exists on the publisher. The real
> problem is that the replication connection lacks SELECT privilege to
> read the sequence data.
>
> The cause is that; sequence synchronization obtains sequence data using
> pg_get_sequence_data(). When the user lacks SELECT privilege on
> the sequence, pg_get_sequence_data() returns a row containing all NULL
> values. Sequence synchronization currently treats that the same as
> a missing sequence, so publisher-side permission failures and
> genuinely missing sequences are not distinguished, leading to
> the misleading warning.
>
> Patch 0001 fixes this by distinguishing the two cases during sequence
> synchronization. It checks whether the replication connection has the
> required privilege for each published sequence and reports
> publisher-side permission failures separately.

The patch looks good to me! I had one suggestion:

>  ##########
>  # Ensure that insufficient privileges on the publisher for a sequence do not
> -# disrupt the subscriber. The subscriber should log a warning and continue
> -# retrying.
> +# get misreported as a missing sequence. The subscriber should log a warning
> +# and continue retrying.
>  ##########

I think a better comment might be:

	Ensure that insufficient privileges on the publisher for a sequence 
	are reported correctly...

My reasoning for suggesting that is because your comment would to 
indicate that any warning is accurate as long as it isn't related to 
a missing sequence.

The API for pg_get_sequence_data() is very _interesting_. Overloading 
the NULLs row to mean many things is a bit strange. I'm not sure what 
a better solution would be. Just thought I would mention that.

> While working on this, I also noticed that the documented privilege
> requirement for pg_get_sequence_data() does not match the
> implementation. The documentation says that USAGE or SELECT privilege
> is sufficient, but the implementation requires SELECT.
>
> Patch 0002 updates the documentation to match the current behavior.
>
> I chose to update the documentation rather than broaden the
> implementation for two reasons.
>
> First, commit c8b06bb969b, which introduced the predecessor of
> pg_get_sequence_data(), described it as a substitute for SELECT
> from a sequence, and its implementation has always required
> SELECT privilege.
>
> Second, the logical replication documentation already states that
> replicating sequence data requires SELECT privilege.

Your reasoning for updating the documentation makes sense, and the patch 
you submitted achieves the stated goal.

-- 
Tristan Partin
PostgreSQL Contributors Team
AWS (https://aws.amazon.com)

  3. Re: Fix publisher-side sequence permission reporting

    Fujii Masao <masao.fujii@gmail.com> — 2026-06-19T14:15:52Z

    On Fri, Jun 19, 2026 at 8:11 AM Tristan Partin <tristan@partin.io> wrote:
> The patch looks good to me! I had one suggestion:
>
> >  ##########
> >  # Ensure that insufficient privileges on the publisher for a sequence do not
> > -# disrupt the subscriber. The subscriber should log a warning and continue
> > -# retrying.
> > +# get misreported as a missing sequence. The subscriber should log a warning
> > +# and continue retrying.
> >  ##########
>
> I think a better comment might be:
>
>         Ensure that insufficient privileges on the publisher for a sequence
>         are reported correctly...
>
> My reasoning for suggesting that is because your comment would to
> indicate that any warning is accurate as long as it isn't related to
> a missing sequence.

Thanks for the review! I've updated the patch as suggested.

Updated patches attached.

Regards,

-- 
Fujii Masao

  4. Re: Fix publisher-side sequence permission reporting

    Tristan Partin <tristan@partin.io> — 2026-06-19T16:38:27Z

    On Fri Jun 19, 2026 at 9:16 AM CDT, Fujii Masao wrote:
> On Fri, Jun 19, 2026 at 8:11 AM Tristan Partin <tristan@partin.io> wrote:
>> The patch looks good to me! I had one suggestion:
>>
>> >  ##########
>> >  # Ensure that insufficient privileges on the publisher for a sequence do not
>> > -# disrupt the subscriber. The subscriber should log a warning and continue
>> > -# retrying.
>> > +# get misreported as a missing sequence. The subscriber should log a warning
>> > +# and continue retrying.
>> >  ##########
>>
>> I think a better comment might be:
>>
>>         Ensure that insufficient privileges on the publisher for a sequence
>>         are reported correctly...
>>
>> My reasoning for suggesting that is because your comment would to
>> indicate that any warning is accurate as long as it isn't related to
>> a missing sequence.
>
> Thanks for the review! I've updated the patch as suggested.
>
> Updated patches attached.

Looks great! Thanks for fixing this.

-- 
Tristan Partin
PostgreSQL Contributors Team
AWS (https://aws.amazon.com)

  5. Re: Fix publisher-side sequence permission reporting

    Fujii Masao <masao.fujii@gmail.com> — 2026-06-20T09:24:07Z

    On Sat, Jun 20, 2026 at 1:38 AM Tristan Partin <tristan@partin.io> wrote:
> > Updated patches attached.
>
> Looks great! Thanks for fixing this.

Thanks for the review! I've pushed the patches.

Regards,

-- 
Fujii Masao

  6. Re: Fix publisher-side sequence permission reporting

    Amit Kapila <amit.kapila16@gmail.com> — 2026-06-20T10:20:58Z

    On Sat, Jun 20, 2026 at 2:54 PM Fujii Masao <masao.fujii@gmail.com> wrote:
>
> >
> > Looks great! Thanks for fixing this.
>
> Thanks for the review! I've pushed the patches.
>

You seem to have forgotten to update the following comment in
pg_get_sequence_data(): "Return all NULLs for missing sequences,
sequences for which we lack
privileges, other sessions' temporary sequences, ...". BTW, if we go
by the logic of your proposal, shouldn't one also distinguish other
cases as mentioned in the comment quoted by me?

Also, we might want to consider additional errhint as follows:
errhint("Grant UPDATE on the sequence to the subscription/sequence
owner on the subscriber.")
errhint("Grant SELECT on the sequence to the replication role on the
publisher.")

-- 
With Regards,
Amit Kapila.

  7. Re: Fix publisher-side sequence permission reporting

    Fujii Masao <masao.fujii@gmail.com> — 2026-06-20T12:43:59Z

    On Sat, Jun 20, 2026 at 7:21 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Sat, Jun 20, 2026 at 2:54 PM Fujii Masao <masao.fujii@gmail.com> wrote:
> >
> > >
> > > Looks great! Thanks for fixing this.
> >
> > Thanks for the review! I've pushed the patches.
> >
>
> You seem to have forgotten to update the following comment in
> pg_get_sequence_data(): "Return all NULLs for missing sequences,
> sequences for which we lack
> privileges, other sessions' temporary sequences, ...".

Do you mean that the documentation for pg_get_sequence_data() should
also mention other sessions' temporary sequences and unlogged sequences
on standbys, as the comment does? If I've misunderstood your point,
could you clarify?


> BTW, if we go
> by the logic of your proposal, shouldn't one also distinguish other
> cases as mentioned in the comment quoted by me?

Do you mean that sequencesync.c should also distinguish other
sessions' temporary sequences and unlogged sequences on standbys, and
report separate warnings for those cases?


> Also, we might want to consider additional errhint as follows:

Sounds good.


> errhint("Grant UPDATE on the sequence to the subscription/sequence
> owner on the subscriber.")

Wouldn't it be better to drop "sequence" from "subscription/sequence
owner"? The sequence owner should already have UPDATE privilege on
the sequence. How about:

    errhint("Grant UPDATE on the sequence to the subscription owner on
the subscriber.")


> errhint("Grant SELECT on the sequence to the replication role on the
> publisher.")

How about making this a bit more precise?

    errhint("Grant SELECT on the sequence to the role used for the
replication connection on the publisher.")

Regards,

-- 
Fujii Masao

  8. Re: Fix publisher-side sequence permission reporting

    Amit Kapila <amit.kapila16@gmail.com> — 2026-06-22T04:03:44Z

    On Sat, Jun 20, 2026 at 6:14 PM Fujii Masao <masao.fujii@gmail.com> wrote:
>
> On Sat, Jun 20, 2026 at 7:21 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > On Sat, Jun 20, 2026 at 2:54 PM Fujii Masao <masao.fujii@gmail.com> wrote:
> > >
> > > >
> > > > Looks great! Thanks for fixing this.
> > >
> > > Thanks for the review! I've pushed the patches.
> > >
> >
> > You seem to have forgotten to update the following comment in
> > pg_get_sequence_data(): "Return all NULLs for missing sequences,
> > sequences for which we lack
> > privileges, other sessions' temporary sequences, ...".
>
> Do you mean that the documentation for pg_get_sequence_data() should
> also mention other sessions' temporary sequences and unlogged sequences
> on standbys, as the comment does? If I've misunderstood your point,
> could you clarify?
>

It is better to update docs for all cases. Sorry, I was wrong in
saying that code comments need an update.

>
> > BTW, if we go
> > by the logic of your proposal, shouldn't one also distinguish other
> > cases as mentioned in the comment quoted by me?
>
> Do you mean that sequencesync.c should also distinguish other
> sessions' temporary sequences and unlogged sequences on standbys, and
> report separate warnings for those cases?
>

Right. I mean to ask if we want to distinguish the lack of privilege
as a separate case then why not others? My opinion on this point is
that improving all these cases (including lack of privileges) together
could be considered as an enhancement for the next version but if you
think this is sort of a must to distinguish one or more cases then we
can do it now as well. However, the reason for doing it now is not
clear to me.

>
> > Also, we might want to consider additional errhint as follows:
>
> Sounds good.
>
>
> > errhint("Grant UPDATE on the sequence to the subscription/sequence
> > owner on the subscriber.")
>
> Wouldn't it be better to drop "sequence" from "subscription/sequence
> owner"? The sequence owner should already have UPDATE privilege on
> the sequence. How about:
>
>     errhint("Grant UPDATE on the sequence to the subscription owner on
> the subscriber.")
>

makes sense.

>
> > errhint("Grant SELECT on the sequence to the replication role on the
> > publisher.")
>
> How about making this a bit more precise?
>
>     errhint("Grant SELECT on the sequence to the role used for the
> replication connection on the publisher.")
>

makes sense.

-- 
With Regards,
Amit Kapila.

  9. Re: Fix publisher-side sequence permission reporting

    Fujii Masao <masao.fujii@gmail.com> — 2026-06-24T00:41:16Z

    On Mon, Jun 22, 2026 at 1:03 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > Do you mean that the documentation for pg_get_sequence_data() should
> > also mention other sessions' temporary sequences and unlogged sequences
> > on standbys, as the comment does? If I've misunderstood your point,
> > could you clarify?
> >
>
> It is better to update docs for all cases. Sorry, I was wrong in
> saying that code comments need an update.

Understood.

BTW, isn't the current documentation a bit misleading? It says:

    This function returns a row of NULL values if the sequence does not exist.

But if the specified object does not exist, pg_get_sequence_data()
raises an error rather than returning a row of NULL values. On the
other hand, it does return a row of NULL values if the specified
object exists but is not a sequence. Is my understanding correct? If
so, how about something like:

---------------------
This function returns a row of NULL values if the specified object
exists but is not a sequence, if the current user lacks privileges on
the sequence, if the sequence is another session's temporary
sequence, or if it is an unlogged sequence on a standby server.
---------------------


> > Do you mean that sequencesync.c should also distinguish other
> > sessions' temporary sequences and unlogged sequences on standbys, and
> > report separate warnings for those cases?
> >
>
> Right. I mean to ask if we want to distinguish the lack of privilege
> as a separate case then why not others? My opinion on this point is
> that improving all these cases (including lack of privileges) together
> could be considered as an enhancement for the next version but if you
> think this is sort of a must to distinguish one or more cases then we
> can do it now as well. However, the reason for doing it now is not
> clear to me.

I think the lack-of-privilege case should be checked first, since it is
likely to be the most common one.

As for another session's temporary sequences, I don't think a sequence
sync worker can actually encounter one. To do so, it would have to
specify another session's temporary namespace when executing the query
below. However, the namespace comes from the publication, and temporary
sequences are never published, so that doesn't seem possible.

appendStringInfo(&cmd,
"SELECT s.seqidx, has_sequence_privilege(c.oid, 'SELECT'),\n"
"       ps.*, seq.seqtypid,\n"
"       seq.seqstart, seq.seqincrement, seq.seqmin,\n"
"       seq.seqmax, seq.seqcycle\n"
"FROM ( VALUES %s ) AS s (schname, seqname, seqidx)\n"
"JOIN pg_namespace n ON n.nspname = s.schname\n"
"JOIN pg_class c ON c.relnamespace = n.oid AND c.relname = s.seqname\n"
"JOIN pg_sequence seq ON seq.seqrelid = c.oid\n"
"JOIN LATERAL pg_get_sequence_data(seq.seqrelid) AS ps ON true\n",
seqstr.data);

An unlogged sequence on a standby seems theoretically possible, but
only under unlikely sequence of events:

1. The sequence sync worker fetches the sequence information from the
publication.
2. The sequence is dropped on the primary.
3. An unlogged sequence with the same name is created.
4. The schema change is replicated to the standby.
5. The sequence sync worker executes the above query with the fetched info.

So I think this case is possible, but unlikely.


> > > errhint("Grant SELECT on the sequence to the replication role on the
> > > publisher.")
> >
> > How about making this a bit more precise?
> >
> >     errhint("Grant SELECT on the sequence to the role used for the
> > replication connection on the publisher.")
> >
>
> makes sense.

The attached patch adds those HINT messages. It also updates the
related documentation.

Regards,

-- 
Fujii Masao

  10. Re: Fix publisher-side sequence permission reporting

    Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> — 2026-06-24T05:40:44Z

    Hi,

On Tue, Jun 23, 2026 at 5:41 PM Fujii Masao <masao.fujii@gmail.com> wrote:
>
> BTW, isn't the current documentation a bit misleading? It says:
>
>     This function returns a row of NULL values if the sequence does not exist.
>
> But if the specified object does not exist, pg_get_sequence_data()
> raises an error rather than returning a row of NULL values. On the
> other hand, it does return a row of NULL values if the specified
> object exists but is not a sequence. Is my understanding correct? If
> so, how about something like:

When the provided relation oid doesn't exist, returns NULL:
postgres=# select pg_get_sequence_data(99999999);
 pg_get_sequence_data
----------------------
 (,,)
(1 row)

When the provided relation oid (pg_statistic) exists but not a
sequence, returns NULL:
postgres=# select pg_get_sequence_data(2619);
 pg_get_sequence_data
----------------------
 (,,)
(1 row)

When the provided relation oid exists and is a sequence, returns sequence data:
postgres=# select pg_get_sequence_data(16388);
 pg_get_sequence_data
----------------------
 (1,f,0/017DD9A0)
(1 row)

> > > Do you mean that sequencesync.c should also distinguish other
> > > sessions' temporary sequences and unlogged sequences on standbys, and
> > > report separate warnings for those cases?
> > >
> >
> > Right. I mean to ask if we want to distinguish the lack of privilege
> > as a separate case then why not others? My opinion on this point is
> > that improving all these cases (including lack of privileges) together
> > could be considered as an enhancement for the next version but if you
> > think this is sort of a must to distinguish one or more cases then we
> > can do it now as well. However, the reason for doing it now is not
> > clear to me.
>
> I think the lack-of-privilege case should be checked first, since it is
> likely to be the most common one.
>
> As for another session's temporary sequences, I don't think a sequence
> sync worker can actually encounter one. To do so, it would have to
> specify another session's temporary namespace when executing the query
> below. However, the namespace comes from the publication, and temporary
> sequences are never published, so that doesn't seem possible.

Ideally, all these checks (except seqrel being NULL when
try_relation_open detects a concurrent drop) should report an ERROR if
not met, instead of silently emitting NULLs, so the subscriber catches
these early and reports "could not fetch sequence information from the
publisher:" But I understand that on the subscriber side it would
require error message parsing to distinguish the exact cause, which is
not ideal.

However, instead of pg_get_sequence_data emitting just 3 columns, it
could emit additional columns such as:

"last_value", "is_called", "page_lsn", "has_privileges",
"is_sequence", "is_temp_relation", "is_recovery_in_progress"

When any of the new flag columns are true, the first three values
would be NULLs.

This keeps the subscriber-side query simple and leaves the existing
error-handling code as-is.

> > > > errhint("Grant SELECT on the sequence to the replication role on the
> > > > publisher.")
> > >
> > > How about making this a bit more precise?
> > >
> > >     errhint("Grant SELECT on the sequence to the role used for the
> > > replication connection on the publisher.")
> > >
> >
> > makes sense.
>
> The attached patch adds those HINT messages. It also updates the
> related documentation.

Nice! This looks more explicit and clarifying. The patch LGTM.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

  11. Re: Fix publisher-side sequence permission reporting

    Amit Kapila <amit.kapila16@gmail.com> — 2026-06-24T07:40:11Z

    On Wed, Jun 24, 2026 at 6:11 AM Fujii Masao <masao.fujii@gmail.com> wrote:
>
> On Mon, Jun 22, 2026 at 1:03 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > > Do you mean that the documentation for pg_get_sequence_data() should
> > > also mention other sessions' temporary sequences and unlogged sequences
> > > on standbys, as the comment does? If I've misunderstood your point,
> > > could you clarify?
> > >
> >
> > It is better to update docs for all cases. Sorry, I was wrong in
> > saying that code comments need an update.
>
> Understood.
>
> BTW, isn't the current documentation a bit misleading? It says:
>
>     This function returns a row of NULL values if the sequence does not exist.
>
> But if the specified object does not exist, pg_get_sequence_data()
> raises an error rather than returning a row of NULL values. On the
> other hand, it does return a row of NULL values if the specified
> object exists but is not a sequence. Is my understanding correct? If
> so, how about something like:
>
> ---------------------
> This function returns a row of NULL values if the specified object
> exists but is not a sequence, if the current user lacks privileges on
> the sequence, if the sequence is another session's temporary
> sequence, or if it is an unlogged sequence on a standby server.
> ---------------------
>
>
> > > Do you mean that sequencesync.c should also distinguish other
> > > sessions' temporary sequences and unlogged sequences on standbys, and
> > > report separate warnings for those cases?
> > >
> >
> > Right. I mean to ask if we want to distinguish the lack of privilege
> > as a separate case then why not others? My opinion on this point is
> > that improving all these cases (including lack of privileges) together
> > could be considered as an enhancement for the next version but if you
> > think this is sort of a must to distinguish one or more cases then we
> > can do it now as well. However, the reason for doing it now is not
> > clear to me.
>
> I think the lack-of-privilege case should be checked first, since it is
> likely to be the most common one.
>
> As for another session's temporary sequences, I don't think a sequence
> sync worker can actually encounter one. To do so, it would have to
> specify another session's temporary namespace when executing the query
> below. However, the namespace comes from the publication, and temporary
> sequences are never published, so that doesn't seem possible.
>
> appendStringInfo(&cmd,
> "SELECT s.seqidx, has_sequence_privilege(c.oid, 'SELECT'),\n"
> "       ps.*, seq.seqtypid,\n"
> "       seq.seqstart, seq.seqincrement, seq.seqmin,\n"
> "       seq.seqmax, seq.seqcycle\n"
> "FROM ( VALUES %s ) AS s (schname, seqname, seqidx)\n"
> "JOIN pg_namespace n ON n.nspname = s.schname\n"
> "JOIN pg_class c ON c.relnamespace = n.oid AND c.relname = s.seqname\n"
> "JOIN pg_sequence seq ON seq.seqrelid = c.oid\n"
> "JOIN LATERAL pg_get_sequence_data(seq.seqrelid) AS ps ON true\n",
> seqstr.data);
>
> An unlogged sequence on a standby seems theoretically possible, but
> only under unlikely sequence of events:
>
> 1. The sequence sync worker fetches the sequence information from the
> publication.
>

Assume a case where the primary fails and the system promotes standby
as a new primary. Then the subscriber starts sync from the new
primary, there it can lead to an unlogged sequence sync scenario?

-- 
With Regards,
Amit Kapila.

  12. Re: Fix publisher-side sequence permission reporting

    Fujii Masao <masao.fujii@gmail.com> — 2026-06-24T08:50:17Z

    On Wed, Jun 24, 2026 at 4:40 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> Assume a case where the primary fails and the system promotes standby
> as a new primary. Then the subscriber starts sync from the new
> primary, there it can lead to an unlogged sequence sync scenario?

When I tested pg_get_sequence_data() with an unlogged sequence on
new primary after promotion, I hit an assertion failure...

---------------------
1. Create an unlogged sequence on the primary:

=# CREATE UNLOGGED SEQUENCE myseq;
CREATE SEQUENCE

2. Confirm that pg_get_sequence_data() returns NULL values on the standby:

=# SELECT * FROM pg_get_sequence_data('myseq');
 last_value | is_called | page_lsn
------------+-----------+----------
     (null) | (null)    | (null)
(1 row)

3. Promote the standby, then call pg_get_sequence_data() on the new primary:

=# SELECT * FROM pg_get_sequence_data('myseq');

This results in the following assertion failure:

TRAP: failed Assert("((const PageHeaderData *) page)->pd_special >=
SizeOfPageHeaderData"), File:
"../../../src/include/storage/bufpage.h", Line: 357, PID: 96253
0   postgres                            0x000000010d8d1412
ExceptionalCondition + 178
1   postgres                            0x000000010d316970
PageValidateSpecialPointer + 128
2   postgres                            0x000000010d3142df read_seq_tuple + 79
3   postgres                            0x000000010d31635e
pg_get_sequence_data + 382
4   postgres                            0x000000010d3bc42e
ExecMakeTableFunctionResult + 702
5   postgres                            0x000000010d3da6cf FunctionNext + 175
6   postgres                            0x000000010d3bde92 ExecScanFetch + 626
7   postgres                            0x000000010d3bd88f ExecScanExtended + 95
8   postgres                            0x000000010d3bd81f ExecScan + 95
9   postgres                            0x000000010d3da2c5 ExecFunctionScan + 53
10  postgres                            0x000000010d3b8a2b
ExecProcNodeFirst + 75
11  postgres                            0x000000010d3affee ExecProcNode + 46
12  postgres                            0x000000010d3ab507 ExecutePlan + 199
13  postgres                            0x000000010d3ab3e0
standard_ExecutorRun + 368
14  postgres                            0x000000010d3ab263 ExecutorRun + 67
15  postgres                            0x000000010d6c2710 PortalRunSelect + 256
16  postgres                            0x000000010d6c2170 PortalRun + 672
17  postgres                            0x000000010d6bd53c
exec_simple_query + 1292
18  postgres                            0x000000010d6bc6e5 PostgresMain + 2981
19  postgres                            0x000000010d6b5228 BackendMain + 168
20  postgres                            0x000000010d5a2269
postmaster_child_launch + 377
21  postgres                            0x000000010d5a8775 BackendStartup + 277
22  postgres                            0x000000010d5a6d85 ServerLoop + 341
23  postgres                            0x000000010d5a5b84 PostmasterMain + 5748
24  postgres                            0x000000010d43e533 main + 771
25  dyld                                0x00007ff80fc71530 start + 3056
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

Regards,

-- 
Fujii Masao

  13. Re: Fix publisher-side sequence permission reporting

    Fujii Masao <masao.fujii@gmail.com> — 2026-06-24T10:30:09Z

    On Wed, Jun 24, 2026 at 5:50 PM Fujii Masao <masao.fujii@gmail.com> wrote:
>
> On Wed, Jun 24, 2026 at 4:40 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > Assume a case where the primary fails and the system promotes standby
> > as a new primary. Then the subscriber starts sync from the new
> > primary, there it can lead to an unlogged sequence sync scenario?
>
> When I tested pg_get_sequence_data() with an unlogged sequence on
> new primary after promotion, I hit an assertion failure...

The assertion failure seems to be caused by seq_redo() not flushing
the init fork buffer from shared buffers. As a result, the init fork of
an unlogged sequence can remain invalid. During promotion,
ResetUnloggedRelations() creates the main fork by copying the init
fork from disk, so the main fork also becomes invalid. When
pg_get_sequence_data() later reads the invalid page, it hits the
assertion failure.

The attached patch adds a common function to flush an init fork buffer
and updates seq_redo() to use it. It also updates hash_xlog.c to
reuse the same function to simplify the code.

Thought?

Regards,

-- 
Fujii Masao

  14. Re: Fix publisher-side sequence permission reporting

    vignesh C <vignesh21@gmail.com> — 2026-06-24T10:49:25Z

    On Wed, 24 Jun 2026 at 16:00, Fujii Masao <masao.fujii@gmail.com> wrote:
>
> On Wed, Jun 24, 2026 at 5:50 PM Fujii Masao <masao.fujii@gmail.com> wrote:
> >
> > On Wed, Jun 24, 2026 at 4:40 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > > Assume a case where the primary fails and the system promotes standby
> > > as a new primary. Then the subscriber starts sync from the new
> > > primary, there it can lead to an unlogged sequence sync scenario?
> >
> > When I tested pg_get_sequence_data() with an unlogged sequence on
> > new primary after promotion, I hit an assertion failure...
>
> The assertion failure seems to be caused by seq_redo() not flushing
> the init fork buffer from shared buffers. As a result, the init fork of
> an unlogged sequence can remain invalid. During promotion,
> ResetUnloggedRelations() creates the main fork by copying the init
> fork from disk, so the main fork also becomes invalid. When
> pg_get_sequence_data() later reads the invalid page, it hits the
> assertion failure.
>
> The attached patch adds a common function to flush an init fork buffer
> and updates seq_redo() to use it. It also updates hash_xlog.c to
> reuse the same function to simplify the code.
>
> Thought?

Thanks for the patch. I verified that it fixes the issue with reading
unlogged sequences on a promoted standby.
Do you think it would be worthwhile to add a test for this scenario,
or do you feel the additional test is not necessary in this case?

Regards,
Vignesh

  15. Re: Fix publisher-side sequence permission reporting

    Fujii Masao <masao.fujii@gmail.com> — 2026-06-26T07:04:21Z

    On Wed, Jun 24, 2026 at 7:49 PM vignesh C <vignesh21@gmail.com> wrote:
> Thanks for the patch. I verified that it fixes the issue with reading
> unlogged sequences on a promoted standby.

Thanks for testing!


> Do you think it would be worthwhile to add a test for this scenario,
> or do you feel the additional test is not necessary in this case?

I think it's worth adding a test for this scenario, so I've added one to
the patch. The test uses nextval() to read the unlogged sequence
instead of pg_get_sequence_data(), since this patch needs to be
backpatched to v15, while pg_get_sequence_data() was introduced in
v19.

Attached are updated patches for master and the stable branches.

Regards,

-- 
Fujii Masao

  16. Re: Fix publisher-side sequence permission reporting

    vignesh C <vignesh21@gmail.com> — 2026-06-29T16:01:14Z

    On Fri, 26 Jun 2026 at 12:34, Fujii Masao <masao.fujii@gmail.com> wrote:
>
> On Wed, Jun 24, 2026 at 7:49 PM vignesh C <vignesh21@gmail.com> wrote:
> > Thanks for the patch. I verified that it fixes the issue with reading
> > unlogged sequences on a promoted standby.
>
> Thanks for testing!
>
>
> > Do you think it would be worthwhile to add a test for this scenario,
> > or do you feel the additional test is not necessary in this case?
>
> I think it's worth adding a test for this scenario, so I've added one to
> the patch. The test uses nextval() to read the unlogged sequence
> instead of pg_get_sequence_data(), since this patch needs to be
> backpatched to v15, while pg_get_sequence_data() was introduced in
> v19.

Thanks for the updated patches.  I verified that the issue occurs with
nextval() on all supported branches up to v15, where unlogged
sequences are available. The corresponding back patches apply cleanly
to their respective branches and fix the issue.  I also checked other
unlogged objects but couldn't reproduce a similar issue with them.

Overall, the patches look good to me.

Regards,
Vignesh

  17. Re: Fix publisher-side sequence permission reporting

    Fujii Masao <masao.fujii@gmail.com> — 2026-06-29T23:55:57Z

    On Tue, Jun 30, 2026 at 1:01 AM vignesh C <vignesh21@gmail.com> wrote:
> Thanks for the updated patches.  I verified that the issue occurs with
> nextval() on all supported branches up to v15, where unlogged
> sequences are available. The corresponding back patches apply cleanly
> to their respective branches and fix the issue.  I also checked other
> unlogged objects but couldn't reproduce a similar issue with them.
>
> Overall, the patches look good to me.

Thanks for the review! I've pushed the patch.

Regards,

-- 
Fujii Masao

  18. Re: Fix publisher-side sequence permission reporting

    Fujii Masao <masao.fujii@gmail.com> — 2026-07-01T16:08:44Z

    On Wed, Jun 24, 2026 at 2:40 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
>
> Hi,
>
> On Tue, Jun 23, 2026 at 5:41 PM Fujii Masao <masao.fujii@gmail.com> wrote:
> >
> > BTW, isn't the current documentation a bit misleading? It says:
> >
> >     This function returns a row of NULL values if the sequence does not exist.
> >
> > But if the specified object does not exist, pg_get_sequence_data()
> > raises an error rather than returning a row of NULL values. On the
> > other hand, it does return a row of NULL values if the specified
> > object exists but is not a sequence. Is my understanding correct? If
> > so, how about something like:
>
> When the provided relation oid doesn't exist, returns NULL:
> postgres=# select pg_get_sequence_data(99999999);
>  pg_get_sequence_data
> ----------------------
>  (,,)
> (1 row)

Yes. If the specified OID does not exist, the function returns a row of
NULL values. However, if the object is specified by name and it does
not exist, an error is raised because the argument has type regclass.

That's why I find the current wording "This function returns a row of
NULL values if the sequence does not exist." a bit misleading.

How about something like this instead?

    This function returns a row of NULL values if the specified relation
    OID does not exist, if it is not a sequence, if the current user lacks
    <literal>SELECT</literal> privilege on the sequence, if the sequence
    is another session's temporary sequence, or if it is an unlogged
    sequence on a standby server.


> > The attached patch adds those HINT messages. It also updates the
> > related documentation.
>
> Nice! This looks more explicit and clarifying. The patch LGTM.

Thanks for the review!

I've updated the patch furthermore. Attached.

Regards,

-- 
Fujii Masao

  19. Re: Fix publisher-side sequence permission reporting

    Amit Kapila <amit.kapila16@gmail.com> — 2026-07-02T09:55:13Z

    On Wed, Jul 1, 2026 at 9:38 PM Fujii Masao <masao.fujii@gmail.com> wrote:
>
> On Wed, Jun 24, 2026 at 2:40 PM Bharath Rupireddy
> <bharath.rupireddyforpostgres@gmail.com> wrote:
> >
> > Hi,
> >
> > On Tue, Jun 23, 2026 at 5:41 PM Fujii Masao <masao.fujii@gmail.com> wrote:
> > >
> > > BTW, isn't the current documentation a bit misleading? It says:
> > >
> > >     This function returns a row of NULL values if the sequence does not exist.
> > >
> > > But if the specified object does not exist, pg_get_sequence_data()
> > > raises an error rather than returning a row of NULL values. On the
> > > other hand, it does return a row of NULL values if the specified
> > > object exists but is not a sequence. Is my understanding correct? If
> > > so, how about something like:
> >
> > When the provided relation oid doesn't exist, returns NULL:
> > postgres=# select pg_get_sequence_data(99999999);
> >  pg_get_sequence_data
> > ----------------------
> >  (,,)
> > (1 row)
>
> Yes. If the specified OID does not exist, the function returns a row of
> NULL values. However, if the object is specified by name and it does
> not exist, an error is raised because the argument has type regclass.
>
> That's why I find the current wording "This function returns a row of
> NULL values if the sequence does not exist." a bit misleading.
>
> How about something like this instead?
>
>     This function returns a row of NULL values if the specified relation
>     OID does not exist, if it is not a sequence, if the current user lacks
>     <literal>SELECT</literal> privilege on the sequence, if the sequence
>     is another session's temporary sequence, or if it is an unlogged
>     sequence on a standby server.
>

Sounds reasonable. But after this we don't need the next para to say:
"It requires <literal>SELECT</literal> privilege on the sequence.".
See attached.

>
> > > The attached patch adds those HINT messages. It also updates the
> > > related documentation.
> >
> > Nice! This looks more explicit and clarifying. The patch LGTM.
>
> Thanks for the review!
>
> I've updated the patch furthermore. Attached.
>

LGTM.

-- 
With Regards,
Amit Kapila.

  20. Re: Fix publisher-side sequence permission reporting

    Fujii Masao <masao.fujii@gmail.com> — 2026-07-08T09:19:44Z

    On Thu, Jul 2, 2026 at 6:55 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > How about something like this instead?
> >
> >     This function returns a row of NULL values if the specified relation
> >     OID does not exist, if it is not a sequence, if the current user lacks
> >     <literal>SELECT</literal> privilege on the sequence, if the sequence
> >     is another session's temporary sequence, or if it is an unlogged
> >     sequence on a standby server.
> >
>
> Sounds reasonable. But after this we don't need the next para to say:
> "It requires <literal>SELECT</literal> privilege on the sequence.".
> See attached.

Thanks for the patch! I've pushed it.

> > I've updated the patch furthermore. Attached.
> >
>
> LGTM.

Thanks for the review! I've pushed this as well.

Regards,

-- 
Fujii Masao