[GENERAL] Pgsql 7.3.3 on redhat 7.2
> I can't get a rest for a min guys. > > I go away for the weekend and my server is getting this error. > > Fatal error: Call to undefined function: pg_connect() in > /var/www/html/crohns/phpBB2/db/postgres7.php on line 79 Your PHP doesn't have support for PostgreSQL. Look at phpinfo(); ---> >From the backup to the current php.ini file... this is what was changed... Looks like an autoupdate on php happened. This was turned off in the current php.ini ; File Uploads ; ; Whether to allow HTTP file uploads. file_uploads = Off the backup had this tuned on ? this cause it? -Dan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] pgsql error - situation update
My sysadmin just called and tol me he updated the php.rpm... he happens to be in California totally across the US from me for a week... So I check the ver and... Php version 4.1.2-7.2.6 recently updated - yesterday Postgresql 7.3.3 installed in august sometime - upgraded from basic redhat 7.2 postgresql Since he upgraded the php rpm this problem started. Fatal error: Call to undefined function: pg_connect() in /var/www/html/crohns/phpBB2/db/postgres7.php on line 79 in website address http://www.dealingwithcrohns.com/phpBB2/index.php So you maybe right on recompiling and if so...how do I go about that -Dan ...lost in pgsql... ..many thanks.. ---(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 7.3.3 on redhat 7.2
On 22/10/2003 14:36 frbn wrote: hi, it seems you use the /usr/bin/pg_dumpall featured by the redhat default install verify this with "which pg_dumpall" (rpm -ql postgresql to see all the files of this package) you upgraded from sources didn't you ? your fresh new install has the default prefix /usr/local (redhat uses /usr) 2 ways : recompile/install with ./configure --prefix=/usr ... or remove completely the redhat postgresql package (rpm -e postgresql) I've found the easiest way to over-write a RPM installed version with one one compiled from source is to use pg_config --configure to get all the original options which the RMP version was built with. I then supply these options to the configure script. This way, I know that the new version will over-write the old version. HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] ShmemAlloc errors
Nick Burrett <[EMAIL PROTECTED]> writes: > I expected that specifying a specific length for a column would allow > for more efficient indexing and searching because: > a) you already know the exact length of the column > b) potentially the column-data could be stored starting on a word-boundary > c) apply string comparison functions that are optimised for data > starting on word-boundaries (i.e. by comparing words rather than bytes). > Certainly for the C-locale. None of those considerations really apply for Postgres. We used to have some (very marginal anyway) optimizations that assumed CHAR(n) is fixed-width, but they went away when we added support for multibyte character encodings. In any case there isn't anything here that wouldn't be swamped by increased I/O demands due to the wasted space. Maybe if all your machine names run between 29 and 32 characters it'd be worth doing, but if you're paying any actual space cost to padding I think it has to be a net loss. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Recomended FS
- Original Message - From: "Shridhar Daithankar" <[EMAIL PROTECTED]> To: "Ben-Nes Michael" <[EMAIL PROTECTED]> Cc: "Nick Burrett" <[EMAIL PROTECTED]>; "postgresql" <[EMAIL PROTECTED]> Sent: Monday, October 20, 2003 3:06 PM Subject: Re: [GENERAL] Recomended FS > Ben-Nes Michael wrote: > > >>I'm saying: don't expect your DB performance to come on leaps and bounds > >>just because you changed to a different filesystem format. If you've > >>got speed problems then it might help to look elsewhere first. > >> > > > > I dont expect miracles :) > > but still i have to choose one,so why shouldnt i choose the one which best > > fit ? > > All things being equal, you should optimise your application design and database > tuning before you choose file system. > > If a thing works well for you, with a better file system it will just work > better. That's the point. > I agree, but still ill have to choose an FS, does the list have any opinion on what FS to choose ? > Shridhar > > ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] tsearch2 and aspell
Hello Can I use tsearch2 with aspell? I didn't find any info about it, and I don't know anything about difference between ispell and aspell. Thank You Pavel Stehule ---(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] Nullable 'Foreign Key-like' Constraint
Ron, I have done this by adding the attribute to the table with nulls allowed and adding a constraint to the table for the foreign key... works like a charm: CREATE TABLE TESTTYPE ( TESTTYPEKEY char(30) NOT NULL, TESTTYPENAME varchar(255) NULL, TESTTYPEDESC varchar(255) NULL, TESTTYPELABEL varchar(255) NULL, CONSTRAINT XPKTESTTYPE PRIMARY KEY (TESTTYPEKEY) ) ; CREATE TABLE TEST ( TESTKEY char(30) NOT NULL, TESTTYPEKEY char(30) NULL, CONSTRAINT LOG_PK PRIMARY KEY (TEST_PK), CONSTRAINT testtype_test FOREIGN KEY (TESTTYPEKEY) REFERENCES TESTTYPE ) ; Karen L. Grose Vigilos Inc. -Original Message- From: Ron [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 9:02 AM To: [EMAIL PROTECTED] Subject: [GENERAL] Nullable 'Foreign Key-like' Constraint I posted this to 'questions' yesterday instead of 'general' by mistake. Sorry if anyone received duplicates. Mandatories: Ver 7.3.4, Redhat Linux 8.0, P4, 2GB RAM I want to add a 'nullable' foreign key to a column in a table. I have tables "company" and "project" which may be related by company.companyID <-> project.companyID. project.companyID is allowed to be null. However, when someone tries to delete a company which is still referenced in "project" I want a constraint restricting deletion. I tried: ALTER TABLE company ADD CONSTRAINT company_is_ta CHECK (companyID IN (SELECT companyID FROM project)); and I receive: ERROR: cannot use subselect in CHECK constraint expression Then I came across this previous post which showed how to set it up when the table is created. I tried it and it works for a new table, but I can't get it to work with existing tables. 1) My attempt: ALTER TABLE project ALTER COLUMN companyID SET DEFAULT NULL; ALTER TABLE project ADD CONSTRAINT company_is_ta companyID REFERENCES company(companyID); (plus variations on the above, resulting in errors, all similar to:) ERROR: parser: parse error at or near "companyID" at character 53 2) based on this previous posting: > From: Manfred Koizar ([EMAIL PROTECTED]) > Subject: Re: NULL Foreign Key > Newsgroups:comp.databases.postgresql.general, > comp.databases.postgresql.questions > Date: 2002-07-17 05:51:19 PST > On Tue, 16 Jul 2002 17:10:32 -0700, "Kuhn, Dylan K (NDTI)" > <[EMAIL PROTECTED]> wrote: > >Can I make a foreign key that is allowed to be NULL? > Yes: > fred=# CREATE TABLE father (i INT PRIMARY KEY); > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index > 'father_pkey' for table 'father' > CREATE > fred=# CREATE TABLE son (i INT REFERENCES father); > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY > check(s) > CREATE > fred=# INSERT INTO father VALUES (1); > INSERT 183317 1 > fred=# INSERT INTO son VALUES (1); > INSERT 183318 1 > fred=# INSERT INTO son VALUES (2); > ERROR: referential integrity violation - key referenced > from son not found in father > fred=# INSERT INTO son VALUES (NULL); > INSERT 183320 1 > Servus > Manfred Anyone know how I can get this to work? BTW I don't want to use 'ignore' rules when someone attempts to delete the company as I want the constraint message to be shown in the app's browser. TIA Ron ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(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] SCSI vs. IDE performance test
> -Original Message- > From: Stephen [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 22, 2003 9:02 AM > To: [EMAIL PROTECTED] > Subject: Re: [GENERAL] SCSI vs. IDE performance test > > > The SCSI improvement over IDE seems overrated in the test. I > would have expected at most a 30% improvement. Other reviews > seem to point out that IDE performs just as well or better. > > See Tom's hardware: > http://www20.tomshardware.com/storage/20020305> /index.html > My own tests show that 15K RPM ultra 320 SCSI drives are considerably faster than any IDE storage. This ATA drive: http://www.wdc.com/en/products/WD360GD.asp Performs as well or better than many SCSI drives, and are not terribly expensive. Therefore, these are a very good choice if price performance is more important than absolute performance. But if you need absolute horsepower, then one of these (or other 15K Ultra320 equivalent) won't be beaten: http://www.storagereview.com/articles/200304/200304068C073x0_1.html ---(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] Recomended FS
Ben-Nes Michael wrote: - Original Message - From: "Nick Burrett" <[EMAIL PROTECTED]> Ben-Nes Michael wrote: But still the greatest question is what FS to put on ? I heard Reiesref can handle small files very quickly. Switching from ext3 to reiserfs for our name servers reduced the time taken to load 110,000 zones from 45 minutes to 5 minutes. However for a database, I don't think you can really factor this type of stuff into the equation. The performance benefits you get from different filesystem types are going to be small compared to the modifications that you can make to your database structure, queries and applications. The actual algorithms used in processing the data will be much slower than the time taken to fetch the data off disk. So you say the FS has no real speed impact on the SB ? In my pg data folder i have 2367 files, some big some small. I'm saying: don't expect your DB performance to come on leaps and bounds just because you changed to a different filesystem format. If you've got speed problems then it might help to look elsewhere first. -- Nick Burrett Network Engineer, Designer Servers Ltd. http://www.dsvr.co.uk ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Simple SQL
On Fri, Oct 24, 2003 at 07:25:44AM +0200, Pavel Stehule wrote: > Not > > SELECT 1 as a, a*2; > ERROR: column "a" does not exist But you can try SELECT a, a*a FROM (select 1 as a); > On Tue, 21 Oct 2003, Bob Messenger wrote: > > > Is it possible to do something like: > > > > select 1 as a, a*a; > > > > in postgres? -- Alvaro Herrera () "Hay que recordar que la existencia en el cosmos, y particularmente la elaboración de civilizaciones dentre de él no son, por desgracia, nada idílicas" (Ijon Tichy) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] plpgsql: return multiple result sets or temp table
Jeff, thank you for the time and suggestion. I'm also trying to use SETOF custom_type as a solution Oksana ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Desperate help needed for Replication
On Thu, Oct 23, 2003 at 07:53:08PM -0300, Martin Marques wrote: > El Jue 23 Oct 2003 19:41, Chris M. Gamble escribi?: > > I am trying to perform what I best understand as Multi-master asynchronous > replication for postgres 7.3.3 servers. After researching, I tried the > 1) I think the best shot should be eRServ It most certainly would not. Erserver is good for exactly one thing (and some people think it isn't good at this): single-master, possibly-multi-slave asynchronous replication. Given its current design, it will _never_ be able to do multi-master replication unless it is "data merge" sort of stuff (e.g. table1 and table 2 on db1; table 3 and table 4 on db2; and table1, table 2, table3, and table4 on db3). A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(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] Desperate help needed for Replication
On Thu, Oct 23, 2003 at 05:55:10PM -0500, Chris M. Gamble wrote: > update replicated to all others. It looked as though I could > only update at 1 server. That's right. At the moment, AFAIK, nobody can offer what you want, although I have heard suggestions otherwise. The distance that IK travels is limited :-/ A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Hierarchical query's
Cláudia Morgado <[EMAIL PROTECTED]> writes: > Oracle has the option with the SQL CONECT BY statement to run through a > hierarchical database with a single SQl-statement: Not really. There is a package in the contrib directory called tablefunc that has a function called connect_by that can do some things like this. I have no experience with it and don't know if it would meet your needs. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Setting up DSPACE for Postgres access
Have you tried placing the jars in the $JAVA_HOME/jre/lib/ext directory? Jars there are automatically searched by the JVM. On Thursday, October 23, 2003, at 01:52 PM, Richard Huxton wrote: On Thursday 23 October 2003 18:07, Ashwin Kutty wrote: Now, the postgresql.jar gets pointed to twice; once from the script with the JARS var and the second with the pointing to it from the $CLASSPATH var set by me. I even echo the $FULLPATH var right before it hits the java command and the echo brings up the huge PATH created by the script that contains the jar files and the directories. I dont get however how the script can have it in the $FULLPATH var but not use it when sent to the command line? Given everything else you've tried, I think you might be on the right track here and somehow your environment variables are getting clobbered. How or why I couldn't say (I'd suspect Tomcat, but you say you can't even run the create-administator app). The only other thing I can think of is that for some reason it's looking for the wrong class. If dspace comes with the source, it might be worth checking whether there isn't a mistake in the Class.forname() call. I must admit, I don't know much java - can you use "strace" to track system calls, or is there a similar utility that would let you see what files it is searching for? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com ---(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
[GENERAL] shared memory on OS X - 7.4beta4
I get this when I try to start up a freshly compiled beta4 on OS X 10.2.6: FATAL: could not create shared memory segment: Invalid argument I saw a previous thread on this for beta2. It sounded like this was a bug that was to be fixed in beta3, but I'm still having this problem with beta4. Thanks, John DeSoi, Ph.D. ---(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] Hierarchical query's
Hello PostgreSQL doesn't support h.q and I will not support this type of h.q. Supported syntax will be like db2 - ANSI SQL 2kx. But you can try hier patch - http://gppl.terminal.ru/readme.html. This is a patch which allows PgSQL to perform hierarchical queries like Oracle does. Regards Pavel Stehule On Thu, 23 Oct 2003, [iso-8859-1] Cláudia Morgado wrote: > Hello! > > Oracle has the option with the SQL CONECT BY statement to run through a hierarchical > database with a single SQl-statement: > > > > Result-set (example): > > ms_id parent_id 1 1.1 1 1.1.1 1.1 1.1.2 1.1 1.1.3 1.1 1.2 1 1.2.1 1.2 > > etc > > > I need to do this in PostgreSql. > > This is possible in the Postgre? > > > regards, > Cláudia Morgado and Carla Mello > > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] SCSI vs. IDE performance test
Unwrap this link (if your newsreader folds it) and click on it for hard drive performance: http://www.storagereview.com/php/benchmark/compare_rtg_2001.php?typeID=1 0&testbedID=3&osID=4&raidconfigID=1&numDrives=1&devID_0=232&devID_1=237& devID_2=213&devID_3=221&devID_4=216&devID_5=249&devID_6=250&devCnt=7 The important part for database is "Server Suite" ---(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] Recomended FS
Ben-Nes Michael wrote: But still the greatest question is what FS to put on ? I heard Reiesref can handle small files very quickly. Switching from ext3 to reiserfs for our name servers reduced the time taken to load 110,000 zones from 45 minutes to 5 minutes. However for a database, I don't think you can really factor this type of stuff into the equation. The performance benefits you get from different filesystem types are going to be small compared to the modifications that you can make to your database structure, queries and applications. The actual algorithms used in processing the data will be much slower than the time taken to fetch the data off disk. -- Nick Burrett Network Engineer, Designer Servers Ltd. http://www.dsvr.co.uk ---(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] HTML generation with PL/PgSQL
On Thursday 23 October 2003 11:38, Birahim FALL wrote: > Is such a product exists (preferably opensource). > I'm ready to go for python etc, but I really wuld have to re-educate > myself. Bir, There are a LOT of ways to make web pages with data from Postgres. I'd suggest you get: The latest Postgres Apache2 (http://httpd.apache.org) Mod_Python (http://www.modpython.org) Get the latest CVS version (3.1.2b at this writing). Python 2.3.2+ (http://www.python.org) Draco (http://draco.boskant.nl) 0.99.4 or (Quixote http://freshmeat.net/redir/quixote/18740/url_homepage/quixote) and Psycopg (http://freshmeat.net/projects/psycopg) and whatever few dependencies these have (mxDateTime comes to mind). Get the latest versions of everything. That's a complete kit to make Anything You Want. Draco and Quixote take much different approaches to gluing Python to web pages. Both are worth a good look, just to understand their ways of doing things. Both are different from the rather inelegant way of printing each HTML element using special coding, which Oracle looks like it uses. Draco has strong logic/presentation seperation. Quixote takes the unique approach of allowing you to embed HTML in Python rather than the more common opposite. There are LOTS of other packages to "glue" HTML and databases together in Python. I wouldn't look anywhere beyond Python. I've found Draco to be very approachable and well designed and fairly complete. (You will need the CVS version 3.1.2b of mod_python or newer to make it work right). Everyone, of course, disagrees on what combination is be the Right Thing. These are my favorites, for now. I'm re-educating myself out of Perl, Procedural Programming and other Wrong Things (ducking to avoid flames). It's a hill to climb but I'm really enjoying it and have Big Plan$ that Postgres will be driving. :-) Happy Trails, Scott ---(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] how to use pg_resetxlog
Title: RE: [GENERAL] how to use pg_resetxlog --Thanks to all that replied --I did the pg_resetxlog -f $PGDATA and --it generated a new log, but when I try to --restart the database, it fails. I look in the --logs and I see: [snip] Oct 21 01:32:13 localsvr postgres[2598]: [1] FATAL 2: control file context is broken Oct 21 01:32:14 localsvr postgresql: Starting postgresql service: failed [/snip] --There doesn't seem to be much info in groups.google.com --about this message. What does this mean and how can --I fix the DB (so I can dump it into a new version)? --Thanks! [snip from past email] This past weekend, the RAID array took a hit and crashed the database. I get a few errors like: [snip] DEBUG: invalid secondary checkpoint record FATAL 2: unable to locate a valid checkpoint record DEBUG: proc_exit(2) DEBUG: shmem_exit(2) DEBUG: exit(2) DEBUG: reaping dead processes DEBUG: startup process (pid 23543) exited with exit code 2 DEBUG: aborting startup due to startup process failure [/snip] [/snip from past email] -X
Re: [GENERAL] Pgsql 7.3.3 on redhat 7.2
On Mon, Oct 20, 2003 at 01:03:23PM -0400, Daniel E. Fisher wrote: > After reading all my server logs: > > No error messages or nothing. Weird.. Including Apache's /var/log/httpd/access_log and /var/log/httpd/error_log during restart? (or wherever you may have moved them) > May have something to do with reading wrong libpq Alvaro? Maybe ... check out what ldd path-to-pgsql.so says (PHP's support library) -- Alvaro Herrera () "Nadie esta tan esclavizado como el que se cree libre no siendolo" (Goethe) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Recomended FS
Peter Childs wrote: On Mon, 20 Oct 2003, Shridhar Daithankar wrote: A fast HD with a good RAID controller. Subject to budget, SCSI are beter buy than IDE. So does hardware SCSI RAID. I hate asking this again. But WHY? OK.. There are only few SCSI disks that I have handled so take it with grain of salt. 1. SCSI bus can share bandwidth much better than IDE disks. Put two IDE disks on same channel and two SCSI disks. See which combo performs better. 2. SCSI disks are idividually tested and IDEs are sampled. Makes a big difference in reliability. I know for some people IDE disks do not crash at all but majority think SCSI are more reliable than IDEs. 3. SCSI disks have Tag commands and things alike, that makes them better at handling load. Technically, if you don't know the load, SCSI would make a better choice. If you know your load very well and it is predictive, IDE might be a choice. I would personally prefer IDE disk array with hardware RAID controller because I can put it in my home machine, unlike SCSI. But every developer I have asked around here, says that IDE performance starts dropping once you hit real world load. Shridhar ---(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] SCSI vs. IDE performance test
Dann Corbit wrote: Unwrap this link (if your newsreader folds it) and click on it for hard drive performance: http://www.storagereview.com/php/benchmark/compare_rtg_2001.php?typeID=1 0&testbedID=3&osID=4&raidconfigID=1&numDrives=1&devID_0=232&devID_1=237& devID_2=213&devID_3=221&devID_4=216&devID_5=249&devID_6=250&devCnt=7 The important part for database is "Server Suite" ---(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 Fairly old data, but it shows AMAZING differences in head seek time. I didn't know head seeks were below 8ms for anything, even today. Also, from what I've read, the SATA drives of those days were non existent? The earliest SATA drives I've read about were just SATA interfaces on OLDER IDE hardware - the manufacutrers had not really signed up on the concept enough to put their good hardware underneath the interface. -- "You are behaving like a man", is an insult from some women, a compliment from an good woman. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] VACUUM degrades performance significantly. Database
Just for another data point, the default on my Debian 2.4.23-pre4 box is: /dev/hdg elevator ID3 read_latency: 128 write_latency: 512 max_bomb_segments: 0 -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] extend INSERT by 'INSERT INTO table FETCH ... FROM cursor' syntax
Please CC me, I am not subscribed. An imaginary SQL statement INSERT INTO table FETCH ... FROM cursor; looks almost the same as currently available INSERT INTO table SELECT ...; I tried it because I needed to insert a row in a table after I DELETEd a set of rows, something like this: BEGIN; DECLARE total CURSOR FOR SELECT SUBSTR(datetime,1,7)||'-01 00:00:00' as month, client, SUM(money) FROM stat WHERE SUBSTR(datetime,1,7)='2003-10' GROUP BY month,client; DELETE FROM stat WHERE SUBSTR(datetime,1,7)='2003-10'; INSERT INTO stat FETCH ALL FROM total; COMMIT; but it does not work, chokes on FETCH ALL. I want to sum up all the money by month, delete all the rows (possibly thousands of them) and insert one row per client with monthly totals. Obviously I cannot swap order of INSERT and DELETE here. I hesitate to post this to [EMAIL PROTECTED], do I have to? ;) -- Alexander Vlasenko ---(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] Setting up DSPACE for Postgres access
> Given everything else you've tried, I think you might be on the right track > here and somehow your environment variables are getting clobbered. How or why > I couldn't say (I'd suspect Tomcat, but you say you can't even run the > create-administator app). Tomcat only uses the minor few and runs like a charm. It doesnt seem to be only the create-administrator app. As I mentioned earlier, this occurs with any app trying to connect to pgsql via jdbc. I used the psql.java file to compile and test that comes bundled in the postgres source. The error I received was as follows: Exception in thread "main" java.lang.NoClassDefFoundError: loaded class psql was in fact named example.psql at 0x40268e17: java.lang.Throwable.Throwable(java.lang.String) (/usr/lib/./libgcj.so.3) at 0x4025bc8e: java.lang.Error.Error(java.lang.String) (/usr/lib/./libgcj.so.3) at 0x4025d6b6: java.lang.LinkageError.LinkageError(java.lang.String) (/usr/lib/./libgcj.so.3) at 0x4025eb36: java.lang.NoClassDefFoundError.NoClassDefFoundError(java.lang.String) (/usr/lib/./libgcj.so.3) at 0x4022d555: ?? (??:0) at 0x4022c96e: _Jv_ClassReader.handleClassBegin(int, int, int) (/usr/lib/./libgcj.so.3) at 0x4022ab61: _Jv_ClassReader.parse() (/usr/lib/./libgcj.so.3) at 0x4022a97f: _Jv_DefineClass(java.lang.Class, byte[], int, int) (/usr/lib/./libgcj.so.3) at 0x40247e6f: java.lang.ClassLoader.defineClass0(java.lang.String, byte[], int, int, java.security.ProtectionDomain) (/usr/lib/./libgcj.so.3) at 0x4025aaa3: java.lang.ClassLoader.defineClass(java.lang.String, byte[], int, int, java.security.ProtectionDomain) (/usr/lib/./libgcj.so.3) at 0x4025aa13: java.lang.ClassLoader.defineClass(java.lang.String, byte[], int, int) (/usr/lib/./libgcj.so.3) at 0x402f1f2e: java.net.URLClassLoader.findClass(java.lang.String) (/usr/lib/./libgcj.so.3) at 0x40248197: gnu.gcj.runtime.VMClassLoader.findClass(java.lang.String) (/usr/lib/./libgcj.so.3) at 0x4025a904: java.lang.ClassLoader.loadClass(java.lang.String, boolean) (/usr/lib/./libgcj.so.3) at 0x402488d9: _Jv_FindClass(_Jv_Utf8Const, java.lang.ClassLoader) (/usr/lib/./libgcj.so.3) at 0x40244cdd: java.lang.Class.forName(java.lang.String, boolean, java.lang.ClassLoader) (/usr/lib/./libgcj.so.3) at 0x40244d9f: java.lang.Class.forName(java.lang.String) (/usr/lib/./libgcj.so.3) at 0x402ad01d: gnu.gcj.runtime.FirstThread.run() (/usr/lib/./libgcj.so.3) at 0x4024fc4c: _Jv_ThreadRun(java.lang.Thread) (/usr/lib/./libgcj.so.3) at 0x4021c8ac: _Jv_RunMain(java.lang.Class, byte const, int, byte const, boolean) (/usr/lib/./libgcj.so.3) at 0x08048910: ?? (??:0) at 0x420156a4: __libc_start_main (/lib/tls/libc.so.6) at 0x080486c1: ?? (??:0) > The only other thing I can think of is that for some reason it's looking for > the wrong class. If dspace comes with the source, it might be worth checking > whether there isn't a mistake in the Class.forname() call. Put it this way, the build works for the rest and after some testing found that outside of create-admin I cant connect to it. > I must admit, I don't know much java - can you use "strace" to track system > calls, or is there a similar utility that would let you see what files it is > searching for? strace revealed nothing really that could explain this behaviour. Thanks a lot for all the assistance btw, I really appreciate it. Hope you have some more ideas to help out. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] ./configure error - readline library not found
Thanks much, that was my problem. For the record, the debian package name (which took me a while to determine) is: libreadline4-dev Thanks again. MT Doug McNaught wrote: Michael Teter <[EMAIL PROTECTED]> writes: Howdy. I looked around for an answer to this, but I was unable to find one that seemed to match my situation. (Incidentally, now I cannot get archives.postgresql.org to respond to my search query...) I'm trying to build 7.3.4 on Libranet2.8.1 (debian). I've built PostgreSQL many times in the past on stock RedHat distros and had no problem. You probably need to install the -devel package for readline. I have libreadline4.3-4 installed, and I think the relevant library file is /lib/libreadline.so.4 This is the runtime part of the library, but you need the headers to compile, which are in the -devel package. -Doug ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Nullable 'Foreign Key-like' Constraint
On Thu, 23 Oct 2003 16:02:03 GMT, Ron <[EMAIL PROTECTED]> wrote: > ALTER TABLE project ADD CONSTRAINT company_is_ta companyID > REFERENCES company(companyID); > (plus variations on the above, resulting in errors, all similar to:) >ERROR: parser: parse error at or near "companyID" at character 53 ALTER TABLE project ADD CONSTRAINT company_is_ta FOREIGN KEY (companyID) REFERENCES company(companyID); ^ ^ Servus Manfred ---(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] About TSearch2 Performance
Teodor Sigaev wrote:
First one will be a bit faster
Diogo Biazus wrote:
Hi,
Is there any performance diference between the following SQL commands:
SELECT * FROM documents WHERE content_ix @@
to_tsquery('word1&word2|word3');
SELECT * FROM documents WHERE content_ix @@ to_tsquery('word1') AND
content_ix @@ to_tsquery('word2') OR content_ix @@ to_tsquery('word3');
I'm having to do this on some complex querys to put LIKEs between
some ts_querys.
Does anyone has such experience?
Thanks in advance,
What do those @@ do for the statement?
--
"You are behaving like a man",
is an insult from some women,
a compliment from an good woman.
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Recomended FS
On Fri, 24 Oct 2003, Michael Teter wrote: > Here are some recent benchmarks on different Linux filesystems. As with > any benchmarks, take what you will from the numbers. > > Note the Summary section, and then the detailed benchmark numbers (if > you have a stomach for huge tables of pure numbers :) > > http://fsbench.netnation.com/ Right, but NONE of the benchmarks I've seen have been with IDE drives with their cache disabled, which is the only way to make them reliable under postgresql should something bad happen. but thanks for the benchmarks, I'll look them over. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] where to find minor release info
David Link wrote: > Hi All, > > Where can I find minor release information between versions: > > 7.3.1 and 7.3.4. See /HISTORY or the release docs --- same place you learn about major releases. > And when is 7.4 expected, and should I hold off before upgrading to > 7.3.4? > > I looked around but was unable to find this information. We are in 7.4beta5 now --- you can try that for testing. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Recomended FS
On Friday 24 October 2003 16:23, scott.marlowe wrote: > Right, but NONE of the benchmarks I've seen have been with IDE drives with > their cache disabled, which is the only way to make them reliable under > postgresql should something bad happen. but thanks for the benchmarks, > I'll look them over. I don't recall seeing anyone explain how to disable caching on a drive in this thread. Did I miss that? 'Would be useful. I'm running a 3Ware mirror of 2 IDE drives. Scott ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [HACKERS] Semaphores
Nailah Ogeer wrote: > Hi all, > Just wanted to know how postgres handles semaphores. Was hoping that i can > can use the locks defined in lwlock.h and lwlock.c . If i create a new > lock and then use LockAcquire and > LockRelease when I want a process to start and stop will this work? Uh, well, we usually try to get a lock via a test-and-set assembly language call into shared memory, and if we fail, we then get a semaphore and wait for someone to wake us up. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Semaphores
Hi all, Just wanted to know how postgres handles semaphores. Was hoping that i can can use the locks defined in lwlock.h and lwlock.c . If i create a new lock and then use LockAcquire and LockRelease when I want a process to start and stop will this work? Nailah ---(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
