need help

Jenny schrieb:

I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2). I've been
dealing with Psql for over than 2 years now, but I've never had this case
before.

I have a table that has about 20 rows in it.

Table "public.s_apotik"
Column | Type | Modifiers
-------------------+------------------------------+------------------
obat_id | character varying(10) | not null
stock | numeric | not null
s_min | numeric | not null
s_jual | numeric |
s_r_jual | numeric |
s_order | numeric |
s_r_order | numeric |
s_bs | numeric |
last_receive | timestamp without time zone |
Indexes:
"s_apotik_pkey" PRIMARY KEY, btree(obat_id)

When I try to UPDATE one of the row, nothing happens for a very long time.
First, I run it on PgAdminIII, I can see the miliseconds are growing as I
waited. Then I stop the query, because the time needed for it is unbelievably
wrong.

Then I try to run the query from the psql shell. For example, the table has
obat_id : A, B, C, D.
db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='A';
(.... nothing happens.. I press the Ctrl-C to stop it. This is what comes out
:)
Cancel request sent
ERROR: canceling query due to user request

(If I try another obat_id)
db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='B';
(Less than a second, this is what comes out :)
UPDATE 1

I can't do anything to that row. I can't DELETE it. Can't DROP the table.
I want this data out of my database.
What should I do? It's like there's a falsely pointed index here.
Any help would be very much appreciated.

1) lets hope you do regulary backups - and actually tested restore.
1a) if not, do it right now
2) reindex the table
3) try again to modify

Q: are there any foreign keys involved? If so, reindex those
tables too, just in case.

did you vacuum regulary?

HTH
Tino

Jenny wrote:

I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2). I've been
dealing with Psql for over than 2 years now, but I've never had this case
before.

Then I try to run the query from the psql shell. For example, the table has
obat_id : A, B, C, D.
db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='A';
(.... nothing happens.. I press the Ctrl-C to stop it. This is what comes out
:)
Cancel request sent
ERROR: canceling query due to user request

(If I try another obat_id)
db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='B';
(Less than a second, this is what comes out :)
UPDATE 1

It could well be another client has a lock on that record, for example
by doing a SELECT FOR UPDATE w/o a NOWAIT.

You can verify by querying pg_locks. IIRC you can also see what query
caused the lock by joining against some other system table, but the
details escape me atm (check the archives, I learned that by following
this list).

If it's indeed a locked record, the process causing the lock is listed.
Either kill it or call it's owner back from his/her coffee break ;)

I doubt it's anything serious.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

//Showing your Vision to the World//

Try to execute your query (in psql) with prefixing by EXPLAIN ANALYZE and
send us the result
db=# EXPLAIN ANALYZE UPDATE s_apotik SET stock = 100 WHERE obat_id='A';

regards

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tino Wildenhain
Sent: mardi 6 décembre 2005 09:55
To: Jenny
Cc: pgsql-general@postgresql.org; pgsql-sql@postgresql.org;
pgsql-performance@postgresql.org
Subject: Re: [PERFORM] [GENERAL] need help

Jenny schrieb:

I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2).
I've been dealing with Psql for over than 2 years now, but I've never
had this case before.

I have a table that has about 20 rows in it.

Table "public.s_apotik"
Column | Type | Modifiers
-------------------+------------------------------+------------------
obat_id | character varying(10) | not null
stock | numeric | not null
s_min | numeric | not null
s_jual | numeric |
s_r_jual | numeric |
s_order | numeric |
s_r_order | numeric |
s_bs | numeric |
last_receive | timestamp without time zone |
Indexes:
"s_apotik_pkey" PRIMARY KEY, btree(obat_id)

When I try to UPDATE one of the row, nothing happens for a very long time.
First, I run it on PgAdminIII, I can see the miliseconds are growing
as I waited. Then I stop the query, because the time needed for it is
unbelievably wrong.

Then I try to run the query from the psql shell. For example, the
table has obat_id : A, B, C, D.
db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='A'; (.... nothing
happens.. I press the Ctrl-C to stop it. This is what comes out
:)
Cancel request sent
ERROR: canceling query due to user request

(If I try another obat_id)
db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='B'; (Less than a
second, this is what comes out :) UPDATE 1

I can't do anything to that row. I can't DELETE it. Can't DROP the table.
I want this data out of my database.
What should I do? It's like there's a falsely pointed index here.
Any help would be very much appreciated.

1) lets hope you do regulary backups - and actually tested restore.
1a) if not, do it right now
2) reindex the table
3) try again to modify

Q: are there any foreign keys involved? If so, reindex those tables too,
just in case.

did you vacuum regulary?

HTH
Tino

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq