From: "Alban Medici \(NetCentrex\)" <amedici@fr.netcentrex.net>
To: <pgsql-general@postgresql.org>, <pgsql-sql@postgresql.org>,
	<pgsql-performance@postgresql.org>
Subject: Re: [PERFORM] need help
Date: Wed, 21 Dec 2005 12:10:33 +0100
Organization: NetCentrex
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
In-reply-to: <439551D0.5070904@wildenhain.de>
Thread-Index: AcX6QtxBWcxHbcD6SaCfuFqV0+6GQgL3BwHQ
Message-Id: <20051221112553.A5E0E9DC80C@postgresql.org>


Try to execute your query (in psql) with prefixing by EXPLAIN ANALYZE =
and
send us the result
 db=3D# EXPLAIN ANALYZE UPDATE s_apotik SET stock =3D 100 WHERE =
obat_id=3D'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=E9cembre 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).=20
> I've been dealing with Psql for over than 2 years now, but I've never=20
> had this case before.
>=20
> I have a table that has about 20 rows in it.
>=20
>        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                      |=20
> s_r_jual           | numeric                      |=20
> s_order            | numeric                      |=20
> s_r_order          | numeric                      |=20
> s_bs               | numeric                      |=20
> last_receive       | timestamp without time zone  |
> Indexes:
>    "s_apotik_pkey" PRIMARY KEY, btree(obat_id)
>   =20
> 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=20
> as I waited. Then I stop the query, because the time needed for it is=20
> unbelievably wrong.
>=20
> Then I try to run the query from the psql shell. For example, the=20
> table has obat_id : A, B, C, D.
> db=3D# UPDATE s_apotik SET stock =3D 100 WHERE obat_id=3D'A'; (.... =
nothing=20
> happens.. I press the Ctrl-C to stop it. This is what comes out
> :)
> Cancel request sent
> ERROR: canceling query due to user request
>=20
> (If I try another obat_id)
> db=3D# UPDATE s_apotik SET stock =3D 100 WHERE obat_id=3D'B'; (Less =
than a=20
> second, this is what comes out :) UPDATE 1
>=20
> I can't do anything to that row. I can't DELETE it. Can't DROP the =
table.=20
> 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.
>=20

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