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
