[GENERAL] How to use postgres 7.0.3 with -F?
Hi, someone gave me the tip to use the -F-flag to improve performance. Since I can't run the postmaster with -F but just postgres how do I do it, if I want to import a big amount of data? Konstantin -- Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: [EMAIL PROTECTED] Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185 "Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres
[GENERAL] Annotatable on-line documentation
(This was: "Re: [GENERAL] Re: PostgreSQL vs Oracle vs DB2 vs MySQL - Which should I use?", but I think a new thread has spun off...) [Tom Lane said ...] I agree we need to work harder on making answers findable outside the mailing lists. Improving the docs, making the mail archives more easily searchable, etc etc. I dunno if an "annotated manual" would help --- I've never used one --- but if people want to try one, it can't hurt. [... to which Bruce Momjian added ...] I will say that the FAQ and my book have visibly reduced the number of questions. When I put something on the FAQ, the questions about that topic just magically go away. I have not used an annotated document either. However, I could see such a beast being used in the development of the documentation in a way that is not dissimilar to the development of the product itself. That is, I see an annotatable set of documentation as similar in nature to the developmental version of the product. After some period of development, some lucky editor(s) would fold the annotations into the document proper, periodically releasing the "stable" version of the docs. Would this lighten the work load on the folk that are currently maintaining the docs? Would it lighten the work load for the core developers? Would it stimulate the development of richer documetation? Would it draw some of the load off the mailing lists? A pilot project may be enlightening. Anyone have experience with setting up/maintaining annotatable on-line documentation? Cheers, Richard Blackwell Programmer/Analyst Simon Fraser University Burnaby, BC Canada __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
Re: [GENERAL] How to use postgres 7.0.3 with -F?
[EMAIL PROTECTED] (Konstantinos Agouros) writes: someone gave me the tip to use the -F-flag to improve performance. Since I can't run the postmaster with -F but just postgres how do I do it, if I want to import a big amount of data? In 7.0.* (but not prior versions) it is safe to run individual backends with -F; other backends will still fsync their changes. So, you can start a backend that's going to do an import with export PGOPTIONS="-F" psql mydb (adjust 'export' command syntax depending on your preferred shell.) libpq passes the value of the environment variable PGOPTIONS over to the backend, and away you go. -dN (debug level) is another backend command- line switch commonly set this way. regards, tom lane
[GENERAL] Number of Connections
Hello, I'm a bit new to postgres. Is there anyway to tell the current number of connections on a database or server? I'm having a connection closing problem and would like to debug it somehow. I know on Sybase you can check a sys table to determine this. Not familiar with how to do this on Postgres. Please advise and thank you.
[GENERAL] Re: Bad book review
On 14 Feb 2001 11:57:47 -0500, Vince Vielhaber wrote: On Wed, 14 Feb 2001, Bruce Momjian wrote: I am not sure how many people have looked at my book on Amazon.com, but I have received my first negative book review: http://www.amazon.com/exec/obidos/ASIN/0201703319/o/qid%3D976592762/sr%3D8-1/ref%3Daps%5Fsr%5Fb%5F1%5F3/104-0116316-8891907 I guess everyone isn't going to like my book. :-) Did you read any of his other reviews? He doesn't strike me as a happy person. Someone musta moved his cheese. His data base server crashed??? Come on now Bruce! We all get a bad review from time to time. Don't lose any sleep. Your next book will be perfect - "Dreamweaver Ultradev application building with Postgresql" =:-` Cheers Tony Grant
[GENERAL] Re: Row ID and auto-increment?
Ok, the type your looking for to auto create a sequence and increment by
one is called SERIAL (an int4 field). And yes, pgsql has oid's. Heres
what your table would look like with serial type's:
CREATE TABLE tablename (
item_idSERIAL,
name VARCHAR(10)
);
The serial type will implicitly create a sequence which defines the
incrememnt to step by, starting vaule, end value and so on. The field
will have a new default value of DEFAULT NEXTVAL('tablename_seq'::text)
or something similar.
If your looking for oid's, they are always there and can be selected as
follows:
SELECT oid,ietm_id,name FROM tablename;
This will list the oid's that were assigned during INSERT into the
table. Keep in mind that oid's are not giving to VIEW's and that when
you dump a db and restore it, the oid's are NOT preserved. So using
oid's in any form on foreign key or table lookup/join is a bad idea!
They should mainly be used when trying to distinguish between similar
values in a table (ie: in case some fields are the same, the oid will
always be unique).
Hope this helps
Dan
Raymond Chui wrote:
If I create a table like
create table tablename (
aNuminteger not null,
namevarchar(10)
);
If I do select * from tablename;
q1. Is there such thing rowid similar to Oracle in PostgreSQL?
q2. How do I make aNum auto increment by 1? Need to write
a trigger? how to write that?
I want to enforce column aNum 0,1,2,.n.
I want to prevent data entry people input 0,1,4,5,8,...n.
Thank you very much in advance!
--Raymond
[GENERAL] statistics
Hi, is there a way to get statistuics from postgres, like the amount of requests it handled, or the amount of connections it had the last 10 min. Just curious so I can link it with Mysql. May pg_statistics is the solution for my problem, but don;t get it atm. ys, -- -- Ramses Smeyers [EMAIL PROTECTED] http://mind.be -- Linux and Open Source Consultant SR8987-NIC RS2000-RIPE RS2-DNSBE
[GENERAL] Row ID and auto-increment?
If I create a table like create table tablename ( aNuminteger not null, namevarchar(10) ); If I do select * from tablename; q1. Is there such thing rowid similar to Oracle in PostgreSQL? q2. How do I make aNum auto increment by 1? Need to write a trigger? how to write that? I want to enforce column aNum 0,1,2,.n. I want to prevent data entry people input 0,1,4,5,8,...n. Thank you very much in advance! --Raymond begin:vcard n:Chui;Raymond tel;fax:(301)713-0963 tel;work:(301)713-0624 Ext. 168 x-mozilla-html:TRUE url:http://members.xoom.com/rchui/ org:NWS, NOAA version:2.1 email;internet:[EMAIL PROTECTED] title:SA, DBA note:ICQ #: 16722494 adr;quoted-printable:;;NOAA, NWS, Office of Hydrology, OH=0D=0A1325 East-West Highway, Room 8112;Silver Spring;MD;20910-3283;U.S.A. x-mozilla-cpt:;-6384 fn:Raymond Chui end:vcard
[GENERAL] order of clauses
create table vals ( x float, y float ); insert into vals values (2,4); insert into vals values (2,2); insert into vals values (2,1); insert into vals values (2,0); select x/y from vals where y0 and x/y1; will give a divide by zero error as A=(y0) and B=(x/y1) can be evaluated in any order (A and B = B and A). I obviously would like (y0) to happen first, but I don't see how this can be achieved.. Any ideas? Cheers, Patrick
[GENERAL] Re: Postgres slowdown on large table joins
Can you EXPLAIN that query and send us the results (the query plan)? That should tell a whole lot. -Mitch - Original Message - From: "Dave Edmondson" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 16, 2001 1:32 PM Subject: Postgres slowdown on large table joins I'm having a problem here. I'm using Postgres 7.0.3 on a FreeBSD 4.2-RELEASE machine... it's a Pentium II/450 w/ 128MB of RAM (not nearly enough, but there'll be an upgrade soon). Anyway, I have a data table, which currently has around 146,000 entries, though it will grow to a few million eventually. There is also config and prefs tables, which have 4-5 rows each. When I execute the following command: SELECT c.unit_id,c.name,c.auxenable,c.bias,c.feedback,c.gain,c.igain, c.mode,c.reverse,c.setpoint,c.switch1,c.switch2,c.timeout, c.valvetype,d.active,d.drive_1_s,d.drive_1_f,d.drive_2_s, d.drive_2_f,d.mval,d.m4val,d.sw1,d.sw2,d.cycle,d.itemp, d.error,d.aval,d.ts,c.ts,p.degree,c.outputa,c.outputb, c.outputc,c.rawtemp FROM config c, data d, prefs p WHERE c.conf_id = '4' AND d.conf_id = c.conf_id AND p.conf_id = c.conf_id ORDER BY d.ts DESC LIMIT 1 ...it takes an astounding 50 seconds to complete, CPU usage goes to about 85% Now, a simple... SELECT * FROM data ORDER BY ts desc LIMIT 1 ...takes about 16-26 seconds - still slw, but not as bad as with the table join. What's really causing the slowdown? ...should I just execute the command differently? I'm trying to get the latest data in all three tables. Once the server has 768MB+ of RAM, is it possible to load the entire table into memory? should speed things up considerably. Thanks, -- David Edmondson [EMAIL PROTECTED] GMU/FA d-(--) s+: a18? C$ UB$ P++ L- E--- W++ N- o K- w-- O? M-(--) V? PS+ PE+ Y? PGP t 5 X R+ tv--! b DI+++ D+ G(--) e* h!+ r++ y+++ ICQ: 79043921 AIM: AbsintheXL #music,#hellven on irc.esper.net
[GENERAL] Postgres slowdown on large table joins
I'm having a problem here. I'm using Postgres 7.0.3 on a FreeBSD 4.2-RELEASE machine... it's a Pentium II/450 w/ 128MB of RAM (not nearly enough, but there'll be an upgrade soon). Anyway, I have a data table, which currently has around 146,000 entries, though it will grow to a few million eventually. There is also config and prefs tables, which have 4-5 rows each. When I execute the following command: SELECT c.unit_id,c.name,c.auxenable,c.bias,c.feedback,c.gain,c.igain, c.mode,c.reverse,c.setpoint,c.switch1,c.switch2,c.timeout, c.valvetype,d.active,d.drive_1_s,d.drive_1_f,d.drive_2_s, d.drive_2_f,d.mval,d.m4val,d.sw1,d.sw2,d.cycle,d.itemp, d.error,d.aval,d.ts,c.ts,p.degree,c.outputa,c.outputb, c.outputc,c.rawtemp FROM config c, data d, prefs p WHERE c.conf_id = '4' AND d.conf_id = c.conf_id AND p.conf_id = c.conf_id ORDER BY d.ts DESC LIMIT 1 ...it takes an astounding 50 seconds to complete, CPU usage goes to about 85% Now, a simple... SELECT * FROM data ORDER BY ts desc LIMIT 1 ...takes about 16-26 seconds - still slw, but not as bad as with the table join. What's really causing the slowdown? ...should I just execute the command differently? I'm trying to get the latest data in all three tables. Once the server has 768MB+ of RAM, is it possible to load the entire table into memory? should speed things up considerably. Thanks, -- David Edmondson [EMAIL PROTECTED] GMU/FA d-(--) s+: a18? C$ UB$ P++ L- E--- W++ N- o K- w-- O? M-(--) V? PS+ PE+ Y? PGP t 5 X R+ tv--! b DI+++ D+ G(--) e* h!+ r++ y+++ ICQ: 79043921 AIM: AbsintheXL #music,#hellven on irc.esper.net
Re: [GENERAL] order of clauses
You didn't mention what version of Postgres, but in 7.1beta, you could do the following (pretty sure on the syntax): SELECT a.x/b.y FROM vals a, (SELECT y FROM vals WHERE y 0) b WHERE (a.x / b.y) 1; In anything else, you could try a view: CREATE VIEW valid_vals AS SELECT y FROM vals WHERE y 0; SELECT a.x/b.y FROM vals a, valid_vals b WHERE (a.x / b.y) 1 Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Wed, 14 Feb 2001, Patrick Welche wrote: create table vals ( x float, y float ); insert into vals values (2,4); insert into vals values (2,2); insert into vals values (2,1); insert into vals values (2,0); select x/y from vals where y0 and x/y1; will give a divide by zero error as A=(y0) and B=(x/y1) can be evaluated in any order (A and B = B and A). I obviously would like (y0) to happen first, but I don't see how this can be achieved.. Any ideas? Cheers, Patrick
Re: [GENERAL] Number of Connections
Hello, I'm a bit new to postgres. Is there anyway to tell the current number of connections on a database or server? I'm having a connection closing problem and would like to debug it somehow. I know on Sybase you can check a sys table to determine this. Not familiar with how to do this on Postgres. I use: ps ax | grep postgres | wc -l Note the value is often one to high because is picks up the grep process. Use ps ax | grep postgres to look at the processes and see what IP are connected, what users, and what the backend is doing (IDLE, SELECT, ..)
[GENERAL] creating assertions in functions
hi. i was wondering how to go about creating an assertion to be used within a pl/sql function. do i put create assertion assertion name check ... before or after "begin"? thanks -- -leon (in the dungeon of EUII @ UC Davis)
Re: [GENERAL] Case insensitive selects?
On Thu, 15 Feb 2001, Michael Fork wrote:
Indexes *can* and *will* be used if you create the appropiate
functional indexes, i.e:
CREATE INDEX idx_table_field_upper ON table(upper(field));
SELECT field FROM table WHERE upper(field) LIKE upper('some string');
Hmmm...I'd hate to have two indexes on every field I query like this, one
case-senstive, one case-insensitve (like the one you create here). Is
there a configuration option or something that will tell pgsql to do
case-insensitive comparisons (kinda like MS SQL Server has)? That could
save us on indexing overhead, since we want all of our WHERE comparisons
to be case-insensitive, anyway.
I should also not that we're also using --with-multibyte and having all of
our databases use Unicode exclusively.
Thanks!
David
[GENERAL] Easy creation of database driven web sites.
Looking for an open source web development tool that supports commercial as well as open source databases? Check out http://www.openbedrock.org sqlconnect table sqlselect "select * from customers" tr tdvar $name/td tdvar $address/td tdvar $phone/td /tr /sqlselect /table
[GENERAL] Is inet printable?
I have tried the function gethostbyname(text), which should return an inet value
or reference. However, it gave me something like unprintable
sql#= select gethostbyname('www.postgres.org');
Anyone got any clue?
Phillip Pan
---
[GENERAL] Question
Title: Question Does this run on Windows NT or 2000.
[GENERAL] Re: Case insensitive selects?
Hmmm...I'd hate to have two indexes on every field I query like this, one
case-senstive, one case-insensitve (like the one you create here). Is
there a configuration option or something that will tell pgsql to do
case-insensitive comparisons (kinda like MS SQL Server has)? That could
save us on indexing overhead, since we want all of our WHERE comparisons
to be case-insensitive, anyway.
If you want all of them to be case insensitive then make the upper ( or
lower() ) index and don't make any case sensitive queries! :-)
Make sure all your queries use upper() or lower() around the field and value
you're comparing and you're golden.. Unless I've misunderstood you, I don't
see the problem..
SELECT * FROM whatever WHERE lower(myfield) = lower('myvalue'); -- and make
your index on lower(myfield)... Viola!
-Mitch
Re: [GENERAL] Case insensitive selects?
On Thu, 15 Feb 2001, Michael Fork wrote:
Indexes *can* and *will* be used if you create the appropiate
functional indexes, i.e:
CREATE INDEX idx_table_field_upper ON table(upper(field));
SELECT field FROM table WHERE upper(field) LIKE upper('some string');
Hmmm...I'd hate to have two indexes on every field I query like this, one
case-senstive, one case-insensitve (like the one you create here). Is
there a configuration option or something that will tell pgsql to do
case-insensitive comparisons (kinda like MS SQL Server has)? That could
save us on indexing overhead, since we want all of our WHERE comparisons
to be case-insensitive, anyway.
I was wondering if we could do case-insensitive index waking by doing
looking for CAR as:
CAR
CAr
CaR
Car
cAR
cAr
caR
car
Basically you look for CAR, then back up in the btree, to CA and look
for r instead of R. I relized the number of tests would exponentially
explode, but isn't it just like btree walking where we back up to test
the lowercase of the letter.
--
Bruce Momjian| http://candle.pha.pa.us
[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] order of clauses
Patrick Welche [EMAIL PROTECTED] writes: select x/y from vals where y0 and x/y1; will give a divide by zero error as A=(y0) and B=(x/y1) can be evaluated in any order (A and B = B and A). I obviously would like (y0) to happen first, but I don't see how this can be achieved.. Any ideas? Of course you can rewrite this particular case to avoid the division, but I suppose you are looking for a more general answer. Consider something like CASE WHEN y 0 THEN x/y 1 ELSE false END I think that right now, the planner gratuitously reverses the order of the WHERE clauses that it's unable to convert to index/join quals, thus your failure. So you could hack around the problem just by switching the two conditions. I've been meaning to try to figure out where the reversal is happening and undo it, however, so this behavior should not be considered to be documented/supported/guaranteed. regards, tom lane
[GENERAL] vacuumdb question
Hi, What exactly does vacuumdb do? In what way does it 'clean' the db? Also what are the best ways to optimise a pg databaseThanks, Jonathan Daniels
[GENERAL] Storing double-byte strings in text fields.
Hello, I am putting together a web site to display a collection of Chinese woodblock prints. I want to be able to store double byte values (that is to say Big5, Unicode etc encoded) in a text field for things such as the artist's name and the title of the print. I have the following questions: Is this possible using a plain vanilla version of Postgres, ie without the multi-lingual support enabled? As I understand it multi-lingual support allows me to store table and field names etc in non-ASCII, but doesn't really affect what goes into the fields. Are programs such as pgdump and the COPY method 8bit clean or will they mess up the text? I have done some quick trials and it all seems OK but I want to be sure before commiting. If the above is not the case will the multi-lingual support fix my problems? I tried it out but had problems with the backend crashing on certain queries. I'd also rather not use it as it will be easier to port my system to other servers if it just needs a plain vanilla install. I am currently using Postgresql 7.0.3 on RedHat 6.2 (x86) and also on YellowDog 1.2 (PPC). The web server is Apache 1.3.12 with PHP 4.0.x. Thanks, Edmund. -- *** *** Edmund von der Burg *** [EMAIL PROTECTED] *** ***
Re: [GENERAL] order of clauses
: SELECT a.x/b.y FROM vals a, (SELECT y FROM vals WHERE y 0) b WHERE (a.x : / b.y) 1; How much of a performance hit is there when using a select in the FROM clause? Is it even noticeable? How much better is it to create a static view? -Dan
[GENERAL] initdb: pg_encoding failed
Hi. I have a problem getting postgresql to work on my Debian 2.2 system, and i hope someone in this group can help me. I have successfully compiled, installed and used postgresql 7.02 on a RedHat6.2 system. The problem is the same for postgresql 7.02 and 7.03 on two different computers, both running Debian 2.2, so think its either the distro or me(most likly me, but i cant figure out what i'm doing wrong)! What i wrote on all 4 tries: ./configure --enable-locale --enable-multibyte --with-odbc make make install and everything seemed to go smooth. The next thing i tried was: /usr/local/pgsql/bin/initdb -D /var/pgsql/data then i got this error: --- initdb: pg_encoding failed Perhaps you did not configure PostgreSQL for multibyte support or the program was not successfully installed. -- any suggestions??? Thanks [Bad-Knees]
[GENERAL] How to use gethostbyname()
In psql, gethostbyname() is defined as taking a text parameter and return an
inet type value or reference.
However, if you do
psql=# select gethostbyname('www.postgres.org');
gethostbyname
---
unprintable
(1 row)
the inet address will be unprintable.
Has anyone got any idea?
Phillip Pan
---
[GENERAL] Re: Fwd: Stalled post to pgsql-general
help Hi, It would work fine to have a web interface in case the application could be developed with that... In fact, we had started working on the project with PG and Java Servlets et al... However, we ran into problems after a month of designing and actual implementaiton work was over only because the browser / HTML is too static... a. Extensive coding is reqd. for datatypes validations on all forms and reports for filtering, etc. b. Added functionality would result in havoc for adding features like Grids as in MS-Access like forms-subforms and browsing thru such records in order c. Non-use of client-side processing capacity i.e., under-utilisation and over-burdening the server which would in case of client-server be just handling data which would make it much more reliable d. The client would not shell out a single penny when I tell him that two servers would be ideal for him... One for handling data the other for handling http requests e. Moving away from Windows is impossible fof him... He has not heard that there is any other OS other than Windows f. The client is not a jackass... I cant conn him... He knows what all he wants in his software g. I am keen on PG/Interbase h. I KNOW YOU CAN HELP... SO, KINDLY DO i. This MS-Office interface you are talking about... That would involve a lot of wizardry/programming... Thanx Kapil --- Mark Cowlishaw [EMAIL PROTECTED] wrote: I was asked to develop a reasonably large database application for my company intranet. My boss comes from a heavily windows background and wanted it all developed in MS Access etc but we're basically a UNIX house and I knew that would cause havoc with the software developers around here (and myself) who much prefer the UNIX way of doing things (and oftentimes run Linux as their Desktop OS). What did I do? I developed the application using a PostgreSQL backend with a web interface (I used Perl for rapid development, but would have preferred Java Servlets), and then used the ODBC drivers to connect Access to allow MSOffice automation and custom queries within Access so the boss can create his custom reports if he likes. Now I have the best of all worlds: Reliable backend (PostgreSQL), a platform neutral frontend (Web Interface) and Office automation through ODBC. Works for me! - Original Message - From: "The Hermit Hacker" [EMAIL PROTECTED] To: "Kapil Tilwani" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, February 16, 2001 4:54 AM Subject: Re: Fwd: Stalled post to pgsql-general Have you looked at the available ODBC drivers? On Thu, 15 Feb 2001, [iso-8859-1] Kapil Tilwani wrote: help Hi, I am a fresh graduate from India in commerce and do know a little about programming using VB, Java and Oracle... I, along with 5 more friends, have received a small-time project (small for u guys, major for us) for developing a small application We are considering one of the foll.options A. Totally Linux a. Only option is using Swings and Postgres/MySQL Though this would technically be the best option... But, in India, it is like only Windows is the well-known client-side operating system B. Norma for Indian a. Linux Server and Windows client b. Windows Server and Windows client Here, my problem is a very simple one... I need to put my Postgres on a Windows server (this is optional... I can convince my project-giver to go in for a Linux server) ... However, I have a problem in that at least my clients would all be Windows-based... So, how do I install the clients... Can I get some kind of wizarded- .exe file for that... and where? Also, how do I make the connection between the Windows Server and the client... How do I specify... please could u send details rather than a quick overview ... It would be highly appreciative Lastly, is it possible to have a Linux as a server and the Windows clients interacting with this server... and pleaase plz tell me how to do that... If possible, please be descriptive and help me to do that... All I can say is that I will pay back to the community all the favours and more... PLEASE HELP... YOU CAN NOT REALLY MAKE OUT HOW FRUSTRATING THIS IS BECOMING Also, could u just give me yr unofficial comments on Interbase... Is it good enough? ...or MySQL for that matter? Are there any free downloadable development packages for Postgres and Java... Something better than Forte for Java Thanx Kapil P.S. : Where am I reqd. to post my queries related to Postgres, Java , Interbase... --- The Hermit Hacker [EMAIL PROTECTED] wrote: could you resend the original? I tried to read through what you tried to post, and couldn't get past line one before it got all jumbled :( On Wed, 14 Feb 2001,
[GENERAL] PostgreSQL Benchmark
Hello!! I need demostrate that PostgreSQL is a great RDBMS for my undergraduate project, because this, Does somebody has a bechmark (or similar document) between Postgres and others DB (commercial DB's, principally) Thanks in advance!!
Re: [GENERAL] Case insensitive selects?
On Fri, 16 Feb 2001, Bruce Momjian wrote: Yes, our CREATE INDEX lower(col) already does that, but you do have to use lower(col) when doing the query. Right, that's what I'm suggesting a configuration that automates the lower(col) bit in CREATE INDEX and that automates the lower(col) in queries. BTW, I've run into some snags with CREATE INDEX lower(col). First it wouldn't work because my col was varchar (fixec by creating a new function) and then because I tried to combine columns: CREATE UNIQUE INDEX idx_name ON server(lower(col1), col2); Ewe, that is a tough one. We don't support multi-column functional indexes, do we? -- Bruce Momjian| http://candle.pha.pa.us [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] Postgres Benchmark
Hello!! I need demostrate that PostgreSQL is a great RDBMS for my undergraduate project, because this, Does somebody has a bechmark (or similar document) between Postgres and others DB (commercial DB's, principally)? Thanks in advance!!
Re: [GENERAL] Annotatable on-line documentation
You should have a look at the wiki stuff http://c2.com/cgi/wiki?WikiWikiWeb Dave - Original Message - From: "Richard" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 16, 2001 12:40 PM Subject: [GENERAL] Annotatable on-line documentation (This was: "Re: [GENERAL] Re: PostgreSQL vs Oracle vs DB2 vs MySQL - Which should I use?", but I think a new thread has spun off...) [Tom Lane said ...] I agree we need to work harder on making answers findable outside the mailing lists. Improving the docs, making the mail archives more easily searchable, etc etc. I dunno if an "annotated manual" would help --- I've never used one --- but if people want to try one, it can't hurt. [... to which Bruce Momjian added ...] I will say that the FAQ and my book have visibly reduced the number of questions. When I put something on the FAQ, the questions about that topic just magically go away. I have not used an annotated document either. However, I could see such a beast being used in the development of the documentation in a way that is not dissimilar to the development of the product itself. That is, I see an annotatable set of documentation as similar in nature to the developmental version of the product. After some period of development, some lucky editor(s) would fold the annotations into the document proper, periodically releasing the "stable" version of the docs. Would this lighten the work load on the folk that are currently maintaining the docs? Would it lighten the work load for the core developers? Would it stimulate the development of richer documetation? Would it draw some of the load off the mailing lists? A pilot project may be enlightening. Anyone have experience with setting up/maintaining annotatable on-line documentation? Cheers, Richard Blackwell Programmer/Analyst Simon Fraser University Burnaby, BC Canada __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
Re: [GENERAL] Re: PostgreSQL vs Oracle vs DB2 vs MySQL - Whichshould I use?
... I wonder whether this is an inbuilt, unavoidable problem with free software projects once they reach a certain level of popularity. Funny you should mention PHP. I talked to Rasmus about the email volume when I first met him in the fall. He said the volume of email is so great that just reading the subject lines takes a long time. We aren't there yet, but we are heading in that direction. Yes, the shape of the curve is pretty clear --- it's already not possible for the key developers to respond to everything, and that'll get worse. We have to start thinking about ways to spread out the load better. One idea is encouraging people to make local mailing lists all over the world. That will be easier to read/write mails for non-English speakers and will decrease the traffic in the main mailing list. Actually I have been running a mailing list in Japan for years and now it has +4000 subscribers. I believe this kind of thing has been already done in the world (maybe Japan, Russia ...?), but I think we should encourage them more, like having pointers to such lists on the pgsql web site. -- Tatsuo Ishii
[GENERAL] Re: PostgreSQL Benchmark
The license agreements of many commercial DBs forbid licensees from publishing benchmarks of their software. What is your undergraduate project? More details could help, anyway most of the popular RDBMS features are in Postgresql. I'd figure Postgresql is one of the best RDBMSes to use for an undergraduate project. For one, you and your uni don't have to waste time on figuring out how much you have to pay the commercial vendor. No need to send info to the RDBMS salesperson on how many CPU's you are going to use, how many users, the megahertz of your CPU, how much money is in your bank account and so on. And especially in your case if you need a special feature you can even start a miniproject to add it to Postgresql - the source code is available, the documentation is there, and there's a developer mailing list too. I doubt you'd get that from most commercial RDBMSes. If you add a real cool feature, I'm sure it'll look impressive in your project report, and resum for that matter :). Cheerio, Link. At 12:13 PM 2/16/01 -0500, Jreniz wrote: Hello!! I need demostrate that PostgreSQL is a great RDBMS for my undergraduate project, because this, Does somebody has a bechmark (or similar document) between Postgres and others DB (commercial DB's, principally) Thanks in advance!!
Re: [GENERAL] Rserv question or docs?
I'm willing to do some writing if someone will explain things to me. I've pretty much disected all the sql used by Rserv.pm to see what it does so I've got a decent head start. The two questions that are holding me up at this point are: 1. which column do you specify for setting up the replication on a table? So far I've been using the primary key but I'm not sure what to do in the case where there's a composite primary key. 2. how do I figure out what triggers are currently bound to a given table? I'm having a problem dropping all the rserv stuff and recreating it without starting with a fresh database. I'm working on scripts to be able to swap the master and slave relationship. I currently drop each table and drop each function but when they are recreated I get errors like this on inserts DBD::Pg::db do failed: ERROR: fmgr_info: function 68437: cache lookup failed ERROR: fmgr_info: function 68437: cache lookup failed which makes me think that the trigger is calling a function that doesn't exist. Thanks, Alex. On Fri, 16 Feb 2001, Bruce Momjian wrote: Hi is there any documentation for rserv other than the readme? I've got it working and I'm generally understanding what's going on by reading the code and observing the changes to the various _rserv_ tables but I would feel much more comfortable if I could read or someone could explain exactly what is supposed to get logged by the trigger and exactly how the Replicate script is supposed to copy the data using the _rserv_log_ to the secondary server. FYI, this is a 7.1 beta item. We certainly need something more substantial than the README, but no one has offered to write it. -- Bruce Momjian| http://candle.pha.pa.us [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] Storing double-byte strings in text fields.
I am putting together a web site to display a collection of Chinese woodblock prints. I want to be able to store double byte values (that is to say Big5, Unicode etc encoded) in a text field for things such as the artist's name and the title of the print. I have the following questions: Is this possible using a plain vanilla version of Postgres, ie without the multi-lingual support enabled? As I understand it multi-lingual support allows me to store table and field names etc in non-ASCII, but doesn't really affect what goes into the fields. As already Tom mentioned, your RPMS based Linux boxes already have PostgreSQL multi-byte capability enabled. Are programs such as pgdump and the COPY method 8bit clean or will they mess up the text? I have done some quick trials and it all seems OK but I want to be sure before commiting. I don't see any reason that copy or pg_dump is not 8bit clean. If the above is not the case will the multi-lingual support fix my problems? I tried it out but had problems with the backend crashing on certain queries. I'd also rather not use it as it will be easier to port my system to other servers if it just needs a plain vanilla install. You said you use Big5. That might be the problem. PostgreSQL does not accept any encoding conficting with ASCII. Certain Big5 characters include such that second bytes in the ASCII range. In this case you need to create a database with EUC_TW encoding and set the environment varible "PGCLIENTENCODING" to BIG5 in your frontend. This will force the backend to convert Big5 -- EUC_TW automatically. Oh, you use PHP4? then you need to set the environment varible before starting up Apache if you use PHP4 as a module. Also I suspect you might have trouble with PHP4. It has a capability called "magic quote", that adds an escape character (\) to the second byte of Big5 if it's a meta character. You need to disable it otherwise PostgreSQL will be confused. In summary you must be very carefull to use Big5 especially with PHP. Talking about Unicode, it is safe as long as UTF-8 encoding. UCS-2/4 cannot be used with PostgreSQL. PostgreSQL 7.1 will have the ability to do an automatic code conversion between UTF-8 and other encodings including Big5. This might be a good news for you. Another problems I have seen so far with chinese character sets are sometimes data produced by chinese applications are badly broken. Actually PostgreSQL is not so robust against such broken multi-byte strings. I suspect this may be the reason of the backend crash you had if above are not apply. I don't know. I am currently using Postgresql 7.0.3 on RedHat 6.2 (x86) and also on YellowDog 1.2 (PPC). The web server is Apache 1.3.12 with PHP 4.0.x. -- Tatsuo Ishii
Re: [GENERAL] How to use postgres 7.0.3 with -F?
In [EMAIL PROTECTED] [EMAIL PROTECTED] (Tom Lane) writes:
[EMAIL PROTECTED] (Konstantinos Agouros) writes:
someone gave me the tip to use the -F-flag to improve performance. Since
I can't run the postmaster with -F but just postgres how do I do it, if I
want to import a big amount of data?
In 7.0.* (but not prior versions) it is safe to run individual backends
with -F; other backends will still fsync their changes. So, you can
start a backend that's going to do an import with
export PGOPTIONS="-F"
psql mydb
(adjust 'export' command syntax depending on your preferred shell.)
libpq passes the value of the environment variable PGOPTIONS over to the
backend, and away you go. -dN (debug level) is another backend command-
line switch commonly set this way.
A O thanks for the help I was confused. Does this also work that way, if
I use it from DBI::Pg from perl? Like setting $ENV{'PGOPTIONS'}
Konstantin
regards, tom lane
--
Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: [EMAIL PROTECTED]
Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185
"Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres
Re: [GENERAL] How to use postgres 7.0.3 with -F?
[EMAIL PROTECTED] (Konstantinos Agouros) writes:
export PGOPTIONS="-F"
psql mydb
A O thanks for the help I was confused. Does this also work
that way, if I use it from DBI::Pg from perl? Like setting
$ENV{'PGOPTIONS'}
Yeah, I think that should work if you do it before opening a connection,
but a more straightforward way is to set options=-F in the
DBI-connect() command ...
regards, tom lane
Re: [GENERAL] strange query results
HI tom A few days back i had bugged this list about the seemingly impossible select queries results.. ##RECAP## select distinct site_section as "distinct site sections" from exhibit_distributions ; distinct site sections ARCHIVED ARTETC CALENDAR GALLERY POSTCARD (5 rows) select site_section, count(*) from exhibit_distributions group by site_section; site_section | count --+--- | 352 |45 | 1 | 166 | 2 ##RECAP## On going thru the flat files which we used to uplaod the database we noticed a few fields had '' characters to signify '.. This was creating problems in some jdbc queries. One changing them to single ' and reloading all the data, the problem simply disappered.. Thanks for the help Anand Raman On Tue, Feb 13, 2001 at 10:20:53AM -0500, Tom Lane wrote: Anand Raman [EMAIL PROTECTED] writes: The table description is as follows Hmm ... nothing obviously funny here. Is there anything unusual about the history of this table? (For example, were site_section or any other columns added via ALTER TABLE, rather than being there all along?) regards, tom lane
[GENERAL] Are partial indicies supported?
Hi, In the documentation there is this page about partial indicies: http://postgresql.planetmirror.com/devel-corner/docs/user/partial-index.htm From my reading of that I would guess they are supported but since they aren't mentioned anywhere else, I guess they're not. Can somone clarify the situation please? -- Martijn van Oosterhout [EMAIL PROTECTED] http://cupid.suninternet.com/~kleptog/
[GENERAL] Re: order of clauses
Are you referring to short circuit? That's a language feature, isn't it? I
didn't think it had anything to do with the compiler (I know C and a few
other languages do it). Anyway, I could be wrong.. Seems that could break a
lot of code if the programmer relies on short circuit in some conditional
statements.
if ( whatever() OR something() ) {
blah();
}
-- if "whatever" evaluates to true, then "something" isn't executed (the
whole statement is true if one is true)...
This really only comes into play when you're comparing the values returned
by something (a method, function, etc), if you're just looking at boolean
variable I guess it doesn't matter.
-Mitch
- Original Message -
From: "Steve Wolfe" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, February 16, 2001 3:10 PM
Subject: Re: order of clauses
will give a divide by zero error as A=(y0) and B=(x/y1) can be
evaluated in
any order (A and B = B and A). I obviously would like (y0) to happen
first,
but I don't see how this can be achieved.. Any ideas?
I have one idea that would be nifty to implement. In some compilers,
you
can turn off complete boolean checking. As soon as any part of an
expression will invalidate the expression, it stops evaluating all of it.
That can help you avoid division by zero, and keeps you from evaluating
parts of the expression that don't matter. It sounds like a good idea, at
least to an ignoramus like me. : )
steve
Re: [GENERAL] order of clauses
will give a divide by zero error as A=(y0) and B=(x/y1) can be evaluated in any order (A and B = B and A). I obviously would like (y0) to happen first, but I don't see how this can be achieved.. Any ideas? I have one idea that would be nifty to implement. In some compilers, you can turn off complete boolean checking. As soon as any part of an expression will invalidate the expression, it stops evaluating all of it. That can help you avoid division by zero, and keeps you from evaluating parts of the expression that don't matter. It sounds like a good idea, at least to an ignoramus like me. : ) steve
Re: [GENERAL] Re: [ANNOUNCE] Request for speakers at O'Reilly conference
Bruce Momjian wrote: Interesting. I think GIST itself may be too specific for a talk. However, type extensibility would be very interesting. As it turns out, there are just two am's one will most likely build new types upon: btree and GiST. A useful tutorial should include examples of both, explain the interface, and probably offer the audience help in solving their own tasks. But if I were doing that (chances are I will -- I am going to submit a proposal tomorrow), I would start with extensibility in general -- because, as I have recently discovered, it is either not at all obvious to people that one can come and add their own code to the server, or the difficulty of doing so is perceived to be too high. Two engineers next door stopped by the other day and asked me to show how I do it. I had one of them take a glance at the manual and build a working library with a string concatenator from the example. That took them 15 minutes or so, and their final reaction was, "This son of a dog knew about it for years and didn't tell us!". Which, of course, wasn't true: I kept telling everyone. Then the accusation was, "You didn't tell enough -- you failed to explain how simple it was!" The disbelief was so great that they took the code, went home, ran it there and were even more surprised because it still worked. It must have been yet another RTFM case, but we as a group clearly ought to be doing more tutorials. The issue is that a tutorial on it will provide free air travel, while a presentation does not. That probably indicates the futility of speech as a communication medium. --Gene
[GENERAL] RE: Question
Now you know the answer; for some resources of how to do it try taking a look at: http://people.freebsd.org/~kevlo/postgres/portNT.html It is pretty similar to the official windows install docs: http://www.postgresql.org/docs/faq-mswin Or, for a more automated install check out: http://208.160.255.143/pgsql/ There are more, but I seem to have misplaced them. Note that the last site is running on NT right now... Sam -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Stephan Szabo Sent: Friday, February 16, 2001 3:24 PM To: Angelo DiSanto Cc: [EMAIL PROTECTED] Subject: Re: Question You are able to run the server on NT/2000 under cygwin (although there are some issues under 1.1.8 I believe). Does this run on Windows NT or 2000.
Re: [GENERAL] How to use postgres 7.0.3 with -F?
Tom Lane [EMAIL PROTECTED] writes:
[EMAIL PROTECTED] (Konstantinos Agouros) writes:
export PGOPTIONS="-F"
psql mydb
A O thanks for the help I was confused. Does this also work
that way, if I use it from DBI::Pg from perl? Like setting
$ENV{'PGOPTIONS'}
Yeah, I think that should work if you do it before opening a connection,
but a more straightforward way is to set options=-F in the
DBI-connect() command ...
I think Tom's suggestion is the way to go--I found a long time ago
that Perl only puts the %ENV hash into the environment when it's about
to call exec(). I was using DBD::Informix and found that my
INFORMIXDIR variable, set in the Perl script, was not making it
through to the Informix libraries (since no separate program was being
called). I had to run the Perl script from a wrapper shell script
that set INFORMIXDIR...
This may have been fixed by now, though, it was five years ago or so.
-Doug
Re: [ADMIN] Re: [GENERAL] what means INSERT xxx yyy ?
I believe that first number is oid. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Thu, 15 Feb 2001, Richard Huxton wrote: From: "Jean-Arthur Silve" [EMAIL PROTECTED] Does anyone knows what means, after an INSERT for exemple the message : INSERT 19331808 1 What the meaning of the two numbers ? Sorry - don't know what the first number is - I'm getting 0 here on testing. The second is the number of rows inserted. - Richard Huxton
Re: [GENERAL] Are partial indicies supported?
Martijn van Oosterhout [EMAIL PROTECTED] writes: In the documentation there is this page about partial indicies: http://postgresql.planetmirror.com/devel-corner/docs/user/partial-index.htm From my reading of that I would guess they are supported but since they aren't mentioned anywhere else, I guess they're not. Can somone clarify the situation please? There is no provision in the CREATE INDEX grammar for a partial index condition clause. Some digging in the CVS archives shows that this was true clear back to Postgres95, and that Postgres 4.2 was the last release that accepted a condition clause. I have no idea why Andrew and Jolly removed it --- they certainly didn't hesitate to support Postgres 4.2's other non-ANSI-SQL extensions to CREATE INDEX. There is still a substantial amount of code in the planner and executor to support partial indexes, however. Since it hasn't been exercised in six or seven years, it's doubtless suffering bit rot ... but it's there. An optimistic view is that a couple more lines of code in gram.y and some vigorous dust-sweeping would resurrect the feature. A pessimistic view is that Andrew and Jolly disabled the feature because they knew that it was fatally broken for some undocumented, now-forgotten reason. The truth is probably somewhere in between. I'd like to see the feature brought back, myself, but I dunno when it will get to the top of someone's to-do queue. regards, tom lane
