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

Commitae025a1

Browse files
committed
Support OID system column in postgres_fdw.
You can use ALTER FOREIGN TABLE SET WITH OIDS on a foreign table, but theoid column read out as zeros, because the postgres_fdw didn't know aboutit. Teach postgres_fdw how to fetch it.Etsuro Fujita, with an additional test case by me.Discussion: <56E90A76.5000503@lab.ntt.co.jp>
1 parent2533ff0 commitae025a1

File tree

4 files changed

+101
-20
lines changed

4 files changed

+101
-20
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 30 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -287,13 +287,14 @@ foreign_expr_walker(Node *node,
287287
/* Var belongs to foreign table */
288288

289289
/*
290-
* System columns other than ctid should not be sent to
291-
* the remote, since we don't make any effort to ensure
292-
* that local and remote values match (tableoid, in
290+
* System columns other than ctidand oidshould not be
291+
*sent tothe remote, since we don't make any effort to
292+
*ensurethat local and remote values match (tableoid, in
293293
* particular, almost certainly doesn't match).
294294
*/
295295
if (var->varattno<0&&
296-
var->varattno!=SelfItemPointerAttributeNumber)
296+
var->varattno!=SelfItemPointerAttributeNumber&&
297+
var->varattno!=ObjectIdAttributeNumber)
297298
return false;
298299

299300
/* Else check the collation */
@@ -913,8 +914,8 @@ deparseTargetList(StringInfo buf,
913914
}
914915

915916
/*
916-
* Add ctid if needed. We currently don't support retrieving any other
917-
* system columns.
917+
* Add ctidand oidif needed. We currently don't support retrieving any
918+
*othersystem columns.
918919
*/
919920
if (bms_is_member(SelfItemPointerAttributeNumber-FirstLowInvalidHeapAttributeNumber,
920921
attrs_used))
@@ -932,6 +933,22 @@ deparseTargetList(StringInfo buf,
932933
*retrieved_attrs=lappend_int(*retrieved_attrs,
933934
SelfItemPointerAttributeNumber);
934935
}
936+
if (bms_is_member(ObjectIdAttributeNumber-FirstLowInvalidHeapAttributeNumber,
937+
attrs_used))
938+
{
939+
if (!first)
940+
appendStringInfoString(buf,", ");
941+
elseif (is_returning)
942+
appendStringInfoString(buf," RETURNING ");
943+
first= false;
944+
945+
if (qualify_col)
946+
ADD_REL_QUALIFIER(buf,rtindex);
947+
appendStringInfoString(buf,"oid");
948+
949+
*retrieved_attrs=lappend_int(*retrieved_attrs,
950+
ObjectIdAttributeNumber);
951+
}
935952

936953
/* Don't generate bad syntax if no undropped columns */
937954
if (first&& !is_returning)
@@ -1574,13 +1591,19 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, PlannerInfo *root,
15741591
{
15751592
RangeTblEntry*rte;
15761593

1594+
/* We support fetching the remote side's CTID and OID. */
15771595
if (varattno==SelfItemPointerAttributeNumber)
15781596
{
1579-
/* We support fetching the remote side's CTID. */
15801597
if (qualify_col)
15811598
ADD_REL_QUALIFIER(buf,varno);
15821599
appendStringInfoString(buf,"ctid");
15831600
}
1601+
elseif (varattno==ObjectIdAttributeNumber)
1602+
{
1603+
if (qualify_col)
1604+
ADD_REL_QUALIFIER(buf,varno);
1605+
appendStringInfoString(buf,"oid");
1606+
}
15841607
elseif (varattno<0)
15851608
{
15861609
/*

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 33 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -124,6 +124,13 @@ CREATE FOREIGN TABLE ft6 (
124124
c2 int NOT NULL,
125125
c3 text
126126
) SERVER loopback2 OPTIONS (schema_name 'S 1', table_name 'T 4');
127+
-- A table with oids. CREATE FOREIGN TABLE doesn't support the
128+
-- WITH OIDS option, but ALTER does.
129+
CREATE FOREIGN TABLE ft_pg_type (
130+
typname name,
131+
typlen smallint
132+
) SERVER loopback OPTIONS (schema_name 'pg_catalog', table_name 'pg_type');
133+
ALTER TABLE ft_pg_type SET WITH OIDS;
127134
-- ===================================================================
128135
-- tests for validator
129136
-- ===================================================================
@@ -173,15 +180,16 @@ ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1', table_name 'T 1');
173180
ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
174181
ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
175182
\det+
176-
List of foreign tables
177-
Schema | Table | Server | FDW Options | Description
178-
--------+-------+-----------+---------------------------------------+-------------
179-
public | ft1 | loopback | (schema_name 'S 1', table_name 'T 1') |
180-
public | ft2 | loopback | (schema_name 'S 1', table_name 'T 1') |
181-
public | ft4 | loopback | (schema_name 'S 1', table_name 'T 3') |
182-
public | ft5 | loopback | (schema_name 'S 1', table_name 'T 4') |
183-
public | ft6 | loopback2 | (schema_name 'S 1', table_name 'T 4') |
184-
(5 rows)
183+
List of foreign tables
184+
Schema | Table | Server | FDW Options | Description
185+
--------+------------+-----------+--------------------------------------------------+-------------
186+
public | ft1 | loopback | (schema_name 'S 1', table_name 'T 1') |
187+
public | ft2 | loopback | (schema_name 'S 1', table_name 'T 1') |
188+
public | ft4 | loopback | (schema_name 'S 1', table_name 'T 3') |
189+
public | ft5 | loopback | (schema_name 'S 1', table_name 'T 4') |
190+
public | ft6 | loopback2 | (schema_name 'S 1', table_name 'T 4') |
191+
public | ft_pg_type | loopback | (schema_name 'pg_catalog', table_name 'pg_type') |
192+
(6 rows)
185193

186194
-- Now we should be able to run ANALYZE.
187195
-- To exercise multiple code paths, we use local stats on ft1
@@ -2485,7 +2493,7 @@ DEALLOCATE st2;
24852493
DEALLOCATE st3;
24862494
DEALLOCATE st4;
24872495
DEALLOCATE st5;
2488-
-- System columns, except ctid, should not be sent to remote
2496+
-- System columns, except ctid and oid, should not be sent to remote
24892497
EXPLAIN (VERBOSE, COSTS OFF)
24902498
SELECT * FROM ft1 t1 WHERE t1.tableoid = 'pg_class'::regclass LIMIT 1;
24912499
QUERY PLAN
@@ -2553,6 +2561,21 @@ SELECT ctid, * FROM ft1 t1 LIMIT 1;
25532561
(0,1) | 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
25542562
(1 row)
25552563

2564+
EXPLAIN (VERBOSE, COSTS OFF)
2565+
SELECT oid, * FROM ft_pg_type WHERE typname = 'int4';
2566+
QUERY PLAN
2567+
----------------------------------------------------------------------------------------------------
2568+
Foreign Scan on public.ft_pg_type
2569+
Output: oid, typname, typlen
2570+
Remote SQL: SELECT typname, typlen, oid FROM pg_catalog.pg_type WHERE ((typname = 'int4'::name))
2571+
(3 rows)
2572+
2573+
SELECT oid, * FROM ft_pg_type WHERE typname = 'int4';
2574+
oid | typname | typlen
2575+
-----+---------+--------
2576+
23 | int4 | 4
2577+
(1 row)
2578+
25562579
-- ===================================================================
25572580
-- used in pl/pgsql function
25582581
-- ===================================================================

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 26 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4374,6 +4374,7 @@ make_tuple_from_result_row(PGresult *res,
43744374
Datum*values;
43754375
bool*nulls;
43764376
ItemPointerctid=NULL;
4377+
Oidoid=InvalidOid;
43774378
ConversionLocationerrpos;
43784379
ErrorContextCallbackerrcallback;
43794380
MemoryContextoldcontext;
@@ -4431,7 +4432,11 @@ make_tuple_from_result_row(PGresult *res,
44314432
else
44324433
valstr=PQgetvalue(res,row,j);
44334434

4434-
/* convert value to internal representation */
4435+
/*
4436+
* convert value to internal representation
4437+
*
4438+
* Note: we ignore system columns other than ctid and oid in result
4439+
*/
44354440
errpos.cur_attno=i;
44364441
if (i>0)
44374442
{
@@ -4446,7 +4451,7 @@ make_tuple_from_result_row(PGresult *res,
44464451
}
44474452
elseif (i==SelfItemPointerAttributeNumber)
44484453
{
4449-
/* ctid--- note we ignore any other system column in result*/
4454+
/* ctid */
44504455
if (valstr!=NULL)
44514456
{
44524457
Datumdatum;
@@ -4455,6 +4460,17 @@ make_tuple_from_result_row(PGresult *res,
44554460
ctid= (ItemPointer)DatumGetPointer(datum);
44564461
}
44574462
}
4463+
elseif (i==ObjectIdAttributeNumber)
4464+
{
4465+
/* oid */
4466+
if (valstr!=NULL)
4467+
{
4468+
Datumdatum;
4469+
4470+
datum=DirectFunctionCall1(oidin,CStringGetDatum(valstr));
4471+
oid=DatumGetObjectId(datum);
4472+
}
4473+
}
44584474
errpos.cur_attno=0;
44594475

44604476
j++;
@@ -4498,6 +4514,12 @@ make_tuple_from_result_row(PGresult *res,
44984514
HeapTupleHeaderSetXmin(tuple->t_data,InvalidTransactionId);
44994515
HeapTupleHeaderSetCmin(tuple->t_data,InvalidTransactionId);
45004516

4517+
/*
4518+
* If we have an OID to return, install it.
4519+
*/
4520+
if (OidIsValid(oid))
4521+
HeapTupleSetOid(tuple,oid);
4522+
45014523
/* Clean up */
45024524
MemoryContextReset(temp_context);
45034525

@@ -4525,6 +4547,8 @@ conversion_error_callback(void *arg)
45254547
attname=NameStr(tupdesc->attrs[errpos->cur_attno-1]->attname);
45264548
elseif (errpos->cur_attno==SelfItemPointerAttributeNumber)
45274549
attname="ctid";
4550+
elseif (errpos->cur_attno==ObjectIdAttributeNumber)
4551+
attname="oid";
45284552

45294553
relname=RelationGetRelationName(errpos->rel);
45304554
}

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -136,6 +136,14 @@ CREATE FOREIGN TABLE ft6 (
136136
c3text
137137
) SERVER loopback2 OPTIONS (schema_name'S 1', table_name'T 4');
138138

139+
-- A table with oids. CREATE FOREIGN TABLE doesn't support the
140+
-- WITH OIDS option, but ALTER does.
141+
CREATE FOREIGN TABLE ft_pg_type (
142+
typname name,
143+
typlensmallint
144+
) SERVER loopback OPTIONS (schema_name'pg_catalog', table_name'pg_type');
145+
ALTERTABLE ft_pg_typeSET WITH OIDS;
146+
139147
-- ===================================================================
140148
-- tests for validator
141149
-- ===================================================================
@@ -577,7 +585,7 @@ DEALLOCATE st3;
577585
DEALLOCATE st4;
578586
DEALLOCATE st5;
579587

580-
-- System columns, except ctid, should not be sent to remote
588+
-- System columns, except ctid and oid, should not be sent to remote
581589
EXPLAIN (VERBOSE, COSTS OFF)
582590
SELECT*FROM ft1 t1WHEREt1.tableoid='pg_class'::regclassLIMIT1;
583591
SELECT*FROM ft1 t1WHEREt1.tableoid='ft1'::regclassLIMIT1;
@@ -590,6 +598,9 @@ SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)';
590598
EXPLAIN (VERBOSE, COSTS OFF)
591599
SELECT ctid,*FROM ft1 t1LIMIT1;
592600
SELECT ctid,*FROM ft1 t1LIMIT1;
601+
EXPLAIN (VERBOSE, COSTS OFF)
602+
SELECToid,*FROM ft_pg_typeWHERE typname='int4';
603+
SELECToid,*FROM ft_pg_typeWHERE typname='int4';
593604

594605
-- ===================================================================
595606
-- used in pl/pgsql function

0 commit comments

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