PHP: Hypertext Preprocessor (original) (raw)

pg_pconnect

(PHP 4, PHP 5, PHP 7, PHP 8)

pg_pconnect — Open a persistent PostgreSQL connection

Description

If a second call is made to pg_pconnect() with the same connection_string as an existing connection, the existing connection will be returned unless you pass**[PGSQL_CONNECT_FORCE_NEW](pgsql.constants.php#constant.pgsql-connect-force-new)** asflags.

To enable persistent connections, thepgsql.allow_persistent php.ini directive must be set to "On" (which is the default). The maximum number of persistent connections can be defined with thepgsql.max_persistent php.ini directive (defaults to -1 for no limit). The total number of connections can be set with thepgsql.max_links php.ini directive.

pg_close() will not close persistent links generated by pg_pconnect().

Parameters

connection_string

The connection_string can be empty to use all default parameters, or it can contain one or more parameter settings separated by whitespace. Each parameter setting is in the form keyword = value. Spaces around the equal sign are optional. To write an empty value or a value containing spaces, surround it with single quotes, e.g., keyword = 'a value'. Single quotes and backslashes within the value must be escaped with a backslash, i.e., \' and \\.

The currently recognized parameter keywords are:host, hostaddr, port,dbname, user,password, connect_timeout,options, tty (ignored), sslmode,requiressl (deprecated in favor of sslmode), andservice. Which of these arguments exist depends on the PostgreSQL version.

flags

If [PGSQL_CONNECT_FORCE_NEW](pgsql.constants.php#constant.pgsql-connect-force-new) is passed, then a new connection is created, even if the connection_string is identical to an existing connection.

Examples

Example #1 Using pg_pconnect()

<?php // Connect to a database named "mary" $dbconn = pg_pconnect("dbname=mary");// Connect to a database named "mary" on "localhost" at port "5432" $dbconn2 = pg_pconnect("host=localhost port=5432 dbname=mary");// Connect to a database named "mary" on the host "sheep" with a username and password $dbconn3 = pg_pconnect("host=sheep port=5432 dbname=mary user=lamb password=foo");// Connect to a database named "test" on the host "sheep" with a username and password $conn_string = "host=sheep port=5432 dbname=test user=lamb password=bar"; <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>d</mi><mi>b</mi><mi>c</mi><mi>o</mi><mi>n</mi><mi>n</mi><mn>4</mn><mo>=</mo><mi>p</mi><msub><mi>g</mi><mi>p</mi></msub><mi>c</mi><mi>o</mi><mi>n</mi><mi>n</mi><mi>e</mi><mi>c</mi><mi>t</mi><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">dbconn4 = pg_pconnect(</annotation></semantics></math>dbconn4=pgpconnect(conn_string); ?>

Found A Problem?

Dennis Fogg ¶

17 years ago

robertb ¶

16 years ago

`You should not use pg_pconnect - it's broken. It will work but it doesn't really pool, and it's behaviour is unpredictable. It will only make you rise the max_connections parameter in postgresql.conf file until you run out of resources (which will slow your database down).

If you have many concurrent connections to your database, you should use the PostgreSQL connection pooler PgBouncer (developed by the Skype-team). When using pgbouncer, make sure you use pg_connect and NOT pg_pconnect. Also, make sure you close your connections with pg_close.

PGBouncer homepage:
http://developer.skype.com/SkypeGarage/DbProjects/PgBouncer
PostgreSQL pooling article by Last.fm:
http://www.last.fm/user/Russ/journal/2008/02/21
/zd_postgres_connection_pools:_pgpool_vs._pgbouncer

Spiros Ioannou ¶

22 years ago

Instead of reducing MaxClients in apache you may try to reduce pgsql.max_links in php to at least the number of postmasters. It should work and leave you with more available httpds for static html pages.

garrett at bgb dot cc ¶

23 years ago

`If a transaction is in progress when page processing ends, is it aborted before the connection placed bak in the pool? Or is the connection added "as is"?

It would seem that the correct thing to do is to always 'ABORT' before adding to the pool.

As a note, this would be a good time to check and see if the connection is still open before readding it. Thus allowing closed connections to be cleaned up over time, instead of hanging around for ever as they do now.

ts at dev dot websafe dot pl ¶

17 years ago

`<?php
//
// Using pg_pconnect in a class.
//
// Why this? Because the manual says:
//
// If a second call is made to pg_pconnect() with the same
// connection_string as an existing connection, the existing
// connection will be returned unless you pass
// PGSQL_CONNECT_FORCE_NEW as connect_type.
//
// This is not always true.
//
/**

MyClassA creates a postgresql connection using pg_pconnect
and stores the resulting resource id to $this->conn
/
class MyClassA
{
function __construct($connection_string)
{
$this->conn =
pg_pconnect($connection_string)
or die('Wrong CONN_STRING');
}
}//
// Showing current php.ini settings to be sure
// that persistent connections s are allowed.
// -1 means 'unlimited'
//
echo '
pgsql.allow_persistent: ' . ini_get('pgsql.allow_persistent');
echo '
pgsql.max_persistent: ' . ini_get('pgsql.max_persistent');
echo '
pgsql.max_links: ' . ini_get('pgsql.max_links');
echo '

';// setting one custom connection string for all objects
// (modify $connection_string to fit your needs)
$connection_string =
'host=localhost port=5432' .
' dbname=test user=test password=test';//
// Creating 10 MyClassA objects using the same $connection_string
//
$objArr = Array();
for ($i = 0; i<10;i < 10; i<10;i++)
{ objArr[]=newMyClassA(objArr[] = new MyClassA(objArr[]=newMyClassA(connection_string);
}//
// Human readable result:
//
foreach($objArr as id=>id => id=>object)
{
printf(
'%s: Object %s: using db %s
',
get_class($object), id,id, id,object->conn
);
}/ ------------------------------------------------------------- /
// The result
// pgsql.allow_persistent: 1
// pgsql.max_persistent: -1
// pgsql.max_links: -1
//
// MyClassA: Object 0: using db Resource id #2
// MyClassA: Object 1: using db Resource id #3
// MyClassA: Object 2: using db Resource id #4
// MyClassA: Object 3: using db Resource id #5
// MyClassA: Object 4: using db Resource id #6
// MyClassA: Object 5: using db Resource id #7
// MyClassA: Object 6: using db Resource id #8
// MyClassA: Object 7: using db Resource id #9
// MyClassA: Object 8: using db Resource id #10
// MyClassA: Object 9: using db Resource id #11
//
/ ------------------------------------------------------------- */
//
// Each MyClassA object will use its own database Resource id
//
?>`

raggaflo at libertysurf dot fr ¶

23 years ago

Be careful when using Apache/PHP dynamic module/PostgreSQL : in httpd.conf (Apache conf) default MaxClients is 150, whereas default PG's max_connections is 32 which is much fewer than 150. You have to set max_connections to at least MaxClients (and pg's shared_buffers to 2*max_connections at least) to avoid PG's errors with pg_pconnect like : "Sorry, too many clients already connected"

etsysx dot i dot hate dot spam at teleline dot es ¶

23 years ago

To setup a high availability server with apache as a static module and postgreSQL, change httpd.conf and set MaxClients to less than max postgreSQL simultaneous connections (like 32 or 64). This way pg_pconnect will allways return a valid handle under heavy traffic or under a request flow attack without wasting resources and without connection problems.