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

Commit873b9b8

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 parent240076b commit873b9b8

File tree

3 files changed

+97
-38
lines changed

3 files changed

+97
-38
lines changed

‎contrib/dblink/dblink.c

Lines changed: 36 additions & 38 deletions
Original file line numberDiff line numberDiff line change
@@ -1456,7 +1456,7 @@ get_sql_insert(Relation rel, int *pkattnums, int pknumatts, char **src_pkattvals
14561456
appendStringInfo(str,") VALUES(");
14571457

14581458
/*
1459-
*remember attvals are 1 based
1459+
*Note: i is physical column number (counting from 0).
14601460
*/
14611461
needComma= false;
14621462
for (i=0;i<natts;i++)
@@ -1467,12 +1467,9 @@ get_sql_insert(Relation rel, int *pkattnums, int pknumatts, char **src_pkattvals
14671467
if (needComma)
14681468
appendStringInfo(str,",");
14691469

1470-
if (tgt_pkattvals!=NULL)
1471-
key=get_attnum_pk_pos(pkattnums,pknumatts,i);
1472-
else
1473-
key=-1;
1470+
key=get_attnum_pk_pos(pkattnums,pknumatts,i);
14741471

1475-
if (key>-1)
1472+
if (key >=0)
14761473
val=pstrdup(tgt_pkattvals[key]);
14771474
else
14781475
val=SPI_getvalue(tuple,tupdesc,i+1);
@@ -1503,7 +1500,6 @@ get_sql_delete(Relation rel, int *pkattnums, int pknumatts, char **tgt_pkattvals
15031500
intnatts;
15041501
StringInfostr=makeStringInfo();
15051502
char*sql;
1506-
char*val=NULL;
15071503
inti;
15081504

15091505
/* get relation name including any needed schema prefix and quoting */
@@ -1523,17 +1519,9 @@ get_sql_delete(Relation rel, int *pkattnums, int pknumatts, char **tgt_pkattvals
15231519
appendStringInfo(str,"%s",
15241520
quote_ident_cstr(NameStr(tupdesc->attrs[pkattnum]->attname)));
15251521

1526-
if (tgt_pkattvals!=NULL)
1527-
val=pstrdup(tgt_pkattvals[i]);
1528-
else
1529-
/* internal error */
1530-
elog(ERROR,"target key array must not be NULL");
1531-
1532-
if (val!=NULL)
1533-
{
1534-
appendStringInfo(str," = %s",quote_literal_cstr(val));
1535-
pfree(val);
1536-
}
1522+
if (tgt_pkattvals[i]!=NULL)
1523+
appendStringInfo(str," = %s",
1524+
quote_literal_cstr(tgt_pkattvals[i]));
15371525
else
15381526
appendStringInfo(str," IS NULL");
15391527
}
@@ -1585,12 +1573,9 @@ get_sql_update(Relation rel, int *pkattnums, int pknumatts, char **src_pkattvals
15851573
appendStringInfo(str,"%s = ",
15861574
quote_ident_cstr(NameStr(tupdesc->attrs[i]->attname)));
15871575

1588-
if (tgt_pkattvals!=NULL)
1589-
key=get_attnum_pk_pos(pkattnums,pknumatts,i);
1590-
else
1591-
key=-1;
1576+
key=get_attnum_pk_pos(pkattnums,pknumatts,i);
15921577

1593-
if (key>-1)
1578+
if (key >=0)
15941579
val=pstrdup(tgt_pkattvals[key]);
15951580
else
15961581
val=SPI_getvalue(tuple,tupdesc,i+1);
@@ -1617,16 +1602,10 @@ get_sql_update(Relation rel, int *pkattnums, int pknumatts, char **src_pkattvals
16171602
appendStringInfo(str,"%s",
16181603
quote_ident_cstr(NameStr(tupdesc->attrs[pkattnum]->attname)));
16191604

1620-
if (tgt_pkattvals!=NULL)
1621-
val=pstrdup(tgt_pkattvals[i]);
1622-
else
1623-
val=SPI_getvalue(tuple,tupdesc,pkattnum+1);
1605+
val=tgt_pkattvals[i];
16241606

16251607
if (val!=NULL)
1626-
{
16271608
appendStringInfo(str," = %s",quote_literal_cstr(val));
1628-
pfree(val);
1629-
}
16301609
else
16311610
appendStringInfo(str," IS NULL");
16321611
}
@@ -1694,30 +1673,49 @@ get_tuple_of_interest(Relation rel, int *pkattnums, int pknumatts, char **src_pk
16941673
{
16951674
char*relname;
16961675
TupleDesctupdesc;
1676+
intnatts;
16971677
StringInfostr=makeStringInfo();
16981678
char*sql=NULL;
16991679
intret;
17001680
HeapTupletuple;
17011681
inti;
17021682
char*val=NULL;
17031683

1704-
/* get relation name including any needed schema prefix and quoting */
1705-
relname=generate_relation_name(rel);
1706-
1707-
tupdesc=rel->rd_att;
1708-
17091684
/*
17101685
* Connect to SPI manager
17111686
*/
17121687
if ((ret=SPI_connect())<0)
17131688
/* internal error */
17141689
elog(ERROR,"SPI connect failure - returned %d",ret);
17151690

1691+
/* get relation name including any needed schema prefix and quoting */
1692+
relname=generate_relation_name(rel);
1693+
1694+
tupdesc=rel->rd_att;
1695+
natts=tupdesc->natts;
1696+
17161697
/*
1717-
* Build sql statement to look up tuple of interest Use src_pkattvals
1718-
* as the criteria.
1698+
* Build sql statement to look up tuple of interest, ie, the one matching
1699+
* src_pkattvals. We used to use "SELECT *" here, but it's simpler to
1700+
* generate a result tuple that matches the table's physical structure,
1701+
* with NULLs for any dropped columns. Otherwise we have to deal with
1702+
* two different tupdescs and everything's very confusing.
17191703
*/
1720-
appendStringInfo(str,"SELECT * FROM %s WHERE ",relname);
1704+
appendStringInfoString(str,"SELECT ");
1705+
1706+
for (i=0;i<natts;i++)
1707+
{
1708+
if (i>0)
1709+
appendStringInfoString(str,", ");
1710+
1711+
if (tupdesc->attrs[i]->attisdropped)
1712+
appendStringInfoString(str,"NULL");
1713+
else
1714+
appendStringInfoString(str,
1715+
quote_ident_cstr(NameStr(tupdesc->attrs[i]->attname)));
1716+
}
1717+
1718+
appendStringInfo(str," FROM %s WHERE ",relname);
17211719

17221720
for (i=0;i<pknumatts;i++)
17231721
{

‎contrib/dblink/expected/dblink.out

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -477,3 +477,39 @@ SELECT dblink_disconnect('myconn');
477477
-- should get 'connection "myconn" not available' error
478478
SELECT dblink_disconnect('myconn');
479479
ERROR: connection "myconn" not available
480+
-- test dropped columns in dblink_build_sql_insert, dblink_build_sql_update
481+
CREATE TEMP TABLE test_dropped
482+
(
483+
col1 INT NOT NULL DEFAULT 111,
484+
id SERIAL PRIMARY KEY,
485+
col2 INT NOT NULL DEFAULT 112,
486+
col2b INT NOT NULL DEFAULT 113
487+
);
488+
NOTICE: CREATE TABLE will create implicit sequence "test_dropped_id_seq" for "serial" column "test_dropped.id"
489+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_dropped_pkey" for table "test_dropped"
490+
ALTER TABLE test_dropped DROP COLUMN col1;
491+
ALTER TABLE test_dropped DROP COLUMN col2;
492+
ALTER TABLE test_dropped ADD COLUMN col3 VARCHAR(10);
493+
ALTER TABLE test_dropped ADD COLUMN col4 INT;
494+
INSERT INTO test_dropped VALUES(default,default,'foo','42');
495+
SELECT dblink_build_sql_insert('test_dropped', '2', 1,
496+
ARRAY['1'::TEXT], ARRAY['2'::TEXT]);
497+
dblink_build_sql_insert
498+
---------------------------------------------------------------------------
499+
INSERT INTO test_dropped(id,col2b,col3,col4) VALUES('2','113','foo','42')
500+
(1 row)
501+
502+
SELECT dblink_build_sql_update('test_dropped', '2', 1,
503+
ARRAY['1'::TEXT], ARRAY['2'::TEXT]);
504+
dblink_build_sql_update
505+
-------------------------------------------------------------------------------------------
506+
UPDATE test_dropped SET id = '2', col2b = '113', col3 = 'foo', col4 = '42' WHERE id = '2'
507+
(1 row)
508+
509+
SELECT dblink_build_sql_delete('test_dropped', '2', 1,
510+
ARRAY['2'::TEXT]);
511+
dblink_build_sql_delete
512+
-----------------------------------------
513+
DELETE FROM test_dropped WHERE id = '2'
514+
(1 row)
515+

‎contrib/dblink/sql/dblink.sql

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -244,3 +244,28 @@ SELECT dblink_disconnect('myconn');
244244
-- close the named persistent connection again
245245
-- should get 'connection "myconn" not available' error
246246
SELECT dblink_disconnect('myconn');
247+
248+
-- test dropped columns in dblink_build_sql_insert, dblink_build_sql_update
249+
CREATE TEMP TABLE test_dropped
250+
(
251+
col1INTNOT NULL DEFAULT111,
252+
idSERIALPRIMARY KEY,
253+
col2INTNOT NULL DEFAULT112,
254+
col2bINTNOT NULL DEFAULT113
255+
);
256+
257+
ALTERTABLE test_dropped DROP COLUMN col1;
258+
ALTERTABLE test_dropped DROP COLUMN col2;
259+
ALTERTABLE test_dropped ADD COLUMN col3VARCHAR(10);
260+
ALTERTABLE test_dropped ADD COLUMN col4INT;
261+
262+
INSERT INTO test_droppedVALUES(default,default,'foo','42');
263+
264+
SELECT dblink_build_sql_insert('test_dropped','2',1,
265+
ARRAY['1'::TEXT], ARRAY['2'::TEXT]);
266+
267+
SELECT dblink_build_sql_update('test_dropped','2',1,
268+
ARRAY['1'::TEXT], ARRAY['2'::TEXT]);
269+
270+
SELECT dblink_build_sql_delete('test_dropped','2',1,
271+
ARRAY['2'::TEXT]);

0 commit comments

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