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

On 12 June 2013 18:35, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> looking at this patch some more ...
>
> Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> writes:
>> 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).
>
> I'm not convinced about this change. The spec's notion of updatability
> requires both UPDATE and DELETE to be allowed; that's why they don't
> have a separate is_deletable attribute. And they don't have any such
> thing as a column whose updatability doesn't require updatability of the
> underlying table. So I think the previous behavior was correct and
> should be maintained: although Postgres does permit decoupling
> deletability from updatability, only tables/columns for which both
> operations are possible should be marked is_updatable in the
> information_schema. Otherwise, an application relying on the assumption
> that "is_updatable" means it can DELETE will be broken.
>
> I can see however that varying opinions on this are possible. Although
> I'd removed the separate pg_column_is_updatable() function from your
> patch with the intent of using pg_relation_is_updatable() directly,
> I'm now thinking about putting back the former, so that this decision
> is taken in C code where we can change it without an initdb.
>

The more I read the spec, the less sense it seems to make, and each
time I read it, I seem to reach a different conclusion.

On my latest reading, I've almost convinced myself that "updatable" is
meant to imply support for all 3 operations (INSERT, UPDATE and
DELETE), at least in the absence of transient tables. The descriptions
of all 3 seem to require the table to be updatable. INSERT requires
the table to be insertable-into, updatable and all its columns to be
updatable, but the requirement for insertable-into is only to rule out
transient tables. So if you don't have transient tables, which aren't
insertable-into, then all 3 operations are possible if and only if the
table is updatable.

That interpretation could be used to simplify the API, but no doubt
when I re-read the spec tomorrow, I'll reach a different conclusion.

Regards,
Dean