I'm using Npgsql 3.2 and it seems that the driver is now preparing statements automatically.
I see the following in the PostgreSQL logs:
LOG: execute <unnamed>:SELECT * FROM database.update_item($1, $2, $3 , $4)
I may be wrong but this is not activated on the connection string and I see no previous 'prepare' call in the logs.
What am I missing ?
Also I'm using Dapper 1.50.2 on top of Npgsql to query the database. This is not yet implemented at this level but I see that there are some talks about such a feature on GitHub.
I'm using a READ COMMITTED transaction to update a row in the database. The row is updated twice with 2 disctinct statements.
When playing the statements one by one in a pgadmin query window it works fine.
When the statements are played by the driver through execute, the first statement seems to put locks on the record, and the second statement hang.
Here is the queries ran in the query window (runs to completion) :
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM database.update_item(params...);
SELECT * from database.update_item(params...);
ROLLBACK;
The corresponding PG logs:
LOG: statement: BEGIN;
LOG: statement: SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
LOG: statement: SELECT * FROM database.update_item(params...);
LOG: statement: SELECT * from database.update_item(params...);
LOG: statement: ROLLBACK;
The PostgreSQL logs when played by Npgsql:
LOG: statement: BEGIN
LOG: statement: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
LOG: execute <unnamed>: select * from database.update_item(params...)
LOG: execute <unnamed>: select * from database.update_item(params...) <-- hangs
Any help appreciated.
EDIT 1:
Additional information: The update_item() plpgsql function is updating the row with an EXECUTE statement.
EDIT 2:
Added PG logs for query window.
First, Npgsql's automatic preparation feature is opt-in - it's not activated without the connection string. Even then, the same SQL needs to be executed several times (5 by default) before Npgsql prepares it. See the documentation for more details.
Regarding your deadlock, are you running your code concurrently? In other words, do you have multiple transactions at the same time, updating the same rows? If so, then this is probably the expected PostgreSQL behavior and has nothing to do with Npgsql. When you update rows in a transaction, these rows are locked until the transaction is committed. The fix in general is to update rows in the same order. See the PostgreSQL documentation for more details.