Обсуждение: Duplicate tables information through metadata queries
Hello,
I have recently found some strange behavior when getting a list of tables using the JDBC meta-data APIs: I am getting duplicate results for some tables. I have reproduced this on Postgres 11.2, 13.4, with drivers 42.2.15 and 42.2.19, Java 15 JVM all running locally on a Windows 10 laptop, and also tried on a Windows Server 2012 VM. I am doing the following:
import java.sql.*;
public class MetaDataTest
{
public static void main(String[] args)
throws Exception
{
Class.forName("org.postgresql.Driver");
java.sql.Connection C = DriverManager.getConnection("jdbc:postgresql://localhost:5432/Pepper", "postgres", args[0]);
DatabaseMetaData meta = C.getMetaData();
ResultSet RS1 = meta.getTables(null, "people", "contact", null);
while (RS1.next() != false)
{
System.out.println("Table PEOPLE.Contact");
printResult(RS1, " ");
ResultSet RS2 = meta.getColumns(null, "people", "contact", null);
System.out.println(" Columns:");
while (RS2.next() != false)
printResult(RS2, " ");
}
}
protected static void printResult(ResultSet RS, String header)
throws SQLException
{
StringBuilder str = new StringBuilder(header);
int count = RS.getMetaData().getColumnCount();
for (int i = 1; i <= count; ++i)
str.append(RS.getMetaData().getColumnName(i) + ":" + RS.getString(i) + "; ");
System.out.println(str.toString());
}
}
What’s weird is that I am getting 2 results for that one table I am looking for. This happens for a handful of tables across our environment consisting of 24 schemas and over 500 tables. This is a database that has existed on 11 and was migrated to 13. For example, for one of those tables, this is what I am getting from the code above:
Table PEOPLE.Contact
table_cat:null; table_schem:people; table_name:contact; table_type:TABLE; remarks:btree comparison function; type_cat:; type_schem:; type_name:; self_referencing_col_name:; ref_generation:;
Columns:
TABLE_CAT:null; TABLE_SCHEM:people; TABLE_NAME:contact; COLUMN_NAME:person_rn; DATA_TYPE:-5; TYPE_NAME:int8; COLUMN_SIZE:19; BUFFER_LENGTH:null; DECIMAL_DIGITS:0; NUM_PREC_RADIX:10; NULLABLE:0; REMARKS:The person this contact record belongs to; COLUMN_DEF:null; SQL_DATA_TYPE:null; SQL_DATETIME_SUB:null; CHAR_OCTET_LENGTH:19; ORDINAL_POSITION:1; IS_NULLABLE:NO; SCOPE_CATALOG:null; SCOPE_SCHEMA:null; SCOPE_TABLE:null; SOURCE_DATA_TYPE:null; IS_AUTOINCREMENT:NO; IS_GENERATEDCOLUMN:;
TABLE_CAT:null; TABLE_SCHEM:people; TABLE_NAME:contact; COLUMN_NAME:type; DATA_TYPE:1; TYPE_NAME:bpchar; COLUMN_SIZE:5; BUFFER_LENGTH:null; DECIMAL_DIGITS:0; NUM_PREC_RADIX:10; NULLABLE:0; REMARKS:The type of this contact; COLUMN_DEF:'HM'::bpchar; SQL_DATA_TYPE:null; SQL_DATETIME_SUB:null; CHAR_OCTET_LENGTH:5; ORDINAL_POSITION:2; IS_NULLABLE:NO; SCOPE_CATALOG:null; SCOPE_SCHEMA:null; SCOPE_TABLE:null; SOURCE_DATA_TYPE:null; IS_AUTOINCREMENT:NO; IS_GENERATEDCOLUMN:;
...
table_cat:null; table_schem:people; table_name:contact; table_type:TABLE; remarks:blah blah; type_cat:; type_schem:; type_name:; self_referencing_col_name:; ref_generation:;
Columns:
TABLE_CAT:null; TABLE_SCHEM:people; TABLE_NAME:contact; COLUMN_NAME:person_rn; DATA_TYPE:-5; TYPE_NAME:int8; COLUMN_SIZE:19; BUFFER_LENGTH:null; DECIMAL_DIGITS:0; NUM_PREC_RADIX:10; NULLABLE:0; REMARKS:The person this contact record belongs to; COLUMN_DEF:null; SQL_DATA_TYPE:null; SQL_DATETIME_SUB:null; CHAR_OCTET_LENGTH:19; ORDINAL_POSITION:1; IS_NULLABLE:NO; SCOPE_CATALOG:null; SCOPE_SCHEMA:null; SCOPE_TABLE:null; SOURCE_DATA_TYPE:null; IS_AUTOINCREMENT:NO; IS_GENERATEDCOLUMN:;
TABLE_CAT:null; TABLE_SCHEM:people; TABLE_NAME:contact; COLUMN_NAME:type; DATA_TYPE:1; TYPE_NAME:bpchar; COLUMN_SIZE:5; BUFFER_LENGTH:null; DECIMAL_DIGITS:0; NUM_PREC_RADIX:10; NULLABLE:0; REMARKS:The type of this contact; COLUMN_DEF:'HM'::bpchar; SQL_DATA_TYPE:null; SQL_DATETIME_SUB:null; CHAR_OCTET_LENGTH:5; ORDINAL_POSITION:2; IS_NULLABLE:NO; SCOPE_CATALOG:null; SCOPE_SCHEMA:null; SCOPE_TABLE:null; SOURCE_DATA_TYPE:null; IS_AUTOINCREMENT:NO; IS_GENERATEDCOLUMN:;
...
The only difference I saw was in the “remarks” for the two tables, with one of them saying "btree comparison function" which is strange.
If I query the Postgres information schema, I am only seeing 1 result:
select * from information_schema.tables WHERE table_name='contact'
table_catalog|table_schema|table_name|table_type|self_referencing_column_name|reference_generation|user_defined_type_catalog|user_defined_type_schema|user_defined_type_name|is_insertable_into|is_typed|commit_action|
-------------+------------+----------+----------+----------------------------+--------------------+-------------------------+------------------------+----------------------+------------------+--------+-------------+
Pepper |people |contact |BASE TABLE|[NULL] |[NULL] |[NULL] |[NULL] |[NULL] |YES |NO |[NULL] |
Same if I query the PG Catalog
SELECT *
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'contact'
oid |relname|relnamespace|reltype|reloftype|relowner|relam|relfilenode|reltablespace|relpages|reltuples|relallvisible|reltoastrelid|relhasindex|relisshared|relpersistence|relkind|relnatts|relchecks|relhasrules|relhastriggers|relhassubclass|relrowsecurity|relforcerowsecurity|relispopulated|relreplident|relispartition|relrewrite|relfrozenxid|relminmxid|relacl |reloptions|relpartbound|oid |nspname|nspowner|nspacl |
-----+-------+------------+-------+---------+--------+-----+-----------+-------------+--------+---------+-------------+-------------+-----------+-----------+--------------+-------+--------+---------+-----------+--------------+--------------+--------------+-------------------+--------------+------------+--------------+----------+------------+----------+----------------------------------------------------------------------------------------------------------+----------+------------+-----+-------+--------+-----------------------------------------------------------------------+
17181|contact| 16743| 17183| 0| 10| 2| 17181| 0| 1| 26.0| 1| 17187|true |false |p |r | 21| 0|false |true |false |false |false |true |d |false | 0|1675 |1 |{postgres=arwdDxt/postgres,tildareadonly=r/postgres,tilda_app=arwdDxt/postgres,tilda_read_only=r/postgres}|NULL |[NULL] |16743|people | 10|{postgres=UC/postgres,tilda_app=UC/postgres,tilda_read_only=U/postgres}|
I understand this is a difficult scenario to replicate although I do have 2 copies of that database in two different environments as per the above and the issue exists in both places: looks like this issue survives a backup/restore. I am not sure if I am doing something wrong in my Java code, or if I found a bug in the JDBC Driver…
Thank you,
Laurent.
On Wednesday, September 8, 2021, ldh@laurent-hasson.com <ldh@laurent-hasson.com> wrote:
The only difference I saw was in the “remarks” for the two tables, with one of them saying "btree comparison function" which is strange.
This would seem to indicate that the catalog pg_description has two rows for this particular table and thus the join to it [1] causes the single pg_class entry to become duplicated.
Per the comment command page each object gets at most one comment so having multiples in the catalog is data corruption. If you confirm that you do indeed have duplicates hopefully issuing create comment on the problematic records clears up the issue.
David J.
> > > From: David G. Johnston <david.g.johnston@gmail.com> > Sent: Wednesday, September 8, 2021 15:53 > To: ldh@laurent-hasson.com > Cc: pgsql-jdbc@lists.postgresql.org > Subject: Re: Duplicate tables information through metadata queries > > On Wednesday, September 8, 2021, mailto:ldh@laurent-hasson.com <mailto:ldh@laurent-hasson.com> wrote: > > The only difference I saw was in the “remarks” for the two tables, with one of them saying "btree comparison function"which is strange. > > > This would seem to indicate that the catalog pg_description has two rows for this particular table and thus the join toit [1] causes the single pg_class entry to become duplicated. > > Per the comment command page each object gets at most one comment so having multiples in the catalog is data corruption. If you confirm that you do indeed have duplicates hopefully issuing create comment on the problematic recordsclears up the issue. > > David J. > > [1] https://github.com/pgjdbc/pgjdbc/blob/151b287732a551c380dcaa34f9c0549aeeb26208/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java#L1314 > > Hello David, I think this is it! SELECT * FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0) WHERE c.relname = 'contact' oid |relname|relnamespace|reltype|reloftype|relowner|relam|relfilenode|reltablespace|relpages|reltuples|relallvisible|reltoastrelid|relhasindex|relisshared|relpersistence|relkind|relnatts|relchecks|relhasrules|relhastriggers|relhassubclass|relrowsecurity|relforcerowsecurity|relispopulated|relreplident|relispartition|relrewrite|relfrozenxid|relminmxid|relacl |reloptions|relpartbound|oid |nspname|nspowner|nspacl |objoid|classoid|objsubid|description | -----+-------+------------+-------+---------+--------+-----+-----------+-------------+--------+---------+-------------+-------------+-----------+-----------+--------------+-------+--------+---------+-----------+--------------+--------------+--------------+-------------------+--------------+------------+--------------+----------+------------+----------+----------------------------------------------------------------------------------------------------------+----------+------------+-----+-------+--------+-----------------------------------------------------------------------+------+--------+--------+-------------------------+ 17181|contact| 16743| 17183| 0| 10| 2| 17181| 0| 1| 26.0| 1| 17187|true |false |p |r | 21| 0|false |true |false |false |false |true |d |false | 0|1675 |1 |{postgres=arwdDxt/postgres,tildareadonly=r/postgres,tilda_app=arwdDxt/postgres,tilda_read_only=r/postgres}|NULL |[NULL] |16743|people | 10|{postgres=UC/postgres,tilda_app=UC/postgres,tilda_read_only=U/postgres}| 17181| 1255| 0|btree comparison function| 17181|contact| 16743| 17183| 0| 10| 2| 17181| 0| 1| 26.0| 1| 17187|true |false |p |r | 21| 0|false |true |false |false |false |true |d |false | 0|1675 |1 |{postgres=arwdDxt/postgres,tildareadonly=r/postgres,tilda_app=arwdDxt/postgres,tilda_read_only=r/postgres}|NULL |[NULL] |16743|people | 10|{postgres=UC/postgres,tilda_app=UC/postgres,tilda_read_only=U/postgres}| 17181| 1259| 0|blah blah | I am not sure about your solution for cleanup however. Do you mean? COMMENT ON TABLE PEOPLE.Contact IS 'Blah'; This doesn't seem to have any effect except updating the comment for the second row above. Should I just delete the recordfrom the table for the tables affected? I know this is generally not good practice AT ALL... 😊 Also, you mention data corruption, but would that survive a backup/restore? Thank you, Laurent.
On Wednesday, September 8, 2021, ldh@laurent-hasson.com <ldh@laurent-hasson.com> wrote:
I am not sure about your solution for cleanup however. Do you mean?
COMMENT ON TABLE PEOPLE.Contact IS 'Blah';
Yes
This doesn't seem to have any effect except updating the comment for the second row above. Should I just delete the record from the table for the tables affected? I know this is generally not good practice AT ALL... 😊
Maybe reindex the table first, then do comment on? It might be worth posting this to -bugs and solicit suggestions on getting out the situation from a better targeted audience.
Also, you mention data corruption, but would that survive a backup/restore?
Both rows should be copied out on backup and copied back in during restore.
David J.
From: David G. Johnston <david.g.johnston@gmail.com> Sent: Wednesday, September 8, 2021 17:24 To: ldh@laurent-hasson.com Cc: pgsql-jdbc@lists.postgresql.org Subject: Re: Duplicate tables information through metadata queries On Wednesday, September 8, 2021, mailto:ldh@laurent-hasson.com <mailto:ldh@laurent-hasson.com> wrote: I am not sure about your solution for cleanup however. Do you mean? COMMENT ON TABLE PEOPLE.Contact IS 'Blah'; Yes This doesn't seem to have any effect except updating the comment for the second row above. Should I just delete the recordfrom the table for the tables affected? I know this is generally not good practice AT ALL... 😊 Maybe reindex the table first, then do comment on? It might be worth posting this to -bugs and solicit suggestions on gettingout the situation from a better targeted audience. Also, you mention data corruption, but would that survive a backup/restore? Both rows should be copied out on backup and copied back in during restore. David J. ------------------------------------------------------------------------------------------------------------------------------------------ Hello David, VACUUM FULL FREEZE ANALYZE PEOPLE.contact; No change. What do you mean by "posting this to -bugs"? Thank you, Laurent.
On Wednesday, September 8, 2021, ldh@laurent-hasson.com <ldh@laurent-hasson.com> wrote:
VACUUM FULL FREEZE ANALYZE PEOPLE.contact;
No change.
That is not surprising. I’m lretty sure that also doesn’t cause indexes to be rebuilt.
What do you mean by "posting this to -bugs"?
This conversation is taking place on the -jdbc mailing list but it is off-topic now that the root problem has been discovered. The -bugs mailing list (possibly via the online form) is a better place to discuss this.
David J.
On Wednesday, September 8, 2021, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, September 8, 2021, ldh@laurent-hasson.com <ldh@laurent-hasson.com> wrote:
VACUUM FULL FREEZE ANALYZE PEOPLE.contact;
No change.That is not surprising. I’m lretty sure that also doesn’t cause indexes to be rebuilt.
Sorry, by “the table” I meant pg_description, i.e. the one with the bad data.
David J.
> > From: David G. Johnston <david.g.johnston@gmail.com> > Sent: Wednesday, September 8, 2021 17:44 > To: ldh@laurent-hasson.com > Cc: pgsql-jdbc@lists.postgresql.org > Subject: Re: Duplicate tables information through metadata queries > > On Wednesday, September 8, 2021, David G. Johnston <mailto:david.g.johnston@gmail.com> wrote: > On Wednesday, September 8, 2021, mailto:ldh@laurent-hasson.com <mailto:ldh@laurent-hasson.com> wrote: > > VACUUM FULL FREEZE ANALYZE PEOPLE.contact; > > No change. > > That is not surprising. I’m lretty sure that also doesn’t cause indexes to be rebuilt. > > > Sorry, by “the table” I meant pg_description, i.e. the one with the bad data. > > David J. > Hello David, I figured out what you meant with "-bugs" and posted there a few minutes ago. Thank you for the pointer. Vacuum Full doesn't rebuild indices??? I always thought it did as per the docs on https://www.postgresql.org/docs/13/routine-vacuuming.html. I did try a reindex anyways on my table and that didn't do anything. I also tried a reindex/vacuum on the PG_CATALOG.pg_descriptiontable but that didn't work: I get a lock timeout in both cases. I don't know if that's possible. In any case, thank you for your help... I have posted to the "bugs" mailing list and hope to get some help there. Thank you, Laurent.
On 9/8/21 5:15 PM, ldh@laurent-hasson.com wrote: > > SELECT * > FROM pg_catalog.pg_class c > LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace > LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0) > WHERE c.relname = 'contact' Umm, that doesn't look right. For queries against pg_description you need to specify the classoid (in this case 'pg_class'::regclass) as well as the objoid (and possibly the objsubid). Remember, Oids are not unique across the whole catalog. I looks to me like here one rwo is picking up a description for an entry in some other catalog See https://www.postgresql.org/docs/devel/catalog-pg-description.html cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
On Wednesday, September 8, 2021, ldh@laurent-hasson.com <ldh@laurent-hasson.com> wrote:
Vacuum Full doesn't rebuild indices??? I always thought it did as per the docs on https://www.postgresql.org/docs/13/routine-vacuuming.html .
Yeah, it has to rebuild the indexes since the physical locations of everything change during a vacuum full. But doing that on the user table will not help - the oid stays the same and that is the only link (an indirect one at that) to the problem comment data. Doing it on the catalog might help…never tried myself, vacuum full or a independent reindex.
David J.
On Wednesday, September 8, 2021, Andrew Dunstan <andrew@dunslane.net> wrote:
On 9/8/21 5:15 PM, ldh@laurent-hasson.com wrote:
>
> SELECT *
> FROM pg_catalog.pg_class c
> LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)
> WHERE c.relname = 'contact'
Umm, that doesn't look right. For queries against pg_description you
need to specify the classoid (in this case 'pg_class'::regclass) as well
as the objoid (and possibly the objsubid). Remember, Oids are not unique
across the whole catalog. I looks to me like here one rwo is picking up
a description for an entry in some other catalog
See https://www.postgresql.org/docs/devel/catalog-pg-description .html
Doh! I knew I was forgetting something. This is indeed a bug in the JDBC driver. In the query results a few messages above one is in catalog 1255 and the other (correct one) is in 1259.
David J.
On Wed, 8 Sept 2021 at 18:19, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, September 8, 2021, Andrew Dunstan <andrew@dunslane.net> wrote:
On 9/8/21 5:15 PM, ldh@laurent-hasson.com wrote:
>
> SELECT *
> FROM pg_catalog.pg_class c
> LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)
> WHERE c.relname = 'contact'
Umm, that doesn't look right. For queries against pg_description you
need to specify the classoid (in this case 'pg_class'::regclass) as well
as the objoid (and possibly the objsubid). Remember, Oids are not unique
across the whole catalog. I looks to me like here one rwo is picking up
a description for an entry in some other catalog
See https://www.postgresql.org/docs/devel/catalog-pg-description.htmlDoh! I knew I was forgetting something. This is indeed a bug in the JDBC driver. In the query results a few messages above one is in catalog 1255 and the other (correct one) is in 1259.
The next line in the driver is
| + " LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class') " |
Dave
David J.
On Wednesday, September 8, 2021, Dave Cramer <davecramer@postgres.rocks> wrote:
On Wed, 8 Sept 2021 at 18:19, David G. Johnston <david.g.johnston@gmail.com> wrote:On Wednesday, September 8, 2021, Andrew Dunstan <andrew@dunslane.net> wrote:
On 9/8/21 5:15 PM, ldh@laurent-hasson.com wrote:
>
> SELECT *
> FROM pg_catalog.pg_class c
> LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)
> WHERE c.relname = 'contact'
Umm, that doesn't look right. For queries against pg_description you
need to specify the classoid (in this case 'pg_class'::regclass) as well
as the objoid (and possibly the objsubid). Remember, Oids are not unique
across the whole catalog. I looks to me like here one rwo is picking up
a description for an entry in some other catalog
See https://www.postgresql.org/docs/devel/catalog-pg- description.html Doh! I knew I was forgetting something. This is indeed a bug in the JDBC driver. In the query results a few messages above one is in catalog 1255 and the other (correct one) is in 1259.The next line in the driver is
+ " LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class') "
I cannot test right now but the observed behavior indicates that isn’t sufficient; and I would tend to agree since the left join isn’t going to cause rows already selected on the outer side of the join to be removed - it will just fill in additional details for the records that do match.
David J.
On 9/8/21 6:45 PM, Dave Cramer wrote: > > > On Wed, 8 Sept 2021 at 18:19, David G. Johnston > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote: > > On Wednesday, September 8, 2021, Andrew Dunstan > <andrew@dunslane.net <mailto:andrew@dunslane.net>> wrote: > > > On 9/8/21 5:15 PM, ldh@laurent-hasson.com > <mailto:ldh@laurent-hasson.com> wrote: > > > > SELECT * > > FROM pg_catalog.pg_class c > > LEFT JOIN pg_catalog.pg_namespace n ON n.oid = > c.relnamespace > > LEFT JOIN pg_catalog.pg_description d ON (c.oid = > d.objoid AND d.objsubid = 0) > > WHERE c.relname = 'contact' > > > > Umm, that doesn't look right. For queries against > pg_description you > need to specify the classoid (in this case > 'pg_class'::regclass) as well > as the objoid (and possibly the objsubid). Remember, Oids are > not unique > across the whole catalog. I looks to me like here one rwo is > picking up > a description for an entry in some other catalog > > > See > https://www.postgresql.org/docs/devel/catalog-pg-description.html > <https://www.postgresql.org/docs/devel/catalog-pg-description.html> > > > Doh! I knew I was forgetting something. This is indeed a bug in > the JDBC driver. In the query results a few messages above one is > in catalog 1255 and the other (correct one) is in 1259. > > > The next line in the driver is > > + " LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND > dc.relname='pg_class') " > > > > "LEFT" here surely defeats the purpose. Far better than to have this clause at all would be to add " and d.classoid = 'pg_class'::regclass" to the previous join condition. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
On Wed, 8 Sept 2021 at 20:01, Andrew Dunstan <andrew@dunslane.net> wrote:
On 9/8/21 6:45 PM, Dave Cramer wrote:
>
>
> On Wed, 8 Sept 2021 at 18:19, David G. Johnston
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>
> On Wednesday, September 8, 2021, Andrew Dunstan
> <andrew@dunslane.net <mailto:andrew@dunslane.net>> wrote:
>
>
> On 9/8/21 5:15 PM, ldh@laurent-hasson.com
> <mailto:ldh@laurent-hasson.com> wrote:
> >
> > SELECT *
> > FROM pg_catalog.pg_class c
> > LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
> c.relnamespace
> > LEFT JOIN pg_catalog.pg_description d ON (c.oid =
> d.objoid AND d.objsubid = 0)
> > WHERE c.relname = 'contact'
>
>
>
> Umm, that doesn't look right. For queries against
> pg_description you
> need to specify the classoid (in this case
> 'pg_class'::regclass) as well
> as the objoid (and possibly the objsubid). Remember, Oids are
> not unique
> across the whole catalog. I looks to me like here one rwo is
> picking up
> a description for an entry in some other catalog
>
>
> See
> https://www.postgresql.org/docs/devel/catalog-pg-description.html
> <https://www.postgresql.org/docs/devel/catalog-pg-description.html>
>
>
> Doh! I knew I was forgetting something. This is indeed a bug in
> the JDBC driver. In the query results a few messages above one is
> in catalog 1255 and the other (correct one) is in 1259.
>
>
> The next line in the driver is
>
> + " LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND
> dc.relname='pg_class') "
>
>
>
>
"LEFT" here surely defeats the purpose. Far better than to have this
clause at all would be to add " and d.classoid = 'pg_class'::regclass"
to the previous join condition.
Thanks!
I'd like to add a test case that would break otherwise,
Dave
On Wednesday, September 8, 2021, Dave Cramer <davecramer@postgres.rocks> wrote:
I'd like to add a test case that would break otherwise,
Seems like munging OIDs on system tables would be outside what the test environment would be reasonably capable of doing, though I’m not familiar with whether you can mock the server and stub out a resultset response to the query that would contain multiple records. The error mode is not returning exactly one record. Testing for that at runtime is simple, but what is a good response if that unlikely event happens?
I don’t know if it is even possible for a stock install to have two OIDs globally duplicated - though it isn’t hard to check for on a given server. Creating thousands of tables, or types, or whatnot on said server until a global duplicate appears would be fairly straight-forward, if potentially time and, to a lesser extent (drop table et al.) space consuming. Probably worth doing for adhoc testing but less so in a unit test suite.
David J.
> > From: David G. Johnston <david.g.johnston@gmail.com> > Sent: Wednesday, September 8, 2021 20:54 > To: Dave Cramer <davecramer@postgres.rocks> > Cc: Andrew Dunstan <andrew@dunslane.net>; ldh@laurent-hasson.com; pgsql-jdbc@lists.postgresql.org > Subject: Re: Duplicate tables information through metadata queries > > On Wednesday, September 8, 2021, Dave Cramer <mailto:davecramer@postgres.rocks> wrote: > https://github.com/pgjdbc/pgjdbc/pull/2245 > > I'd like to add a test case that would break otherwise, > > Seems like munging OIDs on system tables would be outside what the test environment would be reasonably capable of doing,though I’m not familiar with whether you can mock the server and stub out a resultset response to the query that wouldcontain multiple records. The error mode is not returning exactly one record. Testing for that at runtime is simple,but what is a good response if that unlikely event happens? > > I don’t know if it is even possible for a stock install to have two OIDs globally duplicated - though it isn’t hard tocheck for on a given server. Creating thousands of tables, or types, or whatnot on said server until a global duplicateappears would be fairly straight-forward, if potentially time and, to a lesser extent (drop table et al.) spaceconsuming. Probably worth doing for adhoc testing but less so in a unit test suite. > > David J. Hello David, Andrew, We certainly have a lot of migrations and "vacuum full" over the years for example on our many db instances. And we havelots of entities in the db as per the logs I shared (thousands including columns, tables, views etc...). Is there somethingI could help with given what I have on my local dev environment? Maybe a limited schema-only backup that would allowto replicate the issue somewhere else or are you good? I can certainly look at a debug version of the driver (if I canget access to the JAR somewhere) and could test it. I am not able to make a build from github based on what I saw forhttps://github.com/pgjdbc/pgjdbc/pull/2245, which looks like the right fix as per the thread. Thank you, Laurent.
On 9/8/21 10:56 PM, ldh@laurent-hasson.com wrote: >> >> From: David G. Johnston <david.g.johnston@gmail.com> >> Sent: Wednesday, September 8, 2021 20:54 >> To: Dave Cramer <davecramer@postgres.rocks> >> Cc: Andrew Dunstan <andrew@dunslane.net>; ldh@laurent-hasson.com; pgsql-jdbc@lists.postgresql.org >> Subject: Re: Duplicate tables information through metadata queries >> >> On Wednesday, September 8, 2021, Dave Cramer <mailto:davecramer@postgres.rocks> wrote: >> https://github.com/pgjdbc/pgjdbc/pull/2245 >> >> I'd like to add a test case that would break otherwise, >> >> Seems like munging OIDs on system tables would be outside what the test environment would be reasonably capable of doing,though I’m not familiar with whether you can mock the server and stub out a resultset response to the query that wouldcontain multiple records. The error mode is not returning exactly one record. Testing for that at runtime is simple,but what is a good response if that unlikely event happens? >> >> I don’t know if it is even possible for a stock install to have two OIDs globally duplicated - though it isn’t hard tocheck for on a given server. Creating thousands of tables, or types, or whatnot on said server until a global duplicateappears would be fairly straight-forward, if potentially time and, to a lesser extent (drop table et al.) spaceconsuming. Probably worth doing for adhoc testing but less so in a unit test suite. >> >> David J. > > Hello David, Andrew, > > We certainly have a lot of migrations and "vacuum full" over the years for example on our many db instances. And we havelots of entities in the db as per the logs I shared (thousands including columns, tables, views etc...). Is there somethingI could help with given what I have on my local dev environment? Maybe a limited schema-only backup that would allowto replicate the issue somewhere else or are you good? I can certainly look at a debug version of the driver (if I canget access to the JAR somewhere) and could test it. I am not able to make a build from github based on what I saw forhttps://github.com/pgjdbc/pgjdbc/pull/2245, which looks like the right fix as per the thread. > David is right about how to recreate conditions for this error. I don't think hacking the catalog to produce the error condition is necessarily out of the question in unit tests - it should be fairly straightforward, and the database will be quite ephemeral. I don't think there's anything that Dave should need from you. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
On Thu, 9 Sept 2021 at 09:02, Andrew Dunstan <andrew@dunslane.net> wrote:
On 9/8/21 10:56 PM, ldh@laurent-hasson.com wrote:
>>
>> From: David G. Johnston <david.g.johnston@gmail.com>
>> Sent: Wednesday, September 8, 2021 20:54
>> To: Dave Cramer <davecramer@postgres.rocks>
>> Cc: Andrew Dunstan <andrew@dunslane.net>; ldh@laurent-hasson.com; pgsql-jdbc@lists.postgresql.org
>> Subject: Re: Duplicate tables information through metadata queries
>>
>> On Wednesday, September 8, 2021, Dave Cramer <mailto:davecramer@postgres.rocks> wrote:
>> https://github.com/pgjdbc/pgjdbc/pull/2245
>>
>> I'd like to add a test case that would break otherwise,
>>
>> Seems like munging OIDs on system tables would be outside what the test environment would be reasonably capable of doing, though I’m not familiar with whether you can mock the server and stub out a resultset response to the query that would contain multiple records. The error mode is not returning exactly one record. Testing for that at runtime is simple, but what is a good response if that unlikely event happens?
>>
>> I don’t know if it is even possible for a stock install to have two OIDs globally duplicated - though it isn’t hard to check for on a given server. Creating thousands of tables, or types, or whatnot on said server until a global duplicate appears would be fairly straight-forward, if potentially time and, to a lesser extent (drop table et al.) space consuming. Probably worth doing for adhoc testing but less so in a unit test suite.
>>
>> David J.
>
> Hello David, Andrew,
>
> We certainly have a lot of migrations and "vacuum full" over the years for example on our many db instances. And we have lots of entities in the db as per the logs I shared (thousands including columns, tables, views etc...). Is there something I could help with given what I have on my local dev environment? Maybe a limited schema-only backup that would allow to replicate the issue somewhere else or are you good? I can certainly look at a debug version of the driver (if I can get access to the JAR somewhere) and could test it. I am not able to make a build from github based on what I saw for https://github.com/pgjdbc/pgjdbc/pull/2245, which looks like the right fix as per the thread.
>
David is right about how to recreate conditions for this error. I don't
think hacking the catalog to produce the error condition is necessarily
out of the question in unit tests - it should be fairly straightforward,
and the database will be quite ephemeral.
I'm curious how that could be done. We can take this private if you wish
Dave
> -----Original Message----- > From: Andrew Dunstan <andrew@dunslane.net> > Sent: Thursday, September 9, 2021 09:02 > To: ldh@laurent-hasson.com; David G. Johnston > <david.g.johnston@gmail.com>; Dave Cramer > <davecramer@postgres.rocks> > Cc: pgsql-jdbc@lists.postgresql.org > Subject: Re: Duplicate tables information through metadata queries > > > On 9/8/21 10:56 PM, ldh@laurent-hasson.com wrote: > >> > >> From: David G. Johnston <david.g.johnston@gmail.com> > >> Sent: Wednesday, September 8, 2021 20:54 > >> To: Dave Cramer <davecramer@postgres.rocks> > >> Cc: Andrew Dunstan <andrew@dunslane.net>; ldh@laurent- > hasson.com; > >> pgsql-jdbc@lists.postgresql.org > >> Subject: Re: Duplicate tables information through metadata queries > >> > >> On Wednesday, September 8, 2021, Dave Cramer > <mailto:davecramer@postgres.rocks> wrote: > >> https://github.com/pgjdbc/pgjdbc/pull/2245 > >> > >> I'd like to add a test case that would break otherwise, > >> > >> Seems like munging OIDs on system tables would be outside what the > test environment would be reasonably capable of doing, though I’m not > familiar with whether you can mock the server and stub out a resultset > response to the query that would contain multiple records. The error > mode is not returning exactly one record. Testing for that at runtime is > simple, but what is a good response if that unlikely event happens? > >> > >> I don’t know if it is even possible for a stock install to have two OIDs > globally duplicated - though it isn’t hard to check for on a given > server. Creating thousands of tables, or types, or whatnot on said server > until a global duplicate appears would be fairly straight-forward, if > potentially time and, to a lesser extent (drop table et al.) space > consuming. Probably worth doing for adhoc testing but less so in a unit > test suite. > >> > >> David J. > > > > Hello David, Andrew, > > > > We certainly have a lot of migrations and "vacuum full" over the years > for example on our many db instances. And we have lots of entities in > the db as per the logs I shared (thousands including columns, tables, > views etc...). Is there something I could help with given what I have on > my local dev environment? Maybe a limited schema-only backup that > would allow to replicate the issue somewhere else or are you good? I can > certainly look at a debug version of the driver (if I can get access to the > JAR somewhere) and could test it. I am not able to make a build from > github based on what I saw for > https://github.com/pgjdbc/pgjdbc/pull/2245, which looks like the right > fix as per the thread. > > > > David is right about how to recreate conditions for this error. I don't > think hacking the catalog to produce the error condition is necessarily > out of the question in unit tests - it should be fairly straightforward, and > the database will be quite ephemeral. > > > I don't think there's anything that Dave should need from you. > > > cheers > > > andrew > > > -- > Andrew Dunstan > EDB: https://www.enterprisedb.com Thank you! Is this something you think will be ready for the next version of the driver? Any ETA on .24? Thank you, Laurent.
> -----Original Message-----
> From: Andrew Dunstan <andrew@dunslane.net>
> Sent: Thursday, September 9, 2021 09:02
> To: ldh@laurent-hasson.com; David G. Johnston
> <david.g.johnston@gmail.com>; Dave Cramer
> <davecramer@postgres.rocks>
> Cc: pgsql-jdbc@lists.postgresql.org
> Subject: Re: Duplicate tables information through metadata queries
>
>
> On 9/8/21 10:56 PM, ldh@laurent-hasson.com wrote:
> >>
> >> From: David G. Johnston <david.g.johnston@gmail.com>
> >> Sent: Wednesday, September 8, 2021 20:54
> >> To: Dave Cramer <davecramer@postgres.rocks>
> >> Cc: Andrew Dunstan <andrew@dunslane.net>; ldh@laurent-
> hasson.com;
> >> pgsql-jdbc@lists.postgresql.org
> >> Subject: Re: Duplicate tables information through metadata queries
> >>
> >> On Wednesday, September 8, 2021, Dave Cramer
> <mailto:davecramer@postgres.rocks> wrote:
> >> https://github.com/pgjdbc/pgjdbc/pull/2245
> >>
> >> I'd like to add a test case that would break otherwise,
> >>
> >> Seems like munging OIDs on system tables would be outside what the
> test environment would be reasonably capable of doing, though I’m not
> familiar with whether you can mock the server and stub out a resultset
> response to the query that would contain multiple records. The error
> mode is not returning exactly one record. Testing for that at runtime is
> simple, but what is a good response if that unlikely event happens?
> >>
> >> I don’t know if it is even possible for a stock install to have two OIDs
> globally duplicated - though it isn’t hard to check for on a given
> server. Creating thousands of tables, or types, or whatnot on said server
> until a global duplicate appears would be fairly straight-forward, if
> potentially time and, to a lesser extent (drop table et al.) space
> consuming. Probably worth doing for adhoc testing but less so in a unit
> test suite.
> >>
> >> David J.
> >
> > Hello David, Andrew,
> >
> > We certainly have a lot of migrations and "vacuum full" over the years
> for example on our many db instances. And we have lots of entities in
> the db as per the logs I shared (thousands including columns, tables,
> views etc...). Is there something I could help with given what I have on
> my local dev environment? Maybe a limited schema-only backup that
> would allow to replicate the issue somewhere else or are you good? I can
> certainly look at a debug version of the driver (if I can get access to the
> JAR somewhere) and could test it. I am not able to make a build from
> github based on what I saw for
> https://github.com/pgjdbc/pgjdbc/pull/2245, which looks like the right
> fix as per the thread.
> >
>
> David is right about how to recreate conditions for this error. I don't
> think hacking the catalog to produce the error condition is necessarily
> out of the question in unit tests - it should be fairly straightforward, and
> the database will be quite ephemeral.
>
>
> I don't think there's anything that Dave should need from you.
>
>
> cheers
>
>
> andrew
>
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
Thank you!
Is this something you think will be ready for the next version of the driver? Any ETA on .24?
Yes, I'll push it shortly and backpatch it. After I push it you should be able to get a snapshot to test.
Thanks
Dave
Thank you,
Laurent.