Updatable view columns

From: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Updatable view columns
Date: 2013-08-12 14:27:52
Message-ID: CAEZATCULXejsZVq3PvG8RJReyXwdPoZU_Myr6H2DWW5=ioR=NA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

Attached is a work-in-progress patch to extend auto-updatable views to
support views containing a mix of updatable and non-updatable columns.
This is basically the "columns" part of SQL Feature T111, "Updatable
joins, unions, and columns".

So specifically, views of the following form will now be auto-updatable:

SELECT <column, expression, sub-query or non-SRF>, ...
FROM <single base table or view>
WHERE <arbitrary quals>
[ORDER BY ...]

with the restriction that no window functions, aggregates or
set-returning functions may appear in the view's targetlist (because
otherwise the rewriting process may move them up into quals of the
top-level query where they are not allowed).

Hopefully this will make auto-updatable views much more useful, since
it covers a much wider class of real-world views.

INSERT and UPDATE are supported provided that they do not attempt to
assign to a non-updatable column (which currently means a column that
is not a simple reference to an updatable column of the base
relation). This also means that the view must have at least 1
updatable column for these operations, which is per-spec.

DELETE on the other hand doesn't actually require any updatable
columns, provided the view satisfies all the other updatability
requirements (single base relation, no distinction, grouping, etc...).
This is actually an extension of the spec, which says that DELETE
should only be supported on updatable views with at least 1 updatable
column, but having played around with the code, it seems it would be
an annoying amount of additional code to enforce this restriction, so
I don't see any reason to not just allow it.

Code-wise, aside from the obvious changes needed to the
xxx_is_updatable() functions, the only other code change needed to
support this is in rewriteTargetListIU(). This had code for UPDATE
which would add new dummy targetlist entries for unassigned-to
attributes in the view, similar to expand_targetlist() in preptlist.c.
It now only does this for trigger-updatable views. For auto-updatable
views, it is definitely the wrong thing to do, since it would assign
targetlist entries for all the non-updatable columns. For
rule-updatable views it was not wrong, but I think it was unnecessary,
since the rule will rewrite the query with a different target
relation, which would then get recursively processed. Basically,
adding these dummy targetlist entries is only necessary if the view
relation remains the target after rewriting. That's consistent with
the fact that this code-block was only added in 9.1 to support
triggers on views.

It still needs more testing and doc updates, but otherwise I hope it
will be in reasonable shape for the next commitfest.

Regards,
Dean

Attachment Content-Type Size
updatable-view-cols.v1.patch application/octet-stream 58.0 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2013-08-12 14:49:40 Re: libpq thread locking during SSL connection start
Previous Message Bruce Momjian 2013-08-12 14:11:44 Re: [BUGS] BUG #8335: trim() un-document behaviour