Thread: Rollback transaction doesn't work

Rollback transaction doesn't work

From
"KJ"
Date:
Hi all,

it seems as if ODBC provider doesn't work correctly in conjunction with MSDTC.
Transaction are handled correctly using pgadmin or an ODBC based client (e.g. WinSQL).

When running statements from MS SQL Server then in case of a rollback transaction a COMMIT is being forwarded to
postgres(see log file below). 

Any help is welcome.

TIA
KJ

example:
/* Commands run from SQL Server */
set XACT_ABORT ON
begin transaction
insert into OPENQUERY(LPGA,'select * from test.test where 1=0')
values (3,convert(varchar(20), getdate(),121),datepart(ms,getdate()))
select @@trancount, * from OPENQUERY(LPGA,'select * from test.test')
rollback tran
select @@trancount, * from OPENQUERY(LPGA,'select * from test.test')


/* Corrensponding pglog */
2007-04-26 15:44:16 LOG:  statement: select oid, typbasetype from pg_type where typname = 'lo'
2007-04-26 15:44:16 LOG:  statement: show max_identifier_length
2007-04-26 15:44:16 LOG:  statement: select * from test.test where 1=0
2007-04-26 15:44:16 LOG:  statement: select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen,
a.atttypmod,a.attnotnull, c.relhasrules, c.relkind, c.oid, d.adsrc from (((pg_catalog.pg_class c inner join
pg_catalog.pg_namespacen on n.oid = c.relnamespace and c.oid = 18288) inner join pg_catalog.pg_attribute a on (not
a.attisdropped)and a.attnum > 0 and a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid = a.atttypid) left
outerjoin pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname,
attnum
2007-04-26 15:44:16 LOG:  statement: select * from test.test
2007-04-26 15:44:16 LOG:  statement: select * from test.test
2007-04-26 15:44:16 LOG:  statement: select relhasoids, c.oid from pg_class c, pg_namespace n where relname = 'test'
andnspname = 'test' and c.relnamespace = n.oid 
2007-04-26 15:44:16 LOG:  statement: select a.attname, a.atttypid from pg_index i, pg_attribute a where indrelid=18288
andindnatts=1 and indisunique and indexprs is null and indpred is null and i.indrelid = a.attrelid and
a.attnum=i.indkey[0]and attnotnull and atttypid in (23, 26) 
2007-04-26 15:44:16 LOG:  statement: select * , "ctid", "a" from test.test where 1=0
2007-04-26 15:44:17 LOG:  statement: BEGIN;insert into "test"."test" ("a", "b", "c") values (3, E'2007-04-26
15:44:17.','0.000') returning ctid 
2007-04-26 15:44:17 LOG:  statement: select * , "ctid", "a" from test.test  where ctid = '(0,4)'
2007-04-26 15:44:17 LOG:  statement: COMMIT
2007-04-26 15:44:17 LOG:  statement: select * from test.test
2007-04-26 15:44:17 LOG:  statement: select * from test.test

--
"Feel free" - 10 GB Mailbox, 100 FreeSMS/Monat ...
Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail

Re: Rollback transaction doesn't work

From
Hiroshi Inoue
Date:
KJ wrote:
> Hi all,
>
> it seems as if ODBC provider doesn't work correctly in conjunction with MSDTC.
> Transaction are handled correctly using pgadmin or an ODBC based client (e.g. WinSQL).
>
> When running statements from MS SQL Server then in case of a rollback transaction a COMMIT is being forwarded to
postgres(see log file below). 
>
> Any help is welcome.

Could you send me the Mylog output ?

regards,
Hiroshi Inoue

Re: Rollback transaction doesn't work

From
Hiroshi Inoue
Date:
I wrote:
> KJ wrote:
>> Hi all,
>>
>> it seems as if ODBC provider doesn't work correctly in conjunction with MSDTC.
>> Transaction are handled correctly using pgadmin or an ODBC based client (e.g. WinSQL).
>>
>> When running statements from MS SQL Server then in case of a rollback transaction a COMMIT is being forwarded to
postgres(see log file below). 
>>
>> Any help is welcome.
>
> Could you send me the Mylog output ?

Could you try first
  begin distributed transaction
instead of
  begin transaction
?

regards,
Hiroshi Inoue