how to find out whether a view is updatable
I was looking for a way in which the average psql user could learn
whether a view is updatable. I was expecting something in \d, \d+, \dv,
\dv+, or a NOTICE from CREATE VIEW. So far, the only way appears to be
through the information schema or the underlying pg_view_is_updatable
function. Not even pg_views shows anything. Is this intentional or an
oversight?
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Jun 5, 2013 at 12:59 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
I was looking for a way in which the average psql user could learn
whether a view is updatable. I was expecting something in \d, \d+, \dv,
\dv+, or a NOTICE from CREATE VIEW. So far, the only way appears to be
through the information schema or the underlying pg_view_is_updatable
function. Not even pg_views shows anything. Is this intentional or an
oversight?
Just by recalling the thread, an oversight. Having this information in ¥dv+
would
be indeed a nice addition.
--
Michael
On 4 June 2013 23:35, Michael Paquier <michael.paquier@gmail.com> wrote:
On Wed, Jun 5, 2013 at 12:59 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
I was looking for a way in which the average psql user could learn
whether a view is updatable. I was expecting something in \d, \d+, \dv,
\dv+, or a NOTICE from CREATE VIEW. So far, the only way appears to be
through the information schema or the underlying pg_view_is_updatable
function. Not even pg_views shows anything. Is this intentional or an
oversight?Just by recalling the thread, an oversight. Having this information in ¥dv+
would
be indeed a nice addition.
Yes, agreed -- something like this would be nice. It's not just views
though -- foreign tables may now also be updatable, so I think it
should work for \d+ in general, not just \dv+.
Perhaps we should add a new column to \d+'s list of relations
(provided that doesn't make it too wide) and add an extra line at the
end of the \d+ description for a single relation. Should this also
distinguish between insertable, updatable and deletable (i.e., support
for INSERT, UPDATE and DELETE)?
I'm still not happy with pg_view_is_updatable() et al. and the
information_schema views. I accept that the information_schema views
have to be the way they are because that's what's defined in the
standard, but as it stands, the distinction between updatable and
trigger-updatable makes it impossible in general to answer the simple
question "does foo support UPDATEs?".
I'm thinking what we really need is a single function with a slightly
different signature, that can be used to support both the information
schema views and psql's \d+ (and potentially other client apps).
Perhaps something like:-
pg_relation_is_updatable(include_triggers boolean)
returns int
which would work for all relation kinds, returning a bitmask
indicating which of the operations (INSERT, UPDATE and DELETE) are
supported, together with a matching function in the FDW API.
Thoughts?
Dean
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 5 June 2013 08:59, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
I'm still not happy with pg_view_is_updatable() et al. and the
information_schema views. I accept that the information_schema views
have to be the way they are because that's what's defined in the
standard, but as it stands, the distinction between updatable and
trigger-updatable makes it impossible in general to answer the simple
question "does foo support UPDATEs?".I'm thinking what we really need is a single function with a slightly
different signature, that can be used to support both the information
schema views and psql's \d+ (and potentially other client apps).
Perhaps something like:-pg_relation_is_updatable(include_triggers boolean)
returns int
OK, here's what it looks like using this approach:
FUNCTION pg_relation_is_updatable(reloid oid,
include_triggers boolean)
RETURNS integer
FUNCTION pg_column_is_updatable(reloid oid,
attnum integer,
include_triggers boolean)
RETURNS boolean
These replace pg_view_is_updatable() and pg_view_is_insertable(). I
think I definitely prefer this over the old API, because it gives much
greater flexibility.
The information schema views all pass include_triggers = false for
compatibility with the standard. The return value from
pg_relation_is_updatable() is now an integer bitmask reflecting
whether or not the relation is insertable, updatable and/or deletable.
psql and other clients can more usefully pass include_triggers = true
to determine whether a relation actually supports INSERT, UPDATE and
DELETE, including checks for INSTEAD OF triggers on the specified
relation or any underlying base relations.
I thought about having pg_relation_is_updatable() return text, like
the GRANT support functions, but I thought that it would make the
information schema views harder to write, using a single call to check
for updatable+deletable, whereas integer bit operations are easy.
There is a backwards-incompatible change to the information schema,
reflected in the regression tests: if a view is updatable but not
deletable, the relevant rows in information_schema.columns now say
'YES' --- the columns are updatable, even though the relation as a
whole isn't.
I've initially defined matching FDW callback functions:
int
IsForeignRelUpdatable (Oid foreigntableid,
bool include_triggers);
bool
IsForeignColUpdatable (Oid foreigntableid,
int attnum,
bool include_triggers);
but I'm now having second thoughts about whether we should bother
passing include_triggers to the FDW. If we regard the foreign table as
a black box, we only care about whether it is updatable, not *how*
that update is performed.
Regards,
Dean
Attachments:
pg_relation_is_updatable.patchapplication/octet-stream; name=pg_relation_is_updatable.patchDownload
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
On 6 June 2013 08:09, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
On 5 June 2013 08:59, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
I'm still not happy with pg_view_is_updatable() et al. and the
information_schema views. I accept that the information_schema views
have to be the way they are because that's what's defined in the
standard, but as it stands, the distinction between updatable and
trigger-updatable makes it impossible in general to answer the simple
question "does foo support UPDATEs?".I'm thinking what we really need is a single function with a slightly
different signature, that can be used to support both the information
schema views and psql's \d+ (and potentially other client apps).
Perhaps something like:-pg_relation_is_updatable(include_triggers boolean)
returns intOK, here's what it looks like using this approach:
FUNCTION pg_relation_is_updatable(reloid oid,
include_triggers boolean)
RETURNS integerFUNCTION pg_column_is_updatable(reloid oid,
attnum integer,
include_triggers boolean)
RETURNS booleanThese replace pg_view_is_updatable() and pg_view_is_insertable(). I
think I definitely prefer this over the old API, because it gives much
greater flexibility.The information schema views all pass include_triggers = false for
compatibility with the standard. The return value from
pg_relation_is_updatable() is now an integer bitmask reflecting
whether or not the relation is insertable, updatable and/or deletable.psql and other clients can more usefully pass include_triggers = true
to determine whether a relation actually supports INSERT, UPDATE and
DELETE, including checks for INSTEAD OF triggers on the specified
relation or any underlying base relations.I thought about having pg_relation_is_updatable() return text, like
the GRANT support functions, but I thought that it would make the
information schema views harder to write, using a single call to check
for updatable+deletable, whereas integer bit operations are easy.There is a backwards-incompatible change to the information schema,
reflected in the regression tests: if a view is updatable but not
deletable, the relevant rows in information_schema.columns now say
'YES' --- the columns are updatable, even though the relation as a
whole isn't.I've initially defined matching FDW callback functions:
int
IsForeignRelUpdatable (Oid foreigntableid,
bool include_triggers);bool
IsForeignColUpdatable (Oid foreigntableid,
int attnum,
bool include_triggers);but I'm now having second thoughts about whether we should bother
passing include_triggers to the FDW. If we regard the foreign table as
a black box, we only care about whether it is updatable, not *how*
that update is performed.
Here's a more complete patch along those lines. It defines the
following pair of functions to test for updatability from SQL:
FUNCTION pg_catalog.pg_relation_is_updatable(reloid oid,
include_triggers boolean)
RETURNS integer
FUNCTION pg_catalog.pg_column_is_updatable(reloid oid,
attnum smallint,
include_triggers boolean)
RETURNS boolean
and the following FDW functions:
int IsForeignRelUpdatable (Oid foreigntableid);
bool IsForeignColUpdatable (Oid foreigntableid,
AttrNumber attnum);
As an initial implementation of this API in the postgres-fdw, I've
added a new option "updatable" (true by default), which can be
specified as a server option or as a per-table option, to give user
control over whether individual foreign tables are read-only or
updatable.
I called it updatable rather than "writable" or "read-only" because it
might perhaps be extended in the future with separate options for
"insertable" and "deletable". It could also be extended to give
column-level control over updatability, or something like
"use_remote_updatability" could be added, but that all feels like 9.4
material.
Regards,
Dean
Attachments:
pg_relation_is_updatable.patchapplication/octet-stream; name=pg_relation_is_updatable.patchDownload
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
new file mode 100644
index 123cb4f..5edbf3e
*** a/contrib/postgres_fdw/option.c
--- b/contrib/postgres_fdw/option.c
*************** postgres_fdw_validator(PG_FUNCTION_ARGS)
*** 106,114 ****
/*
* Validate option value, when we can do so without any context.
*/
! if (strcmp(def->defname, "use_remote_estimate") == 0)
{
! /* use_remote_estimate accepts only boolean values */
(void) defGetBoolean(def);
}
else if (strcmp(def->defname, "fdw_startup_cost") == 0 ||
--- 106,115 ----
/*
* Validate option value, when we can do so without any context.
*/
! if (strcmp(def->defname, "use_remote_estimate") == 0 ||
! strcmp(def->defname, "updatable") == 0)
{
! /* these accept only boolean values */
(void) defGetBoolean(def);
}
else if (strcmp(def->defname, "fdw_startup_cost") == 0 ||
*************** InitPgFdwOptions(void)
*** 151,156 ****
--- 152,160 ----
/* cost factors */
{"fdw_startup_cost", ForeignServerRelationId, false},
{"fdw_tuple_cost", ForeignServerRelationId, false},
+ /* updatability may be specified on the server and on tables */
+ {"updatable", ForeignServerRelationId, false},
+ {"updatable", ForeignTableRelationId, false},
{NULL, InvalidOid, false}
};
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
new file mode 100644
index 49dfe2c..376f5aa
*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
*************** static void postgresBeginForeignScan(For
*** 251,256 ****
--- 251,259 ----
static TupleTableSlot *postgresIterateForeignScan(ForeignScanState *node);
static void postgresReScanForeignScan(ForeignScanState *node);
static void postgresEndForeignScan(ForeignScanState *node);
+ static int postgresIsForeignRelUpdatable(Oid foreigntableid);
+ static bool postgresIsForeignColUpdatable(Oid foreigntableid,
+ AttrNumber attnum);
static void postgresAddForeignUpdateTargets(Query *parsetree,
RangeTblEntry *target_rte,
Relation target_relation);
*************** postgres_fdw_handler(PG_FUNCTION_ARGS)
*** 348,353 ****
--- 351,358 ----
routine->EndForeignScan = postgresEndForeignScan;
/* Functions for updating foreign tables */
+ routine->IsForeignRelUpdatable = postgresIsForeignRelUpdatable;
+ routine->IsForeignColUpdatable = postgresIsForeignColUpdatable;
routine->AddForeignUpdateTargets = postgresAddForeignUpdateTargets;
routine->PlanForeignModify = postgresPlanForeignModify;
routine->BeginForeignModify = postgresBeginForeignModify;
*************** postgresEndForeignScan(ForeignScanState
*** 1099,1104 ****
--- 1104,1171 ----
}
/*
+ * postgresIsForeignRelUpdatable
+ * Determine whether a foreign table supports INSERT, UPDATE and/or
+ * DELETE.
+ */
+ static int
+ postgresIsForeignRelUpdatable(Oid foreigntableid)
+ {
+ bool updatable;
+ ForeignTable *table;
+ ForeignServer *server;
+ ListCell *lc;
+
+ /*
+ * By default, all postgresql foreign tables are updatable. This may
+ * overridden by a per-server setting, which may in turn be overridden by
+ * a per-table setting.
+ */
+ updatable = true;
+
+ table = GetForeignTable(foreigntableid);
+ server = GetForeignServer(table->serverid);
+
+ foreach(lc, server->options)
+ {
+ DefElem *def = (DefElem *) lfirst(lc);
+
+ if (strcmp(def->defname, "updatable") == 0)
+ updatable = defGetBoolean(def);
+ }
+ foreach(lc, table->options)
+ {
+ DefElem *def = (DefElem *) lfirst(lc);
+
+ if (strcmp(def->defname, "updatable") == 0)
+ updatable = defGetBoolean(def);
+ }
+
+ /*
+ * Currently "updatable" means suport for INSERT, UPDATE and DELETE.
+ */
+ return updatable ?
+ (1 << CMD_INSERT) | (1 << CMD_UPDATE) | (1 << CMD_DELETE) : 0;
+ }
+
+ /*
+ * postgresIsForeignColUpdatable
+ * Determine whether a column of a foreign table is updatable.
+ */
+ static bool
+ postgresIsForeignColUpdatable(Oid foreigntableid,
+ AttrNumber attnum)
+ {
+ /*
+ * For now we assume that all foreign table columns are updatable, if the
+ * foreign table supports UPDATE.
+ */
+ int events = postgresIsForeignRelUpdatable(foreigntableid);
+
+ return (events & (1 << CMD_UPDATE)) != 0;
+ }
+
+ /*
* postgresAddForeignUpdateTargets
* Add resjunk column(s) needed for update/delete on a foreign table
*/
diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml
new file mode 100644
index c94988a..9be2edf
*** a/doc/src/sgml/fdwhandler.sgml
--- b/doc/src/sgml/fdwhandler.sgml
*************** EndForeignScan (ForeignScanState *node);
*** 286,291 ****
--- 286,316 ----
<para>
<programlisting>
+ int
+ IsForeignRelUpdatable (Oid foreigntableid);
+ </programlisting>
+
+ Determine which update operations the specified foreign table supports.
+ The return value should be a bitmask of rule event numbers indicating
+ which operations are supported by the foreign table, based on the
+ <literal>CmdType</> enumeration
+ (<literal>(1 << CMD_UPDATE) = 4</> for <command>UPDATE</>,
+ <literal>(1 << CMD_INSERT) = 8</> for <command>INSERT</>,
+ <literal>(1 << CMD_DELETE) = 16</> for <command>DELETE</>).
+ </para>
+
+ <para>
+ <programlisting>
+ bool
+ IsForeignColUpdatable (Oid foreigntableid,
+ AttrNumber attnum);
+ </programlisting>
+
+ Test if the specified column of the specified foreign table is updatable.
+ </para>
+
+ <para>
+ <programlisting>
void
AddForeignUpdateTargets (Query *parsetree,
RangeTblEntry *target_rte,
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
new file mode 100644
index 4aa798a..be91485
*** a/doc/src/sgml/postgres-fdw.sgml
--- b/doc/src/sgml/postgres-fdw.sgml
***************
*** 254,259 ****
--- 254,284 ----
</para>
</sect3>
+
+ <sect3>
+ <title>Updatability Options</title>
+
+ <para>
+ By default all foreign tables using <filename>postgres_fdw</> are assumed
+ to be updatable. This may be overridden using the following option:
+ </para>
+
+ <variablelist>
+
+ <varlistentry>
+ <term><literal>updatable</literal></term>
+ <listitem>
+ <para>
+ This option can be specified for a foreign table or a foreign server.
+ It controls whether <filename>postgres_fdw</> allows foreign tables to
+ be modified using <command>INSERT</>, <command>UPDATE</> and
+ <command>DELETE</> commands. The default is true.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </sect3>
</sect2>
<sect2>
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/executor/execMain.c b/src/backend/executor/execMain.c
new file mode 100644
index e1b280a..036dd2f
*** a/src/backend/executor/execMain.c
--- b/src/backend/executor/execMain.c
*************** CheckValidResultRel(Relation resultRel,
*** 1009,1033 ****
switch (operation)
{
case CMD_INSERT:
! if (fdwroutine->ExecForeignInsert == NULL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot insert into foreign table \"%s\"",
RelationGetRelationName(resultRel))));
break;
case CMD_UPDATE:
! if (fdwroutine->ExecForeignUpdate == NULL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot update foreign table \"%s\"",
RelationGetRelationName(resultRel))));
break;
case CMD_DELETE:
! if (fdwroutine->ExecForeignDelete == NULL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot delete from foreign table \"%s\"",
RelationGetRelationName(resultRel))));
break;
default:
elog(ERROR, "unrecognized CmdType: %d", (int) operation);
--- 1009,1054 ----
switch (operation)
{
case CMD_INSERT:
! if (fdwroutine->ExecForeignInsert == NULL ||
! fdwroutine->IsForeignRelUpdatable == NULL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot insert into foreign table \"%s\"",
RelationGetRelationName(resultRel))));
+ if ((fdwroutine->IsForeignRelUpdatable(resultRel->rd_id)
+ & (1<< CMD_INSERT)) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("foreign table \"%s\" does not allow inserts",
+ RelationGetRelationName(resultRel))));
break;
case CMD_UPDATE:
! if (fdwroutine->ExecForeignUpdate == NULL ||
! fdwroutine->IsForeignRelUpdatable == NULL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot update foreign table \"%s\"",
RelationGetRelationName(resultRel))));
+ if ((fdwroutine->IsForeignRelUpdatable(resultRel->rd_id)
+ & (1 << CMD_UPDATE)) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("foreign table \"%s\" does not allow updates",
+ RelationGetRelationName(resultRel))));
break;
case CMD_DELETE:
! if (fdwroutine->ExecForeignDelete == NULL ||
! fdwroutine->IsForeignRelUpdatable == NULL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot delete from foreign table \"%s\"",
RelationGetRelationName(resultRel))));
+ if ((fdwroutine->IsForeignRelUpdatable(resultRel->rd_id)
+ & (1 << CMD_DELETE)) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("foreign table \"%s\" does not allow deletes",
+ RelationGetRelationName(resultRel))));
break;
default:
elog(ERROR, "unrecognized CmdType: %d", (int) operation);
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
new file mode 100644
index 83f26e3..7587f76
*** 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,2173 ----
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)
+ & 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;
}
--- 2186,2205 ----
{
/* 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..995f17c
*** 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,620 ----
}
+ /* ----------------------------------------------------------------------
+ * 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);
! AttrNumber attnum = PG_GETARG_INT16(1);
! bool include_triggers = PG_GETARG_BOOL(2);
! Relation rel;
! bool updatable;
! /* System columns are never updatable */
! if (attnum <= 0)
! PG_RETURN_BOOL(false);
!
! rel = try_relation_open(reloid, AccessShareLock);
! if (rel == NULL)
! PG_RETURN_BOOL(false);
!
! if (attnum > rel->rd_att->natts ||
! rel->rd_att->attrs[attnum - 1]->attisdropped)
! {
! /*
! * The column number is out of range, or the column has been dropped,
! * so it is not updatable.
! */
! updatable = false;
! }
! else 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);
! }
! else
! {
! /*
! * Otherwise, for non-foreign tables, the column is updatable if and
! * only if the relation supports UPDATE.
! */
! int 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..55c4070
*** 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 21 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..bca1d59
*** a/src/include/foreign/fdwapi.h
--- b/src/include/foreign/fdwapi.h
*************** typedef void (*ReScanForeignScan_functio
*** 47,52 ****
--- 47,57 ----
typedef void (*EndForeignScan_function) (ForeignScanState *node);
+ typedef int (*IsForeignRelUpdatable_function) (Oid foreigntableid);
+
+ typedef bool (*IsForeignColUpdatable_function) (Oid foreigntableid,
+ AttrNumber attnum);
+
typedef void (*AddForeignUpdateTargets_function) (Query *parsetree,
RangeTblEntry *target_rte,
Relation target_relation);
*************** typedef struct FdwRoutine
*** 127,132 ****
--- 132,139 ----
*/
/* 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
Sorry for my late reply.
On Sun, Jun 9, 2013 at 6:45 PM, Dean Rasheed <dean.a.rasheed@gmail.com>wrote:
I called it updatable rather than "writable" or "read-only" because it
might perhaps be extended in the future with separate options for
"insertable" and "deletable". It could also be extended to give
column-level control over updatability, or something like
"use_remote_updatability" could be added, but that all feels like 9.4
material.
Yes this is definitely material for 9.4. You should add this patch to the
1st commit fest. I'll add myself as a reviewer.
Thanks,
--
Michael
On 11 June 2013 01:03, Michael Paquier <michael.paquier@gmail.com> wrote:
Sorry for my late reply.
On Sun, Jun 9, 2013 at 6:45 PM, Dean Rasheed <dean.a.rasheed@gmail.com>
wrote:I called it updatable rather than "writable" or "read-only" because it
might perhaps be extended in the future with separate options for
"insertable" and "deletable". It could also be extended to give
column-level control over updatability, or something like
"use_remote_updatability" could be added, but that all feels like 9.4
material.Yes this is definitely material for 9.4. You should add this patch to the
1st commit fest. I'll add myself as a reviewer.
Thanks,
Thanks. Arguably though, the API changes are something that should be
sorted out in 9.3, but I'm not sure how much of an appetite there is
for that, or whether it's too late.
pg_view_is_updatable() and pg_view_is_insertable() are both new to
9.3. They were designed purely to support the information schema
views, but are inadequate for most other practical purposes. Once 9.3
is out, we'll be stuck with them - although of course that doesn't
stop us adding more functions, I think it would be better to replace
them now.
Likewise the writable FDW API is new to 9.3, so I think 9.3 should at
least decide on the API for a FDW to specify whether a foreign table
is updatable.
Regards,
Dean
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
On 11 June 2013 01:03, Michael Paquier <michael.paquier@gmail.com> wrote:
Yes this is definitely material for 9.4. You should add this patch to the
Thanks. Arguably though, the API changes are something that should be
sorted out in 9.3,
I agree --- I'm planning to look at this in the next few days.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Jun 11, 2013 at 4:07 PM, Dean Rasheed <dean.a.rasheed@gmail.com>wrote:
Thanks. Arguably though, the API changes are something that should be
sorted out in 9.3, but I'm not sure how much of an appetite there is
for that, or whether it's too late.
I see, OK for the API changes on the functions, but I am not sure it is
time to add new options in postgres_fdw as you do in your second patch.
Unfortunately I will not be able to have a look in details at your patch, I
am sick...
--
Michael
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
Here's a more complete patch along those lines. It defines the
following pair of functions to test for updatability from SQL:
FUNCTION pg_catalog.pg_relation_is_updatable(reloid oid,
include_triggers boolean)
RETURNS integer
FUNCTION pg_catalog.pg_column_is_updatable(reloid oid,
attnum smallint,
include_triggers boolean)
RETURNS boolean
and the following FDW functions:
int IsForeignRelUpdatable (Oid foreigntableid);
bool IsForeignColUpdatable (Oid foreigntableid,
AttrNumber attnum);
I'm looking at this patch now. I do not see the point of
pg_column_is_updatable nor IsForeignColUpdatable --- that's loading
additional complexity onto every FDW, to support what use-cases exactly?
Is it really likely that (a) there are any cases out there where FDWs
would support updating only some columns, and (b) anybody would care
whether or not information_schema.columns reflects such a restriction
accurately? So I'm inclined to drop that part.
As an initial implementation of this API in the postgres-fdw, I've
added a new option "updatable" (true by default), which can be
specified as a server option or as a per-table option, to give user
control over whether individual foreign tables are read-only or
updatable.
Do we really want that as a server option? Why?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 11 June 2013 22:53, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
Here's a more complete patch along those lines. It defines the
following pair of functions to test for updatability from SQL:FUNCTION pg_catalog.pg_relation_is_updatable(reloid oid,
include_triggers boolean)
RETURNS integerFUNCTION pg_catalog.pg_column_is_updatable(reloid oid,
attnum smallint,
include_triggers boolean)
RETURNS booleanand the following FDW functions:
int IsForeignRelUpdatable (Oid foreigntableid);
bool IsForeignColUpdatable (Oid foreigntableid,
AttrNumber attnum);I'm looking at this patch now. I do not see the point of
pg_column_is_updatable nor IsForeignColUpdatable --- that's loading
additional complexity onto every FDW, to support what use-cases exactly?
Is it really likely that (a) there are any cases out there where FDWs
would support updating only some columns, and (b) anybody would care
whether or not information_schema.columns reflects such a restriction
accurately? So I'm inclined to drop that part.
I originally thought of adding pg_column_is_updatable() because I was
imagining supporting more of the SQL standard on updatable views,
which allows for a subset of the columns to be updatable, but we could
always add such a function when/if we implement that feature. As for
IsForeignColUpdatable(), I think you're probably right. If it's only
purpose is to support information_schema.columns, it's probably of
very limited interest to anyone.
As an initial implementation of this API in the postgres-fdw, I've
added a new option "updatable" (true by default), which can be
specified as a server option or as a per-table option, to give user
control over whether individual foreign tables are read-only or
updatable.Do we really want that as a server option? Why?
Not sure. I thought it might be useful if you were setting up a
connection to a foreign server and you knew that you only wanted read
access to all the tables in it, this would avoid having to specify the
option on every table.
Regards,
Dean
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
[ pg_relation_is_updatable.patch ]
I've committed this with some modifications as mentioned. There is
still room to debate exactly what
information_schema.columns.is_updatable means --- we can now change that
without an initdb.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12 June 2013 23:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
[ pg_relation_is_updatable.patch ]
I've committed this with some modifications as mentioned. There is
still room to debate exactly what
information_schema.columns.is_updatable means --- we can now change that
without an initdb.
Thanks. Those modifications all look pretty neat.
I'm inclined to stick with the current definition of what updatable
means in the information schema. I'm not entirely convinced that other
possible interpretations of the spec are any more valid, and it
certainly doesn't seem worth another initdb or a break with backwards
compatibility by changing it. At least we now have functions that can
give a more intuitive result for updatability.
Regards,
Dean
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 6/13/13 1:37 AM, Dean Rasheed wrote:
On 12 June 2013 23:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
[ pg_relation_is_updatable.patch ]
I've committed this with some modifications as mentioned. There is
still room to debate exactly what
information_schema.columns.is_updatable means --- we can now change that
without an initdb.Thanks. Those modifications all look pretty neat.
We still don't have any support for this in psql, do we?
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 19 June 2013 15:22, Peter Eisentraut <peter_e@gmx.net> wrote:
We still don't have any support for this in psql, do we?
No, but at least we now have an API that psql can use.
There are still a number of questions about the best way to display it in psql.
Should it be another column in \d+'s list of relations?
Should it appear in \d+ for a single relation?
Should it distinguish updatable from insertable and deletable?
Should tab-completion also be modified?
Currently I'm thinking yes, yes, no, yes.
Regards,
Dean
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 6/19/13 11:50 AM, Dean Rasheed wrote:
On 19 June 2013 15:22, Peter Eisentraut <peter_e@gmx.net> wrote:
We still don't have any support for this in psql, do we?
No, but at least we now have an API that psql can use.
There are still a number of questions about the best way to display it in psql.
Should it be another column in \d+'s list of relations?
Should it appear in \d+ for a single relation?
Should it distinguish updatable from insertable and deletable?
Should tab-completion also be modified?Currently I'm thinking yes, yes, no, yes.
I would be satisfied with no, yes, no, no. Although I don't know what
tab completion changes you have in mind.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 19 June 2013 18:12, Peter Eisentraut <peter_e@gmx.net> wrote:
On 6/19/13 11:50 AM, Dean Rasheed wrote:
On 19 June 2013 15:22, Peter Eisentraut <peter_e@gmx.net> wrote:
We still don't have any support for this in psql, do we?
No, but at least we now have an API that psql can use.
There are still a number of questions about the best way to display it in psql.
Should it be another column in \d+'s list of relations?
Should it appear in \d+ for a single relation?
Should it distinguish updatable from insertable and deletable?
Should tab-completion also be modified?Currently I'm thinking yes, yes, no, yes.
I would be satisfied with no, yes, no, no. Although I don't know what
tab completion changes you have in mind.
Yes, on reflection having an extra column in the list of relations is
probably not a good idea. In many cases that's just going to be a list
of tables, all of which will be updatable.
So it would only be for \d+ on a single view or foreign table - simply:
Updatable: yes|no
Tab-completion was discussed on the original thread, but then I forgot about it:
/messages/by-id/CAA-aLv4_atXiJ7pAQGvh73N5A0F-paTvH5eM-LMqu+oFuzE63w@mail.gmail.com
Regards,
Dean
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 19 June 2013 18:12, Peter Eisentraut <peter_e@gmx.net> wrote:
On 6/19/13 11:50 AM, Dean Rasheed wrote:
On 19 June 2013 15:22, Peter Eisentraut <peter_e@gmx.net> wrote:
We still don't have any support for this in psql, do we?
No, but at least we now have an API that psql can use.
There are still a number of questions about the best way to display it in psql.
Should it be another column in \d+'s list of relations?
Should it appear in \d+ for a single relation?
Should it distinguish updatable from insertable and deletable?
Should tab-completion also be modified?Currently I'm thinking yes, yes, no, yes.
I would be satisfied with no, yes, no, no. Although I don't know what
tab completion changes you have in mind.
Here's a patch that does that for foreign tables and views. It regards
"updatable" as support for *any* of the DML operations. Bernd Helmle
has written a patch for tab completion.
Regards,
Dean
Attachments:
psql-describe.patchapplication/octet-stream; name=psql-describe.patchDownload
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
new file mode 100644
index 9b6b9c2..bd2519c
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
*************** describeOneTableDetails(const char *sche
*** 1149,1154 ****
--- 1149,1155 ----
char *reloptions;
char *reloftype;
char relpersistence;
+ bool updatable;
} tableinfo;
bool show_modifiers = false;
bool retval;
*************** describeOneTableDetails(const char *sche
*** 1164,1170 ****
initPQExpBuffer(&tmpbuf);
/* Get general table info */
! if (pset.sversion >= 90100)
{
printfPQExpBuffer(&buf,
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
--- 1165,1189 ----
initPQExpBuffer(&tmpbuf);
/* Get general table info */
! if (pset.sversion >= 90300)
! {
! printfPQExpBuffer(&buf,
! "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
! "c.relhastriggers, c.relhasoids, "
! "%s, c.reltablespace, "
! "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
! "c.relpersistence, "
! "c.relkind = 'r' OR (c.relkind IN ('f', 'v') AND pg_catalog.pg_relation_is_updatable(c.oid, true) <> 0)\n"
! "FROM pg_catalog.pg_class c\n "
! "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
! "WHERE c.oid = '%s';",
! (verbose ?
! "pg_catalog.array_to_string(c.reloptions || "
! "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
! : "''"),
! oid);
! }
! else if (pset.sversion >= 90100)
{
printfPQExpBuffer(&buf,
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
*************** describeOneTableDetails(const char *sche
*** 1269,1274 ****
--- 1288,1295 ----
pg_strdup(PQgetvalue(res, 0, 8)) : NULL;
tableinfo.relpersistence = (pset.sversion >= 90100) ?
*(PQgetvalue(res, 0, 9)) : 0;
+ tableinfo.updatable = (pset.sversion >= 90300) ?
+ strcmp(PQgetvalue(res, 0, 10), "t") == 0 : false;
PQclear(res);
res = NULL;
*************** describeOneTableDetails(const char *sche
*** 2291,2296 ****
--- 2312,2331 ----
printTableAddFooter(&cont, buf.data);
}
+ /*
+ * starting from 9.3, foreign tables may be updatable and views may be
+ * auto-updatable (in addition to being updatable using rules or triggers).
+ */
+ if (verbose && pset.sversion >= 90300 &&
+ (tableinfo.relkind == 'f' || tableinfo.relkind == 'v'))
+ {
+ const char *s = _("Updatable");
+
+ printfPQExpBuffer(&buf, "%s: %s", s,
+ (tableinfo.updatable ? _("yes") : _("no")));
+ printTableAddFooter(&cont, buf.data);
+ }
+
printTable(&cont, pset.queryFout, pset.logfile);
printTableCleanup(&cont);