Execute queries with NOLOCK on MS SQL side

[kloba]

Hello,

How to run all queries throughout tds_fdw in NOLOCK mode on MS SQL side?

I need this, in a case to don't lock a table on an OLTP database.

Best regards,
Taras Kloba

[SudoerWithAnOpinion]

Use the query option in the foreign table definition and define your query WITH NOLOCK

CREATE FOREIGN TABLE mssql_table (
	id integer,
	data varchar)
	SERVER mssql_svr
	OPTIONS ( query 'SELECT * FROM dbo.mytable WITH (NOLOCK)' );

Keep in mind, this causes READ UNCOMMITED (or dirty reads) and is generally considered bad practice. The MSSQL lock manger should allow your query to read data without locking the data from other transactions and updates (since this is a non-blocking type of lock) unless an UPDATE needs an exclusive lock for some reason.
Due to the issues that arise from dirty reads, you might be returning more data (or incomplete/ or duplicated data) back to postgres. Use this option with care.

[SudoerWithAnOpinion]

@kloba: If this resolved your issue, can you be a sweetie and close this issue?
@GeoffMontee: I think this has gone stale, can you close this if @kloba does not reply?

[kloba]

Thank you a lot.