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

This might be a premature question considering write-able CTEs are not
in core, but...

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.



--
Regards,
Richard Broersma Jr.

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

On Sat, May 29, 2010 at 01:29:50PM -0700, Richard Broersma wrote:
> This might be a premature question considering write-able CTEs are not
> in core, but...
>
> 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.  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.

They'll be pretty nice even without the automated view stuff, though :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.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

David Fetter wrote:
> On Sat, May 29, 2010 at 01:29:50PM -0700, Richard Broersma wrote:
> > This might be a premature question considering write-able CTEs are not
> > in core, but...
> >
> > 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.  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.
>
> They'll be pretty nice even without the automated view stuff, though :)

If the user wants to submit it, fine, but neither Tom nor I are excited
about it.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

On Sat, May 29, 2010 at 09:38:30PM -0400, Bruce Momjian wrote:
> David Fetter wrote:
> > On Sat, May 29, 2010 at 01:29:50PM -0700, Richard Broersma wrote:
> > > This might be a premature question considering write-able CTEs
> > > are not in core, but...
> > >
> > > 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.  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.
> >
> > They'll be pretty nice even without the automated view stuff,
> > though :)
>
> If the user wants to submit it, fine, but neither Tom nor I are
> excited about it.

Could you clarify what you mean by, "it" in the sentence above?  At
the developer meeting, we put "Writeable CTEs" as one of the
achievable 9.1 targets, and Tom encouraged me to see that the patch
gets fixed up and resubmitted for the first reviewfest, i.e. the
middle of next month.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.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

David Fetter wrote:
> On Sat, May 29, 2010 at 09:38:30PM -0400, Bruce Momjian wrote:
> > David Fetter wrote:
> > > On Sat, May 29, 2010 at 01:29:50PM -0700, Richard Broersma wrote:
> > > > This might be a premature question considering write-able CTEs
> > > > are not in core, but...
> > > >
> > > > 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.  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.
> > >
> > > They'll be pretty nice even without the automated view stuff,
> > > though :)
> >
> > If the user wants to submit it, fine, but neither Tom nor I are
> > excited about it.
>
> Could you clarify what you mean by, "it" in the sentence above?  At
> the developer meeting, we put "Writeable CTEs" as one of the
> achievable 9.1 targets, and Tom encouraged me to see that the patch
> gets fixed up and resubmitted for the first reviewfest, i.e. the
> middle of next month.

Sorry, my mistake.  I thought I was commenting on the psql regression
test suite.  Please ignore.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

On Sat, May 29, 2010 at 6:25 PM, David Fetter <david@fetter.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

On Sat, May 29, 2010 at 8:21 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:
> 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

Actually this link better demonstrates the concern:

http://archives.postgresql.org/pgsql-odbc/2006-12/msg00039.php


--
Regards,
Richard Broersma Jr.

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

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@fetter.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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.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

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@fetter.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

On Tue, Jun 1, 2010 at 6:07 AM, David Fetter <david@fetter.org> wrote:

> 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. :)

There really isn't a connection, other than what appears to me to be a
similar problem domains.  My understanding is that both could
simultaneously issue DML to multiple tables.

I thought that writeable CTE's could be used to overcome two problems
that I see with rule based update-able views in a client-side
optimistic locking environment .

Problem 1:
Views based on vertically partitioned tables require its rule to have
multiple update statements for each table involved.  Some of  these
DML statements will not execute(I'm not sure why but is has something
to do with what gets passed to the WHERE clauses of the rule
statements by the update statement issued to the view).  So there are
case where a view's virtual row appears to have non-atomic updates as
demonstrated from psql in this link:
http://archives.postgresql.org/pgsql-odbc/2006-12/msg00039.php  When
this happens a client app using optimistic locking will roll back it
transaction and report concurrent update error.


Problem 2:
It possible to force atomic updates using a function.  But this posses
its own challenge with client-side optimistic locking.  When a client
updates a virtual row, the client checks the updated row count against
a count that it expects see.  If the two counts don't match, the
client rolls-back the transaction.  When the rule calls function to
preform atomic updates, an update count of 0 is always return rather
than the number of virtual rows updated.

So I was hoping that writeable CTEs would allow for atomic updates
while issuing update row counts. If the row counts match with what is
seen in the view, writeable CTEs could be used in rules to make views
truly behave has tables would from the perceptive clients using
optimistic locking.

However, as Marko mentioned, they probably wont. :(


--
Regards,
Richard Broersma Jr.

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