Thread: Delete Problem
Just today i have noticed i have one certain table that i cannot delete any records from 1 have delete all Triggers, Vacuumed the tables removed all foreign keys that linked to this table. -- *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/>
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
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: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?
>
>
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
Not sure if it disk space as i have around 10gig free 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. > > 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 > > ------------------------------------------------------------------------ > > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.394 / Virus Database: 268.9.10/387 - Release Date: 12/07/2006 >
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
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/>
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.