Обсуждение: psql seems to hang during delete query
i am using postgresql 8.0.0
my disk is 100% full
i have a database called x
i do the following
psql x
delete from table;
nothing happens
i try to see the top output
i see the following
1768 sdc 25 0 41860 32m 30m R 99.7 0.8 11:29.35 postmaster
i also saw this appearing
i also saw this appearing
2525 root 16 0 7296 720 620 S 0.3 0.0 0:07.62 hald-addon-stor
its been almost half an hour
and the database is still in the same state. (all the rows still exist)
what is happening?
hasnt it started deleteing yet?
postgreslog dont say anything.
what can be done in such a case?
thanks,
regards
Surabhi
You can't delete from a full disk :)
No, really: database systems need space for logging, WAL, whatever.
And even with "delete" PostgreSQL will free no single byte, as deleting is done with marking the row as deleted. Read up on multi version concurrency why this has to be that way.
To reclaim space: vacuum freeze
So, to get space, you have to have space first. Delete something else.
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
on different matter:
EuroPython 2006 is over. It was a GREAT conference. If you missed it, now you can prepare budget for visiting EuroPython 2007.
No, really: database systems need space for logging, WAL, whatever.
And even with "delete" PostgreSQL will free no single byte, as deleting is done with marking the row as deleted. Read up on multi version concurrency why this has to be that way.
To reclaim space: vacuum freeze
So, to get space, you have to have space first. Delete something else.
Harald
On 7/20/06, surabhi.ahuja <surabhi.ahuja@iiitb.ac.in> wrote:
i am using postgresql 8.0.0my disk is 100% fulli have a database called xi do the followingpsql xdelete from table;nothing happensi try to see the top outputi see the following1768 sdc 25 0 41860 32m 30m R 99.7 0.8 11:29.35 postmaster
i also saw this appearing2525 root 16 0 7296 720 620 S 0.3 0.0 0:07.62 hald-addon-storits been almost half an hourand the database is still in the same state. (all the rows still exist)what is happening?hasnt it started deleteing yet?postgreslog dont say anything.what can be done in such a case?thanks,regardsSurabhi
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
on different matter:
EuroPython 2006 is over. It was a GREAT conference. If you missed it, now you can prepare budget for visiting EuroPython 2007.
so how much space should be free so that i am able to delete
i have a hard disk of size 130 G.
thanks,
regards
Surabhi
From: Harald Armin Massa [mailto:haraldarminmassa@gmail.com]
Sent: Thu 7/20/2006 12:16 PM
To: surabhi.ahuja
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] psql seems to hang during delete query
*********************** Your mail has been scanned by InterScan VirusWall. ***********-*********** |
No, really: database systems need space for logging, WAL, whatever.
And even with "delete" PostgreSQL will free no single byte, as deleting is done with marking the row as deleted. Read up on multi version concurrency why this has to be that way.
To reclaim space: vacuum freeze
So, to get space, you have to have space first. Delete something else.
Harald
On 7/20/06, surabhi.ahuja <surabhi.ahuja@iiitb.ac.in> wrote:
i am using postgresql 8.0.0my disk is 100% fulli have a database called xi do the followingpsql xdelete from table;nothing happensi try to see the top outputi see the following1768 sdc 25 0 41860 32m 30m R 99.7 0.8 11:29.35 postmaster
i also saw this appearing2525 root 16 0 7296 720 620 S 0.3 0.0 0:07.62 hald-addon-storits been almost half an hourand the database is still in the same state. (all the rows still exist)what is happening?hasnt it started deleteing yet?postgreslog dont say anything.what can be done in such a case?thanks,regardsSurabhi
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
on different matter:
EuroPython 2006 is over. It was a GREAT conference. If you missed it, now you can prepare budget for visiting EuroPython 2007.
On Thu, 2006-07-20 at 12:48 +0530, surabhi.ahuja wrote: > so how much space should be free so that i am able to delete > > i have a hard disk of size 130 G. > > > thanks, > regards > Surabhi > > > ______________________________________________________________________ If you are trying to delete the entire contents of a table (and are not using slony replication or similar) then a simple TRUNCATE <tablename> will achieve the desired result and free up some diskspace. Alternatively, if you have indexes on the table, you could try to drop the indexes (if they are bloated, you can probably regain some diskspace that way). Delete the rows you need and then create the indexes anew. Sven