On Tue, Jun 1, 2010 at 6:07 AM, David Fetter <david@xxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
