X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id EC8D94763A7
	for <pgsql-sql@postgresql.org>; Wed,  7 May 2003 05:26:42 -0400 (EDT)
Received: from pns.mm.eutelsat.org (pns.mm.eutelsat.org [194.214.173.227])
	by postgresql.org (Postfix) with ESMTP id 6573D476355
	for <pgsql-sql@postgresql.org>; Wed,  7 May 2003 05:26:41 -0400 (EDT)
Received: from nts-03.mm.eutelsat.org (localhost [127.0.0.1])
	by pns.mm.eutelsat.org (8.11.6/linuxconf) with ESMTP id h478Req01750;
	Wed, 7 May 2003 10:27:40 +0200
Received: from GMENDOLA2 (gmendola2.mm.eutelsat.org [194.214.173.26])
	by nts-03.mm.eutelsat.org (8.11.6/linuxconf) with SMTP id h479OFR29981;
	Wed, 7 May 2003 11:24:15 +0200
Message-ID: <005101c3147a$c31c18f0$152aa8c0@GMENDOLA2>
From: "Mendola Gaetano" <mendola@bigfoot.com>
To: "Michael A Nachbaur" <mike@nachbaur.com>,
	"Diehl, Jeffrey" <jdiehl@sandia.gov>, <pgsql-sql@postgresql.org>
References: <B51F0C636E578A4E832D3958690CD73E0130C42C@es04snlnt.sandia.gov>
	<000e01c313f8$941ef5e0$10d4a8c0@mm.eutelsat.org>
	<200305061116.27628.mike@nachbaur.com>
Subject: Re: pgsql Replication Proxy (was Re: Replication for a
Date: Wed, 7 May 2003 11:26:37 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
x-mimeole: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Spam-Status: No, hits=-29.3 required=5.0
	tests=BAYES_01,EMAIL_ATTRIBUTION,QUOTED_EMAIL_TEXT,REFERENCES,
	REPLY_WITH_QUOTES autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200305/118
X-Sequence-Number: 13283

And if the first commit fail if a key is duplicated, or for another
trigger issue? Should be possible understand why the commit
fails and consider this a normal event too, I  think anyway that
is hard.
Also the Remirror ( the point B) is hard to do without
"Point in time recovery" how do a remirror of a live DB without
have an exclusive lock on it ?

Regards
Gaetano

On: Tuesday, May 06, 2003 8:16 PM "Michael A Nachbaur" <mike@nachbaur.com>
wrote:
> Then it is removed from the list of "valid" servers, and it isn't included
in
> updates, at least until a) a DBA fixes the issue, or b) the DBA tells the
> database server to re-mirror itself from the other available servers.
>
> Once two-phased commit support is available, this could be changed to be
much
> more robust.
>
> Any alternative ideas?
>
> On Tuesday 06 May 2003 10:54 am, Mendola Gaetano wrote:
> > Postgres don't have the support ( yet ) for the 2 phase commit, so
> > I think that is impossible do it now.
> > What happen if the last server do an error in commit phase ?
> >
> > Regards
> > Gaetano
> >
> > ----- Original Message -----
> > From: "Diehl, Jeffrey" <jdiehl@sandia.gov>
> > To: "'Michael A Nachbaur'" <mike@nachbaur.com>;
<pgsql-sql@postgresql.org>
> > Sent: Tuesday, May 06, 2003 6:28 PM
> > Subject: Re: pgsql Replication Proxy (was Re: [SQL] Replication for a
> >
> > > I love this idea.  The proxy could return immediately instead of
making
> > > my program block on update.
> > >
> > > One note, though.  Instead of a stack, you need a FIFO.  For example:
> > >
> > > delete from sometable where field=value;
> > > insert into sometable (field) values (value1);
> > > insert into sometable (field) values (value2);
> > > ....
> > >
> > >
> > > This code breaks in a stack and only works in a fifo.  Minor point,
> >
> > though.
> >
> > > So do we have a volunteer to write such a tool?  <grin>
> > >
> > > Mike Diehl.
> > >
> > > -----Original Message-----
> > > From: Michael A Nachbaur [mailto:mike@nachbaur.com]
> > > Sent: Monday, May 05, 2003 1:57 PM
> > > To: pgsql-sql@postgresql.org
> > > Subject: pgsql Replication Proxy (was Re: [SQL] Replication for a
large
> > > database)
> > >
> > >
> > > I've thought some more about this, and I want to pass this idea past
you
> > > guys.
> > > What do you think about a replication proxy, essentially a daemon that
> >
> > sits
> >
> > > between a PostgreSQL client and server.  Every single SQL query,
> >
> > transaction
> >
> > > statement, etc that the proxy recieves it repeats back to all the
> > > database servers.  In this way, if a back-end database server goes
down
> > > queries
> >
> > will
> >
> > > continue unabated (except the downed server won't recieve updates).
> > >
> > > Basically, the proxy server could intercept these queries and place
them
> >
> > in
> >
> > > a
> > > stack (on a per-database basis) and when every server in the queue
> > > acknowledges the update, the query is removed from the stack.  Each
> >
> > database
> >
> > > server can have their own position in the stack, so if servers A and B
> > > successfully run a query, but C doesn't (e.g. it requires human
> > > intervention), C is removed from the list of acceptable servers but A
and
> >
> > B
> >
> > > can keep moving through the queue.
> > >
> > > What do you think?  Also, should this discussion be moved to another
> >
> > mailing
> >
> > > list?
> > >
> > > On Monday 05 May 2003 12:26 pm, Michael A Nachbaur wrote:
> > > > I have thought about this.  The problem I come into is data
> > > > consistancy.
> > >
> > > I
> > >
> > > > have at least 8 different processes that harvest data, and an
intranet
> > > > website that can also manipulate the database (to assign customers
to
> > > > different packages, re-assign modems to different customers, etc).
> >
> > Trying
> >
> > > > to maintain consistancy across the entire application would be such
a
> > > > nightmare, I don't want to think about it.
> > > >
> > > > If I go with a centralized middleware server that manages all
database
> > > > access, then I could perhaps do that in there...and then I could use
> > > > transactions on both databases, and if either transaction fails then
> >
> > I'll
> >
> > > > roll back the other.  But this would make my entire framework very
> >
> > rigid.
> >
> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
> > >
> > >
> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>