On Tue, Mar 28, 2023 at 6:40 AM Sebastien Flaesch <sebastien.flaesch@xxxxxxx> wrote:
...I think if you're honest with yourself you already know the answer to this question. The only real solution is to update the legacy code to use the primary key, or (if that's not possible) change the table definition to add your own indexed BIGSERIAL value called "ROWID" to the rows and use that instead (assuming it will be large enough).
Geoff
I have to second this... Why not, during conversion, create a ROWID BIGSERIAL column in the PG only version. (And if not large enough, it's easy enough to use a NUMERIC field, and a sequence)
Then the code would have access to this field, and when it uses it to delete/update it should work.
I cringe at the thought of using CTID. And while it's probably "safe enough" inside a single transaction. I doubt that there is much "testing" of this concept.
Having been through this process (Oracle to PG), I wonder how far you are into the process... Because Packages/Package Variables, Global Temp Tables, and Autonomous Transactions all consumed significant time in our process, as well as variable/field naming problems... If you pull off converting this to PG without changing the source. Let me know...
Regards, Kirk
