On Postgres I created a table with a compound primary key like this:
- Code: Select all
create table upd_test (
pk1 int not null,
pk2 smallint not null,
s1 text,
primary key (pk1, pk2)
);
do $$
begin
for i in 1..10 loop
for j in 1..10 loop
insert into upd_test select i, j, 'bla';
end loop;
end loop;
end;
$$ language plpgsql;
On Microsoft I created a linked Server:
- Code: Select all
EXEC master.dbo.sp_addlinkedserver @server = N'MyServer', @srvproduct=N'PGNP', @provider=N'PGNP', @datasrc=N'MyServer', @provstr=N'', @catalog=N'postgres'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyServer',@useself=N'False',@locallogin=NULL,@rmtuser=N'postgres',@rmtpassword=''
GO
EXEC master.dbo.sp_serveroption @server=N'MyServer', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MyServer', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MyServer', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MyServer', @optname=N'use remote collation', @optvalue=N'true'
GO
- Code: Select all
update MyServer.postgres.[public].upd_test set s1 = 'x' where pk1=1 and pk2=1
(1 row(s) affected)
But:
- Code: Select all
select * from MyServer.postgres.[public].upd_test where pk1 = 1
shows me, that all rows for pk1=1 are updated.
- Code: Select all
update MyServer.postgres.[public].upd_test set s1 = 'y' where pk2=1
(10 row(s) affected)
But:
- Code: Select all
select * from MyServer.postgres.[public].upd_test
shows me, that all rows are updated
It seems, that pk2 is ignored all times.
I have also testet against PostgresQL-9.0.5 on Gentoo Linux x86_64 with same results.
