[pgpool-general: 997] Re: Fwd: Timestamp and pgpool-II 3.2
Tatsuo Ishii
ishii at postgresql.org
Thu Sep 13 23:55:43 JST 2012
Your CREATE TABLE statement contains error (notice no space between
"name" and "character"). Probably what happend was:
1) "test" table was not created becuase of error.
2) subsequent INSERT failed because no such table exists.
3) in the mean time pgpool checks systems catalog whether test table
contains any timetamp columns. If the test succeeds, INSERT
statement which lacks values for timestamp columns are replaced
with constant time values taken from master node.
4) unfortunately because "test" table does not exist, the query
against the system catalog returns no data, so pgpool decides not
to replace timestamp columuns and this decision is cached.
5) later on "table" table was created by fixed create table statement.
6) same INSERT statement executed but because of the cache created at
#4, timestampe column rewritten did not happen and you see time
different between those servers.
Please fix the CREATE statement, retstart pgpool and do the test
again. If you enable log_per_node_statement, you should see time
stampe rewriting effect something like this:
2012-09-13 23:53:11 LOG: pid 19571: DB node id: 0 backend pid: 19664 statement: INSERT INTO "test"("name", "alta", "mod") VALUES ('first test','2012-09-13 23:53:11.704591+09','2012-09-13 23:53:11.704591+09')
2012-09-13 23:53:11 LOG: pid 19571: DB node id: 1 backend pid: 19663 statement: INSERT INTO "test"("name", "alta", "mod") VALUES ('first test','2012-09-13 23:53:11.704591+09','2012-09-13 23:53:11.704591+09')
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
> thanks for replying,
>
> I have my development database that contains the table test. well, to insert
> records, comparing both tables and I see the difference in milliseconds that
> were generated in each server. I need that times are exact, since we
> have a procurement
> system for auction, which requires the milliseconds, and that can make a
> difference to any bidder wins the bid.
> the truth is that it's random, I went back to try and generate some logs me
> equal, and others have differences in milliseconds
>
> CREATE TABLE test
> (
> id bigserial NOT NULL,
> namecharacter varying(200),
> alta timestamp without time zone DEFAULT now(),
> mod timestamp with time zone DEFAULT now()
> )
> WITH (
> OIDS=FALSE
> );
>
> insert into test(name) values('first test');
>
>
> server 1 : select * from prueba;
> id | nombre | tiempo |
> alta | mod
> ----+------------------------------+---------------------+----------------------------+-------------------------------
> 1 | diego | 2012-09-04 14:23:45 | 2012-09-04
> 14:25:33.040833 | 2012-09-04 14:56:57.631909-04
> 2 | ayala | 2012-09-04 14:24:22 | 2012-09-04
> 14:25:33.040833 | 2012-09-04 14:56:57.631909-04
> 3 | pepe | 2012-09-04 14:24:28 | 2012-09-04
> 14:25:33.040833 | 2012-09-04 14:56:57.631909-04
> 4 | mujica | 2012-09-04 14:24:35 | 2012-09-04
> 14:25:33.040833 | 2012-09-04 14:56:57.631909-04
> 5 | rolo | 2012-09-04 14:25:45 | 2012-09-04
> 14:25:45.143966 | 2012-09-04 14:56:57.631909-04
> 6 | chespi | 2012-09-04 14:26:00 | 2012-09-04
> 14:25:59.735838 | 2012-09-04 14:56:57.631909-04
> 7 | trifulcio rapado | 2012-09-04 14:26:26 | 2012-09-04
> 14:26:26.056678 | 2012-09-04 14:56:57.631909-04
> 8 | eraldo | 2012-09-04 14:27:45 | 2012-09-04
> 14:27:45.082837 | 2012-09-04 14:56:57.631909-04
> 9 | CORTINAS | 2012-09-04 14:53:07 | 2012-09-04
> 14:53:07.086145 | 2012-09-04 14:56:57.631909-04
> 10 | PUERTAS | 2012-09-04 14:57:30 | 2012-09-04
> 14:57:30.109121 | 2012-09-04 14:57:30.109121-04
>
> 11 | prueba de desarrollo | 2012-09-13 09:05:25 | 2012-09-13
> 09:05:25.393988 | 2012-09-13 09:05:25.393988-04
> 12 | segunda prueba de desarrollo | 2012-09-13 09:06:24 | 2012-09-13
> 09:06:24.083936 | 2012-09-13 09:06:24.083936-04
> (12 rows)
>
>
>
> server 2: select * from prueba;
> id | nombre | tiempo |
> alta | mod
> ----+------------------------------+---------------------+----------------------------+-------------------------------
> 1 | diego | 2012-09-04 14:23:45 | 2012-09-04
> 14:25:33.042316 | 2012-09-04 14:56:57.633128-04
> 2 | ayala | 2012-09-04 14:24:22 | 2012-09-04
> 14:25:33.042316 | 2012-09-04 14:56:57.633128-04
> 3 | pepe | 2012-09-04 14:24:28 | 2012-09-04
> 14:25:33.042316 | 2012-09-04 14:56:57.633128-04
> 4 | mujica | 2012-09-04 14:24:35 | 2012-09-04
> 14:25:33.042316 | 2012-09-04 14:56:57.633128-04
> 5 | rolo | 2012-09-04 14:25:45 | 2012-09-04
> 14:25:45.144954 | 2012-09-04 14:56:57.633128-04
> 6 | chespi | 2012-09-04 14:26:00 | 2012-09-04
> 14:25:59.736717 | 2012-09-04 14:56:57.633128-04
> 7 | trifulcio rapado | 2012-09-04 14:26:26 | 2012-09-04
> 14:26:26.057284 | 2012-09-04 14:56:57.633128-04
> 8 | eraldo | 2012-09-04 14:27:45 | 2012-09-04
> 14:27:45.083635 | 2012-09-04 14:56:57.633128-04
> 9 | CORTINAS | 2012-09-04 14:53:07 | 2012-09-04
> 14:53:07.087332 | 2012-09-04 14:56:57.633128-04
> 10 | PUERTAS | 2012-09-04 14:57:30 | 2012-09-04
> 14:57:30.110247 | 2012-09-04 14:57:30.110247-04
>
> 11 | prueba de desarrollo | 2012-09-13 09:05:25 | 2012-09-13
> 09:05:25.393988 | 2012-09-13 09:05:25.393988-04
> 12 | segunda prueba de desarrollo | 2012-09-13 09:06:24 | 2012-09-13
> 09:06:24.083936 | 2012-09-13 09:06:24.083936-04
> (12 rows)
>
>
>
>
> lazaro therefore seen, the watchdog does almost the same as the hearbeat,
> just as enbebido say already in the nucleus of pgPool-II, also, it proves
>
> thanks for the help
>
>
>
>
>
>
>
>
>
>
>
> 2012/9/12 Lazáro Rubén García Martínez <lgarciam at vnz.uci.cu>
>
>> The core of pgpool-II 3.2 has a new functionality developed for the high
>> availability of pgpool, it is called watchdog. Maybe you could use it
>> instead of heartbeat. This is not the case of the problem, this is only one
>> recomendation.
>>
>> Regards.
>> ________________________________________
>> From: pgpool-general-bounces at pgpool.net [pgpool-general-bounces at pgpool.net]
>> On Behalf Of Diego Ayala [netdiego81 at gmail.com]
>> Sent: Wednesday, September 12, 2012 5:18 PM
>> To: Tatsuo Ishii
>> Cc: pgpool-general at pgpool.net
>> Subject: [pgpool-general: 989] Re: Fwd: Timestamp and pgpool-II 3.2
>>
>> this is my config file. forget to mention, I'm also using heartbeat, high
>> availability for pgPool-II
>>
>> 2012/9/12 Tatsuo Ishii <ishii at postgresql.org<mailto:ishii at postgresql.org>>
>> > good afternoon, I have a question, if you could get me out of doubt, to
>> have
>> > a timestamp() without time zone NOT NULL DEFAULT now (), to replicate,
>> > there differences
>> > in thousandths between each server. That is a problem or pgPool-II, there
>> > is a solution to not happen? I'm using version 3.2 on pgPool REL6-II and
>> > PostgreSQL 9.1
>> >
>> > thanks for the help
>> >
>> > eg
>> > Server 1 = 2012-09-04 14:57:30.109121-04
>> > Server 2 = 2012-09-04 14:57:30.110247-04
>>
>> Please show us pgpool.conf.
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>>
>>
>> ________________________________
>> Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE
>> ENCUENTRAN INJUSTAMENTE EN PRISIONES DE LOS EEUU!
>> http://www.antiterroristas.cu
>> http://justiciaparaloscinco.wordpress.com
>>
>> Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE
>> ENCUENTRAN INJUSTAMENTE EN PRISIONES DE LOS EEUU!
>> http://www.antiterroristas.cu
>> http://justiciaparaloscinco.wordpress.com
>>
More information about the pgpool-general
mailing list