Thread: V3 protocol vs INSERT/UPDATE RETURNING
I'm looking at modifying the portal execution strategy so that INSERT RETURNING and friends will work when invoked through "extended query" protocol. Currently, INSERT/UPDATE/DELETE queries are always executed under PORTAL_MULTI_QUERY strategy, which runs the portal's queries to completion and discards any results. Obviously that's gotta change. I was considering making a PORTAL_ONE_RETURNING strategy that works just about like PORTAL_ONE_SELECT, but that would have an interesting side effect. In PORTAL_ONE_SELECT, we can execute the query incrementally (if the client sends Execute messages with row limit counts specified), and we don't insist that the client send enough Execute messages to run the query to completion. If applied to a RETURNING query this would mean that a multi-row update might not be executed completely. I can think of a number of possible ways to handle this: 1. Define it as a feature not a bug. People do occasionally ask for "UPDATE foo ... LIMIT 1" after all. But this is a pretty klugy way of getting that, and the arguments that say allowing LIMIT on updating queries would be a bad idea haven't lost their force. 2. Ignore any requested Execute limit in PORTAL_ONE_RETURNING mode. Trivial to implement but violates the protocol specification. 3. Throw an error (thereby rolling back the incomplete update) if client closes the portal without having run it to completion. 4. Treat PORTAL_ONE_RETURNING like PORTAL_UTIL_SELECT rather than like PORTAL_ONE_SELECT; that is, execute the query to completion on first call and stash the results in a tuplestore until the client fetches them. I don't like #1 much, #2 and #3 seem klugy as well, but #4 is pretty inefficient. At the moment I'm thinking #3 is the least bad answer, but does anyone have another idea? regards, tom lane
Sorry, copied to list. On 8/11/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > 3. Throw an error (thereby rolling back the incomplete update) > if client closes the portal without having run it to completion. Sounds like the most reasonable considering. I'm not averse to it. > 4. Treat PORTAL_ONE_RETURNING like PORTAL_UTIL_SELECT rather than > like PORTAL_ONE_SELECT; that is, execute the query to completion > on first call and stash the results in a tuplestore until the > client fetches them. I agree that it's inefficient, but am trying to think of any other positive reasons for doing #4 instead. Can you think of any other advantages system-wide to using #4 instead of #3? -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
> 1. Define it as a feature not a bug. People do occasionally ask for > "UPDATE foo ... LIMIT 1" after all. But this is a pretty klugy way of > getting that, and the arguments that say allowing LIMIT on updating > queries would be a bad idea haven't lost their force. Being one of those who was asking for an UPDATE/DELETE with limit, I would be very glad if this would be implemented... it would be a big help for batch-processing data in OLTP environment (no long running queries allowed). I still don't see why would nondeterminism be generally a bad thing when there are applications which don't care about that... Cheers, Csaba.
"Jonah H. Harris" <jonah.harris@gmail.com> writes: > On 8/11/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> 4. Treat PORTAL_ONE_RETURNING like PORTAL_UTIL_SELECT rather than >> like PORTAL_ONE_SELECT; that is, execute the query to completion >> on first call and stash the results in a tuplestore until the >> client fetches them. > I agree that it's inefficient, but am trying to think of any other > positive reasons for doing #4 instead. Can you think of any other > advantages system-wide to using #4 instead of #3? Not really. Locks and so forth held by the query would be held till commit in any case, so I don't see much advantage in finishing the query immediately. regards, tom lane
On 8/11/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Not really. Locks and so forth held by the query would be held till > commit in any case, so I don't see much advantage in finishing the > query immediately. Very true. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
"Jonah H. Harris" <jonah.harris@gmail.com> writes: > On 8/11/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> 4. Treat PORTAL_ONE_RETURNING like PORTAL_UTIL_SELECT rather than >> like PORTAL_ONE_SELECT; that is, execute the query to completion >> on first call and stash the results in a tuplestore until the >> client fetches them. > I agree that it's inefficient, but am trying to think of any other > positive reasons for doing #4 instead. I found a showstopper reason why it has to be done this way: the AFTER TRIGGER code isn't capable of dealing with interleaved execution of different queries (it can basically only track nested queries). Possibly that could be improved in the future, but for 8.2 I think we're stuck with using a tuplestore. One optimization I think might not be too hard is to bypass the tuplestore and stream RETURNING tuples directly to the client if the first Execute for the portal doesn't give a row limit (which is surely the typical case). I don't plan to do that in the first cut though. regards, tom lane