update not working with compound key

More
8 years 3 months ago #4344 by Pittiplatsch
Pittiplatsch created the topic: update not working with compound key
I am evaluating PGNP-1.3.0.2256. The Microsoft side is SQL Server 2008 Developer Edition on Windows Server 2003 (64bit). The Postgres side is PostgeSQL-9.1.2-r1 on Gentoo Linux i686.
On Postgres I created a table with a compound primary key like this:
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:
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
update MyServer.postgres.[public].upd_test set s1 = 'x' where pk1=1 and pk2=1
(1 row(s) affected)

But:
select * from MyServer.postgres.[public].upd_test where pk1 = 1
shows me, that all rows for pk1=1 are updated.
update MyServer.postgres.[public].upd_test set s1 = 'y' where pk2=1
(10 row(s) affected)

But:
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.

Please Log in or Create an account to join the conversation.

More
8 years 3 months ago #4345 by Moderator
Moderator replied the topic: Re: update not working with compound key
This is a defect, and we are able to reproduce it. We are working on a fix. We'll update you on status in couple of days.

Thank you!

Please Log in or Create an account to join the conversation.

More
7 years 11 months ago #6423 by Pittiplatsch
Pittiplatsch replied the topic: Re: update not working with compound key
This bug is not mentioned in the PGNP Project History. Ist it fixed in 1.4 release?

Please Log in or Create an account to join the conversation.

More
7 years 11 months ago #6447 by Moderator
Moderator replied the topic: Re: update not working with compound key
yes, it is fixed in v1.4.

Please Log in or Create an account to join the conversation.

Time to create page: 0.090 seconds
Powered by Kunena Forum