Re: timeout implementation issues

OK, I have a few ideas on this and I think one of them will have to be
implemented. Basically, we have this SET problem with all our
variables, e.g. if you SET explain_pretty_print or enable_seqscan in a
multi-statement transaction, and the transaction aborts after the
variable is turned on but before the variable is turned off, it will
remain on for the remainder of the session. See the attached email for
an example. It shows this problem with timeout, but all the SET
variables have this issue.

I think we have only a few options:

o Allow SET to execute even if the transaction is in ABORT
state (Tom says some SET variables need db access and will
fail.)
o If a SET is performed while in transaction ABORT state, queue
up the SET commands to run after the transaction completes
o Issue a RESET on transaction completion (commit or abort) for any
SET variable set in the transaction. (This will cause problems
for API's like ecpg which are always in a transaction.)
o Issue a variable RESET on transaction ABORT for any SET variable
modified by a transaction.

I think the last one is the most reasonable option.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Jessica Perry Hekman wrote:
> > On Tue, 2 Apr 2002, Barry Lind wrote:
> >
> > > Since both the JDBC and ODBC specs have essentially the same symantics
> > > for this, I would hope this is done in the backend instead of both
> > > interfaces.
> >
> > The current plan seems to be to make changes in the backend and the JDBC
> > interface, the bulk of the implementation being in the backend.
>
> Yes, ODBC and JDBC need this, and I am sure psql folks will use it too,
> not counting libpq and all the others.
>
> We just need a way to specify statement-level SET options inside a
> transaction where the statement may fail and ignore the SET command that
> resets the timeout. We don't have any mechanism to reset the timeout
> parameter at the end of a transaction automatically, which would solve
> our problem with failed transactions.
>
> Does anyone know the ramifications of allowing SET to work in an aborted
> transaction? It is my understanding that SET doesn't really have
> transaction semantics anyway, e.g. a SET that is done in a transaction
> that is later aborted is still valid:
>
> test=> BEGIN;
> BEGIN
> test=> SET server_min_messages to 'debug5';
> SET VARIABLE
> test=> ABORT;
> ROLLBACK
> test=> SHOW server_min_messages;
> INFO: server_min_messages is debug5
> SHOW VARIABLE
>
> Having shown this, it could be argued that SET should work in an
> already-aborted transaction. Why should having the SET before or after
> the transaction is canceled have any effect. This illustrates it a
> little clearer:
>
> test=> BEGIN;
> BEGIN
> test=> SET server_min_messages to 'debug3';
> SET VARIABLE
> test=> asdf;
> ERROR: parser: parse error at or near "asdf"
> test=> SET server_min_messages to 'debug1';
> WARNING: current transaction is aborted, queries ignored until end of
> transaction block
> *ABORT STATE*
> test=> COMMIT;
> COMMIT
> test=> SHOW server_min_messages;
> INFO: server_min_messages is debug3
> SHOW VARIABLE
> test=>
>
> Why should the 'debug3' be honored if the transaction aborted. And if
> it is OK that is was honored, is it OK that the 'debug1' was not
> honored?
>
> Allowing SET to be valid after a transaction aborts would solve our SET
> timeout problem.
>
> There is also a feeling that people may want to set maximum counts for
> transactions too because the transaction could be holding locks you want
> released.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026