Supporting MERGE on updatable views
Dean Rasheed <dean.a.rasheed@gmail.com>
From: Dean Rasheed <dean.a.rasheed@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-12-08T10:03:29Z
Lists: pgsql-hackers
Attachments
- support-merge-into-view-v1.patch (text/x-patch) patch v1
I have been playing around with making updatable views support MERGE, and it looks to be fairly straightforward. I'm intending to support auto-updatable views, WITH CHECK OPTION, and trigger-updatable views, but not views with rules, as I think that would be more trouble than it's worth. Per the SQL standard, if the view isn't auto-updatable, it requires the appropriate INSTEAD OF INSERT/UPDATE/DELETE triggers to perform the merge actions. One limitation with the current patch is that it will only work if the view is either auto-updatable with no INSTEAD OF triggers, or it has a full set of INSTEAD OF triggers for all INSERT/UPDATE/DELETE actions mentioned in the MERGE command. It doesn't support a mix of those 2 cases (i.e., a partial set of INSTEAD OF triggers, such as an INSTEAD OF INSERT trigger only, on an otherwise auto-updatable view). Perhaps it will be possible to overcome that limitation in the future, but I think that it will be hard. In practice though, I think that this shouldn't be very limiting -- I think it's uncommon for people to define INSTEAD OF triggers on auto-updatable views, and if they do, they just need to be sure to provide a full set. Attached is a WIP patch, which I'll add to the next CF. I still need to do more testing, and update the docs, but so far, everything appears to work. Regards, Dean
Commits
-
Support MERGE into updatable views.
- 5f2e179bd31e 17.0 landed
-
Remove field UpdateContext->updated in nodeModifyTable.c
- 362de947cd7e 17.0 landed
-
Remove dummy_spinlock
- 55627ba2d334 17.0 cited