Re: Is there way to detect uncommitted 'new table' in pg_class?
Thanks On Thu, Nov 1, 2018 at 8:38 AM Michael Paquier 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
Re: Is there way to detect uncommitted 'new table' in pg_class?
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 signature.asc Description: PGP signature
Re: Is there way to detect uncommitted 'new table' in pg_class?
On Wed, Oct 31, 2018 at 6:05 AM Hubert Zhang 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
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