BUG #8710: dblink dblink_get_pkey output bug, and dblink_build_sql_update bug
От | digoal@126.com |
---|---|
Тема | BUG #8710: dblink dblink_get_pkey output bug, and dblink_build_sql_update bug |
Дата | |
Msg-id | E1VxnVm-0003Co-DJ@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #8710: dblink dblink_get_pkey output bug, and dblink_build_sql_update bug
|
Список | pgsql-bugs |
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.
В списке pgsql-bugs по дате отправления: