On 16 May 2013 22:16, Tom Lane <t...@sss.pgh.pa.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
writable-fdw-view2.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
