diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql new file mode 100644 index 2307586..cd4972c *** a/src/backend/catalog/information_schema.sql --- b/src/backend/catalog/information_schema.sql *************** CREATE VIEW columns AS *** 731,737 **** CAST(null AS character_data) AS generation_expression, CAST(CASE WHEN c.relkind = 'r' OR ! (c.relkind = 'v' AND pg_view_is_updatable(c.oid)) THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum) --- 731,737 ---- CAST(null AS character_data) AS generation_expression, CAST(CASE WHEN c.relkind = 'r' OR ! (c.relkind IN ('v', 'f') AND pg_column_is_updatable(c.oid, a.attnum, false)) THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum) *************** CREATE VIEW tables AS *** 1895,1901 **** CAST(t.typname AS sql_identifier) AS user_defined_type_name, CAST(CASE WHEN c.relkind = 'r' OR ! (c.relkind = 'v' AND pg_view_is_insertable(c.oid)) THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into, CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed, --- 1895,1902 ---- CAST(t.typname AS sql_identifier) AS user_defined_type_name, CAST(CASE WHEN c.relkind = 'r' OR ! (c.relkind IN ('v', 'f') AND ! pg_relation_is_updatable(c.oid, false) & 8 = 8) THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into, CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed, *************** CREATE VIEW views AS *** 2494,2504 **** CAST('NONE' AS character_data) AS check_option, CAST( ! CASE WHEN pg_view_is_updatable(c.oid) THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable, CAST( ! CASE WHEN pg_view_is_insertable(c.oid) THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into, CAST( --- 2495,2505 ---- CAST('NONE' AS character_data) AS check_option, CAST( ! CASE WHEN pg_relation_is_updatable(c.oid, false) & 20 = 20 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable, CAST( ! CASE WHEN pg_relation_is_updatable(c.oid, false) & 8 = 8 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into, CAST( diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c new file mode 100644 index 83f26e3..f0f327a *** a/src/backend/rewrite/rewriteHandler.c --- b/src/backend/rewrite/rewriteHandler.c *************** view_is_auto_updatable(Relation view) *** 2014,2019 **** --- 2014,2020 ---- base_rte = rt_fetch(rtr->rtindex, viewquery->rtable); if (base_rte->rtekind != RTE_RELATION || (base_rte->relkind != RELKIND_RELATION && + base_rte->relkind != RELKIND_FOREIGN_TABLE && base_rte->relkind != RELKIND_VIEW)) return gettext_noop("Views that do not select from a single table or view are not automatically updatable."); *************** view_is_auto_updatable(Relation view) *** 2058,2064 **** /* ! * relation_is_updatable - test if the specified relation is updatable. * * This is used for the information_schema views, which have separate concepts * of "updatable" and "trigger updatable". A relation is "updatable" if it --- 2059,2066 ---- /* ! * relation_is_updatable - determine which update events the specified ! * relation supports. * * This is used for the information_schema views, which have separate concepts * of "updatable" and "trigger updatable". A relation is "updatable" if it *************** view_is_auto_updatable(Relation view) *** 2074,2089 **** * In the case of an automatically updatable view, the base relation must * also be updatable. * ! * reloid is the pg_class OID to examine. req_events is a bitmask of ! * rule event numbers; the relation is considered rule-updatable if it has ! * all the specified rules. (We do it this way so that we can test for ! * UPDATE plus DELETE rules in a single call.) */ ! bool ! relation_is_updatable(Oid reloid, int req_events) { Relation rel; RuleLock *rulelocks; rel = try_relation_open(reloid, AccessShareLock); --- 2076,2094 ---- * In the case of an automatically updatable view, the base relation must * also be updatable. * ! * reloid is the pg_class OID to examine. include_triggers determines whether ! * to treat trigger-updatable as updatable, which is useful for clients that ! * only care if data-modifying SQL will work. The return value is a bitmask ! * of rule event numbers indicating which of the INSERT, UPDATE and DELETE ! * operations are supported. (We do it this way so that we can test for ! * UPDATE plus DELETE support in a single call.) */ ! int ! relation_is_updatable(Oid reloid, bool include_triggers) { Relation rel; RuleLock *rulelocks; + int events = 0; rel = try_relation_open(reloid, AccessShareLock); *************** relation_is_updatable(Oid reloid, int re *** 2094,2106 **** * deleted according to a SnapshotNow probe. */ if (rel == NULL) ! return false; /* Look for unconditional DO INSTEAD rules, and note supported events */ rulelocks = rel->rd_rules; if (rulelocks != NULL) { - int events = 0; int i; for (i = 0; i < rulelocks->numLocks; i++) --- 2099,2119 ---- * deleted according to a SnapshotNow probe. */ if (rel == NULL) ! return 0; ! ! /* If the relation is a table, it is always updatable */ ! #define ALL_EVENTS ((1 << CMD_INSERT) | (1 << CMD_UPDATE) | (1 << CMD_DELETE)) ! ! if (rel->rd_rel->relkind == RELKIND_RELATION) ! { ! relation_close(rel, AccessShareLock); ! return ALL_EVENTS; ! } /* Look for unconditional DO INSTEAD rules, and note supported events */ rulelocks = rel->rd_rules; if (rulelocks != NULL) { int i; for (i = 0; i < rulelocks->numLocks; i++) *************** relation_is_updatable(Oid reloid, int re *** 2108,2125 **** if (rulelocks->rules[i]->isInstead && rulelocks->rules[i]->qual == NULL) { ! events |= 1 << rulelocks->rules[i]->event; } } ! /* If we have all rules needed, say "yes" */ ! if ((events & req_events) == req_events) { relation_close(rel, AccessShareLock); ! return true; } } /* Check if this is an automatically updatable view */ if (rel->rd_rel->relkind == RELKIND_VIEW && view_is_auto_updatable(rel) == NULL) --- 2121,2174 ---- if (rulelocks->rules[i]->isInstead && rulelocks->rules[i]->qual == NULL) { ! events |= ((1 << rulelocks->rules[i]->event) & ALL_EVENTS); } } ! /* If we have all rules needed, return now */ ! if (events == ALL_EVENTS) { relation_close(rel, AccessShareLock); ! return events; } } + /* Similarly look for INSTEAD OF triggers, if they are to be included */ + if (include_triggers) + { + TriggerDesc *trigDesc = rel->trigdesc; + + if (trigDesc && trigDesc->trig_insert_instead_row) + events |= (1 << CMD_INSERT); + if (trigDesc && trigDesc->trig_update_instead_row) + events |= (1 << CMD_UPDATE); + if (trigDesc && trigDesc->trig_delete_instead_row) + events |= (1 << CMD_DELETE); + + /* Return if we now support all update events */ + if (events == ALL_EVENTS) + { + relation_close(rel, AccessShareLock); + return events; + } + } + + /* If this is a foreign table, check which update events it supports */ + if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + { + FdwRoutine *fdwroutine = GetFdwRoutineForRelation(rel, false); + + if (fdwroutine->IsForeignRelUpdatable != NULL) + { + events |= (fdwroutine->IsForeignRelUpdatable(reloid, + include_triggers) + & ALL_EVENTS); + } + + relation_close(rel, AccessShareLock); + return events; + } + /* Check if this is an automatically updatable view */ if (rel->rd_rel->relkind == RELKIND_VIEW && view_is_auto_updatable(rel) == NULL) *************** relation_is_updatable(Oid reloid, int re *** 2138,2157 **** { /* Tables are always updatable */ relation_close(rel, AccessShareLock); ! return true; } else { /* Do a recursive check for any other kind of base relation */ baseoid = base_rte->relid; relation_close(rel, AccessShareLock); ! return relation_is_updatable(baseoid, req_events); } } ! /* If we reach here, the relation is not updatable */ relation_close(rel, AccessShareLock); ! return false; } --- 2187,2206 ---- { /* Tables are always updatable */ relation_close(rel, AccessShareLock); ! return ALL_EVENTS; } else { /* Do a recursive check for any other kind of base relation */ baseoid = base_rte->relid; relation_close(rel, AccessShareLock); ! return relation_is_updatable(baseoid, include_triggers); } } ! /* If we reach here, the relation may support some update commands */ relation_close(rel, AccessShareLock); ! return events; } diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c new file mode 100644 index 4e38d7c..c1f70c4 *** a/src/backend/utils/adt/misc.c --- b/src/backend/utils/adt/misc.c *************** *** 25,30 **** --- 25,31 ---- #include "catalog/pg_type.h" #include "commands/dbcommands.h" #include "common/relpath.h" + #include "foreign/fdwapi.h" #include "funcapi.h" #include "miscadmin.h" #include "parser/keywords.h" *************** *** 37,42 **** --- 38,44 ---- #include "utils/lsyscache.h" #include "tcop/tcopprot.h" #include "utils/builtins.h" + #include "utils/rel.h" #include "utils/timestamp.h" #define atooid(x) ((Oid) strtoul((x), NULL, 10)) *************** pg_collation_for(PG_FUNCTION_ARGS) *** 527,557 **** } /* ! * information_schema support functions * ! * Test whether a view (identified by pg_class OID) is insertable-into or ! * updatable. The latter requires delete capability too. This is an ! * artifact of the way the SQL standard defines the information_schema views: ! * if we defined separate functions for update and delete, we'd double the ! * work required to compute the view columns. * ! * These rely on relation_is_updatable(), which is in rewriteHandler.c. */ Datum ! pg_view_is_insertable(PG_FUNCTION_ARGS) { ! Oid viewoid = PG_GETARG_OID(0); ! int req_events = (1 << CMD_INSERT); ! PG_RETURN_BOOL(relation_is_updatable(viewoid, req_events)); } Datum ! pg_view_is_updatable(PG_FUNCTION_ARGS) { ! Oid viewoid = PG_GETARG_OID(0); ! int req_events = (1 << CMD_UPDATE) | (1 << CMD_DELETE); ! PG_RETURN_BOOL(relation_is_updatable(viewoid, req_events)); } --- 529,608 ---- } + /* ---------------------------------------------------------------------- + * Information_schema support functions. + * + * These rely on relation_is_updatable(), which is in rewriteHandler.c. + * ---------------------------------------------------------------------- + */ + /* ! * Determine which update events are supported by a relation (identified by ! * pg_class OID). * ! * This may optionally include or exclude INSTEAD OF triggers from ! * consideration --- the information_schema views need to exclude such ! * triggers but other client applications may want to include them to ! * determine whether a given relation actually supports INSERT, UPDATE and ! * DELETE operations. * ! * The return value is a bitmask indicating which of the INSERT, UPDATE and ! * DELETE operations are supported. This works for all relation kinds, ! * although it is typically only called for views and foreign tables, since ! * the result is trivial for other relkinds. */ Datum ! pg_relation_is_updatable(PG_FUNCTION_ARGS) { ! Oid reloid = PG_GETARG_OID(0); ! bool include_triggers = PG_GETARG_BOOL(1); ! PG_RETURN_INT32(relation_is_updatable(reloid, include_triggers)); } + /* + * Test whether a column (identified by pg_class OID and attnum) is updatable. + * + * This is used in information_schema.columns, and it supports all kinds of + * relations although we only actually use it for views and foreign tables + * since the other relkinds are trivial. + */ Datum ! pg_column_is_updatable(PG_FUNCTION_ARGS) { ! Oid reloid = PG_GETARG_OID(0); ! int attnum = PG_GETARG_INT32(1); ! bool include_triggers = PG_GETARG_BOOL(2); ! Relation rel; ! bool updatable; ! int events; ! rel = try_relation_open(reloid, AccessShareLock); ! if (rel == NULL) ! PG_RETURN_BOOL(false); ! ! if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) ! { ! /* ! * For foreign tables, ask the FDW if the column is updatable ! */ ! FdwRoutine *fdwroutine = GetFdwRoutineForRelation(rel, false); ! ! updatable = fdwroutine->IsForeignColUpdatable != NULL && ! fdwroutine->IsForeignColUpdatable(reloid, attnum, ! include_triggers); ! } ! else ! { ! /* ! * At the moment, for all non-foreign relations, every column is ! * updatable if the relation supports UPDATE ! */ ! events = relation_is_updatable(rel->rd_id, include_triggers); ! updatable = events & (1 << CMD_UPDATE); ! } ! ! relation_close(rel, AccessShareLock); ! ! PG_RETURN_BOOL(updatable); } diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h new file mode 100644 index 685b9c7..b4dd8b3 *** a/src/include/catalog/pg_proc.h --- b/src/include/catalog/pg_proc.h *************** DESCR("type of the argument"); *** 1976,1985 **** DATA(insert OID = 3162 ( pg_collation_for PGNSP PGUID 12 1 0 0 0 f f f f f f s 1 0 25 "2276" _null_ _null_ _null_ _null_ pg_collation_for _null_ _null_ _null_ )); DESCR("collation of the argument; implementation of the COLLATION FOR expression"); ! DATA(insert OID = 3842 ( pg_view_is_insertable PGNSP PGUID 12 10 0 0 0 f f f f t f s 1 0 16 "26" _null_ _null_ _null_ _null_ pg_view_is_insertable _null_ _null_ _null_ )); ! DESCR("is a view insertable-into"); ! DATA(insert OID = 3843 ( pg_view_is_updatable PGNSP PGUID 12 10 0 0 0 f f f f t f s 1 0 16 "26" _null_ _null_ _null_ _null_ pg_view_is_updatable _null_ _null_ _null_ )); ! DESCR("is a view updatable"); /* Deferrable unique constraint trigger */ DATA(insert OID = 1250 ( unique_key_recheck PGNSP PGUID 12 1 0 0 0 f f f f t f v 0 0 2279 "" _null_ _null_ _null_ _null_ unique_key_recheck _null_ _null_ _null_ )); --- 1976,1985 ---- DATA(insert OID = 3162 ( pg_collation_for PGNSP PGUID 12 1 0 0 0 f f f f f f s 1 0 25 "2276" _null_ _null_ _null_ _null_ pg_collation_for _null_ _null_ _null_ )); DESCR("collation of the argument; implementation of the COLLATION FOR expression"); ! DATA(insert OID = 3842 ( pg_relation_is_updatable PGNSP PGUID 12 10 0 0 0 f f f f t f s 2 0 23 "26 16" _null_ _null_ _null_ _null_ pg_relation_is_updatable _null_ _null_ _null_ )); ! DESCR("is a relation updatable"); ! DATA(insert OID = 3843 ( pg_column_is_updatable PGNSP PGUID 12 10 0 0 0 f f f f t f s 3 0 16 "26 23 16" _null_ _null_ _null_ _null_ pg_column_is_updatable _null_ _null_ _null_ )); ! DESCR("is a column updatable"); /* Deferrable unique constraint trigger */ DATA(insert OID = 1250 ( unique_key_recheck PGNSP PGUID 12 1 0 0 0 f f f f t f v 0 0 2279 "" _null_ _null_ _null_ _null_ unique_key_recheck _null_ _null_ _null_ )); diff --git a/src/include/foreign/fdwapi.h b/src/include/foreign/fdwapi.h new file mode 100644 index 485eee3..a03202e *** a/src/include/foreign/fdwapi.h --- b/src/include/foreign/fdwapi.h *************** typedef void (*ReScanForeignScan_functio *** 47,52 **** --- 47,59 ---- typedef void (*EndForeignScan_function) (ForeignScanState *node); + typedef int (*IsForeignRelUpdatable_function) (Oid foreigntableid, + bool include_triggers); + + typedef bool (*IsForeignColUpdatable_function) (Oid foreigntableid, + int attnum, + bool include_triggers); + typedef void (*AddForeignUpdateTargets_function) (Query *parsetree, RangeTblEntry *target_rte, Relation target_relation); *************** typedef struct FdwRoutine *** 127,132 **** --- 134,141 ---- */ /* Functions for updating foreign tables */ + IsForeignRelUpdatable_function IsForeignRelUpdatable; + IsForeignColUpdatable_function IsForeignColUpdatable; AddForeignUpdateTargets_function AddForeignUpdateTargets; PlanForeignModify_function PlanForeignModify; BeginForeignModify_function BeginForeignModify; diff --git a/src/include/rewrite/rewriteHandler.h b/src/include/rewrite/rewriteHandler.h new file mode 100644 index 5983315..44e682c *** a/src/include/rewrite/rewriteHandler.h --- b/src/include/rewrite/rewriteHandler.h *************** extern List *QueryRewrite(Query *parsetr *** 21,26 **** extern void AcquireRewriteLocks(Query *parsetree, bool forUpdatePushedDown); extern Node *build_column_default(Relation rel, int attrno); ! extern bool relation_is_updatable(Oid reloid, int req_events); #endif /* REWRITEHANDLER_H */ --- 21,26 ---- extern void AcquireRewriteLocks(Query *parsetree, bool forUpdatePushedDown); extern Node *build_column_default(Relation rel, int attrno); ! extern int relation_is_updatable(Oid reloid, bool include_triggers); #endif /* REWRITEHANDLER_H */ diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h new file mode 100644 index 15b60ab..218e645 *** a/src/include/utils/builtins.h --- b/src/include/utils/builtins.h *************** extern Datum pg_sleep(PG_FUNCTION_ARGS); *** 485,492 **** extern Datum pg_get_keywords(PG_FUNCTION_ARGS); extern Datum pg_typeof(PG_FUNCTION_ARGS); extern Datum pg_collation_for(PG_FUNCTION_ARGS); ! extern Datum pg_view_is_insertable(PG_FUNCTION_ARGS); ! extern Datum pg_view_is_updatable(PG_FUNCTION_ARGS); /* oid.c */ extern Datum oidin(PG_FUNCTION_ARGS); --- 485,492 ---- extern Datum pg_get_keywords(PG_FUNCTION_ARGS); extern Datum pg_typeof(PG_FUNCTION_ARGS); extern Datum pg_collation_for(PG_FUNCTION_ARGS); ! extern Datum pg_relation_is_updatable(PG_FUNCTION_ARGS); ! extern Datum pg_column_is_updatable(PG_FUNCTION_ARGS); /* oid.c */ extern Datum oidin(PG_FUNCTION_ARGS); diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out new file mode 100644 index ecb61e0..3adba33 *** a/src/test/regress/expected/updatable_views.out --- b/src/test/regress/expected/updatable_views.out *************** SELECT table_name, column_name, is_updat *** 468,477 **** ORDER BY table_name, ordinal_position; table_name | column_name | is_updatable ------------+-------------+-------------- ! rw_view1 | a | NO ! rw_view1 | b | NO ! rw_view2 | a | NO ! rw_view2 | b | NO (4 rows) CREATE RULE rw_view1_del_rule AS ON DELETE TO rw_view1 --- 468,477 ---- ORDER BY table_name, ordinal_position; table_name | column_name | is_updatable ------------+-------------+-------------- ! rw_view1 | a | YES ! rw_view1 | b | YES ! rw_view2 | a | YES ! rw_view2 | b | YES (4 rows) CREATE RULE rw_view1_del_rule AS ON DELETE TO rw_view1