[SQL] graphical interface - admin
What's the best open source GUI for DB administration? (postgres) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Calculating with sql
I'm having trouble figuring out how to select something. This is the problem: From a copy of the CDDB-database i want to select the artist album that has on average the longest tracks. But in the tracks table there is no such field as length, so i have to calculate it. But when i try to do so it just gives me errors. This is the erroneous query i have made so far select albums.ARTIST, albums.TITLE from tracks,albums where tracks.DISCID = albums.DISCID group by tracks.DISCID having(albums.LENGTH/count(tracks.TRACK)) = ( select max(albums.LENGTH/count(tracks.TRACK)) from tracks,albums where tracks.DISCID = albums.DISCID group by tracks.DISCID ); What is the correct way of selecting the album? -- Dennis K. ~.. It is impossible to make anything foolproof, .. because fools are so ingenious -Roger Berg- |\ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] CHECK clause doesn't work with CASE clause
Hi folks,
I have a problem with a CHECK clause that doesn't seem to work properly. The
CREATE TABLE statement looks like this:
CREATE TABLE epilepsy_information (
[...]
epilepsy_class Char
CHECK (epilepsy_class IN ('f', 'g', 'n')),
focusInteger REFERENCES focus(id)
CHECK (
CASE
WHEN ((focus NULL) AND (epilepsy_class = 'f')) THEN
TRUE
WHEN ((focus NULL) AND (epilepsy_class IN ('g',
'n'))) THEN FALSE
END),
[...]
);
It shouldn't be possible to insert a value into focus when epilepsy_class
has one of the values 'g' or 'n'. But it is. Can anyone help?
Thanx sincerefully,
Joerg
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CHECK clause doesn't work with CASE clause
Jörg Holetschek [EMAIL PROTECTED] writes: CASE WHEN ((focus NULL) AND (epilepsy_class = 'f')) THEN TRUE Try focus IS NOT NULL. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] CHECK clause doesn't work with CASE clause
On Thu, Jun 27, 2002 at 11:52:54AM +0200, J?rg Holetschek wrote:
Hi folks,
I have a problem with a CHECK clause that doesn't seem to work properly. The
CASE
WHEN ((focus NULL) AND (epilepsy_class = 'f')) THEN
TRUE
WHEN ((focus NULL) AND (epilepsy_class IN ('g',
'n'))) THEN FALSE
END),
The condition focus NULL can't ever come out true. You probably
mean IS NOT NULL.
Richard
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] 2 Selects 1 is faster, why?
On Wed, 26 Jun 2002 17:34:47 +0200 Alvar Freude [EMAIL PROTECTED] wrote: Hi, -- Eric [EMAIL PROTECTED] wrote: If I perform the following 2 selects, the first one is EXTREMELY slow where the 2nd one is very fast. [...] Why would 2 be so much faster? I have ran the EXPLAIN on this and index scans are being used. I guess, the first query has to search for all ol.orderid the equivalent o.orderid; the second variant only has to search for '1234' in each ?.orderid, which is much faster. You are right. And this type of optimising are not yet implemented. Tom said it in the prior discussions. Regards, Masaru Sugawara ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Possibility of Index-Only access in PostgreSQL?
Tom Lane wrote: It won't happen. See the many, many prior discussions of this point in the archives. Hmm, I searched the archives both in my local mailbox and on Google, could you give me some pointer to a relevant thread? I certainly want to learn from past discussions. So, it's that MVCC issue? I will then pursue distributing indexes and tables over more spindles and will also consider CLUSTERing the table (although that's difficult for two *different* use cases I have.) However, when everything is said and done, the it won't happen still sounds frightening to me. It puts PostgreSQL at a BIG disadvantage, limiting it to 150 records per second random access ceiling when the data needed is really all available for sequential index scan. I'm eager to learn more about it and may be put versioning into the index rows (or whatever it is.) Who am I to doubt Tom Lane's words it won't happen? But isn't this a very serious conclusion? In any of this we have that challenge/competition with Oracle in mind, want it or not. If we want to use PostgreSQL seriously for our medical record, an it won't happen answer can make a huge negative impression on those I am working with. PostgreSQL for our medical record system -- it won't happen!? ... Heck no! This can't be the answer! regards -Gunther -- Gunther Schadow, M.D., Ph.D.[EMAIL PROTECTED] Medical Information Scientist Regenstrief Institute for Health Care Adjunct Assistant ProfessorIndiana University School of Medicine tel:1(317)630-7960 http://aurora.regenstrief.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] Slow SELECT - Growing Database
On Mon, 2002-06-24 at 22:43, Stephan Szabo wrote:
On 24 Jun 2002, Marcos Garcia wrote:
Hmm, that should only happen if you're doing alot of updates or deletes
I would guess (updates would cause it to slow down as well since it's
similar to a delete and insert under MVCC). And frequent normal vacuums
should do that as well unless the table has high turnover.
Well, if you haven't yet, you might try upping the sort_mem and
shared_buffers amounts, although I think the former would only
potentially cut down the difference between 32s and 59s and the
latter would probably only help on a later use of the call if the
buffer is big enough to hold a significant portion of the pages.
The problem isn't in the select.
I realize that my database is growing and growing.
I've two tables that have, lets say, 120.000 records each, and:
- delete about 30.000 records a day from each table
- insert about 30.000 records a day on each table
- update each record at least 4 four times
I've two other ones, that were mentioned in my previous emails, that
have 12.000 records each, and:
- insert 48 records a day in each table
- =~ 120.000 updates in the last inserted records.
Track the problem:
# df -h /var/lib/pgsql - 7.8 GB (I create this database 4 month's ago)
# pg_dump dbnane dbname.dump
# dropdb dbname
# createdb dbname
# psql dbaname dbname.dump
# df -h /var/lib/pgsql - 140 M
I don't understand why the database is growing
And is still growing.
I make a vacuum -z -d dbname everyday (I checked if it really runs).
The only reason, for the growing of the database space, that i can see
for now, is described in the following lines.
I've some perl programs that are concurrent in the access to the
database, so i've have to make SELECT FOR UPDATE. The algorithm of the
program is:
Autocommit = 0;
eval {
select id from table where state=1 for update limit 10;
update table set locked = true where id in (?);
};
if (ERROR){
ROLLBACK;
}else{
COMMIT;
}
Autocommit = 1;
What are the major reasons for the growing of the database disk space?
Maybe the following information is important:
dbname select relname, relpages,reltuples from pg_class order by relpages desc limit
10;
relname | relpages | reltuples
-+--+---
sms_recv|30911 | 46801
sms_send| 7026 | 49119
sms_recv_unique_idclimsgidclien | 4561 | 46801
sms_recv_pkey | 3647 | 46801
sms_recv_msgidclient_idx| 3615 | 46801
recv_total | 1864 | 8120
send_total | 1378 | 12315
sms_send_pkey | 991 | 49119
sms_send_idclient_idx | 913 | 49119
recv_total_idclismsdate | 686 | 8120
(10 rows)
I really appreciate your help,
thanks,
M.P.Garcia
--
M.P.Garcia
PT Inovação, SA
Serviços e Redes Móveis
Rua José Ferreira Pinto Basto - 3810 Aveiro
Tel: 234 403 253 - Fax: 234 424 160
E-mail: [EMAIL PROTECTED]
signature.asc
Description: This is a digitally signed message part
Re: [SQL] Calculating with sql
On Wed, 26 Jun 2002 22:18:07 +0200 Dennis Kaarsemaker [EMAIL PROTECTED] wrote: From a copy of the CDDB-database i want to select the artist album that has on average the longest tracks. But in the tracks table there is no such field as length, so i have to calculate it. But when i try to do so it just gives me errors. This is the erroneous query i have made so far select albums.ARTIST, albums.TITLE from tracks,albums where tracks.DISCID = albums.DISCID group by tracks.DISCID having(albums.LENGTH/count(tracks.TRACK)) = ( select max(albums.LENGTH/count(tracks.TRACK)) from tracks,albums where tracks.DISCID = albums.DISCID group by tracks.DISCID ); What is the correct way of selecting the album? As for this query, some columns, AERIST, TITLE, and LENGTH, are lacking at the GROUP BY clauses. Probably, I would think a nearly correct one is something like the following. SELECT a1.ARTIST, a1.TITLE FROM (SELECT a0.DISCID, a0.ARTIST, a0.TITLE FROM tracks AS t0, albums AS a0 WHERE t0.DISCID = a0.DISCID GROUP BY a0.DISCID, a0.ARTIST, a0.TITLE , a0.LENGTH HAVING a0.LENGTH /COUNT(t0.TRACK) = (SELECT max(mx.average) FROM (SELECT albums.DISCID, albums.LENGTH /COUNT(tracks.TRACK) AS average FROM tracks, albums WHERE tracks.DISCID = albums.DISCID GROUP BY albums.DISCID, albums.LENGTH ) AS mx ) ) AS a1 Regards, Masaru Sugawara ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Slow SELECT - Growing Database
On 27 Jun 2002, Marcos Garcia wrote: On Mon, 2002-06-24 at 22:43, Stephan Szabo wrote: On 24 Jun 2002, Marcos Garcia wrote: Hmm, that should only happen if you're doing alot of updates or deletes I would guess (updates would cause it to slow down as well since it's similar to a delete and insert under MVCC). And frequent normal vacuums should do that as well unless the table has high turnover. Well, if you haven't yet, you might try upping the sort_mem and shared_buffers amounts, although I think the former would only potentially cut down the difference between 32s and 59s and the latter would probably only help on a later use of the call if the buffer is big enough to hold a significant portion of the pages. The problem isn't in the select. I realize that my database is growing and growing. I've two tables that have, lets say, 120.000 records each, and: - delete about 30.000 records a day from each table - insert about 30.000 records a day on each table - update each record at least 4 four times I've two other ones, that were mentioned in my previous emails, that have 12.000 records each, and: - insert 48 records a day in each table - =~ 120.000 updates in the last inserted records. Track the problem: # df -h /var/lib/pgsql- 7.8 GB (I create this database 4 month's ago) # pg_dump dbnane dbname.dump # dropdb dbname # createdb dbname # psql dbaname dbname.dump # df -h /var/lib/pgsql- 140 M I don't understand why the database is growing Well, I'd suggest starting with doing some reindex (or drop/create) indexes on big indexes (their space doesn't get reclaimed by vacuum currently). Another possibility is that you've got more pages having open space than the free space map has space for so you might want to increase max_fsm_pages and see if that helps. And, if you're effectively updating the table entirely 4 times in the day, you might want to run a vacuum more often (you probably don't need the analyze) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] graphical interface - admin
On Thu, Jun 27, 2002 at 07:50:09PM +0800, q u a d r a wrote: What's the best open source GUI for DB administration? (postgres) Emacs. ;) -- Ron Peterson -o) 87 Taylor Street /\\ Granby, MA 01033 _\_v https://www.yellowbank.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Slow SELECT - Growing Database
On Thu, Jun 27, 2002 at 04:24:04PM +0100, Marcos Garcia wrote:
On Mon, 2002-06-24 at 22:43, Stephan Szabo wrote:
On 24 Jun 2002, Marcos Garcia wrote:
Hmm, that should only happen if you're doing alot of updates or deletes
I would guess (updates would cause it to slow down as well since it's
similar to a delete and insert under MVCC). And frequent normal vacuums
should do that as well unless the table has high turnover.
Well, if you haven't yet, you might try upping the sort_mem and
shared_buffers amounts, although I think the former would only
potentially cut down the difference between 32s and 59s and the
latter would probably only help on a later use of the call if the
buffer is big enough to hold a significant portion of the pages.
The problem isn't in the select.
I realize that my database is growing and growing.
I've two tables that have, lets say, 120.000 records each, and:
- delete about 30.000 records a day from each table
- insert about 30.000 records a day on each table
- update each record at least 4 four times
if you have a lot of change going on in your db do not forget
to reindex your tables every so often. Index space is not
reclamed by vacuum, the first time can take a long time. Also
in 7.2+ vacuum got a new keyword 'full'. vacuum full' reclaims
disk space like in 7.1, the devault vacuum just marks rows that
were deleted as reusable.
lookin at your numbers you have 60,000 definate index entries
created each day, per index. And you have 120,000 x 4(min)=
480,000 endex entries created per index, if I remember correctly
update is handled by inserting a new row and deleteing the old
row. So it looks like 540,000 index entries changed per day.
good luck
marc
I've two other ones, that were mentioned in my previous emails, that
have 12.000 records each, and:
- insert 48 records a day in each table
- =~ 120.000 updates in the last inserted records.
Track the problem:
# df -h /var/lib/pgsql- 7.8 GB (I create this database 4 month's ago)
# pg_dump dbnane dbname.dump
# dropdb dbname
# createdb dbname
# psql dbaname dbname.dump
# df -h /var/lib/pgsql- 140 M
I don't understand why the database is growing
And is still growing.
I make a vacuum -z -d dbname everyday (I checked if it really runs).
The only reason, for the growing of the database space, that i can see
for now, is described in the following lines.
I've some perl programs that are concurrent in the access to the
database, so i've have to make SELECT FOR UPDATE. The algorithm of the
program is:
Autocommit = 0;
eval {
select id from table where state=1 for update limit 10;
update table set locked = true where id in (?);
};
if (ERROR){
ROLLBACK;
}else{
COMMIT;
}
Autocommit = 1;
What are the major reasons for the growing of the database disk space?
Maybe the following information is important:
dbname select relname, relpages,reltuples from pg_class order by relpages desc
limit 10;
relname | relpages | reltuples
-+--+---
sms_recv|30911 | 46801
sms_send| 7026 | 49119
sms_recv_unique_idclimsgidclien | 4561 | 46801
sms_recv_pkey | 3647 | 46801
sms_recv_msgidclient_idx| 3615 | 46801
recv_total | 1864 | 8120
send_total | 1378 | 12315
sms_send_pkey | 991 | 49119
sms_send_idclient_idx | 913 | 49119
recv_total_idclismsdate | 686 | 8120
(10 rows)
I really appreciate your help,
thanks,
M.P.Garcia
--
M.P.Garcia
PT Inovação, SA
Serviços e Redes Móveis
Rua José Ferreira Pinto Basto - 3810 Aveiro
Tel: 234 403 253 - Fax: 234 424 160
E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Unsubscription -- How?
Can someone please tell me how to unsubscribe from all the pgsql lists? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] 2 Selects 1 is faster, why?
Masaru Sugawara wrote: You are right. And this type of optimising are not yet implemented. Tom said it in the prior discussions. ...but is it true that if you place the filter clause first, the join will not have to complete the whole table? eg. SELECT item.description, stock.available FROM item, stock WHERE item.itemid = '1234' AND item.itemid=stock.itemid; ...would be more efficient than, SELECT item.description, stock.available FROM item, stock WHERE item.itemid=stock.itemid AND item.itemid = '1234'; -- Keith Gray Technical Services Manager Heart Consulting Services P/L mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] Slow SELECT - Growing Database
The problem isn't in the select. I realize that my database is growing and growing. I've two tables that have, lets say, 120.000 records each, and: - delete about 30.000 records a day from each table - insert about 30.000 records a day on each table - update each record at least 4 four times I've two other ones, that were mentioned in my previous emails, that have 12.000 records each, and: - insert 48 records a day in each table - =~ 120.000 updates in the last inserted records. Track the problem: # df -h /var/lib/pgsql- 7.8 GB (I create this database 4 month's ago) # pg_dump dbnane dbname.dump # dropdb dbname # createdb dbname # psql dbaname dbname.dump # df -h /var/lib/pgsql- 140 M I don't understand why the database is growing And is still growing. I make a vacuum -z -d dbname everyday (I checked if it really runs). You have never mentioned PostgreSQL verison you are using, so... (1) if that's 7.2.x, you need to slightly increase FSM parameters in postgresql.conf. As you have ~8GB database, probably you need to set max_fsm_pages to: 8*1024*1024*1024/8192 = 1048576. It will need about 7MB more shmem, but it would not be too much for modern PC. (2) if that's 7.1.x, or earlier, you need to vacuum and reindex more frequently. -- Tatsuo Ishii ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] time difference
I have a query that returns ?column? - 6 days 01:56:14 (1 row) How can I get it as total number of hours? Instead of days and hours, I need hours only (all in all, so 24hours * 6 days + 01:56:14) Can you help me please? Thanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] 2 Selects 1 is faster, why?
Keith Gray [EMAIL PROTECTED] writes: ...but is it true that if you place the filter clause first, the join will not have to complete the whole table? PG's planner does not pay attention to the ordering of WHERE clauses; it will do what it thinks best with them in any case. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] transaction in plpgsql
how can i implement transactions inside my plpgsql functions ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] transaction in plpgsql
Joe, how can i implement transactions inside my plpgsql functions You can't. Each PL/pgSQL function is automatically treated as a single transaction by Postgres. As such, the entire function either succeeds or fails. To manage a transaction *inside* the PL/pgSQL function, Postgres would have to support nested transactions ... which we currently don't. Nested transactions and MVCC are somewhat of a tangle to reconcile, and we don't expect a solution until Postgres 8.0 (if then). -Josh Berkus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
