[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