SQLRDD++: index keys with Harbour functions now use native PostgreSQL expression indexes (no more INDKEY_ columns)
48 views
Skip to first unread message
omarco...@gmail.com
Jul 13, 2026, 3:03:11 PM (17 hours ago) Jul 13
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
Jul 13, 2026, 4:25:16 PM (15 hours ago) Jul 13
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.
omarco...@gmail.com
7:46 AM (1 minute ago) 7:46 AM
to Harbour Users
Hi Mario,
Thanks for the feedback and for testing it so quickly!
About ODBC/SQLEX: no, the expression index support doesn't apply there yet — and that explains what you saw. The new logic lives in the PostgreSQL native variant of the RDD (source/sqlrdd2_postgresql.prg, i.e. the sqlrddpp-postgresql build with the SR_PGS connection). The ODBC and SQLEX paths use the generic core (sqlrdd2.prg), which still has the original synthetic index behavior, so INDKEY_xxx columns are still created there regardless of settings.
Two clarifications about the switches, since they are easy to confuse:
SR_SetSyntheticIndex(.F.) is already the default — it only controls forcing ALL indexes to be synthetic. It has never translated function keys; a key like UPPER(...) or STRZERO(...) still became synthetic with it set to .F.;
The new behavior is controlled by SR_SetExpressionIndex() (default .T.), and it only exists in the PostgreSQL native build for now.
Porting it to the generic ODBC/SQLEX core is quite feasible — the translator produces plain PostgreSQL SQL, so it's mostly a matter of replicating the logic in sqlrdd2.prg guarded by nSystemID == SQLRDD_RDBMS_POSTGR.
My plan is to prepare a PR bringing the same logic to the ODBC path and submit it to Marcos Gambeta for review and approval (and later do the same for the Firebird/MySQL/Oracle native variants, which have equivalent features).
About bulk upload speed: part of the "several hours" you measured is very likely the synthetic index machinery itself, and that's exactly what this change removes. Two things to check in your migration flow:
Index creation on a populated table: with synthetic indexes, INDEX ON runs one UPDATE per row to fill the INDKEY column — on 800k records that's 800k round-trips per index. With the new expression indexes this becomes a single CREATE INDEX statement, so this cost simply disappears for standard-function keys;
Indexes created before the load: if the indexes already exist while you're appending, every INSERT also recomputes and writes the INDKEY columns client-side. The usual advice applies (load first, index later), but again, with expression indexes the per-insert overhead is gone too, since the database maintains the index itself.
Beyond that, wrapping the load in explicit transactions (instead of the default per-statement commit) and making sure trace/log is off (sr_StartLog) also make a big difference in round-trip-bound loads. If you can share whether your slow native test created the indexes before or after the upload, that would help pinpoint how much of the time was INDKEY maintenance versus raw INSERT throughput.
By the way, your 800k-record migration sounds like a perfect benchmark for this work. Could you share the table structure, the index keys and a rough description of the upload loop? No need for the actual data — with the structure I can generate a similar volume and reproduce the scenario here.
Best regards,
Marco
Thanks for the feedback and for testing it so quickly!
About ODBC/SQLEX: no, the expression index support doesn't apply there yet — and that explains what you saw. The new logic lives in the PostgreSQL native variant of the RDD (source/sqlrdd2_postgresql.prg, i.e. the sqlrddpp-postgresql build with the SR_PGS connection). The ODBC and SQLEX paths use the generic core (sqlrdd2.prg), which still has the original synthetic index behavior, so INDKEY_xxx columns are still created there regardless of settings.
Two clarifications about the switches, since they are easy to confuse:
SR_SetSyntheticIndex(.F.) is already the default — it only controls forcing ALL indexes to be synthetic. It has never translated function keys; a key like UPPER(...) or STRZERO(...) still became synthetic with it set to .F.;
The new behavior is controlled by SR_SetExpressionIndex() (default .T.), and it only exists in the PostgreSQL native build for now.
Porting it to the generic ODBC/SQLEX core is quite feasible — the translator produces plain PostgreSQL SQL, so it's mostly a matter of replicating the logic in sqlrdd2.prg guarded by nSystemID == SQLRDD_RDBMS_POSTGR.
My plan is to prepare a PR bringing the same logic to the ODBC path and submit it to Marcos Gambeta for review and approval (and later do the same for the Firebird/MySQL/Oracle native variants, which have equivalent features).
About bulk upload speed: part of the "several hours" you measured is very likely the synthetic index machinery itself, and that's exactly what this change removes. Two things to check in your migration flow:
Index creation on a populated table: with synthetic indexes, INDEX ON runs one UPDATE per row to fill the INDKEY column — on 800k records that's 800k round-trips per index. With the new expression indexes this becomes a single CREATE INDEX statement, so this cost simply disappears for standard-function keys;
Indexes created before the load: if the indexes already exist while you're appending, every INSERT also recomputes and writes the INDKEY columns client-side. The usual advice applies (load first, index later), but again, with expression indexes the per-insert overhead is gone too, since the database maintains the index itself.
Beyond that, wrapping the load in explicit transactions (instead of the default per-statement commit) and making sure trace/log is off (sr_StartLog) also make a big difference in round-trip-bound loads. If you can share whether your slow native test created the indexes before or after the upload, that would help pinpoint how much of the time was INDKEY maintenance versus raw INSERT throughput.
By the way, your 800k-record migration sounds like a perfect benchmark for this work. Could you share the table structure, the index keys and a rough description of the upload loop? No need for the actual data — with the structure I can generate a similar volume and reproduce the scenario here.
Best regards,
Marco
Reply all
Reply to author
Forward
0 new messages