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

On Tue, Jun 1, 2010 at 6:07 AM, David Fetter <david(at)fetter(dot)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