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

On 2010-06-01 16:07 +0300, David Fetter wrote:
> 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?

There were some issues with the previous design, but they will all be
gone if it ever gets committed.

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

There are major problems with updateable views in postgres, and
writeable CTEs can't make them go away.

Regards,
Marko Tiikkaja