[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 y>0 and x/y>1; will give a divide by zero error as A=(y>0) and B=(x/y>1) can be evaluated in any order (A and B = B and A). I obviously would like (y>0) 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
Well, it doesn't solve the ordering question, but you could use a where something like this I guess: where y>0 and (x/(case when y=0 then 1 else y end))>1 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 y>0 and x/y>1; > > will give a divide by zero error as A=(y>0) and B=(x/y>1) can be evaluated in > any order (A and B = B and A). I obviously would like (y>0) to happen first, > but I don't see how this can be achieved.. Any ideas?
[GENERAL] SPI_finish()
Sorry for the lack of description, but here goes. I have a function which runs properly, causes no errors until I call SPI_Finish(). Upon that call the backend closes (not what I want). If I remove that line (leaving in the SPI_Connect() statement) it runs fine, but issues numerous warnings about connections already having been established. Is there a known way to make SPI_Finish() crash that I could have run into? Function consists of a couple selects and a couple inserts. Of course, removing both SPI_Connect() doesn't allow the queries to be performed. -- Rod Taylor There are always four sides to every story: your side, their side, the truth, and what really happened. BEGIN:VCARD VERSION:2.1 N:Taylor;Rod;B FN:Taylor, Rod B ORG:BarChord Entertainment Inc.;System Operation and Development TITLE:Chief Technical Officer ADR;WORK:;;;Toronto;Ontario;;Canada LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Toronto, Ontario=0D=0ACanada X-WAB-GENDER:2 URL: URL:http://www.barchord.com BDAY:19790401 EMAIL;INTERNET:[EMAIL PROTECTED] EMAIL;PREF;INTERNET:[EMAIL PROTECTED] EMAIL;INTERNET:[EMAIL PROTECTED] REV:20010216T190107Z END:VCARD
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 y>0 and x/y>1; > > will give a divide by zero error as A=(y>0) and B=(x/y>1) can be evaluated in > any order (A and B = B and A). I obviously would like (y>0) 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 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
[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
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 y>0 and x/y>1; > will give a divide by zero error as A=(y>0) and B=(x/y>1) can be evaluated in > any order (A and B = B and A). I obviously would like (y>0) 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
---
(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... > > > > >
[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=(y>0) and B=(x/y>1) can be
> evaluated in
> > > any order (A and B = B and A). I obviously would like (y>0) 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=(y>0) and B=(x/y>1) can be evaluated in > > any order (A and B = B and A). I obviously would like (y>0) 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
