Re: [SQL] PHP postgres connections
Hi,
On 4/29/05, Mauro Bertoli [EMAIL PROTECTED] wrote:
I need to connect to 2 differents Postgres 8.0.0
databases located in the same machine using the same
PHP script with an db wrapper object instance
(pg_Connect)... simply a PHP page with contemporarily
2 database connections...
Firstly some extra information from php.net:
{{{
[http://tr.php.net/pg_connect]
resource pg_connect ( string connection_string [, int connect_type] )
...
If a second call is made to pg_connect() with the same connection_string,
no new connection will be established unless you pass
SQL_CONNECT_FORCE_NEW as connect_type, but instead, the connection
resource of the already opened connection will be returned. You can have
multiple connections to the same database if you use different connection
strings.
}}}
Here's a simple db wrapper class for 2 different db connections:
class dbw
{
/* Connection parameter variables. */
var connParam1;
var connParam2;
function dbw()
{
/* Assigning values to conn. params. */
$this-connParam1 = ...;
$this-connParam2 = ...;
}
function connect($connParam)
{
/* Pay attention to SQL_CONNECT_FORCE_NEW parameter. */
return pg_connect($connParam, SQL_CONNECT_FORCE_NEW);
}
/* ... */
}
/* Creating DB Wrapper */
$dbw = new dbw();
/*
* If we're not happy with the current connParam1 value:
* $dbw-connParam1 = ...;
*/
$dbConn1 = $dbw-connect($dbw-connParam1);
$dbConn2 = $dbw-connect($dbw-connParam2);
Can I use however persistent connections ?
Yep. Just replace pg_connect line in the code with pg_pconnect. But I
(as PHP team) don't recommend using persistent connections. Please
read Persistent Database Connections [1] before deciding to use.
[1] http://php.net/manual/en/features.persistent-connections.php
Regards.
P.S. Next time, please try to use pgsql-php listen for PHP related questions.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Build issues: -static builds resulting initdb problems
Metin Ozisik [EMAIL PROTECTED] writes: The purpose of using static linking is to reduce dependencies to shared-libraries (dependencies to different types and versions of Linux), so an instance of postgreSQL, say built on Suse 9.0, would still work on Mandrake 10.1. Yes it gets a bit bulky and have a number of disadvantages over dynamic linking (on the plus side it would be a bit faster), however the main motivater is binary portability. Well, both the PL languages and the character set conversion support are *only* buildable as shared libraries right now. If you want to statically link those into the main backend build, you can probably do it, but it will take some nontrivial hacking. In the meantime it would appear that your linker ignores the -E (export all symbols) switch when -static is specified. I suppose it thinks that not only don't you want any shared libraries now, but you won't want any dynamically loaded libraries later. This is a Bad Idea; complain to the linker hackers about it. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] multi-column unique constraints with nullable columns
On Sat, 30 Apr 2005, Tornroth, Phill wrote: I believe you can add partial unique indexes to cover the case where a column is null, but if you have multiple nullable columns you need to worry about you end up with a bunch of indexes. Hmmm. I hadn't thought of that, thanks. Yes, the indexes would be unnessecary though. Speaking of, should be concerned about indexing NULLABLE columns? I believe you're at least safe with btree indexes. The advantage of using the unique partial indexes is that it'll handle concurrent inserts without you having to worry about it. Also, is this in compliance with SQL92? I'm surprised constraints work this way. he I read that. I think you're right, it sounds like any comparison containing NULL at all will fail. I wrote the following procedure, which seems to do the trick. I guess my plan would be to write a bunch of these, and create the indexes manually. If anyone sees any problems with this, I'd love some input. Also, if anyone at the end of this email is a DBA/Consultant type and works in the San Diego area... Definitely let me know :) CREATE FUNCTION check_mytable_natkey() RETURNS opaque AS ' DECLARE conflictingpk integer; BEGIN SELECT INTO conflictingpk a FROM mytable WHERE ((b is null and NEW.b is null) or b = NEW.b) AND ((c is null and NEW.c is null) or c = NEW.c); Unfortunately, I don't think this will work if two sessions come in at the same time trying to insert the same values since they won't see each other's changes. I think it also will raise an error if the existing row has been deleted by a not yet committed transaction while our current implementation of unique constraints would wait to see if the transaction commits. On a side note, I believe (x is null and y is null) or x=y can be written a little more succintly with NOT(x IS DISTINCT FROM y). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] pgtop, display PostgreSQL processes in `top' style
Hi everybody, I'm currently working on something like a `mytop' clone, also written in Perl with DBI + DBD::Pg interface. mytop is a command line utility that displays mysql server status and clients connected modeled after `top' unix utility. (http://mytop.sourceforge.net). What I'd like to achieve is something like that, but for PostgreSQL, of course. Now the project is in the very early stages, but it already does something useful with Pg backend versions 7.2+ with stats enabled. Obviously, it looks at pg_stat* relations to extract some useful information. There are some things that are nice in `mytop', and I think it would be nice to have also in `pgtop', but I don't know how to replicate them: 1) is it possible to know Pg backend uptime with SQL queries? Or must I look at postmaster.pid file? or even something else? 2) how can I know the ip addresses/hostnames of clients connecting to server? 3) Is there a way to know the number of queries performed against Pg backend (by each client)? Can I distinguish between selects / updates / inserts / copy / ... ? If you want to take a look at it, point your browser at http://search.cpan.org/dist/pgtop . -- Cosimo ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
