Continuing my "novice interpretation" from before...
Tatsuo Ishii-4 wrote
> It would be nice if something like "unnamed portal will be destroyed
> by a Sync message if you are in an explicit transaction" is in our
> manual.
I do not believe this to be true from what I've scanned. Inside an
"implicit" transaction it will be but in an "explicit" transaction the SYNC
does not cause the transaction to close so any named and unnamed portals
should still exist after the SYNC (or FLUSH) sequence is completed. This
allows for cursors to be maintained over the entire lifetime of the explicit
transaction.
I do not follow where Tom's FLUSH comment is helpful here. In an explicit
transaction their behavior should be semantically identical. In the
implicit transaction case the use of FLUSH should not enable you to change
the statement that is being executed inside the
single-statement-transaction. In the example the error should come at #2 and
you'd never get to #3 where you'd issue SYNC/FLUSH (or rather when you get
to three you are already dead).
SYNC vs. FLUSH in the documentation is confusing as to how they interact
with implicit vs. explicit transactions. In the explicit transaction since
SYNC issues a FLUSH anyway, the only difference seems to be the presence of
the RFQ message.
How does this (SQL) sequence work - knowing each COMMAND is a sequence of
PARSE-BIND-EXECUTE extended commands that either suceeds or fails:
1) BEGIN;
2) SUCCESSFUL COMMAND;
3) SYNC;
4) ERROR COMMAND;
5) SUCCESSFUL (in theory) COMMAND;
6) SYNC;
7) SUCCESSFUL (in theory) COMMAND;
8) COMMIT;
The second SYNC resets and "returns to normal message processing" but the
transaction as a whole is in a failure mode. The interesting items are 5
and 7.
> So if we are not in an explicit transaction we cannot reuse unnamed portal
> because Sync closes the transaction
Correct. In an implicit transaction all un-held portals are closed upon
SYNC - named or otherwise.
Note that the use of "not explicit" instead of "implicit" makes reading this
discussion a little more difficult.
However, because of:
> An unnamed prepared statement lasts only until the next Parse statement
> specifying the unnamed statement as destination is issued.
the unnamed_statement should still exist. I can see this being useful if
you are inserting 10,000 records via the extended protocol and want to
"save" every 1,000. After SYNCing the first 1,000 BIND/EXECUTES you can
continue using BINDing the original unnamed_statement to execute the next
1,000 and so forth.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Question-regarding-Sync-message-and-unnamed-portal-tp5726023p5770370.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.