Thread: Is there way to detect uncommitted 'new table' in pg_class?
Hi all,
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?
-- Thanks
Hubert Zhang
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
Attachment
Thanks
On Thu, Nov 1, 2018 at 8:38 AM Michael Paquier <michael@paquier.xyz> wrote:
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
Thanks
Hubert Zhang