We have a multi-threaded environment in linux where multiple threads are performing database operations(insert, update, select and at times delete as well) in transaction mode (which may span across stored
procedures) using unixodbc. Now this as is, works fine.
If we introduce postgres triggers (trigger functions) on some of the tables to track insert/update/delete operations, (This is done by keeping and updating a counter field for every insert, update and delete operation performed on a set of tables inside trigger function) at this point, one or more threads get stuck in lock while executing the query, to the extent that sometimes even with the pgadmin, the database tables cannot be updated.
We are using postgres v8.4 and unixodbc v2.2.14.
Here in this case when using postgres triggers in a multithreaded application, do we have to take care of table/row level locks inside trigger function.
Thanks,
Hardik