search

tds-fdw / tds_fdw

A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server)
Other
357 stars 99 forks source link

Connection Transaction Isolation Level #345

Open lxndrcx opened 7 months ago

lxndrcx commented 7 months ago

Question

Is there a way (or could there be) to set the TRANSACTION ISOLATION LEVEL for the whole connection? I am connecting to a database with hundreds of tables, so I am using the schema import to get them all.

I have seen https://github.com/tds-fdw/tds_fdw/issues/200 and I understand I control it on a table-by-table basis using WITH in queries. This is not convenient for hundreds of tables.

I think it's possible to set the isolation level per connection (or at least per transaction), so it would be helpful to be able to set that in the Foreign Server configuration.

Thanks in advance for reading my question.

I'll include version information even though I'm not suggesting there is an issue:

Operating system

PRETTY_NAME="Debian GNU/Linux 11 (bullseye)"

Version of tds_fdw

List of installed extensions
  Name   | Version |   Schema   |                                    Description                                    
---------+---------+------------+-----------------------------------------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 tds_fdw | 2.0.3   | public     | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)

Version of PostgreSQL

version                                                           
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 (Debian 15.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

Version of FreeTDS

ii  freetds-common            1.2.3-1                      all          configuration files for FreeTDS SQL client libraries
GeoffMontee commented 7 months ago

This is not currently possible, but this is a great feature request. Thanks!

GeoffMontee commented 7 months ago

If anyone wants to submit a pull request for this, it looks like one way to implement it would be to call dbsetopt() and set DBISOLATION: https://github.com/FreeTDS/freetds/blob/4dc87b0800b67f3dbf4eddbdf35eab3bc133d2f7/src/dblib/dblib.c#L4495

Of course, another way would be to execute SET TRANSACTION ISOLATION LEVEL.