SQLRDD++: index keys with Harbour functions now use native PostgreSQL expression indexes (no more INDKEY_ columns)
18 views
Skip to first unread message
omarco...@gmail.com
3:03 PM (9 hours ago) 3:03 PM
to Harbour Users
Hi all,
I'd like to share an improvement that has just been merged into SQLRDD++ (the open source fork of SQLRDD maintained on GitHub), solving an old pain point for those using the library with PostgreSQL: synthetic indexes.
The problem
SQLRDD users know this well: when you create an index with a function in the key, like
INDEX ON UPPER(NAME) TAG cli1 TO customers
INDEX ON STRZERO(ID,10) TAG cli2 TO customers
INDEX ON DTOS(HIREDATE) + STRZERO(ID,10) TAG cli3 TO customers
the library used to add an extra column to the table (INDKEY_001, INDKEY_002...) to store the computed key value, and indexed that column instead. This had several side effects:
Tables got "polluted" with columns that don't belong to your data model;
Index creation scanned the whole table doing one UPDATE per row (very slow on large tables);
Every INSERT/UPDATE had to recompute and rewrite those columns — a permanent write overhead;
If the table was modified from outside the application (pgAdmin, scripts, another system), the index silently went out of sync;
Worse: even indexes with no functions at all, just more than 3 fields (INDEX ON ID+NAME+CITY), fell under the same rule and got an extra column.
What changed
On PostgreSQL, index keys built with standard Harbour functions now become native expression indexes. INDEX ON UPPER(NAME) generates something like this directly in PostgreSQL:
CREATE INDEX ... ON customers ((rtrim(upper(coalesce("name",'')))), "sr_recno")
No extra column, instant index creation (no table scan), and the database maintains the index by itself — INSERTs/UPDATEs pay no extra cost, and external changes can no longer desynchronize anything. SEEK (including partial/soft seek), SKIP, SET SCOPE, DBEDIT — everything behaves exactly like a DBF.
Functions translated in this first version: UPPER, SUBSTR, LEFT, STRZERO (with and without decimals) — plus STR and DTOS, which were already handled. Multi-column indexes no longer become synthetic either, regardless of the number of fields.
What about user-defined functions?
If the key uses your own function (INDEX ON MYFUNC(NAME)), the classic synthetic column mechanism still applies — the database has no way to evaluate a function that only exists inside your executable. In other words: the extra column is now created only when there is truly no alternative.
Compatibility (important!)
Existing indexes keep working with the new library, no reindexing required;
Conversion is automatic and incremental: whenever your reindex routine recreates an index, its INDKEY_ column is dropped and the expression index takes its place;
Caveat: executables built with the OLD library cannot open indexes that have already been converted. Rule of thumb: update all executables that access the database BEFORE reindexing;
If you need the old behavior for any reason: SR_SetExpressionIndex(.F.).
How to try it
The changes are already merged into main. In tests/postgresql there is a new program, testexprindex.prg, which creates and populates a table, builds indexes with UPPER/SUBSTR/STRZERO/DTOS plus one with a UDF, shows via information_schema that only the UDF index created an extra column, runs SEEK batteries and opens DBEDIT so you can browse each order and see it with your own eyes.
For now this improvement is specific to PostgreSQL (the sqlrddpp-postgresql build), but the architecture is ready to bring the same idea to Firebird, MySQL/MariaDB and Oracle — all of them have an equivalent feature (expression indexes / computed columns).
Known limitations, to be upfront: nested functions (UPPER(SUBSTR(...))) still fall back to the synthetic column, and character ordering follows the database collation (as it always has in SQLRDD, including with INDKEY_ columns).
Repository: https://github.com/marcosgambeta/sqlrddpp
If you use SQLRDD with PostgreSQL, please give it a try and share your feedback — the more people validating it in real-world scenarios, the faster this matures and gets ported to the other databases.
Special thanks to Marcos Antonio Gambeta for maintaining SQLRDD++ and for the prompt review and merge of this contribution.
Best regards!
I'd like to share an improvement that has just been merged into SQLRDD++ (the open source fork of SQLRDD maintained on GitHub), solving an old pain point for those using the library with PostgreSQL: synthetic indexes.
The problem
SQLRDD users know this well: when you create an index with a function in the key, like
INDEX ON UPPER(NAME) TAG cli1 TO customers
INDEX ON STRZERO(ID,10) TAG cli2 TO customers
INDEX ON DTOS(HIREDATE) + STRZERO(ID,10) TAG cli3 TO customers
the library used to add an extra column to the table (INDKEY_001, INDKEY_002...) to store the computed key value, and indexed that column instead. This had several side effects:
Tables got "polluted" with columns that don't belong to your data model;
Index creation scanned the whole table doing one UPDATE per row (very slow on large tables);
Every INSERT/UPDATE had to recompute and rewrite those columns — a permanent write overhead;
If the table was modified from outside the application (pgAdmin, scripts, another system), the index silently went out of sync;
Worse: even indexes with no functions at all, just more than 3 fields (INDEX ON ID+NAME+CITY), fell under the same rule and got an extra column.
What changed
On PostgreSQL, index keys built with standard Harbour functions now become native expression indexes. INDEX ON UPPER(NAME) generates something like this directly in PostgreSQL:
CREATE INDEX ... ON customers ((rtrim(upper(coalesce("name",'')))), "sr_recno")
No extra column, instant index creation (no table scan), and the database maintains the index by itself — INSERTs/UPDATEs pay no extra cost, and external changes can no longer desynchronize anything. SEEK (including partial/soft seek), SKIP, SET SCOPE, DBEDIT — everything behaves exactly like a DBF.
Functions translated in this first version: UPPER, SUBSTR, LEFT, STRZERO (with and without decimals) — plus STR and DTOS, which were already handled. Multi-column indexes no longer become synthetic either, regardless of the number of fields.
What about user-defined functions?
If the key uses your own function (INDEX ON MYFUNC(NAME)), the classic synthetic column mechanism still applies — the database has no way to evaluate a function that only exists inside your executable. In other words: the extra column is now created only when there is truly no alternative.
Compatibility (important!)
Existing indexes keep working with the new library, no reindexing required;
Conversion is automatic and incremental: whenever your reindex routine recreates an index, its INDKEY_ column is dropped and the expression index takes its place;
Caveat: executables built with the OLD library cannot open indexes that have already been converted. Rule of thumb: update all executables that access the database BEFORE reindexing;
If you need the old behavior for any reason: SR_SetExpressionIndex(.F.).
How to try it
The changes are already merged into main. In tests/postgresql there is a new program, testexprindex.prg, which creates and populates a table, builds indexes with UPPER/SUBSTR/STRZERO/DTOS plus one with a UDF, shows via information_schema that only the UDF index created an extra column, runs SEEK batteries and opens DBEDIT so you can browse each order and see it with your own eyes.
For now this improvement is specific to PostgreSQL (the sqlrddpp-postgresql build), but the architecture is ready to bring the same idea to Firebird, MySQL/MariaDB and Oracle — all of them have an equivalent feature (expression indexes / computed columns).
Known limitations, to be upfront: nested functions (UPPER(SUBSTR(...))) still fall back to the synthetic column, and character ordering follows the database collation (as it always has in SQLRDD, including with INDKEY_ columns).
Repository: https://github.com/marcosgambeta/sqlrddpp
If you use SQLRDD with PostgreSQL, please give it a try and share your feedback — the more people validating it in real-world scenarios, the faster this matures and gets ported to the other databases.
Special thanks to Marcos Antonio Gambeta for maintaining SQLRDD++ and for the prompt review and merge of this contribution.
Best regards!
Mario H. Sabado
4:25 PM (7 hours ago) 4:25 PM
to harbou...@googlegroups.com
Hi Marco and Marcos,
Thank you very much for this contribution and update.
Can this be applied also with PostgreSQL ODBC using SQLEX? I tested it using ODBC connection and set SR_SetSyntheticIndex(.F.) in my code but it still creates the INDKEY_xxx columns for each table.
I'm forced to use ODBC/SQLEX connection when migrating/uploading because it's super fast compared to native SQLRDD. In my test when uploading 800k+ records, it only takes ~5mins for PostgreSQL, MySQL/MariaDB and ~3mins for FB5. With native SQLRDD native connection, it took several hours. This process includes index creation. Any hint why native SQLRDD takes so long for bulk upload?
For the application runtime, I'm using SQLRDD connection (Native or ODBC) because it's more compatible with existing code.
Best regards,
Mario
--
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: https://groups.google.com/group/harbour-users
---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/harbour-users/de21c63b-1298-44a3-8b0c-01e2ddfabe3bn%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages