MIME-Version: 1.0
In-Reply-To: <BANLkTin_CFVj24_XX=+o_gZKtTCuqgENQg@mail.gmail.com>
References: <BANLkTimwCWg6LGavaNU3YMJNcBiWppX9UQ@mail.gmail.com>
	<op.vxuoogblkhmbxg@klump-pc.ghoffice>
	<BANLkTin_CFVj24_XX=+o_gZKtTCuqgENQg@mail.gmail.com>
Date: Thu, 30 Jun 2011 10:02:07 +0100
Message-ID: <BANLkTim7gViO93cRhBPCjd-+n96mTTrORw@mail.gmail.com>
Subject: Re: is parallel union all possible over dblink?
From: Svetlin Manavski <svetlin.manavski@gmail.com>
To: Greg Spiegelberg <gspiegelberg@gmail.com>
Cc: Marinos Yannikos <mjy@geizhals.at>, pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=20cf3054a70dd5073c04a6ea2706

--20cf3054a70dd5073c04a6ea2706
Content-Type: text/plain; charset=ISO-8859-1

I am now a bit puzzled after the initial satisfaction by Marinos' reply.

1. what do you mean exactly by "to ensure your UNION succeeds". The dblink
docs do not mention anything about issues using directly  the suggested
dblink_send_query() + dblink_get_results(). What problems should I expect in
using them as suggested by Marinos?

2. If I understand correctly your method, it is not applicable from inside a
stored procedure, is it? I need to keep all the business logic within
PostgreSQL and provide just a clean interface to a simple GUI layer

3. Unfortunately GridSQL and Pgpool-II do not seem mature and stable
products to be used in commercial software. Neither one provides clear
documentation. GridSQL has been discontinued and it is not clear what kind
of future it will have. I have not tried GridSQL but I did try Pgpool-II. It
is disappointing that it may stop working correctly even just because of the
way you write the query (e.g. using uppercase in a field or using named
field in group by, ecc.). Even worse, when it does not recognize something
in the parallel query, it just provides incorrect result (from only the
local DB) rather than raising an exception. So I guess Pgpool-II in its
current state is good only for very simple applications, which are not
supposed to be reliable at all.

Thank you,
Svetlin Manavski


On Wed, Jun 29, 2011 at 8:14 PM, Greg Spiegelberg <gspiegelberg@gmail.com>wrote:

>
> This does work however you'll need to add a little more to it to ensure
> your UNION succeeds.  In pseudo...
>
> connection #1:
> CREATE TABLE target_1 ...
> BEGIN;
> LOCK TABLE target_1 IN ACCESS EXCLUSIVE MODE;
> INSERT INTO target_1 SELECT ...
> COMMIT;
>
> connection #2:
> CREATE TABLE target_2 ...
> BEGIN;
> LOCK TABLE target_2 IN ACCESS EXCLUSIVE MODE;
> INSERT INTO target_2 SELECT ...
> COMMIT;
>
> connection #3:
> SELECT * FROM target_1 UNION SELECT * FROM target_2;
>
> Connections 1 and 2 can be done in simultaneously and after both have
> reached the LOCK statement then the SELECT on connection 3 can be executed.
>  Same fundamentals if all three connections are to different databases and
> connection 3 uses dblink to pull the data.
>
> Another alternative is to use GridSQL.  I haven't used it myself but seen
> it in action on a large install with 4 backend databases.  Pretty slick.
>
> Greg
>
>

--20cf3054a70dd5073c04a6ea2706
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I am now a bit puzzled after the initial satisfaction by Marinos&#39; reply=
. <br><br>1. what do you mean exactly by &quot;to ensure your UNION succeed=
s&quot;. The dblink docs do not mention anything about issues using directl=
y=A0 the suggested dblink_send_query() + dblink_get_results(). What problem=
s should I expect in using them as suggested by Marinos?<br>
<br>2. If I understand correctly your method, it is not applicable from ins=
ide a stored procedure, is it? I need to keep all the business logic within=
 PostgreSQL and provide just a clean interface to a simple GUI layer<br>
<br>3. Unfortunately GridSQL and Pgpool-II do not seem mature and stable pr=
oducts to be used in commercial software. Neither one provides clear docume=
ntation. GridSQL has been discontinued and it is not clear what kind of fut=
ure it will have. I have not tried GridSQL but I did try Pgpool-II. It is d=
isappointing that it may stop working correctly even just because of the wa=
y you write the query (e.g. using uppercase in a field or using named field=
 in group by, ecc.). Even worse, when it does not recognize something in th=
e parallel query, it just provides incorrect result (from only the local DB=
) rather than raising an exception. So I guess Pgpool-II in its current sta=
te is good only for very simple applications, which are not supposed to be =
reliable at all.<br>
<br>Thank you,<br>Svetlin Manavski<br><br><br><div class=3D"gmail_quote">On=
 Wed, Jun 29, 2011 at 8:14 PM, Greg Spiegelberg <span dir=3D"ltr">&lt;<a hr=
ef=3D"mailto:gspiegelberg@gmail.com">gspiegelberg@gmail.com</a>&gt;</span> =
wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex;"><div class=3D"gmail_quote"><div><div></div>=
<div class=3D"h5"><br></div></div><div>This does work however you&#39;ll ne=
ed to add a little more to it to ensure your UNION succeeds. =A0In pseudo..=
.</div>
<div><br></div><div>connection #1:</div><div>CREATE TABLE target_1 ...</div=
>
<div>BEGIN;</div><div>LOCK TABLE target_1 IN ACCESS EXCLUSIVE MODE;</div><d=
iv>INSERT INTO target_1 SELECT ...</div><div>COMMIT;</div><div><br></div><d=
iv><div>
connection #2:</div><div>CREATE TABLE target_2 ...</div><div>BEGIN;</div><d=
iv>LOCK TABLE target_2 IN ACCESS EXCLUSIVE MODE;</div><div>INSERT INTO targ=
et_2 SELECT ...</div><div>COMMIT;</div></div><div><br></div><div>connection=
 #3:</div>

<div>SELECT * FROM target_1 UNION SELECT * FROM target_2;</div><div><br></d=
iv><div>Connections 1 and 2 can be done in simultaneously and after both ha=
ve reached the LOCK statement then the SELECT on connection 3 can be execut=
ed. =A0Same fundamentals if all three connections are to different database=
s and connection 3 uses dblink to pull the data.</div>

<div><br></div><div>Another alternative is to use GridSQL. =A0I haven&#39;t=
 used it myself but seen it in action on a large install with 4 backend dat=
abases. =A0Pretty slick.</div><div><br></div><div>Greg</div><div><br></div>

</div>
</blockquote></div><br>

--20cf3054a70dd5073c04a6ea2706--