Re: Write-able CTEs, Update-able views, Hierarchical data, and optimistic locking

On Sat, May 29, 2010 at 08:21:46PM -0700, Richard Broersma wrote:
> On Sat, May 29, 2010 at 6:25 PM, David Fetter <david(at)fetter(dot)org> wrote:
>
> >> I wondering if write-able CTE's will be the silver bullet that
> >> will make rule based update-able views based multiple vertically
> >> partitioned table robust.  By robust, I mean to elimination the
> >> update anomalies that can occur from the view point client side
> >> optimistic locking where the virtual row appears to be
> >> inconsistently updated.
> >
> > I'm not sure I understand.
>
> Sorry about that, unreadable text is was happens when I don't proof
> read before sending.
>
> >  When the concurrency issues in writeable CTEs get fixed, they
> > could become a mechanism for doing what you describe, but I
> > suspect there would be significant work involved in harnessing
> > them to that task.
>
> Actually I wasn't aware of the concurrency issue of write-able
> CTE's.

The concern, as I understand it, has to do with modifications to the
current snapshot. I'm sure someone who knows the code better can go
into more detail. Marko?

> The concern I have specifically relates to update-able views that
> were based upon joined tables (using these views was an attempt to
> hide the complexity of Generalization Hierarchies from the client
> side application). Updates to these kinds of views can give the
> appearance of non-atom updates on the view's virtual row. Also, if
> the view's reported row update count doesn't match what the client
> side software expects, the client automatically rolls back the
> transaction and reports a concurrent update error. However, when
> this happens some of the underlying rule's update statements were in
> fact processed, so the refreshed row in the view appears to have an
> non-atomic update even though the client rolls back the transaction.
>
> The following email was my first discovery that these kinds of
> update-able view were not get-along well with client side optimistic
> locking.
>
> http://archives.postgresql.org/pgsql-odbc/2006-12/msg00029.php

I'm not trying to be obtuse, but I am not understanding how you
connect this issue, which has to do with the way PostgreSQL's RULE
system works, with writeable CTEs, which have approximately nothing in
common with the issue except in that they, too, need to deal with the
PostgreSQL RULE system, the fixing of which I have written about here:

http://archives.postgresql.org/pgsql-hackers/2009-10/msg00249.php

Please help me by making explicit the connection(s) you see between
the writeable VIEWs and writeable CTEs, apart from that first word. :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate