trigger not firing on foreign table

[DavidBuch1]

Hi I have a connection from postgres to a mariadb database, and the foreign tables work just fine - I can read from them and can write to them. However I need to have a trigger on the foreign table, but it just doesnt fire at all. I have cut it down to the simplest - ie just an insert of one line into a log file, and it never calls it.

Any ideas what could be wrong? im using Postgres 15 on ubuntu 22.04

thanks!

[DavidBuch1]

Hi have resolved the issue - I need to create the triggers as postgres with super user permissions and then it works.

[DavidBuch1]

I spoke to soon. The triggers fire when I do an insert from within postgres, but they do not fire when an insert is done on the Mysql table. I do see the new record in my postgres table, but the trigger never fires Any idea?

thanks

[surajkharage19]

Hi @DavidBuch1,

Let me understand your scenario, please correct me if I am wrong.

You have created a trigger on foreign table on Postgres but not on MySQL table. It gets fired when you insert through foreign table but doesn't get fired when you insert directly into the MySQL table. right?

If my understanding is correct then that is an expected behavior. We do not have any mechanism to tell Postgres that insert happens on foreign table when we insert directly on MySQL table. You can see inserted data on foreign table after insert as select command fetches the data from MySQL side.

If you want to get a trigger fired in your case then you need to consider creating a trigger on MySQL side.

Hope this helps.

[DavidBuch1]

Hi. no it doesnt help really. I need to have the trigger in postgres do extra processing when a new record appears in Mysql. Its no use for me to create a trigger on mysql.

[jeevanchalke]

I don't think that's possible. There is no way the Postgres side knows of any DMLs happening on the MySQL side.

However, if you do all those operations through the foreign table only, then you can do the required extra processing on the Postgres side by using the trigger created on the foreign table.

[DavidBuch1]

I think we are misunderstanding each other. There is a trigger created on the foreign table in postgres. but it does not get fired when a new record is added in mysql. Its not about modifying the table structure, but rather using the data that has been retrieved to do processing on that.

[surajkharage19]

Can you please clarify how the records are getting inserted into MySQL table? using foreign table or directly into MySQL (without foreign table) or any other tool?

[DavidBuch1]

They are inserted into Mysql from a seperate application. Postgres will not be inserting into mysql, just reading from it

[surajkharage19]

Thanks for the clarification.

As informed earlier, this is not possible. When you insert directly using an application into MySQL table then Postgres foreign table's trigger will not run. You may consider redirecting your application inserts through foreign table to run the trigger.