Re: Fix publisher-side sequence permission reporting

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.