timeout implementation issues

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

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

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

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

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

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

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

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

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

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

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

Peter Eisentraut wrote:

Bruce Momjian writes:

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.

Note that the first is a statement-level timeout and the second is a
transaction-level timeout. Be sure to clarify which one we want.

Oh, wow, that is an interesting distinction. If there is a multi-query
transaction, do we time each query separately or the entire transaction?
I don't know which people want, and maybe this is why we need both GUC
and BEGIN WORK timeouts. I don't remember this distinction in previous
discussions but it may be significant. Of course, the GUC could behave
at a transaction level as well. It will be tricky to manage multiple
alarms in a single process, but it can be done by creating an alarm
queue.

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

... It will be tricky to manage multiple
alarms in a single process, but it can be done by creating an alarm
queue.

I would argue that we should only support *one* kind of timeout, either
transaction-level or statement-level, so as to avoid that complexity.
I don't want to see us gilding the lily in the first implementation of
something that IMHO is of dubious usefulness in the first place.
We can think about extending the facility later, when and if it proves
sufficiently useful to justify more complexity.

I don't have a very strong feeling about whether transaction-level or
statement-level is more useful; am willing to do whichever one the
JDBC spec wants.

regards, tom lane

On Mon, 1 Apr 2002, Bruce Momjian wrote:

I don't know which people want, and maybe this is why we need both GUC
and BEGIN WORK timeouts. I don't remember this distinction in previous
discussions but it may be significant. Of course, the GUC could behave
at a transaction level as well. It will be tricky to manage multiple
alarms in a single process, but it can be done by creating an alarm
queue.

I think we should do just BEGIN WORK (transaction-level) timeouts; that is
all that the JDBC spec asks for. Does that sound good to people?

So the work that would need to be done is asking the driver to request the
timeout via "BEGIN WORK TIMEOUT 5"; getting the backend to parse that
request and set the alarm on each query in that transaction; getting the
backend to send a cancel request if the alarm goes off. I am right now in
the process of finding the place where BEGIN-level queries are parsed. Any
pointers to the right files to read would be appreciated.

j

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

... It will be tricky to manage multiple
alarms in a single process, but it can be done by creating an alarm
queue.

I would argue that we should only support *one* kind of timeout, either
transaction-level or statement-level, so as to avoid that complexity.
I don't want to see us gilding the lily in the first implementation of
something that IMHO is of dubious usefulness in the first place.
We can think about extending the facility later, when and if it proves
sufficiently useful to justify more complexity.

I don't have a very strong feeling about whether transaction-level or
statement-level is more useful; am willing to do whichever one the
JDBC spec wants.

Agreed, only one timeout. I just considered the statement/transaction
level quite interesting. We could easily do GUC for query level, and
allow BEGIN WORK to override that for transaction level. That would
give us the best of both worlds, if we want it. I am not sure what
people are going to use this timeout for. My guess is that only
transaction level is the way to go.

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

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

Jessica,

My reading of the JDBC spec would indicate that this is a statement
level property (aka query level) since the method to enable this is on
the Statement object and is named setQueryTimeout(). There is nothing I
can find that would indicate that this would apply to the transaction in
my reading of the jdbc spec.

thanks,
--Barry

Jessica Perry Hekman wrote:

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

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

Barry Lind writes:

My reading of the JDBC spec would indicate that this is a statement
level property (aka query level) since the method to enable this is on
the Statement object and is named setQueryTimeout(). There is nothing I
can find that would indicate that this would apply to the transaction in
my reading of the jdbc spec.

Does it time out only queries or any kind of statement?

--
Peter Eisentraut peter_e@gmx.net

On Mon, 1 Apr 2002, Peter Eisentraut wrote:

Does it time out only queries or any kind of statement?

Any kind, I believe.

FWIW, I took a look at the recommended JDBC driver for MySQL, hoping for
ideas; it does not implement query timeouts at all. I'll take a look at
mSQL next.

j

The spec isn't clear on that point, but my interpretation is that it
would apply to all types of statements not just queries.

--Barry

Peter Eisentraut wrote:

On Monday 01 April 2002 20:18, Bruce Momjian wrote:

Tom Lane wrote:>
Agreed, only one timeout.
...

We have (at least) two ortogonal reasons why we want
to abort a long running transaction:

- The long running transaction might compute a result
we are not interesed anymore (because it just takes
too long to wait for the result). We do NOT always
know in advance how patient we will be to wait for
the result. Therefore I think the client should tell
the server, when his client (user?) got impatinet
and aborted the whole transaction...

- The long running transaction might hold exclusive locks
and therefore decreases (or even nullifies) the overall
concurrency. We want to be able to disallow this by design.

I think a nice timout criteria would be a maximum lock time
for all resources aquired exclusivly within a transaction.
This would then affect transaction timeouts as well as statement
timeouts with the advantage, the get concurrency guaratees.

Robert

So the work that would need to be done is asking the driver to request the
timeout via "BEGIN WORK TIMEOUT 5"; getting the backend to parse that
request and set the alarm on each query in that transaction; getting the

Well imho that interpretation would be completely unobvious.
My first guess would have been, that with this syntax the whole transaction
must commit or rollback within 5 seconds.

Thus I think we only need statement_timeout. ODBC, same as JDBC wants it at the
statement handle level. ODBC also provides for a default that applies to all
statement handles of this connection (They call the statement attr QUERY_TIMEOUT,
so imho there is room for interpretation whether it applies to selects only, which
I would find absurd).

Andreas

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

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:

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

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

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

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

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

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

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

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

Bruce Momjian wrote:

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.

As for ODBC, there's no state that *abort* but still inside
a transaction currently.

regards,
Hiroshi Inoue

Hiroshi Inoue wrote:

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.

As for ODBC, there's no state that *abort* but still inside
a transaction currently.

Yes, the strange thing is that SET inside a transaction _after_ the
transaction aborts is ignored, while SET before inside a transaction
before the transaction aborts is accepted.

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

Bruce Momjian wrote:

Hiroshi Inoue wrote:

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.

As for ODBC, there's no state that *abort* but still inside
a transaction currently.

Yes, the strange thing is that SET inside a transaction _after_ the
transaction aborts is ignored, while SET before inside a transaction
before the transaction aborts is accepted.

What I meant is there's no such problem with psqlodbc
at least currently because the driver issues ROLLBACK
automatically on abort inside a transaction.

regards,
Hiroshi Inoue

Hiroshi Inoue wrote:

Bruce Momjian wrote:

Hiroshi Inoue wrote:

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.

As for ODBC, there's no state that *abort* but still inside
a transaction currently.

Yes, the strange thing is that SET inside a transaction _after_ the
transaction aborts is ignored, while SET before inside a transaction
before the transaction aborts is accepted.

What I meant is there's no such problem with psqlodbc
at least currently because the driver issues ROLLBACK
automatically on abort inside a transaction.

If it does that, what happens with the rest of the queries in a
transaction? Do they get executed in their own transactions, or are
they somehow ignored.

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

Bruce Momjian wrote:

Hiroshi Inoue wrote:

Bruce Momjian wrote:

Hiroshi Inoue wrote:

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.

As for ODBC, there's no state that *abort* but still inside
a transaction currently.

Yes, the strange thing is that SET inside a transaction _after_ the
transaction aborts is ignored, while SET before inside a transaction
before the transaction aborts is accepted.

What I meant is there's no such problem with psqlodbc
at least currently because the driver issues ROLLBACK
automatically on abort inside a transaction.

If it does that, what happens with the rest of the queries in a
transaction? Do they get executed in their own transactions, or are
they somehow ignored.

They would be executed in a new transaction. Queries shouldn't
be issued blindly(without error checking).

regards,
Hiroshi Inoue

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

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

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

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

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

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

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

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

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 #

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

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

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

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 #

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

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 #

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 #

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 #

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

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

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

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

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

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

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

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

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

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 #

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

"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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

...

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

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

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

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

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

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.

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/

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

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

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

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

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/

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

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/

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?

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/