pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedneed help
6+ messages / 6 participants
[nested] [flat]
* need help
@ 2005-12-06 08:38 Jenny <jenny_tania@yahoo.com>
2005-12-06 08:54 ` Re: need help Tino Wildenhain <tino@wildenhain.de>
2005-12-06 10:45 ` Re: need help Alban Hertroys <alban@magproductions.nl>
0 siblings, 2 replies; 6+ messages in thread
From: Jenny @ 2005-12-06 08:38 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql@postgresql.org; pgsql-performance
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.
Regards,
Jenny Tania
__________________________________________
Yahoo! DSL – Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: need help
2005-12-06 08:38 need help Jenny <jenny_tania@yahoo.com>
@ 2005-12-06 08:54 ` Tino Wildenhain <tino@wildenhain.de>
2005-12-21 11:10 ` Re: [PERFORM] need help Alban Medici (NetCentrex) <amedici@fr.netcentrex.net>
1 sibling, 1 reply; 6+ messages in thread
From: Tino Wildenhain @ 2005-12-06 08:54 UTC (permalink / raw)
To: Jenny <jenny_tania@yahoo.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql@postgresql.org; pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PERFORM] need help
2005-12-06 08:38 need help Jenny <jenny_tania@yahoo.com>
2005-12-06 08:54 ` Re: need help Tino Wildenhain <tino@wildenhain.de>
@ 2005-12-21 11:10 ` Alban Medici (NetCentrex) <amedici@fr.netcentrex.net>
0 siblings, 0 replies; 6+ messages in thread
From: Alban Medici \(NetCentrex\) @ 2005-12-21 11:10 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql@postgresql.org; pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: need help
2005-12-06 08:38 need help Jenny <jenny_tania@yahoo.com>
@ 2005-12-06 10:45 ` Alban Hertroys <alban@magproductions.nl>
1 sibling, 0 replies; 6+ messages in thread
From: Alban Hertroys @ 2005-12-06 10:45 UTC (permalink / raw)
To: Jenny <jenny_tania@yahoo.com>; +Cc: pgsql-general@postgresql.org
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//
^ permalink raw reply [nested|flat] 6+ messages in thread
* Need Help
@ 2006-12-28 02:42 JM <jerome@gmanmi.tv>
2006-12-28 02:54 ` Re: Need Help Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: JM @ 2006-12-28 02:42 UTC (permalink / raw)
To: pgsql-general@postgresql.org; +Cc: pgsql-performance
how can i get the disk usage for each table? can i do it via SQL?
Thanks,
Mailing-Lists
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need Help
2006-12-28 02:42 Need Help JM <jerome@gmanmi.tv>
@ 2006-12-28 02:54 ` Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: Richard Broersma Jr @ 2006-12-28 02:54 UTC (permalink / raw)
To: JM <jerome@gmanmi.tv>; pgsql-general@postgresql.org; +Cc: pgsql-performance
--- JM <jerome@gmanmi.tv> wrote:
> how can i get the disk usage for each table? can i do it via SQL?
This function should do what you want:
pg_relation_size(text) or pg_total_relation_size(text)
I found it on the following link:
http://www.postgresql.org/docs/8.2/interactive/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-12-28 02:54 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-06 08:38 need help Jenny <jenny_tania@yahoo.com>
2005-12-06 08:54 ` Tino Wildenhain <tino@wildenhain.de>
2005-12-21 11:10 ` Alban Medici (NetCentrex) <amedici@fr.netcentrex.net>
2005-12-06 10:45 ` Alban Hertroys <alban@magproductions.nl>
2006-12-28 02:42 Need Help JM <jerome@gmanmi.tv>
2006-12-28 02:54 ` Re: Need Help Richard Broersma Jr <rabroersma@yahoo.com>
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