By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,903 Members | 1,104 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,903 IT Pros & Developers. It's quick & easy.

PostgreSQL seems to wait and block all the queries

P: n/a
HM
Hello !

-----------------------------------------------------------------------------
I would like to know why the database seems frozen.

- psql can access to database on the database server
- There are no error logs. There are only normal logs
- pgadmin cannot connect to the database.
- the webserver cannot execute any script
- other PHP scripts are waiting.

A lock seems to block all the database.
I've left the database as it is. I installed the database on an other
server to check the hardware of the old server.

IN ADVANCE THANK YOU !
-------------------------------------------------------------------------------

PostgreSQL 7.4.5 with "connect by" patch
Debian linux woody

The database is waiting and seems frozen.

1790 ? S 0:00 postgres: database database 192.168.1.8
SELECT
1791 ? S 0:00 postgres: database database 192.168.1.8
SELECT
1794 ? S 0:00 postgres: database database 192.168.1.8
SELECT
5559 ? S 0:00 postgres: database database 192.168.1.8
SELECT waiting
5980 ? S 0:00 postgres: database database 192.168.1.8
SELECT waiting
13449 ? S 0:00 postgres: database database 192.168.1.8
SELECT
18255 ? S 0:00 postgres: database database 192.168.1.8
SELECT waiting
18313 ? S 0:00 postgres: database database 192.168.1.8
SELECT waiting
29331 ? S 0:00 /USR/SBIN/CRON
29334 ? S 0:00 /bin/sh /script_vacuum
29337 ? S 0:00 /usr/sbin/sendmail -i -FCronDaemon -odi
-oem postgres
29339 ? S 0:00 /usr/bin/vacuumdb -z -f -v -U postgres
database
29340 ? S 0:00 /usr/sbin/postdrop -r
29341 ? S 0:00 postgres: postgres database [local] VACUUM
waiting
29350 ? S 0:00 postgres: database database 192.168.1.8
SELECT waiting
29358 ? S 0:00 postgres: database database 192.168.1.8
SELECT waiting
29362 ? S 0:00 postgres: database database 192.168.1.8
SELECT waiting

.... A lot of SELECT OR UPDATE waiting ...

Here is the result of select * from pg_stat_activity

datid | datname | procpid | usesysid | usename | current_query |
query_start
----------+----------+---------+----------+----------+----------------------------------------------------------------------
12777577 | database | 1790 | 100 | database | SELECT
price(ID) AS price FROM PRODUCTS WHERE ID=1 | 2004-10-05
18:00:55.032409+02
12777577 | database | 1791 | 100 | database | SELECT
price(ID) AS price FROM PRODUCTS WHERE ID=1 | 2004-10-05
18:00:56.185738+02
12777577 | database | 1794 | 100 | database | SELECT
price(ID) AS price FROM PRODUCTS WHERE ID=1 | 2004-10-05
18:01:01.294875+02
12777577 | database | 5559 | 100 | database | SELECT
price(ID) AS price FROM PRODUCTS WHERE ID=1 | 2004-10-05
18:47:38.033507+02
12777577 | database | 5980 | 100 | database | SELECT
price(ID) AS price FROM PRODUCTS WHERE ID=1 | 2004-10-05
18:52:08.089988+02
12777577 | database | 13449 | 100 | database | SELECT
price(ID) AS price FROM PRODUCTS WHERE ID=1 | 2004-10-05
20:51:12.717093+02
12777577 | database | 18255 | 100 | database | SELECT
price(ID) AS price FROM PRODUCTS WHERE ID=1 | 2004-10-05
22:08:17.587026+02
12777577 | database | 18313 | 100 | database | SELECT
price(ID) AS price FROM PRODUCTS WHERE ID=1 | 2004-10-05
22:09:07.963451+02
12777577 | database | 29350 | 100 | database | SELECT
price(ID) AS price FROM PRODUCTS WHERE ID=1 | 2004-10-06
04:15:09.556203+02
12777577 | database | 29358 | 100 | database | SELECT
price(ID) AS price FROM PRODUCTS WHERE ID=1 | 2004-10-06
04:15:22.681887+02
12777577 | database | 29362 | 100 | database | SELECT
price(ID) AS price FROM PRODUCTS WHERE ID=1 | 2004-10-06
04:15:27.485337+02

The problem happened at 04:15 AM during a vacuum FULL and could occurs
at every moment

Here is the result of select * from pglocks

relation | database | transaction | pid | mode |
granted
----------+----------+-------------+-------+------------------+---------
12777689 | 12777577 | | 1790 | AccessShareLock | t
20633160 | 12777577 | | 1790 | AccessShareLock | t
12777668 | 12777577 | | 1790 | AccessShareLock | t
| | 229273687 | 1790 | ExclusiveLock | t
12777773 | 12777577 | | 1790 | AccessShareLock | t
12777773 | 12777577 | | 1790 | RowExclusiveLock | t
12777689 | 12777577 | | 1791 | AccessShareLock | t
12777668 | 12777577 | | 1791 | AccessShareLock | t
20633160 | 12777577 | | 1791 | AccessShareLock | t
12777773 | 12777577 | | 1791 | AccessShareLock | t
12777773 | 12777577 | | 1791 | RowExclusiveLock | t
| | 229273691 | 1791 | ExclusiveLock | t
| | 229273699 | 1794 | ExclusiveLock | t
12777773 | 12777577 | | 1794 | AccessShareLock | t
12777773 | 12777577 | | 1794 | RowExclusiveLock | t
20633160 | 12777577 | | 1794 | AccessShareLock | t
12777689 | 12777577 | | 1794 | AccessShareLock | t
12777668 | 12777577 | | 1794 | AccessShareLock | t
12777689 | 12777577 | | 13449 | AccessShareLock | t
12777773 | 12777577 | | 13449 | AccessShareLock | t
12777773 | 12777577 | | 13449 | RowExclusiveLock | t
12777668 | 12777577 | | 13449 | AccessShareLock | t
| | 229354045 | 13449 | ExclusiveLock | t
20633160 | 12777577 | | 13449 | AccessShareLock | t
I would like to know if these functions and queries could cause
problems to postgresql.

This is a SELECT query of a field which is updated by a function in
the same query.

Example :

SELECT price(ID) AS price FROM PRODUCTS WHERE ID=1

function price(ID) - PL/SQL Function
{
IF TEST THEN RETURN get_price(ID)
}

function get_price(ID) - PL/Python
{
exec \'from urllib2 import *\' in globals(), locals()
url = "http://www.toto.com"
fichier = urlopen(url)

--
-- PARSE XML RETURNED BY toto.com
-- value = arbre.getElementsByTagName("test")[0].firstChild.data
--

UPDATE PRODUCTS SET price = value WHERE id=1

RETURN Value
}
Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
HM wrote:
I would like to know why the database seems frozen.
...
The problem happened at 04:15 AM during a vacuum FULL and could occurs
at every moment


VACUUM FULL locks your tables.
Nov 23 '05 #2

P: n/a
HM
Hello !

Thank you for your answer.

VACUUM FULL maybe lock tables but don't release them. It seems waits
an the release of an other lock in the database "database".
The database still frozen at this moment (2 days).

Here is a sample of VACUUM log
1 INFO: vacuuming "information_schema.sql_features"
2 INFO: "sql_features": found 0 removable, 360 nonremovable row
versions in 5 pages
3 DETAIL: 0 dead row versions cannot be removed yet.
4 Nonremovable row versions range from 68 to 208 bytes long.

....

796 INFO: index "pg_toast_16384_index" now contains 0 row versions in
1 pages
797 DETAIL: 0 index pages have been deleted, 0 are currently
reusable.
798 CPU 0.00s/0.00u sec elapsed 0.00 sec.
799 INFO: analyzing "pg_catalog.pg_attrdef"
800 INFO: "pg_attrdef": 4 pages, 63 rows sampled, 63 estimated total
rows

no more log from VACUUM

In the normal operation vaccum logs 2700 lines including vacuum log of
the database "database".

Bye

HM
William Yu <wy*@talisys.com> wrote in message news:<ck**********@news.hub.org>...
HM wrote:
I would like to know why the database seems frozen.
> ...
The problem happened at 04:15 AM during a vacuum FULL and could occurs
at every moment


VACUUM FULL locks your tables.

Nov 23 '05 #3

P: n/a
HM
Hello !

Thank you for your answer.

VACUUM FULL maybe lock tables but don't release them. It seems waits
an the release of an other lock in the database "database".
The database still frozen at this moment (2 days).

Here is a sample of VACUUM log
1 INFO: vacuuming "information_schema.sql_features"
2 INFO: "sql_features": found 0 removable, 360 nonremovable row
versions in 5 pages
3 DETAIL: 0 dead row versions cannot be removed yet.
4 Nonremovable row versions range from 68 to 208 bytes long.

....

796 INFO: index "pg_toast_16384_index" now contains 0 row versions in
1 pages
797 DETAIL: 0 index pages have been deleted, 0 are currently
reusable.
798 CPU 0.00s/0.00u sec elapsed 0.00 sec.
799 INFO: analyzing "pg_catalog.pg_attrdef"
800 INFO: "pg_attrdef": 4 pages, 63 rows sampled, 63 estimated total
rows

no more log from VACUUM

In the normal operation vaccum logs 2700 lines including vacuum log of
the database "database".

Bye

HM
William Yu <wy*@talisys.com> wrote in message news:<ck**********@news.hub.org>...
HM wrote:
I would like to know why the database seems frozen.
> ...
The problem happened at 04:15 AM during a vacuum FULL and could occurs
at every moment


VACUUM FULL locks your tables.

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.