Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit3b3706d

Browse files
committed
Fix dblink_build_sql_insert() and related functions to handle dropped
columns correctly. In passing, get rid of some dead logic in theunderlying get_sql_insert() etc functions --- there is no caller thatwill pass null value-arrays to them.Per bug report from Robert Voinea.
1 parent4a96908 commit3b3706d

File tree

3 files changed

+101
-32
lines changed

3 files changed

+101
-32
lines changed

‎contrib/dblink/dblink.c‎

Lines changed: 38 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Darko Prenosil <Darko.Prenosil@finteh.hr>
99
* Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
1010
*
11-
* $PostgreSQL: pgsql/contrib/dblink/dblink.c,v 1.96 2010/06/1516:22:19 tgl Exp $
11+
* $PostgreSQL: pgsql/contrib/dblink/dblink.c,v 1.97 2010/06/1519:04:15 tgl Exp $
1212
* Copyright (c) 2001-2010, PostgreSQL Global Development Group
1313
* ALL RIGHTS RESERVED;
1414
*
@@ -1741,7 +1741,7 @@ get_sql_insert(Relation rel, int *pkattnums, int pknumatts, char **src_pkattvals
17411741
appendStringInfo(&buf,") VALUES(");
17421742

17431743
/*
1744-
*remember attvals are 1 based
1744+
*Note: i is physical column number (counting from 0).
17451745
*/
17461746
needComma= false;
17471747
for (i=0;i<natts;i++)
@@ -1752,12 +1752,9 @@ get_sql_insert(Relation rel, int *pkattnums, int pknumatts, char **src_pkattvals
17521752
if (needComma)
17531753
appendStringInfo(&buf,",");
17541754

1755-
if (tgt_pkattvals!=NULL)
1756-
key=get_attnum_pk_pos(pkattnums,pknumatts,i);
1757-
else
1758-
key=-1;
1755+
key=get_attnum_pk_pos(pkattnums,pknumatts,i);
17591756

1760-
if (key>-1)
1757+
if (key >=0)
17611758
val=tgt_pkattvals[key] ?pstrdup(tgt_pkattvals[key]) :NULL;
17621759
else
17631760
val=SPI_getvalue(tuple,tupdesc,i+1);
@@ -1802,10 +1799,6 @@ get_sql_delete(Relation rel, int *pkattnums, int pknumatts, char **tgt_pkattvals
18021799
appendStringInfoString(&buf,
18031800
quote_ident_cstr(NameStr(tupdesc->attrs[pkattnum]->attname)));
18041801

1805-
if (tgt_pkattvals==NULL)
1806-
/* internal error */
1807-
elog(ERROR,"target key array must not be NULL");
1808-
18091802
if (tgt_pkattvals[i]!=NULL)
18101803
appendStringInfo(&buf," = %s",
18111804
quote_literal_cstr(tgt_pkattvals[i]));
@@ -1845,6 +1838,9 @@ get_sql_update(Relation rel, int *pkattnums, int pknumatts, char **src_pkattvals
18451838

18461839
appendStringInfo(&buf,"UPDATE %s SET ",relname);
18471840

1841+
/*
1842+
* Note: i is physical column number (counting from 0).
1843+
*/
18481844
needComma= false;
18491845
for (i=0;i<natts;i++)
18501846
{
@@ -1857,12 +1853,9 @@ get_sql_update(Relation rel, int *pkattnums, int pknumatts, char **src_pkattvals
18571853
appendStringInfo(&buf,"%s = ",
18581854
quote_ident_cstr(NameStr(tupdesc->attrs[i]->attname)));
18591855

1860-
if (tgt_pkattvals!=NULL)
1861-
key=get_attnum_pk_pos(pkattnums,pknumatts,i);
1862-
else
1863-
key=-1;
1856+
key=get_attnum_pk_pos(pkattnums,pknumatts,i);
18641857

1865-
if (key>-1)
1858+
if (key >=0)
18661859
val=tgt_pkattvals[key] ?pstrdup(tgt_pkattvals[key]) :NULL;
18671860
else
18681861
val=SPI_getvalue(tuple,tupdesc,i+1);
@@ -1889,16 +1882,10 @@ get_sql_update(Relation rel, int *pkattnums, int pknumatts, char **src_pkattvals
18891882
appendStringInfo(&buf,"%s",
18901883
quote_ident_cstr(NameStr(tupdesc->attrs[pkattnum]->attname)));
18911884

1892-
if (tgt_pkattvals!=NULL)
1893-
val=tgt_pkattvals[i] ?pstrdup(tgt_pkattvals[i]) :NULL;
1894-
else
1895-
val=SPI_getvalue(tuple,tupdesc,pkattnum+1);
1885+
val=tgt_pkattvals[i];
18961886

18971887
if (val!=NULL)
1898-
{
18991888
appendStringInfo(&buf," = %s",quote_literal_cstr(val));
1900-
pfree(val);
1901-
}
19021889
else
19031890
appendStringInfo(&buf," IS NULL");
19041891
}
@@ -1964,30 +1951,49 @@ get_tuple_of_interest(Relation rel, int *pkattnums, int pknumatts, char **src_pk
19641951
{
19651952
char*relname;
19661953
TupleDesctupdesc;
1954+
intnatts;
19671955
StringInfoDatabuf;
19681956
intret;
19691957
HeapTupletuple;
19701958
inti;
19711959

1960+
/*
1961+
* Connect to SPI manager
1962+
*/
1963+
if ((ret=SPI_connect())<0)
1964+
/* internal error */
1965+
elog(ERROR,"SPI connect failure - returned %d",ret);
1966+
19721967
initStringInfo(&buf);
19731968

19741969
/* get relation name including any needed schema prefix and quoting */
19751970
relname=generate_relation_name(rel);
19761971

19771972
tupdesc=rel->rd_att;
1973+
natts=tupdesc->natts;
19781974

19791975
/*
1980-
* Connect to SPI manager
1976+
* Build sql statement to look up tuple of interest, ie, the one matching
1977+
* src_pkattvals. We used to use "SELECT *" here, but it's simpler to
1978+
* generate a result tuple that matches the table's physical structure,
1979+
* with NULLs for any dropped columns. Otherwise we have to deal with
1980+
* two different tupdescs and everything's very confusing.
19811981
*/
1982-
if ((ret=SPI_connect())<0)
1983-
/* internal error */
1984-
elog(ERROR,"SPI connect failure - returned %d",ret);
1982+
appendStringInfoString(&buf,"SELECT ");
19851983

1986-
/*
1987-
* Build sql statement to look up tuple of interest Use src_pkattvals as
1988-
* the criteria.
1989-
*/
1990-
appendStringInfo(&buf,"SELECT * FROM %s WHERE ",relname);
1984+
for (i=0;i<natts;i++)
1985+
{
1986+
if (i>0)
1987+
appendStringInfoString(&buf,", ");
1988+
1989+
if (tupdesc->attrs[i]->attisdropped)
1990+
appendStringInfoString(&buf,"NULL");
1991+
else
1992+
appendStringInfoString(&buf,
1993+
quote_ident_cstr(NameStr(tupdesc->attrs[i]->attname)));
1994+
}
1995+
1996+
appendStringInfo(&buf," FROM %s WHERE ",relname);
19911997

19921998
for (i=0;i<pknumatts;i++)
19931999
{

‎contrib/dblink/expected/dblink.out‎

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -887,3 +887,40 @@ SELECT dblink_disconnect();
887887
OK
888888
(1 row)
889889

890+
-- test dropped columns in dblink_build_sql_insert, dblink_build_sql_update
891+
CREATE TEMP TABLE test_dropped
892+
(
893+
col1 INT NOT NULL DEFAULT 111,
894+
id SERIAL PRIMARY KEY,
895+
col2 INT NOT NULL DEFAULT 112,
896+
col2b INT NOT NULL DEFAULT 113
897+
);
898+
NOTICE: CREATE TABLE will create implicit sequence "test_dropped_id_seq" for serial column "test_dropped.id"
899+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_dropped_pkey" for table "test_dropped"
900+
INSERT INTO test_dropped VALUES(default);
901+
ALTER TABLE test_dropped
902+
DROP COLUMN col1,
903+
DROP COLUMN col2,
904+
ADD COLUMN col3 VARCHAR(10) NOT NULL DEFAULT 'foo',
905+
ADD COLUMN col4 INT NOT NULL DEFAULT 42;
906+
SELECT dblink_build_sql_insert('test_dropped', '2', 1,
907+
ARRAY['1'::TEXT], ARRAY['2'::TEXT]);
908+
dblink_build_sql_insert
909+
---------------------------------------------------------------------------
910+
INSERT INTO test_dropped(id,col2b,col3,col4) VALUES('2','113','foo','42')
911+
(1 row)
912+
913+
SELECT dblink_build_sql_update('test_dropped', '2', 1,
914+
ARRAY['1'::TEXT], ARRAY['2'::TEXT]);
915+
dblink_build_sql_update
916+
-------------------------------------------------------------------------------------------
917+
UPDATE test_dropped SET id = '2', col2b = '113', col3 = 'foo', col4 = '42' WHERE id = '2'
918+
(1 row)
919+
920+
SELECT dblink_build_sql_delete('test_dropped', '2', 1,
921+
ARRAY['2'::TEXT]);
922+
dblink_build_sql_delete
923+
-----------------------------------------
924+
DELETE FROM test_dropped WHERE id = '2'
925+
(1 row)
926+

‎contrib/dblink/sql/dblink.sql‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -412,3 +412,29 @@ SELECT notify_name, be_pid = (select t.be_pid from dblink('select pg_backend_pid
412412
SELECT*from dblink_get_notify();
413413

414414
SELECT dblink_disconnect();
415+
416+
-- test dropped columns in dblink_build_sql_insert, dblink_build_sql_update
417+
CREATE TEMP TABLE test_dropped
418+
(
419+
col1INTNOT NULL DEFAULT111,
420+
idSERIALPRIMARY KEY,
421+
col2INTNOT NULL DEFAULT112,
422+
col2bINTNOT NULL DEFAULT113
423+
);
424+
425+
INSERT INTO test_droppedVALUES(default);
426+
427+
ALTERTABLE test_dropped
428+
DROP COLUMN col1,
429+
DROP COLUMN col2,
430+
ADD COLUMN col3VARCHAR(10)NOT NULL DEFAULT'foo',
431+
ADD COLUMN col4INTNOT NULL DEFAULT42;
432+
433+
SELECT dblink_build_sql_insert('test_dropped','2',1,
434+
ARRAY['1'::TEXT], ARRAY['2'::TEXT]);
435+
436+
SELECT dblink_build_sql_update('test_dropped','2',1,
437+
ARRAY['1'::TEXT], ARRAY['2'::TEXT]);
438+
439+
SELECT dblink_build_sql_delete('test_dropped','2',1,
440+
ARRAY['2'::TEXT]);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp