Thursday, 12 December 2024

Postgres and Migrating Databases

So, it's doable to transfer all data from an Oracle database structure into a similar (hopefully exactly the same) database structure in Postgres.

One of the problems you encounter, is the Foreign Keys. These will get triggered when inserting data .

Now, in a perfect scenario, you know that the Oracle database structure, which also has the same Foreign Keys, is fine, so we won't need to check the Foreign Keys in Postgres when inserting data.

You cannot turn off Foreign Keys in Postgres, however, Postgres automatically creates system triggers for Foreign Keys that check if referential integrity is maintained.

So it simply comes down to temporarily disabling those triggers.

ALTER TABLE some_table DISABLE TRIGGER ALL;

And afterwards, enable them again.

ALTER TABLE some_table ENABLE TRIGGER ALL;

Problem

But what is not all data was successfully transferred? (due to network problems for instance). Now you have a Postgres database that no longer has referential integrity in all cases and you might not find out about this until God knows when.

So, you need to check afterwards if all the Foreign Keys are accounted for, i.e. does every value for a foreign key have an equivalent row in the related table?

I found a script for this at [1].

References

[1] Github.com - enrico-lt/ForeignKeyCheck
https://github.com/enrico-lt/ForeignKeyCheck

Thursday, 28 November 2024

Solution: Problems with Kotlin

Well, the solution to [1] is obviously that you are missing a "&&".

This causes Kotlin to assume the expression:

it.address.country == "United Kingdom"

... is the expression that needs to be returned. This causes the code to spit out all people currently in the United Kingdom.

It took me a little while to notice the problem. My IDE didn't help in the slightest.

References

[1] Problems with Kotlin