Farooq Ayoade created FINERACT-2468:
---------------------------------------
Summary: Datatable not found / GET /datatables 404 on PostgreSQL
with non-public schema
Key: FINERACT-2468
URL: https://issues.apache.org/jira/browse/FINERACT-2468
Project: Apache Fineract
Issue Type: Bug
Components: Data Tables
Reporter: Farooq Ayoade
On PostgreSQL when using a *non-{{{}public{}}} schema* (e.g. tenant-specific
schema), creating a new datatable causes:
* *GET /datatables* to return *404* with "Datatable not found" for the new
datatable and break the entire list response
* Saving data to the new datatable to fail with "Datatable not found"
* Recovery only possible by deleting the problematic datatable
h2. Steps to reproduce
# Configure Fineract with PostgreSQL and a tenant that uses a
*non-{{{}public{}}} schema* (e.g. schema name = tenant identifier).
# Create a new datatable via POST /datatables
# Call *GET /datatables* (or GET /datatables/ with no filter)
h2. Root cause
In {*}{{fineract-core}}{*}, class {*}{{PostgreSQLQueryService}}{*}:
* *{{getTableColumns()}}* correctly uses *{{current_schema}}* (schema of the
current connection), so the table is found when it lives in the tenant schema.
* *{{getTableIndexes()}}* and *{{isTablePresent()}}* hardcode *{{'public'}}*
for the schema.
When listing datatables, the flow is:
# {{DatatableReadServiceImpl.retrieveDatatableNames()}} loads all permitted
datatables from {{{}x_registered_table{}}}.
# For each datatable it calls
{{{}GenericDataServiceImpl.fillResultsetColumnHeaders(datatableName){}}}.
# {{fillResultsetColumnHeaders()}} calls:
** {{getTableMetaData(tableName)}} → uses {{getTableColumns()}} → *finds the
table* (current_schema).
** {{getDatatableIndexData(tableName)}} → uses {{getTableIndexes()}} → looks
only in *{{public}}* → finds no rows → throws {{IllegalArgumentException("Table
... is not found")}} → mapped to {*}DatatableNotFoundException{*}.
So the table exists in the tenant schema, but the index query looks in
{{public}} and fails, breaking GET /datatables and any operation that needs
index metadata for that datatable.
*Stack trace (relevant part):*
!http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
{{org.apache.fineract.infrastructure.dataqueries.exception.DatatableNotFoundException:
Datatable not found.
at
org.apache.fineract.infrastructure.dataqueries.service.GenericDataServiceImpl.getDatatableIndexData(GenericDataServiceImpl.java:299)
at
org.apache.fineract.infrastructure.dataqueries.service.GenericDataServiceImpl.fillResultsetColumnHeaders(GenericDataServiceImpl.java:98)
at
org.apache.fineract.infrastructure.dataqueries.service.DatatableReadServiceImpl.retrieveDatatableNames(DatatableReadServiceImpl.java:93)
at
org.apache.fineract.infrastructure.dataqueries.api.DatatablesApiResource.getDatatables(DatatablesApiResource.java:92)}}
h2. Fix
*File:*
{{fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/PostgreSQLQueryService.java}}
Use *{{current_schema}}* instead of *{{'public'}}* so behavior is consistent
with {{getTableColumns()}} and works with tenant/custom schemas.
h3. 1. {{getTableIndexes}}
*Before:*
!http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
{{String sql = "SELECT indexname FROM pg_indexes WHERE schemaname = 'public'
AND tablename = ?";}}
*After:*
!http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
{{// Use current_schema so tenant/schema-specific tables (e.g. datatables) are
found;
// hardcoding 'public' breaks GET /datatables and datatable operations in
multi-tenant setups.
String sql = "SELECT indexname FROM pg_indexes WHERE schemaname =
current_schema AND tablename = ?";}}
h3. 2. {{isTablePresent}}
*Before:*
!http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
{{Integer result = jdbcTemplate.queryForObject(
"SELECT COUNT(table_name) FROM information_schema.tables " + "WHERE
table_schema = 'public' AND table_name = ?",
Integer.class, tableName);}}
*After:*
!http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
{{// Use current_schema for consistency with getTableColumns/getTableIndexes
(tenant/schema-aware).
Integer result = jdbcTemplate.queryForObject(
"SELECT COUNT(table_name) FROM information_schema.tables WHERE
table_schema = current_schema AND table_name = ?",
Integer.class, tableName);}}
h2. Verification
* With PostgreSQL and a non-{{{}public{}}} schema (e.g. tenant schema):
** Create a new datatable.
** GET /datatables returns 200 and includes the new datatable.
** Saving data to the new datatable succeeds.
* With single-tenant {{public}} schema, behaviour unchanged
({{{}current_schema{}}} is {{{}public{}}}).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
