[Pgpool-general] [HELP] Sometime it's running sometime not
Hervé Piedvache
herve at elma.fr
Thu Sep 27 10:15:40 UTC 2007
Hi,
Yes I'm using pgpooll-II v1.2.
OK The php code you gived me work yes, once ... but if you make a loop you
will see errors ! (in my case ...)
So I have another demonstration for you ... Hope this will more usefull for
explanation ... forget PDO ... just using PSQL ...
What is done :
I deallocate a prepared statement
I look at the Slony-I node to see where I'm.
I prepare the statement
I execute the statement
I look again the Slony-I node used ... (I think it's un-necessary ...)
~ *- (echo "DEALLOCATE test; select * from
_cluster_ef.sl_local_node_id;";echo 'PREPARE test(text) as select * from
member where login = $1;';echo "EXECUTE test('footcow');select * from
_cluster_ef.sl_local_node_id;") | /usr/local/pgsql/bin/psql -U postgres
rss -h 192.168.0.151 -p 9999
Timing is on.
ERROR: prepared statement "test" does not exist
sequence_name | last_value | increment_by | max_value |
min_value | cache_value | log_cnt | is_cycled | is_called
------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
sl_local_node_id | 1 | 1 |
9223372036854775807 | -1 | 1 | 0 | f | t
(1 row)
Time: 1,213 ms
PREPARE
Time: 1,971 ms
member_id | id32 | login |
password_md5 | state | creation_date
-----------+----------------------------------+---------+----------------------------------+-------+----------------------------
6 | kaaxssO0YVzxvjNMvpaKoS6XNesLEYRp | footcow |
4310b48026b07655f32773107e60fef1 | 4 | 2007-05-16 11:11:20.582996
(1 row)
Time: 0,603 ms
sequence_name | last_value | increment_by | max_value |
min_value | cache_value | log_cnt | is_cycled | is_called
------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
sl_local_node_id | 1 | 1 |
9223372036854775807 | -1 | 1 | 0 | f | t
(1 row)
Time: 0,526 ms
It's running perfectly ... on host0 (Number 1 of Slony)
~ *- (echo "DEALLOCATE test;select * from
_cluster_ef.sl_local_node_id;";echo 'PREPARE test(text) as select * from
member where login = $1;';echo "EXECUTE test('footcow');select * from
_cluster_ef.sl_local_node_id;") | /usr/local/pgsql/bin/psql -U postgres
rss -h 192.168.0.151 -p 9999
Timing is on.
ERROR: prepared statement "test" does not exist
sequence_name | last_value | increment_by | max_value |
min_value | cache_value | log_cnt | is_cycled | is_called
------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
sl_local_node_id | 2 | 1 |
9223372036854775807 | -1 | 1 | 0 | f | t
(1 row)
Time: 1,340 ms
PREPARE
Time: 0,595 ms
ERROR: prepared statement "test" does not exist
sequence_name | last_value | increment_by | max_value |
min_value | cache_value | log_cnt | is_cycled | is_called
------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
sl_local_node_id | 2 | 1 |
9223372036854775807 | -1 | 1 | 0 | f | t
(1 row)
Time: 0,488 ms
Here I'm just using the host1 (Number 2 of Slony) ... and as you see it's not
running ...
In my pgpooll config I have done the test changing host0 and host1 IP
address ... I get the same result ... each time pgpool access to my host1 ...
I get the error with a not existing prepared statement ... :o(
I think you can easily reproduce this ... and I hope it's not only for me ;o)
I join my pgpool.conf file ...
Thanks for your help ...
Le jeudi 27 septembre 2007, Yoshiyuki Asaba a écrit :
> Hi,
>
> From: Hervé_Piedvache <herve at elma.fr>
> Subject: Re: Fwd: Re: [Pgpool-general] [HELP] Sometime it's running
> sometime not Date: Wed, 26 Sep 2007 12:08:27 +0200
>
> > Ok I think I have found the origine of the problem ...
> > If I have a PDO request from my php code ... it seems to being prepared
> > on the host0 and executed on the host1 !!!
>
> Do you use pgpool-II 1.2? It works perfectly fine with the following
> code.
>
> ----
> <?php
> try {
> $dbh = new PDO('pgsql:host=localhost;port=9999;dbname=test', 'y-asaba',
> '');
>
> echo "connect\n";
> $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
> $stmt = $dbh->prepare("select $1::int");
> $stmt->bindParam(1, $val, PDO::PARAM_INT);
> $val = 123;
> $stmt->execute();
> print_r($stmt->fetch());
> } catch (Exception $e) {
> echo "failed: " . $e->getMessage();
> }
>
> ?>
> ----
>
> BTW, I've fixed handling Parse message. It caused deadlock error.
> I think your problem have been fixed in CVS repository.
>
> ----
> 2007-09-25 22:53:57 DEBUG: pid 13321: read kind from frontend P(50)
> ^^^^^
> Parse message
> 2007-09-25 22:53:57 ERROR: pid 13321: pool_read_kind: kind does not match
> between master(49) slot[1] (69)
> ----
>
> We release pgpool-II V1.2.1 at tomorrow.
>
> Regards,
> --
> Yoshiyuki Asaba
> y-asaba at sraoss.co.jp
--
Hervé Piedvache
Elma Ingénierie Informatique
Groupe Maximiles S.A.
3 rue d'Uzès
F-75002 - Paris - France
Pho. 33-144949901
Fax. 33-144882747
-------------- next part --------------
#
# pgpool configuration file sample
# $Header: /cvsroot/pgpool/pgpool-II/pgpool.conf.sample,v 1.2 2006/11/29 21:54:26 devrim Exp $
# Host name or IP address to listen on: '*' for all, '' for no TCP/IP
# connections
listen_addresses = '192.168.0.151'
# Port number for pgpool
port = 9999
# Port number for pgpool communication manager
pcp_port = 9898
# Unix domain socket path. (The Debian package defaults to
# /var/run/postgresql.)
socket_dir = '/tmp'
# Unix domain socket path for pgpool communication manager.
# (Debian package defaults to /var/run/postgresql)
pcp_socket_dir = '/tmp'
# Unix domain socket path for the backend. Debian package defaults to /var/run/postgresql!
backend_socket_dir = '/tmp'
# pgpool communication manager timeout. 0 means no timeout, but strongly not recommended!
pcp_timeout = 10
# number of pre-forked child process
num_init_children = 64
# Number of connection pools allowed for a child process
max_pool = 5
# If idle for this many seconds, child exits. 0 means no timeout.
child_life_time = 300
# If idle for this many seconds, connection to PostgreSQL closes.
# 0 means no timeout.
connection_life_time = 0
# If child_max_connections connections were received, child exits.
# 0 means no exit.
child_max_connections = 0
# Logging directory
logdir = '/tmp'
# Replication mode
replication_mode = false
# Set this to true if you want to avoid deadlock situations when
# replication is enabled. There will, however, be a noticable performance
# degradation. A workaround is to set this to false and insert a /*STRICT*/
# comment at the beginning of the SQL command.
replication_strict = true
# When replication_strict is set to false, there will be a chance for
# deadlocks. Set this to nonzero (in milliseconds) to detect this
# situation and resolve the deadlock by aborting current session.
replication_timeout = 5000
# Load balancing mode, i.e., all SELECTs except in a transaction block
# are load balanced. This is ignored if replication_mode is false.
load_balance_mode = true
# if there's a data mismatch between master and secondary
# start degeneration to stop replication mode
replication_stop_on_mismatch = false
# Semicolon separated list of queries to be issued at the end of a session
reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
# If true print timestamp on each log line.
print_timestamp = true
# If true, operate in master/slave mode.
master_slave_mode = true
# If true, cache connection pool.
connection_cache = true
# Health check timeout. 0 means no timeout.
health_check_timeout = 20
# Health check period. 0 means no health check.
health_check_period = 0
# Health check user
health_check_user = 'nobody'
# If true, automatically lock table with INSERT statements to keep SERIAL
# data consistency. An /*INSERT LOCK*/ comment has the same effect. A
# /NO INSERT LOCK*/ comment disables the effect.
insert_lock = false
# If true, ignore leading white spaces of each query while pgpool judges
# whether the query is a SELECT so that it can be load balanced. This
# is useful for certain APIs such as DBI/DBD which is known to adding an
# extra leading white space.
ignore_leading_white_space = true
# If true, print all statements to the log. Like the log_statement option
# to PostgreSQL, this allows for observing queries without engaging in full
# debugging.
log_statement = true
# If true, incoming connections will be printed to the log.
log_connections = true
# If true, hostname will be shown in ps status. Also shown in
# connection log if log_connections = true.
# Be warned that this feature will add overhead to look up hostname.
log_hostname = true
# if non 0, run in parallel query mode
parallel_mode = false
# if non 0, use query cache
enable_query_cache = false
#set pgpool2 hostname
pgpool2_hostname = '192.168.0.151'
# system DB info
system_db_hostname = 'localhost'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'postgres'
system_db_password = ''
# backend_hostname, backend_port, backend_weight
# here are examples
backend_hostname0 = '192.168.0.151'
backend_port0 = 5432
backend_weight0 = 2
backend_hostname1 = '192.168.0.152'
backend_port1 = 5432
backend_weight1 = 2
#backend_hostname2 = '192.168.0.153'
#backend_port2 = 5432
#backend_weight2 = 2
#backend_hostname3 = '192.168.0.158'
#backend_port3 = 5432
#backend_weight3 = 2
# - HBA -
# If true, use pool_hba.conf for client authentication. In pgpool-II
# 1.1, the default value is false. The default value will be true in
# 1.2.
enable_pool_hba = false
More information about the Pgpool-general
mailing list