RE: [GENERAL] changing between 6.4.1 and 6.5
At 18:02 25.08.99 +0200, you wrote: have You set any triggers, rules, listens or defaults for tablex? Nothing is set. I create a new table for tests. -- create table test1 (x1 int2, x2 int2, tx char); then I've inserted some values: -- insert into test1 values (1,2, 'a'); -- insert into test1 values (1,2, 'b'); -- insert into test1 values (1,3, 'b'); -- insert into test1 values (1,4, 'b'); -- insert into test1 values (2,2, 'b'); -- insert into test1 values (2,3, 'b'); after that I want a select with group by: -- select * from test1 group by x1, x2; ERROR: illegal use of aggregate or non-group column in target list I believe, that all fields are in group column, but what means target list: is it the native table test1 or is mean the output list, which is seen on screen after the statement? And another part is: the same table and the same statement bring out the correct values in a postgreql v6.4.x . Why not in v6.5? Jens
RE: [GENERAL] changing between 6.4.1 and 6.5
At 8:38 am +0200 26/8/99, Jens Felber wrote:
At 18:02 25.08.99 +0200, dpeder wrote:
have You set any triggers, rules, listens or defaults for tablex?
Nothing is set. I create a new table for tests.
-- create table test1 (x1 int2, x2 int2, tx char);
then I've inserted some values:
-- insert into test1 values (1,2, 'a');
-- insert into test1 values (1,2, 'b');
-- insert into test1 values (1,3, 'b');
-- insert into test1 values (1,4, 'b');
-- insert into test1 values (2,2, 'b');
-- insert into test1 values (2,3, 'b');
after that I want a select with group by:
-- select * from test1 group by x1, x2;
ERROR: illegal use of aggregate or non-group column in target list
OK, in your original posting you had: select x1, x2, x3, x4 on tablex order
by x1, x2.
...which has little to do with the statement you are now posting; (the
former has a syntax error and uses ORDER BY, the second has an SQL error
and uses GROUP BY).
I believe, that all fields are in group column, but what means target list:
is it the native table test1 or is mean the output list, which is seen on
screen
after the statement?
The target list is the list of fields that you SELECT statement will
return, in your select * statement, these fields are: x1,x2 and tx.
And another part is: the same table and the same statement bring out the
correct values in a postgreql v6.4.x . Why not in v6.5?
I've tried your SELECTs under postgres 6.4.0 and I'm begining to see where
the confusion might have arose.
PG6.5 is perfectly correct to flag up an error with the statement "select *
from test1 group by x1, x2;"
Think of it this way: you are asking it to form distinct groups on the
basis of having a unique combination of x1 and x2; then you ask, for each
such group, for the values of x1, x2 and tx. The problem is, for the group
where the value of x1 is 1 and the value of x2 is 2, there are two valid
values of tx ('a' and 'b').
Postgres can't -and indeed shouldn't have to- resolve this ambiguity so it
flags an error in version 6.5. Unfortunately it does not in version 6.4
which I would consider a bug!
You get the appropriate error message under PG 6.4 if you try the following:
SELECT *,count(tx) FROM test1 GROUP BY x1, x2;
To get your statement to work under PG6.5 you must either include the tx
field into your GROUP BY list:
SELECT * FROM test1 GROUP BY x1, x2,tx;
or drop it from your target list:
SELECT x1, x2 FROM test1 GROUP BY x1, x2,tx;
Personally, I think that you need to rethink exactly what you are trying to
do with your select and chose the appropriate solution.
The good news is that PG6.5 probably saved you from many silent errors
(because your select statement under PG6.4 could not be guaranteed to
always return the same value for tx!).
Hope this helps.
Regards,
Stuart.
+--+--+
| Stuart C. G. Rison | Ludwig Institute for Cancer Research |
+--+ 91 Riding House Street |
| N.B. new phone code!!| London, W1P 8BT |
| Tel. +44 (0)207 878 4041 | UNITED KINGDOM |
| Fax. +44 (0)207 878 4040 | [EMAIL PROTECTED] |
+--+--+
[GENERAL] Web pages
The link from http://www.postgresql.org/index.html to http://www.pgsql.com/contribute actually brings up the merchandise page. -- Vote against SPAM: http://www.politik-digital.de/spam/ Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 "But the day of the Lord will come as a thief in the night. The heavens shall pass away with a great noise, and the elements shall melt with fervent heat, and the earth and the works that are therein shall be burned up." II Peter 3:10
[GENERAL] Hardware optimising
Hi
I am about to upgrade a server that is about to be running a large and busy
postgresql database
currently it has
128 MB 100 MHz SDRAM
AMD K6-2/300 CPU
10 GB 7200RPM 9.0ms IBM IDE HDD
It will, over the next few months, as money becomes available, be upgraded to:
256 MB 100 MHz SDRAM
Dual Athlon 500 CPUs
10 GB UltraII Wide SCSI drive
The database will contain several million records and needs to be able to do
very fast selects from tables with a lot of rows, and do small updates and
inserts onto these tables at a good speed also.
Now, as the funds for this upgrade are trickling through slowly, I need to know
where I would see the most performance increase initially. Which of the three
peices would I be best to upgrade first to see the best performance increase. I
am leaning towards the fast scsi, as there is a LOT of data transfer.
Any insights from people that already run big databases, do we need memory, CPU
or fast disc most?
Thanx
M Simms
--
#define z(x,y) for (x=0;xy;x++){
main(){long int i[3]={1214606444,1864390511,1919706122};int x,y;
z(x,3)z(y,4)putchar((i[x]((3-y)3))(255));
}}}
Re: [GENERAL] Web pages
On Thu, 26 Aug 1999, Oliver Elphick wrote:
The link from http://www.postgresql.org/index.html to
http://www.pgsql.com/contribute actually brings up the merchandise page.
We merged the two...there is now a 'contribute' option on that page that
will be extended over the next few days to give a select list of TODO
items that your contribution can go towards...
Also, we've put in an order for "PostgreSQL Contributor" t-shirt that will
be sent out to contributors as a simple thanks. Since it wouldn't make
any sense otherwise, they will only be sent out on contributions of
$100CDN+...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: [GENERAL] Hardware optimising
if in doubt, memory first -- although it depends your app and hits.
processor/disk: check your cpu use.
amy
From: Michael [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: [GENERAL] Hardware optimising
Date: Thu, 26 Aug 1999 11:19:39 +0100
Hi
I am about to upgrade a server that is about to be running a large and busy
postgresql database
currently it has
128 MB 100 MHz SDRAM
AMD K6-2/300 CPU
10 GB 7200RPM 9.0ms IBM IDE HDD
It will, over the next few months, as money becomes available, be upgraded
to:
256 MB 100 MHz SDRAM
Dual Athlon 500 CPUs
10 GB UltraII Wide SCSI drive
The database will contain several million records and needs to be able to
do
very fast selects from tables with a lot of rows, and do small updates and
inserts onto these tables at a good speed also.
Now, as the funds for this upgrade are trickling through slowly, I need to
know
where I would see the most performance increase initially. Which of the
three
peices would I be best to upgrade first to see the best performance
increase. I
am leaning towards the fast scsi, as there is a LOT of data transfer.
Any insights from people that already run big databases, do we need memory,
CPU
or fast disc most?
Thanx
M Simms
--
#define z(x,y) for (x=0;xy;x++){
main(){long int i[3]={1214606444,1864390511,1919706122};int x,y;
z(x,3)z(y,4)putchar((i[x]((3-y)3))(255));
}}}
___
Get Free Email and Do More On The Web. Visit http://www.msn.com
[GENERAL] pg_class missing
I want to use postgresql. The error message is: /usr/bin/postmaster does not find the database system The problem seems to be: file /var/lib/pgsql/base/template1/pg_class is missing. in fact I can only find up to /var/lib/pgsgl/ How do I create the missing directories and file(s) ? I installed RedHat Linux from Mandrake. Thanks Phil Oelkers Database Analyst - Team O Experian DirectTech
Re: [GENERAL] Hardware optimising
hi... 128 MB 100 MHz SDRAM AMD K6-2/300 CPU 10 GB 7200RPM 9.0ms IBM IDE HDD It will, over the next few months, as money becomes available, be upgraded to: 256 MB 100 MHz SDRAM Dual Athlon 500 CPUs 10 GB UltraII Wide SCSI drive The database will contain several million records and needs to be able to do very fast selects from tables with a lot of rows, and do small updates and inserts onto these tables at a good speed also. seems there are a lot of opinions on this one floating around, and not alot of explanations to go along with them. =) in my experience, it really depends on what you are doing. if you are going to be doing the same selects alot, then extra RAM will help a lot. just be sure to set the buffers when starting postmaster to be relatively high (i.e. a few thousand). also, since you are doing few inserts, i'd turn off f-sync for greater speed and less disk access (which with your current IDE will be expensive (time wise) at best). this will run the risk of losing inserts if the machine crashes, but if you are using a stable OS (i.e. not NT) then you'll probably be just fine if the inserts are few compared to selects. also, if you are going to be doing a lot of pre- and post-processing of the data (i.e. grabbing bits of data based on a algorythm (sp) or getting bits of data and massaging them about a lot (i.e. creating graphs, lay-out, doing analysis, etc)) then RAM will also see a boost as you will be able to do these in memory, allowing the database the disk more to itself... the SCSI drive will see an increase in speed to be sure! in fact, i'd suggest giving the database the drive all to itself for data... leave everything else on the IDE drive (OS, database engine, etc) and format the SCSI drive with large i-node blocks (i.e. 1MB) and just let the data reside on the SCSI disk. besides gaining the speed of the disk you'll also allow the rest of the system to stay the hell out of the way of that disk intensive database! =) you'll probably see a tremendous increase in speed doing it this way (large inodes, only database data) than if you just simply replace the IDE with the SCSI drive... of course, as time gos on, if you use a mirroring raid array by adding another disk, you'll see even more speed increase. other RAIDs, while preserving your data, will result in slow downs.. though it will still be faster than an IDE drive with everything on it. RAID 5 is cheaper (more out of your disk space) but will be a bit slower than a single disk system or a RAID 5... but RAID 5 is a nice way to go... however, if you do go RAID, DO NOT use software RAID. why? well... it negates some of the fail-safe power of the RAID (although if well set up you can render this moot) but more importantly it will drag significantly on your processors (~10% or so is common) as for the processor, this will see an increase, of course. note, however, that since PostgreSQL is _not_ multithreaded, that it will run only on one of the processors. (i'm about to assume you are using linux here... 'scuse me if i'm wrong) however, the good news is that you can encourage linux (through the scheduler) to run postgres on one of the processors and everything else on the other one. this should give the database its own processor more oft than not. things may still drift, etc... but it will be better this way the processor boost will be important, again, if you are doing lots of pre/post-processing of data. it will also see an improvement if you are offering other services (i.e. WWW) on the machine (which i'm guessing you are). this will require a kernel recompile and some muckin' about to get it all running as quickly/smoothly/efficiently as possible. this is another side of things to look at: RAM is quick and instant. power down, slap in some more ram, power up. fast down time. DISK upgrade will take more time. i.e. formatting; setting it in your FSTAB, etc; changing your start up scripts to tell postgres where the data is now; copying things to the new disk, etc... this will result in some fairly good down time. the installation can be done quickly (if well thought out, i.e. pre-format the drive, etc...) and the rest can be done while online. although you'll want to shut the database down while copying data files. the longer you wait on this one, the longer your down time will be (more data to copy, etc...) CPU upgrade will require downtime to install (not nearly as fast or easy as RAM).. then kernel recompiling.. then testing of the new kernel... then tweaking the system. probably resulting in even more down time than with the disk upgrade. i'm guessing that while the system is new, you'll probably be more agreeable to longer downtimes. so perhaps the disk upgrade would be better earlier on in that it will probably give you the best improvement while absorbing down time impact early in on the venture when it might not be noticed so much (i'm guessing here again as to the nature of your usage...
Re: [GENERAL] Hardware optimising
as for the processor, this will see an increase, of course. note, however, that since PostgreSQL is _not_ multithreaded, that it will run only on one of the processors. (i'm about to assume you are using linux here... 'scuse me if i'm wrong) however, the good news is that you can encourage linux (through the scheduler) to run postgres on one of the processors and everything else on the other one. this should give the database its own processor more oft than not. things may still drift, etc... but it will be better this way Different backends can use different CPU's, no problem. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Hardware optimising
What scheduler are we speaking of here? Andy On Thu, 26 Aug 1999, Bruce Momjian wrote: as for the processor, this will see an increase, of course. note, however, that since PostgreSQL is _not_ multithreaded, that it will run only on one of the processors. (i'm about to assume you are using linux here... 'scuse me if i'm wrong) however, the good news is that you can encourage linux (through the scheduler) to run postgres on one of the processors and everything else on the other one. this should give the database its own processor more oft than not. things may still drift, etc... but it will be better this way Different backends can use different CPU's, no problem. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[GENERAL] Pg.pm for Windows?
How can I use Pg.pm in my perl scripts running on Windows through ActivePerl? Some of my developers are refusing to work on Linux machines for just writing perl scripts :-). Thanks, Anand.
Re: [GENERAL] Hardware optimising
What scheduler are we speaking of here? Andy On Thu, 26 Aug 1999, Bruce Momjian wrote: as for the processor, this will see an increase, of course. note, however, that since PostgreSQL is _not_ multithreaded, that it will run only on one of the processors. (i'm about to assume you are using linux here... 'scuse me if i'm wrong) however, the good news is that you can encourage linux (through the scheduler) to run postgres on one of the processors and everything else on the other one. this should give the database its own processor more oft than not. things may still drift, etc... but it will be better this way Different backends can use different CPU's, no problem. Each backend is a different process, so they can run at the same time on multiple cpu's. Any OS that can handle multiple cpu's can handle PostgreSQL running multiple backends at the same time. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Hardware optimising
--- Andy Lewis [EMAIL PROTECTED] wrote: What scheduler are we speaking of here? Andy On Thu, 26 Aug 1999, Bruce Momjian wrote: as for the processor, this will see an increase, of course. note, however, that since PostgreSQL is _not_ multithreaded, that it will run only on one of the processors. (i'm about to assume you are using linux here... 'scuse me if i'm wrong) however, the good news is that you can encourage linux (through the scheduler) to run postgres on one of the processors and everything else on the other one. this should give the database its own processor more oft than not. things may still drift, etc... but it will be better this way Different backends can use different CPU's, no problem. -- Bruce Momjian Bruce, of course, is, as always, absolutely correct. Each connection to the backend starts a postgres process which will be assigned to either CPU by Linux. I have read (either somewhere in the SMP FAQ or some mailing list) that there are utilities forthcoming (this was awhile ago) to assign a process to a specific CPU. There are several advantages to having a multithreaded backend instead of a multitasking backend since connections would be faster, no need for shared memory segments, etc., but use of multiple processors is not exclusive to multithreading applications. Any application which forks() or execs() another can take advantage of multiple processors. And there are disadvantages to multithreading too as pointed out in previous threads (no pun intended), such as stability of the running process if one of its threads dies abnormally. With regard to the original post, I again, agree fully with Bruce - SCSI first. And spend an extra couple hundred to get the 80MBs variety, dual channel controllers; its worth it. Hopefully one would also be able to optimize the disk configuration as well. We run RedHat Linux 2.0.36 on a Dual 450Mhz deskside server with 256M of RAM. The only regret I have is we didn't get the 80MBs (we got 40MBs) controller and (6) 4 Gig hard drives. Instead we got (2) 9 Gig drives. This forces us to only run RAID 1. For only a few hundred more, we could have run RAID 0+1 on dual channels (with each mirror on the other channel). We also put the database on the second innermost partition, with the outer being swap. Finally, if you are using Linux and choose to go the SMP route, I highly recommend the newer 2.2 kernels. We saw dramatic improvement in speed over 2.0.36 vs. 2.2.x in our testing environment. In fact, to enable SMP on a 2.0.36 kernel, you must modify the top-level Makefile for the kernel and rebuild. Anyways, Hope that helps, Mike Mascari ([EMAIL PROTECTED]) P.S. From previous posts, I'm starting to think that there is a VAST misconception that a single-threaded database engine (which is what Oracle was until some version 7 releases, I believe, called Oracle MTS appeared) can only handle ONE query at a time, and does not exec() a child process for each connection. Someone ought to start the propoganda of claiming multi-threaded DBMS as "single process" servers. __ Do You Yahoo!? Bid and sell for free at http://auctions.yahoo.com
Re: [GENERAL] Hardware optimising
Thanks for the info! Much appreciated! Andy On Thu, 26 Aug 1999, Mike Mascari wrote: --- Andy Lewis [EMAIL PROTECTED] wrote: What scheduler are we speaking of here? Andy On Thu, 26 Aug 1999, Bruce Momjian wrote: as for the processor, this will see an increase, of course. note, however, that since PostgreSQL is _not_ multithreaded, that it will run only on one of the processors. (i'm about to assume you are using linux here... 'scuse me if i'm wrong) however, the good news is that you can encourage linux (through the scheduler) to run postgres on one of the processors and everything else on the other one. this should give the database its own processor more oft than not. things may still drift, etc... but it will be better this way Different backends can use different CPU's, no problem. -- Bruce Momjian Bruce, of course, is, as always, absolutely correct. Each connection to the backend starts a postgres process which will be assigned to either CPU by Linux. I have read (either somewhere in the SMP FAQ or some mailing list) that there are utilities forthcoming (this was awhile ago) to assign a process to a specific CPU. There are several advantages to having a multithreaded backend instead of a multitasking backend since connections would be faster, no need for shared memory segments, etc., but use of multiple processors is not exclusive to multithreading applications. Any application which forks() or execs() another can take advantage of multiple processors. And there are disadvantages to multithreading too as pointed out in previous threads (no pun intended), such as stability of the running process if one of its threads dies abnormally. With regard to the original post, I again, agree fully with Bruce - SCSI first. And spend an extra couple hundred to get the 80MBs variety, dual channel controllers; its worth it. Hopefully one would also be able to optimize the disk configuration as well. We run RedHat Linux 2.0.36 on a Dual 450Mhz deskside server with 256M of RAM. The only regret I have is we didn't get the 80MBs (we got 40MBs) controller and (6) 4 Gig hard drives. Instead we got (2) 9 Gig drives. This forces us to only run RAID 1. For only a few hundred more, we could have run RAID 0+1 on dual channels (with each mirror on the other channel). We also put the database on the second innermost partition, with the outer being swap. Finally, if you are using Linux and choose to go the SMP route, I highly recommend the newer 2.2 kernels. We saw dramatic improvement in speed over 2.0.36 vs. 2.2.x in our testing environment. In fact, to enable SMP on a 2.0.36 kernel, you must modify the top-level Makefile for the kernel and rebuild. Anyways, Hope that helps, Mike Mascari ([EMAIL PROTECTED]) P.S. From previous posts, I'm starting to think that there is a VAST misconception that a single-threaded database engine (which is what Oracle was until some version 7 releases, I believe, called Oracle MTS appeared) can only handle ONE query at a time, and does not exec() a child process for each connection. Someone ought to start the propoganda of claiming multi-threaded DBMS as "single process" servers. __ Do You Yahoo!? Bid and sell for free at http://auctions.yahoo.com
Re: [GENERAL] Hardware optimising
P.S. From previous posts, I'm starting to think that there is a VAST misconception that a single-threaded database engine (which is what Oracle was until some version 7 releases, I believe, called Oracle MTS appeared) can only handle ONE query at a time, and does not exec() a child process for each connection. Someone ought to start the propoganda of claiming multi-threaded DBMS as "single process" servers. Yes, I am totally unsure how this gets confused by people. I am going to put it int the FAQ. Yes, and I agree that most multi-threaded DBMS are "single process", which can't make use if multiple cpus, except on some very special OS's that allow threads to move between cpus, sometimes called kernel threads, I think, but I am not sure on that. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
