[
https://issues.apache.org/jira/browse/FINERACT-2468?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18056831#comment-18056831
]
Ashhar Ahmad Khan commented on FINERACT-2468:
---------------------------------------------
PR submitted: https://github.com/apache/fineract/pull/5452
This PR fixes the datatable operations in PostgreSQL non-public schemas by
using
current_schema instead of hardcoded 'public' in getTableIndexes() and
isTablePresent().
Changes:
- PostgreSQLQueryService.java: 2 SQL queries updated
- All unit tests pass (93/93)
- Manual PostgreSQL verification confirms fix
Ready for review.
> 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
> Priority: Major
>
> 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):*
>
> {{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)
