Re: how to find out whether a view is updatable

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