postgres-xc-developers — Postgres-XC hackers and developers

Re: [Postgres-xc-developers] Replicated update and delete

[Abbas B. <abb...@en...>]

On Wed, Feb 12, 2014 at 3:47 PM, Mason Sharp <ms...@tr...>wrote:

>
>
>
> On Wed, Feb 12, 2014 at 1:08 AM, 鈴木 幸市 <ko...@in...> wrote:
>
>>  2014/02/12 15:00、Ashutosh Bapat <ash...@en...> のメール：
>>
>>
>>
>>
>> On Tue, Feb 11, 2014 at 8:03 PM, Abbas Butt <abb...@en...>wrote:
>>
>>>
>>>  The summary of the discussion so far:
>>>
>>>  Approach A: (Suggested by Amit)
>>> In the scan plan, fetch ctid, node_id from all the datanodes.
>>> While scanning, the tuples need to be fetched in the same order,
>>>  may be using order by 1, 2, 3, ...
>>>  Use UPDATE where ctd = ? , but use nodeid-based method to
>>> generate the ExecNodes at execute-time (enhance ExecNodes->en_expr
>>> evaluation so as to use the nodeid from source plan, as against
>>> the distribution column that it currently uses for distributed tables).
>>>  This method will not work as-is in case of non-shippable row triggers.
>>> Because trigger needs to be fired only once per row, and we are going
>>> to execute UPDATE for all of the ctids of a given row corresponding
>>> to all of the datanodes. So somehow we should fire triggers only once.
>>> This method will also hit performance, because currently we fetch *all*
>>> columns and not just ctid, so it's better to first do that optimization
>>> of fetching only reqd columns (there's one pending patch submitted in
>>> the mailing list, which fixes this).
>>>
>>
>>>  Approach B: (Suggested by many)
>>> If the replicated table does not have primary or unique not null key
>>> then error out on a non-shippable update or delete otherwise use the
>>> patch sent by Mason after some testing and refactoring.
>>>
>>>
>>  This would break backward compatibility. Also, a table which is fairly
>> stable and doesn't have a primary key or unique key, will need to be
>> distributed even though it's a perfect candidate for being a replicated
>> table. Also, we have to see if updating primary or unique key would cause a
>> problem.
>>
>>
>>  Then we should keep using the same WHERE close in shipped statement too.
>>    As pointed out, using ctid in replicated table is very dangerous.
>>
>
> I agree.
>
> I don't think it is unreasonable at all to require a primary key or unique
> index for replicated tables... normally one would want to do that. If they
> don't have a primary key, they themselves can just add a SERIAL at creation
> time and use that.
>
> As an alternative, all columns could be used as a fake primary key to try
> to find the particular row. In GridSQL we used that approach, but does not
> seem so clean... I believe that there is a check in there that if multiple
> rows match the criteria that the operation fails since the row is not
> uniquely identifiable. In hindsight, I wish we had not bothered.
>
>
>
>>
>>
>>   Approach C: (Suggested by Amit)
>>> Always have some kind of a hidden (or system) column for replicated
>>> tables.
>>>  Its type can be serial type, or an int column with default
>>> nextval('sequence_type')
>>>  so that it will always be executed on coordinator and use this colum
>>> as primary key.
>>>
>>>
>>  This looks a better approach, but also means that the inserts in the
>> replicated table have to be driven through the coordinator. This might not
>> be that stringent a condition, given that the replicated tables are
>> expected to be fairly stable. Any replicated table being inserted so often
>> would anyway get into the performance problem.
>>
>>
>>  I’m afraid it takes long effort to fix all the influences of this
>> change.   How do you think about this?    As I noted, approach C has good
>> point.   The issue is how long it takes.   With approach B, we can easily
>> change this handling to approach C.   I’d like to have you opinion on this.
>>
>
> It seems unnecessary if the table already has a primary key or unique
> index. Anyway, approach C is the approach that I originally took with
> GridSQL/Stado, adding something called xrowid, but we later disabled it by
> default.
>

Was there any other reason of disabling it other than code simplicity and
maintainability?


> In hindsight I would have saved the trouble and not implemented it to keep
> the code simpler and easier to maintain, and just left it up to the user to
> use a key.
>
> To summarize, I would go with B.
>

What is your stance on the fact that going with option B makes us backward
in-compatible?


>
>
>
>
>>
>>
>>>  My vote is for approach B.
>>>
>>>
>>  Whatever approach is taken, we will have to stick to it in future
>> versions of XC. We can not keep on changing the user visible functionality
>> with every version. Till 1.2 we didn't have the restriction that replicated
>> tables should have a primary key. Now introducing that requirement, means
>> users have to modify their applications. If we change it again, in the next
>> version, we will break the compatibility again. So, considering the long
>> term benefit, we should go with C.
>>
>>
>> This is reasonable to require at this stage since how it is done now is
> very dangerous. As previously mentioned, I have seen this cause problems
> with production data.  I think people would gladly add a key in exchange
> for not having bad things happen with their data.
>
> Also, I would not release any new version of Postgres-XC without this fix.
> If a release of 1.2 is a ways away, there should be an intermediate 1.1.x
> release that fixes this soon. I would not recommend using Postgres-XC for
> people who will be updating replicated tables without the patch I
> submitted, it is too dangerous.
>
>
> --
> Mason Sharp
>
> TransLattice - http://www.translattice.com
> Distributed and Clustered Database Solutions
>
>
>
>
> ------------------------------------------------------------------------------
> Android apps run on BlackBerry 10
> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
> Now with support for Jelly Bean, Bluetooth, Mapview and more.
> Get your Android app in front of a whole new audience.  Start now.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
>


-- 
-- 
*Abbas*
 Architect

Ph: 92.334.5100153
 Skype ID: gabbasb
www.enterprisedb.co
<http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars,
whitepapers<http://www.enterprisedb.com/resources-community>and
more<http://www.enterprisedb.com/resources-community>

Re: [Postgres-xc-developers] Replicated update and delete

[Mason S. <ms...@tr...>]

On Wed, Feb 12, 2014 at 1:08 AM, 鈴木 幸市 <ko...@in...> wrote:

>  2014/02/12 15:00、Ashutosh Bapat <ash...@en...> のメール：
>
>
>
>
> On Tue, Feb 11, 2014 at 8:03 PM, Abbas Butt <abb...@en...>wrote:
>
>>
>>  The summary of the discussion so far:
>>
>>  Approach A: (Suggested by Amit)
>> In the scan plan, fetch ctid, node_id from all the datanodes.
>> While scanning, the tuples need to be fetched in the same order,
>>  may be using order by 1, 2, 3, ...
>>  Use UPDATE where ctd = ? , but use nodeid-based method to
>> generate the ExecNodes at execute-time (enhance ExecNodes->en_expr
>> evaluation so as to use the nodeid from source plan, as against
>> the distribution column that it currently uses for distributed tables).
>>  This method will not work as-is in case of non-shippable row triggers.
>> Because trigger needs to be fired only once per row, and we are going
>> to execute UPDATE for all of the ctids of a given row corresponding
>> to all of the datanodes. So somehow we should fire triggers only once.
>> This method will also hit performance, because currently we fetch *all*
>> columns and not just ctid, so it's better to first do that optimization
>> of fetching only reqd columns (there's one pending patch submitted in
>> the mailing list, which fixes this).
>>
>
>>  Approach B: (Suggested by many)
>> If the replicated table does not have primary or unique not null key
>> then error out on a non-shippable update or delete otherwise use the
>> patch sent by Mason after some testing and refactoring.
>>
>>
>  This would break backward compatibility. Also, a table which is fairly
> stable and doesn't have a primary key or unique key, will need to be
> distributed even though it's a perfect candidate for being a replicated
> table. Also, we have to see if updating primary or unique key would cause a
> problem.
>
>
>  Then we should keep using the same WHERE close in shipped statement too.
>    As pointed out, using ctid in replicated table is very dangerous.
>

I agree.

I don't think it is unreasonable at all to require a primary key or unique
index for replicated tables... normally one would want to do that. If they
don't have a primary key, they themselves can just add a SERIAL at creation
time and use that.

As an alternative, all columns could be used as a fake primary key to try
to find the particular row. In GridSQL we used that approach, but does not
seem so clean... I believe that there is a check in there that if multiple
rows match the criteria that the operation fails since the row is not
uniquely identifiable. In hindsight, I wish we had not bothered.



>
>
>   Approach C: (Suggested by Amit)
>> Always have some kind of a hidden (or system) column for replicated
>> tables.
>>  Its type can be serial type, or an int column with default
>> nextval('sequence_type')
>>  so that it will always be executed on coordinator and use this colum as
>> primary key.
>>
>>
>  This looks a better approach, but also means that the inserts in the
> replicated table have to be driven through the coordinator. This might not
> be that stringent a condition, given that the replicated tables are
> expected to be fairly stable. Any replicated table being inserted so often
> would anyway get into the performance problem.
>
>
>  I’m afraid it takes long effort to fix all the influences of this
> change.   How do you think about this?    As I noted, approach C has good
> point.   The issue is how long it takes.   With approach B, we can easily
> change this handling to approach C.   I’d like to have you opinion on this.
>

It seems unnecessary if the table already has a primary key or unique
index. Anyway, approach C is the approach that I originally took with
GridSQL/Stado, adding something called xrowid, but we later disabled it by
default. In hindsight I would have saved the trouble and not implemented it
to keep the code simpler and easier to maintain, and just left it up to the
user to use a key.

To summarize, I would go with B.




>
>
>>  My vote is for approach B.
>>
>>
>  Whatever approach is taken, we will have to stick to it in future
> versions of XC. We can not keep on changing the user visible functionality
> with every version. Till 1.2 we didn't have the restriction that replicated
> tables should have a primary key. Now introducing that requirement, means
> users have to modify their applications. If we change it again, in the next
> version, we will break the compatibility again. So, considering the long
> term benefit, we should go with C.
>
>
> This is reasonable to require at this stage since how it is done now is
very dangerous. As previously mentioned, I have seen this cause problems
with production data.  I think people would gladly add a key in exchange
for not having bad things happen with their data.

Also, I would not release any new version of Postgres-XC without this fix.
If a release of 1.2 is a ways away, there should be an intermediate 1.1.x
release that fixes this soon. I would not recommend using Postgres-XC for
people who will be updating replicated tables without the patch I
submitted, it is too dangerous.


-- 
Mason Sharp

TransLattice - http://www.translattice.com
Distributed and Clustered Database Solutions

Re: [Postgres-xc-developers] How to "order by" rows obtained by RETURNING clause

[Koichi S. <koi...@gm...>]

It is just to maintain the order of rows and there are no other
intention.   It is just like adding ORDER BY clause, NUM NODES OFF and
NODES OFF to maintain .out identical.

RETURNING clause reports rows but the order may vary occasionally,
unlike vanilla PG.   We need a means to maintain the row order in such
a case.

Regards;
---
Koichi Suzuki


2014-02-12 18:48 GMT+09:00 Ashutosh Bapat <ash...@en...>:
> Hi
> Just a note, while changing the testcases like this, please make sure that
> the original intention of the testcase is not challenged. Can you please
> paste some examples?
>
>
> On Wed, Feb 12, 2014 at 3:03 PM, 鈴木 幸市 <ko...@in...> wrote:
>>
>> How about SELECT * FROM (UPDATE ,,,, RETURNING *) AS X ORDER BY 1, 2; ?
>> ---
>> Koichi Suzuki
>>
>> 2014/02/12 17:18、Abbas Butt <abb...@en...> のメール：
>>
>>
>>
>>
>> On Wed, Feb 12, 2014 at 12:14 PM, Koichi Suzuki <koi...@gm...>
>> wrote:
>>>
>>> I'm doing XC 1.2 release to fix regression more robust.
>>>
>>> I found RETURNING clause occasionally reruns rows in different order.
>>>   It is what we should expect but is not good because regression
>>> fails.
>>>
>>> Does anybody know how to order rows from RETURNING clause?
>>
>>
>> Yes, you can use WITH e.g.
>>
>> with t as
>> (
>>   insert into rep_foo values(3,4), (5,6), (7,8)
>>   returning b, a, b, b, b+a, b-a, ctid
>> ) select * from t order by 1, 2;
>>
>> We have used this technique in xc_returning.sql
>>
>>>
>>>
>>> Regards;
>>> ---
>>> Koichi Suzuki
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Android apps run on BlackBerry 10
>>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>>> Get your Android app in front of a whole new audience.  Start now.
>>>
>>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>>> _______________________________________________
>>> Postgres-xc-developers mailing list
>>> Pos...@li...
>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>>
>>
>>
>> --
>> --
>> Abbas
>> Architect
>>
>> Ph: 92.334.5100153
>> Skype ID: gabbasb
>> www.enterprisedb.com
>>
>> Follow us on Twitter
>> @EnterpriseDB
>>
>> Visit EnterpriseDB for tutorials, webinars, whitepapers and more
>>
>> ------------------------------------------------------------------------------
>> Android apps run on BlackBerry 10
>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>> Get your Android app in front of a whole new audience.  Start now.
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk_______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Android apps run on BlackBerry 10
>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>> Get your Android app in front of a whole new audience.  Start now.
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>
> ------------------------------------------------------------------------------
> Android apps run on BlackBerry 10
> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
> Now with support for Jelly Bean, Bluetooth, Mapview and more.
> Get your Android app in front of a whole new audience.  Start now.
> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>

Re: [Postgres-xc-developers] How to "order by" rows obtained by RETURNING clause

[Ashutosh B. <ash...@en...>]

Hi
Just a note, while changing the testcases like this, please make sure that
the original intention of the testcase is not challenged. Can you please
paste some examples?


On Wed, Feb 12, 2014 at 3:03 PM, 鈴木 幸市 <ko...@in...> wrote:

>  How about SELECT * FROM (UPDATE ,,,, RETURNING *) AS X ORDER BY 1, 2; ?
> ---
> Koichi Suzuki
>
>  2014/02/12 17:18、Abbas Butt <abb...@en...> のメール：
>
>
>
>
> On Wed, Feb 12, 2014 at 12:14 PM, Koichi Suzuki <koi...@gm...>wrote:
>
>> I'm doing XC 1.2 release to fix regression more robust.
>>
>> I found RETURNING clause occasionally reruns rows in different order.
>>   It is what we should expect but is not good because regression
>> fails.
>>
>> Does anybody know how to order rows from RETURNING clause?
>>
>
>  Yes, you can use WITH e.g.
>
>  with t as
> (
>   insert into rep_foo values(3,4), (5,6), (7,8)
>   returning b, a, b, b, b+a, b-a, ctid
> ) select * from t order by 1, 2;
>
>  We have used this technique in xc_returning.sql
>
>
>>
>> Regards;
>> ---
>> Koichi Suzuki
>>
>>
>> ------------------------------------------------------------------------------
>> Android apps run on BlackBerry 10
>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>> Get your Android app in front of a whole new audience.  Start now.
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>
>
>
>  --
> --
> *Abbas*
>  Architect
>
> Ph: 92.334.5100153
>  Skype ID: gabbasb
>  www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>
>
> * Follow us on Twitter*
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community>
>
> ------------------------------------------------------------------------------
> Android apps run on BlackBerry 10
> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
> Now with support for Jelly Bean, Bluetooth, Mapview and more.
> Get your Android app in front of a whole new audience.  Start now.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk_______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
>
>
>
> ------------------------------------------------------------------------------
> Android apps run on BlackBerry 10
> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
> Now with support for Jelly Bean, Bluetooth, Mapview and more.
> Get your Android app in front of a whole new audience.  Start now.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
>


-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: [Postgres-xc-developers] How to "order by" rows obtained by RETURNING clause

[鈴木 幸市 <ko...@in...>]

How about SELECT * FROM (UPDATE ,,,, RETURNING *) AS X ORDER BY 1, 2; ?
---
Koichi Suzuki

2014/02/12 17:18、Abbas Butt <abb...@en...<mailto:abb...@en...>> のメール：




On Wed, Feb 12, 2014 at 12:14 PM, Koichi Suzuki <koi...@gm...<mailto:koi...@gm...>> wrote:
I'm doing XC 1.2 release to fix regression more robust.

I found RETURNING clause occasionally reruns rows in different order.
  It is what we should expect but is not good because regression
fails.

Does anybody know how to order rows from RETURNING clause?

Yes, you can use WITH e.g.

with t as
(
  insert into rep_foo values(3,4), (5,6), (7,8)
  returning b, a, b, b, b+a, b-a, ctid
) select * from t order by 1, 2;

We have used this technique in xc_returning.sql


Regards;
---
Koichi Suzuki

------------------------------------------------------------------------------
Android apps run on BlackBerry 10
Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
Now with support for Jelly Bean, Bluetooth, Mapview and more.
Get your Android app in front of a whole new audience.  Start now.
http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
_______________________________________________
Postgres-xc-developers mailing list
Pos...@li...<mailto:Pos...@li...>
https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers



--
--
Abbas
Architect

Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co<http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>

Follow us on Twitter
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community> and more<http://www.enterprisedb.com/resources-community>
------------------------------------------------------------------------------
Android apps run on BlackBerry 10
Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
Now with support for Jelly Bean, Bluetooth, Mapview and more.
Get your Android app in front of a whole new audience.  Start now.
http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk_______________________________________________
Postgres-xc-developers mailing list
Pos...@li...
https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers

Re: [Postgres-xc-developers] How to "order by" rows obtained by RETURNING clause

[Abbas B. <abb...@en...>]

On Wed, Feb 12, 2014 at 12:14 PM, Koichi Suzuki <koi...@gm...>wrote:

> I'm doing XC 1.2 release to fix regression more robust.
>
> I found RETURNING clause occasionally reruns rows in different order.
>   It is what we should expect but is not good because regression
> fails.
>
> Does anybody know how to order rows from RETURNING clause?
>

Yes, you can use WITH e.g.

with t as
(
  insert into rep_foo values(3,4), (5,6), (7,8)
  returning b, a, b, b, b+a, b-a, ctid
) select * from t order by 1, 2;

We have used this technique in xc_returning.sql


>
> Regards;
> ---
> Koichi Suzuki
>
>
> ------------------------------------------------------------------------------
> Android apps run on BlackBerry 10
> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
> Now with support for Jelly Bean, Bluetooth, Mapview and more.
> Get your Android app in front of a whole new audience.  Start now.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>



-- 
-- 
*Abbas*
 Architect

Ph: 92.334.5100153
 Skype ID: gabbasb
www.enterprisedb.co
<http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars,
whitepapers<http://www.enterprisedb.com/resources-community>and
more<http://www.enterprisedb.com/resources-community>

[Postgres-xc-developers] How to "order by" rows obtained by RETURNING clause

[Koichi S. <koi...@gm...>]

I'm doing XC 1.2 release to fix regression more robust.

I found RETURNING clause occasionally reruns rows in different order.
  It is what we should expect but is not good because regression
fails.

Does anybody know how to order rows from RETURNING clause?

Regards;
---
Koichi Suzuki

Re: [Postgres-xc-developers] Replicated update and delete

[鈴木 幸市 <ko...@in...>]

2014/02/12 15:00、Ashutosh Bapat <ash...@en...<mailto:ash...@en...>> のメール：




On Tue, Feb 11, 2014 at 8:03 PM, Abbas Butt <abb...@en...<mailto:abb...@en...>> wrote:

The summary of the discussion so far:

Approach A: (Suggested by Amit)
In the scan plan, fetch ctid, node_id from all the datanodes.
While scanning, the tuples need to be fetched in the same order,
may be using order by 1, 2, 3, ...
Use UPDATE where ctd = ? , but use nodeid-based method to
generate the ExecNodes at execute-time (enhance ExecNodes->en_expr
evaluation so as to use the nodeid from source plan, as against
the distribution column that it currently uses for distributed tables).
This method will not work as-is in case of non-shippable row triggers.
Because trigger needs to be fired only once per row, and we are going
to execute UPDATE for all of the ctids of a given row corresponding
to all of the datanodes. So somehow we should fire triggers only once.
This method will also hit performance, because currently we fetch *all*
columns and not just ctid, so it's better to first do that optimization
of fetching only reqd columns (there's one pending patch submitted in
the mailing list, which fixes this).

Approach B: (Suggested by many)
If the replicated table does not have primary or unique not null key
then error out on a non-shippable update or delete otherwise use the
patch sent by Mason after some testing and refactoring.


This would break backward compatibility. Also, a table which is fairly stable and doesn't have a primary key or unique key, will need to be distributed even though it's a perfect candidate for being a replicated table. Also, we have to see if updating primary or unique key would cause a problem.

Then we should keep using the same WHERE close in shipped statement too.    As pointed out, using ctid in replicated table is very dangerous.


Approach C: (Suggested by Amit)
Always have some kind of a hidden (or system) column for replicated tables.
Its type can be serial type, or an int column with default nextval('sequence_type')
so that it will always be executed on coordinator and use this colum as primary key.


This looks a better approach, but also means that the inserts in the replicated table have to be driven through the coordinator. This might not be that stringent a condition, given that the replicated tables are expected to be fairly stable. Any replicated table being inserted so often would anyway get into the performance problem.

I’m afraid it takes long effort to fix all the influences of this change.   How do you think about this?    As I noted, approach C has good point.   The issue is how long it takes.   With approach B, we can easily change this handling to approach C.   I’d like to have you opinion on this.

Regards;
—
Koichi Suzuki



My vote is for approach B.


Whatever approach is taken, we will have to stick to it in future versions of XC. We can not keep on changing the user visible functionality with every version. Till 1.2 we didn't have the restriction that replicated tables should have a primary key. Now introducing that requirement, means users have to modify their applications. If we change it again, in the next version, we will break the compatibility again. So, considering the long term benefit, we should go with C.

Comments?

Best Regards



On Fri, Nov 8, 2013 at 10:05 AM, Amit Khandekar <ami...@en...<mailto:ami...@en...>> wrote:



On 7 November 2013 19:50, Mason Sharp <ms...@tr...<mailto:ms...@tr...>> wrote:



On Thu, Nov 7, 2013 at 12:45 AM, 鈴木 幸市 <ko...@in...<mailto:ko...@in...>> wrote:
Yes, we need to focus on such general solution for replicated tuple identification.

I'm afraid it may take much more research and implementation work.   I believe the submitted patch handles tuple replica based on the primary key or other equivalents if available.   If not, the code falls into the current case, local CTID.   The latter could lead to inconsistent replica but it is far better than the current situation.

For short-term solution, I think Mason's code looks reasonable if I understand the patch correctly.

Mason, do you have any more thoughts/comments?

I don't think it is unreasonable if a primary/unique key is required to handle this case.

I did some testing, but it would be nice if someone else gave it a test as well. I enabled statement logging to make sure it was doing the right thing.

I see someone mentioned a patch out there to only get needed columns. At the moment extra columns may be used, but at least the data remains consistent across the cluster, which is most important here. Unfortunately, I do not have time at the moment to improve this further.  If someone else has time, that would be great, or done as a separate commit.

Anyway, since this is a critical issue, I think it should get committed to STABLE_1_1 once reviewed.



---
Koichi Suzuki

On 2013/11/07, at 14:21, Amit Khandekar <ami...@en...<mailto:ami...@en...>>
 wrote:




On 6 November 2013 18:31, Michael Paquier <mic...@gm...<mailto:mic...@gm...>> wrote:
On Wed, Nov 6, 2013 at 3:28 PM, Amit Khandekar
<ami...@en...<mailto:ami...@en...>> wrote:
> What exactly does the PostgreSQL FDW doc say about updates and primary key ?
By having a look here:
http://www.postgresql.org/docs/9.3/static/fdw-callbacks.html#FDW-CALLBACKS-UPDATE
It is recommended to use a kind of row ID or the primary key columns.
In the case of XC row ID = CTID, and its uniqueness is not guaranteed
except if coupled with a node ID, which I think it has... Using a CTID
+ node ID combination makes the analysis of tuple uniqueness
impossible for replicated tables either way, so a primary key would be
better IMO.

> How does the postgres_fdw update a table that has no primary or unique key ?
It uses the CTID when scanning remote tuples for UPDATE/DELETE, thing
guarantying that tuples are unique in this case as the FDW deals with
a single server, here is for example the case of 2 nodes listening
ports 5432 and 5433.
$ psql -p 5433 -c "CREATE TABLE aa (a int, b int);"
CREATE TABLE

On server with port 5432:
=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
=# CREATE SERVER postgres_server FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', port '5432', dbname 'ioltas');
CREATE SERVER
=# CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS (password '');
CREATE USER MAPPING
=# CREATE FOREIGN TABLE aa_foreign (a int, b int) SERVER
postgres_server OPTIONS (table_name 'aa');
CREATE FOREIGN TABLE
=# explain verbose update  aa_foreign set a = 1, b=2 where a = 1;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Update on public.aa_foreign  (cost=100.00..144.40 rows=14 width=6)
   Remote SQL: UPDATE public.aa SET a = $2, b = $3 WHERE ctid = $1
   ->  Foreign Scan on public.aa_foreign  (cost=100.00..144.40 rows=14 width=6)
         Output: 1, 2, ctid
         Remote SQL: SELECT ctid FROM public.aa WHERE ((a = 1)) FOR UPDATE
(5 rows)
And ctid is used for scanning...

> In the patch, what do we do when the replicated table has no unique/primary
> key ?
I didn't look at the patch, but I think that replicated tables should
also need a primary key. Let's imagine something like that with
sessions S1 and S2 for a replication table, and 2 datanodes (1 session
runs in common on 1 Coordinator and each Datanode):
S1: INSERT VALUES foo in Dn1
S2: INSERT VALUES foo2 in Dn1
S2: INSERT VALUES foo2 in Dn2
S1: INSERT VALUES foo in Dn2
This will imply that those tuples have a different CTID, so a primary
key would be necessary as I think that this is possible.

If the patch does not handle the case of replicated table without unique key, I think we should have a common solution which takes care of this case also. Or else, if this solution can be extended to handle no-unique-key case, then that would be good. But I think we would end up in having two different implementations, one for unique-key method, and another for the other method, which does not seem good.

The method I had in mind was :
In the scan plan, fetch ctid, node_id from all the datanodes.  Use UPDATE where ctd = ? , but use nodeid-based method to  generate the ExecNodes at execute-time  (enhance ExecNodes->en_expr evaluation so as to use the nodeid from source plan, as against the distribution column that it currently uses for distributed tables) .

Would that work in all cases? What if 2 tuples on each node fulfill the criteria and a sequence is value being assigned? Might the tuples be processed in a different order on each node the data ends up being inconsistent  (tuple A gets the value 101, B gets the value 102 on node 1, and B gets 101, A gets 102 on node 2).  I am not sure it is worth trying to handle the case, and just require a primary key or unique index.
Yes, the tuples need to be fetched in the same order. May be using order by 1, 2, 3, ... . (I hope that if one column is found to be having unique values for a set of rows, sorting is not attempted again for the same set of rows using the remaining columns).

Another approach can be considered where we would always have some kind of a hidden (or system) column for replicated tables Its type can be serial type, or an int column with default nextval('sequence_type') so that it will always be executed on coordinator. And use this one as against the primary key. Or may be create table with oids. But not sure if OIDs get incremented/wrapped around exactly the same way regardless of anything. Also, they are documented as having deprecated.

These are just some thoughts for a long term solution. I myself don't have the bandwidth to work on XC at this moment, so won't be able to review the patch, so I don't want to fall into a situation where the patch is reworked and I won't review it after all. But these are just points to be thought of in case the would-be reviewer or the submitter feels like extending/modifying this patch for a long term solution taking care of tables without primary key.


But this method will not work as-is in case of non-shippable row triggers. Because trigger needs to be fired only once per row, and we are going to execute UPDATE for all of the ctids of a given row corresponding to all of the datanodes. So somehow we should fire triggers only once. This method will also hit performance, because currently we fetch *all* columns and not just ctid, so it's better to first do that optimization of fetching only reqd columns (there's one pending patch submitted in the mailing list, which fixes this).

This is just one approach, there might be better approaches..

Overall, I think if we decide to get this issue solved (and I think we should really, this is a serious issue), sufficient resource time needs to be given to think over and have discussions before we finalize the approach.


--
Michael

------------------------------------------------------------------------------
November Webinars for C, C++, Fortran Developers
Accelerate application performance with scalable programming models. Explore
techniques for threading, error checking, porting, and tuning. Get the most
from the latest Intel processors and coprocessors. See abstracts and register
http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk_______________________________________________
Postgres-xc-developers mailing list
Pos...@li...<mailto:Pos...@li...>
https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers


------------------------------------------------------------------------------
November Webinars for C, C++, Fortran Developers
Accelerate application performance with scalable programming models. Explore
techniques for threading, error checking, porting, and tuning. Get the most
from the latest Intel processors and coprocessors. See abstracts and register
http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
_______________________________________________
Postgres-xc-developers mailing list
Pos...@li...<mailto:Pos...@li...>
https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers




--
Mason Sharp

TransLattice - http://www.translattice.com<http://www.translattice.com/>
Distributed and Clustered Database Solutions




------------------------------------------------------------------------------
November Webinars for C, C++, Fortran Developers
Accelerate application performance with scalable programming models. Explore
techniques for threading, error checking, porting, and tuning. Get the most
from the latest Intel processors and coprocessors. See abstracts and register
http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
_______________________________________________
Postgres-xc-developers mailing list
Pos...@li...<mailto:Pos...@li...>
https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers




--
--
Abbas
Architect

Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co<http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>

Follow us on Twitter
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community> and more<http://www.enterprisedb.com/resources-community>

------------------------------------------------------------------------------
Android apps run on BlackBerry 10
Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
Now with support for Jelly Bean, Bluetooth, Mapview and more.
Get your Android app in front of a whole new audience.  Start now.
http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
_______________________________________________
Postgres-xc-developers mailing list
Pos...@li...<mailto:Pos...@li...>
https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers




--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
------------------------------------------------------------------------------
Android apps run on BlackBerry 10
Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
Now with support for Jelly Bean, Bluetooth, Mapview and more.
Get your Android app in front of a whole new audience.  Start now.
http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk_______________________________________________
Postgres-xc-developers mailing list
Pos...@li...
https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers

Re: [Postgres-xc-developers] Replicated update and delete

[Ashutosh B. <ash...@en...>]

On Tue, Feb 11, 2014 at 8:03 PM, Abbas Butt <abb...@en...>wrote:

>
> The summary of the discussion so far:
>
> Approach A: (Suggested by Amit)
> In the scan plan, fetch ctid, node_id from all the datanodes.
> While scanning, the tuples need to be fetched in the same order,
> may be using order by 1, 2, 3, ...
> Use UPDATE where ctd = ? , but use nodeid-based method to
> generate the ExecNodes at execute-time (enhance ExecNodes->en_expr
> evaluation so as to use the nodeid from source plan, as against
> the distribution column that it currently uses for distributed tables).
> This method will not work as-is in case of non-shippable row triggers.
> Because trigger needs to be fired only once per row, and we are going
> to execute UPDATE for all of the ctids of a given row corresponding
> to all of the datanodes. So somehow we should fire triggers only once.
> This method will also hit performance, because currently we fetch *all*
> columns and not just ctid, so it's better to first do that optimization
> of fetching only reqd columns (there's one pending patch submitted in
> the mailing list, which fixes this).
>

> Approach B: (Suggested by many)
> If the replicated table does not have primary or unique not null key
> then error out on a non-shippable update or delete otherwise use the
> patch sent by Mason after some testing and refactoring.
>
>
This would break backward compatibility. Also, a table which is fairly
stable and doesn't have a primary key or unique key, will need to be
distributed even though it's a perfect candidate for being a replicated
table. Also, we have to see if updating primary or unique key would cause a
problem.

Approach C: (Suggested by Amit)
> Always have some kind of a hidden (or system) column for replicated tables.
> Its type can be serial type, or an int column with default
> nextval('sequence_type')
> so that it will always be executed on coordinator and use this colum as
> primary key.
>
>
This looks a better approach, but also means that the inserts in the
replicated table have to be driven through the coordinator. This might not
be that stringent a condition, given that the replicated tables are
expected to be fairly stable. Any replicated table being inserted so often
would anyway get into the performance problem.


> My vote is for approach B.
>
>
Whatever approach is taken, we will have to stick to it in future versions
of XC. We can not keep on changing the user visible functionality with
every version. Till 1.2 we didn't have the restriction that replicated
tables should have a primary key. Now introducing that requirement, means
users have to modify their applications. If we change it again, in the next
version, we will break the compatibility again. So, considering the long
term benefit, we should go with C.


> Comments?
>
> Best Regards
>
>
>
> On Fri, Nov 8, 2013 at 10:05 AM, Amit Khandekar <
> ami...@en...> wrote:
>
>>
>>
>>
>> On 7 November 2013 19:50, Mason Sharp <ms...@tr...> wrote:
>>
>>>
>>>
>>>
>>> On Thu, Nov 7, 2013 at 12:45 AM, 鈴木 幸市 <ko...@in...> wrote:
>>>
>>>>  Yes, we need to focus on such general solution for replicated tuple
>>>> identification.
>>>>
>>>>  I'm afraid it may take much more research and implementation work.   I
>>>> believe the submitted patch handles tuple replica based on the primary key
>>>> or other equivalents if available.   If not, the code falls into the
>>>> current case, local CTID.   The latter could lead to inconsistent replica
>>>> but it is far better than the current situation.
>>>>
>>>>  For short-term solution, I think Mason's code looks reasonable if I
>>>> understand the patch correctly.
>>>>
>>>>  Mason, do you have any more thoughts/comments?
>>>>
>>>
>>> I don't think it is unreasonable if a primary/unique key is required to
>>> handle this case.
>>>
>>>  I did some testing, but it would be nice if someone else gave it a test
>>> as well. I enabled statement logging to make sure it was doing the right
>>> thing.
>>>
>>> I see someone mentioned a patch out there to only get needed columns. At
>>> the moment extra columns may be used, but at least the data remains
>>> consistent across the cluster, which is most important here. Unfortunately,
>>> I do not have time at the moment to improve this further.  If someone else
>>> has time, that would be great, or done as a separate commit.
>>>
>>> Anyway, since this is a critical issue, I think it should get committed
>>> to STABLE_1_1 once reviewed.
>>>
>>>
>>>
>>>
>>>>  ---
>>>> Koichi Suzuki
>>>>
>>>>  On 2013/11/07, at 14:21, Amit Khandekar <
>>>> ami...@en...>
>>>>  wrote:
>>>>
>>>>
>>>>
>>>>
>>>> On 6 November 2013 18:31, Michael Paquier <mic...@gm...>wrote:
>>>>
>>>>> On Wed, Nov 6, 2013 at 3:28 PM, Amit Khandekar
>>>>> <ami...@en...> wrote:
>>>>> > What exactly does the PostgreSQL FDW doc say about updates and
>>>>> primary key ?
>>>>>  By having a look here:
>>>>>
>>>>> http://www.postgresql.org/docs/9.3/static/fdw-callbacks.html#FDW-CALLBACKS-UPDATE
>>>>> It is recommended to use a kind of row ID or the primary key columns.
>>>>> In the case of XC row ID = CTID, and its uniqueness is not guaranteed
>>>>> except if coupled with a node ID, which I think it has... Using a CTID
>>>>> + node ID combination makes the analysis of tuple uniqueness
>>>>> impossible for replicated tables either way, so a primary key would be
>>>>> better IMO.
>>>>>
>>>>> > How does the postgres_fdw update a table that has no primary or
>>>>> unique key ?
>>>>>  It uses the CTID when scanning remote tuples for UPDATE/DELETE, thing
>>>>> guarantying that tuples are unique in this case as the FDW deals with
>>>>> a single server, here is for example the case of 2 nodes listening
>>>>> ports 5432 and 5433.
>>>>> $ psql -p 5433 -c "CREATE TABLE aa (a int, b int);"
>>>>> CREATE TABLE
>>>>>
>>>>> On server with port 5432:
>>>>> =# CREATE EXTENSION postgres_fdw;
>>>>> CREATE EXTENSION
>>>>> =# CREATE SERVER postgres_server FOREIGN DATA WRAPPER postgres_fdw
>>>>> OPTIONS (host 'localhost', port '5432', dbname 'ioltas');
>>>>> CREATE SERVER
>>>>> =# CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS
>>>>> (password '');
>>>>> CREATE USER MAPPING
>>>>> =# CREATE FOREIGN TABLE aa_foreign (a int, b int) SERVER
>>>>> postgres_server OPTIONS (table_name 'aa');
>>>>> CREATE FOREIGN TABLE
>>>>> =# explain verbose update  aa_foreign set a = 1, b=2 where a = 1;
>>>>>                                    QUERY PLAN
>>>>>
>>>>> --------------------------------------------------------------------------------
>>>>>  Update on public.aa_foreign  (cost=100.00..144.40 rows=14 width=6)
>>>>>    Remote SQL: UPDATE public.aa SET a = $2, b = $3 WHERE ctid = $1
>>>>>    ->  Foreign Scan on public.aa_foreign  (cost=100.00..144.40 rows=14
>>>>> width=6)
>>>>>          Output: 1, 2, ctid
>>>>>          Remote SQL: SELECT ctid FROM public.aa WHERE ((a = 1)) FOR
>>>>> UPDATE
>>>>> (5 rows)
>>>>> And ctid is used for scanning...
>>>>>
>>>>> > In the patch, what do we do when the replicated table has no
>>>>> unique/primary
>>>>> > key ?
>>>>>  I didn't look at the patch, but I think that replicated tables should
>>>>> also need a primary key. Let's imagine something like that with
>>>>> sessions S1 and S2 for a replication table, and 2 datanodes (1 session
>>>>> runs in common on 1 Coordinator and each Datanode):
>>>>> S1: INSERT VALUES foo in Dn1
>>>>> S2: INSERT VALUES foo2 in Dn1
>>>>> S2: INSERT VALUES foo2 in Dn2
>>>>> S1: INSERT VALUES foo in Dn2
>>>>> This will imply that those tuples have a different CTID, so a primary
>>>>> key would be necessary as I think that this is possible.
>>>>>
>>>>
>>>>  If the patch does not handle the case of replicated table without
>>>> unique key, I think we should have a common solution which takes care of
>>>> this case also. Or else, if this solution can be extended to handle
>>>> no-unique-key case, then that would be good. But I think we would end up in
>>>> having two different implementations, one for unique-key method, and
>>>> another for the other method, which does not seem good.
>>>>
>>>>  The method I had in mind was :
>>>> In the scan plan, fetch ctid, node_id from all the datanodes.  Use
>>>> UPDATE where ctd = ? , but use nodeid-based method to  generate the
>>>> ExecNodes at execute-time  (enhance ExecNodes->en_expr evaluation so as to
>>>> use the nodeid from source plan, as against the distribution column that it
>>>> currently uses for distributed tables) .
>>>>
>>>>
>>> Would that work in all cases? What if 2 tuples on each node fulfill the
>>> criteria and a sequence is value being assigned? Might the tuples be
>>> processed in a different order on each node the data ends up being
>>> inconsistent  (tuple A gets the value 101, B gets the value 102 on node 1,
>>> and B gets 101, A gets 102 on node 2).  I am not sure it is worth trying to
>>> handle the case, and just require a primary key or unique index.
>>>
>> Yes, the tuples need to be fetched in the same order. May be using order
>> by 1, 2, 3, ... . (I hope that if one column is found to be having unique
>> values for a set of rows, sorting is not attempted again for the same set
>> of rows using the remaining columns).
>>
>> Another approach can be considered where we would always have some kind
>> of a hidden (or system) column for replicated tables Its type can be serial
>> type, or an int column with default nextval('sequence_type') so that it
>> will always be executed on coordinator. And use this one as against the
>> primary key. Or may be create table with oids. But not sure if OIDs get
>> incremented/wrapped around exactly the same way regardless of anything.
>> Also, they are documented as having deprecated.
>>
>> These are just some thoughts for a long term solution. I myself don't
>> have the bandwidth to work on XC at this moment, so won't be able to review
>> the patch, so I don't want to fall into a situation where the patch is
>> reworked and I won't review it after all. But these are just points to be
>> thought of in case the would-be reviewer or the submitter feels like
>> extending/modifying this patch for a long term solution taking care of
>> tables without primary key.
>>
>>
>>>
>>>>  But this method will not work as-is in case of non-shippable row
>>>> triggers. Because trigger needs to be fired only once per row, and we are
>>>> going to execute UPDATE for all of the ctids of a given row corresponding
>>>> to all of the datanodes. So somehow we should fire triggers only once. This
>>>> method will also hit performance, because currently we fetch *all* columns
>>>> and not just ctid, so it's better to first do that optimization of fetching
>>>> only reqd columns (there's one pending patch submitted in the mailing list,
>>>> which fixes this).
>>>>
>>>>  This is just one approach, there might be better approaches..
>>>>
>>>>  Overall, I think if we decide to get this issue solved (and I think
>>>> we should really, this is a serious issue), sufficient resource time needs
>>>> to be given to think over and have discussions before we finalize the
>>>> approach.
>>>>
>>>>
>>>>  --
>>>>> Michael
>>>>>
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> November Webinars for C, C++, Fortran Developers
>>>> Accelerate application performance with scalable programming models.
>>>> Explore
>>>> techniques for threading, error checking, porting, and tuning. Get the
>>>> most
>>>> from the latest Intel processors and coprocessors. See abstracts and
>>>> register
>>>>
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk_______________________________________________
>>>> Postgres-xc-developers mailing list
>>>> Pos...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>
>>>>
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> November Webinars for C, C++, Fortran Developers
>>>> Accelerate application performance with scalable programming models.
>>>> Explore
>>>> techniques for threading, error checking, porting, and tuning. Get the
>>>> most
>>>> from the latest Intel processors and coprocessors. See abstracts and
>>>> register
>>>>
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
>>>> _______________________________________________
>>>> Postgres-xc-developers mailing list
>>>> Pos...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>
>>>>
>>>
>>>
>>> --
>>> Mason Sharp
>>>
>>> TransLattice - http://www.translattice.com
>>> Distributed and Clustered Database Solutions
>>>
>>>
>>>
>>
>>
>> ------------------------------------------------------------------------------
>> November Webinars for C, C++, Fortran Developers
>> Accelerate application performance with scalable programming models.
>> Explore
>> techniques for threading, error checking, porting, and tuning. Get the
>> most
>> from the latest Intel processors and coprocessors. See abstracts and
>> register
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>>
>
>
> --
> --
> *Abbas*
>  Architect
>
> Ph: 92.334.5100153
>  Skype ID: gabbasb
> www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>
>
> *Follow us on Twitter*
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community>
>
>
> ------------------------------------------------------------------------------
> Android apps run on BlackBerry 10
> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
> Now with support for Jelly Bean, Bluetooth, Mapview and more.
> Get your Android app in front of a whole new audience.  Start now.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
>


-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: [Postgres-xc-developers] Replicated update and delete

[Koichi S. <koi...@gm...>]

I'd vote for approach B.

Some more inputs:

I'm not sure if Approach A works fine.  Approach C is what I suggested
as well.   It is just OID but local to a given table.   This looks to
work against both replicated and distributed table.   On the other
hand, influence of this is much bigger than approach B and could be
much more than XC can handle as its own feature.

Approach B looks reasonable.   One concern is if it works with current
regression.   I hope it's not serious because most of the tests use
distributed tables, not replicated ones.

Regards;
---
Koichi Suzuki


2014-02-11 23:33 GMT+09:00 Abbas Butt <abb...@en...>:
>
> The summary of the discussion so far:
>
> Approach A: (Suggested by Amit)
> In the scan plan, fetch ctid, node_id from all the datanodes.
> While scanning, the tuples need to be fetched in the same order,
> may be using order by 1, 2, 3, ...
> Use UPDATE where ctd = ? , but use nodeid-based method to
> generate the ExecNodes at execute-time (enhance ExecNodes->en_expr
> evaluation so as to use the nodeid from source plan, as against
> the distribution column that it currently uses for distributed tables).
> This method will not work as-is in case of non-shippable row triggers.
> Because trigger needs to be fired only once per row, and we are going
> to execute UPDATE for all of the ctids of a given row corresponding
> to all of the datanodes. So somehow we should fire triggers only once.
> This method will also hit performance, because currently we fetch *all*
> columns and not just ctid, so it's better to first do that optimization
> of fetching only reqd columns (there's one pending patch submitted in
> the mailing list, which fixes this).
>
> Approach B: (Suggested by many)
> If the replicated table does not have primary or unique not null key
> then error out on a non-shippable update or delete otherwise use the
> patch sent by Mason after some testing and refactoring.
>
> Approach C: (Suggested by Amit)
> Always have some kind of a hidden (or system) column for replicated tables.
> Its type can be serial type, or an int column with default
> nextval('sequence_type')
> so that it will always be executed on coordinator and use this colum as
> primary key.
>
> My vote is for approach B.
>
> Comments?
>
> Best Regards
>
>
>
> On Fri, Nov 8, 2013 at 10:05 AM, Amit Khandekar
> <ami...@en...> wrote:
>>
>>
>>
>>
>> On 7 November 2013 19:50, Mason Sharp <ms...@tr...> wrote:
>>>
>>>
>>>
>>>
>>> On Thu, Nov 7, 2013 at 12:45 AM, 鈴木 幸市 <ko...@in...> wrote:
>>>>
>>>> Yes, we need to focus on such general solution for replicated tuple
>>>> identification.
>>>>
>>>> I'm afraid it may take much more research and implementation work.   I
>>>> believe the submitted patch handles tuple replica based on the primary key
>>>> or other equivalents if available.   If not, the code falls into the current
>>>> case, local CTID.   The latter could lead to inconsistent replica but it is
>>>> far better than the current situation.
>>>>
>>>> For short-term solution, I think Mason's code looks reasonable if I
>>>> understand the patch correctly.
>>>>
>>>> Mason, do you have any more thoughts/comments?
>>>
>>>
>>> I don't think it is unreasonable if a primary/unique key is required to
>>> handle this case.
>>>
>>> I did some testing, but it would be nice if someone else gave it a test
>>> as well. I enabled statement logging to make sure it was doing the right
>>> thing.
>>>
>>> I see someone mentioned a patch out there to only get needed columns. At
>>> the moment extra columns may be used, but at least the data remains
>>> consistent across the cluster, which is most important here. Unfortunately,
>>> I do not have time at the moment to improve this further.  If someone else
>>> has time, that would be great, or done as a separate commit.
>>>
>>> Anyway, since this is a critical issue, I think it should get committed
>>> to STABLE_1_1 once reviewed.
>>>
>>>
>>>
>>>>
>>>> ---
>>>> Koichi Suzuki
>>>>
>>>> On 2013/11/07, at 14:21, Amit Khandekar
>>>> <ami...@en...>
>>>>  wrote:
>>>>
>>>>
>>>>
>>>>
>>>> On 6 November 2013 18:31, Michael Paquier <mic...@gm...>
>>>> wrote:
>>>>>
>>>>> On Wed, Nov 6, 2013 at 3:28 PM, Amit Khandekar
>>>>> <ami...@en...> wrote:
>>>>> > What exactly does the PostgreSQL FDW doc say about updates and
>>>>> > primary key ?
>>>>> By having a look here:
>>>>>
>>>>> http://www.postgresql.org/docs/9.3/static/fdw-callbacks.html#FDW-CALLBACKS-UPDATE
>>>>> It is recommended to use a kind of row ID or the primary key columns.
>>>>> In the case of XC row ID = CTID, and its uniqueness is not guaranteed
>>>>> except if coupled with a node ID, which I think it has... Using a CTID
>>>>> + node ID combination makes the analysis of tuple uniqueness
>>>>> impossible for replicated tables either way, so a primary key would be
>>>>> better IMO.
>>>>>
>>>>> > How does the postgres_fdw update a table that has no primary or
>>>>> > unique key ?
>>>>> It uses the CTID when scanning remote tuples for UPDATE/DELETE, thing
>>>>> guarantying that tuples are unique in this case as the FDW deals with
>>>>> a single server, here is for example the case of 2 nodes listening
>>>>> ports 5432 and 5433.
>>>>> $ psql -p 5433 -c "CREATE TABLE aa (a int, b int);"
>>>>> CREATE TABLE
>>>>>
>>>>> On server with port 5432:
>>>>> =# CREATE EXTENSION postgres_fdw;
>>>>> CREATE EXTENSION
>>>>> =# CREATE SERVER postgres_server FOREIGN DATA WRAPPER postgres_fdw
>>>>> OPTIONS (host 'localhost', port '5432', dbname 'ioltas');
>>>>> CREATE SERVER
>>>>> =# CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS
>>>>> (password '');
>>>>> CREATE USER MAPPING
>>>>> =# CREATE FOREIGN TABLE aa_foreign (a int, b int) SERVER
>>>>> postgres_server OPTIONS (table_name 'aa');
>>>>> CREATE FOREIGN TABLE
>>>>> =# explain verbose update  aa_foreign set a = 1, b=2 where a = 1;
>>>>>                                    QUERY PLAN
>>>>>
>>>>> --------------------------------------------------------------------------------
>>>>>  Update on public.aa_foreign  (cost=100.00..144.40 rows=14 width=6)
>>>>>    Remote SQL: UPDATE public.aa SET a = $2, b = $3 WHERE ctid = $1
>>>>>    ->  Foreign Scan on public.aa_foreign  (cost=100.00..144.40 rows=14
>>>>> width=6)
>>>>>          Output: 1, 2, ctid
>>>>>          Remote SQL: SELECT ctid FROM public.aa WHERE ((a = 1)) FOR
>>>>> UPDATE
>>>>> (5 rows)
>>>>> And ctid is used for scanning...
>>>>>
>>>>> > In the patch, what do we do when the replicated table has no
>>>>> > unique/primary
>>>>> > key ?
>>>>> I didn't look at the patch, but I think that replicated tables should
>>>>> also need a primary key. Let's imagine something like that with
>>>>> sessions S1 and S2 for a replication table, and 2 datanodes (1 session
>>>>> runs in common on 1 Coordinator and each Datanode):
>>>>> S1: INSERT VALUES foo in Dn1
>>>>> S2: INSERT VALUES foo2 in Dn1
>>>>> S2: INSERT VALUES foo2 in Dn2
>>>>> S1: INSERT VALUES foo in Dn2
>>>>> This will imply that those tuples have a different CTID, so a primary
>>>>> key would be necessary as I think that this is possible.
>>>>
>>>>
>>>> If the patch does not handle the case of replicated table without unique
>>>> key, I think we should have a common solution which takes care of this case
>>>> also. Or else, if this solution can be extended to handle no-unique-key
>>>> case, then that would be good. But I think we would end up in having two
>>>> different implementations, one for unique-key method, and another for the
>>>> other method, which does not seem good.
>>>>
>>>> The method I had in mind was :
>>>> In the scan plan, fetch ctid, node_id from all the datanodes.  Use
>>>> UPDATE where ctd = ? , but use nodeid-based method to  generate the
>>>> ExecNodes at execute-time  (enhance ExecNodes->en_expr evaluation so as to
>>>> use the nodeid from source plan, as against the distribution column that it
>>>> currently uses for distributed tables) .
>>>
>>>
>>> Would that work in all cases? What if 2 tuples on each node fulfill the
>>> criteria and a sequence is value being assigned? Might the tuples be
>>> processed in a different order on each node the data ends up being
>>> inconsistent  (tuple A gets the value 101, B gets the value 102 on node 1,
>>> and B gets 101, A gets 102 on node 2).  I am not sure it is worth trying to
>>> handle the case, and just require a primary key or unique index.
>>
>> Yes, the tuples need to be fetched in the same order. May be using order
>> by 1, 2, 3, ... . (I hope that if one column is found to be having unique
>> values for a set of rows, sorting is not attempted again for the same set of
>> rows using the remaining columns).
>>
>> Another approach can be considered where we would always have some kind of
>> a hidden (or system) column for replicated tables Its type can be serial
>> type, or an int column with default nextval('sequence_type') so that it will
>> always be executed on coordinator. And use this one as against the primary
>> key. Or may be create table with oids. But not sure if OIDs get
>> incremented/wrapped around exactly the same way regardless of anything.
>> Also, they are documented as having deprecated.
>>
>> These are just some thoughts for a long term solution. I myself don't have
>> the bandwidth to work on XC at this moment, so won't be able to review the
>> patch, so I don't want to fall into a situation where the patch is reworked
>> and I won't review it after all. But these are just points to be thought of
>> in case the would-be reviewer or the submitter feels like
>> extending/modifying this patch for a long term solution taking care of
>> tables without primary key.
>>
>>>
>>>>
>>>> But this method will not work as-is in case of non-shippable row
>>>> triggers. Because trigger needs to be fired only once per row, and we are
>>>> going to execute UPDATE for all of the ctids of a given row corresponding to
>>>> all of the datanodes. So somehow we should fire triggers only once. This
>>>> method will also hit performance, because currently we fetch *all* columns
>>>> and not just ctid, so it's better to first do that optimization of fetching
>>>> only reqd columns (there's one pending patch submitted in the mailing list,
>>>> which fixes this).
>>>>
>>>> This is just one approach, there might be better approaches..
>>>>
>>>> Overall, I think if we decide to get this issue solved (and I think we
>>>> should really, this is a serious issue), sufficient resource time needs to
>>>> be given to think over and have discussions before we finalize the approach.
>>>>
>>>>
>>>>> --
>>>>> Michael
>>>>
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> November Webinars for C, C++, Fortran Developers
>>>> Accelerate application performance with scalable programming models.
>>>> Explore
>>>> techniques for threading, error checking, porting, and tuning. Get the
>>>> most
>>>> from the latest Intel processors and coprocessors. See abstracts and
>>>> register
>>>>
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk_______________________________________________
>>>> Postgres-xc-developers mailing list
>>>> Pos...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>
>>>>
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> November Webinars for C, C++, Fortran Developers
>>>> Accelerate application performance with scalable programming models.
>>>> Explore
>>>> techniques for threading, error checking, porting, and tuning. Get the
>>>> most
>>>> from the latest Intel processors and coprocessors. See abstracts and
>>>> register
>>>>
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
>>>> _______________________________________________
>>>> Postgres-xc-developers mailing list
>>>> Pos...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>
>>>
>>>
>>>
>>> --
>>> Mason Sharp
>>>
>>> TransLattice - http://www.translattice.com
>>> Distributed and Clustered Database Solutions
>>>
>>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> November Webinars for C, C++, Fortran Developers
>> Accelerate application performance with scalable programming models.
>> Explore
>> techniques for threading, error checking, porting, and tuning. Get the
>> most
>> from the latest Intel processors and coprocessors. See abstracts and
>> register
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>
>
>
> --
> --
> Abbas
> Architect
>
> Ph: 92.334.5100153
> Skype ID: gabbasb
> www.enterprisedb.com
>
> Follow us on Twitter
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers and more
>
> ------------------------------------------------------------------------------
> Android apps run on BlackBerry 10
> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
> Now with support for Jelly Bean, Bluetooth, Mapview and more.
> Get your Android app in front of a whole new audience.  Start now.
> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>

Re: [Postgres-xc-developers] [Postgres-xc-committers] Postgres-XC branch, REL1_2_STABLE, deleted. XC1_0_BETA1_PG9_1-3983-g544dee1

[Michael P. <mic...@gm...>]

On Tue, Feb 11, 2014 at 12:06 PM, Koichi Suzuki <koi...@gm...> wrote:
> Yes, I made a mistake to begin this branch from wrong commit point.
> Correction has been pushed with beta tag.
>
> Sorry for the inconvenience.
No pb. Thanks!
-- 
Michael

Re: [Postgres-xc-developers] Replicated update and delete

[Abbas B. <abb...@en...>]

The summary of the discussion so far:

Approach A: (Suggested by Amit)
In the scan plan, fetch ctid, node_id from all the datanodes.
While scanning, the tuples need to be fetched in the same order,
may be using order by 1, 2, 3, ...
Use UPDATE where ctd = ? , but use nodeid-based method to
generate the ExecNodes at execute-time (enhance ExecNodes->en_expr
evaluation so as to use the nodeid from source plan, as against
the distribution column that it currently uses for distributed tables).
This method will not work as-is in case of non-shippable row triggers.
Because trigger needs to be fired only once per row, and we are going
to execute UPDATE for all of the ctids of a given row corresponding
to all of the datanodes. So somehow we should fire triggers only once.
This method will also hit performance, because currently we fetch *all*
columns and not just ctid, so it's better to first do that optimization
of fetching only reqd columns (there's one pending patch submitted in
the mailing list, which fixes this).

Approach B: (Suggested by many)
If the replicated table does not have primary or unique not null key
then error out on a non-shippable update or delete otherwise use the
patch sent by Mason after some testing and refactoring.

Approach C: (Suggested by Amit)
Always have some kind of a hidden (or system) column for replicated tables.
Its type can be serial type, or an int column with default
nextval('sequence_type')
so that it will always be executed on coordinator and use this colum as
primary key.

My vote is for approach B.

Comments?

Best Regards



On Fri, Nov 8, 2013 at 10:05 AM, Amit Khandekar <
ami...@en...> wrote:

>
>
>
> On 7 November 2013 19:50, Mason Sharp <ms...@tr...> wrote:
>
>>
>>
>>
>> On Thu, Nov 7, 2013 at 12:45 AM, 鈴木 幸市 <ko...@in...> wrote:
>>
>>>  Yes, we need to focus on such general solution for replicated tuple
>>> identification.
>>>
>>>  I'm afraid it may take much more research and implementation work.   I
>>> believe the submitted patch handles tuple replica based on the primary key
>>> or other equivalents if available.   If not, the code falls into the
>>> current case, local CTID.   The latter could lead to inconsistent replica
>>> but it is far better than the current situation.
>>>
>>>  For short-term solution, I think Mason's code looks reasonable if I
>>> understand the patch correctly.
>>>
>>>  Mason, do you have any more thoughts/comments?
>>>
>>
>> I don't think it is unreasonable if a primary/unique key is required to
>> handle this case.
>>
>>  I did some testing, but it would be nice if someone else gave it a test
>> as well. I enabled statement logging to make sure it was doing the right
>> thing.
>>
>> I see someone mentioned a patch out there to only get needed columns. At
>> the moment extra columns may be used, but at least the data remains
>> consistent across the cluster, which is most important here. Unfortunately,
>> I do not have time at the moment to improve this further.  If someone else
>> has time, that would be great, or done as a separate commit.
>>
>> Anyway, since this is a critical issue, I think it should get committed
>> to STABLE_1_1 once reviewed.
>>
>>
>>
>>
>>>  ---
>>> Koichi Suzuki
>>>
>>>  On 2013/11/07, at 14:21, Amit Khandekar <
>>> ami...@en...>
>>>  wrote:
>>>
>>>
>>>
>>>
>>> On 6 November 2013 18:31, Michael Paquier <mic...@gm...>wrote:
>>>
>>>> On Wed, Nov 6, 2013 at 3:28 PM, Amit Khandekar
>>>> <ami...@en...> wrote:
>>>> > What exactly does the PostgreSQL FDW doc say about updates and
>>>> primary key ?
>>>>  By having a look here:
>>>>
>>>> http://www.postgresql.org/docs/9.3/static/fdw-callbacks.html#FDW-CALLBACKS-UPDATE
>>>> It is recommended to use a kind of row ID or the primary key columns.
>>>> In the case of XC row ID = CTID, and its uniqueness is not guaranteed
>>>> except if coupled with a node ID, which I think it has... Using a CTID
>>>> + node ID combination makes the analysis of tuple uniqueness
>>>> impossible for replicated tables either way, so a primary key would be
>>>> better IMO.
>>>>
>>>> > How does the postgres_fdw update a table that has no primary or
>>>> unique key ?
>>>>  It uses the CTID when scanning remote tuples for UPDATE/DELETE, thing
>>>> guarantying that tuples are unique in this case as the FDW deals with
>>>> a single server, here is for example the case of 2 nodes listening
>>>> ports 5432 and 5433.
>>>> $ psql -p 5433 -c "CREATE TABLE aa (a int, b int);"
>>>> CREATE TABLE
>>>>
>>>> On server with port 5432:
>>>> =# CREATE EXTENSION postgres_fdw;
>>>> CREATE EXTENSION
>>>> =# CREATE SERVER postgres_server FOREIGN DATA WRAPPER postgres_fdw
>>>> OPTIONS (host 'localhost', port '5432', dbname 'ioltas');
>>>> CREATE SERVER
>>>> =# CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS
>>>> (password '');
>>>> CREATE USER MAPPING
>>>> =# CREATE FOREIGN TABLE aa_foreign (a int, b int) SERVER
>>>> postgres_server OPTIONS (table_name 'aa');
>>>> CREATE FOREIGN TABLE
>>>> =# explain verbose update  aa_foreign set a = 1, b=2 where a = 1;
>>>>                                    QUERY PLAN
>>>>
>>>> --------------------------------------------------------------------------------
>>>>  Update on public.aa_foreign  (cost=100.00..144.40 rows=14 width=6)
>>>>    Remote SQL: UPDATE public.aa SET a = $2, b = $3 WHERE ctid = $1
>>>>    ->  Foreign Scan on public.aa_foreign  (cost=100.00..144.40 rows=14
>>>> width=6)
>>>>          Output: 1, 2, ctid
>>>>          Remote SQL: SELECT ctid FROM public.aa WHERE ((a = 1)) FOR
>>>> UPDATE
>>>> (5 rows)
>>>> And ctid is used for scanning...
>>>>
>>>> > In the patch, what do we do when the replicated table has no
>>>> unique/primary
>>>> > key ?
>>>>  I didn't look at the patch, but I think that replicated tables should
>>>> also need a primary key. Let's imagine something like that with
>>>> sessions S1 and S2 for a replication table, and 2 datanodes (1 session
>>>> runs in common on 1 Coordinator and each Datanode):
>>>> S1: INSERT VALUES foo in Dn1
>>>> S2: INSERT VALUES foo2 in Dn1
>>>> S2: INSERT VALUES foo2 in Dn2
>>>> S1: INSERT VALUES foo in Dn2
>>>> This will imply that those tuples have a different CTID, so a primary
>>>> key would be necessary as I think that this is possible.
>>>>
>>>
>>>  If the patch does not handle the case of replicated table without
>>> unique key, I think we should have a common solution which takes care of
>>> this case also. Or else, if this solution can be extended to handle
>>> no-unique-key case, then that would be good. But I think we would end up in
>>> having two different implementations, one for unique-key method, and
>>> another for the other method, which does not seem good.
>>>
>>>  The method I had in mind was :
>>> In the scan plan, fetch ctid, node_id from all the datanodes.  Use
>>> UPDATE where ctd = ? , but use nodeid-based method to  generate the
>>> ExecNodes at execute-time  (enhance ExecNodes->en_expr evaluation so as to
>>> use the nodeid from source plan, as against the distribution column that it
>>> currently uses for distributed tables) .
>>>
>>>
>> Would that work in all cases? What if 2 tuples on each node fulfill the
>> criteria and a sequence is value being assigned? Might the tuples be
>> processed in a different order on each node the data ends up being
>> inconsistent  (tuple A gets the value 101, B gets the value 102 on node 1,
>> and B gets 101, A gets 102 on node 2).  I am not sure it is worth trying to
>> handle the case, and just require a primary key or unique index.
>>
> Yes, the tuples need to be fetched in the same order. May be using order
> by 1, 2, 3, ... . (I hope that if one column is found to be having unique
> values for a set of rows, sorting is not attempted again for the same set
> of rows using the remaining columns).
>
> Another approach can be considered where we would always have some kind of
> a hidden (or system) column for replicated tables Its type can be serial
> type, or an int column with default nextval('sequence_type') so that it
> will always be executed on coordinator. And use this one as against the
> primary key. Or may be create table with oids. But not sure if OIDs get
> incremented/wrapped around exactly the same way regardless of anything.
> Also, they are documented as having deprecated.
>
> These are just some thoughts for a long term solution. I myself don't have
> the bandwidth to work on XC at this moment, so won't be able to review the
> patch, so I don't want to fall into a situation where the patch is reworked
> and I won't review it after all. But these are just points to be thought of
> in case the would-be reviewer or the submitter feels like
> extending/modifying this patch for a long term solution taking care of
> tables without primary key.
>
>
>>
>>>  But this method will not work as-is in case of non-shippable row
>>> triggers. Because trigger needs to be fired only once per row, and we are
>>> going to execute UPDATE for all of the ctids of a given row corresponding
>>> to all of the datanodes. So somehow we should fire triggers only once. This
>>> method will also hit performance, because currently we fetch *all* columns
>>> and not just ctid, so it's better to first do that optimization of fetching
>>> only reqd columns (there's one pending patch submitted in the mailing list,
>>> which fixes this).
>>>
>>>  This is just one approach, there might be better approaches..
>>>
>>>  Overall, I think if we decide to get this issue solved (and I think we
>>> should really, this is a serious issue), sufficient resource time needs to
>>> be given to think over and have discussions before we finalize the approach.
>>>
>>>
>>>  --
>>>> Michael
>>>>
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> November Webinars for C, C++, Fortran Developers
>>> Accelerate application performance with scalable programming models.
>>> Explore
>>> techniques for threading, error checking, porting, and tuning. Get the
>>> most
>>> from the latest Intel processors and coprocessors. See abstracts and
>>> register
>>>
>>> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk_______________________________________________
>>> Postgres-xc-developers mailing list
>>> Pos...@li...
>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>
>>>
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> November Webinars for C, C++, Fortran Developers
>>> Accelerate application performance with scalable programming models.
>>> Explore
>>> techniques for threading, error checking, porting, and tuning. Get the
>>> most
>>> from the latest Intel processors and coprocessors. See abstracts and
>>> register
>>>
>>> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
>>> _______________________________________________
>>> Postgres-xc-developers mailing list
>>> Pos...@li...
>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>
>>>
>>
>>
>> --
>> Mason Sharp
>>
>> TransLattice - http://www.translattice.com
>> Distributed and Clustered Database Solutions
>>
>>
>>
>
>
> ------------------------------------------------------------------------------
> November Webinars for C, C++, Fortran Developers
> Accelerate application performance with scalable programming models.
> Explore
> techniques for threading, error checking, porting, and tuning. Get the most
> from the latest Intel processors and coprocessors. See abstracts and
> register
> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
>


-- 
-- 
*Abbas*
 Architect

Ph: 92.334.5100153
 Skype ID: gabbasb
www.enterprisedb.co
<http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars,
whitepapers<http://www.enterprisedb.com/resources-community>and
more<http://www.enterprisedb.com/resources-community>

[Postgres-xc-developers] Postgres-XC 1.2 beta is out

[Koichi S. <koi...@gm...>]

Postgres-XC 1.1 beta is now out.   Thank you very much for waiting long.

This release includes many major new features from PostgreSQL 9.3.
Also this release changes its release schedule from June - August to
January - February to make it easier to catch up PostgreSQL
development.

Source tarball is available at
https://sourceforge.net/projects/postgres-xc/files/Version_1.2/

Document page is at http://postgres-xc.sourceforge.net/docs/1_2_beta/

Release note is at
http://postgres-xc.sourceforge.net/docs/1_2_beta/release-xc-1-2.html

This patch enables all the major PostgreSQL new features as follows:

1) Materialized view, by Ashutosh Bapat
2) Event trigger, by Koichi Suzuki
3) Automatic updatable views, by Abbas But
4) LATERAL, by Ashutosh Bapat

Due to many internal API changes of PosgreSQL planner, corresponding
Postgres-XC planner needed many changes too.

This effort inlcudes the following by Abbas But:

1) Fix false report of partition column updation.
2) Fix for alias problem in returning list of INSERT statements.
3) Fix a problem in RETURNING support when all the items of the list
are not shippable.
4) Change the way UPDATEs are handeled, by updating all the columns of
the result relation instead of updating only the columns that were in
the target list.
5) Fix for all problems emerging because the query deparsing system
now generates unique rtable names by appending digits Initdb message
improvement was done by Masataka Saito.

Postgres-XC development group appreciates for all the inputs and helps
from PGXC'ers.

Enjoy.
----------
Koichi Suzuki

---
Koichi Suzuki

Re: [Postgres-xc-developers] [Postgres-xc-committers] Postgres-XC branch, REL1_2_STABLE, deleted. XC1_0_BETA1_PG9_1-3983-g544dee1

[Koichi S. <koi...@gm...>]

Yes, I made a mistake to begin this branch from wrong commit point.
Correction has been pushed with beta tag.

Sorry for the inconvenience.
---
Koichi Suzuki


2014-02-11 11:37 GMT+09:00 Michael Paquier <mic...@gm...>:
> On Tue, Feb 11, 2014 at 11:35 AM, Koichi Suzuki
> <koi...@us...> wrote:
>> Project "Postgres-XC".
>>
>> The branch, REL1_2_STABLE has been deleted
>>        was  544dee10e2d9538be67677b8abbe728edd76b2ff
>>
>> -----------------------------------------------------------------------
>> 544dee10e2d9538be67677b8abbe728edd76b2ff This is second REL1_2_STABLE commit which is successful in the binary build. I commit this to make smaller step visible to public.
>> -----------------------------------------------------------------------
> Isn't it a problem to remove this branch?
> --
> Michael
>
> ------------------------------------------------------------------------------
> Android apps run on BlackBerry 10
> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
> Now with support for Jelly Bean, Bluetooth, Mapview and more.
> Get your Android app in front of a whole new audience.  Start now.
> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers

Re: [Postgres-xc-developers] [Postgres-xc-committers] Postgres-XC branch, REL1_2_STABLE, deleted. XC1_0_BETA1_PG9_1-3983-g544dee1

[Michael P. <mic...@gm...>]

On Tue, Feb 11, 2014 at 11:35 AM, Koichi Suzuki
<koi...@us...> wrote:
> Project "Postgres-XC".
>
> The branch, REL1_2_STABLE has been deleted
>        was  544dee10e2d9538be67677b8abbe728edd76b2ff
>
> -----------------------------------------------------------------------
> 544dee10e2d9538be67677b8abbe728edd76b2ff This is second REL1_2_STABLE commit which is successful in the binary build. I commit this to make smaller step visible to public.
> -----------------------------------------------------------------------
Isn't it a problem to remove this branch?
-- 
Michael

Re: [Postgres-xc-developers] What is best choice to set up a PGXC HA environment?

[Koichi S. <koi...@gm...>]

I agree that XC should have its own monitoring and failover feature.

Now pgxc_ctl provides monitoring and failover feature of GTM,
coordinator and datanode.    Basic missing features are, among others,
as follows:

1) Automatic failover,
2) Agent to allow more than one pgxc_ctl instance to share the cluster status.

We can extend pgxc_ctl with these features as a part of release 1.3.
Pgxc_ctl may need additional interface to accept reports from external
monitoring and failover tools such as Pacemaker/Corosync to detect
hardware and network failure as well as other general failure and
their failover.

In such a way, I'd like to make XC HA-ready and simpler to integrate
with other failover tools.

Thoughts?
---
Koichi Suzuki


2014-02-08 11:13 GMT+09:00 ZhangJulian <jul...@ou...>:
> Hi Mason,
>
> Yes, if I can help I am honored to join such a development work.
>
> I was trying to build a HA environment for PG, and then PGXC, but still
> haven't achieve it by far. My OS is RHEL 6.4, do you have some documents or
> web links sharing with me?
>
> I have another idea which may not be a good one, but still would like to
> post here and have your advice. For the HA component, it looks like donot
> need to be installed on all the machines. We can develop a seperated module
> just like GTM, I call it XCMon temporarily which is suggested to be
> installed on the server of GTM and GTM slave.
> Machine1 (GTM, XCMon)  ---> Machine2(GTM slave, XCMon slave)
> XCMon will monitor all the components by sending SELECT 1+1 to all
> Coordinators and all data nodes, and trying to get snapshot from GTM and all
> GTM proxies periodically. XCMon slave will detect XCMon master and prepare
> to take the control once XCMon master failed. Further, we can even combine
> XCMon functions into GTM and maker the deployment more eaiser.
>
> Thanks
> Julian
>
>
> ________________________________
> Date: Fri, 7 Feb 2014 10:49:23 -0500
> Subject: Re: [Postgres-xc-developers] What is best choice to set up a PGXC
> HA environment?
> From: ms...@tr...
> To: jul...@ou...
> CC: his...@la...; koi...@gm...;
> pos...@li...
>
>
>
>
>
> On Fri, Feb 7, 2014 at 2:39 AM, ZhangJulian <jul...@ou...>
> wrote:
>
> Hi Hisada,
>
> It is great to know the resource agent will be released within a few
> monthes. Thank your for your work and I am glad to be one of your first
> batch of users.
>
> About the feature of PGXC internal HA, I just think it is a attractive
> feature from a user's perspective, you had mentioned it has some advantanges
> such as no need to install other external tools.  Just now, I read the Admin
> Guide of Greenplum, it seems that GP has the internal HA support by a
> process named ftsprobe.
>
> I was thinking each Coordinator will fork one more process at the starting
> time along with the autovacuum/bgwriter processes, and the new process will
> do all the work as the Pacemaker does.
>
> When the GTM is down, each Coordinator will recognize it when it fetches the
> snapshots from GTM, then it will talk with other Coordinators and negociate
> to restart the GTM master or promote the GTM slave to master. But I am not
> sure how to send the RESTART GTM or PROMOTE GTM SLAVE command from a
> Coordinator process. Maybe the PROMOTE command can be replaced by a API
> invocation to the GTM component.
>
> When one coordinator is down,  when the other coordinators execute a DDL (or
> each coordinator could send SELECT 1+1 to other coordinators periodically to
> verify it they are all alive), they will find the failed coordinator, then
> the alived coordinators can decide to remove the failed coordinator from the
> pgxc_nodes.
>
> When one datanode is down, the coordinator will know it when it sends the
> REMOTE QUEYR to data node, or it can also send the SELECT 1+1 to each
> datanodes periodically. Then all the coordinator will negociate to promote
> the DataNode slave to master.
>
>
> But maybe it is not a better solution if the Pacemaker is easier to use? for
> example, we can develop a PGXC-Pacemaker Glue layer which can fetch all the
> cluster configuration from PGXC and then configure Pacemaker
> automatically....
>
>
>
> These are all good thoughts and somewhat along the lines of what I have been
> thinking as well.
>
> We have been using Corosync/Pacemaker for quite some time. It works, but in
> hindsight I wish we would have put effort into an internal solution. While
> the current solution works, we have spent a lot of time tweaking and
> maintaining. In the past we have had seen aggressive failovers
> unnecessarily, for example. Also, it takes some resources and it does not
> like to manage too many components at once. In our case, we like to have two
> replicas of each data node on the other servers that have masters. Making
> node membership more flexible and getting components to agree when to
> failover is likely better long term solution. There would be more upfront
> effort, but easier installation and less management and maintenance long
> term.  Let me know if you have the time to collaborate on such a development
> effort if we undertake this at some point.
>
> Our other product, TED (unrelated to Postgres-XC), manages failover
> internally and works well, including automatic recovery of downed nodes. We
> can perhaps draw on lessons there, too.
>
> --
> Mason Sharp
>
> TransLattice - http://www.translattice.com
> Distributed and Clustered Database Solutions
>
>

Re: [Postgres-xc-developers] What is best choice to set up a PGXC HA environment?

[ZhangJulian <jul...@ou...>]

Hi Mason,

Yes, if I can help I am honored to join such a development work. 

I was trying to build a HA environment for PG, and then PGXC, but still haven't achieve it by far. My OS is RHEL 6.4, do you have some documents or web links sharing with me? 

I have another idea which may not be a good one, but still would like to post here and have your advice. For the HA component, it looks like donot need to be installed on all the machines. We can develop a seperated module just like GTM, I call it XCMon temporarily which is suggested to be installed on the server of GTM and GTM slave. 
Machine1 (GTM, XCMon)  ---> Machine2(GTM slave, XCMon slave)
XCMon will monitor all the components by sending SELECT 1+1 to all Coordinators and all data nodes, and trying to get snapshot from GTM and all GTM proxies periodically. XCMon slave will detect XCMon master and prepare to take the control once XCMon master failed. Further, we can even combine XCMon functions into GTM and maker the deployment more eaiser. 

Thanks
Julian


Date: Fri, 7 Feb 2014 10:49:23 -0500
Subject: Re: [Postgres-xc-developers] What is best choice to set up a PGXC HA environment?
From: ms...@tr...
To: jul...@ou...
CC: his...@la...; koi...@gm...; pos...@li...




On Fri, Feb 7, 2014 at 2:39 AM, ZhangJulian <jul...@ou...> wrote:




Hi Hisada,

It is great to know the resource agent will be released within a few monthes. Thank your for your work and I am glad to be one of your first batch of users. 

About the feature of PGXC internal HA, I just think it is a attractive feature from a user's perspective, you had mentioned it has some advantanges such as no need to install other external tools.  Just now, I read the Admin Guide of Greenplum, it seems that GP has the internal HA support by a process named ftsprobe. 


I was thinking each Coordinator will fork one more process at the starting time along with the autovacuum/bgwriter processes, and the new process will do all the work as the Pacemaker does. 

When the GTM is down, each Coordinator will recognize it when it fetches the snapshots from GTM, then it will talk with other Coordinators and negociate to restart the GTM master or promote the GTM slave to master. But I am not sure how to send the RESTART GTM or PROMOTE GTM SLAVE command from a Coordinator process. Maybe the PROMOTE command can be replaced by a API invocation to the GTM component.


When one coordinator is down,  when the other coordinators execute a DDL (or each coordinator could send SELECT 1+1 to other coordinators periodically to verify it they are all alive), they will find the failed coordinator, then the alived coordinators can decide to remove the failed coordinator from the pgxc_nodes.


When one datanode is down, the coordinator will know it when it sends the REMOTE QUEYR to data node, or it can also send the SELECT 1+1 to each datanodes periodically. Then all the coordinator will negociate to promote the DataNode slave to master. 



But maybe it is not a better solution if the Pacemaker is easier to use? for example, we can develop a PGXC-Pacemaker Glue layer which can fetch all the cluster configuration from PGXC and then configure Pacemaker automatically....



These are all good thoughts and somewhat along the lines of what I have been thinking as well.  
We have been using Corosync/Pacemaker for quite some time. It works, but in hindsight I wish we would have put effort into an internal solution. While the current solution works, we have spent a lot of time tweaking and maintaining. In the past we have had seen aggressive failovers unnecessarily, for example. Also, it takes some resources and it does not like to manage too many components at once. In our case, we like to have two replicas of each data node on the other servers that have masters. Making node membership more flexible and getting components to agree when to failover is likely better long term solution. There would be more upfront effort, but easier installation and less management and maintenance long term.  Let me know if you have the time to collaborate on such a development effort if we undertake this at some point.

Our other product, TED (unrelated to Postgres-XC), manages failover internally and works well, including automatic recovery of downed nodes. We can perhaps draw on lessons there, too.

-- 
Mason Sharp
TransLattice - http://www.translattice.com
Distributed and Clustered Database Solutions


 		 	   		  

Re: [Postgres-xc-developers] What is best choice to set up a PGXC HA environment?

[Mason S. <ms...@tr...>]

On Fri, Feb 7, 2014 at 2:39 AM, ZhangJulian <jul...@ou...>wrote:

> Hi Hisada,
>
> It is great to know the resource agent will be released within a few
> monthes. Thank your for your work and I am glad to be one of your first
> batch of users.
>
> About the feature of PGXC internal HA, I just think it is a attractive
> feature from a user's perspective, you had mentioned it has some
> advantanges such as no need to install other external tools.  Just now, I
> read the Admin Guide of Greenplum, it seems that GP has the internal HA
> support by a process named *ftsprobe*.
>
> I was thinking each Coordinator will fork one more process at the starting
> time along with the autovacuum/bgwriter processes, and the new process will
> do all the work as the Pacemaker does.
>
> When the GTM is down, each Coordinator will recognize it when it fetches
> the snapshots from GTM, then it will talk with other Coordinators and
> negociate to restart the GTM master or promote the GTM slave to master. But
> I am not sure how to send the RESTART GTM or PROMOTE GTM SLAVE command from
> a Coordinator process. Maybe the PROMOTE command can be replaced by a API
> invocation to the GTM component.
>
> When one coordinator is down,  when the other coordinators execute a DDL
> (or each coordinator could send SELECT 1+1 to other coordinators
> periodically to verify it they are all alive), they will find the failed
> coordinator, then the alived coordinators can decide to remove the failed
> coordinator from the pgxc_nodes.
>
> When one datanode is down, the coordinator will know it when it sends the
> REMOTE QUEYR to data node, or it can also send the SELECT 1+1 to each
> datanodes periodically. Then all the coordinator will negociate to promote
> the DataNode slave to master.
>
>
> But maybe it is not a better solution if the Pacemaker is easier to use?
> for example, we can develop a PGXC-Pacemaker Glue layer which can fetch all
> the cluster configuration from PGXC and then configure Pacemaker
> automatically....
>


These are all good thoughts and somewhat along the lines of what I have
been thinking as well.

We have been using Corosync/Pacemaker for quite some time. It works, but in
hindsight I wish we would have put effort into an internal solution. While
the current solution works, we have spent a lot of time tweaking and
maintaining. In the past we have had seen aggressive failovers
unnecessarily, for example. Also, it takes some resources and it does not
like to manage too many components at once. In our case, we like to have
two replicas of each data node on the other servers that have masters.
Making node membership more flexible and getting components to agree when
to failover is likely better long term solution. There would be more
upfront effort, but easier installation and less management and maintenance
long term.  Let me know if you have the time to collaborate on such a
development effort if we undertake this at some point.

Our other product, TED (unrelated to Postgres-XC), manages failover
internally and works well, including automatic recovery of downed nodes. We
can perhaps draw on lessons there, too.

-- 
Mason Sharp

TransLattice - http://www.translattice.com
Distributed and Clustered Database Solutions

Re: [Postgres-xc-developers] What is best choice to set up a PGXC HA environment?

[ZhangJulian <jul...@ou...>]

Hi Hisada,

It is great to know the resource agent will be released within a few monthes. Thank your for your work and I am glad to be one of your first batch of users. 

About the feature of PGXC internal HA, I just think it is a attractive feature from a user's perspective, you had mentioned it has some advantanges such as no need to install other external tools.  Just now, I read the Admin Guide of Greenplum, it seems that GP has the internal HA support by a process named ftsprobe. 

I was thinking each Coordinator will fork one more process at the starting time along with the autovacuum/bgwriter processes, and the new process will do all the work as the Pacemaker does. 

When the GTM is down, each Coordinator will recognize it when it fetches the snapshots from GTM, then it will talk with other Coordinators and negociate to restart the GTM master or promote the GTM slave to master. But I am not sure how to send the RESTART GTM or PROMOTE GTM SLAVE command from a Coordinator process. Maybe the PROMOTE command can be replaced by a API invocation to the GTM component.

When one coordinator is down,  when the other coordinators execute a DDL (or each coordinator could send SELECT 1+1 to other coordinators periodically to verify it they are all alive), they will find the failed coordinator, then the alived coordinators can decide to remove the failed coordinator from the pgxc_nodes.

When one datanode is down, the coordinator will know it when it sends the REMOTE QUEYR to data node, or it can also send the SELECT 1+1 to each datanodes periodically. Then all the coordinator will negociate to promote the DataNode slave to master. 


But maybe it is not a better solution if the Pacemaker is easier to use? for example, we can develop a PGXC-Pacemaker Glue layer which can fetch all the cluster configuration from PGXC and then configure Pacemaker automatically....

Thanks
Julian

> From: his...@la...
> To: koi...@gm...; jul...@ou...
> CC: pos...@li...
> Subject: RE: [Postgres-xc-developers] What is best choice to set up a PGXC	HA environment?
> Date: Tue, 4 Feb 2014 11:23:05 +0900
> 
> Hi, Julian, 
> 
> > > I am thinking 3 choices as below:
> > >
> > > 1. Pacemaker and Corosync.
> > > I have little experience on Linux HA, so one week passed, I even can
> > > not install them successfully, including
> Pacemaker/Corosync/crmsh/resouce
> > agent.
> > > There are some website mentioned Pacemaker/corosync can help PGXC to
> > > build a HA infrastructure, but I can not find a comprehensive guide to
> > > do it. There are much more commponents in PGXC than PG, I think I
> > > should learn how to build it based on PG first.
> > 
> > I know separate XC project to provide Pacemaker/Corosync resource
> > agent for XC.   Please let me push them to provide info.
> 
> We are planning to release resource agent for pacemaker/heartbeat within a
> few months.
> Basic idea is to manage pairs of Master-Slave for Datanode, Coordinator and
> GTM at each server by pacemaker.
> Hopefully this could be one of the solution to HA feature at XC.
> 
> > > 2. Zookeeper
> > > It seems that Zookeeper has the ability to build a HA solution for
> > > PGXC, which have the similar function with Pacemaker, but I have to
> > > develop the heartbeat function for Zookeeper to
> > > start/stop/monitor/failover PGXC. And I do not know if my understand is
> > right.
> > 
> > Sorry, I'm not familiar with Zookeeper.
> >
> > > 3. PGXC support HA internally.
> > > Because the table of pgxc_nodes in coordinator already have some
> > > information about the cluster, it can be enhanced to save the
> > > Master/Slave relations, it is replicated between all coordinators,
> > > then it can used as a CRM(Cluster Resource Management, as Pacemaker)
> > compoment.
> > > And the coordinator will connect to datanode/gtm/other coordinator in
> > > its regular work, so the heartbeat function exists natually. Even when
> > > the database is in the spare time, the coordinator can send a simple
> > > query as "select 1+1" to datanodes as the heartbeat ticks.
> > > What need to do is that, the coordinator will start a new process when
> > > starting, the new process will act as a heartbeat /resouce_agent to
> > > monitor the cluster status, and restart/failover once one commponent
> fails.
> 
> How about monitoring coordinator and GTM? Do you have any idea?
> 
> > > As my initial understanding, Choice 3 is better than Choice 2 which is
> > > better than Choice 1. But for the development effort, the order is
> > > reversed, Choice 1 is easy achieved based on current existing codes.
> 
> What do we mean by better?
> My requirement is as follows : 
> 
> Availability :
>  - Shorten Failure detection
>  - Shorten downtime at Failover / Switchover
> 
> Node management usability : 
>  - We can manage Slave node as well as Master node into XC Cluster
>  - Enables node monitoring and management at psql
>  - No need to install / configure external tools : pacemaker / colosync 
> 
> What else?
> 
> Regards,
> 
> Hisada
> 
> > > I am very appreciated that you can share your advice with me.
> > 
> > Yes, I do agree with this solution.   I'd like to have this as a part
> > of XC release 1.3.
> 
> 
> 
> > PGXC internal HA should be integrated with other monitoring feature such
> as
> > server hardware, power and network.
> > 
> > It will be exciting to begin this discussion in this mailing list.
> > 
> > Regards;
> > ---
> > Koichi Suzuki
> > 
> > >
> > > Thanks
> > > Julian
> > >
> > >
> > >
> > > ----------------------------------------------------------------------
> > > -------- CenturyLink Cloud: The Leader in Enterprise Cloud Services.
> > > Learn Why More Businesses Are Choosing CenturyLink Cloud For Critical
> > > Workloads, Development Environments & Everything In Between.
> > > Get a Quote or Start a Free Trial Today.
> > > http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.
> > > clktrk _______________________________________________
> > > Postgres-xc-developers mailing list
> > > Pos...@li...
> > > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
> > >
> > 
> > -------------------------------------------------------------------------
> > -----
> > WatchGuard Dimension instantly turns raw network data into actionable
> security
> > intelligence. It gives you real-time visual feedback on key security
> issues
> > and trends.  Skip the complicated setup - simply import a virtual
> appliance
> > and go from zero to informed in seconds.
> > http://pubads.g.doubleclick.net/gampad/clk?id=123612991&iu=/4140/ostg.clk
> > trk
> > _______________________________________________
> > Postgres-xc-developers mailing list
> > Pos...@li...
> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
> 
 		 	   		  

Re: [Postgres-xc-developers] Detecting XC

[David E. W. <da...@ju...>]

On Feb 5, 2014, at 5:33 PM, Tatsuo Ishii <is...@po...> wrote:

> What about this?
> 
> SELECT count(*) from
> 	   (SELECT has_function_privilege('%s', 'pgxc_version(text)', 'execute')
> 	   WHERE EXISTS(SELECT * FROM pg_catalog.pg_proc AS p WHERE p.proname = 'pgxc_version')) AS s"

I don’t much care about permission, so I would just do

    SELECT TRUE
      FROM pg_catalog.pg_proc p
      JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid
     WHERE nspname = 'pg_catalog'
       AND proname = 'pgxc_version';

Best,

David

Re: [Postgres-xc-developers] ERROR: unexpected varno 6 in JOIN RTE 5

[Ahsan H. <ahs...@en...>]

Abbas,

Can you test this with 1.1? Perhaps we fixed this issue in the master which
means that it will be part of 1.2 beta that is planned for this month.

-- Ahsan


On Wed, Feb 5, 2014 at 10:03 PM, David E. Wheeler <da...@ju...>wrote:

> On Feb 4, 2014, at 10:18 PM, Abbas Butt <abb...@en...>
> wrote:
>
> > I had to change the insert query a little bit and had to do some dummy
> inserts in projects and changes too, but the query worked fine for me. BTW
> I tried on current master, I am not sure which version you are using to
> test.
>
> The 1.1 release.
>
> > Here are the queries I tried.
> >
> > After creating the three tables, I did
> >
> > insert into projects values('?', '?', DEFAULT, '?', '?');
> >
> > insert into changes values('?', '?', '?', '?', DEFAULT,  '?', '?',
> clock_timestamp(), '?', '?');
> >
> > INSERT INTO tags (tag_id, tag, project, change_id, note, committer_name,
> committer_email, planned_at, planner_name, planner_email)
> > SELECT tid, tg, proj, chid, n, name, email, at, pname, pemail FROM (
> VALUES ('?', '?', '?', '?', '?', '?', '?', clock_timestamp()::timestamptz,
> '?', '?')) i(tid, tg, proj, chid, n, name, email, at, pname, pemail) LEFT
> JOIN tags ON i.tid = tags.tag_id WHERE tags.tag_id IS NULL;
>
> On 1.1:
>
> dwheeler=# insert into sqitch.projects values('?', '?', DEFAULT, '?', '?');
> INSERT 0 1
> Time: 48.720 ms
> dwheeler=# insert into sqitch.changes values('?', '?', '?', '?', DEFAULT,
>  '?', '?', clock_timestamp(), '?', '?');
> INSERT 0 1
> Time: 43.704 ms
> dwheeler=# INSERT INTO sqitch.tags (tag_id, tag, project, change_id, note,
> committer_name, committer_email, planned_at, planner_name, planner_email)
> dwheeler=# INSERT INTO sqitch.tags (tag_id, tag, project, change_id, note,
> committer_name, committer_email, planned_at, planner_name, planner_email)
> dwheeler-# SELECT tid, tg, proj, chid, n, name, email, at, pname, pemail
> FROM ( VALUES ('?', '?', '?', '?', '?', '?', '?',
> clock_timestamp()::timestamptz, '?', '?')) i(tid, tg, proj, chid, n, name,
> email, at, pname, pemail) LEFT JOIN sqitch.tags ON i.tid = tags.tag_id
> WHERE tags.tag_id IS NULL;
> ERROR:  unexpected varno 6 in JOIN RTE 5
> Time: 4.064 ms
>
> Best,
>
> David
>
>
>
> ------------------------------------------------------------------------------
> Managing the Performance of Cloud-Based Applications
> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
> Read the Whitepaper.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=121051231&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>



-- 
Ahsan Hadi
Snr Director Product Development
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: +92-51-8358874
Mobile: +92-333-5162114

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.

Re: [Postgres-xc-developers] Detecting XC

[Tatsuo I. <is...@po...>]

> PGXC Hackers,
> 
> What is the simplest way to tell if the server one has connected to is XC? Try to call pgxc_version()? `SHOW gtm_host`? Or is there something else to check, maybe something that doesn't throw an exception?

What about this?

SELECT count(*) from
	   (SELECT has_function_privilege('%s', 'pgxc_version(text)', 'execute')
	   WHERE EXISTS(SELECT * FROM pg_catalog.pg_proc AS p WHERE p.proname = 'pgxc_version')) AS s"

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

Re: [Postgres-xc-developers] Detecting XC

[David E. W. <da...@ju...>]

On Feb 4, 2014, at 5:36 PM, Koichi Suzuki <koi...@gm...> wrote:

> Because XC didn't change libpq at all, I cannot find explicit way if
> you're connecting to PG or XC.   Even psql binary from PG works with
> XC.   I'd suggest to test if pgxc_class, pgxc_node and pgxc_nodegroup
> catalog is available, which are all XC specifyc.   To avoid name
> conflict (you can create table pgxc_class in PG), you can specify
> pgxc_class oid value, which will be release-specific though.

I don’t think I need to be *that* anal. If someone creates a table named pgxc_class in pg_catalog then the problem is theirs, IMHO.

Thanks,

David

Re: [Postgres-xc-developers] Detecting XC

[David E. W. <da...@ju...>]

On Feb 4, 2014, at 5:56 PM, Michael Paquier <mic...@gm...> wrote:

> How do you actually do for PG itself? Do you use PG_VERSION_NUM or similar?

I don’t. Previously I did not have to distinguish Postgres from anything else.

David

Re: [Postgres-xc-developers] ERROR: unexpected varno 6 in JOIN RTE 5

[David E. W. <da...@ju...>]

On Feb 4, 2014, at 10:18 PM, Abbas Butt <abb...@en...> wrote:

> I had to change the insert query a little bit and had to do some dummy inserts in projects and changes too, but the query worked fine for me. BTW I tried on current master, I am not sure which version you are using to test.

The 1.1 release.

> Here are the queries I tried.
> 
> After creating the three tables, I did
> 
> insert into projects values('?', '?', DEFAULT, '?', '?');
> 
> insert into changes values('?', '?', '?', '?', DEFAULT,  '?', '?', clock_timestamp(), '?', '?');
> 
> INSERT INTO tags (tag_id, tag, project, change_id, note, committer_name, committer_email, planned_at, planner_name, planner_email)
> SELECT tid, tg, proj, chid, n, name, email, at, pname, pemail FROM ( VALUES ('?', '?', '?', '?', '?', '?', '?', clock_timestamp()::timestamptz, '?', '?')) i(tid, tg, proj, chid, n, name, email, at, pname, pemail) LEFT JOIN tags ON i.tid = tags.tag_id WHERE tags.tag_id IS NULL;

On 1.1:

dwheeler=# insert into sqitch.projects values('?', '?', DEFAULT, '?', '?');
INSERT 0 1
Time: 48.720 ms
dwheeler=# insert into sqitch.changes values('?', '?', '?', '?', DEFAULT,  '?', '?', clock_timestamp(), '?', '?');
INSERT 0 1
Time: 43.704 ms
dwheeler=# INSERT INTO sqitch.tags (tag_id, tag, project, change_id, note, committer_name, committer_email, planned_at, planner_name, planner_email)
dwheeler=# INSERT INTO sqitch.tags (tag_id, tag, project, change_id, note, committer_name, committer_email, planned_at, planner_name, planner_email)
dwheeler-# SELECT tid, tg, proj, chid, n, name, email, at, pname, pemail FROM ( VALUES ('?', '?', '?', '?', '?', '?', '?', clock_timestamp()::timestamptz, '?', '?')) i(tid, tg, proj, chid, n, name, email, at, pname, pemail) LEFT JOIN sqitch.tags ON i.tid = tags.tag_id WHERE tags.tag_id IS NULL;
ERROR:  unexpected varno 6 in JOIN RTE 5
Time: 4.064 ms

Best,

David