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_activit y
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 | RowExclusiveLoc k | t
12777689 | 12777577 | | 1791 | AccessShareLock | t
12777668 | 12777577 | | 1791 | AccessShareLock | t
20633160 | 12777577 | | 1791 | AccessShareLock | t
12777773 | 12777577 | | 1791 | AccessShareLock | t
12777773 | 12777577 | | 1791 | RowExclusiveLoc k | t
| | 229273691 | 1791 | ExclusiveLock | t
| | 229273699 | 1794 | ExclusiveLock | t
12777773 | 12777577 | | 1794 | AccessShareLock | t
12777773 | 12777577 | | 1794 | RowExclusiveLoc k | 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 | RowExclusiveLoc k | 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.getElemen tsByTagName("te st")[0].firstChild.dat a
--
UPDATE PRODUCTS SET price = value WHERE id=1
RETURN Value
}
