Re: [HACKERS] Statement timeout behavior in extended queries

> On 2017-09-10 17:12:19 -0700, Andres Freund wrote: >> On 2017-09-11 09:10:49 +0900, Tatsuo Ishii wrote: >> > If you don't mind, can you please commit/push the patch? >> >> Ok, will do so. > > And, done. Thanks for patch and reminder! Thanks! -- Tatsuo Ishii SRA OSS, Inc. Japan English:

Re: [HACKERS] Statement timeout behavior in extended queries

On 2017-09-10 17:12:19 -0700, Andres Freund wrote: > On 2017-09-11 09:10:49 +0900, Tatsuo Ishii wrote: > > If you don't mind, can you please commit/push the patch? > > Ok, will do so. And, done. Thanks for patch and reminder! - Andres -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Statement timeout behavior in extended queries

Hi, On 2017-09-11 09:10:49 +0900, Tatsuo Ishii wrote: > If you don't mind, can you please commit/push the patch? Ok, will do so. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] Statement timeout behavior in extended queries

Andres, If you don't mind, can you please commit/push the patch? Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp >> What do you think? I've not really tested this with the extended >> protocol, so I'd appreciate

Re: [HACKERS] Statement timeout behavior in extended queries

> What do you think? I've not really tested this with the extended > protocol, so I'd appreciate if you could rerun your test from the > older thread. Attached is your patch just rebased against master branch. Also I attached the test cases and results using pgproto on patched master branch.

Re: [HACKERS] Statement timeout behavior in extended queries

On 2017-04-05 09:46:31 -0400, Tom Lane wrote: > Andres Freund writes: > > On 2017-04-05 00:39:51 -0400, Tom Lane wrote: > >> but > >> if that's what we're doing, let's make sure we do it consistently. > >> I haven't read the patch, but the comments in this thread make me fear

Re: [HACKERS] Statement timeout behavior in extended queries

Andres Freund writes: > On 2017-04-05 00:39:51 -0400, Tom Lane wrote: >> but >> if that's what we're doing, let's make sure we do it consistently. >> I haven't read the patch, but the comments in this thread make me fear >> that it's introducing some ad-hoc, inconsistent

Re: [HACKERS] Statement timeout behavior in extended queries

> Then, the following sequence should have occurred. The test result is valid. Yes, I remembered that and was about to make a posting :-) > # Execute statement which takes 2 seconds. > 'P' "S1""SELECT pg_sleep(2)"0 > -> start transaction T1 > 'B' "S2""S1"0 0 0

Re: [HACKERS] Statement timeout behavior in extended queries

On 2017-04-05 00:39:51 -0400, Tom Lane wrote: > Andres Freund writes: > > On 2017-04-05 10:05:19 +0900, Tatsuo Ishii wrote: > >> What's your point of the question? What kind of problem do you expect > >> if the timeout starts only once at the first parse meesage out of > >>

Re: [HACKERS] Statement timeout behavior in extended queries

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tatsuo Ishii > Since pgproto is a dumb protocol machine, it does not start a transaction > automatically (user needs to explicitly send a start transaction command > via either simple or extended

Re: [HACKERS] Statement timeout behavior in extended queries

> From: pgsql-hackers-ow...@postgresql.org >> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tatsuo Ishii >> I have done tests using pgproto. One thing I noticed a strange behavior. >> Below is an output of pgproto. The test first set the timeout to 3 seconds, >> and parse/bind for

Re: [HACKERS] Statement timeout behavior in extended queries

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tatsuo Ishii > I have done tests using pgproto. One thing I noticed a strange behavior. > Below is an output of pgproto. The test first set the timeout to 3 seconds, > and parse/bind for "SELECT

Re: [HACKERS] Statement timeout behavior in extended queries

>> What do you think? I've not really tested this with the extended protocol, >> so I'd appreciate if you could rerun your test from the older thread. > > The patch looks good and cleaner. It looks like the code works as expected. > As before, I ran one INSERT statement with PgJDBC, with

Re: [HACKERS] Statement timeout behavior in extended queries

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of 'Andres Freund' > Attached. I did not like that the previous patch had the timeout handling > duplicated in the individual functions, I instead centralized it into > start_xact_command(). Given

Re: [HACKERS] Statement timeout behavior in extended queries

Andres Freund writes: > On 2017-04-05 10:05:19 +0900, Tatsuo Ishii wrote: >> What's your point of the question? What kind of problem do you expect >> if the timeout starts only once at the first parse meesage out of >> bunch of parse messages? > It's perfectly valid to send a

Re: [HACKERS] Statement timeout behavior in extended queries

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tatsuo Ishii > Hmm. IMO, that could happen even with the current statement timeout > implementation as well. > > Or we could start/stop the timeout in exec_execute_message() only. This > could

Re: [HACKERS] Statement timeout behavior in extended queries

> On 2017-04-04 16:56:26 -0700, 'Andres Freund' wrote: >> On 2017-04-04 23:52:28 +, Tsunakawa, Takayuki wrote: >> > From: Andres Freund [mailto:and...@anarazel.de] >> > > Looks to me like npgsql doesn't do that either. None of libpq, pgjdbs >> > > and >> > > npgsql doing it seems like some

Re: [HACKERS] Statement timeout behavior in extended queries

On 2017-04-04 16:56:26 -0700, 'Andres Freund' wrote: > On 2017-04-04 23:52:28 +, Tsunakawa, Takayuki wrote: > > From: Andres Freund [mailto:and...@anarazel.de] > > > Looks to me like npgsql doesn't do that either. None of libpq, pgjdbs and > > > npgsql doing it seems like some evidence that

Re: [HACKERS] Statement timeout behavior in extended queries

>> What's your point of the question? What kind of problem do you expect >> if the timeout starts only once at the first parse meesage out of >> bunch of parse messages? > > It's perfectly valid to send a lot of Parse messages without > interspersed Sync or Bind/Execute message. There'll be one

Re: [HACKERS] Statement timeout behavior in extended queries

On 2017-04-05 10:05:19 +0900, Tatsuo Ishii wrote: > > Hm. I started to edit it, but I'm halfway coming back to my previous > > view that this isn't necessarily ready. > > > > If a client were to to prepare a large number of prepared statements > > (i.e. a lot of parse messages), this'll only

Re: [HACKERS] Statement timeout behavior in extended queries

> Hm. I started to edit it, but I'm halfway coming back to my previous > view that this isn't necessarily ready. > > If a client were to to prepare a large number of prepared statements > (i.e. a lot of parse messages), this'll only start the timeout once, at > the first statement sent. It's not

Re: [HACKERS] Statement timeout behavior in extended queries

On 2017-04-04 23:52:28 +, Tsunakawa, Takayuki wrote: > From: Andres Freund [mailto:and...@anarazel.de] > > Looks to me like npgsql doesn't do that either. None of libpq, pgjdbs and > > npgsql doing it seems like some evidence that it's ok. > > And psqlODBC now uses always libpq. > > Now

Re: [HACKERS] Statement timeout behavior in extended queries

From: Andres Freund [mailto:and...@anarazel.de] > Looks to me like npgsql doesn't do that either. None of libpq, pgjdbs and > npgsql doing it seems like some evidence that it's ok. And psqlODBC now uses always libpq. Now time for final decision? Regards Takayuki Tsunakawa -- Sent via

Re: [HACKERS] Statement timeout behavior in extended queries

On 2017-04-04 16:38:53 -0700, Andres Freund wrote: > On 2017-04-04 16:10:32 +0900, Tatsuo Ishii wrote: > > >> If what Tatsuo-san said to Tom is correct (i.e. each Parse/Bind/Execute > > >> starts and stops the timer), then it's a concern and the patch should > > >> not be ready for committer.

Re: [HACKERS] Statement timeout behavior in extended queries

On 2017-04-05 08:34:43 +0900, Tatsuo Ishii wrote: > Andres, > > >> I think the code needs a few clarifying comments around this, but > >> otherwise seems good. Not restarting the timeout in those cases > >> obviously isn't entirely "perfect"/"correct", but a tradeoff - the > >> comments should

Re: [HACKERS] Statement timeout behavior in extended queries

On 2017-04-04 16:10:32 +0900, Tatsuo Ishii wrote: > >> If what Tatsuo-san said to Tom is correct (i.e. each Parse/Bind/Execute > >> starts and stops the timer), then it's a concern and the patch should not > >> be ready for committer. However, the current patch is not like that -- it > >>

Re: [HACKERS] Statement timeout behavior in extended queries

Andres, >> I think the code needs a few clarifying comments around this, but >> otherwise seems good. Not restarting the timeout in those cases >> obviously isn't entirely "perfect"/"correct", but a tradeoff - the >> comments should note that. >> >> Tatsuo-san, do you want to change those, and

Re: [HACKERS] Statement timeout behavior in extended queries

>> If what Tatsuo-san said to Tom is correct (i.e. each Parse/Bind/Execute >> starts and stops the timer), then it's a concern and the patch should not be >> ready for committer. However, the current patch is not like that -- it >> seems to do what others in this thread are expecting. > > Oh,

Re: [HACKERS] Statement timeout behavior in extended queries

On 2017-04-04 06:35:00 +, Tsunakawa, Takayuki wrote: > From: Andres Freund [mailto:and...@anarazel.de] > Given the concern raised in > > http://archives.postgresql.org/message-id/12207.1491228316%40sss.pgh.p > > a.us > > I don't see this being ready for committer. > > If what Tatsuo-san said

Re: [HACKERS] Statement timeout behavior in extended queries

From: Andres Freund [mailto:and...@anarazel.de] Given the concern raised in > http://archives.postgresql.org/message-id/12207.1491228316%40sss.pgh.p > a.us > I don't see this being ready for committer. If what Tatsuo-san said to Tom is correct (i.e. each Parse/Bind/Execute starts and stops the

Re: [HACKERS] Statement timeout behavior in extended queries

On 2017-04-04 06:18:04 +, Tsunakawa, Takayuki wrote: > From: Tatsuo Ishii [mailto:is...@sraoss.co.jp] > > It's too late. Someone has already moved the patch to the next CF (for > > PostgreSQL 11). > > Yes, but this patch will be necessary by the final release of PG 10 if the > libpq

Re: [HACKERS] Statement timeout behavior in extended queries

From: Tatsuo Ishii [mailto:is...@sraoss.co.jp] > It's too late. Someone has already moved the patch to the next CF (for > PostgreSQL 11). Yes, but this patch will be necessary by the final release of PG 10 if the libpq batch/pipelining is committed in PG 10. I marked this as ready for committer

Re: [HACKERS] Statement timeout behavior in extended queries

> The patch doesn't seem to behave like that. The Parse message calls > start_xact_command() -> enable_statement_timeout() -> enable_timeout(), and > set stmt_timer_started to true. Subsequent Bind and Execute messages call > enable_statement_timeout(), but enable_statement_timeout() doesn't

Re: [HACKERS] Statement timeout behavior in extended queries

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tatsuo Ishii > Actually the statement timer is replaced with new statement timer value > in enable_statement_timeout(). The patch doesn't seem to behave like that. The Parse message calls

Re: [HACKERS] Statement timeout behavior in extended queries

> Where is the statement_timeout timer stopped when processing Parse and Bind > messages? Actually the statement timer is replaced with new statement timer value in enable_statement_timeout(). > Do you mean the following sequence of operations are performed in this patch? > > Parse(statement1)

Re: [HACKERS] Statement timeout behavior in extended queries

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tatsuo Ishii > No. Parse, bind and Execute message indivually stops and starts the > statement_timeout timer with the patch. Unless there's a lock conflict, > parse and bind will take very short

Re: [HACKERS] Statement timeout behavior in extended queries

On 2017-04-03 23:31:59 +0900, Tatsuo Ishii wrote: > > That seems like it could represent quite a lot of added overhead, > > on machines where gettimeofday() is slow ... which is still a lot > > of them, unfortunately. > > Maybe. I think we could eliminate restarting the timer for parse and >

Re: [HACKERS] Statement timeout behavior in extended queries

> That seems like it could represent quite a lot of added overhead, > on machines where gettimeofday() is slow ... which is still a lot > of them, unfortunately. Maybe. I think we could eliminate restarting the timer for parse and bind. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English:

Re: [HACKERS] Statement timeout behavior in extended queries

Tatsuo Ishii writes: >> * The difference is that the Execute message stops the statement_timeout >> timer, > No. Parse, bind and Execute message indivually stops and starts the > statement_timeout timer with the patch. That seems like it could represent quite a lot of

Re: [HACKERS] Statement timeout behavior in extended queries

Thank you for reviewing my patch! > Hello, > > > I've reviewed the patch. My understanding is as follows. Please correct me > if I'm wrong. > > * The difference is that the Execute message stops the statement_timeout > timer, No. Parse, bind and Execute message indivually stops and

Re: [HACKERS] Statement timeout behavior in extended queries

Hello, I've reviewed the patch. My understanding is as follows. Please correct me if I'm wrong. * The difference is that the Execute message stops the statement_timeout timer, so that the execution of one statement doesn't shorten the timeout period of subsequent statements when they are

Re: [HACKERS] Statement timeout behavior in extended queries

> On Wed, Feb 22, 2017 at 11:50:44AM +0900, Tatsuo Ishii wrote: >> Last year I have proposed an enhancement regarding behavior of the >> statement timeout in extended queries. >> >> https://www.postgresql.org/message-id/20160528.220442.1489791680347556026.t-ishii%40sraoss.co.jp >> >> IMO the

Re: [HACKERS] Statement timeout behavior in extended queries

On Wed, Feb 22, 2017 at 11:50:44AM +0900, Tatsuo Ishii wrote: > Last year I have proposed an enhancement regarding behavior of the > statement timeout in extended queries. > > https://www.postgresql.org/message-id/20160528.220442.1489791680347556026.t-ishii%40sraoss.co.jp > > IMO the current

[HACKERS] Statement timeout behavior in extended queries

Last year I have proposed an enhancement regarding behavior of the statement timeout in extended queries. https://www.postgresql.org/message-id/20160528.220442.1489791680347556026.t-ishii%40sraoss.co.jp IMO the current behavior is counter intuitive and I would like to change it toward PostgreSQL

Re: [HACKERS] Statement timeout

>> BTW, aren't you missing a re-enable of the timeout for statements after >> the first one? > > Will check. You are right. Here is the revised patch. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp diff --git

Re: [HACKERS] Statement timeout

>> I didn't noticed it. Could you give me the message id or URL? >> >> > https://commitfest.postgresql.org/10/634/ > > https://www.postgresql.org/message-id/flat/CAMsr+YFUjJytRyV4J-16bEoiZyH=4nj+sQ7JP9ajwz=b4dm...@mail.gmail.com#CAMsr+YFUjJytRyV4J-16bEoiZyH=4nj+sQ7JP9ajwz=b4dm...@mail.gmail.com

Re: [HACKERS] Statement timeout

On 3 June 2016 at 09:45, Tatsuo Ishii wrote: > > Well, multiple parse/bind/execute messages before a sync are definitely > > used by PgJDBC and nPgSQL for batching, > > Yes, I realized in JDBC. > > > and I just posted a patch for it > > for libpq. > > I didn't noticed it.

Re: [HACKERS] Statement timeout

> Well, multiple parse/bind/execute messages before a sync are definitely > used by PgJDBC and nPgSQL for batching, Yes, I realized in JDBC. > and I just posted a patch for it > for libpq. I didn't noticed it. Could you give me the message id or URL? I wouldn't have considered it to simulate

Re: [HACKERS] Statement timeout

On 1 June 2016 at 08:33, Tatsuo Ishii wrote: > > FWIW, I think the existing behavior is just fine. It corresponds to what > > PQexec has always done with multi-statement query strings; that is, > > statement_timeout governs the total time to execute the transaction (the >

Re: [HACKERS] Statement timeout

On Wed, Jun 1, 2016 at 6:03 AM, Tatsuo Ishii wrote: > > From the document about statement_timeout (config.sgml): > > Abort any statement that takes more than the specified number of > milliseconds, starting from the time the command arrives at the server >

Re: [HACKERS] Statement timeout

> FWIW, I think the existing behavior is just fine. It corresponds to what > PQexec has always done with multi-statement query strings; that is, > statement_timeout governs the total time to execute the transaction (the > whole query string, unless you put transaction control commands in there).

Re: [HACKERS] Statement timeout

Tatsuo Ishii writes: >> Oops. Previous example was not appropriate. Here are revised >> examples. (in any case, the time consumed at parse and bind are small, >> and I omit the CHECK_FOR_INTERRUPTS after these commands) FWIW, I think the existing behavior is just fine. It

Re: [HACKERS] Statement timeout

> Oops. Previous example was not appropriate. Here are revised > examples. (in any case, the time consumed at parse and bind are small, > and I omit the CHECK_FOR_INTERRUPTS after these commands) > > [example 1] > > statement_timeout = 3s > > parse(statement1) -- time 0. set statement timout

Re: [HACKERS] Statement timeout

> Really? It should get reported at some execution of CHECK_FOR_INTERRUPTS > after we pass the 2-second mark in execute(portal1). If that's not what > you're observing, maybe you've found a code path that's missing some > CHECK_FOR_INTERRUPTS call(s). Oops. Previous example was not appropriate.

Re: [HACKERS] Statement timeout

Tatsuo Ishii writes: > When extended query protocol message is used, statement timeout is not > checked until a sync message is received (more precisely, statement > timeout timer is canceled while processing the sync message, and > actual checking timeout is done in

[HACKERS] Statement timeout

When extended query protocol message is used, statement timeout is not checked until a sync message is received (more precisely, statement timeout timer is canceled while processing the sync message, and actual checking timeout is done in CHECK_FOR_INTERRUPTS). Example: parse(statement1)

Re: [HACKERS] Statement timeout logging

On 6 June 2013 17:28, Pavel Stehule pavel.steh...@gmail.com wrote: 2013/6/6 Thom Brown t...@linux.com: Hi, When a statement is cancelled due to it running for long enough for statement_timeout to take effect, it logs a message: ERROR: canceling statement due to statement timeout

[HACKERS] Statement timeout logging

Hi, When a statement is cancelled due to it running for long enough for statement_timeout to take effect, it logs a message: ERROR: canceling statement due to statement timeout However, it doesn't log what the timeout was at the time of the cancellation. This may be set in postgresql.conf,

Re: [HACKERS] Statement timeout logging

2013/6/6 Thom Brown t...@linux.com: Hi, When a statement is cancelled due to it running for long enough for statement_timeout to take effect, it logs a message: ERROR: canceling statement due to statement timeout However, it doesn't log what the timeout was at the time of the

Re: [HACKERS] statement timeout vs dump/restore

On Monday 05 May 2008 09:01:25 Andrew Dunstan wrote: Zeugswetter Andreas OSB sIT wrote: Do we want the following: 1. pg_dump issues set statement_timeout = 0; to the database prior to taking its copy of data (yes/no/default-but-switchable) 2. pg_dump/pg_restore issue set

Re: [HACKERS] statement timeout vs dump/restore

Robert Treat [EMAIL PROTECTED] writes: ISTR being unconvinced by the pg_restore arguments, but as I think about it some more, for someone to set statement_timeout on a production system, and then have that be blindly overridden by any random pg_dump user seems a bit unfair. pg_dump is not

Re: [HACKERS] statement timeout vs dump/restore

Tom Lane wrote: Robert Treat [EMAIL PROTECTED] writes: ISTR being unconvinced by the pg_restore arguments, but as I think about it some more, for someone to set statement_timeout on a production system, and then have that be blindly overridden by any random pg_dump user seems a bit

Re: [HACKERS] statement timeout vs dump/restore

Do we want the following: 1. pg_dump issues set statement_timeout = 0; to the database prior to taking its copy of data (yes/no/default-but-switchable) 2. pg_dump/pg_restore issue set statement_timeout = 0; in text mode output (yes/no/default-but-switchable) 3. pg_restore

Re: [HACKERS] statement timeout vs dump/restore

Zeugswetter Andreas OSB sIT wrote: Do we want the following: 1. pg_dump issues set statement_timeout = 0; to the database prior to taking its copy of data (yes/no/default-but-switchable) 2. pg_dump/pg_restore issue set statement_timeout = 0; in text mode

[HACKERS] statement timeout vs dump/restore

I'm a bit confused about where the consensus is on this issue ( http://archives.postgresql.org/message-id/[EMAIL PROTECTED] et al) Do we want the following: 1. pg_dump issues set statement_timeout = 0; to the database prior to taking its copy of data (yes/no/default-but-switchable) 2.

Re: [HACKERS] statement timeout vs dump/restore

Andrew Dunstan [EMAIL PROTECTED] writes: Do we want the following: 1. pg_dump issues set statement_timeout = 0; to the database prior to taking its copy of data (yes/no/default-but-switchable) 2. pg_dump/pg_restore issue set statement_timeout = 0; in text mode output

Re: [HACKERS] statement timeout vs dump/restore

Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Do we want the following: 1. pg_dump issues set statement_timeout = 0; to the database prior to taking its copy of data (yes/no/default-but-switchable) 2. pg_dump/pg_restore issue set statement_timeout = 0; in text mode output