[GENERAL] How to determine field names in a trigger?
Hi all, I'm creating a centralised table to keep a log of changes in other tables. In thinking about the PL/pgSQL trigger to write and attach to the monitored tables (probably a row level AFTER trigger), I can see two approaches: a) Write a separate PL/pgSQL function for each table, with the hard coded field names in the function. or b) Write one PL/pgSQL function that can be used in the triggers for all of the monitored tables. It sounds like b) would be most time effective to write and maintain, but in looking through the PG docs I haven't seen anything that says how to determine the field names in the OLD nor NEW records, nor how many fields there are: http://www.postgresql.org/docs/7.4/static/plpgsql-trigger.html For example, let's say I have two tables. One is called table_a, and has two columns, and the other is table_b and has three columns. The advantage of having the PL/pgSQL trigger knowing the number of fields and their names in the OLD or NEW rows would be in being able to do this: (pseudo code) LOOP -- If we've processed all the fields, then exit IF i > OLD.number_of_fields EXIT; END IF; -- Check if the next field was changed, and if so, record it IF OLD.nextfield <> NEW.nextfield INSERT INTO log_table (table, field, old_val, new_val) VALUES (TG_RELNAME, nextfield, OLD.nextfield, NEW.nextfield); END IF; -- Increment the loop counter i := i + 1; END LOOP Are there any way to do this kind of thing for triggers with PL/pgSQL at present? Regards and best wishes, Justin Clift ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Looking for dependent object DROP and CREATE scripts
I'm looking for a SELECT that I could add a WHERE clause to and get all the CREATE (and seperately, DROP) statements that I need to rebuild dependent objects before I make changes to my tables. For instance, I have a column datatype I want to change in a table. In order to do this I need to drop all the 'first tier' views that depend on it and then re-create them when I'm done, plus I need the 'second tier' views and rules that that depend on the 'first tier' views etc... So if I could just run a couply SELECTs and save the results as .sql scripts I could run, I'd be all set. Rarely the CREATE script would need to be edited to run without error, but it still would be a huge time saver. So essentially I'm looking for two SELECTs (DROP and CREATE) that would hand me the scripts to do this. (Or is there another way?) Just so you don't think I didn't try to figure this out already, I've tried to work with the pg_depend table and this is what I have so far. Read on to see where I'm stuck. Select distinct pg_depend.objid, desc1.relname AS desca, pg_depend.refobjid, desc2.relname AS descb, pg_depend.deptype from pg_depend left join (select pg_class.oid, pg_class.relname from pg_class UNION select pg_rewrite.ev_class, pg_rewrite.rulename from pg_rewrite UNION select pg_rewrite.oid, pg_rewrite.rulename from pg_rewrite UNION select pg_namespace.oid, pg_namespace.nspname from pg_namespace UNION select pg_constraint.oid, pg_constraint.conname from pg_constraint UNION select pg_type.oid, pg_type.typname from pg_type UNION select pg_attrdef.oid, pg_attrdef.adsrc from pg_attrdef) desc1 on pg_depend.objid = desc1.oid left join (select pg_class.oid, pg_class.relname from pg_class UNION select pg_rewrite.ev_class, pg_rewrite.rulename from pg_rewrite UNION select pg_rewrite.oid, pg_rewrite.rulename from pg_rewrite UNION select pg_namespace.oid, pg_namespace.nspname from pg_namespace UNION select pg_constraint.oid, pg_constraint.conname from pg_constraint UNION select pg_type.oid, pg_type.typname from pg_type UNION select pg_attrdef.oid, pg_attrdef.adsrc from pg_attrdef) desc2 on pg_depend.refobjid = desc2.oid where deptype <> 'p' This gives me the table I'm looking for in column DescB, hooked to its dependent rules in column DescA, but I haven't been able to get dependent views to show up at all. In fact views that depend on views don't show up either. Does pg_depend not have records for dependent views? If not, how is it done? --- justin tocci Fort Wayne, IN
[GENERAL] INSERT RULE QUERY ORDER
When my RULE takes the form of: CREATE RULE name AS ON INSERT TO table DO INSTEAD ( UPDATE query ; INSERT query) ; The INSERT query doesn't fire and there is no error. Putting the INSERT first allows them to both fire. Can anyone tell me why? I think it has something to do with *NEW* and *OLD* being initialized differently for an UPDATE than for an INSERT. I'd like to know what's going on so I can be confident I'm writing my rules correctly. Thanks all. --- jtocci Fort Wayne, IN
Re: [GENERAL] INSERT RULE QUERY ORDER
You are correct Tom, vtquotehistory is a view of tquotehistory, and I am
trying to log in this example. Sorry I didn't point that out. The view is a
straight view with no WHERE clause or criteria of any kind. The UPDATE does
cause the OLD record to become updated to whatever changes have been set as
needed in the NEW record. In that sense, the OLD record is discarded, but my
code sometimes assumes the keyword OLD is still a valid reference even after
an UPDATE. Is that my problem?
ARGGHH!!! I just re-read 9.2.1. Read Committed Isolation Level and I am
screwed! It was my understanding that all the queries in parenthesis in a
RULE were evaluated within a transaction, and that that meant that all
queries saw the same snapshot of the database and changes were committed all
at once at the end. I see now that queries within a transaction are only
ever free from seeing the COMMITs of _other_ transactions, and even that is
only for SERIALIZABLE transactions.
I've got over twenty rules with multiple updates within them that could be
affecting each other in ways I haven't evaluated. I've got my work cut out
for me. You're a lifesaver Tom, thanks.
---
jtocci
Fort Wayne, IN
PS - I can't abandon my beautiful rules for triggers just yet :-) With only
five pages of code, all rules, I've built a replacement for the DOS
application we use (soon to be 'used') to keep track of inventory,
shipping/receiving, purchasing and manufacturing('build' from a bill of
materials).
---
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Hm. Am I right in supposing that vtquotehistory is a view on
tquotehistory? Does the UPDATE cause the row that was visible in the
view to be no longer visible in the view (or at least not matched by the
constraints on the original UPDATE command)? If so, that's your problem
--- the "old" references in the INSERT will no longer find any matching
row in the view.
If your goal is to log operations on tquotehistory, my recommendation is
to forget about views and rules and just use a trigger on tquotehistory.
Triggers are *way* easier to understand, even if the notation looks
worse.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread
Josh Berkus wrote: Matt, The quality of the material is very good, so please don't get me wrong, I just think it's hard to find stuff. Both PHP and MySQL have well laid out docs, with PHP being the better of the two. I certainly agree ... one of my goals (shared with some other people) is to eventually migrate all of the *accessory* documentation (techdocs, etc.) to a searchable system that's easy for non-programmers to contribute to and edit (i.e. SGML and CVS not required). Yep. The present Techdocs site is kind of unmaintained, and the Plone area isn't being worked on either presently (lack of time). Finally got Bricolage installed on a system here at work to play around with. Reckon Josh'll be interested in that... :-) Regards and best wishes, Justin Clift Item #87 on Josh's ToDo list ... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Performance question (stripped down the problem)
Hi Andreas, I'm running PostgreSQL 7.1.3 here on a PC with nearly a gig of ram, and running Linux Mandrake 8.0 First thing I did was to increase the amount of shared memory and stuff which Linux allows things to use : echo "kernel.shmall = 134217728" >> /etc/sysctl.conf echo "kernel.shmmax = 134217728" >> /etc/sysctl.conf For my system, that'll raise the shared memory limits to 128MB at system boot time. btw, the "134217728" figure = 128MB (128 * 1024 * 1024) Then I changed the limits for the running system (so no reboot is necessary) : echo 134217728 > /proc/sys/kernel/shmall echo 134217728 > /proc/sys/kernel/shmmax Then adjusted the postgresql.conf file with these values : sort_mem = 32768 shared_buffers = 220 Now, that's a bunch of shared_buffers, but at the same time I also raised the max_connections to 110. This seems to have dropped my execution times, but I haven't seriously gotten around to tuning this system. The key thing I think you've missed is to update the shared memory, etc. More info about it can be found at : http://www.postgresql.org/idocs/index.php?kernel-resources.html Bruce Momjian also put together some information about optimising things with PostgreSQL at : http://www.ca.postgresql.org/docs/hw_performance/ If you want to be able to benchmark things on your system, I use the "Open Source Database Benchmark" (Linux only at present), running the latest CVS version of it, and also tweaked to not use hash indices. A tarball of working source code is available at : http://techdocs.postgresql.org/techdocs/perftuningfigures.php Hope this is of assistance Andreas. Regards and best wishes, Justin Clift "Tille, Andreas" wrote: > > On Thu, 20 Sep 2001, Justin Clift wrote: > > > Sorry, I haven't seen the history of this thread. One question which > > might be relevant is, have you adjusted the postgresql.conf file from > > the default memory settings to be something better? > I adjusted two parameters: > > shared_buffers = 2048 > (When I tried 4096 I´ve got a connection error. Don´t know what this > means, but anyway increasing of this value did not changed anything.) > > sort_mem = 2048 > (After increasing this value (from default 512) to 1024 I got an > increase in speed from 20s to 18s - not much but better than nothing. > Further increase to 2048 did not change anything further so I stopped > here.) > > > If these are the times you're getting from a default configuration, you > > might be able to get far better results by doing performance tuning of > > PostgreSQL and/or the server. > Any other values which might help here? > > Kind regards > >Andreas. > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Performance question (stripped down the problem)
Hi Andreas, Sorry, I haven't seen the history of this thread. One question which might be relevant is, have you adjusted the postgresql.conf file from the default memory settings to be something better? If these are the times you're getting from a default configuration, you might be able to get far better results by doing performance tuning of PostgreSQL and/or the server. What do you think? Regards and best wishes, Justin Clift "Tille, Andreas" wrote: > > On Thu, 20 Sep 2001, Herbert Liechti wrote: > > > I tried it. See my actions below. The main performance boost is > > reached by creating an index and disabling the sequential scan: > Thanks. I tried this and it helps in dead (see below). > > > --- > > create index ix_1 on hauptdaten_fall(meldekategorie); > I did so before for in other tests. > > --- > > > > Same statement > > > > real0m18.259s > > user0m0.020s > > sys 0m0.010s > The same on my machine: > > real0m18.128s > user0m0.070s > sys 0m0.010s > > > --- > > now disable seqscan: > > --- > > time psql tt < > set enable_seqscan = off; > > SELECT Hauptdaten_Fall.MeldeKategorie, Count(Hauptdaten_Fall.ID) AS Anz > > FROM Hauptdaten_Fall WHERE Hauptdaten_Fall.IstAktuell = 20 GROUP BY > > Hauptdaten_Fall.MeldeKategorie ORDER BY Hauptdaten_Fall.MeldeKategorie; > > END > > > > > > real0m3.701s > > user0m0.010s > > sys 0m0.000s > > real0m5.905s > user0m0.060s > sys 0m0.030s > > I have no real explanation why I have the same result in the first > case but significant more time for the second. but it helps for the > first step. > > On the other hand if I test my *real* database: > > real0m20.539s > user0m0.060s > sys 0m0.060s > > and with "set enable_seqscan = off;" > > real0m10.133s > user0m0.040s > sys 0m0.020s > > I get in fact an increase of speed by factor 2, but anyway it is even > far to slow for our application. If I start a slightly more complex > query (not to mention that we are far from the amount of data we will > get after a year, I get the following stats: > > -- default -- set enable_seqscan = off; MS-SQL server > real0m30.891s real0m27.165s about 1s > user0m0.050s user0m0.080s > sys 0m0.070s sys 0m0.050s > > > > real0m53.698s real0m54.481s about 2.5s > user0m0.190s user0m0.180s > sys 0m0.040s sys 0m0.040s > > This is about factor 20 compared to the MS-SQL server and I have > real hard arguing for PostgreSQL. In fact the MS-SQL server times > are estimated from inside Access - the plain server would be even > faster. > > By the way - the last example shows that enforcing index scan don´t > necessarily makes the thing faster - perhaps it could even slow down > for other queries?? > > I would be happy to forward the exact queries which lead to this > measures if someone is interested. > > Kind regards > > Andreas. > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [HACKERS] MySQL development MUST immdediately cease - Due to
To whoever sent this posting (being Al Dev, or someone spoofing), Please stop posting to the PostgreSQL mailing lists. You are not helping PostgreSQL with your postings, instead you are inciting anger and hostility. Cease and desist these postings immediately. Justin Clift peace_flower wrote: > > This is a immediate order to COMPLETELY STOP and CEASE all development > of MySQL database server. > This is due to rapid changes in the Global Economic conditions.. > > You do not have time to deal with one powerful SQL server like > PostgreSQL, where do you have time to > deal with two types of SQL servers. Question is why have two?? > > There is no time to deal with two open source SQL servers. After doing > research, it is recommended > that you stick with just PostgreSQL. With huge amount of efforts MySQL > can be brought closer to PostgreSQL > level (and perhaps it may NEVER be possible to bring MySQL to the level > of technology of PostgreSQL). Even > if it is done it will be a waste of time.. > > The WORLD economy started taking nose dive for the last 2 years. > Last year a mild global economic recession started which forced > thousands of companies world-wide closing down. > Last year millions of dot-com went bust. > > It is predicted that there is a impending "World-War-III" like situation > is developing in the middle-east and Afghanistan > which may have significant effect in Asian and European countries. > But that may NOT have lot of economic effects on North/South American > countries like Brazil, USA, Canada, Mexico.. > > Nevertheless, overall economy of the globe will get the impact. > > And, hence drop off the MySQL now and migrate all your data to > PostgreSQL.. > > By the way, PostgreSQL runs on all platforms - All unixes, linux, Apple > Macintosh and MS Windows 98/NT/2000 > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] PostgreSQL jobs page updated (included Red Hat's search for Database Engineers)
Hi all, Just updated PostgreSQL jobs page to include a direct submission from the guys at Red Hat. Mentioning it here on the list, as it might be a really good opportunity for the ex GreatBridge people, (as well as others of course) : http://techdocs.postgresql.org/jobs.php :-) Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL]
Hi Haifa, It sounds like you have a weird version of gcc installed. The most common versions of gcc to install on Solaris are available as packages from www.sunfreeware.com. Please note though, I haven't used the versions of gcc which are bundled with Solaris 8 nowdays, so if you're using one of those it might be ok. When you've figured out your compiler problem, you can follow the Solaris specific installation instructions for PostgreSQL at : http://techdocs.postgresql.org/installguides.php#solaris :-) Regards and best wishes, Justin Clift haifa touati wrote: > > Hi, > I'm installing PostgreSQL-7.1.3 on a Sun Solaris > station, > To configure it I worte: > env CC=/opt/app/gnu-0.2/bin/gcc ./configure > --prefix=.../PostgreSQL/postgresql-7.1.3 > But I receive: > checking whether the C compiler (/opt/app/gnu-0.2/gcc > ) works...no > configure:error: installation or configuration > problem: C compiler cannot create exécutable. > Please, can you help me to resolve this problem? > thanks a lot. > Haifa. > > ___ > Do You Yahoo!? -- Un e-mail gratuit @yahoo.fr ! > Yahoo! Courrier : http://fr.mail.yahoo.com > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Problem with database: FATAL 1: cannot find attribute 24
Hi Tauren, Have you found the solution for this yet? It really sounds like part of your customer's database has been deleted or gone missing. Which version of PostgreSQL are you using, and which operating system is it on? :-) Regards and best wishes, Justin Clift Tauren Mills wrote: > > Sorry for cross-posting this to both admin and general. I first posted to > admin, but as I'm desperate here and there is more activity in the general > list, I'm now posting it here as well. > > --- > > Help! > > One of my customers is having a problem with their database causing all of > their sites to be down right now. The problem seems to effect only their > database, not any other databases on the server. Here is the output when > connecting to it: > > [root@s1 data]# psql -U postgres dans_pgsql > Password: > Asql: FATAL 1: cannot find attribute 24 of relation > > I've searched the email archives as well as deja.com, but cannot find this > error message mentioned anywhere. Does anyone know what it means? I've > tried restarting postgres. Can anyone help suggest what I can do to get > this database working again? > > I have backups of all raw files on the server, but the customer has not done > a dump of their database recently. I rather doubt that restoring their > database from tape would replace it in a consistent state. Any thoughts on > this as well? > > One more thing... > > The vacuum command is giving the same error: > > [root@s1 data]# vacuumdb -v -U postgres dans_pgsql > Password: > Asql: FATAL 1: cannot find attribute 24 of relation > vacuumdb: vacuum dans_pgsql failed > > Is there another way to fix a corrupt database (assuming that is what the > problem here is)? > > Thanks, > Tauren > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Does Postgresql 7.1.x Still Have 8K Maximum Field Size?
Hi Philip, The version 7.1 series of PostgreSQL doesn't have the 8k limitation, it's now limited to a maximum of 1GB per field. Version 7.1.3 is the latest release (the recommended and most stable version), and we'll soon be beginning beta testing of the 7.2 series (don't use this for production projects until it's finalised). You can download the latest RPM's of PostgreSQL for RedHat 7.1 from the PostgreSQL ftp sites (www.postgresql.org lists the mirrors). A direct URL is : ftp://postgresql.wavefire.com/pub/binary/v7.1.3/RPMS/redhat-7.1/ Regards and best wishes, Justin Clift Philip wrote: > > Hello all. > > I will be creating one of my first web applications and would like to use Postgresql >as the database on a Red Hat 6.2 or 7.1 system with Apache and either ColdFusion or >PHP. > > Anyway, the application will have a text field where employees can update their >progress on assigned tasks. By the time a task is completed, the field may exceed >8k. I am not a person who likes to compile from tarball's because training non-Linux >experienced fellow system administrators to use RPMs is enough. Updates are easier >as well. > > The question: Is it possible to have a text field greater than 8k but only using >RPMs -- not compiling and/or chaining via a separate table (breaking something into >8k chunks)? If so, how? > > Any assistance you can provide is appreciated. Thanks. > > __ > Your favorite stores, helpful shopping tools and great gift ideas. Experience the >convenience of buying online with Shop@Netscape! http://shopnow.netscape.com/ > > Get your own FREE, personal Netscape Mail account today at >http://webmail.netscape.com/ > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Ridicolus Postgresql review
Thanks Bruce, I'll check the Archives for more info about it then. Prob should have done that before asking. :-) Regards and best wishes, Justin Clift Bruce Momjian wrote: > > > Hey guys, > > > > Does anyone know anything about this SQL*Net compatible thing which we > > are supposed to be developing according to the article? > > > > I haven't heard anything about it. > > It is on our TODO list. That's as far as it has gone. > > > Also, it says we are planning full Unicode support for the future. > > Don't we already have unicode support? (not sure I'm not experienced > > with multi-byte encodings at present). > > Yes, have had it for many releases, but is a configure flag. > > -- > 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 -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] MacOS X Build Fails with 7.1.3
Hi, You could try either of : http://techdocs.postgresql.org/installguides.php#macosx or http://techdocs.postgresql.org/oresources.php#macosx :-) Regards and best wishes, Justin Clift Hunter Hillegas wrote: > > My postgres build on MacOS X is failing: > > cc -traditional-cpp -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations > -bundle -undefined suppress -bundle -undefined suppress fe-auth.o > fe-connect.o fe-exec.o fe-misc.o fe-print.o fe-lobj.o pqexpbuffer.o dllist.o > pqsignal.o -o libpq.so.2.1 > /usr/bin/ld: -undefined error must be used when -twolevel_namespace is in > effect > make[3]: *** [libpq.so.2.1] Error 1 > make[2]: *** [all] Error 2 > make[1]: *** [all] Error 2 > make: *** [all] Error 2 > > Any ideas? > > Hunter > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Idea: jobs.postgresql.org
Sorry Vince, My mistake. I must have worded that badly. What I mean is that some Agencies find it difficult to believe that we would actually assist them in looking for candidates, especially directly to a targeted audience, without demanding to be paid for the effort. Once they understand we're doing this for the benefit of the PostgreSQL Community, they're cool with it. :-) Regards and best wishes, Justin Clift Vince Vielhaber wrote: > > On Sun, 9 Sep 2001, Justin Clift wrote: > > > I definitely believe we should allow agencies to list their jobs at no > > charge. Just because they're into business doesn't mean we have to take > > a cut of it. This should also mean they feel more comfortable about > > posting PostgreSQL jobs, and thereby benefits our community. > > My comment was because you said they were uncomfortable with no charge > for posting. If it makes them feel better or will keep them from posting, > let them donate. > > Vince. > -- > == > Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net > 56K Nationwide Dialup from $16.00/mo at Pop4 Networking > Online Campground Directoryhttp://www.camping-usa.com >Online Giftshop Superstorehttp://www.cloudninegifts.com > == -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Idea: jobs.postgresql.org
"Marc G. Fournier" wrote: > > On Sat, 8 Sep 2001, Vince Vielhaber wrote: > > > We can talk to Marc, but we can set it up as 1) someone looking for > > work gets a free listing, 2) a company looking for someone gets a free > > listing, but 3) a headhunter pays $10/listing - or "donates" it to > > postgresql.org or run it thru pgsql.com of if neither do we can run it > > thru libpq.com. > > Since its sort of dead right now, why don't set just setup a > jobs.postgresql.org site that allows ppl to a) post resumes and b) post > jobs ... I would personally rather encouraging posting of both to show a > vibrant PgSQL marketplace then discourage it ... Sounds good to me. I believe we should wait a few weeks though. The resources we have for doing site work at the moment are kind of committed to things, and it will take a while to introduce new people, etc. I'm all for adding listings of jobs, but I'm not sure if listing people who are looking around is a good idea. I definitely believe we should allow agencies to list their jobs at no charge. Just because they're into business doesn't mean we have to take a cut of it. This should also mean they feel more comfortable about posting PostgreSQL jobs, and thereby benefits our community. :-) Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Idea: jobs.postgresql.org
Vince Vielhaber wrote: > > On Sat, 8 Sep 2001, Justin Clift wrote: > > > Hi Guys, > > > > It is definitely something interesting, and there are a fair few > > PostgreSQL jobs out there. > > > > The address Joe mentioned is correct, I'm just so short of time now I > > haven't been able to do much in the way of site maintenance recently, so > > that page hasn't been updated in ages. > > If you want we can merge jobs into the main site for now. Sounds good to me. When it's up, the jobs page is the third most popular techdocs page people visit. The hardest part I've found is knowing when jobs have been filled, because people routinely "forget" to say when. An attitude I've found dissapointing is the weird belief a number of agencies have that there must be "some kind of catch" to putting their PostgreSQL related jobs online for free. No charge, no banner advertising, etc. You'd think they'd never heard of "Community Spirit". A lot of the stuff I do on the techdocs site is "lets see if this works and is beneficial". The jobs section was and is, and so I'm happy for it to become something more, main site, sub-domain, whatever. Regards and best wishes, Justin Clift > > Vince. > -- > == > Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net > 56K Nationwide Dialup from $16.00/mo at Pop4 Networking > Online Campground Directoryhttp://www.camping-usa.com >Online Giftshop Superstorehttp://www.cloudninegifts.com > == -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] RFC: PostgreSQL and MySQL comparison.
Hi guys, I'm willing to put this on techdocs.postgresql.org, as long as I feel it's been written for the right reasons. i.e. I'm all for a document(s) which helps people needing a good, solid, Open Source database needing transactions, ACID features, etc. But, I'm definitely not for a document which will incite PostgreSQL-vs-MySQL warfare and not try and get people to choost the appropriate product(s). :) Regards and best wishes, Justin Clift Digital Wokan wrote: > > Knock yourself out. I got jumped by a MySQL user on AZPHP for asking > another person why they had their Linux/PHP setup use an NT/MSSQL > database backend instead of Linux/PGSQL. (IMHO, PGSQL is far closer to > the features desired by MSSQL developers. Though I thought I heard > something about PGSQL procedures not being able to return recordsets. I > hope I heard wrong.) > > Alexey Borzov wrote: > > > > Greetings! > > > > Well, I suppose everyone on this list will agree that Postgres is > > superior over MySQL (or else they would have joined MySQL mailing list > > *chuckle*). So I would just note one area where MySQL is considerably > > stronger: PR. > > > > Every fart of MySQL developers gets noticed by high profile sites > > (change of logo, "NASA switches from Oracle to MySQL" - remember this > > one?, addition of Perl SPs, etc). I even remember "Gemini > > table type" announcement on Slashdot when this table type was complete > > vapourware. Besides, every comparison between PgSQL and MySQL draws > > attention from MySQL employees and volunteer trolls (check talkbacks > > on PHPBuilder, for example). > > > > I suppose PgSQL has to take a more active stance as well. Consider > > "M$ vs Linux debates". Of course here both projects are Open Source so > > the discussion should not be as heated... But I do think that > > the statements in > > http://www.mysql.com/doc/M/y/MySQL-PostgreSQL_features.html > > should NOT go unanswered. > > > > So, I propose the (semi-)official featureset comparison, but from > > Postgres users' POV. With a healthy dose of FUD as well, it is time for MySQL > > folks to taste their own medicine... > > > > Things that, IMHO, should go into this comparison: > > 1. MySQL does not satisfy the semi-official definition of RDBMS - > > "Codd's 12 rules", as it is in complete violation of rules 4 and 6 > > 2. MySQL is not SQL-compliant as views and subselects are required by > > entry-level SQL92 spec (I may be mistaken here, 'cause I have only the > > Russian translation of Gruber's "SQL Instant Reference") > > 3. MySQL did not have a major release to fix their shortcomings > > in several years, while Postgres evolves constantly. Moreover, > > according to MySQL's "roadmap" the most requested features are pushed > > back from mythical "4.0" to even more mythical "4.1" and "4.2" > > 4. It is very difficult to port to or from MySQL, 'cause the logic > > that is usually incapsulated in DB should be rewritten in application. > > > > Of course I don't think this should go into PgSQL manual, it is > > definitely not the place for such rants, but it should be published on > > some of "official" PgSQL sites. And then submitted to /. and such. :] > > > > Well, I *can* take up this "project", if it will be approved here, > > but must admit that the results should br reviewed by someone for whom > > English is a native language. :] > > > > -- > > Yours, Alexey V. Borzov, Webmaster of RDW.ru > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] problems on solaris 7
Ouch... Did you definitely make sure it was doing the test with Unix Domain Sockets instead of via TCP (it's a search and replace, as per that Installation Guide)? If so, then the test results are accurate and it could be a problem. Regards and best wishes, Justin Clift Peter Lauko wrote: > > YESSS, it seems to work now!!! > Thank you very very much!!! (of course everyone who tried to help) > > only one thing: > regression test fails at 2 points > abstime ... FAILED > random ... failed (ignored) > > the regression.diffs file looks: > > *** ./expected/abstime-solaris-1947.out Thu May 3 21:00:37 2001 > --- ./results/abstime.out Sun Aug 26 18:03:49 2001 > *** > *** 47,56 >| Sun Jan 14 03:14:21 1973 PST >| Mon May 01 00:30:30 1995 PDT >| epoch > - | current >| -infinity >| Sat May 10 23:59:12 1947 PDT > ! (6 rows) > > SELECT '' AS six, ABSTIME_TBL.* > WHERE ABSTIME_TBL.f1 > abstime '-infinity'; > --- 47,55 >| Sun Jan 14 03:14:21 1973 PST >| Mon May 01 00:30:30 1995 PDT >| epoch >| -infinity >| Sat May 10 23:59:12 1947 PDT > ! (5 rows) > > SELECT '' AS six, ABSTIME_TBL.* > WHERE ABSTIME_TBL.f1 > abstime '-infinity'; > > == > > *** ./expected/random.out Thu Jan 6 07:40:54 2000 > --- ./results/random.outSun Aug 26 18:05:12 2001 > *** > *** 31,35 > WHERE random NOT BETWEEN 80 AND 120; >random > > ! (0 rows) > > --- 31,36 > WHERE random NOT BETWEEN 80 AND 120; >random > ---- > ! 127 > ! (1 row) > > ### > > What does it mean? CURRENT_TIMESTAMP and CURRENT_DATE seem to produce the expected >results. > thanks again, Peter > > Justin Clift <[EMAIL PROTECTED]> wrote: > > > Hi Peter, > > > > There's a Solaris specific installation guide for PostgreSQL at : > > > > http://techdocs.postgresql.org/installguides.php#solaris > > > > Have you taken a look at it yet? > > > > Regards and best wishes, > > > > Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] problems on solaris 7
Hi Peter, There's a Solaris specific installation guide for PostgreSQL at : http://techdocs.postgresql.org/installguides.php#solaris Have you taken a look at it yet? Regards and best wishes, Justin Clift Peter Lauko wrote: > > Hi! > I've installed 7.1.2 on sparc solaris 7. > configure, make, install and initdb worked perfectly. > When I try to start postmaster: > > postgres$ postmaster > IpcMemoryCreate: shmget(key=5432001, size=1245184, 03600) failed: Invalid argument > > I've read in administrator's guide to put the following lines in /etc/system: > > set shmsys:shminfo_shmmax=0x200 > set shmsys:shminfo_shmmin=1 > set shmsys:shminfo_shmmni=256 > set shmsys:shminfo_shmseg=256 > set semsys:seminfo_semmap=256 > set semsys:seminfo_semmni=512 > set semsys:seminfo_semmns=512 > set semsys:seminfo_semmsl=32 > > Since then postmaster starts, but when I try to use psql, createuser or createdb >they drop segmentation fault. (when trying to use psql with a nonexisting database - >i.e. other than template1 - it works fine: reports the error and exits). Regression >check also fails at createdb with segfault. > > I suppose this is a problem in my solaris settings and not in postgres, but I'm not >an expert of kernel level solaris configuration. Could anyone help me please?! I'm >running out of time. > > thanks, Peter > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: MySQL's (false?) claims... (was: Re: [GENERAL] PL/java?)
Hi Sean, We can always ask them to change things. The thing which strike me as wrong the most is the stability issue with PostgreSQL. I've only very rarely heard reports by anyone saying MySQL was more stable than PostgreSQL for them. Most of the rest I think can be justified in one way or another. If anyone else can see things blatantly wrong on that page, email me about them and I'll ask Monty (the MySQL guy) to please change/remove/fix them. :-) Regards and best wishes, Justin Clift Sean Chittenden wrote: > > > Has anyone seen this page on Mysql.org comparing PostgreSQL to MySQL: > > > > http://www.mysql.com/doc/M/y/MySQL-PostgreSQL_features.html > > Yeah, I've had a few developers show it to me... the best part > of this is though, when I tried to post a comment, I got a MySQL > database error. ::grin:: At anyrate, it looks like a load of FUD from > a bad marketing department (at least Microsoft lies well). -sc > > -- > Sean Chittenden > > >Part 1.2Type: application/pgp-signature > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] During dump: function not found
Hi Andrew, These functions were indeed created by PgAdmin. It seems to create tables, functions, triggers and views (maybe rules too, not sure) going by the name pgadmin_xxx and pga_xxx I'm pretty sure from looking at PgAdmin 7.1.0 recently it has a menu option to remove it's tables and other constructs from a target database by itself. You might just want to run that instead of doing it yourself/ :-) Regards and best wishes, Justin Clift Andrew Gould wrote: > > During a pg_dump, I got the following messages: > > Notice: function "pgadmin_get_rows" is not dumped. > Reason: return type name (oid 87589805) not found. > Notice: function "pgadmin_get_sequence" is not dumped. > Reason: return type name (oid 87589772) not found. > > Does this simply mean that these functions will not be > available if I restore from the dump file? Will these > messages haunt me down the road in other ways? > > I'm assuming (dangerous, I know) that these functions > were created by PgAdmin rather than being a part of > PostgreSQL. I deleted all tables and views named > pgadmin* and am trying to weed out everything created > by PgAdmin. > > Thanks, > > Andrew Gould > > __ > Do You Yahoo!? > Make international calls for as low as $.04/minute with Yahoo! Messenger > http://phonecard.yahoo.com/ > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] time interval question
Hi,
You could do something along the lines of :
SELECT foo, bar FROM baz WHERE date_part('hour', foo) > 0 AND
date_part('hour', foo) < 8;
Simple, but effective. Only works for whole hours like in your example
though.
:-)
Regards and best wishes,
Justin Clift
Cefull Lo wrote:
>
> given a timestamp data, e.g. 2001-08-18 06:01:23+08
> How can I check that is it lie between 00:00:00 and 08:00:00 and ignore the
> date?
>
> --
> content-type:kiss/hug
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Is it possible to work with big databases (up to 10Gb) under Postgres?
Hi Ivan, I was running a database with ~27GB of info (admittedly in large, very simple tables) on PostgreSQL 7.0.3 a while ago for a few months. Didn't have any real problems with it, apart from having to accept that a lot of data takes time to process. :-/ Regards and best wishes, Justin Clift Ivan Babikov wrote: > > Hello All! > > Maybe this is one of those typical questions, but I have heard people > complaining that Postgres is just for quite small bases, less than 2Gb. > > Now, we have to choose a free database engine for an inexpensive branch of > our project and Interbase looks better in sense of capability to work with > quite big bases (up to 10-20Gb). I am not sure that Postgres will work fine > with databases greater than 10Gb. What does All think of it? > > Thanks in advance, Ivan Babikoff. > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Real newbie question.
Hi Bill,
Have you solved this yet?
When the psql prompt becomes a ' it means you've started a text string
and not ended it. i.e. :
psql# INSERT INTO foo (stringfield) VALUES ('blah blah blah);
psql'#
You need to type another ' then press enter to finish the string. Once
on a new line, type \r to clear the buffer of the command you stuffed,
or \e to open up a window which edits it. i.e. :
psql# INSERT INTO foo (stringfield) VALUES ('blah blah blah);
psql'# safdsadfa
psql'#
psql'# Aha...
psql'# '
psql# \r
Query buffer reset.
psql# INSERT INTO foo (stringfield) VALUES ('blah blah blah');
:-)
Hope that's helpful, did it from memory, so the exact output may look
slightly different.
Regards and best wishes,
Justin Clift
"William D. Tallman" wrote:
>
> Just started fiddling with PostgreSQL, and know nothing of databases.
>
> I have the documentation that came with the Mandrake-8.0 installation
> written by Thomas Lockhard, and I've perused Bruce Momjian's book online,
> and I cannot find the answer to this problem:
>
> When I am entering values into a table, and make a mistake, sometimes
> there is an error message, and sometimes the equal sign in the prompt
> becomes a single quote. Error messages I can puzzle out, but I have not
> discovered how to recover from the single quote in the prompt. I wind up
> having to kill the terminal and start all over
>
> Can someone point me to the explanation of this?
>
> Thanks, and I hope this question isn't too rudimentary for this NG.
>
> Bill Tallman
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [GENERAL] Serial not so unique?
Hi Stephen,
That's weird behaviour. If you'd manually created the sequence like
this :
CREATE SEQUENCE foo_seq MINVALUE 4 MAXVALUE 101 CYCLE
Then referenced it as the default like this :
CREATE TABLE bar (idnum integer UNIQUE DEFAULT nextval('foo_seq') NOT
NULL, otherstuff varchar(20));
That would explain the wrapping around behaviour, but not when the field
is a SERIAL type.
:(
+ Justin Clift
Stephen Robert Norris wrote:
>
> On Sat, Aug 18, 2001 at 03:49:10PM -0700, Joe Conway wrote:
> > > > > Sometimes (about 20%, it seems) with several of the data sets, we
> > > > > get an error trying to insert rows into the table with the serial in
> > it.
> > > > > On investigation, it seems that the serial number has got to 101, then
> > > > > set itself back to 4, causing nextval to return 5, and there are
> > already
> > > > > entries from 1-101.
> > > > >
> > > > > Now, we use the serial as the primary key, and we never explicitly set
> > it.
> > > > >
> > > > > Has anyone seen anything like this? I can work around it by generating
> > > > > a serial number within the application, but that's not ideal.
> > > >
> > > > Odd problem. What do you get if you run:
> > > > select * from name_of_this_troublesome_sequence;
> > > > particularly for increment_by, max_value, min_value, and is_cycled?
> > > >
> > > > -- Joe
> > >
> > > 1, 2^31 -1, 1, f
> > >
> > > Stephen
> >
> > Nothing stands out there. You might try to drop and recreate the sequence if
> > you haven't already. Or, a longshot, but . . . you might check the table
> > definition to be sure it's using the sequence that you think it is.
> >
> > -- Joe
>
> Recreating the sequence solves the problem, of course. So does setval(102).
> My problem is that it got into this state originally. The test case that
> demonstrates it sometimes takes about 1.5 hours to run, and I have only got
> about 24 hours left, so I may have to stop investigating and make the
> application generate the id instead.
>
> Stephen
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[GENERAL] PostgreSQL buffer exploits
Hi all, Just wondering if anyone knows of or has tested for PostgreSQL buffer exploits over the various interfaces (JDBC, ODBC, psql, etc) or directly through socket connections? Working on a sensitive application at the moment, and I've realised I've never seen anyone mention testing PostgreSQL in this regard yet. Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] PostgreSQL Article on tucows
Hi all, Branden has written an "Introduction to PostgreSQL" article for the Tucows network (another high profile group of sites): http://news.tucows.com/ http://news.tucows.com/linux/40605.html :-) Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] OpenLDAP - Postgresql HOWTO
Hi Gilles, That's cool. Was hoping you'd figure it out! Just updated the buggy link to point to your new HOWTO : http://techdocs.postgresql.org/oresources.php#ldap :-) Regards and best wishes, Justin Clift Gilles DAROLD wrote: > > Hi all, > > Some of you dream of that, I just had nigthmare with that :-) So it's the first > draft of > the OpenLDAP/PostgreSQL HOWTO and it is available at the following URI: > > http://www.samse.fr/GPL/ > > It was really hard to have it work but nothing impossible, I just write the howto > > so there probably some miss or fault. I will review it in some days after a > submit > to the OpenLDAP team to have some patch integration. This howto will also be > submitted to the LDP. > > Justin I think you can update your buggy link :-) > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] An unfair comparision....
Hi Tony, PostgreSQL is *very* stable. Individual backends can be killed off by weird SQL queries that trigger a known bug (not very many, and always being fixed), but that is very very rare, and nothing which 99.999% of normal SQL queries will get even close to. There are a few 3rd party replication products for Solaris. The most developed one I'm aware of is PostgreSQL Replicator (pgreplicator.sourceforge.net). The speed issue is a matter of sizing the database & hardware correctly, you'll probably want to speak to a Commercial Support company for this. And for Solaris support, I'd also advise contacting one of the Commercial Support companies : PostgreSQL Inc. www.pgsql.com GreatBridge LLC www.greatbridge.com In Japan, then Software Research Associates : osb.sra.co.jp In Vienna, Cybertec Geschwinde &. Schvnig OEG postgres.cybertec.at Further details of commercial support can be found at the PostgreSQL Commercial Support page : http://www.ca.postgresql.org/users-lounge/commercial-support.html Hope this is helpful. Regards and best wishes, Justin Clift Tony Hoyt wrote: > > I'm curious how PostgreSQL compares to Oracle in terms > of stability and speed. My company is looking into > alternative database software for our product but we > need to know if it's stable enough for our needs. > > Our current requirements list for a good Database > software application are the following. > > 1. Stability - It can survive power outages. > 2. Speed - We're only going to perform very basic > read and write transactions. Some tables are rather > large but over all, we need a at least 200 sql > transactions per second. > 3. Redudency - Can we have two physical systems with > one database being updated by the master database > automaticly in case of failure. This is above and > beyond RAID. > 4. Solaris Support - I'm already positive that > PostgreSQL can run on Solaris, but how well is it > supported? > > If someone can adaquitly answer those issues for me, > I would be extreamly greatfull. Thank you. > >Tony > > __ > Do You Yahoo!? > Make international calls for as low as $.04/minute with Yahoo! Messenger > http://phonecard.yahoo.com/ > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] [Fwd: MySQL Benchmark page - Problem with vacuum() in PostgreSQL]
Hi all, The "MySQL Benchmark" page on the mysql.com website has benchmarks for various databases : http://www.mysql.com/information/benchmarks.html In the PostgreSQL entry they aren't able to report an "accurate" result for all things, as they haven't been able to get vacuum to work 100% correctly. If someone can take a look at the description of the problem which Monty is having (he's the lead MySQL developer) we'll be able to have accurate benchmark for PostgreSQL on their benchmark page. So, anyone want to lend a hand? :-) Regards and best wishes, Justin Clift Original Message Subject: MySQL Benchmark page - Problem with vacuum() in PostgreSQL Date: Sat, 28 Jul 2001 14:01:22 +0200 From: Anna Ewerlid <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Hi! >>>>> "Justin" == Justin Clift <[EMAIL PROTECTED]> writes: Justin> Hi, Justin> I'm looking at your MySQL benchmark page : Justin> http://www.mysql.com/information/benchmarks.html Justin> It says you guys weren't able to get vacuum() working reliably with Justin> PostgreSQL 7.1.1, and you'll generate a fast version of the benchmarks Justin> when you can. Justin> What exactly was the problem? I have tried to explain the problem in document that we have published under the benchmarks results. The problem was that when we run the benchmark with the --fast option, which basicly does a vacuum() between after each batch of updates, postmaster started to fill up disk with log files during one of the vacuum() runs and didn't stop until the disk was full. When this happened I killed postmaster and tried to restart it, but postmaster just died with a core dump after that :( I repeated the above a couple of times and was able to repeat the problem with disk full, but didn't manage to crash postmaster again. I would really appreciate if you could help us locate and fix the problem is PostgreSQL, because I would really like to see that all open source databases gets good benchmarks results to be able to deliver the message that open source is a good alternative to commercial databases. If you have any time over, please take a look at the benchmarks and see if there is anything that could be improved to get better results for PostgreSQL, especially when running with the --fast option! If you think we have missed any important 'single user' benchmark, feel free to add it to the benchmark suite and email Anna and me a patch; We really want to be able to show which operations are fast and which are slow for the different databases, so that the different database users know to which constructs they have to avoid! We just hired Anna to start working on a multi-user benchmark suite that will be a complement to the current single-user benchmark suite. If you have any comments about the benchmarks, please share them with us! I have worked hard to make them fair against all databases; I have actually put more work into getting PostgreSQL to give good numbers than for any other database! The problem is that I don't know PostgreSQL intimately which makes it a bit harder to get really benchmark results for it! Regards, Monty ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Hardware Recommendations
Hi all, One of my personal rules is that whenever someone says they're going to get a 10K RPM SCSI drive, I always tell them to go 2 x 10K RPM SCSI drives (half the size each) and do RAID 0. Or, preferably go 3 x and do RAID 5. My point is that 10K RPM drives are nice, but more is better (and effective) with SCSI. :) Regards and best wishes, Justin Clift Steve Wolfe wrote: > > > I respectfully disagree with Steve's "disk speed isn't terribly > > important" -- it can be (and is totally dependent on what you're doing) > and > > since you have the on-board U160 with all the dual Athlon boards I've > seen, > > you might as well take full advantage of it.. That and you know Seagate > has > > those new 15,000 RPM U160 drives out *evil grin*.. > > You can't take advantage of U160 without at *least* three disks. ; ) > > If you have enough RAM for the cache, and you're not using fsync(), then > an insert/update doesn't actually hit the disk, it just returns. The > kernel can update the disk later at it's leisure. I'm not sorry that I > spent all of the money for the large array that I use, it provides > redundancy that lets me sleep easier - but I can guarantee that I'm not > using 1/100th of it's potential as far as write capabilities. > > steve > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] psql on red hat 7.1
Hi again, You're probably best to look through the mailing list archives for the best ways people have found for doing this. There have been a few people with similar problems over time. :-) If you don't find anything there, feel free to ask again. :-) Regards and best wishes, Justin Clift [EMAIL PROTECTED] wrote: > > On Mon, Jul 23, 2001 at 10:23:51AM +1000, Justin Clift wrote: > > Hi, > > > > I'm guessing that you've compiled your own version of PostgreSQL 7.1.2. > > > > The symptoms you mention are what happens when PostgreSQL is compiled > > without the "readline" library(ies). Normally the PostgreSQL > > "configure" program finds this if it's on your system, but I'm thinking > > that perhaps you don't have it installed or the "configure" program > > didn't find it for some reason. > > I compiled 7.1.2 myself > > I now see that configure does not > find readline even though it is > installed > > How should I tell configure > where to find it? > > Thanks > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] simple linking question
Hi Jeremy, It sounds like what you're saying is that you have data in Table A, and you also have data in Table B, but you want one of the fields in Table B to really be from a field in Table A at all times (transparently). Is that right? You could create a VIEW using the "CREATE VIEW" command (http://www.postgresql.org/idocs/index.php?sql-createview.html). It's kind of like creating Table C - but it's a dynamically updated virtual table whose data really comes from other tables (it doesn't have any real data of it's own). Kind of like this : --- Table C --- ^ | --- | | --- --- Table A Table B --- --- (Did this line art in Netscape, so I'm not sure if it'll work in your mail reader) And you use normal everyday SQL queries with Table C, and they automatically redirect to the fields from Table A and Table B appropriately. It's not really all that hard to setup either. Well, not if you're mainly going to be doing SELECT statements on the new "Table C". Get's a little more tedious setting things up if you want to do changes through a VIEW. Does that help? :-) Regards and best wishes, Justin Clift Jeremy Hansen wrote: > > I'm trying to do some more advanced things in sql to make my tables more > efficient. I'd like to do a link, or perhaps it's a join, although > everything I've looked at relating to a join makes me think that it's not > what I'm looking for. > > What I would like to do is have a field in a table retrieve its data from > a field in another table. The idea is not to have duplicated information > in two different tables. Is this possible to do with postgres? I studied > join selects, but it seems this method requires that information be > duplicated in a third table. I want to avoid duplicating data. > > Or is this just stupid? > > Thanks for any help or pointers to documentation. > > -jeremy > > -- > salad. > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Migration
Hi Todd, There's an ODBC specific part of the PostgreSQL group of sites at : http://odbc.postgresql.org That might have information you'll find useful. There's also a list of ODBC related PostgreSQL material at : http://techdocs.postgresql.org/oresources.php#odbc And there's always the psqlodbcplus Project (an improved ODBC driver for PostgreSQL) at : http://www.greatbridge.org/project/psqlodbcplus/projdisplay.php Additionally, Michael Davis <[EMAIL PROTECTED]> wrote up a quick page about PostgreSQL ODBC configuration. It's available at : http://www.sevainc.com/Access/PostgreSQL_ODBC_Configuration.html Hope these prove to be useful. :-) Regards and best wishes, Justin Clift Todd LaClair wrote: > > I will be migrating a Tango app on Windows with a SQL Server backend > over to Linux, Apache, and Postgresql. What do I need to be on the > lookout for in regards to the odbc portion of this migration. Some hints > and links to help pages would be appreciated. > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Program to convert from PostgreSQL to Access or view table relationships
Hi Steve, Have you considered using a PostgreSQL ODBC driver (http://odbc.postgresql.org) and allowing Access to connect to the PostgreSQL database directly? Additionally, there's a listing of PostgreSQL related resources at : http://techdocs.postgresql.org/oresources.php In the "Administration Tools" section, it has a program called pgAdmin (hosted by Great Bridge). This might allow you to view table relationships, without MS Access. Not sure, it's on my schedule to take a look at this program in the next few days. If you can make use of a unix box (probably linux) instead of Windows, then DdDesigner might do the trick too (in the "ERD Tools" section). Failing that, pgAccess (also in the "Administration Tools" section) can be used to display a schema graphically, but you have to manually link the table relationships together onscreen, and it can only generate PostScript output files from what I remember. :-) Regards and best wishes, Justin Clift Steve Werby wrote: > > I am looking for a program to convert a PostgreSQL database to Access. I'm > not migrating to Access, I simply want an easy way to load the schema into > Access so I can get a graphical view of the table relationships which I can > print a hard copy from and make notes on for a project I've inherited. I > have already searched on Google and the list archives at the AIMS Group. > I know I can dump the schema using "pg_dump -s", but I was hoping for a > program that could automate the conversion. Or if there's an open source > Windows or PHP-based web application that can generate the output I want > directly from PostgreSQL that would be even better. > > -- > Steve Werby > President, Befriend Internet Services LLC > http://www.befriend.com/ > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] replication?
Hi Fran, You might want to check out the PostgreSQL replication solutions linked to from : http://techdocs.postgresql.org/oresources.php#replication PostgreSQL Replicator seems to be the most complete, but I'm not sure (haven't yet gotten around to testing them properly). :-) Regards and best wishes, Justin Clift Fran Fabrizio wrote: > > Does postgres support replication? I think this is the feature I need. > I have a table in one db that I need to use from another db on another > machine. The first db is behind two firewalls (for a good reason =) and > the second needs to be hung out on the internet, so I can't really use > the same db. But I would like to have the table on the internet Pg > install to be updated whenever the master table behind the firewalls is > updated. It doesn't need to be real-time, but maybe once every hour or > half hour. I certainly have no problem cheating and just copying a file > over or something if that will work. I looked through the docs but > didn't see anything about replication in the Administrator manual, where > I assume it'd be discussed. I did see a vague reference to a > replication toolkit in the release notes, is this what I'm looking for? > > Thanks, > Fran > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Postgres User and Password
Hi Bob, This sounds more like a general Unix security problems than anything PostgreSQL specific. If you are only changing to the "postgres" user to start the database server, then would it be better for your Linux startup scripts to automatically start PostgreSQL when the server starts, then have your user accounts setup so they can access PostgreSQL directly? That way people should be able to log in as themselves and use PostgreSQL directly without having to su anywhere? :-) Regards and best wishes, Justin Clift R Talbot wrote: > > I was not able to get an RPM build for Calder Edesktop 2.3 & 2.4.. > So, I downloaded source for Postgres v 7.0.3 and rolled my own.. > I compiled the source to 3 linux boxes and Installed to 3 OS/2 boxes. > > My problem is with my Linux Posgres installations. > I compiled as root and installed as root user. As root I can > root@thinkpad root]# su postgres > root@thinkpad root]# pg_ctl start ... Or use postmaster > > and successfully start the server.. As I have set all Path and Data > enviormental variables. > But, if I try this as a user > Bob@thinkpad Bob]# su postgres . My results is > password: Then no > matter whats entered, even NULL > > ... I get > su: incorrect password > > I had no password for User postgres but I finally gave it one but no > help.. > I made root and Bob both members of Group database.. no help. > Once server is started Bob is a postgres user and can start and use > psql... > > Very inconvenient to log in as root then su postgres then back to User > login to > development tools.. > How can I fix this problem.. > > Thank You > Bob T > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Performance tuning for linux, 1GB RAM, dual CPU?
Hi Adam, There are a few links to benchmark-type things you might find useful at : http://techdocs.postgresql.org/oresources.php#benchmark Hope they're useful. :-) Regards and best wishes, Justin Clift Adam Manock wrote: > > >This is almost certainly a lousy idea. You do *not* want to chew up all > >available memory for PG shared buffers; you should leave a good deal of > >space for kernel-level disk buffers. > > I decided to start high on buffers because of Bruce's: > http://www.ca.postgresql.org/docs/hw_performance/ > From that I get the impression that operations using kernel disk buffer > cache are considerably more expensive than if the data was in shared > buffer cache, and that increasing PG's memory usage until the system > is almost using swap is The Right Thing To Do. Has anyone got real > world test data to confirm or refute this?? > If not, then I am going to need to find or create a benchmarking program > to load down PG against a fake multi-gigabyte "production" database. > Or I could wait a week to see what RedHat does to tune their > implementation of PG :-) > > Adam > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] QUERY - Including the new LDP PostgreSQL HOWTO in the main PostgreSQL distribution
Hi all, We've had the go-ahead from the Linux Documentation Project (LDP) guys to create a new PostgreSQL HOW-TO for a while now. I was going to start it off just to make sure we had something, so that it could be put around instead of Al Dev's Database Guide (the bad one). Well, I haven't had time. Here's my thought, I'd like to know what people think. - How about we create a new PostgreSQL HOW-TO, have it as part of the main distribution, and have that be the one which the LDP guys use? They use DocBook format, as do we, and I feel this will allow us to keep a very in-sync relationship between the new PostgreSQL HOW-TO and the present version. It also means that no one person has total control of it, and it can be contributed to by anyone that has a suggestion and/or can generate a diff. So, what do people think? :-) Regards and best wishes, Justin Clift ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] PostgreSQL perl / libpq.so.2 problem - again :(
Hi all, Are you familiar with Symantec Ghost? The way I work I keep fresh installed versions of Mandrake 8.0, Win NT (with SP6a), Solaris 8.0 INTEL, etc, as compressed image files. When it comes time to build software on a "freshly installed" machine it takes just under 10 minutes to restore the appropriate image to a hard drive with Ghost. Works well for me. What do you guys think, good approach? :-) Regards and best wishes, Justin Clift Lamar Owen wrote: > > On Friday 06 July 2001 09:04, [EMAIL PROTECTED] wrote: > > This might sound like a flame, but it isn't meant to be. Uninstall the rpm > > packages that are giving you trouble and reinstall them from source. For > > me, rpm's are just grief, especially for apps that I'm going to do a lot of > > configuration anyway. > > For a machine that has only RPM-installed packages, the RPM's can be more > convenient. > > > compile postgresql and apache from source, both to get the most recent > > updates immediately and so they could be optimized for that machine. > > As the PostgreSQL release cycle is rather slower than much software, this > isn't really an issue for PostgreSQL. Patience is still a virtue. > > > OTOH, if you can get the rpm's to work, by all means more power to you. > > RPM offers more to users than just convenience. But, if you're not > convinced, I'm not going to argue with you. > > In my case, I used the RPM's before I started maintaining the RPM's because I > don't install development tools on production servers -- and at the time > Ididn't have a development server to build on. I now have enough boxen to do > the development with -- but I still prefer the RPM way of doing things, as it > results in a more consistent system. And I take great pains toleave my > development machines in 'out-of-the-box' condition (except for security > updates) so that the RPM's I build are usable by the most people. > > Otherwise, you will have to rebuild the RPMset from the source RPM for your > custom setup. > > But, if you start installing core OS packages from source on an RPM box, you > will likely need to install all dependent packages from source as well, as > the RPM database won't have the correct dependency information. > > In the example that started this thread, had Perl 5.6 been installed as RPM, > the postgresql-perl RPM installation should have barfed, as it depends upon > the perl version registered with the RPM database to equal 5.00503 -- not > greater and not less. But perl 5.6 was installed from source -- and the RPM > database dependencies we're updated -- which could cause more problems than > for just the postgresql-perl RPM, as any other RPMs that depend upon > perl=5.00503 will install silently to the older perl directory, which will be > incorrect. > > On a related note, I no longer have RedHat 6.2 or 7.0 boxen -- Red Hat 7.1 is > so much more stable (and so much faster!) that I have migrated all but my > production server to RH 7.1 --and the production server will get the upgrade > next. If anyone wants to donate a old hard drive or two to see older or > other distributions supported, I won't argue :-). The installation of Red > Hat necessary to build the RPMset varies, but is almost always greater than > 1GB -- a 2 or 3 GB hard drive is enough to install a development set on -- > and I will continue support for those distributions as long as the hard drive > lives. > > So, unless things change, future PostgreSQL binary RPMsets will be built only > for RHL 7.1 by me -- others are already building Mandrake 7.2 and 8.0 sets. > You can rebuild from the source RPM fairly easily, though, as long as you > have at least RPM version 3.0.5. Instructions are in the README.rpm-dist. > -- > Lamar Owen > WGCR Internet Radio > 1 Peter 4:11 > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] CLISP ?
Hi all, Does anyone know what the heck CLISP is, and/or if there's a driver for using PostgreSQL with it? :-) I'll add it to the techdocs.postgresql.org website if there is too. :-) Regards and best wishes, Justin Clift Gunjan Varshney wrote: > > Dear, > > We are working on a project in CLISP in which we are using PoastgreSQL. > Can you please suggest us a driver to connect through clisp script? > > Regards, > Gunjan -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Install with Apache/PHP (Was "Stubborn Multibyte")
Hi Karen, It wouldn't hurt to update to at least PHP 4.0.5, as it fixes a bunch of bugs, and also knows about the changes in the PostgreSQL naming of include files, etc. :-) Apache 1.3.20 is also available last I checked too. :-) Regards and best wishes, Justin Clift Karen Ellrick wrote: > > Update on my problem: I had another machine waiting in the wings, that would > eventually replace the one I am having trouble with regarding the multibyte > complaint. I decided to try installing PostgreSQL 7.1.1 on that one, and it > worked. So if I can get everything else working on the new machine, I will > just swap it out and ignore the fact that the present machine is being > mysteriously stubborn about multibyte errors. > > However, on the new machine (which is also RedHat 6.1J), when I tried my > tried-and-true sequence of configuring/compiling/installing the combination > of Apache and PHP4 with related PostgreSQL files, I ran into a different > problem. This is my sequence, after untarring all three packages > (theoretically it doesn't matter whether I install pgsql first or last, as > long as the untarred directory is there): >cd apache_1.3.17 >./configure --prefix=/usr/apache >cd ../php-4.0 > > ./configure --with-apache=../apache_1.3.17 --with-pgsql=../postgresql-7.1.1 >make >make install >cd ../apache_1.3.17 > > ./configure --prefix=/usr/apache --activate-module=src/modules/php4/libphp4. > a >make >make install > > But this time (never before on two other machines) I get the following error > during PHP's make: >In file included from pgsql.c:29: >php_pgsql.h:32: postgres.h: No such file or directory > > Anyone done this before, and had this message? I am using the same tarballs > for PHP and Apache that I used on the previous two installations. After it > failed the first time, I even tried untarring the previously used tarball > for postgresql (7.0.2) and pointing to it during configure, but the same > error appeared. There are no shortage of files called postgres.h on the > machine, but I don't know where it is looking, and I don't think I should > have to start disecting the Makefile. It worked before, so it shouldn't be > a typo or incompatibility, should it? > > > Karen Ellrick > S & C Technology, Inc. > 1-21-35 Kusatsu-shinmachi > Hiroshima 733-0834 Japan > (from U.S. 011-81, from Japan 0) 82-293-2838 > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Re: What's the best front end/client under MS Windows?
Hi Greg, Instead of using psql to get the NOTICES, when I program in PL/pgSQL, I start the database with -d 2 (debug level of 2) and this forces the NOTICES into the PostgreSQL log file. Then, I just have a telnet/ssh/etc session on the server running : tail -f postgresql.log So I constantly have access to the NOTICES which PostgreSQL generates, regardless of how I'm hitting it. Would this be useful to you? :-) Regards and best wishes, Justin Clift Gregory Wood wrote: > > I'm partial to the ZEOS Database Explorer myself. I don't have experience > with a multitude of tools, so the best I can offer is that I have no > problems with the latest version :) Actually, I can tell you that it has the > advantage of a native interface (rather than relying on ODBC). And it's > free, that's always an advantage. > > Oh, and it does have the disadvantage of not showing NOTICEs, which is a > pain in the ass when doing benchmarking via EXPLAIN, for which I'm forced to > use psql. There were some problems in older versions (specifically it was > having trouble stripping out spaces on newlines and with quoting on > triggers), but those problems seem to be resolved with the latest version. > It's not the most stable program, but most people don't use Windows for > stability... > > Address is http://www.zeoslib.org/ but it appears to be down at the moment. > > Greg > > - Original Message - > From: "Walter Chong" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, May 11, 2001 11:03 PM > Subject: What's the best front end/client under MS Windows? > > > Kindly list the advantages/disadvantages for me, THX! > > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Re: Database Recovery Help...
Hi John, What OS are you running it on? For example, I've been told that Debian Linux puts its socket files in /var/run/postgresql instead of /tmp/. The idea here, is to find that socket file and remove it. For most OS's, it's in /tmp/ but for yours it might not be, especially if you've installed from a package (rpm, deb, etc). It's something to look into. :-) Regards and best wishes, Justin Clift "John Clark L. Naldoza" wrote: > > Hi, > > Chris Smith wrote: > > > > Hi, > > > > Did you try what it suggests? > > > > If you're sure PostgreSQL isn't already running, remove the > > "/tmp/.s.PGSQL.5432" file, and try again. > > That file didn't exist...;-) > > -- > /) John Clark Naldoza y Lopez (\ > / )Software Design Engineer II ( \ > _( (__ Web-Application Development_) )_ > (((\ \> /_>Cable Modem Network Management System <_\( \_/ / NEC Telecom Software Phils., Inc. \ \_/ ) > \ / \ / >\_/ phone: (+63 32) 233-9142 loc. 3112\_/ >/ / cellphone: (+63 919) 399-4742 \ \ > / / email: [EMAIL PROTECTED]\ \ > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] When DST occurs
Hi all, I'm trying to figure out how DST change times will affect a running database? For example, if I'm running a PostgreSQL 7.1 server through a DST changeover period, how does the database handle this? Apparently linux automatically does some kind of update during the changeover period, but does that transfer through to PostgreSQL? Or would the database need to be re-started? Or ? Need to find out this info, but the documentation doesn't appear to mention anything about it, and the mailing list stuff I've read just directly talk about this. Regards and best wishes, Justin Clift ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Daylight savings
Hi all, I'm wondering if anyone has scripts or knows of any tools which will detect the next DST change, and setup a cronjob to be run just before it? This is so we can have certain types of data which are stored in the database as localtime, updated to still be correct during DST and after. At present I'm considering running a mismash of zdump, perl, psql, etc, and I'm kind of hoping there's already something out there. Regards and best wishes, Justin Clift ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] DB Getting Slower and Slower and Slower....
Hi Guys, I don't know if this has any bearing, but I remember someone recently saying that Sun's JDK 1.3 has a memory leak bug in one of it's releases, and our JDBC driver is affected by that. Not sure of it's present status though. Regards and best wishes, Justin Clift Doug McNaught wrote: > > "Othman Laraki" <[EMAIL PROTECTED]> writes: > > > I recently upgraded to V7.1 version of Postgres on Linux and have > > noticed that as the DB is being used, it gets progressively slower > > and slower, until very simple queries slow down to a crawl. > > First question: are you VACUUMing enough? How active is the DB with > respect to inserts/updates? You should be doing VACUUM ANALYZE at > least nightly (some people with very busy databases do it every couple > hours). > > > The > > basic setup I have is a set of JSPs and Servlets running on Tomcat, > > that use Java classes which connect to Postgres through a JDBC > > driver and perform various DB operations. But, as I said, the DB > > gets noticeably slower, as if it does not clean up behind itself > > after performing operations and finally runs out of memory... Then, > > the only thing that works is stopping the postmaster and rebooting > > the machine. > > Very odd. You certainly shouldn't have to reboot to reclaim memory; > restarting the postmaster should be enough. > > Are you sure the Java app or some other process isn't eating up all > the memory? I was getting PG backend crashes due to lack of memory > every few days for a while, and finally found that it was my > webserver that was running away with itself... > > Also make sure you're using the latest JDBC driver (early 7.1 RPMs had > one that was out of date). > > Are you doing connection pooling in your web app? Or does each > servlet/JSP connect and disconnect on its own? > > -Doug > -- > The rain man gave me two cures; he said jump right in, > The first was Texas medicine--the second was just railroad gin, > And like a fool I mixed them, and it strangled up my mind, > Now people just get uglier, and I got no sense of time... --Dylan > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Serial Type
Hi Chris, If you do an INSERT straight into the table, AND also make sure you give it the value you want in the serial column, it will accept it and not increment the serial column's counter. There are also a few function which are useful with the serial type : currval() setval() nextval() See http://odbc.postgresql.org/docs/index.php?sql-createsequence.html for more info. Regards and best wishes, Justin Clift Christian Marschalek wrote: > > The serial data type automaticaly takes the next higher value, but what > if there's a drop and I want to assing 3 even if there are 20 records > and the next higher number would be 21 not 3? > > Tia Chris > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] JDBC speed question.
Hi Clayton, >From what I remember, using -p will change BOTH the TCP/IP port and the Unix domain socket. i.e. pg_ctl start -o '-i -p ' Should make PostgreSQL listen on TCP port , and on most unix systems will create a socket file in /tmp/.s.PGSQL. (and its corresponding lock file). Regards and best wishes, Justin Clift Clayton Vernon wrote: > > Thanks, but can I specify each port? I've only seen the one documented > command option "-p" which I assume was for the TCP/IP port. > > Again, thanks for the help, > > Clayton > > - Original Message - > From: "Doug McNaught" <[EMAIL PROTECTED]> > To: "Clayton Vernon" <[EMAIL PROTECTED]> > Cc: "John Oakes" <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]> > Sent: Thursday, April 26, 2001 6:19 PM > Subject: Re: [GENERAL] JDBC speed question. > > > "Clayton Vernon" <[EMAIL PROTECTED]> writes: > > > > > Related question: how do you run postgreSQL to simultaneously support a > Unix > > > socket and TCP/IP? > > > > Just add '-i' to the postmaster startup options. The Unix socket will > > still be available. > > > > -Doug > > -- > > The rain man gave me two cures; he said jump right in, > > The first was Texas medicine--the second was just railroad gin, > > And like a fool I mixed them, and it strangled up my mind, > > Now people just get uglier, and I got no sense of time... --Dylan > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Re: On the _need_ to vacuum...
Totally agreed. It could just be from a lack of people's time to do things, or I wonder if Alfred's patch is doing things which might not be beneficial? (Maybe there have been decisions on a better way to get it done, but it just hasn't been implemented yet). I'm curious also. Regards and best wishes, Justin Clift [EMAIL PROTECTED] wrote: > > I am rather staggered by a developer considering it necessary to > attempt to cooerce the core development team into including a patch. > > If the work that Alfred has done is as effective as he claims, then > there must be a *REALLY* good reason why it isn't being included. > > I don't want to start any form of war > But as a user I'd be interested to know why such a patch would appear > to be unacceptable. > > -- > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > Glen Eustace, > GodZone Internet Services, a division of AGRE Enterprises Ltd., > P.O. Box 8020, Palmerston North, New Zealand 5301 > Ph/Fax: +64 6 357 8168, Mob: +64 21 424 015 > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Report Writer for PostgreSQL
Hi guys, Does anyone have experience with report writers? It's not something I know much about yet. Regards and best wishes, Justin Clift Original Message Subject: Re: [ANNOUNCE] New PostgreSQL Review at epinions.com Date: Thu, 26 Apr 2001 21:19:08 -0400 (EDT) From: Patrick Lanphier <[EMAIL PROTECTED]> To: Justin Clift <[EMAIL PROTECTED]> Well it needs the capability format data on many different graph, capable of generating HTML, PDF, and RTF formats. The server will be running on Linux but the design platform can be whatever. The problem I had with one report writer was the data from the database was present one way and it was not capable on rotating the data for the graph and I wasn't about to do this for the report writer. Is there somebody I should contact that you know about a report writer? Patrick Lanphier The Artemis Group http://www.artemisgroup.com On Fri, 27 Apr 2001, Justin Clift wrote: > No problem Patrick. :-) > > Two questions : > > a) Which operating system(s) does it need to run on? > > b) What features does it need to have? > > Honestly, I haven't done much with report writing, so I'm not going to > be the best person to ask. BUT if you do a quick subscribe to the > [EMAIL PROTECTED] mailing list and ask there, many capable > people are around. :-) > > (You subscribe by sending "subscribe" as a message to > [EMAIL PROTECTED] Unsubscribing later on is the > same, but sending "unsubscribe" ) > > :-) > > Regards and best wishes, > > Justin Clift > > Patrick Lanphier wrote: > > > > Sorry to grab your email address and ask you this. But I have been > > looking for an inexpensive report writer that is feature rich. What are > > your thoughts? > > > > Patrick Lanphier > > The Artemis Group > > http://www.artemisgroup.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] replicating a postgres database
Hi Cheryl, Do you want a one-off copy (a duplicate), or replication which continuously keeps the databases synchronised? Regards and best wishes, Justin Clift Cheryl Ayres wrote: > > is there a way to replicate an existing Postgres database. We want to > duplicate it with a new name and login. > > CA Web Design > Website: www.cadesign.on.ca > Email: [EMAIL PROTECTED] > Phone 519.941.6446 > > Proud member of: > Orangeville & District Chamber of Commerce www.chamber.orangeville.on.ca > Headwaters Country Tourism Association www.headwaterstourism.com > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Re: Success: PostgreSQL 7.1 on MacOS X (Darwin 1.3)
Hi Dieter,
Yes, definitely interested.
What I will do is forward your email to the guys in the PostgreSQL
mailing lists, as it's those guys who would probably make a mirror or
your packages for everyone to use if they like it.
The other thing I can do is make a link to your packages from the
techdocs.postgresql.org website, and I'll also ask you for some
assistance.
Alexander Lohse <[EMAIL PROTECTED]> has put together his notes on
installing PostgreSQL on MacOS X at
http://techdocs.postgresql.org/installguides.html#macosx
If you can work with him on suggestions for expanding, improving, and
updating those notes, that will be a really great contribution.
Thanks heaps for your time and effort Dieter.
Regards and best wishes,
Justin Clift
[EMAIL PROTECTED] wrote:
>
> Dear,
>
> i have sucessfully configure, compile, check (make check), install, create database
>and dbuser without any problems on MacOS X 10.0.1 (Darwin 1.3). Simple tests on a
>database was successfully, too!
> Also, i have made PHP4.04pl1 (with own made patches for work-around Darwins
>"mktime()"; see PHP-bugtraq 8828) as a loadable file for apache. It will tested
>together at the next few days!
>
> I have made a MacOSX like Package file ("Postgresql.pkg") to easy install postgres
>on MacOSX at "/usr/local/pgsql" (there will install a short README how to continue
>the installation; create "postgres" user, initdb, ...).
>
> Do you interest in this package?
> http://home.t-online.de/home/dieter.fiebelkorn/postgresql/Postgresql.pkg.tgz
>
> PHP4 with mktime()-patch, MySQL, PostgreSQL, OpenBase:
> http://home.t-online.de/home/dieter.fiebelkorn/postgresql/libphp4.pkg.tgz
>
> Ciao
> Dieter
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] HOWTO document
Hi all, Has anyone stepped up to review and re-work the present English HOWTO document, the one that has been removed due to the misinformation and inaccuracies in it? If not, I'm going to start on it, with a view to have it ready in the next month or two. Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
RE: [GENERAL] Shared memory failure?
Thanks Tom. Yeah, I heard that an older version PostgreSQL was used. So if I just use a different port number, and not try replacing the version that comes with the OS, everything should work fine? How do I start PostgreSQL on a different port (and which would you recommend)? Thanks. Sincerely, Justin Stayton CometFly Media, LLC. [EMAIL PROTECTED] -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Friday, April 13, 2001 2:59 PM To: [EMAIL PROTECTED] Cc: Steve Wolfe; [EMAIL PROTECTED] Subject: Re: [GENERAL] Shared memory failure? "Justin S." <[EMAIL PROTECTED]> writes: > Cobalt RaQ4i with version 6 of their operating system. Oh. I think Cobalt has a Postgres (6.5.something?) installed as part of the OS. You'll need to pick a different port number than 5432 to avoid conflicting with the system's server. BTW, I've heard that people tried to replace the built-in Postgres with newer releases and had troubles, so I don't recommend trying that unless you're ready to do some debugging. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [GENERAL] Shared memory failure?
Cobalt RaQ4i with version 6 of their operating system. Sincerely, Justin Stayton CometFly Media, LLC. [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Steve Wolfe Sent: Friday, April 13, 2001 1:13 PM To: [EMAIL PROTECTED] Subject: Re: [GENERAL] Shared memory failure? > First off, I'd just like to thank everyone for their help with my last > problem. It worked, but now it gives me another error: > > IpcMemoryCreate: shmget failed (Permission denied) key=5432010, size=144, > permission=700 > This type of error is usually caused by an improper > shared memory or System V IPC semaphore configuration. > For more information, see the FAQ and platform-specific > FAQ's in the source directory pgsql/doc or on our > web site at http://www.postgresql.org. > IpcMemoryAttach: shmat failed (Permission denied) id=2 > FATAL 1: AttachSLockMemory: could not attach segment > > I looked through the troubleshooting guide, but it wasn't very helpful. > Anyone know how to get around this? Thanks again. It's OS-dependant. What OS are you running, and if it's Linux, which kernel? steve ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Shared memory failure?
First off, I'd just like to thank everyone for their help with my last problem. It worked, but now it gives me another error: IpcMemoryCreate: shmget failed (Permission denied) key=5432010, size=144, permission=700 This type of error is usually caused by an improper shared memory or System V IPC semaphore configuration. For more information, see the FAQ and platform-specific FAQ's in the source directory pgsql/doc or on our web site at http://www.postgresql.org. IpcMemoryAttach: shmat failed (Permission denied) id=2 FATAL 1: AttachSLockMemory: could not attach segment I looked through the troubleshooting guide, but it wasn't very helpful. Anyone know how to get around this? Thanks again. Sincerely, Justin Stayton CometFly Media, LLC. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Re: ongoing holy grail thread - VICTORY!!!
Hi Tony, That's great to hear. :-) You need a saying like "'twas a long hard battle, but in the end we emerged victorious" about now. Heh Heh Heh Tony Grant wrote: > Does not work, must be "host db_name IP user ident" > > Maybe this has something to do with java security??? Dunno. Errr... could be. > Please bear with me on this one people: I'll try to write up a sort of > HOWTO to document for this. But it has taken me a week full time so far > and I have to get back to looking after my clients. Weekend is afoot too. Relax, have fun. :-) > For now the secret was a clean install of _everything_ on a developpment > server. Everything meaning RedHat 7.0 on down to the Apache module... > > I think that we should maybe get this info out to Macromedia to > supplement the MySQL stuff that is already in Ultradev. I agree. If you can get the info to the rest of us too, we can make it available for people cruising part the mailing list archives, techdocs website, etc. >From memory Macromedia has their Macromedia Exchange area where people can share and contribe extensions for Dreamweaver, Ultradev, etc. They also have a newsletter. Wonder how you would get a mention that PostgreSQL works with it in their monthly newsletter? Probably be quite a few people interested in hearing that. Regards and best wishes, Justin Clift > Cheers > > Tony Grant > > -- > RedHat Linux on Sony Vaio C1XD/S > http://www.animaproductions.com/linux2.html > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] New look for the techdocs website...
Hi all, I've just taken a bit of time to give a more organised look to the whole techdocs.postgresql.org website. If anyone has a few moments spare, could they please take a look and tell me what they think? I like it, but if most people don't, I'll change it back. Regards and best wishes, Justin Clift P.S. - As a bonus, I've even figured out how to access my [EMAIL PROTECTED] email, so I can start using this again! :-) -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Address already in use?
Hi Everyone, Alright, I've installed PostgreSQL 7.0.3 succesfully, but I still have a problem. When I try starting the PostgreSQL server, using the following command: /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data I get the following error: FATAL: StreamServerPort: bind() failed: Address already in use Is another postmaster already running on that port? If not, remove socket node (/tmp/.s.PGSQL.5432) and retry. Anyone know how to fix this? Thanks. Sincerely, Justin Stayton CometFly Media, LLC. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Non-english articles on the techdocs.postgresql.org website
Hi all, I'm looking at the stats of the domains of the people who are visiting the techdocs website (these stats are publicly viewable at http://s3.thecounter.com/counter/2228050.vdomain) and noticing how many non-english speaking people are visiting the site's front page and then leaving. Any there any people who can understand both english and non-english languages, who wouldn't mind translating an article or two on the techdocs.postgresql.org website to a different language? Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] linux/bsd
Hi Marc,
No argument there. Solaris 8 is a LOT better then previous versions in
regards to driver support, but still a long way behind *BSD, linux,
Windows, etc.
BUT, if you've got hardware it DOES work on... it's really nice.
Regards and best wishes,
Justin Clift
The Hermit Hacker wrote:
>
> On Mon, 9 Apr 2001, Justin Clift wrote:
>
> > Hi Richard,
> >
> > Solaris 8 INTEL is very stable and free for commercial use :
> > http://www.sun.com/solaris/
> >
> > You can download it directly from the web too (full version, no timeouts
> > or anything holding back):
> > http://www.sun.com/software/solaris/binaries/download.html
> >
> > It's kind of picky about which hardware it will work on though. There
> > is a list at :
> > http://soldc.sun.com/support/drivers/hcl/8/101/BOOK.htm
> >
> > If you have hardware it likes, I'd go with Solaris INTEL, otherwise one
> > of the BSD crowd (known for stability) or Linux.
>
> Based on my personal experiences (University I work at is predominantly
> Solaris based for Unix .. .slowly shifting *muhahaha*) ... Solaris INTEL
> is one of the worst OSs I've had the misfortune to deal with :( Ppl
> complain about how the BSD OSs don't support as much hardware as Linux,
> but Solaris takes the cake in that department ... you have to go to
> Adaptec's web site to get drives if you want to use a U160 SCSI
> controller, as *it* isn't supported :(
>
> >
> > Regards and best wishes,
> >
> > Justin Clift
> >
> > Richard Boyes wrote:
> > >
> > > Hello all,
> > >
> > > I'm in the process of making some decisions as to
> > > what OS I should be using for a product that development
> > > will begin on soon. It's a web based thing using postgres,
> > > apache etc and will be all on one machine.
> > >
> > > My question is to what operating system would be a better
> > > one to use for postgres. ie linux/bsd.
> > >
> > > I know postgres works fine on both but some opinion
> > > from the postgres developer community as to which OS
> > > they prefer would have a bearing on this decision.
> > >
> > > PS sorry in advance for posting a question that might
> > >spark a lot of opinions.
> > >
> > > Thanks
> > > Richard.
> > >
> > > ---(end of broadcast)---
> > > TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> >
> > --
> > "My grandfather once told me that there are two kinds of people: those
> > who work and those who take the credit. He told me to try to be in the
> > first group; there was less competition there."
> > - Indira Gandhi
> >
> > ---(end of broadcast)---
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> >
>
> Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [DOCS] Re: [GENERAL] Better Features document?
Hi Joel, Since you've got a 5 page document already, maybe you'd like to put what you've got on the net somewhere (I can put it on techdocs.postgresql.org if you'd like), then massage it into shape for an updated 2 page intro Peter is suggesting? Don't want to lose 3 pages of what may just be really insightful work... :-) Regards and best wishes, Justin Clift Peter Eisentraut wrote: > If you can make it a little less than 5 pages (like 2), we can put it into > the preface of the documentation. I'd imagine it stuck somewhere near the > following URL: -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] www.postgresql.org down?
Hi all, Is it just my route to the main webserver, or is the website at www.postgresql.org down? Can access the mirrors, but can't touch anything at the main PostgreSQL website. Rats, wanted to submit results of 7.1RC3 to Vince's database... Regards and best wishes, Justin Clift ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Postgres 7.1RC1 on Solaris 7
Hi,
It doesn't seem to have been mentioned that the preferable way to
compile PostgreSQL on Solaris is to unset your LD_LIBRARY_PATH and set
an LD_RUN_PATH instead.
That is right isn't it?
Regards and best wishes,
Justin Clift
Ahmed Moustafa wrote:
>
> Hi Tom,
>
> I did the test by hand and here is the output:
>
> bash-2.03# echo "int main() { return 0; }" >conftest.c
> bash-2.03# gcc -o conftest conftest.c -lz -lresolv -lgen -lnsl -lsocket -ldl
> -lm
> bash-2.03# ./conftest
> ld.so.1: ./conftest: fatal: libz.so: open failed: No such file or directory
> Killed
> bash-2.03# ldd conftest
> libz.so => (file not found)
> libresolv.so.2 =>/usr/lib/libresolv.so.2
> libgen.so.1 => /usr/lib/libgen.so.1
> libnsl.so.1 => /usr/lib/libnsl.so.1
> libsocket.so.1 =>/usr/lib/libsocket.so.1
> libdl.so.1 =>/usr/lib/libdl.so.1
> libm.so.1 => /usr/lib/libm.so.1
> libc.so.1 => /usr/lib/libc.so.1
> libmp.so.2 =>/usr/lib/libmp.so.2
> /usr/platform/SUNW,Ultra-5_10/lib/libc_psr.so.1
> bash-2.03#
>
> So, how can I fix the problem, please?
>
> Best Regards,
>
> Ahmed
>
> Tom Lane wrote:
>
> > Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > > Ahmed Moustafa writes:
> > >> I downloaded Postgres 7.1RC1 from
> > >> ftp://ftp.postgresql.org/pub/dev/postgresql-7.1RC1.tar.gz. When I did
> > >> ./configure, I got a error with the test program. I could not figure out
> > >> the problem. Would you take a look at the config.log
> > >> (http://www.photo.net/users/ahmed/config.log) and tell me the problem
> > >> is, please?
> >
> > > In configure, line 6846, can you replace the 'return 0;' by 'exit(0);'?
> > > It's a long shot, but I don't see any other possibility.
> >
> > I am suspicious that it's a library linking issue. Unfortunately,
> > configure unhelpfully /dev/null's the stderr output obtained while
> > trying to execute the test program. (Peter, shouldn't we suggest to
> > the autoconf boys that that output ought to go into config.log?)
> >
> > Please try running the same test scenario by hand so we can see what is
> > printed:
> >
> > echo "int main() { return 0; }" >conftest.c
> > gcc -o conftest conftest.c -lz -lresolv -lgen -lnsl -lsocket -ldl -lm
> > ./conftest
> >
> > It might also be useful to see what "ldd conftest" produces.
> >
> > regards, tom lane
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Need PostgreSQL startup scripts
Hi all, If anybody has a minute or two, can they please email me the startup script they use on their system to automatically start PostgreSQL when the system boots? I have a Linux Mandrake 7.2 startup script for PostgreSQL 7.0.3 on the techdocs.postgresql.org site, and I'm looking for startup scripts for other OS's. Startup scripts that are SANE of course. >From what I understand of Debian, it uses a different method of starting up than Mandrake, so I'd appreciate a copy of the Debian startup script especially. Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] PostgreSQL non-FAQ documentation
Hi all, I've just started starting a project on Sourceforge to contain PostgreSQL information about known bugs for specific versions, work-arounds, and any other general stuff that doesn't belong in a FAQ. Does anyone have anything they'd like to contribute or similar? At www.sourceforge.net/projects/pgdocs :-) Regards and best wishes, Justin Clift Database Administrator
[GENERAL] Re: Sizing of LARGE databases.
Hi Michael, I have recently been working on a database which is presently 14 GB in size and will grow to nearly 1/2 terabyte in the next 12 months. This database is mostly large static two column tables needing fast searches. So far what I've done is split the table into various subtables - for example the entries starting with 'AAA' go in one table, the entries starting with 'AAB' go in another table, etc. After this is done I then index the individual tables. As each table and each index is it's own file, I then stop the PostgreSQL daemon and move the individual files & indexes onto separate RAID drives, then create soft links from the /data/base/ directory to the files. i.e. /opt/postgres/data/base//AAA -> /drives/AAA/AAA /opt/postgres/data/base//AAA_idx -> /drives/AAA/AAA_idx /opt/postgres/data/base//AAB -> /drives/AAB/AAB /opt/postgres/data/base//AAB_idx -> /drives/AAB/AAB_idx and so on. The biggest limitation I have found with this is the /opt/postgres/data/pg_log file seeming to need to log (write) bunches of data, even when just doing searches (reads) on indexes on other tables. No matter how fast all the other disk subsystems are, the speed of the disk system the pg_log file is on creates an 'artificial' upper throughput limit. My recommendation would be (for mostly static data just doing look-ups) to split things into many logical tables and move these tables onto seperate RAID subsystems. Then put the pg_log file onto the fastest disk subsystem you can buy. I haven't yet moved the pg_log file to a different disk than the main postgresql installation and created a soft link to it (this is the next step) but hopefully it won't be a problem. The system in question is PostgreSQL 7.0.3 running on Solaris 7. Regards and best wishes, Justin Clift Database Administrator Michael Miyabara-McCaskey wrote: > > Hello there, > > I have a DB that I am attempting to buy "the right hardware" for. > > This DB today is about 25GB total, with typical tables in the area of 3GB in > size, and up to about 20 million records per table for the big tables. > > Is there any set of recommendations for sizing a DB using PostgreSQL? (I > have all the docs but have not found anything of any use). > > This DB itself will be mostly reads, for a decision system. However, there > will be a lot more data added in the future where the DB could easily grow > to about 150GB in size, and up to about 60millions records for the large > tables. > > My current setup is Linux RH7 w/PostgreSQL 7.0.2, my intention is to build a > large array of Linux Clusters, but I also can not find any documentation on > how if at all PostgreSQL handles a clustered environment. (Although I did > see something that said you could have multiple "databases" and use a create > view to stich together multiple tables from different DBs in a HOWTO on the > www.kernel.org/LDP I have also noticed that the doc is longer there, and > people who have posted about how to do this on these forums have been told > this method does not work). > > Any thoughts would be greatly appreciated. > > Michael Miyabara-McCaskey > Email: [EMAIL PROTECTED] > Web: http://www.miyabara.com/mykarz/ > Mobile: +1 408 504 9014
[GENERAL] Advice needed please
Hi all, As I not familiar with the linux newsgroups and mailing lists, can anyone give me some guidance as to where I should post details of a java development job? It's a Java GUI application which interfaces to a PostgreSQL database backend through JDBC. Regards and best wishes, Justin Clift PostgreSQL Database Administrator
Re: [GENERAL] Memory Leak
Here is a sample of the code which demonstrates the memory problem I am having. The
problem does not occur immediately after memory has been maxed
out. It appears that there is an attempt to recover some memory, about 1 Kbytes,
once max is near. This works for about a half a day to one full day
until everything finally freezes up.
btw: This project has just been dropped into my lap this week, so please excuse my
ignorance. I am wading through one of my co-workers code and trying
to catch up on the research. Your help is greatly appreciated. :)
Since my initial testing I have discovered that doing 1000 updates of 200 rows in a
single transaction is sort of overkill. Only the last 200 updates
will every be seen. Right? :) The code demanstrates the problem nonetheless.
---
int main(int argc, char **argv)
{
int i;
PGconn *conn;/* The connection to the database */
PGresult *res; /* data structure holding query results */
time_t start, end, stime;
time_t begin;
char lastValue[10];
char query[250];
int k;
conn = PQconnectdb (CONNECTION_STRING);
if (PQstatus (conn) == CONNECTION_BAD)
{
printf ("Unable to connect to database\n");
return 1;
}
time (&begin);
for (k=0;;k++)
{
time (&start);
#ifdef USE_TRANSACTIONS
/* Create a transaction, so we can use cursors */
res = PQexec (conn, "BEGIN");
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
PQclear (res);
}
PQclear (res);
#endif
for (i=0; i < 1000; i++)
{
time (&stime);
sprintf (lastValue, "%d%x", i, i);
sprintf (query, DDT_UPDATE_REALTIME, lastValue, ctime (&stime));
res = PQexec (conn, query);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
printf ("Error processing query\n");
PQclear (res);
return 1;
}
/* PQcmdTuples returns an empty string if no rows were affected */
if (strlen (PQcmdTuples (res)) <= 0)
{
printf ("Error processing query\n");
return FALSE;
}
PQclear (res);
}
#ifdef USE_TRANSACTIONS
res = PQexec (conn, "COMMIT");
PQclear (res);
res = PQexec (conn, "VACUUM ANALYZE");
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
PQclear (res);
}
PQclear (res);
#endif
time (&end);
printf ("Inner Loop %d, started: %s: Elapsed time: %d\n", k,
ctime (&begin), (int) (end-start));
}
PQfinish (conn);
return 0;
}
Justin Foster wrote:
> I am using Postgres 7.0.2 :)
>
> Sorry about that. I promise to put done my crack pipe before I send emails :)
>
> Joseph Shraibman wrote:
>
> > 7.1 is in development. Things like this should be discussed in the
> > hackers list.
> >
> > Justin Foster wrote:
> > >
> > > Hello,
> > >
> > > RedHat 7.0, Postgres 7.1 (libpq), Intel Cel 433, 64mb, 15g hd.
> > >
> > > I am running a test which performs 1000 transactions of 1000 updates of a single
>column in a single table, or (1 tranaction = 1000 updates) *
> > > 1000. I have no indecies for any of the columns and the table has 3 columns and
>200 records. I do a VACUUM ANALYZE after every transaction. A
> > > single transaction takes about 3-6 seconds.
> > >
> > > It appears that RAM decreases at about 10 to 100K a second until it is all gone.
> Any thoughts on how I can optimise/configure the db to
> > > alleviate this problem? Any hints on where this leak maybe occurring?
> > >
> > > Thanks,
> > > -justin
> >
> > --
> > Joseph Shraibman
> > [EMAIL PROTECTED]
> > Increase signal to noise ratio. http://www.targabot.com
[GENERAL] Memory Leak
Hello, RedHat 7.0, Postgres 7.1 (libpq), Intel Cel 433, 64mb, 15g hd. I am running a test which performs 1000 transactions of 1000 updates of a single column in a single table, or (1 tranaction = 1000 updates) * 1000. I have no indecies for any of the columns and the table has 3 columns and 200 records. I do a VACUUM ANALYZE after every transaction. A single transaction takes about 3-6 seconds. It appears that RAM decreases at about 10 to 100K a second until it is all gone. Any thoughts on how I can optimise/configure the db to alleviate this problem? Any hints on where this leak maybe occurring? Thanks, -justin
Re: [GENERAL] How to remove a user ?
You can use the command DROP USER username; at the interactive sql prompt. Justin From: "Fred" <[EMAIL PROTECTED]> Reply-To: "Fred" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: [GENERAL] How to remove a user ? Date: Tue, 11 Jul 2000 21:09:53 GMT Hi, How to remove a user in postgres ? Thanks fred Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
[GENERAL] Using the JDBC Driver in version 7.02
Hello. I am using Postgres 7.02 on my redhat linux box. I have installed
the JDK and a JSP engine.
I have also copied the JDBC level 4 Drivers that came with postgresql 7.02
onto my hard drive.
When I try to interface with it in java, even after loading the driver with
the line:
Class.forName("org.postgresql.Driver");
I get an error that the DriverManager cannot find a suitable driver.
The code I use to connect goes as follows:
Connection chamber = DriverManager.getConnection("localhost:5432");
I also tried it as:
Connection chamber =
DriverManager.getConnection("localhost:5432","username","password");
and still no luck.
Can anyone help with this problem?
I have two suspicions: First, that I am not pointing to the right class in
the jar file. Driver.class might be the wrong file to start with. Second,
maybe my server is not taking requests properly, so Java says "hey, this
driver is not working." when it's not the drivers fault, but the servers
fault.
Just so you know, I set up my server to trust all localhost connections.
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
[GENERAL] DELETE COLUMN function
Does anyone have any tips as to how I would go about: 1. deleting a column out of a database 2. changing the width of a column in the database I've looked at the ALTER TABLE function but all it seems to be able to do is to add a column and rename a column. I had thought that I could use it to create a new column of the width desired, copy the old column into the new, and delete the old, then rename the new to the old, but without a delete function this isn't possible... Never retreat. Never surrender. Never cut a deal with a dragon. _______ Justin Long CIO / Site Editor 616 Station Square Ct Network for Strategic Missions Chesapeake, VA 23320 977 Centerville Trnpk CSB 317 [EMAIL PROTECTED] Va Beach, VA 23463 Check out our site at:http://www.strategicnetwork.org
[GENERAL] Speed
I find that it is actually faster to do a SELECT from a single record set, then fetch each individual row and do additional SELECTS to find additional pieces of data from other databases, rather than to a join between two or three databases for example SELECT ctryid, dirid, notes from initiative order by postedon limit 3; (for each individual record in the above select)... select name from country where country.ctryid=iRec.ctryid select name from directory where directory.dirid=iRec.dirid (well this is kind of abstract code but you can follow the logic) Has anyone else noticed that when you do multiple joins the speed of PGSQL6.5 goes down dramatically? This is the difference between 2 seconds for the above and 15 for a join... Is there some way to optimize? Never retreat. Never surrender. Never cut a deal with a dragon. ___ Justin Long CIO / Site Editor 616 Station Square Ct Network for Strategic Missions Chesapeake, VA 23320 977 Centerville Trnpk CSB 317 [EMAIL PROTECTED] Va Beach, VA 23463 Check out our site at:http://www.strategicnetwork.org
[GENERAL] Y2K compliant
Hello all, I was recently asked to determine if PostgreSQL was Y2K compliant. Looking at the questions archive, I found the following: On April 13, 1998, Thomas G. Lockhart writes: > For Postgres v6.3 (and earlier) the default date/time style is "traditional > Postgres". In future releases, the default may become ISO-8601, which > alleviates date specification ambiguities and Y2K collation problems. Does this imply that PostgreSQL is not Y2K compliant but will be in a future release? BTW please CC any replies since I am not on the mailing list. Thank you in advance for any information. -- Sincerely, Jazzman (a.k.a. Justin Hickey) e-mail: [EMAIL PROTECTED] High Performance Computing Center National Electronics and Computer Technology Center (NECTEC) Bangkok, Thailand == People who think they know everything are very irritating to those of us who do. ---Anonymous Jazz and Trek Rule!!! ==
