CODEWITHSUNDEEP
postgresqlpostgresql-9.1
futoshita
futoshita

Reputation: 161

FDW seems to lock table on foreign server

I try to use foreign table to link 2 postgresql databases

everything is fine and I can retrieve all data I want

the only issue is that the data wrapper seems to lock tables in foreign server and it's very annoying when I unit test my code

if I don't do any select request I can initialize data and truncate both tables in local server and tables in remote server but I execute one select statement the truncate command on remote server seems to be in deadlock state

do you know how I can avoid this lock?

thanks

[edit]

I use this data wrapper to link 2 postgresql databases: http://interdbconnect.sourceforge.net/pgsql_fdw/pgsql_fdw-en.html

I use table1 of db1 as foreign table in db2

when I execute a select query in foreign_table1 in db2, there is an AccessShareLock for table1 in db1 the query is very simple: select * from foreign_table1

the lock is never released so when I execute a truncate command at the end of my unit test, there is a conflict because the truncate add an AccessExclusiveLock

I don't know how to release the first AccessShareLock but I think it would be done automatically by the wrapper...

hope this help

Upvotes: 2

Views: 1068

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324731

AccessExclusiveLock and AccessShareLock aren't generally obtained explicitly. They're obtained automatically by certain normal statements. See locking - the lock list says which statements acquire which locks, which says:

ACCESS SHARE

Conflicts with the ACCESS EXCLUSIVE lock mode only.

The SELECT command acquires a lock of this mode on referenced tables. In general, any query that only reads a table and does not modify it will acquire this lock mode.

What this means is that your 1st transaction hasn't committed or rolled back (thus releasing its locks) yet, so the 2nd can't TRUNCATE the table because TRUNCATE requires ACCESS EXCLUSIVE which conflicts with ACCESS SHARE.

Make sure the 1st transaction commits or rolls back.

BTW, is the "foreign" database actually the local database, ie are you using pgsql_fdw as an alternative to dblink to simulate autonomous transactions?

Upvotes: 1

Related Questions