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

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 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

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug