Thread

timeout implementation issues

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> — 2002-03-29T20:44:34Z

I have been talking with Bruce Momjian about implementing query
timeouts in the JDBC driver. As things stand, you can call
setQueryTimeout() or getQueryTimeout(), but a slow query will never
actually timeout, even if a timeout is set. The result of a timeout
should be a SQLException.

Bruce feels that this should be implemented in the backend: set an
alarm() in the backend on transaction start, then call the query
cancel() code if the alarm() goes off, and reset the alam if the query
finishes before the timeout.

I am concerned that this method does not provide a means of triggering
the SQLException in the driver. For an example, look at how cancel is
implemented (org.postgresql.Connection::cancelQuery()): we create a
new PG_Stream and send some integers to it which represent the cancel
request. Then we close the PG_Stream. There is no point at which we
receive any notification from the backend that the query has been
cancelled.

I looked in postmaster.c, processCancelRequest() to see what the
backend does. A SIGINT is sent to the backend when the cancel request
is successfully fulfilled, but nothing seems to be sent to the
interface.

One possibility is that the driver might just notice that the connection
has closed, and throw an Exception then. javax.sql.PooledConnection has an
addConnectionEventListener() method; we could add a
ConnectionEventListener there which would throw an Exception when the
connection closes.

In practice, this may or may not be a good idea. The place to get hold
of a PooledConnection seems to be in XAConnectionImpl (I am not sure
how the driver would actually request the relevant XAConnectionImpl
object, but I am sure I could figure that out). The thing is that this
class only allows one ConnectionEventListener to be set, so if we set
it, the user would be out of luck if he wanted to add his own
listener.

My proposal, then, is that the Java driver should submit the
transaction request; wait for the timeout; if it goes off, submit a
cancel request; and then throw a SQLException. We would not handle
this in the backend at all.

Bruce agreed that this was a good point to ask what the rest of the
hackers list thought. Any input?

Thanks,
Jessica

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-03-30T04:36:39Z

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> writes:
> [snip]
> My proposal, then, is that the Java driver should submit the
> transaction request; wait for the timeout; if it goes off, submit a
> cancel request; and then throw a SQLException. We would not handle
> this in the backend at all.

> Bruce agreed that this was a good point to ask what the rest of the
> hackers list thought. Any input?

I guess the $64 question is whether any frontends other than JDBC want
this behavior.  If it's JDBC-only then I'd certainly vote for making
JDBC handle it ... but as soon as we see several different frontends
implementing similar behavior, I'd say it makes sense to implement it
once in the backend.

So, what's the market?

			regards, tom lane

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-03-30T05:16:36Z

Tom Lane wrote:
> Jessica Perry Hekman <jphekman@dynamicdiagrams.com> writes:
> > [snip]
> > My proposal, then, is that the Java driver should submit the
> > transaction request; wait for the timeout; if it goes off, submit a
> > cancel request; and then throw a SQLException. We would not handle
> > this in the backend at all.
> 
> > Bruce agreed that this was a good point to ask what the rest of the
> > hackers list thought. Any input?
> 
> I guess the $64 question is whether any frontends other than JDBC want
> this behavior.  If it's JDBC-only then I'd certainly vote for making
> JDBC handle it ... but as soon as we see several different frontends
> implementing similar behavior, I'd say it makes sense to implement it
> once in the backend.
> 
> So, what's the market?

There is clearly interest from all interfaces.  This item has been
requested quite often, usually related to client apps or web apps.

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

Re: timeout implementation issues

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> — 2002-03-30T13:43:32Z

On Sat, 30 Mar 2002, Bruce Momjian wrote:

> There is clearly interest from all interfaces.  This item has been
> requested quite often, usually related to client apps or web apps.

I definitely agree that implementing it in the backend would be the best
plan, if it's feasible. I just can't figure out how to pass information
back to the driver that the request has been cancelled (and that, in
JDBC's case, a SQLException should be thrown). Any thoughts about that?

j

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-03-30T17:59:03Z

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> writes:
> I definitely agree that implementing it in the backend would be the best
> plan, if it's feasible. I just can't figure out how to pass information
> back to the driver that the request has been cancelled (and that, in
> JDBC's case, a SQLException should be thrown). Any thoughts about that?

Why would this be any different from a cancel-signal-instigated abort?
You'd be reporting elog(ERROR) in any case.

			regards, tom lane

Re: timeout implementation issues

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> — 2002-03-30T19:20:19Z

On Sat, 30 Mar 2002, Tom Lane wrote:

> Why would this be any different from a cancel-signal-instigated abort?
> You'd be reporting elog(ERROR) in any case.

If I understand the code correctly, in the case of a cancel signal, the
driver sends the signal and then assumes that the backend has accepted it
and cancelled; the back end does not report back. In this case, the driver
would not be sending a signal, so it would not know that the process had
reached the timeout and stopped (and it needs to know that). What we
*could* do is have *both* the driver and the backend run timers and both
stop when the timeout is reached. This seems like a solution just begging
to produce ugly bugs, though -- and if we have to implement such a wait in
the driver, we may as well implement the whole thing in the driver and
just have it send a cancel signal when it times out.

Or am I misunderstanding the situation?

j

Re: timeout implementation issues

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> — 2002-03-30T19:31:34Z

On Sat, 30 Mar 2002, Tom Lane wrote:

> Au contraire, it is not assuming anything.  It is sending off a cancel
> request and then waiting to see what happens.  Maybe the query will be
> canceled, or maybe it will complete normally, or maybe it will fail
> because of some error unrelated to the cancel request.  In any case the
> backend *will* eventually report completion/error status, and the
> frontend does not assume anything until it gets that report.

Ah, okay; this was not my understanding. I'll look at the code again.

> Why does it need to know that?  When it gets the error report back, it
> can notice that the error says "Query aborted by timeout" (or however we
> phrase it) ... but I'm not seeing why it should care.

I just meant it needed to know that the process had stopped prematurely; I
didn't mean it needed to know why.

I'll get back to you after doing a little more research.

j

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-03-30T19:32:51Z

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> writes:
> If I understand the code correctly, in the case of a cancel signal, the
> driver sends the signal and then assumes that the backend has accepted it
> and cancelled; the back end does not report back.

Au contraire, it is not assuming anything.  It is sending off a cancel
request and then waiting to see what happens.  Maybe the query will be
canceled, or maybe it will complete normally, or maybe it will fail
because of some error unrelated to the cancel request.  In any case the
backend *will* eventually report completion/error status, and the
frontend does not assume anything until it gets that report.

> In this case, the driver
> would not be sending a signal, so it would not know that the process had
> reached the timeout and stopped (and it needs to know that).

Why does it need to know that?  When it gets the error report back, it
can notice that the error says "Query aborted by timeout" (or however we
phrase it) ... but I'm not seeing why it should care.

			regards, tom lane

Re: timeout implementation issues

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> — 2002-04-01T15:49:04Z

> On Sat, 30 Mar 2002, Tom Lane wrote:
> 
> > Au contraire, it is not assuming anything.  It is sending off a cancel
> > request and then waiting to see what happens.  Maybe the query will be

Okay, I see now: when processCancelRequest() is called, a return of 127 is
sent. That would indeed work; thanks for walking me through it.

My other question was how to send the timeout value to the backend. Bruce
said at one point:

> Timeout can be part of BEGIN, or a SET value, which would work from
> jdbc.

I'm not sure how this would work. The timeout value would be sent as part
of a SQL query?

j

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-01T16:22:21Z

Jessica Perry Hekman wrote:
> > On Sat, 30 Mar 2002, Tom Lane wrote:
> > 
> > > Au contraire, it is not assuming anything.  It is sending off a cancel
> > > request and then waiting to see what happens.  Maybe the query will be
> 
> Okay, I see now: when processCancelRequest() is called, a return of 127 is
> sent. That would indeed work; thanks for walking me through it.
> 
> My other question was how to send the timeout value to the backend. Bruce
> said at one point:
> 
> > Timeout can be part of BEGIN, or a SET value, which would work from
> > jdbc.
> 
> I'm not sure how this would work. The timeout value would be sent as part
> of a SQL query?

I think there are two ways of making this capability visible to users. 
First, you could do:

	SET query_timeout = 5;
	
and all queries after that would time out at 5 seconds.  Another option
is:

	BEGIN WORK TIMEOUT 5;
	...
	COMMIT;

which would make the transaction timeout after 5 seconds.  We never
decided which one we wanted, or both.

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

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-01T16:26:19Z

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> writes:
> My other question was how to send the timeout value to the backend.

I would imagine that the most convenient way to handle it would be as
a SET variable:

	SET query_timeout = n;

Establishes a time limit on subsequent queries (n expressed in
milliseconds, perhaps).

	SET query_timeout = 0;

Disables query time limit.

This assumes that the query timeout should apply to each subsequent
query, individually, until explicitly canceled.  If you want a timeout
that applies to only one query and is then forgotten, then maybe this
wouldn't be the most convenient definition.  What semantics are you
trying to obtain, exactly?

			regards, tom lane

Re: timeout implementation issues

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> — 2002-04-01T16:50:16Z

On Mon, 1 Apr 2002, Tom Lane wrote:

> This assumes that the query timeout should apply to each subsequent
> query, individually, until explicitly canceled.  If you want a timeout
> that applies to only one query and is then forgotten, then maybe this
> wouldn't be the most convenient definition.  What semantics are you
> trying to obtain, exactly?

The semantices of the JDBC API:

"Transaction::setQueryTimeout(): Sets the number of seconds the driver
 will wait for a Statement to execute to the given number of seconds.
 If the limit is exceeded, a SQLException is thrown."

So it should apply to all queries on a given transaction. I think that the
above implemenation suggestion (and Bruce's) would apply to all queries,
regardless of which transaction they were associated with. If each
transaction has some kind of unique ID, maybe that could be added to the
SET statement?

Does anyone know how someone else did this (mSQL, mySQL, etc)? It seems
like there ought to already exist some sort of standard. I'll poke around
and see if I can find anything.

j

Re: timeout implementation issues

Jan Wieck <janwieck@yahoo.com> — 2002-04-01T20:08:41Z

Tom Lane wrote:
> Jessica Perry Hekman <jphekman@dynamicdiagrams.com> writes:
> > My other question was how to send the timeout value to the backend.
>
> I would imagine that the most convenient way to handle it would be as
> a SET variable:
>
>    SET query_timeout = n;
>
> Establishes a time limit on subsequent queries (n expressed in
> milliseconds, perhaps).
>
>    SET query_timeout = 0;
>
> Disables query time limit.
>
> This assumes that the query timeout should apply to each subsequent
> query, individually, until explicitly canceled.  If you want a timeout
> that applies to only one query and is then forgotten, then maybe this
> wouldn't be the most convenient definition.  What semantics are you
> trying to obtain, exactly?

    Why  don't  we  use  two separate GUC variables and leave the
    BEGIN syntax as is completely?

        SET transaction_timeout = m;
        SET statement_timeout = n;

    The alarm is set to the smaller  of  (what's  left  for)  the
    transaction or statement.

    If   you   want   to  go  sub-second,  I  suggest  making  it
    microseconds. That's what  struct  timeval  (used  in  struct
    itimerval)  uses. But I strongly suggest not doing so at all,
    because the usage of itimers disables the ability to  profile
    with  gprof  completely.  Compute  the time spent so far in a
    transaction exactly, but round UP to  full  seconds  for  the
    alarm allways.

    And   before   someone   asks,  no,  I  don't  think  that  a
    connection_timeout is a good thing.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Re: timeout implementation issues

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> — 2002-04-01T22:27:04Z

On Mon, 1 Apr 2002, Jan Wieck wrote:

>     Why  don't  we  use  two separate GUC variables and leave the
>     BEGIN syntax as is completely?
> 
>         SET transaction_timeout = m;
>         SET statement_timeout = n;

What's a GUC variable? Would this apply to all subsequent statements? I
think it needs to apply to just the specified statement.

I'm sorry about the confusion earlier when I said that
setQueryTimeout() was transaction-level; Barry Lind correctly pointed out
that it is statement-level. We mostly seem to feel that we don't want to
do both, so is statement-only okay? Jan, do you feel strongly that you
want to see both implemented?

>     If   you   want   to  go  sub-second,  I  suggest  making  it
>     microseconds. That's what  struct  timeval  (used  in  struct

I don't think that's necessary. JDBC only wants it specified in seconds.

j

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-01T23:15:00Z

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> writes:
> What's a GUC variable?

A parameter that you can set with SET.

> Would this apply to all subsequent statements? I
> think it needs to apply to just the specified statement.

Yes, if the JDBC spec expects this to be applied to just a single
statement, then a SET variable doesn't fit very nicely with that.
You'd have to have logic on the application side to reset the variable
to "no limit" after the statement --- and this could be rather
difficult.  (For example, if you are inside a transaction block and
the statement errors out, you won't be able to simply issue a new SET;
so you'd have to remember that you needed a SET until after you exit
the transaction block.  Ugh.)

On the other hand, we do not have anything in the backend now that
applies to just one statement and then automatically resets afterwards;
and I'm not eager to add a parameter with that behavior just for JDBC's
convenience.  It seems like it'd be a big wart.

			regards, tom lane

Re: timeout implementation issues

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> — 2002-04-02T16:13:19Z

On Mon, 1 Apr 2002, Tom Lane wrote:

> On the other hand, we do not have anything in the backend now that
> applies to just one statement and then automatically resets afterwards;
> and I'm not eager to add a parameter with that behavior just for JDBC's
> convenience.  It seems like it'd be a big wart.

Does that leave us with implementing query timeouts in JDBC (timer in the
driver; then the driver sends a cancel request to the backend)?

j

Re: timeout implementation issues

Barry Lind <barry@xythos.com> — 2002-04-02T18:35:12Z

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.

--Barry

Jessica Perry Hekman wrote:
> On Mon, 1 Apr 2002, Tom Lane wrote:
> 
> 
>>On the other hand, we do not have anything in the backend now that
>>applies to just one statement and then automatically resets afterwards;
>>and I'm not eager to add a parameter with that behavior just for JDBC's
>>convenience.  It seems like it'd be a big wart.
> 
> 
> Does that leave us with implementing query timeouts in JDBC (timer in the
> driver; then the driver sends a cancel request to the backend)?
> 
> j
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-02T18:39:30Z

Jessica Perry Hekman wrote:
> On Mon, 1 Apr 2002, Tom Lane wrote:
> 
> > On the other hand, we do not have anything in the backend now that
> > applies to just one statement and then automatically resets afterwards;
> > and I'm not eager to add a parameter with that behavior just for JDBC's
> > convenience.  It seems like it'd be a big wart.
> 
> Does that leave us with implementing query timeouts in JDBC (timer in the
> driver; then the driver sends a cancel request to the backend)?

No, I think we have to find a way to do this in the backend; just not
sure how yet.

I see the problem Tom is pointing out, that SET is ignored if the
transaction has already aborted:

	test=> begin;
	BEGIN
	test=> lkjasdf;
	ERROR:  parser: parse error at or near "lkjasdf"
	test=> set server_min_messages = 'log';
	WARNING:  current transaction is aborted, queries ignored until end of
	transaction block
	*ABORT STATE*
	test=> 

so if the transaction aborted, the reset of the statement_timeout would
not happen.  The only way the application could code this would be with
this:

	BEGIN WORK;
	query;
	SET statement_timeout = 4;
	query;
	SET statement_timeout = 0;
	query;
	COMMIT;
	SET statement_timeout = 0;

Basically, it does the reset twice, once assuming the transaction
doesn't abort, and another assuming it does abort.  Is this something
that the JDBC and ODBC drivers can do automatically?

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

Re: timeout implementation issues

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> — 2002-04-02T19:17:20Z

On Tue, 2 Apr 2002, Bruce Momjian wrote:

> 	BEGIN WORK;
> 	query;
> 	SET statement_timeout = 4;
> 	query;
> 	SET statement_timeout = 0;
> 	query;
> 	COMMIT;
> 	SET statement_timeout = 0;
> 
> Basically, it does the reset twice, once assuming the transaction
> doesn't abort, and another assuming it does abort.  Is this something
> that the JDBC and ODBC drivers can do automatically?

I can't speak for ODBC. Seems like in JDBC, Connection::commit() would
call code clearing the timeout, and Statement::executeQuery() and
executeUpdate() would do the same.

j

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-02T19:39:08Z

Jessica Perry Hekman wrote:
> On Tue, 2 Apr 2002, Bruce Momjian wrote:
> 
> > 	BEGIN WORK;
> > 	query;
> > 	SET statement_timeout = 4;
> > 	query;
> > 	SET statement_timeout = 0;
> > 	query;
> > 	COMMIT;
> > 	SET statement_timeout = 0;
> > 
> > Basically, it does the reset twice, once assuming the transaction
> > doesn't abort, and another assuming it does abort.  Is this something
> > that the JDBC and ODBC drivers can do automatically?
> 
> I can't speak for ODBC. Seems like in JDBC, Connection::commit() would
> call code clearing the timeout, and Statement::executeQuery() and
> executeUpdate() would do the same.

Well, then a SET variable would work fine for statement-level queries. 
Just add the part for commit/abort transaction.

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

Re: timeout implementation issues

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> — 2002-04-03T15:26:04Z

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.

j

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-03T18:36:33Z

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@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-03T20:03:23Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Does anyone know the ramifications of allowing SET to work in an aborted
> transaction?

This is not an option.

The case that will definitely Not Work is SET variables whose setting
or checking requires database accesses.  The new search_path variable
certainly works that way; not sure if there are any other cases at the
moment, but I'd not like to say that there can never be any such
variables.

			regards, tom lane

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-04T01:25:47Z

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.

I wasn't able to follow this thread sorry.
ODBC has QUERY_TIMEOUT and CONNECTION_TIMEOUT.

> 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,

Why should the timeout be reset automatically ?

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-04T02:21:53Z

> > > 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.
> 
> I wasn't able to follow this thread sorry.
> ODBC has QUERY_TIMEOUT and CONNECTION_TIMEOUT.
> 
> > 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,
> 
> Why should the timeout be reset automatically ?

It doesn't need to be reset automatically, but the problem is that if
you are doing a timeout for single statement in a transaction, and that
statement aborts the transaction, the SET command after it to reset the
timeout fails.

I am attaching the email that describes the issue.

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

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-04T14:23:39Z

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@candle.pha.pa.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@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

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

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-04T15:25:10Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I think we have only a few options:

You forgot

	o  Do nothing.

IMHO the current behavior is not broken, and does not need fixed.
All of the options you suggest are surely more broken than the current
behavior.

> 	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.)

RESET would certainly not be a desirable behavior.  If we want SET vars
to roll back on abort, then they should roll back --- ie, resume their
transaction-start-time values.  But I doubt it's worth the trouble.
That behavior would do nothing to help JDBC implement timeouts, since
they'd still need to change the value again explicitly after successful
transaction completion.

			regards, tom lane

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-04T19:11:55Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I think we have only a few options:
> 
> You forgot
> 
> 	o  Do nothing.
> 
> IMHO the current behavior is not broken, and does not need fixed.
> All of the options you suggest are surely more broken than the current
> behavior.

I think it is broken.  What logic is there that SET before transaction
abort is performed, but after abort it is ignored?  What if someone
wants a specific optimizer parameter for a statement in a transaction,
like geqo_* or enable_seqscan off, and they perform the SET before the
statement OK but if the statement fails, the SET after it is ignored.
That doesn't seem like very normal behavior to me.

We are seeing this in the timeout case, but in fact the other SET
commands when run in a transaction have the same problem.

> > 	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.)
> 
> RESET would certainly not be a desirable behavior.  If we want SET vars
> to roll back on abort, then they should roll back --- ie, resume their
> transaction-start-time values.  But I doubt it's worth the trouble.
> That behavior would do nothing to help JDBC implement timeouts, since
> they'd still need to change the value again explicitly after successful
> transaction completion.

Yes, I now think that saving the SET commands that are ignored in a
transaction and running them _after_ the transaction completes may be
the best thing.  They can be stored as C strings in a stable memory
context and just run on transaction completion.

If we don't somehow get this to work, how do we do timeouts, which we
all know we should have?

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

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-04T19:52:29Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Yes, I now think that saving the SET commands that are ignored in a
> transaction and running them _after_ the transaction completes may be
> the best thing.

No, that's just plain ridiculous.  If you want to change the semantics
of SET, then make it work *correctly*, viz like an SQL statement: roll
it back on transaction abort.  Otherwise leave it alone.

> If we don't somehow get this to work, how do we do timeouts, which we
> all know we should have?

This is utterly unrelated to timeouts.  With or without any changes in
SET behavior, JDBC would need to issue a SET after completion of the
transaction if they wanted to revert a query_timeout variable to the
no-timeout state.

			regards, tom lane

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-04T21:18:01Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Yes, I now think that saving the SET commands that are ignored in a
> > transaction and running them _after_ the transaction completes may be
> > the best thing.
> 
> No, that's just plain ridiculous.  If you want to change the semantics

No more ridiculous than what we have now.

> of SET, then make it work *correctly*, viz like an SQL statement: roll
> it back on transaction abort.  Otherwise leave it alone.

I am not going to leave it alone based only on your say-so, Tom.

> > If we don't somehow get this to work, how do we do timeouts, which we
> > all know we should have?
> 
> This is utterly unrelated to timeouts.  With or without any changes in
> SET behavior, JDBC would need to issue a SET after completion of the
> transaction if they wanted to revert a query_timeout variable to the
> no-timeout state.

"Utterly unrelated?"  No.  If we can get SET to work properly in
transactions, jdbc can cleanly issue SET timeout=4, statement, SET
timeout=0.  Without it, using SET for timeout is a problem.  That's how
we got to this issue in the first place.

I am still looking for a constructive idea on how we can get this to
work, rather than calling my ideas "ridiculous".

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

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-04T22:33:27Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I am still looking for a constructive idea on how we can get this to
> work, rather than calling my ideas "ridiculous".

We know very well how to make it work: JDBC can issue a SET timeout = 0
after exiting the transaction.  You're proposing to change the semantics
of SET into something quite bizarre in order to allow JDBC to not have
to work as hard.  I think that's a bad tradeoff.

			regards, tom lane

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-04T23:48:24Z

Tom Lane wrote:
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I am still looking for a constructive idea on how we can get this to
> > work, rather than calling my ideas "ridiculous".
> 
> We know very well how to make it work: JDBC can issue a SET timeout = 0
> after exiting the transaction.  You're proposing to change the semantics
> of SET into something quite bizarre in order to allow JDBC to not have
> to work as hard.  I think that's a bad tradeoff.

Or we don't have to reset the timeout at all.
For example when we are about to issue a command, we
can check if the requested timeout is different from
the current server's timeout. We don't have to (re)set
the timeout unless they are different.

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-05T03:22:57Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I am still looking for a constructive idea on how we can get this to
> > work, rather than calling my ideas "ridiculous".
> 
> We know very well how to make it work: JDBC can issue a SET timeout = 0
> after exiting the transaction.  You're proposing to change the semantics
> of SET into something quite bizarre in order to allow JDBC to not have
> to work as hard.  I think that's a bad tradeoff.

It that acceptable to the JDBC folks?  It requires two "SET timeout = 0"
statements, one after the statement in the transaction, and another
after the transaction COMMIT WORK.

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

Re: timeout implementation issues

Jan Wieck <janwieck@yahoo.com> — 2002-04-05T15:04:09Z

Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Yes, I now think that saving the SET commands that are ignored in a
> > > transaction and running them _after_ the transaction completes may be
> > > the best thing.
> >
> > No, that's just plain ridiculous.  If you want to change the semantics
>
> No more ridiculous than what we have now.
>
> > of SET, then make it work *correctly*, viz like an SQL statement: roll
> > it back on transaction abort.  Otherwise leave it alone.
>
> I am not going to leave it alone based only on your say-so, Tom.

    I  have to agree with Tom here. It's not right to hack up SET
    to be accepted in transaction abort state. Nor is it right to
    queue  up  SET  requests  then. If those queued SET's lead to
    errors, when do you report them? On ROLLBACK?

    If at all, SET commands should behave like  everything  else.
    If done inside a transaction, they have to rollback.

> > > If we don't somehow get this to work, how do we do timeouts, which we
> > > all know we should have?
> >
> > This is utterly unrelated to timeouts.  With or without any changes in
> > SET behavior, JDBC would need to issue a SET after completion of the
> > transaction if they wanted to revert a query_timeout variable to the
> > no-timeout state.
>
> "Utterly unrelated?"  No.  If we can get SET to work properly in
> transactions, jdbc can cleanly issue SET timeout=4, statement, SET
> timeout=0.  Without it, using SET for timeout is a problem.  That's how
> we got to this issue in the first place.

    Could  we  get  out  of  this  by  defining that "timeout" is
    automatically reset at next statement end? So that the entire
    thing is

        SET timeout=4;
        SELECT ...;
        -- We're back in no-timeout

    And  that it doesn't matter if we're in a transaction, if the
    statement aborts, yadda yadda...


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: timeout implementation issues

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> — 2002-04-05T15:13:48Z

On Thu, 4 Apr 2002, Bruce Momjian wrote:

> It that acceptable to the JDBC folks?  It requires two "SET timeout = 0"
> statements, one after the statement in the transaction, and another
> after the transaction COMMIT WORK.

That's fine, though probably about as much work as just implementing the
whole thing in JDBC.

j

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-05T16:19:04Z

Jan Wieck <janwieck@yahoo.com> writes:
>     Could  we  get  out  of  this  by  defining that "timeout" is
>     automatically reset at next statement end?

I was hoping to avoid that, because it seems like a wart.  OTOH,
it'd be less of a wart than the global changes of semantics that
Bruce is proposing :-(

How exactly would you make this happen?  The simplest way I can think of
to do it (reset timeout in outer loop in postgres.c) would not work,
because it'd reset the timeout as soon as the SET statement completes.
How would you get the setting to survive for exactly one additional
statement?

			regards, tom lane

Re: timeout implementation issues

Ross Reedstrom <reedstrm@rice.edu> — 2002-04-05T17:23:26Z

On Fri, Apr 05, 2002 at 11:19:04AM -0500, Tom Lane wrote:
> Jan Wieck <janwieck@yahoo.com> writes:
> >     Could  we  get  out  of  this  by  defining that "timeout" is
> >     automatically reset at next statement end?
> 
> I was hoping to avoid that, because it seems like a wart.  OTOH,
> it'd be less of a wart than the global changes of semantics that
> Bruce is proposing :-(
> 
> How exactly would you make this happen?  The simplest way I can think of
> to do it (reset timeout in outer loop in postgres.c) would not work,
> because it'd reset the timeout as soon as the SET statement completes.
> How would you get the setting to survive for exactly one additional
> statement?

How about not messing with the SET, but adding it to the SELECT syntax
itself? a "WITH TIMEOUT" clause?

This is the first of the (proposed) SET variables that affects query
performance that is not a 'twiddle with the internals because something
is really wrong' hack (or debugging tool, if you will) Argueably,
those also suffer from the punching through the transaction problem:
I'd certainly hate (for example) to have sequential scans disabled for
an entire connection because one gnarly query that the optimizer guesses
wrong on died, and my reset got ignored.  I'd hate it, but understand
that it's a crufty hack to get around a problem, and just deal with
resetting the transaction/connection.

Timeouts, on the other hand, are a much more respectable mainline sort
of extension, apparently required for certain standards (The JDBC people
started this discussion, right?). They should be fully supported by the
transactional machinery, however that is decided. If that means all
SETs become transactional, I don't really see a problem with that.

Or, as I suggested above, extend the SELECT (and other querys?) syntax
seems reasonable. More so than the non-standard 'use this index, really'
types of extensions that other RDBMSs provide, that we've rightly avoided.

Thoughts?

Ross

Re: timeout implementation issues

Jan Wieck <janwieck@yahoo.com> — 2002-04-05T18:53:56Z

Tom Lane wrote:
> Jan Wieck <janwieck@yahoo.com> writes:
> >     Could  we  get  out  of  this  by  defining that "timeout" is
> >     automatically reset at next statement end?
>
> I was hoping to avoid that, because it seems like a wart.  OTOH,
> it'd be less of a wart than the global changes of semantics that
> Bruce is proposing :-(
>
> How exactly would you make this happen?  The simplest way I can think of
> to do it (reset timeout in outer loop in postgres.c) would not work,
> because it'd reset the timeout as soon as the SET statement completes.
> How would you get the setting to survive for exactly one additional
> statement?

    I  would  vote  for a general callback registering mechanism,
    where you can specify an event,  a  function  and  an  opaque
    pointer.  Possible events then would be end of statement, end
    of transaction, commit, abort, regular end of session.

    Sure, it looks  like  total  overkill  for  this  minor  JDBC
    problem.   But  I  like  general  support structures to be in
    place early.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-05T19:13:26Z

Jan Wieck <janwieck@yahoo.com> writes:
>     If at all, SET commands should behave like  everything  else.
>     If done inside a transaction, they have to rollback.

I have thought of a scenario that may be sufficient to justify fixing
SETs to roll back on transaction abort.  Consider

	BEGIN;

	CREATE SCHEMA foo;

	SET search_path = 'foo, public';

	ROLLBACK;

As the code stands, this will leave you with an invalid search path.
(What's worse, if you now execute CREATE TABLE, it will happily create
tables belonging to the vanished namespace foo.  Everything will seem
to work fine ... until you try to find those tables again in a new
session ...)

It seems clear to me that SET *should* roll back on abort.  Just a
matter of how important is it to fix.

			regards, tom lane

Re: timeout implementation issues

Jan Wieck <janwieck@yahoo.com> — 2002-04-05T19:22:02Z

Ross J. Reedstrom wrote:
> On Fri, Apr 05, 2002 at 11:19:04AM -0500, Tom Lane wrote:
> > Jan Wieck <janwieck@yahoo.com> writes:
> > >     Could  we  get  out  of  this  by  defining that "timeout" is
> > >     automatically reset at next statement end?
> >
> > I was hoping to avoid that, because it seems like a wart.  OTOH,
> > it'd be less of a wart than the global changes of semantics that
> > Bruce is proposing :-(
> >
> > How exactly would you make this happen?  The simplest way I can think of
> > to do it (reset timeout in outer loop in postgres.c) would not work,
> > because it'd reset the timeout as soon as the SET statement completes.
> > How would you get the setting to survive for exactly one additional
> > statement?
>
> How about not messing with the SET, but adding it to the SELECT syntax
> itself? a "WITH TIMEOUT" clause?

    Only SELECT? I thought all DML-statements should honour it.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-06T01:32:47Z

> Or, as I suggested above, extend the SELECT (and other querys?) syntax
> seems reasonable. More so than the non-standard 'use this index, really'
> types of extensions that other RDBMSs provide, that we've rightly avoided.

I think we need timeout for all statement.

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

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-06T01:33:37Z

Tom Lane wrote:
> Jan Wieck <janwieck@yahoo.com> writes:
> >     Could  we  get  out  of  this  by  defining that "timeout" is
> >     automatically reset at next statement end?
> 
> I was hoping to avoid that, because it seems like a wart.  OTOH,
> it'd be less of a wart than the global changes of semantics that
> Bruce is proposing :-(
> 
> How exactly would you make this happen?  The simplest way I can think of
> to do it (reset timeout in outer loop in postgres.c) would not work,
> because it'd reset the timeout as soon as the SET statement completes.
> How would you get the setting to survive for exactly one additional
> statement?

Sure, you could reset it, but there are going to be cases where you want
to do a timeout=6000 for the entire session.  If it resets after the
first statement, this is hard to do.

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

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-06T01:38:58Z

Tom Lane wrote:
> Jan Wieck <janwieck@yahoo.com> writes:
> >     If at all, SET commands should behave like  everything  else.
> >     If done inside a transaction, they have to rollback.
> 
> I have thought of a scenario that may be sufficient to justify fixing
> SETs to roll back on transaction abort.  Consider
> 
> 	BEGIN;
> 
> 	CREATE SCHEMA foo;
> 
> 	SET search_path = 'foo, public';
> 
> 	ROLLBACK;
> 
> As the code stands, this will leave you with an invalid search path.
> (What's worse, if you now execute CREATE TABLE, it will happily create
> tables belonging to the vanished namespace foo.  Everything will seem
> to work fine ... until you try to find those tables again in a new
> session ...)
> 
> It seems clear to me that SET *should* roll back on abort.  Just a
> matter of how important is it to fix.

That was my point, that having SET work pre-abort and ignored post-abort
is broken itself, whether we implement timeout or not.  Before we had
tuple-reading SET variables, it probably didn't matter, but now with
schemas, I can see it is more of an issue.

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

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-06T09:14:53Z

> -----Original Message-----
> From: Tom Lane
> 
> Jan Wieck <janwieck@yahoo.com> writes:
> >     Could  we  get  out  of  this  by  defining that "timeout" is
> >     automatically reset at next statement end?
> 
> I was hoping to avoid that, because it seems like a wart.  OTOH,
> it'd be less of a wart than the global changes of semantics that
> Bruce is proposing :-(

Probably I'm misunderstanding this thread.
Why must the query_timeout be reset particularly ?
What's wrong with simply issueing set query_timeout
command just before every query ?

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-06T22:17:01Z

Hiroshi Inoue wrote:
> > -----Original Message-----
> > From: Tom Lane
> > 
> > Jan Wieck <janwieck@yahoo.com> writes:
> > >     Could  we  get  out  of  this  by  defining that "timeout" is
> > >     automatically reset at next statement end?
> > 
> > I was hoping to avoid that, because it seems like a wart.  OTOH,
> > it'd be less of a wart than the global changes of semantics that
> > Bruce is proposing :-(
> 
> Probably I'm misunderstanding this thread.
> Why must the query_timeout be reset particularly ?
> What's wrong with simply issueing set query_timeout
> command just before every query ?

You could do that, but we also imagine cases where people would want to
set a timeout for each query in an entire session.

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

Re: timeout implementation issues

Jessica Perry Hekman <jphekman@dynamicdiagrams.com> — 2002-04-06T22:23:00Z

On Sat, 6 Apr 2002, Bruce Momjian wrote:

> > What's wrong with simply issueing set query_timeout
> > command just before every query ?
> 
> You could do that, but we also imagine cases where people would want to
> set a timeout for each query in an entire session.

One approach might be for the interface to take care of setting the query
timeout before each query, and just ask the backend to handle timeouts
per-query. So from the user's perspective, session-level timeouts would
exist, but the backend would not have to worry about rolling back
timeouts.

j

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-06T23:16:22Z

Jessica Perry Hekman wrote:
> On Sat, 6 Apr 2002, Bruce Momjian wrote:
> 
> > > What's wrong with simply issueing set query_timeout
> > > command just before every query ?
> > 
> > You could do that, but we also imagine cases where people would want to
> > set a timeout for each query in an entire session.
> 
> One approach might be for the interface to take care of setting the query
> timeout before each query, and just ask the backend to handle timeouts
> per-query. So from the user's perspective, session-level timeouts would
> exist, but the backend would not have to worry about rolling back
> timeouts.

Yes, that would work, but libpq and psql would have trouble doing full
session timeouts.

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

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-07T01:40:14Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> One approach might be for the interface to take care of setting the query
>> timeout before each query, and just ask the backend to handle timeouts
>> per-query. So from the user's perspective, session-level timeouts would
>> exist, but the backend would not have to worry about rolling back
>> timeouts.

> Yes, that would work, but libpq and psql would have trouble doing full
> session timeouts.

From the backend's perspective it'd be a *lot* cleaner to support
persistent timeouts (good 'til canceled) than one-shots.  If that's
the choice then let's let the frontend library worry about implementing
one-shots.

Note: I am now pretty well convinced that we *must* fix SET to roll back
to start-of-transaction settings on transaction abort.  If we do that,
at least some of the difficulty disappears for JDBC to handle one-shot
timeouts by issuing SETs before and after the target query against a
query_timeout variable that otherwise acts like a good-til-canceled
setting.  Can we all compromise on that?

			regards, tom lane

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-07T01:59:16Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> One approach might be for the interface to take care of setting the query
> >> timeout before each query, and just ask the backend to handle timeouts
> >> per-query. So from the user's perspective, session-level timeouts would
> >> exist, but the backend would not have to worry about rolling back
> >> timeouts.
> 
> > Yes, that would work, but libpq and psql would have trouble doing full
> > session timeouts.
> 
> >From the backend's perspective it'd be a *lot* cleaner to support
> persistent timeouts (good 'til canceled) than one-shots.  If that's
> the choice then let's let the frontend library worry about implementing
> one-shots.
> 
> Note: I am now pretty well convinced that we *must* fix SET to roll back
> to start-of-transaction settings on transaction abort.  If we do that,
> at least some of the difficulty disappears for JDBC to handle one-shot
> timeouts by issuing SETs before and after the target query against a
> query_timeout variable that otherwise acts like a good-til-canceled
> setting.  Can we all compromise on that?

Added to TODO:

	* Abort SET changes made in aborted transactions                 

We do have on_shmem_exit and on_proc_exit function call queues.  Seems
we will need SET to create a queue of function calls containing previous
values of variables SEt in multi-statement transactions.  If we execute
the queue in last-in-first-out order, the variables will be restored
properly.


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

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-07T02:14:36Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We do have on_shmem_exit and on_proc_exit function call queues.  Seems
> we will need SET to create a queue of function calls containing previous
> values of variables SEt in multi-statement transactions.  If we execute
> the queue in last-in-first-out order, the variables will be restored
> properly.

That's most certainly the hard way.  I was planning to just make GUC
save a spare copy of the start-of-transaction value of each variable.

			regards, tom lane

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-07T02:15:50Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > We do have on_shmem_exit and on_proc_exit function call queues.  Seems
> > we will need SET to create a queue of function calls containing previous
> > values of variables SEt in multi-statement transactions.  If we execute
> > the queue in last-in-first-out order, the variables will be restored
> > properly.
> 
> That's most certainly the hard way.  I was planning to just make GUC
> save a spare copy of the start-of-transaction value of each variable.

Ewe, I was hoping for something with zero overhead for the non-SET case.
Can we trigger the save for the first SET in the transaction?

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

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-07T02:20:39Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Ewe, I was hoping for something with zero overhead for the non-SET case.

Well, a function call and immediate return if no SET has been executed
in the current xact seems low enough overhead to me.  We'll just keep
a flag showing whether there's anything to do.

			regards, tom lane

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-07T02:22:27Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Ewe, I was hoping for something with zero overhead for the non-SET case.
> 
> Well, a function call and immediate return if no SET has been executed
> in the current xact seems low enough overhead to me.  We'll just keep
> a flag showing whether there's anything to do.

Oh, I thought you were going to save all the GUC variables on
transaction start.  I now assume you are going to have one field per
variable for the pre-xact value.  That is fine.

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

Re: timeout implementation issues

Barry Lind <barry@xythos.com> — 2002-04-07T04:40:22Z

Tom Lane wrote:
> Note: I am now pretty well convinced that we *must* fix SET to roll back
> to start-of-transaction settings on transaction abort.  If we do that,
> at least some of the difficulty disappears for JDBC to handle one-shot
> timeouts by issuing SETs before and after the target query against a
> query_timeout variable that otherwise acts like a good-til-canceled
> setting.  Can we all compromise on that?
> 

This plan should work well for JDBC.  (It actually makes the code on the 
jdbc side pretty easy).

thanks,
--Barry

Re: timeout implementation issues

Peter Eisentraut <peter_e@gmx.net> — 2002-04-07T05:09:10Z

Tom Lane writes:

> Note: I am now pretty well convinced that we *must* fix SET to roll back
> to start-of-transaction settings on transaction abort.  If we do that,
> at least some of the difficulty disappears for JDBC to handle one-shot
> timeouts by issuing SETs before and after the target query against a
> query_timeout variable that otherwise acts like a good-til-canceled
> setting.  Can we all compromise on that?

No.

I agree that there may be some variables that must be rolled back, or
where automatic reset on transaction end may be desirable (note that these
are two different things), but for some variables it's completely
nonsensical.  Those variables describe session characteristics, not
database state.  For instance, time zone, default_transaction_isolation.
Or consider you're raising the debug level, but it gets reset during
commit so you can't debug the commit process.  Or in the future we may
have some SQL-compatible always-in-transaction mode which would mean that
you could never set any variable to last.

If you want something that's transaction-specific, invent a new mechanism.
Hook in the set transaction isolation level command while you're at it.
But don't break everything that's worked so far.

-- 
Peter Eisentraut   peter_e@gmx.net

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-07T05:32:04Z

Peter Eisentraut <peter_e@gmx.net> writes:
>> Can we all compromise on that?

> No.

Oh dear...

> I agree that there may be some variables that must be rolled back, or
> where automatic reset on transaction end may be desirable (note that these
> are two different things), but for some variables it's completely
> nonsensical.  Those variables describe session characteristics, not
> database state.  For instance, time zone, default_transaction_isolation.

Uh, why?  I do not see why it's unreasonable for
	BEGIN;
	SET time_zone = whatever;
	ROLLBACK;
to be a no-op.  The fact that we haven't done that historically doesn't
count for much (unless your argument is "backwards compatibility" ...
but you didn't say that).  Not long ago we couldn't roll back a DROP
TABLE command; but that didn't make it right.

> Or consider you're raising the debug level, but it gets reset during
> commit so you can't debug the commit process.

It wouldn't get reset during commit, so I assume you really meant you
wanted to debug an abort problem.  But even there, what's the problem?
Set the variable *before* you enter the transaction that will abort.

> Or in the future we may
> have some SQL-compatible always-in-transaction mode which would mean that
> you could never set any variable to last.

Only if this mode prevents you from ever committing anything.  Somehow
I doubt that that's either SQL-compatible or useful.

> If you want something that's transaction-specific, invent a new mechanism.

I didn't say "transaction specific".  I said that if you do a SET inside
a transaction block, and then the transaction is aborted, the effects of
the SET ought to roll back along with everything else you did inside
that transaction block.  I'm not seeing what the argument is against
this.

			regards, tom lane

Re: timeout implementation issues

Peter Eisentraut <peter_e@gmx.net> — 2002-04-07T06:01:07Z

Tom Lane writes:

> I didn't say "transaction specific".  I said that if you do a SET inside
> a transaction block, and then the transaction is aborted, the effects of
> the SET ought to roll back along with everything else you did inside
> that transaction block.  I'm not seeing what the argument is against
> this.

I consider SET variables metadata that are not affected by transactions.
I should be able to change my mind about my session preferences in the
middle of a transaction, no matter what happens to the data in it.  Say
somewhere in the middle of a long transaction I think, "I should really be
logging this stuff".  I turn a knob to do so, and the next command fails.
Is the failure logged?  In which order does the rollback happen?  What if
I want to continue logging?

If anything were to change I would like to continue accepting SET commands
after an error.  Of course, I would like to continue accepting any command
after an error, but that's a different debate.

I guess it's a matter of definition: Do you consider SET variables
database state or session metadata?  I think some are this and some are
that.  I'm not sure how to draw the line, but throwing everything from one
category into the other isn't my favorite solution.

-- 
Peter Eisentraut   peter_e@gmx.net

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-07T06:08:46Z

Peter Eisentraut <peter_e@gmx.net> writes:
> I consider SET variables metadata that are not affected by transactions.

Why?  Again, the fact that historically they've not acted that way isn't
sufficient reason for me.

> I should be able to change my mind about my session preferences in the
> middle of a transaction, no matter what happens to the data in it.  Say
> somewhere in the middle of a long transaction I think, "I should really be
> logging this stuff".  I turn a knob to do so, and the next command fails.
> Is the failure logged?  In which order does the rollback happen?  What if
> I want to continue logging?

Hm.  That's a slightly more interesting example than before ... but it
comes close to arguing that logging should be under transaction control.
Surely you'd not argue that a failed transaction should erase all its
entries from the postmaster log?  Why would you expect changes in log
levels to be retroactive?

> I guess it's a matter of definition: Do you consider SET variables
> database state or session metadata?  I think some are this and some are
> that.  I'm not sure how to draw the line, but throwing everything from one
> category into the other isn't my favorite solution.

You seem to be suggesting that we should make a variable-by-variable
decision about whether SET variables roll back on ABORT or not.  I think
that way madness lies; we could spend forever debating which vars are
which, and then who will remember without consulting the documentation?

I feel we should just do it.  Yeah, there might be some corner cases
where it's not the ideal behavior; but you haven't convinced me that
there are more cases where it's bad than where it's good. You sure
haven't convinced me that it's worth making SET's behavior
nigh-unpredictable-without-a-manual, which is what per-variable behavior
would be.

			regards, tom lane

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-07T07:59:16Z

> -----Original Message-----
> From: Peter Eisentraut [mailto:peter_e@gmx.net]
> 
> 
> I guess it's a matter of definition: Do you consider SET variables
> database state or session metadata?  

Session metadata IMHO. If there are(would be) database state
variables we should introduce another command for them.
For example I don't think QUERY_TIMEOUT is such a variable.
As I mentioned many times we can set QUERY_TIMEOUT before
each query. If the overhead is an issue we can keep track of the
varaible and reduce the command calls to minimum easily. 

regards,
Hiroshi Inoue

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-07T07:59:38Z

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> 
> Hiroshi Inoue wrote:
> > > -----Original Message-----
> > > From: Tom Lane
> > > 
> > > Jan Wieck <janwieck@yahoo.com> writes:
> > > >     Could  we  get  out  of  this  by  defining that "timeout" is
> > > >     automatically reset at next statement end?
> > > 
> > > I was hoping to avoid that, because it seems like a wart.  OTOH,
> > > it'd be less of a wart than the global changes of semantics that
> > > Bruce is proposing :-(
> > 
> > Probably I'm misunderstanding this thread.
> > Why must the query_timeout be reset particularly ?
> > What's wrong with simply issueing set query_timeout
> > command just before every query ?
> 
> You could do that, but we also imagine cases where people would want to
> set a timeout for each query in an entire session.

Sorry I couldn't understand your point.
It seems the simplest and the most certain way is to call 
'SET QUERY_TIMEOUT per query. The way dosen't require
RESET at all.  Is the overhead an issue ?

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-07T23:27:52Z

> > > Probably I'm misunderstanding this thread.
> > > Why must the query_timeout be reset particularly ?
> > > What's wrong with simply issueing set query_timeout
> > > command just before every query ?
> > 
> > You could do that, but we also imagine cases where people would want to
> > set a timeout for each query in an entire session.
> 
> Sorry I couldn't understand your point.
> It seems the simplest and the most certain way is to call 
> 'SET QUERY_TIMEOUT per query. The way dosen't require
> RESET at all.  Is the overhead an issue ?

What about psql and libpq.  Doing a timeout before every query is a
pain.  I realize it can be done easily in ODBC and JDBC, but we need a
general timeout mechanism.

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

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-07T23:34:02Z

> > I guess it's a matter of definition: Do you consider SET variables
> > database state or session metadata?  I think some are this and some are
> > that.  I'm not sure how to draw the line, but throwing everything from one
> > category into the other isn't my favorite solution.
> 
> You seem to be suggesting that we should make a variable-by-variable
> decision about whether SET variables roll back on ABORT or not.  I think
> that way madness lies; we could spend forever debating which vars are
> which, and then who will remember without consulting the documentation?
> 
> I feel we should just do it.  Yeah, there might be some corner cases
> where it's not the ideal behavior; but you haven't convinced me that
> there are more cases where it's bad than where it's good. You sure
> haven't convinced me that it's worth making SET's behavior
> nigh-unpredictable-without-a-manual, which is what per-variable behavior
> would be.

I am with Tom on this one.  (Nice to see he is now arguing on my side.) 
Making different variables behave differently is clearly going to
confuse users.  The argument that we should allow SET to work when the
transaction is in ABORT state seems very wierd to me because we ignore
every other command in that state.  I think reversing out any SET's done
in an aborted transaction is the clear way to go.  If users want their
SET to not be affected by the transaction abort, they should put their
SET's outside a transaction;  seems pretty clear to me.

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

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-07T23:55:58Z

Bruce Momjian wrote:
> 
> > > > Probably I'm misunderstanding this thread.
> > > > Why must the query_timeout be reset particularly ?
> > > > What's wrong with simply issueing set query_timeout
> > > > command just before every query ?
> > >
> > > You could do that, but we also imagine cases where people would want to
> > > set a timeout for each query in an entire session.
> >
> > Sorry I couldn't understand your point.
> > It seems the simplest and the most certain way is to call
> > 'SET QUERY_TIMEOUT per query. The way dosen't require
> > RESET at all.  Is the overhead an issue ?
> 
> What about psql and libpq.  Doing a timeout before every query is a
> pain. 

Psql and libpq would simply issue the query according to the
user's request as they currently do. What's pain with it ?

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-08T00:11:29Z

> > > Sorry I couldn't understand your point.
> > > It seems the simplest and the most certain way is to call
> > > 'SET QUERY_TIMEOUT per query. The way dosen't require
> > > RESET at all.  Is the overhead an issue ?
> > 
> > What about psql and libpq.  Doing a timeout before every query is a
> > pain. 
> 
> Psql and libpq would simply issue the query according to the
> user's request as they currently do. What's pain with it ?

If they wanted to place a timeout on all queries in a session, they
would need a SET for every query, which seems like a pain.

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

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-08T00:27:44Z

Bruce Momjian wrote:
> 
> > > I guess it's a matter of definition: Do you consider SET variables
> > > database state or session metadata?  I think some are this and some are
> > > that.  I'm not sure how to draw the line, but throwing everything from one
> > > category into the other isn't my favorite solution.
> >
> > You seem to be suggesting that we should make a variable-by-variable
> > decision about whether SET variables roll back on ABORT or not.  I think
> > that way madness lies; we could spend forever debating which vars are
> > which, and then who will remember without consulting the documentation?
> >
> > I feel we should just do it.  Yeah, there might be some corner cases
> > where it's not the ideal behavior; but you haven't convinced me that
> > there are more cases where it's bad than where it's good. You sure
> > haven't convinced me that it's worth making SET's behavior
> > nigh-unpredictable-without-a-manual, which is what per-variable behavior
> > would be.
> 
> I am with Tom on this one.  (Nice to see he is now arguing on my side.)

I vote against you. If a variable is local to the session, you
can change it as you like without bothering any other user(session).
Automatic resetting of the varibales is rather confusing to me.

regards,
Hiroshi Inoue

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-08T00:33:33Z

Bruce Momjian wrote:
> 
> > > > Sorry I couldn't understand your point.
> > > > It seems the simplest and the most certain way is to call
> > > > 'SET QUERY_TIMEOUT per query. The way dosen't require
> > > > RESET at all.  Is the overhead an issue ?
> > >
> > > What about psql and libpq.  Doing a timeout before every query is a
> > > pain.
> >
> > Psql and libpq would simply issue the query according to the
> > user's request as they currently do. What's pain with it ?
> 
> If they wanted to place a timeout on all queries in a session, they
> would need a SET for every query, which seems like a pain.

Oh I see. You mean users' pain ?
If a user wants to place a timeout on all the query, he
would issue SET query_timeout command only once.

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-08T03:17:45Z

Hiroshi Inoue wrote:
> Bruce Momjian wrote:
> > 
> > > > I guess it's a matter of definition: Do you consider SET variables
> > > > database state or session metadata?  I think some are this and some are
> > > > that.  I'm not sure how to draw the line, but throwing everything from one
> > > > category into the other isn't my favorite solution.
> > >
> > > You seem to be suggesting that we should make a variable-by-variable
> > > decision about whether SET variables roll back on ABORT or not.  I think
> > > that way madness lies; we could spend forever debating which vars are
> > > which, and then who will remember without consulting the documentation?
> > >
> > > I feel we should just do it.  Yeah, there might be some corner cases
> > > where it's not the ideal behavior; but you haven't convinced me that
> > > there are more cases where it's bad than where it's good. You sure
> > > haven't convinced me that it's worth making SET's behavior
> > > nigh-unpredictable-without-a-manual, which is what per-variable behavior
> > > would be.
> > 
> > I am with Tom on this one.  (Nice to see he is now arguing on my side.)
> 
> I vote against you. If a variable is local to the session, you
> can change it as you like without bothering any other user(session).
> Automatic resetting of the varibales is rather confusing to me.

I don't see how this relates to other users.  All SET commands that can
be changed in psql are per backend, as far as I remember.

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

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-08T03:22:03Z

Hiroshi Inoue wrote:
> Bruce Momjian wrote:
> > 
> > > > > Sorry I couldn't understand your point.
> > > > > It seems the simplest and the most certain way is to call
> > > > > 'SET QUERY_TIMEOUT per query. The way dosen't require
> > > > > RESET at all.  Is the overhead an issue ?
> > > >
> > > > What about psql and libpq.  Doing a timeout before every query is a
> > > > pain.
> > >
> > > Psql and libpq would simply issue the query according to the
> > > user's request as they currently do. What's pain with it ?
> > 
> > If they wanted to place a timeout on all queries in a session, they
> > would need a SET for every query, which seems like a pain.
> 
> Oh I see. You mean users' pain ?

Sorry I was unclear.

> If a user wants to place a timeout on all the query, he
> would issue SET query_timeout command only once.

I am confused.  Above you state you want SET QUERY_TIMEOUT to be
per-query.  I assume you mean that the timeout applies for only the next
query and is turned off after that.  If you do that, it is hard to set a
maximum duration for all queries in your session, especially in psql or
libpq.

Also, I am not saying that the timeout is for the entire session, but
that the timeout makes sure that any query in the session that takes
longer than X milliseconds is automatically cancelled.

Please reply and let me know what you think.  I am sure I am missing
something in your comments.

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

Re: timeout implementation issues

Thomas Swan <tswan@olemiss.edu> — 2002-04-08T04:09:07Z

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <title></title>
</head>
<body>
Bruce Momjian wrote:<br>
<blockquote type="cite"
 cite="mid200204080317.g383Hj511314@candle.pha.pa.us">
  <pre wrap="">Hiroshi Inoue wrote:<br></pre>
  <blockquote type="cite">
    <pre wrap="">Bruce Momjian wrote:<br></pre>
    <blockquote type="cite">
      <blockquote type="cite">
        <blockquote type="cite">
          <pre wrap="">I guess it's a matter of definition: Do you consider SET variables<br>database state or session metadata?  I think some are this and some are<br>that.  I'm not sure how to draw the line, but throwing everything from one<br>category into the other isn't my favorite solution.<br></pre>
        </blockquote>
        <pre wrap="">You seem to be suggesting that we should make a variable-by-variable<br>decision about whether SET variables roll back on ABORT or not.  I think<br>that way madness lies; we could spend forever debating which vars are<br>which, and then who will remember without consulting the documentation?<br><br>I feel we should just do it.  Yeah, there might be some corner cases<br>where it's not the ideal behavior; but you haven't convinced me that<br>there are more cases where it's bad than where it's good. You sure<br>haven't convinced me that it's worth making SET's behavior<br>nigh-unpredictable-without-a-manual, which is what per-variable behavior<br>would be.<br></pre>
      </blockquote>
      <pre wrap="">I am with Tom on this one.  (Nice to see he is now arguing on my side.)<br></pre>
    </blockquote>
    <pre wrap="">I vote against you. If a variable is local to the session, you<br>can change it as you like without bothering any other user(session).<br>Automatic resetting of the varibales is rather confusing to me.<br></pre>
  </blockquote>
  <pre wrap=""><!----><br>I don't see how this relates to other users.  All SET commands that can<br>be changed in psql are per backend, as far as I remember.</pre>
</blockquote>
Per backend or per session?<br>
<blockquote type="cite"
 cite="mid200204080317.g383Hj511314@candle.pha.pa.us">
  <pre wrap=""><br><br></pre>
</blockquote>
<br>
<br>
</body>
</html>

Re: timeout implementation issues

Karel Zak <zakkr@zf.jcu.cz> — 2002-04-08T08:22:11Z

On Fri, Apr 05, 2002 at 08:32:47PM -0500, Bruce Momjian wrote:
> > Or, as I suggested above, extend the SELECT (and other querys?) syntax
> > seems reasonable. More so than the non-standard 'use this index, really'
> > types of extensions that other RDBMSs provide, that we've rightly avoided.
> 
> I think we need timeout for all statement.

 The Oracle has:

 CREATE PROFILE statement with for example following options:

    CONNECT_TIME
    IDLE_TIME


 I think system resource control per user is more useful than simple
 SET command. There is no problem add other limits like QUERY_TIMEOUT.
    
        Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Re: timeout implementation issues

Karel Zak <zakkr@zf.jcu.cz> — 2002-04-08T08:29:35Z

On Fri, Apr 05, 2002 at 02:13:26PM -0500, Tom Lane wrote:

> It seems clear to me that SET *should* roll back on abort.  Just a
> matter of how important is it to fix.

 I want control on this :-)


    SET valname = 'vatdata' ON ROLLBACK UNSET;
    
 or
    
    SET valname = 'vatdata';
    

        Karel
-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Re: timeout implementation issues

Karel Zak <zakkr@zf.jcu.cz> — 2002-04-08T08:44:37Z

On Sun, Apr 07, 2002 at 01:01:07AM -0500, Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > I didn't say "transaction specific".  I said that if you do a SET inside
> > a transaction block, and then the transaction is aborted, the effects of
> > the SET ought to roll back along with everything else you did inside
> > that transaction block.  I'm not seeing what the argument is against
> > this.
> 
> I consider SET variables metadata that are not affected by transactions.
> I should be able to change my mind about my session preferences in the
> middle of a transaction, no matter what happens to the data in it.  Say

 I agree with Peter. For example I have multi-encoding client program 
 that changing client-encoding in the middle of transaction and this
 change not depend on transaction. And the other thing: I have DB
 driver in an program there is not possible do SQL query outsite
 transaction.

 Is there some problem implement "SET ... ON ROLLBACK UNSET" ?

        Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-08T11:45:25Z

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> 
> Hiroshi Inoue wrote:
> > Bruce Momjian wrote:
> > > 
> > > > > > Sorry I couldn't understand your point.
> > > > > > It seems the simplest and the most certain way is to call
> > > > > > 'SET QUERY_TIMEOUT per query. The way dosen't require
> > > > > > RESET at all.  Is the overhead an issue ?
> > > > >
> > > > > What about psql and libpq.  Doing a timeout before every 
> query is a
> > > > > pain.
> > > >
> > > > Psql and libpq would simply issue the query according to the
> > > > user's request as they currently do. What's pain with it ?
> > > 
> > > If they wanted to place a timeout on all queries in a session, they
> > > would need a SET for every query, which seems like a pain.
> > 
> > Oh I see. You mean users' pain ?
> 
> Sorry I was unclear.
> 
> > If a user wants to place a timeout on all the query, he
> > would issue SET query_timeout command only once.
> 
> I am confused.  Above you state you want SET QUERY_TIMEOUT to be
> per-query. I assume you mean that the timeout applies for only the next
> query and is turned off after that.

Hmm there seems a misunderstanding between you and I but I
don't see what it is. Does *SET QUERY_TIMEOUT* start a timer in
your scenario ?  In my scenario *SET QUERY_TIMEOUT* only
registers the timeout value for subsequent queries.

regards,
Hiroshi inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-08T13:02:03Z

Hiroshi Inoue wrote:
> > I am confused.  Above you state you want SET QUERY_TIMEOUT to be
> > per-query. I assume you mean that the timeout applies for only the next
> > query and is turned off after that.
> 
> Hmm there seems a misunderstanding between you and I but I
> don't see what it is. Does *SET QUERY_TIMEOUT* start a timer in
> your scenario ?  In my scenario *SET QUERY_TIMEOUT* only
> registers the timeout value for subsequent queries.

SET QUERY_TIMEOUT does not start a timer.  It makes sure each query
after the SET is timed and automatically canceled if the single query
exceeds the timeout interval.

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

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-08T13:10:55Z

Karel Zak wrote:
>  I agree with Peter. For example I have multi-encoding client program 
>  that changing client-encoding in the middle of transaction and this
>  change not depend on transaction. And the other thing: I have DB
>  driver in an program there is not possible do SQL query outsite
>  transaction.

No problem executing a SET inside its own transaction.  The rollback
happens only if the SET fails, which for a single SEt command, should be
fine.

> 
>  Is there some problem implement "SET ... ON ROLLBACK UNSET" ?

Seems kind of strange.  If anything, I can imagine a NO ROLLBACK
capability.  However, because this can be easily done by executing the
SET in its own transaction, it seems like overengineering.

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

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-08T14:08:17Z

Karel Zak <zakkr@zf.jcu.cz> writes:
>  Is there some problem implement "SET ... ON ROLLBACK UNSET" ?

Yes.  See my previous example concerning search_path: that variable
MUST be rolled back at transaction abort, else we risk its value being
invalid.  We cannot offer the user a choice.

So far I have not seen one single example against SET rollback that
I thought was at all compelling.  In all cases you can simply issue
the SET in a separate transaction if you want to be sure that its
effects persist.  And there seems to be no consideration of the
possibility that applications might find SET rollback to be useful.
ISTM that the example with JDBC and query_timeout generalizes to other
parameters that you might want to set on a per-statement basis, such
as enable_seqscan or transform_null_equals.  Consider

	BEGIN;
	SET enable_seqscan = false;
	some-queries-that-might-fail;
	SET enable_seqscan = true;
	END;

This does not work as intended if the initial SET doesn't roll back
upon transaction failure.  Yeah, you can restructure it to

	SET enable_seqscan = false;
	BEGIN;
	some-queries-that-might-fail;
	END;
	SET enable_seqscan = true;

but what was that argument about some apps/drivers finding it
inconvenient to issue commands outside a transaction block?

			regards, tom lane

Re: timeout implementation issues

Jan Wieck <janwieck@yahoo.com> — 2002-04-08T14:15:18Z

Bruce Momjian wrote:
> > > > Sorry I couldn't understand your point.
> > > > It seems the simplest and the most certain way is to call
> > > > 'SET QUERY_TIMEOUT per query. The way dosen't require
> > > > RESET at all.  Is the overhead an issue ?
> > >
> > > What about psql and libpq.  Doing a timeout before every query is a
> > > pain.
> >
> > Psql and libpq would simply issue the query according to the
> > user's request as they currently do. What's pain with it ?
>
> If they wanted to place a timeout on all queries in a session, they
> would need a SET for every query, which seems like a pain.

    Er,  how  many  "applications" have you implemented by simply
    providing a schema and psql?

    I mean, users normally don't use psql. And if you do,  what's
    wrong  with  controlling  the timeout yourself and hitting ^C
    when "you" time out?  If you do it in a script, it's

        yy... p p p p p.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: timeout implementation issues

Jan Wieck <janwieck@yahoo.com> — 2002-04-08T14:46:11Z

Tom Lane wrote:
> Karel Zak <zakkr@zf.jcu.cz> writes:
> >  Is there some problem implement "SET ... ON ROLLBACK UNSET" ?
>
> Yes.  See my previous example concerning search_path: that variable
> MUST be rolled back at transaction abort, else we risk its value being
> invalid.  We cannot offer the user a choice.

    Not  really on topic, but I was wondering how you ensure that
    you correct the search path in case someone drops the schema?

    Is an invalid search path really that critical (read security
    issue)?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-08T15:08:31Z

Jan Wieck <janwieck@yahoo.com> writes:
>     Is an invalid search path really that critical (read security
>     issue)?

It's not a security issue (unless the OID counter wraps around soon
enough to let someone else get assigned the same OID for a namespace).
But it could be pretty annoying anyway, because the front element of
the search path is also the default creation target namespace.  You
could create a bunch of tables and then be unable to access them later
for lack of a way to name them.

I'm not really excited about establishing positive interlocks across
backends to prevent DROPping a namespace that someone else has in their
search path ... but I do want to handle the simple local-effect cases,
like rollback of creation of a namespace.

			regards, tom lane

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-08T15:28:42Z

Jan Wieck wrote:
> > > Psql and libpq would simply issue the query according to the
> > > user's request as they currently do. What's pain with it ?
> >
> > If they wanted to place a timeout on all queries in a session, they
> > would need a SET for every query, which seems like a pain.
> 
>     Er,  how  many  "applications" have you implemented by simply
>     providing a schema and psql?

Actually, I would assume nightly batch jobs are configured this way.

> 
>     I mean, users normally don't use psql. And if you do,  what's
>     wrong  with  controlling  the timeout yourself and hitting ^C
>     when "you" time out?  If you do it in a script, it's

Yes, clearly meaningless for interactive use.

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

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-08T15:29:58Z

Tom Lane wrote:
> This does not work as intended if the initial SET doesn't roll back
> upon transaction failure.  Yeah, you can restructure it to
> 
> 	SET enable_seqscan = false;
> 	BEGIN;
> 	some-queries-that-might-fail;
> 	END;
> 	SET enable_seqscan = true;
> 
> but what was that argument about some apps/drivers finding it
> inconvenient to issue commands outside a transaction block?

Yes, and if you want to place the SET on a single statement in a
multi-statement transaction, doing SET outside the transaction will not
work either because it will apply to all statements in the transaction.

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

Re: timeout implementation issues

Thomas Lockhart <lockhart@fourpalms.org> — 2002-04-08T15:32:50Z

> > I consider SET variables metadata that are not affected by transactions.
> Why?  Again, the fact that historically they've not acted that way isn't
> sufficient reason for me.

Hmm. Historically, SET controls behaviors *out of band* with the normal
transaction mechanisms. There is strong precedent for this mechanism
*because it is a useful concept*, not simply because it has always been
done this way.

*If* some aspects of SET take on transactional behavior, then this
should be *in addition to* the current global scope for those commands.

What problem are we trying to solve with this? The topic came up in a
discussion on implementing timeouts for JDBC. afaik it has not come up
*in any context* for the last seven years, so maybe we should settle
down a bit and refocus on the problem at hand...

                     - Thomas

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-08T16:07:04Z

Thomas Lockhart wrote:
> > > I consider SET variables metadata that are not affected by transactions.
> > Why?  Again, the fact that historically they've not acted that way isn't
> > sufficient reason for me.
> 
> Hmm. Historically, SET controls behaviors *out of band* with the normal
> transaction mechanisms. There is strong precedent for this mechanism
> *because it is a useful concept*, not simply because it has always been
> done this way.

OK, probably good time for summarization.  First, consider this:

	BEGIN WORK;
	SET something;
	query fails;
	SET something else;
	COMMIT WORK;

Under current behavior, the first SET is honored, while the second is
ignored because the transaction is in ABORT state.  I can see no logical
reason for this behavior.  We ignore normal queries during an ABORT
because the transaction can't possibly change any data because it is
aborted, and the previous non-SET statements in the transactions are
rolled back.  However, the SET commands are not.

The jdbc timeout issue is this:

	BEGIN WORK;
	SET query_timeout=20;
	query fails;
	SET query_timeout=0;
	COMMIT WORK;

In this case, with our current code, the first SET is done, but the
second is ignored.  To make this work, you would need this:

	BEGIN WORK;
	SET query_timeout=20;
	query fails;
	SET query_timeout=0;
	COMMIT WORK;
	SET query_timeout=0;

which seems kind of strange.  The last SET is needed because the query
may abort and the second SET ignored.

> *If* some aspects of SET take on transactional behavior, then this
> should be *in addition to* the current global scope for those commands.

My point is that SET already doesn't have session behavior because it is
ignored if the transaction has already aborted.

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

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-08T16:27:32Z

Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > OK, probably good time for summarization.  First, consider this:
> >
> > 	BEGIN WORK;
> > 	SET something;
> > 	query fails;
> > 	SET something else;
> > 	COMMIT WORK;
> >
> > Under current behavior, the first SET is honored, while the second is
> > ignored because the transaction is in ABORT state.  I can see no logical
> > reason for this behavior.
> 
> But that is not a shortcoming of the SET command.  The problem is that the
> system does not accept any commands after one command has failed in a
> transaction even though it could usefully do so.

Uh, yes, we could allow the second SET to succeed even in an aborted
transaction, but Tom says his schema stuff will not work in an aborted
state, so Tom/I figured the only other option was rollback of the first
SET.

> > The jdbc timeout issue is this:
> >
> >
> > 	BEGIN WORK;
> > 	SET query_timeout=20;
> > 	query fails;
> > 	SET query_timeout=0;
> > 	COMMIT WORK;
> >
> > In this case, with our current code, the first SET is done, but the
> > second is ignored.
> 
> Given appropriate functionality, you could rewrite this thus:
> 
> BEGIN WORK;
> SET FOR THIS TRANSACTION ONLY query_timeout=20;
> query;
> COMMIT WORK;

Yes, but why bother with that when rollback of the first SET is cleaner
and more predictable?

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

Re: timeout implementation issues

Peter Eisentraut <peter_e@gmx.net> — 2002-04-08T16:28:18Z

Bruce Momjian writes:

> OK, probably good time for summarization.  First, consider this:
>
> 	BEGIN WORK;
> 	SET something;
> 	query fails;
> 	SET something else;
> 	COMMIT WORK;
>
> Under current behavior, the first SET is honored, while the second is
> ignored because the transaction is in ABORT state.  I can see no logical
> reason for this behavior.

But that is not a shortcoming of the SET command.  The problem is that the
system does not accept any commands after one command has failed in a
transaction even though it could usefully do so.

> The jdbc timeout issue is this:
>
>
> 	BEGIN WORK;
> 	SET query_timeout=20;
> 	query fails;
> 	SET query_timeout=0;
> 	COMMIT WORK;
>
> In this case, with our current code, the first SET is done, but the
> second is ignored.

Given appropriate functionality, you could rewrite this thus:

BEGIN WORK;
SET FOR THIS TRANSACTION ONLY query_timeout=20;
query;
COMMIT WORK;

-- 
Peter Eisentraut   peter_e@gmx.net

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-08T16:32:52Z

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
>
> Hiroshi Inoue wrote:
> > > >
> > > > I feel we should just do it.  Yeah, there might be some corner cases
> > > > where it's not the ideal behavior; but you haven't convinced me that
> > > > there are more cases where it's bad than where it's good. You sure
> > > > haven't convinced me that it's worth making SET's behavior
> > > > nigh-unpredictable-without-a-manual, which is what
> per-variable behavior
> > > > would be.
> > >
> > > I am with Tom on this one.  (Nice to see he is now arguing on
> my side.)
> >
> > I vote against you. If a variable is local to the session, you
> > can change it as you like without bothering any other user(session).
> > Automatic resetting of the varibales is rather confusing to me.
>
> I don't see how this relates to other users.  All SET commands that can
> be changed in psql are per backend, as far as I remember.

Sorry for my poor explanation. What I meant is that *Rollback*
is to cancel the changes made to SQL-data or schemas
not to put back the variables which are local to the session.

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-08T16:35:41Z

Hiroshi Inoue wrote:
> > > I vote against you. If a variable is local to the session, you
> > > can change it as you like without bothering any other user(session).
> > > Automatic resetting of the varibales is rather confusing to me.
> >
> > I don't see how this relates to other users.  All SET commands that can
> > be changed in psql are per backend, as far as I remember.
> 
> Sorry for my poor explanation. What I meant is that *Rollback*
> is to cancel the changes made to SQL-data or schemas
> not to put back the variables which are local to the session.

OK, got it, so if someone makes a session change while in a transaction,
and the transaction aborts, should the SET be rolled back too?  If not,
then we should honor the SET's that happen after the transaction aborts.
However, Tom's schema changes require a db connection, so it is hard to
honor the SET's once the transaction aborts.  That is how we got to the
abort all SET's in an aborted transaction.

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

Re: timeout implementation issues

Jan Wieck <janwieck@yahoo.com> — 2002-04-08T16:57:13Z

Tom Lane wrote:
> Jan Wieck <janwieck@yahoo.com> writes:
> >     Is an invalid search path really that critical (read security
> >     issue)?
>
> It's not a security issue (unless the OID counter wraps around soon
> enough to let someone else get assigned the same OID for a namespace).
> But it could be pretty annoying anyway, because the front element of
> the search path is also the default creation target namespace.  You
> could create a bunch of tables and then be unable to access them later
> for lack of a way to name them.
>
> I'm not really excited about establishing positive interlocks across
> backends to prevent DROPping a namespace that someone else has in their
> search path ... but I do want to handle the simple local-effect cases,
> like rollback of creation of a namespace.

    How  are  namespaces different from any other objects?  Can I
    specify a foreign key reference to a table that was there  at
    some  time  in  the past? Can I create a view using functions
    that have been there last week?   Sure,  I  can  break  those
    objects  once  created  by dropping the underlying stuff, but
    that's another issue.

    If namespace dropping allows for  creation  of  objects  that
    cannot  be  dropped  afterwards any more, I would call that a
    bug or design flaw, which has to be fixed. Just preventing an
    invalid  search path resulting from a rollback operation like
    in your example is totally insufficient.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-08T17:03:41Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> But that is not a shortcoming of the SET command.  The problem is that the
>> system does not accept any commands after one command has failed in a
>> transaction even though it could usefully do so.

In a situation where the reason for failure was a syntax error, it seems
to me quite dangerous to try to execute any further commands; you may
not be executing what the user thought he typed.  So I'm leery of any
proposals that we allow SETs to execute in transaction-abort state,
even if the implementation could support it.

> Uh, yes, we could allow the second SET to succeed even in an aborted
> transaction, but Tom says his schema stuff will not work in an aborted
> state, so Tom/I figured the only other option was rollback of the first
> SET.

The search_path case is the main reason why I'm intent on changing
the behavior of SET; without that, I'd just leave well enough alone.
Possibly some will suggest that search_path shouldn't be a SET variable
because it needs to be able to be rolled back on error.  But what else
should it be?  It's definitely per-session status, not persistent
database state.  I don't much care for the notion of having SET act
differently for some variables than others, or requiring people to use
a different command for some variables than others.

			regards, tom lane

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-08T17:09:44Z

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> Sorry for my poor explanation. What I meant is that *Rollback*
> is to cancel the changes made to SQL-data or schemas
> not to put back the variables which are local to the session.

Uh, why?  Seems to me you are asserting as a given exactly the
point that is under debate.  Let me give a counterexample:

	BEGIN;
	CREATE TEMP TABLE foo;
	something-erroneous;
	END;

The creation of the temp table will be rolled back on error, no?
Now the temp table is certainly session local --- ideally our
implementation would not let any other session see any trace of
it at all.  (In practice it is visible if you know where to look,
but surely that's just an implementation artifact.)

If you argue that SETs should not roll back because they are
session-local, it seems to me that a logical consequence of that
position is that operations on temp tables should not roll back
either ... and that can hardly be deemed desirable.

			regards, tom lane

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-08T17:20:21Z

Jan Wieck <janwieck@yahoo.com> writes:
>     If namespace dropping allows for  creation  of  objects  that
>     cannot  be  dropped  afterwards any more, I would call that a
>     bug or design flaw, which has to be fixed.

I will not require schema support to wait upon the existence of
dependency checking, if that's what you're suggesting.

This does suggest an interesting hole in our thoughts so far about
dependency checking.  If someone is, say, trying to drop type T,
it's not really sufficient to verify that there are no existing
tables or functions referencing type T.  What of created but as yet
uncommitted objects?  Seems like a full defense would require being
able to obtain a lock on the object to be dropped, while creators
of references must obtain some conflicting lock that they hold until
they commit.  Right now we only have locks on tables ... seems like
that's not sufficient.

			regards, tom lane

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-08T17:56:08Z

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> 
> Hiroshi Inoue wrote:
> > > I am confused.  Above you state you want SET QUERY_TIMEOUT to be
> > > per-query. I assume you mean that the timeout applies for 
> only the next
> > > query and is turned off after that.
> > 
> > Hmm there seems a misunderstanding between you and I but I
> > don't see what it is. Does *SET QUERY_TIMEOUT* start a timer in
> > your scenario ?  In my scenario *SET QUERY_TIMEOUT* only
> > registers the timeout value for subsequent queries.
> 
> SET QUERY_TIMEOUT does not start a timer.  It makes sure each query
> after the SET is timed and automatically canceled if the single query
> exceeds the timeout interval.

OK using your example, one by one

  	BEGIN WORK;
 	SET query_timeout=20;
 	query fails;
 	SET query_timeout=0;

For what the SET was issued ?
What command is issued if the query was successful ?

 	COMMIT WORK;

regards,
Hiroshi Inoue

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-08T17:56:27Z

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> 
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > Sorry for my poor explanation. What I meant is that *Rollback*
> > is to

>> cancel the changes made to SQL-data or schemas

This line is a quote from SQL99 not my creation.
 
> > not to put back the variables which are local to the session.
> 
> Uh, why?  Seems to me you are asserting as a given exactly the
> point that is under debate.  Let me give a counterexample:
> 
> 	BEGIN;
> 	CREATE TEMP TABLE foo;
> 	something-erroneous;
> 	END;
> 
> The creation of the temp table will be rolled back on error, no?

??? TEMP TABLE is a SQL-data not a variable.
I don't think rolling back SETs makes things plain.

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-08T18:05:03Z

Hiroshi Inoue wrote:
> > -----Original Message-----
> > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> > 
> > Hiroshi Inoue wrote:
> > > > I am confused.  Above you state you want SET QUERY_TIMEOUT to be
> > > > per-query. I assume you mean that the timeout applies for 
> > only the next
> > > > query and is turned off after that.
> > > 
> > > Hmm there seems a misunderstanding between you and I but I
> > > don't see what it is. Does *SET QUERY_TIMEOUT* start a timer in
> > > your scenario ?  In my scenario *SET QUERY_TIMEOUT* only
> > > registers the timeout value for subsequent queries.
> > 
> > SET QUERY_TIMEOUT does not start a timer.  It makes sure each query
> > after the SET is timed and automatically canceled if the single query
> > exceeds the timeout interval.
> 
> OK using your example, one by one
> 
>   	BEGIN WORK;
>  	SET query_timeout=20;
>  	query fails;
>  	SET query_timeout=0;
> 
> For what the SET was issued ?
> What command is issued if the query was successful ?
> 
>  	COMMIT WORK;

Here, SET should only to the query labeled "query fails".  However,
right now, because the query failed, the second SET would not be seen,
and the timout would apply to all remaining queries in the session.


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

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-08T21:14:46Z

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> > 
> > OK using your example, one by one
> > 
> >   	BEGIN WORK;
> >  	SET query_timeout=20;
> >  	query fails;
> >  	SET query_timeout=0;
> > 
> > For what the SET was issued ?
> > What command is issued if the query was successful ?
> > 
> >  	COMMIT WORK;
> 
> Here, SET should only to the query labeled "query fails". 

Why should the SET query_timeout = 0 command be issued
only when the query failed ? Is it a JDBC driver's requirement
or some applications' requirements which uses the JDBC driver ?

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-08T21:15:41Z

Hiroshi Inoue wrote:
> > -----Original Message-----
> > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> > > 
> > > OK using your example, one by one
> > > 
> > >   	BEGIN WORK;
> > >  	SET query_timeout=20;
> > >  	query fails;
> > >  	SET query_timeout=0;
> > > 
> > > For what the SET was issued ?
> > > What command is issued if the query was successful ?
> > > 
> > >  	COMMIT WORK;
> > 
> > Here, SET should only to the query labeled "query fails". 
> 
> Why should the SET query_timeout = 0 command be issued
> only when the query failed ? Is it a JDBC driver's requirement
> or some applications' requirements which uses the JDBC driver ?

They want the timeout for only the one statement, so they have to set it
to non-zero before the statement, and to zero after the statement.  In
our current code, if the query fails, the setting to zero is ignored,
meaning all following queries have the timeout, even ones outside that
transaction.

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

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-08T23:38:31Z

Bruce Momjian wrote:
> 
> Hiroshi Inoue wrote:
> > > -----Original Message-----
> > > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> > > >
> > > > OK using your example, one by one
> > > >
> > > >           BEGIN WORK;
> > > >   SET query_timeout=20;
> > > >   query fails;
> > > >   SET query_timeout=0;
> > > >
> > > > For what the SET was issued ?
> > > > What command is issued if the query was successful ?
> > > >
> > > >   COMMIT WORK;
> > >
> > > Here, SET should only to the query labeled "query fails".
> >
> > Why should the SET query_timeout = 0 command be issued
> > only when the query failed ? Is it a JDBC driver's requirement
> > or some applications' requirements which uses the JDBC driver ?
> 
> They want the timeout for only the one statement, so they have to set it
> to non-zero before the statement, and to zero after the statement.

Does setQueryTimeout() issue a corresponding SET QUERY_TIMEOUT
command immediately in the scenario ?

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-09T00:35:48Z

Hiroshi Inoue wrote:
> > > Why should the SET query_timeout = 0 command be issued
> > > only when the query failed ? Is it a JDBC driver's requirement
> > > or some applications' requirements which uses the JDBC driver ?
> > 
> > They want the timeout for only the one statement, so they have to set it
> > to non-zero before the statement, and to zero after the statement.
> 
> Does setQueryTimeout() issue a corresponding SET QUERY_TIMEOUT
> command immediately in the scenario ?

Yes.  If we don't make the SET rollback-able, we have to do all sorts of
tricks in jdbc so aborted transactions get the proper SET value.

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

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-09T00:44:01Z

Bruce Momjian wrote:
> 
> Hiroshi Inoue wrote:
> > > > Why should the SET query_timeout = 0 command be issued
> > > > only when the query failed ? Is it a JDBC driver's requirement
> > > > or some applications' requirements which uses the JDBC driver ?
> > >
> > > They want the timeout for only the one statement, so they have to set it
> > > to non-zero before the statement, and to zero after the statement.
> >
> > Does setQueryTimeout() issue a corresponding SET QUERY_TIMEOUT
> > command immediately in the scenario ?
> 
> Yes.  If we don't make the SET rollback-able, we have to do all sorts of
> tricks in jdbc so aborted transactions get the proper SET value.

In my scenario, setQueryTimeout() only saves the timeout
value and issues the corrsponding SET QUERY_TIMEOUT command
immediately before each query if necessary.

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-09T00:45:46Z

Hiroshi Inoue wrote:
> Bruce Momjian wrote:
> > 
> > Hiroshi Inoue wrote:
> > > > > Why should the SET query_timeout = 0 command be issued
> > > > > only when the query failed ? Is it a JDBC driver's requirement
> > > > > or some applications' requirements which uses the JDBC driver ?
> > > >
> > > > They want the timeout for only the one statement, so they have to set it
> > > > to non-zero before the statement, and to zero after the statement.
> > >
> > > Does setQueryTimeout() issue a corresponding SET QUERY_TIMEOUT
> > > command immediately in the scenario ?
> > 
> > Yes.  If we don't make the SET rollback-able, we have to do all sorts of
> > tricks in jdbc so aborted transactions get the proper SET value.
> 
> In my scenario, setQueryTimeout() only saves the timeout
> value and issues the corrsponding SET QUERY_TIMEOUT command
> immediately before each query if necessary.

Yes, we can do that, but it requires an interface like odbc or jdbc.  It
is hard to use for libpq or psql.

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

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-09T01:06:12Z

Bruce Momjian wrote:
> 
> Hiroshi Inoue wrote:
> > Bruce Momjian wrote:

> > > > > They want the timeout for only the one statement, so they have to set it
> > > > > to non-zero before the statement, and to zero after the statement.
> > > >
> > > > Does setQueryTimeout() issue a corresponding SET QUERY_TIMEOUT
> > > > command immediately in the scenario ?
> > >
> > > Yes.  If we don't make the SET rollback-able, we have to do all sorts of
> > > tricks in jdbc so aborted transactions get the proper SET value.
> >
> > In my scenario, setQueryTimeout() only saves the timeout
> > value and issues the corrsponding SET QUERY_TIMEOUT command
> > immediately before each query if necessary.
> 
> Yes, we can do that,

Something like my scenario is needed because there could be
more than 1 statement objects with relatively different
query timeout at the same time in theory.

> but it requires an interface like odbc or jdbc.  It
> is hard to use for libpq or psql.

We shouldn't expect too much on psql in the first place
because it isn't procedural. I don't expect too much on
libpq either because it's a low level interface. However
applications which use libpq could do like odbc or jdbc
does. Or libpq could also provide a function which encap-
sulates the query timeout handling if necessary.

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-09T01:10:00Z

Hiroshi Inoue wrote:
> Bruce Momjian wrote:
> > 
> > Hiroshi Inoue wrote:
> > > Bruce Momjian wrote:
> 
> > > > > > They want the timeout for only the one statement, so they have to set it
> > > > > > to non-zero before the statement, and to zero after the statement.
> > > > >
> > > > > Does setQueryTimeout() issue a corresponding SET QUERY_TIMEOUT
> > > > > command immediately in the scenario ?
> > > >
> > > > Yes.  If we don't make the SET rollback-able, we have to do all sorts of
> > > > tricks in jdbc so aborted transactions get the proper SET value.
> > >
> > > In my scenario, setQueryTimeout() only saves the timeout
> > > value and issues the corrsponding SET QUERY_TIMEOUT command
> > > immediately before each query if necessary.
> > 
> > Yes, we can do that,
> 
> Something like my scenario is needed because there could be
> more than 1 statement objects with relatively different
> query timeout at the same time in theory.

Yes, if you want multiple timeouts, you clearly could go in that
direction.  Right now, we are considering only single-statement timing
and no one has asked for multiple timers.

> 
> > but it requires an interface like odbc or jdbc.  It
> > is hard to use for libpq or psql.
> 
> We shouldn't expect too much on psql in the first place
> because it isn't procedural. I don't expect too much on
> libpq either because it's a low level interface. However
> applications which use libpq could do like odbc or jdbc
> does. Or libpq could also provide a function which encap-
> sulates the query timeout handling if necessary.

I certainly would like _something_ that works in psql/libpq, and the
simple SET QUERY_TIMEOUT does work for them.   More sophisticated stuff
probably should be done in the application or interface.


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

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-09T01:58:10Z

Bruce Momjian wrote:
> 
> Hiroshi Inoue wrote:
> > Bruce Momjian wrote:
> > >
> > > Hiroshi Inoue wrote:
> > > > Bruce Momjian wrote:
> >
> > > > > > > They want the timeout for only the one statement, so they have to set it
> > > > > > > to non-zero before the statement, and to zero after the statement.
> > > > > >
> > > > > > Does setQueryTimeout() issue a corresponding SET QUERY_TIMEOUT
> > > > > > command immediately in the scenario ?
> > > > >
> > > > > Yes.  If we don't make the SET rollback-able, we have to do all sorts of
> > > > > tricks in jdbc so aborted transactions get the proper SET value.
> > > >
> > > > In my scenario, setQueryTimeout() only saves the timeout
> > > > value and issues the corrsponding SET QUERY_TIMEOUT command
> > > > immediately before each query if necessary.
> > >
> > > Yes, we can do that,
> >
> > Something like my scenario is needed because there could be
> > more than 1 statement objects with relatively different
> > query timeout at the same time in theory.
> 
> Yes, if you want multiple timeouts, you clearly could go in that
> direction.  Right now, we are considering only single-statement timing
> and no one has asked for multiple timers.

I don't ask multiple timers. ODBC driver would be able
to handle multiple timeouts without multiple timers in
my scenario.

> > > but it requires an interface like odbc or jdbc.  It
> > > is hard to use for libpq or psql.
> >
> > We shouldn't expect too much on psql in the first place
> > because it isn't procedural. I don't expect too much on
> > libpq either because it's a low level interface. However
> > applications which use libpq could do like odbc or jdbc
> > does. Or libpq could also provide a function which encap-
> > sulates the query timeout handling if necessary.
> 
> I certainly would like _something_ that works in psql/libpq,

Please don't make things complicated by sticking to such
low level interfaces.

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-09T01:59:01Z

Hiroshi Inoue wrote:
> > Yes, if you want multiple timeouts, you clearly could go in that
> > direction.  Right now, we are considering only single-statement timing
> > and no one has asked for multiple timers.
> 
> I don't ask multiple timers. ODBC driver would be able
> to handle multiple timeouts without multiple timers in
> my scenario.

I understand.

> > > > but it requires an interface like odbc or jdbc.  It
> > > > is hard to use for libpq or psql.
> > >
> > > We shouldn't expect too much on psql in the first place
> > > because it isn't procedural. I don't expect too much on
> > > libpq either because it's a low level interface. However
> > > applications which use libpq could do like odbc or jdbc
> > > does. Or libpq could also provide a function which encap-
> > > sulates the query timeout handling if necessary.
> > 
> > I certainly would like _something_ that works in psql/libpq,
> 
> Please don't make things complicated by sticking to such
> low level interfaces.

OK, what is your proposal?

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

Re: timeout implementation issues

Karel Zak <zakkr@zf.jcu.cz> — 2002-04-09T07:54:56Z

On Mon, Apr 08, 2002 at 12:28:18PM -0400, Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > OK, probably good time for summarization.  First, consider this:
> >
> > 	BEGIN WORK;
> > 	SET something;
> > 	query fails;
> > 	SET something else;
> > 	COMMIT WORK;
> >
> > Under current behavior, the first SET is honored, while the second is
> > ignored because the transaction is in ABORT state.  I can see no logical
> > reason for this behavior.
> 
> But that is not a shortcoming of the SET command.  The problem is that the
> system does not accept any commands after one command has failed in a
> transaction even though it could usefully do so.
> 
> > The jdbc timeout issue is this:
> >
> >
> > 	BEGIN WORK;
> > 	SET query_timeout=20;
> > 	query fails;
> > 	SET query_timeout=0;
> > 	COMMIT WORK;
> >
> > In this case, with our current code, the first SET is done, but the
> > second is ignored.
> 
> Given appropriate functionality, you could rewrite this thus:
> 
> BEGIN WORK;
> SET FOR THIS TRANSACTION ONLY query_timeout=20;
> query;
> COMMIT WORK;

 If I compare Peter's and Bruce's examples the Peter is still winner :-)

 Sorry, but a code with "set-it-after-abort" seems ugly.

        Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Re: timeout implementation issues

Karel Zak <zakkr@zf.jcu.cz> — 2002-04-09T08:19:33Z

On Mon, Apr 08, 2002 at 01:03:41PM -0400, Tom Lane wrote:

> The search_path case is the main reason why I'm intent on changing
> the behavior of SET; without that, I'd just leave well enough alone.

 Is there more variables like "search_path"? If not, I unsure if one
 item is good consideration for change others things.

> Possibly some will suggest that search_path shouldn't be a SET variable
> because it needs to be able to be rolled back on error.  But what else
> should it be?  It's definitely per-session status, not persistent

 It's good point. Why not make it more transparent? You want
 encapsulate it to standard and current SET statement, but if it's
 something different why not use for it different statement?

    SET SESSION search_path TO 'something';

 (...or something other)

    Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Re: timeout implementation issues

Michael Loftis <mloftis@wgops.com> — 2002-04-09T08:47:53Z

Heh pardon me but...

I was under the impression that for a transaction either all commands 
succeed or all commands fail, atleast according to everything I've ever 
read.  So followign that all SETs done within the scope of a 
BEGIN/COMMIT pair should only take effect if the whole set finishes, if 
not the system shoudl roll back to the way it was before the BEGIN.

I might be missing something though, I just got onto the list and there 
might be other parts of the thread I missed....

Karel Zak wrote:

>On Mon, Apr 08, 2002 at 01:03:41PM -0400, Tom Lane wrote:
>
>>The search_path case is the main reason why I'm intent on changing
>>the behavior of SET; without that, I'd just leave well enough alone.
>>
>
> Is there more variables like "search_path"? If not, I unsure if one
> item is good consideration for change others things.
>
>>Possibly some will suggest that search_path shouldn't be a SET variable
>>because it needs to be able to be rolled back on error.  But what else
>>should it be?  It's definitely per-session status, not persistent
>>
>
> It's good point. Why not make it more transparent? You want
> encapsulate it to standard and current SET statement, but if it's
> something different why not use for it different statement?
>
>    SET SESSION search_path TO 'something';
>        
> (...or something other)
>
>    Karel
>

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-09T13:20:22Z

Karel Zak <zakkr@zf.jcu.cz> writes:
>  It's good point. Why not make it more transparent? You want
>  encapsulate it to standard and current SET statement, but if it's
>  something different why not use for it different statement?

>     SET SESSION search_path TO 'something';

But a plain SET is also setting the value for the session.  What's
the difference?  Why should a user remember that he must use this
syntax for search_path, and not for any other variables (or perhaps
only one or two other ones, further down the road)?

			regards, tom lane

Re: timeout implementation issues

Peter Eisentraut <peter_e@gmx.net> — 2002-04-09T18:07:14Z

Michael Loftis writes:

> I was under the impression that for a transaction either all commands
> succeed or all commands fail, atleast according to everything I've ever
> read.

That's an urban legend.

A transaction guarantees (among other things) that all modifications to
the database with the transaction are done atomicly (either all or done or
none).  This does not extend to the commands that supposedly initiate such
modifications.

Take out a database other than PostgreSQL and do

BEGIN; -- or whatever they use; might be implicit
INSERT INTO existing_table ('legal value');
barf;
COMMIT;

The INSERT will most likely succeed.  The reason is that "barf" does not
modify or access the data in the database, so it does not affect the
transactional integrity of the database.

We are trying to make the same argument for SET.  SET does not modify the
database, so it doesn't have to fall under transaction control.

-- 
Peter Eisentraut   peter_e@gmx.net

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-09T18:21:23Z

Peter Eisentraut wrote:
> Michael Loftis writes:
> 
> > I was under the impression that for a transaction either all commands
> > succeed or all commands fail, atleast according to everything I've ever
> > read.
> 
> That's an urban legend.
> 
> A transaction guarantees (among other things) that all modifications to
> the database with the transaction are done atomicly (either all or done or
> none).  This does not extend to the commands that supposedly initiate such
> modifications.
> 
> Take out a database other than PostgreSQL and do
> 
> BEGIN; -- or whatever they use; might be implicit
> INSERT INTO existing_table ('legal value');
> barf;
> COMMIT;
> 
> The INSERT will most likely succeed.  The reason is that "barf" does not
> modify or access the data in the database, so it does not affect the
> transactional integrity of the database.

Ewe, we do fail that test.

> We are trying to make the same argument for SET.  SET does not modify the
> database, so it doesn't have to fall under transaction control.

OK, we have three possibilities:

	o  All SETs are honored in an aborted transaction
	o  No SETs are honored in an aborted transaction
	o  Some SETs are honored in an aborted transaction (current)

I think the problem is our current behavior.  I don't think anyone can
say our it is correct (only honor SET before the transaction reaches
abort state).  Whether we want the first or second is the issue, I think.

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

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-09T23:33:19Z

Bruce Momjian wrote:
> 
> OK, we have three possibilities:
> 
>         o  All SETs are honored in an aborted transaction
>         o  No SETs are honored in an aborted transaction
>         o  Some SETs are honored in an aborted transaction (current)
> 
> I think the problem is our current behavior.  I don't think anyone can
> say our it is correct (only honor SET before the transaction reaches
> abort state).  Whether we want the first or second is the issue, I think.

I think the current state is not that bad at least
is better than the first. I don't think it's a 
*should be* kind of thing and we shouldn't stick 
to it any longer.

regards,
Hiroshi Inoue

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-09T23:42:59Z

Tom Lane wrote:
> 
> Karel Zak <zakkr@zf.jcu.cz> writes:
> >  It's good point. Why not make it more transparent? You want
> >  encapsulate it to standard and current SET statement, but if it's
> >  something different why not use for it different statement?
> 
> >     SET SESSION search_path TO 'something';
> 
> But a plain SET is also setting the value for the session.  What's
> the difference?  Why should a user remember that he must use this
> syntax for search_path, and not for any other variables (or perhaps
> only one or two other ones, further down the road)?

ISTM what Karel meant is that if the search_path is a
much more significant variable than others you had better
express the difference using a different statement.
I agree with Karel though I don't know how siginificant
the varible is.

regards,
Hiroshi Inoue

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-09T23:51:55Z

Hiroshi Inoue wrote:
> 
> Bruce Momjian wrote:
> >
> > OK, we have three possibilities:
> >
> >         o  All SETs are honored in an aborted transaction
> >         o  No SETs are honored in an aborted transaction
> >         o  Some SETs are honored in an aborted transaction (current)
> >
> > I think the problem is our current behavior.  I don't think anyone can
> > say our it is correct (only honor SET before the transaction reaches
> > abort state).  Whether we want the first or second is the issue, I think.
> 
> I think the current state is not that bad at least
> is better than the first.

Oops does the first mean rolling back the variables on abort ?
If so I made a mistake. The current is better than the second.

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-10T00:13:21Z

Hiroshi Inoue wrote:
> Hiroshi Inoue wrote:
> > 
> > Bruce Momjian wrote:
> > >
> > > OK, we have three possibilities:
> > >
> > >         o  All SETs are honored in an aborted transaction
> > >         o  No SETs are honored in an aborted transaction
> > >         o  Some SETs are honored in an aborted transaction (current)
> > >
> > > I think the problem is our current behavior.  I don't think anyone can
> > > say our it is correct (only honor SET before the transaction reaches
> > > abort state).  Whether we want the first or second is the issue, I think.
> > 
> > I think the current state is not that bad at least
> > is better than the first.
> 
> Oops does the first mean rolling back the variables on abort ?
> If so I made a mistake. The current is better than the second.

The second means all SET's are rolled back on abort.
 
-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-10T00:25:50Z

Bruce Momjian wrote:
> 
> Hiroshi Inoue wrote:
> > Hiroshi Inoue wrote:
> > >
> > > Bruce Momjian wrote:
> > > >
> > > > OK, we have three possibilities:
> > > >
> > > >         o  All SETs are honored in an aborted transaction
> > > >         o  No SETs are honored in an aborted transaction
> > > >         o  Some SETs are honored in an aborted transaction (current)
> > > >
> > > > I think the problem is our current behavior.  I don't think anyone can
> > > > say our it is correct (only honor SET before the transaction reaches
> > > > abort state).  Whether we want the first or second is the issue, I think.
> > >
> > > I think the current state is not that bad at least
> > > is better than the first.
> >
> > Oops does the first mean rolling back the variables on abort ?
> > If so I made a mistake. The current is better than the second.
> 
> The second means all SET's are rolled back on abort.

I see.
BTW what varibles are rolled back on abort currently ?

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-10T00:27:01Z

Hiroshi Inoue wrote:
> > > Oops does the first mean rolling back the variables on abort ?
> > > If so I made a mistake. The current is better than the second.
> > 
> > The second means all SET's are rolled back on abort.
> 
> I see.
> BTW what varibles are rolled back on abort currently ?

Currently, none, though the SET commands after the query aborts are
ignored, which is effectively the same as rolling them back.

	BEGIN WORK;
	SET x=3;
	failed query;
	SET x=5;
	COMMIT;

In this case, x=3 at end of query.

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

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-10T00:46:43Z

Bruce Momjian wrote:
> 
> Hiroshi Inoue wrote:
> > > > Oops does the first mean rolling back the variables on abort ?
> > > > If so I made a mistake. The current is better than the second.
> > >
> > > The second means all SET's are rolled back on abort.
> >
> > I see.
> > BTW what varibles are rolled back on abort currently ?
> 
> Currently, none,

??? What do you mean by 
   o  Some SETs are honored in an aborted transaction (current)
?
Is the current state different from
     o  All SETs are honored in an aborted transaction
?

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-10T00:51:07Z

Hiroshi Inoue wrote:
> Bruce Momjian wrote:
> > 
> > Hiroshi Inoue wrote:
> > > > > Oops does the first mean rolling back the variables on abort ?
> > > > > If so I made a mistake. The current is better than the second.
> > > >
> > > > The second means all SET's are rolled back on abort.
> > >
> > > I see.
> > > BTW what varibles are rolled back on abort currently ?
> > 
> > Currently, none,
> 
> ??? What do you mean by 
>    o  Some SETs are honored in an aborted transaction (current)
> ?
> Is the current state different from
>      o  All SETs are honored in an aborted transaction
> ?

In the case of:

	BEGIN WORK;
	SET x=1;
	bad query that aborts transaction;
	SET x=2;
	COMMIT WORK;

Only the first SET is done, so at the end, x = 1.  If all SET's were
honored, x = 2. If no SETs in an aborted transaction were honored, x
would equal whatever it was before the BEGIN WORK above.


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

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-10T02:46:04Z

Bruce Momjian wrote:
> 
> Hiroshi Inoue wrote:
> > Bruce Momjian wrote:
> > >
> > > Hiroshi Inoue wrote:
> > > > > > Oops does the first mean rolling back the variables on abort ?
> > > > > > If so I made a mistake. The current is better than the second.
> > > > >
> > > > > The second means all SET's are rolled back on abort.
> > > >
> > > > I see.
> > > > BTW what varibles are rolled back on abort currently ?
> > >
> > > Currently, none,
> >
> > ??? What do you mean by
> >    o  Some SETs are honored in an aborted transaction (current)
> > ?
> > Is the current state different from
> >      o  All SETs are honored in an aborted transaction
> > ?
> 
> In the case of:
> 
>         BEGIN WORK;
>         SET x=1;
>         bad query that aborts transaction;
>         SET x=2;
>         COMMIT WORK;
> 
> Only the first SET is done, so at the end, x = 1.  If all SET's were
> honored, x = 2. If no SETs in an aborted transaction were honored, x
> would equal whatever it was before the BEGIN WORK above.

IMHO
      o  No SETs are honored in an aborted transaction(current)

The first SET isn't done in an aborted transaction.

regards,
Hiroshi Inoue

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-10T03:48:28Z

Hiroshi Inoue wrote:
> > > ??? What do you mean by
> > >    o  Some SETs are honored in an aborted transaction (current)
> > > ?
> > > Is the current state different from
> > >      o  All SETs are honored in an aborted transaction
> > > ?
> > 
> > In the case of:
> > 
> >         BEGIN WORK;
> >         SET x=1;
> >         bad query that aborts transaction;
> >         SET x=2;
> >         COMMIT WORK;
> > 
> > Only the first SET is done, so at the end, x = 1.  If all SET's were
> > honored, x = 2. If no SETs in an aborted transaction were honored, x
> > would equal whatever it was before the BEGIN WORK above.
> 
> IMHO
>       o  No SETs are honored in an aborted transaction(current)
> 
> The first SET isn't done in an aborted transaction.

Well, yes, when I say aborted transaction, I mean the entire
transaction, not just the part after the abort happens.  All non-SET
commands in the transaction are rolled back already.  I can't think of a
good argument for our current behavior.

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

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-10T03:50:28Z

Hiroshi Inoue wrote:
> > > ??? What do you mean by
> > >    o  Some SETs are honored in an aborted transaction (current)
> > > ?
> > > Is the current state different from
> > >      o  All SETs are honored in an aborted transaction
> > > ?
> > 
> > In the case of:
> > 
> >         BEGIN WORK;
> >         SET x=1;
> >         bad query that aborts transaction;
> >         SET x=2;
> >         COMMIT WORK;
> > 
> > Only the first SET is done, so at the end, x = 1.  If all SET's were
> > honored, x = 2. If no SETs in an aborted transaction were honored, x
> > would equal whatever it was before the BEGIN WORK above.
> 
> IMHO
>       o  No SETs are honored in an aborted transaction(current)
> 
> The first SET isn't done in an aborted transaction.

I guess my point is that with our current code, there is a distinction
that SETs are executed before a transaction aborts, but are ignored
after a transaction aborts, even if the SETs are in the same
transaction.

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

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-10T04:13:59Z

Peter Eisentraut <peter_e@gmx.net> writes:
> Take out a database other than PostgreSQL and do

> BEGIN; -- or whatever they use; might be implicit
> INSERT INTO existing_table ('legal value');
> barf;
> COMMIT;

> The INSERT will most likely succeed.  The reason is that "barf" does not
> modify or access the data in the database, so it does not affect the
> transactional integrity of the database.

No; this example is completely irrelevant to our discussion.  The reason
that (some) other DBMSes will allow the INSERT to take effect in the
above case is that they have savepoints, and the failure of the "barf"
command only rolls back to the savepoint not to the start of the
transaction.  It's a generally-acknowledged shortcoming that we don't
have savepoints ... but this has no relevance to the question of whether
SETs should be rolled back or not.  If we did have savepoints then I'd
be saying that SETs should roll back to a savepoint just like everything
else.

Please note that even in those other databases, if one replaces the
COMMIT with ROLLBACK in the above scenario, the effects of the INSERT
*will* roll back.  Transpose this into current Postgres, and replace
INSERT with SET, and the effects do *not* roll back.  How is that a
good idea?

			regards, tom lane

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-10T04:22:56Z

Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Take out a database other than PostgreSQL and do
> 
> > BEGIN; -- or whatever they use; might be implicit
> > INSERT INTO existing_table ('legal value');
> > barf;
> > COMMIT;
> 
> > The INSERT will most likely succeed.  The reason is that "barf" does not
> > modify or access the data in the database, so it does not affect the
> > transactional integrity of the database.
> 
> No; this example is completely irrelevant to our discussion.  The reason

Actually, we could probably prevent transaction abort on syntax(yacc)
errors, but the other errors like mistyped table names would be hard to
prevent a rollback, so I guess we just roll back on any error.

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

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-10T04:30:32Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> In the case of:

> 	BEGIN WORK;
> 	SET x=1;
> 	bad query that aborts transaction;
> 	SET x=2;
> 	COMMIT WORK;

> Only the first SET is done, so at the end, x = 1.

Perhaps even more to the point:

	SET x=0;
	BEGIN;
	SET x=1;
	bad query;
	SET x=2;
	ROLLBACK;

Now x=1.  How is this sensible?

			regards, tom lane

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-10T04:31:58Z

Bruce Momjian wrote:
> 
> Hiroshi Inoue wrote:
> > > > ??? What do you mean by
> > > >    o  Some SETs are honored in an aborted transaction (current)
> > > > ?
> > > > Is the current state different from
> > > >      o  All SETs are honored in an aborted transaction
> > > > ?
> > >
> > > In the case of:
> > >
> > >         BEGIN WORK;
> > >         SET x=1;
> > >         bad query that aborts transaction;
> > >         SET x=2;
> > >         COMMIT WORK;
> > >
> > > Only the first SET is done, so at the end, x = 1.  If all SET's were
> > > honored, x = 2. If no SETs in an aborted transaction were honored, x
> > > would equal whatever it was before the BEGIN WORK above.
> >
> > IMHO
> >       o  No SETs are honored in an aborted transaction(current)
> >
> > The first SET isn't done in an aborted transaction.
> 
> I guess my point is that with our current code, there is a distinction
> that SETs are executed before a transaction aborts, but are ignored
> after a transaction aborts, even if the SETs are in the same
> transaction.

Not only SET commands but also most commands are ignored
after a transaction aborts currently. SET commands are out
of transactional control but it doesn't mean they are never
ignore(rejecte)d.

regards,
Hiroshi Inoue

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-10T04:42:59Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Actually, we could probably prevent transaction abort on syntax(yacc)
> errors, but the other errors like mistyped table names would be hard to
> prevent a rollback, so I guess we just roll back on any error.

I don't think that what we categorize as an error or not is very
relevant to the discussion, either.  The real point is: should SET
have rollback behavior similar to other SQL commands, or not?
If we had savepoints, or ignorable syntax errors, or other frammishes
this question would still be the same.

			regards, tom lane

Re: timeout implementation issues

Thomas Lockhart <lockhart@fourpalms.org> — 2002-04-10T06:22:14Z

...
> Please note that even in those other databases, if one replaces the
> COMMIT with ROLLBACK in the above scenario, the effects of the INSERT
> *will* roll back.  Transpose this into current Postgres, and replace
> INSERT with SET, and the effects do *not* roll back.  How is that a
> good idea?

Well, as you should have concluded by now, "good" is not the same for
everyone ;)

Frankly, I've been happy with the current SET behavior, but would also
be willing to consider most of the alternatives which have been
suggested, including ones you have dismissed out of hand. Constraints
which seem to have been imposed include:

1) All commands starting with "SET" must have the same transactional
semantics. I'll agree that it might be nice for consistancy, but imho is
not absolutely required.

2) No commands which could be expected to start with "SET" will start
with some other keyword. If we do have "set class" commands which have
different transactional semantics, then we could explore alternative
syntax for specifying each category.

3) "SET" commands must respect transactions. I'm happy with the idea
that these commands are out of band and take effect immediately. And if
they take effect even in the middle of a failing/failed transaction,
that is OK too. The surrounding code would have reset the values anyway,
if necessary.


I do have a concern about how to implement some of the SET commands if
we *do* respect transactional semantics. For example, SET TIME ZONE
saves the current value of an environment variable (if available), and
would need *at least* a "before transaction" and "after transaction
started" pair of values. How would we propagate SET variables to
transaction-specific structures, clearing or resetting them later? Right
now these variables are pretty independent and can be accessed through
global storage; having transactional semantics means that the
interdependencies between different variable types in the SET handlers
may increase.

                       - Thomas

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-10T14:13:51Z

Thomas Lockhart <lockhart@fourpalms.org> writes:
> I do have a concern about how to implement some of the SET commands if
> we *do* respect transactional semantics. For example, SET TIME ZONE
> saves the current value of an environment variable (if available), and
> would need *at least* a "before transaction" and "after transaction
> started" pair of values.

I intended for guc.c to manage this bookkeeping, thus freeing individual
modules from worrying about it.  That would require us to transpose the
last few special-cased SET variables into generic GUC variables, but
I consider that a Good Thing anyway.

			regards, tom lane

Re: timeout implementation issues

Peter Eisentraut <peter_e@gmx.net> — 2002-04-11T05:01:56Z

Thomas Lockhart writes:

> 1) All commands starting with "SET" must have the same transactional
> semantics. I'll agree that it might be nice for consistancy, but imho is
> not absolutely required.

This rule is already violated anyway.  SET TRANSACTION ISOLATION, SET
CONSTRAINTS, SET SESSION AUTHORIZATION, and SET mostly_anything_else
already behave quite differently.

-- 
Peter Eisentraut   peter_e@gmx.net

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-18T03:58:07Z

I have added this to the TODO list, with a question mark.  Hope this is
OK with everyone.

        o Abort SET changes made in aborted transactions (?)

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

Tom Lane wrote:
> Thomas Lockhart <lockhart@fourpalms.org> writes:
> > I do have a concern about how to implement some of the SET commands if
> > we *do* respect transactional semantics. For example, SET TIME ZONE
> > saves the current value of an environment variable (if available), and
> > would need *at least* a "before transaction" and "after transaction
> > started" pair of values.
> 
> I intended for guc.c to manage this bookkeeping, thus freeing individual
> modules from worrying about it.  That would require us to transpose the
> last few special-cased SET variables into generic GUC variables, but
> I consider that a Good Thing anyway.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

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

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-18T05:31:41Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I have added this to the TODO list, with a question mark.  Hope this is
> OK with everyone.

>         o Abort SET changes made in aborted transactions (?)

Actually, I was planning to make only search_path act that way, because
of all the push-back I'd gotten on applying it to other SET variables.
search_path really *has* to have it, but if there's anyone who agrees
with me about doing it for all SET vars, they didn't speak up :-(

			regards, tom lane

Re: timeout implementation issues

Michael Loftis <mloftis@wgops.com> — 2002-04-18T08:56:43Z


Tom Lane wrote:

>Bruce Momjian <pgman@candle.pha.pa.us> writes:
>
>>I have added this to the TODO list, with a question mark.  Hope this is
>>OK with everyone.
>>
>
>>        o Abort SET changes made in aborted transactions (?)
>>
>
>Actually, I was planning to make only search_path act that way, because
>of all the push-back I'd gotten on applying it to other SET variables.
>search_path really *has* to have it, but if there's anyone who agrees
>with me about doing it for all SET vars, they didn't speak up :-(
>
I did and do, strongly.  TRANSACTIONS are supposed to leave things as 
they were before the BEGIN.  It either all happens or it all doesnt' 
happen.  If you need soemthing inside of a transaction to go 
irregardless then it shouldn't be within the transaction.

>regards, tom lane
>

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-18T09:43:43Z

Michael Loftis wrote:
> 
> Tom Lane wrote:
> 
> >Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >
> >>I have added this to the TODO list, with a question mark.  Hope this is
> >>OK with everyone.
> >>
> >
> >>        o Abort SET changes made in aborted transactions (?)
> >>
> >
> >Actually, I was planning to make only search_path act that way, because
> >of all the push-back I'd gotten on applying it to other SET variables.
> >search_path really *has* to have it, but if there's anyone who agrees
> >with me about doing it for all SET vars, they didn't speak up :-(
> >
> I did and do, strongly.  TRANSACTIONS are supposed to leave things as
> they were before the BEGIN.  It either all happens or it all doesnt'
> happen.  If you need soemthing inside of a transaction to go
> irregardless then it shouldn't be within the transaction.

Oops is this issue still living ?
I object to the TODO(why ????) strongly.
Please remove it from the TODO first and put it back
to the neutral position.

regards,
Hiroshi Inoue
	http://w2422.nsk.ne.jp/~inoue/

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-18T14:32:05Z

Hiroshi Inoue wrote:
> Michael Loftis wrote:
> > 
> > Tom Lane wrote:
> > 
> > >Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > >
> > >>I have added this to the TODO list, with a question mark.  Hope this is
> > >>OK with everyone.
> > >>
> > >
> > >>        o Abort SET changes made in aborted transactions (?)
> > >>
> > >
> > >Actually, I was planning to make only search_path act that way, because
> > >of all the push-back I'd gotten on applying it to other SET variables.
> > >search_path really *has* to have it, but if there's anyone who agrees
> > >with me about doing it for all SET vars, they didn't speak up :-(
> > >
> > I did and do, strongly.  TRANSACTIONS are supposed to leave things as
> > they were before the BEGIN.  It either all happens or it all doesnt'
> > happen.  If you need soemthing inside of a transaction to go
> > irregardless then it shouldn't be within the transaction.
> 
> Oops is this issue still living ?
> I object to the TODO(why ????) strongly.
> Please remove it from the TODO first and put it back
> to the neutral position.

OK, how is this:

  o Abort all or commit all SET changes made in an aborted transaction

Is this neutral?  I don't think our current behavior is defended by anyone.

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

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-18T14:34:50Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I have added this to the TODO list, with a question mark.  Hope this is
> > OK with everyone.
> 
> >         o Abort SET changes made in aborted transactions (?)
> 
> Actually, I was planning to make only search_path act that way, because
> of all the push-back I'd gotten on applying it to other SET variables.
> search_path really *has* to have it, but if there's anyone who agrees
> with me about doing it for all SET vars, they didn't speak up :-(

Woh, this all started because of timeout, which needs this fix too.  We
certainly need something and I don't want to get into on of those "we
can't all decide, so we do nothing" situations.

I have updated the TODO to:

    o Abort all or commit all SET changes made in an aborted transaction    

I don't think our current behavior is defended by anyone.  Is abort all
or commit all the only two choices?   If so, we will take a vote and be
done with it.

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

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-18T14:52:40Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I have updated the TODO to:
>     o Abort all or commit all SET changes made in an aborted transaction    
> I don't think our current behavior is defended by anyone.

Hiroshi seems to like it ...

However, "commit SETs even after an error" is most certainly NOT
acceptable.  It's not even sensible --- what if the SETs themselves
throw errors, or are depending on the results of failed non-SET
commands; will you try to commit them anyway?

It seems to me that the choices we realistically have are

	(a) leave the behavior the way it is

	(b) cause all SETs in an aborted transaction to roll back.

			regards, tom lane

Re: timeout implementation issues

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-18T15:19:41Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I have updated the TODO to:
> >     o Abort all or commit all SET changes made in an aborted transaction    
> > I don't think our current behavior is defended by anyone.
> 
> Hiroshi seems to like it ...
> 
> However, "commit SETs even after an error" is most certainly NOT
> acceptable.  It's not even sensible --- what if the SETs themselves
> throw errors, or are depending on the results of failed non-SET
> commands; will you try to commit them anyway?
> 
> It seems to me that the choices we realistically have are
> 
> 	(a) leave the behavior the way it is
> 
> 	(b) cause all SETs in an aborted transaction to roll back.

I disagree.  You commit all the SET's you can, even if in aborted
transactions.  If they throw an error, or rely on a previous non-SET
that aborted, oh well.  That is what some are asking for.

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

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-18T23:53:02Z

Tom Lane wrote:
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I have updated the TODO to:
> >     o Abort all or commit all SET changes made in an aborted transaction
> > I don't think our current behavior is defended by anyone.
> 
> Hiroshi seems to like it ...

Probably I don't love it. Honestly I don't understand
what the new TODO means exactly.
I don't think this is  *all* *should be* or *all
or nothing* kind of thing. If a SET variable has
its reason, it would behave in its own right.

> However, "commit SETs even after an error" is most certainly NOT
> acceptable. 

What I've meant is that SET commands are out of transactional
control and so the word *commit SETs even after* has no meaning
to me. Basically it's a user's responsisbilty to manage the
errors. He only knows what's to do with the errors.

regards,
Hiroshi Inoue
	http://w2422.nsk.ne.jp/~inoue/

Re: timeout implementation issues

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-18T23:56:13Z

Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> I don't think this is  *all* *should be* or *all
> or nothing* kind of thing. If a SET variable has
> its reason, it would behave in its own right.

Well, we could provide some kind of escape hatch to let the behavior
vary from one variable to the next.  But can you give any specific
examples?  Which SET variables should not roll back on error?

			regards, tom lane

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-19T00:19:16Z

Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > I don't think this is  *all* *should be* or *all
> > or nothing* kind of thing. If a SET variable has
> > its reason, it would behave in its own right.
> 
> Well, we could provide some kind of escape hatch to let the behavior
> vary from one variable to the next.  But can you give any specific
> examples?  Which SET variables should not roll back on error?

It seems veeery dangerous to conclude that SET *should* 
roll back even if there's no *should not* roll back case.
There could be no *should not* roll back case because
a user could set the variable as he likes in the next
transaction.

Hiroshi Inoue
	http://w2422.nsk.ne.jp/~inoue/

Re: timeout implementation issues

Michael Loftis <mloftis@wgops.com> — 2002-04-19T00:27:17Z

Hiroshi Inoue wrote:

>Tom Lane wrote:
>
>>Hiroshi Inoue <Inoue@tpf.co.jp> writes:
>>
>>>I don't think this is  *all* *should be* or *all
>>>or nothing* kind of thing. If a SET variable has
>>>its reason, it would behave in its own right.
>>>
>>Well, we could provide some kind of escape hatch to let the behavior
>>vary from one variable to the next.  But can you give any specific
>>examples?  Which SET variables should not roll back on error?
>>
>
>It seems veeery dangerous to conclude that SET *should* 
>roll back even if there's no *should not* roll back case.
>There could be no *should not* roll back case because
>a user could set the variable as he likes in the next
>transaction.
>
In whihc case, if I'm understanding you correctly Hiroshi-san, the
rollback is moot anyway...

IE

BEGIN transaction_1
...
SET SOMEVAR=SOMETHING
...
COMMIT

(transaction_1 fails and rolls back)

BEGIN transaction_2
...
SET SOMEVAR=SOMETHINGELSE
...
COMMIT

(transaction_2 succeeds)

SOMEVAR, in either case, assuming transaction_2 succeeds, would be
SOMETHINGELSE. If both succeed SOMEVAR is SOMETHINGELSE, if the first
succeeds and the second fails SOMEVAR will be SOMETHING. If neither
succeed SOMEVAR (for this short example) is whatever it was before the
two transactions.

Am I understanding you correctly in that this is the example you were
trying to point out?

>
>  
>Hiroshi Inoue
>	http://w2422.nsk.ne.jp/~inoue/
>

Re: timeout implementation issues

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-19T03:07:54Z

Michael Loftis wrote:
> 
> Hiroshi Inoue wrote:
> 
> >Tom Lane wrote:
> >
> >>Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> >>
> >>>I don't think this is  *all* *should be* or *all
> >>>or nothing* kind of thing. If a SET variable has
> >>>its reason, it would behave in its own right.
> >>>
> >>Well, we could provide some kind of escape hatch to let the behavior
> >>vary from one variable to the next.  But can you give any specific
> >>examples?  Which SET variables should not roll back on error?
> >>
> >
> >It seems veeery dangerous to conclude that SET *should*
> >roll back even if there's no *should not* roll back case.
> >There could be no *should not* roll back case because
> >a user could set the variable as he likes in the next
> >transaction.
> >
> In whihc case, if I'm understanding you correctly Hiroshi-san, the
> rollback is moot anyway...
> 
> IE
> 
> BEGIN transaction_1
> ...
> SET SOMEVAR=SOMETHING
> ...
> COMMIT
> 
> (transaction_1 fails and rolls back)

Probably you are misunderstanding my point.
I don't think that SOMEVAR *should* be put back
on failure.
Users must know what value would be set to the
SOMEVAR after an error. In some cases it must
be put back, in some cases the current value
is OK, in other cases new SOMEVAR is needed.
Basically it's a user's resposibilty to set
the value.

regards, 
Hiroshi Inoue
	http://w2422.nsk.ne.jp/~inoue/