Troubles with closing connections

[karpiv]

When I starts a distributed transactions explicitly, like

Code: Select all

Begin tran trn1

  delete from TestPgNP.TestPgNP.[public].test
  where id=1

commit tran

I get a hung transaction and it never ends
(netstat -an | find "xxx.xxx.xxx.xxx" at mssqlserver host where "xxx.xxx.xxx.xxx" - ip of PG server)

[Moderator]

I'll try to reproduce it and will let you know when fix is available. Thank you!

[Moderator]

I haven't been able to reproduce the "hang issue" by calling following commands:

Code: Select all

Begin tran trn1
DELETE FROM PGNPWW.postgres.[public].testtbl WHERE id = 1
commit

I used SQL Server 2008, PGNP Provider build 2132.

Here is fragment of log captured in PGNP Profiler:

Code: Select all

----------   Executed SQL   ---------
START TRANSACTION ISOLATION LEVEL READ COMMITTED

----------   Executed SQL   ---------
SAVEPOINT PGNP_NESTED_TRANS

----------   Client SQL   ------------
IDBSchemaRowset::GetRowset(DBSCHEMA_TABLES)
----------   Executed SQL   ---------
select * from (select schemaname as "TABLE_SCHEMA", tablename as "TABLE_NAME", CASE WHEN schemaname='pg_catalog' or schemaname='information_schema' THEN 'SYSTEM TABLE' ELSE 'TABLE' END as "TABLE_TYPE", NULL as "TABLE_GUID", NULL as "DESCRIPTION", NULL as "TABLE_PROPID", NULL as "DATE_CREATED", NULL as "DATE_MODIFIED" from pg_tables union select schemaname as "TABLE_SCHEMA", viewname as "VIEW_NAME", CASE WHEN schemaname='pg_catalog' or schemaname='information_schema' THEN 'SYSTEM VIEW' ELSE 'VIEW' END as "VIEW_TYPE", NULL as "TABLE_GUID", NULL as "DESCRIPTION", NULL as "TABLE_PROPID", NULL as "DATE_CREATED", NULL as "DATE_MODIFIED" from pg_views) s where "TABLE_NAME"=[PGTYPE_NAME, testtbl] order by "TABLE_TYPE", "TABLE_SCHEMA", "TABLE_NAME"

----------   Client SQL   ------------
IDBSchemaRowset::GetRowset(DBSCHEMA_COLUMNS)
----------   Executed SQL   ---------
select * from (select T.schemaname as "TABLE_SCHEMA", T.tablename as "TABLE_NAME", A.attname as "COLUMN_NAME", A.attnum as "ORDINAL_POSITION", A.atthasdef as "COLUMN_HASDEFAULT", A.atttypid as "DATA_TYPE", TY.typname as "TYPNAME", A.attnotnull as "NOT_NULL", A.attlen as "FIELD_LENGTH", A.atttypmod as "FIELD_MOD", D.adsrc as "COLUMN_DEFAULT" from pg_attribute A inner join pg_class C on (A.attrelid=C.oid) inner join pg_tables T on (C.relname=T.tablename) inner join pg_namespace NS on (NS.oid=C.relnamespace and NS.nspname=T.schemaname) inner join pg_type TY on (TY.oid=A.atttypid) left outer join pg_attrdef D on (D.adrelid=C.oid and D.adnum=A.attnum) where A.attnum>0 and A.attisdropped='f' union all select T.schemaname as "TABLE_SCHEMA", T.viewname as "TABLE_NAME", A.attname as "COLUMN_NAME", A.attnum as "ORDINAL_POSITION", A.atthasdef as "COLUMN_HASDEFAULT", A.atttypid as "DATA_TYPE", TY.typname as "TYPNAME", A.attnotnull as "NOT_NULL", A.attlen as "FIELD_LENGTH", A.atttypmod as "FIELD_MOD", D.adsrc as "COLUMN_DEFAULT" from pg_attribute A inner join pg_class C on (A.attrelid=C.oid) inner join pg_views T on (C.relname=T.viewname) inner join pg_namespace NS on (NS.oid=C.relnamespace and NS.nspname=T.schemaname) inner join pg_type TY on (TY.oid=A.atttypid) left outer join pg_attrdef D on (D.adrelid=C.oid and D.adnum=A.attnum) where A.attnum>0 and A.attisdropped='f') s where "TABLE_SCHEMA"=[PGTYPE_NAME, public] and "TABLE_NAME"=[PGTYPE_NAME, testtbl] order by "TABLE_SCHEMA", "TABLE_NAME", "ORDINAL_POSITION"

----------   Client SQL   ------------
IDBSchemaRowset::GetRowset(DBSCHEMA_INDEXES)
----------   Executed SQL   ---------
select * from (select T.schemaname as "TABLE_SCHEMA", T.tablename as "TABLE_NAME", (select C1.relname from pg_class C1 where I.indexrelid=C1.oid) as "INDEX_NAME", I.indisprimary as "IS_PRIMARY", I.indisunique as "IS_UNIQUE", I.indisclustered as "IS_CLUSTERED", I.indkey as "INDKEY", C.relpages as "PAGES", C.reltuples as "CARDINALITY", (select AM.amname from pg_class C2 inner join pg_am AM on (C2.relam=AM.oid) where I.indexrelid=C2.oid) as "AM" from pg_index I inner join pg_class C on (I.indrelid=C.oid) inner join pg_tables T on (C.relname=T.tablename)) s where "TABLE_SCHEMA"=[PGTYPE_NAME, public] and "TABLE_NAME"=[PGTYPE_NAME, testtbl]

----------   Client SQL   ------------
SELECT * FROM "postgres"."public"."testtbl"
----------   Executed SQL   ---------
DECLARE pgnpcsr1 CURSOR FOR
select * from "public"."testtbl"

----------   Client SQL   ------------
DELETE FROM "postgres"."public"."testtbl"  WHERE "id"=(1)
----------   Executed SQL   ---------
delete from "public"."testtbl" where "id"=(1)

----------   Executed SQL   ---------
RELEASE SAVEPOINT PGNP_NESTED_TRANS

----------   Executed SQL   ---------
COMMIT

Is this issue reproducible every time?

[karpiv]

Transaction in SQLServer succesfully finished but connection from SQLServer host to PostGreeSQL host ever "ESTABLISHED" and never close

[Moderator]

yes, the issue is reproducible. we are working on a fix. please hold on.