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
