1

Let's assume, I have two DBs, either MySQL or PostgreSQL (sorry, but we have not yet decided what DBMS to use).

Every second multiple rows are being inserted or updated on the first DB. For each inserted/updated row I want to call a Python script, which connects to another DB on another host, makes some queries there; if everything is fine the script writes these rows (which have been inserted or updated) to the second DB.

After some research I see the following options to call the script:

  • Use triggers and do everything in SQL (it looks like the connection to another DB should be not a problem for PostgreSQL, but may be a problem for MySQL if not using federated storage engine)
  • Use UDF (user-defined functions)

I also read a bit about different patterns of architecting databases, like Saga or publish/subscribe, but didn't find any concrete practical examples of how to do this.

Two of my questions are:

  1. Are there any other possibilities to implement the wanted solution?
  2. The two options I mentioned (triggers and UDF): do they have any hidden traps? Which option is better in terms of performance?
  • Cannot you solve this from where the rows are inserted from into DB1? – dezso Jul 11 '18 at 11:16
  • MySQL, at least, prohibits calling out of itself to Python (or anything else). Yes, you could write a UDF, but it would be better to rethink your requirement. – Rick James Jul 11 '18 at 13:48
  • 1
    you can write PostgreSQL functions in Python – Neil McGuigan Jul 11 '18 at 17:35
  • 1
    If you were using MariaDB (which was designed as a drop-in replacement for MySQL, now slightly diverging), then instead of the FEDERATED storage engine, you could use the CONNECT storage engine which is much better and amongst other features allows use of ODBC so you can run SQL against any other DBMS. This way you can handle everything within SQL on MariaDB, no need for calling an external script. – dbdemon Jul 12 '18 at 2:56
  • This overview might help in deciding between Postgres or MySQL – a_horse_with_no_name Jul 12 '18 at 6:16
1

In Postgres, I would do this as a trigger that uses a foreign table to make the remote table appear as a local one.

The trigger function can then query, update or join the remote table as if it was a local one and Postgres will be smart enough to push the work to the remote server (e.g. filtering, grouping, sorting etc).

The table can also be updated directly from within the trigger, no need for some scripts outside the database

If you prefer Python instead of PL/pgSQL you can write the trigger function in Python.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.