Re: how to find out whether a view is updatable

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 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