The following bug has been logged on the website:
Bug reference: 8710
Logged by: digoal.zhou
Email address: digoal@126.com
PostgreSQL version: 9.3.2
Operating system: CentOS 5.x
Description:
pg93@db-172-16-3-150-> psql
psql (9.3.1)
Type "help" for help.
digoal=# create extension dblink;
CREATE EXTENSION
digoal=# create table tbl_dblink(c1 int, c2 int, c3 text, pk1 int, c4 text,
c5 int, pk2 text, c6 text, c7 int, pk3 int8, c8 int, c9 text, c10 timestamp,
primary key(pk1,pk2,pk3));
CREATE TABLE
digoal=# insert into tbl_dblink values
(1,1,'test',1,'test',2,'pk2','test',1,1,1,'test', now());
INSERT 0 1
digoal=# select * from tbl_dblink ;
c1 | c2 | c3 | pk1 | c4 | c5 | pk2 | c6 | c7 | pk3 | c8 | c9 |
c10
----+----+------+-----+------+----+-----+------+----+-----+----+------+----------------------------
1 | 1 | test | 1 | test | 2 | pk2 | test | 1 | 1 | 1 | test |
2013-12-31 08:39:01.400074
(1 row)
digoal=# select * from dblink_get_pkey('tbl_dblink');
position | colname
----------+---------
1 | pk1
2 | pk2
3 | pk3
(3 rows)
NOTE, postgresql 9.0+ we use dblink_build* to build SQL , the primary key
use the logical order. but we cann't use dblink_get_pkey get the order
int2vector, we must use the pg_attribute catalog get the logical number.
So, the dblink_get_pkey function need change .
and the second bug:
digoal=# select * from dblink_build_sql_update('tbl_dblink', '4 7 10', 3,
$${1, pk2, 1}$$, $${2,pk2,2}$$);
dblink_build_sql_update
------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
UPDATE tbl_dblink SET c1 = '1', c2 = '1', c3 = 'test', pk1 = '2', c4 =
'test', c5 = '2', pk2 = 'pk2', c6 = 'test', c7 = '1', pk3 =
'2', c8 = '1', c9 = 'test', c10 = '2013-12-31 08:39:01.400074' WHERE pk1 =
'2' AND pk2 = 'pk2' AND pk3 = '2'
(1 row)
We see, the WHERE clause not src pk arrays, but target pk arrays. so this is
a bug.
and we must use dblink_build_sql_delete and dblink_build_sql_insert function
to get the correct SQL.
dblink_build_sql_update function need to change, src array contain OLD.* and
target array contain NEW.*, not only OLD.pkey and NEW.pkey now.
