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

Commitc0989c6

Browse files
committed
Change the interpretation of the primary_key_attnums parameter of
dblink_build_sql_insert() and related functions. Now the column numbersare treated as logical not physical column numbers. This will provide sanerbehavior in the presence of dropped columns; furthermore, if we ever getaround to allowing rearrangement of logical column ordering, the originaldefinition would become nearly untenable from a usability standpoint.Per recent discussion of dblink's handling of dropped columns.Not back-patched for fear of breaking existing applications.
1 parent77a4c51 commitc0989c6

File tree

4 files changed

+85
-17
lines changed

4 files changed

+85
-17
lines changed

‎contrib/dblink/dblink.c

Lines changed: 27 additions & 5 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.97 2010/06/1519:04:15 tgl Exp $
11+
* $PostgreSQL: pgsql/contrib/dblink/dblink.c,v 1.98 2010/06/1520:29:01 tgl Exp $
1212
* Copyright (c) 2001-2010, PostgreSQL Global Development Group
1313
* ALL RIGHTS RESERVED;
1414
*
@@ -2381,11 +2381,13 @@ escape_param_str(const char *str)
23812381
* Validate the PK-attnums argument for dblink_build_sql_insert() and related
23822382
* functions, and translate to the internal representation.
23832383
*
2384-
* The user supplies an int2vector of 1-basedphysical attnums, plus a count
2384+
* The user supplies an int2vector of 1-basedlogical attnums, plus a count
23852385
* argument (the need for the separate count argument is historical, but we
23862386
* still check it). We check that each attnum corresponds to a valid,
23872387
* non-dropped attribute of the rel. We do *not* prevent attnums from being
23882388
* listed twice, though the actual use-case for such things is dubious.
2389+
* Note that before Postgres 9.0, the user's attnums were interpreted as
2390+
* physical not logical column numbers; this was changed for future-proofing.
23892391
*
23902392
* The internal representation is a palloc'd int array of 0-based physical
23912393
* attnums.
@@ -2416,12 +2418,32 @@ validate_pkattnums(Relation rel,
24162418
for (i=0;i<pknumatts_arg;i++)
24172419
{
24182420
intpkattnum=pkattnums_arg->values[i];
2421+
intlnum;
2422+
intj;
24192423

2420-
if (pkattnum <=0||pkattnum>natts||
2421-
tupdesc->attrs[pkattnum-1]->attisdropped)
2424+
/* Can throw error immediately if out of range */
2425+
if (pkattnum <=0||pkattnum>natts)
2426+
ereport(ERROR,
2427+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
2428+
errmsg("invalid attribute number %d",pkattnum)));
2429+
2430+
/* Identify which physical column has this logical number */
2431+
lnum=0;
2432+
for (j=0;j<natts;j++)
2433+
{
2434+
/* dropped columns don't count */
2435+
if (tupdesc->attrs[j]->attisdropped)
2436+
continue;
2437+
2438+
if (++lnum==pkattnum)
2439+
break;
2440+
}
2441+
2442+
if (j<natts)
2443+
(*pkattnums)[i]=j;
2444+
else
24222445
ereport(ERROR,
24232446
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
24242447
errmsg("invalid attribute number %d",pkattnum)));
2425-
(*pkattnums)[i]=pkattnum-1;
24262448
}
24272449
}

‎contrib/dblink/expected/dblink.out

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -903,21 +903,21 @@ ALTER TABLE test_dropped
903903
DROP COLUMN col2,
904904
ADD COLUMN col3 VARCHAR(10) NOT NULL DEFAULT 'foo',
905905
ADD COLUMN col4 INT NOT NULL DEFAULT 42;
906-
SELECT dblink_build_sql_insert('test_dropped', '2', 1,
906+
SELECT dblink_build_sql_insert('test_dropped', '1', 1,
907907
ARRAY['1'::TEXT], ARRAY['2'::TEXT]);
908908
dblink_build_sql_insert
909909
---------------------------------------------------------------------------
910910
INSERT INTO test_dropped(id,col2b,col3,col4) VALUES('2','113','foo','42')
911911
(1 row)
912912

913-
SELECT dblink_build_sql_update('test_dropped', '2', 1,
913+
SELECT dblink_build_sql_update('test_dropped', '1', 1,
914914
ARRAY['1'::TEXT], ARRAY['2'::TEXT]);
915915
dblink_build_sql_update
916916
-------------------------------------------------------------------------------------------
917917
UPDATE test_dropped SET id = '2', col2b = '113', col3 = 'foo', col4 = '42' WHERE id = '2'
918918
(1 row)
919919

920-
SELECT dblink_build_sql_delete('test_dropped', '2', 1,
920+
SELECT dblink_build_sql_delete('test_dropped', '1', 1,
921921
ARRAY['2'::TEXT]);
922922
dblink_build_sql_delete
923923
-----------------------------------------

‎contrib/dblink/sql/dblink.sql

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -430,11 +430,11 @@ ALTER TABLE test_dropped
430430
ADD COLUMN col3VARCHAR(10)NOT NULL DEFAULT'foo',
431431
ADD COLUMN col4INTNOT NULL DEFAULT42;
432432

433-
SELECT dblink_build_sql_insert('test_dropped','2',1,
433+
SELECT dblink_build_sql_insert('test_dropped','1',1,
434434
ARRAY['1'::TEXT], ARRAY['2'::TEXT]);
435435

436-
SELECT dblink_build_sql_update('test_dropped','2',1,
436+
SELECT dblink_build_sql_update('test_dropped','1',1,
437437
ARRAY['1'::TEXT], ARRAY['2'::TEXT]);
438438

439-
SELECT dblink_build_sql_delete('test_dropped','2',1,
439+
SELECT dblink_build_sql_delete('test_dropped','1',1,
440440
ARRAY['2'::TEXT]);

‎doc/src/sgml/dblink.sgml

Lines changed: 52 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/dblink.sgml,v 1.12 2010/06/07 02:01:08 itagaki Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/dblink.sgml,v 1.13 2010/06/15 20:29:01 tgl Exp $ -->
22

33
<sect1 id="dblink">
44
<title>dblink</title>
@@ -1294,9 +1294,9 @@ SELECT *
12941294
<title>Description</title>
12951295

12961296
<para>
1297-
<function>dblink_get_notify</> retrieves notifications on either
1297+
<function>dblink_get_notify</> retrieves notifications on either
12981298
the unnamed connection, or on a named connection if specified.
1299-
To receive notifications via dblink, <function>LISTEN</> must
1299+
To receive notifications via dblink, <function>LISTEN</> must
13001300
first be issued, using <function>dblink_exec</>.
13011301
For details see <xref linkend="sql-listen"> and <xref linkend="sql-notify">.
13021302
</para>
@@ -1620,6 +1620,10 @@ SELECT * FROM dblink_get_notify();
16201620
<programlisting>
16211621
CREATE TYPE dblink_pkey_results AS (position int, colname text);
16221622
</programlisting>
1623+
1624+
The <literal>position</> column simply runs from 1 to <replaceable>N</>;
1625+
it is the number of the field within the primary key, not the number
1626+
within the table's columns.
16231627
</para>
16241628
</refsect1>
16251629

@@ -1659,7 +1663,7 @@ test=# select * from dblink_get_pkey('foobar');
16591663
<synopsis>
16601664
dblink_build_sql_insert(text relname,
16611665
int2vector primary_key_attnums,
1662-
int2 num_primary_key_atts,
1666+
integer num_primary_key_atts,
16631667
text[] src_pk_att_vals_array,
16641668
text[] tgt_pk_att_vals_array) returns text
16651669
</synopsis>
@@ -1745,6 +1749,20 @@ test=# select * from dblink_get_pkey('foobar');
17451749
<para>Returns the requested SQL statement as text.</para>
17461750
</refsect1>
17471751

1752+
<refsect1>
1753+
<title>Notes</title>
1754+
1755+
<para>
1756+
As of <productname>PostgreSQL</> 9.0, the attribute numbers in
1757+
<parameter>primary_key_attnums</parameter> are interpreted as logical
1758+
column numbers, corresponding to the column's position in
1759+
<literal>SELECT * FROM relname</>. Previous versions interpreted the
1760+
numbers as physical column positions. There is a difference if any
1761+
column(s) to the left of the indicated column have been dropped during
1762+
the lifetime of the table.
1763+
</para>
1764+
</refsect1>
1765+
17481766
<refsect1>
17491767
<title>Example</title>
17501768

@@ -1775,7 +1793,7 @@ test=# select * from dblink_get_pkey('foobar');
17751793
<synopsis>
17761794
dblink_build_sql_delete(text relname,
17771795
int2vector primary_key_attnums,
1778-
int2 num_primary_key_atts,
1796+
integer num_primary_key_atts,
17791797
text[] tgt_pk_att_vals_array) returns text
17801798
</synopsis>
17811799
</refsynopsisdiv>
@@ -1845,6 +1863,20 @@ test=# select * from dblink_get_pkey('foobar');
18451863
<para>Returns the requested SQL statement as text.</para>
18461864
</refsect1>
18471865

1866+
<refsect1>
1867+
<title>Notes</title>
1868+
1869+
<para>
1870+
As of <productname>PostgreSQL</> 9.0, the attribute numbers in
1871+
<parameter>primary_key_attnums</parameter> are interpreted as logical
1872+
column numbers, corresponding to the column's position in
1873+
<literal>SELECT * FROM relname</>. Previous versions interpreted the
1874+
numbers as physical column positions. There is a difference if any
1875+
column(s) to the left of the indicated column have been dropped during
1876+
the lifetime of the table.
1877+
</para>
1878+
</refsect1>
1879+
18481880
<refsect1>
18491881
<title>Example</title>
18501882

@@ -1875,7 +1907,7 @@ test=# select * from dblink_get_pkey('foobar');
18751907
<synopsis>
18761908
dblink_build_sql_update(text relname,
18771909
int2vector primary_key_attnums,
1878-
int2 num_primary_key_atts,
1910+
integer num_primary_key_atts,
18791911
text[] src_pk_att_vals_array,
18801912
text[] tgt_pk_att_vals_array) returns text
18811913
</synopsis>
@@ -1964,6 +1996,20 @@ test=# select * from dblink_get_pkey('foobar');
19641996
<para>Returns the requested SQL statement as text.</para>
19651997
</refsect1>
19661998

1999+
<refsect1>
2000+
<title>Notes</title>
2001+
2002+
<para>
2003+
As of <productname>PostgreSQL</> 9.0, the attribute numbers in
2004+
<parameter>primary_key_attnums</parameter> are interpreted as logical
2005+
column numbers, corresponding to the column's position in
2006+
<literal>SELECT * FROM relname</>. Previous versions interpreted the
2007+
numbers as physical column positions. There is a difference if any
2008+
column(s) to the left of the indicated column have been dropped during
2009+
the lifetime of the table.
2010+
</para>
2011+
</refsect1>
2012+
19672013
<refsect1>
19682014
<title>Example</title>
19692015

0 commit comments

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