From: Alban Medici \(NetCentrex\) <amedici@fr.netcentrex.net>
To: pgsql-general@postgresql.org
To: pgsql-sql@postgresql.org
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] need help
Date: Wed, 21 Dec 2005 12:10:33 +0100
Message-ID: <20051221112553.A5E0E9DC80C@postgresql.org> (raw)
In-Reply-To: <439551D0.5070904@wildenhain.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: amedici@fr.netcentrex.net, pgsql-general@postgresql.org, pgsql-performance@postgresql.org
Subject: Re: [PERFORM] need help
In-Reply-To: <20051221112553.A5E0E9DC80C@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox