Re: [HACKERS] [GENERAL] now() AT TIME ZONE 'GMT';
(on -hackers) > If you apply the AT TIME ZONE operator to a TIMESTAMP WITH TIME ZONE > value, what happens is the internal GMT value is rotated to the > specified zone and then the output is labeled as type TIMESTAMP WITHOUT > TIME ZONE, preventing any further automatic zone rotations. Hmm. That is how it probably *should* work, but at the moment the timestamptz_zone() function actually outputs a character string! That is a holdover from previous versions which did not have a "no zone" timestamp; it would seem now to be more appropriate to output a no-zone timestamp. I'll look at changing this in my upcoming patch set... - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Strange problem when upgrading to 7.2 with pg_upgrade.
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Since I have done a vacuum, I assume I just keep creating 256k chunks > > until I reach the max xid from the previous release, and delete the > > files prior to the 1gb size limit. > > Keep your hands *off* the existing segments. The CLOG code will clean > them up when it's good and ready ... OK. Fill out the current clog and add additional ones to reach the current max xid, rounded to the nearest 8k, assuming 256k file equals 1mb of xids. Why do you take these things so personally? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Strange problem when upgrading to 7.2 with pg_upgrade.
Bruce Momjian <[EMAIL PROTECTED]> writes: > Since I have done a vacuum, I assume I just keep creating 256k chunks > until I reach the max xid from the previous release, and delete the > files prior to the 1gb size limit. Keep your hands *off* the existing segments. The CLOG code will clean them up when it's good and ready ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Strange problem when upgrading to 7.2 with pg_upgrade.
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > OK, I can 'dd' /dev/zero to append zeros to pad out the file. How large > > does the clog file get, 1gb? Do I need to rename it at all? > > 256KB per segment. Do *not* rename existing segments. Right, no rename, but I will have to create additional files in 256kb chunks, and I assume 1gb of chunks remains in pg_clog directory? Since I have done a vacuum, I assume I just keep creating 256k chunks until I reach the max xid from the previous release, and delete the files prior to the 1gb size limit. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Strange problem when upgrading to 7.2 with pg_upgrade.
Bruce Momjian <[EMAIL PROTECTED]> writes: > OK, I can 'dd' /dev/zero to append zeros to pad out the file. How large > does the clog file get, 1gb? Do I need to rename it at all? 256KB per segment. Do *not* rename existing segments. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Strange problem when upgrading to 7.2 with pg_upgrade.
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom, any suggestion on how I can increase clog as part of pg_upgrade? > > Append zeroes ... OK, I can 'dd' /dev/zero to append zeros to pad out the file. How large does the clog file get, 1gb? Do I need to rename it at all? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Strange problem when upgrading to 7.2 with pg_upgrade.
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom, any suggestion on how I can increase clog as part of pg_upgrade? Append zeroes ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Strange problem when upgrading to 7.2 with pg_upgrade.
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > >> I suspect that pg_upgrade has neglected to make sure the clog is long > >> enough. > > > Here is the code that sets the transaction id. Tom, does pg_resetxlog > > handle pg_clog file creation properly? > > pg_resetxlog doesn't know a single solitary thing about the clog. > > The problem here is that if you're going to move the current xact ID > forward, you need to be prepared to create pages of the clog > accordingly. Or maybe the clog routines need to be less rigid in their > assumptions, but I'm uncomfortable with relaxing their expectations > unless it can be shown that they may fail to cope with cases that > arise in normal system operation. This isn't such a case. Tom, any suggestion on how I can increase clog as part of pg_upgrade? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Solaris ISM Testing
Added to TODO: > * Add Intimate Shared Memory(ISM) for Solaris > * Add documentation to lock shared memory into RAM for each OS, if possible I have re-requested the Solaris patch for ISM. --- Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > The attached email shows that Solaris benefits from the ISM or Intimate > > Shared Memory setting during shmat() shared memory creation. It causes > > processes mapping the same shared memory to shared mapping pages _and_ > > locks the pages in RAM. > > Huh? I understand "locks the pages in RAM" but I don't understand the > first part of that. ISTM shared memory is shared memory; if we didn't > share it without this flag, we'd not be working at all on Solaris. > > > I know many OS's lock shared memory in RAM anyway, or have OS parameters > > that control this (FreeBSD), but it seems Solaris does this on a per > > shmat() basis. Should we add this flag to shmat() calls for Solaris? > > Certainly on any OS where we can request pinning our shmem in RAM, we > should do so --- I've pointed out before that allowing our disk buffers > to be swapped out can't be anything but counterproductive. Not sure > that this should be thought of as an "#ifdef SOLARIS" kind of change; > do any other Unixen share this aspect of the API? > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PL/pgSQL RENAME bug?
Added to TODO: o Fix PL/pgSQL RENAME to work on on variable names --- Jan Wieck wrote: > Bruce Momjian wrote: > > Digging into it now, I remember why it is there. In the > Oracle world, someone can declare a trigger that references > to NEW or OLD by other names. This RENAME was a workaround so > one doesn't need to change the whole trigger body, but just > adds a line in the DECLARE section doing the job. > > Therefore, I think removal is not such a good idea. Fixing it > properly will take a little longer as I am a little busy at > the moment. > > > Jan > > > Jan, seems no one has commented on this. Patch? > > > > Jan Wieck wrote: > > > Tom Lane wrote: > > > > "Command Prompt, Inc." <[EMAIL PROTECTED]> writes: > > > > > Mainly, the existing documentation on the RENAME statement seems > > > > > inaccurate; it states that you can re-name variables, records, or > > > > > rowtypes. However, in practice, our tests show that attempting to RENAME > > > > > valid variables with: > > > > > RENAME varname TO newname; > > > > > ...yeilds a PL/pgSQL parse error, inexplicably. If I try the same syntax > > > > > on a non-declared variable, it actually says "there is no variable" with > > > > > that name in the current block, so...I think something odd is happening. :) > > > > > > > > Yup, this is a bug. The plpgsql grammar expects varname to be a T_WORD, > > > > but in fact the scanner will only return T_WORD for a name that is not > > > > any known variable name. Thus RENAME cannot possibly work, and probably > > > > never has worked. > > > > > > > > Looks like it should accept T_VARIABLE, T_RECORD, T_ROW (at least). > > > > T_WORD ought to draw "no such variable". Jan, I think this is your turf... > > > > > > Sounds pretty much like that. Will take a look. > > > > > > > > > > > > The RENAME statement seems kind of odd, since it seems that you could just > > > > > as easily declare a general variable with the right name to begin with, > > > > > > > > It seems pretty useless to me too. Perhaps it's there because Oracle > > > > has one? > > > > > > And I don't even remember why I've put it in. Maybe because > > > it's an Oracle thing. This would be a cool fix, removing the > > > damned thing completely. I like that solution :-) > > > > > > Anyone against removal? > > > > > > > > > Jan > > > > > > -- > > > > > > #==# > > > # It's easier to get forgiveness for being wrong than for being right. # > > > # Let's break this rule - forgive me. # > > > #== [EMAIL PROTECTED] # > > > > > > > > > > > > _ > > > Do You Yahoo!? > > > Get your free @yahoo.com address at http://mail.yahoo.com > > > > > > > > > ---(end of broadcast)--- > > > TIP 5: Have you checked our extensive FAQ? > > > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > > > > -- > > Bruce Momjian| http://candle.pha.pa.us > > [EMAIL PROTECTED] | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > > > > > -- > > #==# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #== [EMAIL PROTECTED] # > > > > _ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] timeout implementation issues
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 [EMAIL PROTECTED] | (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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout implementation issues
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] timeout implementation issues
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 [EMAIL PROTECTED] | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] timeout implementation issues
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] timeout implementation issues
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 [EMAIL PROTECTED] | (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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] timeout implementation issues
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] What's the CURRENT schema ?
Tom Lane wrote: > > "Hiroshi Inoue" <[EMAIL PROTECTED]> writes: > > However I can see the following at 5.4 Names and Identifiers > > 11) If a does not contain a , then > >Case: > >a) If the is contained in a > definition>, > >then the that is specified or implicit in the > definition> > > is implicit. > > Yes. Fernando, our existing CREATE SCHEMA command does not get this > right for references from views to tables, does it? It seems to me that > to get compliant behavior, we'll need to temporarily push the new schema > onto the front of the namespace search path while parsing view > definitions inside CREATE SCHEMA. > Correct. It only takes care of proper setting/checking the schema name for the view (as is done for tables) that are being created. Doing as you suggest would be nice (similar to what we do with the authid). BTW, I think have to properly fill/check the schema when the grant objects are tables/views (I am not sure how functions will be handled). I will send a patch in later today or tomorrow, unless you want to do it differently. I prefer to do in in the parser because I can issue and error if a grant is for something that is not an object in the schema being created. Regards, Fernando -- Fernando Nasser Red Hat Canada Ltd. E-Mail: [EMAIL PROTECTED] 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout implementation issues
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 [EMAIL PROTECTED] | (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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout implementation issues
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > > -Original Message- > > > From: Bruce Momjian [mailto:[EMAIL PROTECTED]] > > > > > > > > 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout implementation issues
Hiroshi Inoue wrote: > > -Original Message- > > From: Bruce Momjian [mailto:[EMAIL PROTECTED]] > > > > > > 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 [EMAIL PROTECTED] | (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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] timeout implementation issues
> -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED]] > > > > 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] timeout implementation issues
Hiroshi Inoue wrote: > > -Original Message- > > From: Bruce Momjian [mailto:[EMAIL PROTECTED]] > > > > 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 [EMAIL PROTECTED] | (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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout implementation issues
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED]] > > "Hiroshi Inoue" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] What's the CURRENT schema ?
"Hiroshi Inoue" <[EMAIL PROTECTED]> writes: > However I can see the following at 5.4 Names and Identifiers > 11) If a does not contain a , then >Case: >a) If the is contained in a definition>, >then the that is specified or implicit in the definition> > is implicit. Yes. Fernando, our existing CREATE SCHEMA command does not get this right for references from views to tables, does it? It seems to me that to get compliant behavior, we'll need to temporarily push the new schema onto the front of the namespace search path while parsing view definitions inside CREATE SCHEMA. (The relevance to the current discussion is that this is easy to do if SET variables roll back on error ... but it might be tricky if they do not.) regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] timeout implementation issues
> -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED]] > > 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] timeout implementation issues
Bruce Momjian <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] timeout implementation issues
Jan Wieck <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] What's the CURRENT schema ?
> -Original Message- > From: Fernando Nasser > > Hiroshi Inoue wrote: > > > > > You misunderstood what I've said. You may have how many schemas > > > you please. But you will have to refer to their objects specifying > > > the schema name explicitly. The only cases where you can omit the > > > schema name are (accordingly to the SQL'99 standard): > > > > Please tell me where's the description in SQL99 ? > > I wasn't able to find it unfortunately. > > > > As most things in the SQL standard, you have to collect information > from several places and add it together. > > Look at 4.20, 11.1 and specially at the rules for > . OK I can see at 4.20. If a reference to a does not explicitly contain a , then a specific is implied. The particular associated with such a depends on the context in which the appears and is governed by the rules for . Unfortunately I can't find what to see at 11.1. Please tell me where to see. However I can see the following at 5.4 Names and Identifiers 11) If a does not contain a , then Case: a) If the is contained in a , then the that is specified or implicit in the is implicit. b) Otherwise, the that is specified or implicit for the is implicit. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] timeout implementation issues
"Hiroshi Inoue" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout implementation issues
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 [EMAIL PROTECTED] | (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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] timeout implementation issues
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] timeout implementation issues
> -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED]] > > 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] timeout implementation issues
Tom Lane wrote: > Jan Wieck <[EMAIL PROTECTED]> 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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] timeout implementation issues
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 [EMAIL PROTECTED] | (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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] timeout implementation issues
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 [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] What's the CURRENT schema ?
Hiroshi Inoue wrote: > > > You misunderstood what I've said. You may have how many schemas > > you please. But you will have to refer to their objects specifying > > the schema name explicitly. The only cases where you can omit the > > schema name are (accordingly to the SQL'99 standard): > > Please tell me where's the description in SQL99 ? > I wasn't able to find it unfortunately. > As most things in the SQL standard, you have to collect information from several places and add it together. Look at 4.20, 11.1 and specially at the rules for . Then think a little bit about scenarios, trying to apply the rules. It is a pain, but there is no other way. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: [EMAIL PROTECTED] 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Debugging symbols by default
Bruce Momjian writes: > I am not sure about the idea of -g by default. I know lyx uses -g by > default, and the compile/install takes forever. In fact, I have removed > -g from my compiles here because it takes too long to compile/link and I > do it too often. When I need to debug, I recompile with -g. My concern > is that we may start to look very bloated with -g and those huge > binaries. My question is whether it is worth the install slowness/bloat? PostgreSQL compile time is minimal compared to other packages. If you're worried about 30 seconds, turn off the optimization or use parallel make. If you see yourself doing a full build too often, turn on dependency tracking. The extra time you spend building with -g is the time you save yourself and the users from having to recompile everything because a bug needs to be tracked down. And when you rebuild, the bug might not be reproduceable. I don't buy the disk space argument either. If you're worried about a few megabytes then you going to have a lot of trouble running a database. And if you're still worried, you can run install-strip, which is the standard way to do it. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] timeout implementation issues
> > 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] timeout implementation issues
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 [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] timeout implementation issues
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 [EMAIL PROTECTED] | (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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] timeout implementation issues
Jan Wieck <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] timeout implementation issues
Tom Lane wrote: > Karel Zak <[EMAIL PROTECTED]> 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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] timeout implementation issues
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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] timeout implementation issues
Karel Zak <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout implementation issues
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 [EMAIL PROTECTED] | (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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout implementation issues
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 [EMAIL PROTECTED] | (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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout implementation issues
> -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED]] > > 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] best method for select within all dimensions of an array
hello when selecting in a table with a text[] there's an sql command for looking all dimensions of the array something like 'where text[*]' or only should be implemented trought a 'for (i=0; ihttp://archives.postgresql.org
Re: [HACKERS] timeout implementation issues
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 <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] timeout implementation issues
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 <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] timeout implementation issues
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 <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
