Re: [SQL] [GENERAL] Monitoring a Query
Bruce Momjian wrote: > Neil Conway wrote: > >>Bruce Momjian <[EMAIL PROTECTED]> writes: >> >>>Aaron Held wrote: >>> Is there any way to monitor a long running query? >>> >>>Oh, sorry, you want to know how far the query has progressed. Gee, I >>>don't think there is any easy way to do that. >> >>Would it be a good idea to add the time that the current query began >>execution at to pg_stat_activity? > > > What do people think about this? It seems like a good idea to me. > My application marks the start time of each query and I have found it very useful. The users like to see how long each query took, and the admin can take a quick look and see how many queries are running and how long each has been active for. Good for debugging and billing. -Aaron Held ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Performance w/ multiple WHERE clauses
I am running pg 7.2 the PG reference build. Thanks for the ANALYZE tip, it led me to a answer. This database gets a monthly update and it read only until the next update. I ANALYZE once after each update. Since the data does not change I should not need to ANALYZE again afterwards. I mentioned this to the dbadmin that manages the data and found out one of the other users UPDATED some of the columns the morning that I was seeing this behavior. I'll reANALYZE and see what happens. Thanks, -Aaron Held Josh Berkus wrote: > Aaron, > > >> # SET enable_seqscan to FALSE ; >> forced the use of an Index and sped things up greatly. >> >>I am not sure why it made the switch. The load on the server seems to >>affect the performance, but I am seeing it more on the production server >>with 100 million rows as opposed to the development server with only >>about 6 million. I need to buy more drives and develop on a larger data >>set. > > > What version are you using? > > I'd have 3 suggestions: > 1) ANALYZE, ANALYZE, ANALYZE. Then check if the row estimates made by EXPLAIN > seem accurate. > 2) Modify your postgresql.conf file to raise the cost of seq_scans for parser > estimates. > 3) Test this all again when 7.3 comes out, as parser estimate improves all the > time. > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] Monitoring a Query
On Sun, Sep 22, 2002 at 09:51:55PM -0400, Bruce Momjian wrote: > > > > Would it be a good idea to add the time that the current query began > > execution at to pg_stat_activity? > > What do people think about this? It seems like a good idea to me. OpenACS has a package called "Developer Support" that shows you (among other things) how long a query took to be executed. Very good to finding out slow-running queries that need to be optimized. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Stripping white-space in SELECT statments
Thank's Tom Lane & Stephan Szabo for pointing out the problem to me. After some testing it turned out that the swedish locale, 'sv_SE', doesn't handle sorting spaces as expected, which probably made the SELECT fail. On the other hand, if I use the 'C' locale, the SELECT works but not the sort order of the swedish characters 'åäö'. Does anyone know a solution to this problem, or could give me a hint? > -Ursprungligt meddelande- > Från: Tom Lane [mailto:[EMAIL PROTECTED]] > Skickat: den 19 september 2002 16:32 > Till: [EMAIL PROTECTED] > Kopia: [EMAIL PROTECTED] > Ämne: Re: [SQL] Stripping white-space in SELECT statments > > > =?iso-8859-1?Q?Thorbj=F6rn_Eriksson?= <[EMAIL PROTECTED]> writes: > > I've encountered a strange behavior in postgres 7.2.1 regarding how psql > > handles strings ending with space characters. > > Perhaps you are running in a non-C locale? A lot of locales have > sorting rules that are pretty weird about whitespace. > > > The reason that we don't use 'LIKE 201901 %' is that it don't use > > the index > > This suggests strongly that you are in a non-C locale. Your external > software seems to be emulating the standard LIKE-to-index optimization; > which as you are now discovering, does not work with non-C sorting > rules (so the system doesn't try to apply it). > > regards, tom lane > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Stripping white-space in SELECT statments
=?iso-8859-1?Q?Thorbj=F6rn_Eriksson?= <[EMAIL PROTECTED]> writes: > Thank's Tom Lane & Stephan Szabo for pointing out the problem to me. > After some testing it turned out that the swedish locale, 'sv_SE', doesn't > handle sorting spaces as expected, which probably made the SELECT fail. On > the other hand, if I use the 'C' locale, the SELECT works but not the sort > order of the swedish characters 'åäö'. > Does anyone know a solution to this problem, or could give me a hint? I think you are going to have to create a custom locale definition that sorts the accented characters as you wish, but does not have the strange rules about whitespace. I don't know enough about locale definitions to give advice on how ... but I'll bet after a couple hours study you could do it by copying just the parts you want of the existing Swedish locale definition. 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: [SQL] [GENERAL] Monitoring a Query
It looks like that just timestamps things in its connection pool, that is what I do now. What I would like is to know about queries that have not finished yet. -Aaron Roberto Mello wrote: > On Sun, Sep 22, 2002 at 09:51:55PM -0400, Bruce Momjian wrote: > >>>Would it be a good idea to add the time that the current query began >>>execution at to pg_stat_activity? >> >>What do people think about this? It seems like a good idea to me. > > > OpenACS has a package called "Developer Support" that shows you (among > other things) how long a query took to be executed. Very good to finding > out slow-running queries that need to be optimized. > > -Roberto > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] Monitoring a Query
Roberto Mello wrote: > On Sun, Sep 22, 2002 at 09:51:55PM -0400, Bruce Momjian wrote: > > > > > > Would it be a good idea to add the time that the current query began > > > execution at to pg_stat_activity? > > > > What do people think about this? It seems like a good idea to me. > > OpenACS has a package called "Developer Support" that shows you (among > other things) how long a query took to be executed. Very good to finding > out slow-running queries that need to be optimized. 7.3 will have GUC 'log_duration' which will show query duration. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] Monitoring a Query
Aaron Held wrote: > It looks like that just timestamps things in its connection pool, that > is what I do now. > > What I would like is to know about queries that have not finished yet. OK, added to TODO: * Add start time to pg_stat_activity Should we supply the current duration too? That value would change on each call. Seems redundant. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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: [SQL] [GENERAL] Monitoring a Query
Bruce Momjian <[EMAIL PROTECTED]> writes: > OK, added to TODO: > * Add start time to pg_stat_activity It would be nearly free to include the start time of the current transaction, because we already save that for use by now(). Is that good enough, or do we need start time of the current query? 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: [SQL] [GENERAL] Monitoring a Query
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > OK, added to TODO: > > * Add start time to pg_stat_activity > > It would be nearly free to include the start time of the current > transaction, because we already save that for use by now(). Is > that good enough, or do we need start time of the current query? Current query, I am afraid. We could optimize it so single-query transactions wouldn't need to call that again. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] Getting acces to MVCC version number
That is great! Thanks for the info. Tom Lane wrote: > > Jean-Luc Lachance <[EMAIL PROTECTED]> writes: > > How about making available the MVCC last version number just like oid is > > available. This would simplify a lot of table design. You know, having > > to add a field "updated::timestamp" to detect when a record was updated > > while viewing it (a la pgaccess). > > That way, if the version number do not match, one would know that the > > reccord was updated since last retrieved. > > > What do think? > > I think it's already there: see xmin and cmin. Depending on your needs, > testing xmin might be enough (you'd only need to pay attention to cmin > if you wanted to notice changes within your own transaction). > > regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Getting current transaction id
Hi all, I just read it's possible to get the MVCC last version numbers. Is it also possible to get the current transaction id? Would it be possible to check later if that transaction has been commited? This would be nice for a distributed application to enforce an "exactly once" semantics for transactions (even if there are network related errors while the server sends ack for commiting a transaction). And if it's possible, how long would that information be valid, i.e. when do transaction id's get reused? If it's not working I will have to implement my own transactions table. Thanks in advance, Michael Paesold -- Werden Sie mit uns zum "OnlineStar 2002"! Jetzt GMX wählen - und tolle Preise absahnen! http://www.onlinestar.de ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] Monitoring a Query
On Mon, 23 Sep 2002 11:06:19 -0400 (EDT), Bruce Momjian <[EMAIL PROTECTED]> wrote: >Tom Lane wrote: >> It would be nearly free to include the start time of the current >> transaction, because we already save that for use by now(). Is >> that good enough, or do we need start time of the current query? > >Current query, I am afraid. We could optimize it so single-query >transactions wouldn't need to call that again. This has been discussed before and I know I'm going to get flamed for this, but IMHO having now() (which is a synonym for CURRENT_TIMESTAMP) return the start time of the current transaction is a bug, or at least it is not conforming to the standard. SQL92 says in 6.8 : General Rules 1) The s CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP respectively return the current date, current time, and current timestamp [...] ^^^ 3) If an SQL-statement generally contains more than one reference ^ to one or more s, then all such ref- erences are effectively evaluated simultaneously. The time of evaluation of the during the execution ^^ of the SQL-statement is implementation-dependent. SQL99 says in 6.19 : 3) Let S be an that is not generally contained in a . All s that are generally contained, without an intervening whose subject routines do not include an SQL function, in s that are contained either in S without an intervening or in an contained in the of a trigger activated as a consequence of executing S, are effectively evaluated simultaneously. The time of evaluation of a during the execution of S and its activated triggers is implementation-dependent. I cannot say that I fully understand the second sentence (guess I have to read it for another 100 times), but "during the execution of S" seems to mean "not before the start and not after the end of S". What do you think? Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [GENERAL] Monitoring a Query
Manfred Koizar <[EMAIL PROTECTED]> writes: > This has been discussed before and I know I'm going to get flamed for > this, but IMHO having now() (which is a synonym for CURRENT_TIMESTAMP) > return the start time of the current transaction is a bug, or at least > it is not conforming to the standard. As you say, it's been discussed before. We concluded that the spec defines the behavior as implementation-dependent, and therefore we can pretty much do what we want. If you want exact current time, there's always timeofday(). regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
On Mon, 23 Sep 2002 13:05:42 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: >Manfred Koizar <[EMAIL PROTECTED]> writes: >> This has been discussed before and I know I'm going to get flamed for >> this, but IMHO having now() (which is a synonym for CURRENT_TIMESTAMP) >> return the start time of the current transaction is a bug, or at least >> it is not conforming to the standard. > >As you say, it's been discussed before. Yes, and I hate to be annoying. >We concluded that the spec defines the behavior as >implementation-dependent, AFAICT the spec requires the returned value to meet two conditions. C1: If a statement contains more than one , they all have to return (maybe different formats of) the same value. C2: The returned value has to represent a point in time *during* the execution of the SQL-statement. The only thing an implementor is free to choose is which point in time "during the execution of the SQL-statement" is to be returned, i.e. a timestamp in the interval between the start of the statement and the first time when the value is needed. The current implementation only conforms to C1. >and therefore we can pretty much do what we want. Start time of the statement, ... of the transaction, ... of the session, ... of the postmaster, ... of the century? I understand that with subselects, functions, triggers, rules etc. it is not easy to implement the specification. If we can't do it now, we should at least add a todo and make clear in the documentation that CURRENT_DATE/TIME/TIMESTAMP is not SQL92/99 compliant. Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Manfred, > C2: The returned value has to represent a point in time *during* the > execution of the SQL-statement. > > The only thing an implementor is free to choose is which point in time > "during the execution of the SQL-statement" is to be returned, i.e. a > timestamp in the interval between the start of the statement and the > first time when the value is needed. > > The current implementation only conforms to C1. I, for one, would judge that the start time of the statement is "during the execution"; it would only NOT be "during the execution" if it was a value *before* the start time of the statement. It's a semantic argument. The spec is, IMHO, rather vague on how this would relate to transactions. I do not find it at all inconsitent that Bruce, Thomas, and co. interpreted a transaction to be an extension of an individual SQL statement for this purpose (at least, that's what I guess they did). Thus, if you accept the postulates that: 1) "During" a SQL statement includes the start time of the statement, and 2) A Transaction is the equivalent of a single SQL statement for many purposes, Then the current behavior is a logical conclusion. Further, we could not change that behaviour without breaking many people's applications. Ideally, since we get this question a lot, that a compile-time or execution-time switch to change the behavior of current_timestamp contextually would be nice. We just need someone who;s interested enough in writing one. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Josh Berkus wrote: > I, for one, would judge that the start time of the statement is "during the > execution"; it would only NOT be "during the execution" if it was a value > *before* the start time of the statement. It's a semantic argument. > > The spec is, IMHO, rather vague on how this would relate to transactions. I > do not find it at all inconsitent that Bruce, Thomas, and co. interpreted a > transaction to be an extension of an individual SQL statement for this > purpose (at least, that's what I guess they did). > > Thus, if you accept the postulates that: > 1) "During" a SQL statement includes the start time of the statement, and > 2) A Transaction is the equivalent of a single SQL statement for many > purposes, > Then the current behavior is a logical conclusion. > > Further, we could not change that behaviour without breaking many people's > applications. I don't see how we can defend returning the start of the transaction as the current_timestamp. In a multi-statement transaction, that doesn't seem very current to me. I know there are some advantages to returning the same value for all queries in a transaction, but is that value worth returning such stale time information? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Bruce, > I don't see how we can defend returning the start of the transaction as > the current_timestamp. In a multi-statement transaction, that doesn't > seem very current to me. I know there are some advantages to returning > the same value for all queries in a transaction, but is that value worth > returning such stale time information? Then what *was* the reasoning behind the current behavior? -- -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ---(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: [SQL] [GENERAL] CURRENT_TIMESTAMP
Josh Berkus wrote: > > Bruce, > > > I don't see how we can defend returning the start of the transaction as > > the current_timestamp. In a multi-statement transaction, that doesn't > > seem very current to me. I know there are some advantages to returning > > the same value for all queries in a transaction, but is that value worth > > returning such stale time information? > > Then what *was* the reasoning behind the current behavior? I thought the spec required it, but now that I see it doesn't, I don't know why it was done that way. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Bruce Momjian <[EMAIL PROTECTED]> writes: > I don't see how we can defend returning the start of the transaction as > the current_timestamp. Here's an example: CREATE RULE foo AS ON INSERT TO mytable DO ( INSERT INTO log1 VALUES (... , now(), ...); INSERT INTO log2 VALUES (... , now(), ...) ); I think it's important that these commands store the same timestamp in both log tables (not to mention that any now() being stored into mytable itself generate that same timestamp). If you scale that up just a little bit, you can devise scenarios where successive client-issued commands (within a single transaction) want to store the same timestamp. After all, it's only a minor implementation detail that you chose to fire these logging operations via a rule and not by client-side logic. In short, there are plenty of situations where it's critical for application correctness that a series of commands all be able to operate with the same value of now(). I don't think that it's wise for Postgres to try to decide where within a transaction it's safe to advance now(). That will inevitably break some applications, and it's not obvious what the benefit is. In short: if you want exact current time, there's timeofday(). If you want start of transaction time, we've got that. If you want start of current statement time, I have two questions: why, and exactly how do you want to define current statement, considering functions, rules, triggers, and all that other stuff that makes it interesting? ISTM that if a client or function wants to record intratransaction times, it can call timeofday() at the appropriate points for itself. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] Monitoring a Query
On Mon, Sep 23, 2002 at 10:48:30AM -0400, Bruce Momjian wrote: > > > > > > > > Would it be a good idea to add the time that the current query began > > > > execution at to pg_stat_activity? > > > > > > What do people think about this? It seems like a good idea to me. > > > > OpenACS has a package called "Developer Support" that shows you (among > > other things) how long a query took to be executed. Very good to finding > > out slow-running queries that need to be optimized. > > 7.3 will have GUC 'log_duration' which will show query duration. Forgive my ignorance here, but what is GUC? And how would I access the query duration? -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Q: What is purple and commutes? A: A boolean grape. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
I see what you are saying now --- that even single user statements can trigger multiple statements, so you would have to say transaction start time is time the user query starts. I can see how that seems a little arbitrary. However, don't we have separate paths for user queries and queries sent as part of a rule? --- Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I don't see how we can defend returning the start of the transaction as > > the current_timestamp. > > Here's an example: > > CREATE RULE foo AS ON INSERT TO mytable DO > ( INSERT INTO log1 VALUES (... , now(), ...); > INSERT INTO log2 VALUES (... , now(), ...) ); > > I think it's important that these commands store the same timestamp in > both log tables (not to mention that any now() being stored into mytable > itself generate that same timestamp). > > If you scale that up just a little bit, you can devise scenarios where > successive client-issued commands (within a single transaction) want to > store the same timestamp. After all, it's only a minor implementation > detail that you chose to fire these logging operations via a rule and > not by client-side logic. > > In short, there are plenty of situations where it's critical for > application correctness that a series of commands all be able to operate > with the same value of now(). I don't think that it's wise for Postgres > to try to decide where within a transaction it's safe to advance now(). > That will inevitably break some applications, and it's not obvious what > the benefit is. > > In short: if you want exact current time, there's timeofday(). If you > want start of transaction time, we've got that. If you want start of > current statement time, I have two questions: why, and exactly how do > you want to define current statement, considering functions, rules, > triggers, and all that other stuff that makes it interesting? > > ISTM that if a client or function wants to record intratransaction > times, it can call timeofday() at the appropriate points for itself. > > regards, tom lane > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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: [SQL] [GENERAL] Monitoring a Query
Roberto Mello wrote: > On Mon, Sep 23, 2002 at 10:48:30AM -0400, Bruce Momjian wrote: > > > > > > > > > > Would it be a good idea to add the time that the current query began > > > > > execution at to pg_stat_activity? > > > > > > > > What do people think about this? It seems like a good idea to me. > > > > > > OpenACS has a package called "Developer Support" that shows you (among > > > other things) how long a query took to be executed. Very good to finding > > > out slow-running queries that need to be optimized. > > > > 7.3 will have GUC 'log_duration' which will show query duration. > > Forgive my ignorance here, but what is GUC? And how would I access the > query duration? GUC is postgresql.conf and SET commands. They are variables that can be set. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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: [SQL] [GENERAL] CURRENT_TIMESTAMP
Bruce Momjian <[EMAIL PROTECTED]> writes: > I see what you are saying now --- that even single user statements can > trigger multiple statements, so you would have to say transaction start > time is time the user query starts. I can see how that seems a little > arbitrary. However, don't we have separate paths for user queries and > queries sent as part of a rule? We could use "time of arrival of the latest client command string", if we wanted to do something like this. My point is that that very arbitrarily assumes that those are the significant points within a transaction, and that the client has no need to send multiple commands that want to insert the same timestamp into different tables. This is an unwarranted assumption about the client's control structure, IMHO. A possible compromise is to dissociate now() and current_timestamp, allowing the former to be start of transaction and the latter to be start of client command. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I see what you are saying now --- that even single user statements can > > trigger multiple statements, so you would have to say transaction start > > time is time the user query starts. I can see how that seems a little > > arbitrary. However, don't we have separate paths for user queries and > > queries sent as part of a rule? > > We could use "time of arrival of the latest client command string", > if we wanted to do something like this. My point is that that very > arbitrarily assumes that those are the significant points within a > transaction, and that the client has no need to send multiple commands > that want to insert the same timestamp into different tables. This is > an unwarranted assumption about the client's control structure, IMHO. > > A possible compromise is to dissociate now() and current_timestamp, > allowing the former to be start of transaction and the latter to be > start of client command. I was thinking 'transaction_timestamp' for the transaction start time, and current_timestamp for the statement start time. I would equate now() with current_timestamp. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Tom, Bruce, > > A possible compromise is to dissociate now() and current_timestamp, > > allowing the former to be start of transaction and the latter to be > > start of client command. > > I was thinking 'transaction_timestamp' for the transaction start time, and > current_timestamp for the statement start time. I would equate now() > with current_timestamp. May I point out that this will break compatibility for those used to the current behavior? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Josh Berkus wrote: > > Tom, Bruce, > > > > A possible compromise is to dissociate now() and current_timestamp, > > > allowing the former to be start of transaction and the latter to be > > > start of client command. > > > > I was thinking 'transaction_timestamp' for the transaction start time, and > > current_timestamp for the statement start time. I would equate now() > > with current_timestamp. > > May I point out that this will break compatibility for those used to the > current behavior? I am not saying we have to make that change. My point is that our current behavior may not be the most intuitive, and that most people may prefer a change. Any such change would be documented in the release notes. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Performance w/ multiple WHERE clauses
Aaron, > This database gets a monthly update and it read only until the next > update. I ANALYZE once after each update. Since the data does not > change I should not need to ANALYZE again afterwards. Actually, if the database is read-only between updates, you should: ANALYZE VACUUM FULL FREEZE ... between data updates, which will give you the best performance possible. But not if the data is actually going to be edited, even slightly. See the online manual entries about VACUUM for an explanation. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Bruce Momjian writes: > My point is that our current behavior may not be the most intuitive, and > that most people may prefer a change. I would prefer a change. -- John Hasler [EMAIL PROTECTED] Dancing Horse Hill Elmwood, Wisconsin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
John Hasler wrote: > Bruce Momjian writes: > > My point is that our current behavior may not be the most intuitive, and > > that most people may prefer a change. > > I would prefer a change. Yes, I guess that is my point, that we want to make transaction _and_ statement timestamp values available, but most people are going to use current_timestamp, and most people are going to assume it is statement time, not transaction time. Can I add TODO items for this: o Make CURRENT_TIMESTAMP/now() return statement start time o Add TRANSACTION_TIMESTAMP to return transaction start time -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [GENERAL] Monitoring a Query
Alvaro Herrera wrote: > Bruce Momjian dijo: > > > Roberto Mello wrote: > > > > Forgive my ignorance here, but what is GUC? And how would I access the > > > query duration? > > > > GUC is postgresql.conf and SET commands. They are variables that can be > > set. > > Just for the record, GUC is an acronym for "Grand Unified > Configuration". Thanks. I couldn't remember that. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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: [SQL] [GENERAL] CURRENT_TIMESTAMP
Bruce Momjian <[EMAIL PROTECTED]> writes: > I was thinking 'transaction_timestamp' for the transaction start time, and > current_timestamp for the statement start time. I would equate now() > with current_timestamp. So you want to both (a) invent even more nonstandard syntax than we already have, and (b) break as many traditional-Postgres applications as you possibly can? 'transaction_timestamp' has no reason to live. It's not in the spec. And AFAIK the behavior of now() has been well-defined since the beginning of Postgres. If you want to change 'current_timestamp' to conform to a rather debatable reading of the spec, then fine --- but keep your hands off of now(). regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I was thinking 'transaction_timestamp' for the transaction start time, and > > current_timestamp for the statement start time. I would equate now() > > with current_timestamp. > > So you want to both (a) invent even more nonstandard syntax than we > already have, and (b) break as many traditional-Postgres applications > as you possibly can? No, but I would like to see you stop makeing condescending replies to emails. How is that! > 'transaction_timestamp' has no reason to live. It's not in the spec. > And AFAIK the behavior of now() has been well-defined since the > beginning of Postgres. If you want to change 'current_timestamp' to > conform to a rather debatable reading of the spec, then fine --- but > keep your hands off of now(). Oh, really.When you get down off your chair we can vote on it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Bruce Momjian <[EMAIL PROTECTED]> writes: > Can I add TODO items for this: > o Make CURRENT_TIMESTAMP/now() return statement start time > o Add TRANSACTION_TIMESTAMP to return transaction start time I object to both of those as phrased. If you have already unilaterally determined the design of this feature change, then go ahead and put that in. But I'd suggest o Revise current-time functions to allow access to statement start time which doesn't presuppose the vote about how to do it. 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: [SQL] [GENERAL] CURRENT_TIMESTAMP
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Can I add TODO items for this: > > o Make CURRENT_TIMESTAMP/now() return statement start time > > o Add TRANSACTION_TIMESTAMP to return transaction start time > > I object to both of those as phrased. If you have already unilaterally > determined the design of this feature change, then go ahead and put that > in. But I'd suggest > > o Revise current-time functions to allow access to statement > start time > > which doesn't presuppose the vote about how to do it. OK, I am still just throwing out ideas. I am not sure we even have enough people who want statement_timestamp to put it in TODO. I do think we have a standards issue. My personal opinion is that most people think current_timestamp and now() are statement start time, not transaction start time. In the past we have told them the standard requires that but now I think we are not even sure if that is correct. So, I have these concerns: our CURRENT_TIMESTAMP may not be standards compliant even if it is, it is probably not returning the value most people want most people don't know it is returning the transaction start time So, we can just throw the TODO item you mentioned above with a question mark, or we can try to figure out what to return for CURRENT_TIMESTAMP, now(), and perhaps create a TRANSACTION_TIMESTAMP. So, do people want to discuss it or should we just throw it in TODO with a question mark? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
