Re: race condition in pg_class

[Smolkin Grigory]

> This is going to be a problem with any operation that does a transactional
> pg_class update without taking a lock that conflicts with ShareLock.
GRANT
> doesn't lock the table at all, so I can reproduce this in v17 as follows:
>
> == session 1
> create table t (c int);
> begin;
> grant select on t to public;
>
> == session 2
> alter table t add primary key (c);
>
> == back in session 1
> commit;
>
>
> We'll likely need to change how we maintain relhasindex or perhaps take a
lock
> in GRANT.

Oh, that explains it. Thank you very much.

> Can you explore that as follows?
>
>- PITR to just before the COMMIT record.
>- Save all rows of pg_class.
>- PITR to just after the COMMIT record.
>- Save all rows of pg_class.
>- Diff the two sets of saved rows.

Sure, but it will take some time, its a large db with lots of WAL segments
to apply.

> extensions

  extname   | extversion
+
 plpgsql| 1.0
 pg_stat_statements | 1.8
 pg_buffercache | 1.3
 pgstattuple| 1.5

Re: race condition in pg_class

[Smolkin Grigory]

> ALTER TABLE ADD CONSTRAINT would certainly have taken
> AccessExclusiveLock on the "example" table, which should be sufficient
> to prevent anything else from touching its pg_class row.  The only
> mechanism I can think of that might bypass that is a manual UPDATE on
> pg_class, which would just manipulate the row as a row without concern
> for associated relation-level locks.  Any chance that somebody was
> doing something like that?

No chance. Our infrastructure dont do that, and users dont just have the
privileges to mess with pg_catalog.

ср, 25 окт. 2023 г. в 21:06, Tom Lane :

> Smolkin Grigory  writes:
> > We are running PG13.10 and recently we have encountered what appears to
> be
> > a bug due to some race condition between ALTER TABLE ... ADD CONSTRAINT
> and
> > some other catalog-writer, possibly ANALYZE.
> > The problem is that after successfully creating index on relation (which
> > previosly didnt have any indexes), its pg_class.relhasindex remains set
> to
> > "false", which is illegal, I think.
> > Index was built using the following statement:
> > ALTER TABLE "example" ADD constraint "example_pkey" PRIMARY KEY (id);
>
> ALTER TABLE ADD CONSTRAINT would certainly have taken
> AccessExclusiveLock on the "example" table, which should be sufficient
> to prevent anything else from touching its pg_class row.  The only
> mechanism I can think of that might bypass that is a manual UPDATE on
> pg_class, which would just manipulate the row as a row without concern
> for associated relation-level locks.  Any chance that somebody was
> doing something like that?
>
> regards, tom lane
>