[BUDI-7485] PostgreSQL - Column data will not be returned if the combination of the table and column names exceeds 63 characters.

[melohagan]

Checklist

• [x] I have searched budibase discussions and github issues to check if my issue already exists

Hosting

• Self
  • Method: docker compose
  • Budibase Version: 2.10.6

Describe the bug This may be related to an identifier limit on Postgres: https://stackoverflow.com/questions/27865770/how-long-can-postgresql-table-names-be

However that may be a coincidence, or be part of a more nuanced issue. (See additional context below)

To Reproduce Steps to reproduce the behavior:

1. Create a Postgres table where table name + column name combined is more than 63 characters long
2. Add a Postgres datasource and fetch the table in Budibase
3. See that the value is not returned for the column name that exceeds the combined max length
4. Alter the Postgres SQL schema to shorten the table or column name so that the total length is < 64 characters
5. Refetch the tables in Budibase
6. See that the values for that column are now returned

Expected behavior Budibase should work with my existing data model and support the same limits and schema as Postgres and the rest of my tooling does

Additional context

The query works [with table name + column name > 63 characters] outside of Budibase, even if you write the query in such a way as to make it use a fully qualified column name.

The 63 char ID limit ... is on identifiers (which is a table name, index name, etc.), not a column name (which has it's own limit of 59 chars), and not a combination of a table name and column name a query will also warn you when you exceed the limit, and give you a truncated version of the ID, both on create and at query time, and has no bearing on the functioning of the query

_BUDI-7485

[mike12345567]

We actually discovered this only yesterday - we were writing tests that used UUIDs for both the column and the table name, which when combined means that all kinds of weird issues start to occur @adrinr looping in just for awareness.

We would need to make it so that the table name itself isn't directly used, but an alias of some kind, e.g. table TableWithAReallyLongNameThatWouldCauseProblems would be aliased to just a, then we could use a lookup map to work out what tables they were supposed to pertain to.

This would help with query length overall, improve readability and logging and overall be quite a nice improvement, as it would mean that the column name could be up to 61 characters long before issues would occur (only adding say a. - a single alpha-numeric mapping would allow a few hundred tables mapped if we used all the characters in the ASCII range).

[mike12345567]

Fixed with SQL aliasing.