CODEWITHSUNDEEP
postgresqltriggers
ahmad
ahmad

Reputation: 13

Function firing at same time by trigger

I created trigger in PostgreSQL which may fires multiple time in a single moment. This trigger is calling another function to insert data in table(s). There is a select statement to find the id (by returning statement and save in a variable) of respective record from tabl say TableA and then i have to insert multiple rows in other table say TableB referencing this id .

My issue is: this variable holding Id, sometimes insert wrong value in TableB . what i understand (may be wrong) , this variable value changes during the insertion (at same time trigger fires again).

like

declare Id integer;
select keyID INTO Id from tableA;
INSERT INTO TableB (Id, date) values (Id, now());

Could any one please explain, what exactly the issue in this case. Will be thankful if provide solutions

Upvotes: 1

Views: 291

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246493

A trigger is running in the same transaction as the statement that triggered it, and it cannot see the effects of other transactions that are currently running (no “dirty reads”).

So if two data modifying statements are running at the same time, the concurrently running trigger functions cannot “see” the modifications by the other transaction, and you can end up with a result that could never have happened if the operations had been executed one after the other. This is called an anomaly.

The default READ COMMITTED isolation level cannot protect you from such anomalies, nor can REPEATABLE READ. Only if all transactions are using the SERIALIZABLE isolation level, no anomalies can occur. In this case, one of the transactions will be aborted by a serialization error and has to be repeated.

An alternative approach is to lock rows as you read them by using SELECT ... FOR UPDATE. Then you can use READ COMMITTED isolation, but the locks will be bad for concurrency and can lead to deadlocks, which also lead to the termination of one of the involved transactions.

Upvotes: 2

Related Questions