Re: how to find out whether a view is updatable

On 6 June 2013 08:09, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> On 5 June 2013 08:59, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)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.
>

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