Re: how to find out whether a view is updatable

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