X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 80C7F476438
	for <pgsql-sql@postgresql.org>; Tue,  6 May 2003 14:16:30 -0400 (EDT)
Received: from dbdevel.sunwave.net (h64-141-16-15.sunwave.net [64.141.16.15])
	by postgresql.org (Postfix) with ESMTP id 90E8C476285
	for <pgsql-sql@postgresql.org>; Tue,  6 May 2003 14:16:27 -0400 (EDT)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by dbdevel.sunwave.net (Postfix) with ESMTP
	id BCFC7178002; Tue,  6 May 2003 14:16:27 -0400 (EDT)
From: Michael A Nachbaur <mike@nachbaur.com>
To: "Mendola Gaetano" <mendola@bigfoot.com>,
	"Diehl, Jeffrey" <jdiehl@sandia.gov>, <pgsql-sql@postgresql.org>
Subject: Re: pgsql Replication Proxy (was Re: Replication for a
Date: Tue, 6 May 2003 11:16:27 -0700
User-Agent: KMail/1.5
References: <B51F0C636E578A4E832D3958690CD73E0130C42C@es04snlnt.sandia.gov>
	<000e01c313f8$941ef5e0$10d4a8c0@mm.eutelsat.org>
In-Reply-To: <000e01c313f8$941ef5e0$10d4a8c0@mm.eutelsat.org>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200305061116.27628.mike@nachbaur.com>
X-Spam-Status: No, hits=-32.5 required=5.0
	tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT,
	REFERENCES,REPLY_WITH_QUOTES,USER_AGENT
	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/99
X-Sequence-Number: 13264

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