Thread: Re: [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
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