Обсуждение: Is there way to detect uncommitted 'new table' in pg_class?

On Wed, Oct 31, 2018 at 6:05 AM Hubert Zhang <hzhang@pivotal.io> wrote:
> In PG READ UNCOMMITTED is treated as READ COMMITTED
> But I have a requirement to read dirty table. Is there way to detect table which is created in other uncommitted
transaction?
>
> T1:
> BEGIN;
> create table a(i int);
>
> T2:
> select * from pg_class where relname='a';
> could return table a?

No.  The catalog entries are uncommitted, and therefore invisible to
other transactions.

In theory, at least, you could write C code to scan the catalog tables
with SnapshotDirty to find the catalog entries, but I don't think that
helps a whole lot.  You couldn't necessarily rely on those catalog
entries to be in a consistent state, and even if they were, they might
depend on committed types or functions or similar whose definitions
your backend can't see.  Moreover, the creating backend will have an
AccessExclusiveLock on the table -- if you write C code to bypass that
and read the data anyway, then you will probably destabilize the
entire system for complicated reasons that I don't feel like
explaining right now.

You should try very hard to find some way of solving this problem that
doesn't require reading data from a table that hasn't been committed
yet, because you are almost certainly not going to be able to make
that work reliably even if you are willing to write code in C.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Oct 31, 2018 at 01:30:52PM -0400, Robert Haas wrote:
> In theory, at least, you could write C code to scan the catalog tables
> with SnapshotDirty to find the catalog entries, but I don't think that
> helps a whole lot.  You couldn't necessarily rely on those catalog
> entries to be in a consistent state, and even if they were, they might
> depend on committed types or functions or similar whose definitions
> your backend can't see.  Moreover, the creating backend will have an
> AccessExclusiveLock on the table -- if you write C code to bypass that
> and read the data anyway, then you will probably destabilize the
> entire system for complicated reasons that I don't feel like
> explaining right now.

One take here is that we cannot give any guarantee that a single DDL
will create only one consistent version of the tuple added in system
catalogs.  In those cases a new version is made visible by using
CommandCounterIncrement() so as the follow-up processing can see it.

> You should try very hard to find some way of solving this problem that
> doesn't require reading data from a table that hasn't been committed
> yet, because you are almost certainly not going to be able to make
> that work reliably even if you are willing to write code in C.

+1.
--
Michael