[pgpool-general: 999] Re: Fwd: Timestamp and pgpool-II 3.2
Diego Ayala
netdiego81 at gmail.com
Fri Sep 14 00:34:16 JST 2012
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
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20120913/2b1ab460/attachment-0001.html>
More information about the pgpool-general
mailing list