Re: [HACKERS] V3 protocol vs INSERT/UPDATE RETURNING
"Jonah H. Harris" <[EMAIL PROTECTED]> writes: > On 8/11/06, Tom Lane <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] V3 protocol vs INSERT/UPDATE RETURNING
On 8/11/06, Tom Lane <[EMAIL PROTECTED]> 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| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] V3 protocol vs INSERT/UPDATE RETURNING
"Jonah H. Harris" <[EMAIL PROTECTED]> writes: > On 8/11/06, Tom Lane <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] V3 protocol vs INSERT/UPDATE RETURNING
> 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] V3 protocol vs INSERT/UPDATE RETURNING
Sorry, copied to list. On 8/11/06, Tom Lane <[EMAIL PROTECTED]> 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| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq