V3 protocol vs INSERT/UPDATE RETURNING

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: V3 protocol vs INSERT/UPDATE RETURNING
Date: 2006-08-11 16:36:26
Message-ID: 16022.1155314186@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jonah H. Harris 2006-08-11 16:48:18 Re: V3 protocol vs INSERT/UPDATE RETURNING
Previous Message Joshua D. Drake 2006-08-11 16:35:00 Re: 8.2 features status