Re: [GENERAL] Performance aggregates
On Tuesday 15 May 2001 14:40, you wrote: Hello, I have 2 tables : CREATE TABLE e_kalkn ( idINT4 NOT NULL, roba VARCHAR(6) NOT NULL, dat DATENOT NULL, datv DATENOT NULL, val VARCHAR(3) NOT NULL, kod VARCHAR(3) NOT NULL, tbVARCHAR(2) NOT NULL, tsVARCHAR(2) NOT NULL, dob VARCHAR(6), statusVARCHAR(1) ); CREATE TABLE e_kalkns ( idINT4 NOT NULL, redbr INTNOT NULL, osn NUMERIC(30,6), proc NUMERIC(30,6), izn NUMERIC(30,6) ); and indexes : create unique index e_kalkn_id on e_kalkn(id); create index e_kalkns_id on e_kalkns(id); Table e_kalkn have 4668 rows and e_kalkns 101170 rows. Query : select roba,sum(izn) from e_kalkn k,e_kalkns ks where k.id=ks.id group by roba order by roba is 2.5 times faster on one commercial database (there are tests on Internet that say 'Postgresql is faster than that database). I can't say which database it is. MySQL is 2.5 times faster, too. regards, ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: [HACKERS] Internet is putting lot of competition fire heat under Microsoft SQL Server
Hi, This sort of post is giving open source software a bad name. I am glad that this post has only reached a small number of people who are already pro-PostgreSQL and has not reached the crowd who needs convincing that their time, attention, enthusiasm, and money is better spent on PostgreSQL than on MS SQL Server (in the long run, due to the business model. I know that Microsoft bought Jim Gray and a number of other people who are in a position to build amazing things; well, we have Stonebraker ... or at least had him). If anybody wishes to do good, they better start off by reading one of the advocacy documents. Many of the advocacy arguments that apply to Linux can equally well be used in the context of PostgreSQL. One might start reading here: http://linuxtoday.com/stories/1847.html The gist of the matter is, a post that contains expletives, excessive use of the word boss, derisive mockings of large companies (M$ would be such a mocking), or other unqualified drivel without informational content, will make people think that the whole crowd around PostgreSQL is stupid. An argued comparison of standards compliance with a list of 5 intentional deviations by a Microsoft product might be more convincing. So please stop posting ignorant, but enthusiastic messages that give the development team a bad name. Thank you, Oliver Seidel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Tiiiiiiiiiiiiime
hi all. from a DB table, I'd like to SELECT out the date of 3months before. the command should be = select * from MYTBL where time =XX ; what whould it like to be at X ? I thought that I can use the type of 'today', which is meant to be the 0:00am of the day. or 'current' or things like this. but how can you make it "0:00am of three days ago"? also, if you know about it, I'd like to know about the difference between 'current' and 'now'. thanks about that. hiroko ogawa
[GENERAL] WAL and crash ressialiance
Hi, If I have the WAL pointing to a different disk that the main postgres database, and that other disk crashes (say writing to it blocks) what will postgres do ? Will it continue to write OK to the main database, or will the whole thing crash ? or hang waiting for the WAL writes to finish ? Thanks. ---(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
[GENERAL] NULL indicator variable
Is it possible to use NULL indicator variable for ESQL? If it is, please tell me how to declare the indicator and use.. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Restore from a dead machine.
Hello there, I have a FreeBSD machine that died earlier this week and i have been attempting to restore it (or rather it's contents). Postgres has caused somewhat of a problem in this area. I was running 7.0.1 and decided to upgrade to 7.0.3 since i have to reinstall it anyways. The install has gone fine and i can restore the old database back into the new postgres install without any problems. But when i try to run any of the perl scripts designed for the database I'm getting the following error: Can't locate Pg.pm in @INC (@INC contains: /usr/libdata/perl/5.00503/mach /usr/libdata/perl/5.00503 /usr/local/lib/perl5/site_perl/5.005/i386-freebsd /usr/local/lib/perl5/site_perl/5.005 .) at ./top25.pl line 2. Line 2 of the specific program reads:Use Pg; Nothing seemed out of the ordinary during the config/make/install process, and the only thing i did non standard was to run the config as: ./configure --with-perl --with-odbc. So can anyone tell me why my perl modules don't like me? Or more importantly how to get them to play friendly. If it makes a difference i have a backup of the old (7.0.1) install and a bunch of other directories under usr. I can locate the Pg.pm and DBI.pm in what would have been /usr/local/lib/perl5/site_perl/5.005/i386-freebsd. Thanks in advance. And apologies for typos / sticky keys. .jtp John T Porvaznik Person type person Akadine Press Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] Parallel Serever use PostgreSQL
Help me, please. Can I create a 2 Parallel Server with clustrering DB PostgreSQL? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Re: Queries across multiple databases (was: SELECT from a table in another database).
Trygve Falch wrote: In article [EMAIL PROTECTED], Nils Zonneveld [EMAIL PROTECTED] wrote: Actually I think I found 'Allow queries across multiple databases' in the TODO-list under something they call 'Exotic feature'. I thought that this feature was relativly basic and standard-feature in most DB's. I don't know what you are using those database for, but nothing prevents you from letting your clients connect to the different databases the same time. But that requires me to make a new database connection for each database I need to access. And putting 200+ tables in one single database is not an option. The application which needs to be able to do this is a cross-database-application (MSSQL, Oracle, Sybase) and I have almost no room for doing major changes to the SQL which this application uses. If you have a cross-database-application you must already have multiple connections to several database-engines at the same time. Or is that a situation you want to get rid of? But the lack of this feature in Postgres makes it almost impossible to make a structured database design for huge application. I know this question have been asked before in another postgres forum as early as 1998, and what Bruce Momjian said then was that most commercial databases couldn't do it, which was probably right for 1998, but today even MySQL can do this! Sybase, Oracle and MSSQL can also do this. I think even DB2 and Informix can. I was really suprised when I discovered that this was even an issue with Postgres, because everything else in this wonderful DBM is on an enterprise level of quality and functionality. Sadly, this means I'll have to stick to Oracle (even if I really didn't want to) until this issue is resolved in Postgres. I'm not a PostgreSQL developer, just a humble user :-) If you have the money and resources to use Oracle, use Oracle if you really need schema support. If not, there are workarounds. At the moment PostgreSQL doesn't have schema support. But there's light at the end of the tunnel: I've heard Tom Lane mention schema support several times (can you give us an estimate when schema support will be available in PostgreSQL Tom?). Regards, Nils Zonneveld ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Restore from a dead machine.
Can't locate Pg.pm in @INC (@INC contains: /usr/libdata/perl/5.00503/mach /usr/libdata/perl/5.00503 /usr/local/lib/perl5/site_perl/5.005/i386-freebsd /usr/local/lib/perl5/site_perl/5.005 .) at ./top25.pl line 2. Line 2 of the specific program reads:Use Pg; Make sure that the user running the script has read perms on the Pg.pm file. -- Alex Howansky Wankwood Associates http://www.wankwood.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Unusual slowdown using subselects
I'm stress testing my application by creating large data sets. This particular query selects rows from the schedule table that have a specific owner_id. (I'll show you the results of explain) calendar=# explain select * from schedule where schedule.owner_id=101 or schedule.owner_id=102; Index Scan using schedule_id_index, schedule_id_index on schedule (cost=0.00..78.64 rows=20 width=40) Looks great and executes very fast. calendar=# explain select group_id from groups where user_id=101; NOTICE: QUERY PLAN: Index Scan using groups_id_index on groups (cost=0.00..2.02 rows=1 width=4) Again, very fast. The groups table maps users to groups. However, this next one is slow. calendar=# explain select * from schedule where schedule.owner_id in (select group_id from groups where user_id=101); NOTICE: QUERY PLAN: Seq Scan on schedule (cost=0.00..2039895.00 rows=100 width=40) SubPlan - Materialize (cost=2.02..2.02 rows=1 width=4) - Index Scan using groups_id_index on groups (cost=0.00..2.02 rows=1 width=4) You'll see in this one, where the first example did a index scan, this one with a very similar query does a seq scan. The two queries should be nearly identical, but this one runs very slowly. Can anyone explain why this happens and/or how I can do a sub-select like this and get fast results? Thank you John Aughey ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Unusual slowdown using subselects
calendar=# explain select * from schedule where schedule.owner_id in (select group_id from groups where user_id=101); NOTICE: QUERY PLAN: Seq Scan on schedule (cost=0.00..2039895.00 rows=100 width=40) SubPlan - Materialize (cost=2.02..2.02 rows=1 width=4) - Index Scan using groups_id_index on groups (cost=0.00..2.02 rows=1 width=4) You'll see in this one, where the first example did a index scan, this one with a very similar query does a seq scan. The two queries should be nearly identical, but this one runs very slowly. Can anyone explain why this happens and/or how I can do a sub-select like this and get fast results? Try using EXISTS rather than IN (see the FAQ for more information). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Restore from a dead machine.
User JOHN [EMAIL PROTECTED] writes: But when i try to run any of the perl scripts designed for the database I'm getting the following error: Can't locate Pg.pm in @INC (@INC contains: /usr/libdata/perl/5.00503/mach /usr/libdata/perl/5.00503 /usr/local/lib/perl5/site_perl/5.005/i386-freebsd /usr/local/lib/perl5/site_perl/5.005 .) at ./top25.pl line 2. Perhaps what it's really failing to find is libpq.so. Are you sure your dynamic loader's search path will find libpq? 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])
[GENERAL] Need help using PHP ODBC to connect Postgress 7.1.1
I'm having trouble setting up a remote Apache PHP webserver to connect to our postgres machine using ODBC. This is what I get when trying to connect with the odbc_connect statement: Warning: SQL error: Missing server name, port, or database name in call to CC_connect., SQL state IM002 in SQLConnect in /www/virtual/firebird/common/pre.php on line 25 I've checked the log, and found: DSN info: DSN='panoramaSQL',server='',port='',dbase='',user='',passwd='' onlyread='',protocol='',showoid='',fakeoidindex='',showsystable='' conn_settings='',conn_encoding='OHTER' translation_dll='',translation_option='' conn = 138389968, SQLConnect(DSN='panoramaSQL', UID='remotesite', PWD='password_is_set') Global Options: Version='07.01.0005', fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190 disable_optimizer=1, ksqo=1, unique_index=0, use_declarefetch=0 text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 extra_systable_prefixes='dd_;', conn_settings='' conn_encoding='OHTER' CONN ERROR: func=SQLConnect, desc='Error on CC_connect', errnum=101, errmsg='Could not connect to the server' henv=138348392, conn=138389968, status=0, num_stmts=16 sock=138396432, stmts=138304368, lobj_type=-999 Socket Info --- socket=-1, reverse=0, errornumber=2, errormsg='Could not resolve hostname.' buffer_in=138396472, buffer_out=138385616 buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0 As you see there is no server or port parameter, but they exist in my odbcinst.ini and odbc.ini odbcinst.ini: [PostgreSQL] Debug=0 CommLog=1 odbc.ini: ; ; odbc.ini ; [ODBC Data Sources] panoramaSQL = Connection to Panorama [panoramaSQL] Driver = /usr/local/pgsql/lib/libpsqlodbc.so Servername=pgsq1.freeview.com Username=modularwebsites Password=test4sql Database=worldofsoccer_com Port=5432 ReadOnly = no Trace = 1 TraceFile = /tmp/odbc.log [ODBC] InstallDir = /usr/local/pgsql/lib PHP Was compiled with: ./configure --with-apache=/usr/src/apache_1.3.12-ssl \ --with-mysql=/usr/local/mysql --enable-track-vars --with-imap --with-jpeg-dir=/usr/local/bin --with-gd=/usr/local --with-ttf --with-pgsql --with-iodbc Apache Was Compiled with: ./configure --with-layout=Apache --prefix=/usr/local/apache-ssl --enable-module=info --enable-module=log_referer --enable-module=mime_magic --enable-module=mmap_static --enable-module=status --activate-module=src/modules/php4/libphp4.a --enable-module=rewrite iODBC (libiodbc-2.50.3) Compiled with: ./configure --prefix=/usr/local --with-iodbc-inidir=/etc Postgres 7.1.1 Compiled with: ./configure --with-perl --enable-multibyte --enable-unicode-converstion --enable-odbc Thank you for any help!!! -Allan - Allan Rafuse - Systems Administrator Freeview Publishing Inc. email: [EMAIL PROTECTED] web: http://www.freeview.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Newbe questions: Setting Passwords
Hi, I'm new to this list and Postgres. I have Postgres up and running, but I'm having trouble understanding the password system. In particular, I have the following troubles: 1. Is it correct that users can only be given permissions on a particular TABLE? 2. The owner of all my tables are listed as postgres. Should I then be using this postgres as the user name when I'm connecting (I'll be connecting with PHP). 3. How can I change the password of postgres? Sorry for the base-ness of the above questions, but your input would save me a lot of grief. - anatole ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Newbe questions: Setting Passwords
Hi, You could use the following...;-) psql -U postgres template1 UPDATE pg_shadow SET passwd='whatever' WHERE usename='postgres'; You could also use GRANT or create other users as well... kindly check the administrative section of your documentation...;-) Cheers, John Clark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] PL/Perl in 7.1.1
I recently upgraded from 7.0.3 to 7.1. Everything went fine until I went to install plperl using createlang. It throws the following error message: ERROR: Load of file /usr/local/pgsql/lib/plperl.so failed: /usr/lib/perl5/5.6.0/i686-linux/auto/Opcode/Opcode.so: undefined symbol: Perl_save_vptr createlang: language installation failed. I verified the plperl.so file compiled without problems and is installed correctly at /usr/local/pgsql/lib/. Any ideas? -- Jeff Patterson Site Administrator The Melanoma Patients' Information Page http://www.mpip.org/ ---(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: [GENERAL] PL/Perl in 7.1.1
Jeff Patterson [EMAIL PROTECTED] writes: I recently upgraded from 7.0.3 to 7.1. Everything went fine until I went to install plperl using createlang. It throws the following error message: ERROR: Load of file /usr/local/pgsql/lib/plperl.so failed: /usr/lib/perl5/5.6.0/i686-linux/auto/Opcode/Opcode.so: undefined symbol: Perl_save_vptr Hm. There is no such symbol in the source text of Postgres, so I suspect you are looking at a Perl version-compatibility problem, rather than something that's directly Postgres' fault. I suggest asking Perl gurus rather than Postgres gurus for help ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Tiiiiiiiiiiiiime
This is a guess - have you tried: SELECT * FROM tableName WHERE time = CURRENT_TIMESTAMP - '3 months'::interval let me know if it works! -r At 05:49 PM 5/15/01 +0900, hiroko wrote: hi all. from a DB table, I'd like to SELECT out the date of 3months before. the command should be = select * from MYTBL where time =XX ; what whould it like to be at X ? I thought that I can use the type of 'today', which is meant to be the 0:00am of the day. or 'current' or things like this. but how can you make it 0:00am of three days ago? also, if you know about it, I'd like to know about the difference between 'current' and 'now'. thanks about that. hiroko ogawa --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01 ---(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
[GENERAL] Explicit column naming on INSERT question
Buenas tardes, all. I'm puzzled by my inability to use the following INSERT statement: INSERT INTO RM_USER VALUES(RM_USER.ID, RM_USER.FIRST_NAME) VALUES(1, 'DUBYA') it seems that postgres is unhappy about the explicit column naming (e.g. 'RM_USER.ID' instead of just 'ID') because I end up with either: ERROR: parser: parse error at or near values or ERROR: parser: parse error at or near . Take out the explicit table name and everything is fine. The real problem is that I've got a significant amount of code that uses this explicit naming scheme (whether it's really the best thing to do or not, that's the way it is now) and oh what a headache I'll have if I have to go through it all and change this. I'm running 7.0.2, does anybody know if this is also the case in any more recent versions of postgres? Thanks. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: DB2 on Linux beats MS where would postgres end up?
In article 033f01c0de0d$ce93fcc0$230470d1@INSPIRON, Dave Cramer [EMAIL PROTECTED] wrote: http://www.zdnet.com/zdnn/stories/news/0,4586,2760874,00.html?chkpt=zdnn0516 01 It would be great if we could see where postgres fits in this benchmark There are lies, damned lies, and benchmarks. That said (or shamelessly cribbed from Disraeli), I have found that for my current application (an auditing system for the transportation industry), PostgreSQL is 2-4 times faster than DB2 UDB 7.1 for most of our queries. To say that I was suprised is an understatement (no offense to the PostgreSQL crew). The database has a couple dozen tables, the largest is just over 1GB with 3.5 million rows. The database as a whole is over 6GB. This is running PostgreSQL 7.1 under RedHat 7.1 (it was true under RH 6.2, also). Hardware is an IBM Netfinity 7000 (4xPPro200/1M) with 1.5GB RAM and two RAID-5E arrays. My customer is running on a Dell PowerEdge 2400 (2xPIII 866) with 512MB RAM with a RAID-1 and a RAID-10 array. This one is amazingly fast! As always, your mileage may vary, contents may have settled during shipment, and objects in mirror are closer than they appear. Gordon. -- It doesn't get any easier, you just go faster. -- Greg LeMond ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Newbe questions: Setting Passwords
Hi, Thanks for your advice. I set my password as you advised below, however I'm still having some problems connecting. When I try to connect I get the following error message: Unable to connect to PostgreSQL server: FATAL 1: SetUserId: user 'password=' is not in 'pg_shadow' When connecting I'm using username postgres and the password that I used when I updated the pg_shadow file as suggested. Any ideas??? Hi, You could use the following...;-) psql -U postgres template1 UPDATE pg_shadow SET passwd='whatever' WHERE usename='postgres'; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Newbe questions: Setting Passwords
Anatole Varin wrote: Hi, Thanks for your advice. I set my password as you advised below, however I'm still having some problems connecting. When I try to connect I get the following error message: Unable to connect to PostgreSQL server: FATAL 1: SetUserId: user 'password=' is not in 'pg_shadow' When connecting I'm using username "postgres" and the password that I used when I updated the pg_shadow file as suggested. Any ideas??? Hi, You could use the following...;-) psql -U postgres template1 UPDATE pg_shadow SET passwd='whatever' WHERE usename='postgres'; I don't believe I've had the pleasure of this problem before...;-) Would you mind showing us your exact command, that you tried to execute to get into your postgresql database? (Maybe a little cut and paste...;-)) Is your PostgreSQL database part of your server? Or at another server... And kindly check this file (I assuming youe got PostgreSQL 7.x) /var/lib/pgsql/data/postmaster.opts.default If you have a "-i" in this file. HTH, Cheers, John Clark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Newbe questions: Setting Passwords
Unable to connect to PostgreSQL server: FATAL 1: SetUserId: user 'password=' is not in 'pg_shadow' Some kind of syntactic confusion at the client end, evidently: the software is taking password= as your username, which I'm pretty sure is not what you intended. 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])
