Postgresql and schema

[jeffjensen]

Migrated from SourceForge — Bugs #248
Originally reported by cwo888 on 2009-06-11 15:54:33
Priority: 5
Assigned to (unassigned)
SF status: open

I have different schemas on Postgresql 8.3 using the latest dbunit-2.4.5. I cannot delete table entries by DatabaseOperation.DELETE_ALL.execute(...)

IDatabaseConnection contains the schema (in this case BI, but it does taken into account)

The problem is:
In the DatabaseOperation.DELETE_ALL

a) I put in a table with name: schema.tablename: BI.APP_ROLE_ALL_CHILDREN
-> org.dbunit.dataset.NoSuchTableException: BI.APP_ROLE_ALL_CHILDREN
-> Table 'BI.APP_ROLE_ALL_CHILDREN' not found in tableMap==org.dbunit.dataset.OrderedTableNameMap[_tableNames=[app_role_all_children, ...]

So the check does not take the schema into account.

b) I put in a table without a schema: tablename: APP_ROLE_ALL_CHILDREN
-> postgres likes to execute this sql: select * from APP_ROLE_ALL_CHILDREN without the schema which throws an:
org.postgresql.util.PSQLException: Error Relation »app_role_all_children« does not exist

Which is right as well, because the sql should be: select * from BI.APP_ROLE_ALL_CHILDREN

So both ways don't work. I expected a behavior that takes the given schema in the IDatabaseConnection into account so that I can put in the (same) schema or leave it away. None of these both cases above should generate an exception.

---

gommma — 2009-06-11 22:02:00
Did you try to set the feature DatabaseConfig.FEATURE_CASE_SENSITIVE_TABLE_NAMES and/or DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES? Your first case sounds as if the case is not correct.

cwo888 — 2009-06-12 08:07:45
I checked with lower case as well, but it did not work. The table is found if I remove the schema from the table name even if the case is toUpper. I'll have to check if DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES will help.

gommma — 2009-09-23 16:05:51
Hi cwo,
could you please let me know whether your problem is solved?
thanks,
matthias

[jeffjensen]

Revisited this against current main (3.4.1-SNAPSHOT).

Case (a) - schema-qualified name (BI.APP_ROLE_ALL_CHILDREN): matthias's 2009 suggestion was right - this needs DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES=true. Without it, the internal table map only holds unqualified names, so a qualified lookup always throws NoSuchTableException - expected, not a bug.

With the feature enabled, this case did still have a real defect until recently: DatabaseDataSet queried DatabaseMetaData#getTables() for the schema exactly as given, case-sensitively, regardless of dbUnit's own case-insensitivity setting. PostgreSQL folds unquoted identifiers to lower case, so BI vs. a live bi schema returned zero tables and the same NoSuchTableException. Fixed in #656 (case-insensitive schema resolution in DatabaseDataSet), with a live-PostgreSQL regression test (PostgresqlUppercaseSchemaIT) reproducing this exact scenario.

Case (b) - unqualified name (APP_ROLE_ALL_CHILDREN) relying on the connection's own schema: not reproducible on current main. DeleteAllOperation (and the other operations) always qualify the generated SQL with the IDatabaseConnection's configured schema, independent of FEATURE_QUALIFIED_TABLE_NAMES. Added PostgresqlNonPublicSchemaDeleteAllIT, which runs DELETE_ALL with an unqualified name against a connection scoped to a non-public schema - passes on current main, and I confirmed it reproduces your exact "relation ... does not exist" error when the connection's schema is left unset, so the test isn't passing vacuously.

Both cases work today given a properly-schema'd IDatabaseConnection and, for schema-qualified names, FEATURE_QUALIFIED_TABLE_NAMES=true. Closing as resolved if there's no further activity.