RE: [GENERAL] Problems with new data location
Tom Lane wrote: > But ... um ... did you restart the postmaster with the new path argument? > Or only change the script? Well, now I can't remember the exact sequence I used originally - I probably tried to restart only after all the changes had taken place. I do remember getting the error "can't find /usr/local/pgsql/data/postmaster.pid - is postmaster running?", and dummy me, I assumed it wasn't, without checking ps. Anyway, restarting doesn't seem to work if I actually move the directory rather than copy it. But after I got your message, I checked ps and discovered that indeed, postmaster was still running. So I put the directory back in its old place, successfully ran "pg_ctl -D /usr/local/pgsql/data stop", then moved the directory, and then successfully ran "pg_ctl -D /var/pgdata stop". > BTW, it should work fine to move a data-directory tree from one place to > another (with cp -p -r or tar/untar according to preference). But you > have to shut down the old postmaster *first*. Yup, the key is that both those methods copy (rather than move) the directory. If you copy, you can use restart (I assume - I didn't try it). If you want to simply move the directory, you have to (1) stop, (2) move, (3) start. Thanks for pointing me in the right direction, and also giving me the assurance that a data directory can indeed be moved without redoing initdb. Karen ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Preventing variables from "rolling over"
I assume that if I keep on adding to an INT4, at some point it will roll over and become negative, and then if I add some more, it could become positive. Is there a way to prevent this? Ie, I would like for it to end with an error instead of wrapping around. Or is there a way to check for this condition before I do the addition? Is there a way to put a constraint on a variable, such that it's always positive? Thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] PQclear question...
Thanks. Tim - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Tim Barnard" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, May 29, 2001 3:30 PM Subject: Re: [GENERAL] PQclear question... > "Tim Barnard" <[EMAIL PROTECTED]> writes: > > Is it necessary to call PQclear if a query that > > would normally return tuples fails? > > If you got a non-NULL result from PQexec, you'd better PQclear it, > or suffer the consequences (memory leak -> eventual application crash). > > regards, tom lane > ---(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] PQclear question...
"Tim Barnard" <[EMAIL PROTECTED]> writes: > Is it necessary to call PQclear if a query that > would normally return tuples fails? If you got a non-NULL result from PQexec, you'd better PQclear it, or suffer the consequences (memory leak -> eventual application crash). regards, tom lane ---(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] Timezones on HPUX
"Gilbert Wong" <[EMAIL PROTECTED]> writes: > [ doesn't like the default timezone ] Try setting TZ in the postmaster's environment. Note this is not the same as your client program's environment. Also check to see if AOLServer is sending a timezone-set command when it connects; if plain psql shows the desired timezone and AOLServer is getting different results, then it's certainly the latter. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] PQclear question, repeated
Hello all, Is it necessary to call PQclear if a query that would normally return tuples fails? What if the query returns 0 tuples? In other words, is it only necessary to call it if you get back one or more tuples? Thanks in advance! Tim
Re: [GENERAL] Kylix, dbexpress & PostgreSql
Hi Denis and Ben, > > I searched all the pgsql mailing list (and the > > internet) to find if there > > is a dbexpress driver or some way to get pgsql into Borland > > Kylix. The only > > solution i've found is the beta driver from EasySoft > > "dbExpress Gateway to > > ODBC". > > Try using ZeosDBO at http://www.zeoslib.org/ this has just been ported to > Kylix. Not sure how stable it is but the windows version is great :). If you do not mind missing the DatabaseControls (TDBGrid, etc) you may just get the postgresql unit from Free Pascal (www.freepascal.org). Also take a look at their FCL (Free Component Library), the development version has a clone of a TDataset and a PostgreSQL-specific subclass. Maybe you can compile it with Kylix and use with CLX data controls. []s, Fernando Lozano ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] PQclear question...
Is it necessary to call PQclear if a query that would normally return tuples fails? What if the query returns 0 tuples? In other words, is it only necessary to call it if you get back one or more tuples? Tim
Re: [GENERAL] Modifying a table that other tables refer to forforeigh key
On Tue, 29 May 2001, Mihai Gheorghiu wrote: > I have a database with multiple referential integrity links. > I want to make important changes to one of the tables, which is referenced > by other tables for foreign keys (like deleting some columns, or changing > data type). > What would be the easiest way to do it (without re-creating all the other > tables that are refering to it)? Drop the table, make the changes and use alter table add constraint to re-add the constraints to the referencing tables (the original constraints should go away when the other table is dropped) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Modifying a table that other tables refer to for foreigh key
I have a database with multiple referential integrity links. I want to make important changes to one of the tables, which is referenced by other tables for foreign keys (like deleting some columns, or changing data type). What would be the easiest way to do it (without re-creating all the other tables that are refering to it)? Thanks in advance, Mihai ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Timezones on HPUX
I am running PostgreSQL 7.0.3 on HPUX 11.0. I get the expected behavior if my PGTZ environment variable is not set: select current_timestamp; timestamp 2001-05-29 12:56:16-04 (1 row) The I ran the date command in the shell and received the following output: Tue May 29 09:57:09 PDT 2001 I have my TZ enviornment set to the correct value PST8PDT. When I set the PGTZ variable to PST8PDT, I also get the correct time output: select current_timestamp; timestamp 2001-05-29 09:58:26-07 (1 row) So this works fine if I'm using psql. However, I am using PostgreSQL in conjunction with AOLServer and I get the incorrect date/time when I run the current_timestamp query from a .tcl page in AOLServer. Here is a snippet from my init.d script: su - $PGUSER -c "$PGHOME/bin/pg_ctl -o '$PGOPTS' -D $PGHOME/data start" Is there any way I can pass the PGTZ variable when I execute this script? Thanks. Gilbert _ Get your FREE download of MSN Explorer at http://explorer.msn.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Help with C++
I don't know if it's really needed. The examples at the end of the libpq doc worked just great for me. I found them to be very straight forward. Regarding libpq++, I understand that whole api is being reworked. At least I got that impression from past thread postings. For more info on this see the 11 message thread in the archives, starting with: http://fts.postgresql.org/db/mw/msg.html?mid=114659 Tim - Original Message - From: "Justin Clift" <[EMAIL PROTECTED]> To: "Tim Barnard" <[EMAIL PROTECTED]> Sent: Tuesday, May 29, 2001 3:42 AM Subject: Re: [GENERAL] Help with C++ > Hi Tim, > > Are you any good with programming C and PostgreSQL or C++ and > PostgreSQL? > > I'm thinking an introductory article for people might be useful on the > techdocs site. > > What do you think? > > :-) > > Regards and best wishes, > > Justin Clift > > Tim Barnard wrote: > > > > Look in the programmer docs > > > > For C: www.postgresql.org/idocs/index.php?libpq.html > > For C++: www.postgresql.org/idocs/index.php?libpqplusplus.html > > > > Tim > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Get status of connection (C)
Page 4 of the programmers reference states: "At any time during connection, the status of the connection may be checked, by calling PQstatus." I use it and so far its worked great, by which I mean it has subsequently reported bad connections after having reported CONNECTION_OK, which has happened when the backend has died. Tim - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, May 29, 2001 2:46 AM Subject: [GENERAL] Get status of connection (C) > > I'm writing a pool manager to manage x connections to a pgsql postmaster. > > The getConnection method has to check if the connection is still valid, > and try to reconnect if it ain't. > > Do you guys have a good way of doing this? > > Can I use PQstatus? > > Should I make a little runCommand on it to try it out? Then what command? > > If it's bad, should I run a PQfinish on it before reconnecting or not? > > Thank you plenty! > > --- > Daniel Akerud > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Re: [SQL] Difficult SQL Statement
Thanks for the helpful tip! Tim - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Tim Barnard" <[EMAIL PROTECTED]> Cc: "Jim" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, May 29, 2001 7:50 AM Subject: Re: [GENERAL] Re: [SQL] Difficult SQL Statement > I wrote: > > select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4 > > from > > (select ASMT_CODE, count(*) as TOTAL from RESULTS > > group by ASMT_CODE) as tmp1 > > natural join > > (select ASMT_CODE, count(*) as PASSED from RESULTS > > where STATUS='PASSED' group by ASMT_CODE) as tmp2 > > BTW, although this is a fine example of how to avoid using temp tables, > it's not such a great solution to the original problem. What happens > if there are no 'PASSED' entries at all for a given ASMT_CODE? You > probably won't want that ASMT_CODE to disappear from your report --- > but it will disappear in the join. We could fix this with a left join > and some hackery to deal with the resulting NULL values for PASSED, > but now things are getting ugly. Time to try something different: > > select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4 as PCT > from > (select ASMT_CODE, > count(*) as TOTAL, > sum(CASE WHEN STATUS='PASSED' THEN 1 ELSE 0 END) as PASSED > from RESULTS group by ASMT_CODE) as tmp1 > > Here we use the sub-select only as a way of labeling the count() and > sum() results so that we don't have to write and compute them twice. > You could write it as a simple one-level SELECT if you didn't mind > that redundancy. > > regards, tom lane > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Fresh Installation of 7.1.1 RPMs = no binding to a port
Lamar Owen <[EMAIL PROTECTED]> writes: > I have considered making the default back to TCP/IP listening I think that's a bad idea, for the "be secure by default" reason. -- Trond Eivind Glomsrød Red Hat, Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Re: [SQL] Difficult SQL Statement
I wrote: > select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4 > from > (select ASMT_CODE, count(*) as TOTAL from RESULTS > group by ASMT_CODE) as tmp1 > natural join > (select ASMT_CODE, count(*) as PASSED from RESULTS > where STATUS='PASSED' group by ASMT_CODE) as tmp2 BTW, although this is a fine example of how to avoid using temp tables, it's not such a great solution to the original problem. What happens if there are no 'PASSED' entries at all for a given ASMT_CODE? You probably won't want that ASMT_CODE to disappear from your report --- but it will disappear in the join. We could fix this with a left join and some hackery to deal with the resulting NULL values for PASSED, but now things are getting ugly. Time to try something different: select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4 as PCT from (select ASMT_CODE, count(*) as TOTAL, sum(CASE WHEN STATUS='PASSED' THEN 1 ELSE 0 END) as PASSED from RESULTS group by ASMT_CODE) as tmp1 Here we use the sub-select only as a way of labeling the count() and sum() results so that we don't have to write and compute them twice. You could write it as a simple one-level SELECT if you didn't mind that redundancy. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 7.1.2
sorry, my fault, just fixed all the links in teh ftp ...
On Tue, 29 May 2001, Alexey Borzov wrote:
> Greetings, Martín!
>
> At 29.05.2001, 17:28, you wrote:
>
> MM> Where can I get 7.1.2? The primary ftp server refuses me to enter, and the
> MM> mirror sites are not updated.
> In fact they're updated. There is no link from main directory to
> 7.1.2, although. You should go to "source" and navigate your way
> further. :]
>
> P.S. I've spent two days figuring it out. :]
>
>
> --
> Yours, Alexey V. Borzov, Webmaster of RDW.ru
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
---(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] Problems with new data location
"Karen Ellrick" <[EMAIL PROTECTED]> writes: > ... Then I tried creating /var/pgdata fresh and doing initdb -D > /var/pgdata, and that was okay until I tried to create the database again, > when it said a database of that name already existed. Yes, I have changed > my startup script to reflect the new path. But ... um ... did you restart the postmaster with the new path argument? Or only change the script? BTW, it should work fine to move a data-directory tree from one place to another (with cp -p -r or tar/untar according to preference). But you have to shut down the old postmaster *first*. regards, tom lane ---(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: UPDATE keyword
True, true... but the next time I need the code to fire I would need ot remember that
I had set it to 1 and set it to something else...
Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: [EMAIL PROTECTED]
>>> Rene Pijlman <[EMAIL PROTECTED]> 05/26/01 01:56AM >>>
[EMAIL PROTECTED] ("Ian Harding") schreef:
>UPDATE mytable SET myfield = myfield WHERE...
>to fire an update trigger, and executing only the code in that
>trigger surrounded by UPDATE(myfield).
So you update a field by not changing its value, to execute a piece of
code that has nothing to do with the field and the value :-)
>Is there an easy way to duplicate this, or should I work around it?
Isn't it easier (and more poratble) to add a dummy field to the table?
Then you can do:
UPDATE mytable
SET dummy = 1 - dummy
WHERE ...
to execute the piece of code that responds to an update of 'dummy'.
Regards,
René Pijlman
http://www.applinet.nl
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---(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] Re: [SQL] Difficult SQL Statement
"Tim Barnard" <[EMAIL PROTECTED]> writes: > To my thinking there's got to be a better way to do this whithout so many > temporary tables. In 7.1 you can frequently replace temp tables with subselect-in-FROM. Cutting-and-pasting freely from your solution: select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4 from (select ASMT_CODE, count(*) as TOTAL from RESULTS group by ASMT_CODE) as tmp1 natural join (select ASMT_CODE, count(*) as PASSED from RESULTS where STATUS='PASSED' group by ASMT_CODE) as tmp2 (haven't tried this, but it looks right...) This won't necessarily be a whole lot faster than the solution with temp tables, but it's nice not to have to worry about dropping the temp tables afterwards. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] 7.1.2
Dixit Martín Marqués <[EMAIL PROTECTED]> (le Tue, 29 May 2001 09:57:17 +0300) : » Where can I get 7.1.2? The primary ftp server refuses me to enter, and the » mirror sites are not updated. I found that on some mirrors the symlink v7.1.2 -> source/v7.1.2 is missing so that you may believe that 7.1.2 whereas it is, for sure, trust me. ;-) Thierry ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [GENERAL] Kylix, dbexpress & PostgreSql
Title: RE: [GENERAL] Kylix, dbexpress & PostgreSql Try using ZeosDBO at http://www.zeoslib.org/ this has just been ported to Kylix. Not sure how stable it is but the windows version is great :). Regards Ben > -Original Message- > From: Denis Gasparin [mailto:[EMAIL PROTECTED]] > Sent: 28 May 2001 16:39 > To: [EMAIL PROTECTED] > Subject: [GENERAL] Kylix, dbexpress & PostgreSql > > > Hi to all! > I searched all the pgsql mailing list (and the > internet) to find if there > is a dbexpress driver or some way to get pgsql into Borland > Kylix. The only > solution i've found is the beta driver from EasySoft > "dbExpress Gateway to > ODBC". > Is there another way or is there any plan to support > Kylix & dbExpress? > > Please, let me know: i'm going to develop a program in kylix and i'm > considering what db platform to use. If isn't there another > way i've to > choose Interbase instead of pgsql (with very very much regret...). > > Thank to all, > Denis Gasparin > --- > [EMAIL PROTECTED] > > > ---(end of > broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > * This email and any attachments 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 sender and do not store, copy or disclose the content to any other person. It is the responsibility of the recipient to ensure that opening this message and/or any of its attachments will not adversely affect its systems. No responsibility is accepted by the Company. *
[GENERAL] Problems with new data location
Right after a new installation (7.1.1) and building my first table with the data directory being the normal /usr/local/pgsql/data, I decided I wanted to put my data directory on a different partition, for space reasons (room for database growth) - I chose /var/pgdata. At first I tried just copying the whole data directory, but I got some sort of error (I don't remember it now). Then I tried creating /var/pgdata fresh and doing initdb -D /var/pgdata, and that was okay until I tried to create the database again, when it said a database of that name already existed. Yes, I have changed my startup script to reflect the new path. The next thing I tried was to change the name of /usr/local/pgsql/data to /usr/local/pgsql/data.old (to hide it), erase the stuff in /var/pgdata, and then go through the initdb, pg_ctl, createdb process again. Now createdb complains: pgsql: Missing or erroneous pg_hba.conf file, see postmaster log for details createdb: database creation failed I have a perfectly good /var/pgdata/pg_hba.conf file. Is it still looking for stuff in /usr/local/pgsql/data? How can I determine where it is looking? I don't where the "postmaster log" is - the log file I specify with pg_ctl -l (/var/pgdata/serverlog) is not showing anything about this (just one older error about a lock file). What is the right way to move a data directory or to start over with a new one? (It would be gravy to keep my already created database, but starting over is no big deal - there is no data in it yet.) Thanks for your help. 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Get status of connection (C)
I'm writing a pool manager to manage x connections to a pgsql postmaster. The getConnection method has to check if the connection is still valid, and try to reconnect if it ain't. Do you guys have a good way of doing this? Can I use PQstatus? Should I make a little runCommand on it to try it out? Then what command? If it's bad, should I run a PQfinish on it before reconnecting or not? Thank you plenty! --- Daniel Akerud ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Kylix, dbexpress & PostgreSql
Zeos (www.zeoslib.org) has released the last version of their library and now it supports kylix! Obviously, it isn't dbexpress but it uses directly the pgsql.so library. Give it a try! It works... Denis Gasparin At 23.16 28/05/01, [EMAIL PROTECTED] wrote: >Hello, > >There is one under development. Alternatively you could try and modify the >GNU ObjectPascal driver for use in Kylix. > >J > >On Mon, 28 May 2001, Denis Gasparin wrote: > > > Hi to all! > > I searched all the pgsql mailing list (and the internet) to find > if there > > is a dbexpress driver or some way to get pgsql into Borland Kylix. The only > > solution i've found is the beta driver from EasySoft "dbExpress Gateway to > > ODBC". > > Is there another way or is there any plan to support Kylix & > dbExpress? > > > > Please, let me know: i'm going to develop a program in kylix and i'm > > considering what db platform to use. If isn't there another way i've to > > choose Interbase instead of pgsql (with very very much regret...). > > > > Thank to all, > > Denis Gasparin > > --- > > [EMAIL PROTECTED] > > > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
