Re: how to find out whether a view is updatable

On 11 June 2013 22:53, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)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.
>

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