[GENERAL] what does pg_activity mean when the database is stuck?
I have a problem where postgresql 9.3 got stuck, and the number of postgresql processes increased from about 15 to 225 in 10 minutes. I ran the query: select pid, query_start, waiting, state, query from pg_stat_activity order by query_start; But it showed mostly select statements -- all of them the same one, with a couple of joins. They are not in a waiting state but have been running for over 2 hours. I also checked for locks with the query on http://wiki.postgresql.org/wiki/Lock_Monitoring But it returned no locked tables. So what does this mean? Is the select query getting stuck? -- Si Chen Open Source Strategies, Inc. sic...@opensourcestrategies.com http://www.OpenSourceStrategies.com LinkedIn: http://www.linkedin.com/in/opentaps Twitter: http://twitter.com/opentaps
Re: [GENERAL] what does pg_activity mean when the database is stuck?
I didn't see any from the log. It was just a whole bunch of pretty standard looking SELECT queries. There were no INSERT/COMMIT statements which were still active before the SELECT's, just a few which are waiting after a lot of SELECT statements. Also, if the process just shows COMMIT, is there any way to see what it's trying to commit? On Wed, Jun 11, 2014 at 9:29 AM, Jeff Janes wrote: > On Wed, Jun 11, 2014 at 8:59 AM, Si Chen > wrote: > >> I have a problem where postgresql 9.3 got stuck, and the number of >> postgresql processes increased from about 15 to 225 in 10 minutes. >> >> I ran the query: >> select pid, query_start, waiting, state, query from pg_stat_activity >> order by query_start; >> >> But it showed mostly select statements -- all of them the same one, with >> a couple of joins. They are not in a waiting state but have been running >> for over 2 hours. >> >> I also checked for locks with the query on >> http://wiki.postgresql.org/wiki/Lock_Monitoring >> >> But it returned no locked tables. >> >> So what does this mean? Is the select query getting stuck? >> > > Do you have a huge chunk of newly insert, not yet committed, rows? This > sounds like the issue where all of the processes fight with each other over > the right to check uncommitted rows in order to verify that they are > actually uncommitted. > > Cheers, > > Jeff > -- Si Chen Open Source Strategies, Inc. sic...@opensourcestrategies.com http://www.OpenSourceStrategies.com LinkedIn: http://www.linkedin.com/in/opentaps Twitter: http://twitter.com/opentaps
Re: [GENERAL] what does pg_activity mean when the database is stuck?
The state is "idle". I don't have the state_change, but I will try to collect it if it happens again. On Wed, Jun 11, 2014 at 1:46 PM, Igor Neyman wrote: > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of Si Chen > Sent: Wednesday, June 11, 2014 4:34 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] what does pg_activity mean when the database is > stuck? > > I didn't see any from the log. It was just a whole bunch of pretty > standard looking SELECT queries. There were no INSERT/COMMIT statements > which were still active before the SELECT's, just a few which are waiting > after a lot of SELECT statements. > > Also, if the process just shows COMMIT, is there any way to see what it's > trying to commit? > > On Wed, Jun 11, 2014 at 9:29 AM, Jeff Janes wrote: > On Wed, Jun 11, 2014 at 8:59 AM, Si Chen > wrote: > I have a problem where postgresql 9.3 got stuck, and the number of > postgresql processes increased from about 15 to 225 in 10 minutes. > > I ran the query: > select pid, query_start, waiting, state, query from pg_stat_activity order > by query_start; > > But it showed mostly select statements -- all of them the same one, with a > couple of joins. They are not in a waiting state but have been running for > over 2 hours. > > I also checked for locks with the query on > http://wiki.postgresql.org/wiki/Lock_Monitoring > > But it returned no locked tables. > > So what does this mean? Is the select query getting stuck? > > Do you have a huge chunk of newly insert, not yet committed, rows? This > sounds like the issue where all of the processes fight with each other over > the right to check uncommitted rows in order to verify that they are > actually uncommitted. > > Cheers, > > Jeff > > > -- > Si Chen > Open Source Strategies, Inc. > sic...@opensourcestrategies.com > http://www.OpenSourceStrategies.com > LinkedIn: http://www.linkedin.com/in/opentaps > Twitter: http://twitter.com/opentaps > > > When you query pg_stat_activity, what do you see in state column, and how > state_change compares to query_start? > > Regards, > Igor Neyman > > -- Si Chen Open Source Strategies, Inc. sic...@opensourcestrategies.com http://www.OpenSourceStrategies.com LinkedIn: http://www.linkedin.com/in/opentaps Twitter: http://twitter.com/opentaps
Re: [GENERAL] what does pg_activity mean when the database is stuck?
Is there a way to configure postgresql to automatically release connections that have been idle for a set amount of time? On Wed, Jun 11, 2014 at 3:41 PM, Merlin Moncure wrote: > On Wed, Jun 11, 2014 at 5:37 PM, Si Chen > wrote: > > The state is "idle". I don't have the state_change, but I will try to > > collect it if it happens again. > > If they are idle, then the problem is probably with your application > -- you're grabbing new connections and not closing them or reusing > them. It's a very common problem. The 'query' when idle represents > the last query run -- the database finished it and is sitting around. > > merlin > -- Si Chen Open Source Strategies, Inc. sic...@opensourcestrategies.com http://www.OpenSourceStrategies.com LinkedIn: http://www.linkedin.com/in/opentaps Twitter: http://twitter.com/opentaps
Re: [GENERAL] what does pg_activity mean when the database is stuck?
PgBouncer looks pretty cool. Do you recommend using it with jdbc with about 50 - 100 normal connections? On Thu, Jun 12, 2014 at 6:35 AM, Igor Neyman wrote: > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of Si Chen > Sent: Wednesday, June 11, 2014 10:44 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] what does pg_activity mean when the database is > stuck? > > Is there a way to configure postgresql to automatically release > connections that have been idle for a set amount of time? > > On Wed, Jun 11, 2014 at 3:41 PM, Merlin Moncure > wrote: > On Wed, Jun 11, 2014 at 5:37 PM, Si Chen > wrote: > > The state is "idle". I don't have the state_change, but I will try to > > collect it if it happens again. > If they are idle, then the problem is probably with your application > -- you're grabbing new connections and not closing them or reusing > them. It's a very common problem. The 'query' when idle represents > the last query run -- the database finished it and is sitting around. > > merlin > > -- > Si Chen > Open Source Strategies, Inc. > sic...@opensourcestrategies.com > http://www.OpenSourceStrategies.com > LinkedIn: http://www.linkedin.com/in/opentaps > Twitter: http://twitter.com/opentaps > > > The best solution for this is to use some connection pooler, such as > PgBouncer. > B.t.w., PgBouncer can also disconnect idle client connections (if you > really wish) based on configuration setting. > > Regards, > Igor Neyman > > -- Si Chen Open Source Strategies, Inc. sic...@opensourcestrategies.com http://www.OpenSourceStrategies.com LinkedIn: http://www.linkedin.com/in/opentaps Twitter: http://twitter.com/opentaps
Re: [GENERAL] what does pg_activity mean when the database is stuck?
Yes, that would be nice, but it's a big app a lot of which we didn't write, etc.--you can imagine the rest ;) Is there even a way for postgreql to get me all the queries that has gone through a particular process, so we can see what the app was trying to do that left the connection hanging? On Thu, Jun 12, 2014 at 4:55 PM, John R Pierce wrote: > On 6/12/2014 4:24 PM, Si Chen wrote: > >> PgBouncer looks pretty cool. Do you recommend using it with jdbc with >> about 50 - 100 normal connections? >> > > Java has quite a few built in connection pooling options. > > they all work best if your software is configured to grab a connection, > use it for a transaction, then release it back to the pool. > > -- > john r pierce 37N 122W > somewhere on the middle of the left coast > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Si Chen Open Source Strategies, Inc. sic...@opensourcestrategies.com http://www.OpenSourceStrategies.com LinkedIn: http://www.linkedin.com/in/opentaps Twitter: http://twitter.com/opentaps
[GENERAL] is there a way log last query in pg_stat_activity
I'm using Postgresql 9.0, and my pg_stat_activity.query seems to always be showing when no query is being run. Is there a way for it to show the last query, even if it was run a while ago? -- Si Chen Open Source Strategies, Inc. twitter.com/opentaps Unify social, email, and business communications with opentaps CRM2 for Android <https://play.google.com/store/apps/details?id=com.opentaps.crm2client>, iPad/iPhone <https://itunes.apple.com/us/app/opentaps-crm2/id899333198>, Gmail <https://chrome.google.com/webstore/detail/opentaps-crm2/apkbgpfokhbplllnjkndenaopihfiaop> - It's Free!
[GENERAL] getting the current query from pg_stat_activity
Hello, I have two different postgresql servers running slightly versions. On one them, if I try to use pg_stat_activity to get the current queries, I get 1$ psql psql (9.0.13) Type "help" for help. postgres=# select procpid, query_start, waiting, current_query from pg_stat_activity; procpid | query_start | waiting | current_query -+---+-+ 673 | 2014-03-31 11:45:45.38988-07 | f | 855 | 2014-03-31 11:45:45.478935-07 | f | ... This agrees with the results of $ ps auxw | grep postgres postgres 673 0.3 0.3 243028 55348 ?Ss Mar30 2:25 postgres: pguser databasename 127.0.0.1(53931) idle postgres 855 0.3 0.3 243304 57584 ?Ss Mar30 2:49 postgres: pguser databasename 127.0.0.1(53981) idle which shows that the processes are idle. On the other one, though, $ psql psql (9.2.6) Type "help" for help. postgres=select pid, query_start, waiting, query from pg_stat_activity; # pid | query_start | waiting | query 12333 | 2014-03-31 14:32:30.810934-04 | f | SELECT... 12376 | 2014-03-31 14:48:08.338419-04 | f | COMMIT 12405 | 2014-03-31 14:52:22.903848-04 | f | COMMIT 12406 | 2014-03-31 14:32:48.150378-04 | f | SELECT which is strange, because the processes show they are idle, postgres 12333 0.0 1.8 3437696 279736 ? Ss 14:31 0:00 postgres: opentaps databasename 127.0.0.1(37969) idle postgres 12376 5.0 9.7 3473184 1491196 ? Ss 14:32 1:05 postgres: opentaps databasename 127.0.0.1(38025) idle postgres 12405 1.5 6.5 3467624 1007160 ? Ss 14:32 0:19 postgres: opentaps databasename 127.0.0.1(38085) idle postgres 12406 0.0 0.0 3432512 13024 ? Ss 14:32 0:00 postgres: opentaps databasename 127.0.0.1(38100) idle it seems that there is also a difference between the pg_stat_activity table of version 9.0.13: \d pg_stat_activity; View "pg_catalog.pg_stat_activity" Column | Type | Modifiers --+--+--- datid| oid | datname | name | procpid | integer | usesysid | oid | usename | name | application_name | text | client_addr | inet | client_port | integer | backend_start| timestamp with time zone | xact_start | timestamp with time zone | query_start | timestamp with time zone | waiting | boolean | current_query| text | vs 9.2.6: View "pg_catalog.pg_stat_activity" Column | Type | Modifiers --+--+--- datid| oid | datname | name | pid | integer | usesysid | oid | usename | name | application_name | text | client_addr | inet | client_hostname | text | client_port | integer | backend_start| timestamp with time zone | xact_start | timestamp with time zone | query_start | timestamp with time zone | state_change | timestamp with time zone | waiting | boolean | state| text | query| text | So which one is correct? Why does 9.0.13 show the processes as idle, and 9.2.6 show a query, even though the process shows them as idle? -- Si Chen Open Source Strategies, Inc. sic...@opensourcestrategies.com http://www.OpenSourceStrategies.com LinkedIn: http://www.linkedin.com/in/opentaps Twitter: http://twitter.com/opentaps
Re: [GENERAL] getting the current query from pg_stat_activity
Thanks! That's very helpful and answers my question. On Mon, Mar 31, 2014 at 12:52 PM, David Johnston wrote: > Si Chen-2 wrote > > I have two different postgresql servers running slightly [different] > > versions. > > Versions 9.0 and 9.2 are NOT slightly different. These are two MAJOR > RELEASES (which allow for API changes) apart (i.e., one major release in > between - 9.1) > > The release notes for 9.2 note this particular change explicitly: > > http://www.postgresql.org/docs/9.2/interactive/release-9-2.html > > Section E.9.2.6 > > Note the presence of the "state" column in the 9.2 schema - you use this to > determine if a connection is "idle" instead of looking for "" in a > query column which then allows the query column to be report the "last > known > query" at all times. > > David J. > > > > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/getting-the-current-query-from-pg-stat-activity-tp5798076p5798098.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Si Chen Open Source Strategies, Inc. sic...@opensourcestrategies.com http://www.OpenSourceStrategies.com LinkedIn: http://www.linkedin.com/in/opentaps Twitter: http://twitter.com/opentaps
[GENERAL] simple update query stuck
Hello, I'm using postgresql 9.0.13, and I have a simple query that seems to be stuck. I did a postgres=# select procpid, query_start, waiting, current_query from pg_stat_activity; procpid | query_start | waiting | current_query 32605 | 2014-04-01 12:39:46.957666-07 | t | UPDATE public.GL_ACCOUNT_ORGANIZATION SET ROLE_TYPE_ID=$1, FROM_DATE=$2, THRU_DATE=$3, POSTED_BALANCE=$4, LAST_UPDATED_STAMP=$5, LAST_UPDATED_TX_STAMP=$6, CREATED_STAMP=$7, CREATED_TX_STAMP=$8 WHERE GL_ACCOUNT _ID=$9 AND ORGANIZATION_PARTY_ID=$10 32685 | 2014-04-01 12:25:10.378481-07 | t | UPDATE public.GL_ACCOUNT_ORGANIZATION SET ROLE_TYPE_ID=$1, FROM_DATE=$2, THRU_DATE=$3, POSTED_BALANCE=$4, LAST_UPDATED_STAMP=$5, LAST_UPDATED_TX_STAMP=$6, CREATED_STAMP=$7, CREATED_TX_STAMP=$8 WHERE GL_ACCOUNT _ID=$9 AND ORGANIZATION_PARTY_ID=$10 As you can see this is a pretty simple update query, but it's been running 24 minutes in one thread and nearly 10 minutes in the other thread. What's also strange is it's not trigger a transaction timeout either. I've already re-started the database recently, and there's not too many threads executing -- just 38 threads total. Is it possible that the table is corrupted or needs repair? -- Si Chen Open Source Strategies, Inc. sic...@opensourcestrategies.com http://www.OpenSourceStrategies.com LinkedIn: http://www.linkedin.com/in/opentaps Twitter: http://twitter.com/opentaps
Re: [GENERAL] simple update query stuck
Thanks for writing back, but I don't think so. There's no message of a deadlock in the log, and the first query started at 12:25, the next one 12:31, 12:39, 12:50, 12:54, so there's plenty of time in between. On Tue, Apr 1, 2014 at 1:01 PM, Hoover, Jeffrey wrote: > Could they both be trying to update the same row, resulting in a deadlock? > > > > *From:* pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] *On Behalf Of *Si Chen > *Sent:* Tuesday, April 01, 2014 3:51 PM > *To:* pgsql-general@postgresql.org > *Subject:* [GENERAL] simple update query stuck > > > > Hello, > > > > I'm using postgresql 9.0.13, and I have a simple query that seems to be > stuck. I did a > > postgres=# select procpid, query_start, waiting, current_query from > pg_stat_activity; > > > > procpid | query_start | waiting | > > current_query > > > > >32605 | 2014-04-01 12:39:46.957666-07 | t | UPDATE > public.GL_ACCOUNT_ORGANIZATION SET ROLE_TYPE_ID=$1, FROM_DATE=$2, > THRU_DATE=$3, POSTED_BALANCE=$4, LAST_UPDATED_STAMP=$5, > LAST_UPDATED_TX_STAMP=$6, CREATED_STAMP=$7, CREATED_TX_STAMP=$8 WHERE > GL_ACCOUNT > > _ID=$9 AND ORGANIZATION_PARTY_ID=$10 > > > >32685 | 2014-04-01 12:25:10.378481-07 | t | UPDATE > public.GL_ACCOUNT_ORGANIZATION SET ROLE_TYPE_ID=$1, FROM_DATE=$2, > THRU_DATE=$3, POSTED_BALANCE=$4, LAST_UPDATED_STAMP=$5, > LAST_UPDATED_TX_STAMP=$6, CREATED_STAMP=$7, CREATED_TX_STAMP=$8 WHERE > GL_ACCOUNT > > _ID=$9 AND ORGANIZATION_PARTY_ID=$10 > > > > As you can see this is a pretty simple update query, but it's been running > 24 minutes in one thread and nearly 10 minutes in the other thread. What's > also strange is it's not trigger a transaction timeout either. > > > > I've already re-started the database recently, and there's not too many > threads executing -- just 38 threads total. Is it possible that the table > is corrupted or needs repair? > > > > -- > Si Chen > Open Source Strategies, Inc. > sic...@opensourcestrategies.com > http://www.OpenSourceStrategies.com > LinkedIn: http://www.linkedin.com/in/opentaps > Twitter: http://twitter.com/opentaps > -- Si Chen Open Source Strategies, Inc. sic...@opensourcestrategies.com http://www.OpenSourceStrategies.com LinkedIn: http://www.linkedin.com/in/opentaps Twitter: http://twitter.com/opentaps
Re: [GENERAL] simple update query stuck
You are right. That was the problem. I tried the query from http://wiki.postgresql.org/wiki/Lock_Monitoring and found a COMMIT transaction that was blocking it. I restarted postgresql again, and (it seems) everything went back to normal. Was there another way to unlock the table then? On Tue, Apr 1, 2014 at 1:10 PM, Igor Neyman wrote: > > > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of Si Chen > Sent: Tuesday, April 01, 2014 3:51 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] simple update query stuck > > Hello, > > I'm using postgresql 9.0.13, and I have a simple query that seems to be > stuck. I did a > postgres=# select procpid, query_start, waiting, current_query from > pg_stat_activity; > > procpid | query_start | waiting | > > current_query > > >32605 | 2014-04-01 12:39:46.957666-07 | t | UPDATE > public.GL_ACCOUNT_ORGANIZATION SET ROLE_TYPE_ID=$1, FROM_DATE=$2, > THRU_DATE=$3, POSTED_BALANCE=$4, LAST_UPDATED_STAMP=$5, > LAST_UPDATED_TX_STAMP=$6, CREATED_STAMP=$7, CREATED_TX_STAMP=$8 WHERE > GL_ACCOUNT > _ID=$9 AND ORGANIZATION_PARTY_ID=$10 > >32685 | 2014-04-01 12:25:10.378481-07 | t | UPDATE > public.GL_ACCOUNT_ORGANIZATION SET ROLE_TYPE_ID=$1, FROM_DATE=$2, > THRU_DATE=$3, POSTED_BALANCE=$4, LAST_UPDATED_STAMP=$5, > LAST_UPDATED_TX_STAMP=$6, CREATED_STAMP=$7, CREATED_TX_STAMP=$8 WHERE > GL_ACCOUNT > _ID=$9 AND ORGANIZATION_PARTY_ID=$10 > > As you can see this is a pretty simple update query, but it's been running > 24 minutes in one thread and nearly 10 minutes in the other thread. What's > also strange is it's not trigger a transaction timeout either. > > I've already re-started the database recently, and there's not too many > threads executing -- just 38 threads total. Is it possible that the table > is corrupted or needs repair? > > -- > Si Chen > Open Source Strategies, Inc. > sic...@opensourcestrategies.com > http://www.OpenSourceStrategies.com > LinkedIn: http://www.linkedin.com/in/opentaps > Twitter: http://twitter.com/opentaps > > Both queries are "waiting". Your table must be locked. > Check pg_locks. > > Regards, > Igor Neyman > -- Si Chen Open Source Strategies, Inc. sic...@opensourcestrategies.com http://www.OpenSourceStrategies.com LinkedIn: http://www.linkedin.com/in/opentaps Twitter: http://twitter.com/opentaps
Re: [GENERAL] simple update query stuck
Ok, thanks. I'll keep that in mind. On Tue, Apr 1, 2014 at 7:45 PM, Andrew Sullivan wrote: > On Tue, Apr 01, 2014 at 07:00:16PM -0400, Tom Lane wrote: > > > one of the clients, in a way that isn't visible to the deadlock detector. > > One way for that to happen without any external interconnections is if > the > > client is waiting for a NOTIFY that will never arrive because the > would-be > > sender is blocked. > > I bet the case I was thinking of was the NOTIFY example. That never > occurred to me as an explanation, but now that you mention it, it > seems quite likely to me. > > More generally (and for the OP's problem), my experience is that lots > of updates against the same rows in an unpredictable order is an > excellent way to run into trouble, and long-running transactions are a > major source of these problems. Without a more detailed report about > what is going on in the present case, I don't think it's going to be > possible to diagnose better than has been done. > > A > > -- > Andrew Sullivan > a...@crankycanuck.ca > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Si Chen Open Source Strategies, Inc. sic...@opensourcestrategies.com http://www.OpenSourceStrategies.com LinkedIn: http://www.linkedin.com/in/opentaps Twitter: http://twitter.com/opentaps
[GENERAL] postgresql 8.0 advantages
Hello. I read the PostgreSQL 8.0 "What's New" page (http://www.postgresql.org/docs/whatsnew) and wasn't sure whether version 8.0 is significantly faster, more scalability, or more stable than versions 7.4? I remember big speed improvements between 7.3 and 7.4. It seems the biggest advantage of version 8.0 is being able to run in Windows. Is that true? Thanks, Si Chen ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] postgresql 8.0 advantages
Thanks! Is there any documentation on how to upgrade to 8.0? Is it possible to upgrade from 7.4 to 8.0 while keeping a production database running "in place"? Or should I install 8.0 in another directory/machine and then restore the database into it? Are there any incompatibilities/modifications to databases from the earlier veresion required? (I am running RHEL3.) Si Chen Tom Lane wrote: Si Chen <[EMAIL PROTECTED]> writes: I read the PostgreSQL 8.0 "What's New" page (http://www.postgresql.org/docs/whatsnew) and wasn't sure whether version 8.0 is significantly faster, more scalability, or more stable than versions 7.4? I remember big speed improvements between 7.3 and 7.4. It seems the biggest advantage of version 8.0 is being able to run in Windows. There are some speed improvements in 8.0 too, though that wasn't the main focus of the release cycle. For instance, Lonni Friedman's nearby thread reports on a case where 8.0 consistently finds a much better query plan for a complex query than prior releases did, because we fixed some issues in the GEQO planner module. 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])
[GENERAL] postgresql 8.0 on windows 2003 server
Hello everyone. Thanks for the answers earlier about the new 8.0 version. We have a client who is thinking about putting postgresql 8.0 on Windows 2003 Server, but he is concerned because this is the first version to run natively on windows. Are there any issues with 8.0 on Windows? Also, with Linux it was possible to improve performance by increasing the amount of shared memory in /etc/shmmax (http://www.phpbuilder.com/columns/smith20010821.php3). Does this need to be done in Windows? Thanks! Si Chen ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] survey of the postgresql communiity
Hi everyone. My company (opensourcestrategies.com) is conducting a study of the PostgreSQL community to promote better understanding of open source software. We're trying to help people better understand: 1. the motivations behind contributing to open source software; and 2. dynamics between developers and users in a large, successful open source project. As part of our study, we'd like to ask you to take a short (under 5 minute) and anonymous survey at http://www.opensourcestrategies.com/pgsurvey/control/main. Your answers will help promote better understanding of open source software in general and the PostgreSQL project in particular. Also, if you belong to other open source mailing lists with PostgreSQL users, please help us by re-posting this message on those lists as well, respecting, of course, their list posting etiquette. If you have any questions or comments about the survey, please email me at [EMAIL PROTECTED] Thank you! Si Chen ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] how to release a transaction lock on a table?
Hello everyone. I am trying to do an ALTER TABLE, but it hangs indefinitely. I think the table is locked from a transaction, and in pg_locks I found: relation 75907 database 74861 pid 29604 mode AccessExclusiveLock granted f Is there a way to release this lock? Or does the database need to re-started? Si ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] how to release a transaction lock on a table?
Thanks, Michael, for the input. Is there anyway in PostgreSQL to force some transactions to close (rollback if necessary)? I notice there is no way to release a LOCK manually. Si Michael Fuhr wrote: On Mon, Jan 31, 2005 at 10:13:26AM -0800, Si Chen wrote: Hello everyone. I am trying to do an ALTER TABLE, but it hangs indefinitely. I think the table is locked from a transaction, and in pg_locks I found: relation 75907 database 74861 pid 29604 mode AccessExclusiveLock granted f Look for other processes that have a lock on the table: SELECT * FROM pg_locks WHERE relation = 75907; If you have stats_command_string turned on then you can query pg_stat_activity to see what the other processes are doing. Is there a way to release this lock? Or does the database need to re-started? Before taking drastic steps like restarting the database, find out who holds the conflicting lock and why. You might have an application sitting idle in a transaction that it should be committing or rolling back, in which case the application should probably be fixed. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] how to release a transaction lock on a table?
Michael, I would like to track down what in the application is causing the deadlock, but it's a bit hard since it's a big app with lots going on. I can track down the PID of the transaction which is locking the tables, but is there anyway to go from the PID back to the SQL statement(s) in the transaction? Thanks, Si Michael Fuhr wrote: On Mon, Jan 31, 2005 at 11:43:45AM -0800, Si Chen wrote: Thanks, Michael, for the input. Is there anyway in PostgreSQL to force some transactions to close (rollback if necessary)? I notice there is no way to release a LOCK manually. I'm not aware of a way to terminate a transaction in another session short of killing its backend process. PostgreSQL 8.0 has a pg_cancel_backend() function, but it cancels only a backend's current query, not any outer transaction it might be part of. Have you discovered what's holding the conflicting lock? It would be prudent to understand what's happening before killing one transaction just so another can succeed. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] how to release a transaction lock on a table?
Hi. Sorry about the confusion in terminology. You are right. The transactions are idle--when I do a "ps auxw" on my database server, I see "idle in transaction". Is this what you meant, and would the steps you talked about with pg_stat_activity help me track down the transactions that are idle? What's strange is that usually this does not cause problems. It is just occasionally that I am unable to gain access to a table. Unfortunately that also makes it hard to track down the source of the problem. Thanks for all your help so far--really appreciate it. Si Michael Fuhr wrote: On Tue, Feb 01, 2005 at 10:53:11AM -0800, Si Chen wrote: I would like to track down what in the application is causing the deadlock, Are you sure you understand what "deadlock" means? Deadlock occurs, for example, when connection A holds a lock that connection B wants and connection B holds a lock that connection A wants. PostgreSQL should recognize that situation and cause one of the connections to fail after a timeout (one second by default). That doesn't sound like what you're experiencing -- based on what you've said, one connection holds a lock and another is blocked waiting for it. but it's a bit hard since it's a big app with lots going on. I can track down the PID of the transaction which is locking the tables, but is there anyway to go from the PID back to the SQL statement(s) in the transaction? The query "SELECT * FROM pg_stat_activity" should show connections' current queries if you have stats_command_string set to "on". If stats_command_string is "off" then you can enable it by editing postgresql.conf and restarting the postmaster, but unfortunately that won't help you track down queries that are already running. Is it possible that the transaction holding the lock is idle? Some applications use long-lived transactions that can cause locking problems in other transactions. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] replication
Hello everyone. Are there any recommended ways for doing postgresql replication? Si Chen ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
