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

Commitdc3eb56

Browse files
committed
Improve updatability checking for views and foreign tables.
Extend the FDW API (which we already changed for 9.3) so that an FDW canreport whether specific foreign tables are insertable/updatable/deletable.The default assumption continues to be that they're updatable if therelevant executor callback function is supplied by the FDW, but finergranularity is now possible. As a test case, add an "updatable" option tocontrib/postgres_fdw.This patch also fixes the information_schema views, which previously didnot think that foreign tables were ever updatable, and fixesview_is_auto_updatable() so that a view on a foreign table can beauto-updatable.initdb forced due to changes in information_schema views and the functionsthey rely on. This is a bit unfortunate to do post-beta1, but if we don'tchange this now then we'll have another API break for FDWs when we dochange it.Dean Rasheed, somewhat editorialized on by Tom Lane
1 parent78ed8e0 commitdc3eb56

File tree

15 files changed

+272
-54
lines changed

15 files changed

+272
-54
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -79,6 +79,7 @@ ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
7979
-- configure options
8080
ALTER SERVER testserver1 OPTIONS (
8181
use_remote_estimate 'false',
82+
updatable 'true',
8283
fdw_startup_cost '123.456',
8384
fdw_tuple_cost '0.123',
8485
service 'value',

‎contrib/postgres_fdw/option.c

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -106,9 +106,10 @@ postgres_fdw_validator(PG_FUNCTION_ARGS)
106106
/*
107107
* Validate option value, when we can do so without any context.
108108
*/
109-
if (strcmp(def->defname,"use_remote_estimate")==0)
109+
if (strcmp(def->defname,"use_remote_estimate")==0||
110+
strcmp(def->defname,"updatable")==0)
110111
{
111-
/*use_remote_estimate accepts only boolean values */
112+
/*these accept only boolean values */
112113
(void)defGetBoolean(def);
113114
}
114115
elseif (strcmp(def->defname,"fdw_startup_cost")==0||
@@ -151,6 +152,9 @@ InitPgFdwOptions(void)
151152
/* cost factors */
152153
{"fdw_startup_cost",ForeignServerRelationId, false},
153154
{"fdw_tuple_cost",ForeignServerRelationId, false},
155+
/* updatable is available on both server and table */
156+
{"updatable",ForeignServerRelationId, false},
157+
{"updatable",ForeignTableRelationId, false},
154158
{NULL,InvalidOid, false}
155159
};
156160

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -277,6 +277,7 @@ static TupleTableSlot *postgresExecForeignDelete(EState *estate,
277277
TupleTableSlot*planSlot);
278278
staticvoidpostgresEndForeignModify(EState*estate,
279279
ResultRelInfo*resultRelInfo);
280+
staticintpostgresIsForeignRelUpdatable(Relationrel);
280281
staticvoidpostgresExplainForeignScan(ForeignScanState*node,
281282
ExplainState*es);
282283
staticvoidpostgresExplainForeignModify(ModifyTableState*mtstate,
@@ -355,6 +356,7 @@ postgres_fdw_handler(PG_FUNCTION_ARGS)
355356
routine->ExecForeignUpdate=postgresExecForeignUpdate;
356357
routine->ExecForeignDelete=postgresExecForeignDelete;
357358
routine->EndForeignModify=postgresEndForeignModify;
359+
routine->IsForeignRelUpdatable=postgresIsForeignRelUpdatable;
358360

359361
/* Support functions for EXPLAIN */
360362
routine->ExplainForeignScan=postgresExplainForeignScan;
@@ -1596,6 +1598,51 @@ postgresEndForeignModify(EState *estate,
15961598
fmstate->conn=NULL;
15971599
}
15981600

1601+
/*
1602+
* postgresIsForeignRelUpdatable
1603+
*Determine whether a foreign table supports INSERT, UPDATE and/or
1604+
*DELETE.
1605+
*/
1606+
staticint
1607+
postgresIsForeignRelUpdatable(Relationrel)
1608+
{
1609+
boolupdatable;
1610+
ForeignTable*table;
1611+
ForeignServer*server;
1612+
ListCell*lc;
1613+
1614+
/*
1615+
* By default, all postgres_fdw foreign tables are assumed updatable. This
1616+
* can be overridden by a per-server setting, which in turn can be
1617+
* overridden by a per-table setting.
1618+
*/
1619+
updatable= true;
1620+
1621+
table=GetForeignTable(RelationGetRelid(rel));
1622+
server=GetForeignServer(table->serverid);
1623+
1624+
foreach(lc,server->options)
1625+
{
1626+
DefElem*def= (DefElem*)lfirst(lc);
1627+
1628+
if (strcmp(def->defname,"updatable")==0)
1629+
updatable=defGetBoolean(def);
1630+
}
1631+
foreach(lc,table->options)
1632+
{
1633+
DefElem*def= (DefElem*)lfirst(lc);
1634+
1635+
if (strcmp(def->defname,"updatable")==0)
1636+
updatable=defGetBoolean(def);
1637+
}
1638+
1639+
/*
1640+
* Currently "updatable" means support for INSERT, UPDATE and DELETE.
1641+
*/
1642+
returnupdatable ?
1643+
(1 <<CMD_INSERT) | (1 <<CMD_UPDATE) | (1 <<CMD_DELETE) :0;
1644+
}
1645+
15991646
/*
16001647
* postgresExplainForeignScan
16011648
*Produce extra output for EXPLAIN of a ForeignScan on a foreign table

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -88,6 +88,7 @@ ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
8888
-- configure options
8989
ALTER SERVER testserver1 OPTIONS (
9090
use_remote_estimate'false',
91+
updatable'true',
9192
fdw_startup_cost'123.456',
9293
fdw_tuple_cost'0.123',
9394
service'value',

‎doc/src/sgml/fdwhandler.sgml

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -565,6 +565,33 @@ EndForeignModify (EState *estate,
565565
<literal>NULL</>, no action is taken during executor shutdown.
566566
</para>
567567

568+
<para>
569+
<programlisting>
570+
int
571+
IsForeignRelUpdatable (Relation rel);
572+
</programlisting>
573+
574+
Report which update operations the specified foreign table supports.
575+
The return value should be a bitmask of rule event numbers indicating
576+
which operations are supported by the foreign table, using the
577+
<literal>CmdType</> enumeration; that is,
578+
<literal>(1 << CMD_UPDATE) = 4</> for <command>UPDATE</>,
579+
<literal>(1 << CMD_INSERT) = 8</> for <command>INSERT</>, and
580+
<literal>(1 << CMD_DELETE) = 16</> for <command>DELETE</>.
581+
</para>
582+
583+
<para>
584+
If the <function>IsForeignRelUpdatable</> pointer is set to
585+
<literal>NULL</>, foreign tables are assumed to be insertable, updatable,
586+
or deletable if the FDW provides <function>ExecForeignInsert</>,
587+
<function>ExecForeignUpdate</>, or <function>ExecForeignDelete</>
588+
respectively. This function is only needed if the FDW supports some
589+
tables that are updatable and some that are not. (Even then, it's
590+
permissible to throw an error in the execution routine instead of
591+
checking in this function. However, this function is used to determine
592+
updatability for display in the <literal>information_schema</> views.)
593+
</para>
594+
568595
</sect2>
569596

570597
<sect2 id="fdw-callbacks-explain">

‎doc/src/sgml/postgres-fdw.sgml

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -254,6 +254,43 @@
254254
</para>
255255

256256
</sect3>
257+
258+
<sect3>
259+
<title>Updatability Options</title>
260+
261+
<para>
262+
By default all foreign tables using <filename>postgres_fdw</> are assumed
263+
to be updatable. This may be overridden using the following option:
264+
</para>
265+
266+
<variablelist>
267+
268+
<varlistentry>
269+
<term><literal>updatable</literal></term>
270+
<listitem>
271+
<para>
272+
This option controls whether <filename>postgres_fdw</> allows foreign
273+
tables to be modified using <command>INSERT</>, <command>UPDATE</> and
274+
<command>DELETE</> commands. It can be specified for a foreign table
275+
or a foreign server. A table-level option overrides a server-level
276+
option.
277+
The default is <literal>true</>.
278+
</para>
279+
280+
<para>
281+
Of course, if the remote table is not in fact updatable, an error
282+
would occur anyway. Use of this option primarily allows the error to
283+
be thrown locally without querying the remote server. Note however
284+
that the <literal>information_schema</> views will report a
285+
<filename>postgres_fdw</> foreign table to be updatable (or not)
286+
according to the setting of this option, without any check of the
287+
remote server.
288+
</para>
289+
</listitem>
290+
</varlistentry>
291+
292+
</variablelist>
293+
</sect3>
257294
</sect2>
258295

259296
<sect2>

‎src/backend/catalog/information_schema.sql

Lines changed: 11 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -731,7 +731,8 @@ CREATE VIEW columns AS
731731
CAST(nullAS character_data)AS generation_expression,
732732

733733
CAST(CASE WHENc.relkind='r'OR
734-
(c.relkind='v'AND pg_view_is_updatable(c.oid))
734+
(c.relkindIN ('v','f')AND
735+
pg_column_is_updatable(c.oid,a.attnum, false))
735736
THEN'YES' ELSE'NO' ENDAS yes_or_no)AS is_updatable
736737

737738
FROM (pg_attribute aLEFT JOIN pg_attrdef adON attrelid= adrelidAND attnum= adnum)
@@ -1895,7 +1896,9 @@ CREATE VIEW tables AS
18951896
CAST(t.typnameAS sql_identifier)AS user_defined_type_name,
18961897

18971898
CAST(CASE WHENc.relkind='r'OR
1898-
(c.relkind='v'AND pg_view_is_insertable(c.oid))
1899+
(c.relkindIN ('v','f')AND
1900+
-- 1 << CMD_INSERT
1901+
pg_relation_is_updatable(c.oid, false) &8=8)
18991902
THEN'YES' ELSE'NO' ENDAS yes_or_no)AS is_insertable_into,
19001903

19011904
CAST(CASE WHENt.typnameIS NOT NULL THEN'YES' ELSE'NO' ENDAS yes_or_no)AS is_typed,
@@ -2494,11 +2497,15 @@ CREATE VIEW views AS
24942497
CAST('NONE'AS character_data)AS check_option,
24952498

24962499
CAST(
2497-
CASE WHEN pg_view_is_updatable(c.oid) THEN'YES' ELSE'NO' END
2500+
-- (1 << CMD_UPDATE) + (1 << CMD_DELETE)
2501+
CASE WHEN pg_relation_is_updatable(c.oid, false) &20=20
2502+
THEN'YES' ELSE'NO' END
24982503
AS yes_or_no)AS is_updatable,
24992504

25002505
CAST(
2501-
CASE WHEN pg_view_is_insertable(c.oid) THEN'YES' ELSE'NO' END
2506+
-- 1 << CMD_INSERT
2507+
CASE WHEN pg_relation_is_updatable(c.oid, false) &8=8
2508+
THEN'YES' ELSE'NO' END
25022509
AS yes_or_no)AS is_insertable_into,
25032510

25042511
CAST(

‎src/backend/executor/execMain.c

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1015,20 +1015,38 @@ CheckValidResultRel(Relation resultRel, CmdType operation)
10151015
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
10161016
errmsg("cannot insert into foreign table \"%s\"",
10171017
RelationGetRelationName(resultRel))));
1018+
if (fdwroutine->IsForeignRelUpdatable!=NULL&&
1019+
(fdwroutine->IsForeignRelUpdatable(resultRel)& (1 <<CMD_INSERT))==0)
1020+
ereport(ERROR,
1021+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
1022+
errmsg("foreign table \"%s\" does not allow inserts",
1023+
RelationGetRelationName(resultRel))));
10181024
break;
10191025
caseCMD_UPDATE:
10201026
if (fdwroutine->ExecForeignUpdate==NULL)
10211027
ereport(ERROR,
10221028
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
10231029
errmsg("cannot update foreign table \"%s\"",
10241030
RelationGetRelationName(resultRel))));
1031+
if (fdwroutine->IsForeignRelUpdatable!=NULL&&
1032+
(fdwroutine->IsForeignRelUpdatable(resultRel)& (1 <<CMD_UPDATE))==0)
1033+
ereport(ERROR,
1034+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
1035+
errmsg("foreign table \"%s\" does not allow updates",
1036+
RelationGetRelationName(resultRel))));
10251037
break;
10261038
caseCMD_DELETE:
10271039
if (fdwroutine->ExecForeignDelete==NULL)
10281040
ereport(ERROR,
10291041
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
10301042
errmsg("cannot delete from foreign table \"%s\"",
10311043
RelationGetRelationName(resultRel))));
1044+
if (fdwroutine->IsForeignRelUpdatable!=NULL&&
1045+
(fdwroutine->IsForeignRelUpdatable(resultRel)& (1 <<CMD_DELETE))==0)
1046+
ereport(ERROR,
1047+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
1048+
errmsg("foreign table \"%s\" does not allow deletes",
1049+
RelationGetRelationName(resultRel))));
10321050
break;
10331051
default:
10341052
elog(ERROR,"unrecognized CmdType: %d", (int)operation);

0 commit comments

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