Re: [GENERAL] Query caching
Daniel Freedman wrote: On the topic of query cache (or maybe this is just tangential and I'm confused): I've always heard that Oracle has the ability to essentially suck in as much of the database into RAM as you have memory to allow it, and can then just run its queries on that in-RAM database (or db subset) without doing disk I/O (which I would probably imagine is one of the more expensive parts of a given SQL command). I've looked for references as to Postgresql's ability to do something like this, but I've never been certain if it's possible. Can postgresql do this, please? And, if not, does it have to hit the disk for every SQL instruction (I would assume so)? PostgreSQL hits the disk on UPDATE/DELETE/INSERT operations. SELECT's are cached, but the default cache is only ½MB of RAM. You can change this to whatever you want. I'm using Cold Fusion and it can cache queries itself, so no database action is necessary. But I don't think PHP and others have this possibility. But Cold Fusion costs 1300$ :( Poul L. Christiansen
[GENERAL] Character type delimiters - can they be changed?
Howdy-
Is there a way to change the delimiters for character/date types from
single quotes to any other character?
For instance, if I'm inserting "O'Brien", rather than doing something like
INSERT INTO Names VALUES ('O''Brien'); I'd like to be able to use: INSERT
INTO Names VALUES (~O'Brien~);
I'm new to the list, so a little background on my environment and project:
I'm loading many massive XML files into Postgres using a Java Program.
I'm using PGQSL v6.5.2 on a Debian GNU/Linux 2.2 server.
I'm using the JDBC6.5-1.2 interface.
Although I can (and presently do) just replace ' with '' on all of my
incoming strings, I have some performance problems, and am looking for
ways to cut down on the overhead. Since XML is basically all character
strings, I'm doing the replace routine on almost every field, so cutting
it out would save me a bundle.
I'm a newbie both to Postgres (convert from Oracle) and to JDBC, so I'm
open to any suggestions, don't assume I've already thought of the
"obvious" solutions!
Thanks
--
---
Nick Fankhauser
Business: [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko Co. Software Consulting Services
http://www.ontko.com/
Personal: [EMAIL PROTECTED] http://www.infocom.com/~nickf
smime.p7s
Re: [GENERAL] Query caching
PostgreSQL hits the disk on UPDATE/DELETE/INSERT operations. SELECT's are cached, but the default cache is only ½MB of RAM. You can change this to whatever you want. I'm using Cold Fusion and it can cache queries itself, so no database action is necessary. But I don't think PHP and others have this possibility. But Cold Fusion costs 1300$ :( No, PHP has this. -- Sincerely Yours, Denis Perchine -- E-Mail: [EMAIL PROTECTED] HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 --
[GENERAL] Character type delimiters - Can they be changed?
I apologize for the second post- I think my digital signature may have
screwed up the first one, so I'm sending this again just to be sure... -NF
Howdy-
Is there a way to change the delimiters for character/date types from
single quotes to any other character?
For instance, if I'm inserting "O'Brien", rather than doing something like
INSERT INTO Names VALUES ('O''Brien'); I'd like to be able to use: INSERT
INTO Names VALUES (~O'Brien~);
I'm new to the list, so a little background on my environment and project:
I'm loading many massive XML files into Postgres using a Java Program.
I'm using PGQSL v6.5.2 on a Debian GNU/Linux 2.2 server.
I'm using the JDBC6.5-1.2 interface.
Although I can (and presently do) just replace ' with '' on all of my
incoming strings, I have some performance problems, and am looking for
ways to cut down on the overhead. Since XML is basically all character
strings, I'm doing the replace routine on almost every field, so cutting
it out would save me a bundle.
I'm a newbie both to Postgres (convert from Oracle) and to JDBC, so I'm
open to any suggestions, don't assume I've already thought of the
"obvious" solutions!
Thanks
--
Nick Fankhauser
Business: [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko Co. Software Consulting Services http://www.ontko.com/
Personal: [EMAIL PROTECTED] http://www.infocom.com/~nickf
Re: [GENERAL] Query caching
On Wed, Nov 01, 2000 at 10:16:58AM +, Poul L. Christiansen wrote: PostgreSQL hits the disk on UPDATE/DELETE/INSERT operations. SELECT's are cached, but the default cache is only ½MB of RAM. You can change this to whatever you want. That sound like a very cool thing to do, and the default seems awfully conservative, given the average server´s RAM equipment nowadays. If you have a small Linux server with 128 MB of RAM, it would be interesting to see what happens, performance-wise, if you increase the cache for selects to, for instance, 64 MB. Has anyone tried to benchmark this? How would you benchmark it? Where do you change this cache size? How do you keep the cache from being swapped out to disk (which would presumably all but eradicate the benefits of such a measure)? Cheers Frank -- frank joerdens joerdens new media urbanstr. 116 10967 berlin germany e: [EMAIL PROTECTED] t: +49 (0)30 69597650 f: +49 (0)30 7864046 h: http://www.joerdens.de pgp public key: http://www.joerdens.de/pgp/frank_joerdens.asc
Re: [GENERAL] SQL question - problem with INTERSECT
If I remove the "GROUP BY messages.msgid ...", then the result will be messages whose subject contains either 'Hello' or 'There' in the subject, but not necessarily both. I want messages which have both 'Hello' and 'There' in the subject, and both 'Jim' and 'Jones' in the author. (For example, if I needed all of 'Hello', 'There', and 'Now' in the subject, my first HAVING clause would use a count of 3, while the second HAVING clause would still use a count of 2, for both 'Jim' and 'Jones'.) So I cannot remove either having clause without changing the meaning. What I would really like to know is why INTERSECT does not allow this. If I understand that, maybe I can figure out how to get what I need. -Original Message- From: Igor Roboul [EMAIL PROTECTED] To: PGSQL-General [EMAIL PROTECTED] Date: Wednesday, November 01, 2000 12:03 AM Subject: Re: [GENERAL] SQL question - problem with INTERSECT On Mon, Oct 30, 2000 at 07:59:06PM -0500, Keith L. Musser wrote: "(SELECT messages.msgid FROM messages, subject_index WHERE ((subject_index.word='Hello' or subject_index.word='There') and (subject_index.msgid = messages.msgid)) GROUP BY messages.msgid HAVING count(messages.msgid)=2) INTERSECT (SELECT messages.msgid FROM messages, author_index WHERE ((author_index.word='Jim' or author_index.word='Jones') and (author_index.msgid = messages.msgid)) GROUP BY messages.msgid HAVING count(messages.msgid)=2);" Try removing first "GROUP BY messages.msgid HAVING count(messages.msgid)=2)" -- Igor Roboul, Unix System Administrator Programmer @ sanatorium "Raduga", Sochi, Russia http://www.brainbench.com/transcript.jsp?pid=304744
Re: [GENERAL] True ACID under linux (no fsync)?
On 31 Oct 2000, Gary Howland wrote: Just a quickie - I heard that linux does not have a working fsync() call At least the manpage for fsync says that it does. The implementation: /* .. finally sync the buffers to disk */ dev = inode-i_dev; return sync_buffers(dev, 1); It really looks like it IS implemented. But probably on Linux not just the file data/metadata is synced, also all that device's data, which makes it very inefficient, but presumably `safe'. NB: don't forget that fsync() merely ensures that data was sent to the disk controller. Maybe this one has a cache (e.g. a fast SCSI harddrive), and if power fails, well. If fsync() was calling the SCSI FLUSH command, maybe that could be done, but that would not just sync the file.
[GENERAL] mysqldump export and pg_dump import
Hello everybody, We have a MySQL database, and we're planning to migrate all to a PostgreSQL database. We must migrate all the data to a development system, in order to take real performance numbers. The problem is we don't know how to convert the files generated by mysqldump in order to import them with pg_dump (and have therefore the same tables and data). Does anybody done this before? Is there any script to automate the process? Please, send the answer with a CC to my personal address ([EMAIL PROTECTED]) since I'm not already subscribed to the mailing list. Thank you very much in advance for your help. -- Alberto Otero García e-mail: [EMAIL PROTECTED] Cometa Technologies, S.L. URL: http://www.cometatech.com
Re: [GENERAL] Query caching
Frank Joerdens wrote: On Wed, Nov 01, 2000 at 10:16:58AM +, Poul L. Christiansen wrote: PostgreSQL hits the disk on UPDATE/DELETE/INSERT operations. SELECT's are cached, but the default cache is only ½MB of RAM. You can change this to whatever you want. That sound like a very cool thing to do, and the default seems awfully conservative, given the average server´s RAM equipment nowadays. If you have a small Linux server with 128 MB of RAM, it would be interesting to see what happens, performance-wise, if you increase the cache for selects to, for instance, 64 MB. Has anyone tried to benchmark this? How would you benchmark it? Where do you change this cache size? How do you keep the cache from being swapped out to disk (which would presumably all but eradicate the benefits of such a measure)? I have a PostgreSQL server with 80MB of RAM running Redhat Linux 7.0 and in my /etc/rc.d/init.d/postgresql start script I have these 2 lines that start the postmaster. echo 67108864 /proc/sys/kernel/shmmax su -l postgres -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster -o '-i -B 4096 -o -F' start /dev/null 21" /dev/null The first line increases the maxium shared memory to 64MB. The "-B 4096" indicates 4096 * 8kb = 32MB to each postmaster. I haven't benchmarked it, but I know it's MUCH faster. Poul L. Christiansen
[GENERAL] Does column header support multibyte character?
Can I use chinese as the column header? Thanks Dave
RE: [GENERAL] postgres on redhat 7.0
In general I am pretty pissed at RH attitude to system upgrade, if I were working in a Production environment, I would either hire them and not try anything myself, which kinda contradicts the whole Linux philosophy. Can this kind of stuff get put on a Red Hat mailing list, rather than sent here? Thanks! Rob Nelson [EMAIL PROTECTED]
Re: [GENERAL] postgres on redhat 7.0
It also relates back to a post I made many moons ago. I don't upgrade any OS (outside of minor patches). If it is a new OS version, backup and then clean install. Just too many variables to contend with to trust a type of upgrade script. Adam Lang Systems Engineer Rutgers Casualty Insurance Company - Original Message - From: "Steve Wolfe" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 31, 2000 5:03 PM Subject: Re: [GENERAL] postgres on redhat 7.0 I was the original poster. Really my confusion stemmed from the fact that upgrading from RH6.0 to RH7.0, 7.0 complained (during boot) that my Postgress verision was outdated I need to upgrade. This threw me off. Yeah, the init script that they provide checks what's in PG_VERSION, and tells you to upgrade. The bad side is that it may have already overwritten your old binaries, etc., making it difficult to do a dump. You'd have to reinstall the old one, do the dump, then upgrade PostgreSQL, and then reinsert. Hopefully, their upgrade system was smart enough to not blindly overwrite your old PostgreSQL installation. If it did blindy overwrite it, then it's a very poorly written "upgrade", even Microsoft does better than that in a lot of situations. It would give further validation to my refusal to ever use RedHat's upgrade procedure. This isn't to say that RedHat is the devil. Just that like all *nix varieties, it has it's own behavioural deficiencies that need to be recognized and worked around. If there was a *nix that didn't have deficiencies, then all of the other varieties would quickly go away. In general I am pretty pissed at RH attitude to system upgrade, if I were working in a Production environment, I would either hire them and not try anything myself, which kinda contradicts the whole Linux philosophy. Well, it certainly doesn't contradict the RedHat philosophy of "Give them the product for free, then charge for support." ; )
Re: [GENERAL] postgres on redhat 7.0
I wouldn't say ditch out on Redhat because of Postgres upgrades. As was mentioned on the list before, there is no current silver bullet upgrade for postgres. Dump, install new version, import old data. I would assume then that means SUSE would be no better on that regard either. Also, any bad experiences I have had with Redhat are not with Redhat themselves, per se. Mainly with RPMs. They really just don't mix well with applications you compile... OK, and maybe the occasional wierd directory structure that goes on, but other than that, I still choose them over other distributions. Adam Lang Systems Engineer Rutgers Casualty Insurance Company - Original Message - From: "os390 ibmos" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 31, 2000 5:14 PM Subject: Re: [GENERAL] postgres on redhat 7.0 Exactly my point. My next Linux server that I build is going to be SUSE, atleast some guys are not in a hurry to go public. From: "Steve Wolfe" [EMAIL PROTECTED] Well, it certainly doesn't contradict the RedHat philosophy of "Give them the product for free, then charge for support." ; ) _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. Share information about yourself, create your own public profile at http://profiles.msn.com.
Re: [GENERAL] Case insensitive LIKE queries
select * from myTable where upper(myField) like 'TEST'; Upper will change the fields to upper case for testing purposes. Adam Lang Systems Engineer Rutgers Casualty Insurance Company - Original Message - From: "Yann Ramin" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 01, 2000 12:05 AM Subject: [GENERAL] Case insensitive LIKE queries Hello, What would be the most effective way of preforming a case-insensitive LIKE query? I've notived that MySQL somehow ALWAYS does it case insensitive, which is not very smart, but I should expect this feature to exist without reasonable pain on the programmer;s end? Yann -- Yann Ramin [EMAIL PROTECTED] Atrus Trivalie Productions www.redshift.com/~yramin AIM oddatrus Marina, CA http://profiles.yahoo.com/theatrus IRM Developer Network Toaster Developer SNTS Developer KLevel Developer Electronics Hobbyist person who loves toys Build a man a fire, and he's warm for a day. Set a man on fire, and he'll be warm for the rest of his life. "I'm prepared for all emergencies but totally unprepared for everyday life."
Re: [GENERAL] postgres on redhat 7.0
"Robert D. Nelson" wrote: In general I am pretty pissed at RH attitude to system upgrade, if I were working in a Production environment, I would either hire them and not try anything myself, which kinda contradicts the whole Linux philosophy. Can this kind of stuff get put on a Red Hat mailing list, rather than sent here? Thanks! pgsql-ports for PostgreSQL related stuff. I will announce soon some exciting news related to the RPM's, as well as a dedicated 'postgresqlrpms' mailing list, and the RPM spec files, patches, etc, under a public CVS server. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Re: [GENERAL] Case insensitive LIKE queries
Or use the regular expression syntax for case insensitive pattern matching e.g. SELECT foo FROM bar WHERE foo *= 'aBc'; For more info, see: http://www.postgresql.org/users-lounge/docs/7.0/user/operators2123.htm Hope that helps, Ol. select * from myTable where upper(myField) like 'TEST'; Upper will change the fields to upper case for testing purposes. Adam Lang Systems Engineer Rutgers Casualty Insurance Company - Original Message - From: "Yann Ramin" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 01, 2000 12:05 AM Subject: [GENERAL] Case insensitive LIKE queries Hello, What would be the most effective way of preforming a case-insensitive LIKE query? I've notived that MySQL somehow ALWAYS does it case insensitive, which is not very smart, but I should expect this feature to exist without reasonable pain on the programmer;s end? Yann -- Yann Ramin [EMAIL PROTECTED] Atrus Trivalie Productions www.redshift.com/~yramin AIM oddatrus Marina, CA http://profiles.yahoo.com/theatrus IRM Developer Network Toaster Developer SNTS Developer KLevel Developer Electronics Hobbyist person who loves toys Build a man a fire, and he's warm for a day. Set a man on fire, and he'll be warm for the rest of his life. "I'm prepared for all emergencies but totally unprepared for everyday life." ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. SCEE **
[GENERAL] Array Problem
I'm working with version 6.5.2 and I've created a test table that contains both a one and a two dimensional array. CREATE TABLE testa ( name text, links int2[], vals int2[][] ); I'v populated the table with at least one record, so I should be able run 'select' requests that deliver something *meaningful*. If I select everything or specify an entire array, the query works OK. SELECT name, links FROM testa;# OK If I specify a particular array subscipt, for example: SELECT name, links[1] FROM testa; I get an error message that says: ERROR: Unable to locate type name 'vals' in catalog Yet the documentation seems to indicate that this is a valid *select* statement. Any suggestions? Upgrading to 7.0 is not an option at the present time. Thanks. -- John Burski Lead Programmer 911 Emergency Products, Inc. St. Cloud, MN 56301
[GENERAL] Newbie Question
Is there an upper limit on the size of a "text" character field? If so, how can I extend it? Thanks, John begin:vcard adr;dom:;;232 E. Lyons;Spokane;WA;99208; n:Pilley;John x-mozilla-html:FALSE org:Settlement Plus, Inc. version:2.1 email;internet:[EMAIL PROTECTED] title:Software Engineer tel;fax:509-484-7265 tel;work:509-484-7165 x-mozilla-cpt:;0 fn:John Pilley end:vcard
Re: [GENERAL] Array Problem
On Wed, 1 Nov 2000, John Burski wrote: I'm working with version 6.5.2 and I've created a test table that contains both a one and a two dimensional array. CREATE TABLE testa ( name text, links int2[], vals int2[][] ); I'v populated the table with at least one record, so I should be able run 'select' requests that deliver something *meaningful*. If I select everything or specify an entire array, the query works OK. SELECT name, links FROM testa;# OK If I specify a particular array subscipt, for example: SELECT name, links[1] FROM testa; I get an error message that says: ERROR: Unable to locate type name 'vals' in catalog Yet the documentation seems to indicate that this is a valid *select* statement. Since I don't have 6.5 floating around anymore I can't check it. I do see that it works on CVS sources and on 7.0. My guess is that it was a bug fixed between versions. If you can't go up to 7.0, you might see about trying 6.5.3 just to see if a fix was backpatched.
[GENERAL] psql defaults file?
I couldn't find anything in the man page about this -- does psql check for ~/.psql or anything so I don't have to manually --pset pager=off every time I run it? If there is such a file, what is the format for specifying options? -Jonathan
RE: [GENERAL] Newbie Question
John- According to the documentation and Bruce M's book, there is no limit. I've never hit a limit while putting some pretty large (three page) narratives in a text field. Practically speaking, I would guess that one will take a performance hit due to fragmentation when storing big chunks of data comingled with smaller chunks. -Nick Fankhauser -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of John Pilley Sent: Wednesday, November 01, 2000 12:10 PM To: [EMAIL PROTECTED] Subject: [GENERAL] Newbie Question Is there an upper limit on the size of a "text" character field? If so, how can I extend it? Thanks, John
[GENERAL] a web interface to visualize tables
Hello, I need a tool to interactively visualize (not administer) DB tables from a web interface. Ideally this tool would let me: - rename column headers, - set cell alignments, widths, background colors, - reorder columns, - save all these visualisation settings in a DB, - it would be written in perl (even better: mod_perl), - uses the DBI interface, Does such a beast exist? I am in the process of writing one, so I thought I'd check first... Thanks in advance, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org "Kill a man, and you are an assassin. Kill millions of men, and you are a conqueror. Kill everyone, and you are a god." -- Jean Rostand
Re: [GENERAL] Increasing Table Column Size in 7.0 Syntax
This requires you to change NAMEDATALEN in src/include/postgres_ext.h. Note this requires a recompile, initdb, createdb, etc. Also note that databases with different NAMEDATALEN's can't interoperate. Wade Hello, Looking at the docs for pgsql I have only found stuff on altering a table for default and renaming a column but nothing on changing the size. I want to increase the size of a field from 2048 to 4096. What is the syntax for this? Thanks, J
Re: [GENERAL] Newbie Question
On Wed, Nov 01, 2000 at 12:32:58PM -0500, Nick Fankhauser wrote: According to the documentation and Bruce M's book, there is no limit. I've never hit a limit while putting some pretty large (three page) narratives in a text field. There is no limit on the "text" type as such, but there is still the limit on the total size of a row: about 8k by default, 32k if you're willing to recompile (see the FAQ). This limit will be removed entirely by TOAST in 7.1 . Richard
[GENERAL] Hardwood Website
Hardwood floors and more! See the natural beauty of hardwood flooring and hardwood designs at www.classicdistributors.com or http://209.35.59.31!
[GENERAL]
set digest
