Delete Problem

On Thu, Jul 13, 2006 at 01:45:36PM +1000, Jamie Deppeler wrote:

Just today i have noticed i have one certain table that i cannot delete
any records from

What happens when you try to delete? Do you get an error? Does
the delete succeed but report zero rows deleted? Something else?
What does "EXPLAIN ANALYZE DELETE ..." show? Is the delete done
in a transaction that doesn't commit?

--
Michael Fuhr

[Please copy the mailing list on replies so others can participate
in and learn from the discussion.]

On Thu, Jul 13, 2006 at 03:32:43PM +1000, Jamie Deppeler wrote:

Michael Fuhr wrote:

On Thu, Jul 13, 2006 at 01:45:36PM +1000, Jamie Deppeler wrote:

Just today i have noticed i have one certain table that i cannot delete
any records from

What happens when you try to delete? Do you get an error? Does
the delete succeed but report zero rows deleted? Something else?
What does "EXPLAIN ANALYZE DELETE ..." show? Is the delete done
in a transaction that doesn't commit?

Delete is performed without any errors or warnings but when i refresh
the found set it is still in the table

What do you mean by "refresh the found set"? What client interface
are you using? Might the deleting transaction not be committing?
What happens if you do the delete using psql? What version of
PostgreSQL are you running?

--
Michael Fuhr

Michael Fuhr wrote:

[Please copy the mailing list on replies so others can participate
in and learn from the discussion.]

On Thu, Jul 13, 2006 at 03:32:43PM +1000, Jamie Deppeler wrote:

Michael Fuhr wrote:

On Thu, Jul 13, 2006 at 01:45:36PM +1000, Jamie Deppeler wrote:

Just today i have noticed i have one certain table that i cannot delete
any records from

What happens when you try to delete? Do you get an error? Does
the delete succeed but report zero rows deleted? Something else?
What does "EXPLAIN ANALYZE DELETE ..." show? Is the delete done
in a transaction that doesn't commit?

Delete is performed without any errors or warnings but when i refresh
the found set it is still in the table

What do you mean by "refresh the found set"? What client interface
are you using? Might the deleting transaction not be committing?
What happens if you do the delete using psql? What version of
PostgreSQL are you running?

From psql

db=# select "primary" from job where "primary" = 370;
primary
---------
370
(1 row)

db=# delete from job where "primary" = 370;
DELETE 0

DB is Postgresql 8.0

OS is Debian

even i have seen this problem

i am using postgres 8.0.0

i open psql <dbname>

and there i try to do
delete from <tab1>

it seems that psql gets stuck.

even after 5 mins or something, no deletion happens.

generally this happens when the disk is nearing to full

cant this be avoided, why does postgres hang.

thanks,
regards
Surabhi

________________________________

From: pgsql-general-owner@postgresql.org on behalf of Michael Fuhr
Sent: Thu 7/13/2006 11:48 AM
To: Jamie Deppeler
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Delete Problem

***********************
Your mail has been scanned by InterScan VirusWall.
***********-***********

[Please copy the mailing list on replies so others can participate
in and learn from the discussion.]

On Thu, Jul 13, 2006 at 03:32:43PM +1000, Jamie Deppeler wrote:

Michael Fuhr wrote:

On Thu, Jul 13, 2006 at 01:45:36PM +1000, Jamie Deppeler wrote:

Just today i have noticed i have one certain table that i cannot delete
any records from

What happens when you try to delete? Do you get an error? Does
the delete succeed but report zero rows deleted? Something else?
What does "EXPLAIN ANALYZE DELETE ..." show? Is the delete done
in a transaction that doesn't commit?

Delete is performed without any errors or warnings but when i refresh
the found set it is still in the table

What do you mean by "refresh the found set"? What client interface
are you using? Might the deleting transaction not be committing?
What happens if you do the delete using psql? What version of
PostgreSQL are you running?

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Not sure if it disk space as i have around 10gig free

surabhi.ahuja wrote:

On Thu, Jul 13, 2006 at 04:28:50PM +1000, Jamie Deppeler wrote:

From psql

db=# select "primary" from job where "primary" = 370;
primary
---------
370
(1 row)

db=# delete from job where "primary" = 370;
DELETE 0

You said that you had deleted all triggers -- have you verified
that none remain? Does the table have any rules? What does
"\d job" show?

--
Michael Fuhr

db=# \d job
Table "job"
Column | Type
| Modifiers
---------------------+-----------------------------+----------------------------------------------------------------
primary | integer | not null default
nextval('job_primary_seq'::regclass)
jobnumber | text |
jobname | text |
jobdetails | text |
Indexes:
"job_pkey" PRIMARY KEY, btree ("primary")

Michael Fuhr wrote:

On Thu, Jul 13, 2006 at 04:58:26PM +1000, Jamie Deppeler wrote:

db=# \d job
Table "job"
Column | Type
| Modifiers
---------------------+-----------------------------+----------------------------------------------------------------
primary | integer | not null default
nextval('job_primary_seq'::regclass)
jobnumber | text |
jobname | text |
jobdetails | text |
Indexes:
"job_pkey" PRIMARY KEY, btree ("primary")

You said you were running 8.0 but "nextval('job_primary_seq'::regclass)"
suggests 8.1. What does "SELECT version()" show?

What are the output of the following?

EXPLAIN ANALYZE SELECT "primary" FROM job WHERE "primary" = 370;
EXPLAIN ANALYZE DELETE FROM job WHERE "primary" = 370;

If these commands use index or bitmap index scans, do you get
different results if you execute the following commands and then
try the delete again?

SET enable_indexscan TO off;
SET enable_bitmapscan TO off;

--
Michael Fuhr

PostgreSQL 8.1.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3
20060104 (prerelease) (Debian 4.0.2-6)

Michael Fuhr wrote:

On Thu, Jul 13, 2006 at 04:58:26PM +1000, Jamie Deppeler wrote:

db=# \d job
Table "job"
Column | Type
| Modifiers
---------------------+-----------------------------+----------------------------------------------------------------
primary | integer | not null default
nextval('job_primary_seq'::regclass)
jobnumber | text |
jobname | text |
jobdetails | text |
Indexes:
"job_pkey" PRIMARY KEY, btree ("primary")

You said you were running 8.0 but "nextval('job_primary_seq'::regclass)"
suggests 8.1. What does "SELECT version()" show?

What are the output of the following?

EXPLAIN ANALYZE SELECT "primary" FROM job WHERE "primary" = 370;
EXPLAIN ANALYZE DELETE FROM job WHERE "primary" = 370;

If these commands use index or bitmap index scans, do you get
different results if you execute the following commands and then
try the delete again?

SET enable_indexscan TO off;
SET enable_bitmapscan TO off;

--

*Jamie Deppeler
*Database Administrator

*once:technologies pty ltd
*
*Do It Once!*

46 Roseneath Street
North Geelong
Victoria 3215 Australia

Ph: +61 3 5278 6699
Fax: +61 3 5278 6166
Email: jamie@doitonce.net.au
Web: http://www.oncefabrik.com <http://www.oncefabrik.com/&gt;

On Thu, Jul 13, 2006 at 05:26:31PM +1000, Jamie Deppeler wrote:

PostgreSQL 8.1.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3
20060104 (prerelease) (Debian 4.0.2-6)

What about the other outputs I mentioned?

--
Michael Fuhr

On Thu, 2006-07-13 at 01:41, surabhi.ahuja wrote:

even i have seen this problem

i am using postgres 8.0.0

i open psql <dbname>

and there i try to do
delete from <tab1>

it seems that psql gets stuck.

even after 5 mins or something, no deletion happens.

generally this happens when the disk is nearing to full

cant this be avoided, why does postgres hang.

5 minutes does not a hang make. :) It's likely that postgresql hasn't
hung, but is just taking a really long time to complete your request.
Note that the other user got a response back, it just said zero rows
deleted. Which points to index corruption. Which points to broken
hardware. Hardware which should be tested thoroughly to identify any
problem areas, like bad RAM, CPU, hard drive, cabling, power supply, or
misconfigured memory timings et. al.

Your problem is likely a bit different. When the machine just sits
there for 5 or more minutes, the CPUs or hard drives are likely still
busy.

What do you postgresql logs say, if anything?

Some other points:

1: upgrade your postgresql version. The developers work hard for your
benefit. 8.0.8 or so is out. There's LOTS of bug fixes between 8.0.0
and 8.0.8, and if you're running into a bug on 8.0.0 you're wasting your
time trying to figure it out if the fix is already in.

2: Are you running analyze and vacuum regularly? If your disk is often
nearing full but your actual data set isn't that large, that points to a
lack of vacuuming. check your fsm settings as well.