[pgpool-general: 1000] Re: Fwd: Timestamp and pgpool-II 3.2
Tatsuo Ishii
ishii at postgresql.org
Fri Sep 14 06:33:29 JST 2012
pgpool log with -d option(debug) and log_per_node_statement enabled
please.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
> Thanks,Tatsuo, I did like you said and it works. however, doing an insert
> from pgadmin, configured to point to my virtual ip and port 9999, and
> inserting it from there, again there are differences between the servers,
> but the insert from console, no differences between timestamp fields with
> default now () on both server. the question is, I can not use pgadmin to
> insert or modify data to be pgPool-II?? that creating data objects and
> inclusive, well since I pgadmin replica, but with timestamp fields default now
> (), there does not record the same milliseconds
>
> Regards
>
> 2012/9/13 Tatsuo Ishii <ishii at postgresql.org>
>
>> 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