Re: [9.3] Automatically updatable views vs writable foreign tables

On 16 May 2013 22:16, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Specifically, for foreign tables
>> information_schema.tables.is_insertable_into and
>> information_schema.columns.is_updatable always say 'NO' even if the
>> foreign table is writable. Fixing that would require new C functions
>> along the same lines as pg_view_is_insertable/updatable(), or those
>> functions could just be renamed and repurposed to do the check for all
>> relation kinds, except those known to be always/never updatable.
>
> I'd vote to rename/extend them to be pg_relation_is_updatable I think.
>

I remember now just how ugly this code is. The SQL standard has
separate concepts of trigger-insertable, trigger-updatable,
trigger-deletable, insertable and updatable but not deletable for some
reason. So we define updatable as supporting both UPDATE and DELETE
without triggers. I think what we have implemented is technically
correct with regards to the spec in this area, but it is not
particularly useful as far as telling whether a relation will actually
support a particular query in practice (for example a simple view on
top of a trigger-updatable view is neither updatable nor
trigger-updatable).

One place where I think we have diverged from the spec, however, is in
information_schema.columns.updatable. This should be returning 'YES'
if the individual column is updatable, and I see no reason for that
the require the relation to support DELETE, which is what we currently
do (and always have done).

To implement the information_schema properly per-spec, I think we need
3 functions: pg_relation_is_insertable(), pg_relation_is_updatable()
and pg_column_is_updatable(), with the latter just checking UPDATE
events. It's probably a good idea to add these functions now, since I
hope in the future to support more of the SQL spec regarding
automatically updatable views, which will involve views for which only
a subset of their columns are updatable.

The attached patch does that, and tightens up relation_is_updatable()
to support all relation kinds, but it still assumes that if a FDW
defines, say, ExecForeignUpdate, then all its foreign tables are
updatable. That could be improved upon by defining new FDW API
callbacks that select from the remote information_schema, but I'm now
starting to doubt whether its really worth the trouble, given its
bizzare definition.

Regards,
Dean