[SQL] Selecting empty columns
How can I retrieve empty columns from a table? The affected column is int4, some row do have values, others are empty - how can those empty ones be retrieved and updated to 0 Hans
Re: [SQL] Persistent Connects (pg_pconnect)
Thanks guys and gals, for your quick responses to my query about persistent
connections (you know who you are). It was much appreciated. I am posting
the results the results of our investigations in case you are interested
and also if it might help someone else...
Our investigations of the problem revealed that when using pg_pconnect on
the one Apache web server which was serving PHP pages which had many static
elements like jpgs, our postgres processes where being tied up by child web
processes which just serving lots of static elements. We had a max of 32
postgres connections while we could have up to 300 child web processes and
so problems of the CMS locking us out occurs when when we had more web
processes than postgres resources ie we had a low utilization of postgres
resources. The solution lies in having more than one web server it seems -
one to serve database type PHP pages and the other to server static
elements such as jpgs etc.
As we didn't have time to rebuild our web architecture we have used
pg_connect instead of pg_pconnect as a short term solution. It didn't
appear that much slower than when we used pg_connect.
However we had another problem, the solution with using pg_connect instead
of pg_pconnect worked on DEV but not on PROD. We think it is to do with
different PHP versions. DEV was using PHP4.0.1p2 and PROD was using
PHP4.0.3p1. We think maybe there is a variable scoping problem PHP4.0.3p1.
Perhaps PHP doesn't really work so well with object code and so we
shouldn't be using object code.
I have used the following code to do the database connect in my db.inc. To
make the code work in PROD I have had to make $connection a global variable
(by uncommenting the commented lines) as although it would connect fine and
return a valid connection index, the next time I went to fetch the query,
postgres would return an 'invalid postgres resource link' error. However in
DEV (PHP4.0.1p2) it was working fine - creating a new postgres resource
when a new query object is created.
If you can see a bug in my code and it is not at all a PHP version problem
OR if you can confirm it is a PHP version problem please let me know. I
would like to know if I am just doing it all wrong.
//global $connection;
function db_connect() {
global $DBNAME, $DBUSER, $DBPASS, $DBPORT;
//global $connection;
$connection = pg_connect("dbname=$DBNAME user=$DBUSER password=$DBPASS
port=$DBPORT ") or die("Unable to connect to server.");
return $connection;
}
//db_connect();
Class Query {
var $numrows;
var $query;
var $errormsg;
var $conn;
// constructor function
function Query() {
$this->conn = db_connect();
}
function runquery( $qstr ){
$this->query = pg_exec($this->conn, $qstr);
if ($this->query == 0) {
$this->errormsg = pg_errormessage($this->conn);
$this->numrows = 0;
}
else {
$this->numrows = pg_NumRows($this->query);
}
}
function fetchrow($ind ){
return pg_fetch_row($this->query,$ind);
}
function fetcharray($ind ){
return pg_fetch_array($this->query,$ind);
}
}
Cheers,
Colleen.
Colleen Williams
[EMAIL PROTECTED]
0207 484 8825
Digital Arts | British Columbia House | 1 Regent Street | London | SW1Y 4NR
http://www.digital-arts.co.uk
Antw: [SQL] Selecting empty columns
>>> Hans-Jürgen Schönig <[EMAIL PROTECTED]> 28.11.2000 12.39 Uhr >>> > How can I retrieve empty columns from a table? > The affected column is int4, some row do have values, others are empty - > how can those empty ones be retrieved and updated to 0 > >Hans try: update tablename set columnname=0 where columnname is null; Gerhard
Re(2): [SQL] 7.0.3 BUG
Thanks to you Tom and Thomas. Now I know. - sherwin [EMAIL PROTECTED] writes: >Ah ha (or rather, ha ha ha)! I'd suggest using the RPMs posted on the >postgresql.org ftp site, which include a sample .rpmrc file which fixes >disasterous bugs in Mandrake's default compiler settings for building >RPMs. Specifically, Mandrake sets the -ffast-math flag, which the gcc >folks warn is not compatible with -On optimizations. When I build RPMs I >kill the fast-math option, and the rounding troubles go away. > >The rounding trouble does not show up on other platforms or Linux >distros because no one else ignores the gcc recommendations to this >extent :( > > - Thomas
Re: [SQL] Damaged table "pg_access"
Jens Hartwig <[EMAIL PROTECTED]> writes: > when I try to access the system-table "pg_class", I get the following > error: > zeda=# select * from pg_class; > NOTICE: get_groname: group 1 not found > pqReadData() -- backend closed the channel unexpectedly. Evidently you had created a group with sysid 1, granted some permissions on some table to that group, and then deleted the group. Re-create the group (CREATE GROUP foo WITH SYSID 1). I thought the crash in this situation had been fixed long since, but apparently not :-( ... it still fails in current sources. I'll make sure it's fixed for 7.1. regards, tom lane
Re: [SQL] Selecting empty columns
On Tue, Nov 28, 2000 at 12:39:50PM +0100, Hans-Jürgen Schönig wrote: > How can I retrieve empty columns from a table? > The affected column is int4, some row do have values, others are empty - > how can those empty ones be retrieved and updated to 0 UPDATE foo_table SET bar_column = 0 WHERE bar_column IS NULL; Ross -- Open source code is like a natural resource, it's the result of providing food and sunshine to programmers, and then staying out of their way. [...] [It] is not going away because it has utility for both the developers and users independent of economic motivations. Jim Flynn, Sunnyvale, Calif.
Re: [SQL] Damaged table "pg_access"
Hello, yes, you were completely right, thank you very much, you saved me from spending much time on this problem! The recreation of the groups 1, 2, 3 and 4 resolved the problem :-) My next question is: how can I (as a workaround till version 7.1) permanently remove a group without facing this problem? Can I rudely remove the granted permissions by any command (e.g. deleting it manually from the system catalogue)? Best regards, Jens Tom Lane schrieb: > > Jens Hartwig <[EMAIL PROTECTED]> writes: > > when I try to access the system-table "pg_class", I get the following > > error: > > > zeda=# select * from pg_class; > > NOTICE: get_groname: group 1 not found > > pqReadData() -- backend closed the channel unexpectedly. > > Evidently you had created a group with sysid 1, granted some permissions > on some table to that group, and then deleted the group. Re-create the > group (CREATE GROUP foo WITH SYSID 1). > > I thought the crash in this situation had been fixed long since, but > apparently not :-( ... it still fails in current sources. I'll make > sure it's fixed for 7.1. > > regards, tom lane = Jens Hartwig - debis Systemhaus GEI mbH 10875 Berlin Tel. : +49 (0)30 2554-3282 Fax : +49 (0)30 2554-3187 Mobil: +49 (0)170 167-2648 E-Mail : [EMAIL PROTECTED] =
Re: [SQL] Damaged table "pg_access"
Jens Hartwig <[EMAIL PROTECTED]> writes: > My next question is: how can I (as a > workaround till version 7.1) permanently remove a group without facing > this problem? Can I rudely remove the granted permissions by any command > (e.g. deleting it manually from the system catalogue)? There isn't any code to scan through the table ACLs and remove references to a particular group or user (if there were, DROP GROUP would be invoking it already). Feel free to submit some... regards, tom lane
[SQL] Re: a script that queries db periodically
Thank you for all your response. Not only do I benefit from your response, but hopefully others benefit as well. Thanks again. - Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Services adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada version:2.1 email;internet:[EMAIL PROTECTED] title:Programmer x-mozilla-cpt:;0 fn:Bernie Huang end:vcard
