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

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

[Amit K. <ami...@en...>]

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
>
>
>

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

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

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.



> 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

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

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

This could be an option if such incompatibility is acceptable.    I
understand many such replication system assumes primary keys in all the
tables so the restriction itself is acceptable.

Unique key cannot handle NULL valued key correctly so primary or unique not
null (equivalent to primary key) constraint will be better.

Regards;

---
Koichi Suzuki


2013/11/7 Nikhil Sontakke <ni...@st...>

> Isn't it fair to error out if there are no primary/unique keys on the
> replicated table? IMO, Mason's approach handles things pretty ok for tables
> with unique type of keys.
>
> Regards,
> Nikhils
>
>
> On Thu, Nov 7, 2013 at 11:15 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?
>> ---
>> 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) .
>> 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
>>
>>
>
>
> --
> StormDB - http://www.stormdb.com
> The Database Cloud
>
>
> ------------------------------------------------------------------------------
> 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
>
>

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

[Nikhil S. <ni...@st...>]

Isn't it fair to error out if there are no primary/unique keys on the
replicated table? IMO, Mason's approach handles things pretty ok for tables
with unique type of keys.

Regards,
Nikhils


On Thu, Nov 7, 2013 at 11:15 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?
> ---
> 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) .
> 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
>
>


-- 
StormDB - http://www.stormdb.com
The Database Cloud

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

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

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?
---
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) .
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

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

[Amit K. <ami...@en...>]

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) .
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
>

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

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

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.
-- 
Michael

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

[Amit K. <ami...@en...>]

On 6 November 2013 09:55, Michael Paquier <mic...@gm...> wrote:

> On Wed, Nov 6, 2013 at 11:00 AM, Koichi Suzuki <koi...@gm...>
> wrote:
> > I'm thinking to correct trigger implementation to use primary key or
> "unique
> > and not null" columns.   As you suggested, this could be a serious
> problem.
> > I found that CTID can be different very easily in replicated tables and
> it
> > is very dangerous to use current CTID to identify tuples. ,
> This looks like an approach close to what is recommended for FDWs in
> the Postgres docs, where a primary key should be used to scan foreign
> tuples, primarily for writable APIs. So yes it might be the way to
> go...
>

I won't have bandwidth to review the patch itself. But just a few questions
:

What exactly does the PostgreSQL FDW doc say about updates and primary key
? How does the postgres_fdw update a table that has no primary or unique
key ?

In the patch, what do we do when the replicated table has no unique/primary
key ?

--
> 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
>

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

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

On Wed, Nov 6, 2013 at 11:00 AM, Koichi Suzuki <koi...@gm...> wrote:
> I'm thinking to correct trigger implementation to use primary key or "unique
> and not null" columns.   As you suggested, this could be a serious problem.
> I found that CTID can be different very easily in replicated tables and it
> is very dangerous to use current CTID to identify tuples. ,
This looks like an approach close to what is recommended for FDWs in
the Postgres docs, where a primary key should be used to scan foreign
tuples, primarily for writable APIs. So yes it might be the way to
go...
-- 
Michael

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

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

I'm thinking to correct trigger implementation to use primary key or
"unique and not null" columns.   As you suggested, this could be a serious
problem.    I found that CTID can be different very easily in replicated
tables and it is very dangerous to use current CTID to identify tuples. ,

CTID of replicated tables can be different very easily as follows:

1. Start at the status where a block B1 of the table S can accommodate only
one more tuple both in the datanode D1 and D2.   D1 is the primary node so
every transaction updating S comes to D1 first.

2. Transaction T1 and T2 are trying to insert new tuple R1 and R2
respectively.   Suppose they are not conflicting.

3. T1 comes first to D1 and inserts R1, which goes to B1.

4. Then T2 comes to D1 and inserts R1, which goes to a new block B2.

5. Coordinator running T1 is very busy so T2 comes first to D2, insert R2,
which goes to B1.

6. Then T1 comes to D2, insert R1, which goes to a new block B2.

So CTID of R1 points to B1 at D1, B2 at D2,  CTID of R2 points to B2 at D1,
B1 at D2.

This can happen very easily but not easy to reproduce systematically.   It
is simple to do similar hypothetical experiment inserting rows even in a
same block.

Any more thoughts?

Regards;

---
Koichi Suzuki


2013/11/6 Mason Sharp <ms...@tr...>

>
>
>
> On Mon, Nov 4, 2013 at 8:17 PM, Koichi Suzuki <koi...@gm...>wrote:
>
>> Mason;
>>
>> Thanks for this big patch.    I understand the intention and would like
>> other members to look at this patch too.
>>
>
> Sure.
>
>
>>
>> I'd like to know your idea if this can be a part of the TRIGGER
>> improvement?
>>
>
> What did you have in mind? Yes, this impacts triggers as well.
>
> Regards,
>
> Mason
>
>
>>
>>
>> 2013/11/2 Mason Sharp <ms...@tr...>
>>
>>>  Please see attached patch that tries to address the issue of XC using
>>> CTID for replicated updates and deletes when it is evaluated at a
>>> coordinator instead of being pushed down.
>>>
>>> The problem here is that CTID could be referring to a different tuple
>>> altogether on a different data node, which is what happened for one of our
>>> Postgres-XC support customers, leading to data issues.
>>>
>>> Instead, the patch looks for a primary key or unique index (with the
>>> primary key preferred) and uses those values instead of CTID.
>>>
>>> The patch could be improved further.  Extra parameters are set even if
>>> not used in the execution of the prepared statement sent down to the data
>>> nodes.
>>>
>>>
>>>
> --
> Mason Sharp
>
> TransLattice - http://www.translattice.com
> Distributed and Clustered Database Solutions
>
>
>

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

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

On Mon, Nov 4, 2013 at 8:17 PM, Koichi Suzuki <koi...@gm...> wrote:

> Mason;
>
> Thanks for this big patch.    I understand the intention and would like
> other members to look at this patch too.
>

Sure.


>
> I'd like to know your idea if this can be a part of the TRIGGER
> improvement?
>

What did you have in mind? Yes, this impacts triggers as well.

Regards,

Mason


>
>
> 2013/11/2 Mason Sharp <ms...@tr...>
>
>> Please see attached patch that tries to address the issue of XC using
>> CTID for replicated updates and deletes when it is evaluated at a
>> coordinator instead of being pushed down.
>>
>> The problem here is that CTID could be referring to a different tuple
>> altogether on a different data node, which is what happened for one of our
>> Postgres-XC support customers, leading to data issues.
>>
>> Instead, the patch looks for a primary key or unique index (with the
>> primary key preferred) and uses those values instead of CTID.
>>
>> The patch could be improved further.  Extra parameters are set even if
>> not used in the execution of the prepared statement sent down to the data
>> nodes.
>>
>>
>>
-- 
Mason Sharp

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

Re: [Postgres-xc-developers] Repairing Corrupt Objects

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

There could be several different cause of the problem.

First of all, to ensure that there's no more outstanding 2PCs, could you visit all the nodes (coordinators and datanodes) directly with psql and see if there's no running 2PCs in any of the nodes?

If you find any, you should rollback all of them using the same technique.

If not, could you restart the whole cluster?    I advise to use -m immediate or -m fast option to stop them with pg_ctl.     Then restart the cluster and see what's going on.

I believe only 2PC-repated resources are carried over when nodes are restarted and all the others are cleared up.

I do hope you a allowed to do so.

Regards;
---
Koichi Suzuki

On 2013/11/05, at 12:15, "West, William" <ww...@uc...<mailto:ww...@uc...>> wrote:

Koichi,

Yes it is related. I used your solution and it did rollback the long running prepared statement. I assumed this might have been causing an unregistered block on the table I was attempting to run queries against or vacuum. Unfortunately though this did not appear to alleviate the situation below. I have a single table affected by this. The other object in the database seem to work as expected. If I try to run a query against this table or vacuum it creates 2 pids like so:

505149   19636  0.0  0.0 192248  6824 ?        Ss   18:26   0:00 postgres: bigonc_prd bigonc 72.220.234.184(49302) VACUUM
505149   19889  0.0  0.0 191872  4992 ?        Ss   19:03   0:00 postgres: bigonc_prd bigonc 127.0.0.1(57250) VACUUM waiting

To me this looks like a process is pawned from the client while another process is started on the server. The client process seems to be causing a blocking like against the server pid. This is the output from PG_LOCKS for the client process:

“virtualxid" "3/324” "3/324” 19636 “ExclusiveLock” t t
“transactionid" “” 63030 "3/324” 19636 “ExclusiveLock” t f

Thanks for your help with the other issue as well,

Bill West

From: 鈴木 幸市 <ko...@in...<mailto:ko...@in...>>
Date: Monday, November 4, 2013 at 6:36 PM
To: William West <ww...@uc...<mailto:ww...@uc...>>
Cc: "pos...@li...<mailto:pos...@li...>" <pos...@li...<mailto:pos...@li...>>, "pos...@li...<mailto:pos...@li...>" <pos...@li...<mailto:pos...@li...>>
Subject: Re: [Postgres-xc-developers] Repairing Corrupt Objects

Is this related to the outstanding 2PC three days ago, which I responded with a solution?   If not, could you identify corrupt database object?

Regards;
---
Koichi Suzuki

On 2013/11/02, at 7:34, "West, William" <ww...@uc...<mailto:ww...@uc...>> wrote:

All,

Sorry for the multiple posting but the first emails seemed to get garbled. Hopefully this will post with the full text. Please ignore the other posts with this topic.

I have a database set up but it seems fairly unstable. I have a number of objects in the running database but I have one object that is corrupt. I know this because doing any query or dml statement on this table stalls the client. I also notice that when I run statements that encounter the invalid object. It spawns 2 process ID like below:

505149   19373  0.0  0.0 191768  5908 ?        Ss   10:30   0:00 postgres:  postgresql bigonc [local] VACUUM
505149   19379  1.2  0.0 191972 24776 ?        Ss   10:30   0:00 postgres: postgresql bigonc 127.0.0.1(46305) VACUUM waiting

These processes never complete and seem like they are locking one another. However there is nothing showing in pg_locks. Does anyone recognize this behavior. Is there anyway to repair or drop a corrupt object in the database?

Thanks,
Bill West
------------------------------------------------------------------------------
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

Re: [Postgres-xc-developers] Repairing Corrupt Objects

[West, W. <ww...@uc...>]

Koichi,

Yes it is related. I used your solution and it did rollback the long running prepared statement. I assumed this might have been causing an unregistered block on the table I was attempting to run queries against or vacuum. Unfortunately though this did not appear to alleviate the situation below. I have a single table affected by this. The other object in the database seem to work as expected. If I try to run a query against this table or vacuum it creates 2 pids like so:

505149   19636  0.0  0.0 192248  6824 ?        Ss   18:26   0:00 postgres: bigonc_prd bigonc 72.220.234.184(49302) VACUUM
505149   19889  0.0  0.0 191872  4992 ?        Ss   19:03   0:00 postgres: bigonc_prd bigonc 127.0.0.1(57250) VACUUM waiting

To me this looks like a process is pawned from the client while another process is started on the server. The client process seems to be causing a blocking like against the server pid. This is the output from PG_LOCKS for the client process:

“virtualxid" "3/324” "3/324” 19636 “ExclusiveLock” t t
“transactionid" “” 63030 "3/324” 19636 “ExclusiveLock” t f

Thanks for your help with the other issue as well,

Bill West

From: 鈴木 幸市 <ko...@in...<mailto:ko...@in...>>
Date: Monday, November 4, 2013 at 6:36 PM
To: William West <ww...@uc...<mailto:ww...@uc...>>
Cc: "pos...@li...<mailto:pos...@li...>" <pos...@li...<mailto:pos...@li...>>, "pos...@li...<mailto:pos...@li...>" <pos...@li...<mailto:pos...@li...>>
Subject: Re: [Postgres-xc-developers] Repairing Corrupt Objects

Is this related to the outstanding 2PC three days ago, which I responded with a solution?   If not, could you identify corrupt database object?

Regards;
---
Koichi Suzuki

On 2013/11/02, at 7:34, "West, William" <ww...@uc...<mailto:ww...@uc...>> wrote:

All,

Sorry for the multiple posting but the first emails seemed to get garbled. Hopefully this will post with the full text. Please ignore the other posts with this topic.

I have a database set up but it seems fairly unstable. I have a number of objects in the running database but I have one object that is corrupt. I know this because doing any query or dml statement on this table stalls the client. I also notice that when I run statements that encounter the invalid object. It spawns 2 process ID like below:

505149   19373  0.0  0.0 191768  5908 ?        Ss   10:30   0:00 postgres:  postgresql bigonc [local] VACUUM
505149   19379  1.2  0.0 191972 24776 ?        Ss   10:30   0:00 postgres: postgresql bigonc 127.0.0.1(46305) VACUUM waiting

These processes never complete and seem like they are locking one another. However there is nothing showing in pg_locks. Does anyone recognize this behavior. Is there anyway to repair or drop a corrupt object in the database?

Thanks,
Bill West
------------------------------------------------------------------------------
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

Re: [Postgres-xc-developers] Repairing Corrupt Objects

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

Is this related to the outstanding 2PC three days ago, which I responded with a solution?   If not, could you identify corrupt database object?

Regards;
---
Koichi Suzuki

On 2013/11/02, at 7:34, "West, William" <ww...@uc...<mailto:ww...@uc...>> wrote:

All,

Sorry for the multiple posting but the first emails seemed to get garbled. Hopefully this will post with the full text. Please ignore the other posts with this topic.

I have a database set up but it seems fairly unstable. I have a number of objects in the running database but I have one object that is corrupt. I know this because doing any query or dml statement on this table stalls the client. I also notice that when I run statements that encounter the invalid object. It spawns 2 process ID like below:

505149   19373  0.0  0.0 191768  5908 ?        Ss   10:30   0:00 postgres:  postgresql bigonc [local] VACUUM
505149   19379  1.2  0.0 191972 24776 ?        Ss   10:30   0:00 postgres: postgresql bigonc 127.0.0.1(46305) VACUUM waiting

These processes never complete and seem like they are locking one another. However there is nothing showing in pg_locks. Does anyone recognize this behavior. Is there anyway to repair or drop a corrupt object in the database?

Thanks,
Bill West
------------------------------------------------------------------------------
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

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

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

Mason;

Thanks for this big patch.    I understand the intention and would like
other members to look at this patch too.

I'd like to know your idea if this can be a part of the TRIGGER improvement?

Regards;

---
Koichi Suzuki


2013/11/2 Mason Sharp <ms...@tr...>

> Please see attached patch that tries to address the issue of XC using CTID
> for replicated updates and deletes when it is evaluated at a coordinator
> instead of being pushed down.
>
> The problem here is that CTID could be referring to a different tuple
> altogether on a different data node, which is what happened for one of our
> Postgres-XC support customers, leading to data issues.
>
> Instead, the patch looks for a primary key or unique index (with the
> primary key preferred) and uses those values instead of CTID.
>
> The patch could be improved further.  Extra parameters are set even if not
> used in the execution of the prepared statement sent down to the data nodes.
>
> Regards,
>
>
> --
> 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
>
>

[Postgres-xc-developers] Repairing Corrupt Objects

[West, W. <ww...@uc...>]

All,

Sorry for the multiple posting but the first emails seemed to get garbled. Hopefully this will post with the full text. Please ignore the other posts with this topic.

I have a database set up but it seems fairly unstable. I have a number of objects in the running database but I have one object that is corrupt. I know this because doing any query or dml statement on this table stalls the client. I also notice that when I run statements that encounter the invalid object. It spawns 2 process ID like below:

505149   19373  0.0  0.0 191768  5908 ?        Ss   10:30   0:00 postgres:  postgresql bigonc [local] VACUUM
505149   19379  1.2  0.0 191972 24776 ?        Ss   10:30   0:00 postgres: postgresql bigonc 127.0.0.1(46305) VACUUM waiting

These processes never complete and seem like they are locking one another. However there is nothing showing in pg_locks. Does anyone recognize this behavior. Is there anyway to repair or drop a corrupt object in the database?

Thanks,
Bill West

Re: [Postgres-xc-developers] Fwd: Re: Query Planning bug ?

[Tomonari K. <kat...@po...>]

Hi Ashutosh,

Here is the patch without the testcase.

Thank you!
----------------
Tomonari Katsumata

(2013/10/30 13:51), Ashutosh Bapat wrote:
 > Hi Tomonari,
 > I am waiting for you to provide me a patch without the testcase.
 >
 >
 > On Wed, Oct 30, 2013 at 6:51 AM, Tomonari Katsumata <
 > kat...@po...> wrote:
 >
 >> Hi,
 >>
 >> Almost one month is passing without any
 >> comments about this thread.
 >>
 >> I need this fix as soon as possible, because
 >> these kinds queries are executed by
 >> pg_statsinfo for Postgres-XC.
 >>
 >> Anybody, please commit it.
 >>
 >> regards,
 >> ------------
 >> Tomonari Katsumata
 >>
 >>
 >> (2013/09/28 10:53), Tomonari Katsumata wrote:
 >>> Hi Ashotosh,
 >>>
 >>>>    I never said "don't use functions". Here's what I mailed
 >>>>
 >>> Oh, Sorry for bothering you by my misunderstanding.
 >>>
 >>>>>        What is better for this testcase?
 >>>>
 >>>>    May be we should just drop the testcase. Anyway, EXEC DIRECT is not
 >>> production feature, but debug feature and whosoever wrote this feature
 >> has
 >>> not added any testcases for it anyway.
 >>>>    If anyone else has objections to dropping the testcase, please
 >> suggest
 >>> Tomonari, a better to write it.
 >>>>
 >>> Past 2 days, any suggestions were not sent to me.
 >>> So it seems no objections to dropping the testcase.
 >>>
 >>> regards,
 >>> ----------------
 >>> NTT Software Corporation
 >>> Tomonari Katsumata
 >>>
 >>>
 >>>
 >>> 2013/9/26 Ashutosh Bapat 
<ashutosh.bapat@enterprisedb.**com<ash...@en...>
 >>>
 >>>
 >>>>
 >>>>
 >>>>
 >>>> On Thu, Sep 26, 2013 at 1:29 PM, Tomonari Katsumata <
 >>>> kat...@po....**jp <kat...@po...>>
 >> wrote:
 >>>>
 >>>>> Hi Ashtosh,
 >>>>>
 >>>>> Thanks for your review!
 >>>>>
 >>>>> Previous patch(revised_fqs_r3.patch in [2013/08/14 19:55])
 >>>>> doesn't use a shell script.
 >>>>> Instead, It used test_execute_direct_all_xc_****nodes() function.
 >>
 >>>>>
 >>>>> I've got a comment "don't use functions" from you,
 >>>>> but I could not come up with good idea for the testcase.
 >>>>> The testcase needs to get all node_names to "EXECUTE DIRECT"
 >>>>> against all nodes.
 >>>>>
 >>>>>
 >>>> I never said "don't use functions". Here's what I mailed
 >>>>
 >>>> "In the test file, isn't there any way, you can add the offending
 >> statent
 >>>> without any wrapper function test_execute_direct_all_xc_
 >>>> nodes()? We should minimize the test-code to use only the minimal 
set of
 >>>> features. Since this statement no more gets into infinite loop, please
 >> do
 >>>> not use the statement timeout."
 >>>>
 >>>>
 >>>>> What is better for this testcase?
 >>>>>
 >>>>>
 >>>> May be we should just drop the testcase. Anyway, EXEC DIRECT is not
 >>>> production feature, but debug feature and whosoever wrote this feature
 >> has
 >>>> not added any testcases for it anyway.
 >>>>
 >>>> If anyone else has objections to dropping the testcase, please suggest
 >>>> Tomonari, a better to write it.
 >>>>
 >>>>
 >>>>> regards,
 >>>>>
 >>>>> ----------------
 >>>>> NTT Software Corporation
 >>>>> Tomonari Katsumata
 >>>>>
 >>>>> (2013/09/26 15:44), Ashutosh Bapat wrote:
 >>>>>> Hi Tomonary,
 >>>>>> The testcase you have written uses a shell script. This is not the
 >>>>> standard
 >>>>>> way to add testcases. Can you please see if the testcase can be 
added
 >>>>>> directly as SQL? Please send a revised patch ASAP, so that it can be
 >>>>>> committed. I would have liked to see more testcases for EXEC DIRECT,
 >> but
 >>>>>> since it's a debug only feature (not for production), it gets lesser
 >>>>>> priority in testing and thus we do not see testcases for it.
 >>>>>>
 >>>>>>
 >>>>>> On Thu, Sep 26, 2013 at 8:26 AM, Tomonari Katsumata <
 >>>>>> kat...@po....****jp <katsumata.tomonari@po.ntts.**
 >> co.jp <kat...@po...>>>
 >>
 >>>>> wrote:
 >>>>>>
 >>>>>>> Hi,
 >>>>>>>
 >>>>>>> Does anyone check these patches?
 >>>>>>> If there are no comments, please commit it.
 >>>>>>>
 >>>>>>> regards,
 >>>>>>>
 >>>>>>> ----------------------
 >>>>>>> NTT Software Corporation
 >>>>>>> Tomonari Katsumata
 >>>>>>>
 >>>>>>> -------- Original Message --------
 >>>>>>> Subject:     Re: [Postgres-xc-developers] Query Planning bug ?
 >>>>>>> Date:     Thu, 5 Sep 2013 21:34:32 +0900
 >>>>>>> From:     Tomonari Katsumata <t.k...@gm...>
 >>>>>>> To:     Tomonari Katsumata <katsumata.tomonari@po.ntts.******co.jp<
 >> http://co.jp>
 >>>>> <katsumata.tomonari@po.**ntts.**co.jp <http://ntts.co.jp> <
 >> katsumata.tomonari@po.ntts.**co.jp <kat...@po...>>>
 >>>>>>>>
 >>>>>>> CC:     Ashutosh Bapat <ashutosh.bapat@enterprisedb.******com<
 >>>>> ashutosh.bapat@**enterprisedb.**com <http://enterprisedb.com> <
 >> ashutosh.bapat@enterprisedb.**com <ash...@en...>>>>,
 >>>>>>> pgxc-hackers mailing list <postgres-xc-developers@lists.******
 >>>>> sourceforge.net<postgres-xc-****dev...@li...urceforge.****net<
 >> 
postgres-xc-developers@**lists.sourceforge.net<pos...@li...>
 >>>
 >>
 >>>>>>
 >>>>>
 >>>>>>>>
 >>>>>>>
 >>>>>>>
 >>>>>>> Hi,
 >>>>>>>
 >>>>>>> I'm sorry for long long leaving this thread alone.
 >>>>>>> I've devided the patch to source part and regression part.
 >>>>>>> (against f8e0f994bff1faefa9b1fc2f6e584a******1d3de70241)
 >>
 >>>>>
 >>>>>>>
 >>>>>>> I did not change any process on source part.
 >>>>>>> (revised_fqs_r4.patch)
 >>>>>>>
 >>>>>>> I added a script into src/test/regress/sql directory for
 >>>>>>> changing regression part.
 >>>>>>> (add_test_xc_misc.patch and execute_direct_regress.sh)
 >>>>>>> When regression test runs, the script is executed instead of
 >>>>>>> user defined function.
 >>>>>>>
 >>>>>>> Please check and commit it.
 >>>>>>>
 >>>>>>> regards,
 >>>>>>> ------------------------
 >>>>>>> NTT Software Corporation
 >>>>>>> Tomonari Katsumata
 >>>>>>>
 >>>>>>>
 >>>>>>> 2013/8/14 Tomonari Katsumata 
<katsumata.tomonari@po.ntts.******co.jp
 >> <http://co.jp>
 >>>>> <katsumata.tomonari@po.**ntts.**co.jp <http://ntts.co.jp> <
 >> katsumata.tomonari@po.ntts.**co.jp <kat...@po...>>>
 >>
 >>>>>
 >>>>>>>>
 >>>>>>>
 >>>>>>>> Hi Ashtosh,
 >>>>>>>>
 >>>>>>>> Thanks for your comment!
 >>>>>>>>
 >>>>>>>>
 >>>>>>>>> The code changes are fine, but, the comment in
 >>>>>>> pgxc_collect_RTE_walker()
 >>>>>>>>> seems to specific. I think it should read, "create a copy of
 >> query's
 >>>>>>>> range
 >>>>>>>>> table, so that it can be linked with other RTEs in the 
collector's
 >>>>>>>> context."
 >>>>>>>>>
 >>>>>>>> I've revised the comment as you suggested.
 >>>>>>>>
 >>>>>>>>
 >>>>>>>>> In the test file, isn't there any way, you can add the offending
 >>>>>>> statent
 >>>>>>>>> without any wrapper function test_execute_direct_all_xc_*******
 >>
 >>>>> *nodes()?
 >>>>>
 >>>>>>> We
 >>>>>>>
 >>>>>>>> should
 >>>>>>>>> minimize the test-code to use only the minimal set of features.
 >> Since
 >>>>>>>> this
 >>>>>>>>> statement no more gets into infinite loop, please do not use the
 >>>>>>>> statement
 >>>>>>>>> timeout.
 >>>>>>>>
 >>>>>>>> I've gotten rid of using statement timeout.
 >>>>>>>> But I couldn't come up any idea to testing more simply,
 >>>>>>>> so the function is remaining as same as before patch.
 >>>>>>>> Any ideas?
 >>>>>>>>
 >>>>>>>> Here is the new patch.
 >>>>>>>> (against dec40008b3d689911566514614c511********1c0a61327d)
 >>
 >>>>>
 >>>>>>>
 >>>>>>>>
 >>>>>>>>
 >>>>>>>> regards,
 >>>>>>>> -------------
 >>>>>>>> NTT Software Corporation
 >>>>>>>> Tomonari Katsumata
 >>>>>>>>
 >>>>>>>>
 >>>>>>>
 >>>>>>>
 >>>>>>>
 >>>>>>>
 >>>>>>> 
------------------------------****----------------------------**--**
 >>
 >>>>> ------------------
 >>>>>>> October Webinars: Code for Performance
 >>>>>>> Free Intel webinars can help you accelerate application 
performance.
 >>>>>>> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the
 >>>>> most
 >>>>>>> from
 >>>>>>> the latest Intel processors and coprocessors. See abstracts and
 >>>>> register >
 >>>>>>> http://pubads.g.doubleclick.****net/gampad/clk?id=60133471&iu=****
 >>>>> /4140/ostg.clktrk<http://**pubads.g.doubleclick.net/**
 >> 
gampad/clk?id=60133471&iu=/**4140/ostg.clktrk<http://pubads.g.doubleclick.net/gampad/clk?id=60133471&iu=/4140/ostg.clktrk>
 >>>
 >>
 >>>>>>> ______________________________****_________________
 >>>>>>> Postgres-xc-developers mailing list
 >>>>>>> Postgres-xc-developers@lists.****sourceforge.net<Postgres-xc-**
 >> 
dev...@li...urceforge.**net<Pos...@li...>
 >>>
 >>>>>>> 
https://lists.sourceforge.net/****lists/listinfo/postgres-xc-****<https://lists.sourceforge.net/**lists/listinfo/postgres-xc-**>
 >>>>> developers<https://lists.**sourceforge.net/lists/**
 >> 
listinfo/postgres-xc-**developers<https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers>
 >>>
 >>
 >>>>>>>
 >>>>>>>
 >>>>>>
 >>>>>>
 >>>>>
 >>>>>
 >>>>>
 >>>>
 >>>>
 >>>> --
 >>>> Best Wishes,
 >>>> Ashutosh Bapat
 >>>> EnterpriseDB Corporation
 >>>> The Postgres Database Company
 >>>>
 >>>>
 >>>> ------------------------------**------------------------------**
 >> ------------------
 >>>> October Webinars: Code for Performance
 >>>> Free Intel webinars can help you accelerate application performance.
 >>>> Explore tips for MPI, OpenMP, advanced profiling, and more. Get 
the most
 >>>> from
 >>>> the latest Intel processors and coprocessors. See abstracts and
 >> register >
 >>>> http://pubads.g.doubleclick.**net/gampad/clk?id=60133471&iu=**
 >> 
/4140/ostg.clktrk<http://pubads.g.doubleclick.net/gampad/clk?id=60133471&iu=/4140/ostg.clktrk>
 >>>> ______________________________**_________________
 >>>> Postgres-xc-developers mailing list
 >>>> 
Postgres-xc-developers@lists.**sourceforge.net<Pos...@li...>
 >>>> 
https://lists.sourceforge.net/**lists/listinfo/postgres-xc-**developers<https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers>
 >>>>
 >>>>
 >>>
 >>
 >>
 >>
 >
 >

Re: [Postgres-xc-developers] Fwd: Re: Query Planning bug ?

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

Hi Tomonari,
I am waiting for you to provide me a patch without the testcase.


On Wed, Oct 30, 2013 at 6:51 AM, Tomonari Katsumata <
kat...@po...> wrote:

> Hi,
>
> Almost one month is passing without any
> comments about this thread.
>
> I need this fix as soon as possible, because
> these kinds queries are executed by
> pg_statsinfo for Postgres-XC.
>
> Anybody, please commit it.
>
> regards,
> ------------
> Tomonari Katsumata
>
>
> (2013/09/28 10:53), Tomonari Katsumata wrote:
> > Hi Ashotosh,
> >
> >>    I never said "don't use functions". Here's what I mailed
> >>
> > Oh, Sorry for bothering you by my misunderstanding.
> >
> >>>        What is better for this testcase?
> >>
> >>    May be we should just drop the testcase. Anyway, EXEC DIRECT is not
> > production feature, but debug feature and whosoever wrote this feature
> has
> > not added any testcases for it anyway.
> >>    If anyone else has objections to dropping the testcase, please
> suggest
> > Tomonari, a better to write it.
> >>
> > Past 2 days, any suggestions were not sent to me.
> > So it seems no objections to dropping the testcase.
> >
> > regards,
> > ----------------
> > NTT Software Corporation
> > Tomonari Katsumata
> >
> >
> >
> > 2013/9/26 Ashutosh Bapat <ashutosh.bapat@enterprisedb.**com<ash...@en...>
> >
> >
> >>
> >>
> >>
> >> On Thu, Sep 26, 2013 at 1:29 PM, Tomonari Katsumata <
> >> kat...@po....**jp <kat...@po...>>
> wrote:
> >>
> >>> Hi Ashtosh,
> >>>
> >>> Thanks for your review!
> >>>
> >>> Previous patch(revised_fqs_r3.patch in [2013/08/14 19:55])
> >>> doesn't use a shell script.
> >>> Instead, It used test_execute_direct_all_xc_****nodes() function.
>
> >>>
> >>> I've got a comment "don't use functions" from you,
> >>> but I could not come up with good idea for the testcase.
> >>> The testcase needs to get all node_names to "EXECUTE DIRECT"
> >>> against all nodes.
> >>>
> >>>
> >> I never said "don't use functions". Here's what I mailed
> >>
> >> "In the test file, isn't there any way, you can add the offending
> statent
> >> without any wrapper function test_execute_direct_all_xc_
> >> nodes()? We should minimize the test-code to use only the minimal set of
> >> features. Since this statement no more gets into infinite loop, please
> do
> >> not use the statement timeout."
> >>
> >>
> >>> What is better for this testcase?
> >>>
> >>>
> >> May be we should just drop the testcase. Anyway, EXEC DIRECT is not
> >> production feature, but debug feature and whosoever wrote this feature
> has
> >> not added any testcases for it anyway.
> >>
> >> If anyone else has objections to dropping the testcase, please suggest
> >> Tomonari, a better to write it.
> >>
> >>
> >>> regards,
> >>>
> >>> ----------------
> >>> NTT Software Corporation
> >>> Tomonari Katsumata
> >>>
> >>> (2013/09/26 15:44), Ashutosh Bapat wrote:
> >>>> Hi Tomonary,
> >>>> The testcase you have written uses a shell script. This is not the
> >>> standard
> >>>> way to add testcases. Can you please see if the testcase can be added
> >>>> directly as SQL? Please send a revised patch ASAP, so that it can be
> >>>> committed. I would have liked to see more testcases for EXEC DIRECT,
> but
> >>>> since it's a debug only feature (not for production), it gets lesser
> >>>> priority in testing and thus we do not see testcases for it.
> >>>>
> >>>>
> >>>> On Thu, Sep 26, 2013 at 8:26 AM, Tomonari Katsumata <
> >>>> kat...@po....****jp <katsumata.tomonari@po.ntts.**
> co.jp <kat...@po...>>>
>
> >>> wrote:
> >>>>
> >>>>> Hi,
> >>>>>
> >>>>> Does anyone check these patches?
> >>>>> If there are no comments, please commit it.
> >>>>>
> >>>>> regards,
> >>>>>
> >>>>> ----------------------
> >>>>> NTT Software Corporation
> >>>>> Tomonari Katsumata
> >>>>>
> >>>>> -------- Original Message --------
> >>>>> Subject:     Re: [Postgres-xc-developers] Query Planning bug ?
> >>>>> Date:     Thu, 5 Sep 2013 21:34:32 +0900
> >>>>> From:     Tomonari Katsumata <t.k...@gm...>
> >>>>> To:     Tomonari Katsumata <katsumata.tomonari@po.ntts.******co.jp<
> http://co.jp>
> >>> <katsumata.tomonari@po.**ntts.**co.jp <http://ntts.co.jp> <
> katsumata.tomonari@po.ntts.**co.jp <kat...@po...>>>
> >>>>>>
> >>>>> CC:     Ashutosh Bapat <ashutosh.bapat@enterprisedb.******com<
> >>> ashutosh.bapat@**enterprisedb.**com <http://enterprisedb.com> <
> ashutosh.bapat@enterprisedb.**com <ash...@en...>>>>,
> >>>>> pgxc-hackers mailing list <postgres-xc-developers@lists.******
> >>> sourceforge.net<postgres-xc-****dev...@li...urceforge.****net<
> postgres-xc-developers@**lists.sourceforge.net<pos...@li...>
> >
>
> >>>>
> >>>
> >>>>>>
> >>>>>
> >>>>>
> >>>>> Hi,
> >>>>>
> >>>>> I'm sorry for long long leaving this thread alone.
> >>>>> I've devided the patch to source part and regression part.
> >>>>> (against f8e0f994bff1faefa9b1fc2f6e584a******1d3de70241)
>
> >>>
> >>>>>
> >>>>> I did not change any process on source part.
> >>>>> (revised_fqs_r4.patch)
> >>>>>
> >>>>> I added a script into src/test/regress/sql directory for
> >>>>> changing regression part.
> >>>>> (add_test_xc_misc.patch and execute_direct_regress.sh)
> >>>>> When regression test runs, the script is executed instead of
> >>>>> user defined function.
> >>>>>
> >>>>> Please check and commit it.
> >>>>>
> >>>>> regards,
> >>>>> ------------------------
> >>>>> NTT Software Corporation
> >>>>> Tomonari Katsumata
> >>>>>
> >>>>>
> >>>>> 2013/8/14 Tomonari Katsumata <katsumata.tomonari@po.ntts.******co.jp
> <http://co.jp>
> >>> <katsumata.tomonari@po.**ntts.**co.jp <http://ntts.co.jp> <
> katsumata.tomonari@po.ntts.**co.jp <kat...@po...>>>
>
> >>>
> >>>>>>
> >>>>>
> >>>>>> Hi Ashtosh,
> >>>>>>
> >>>>>> Thanks for your comment!
> >>>>>>
> >>>>>>
> >>>>>>> The code changes are fine, but, the comment in
> >>>>> pgxc_collect_RTE_walker()
> >>>>>>> seems to specific. I think it should read, "create a copy of
> query's
> >>>>>> range
> >>>>>>> table, so that it can be linked with other RTEs in the collector's
> >>>>>> context."
> >>>>>>>
> >>>>>> I've revised the comment as you suggested.
> >>>>>>
> >>>>>>
> >>>>>>> In the test file, isn't there any way, you can add the offending
> >>>>> statent
> >>>>>>> without any wrapper function test_execute_direct_all_xc_*******
>
> >>> *nodes()?
> >>>
> >>>>> We
> >>>>>
> >>>>>> should
> >>>>>>> minimize the test-code to use only the minimal set of features.
> Since
> >>>>>> this
> >>>>>>> statement no more gets into infinite loop, please do not use the
> >>>>>> statement
> >>>>>>> timeout.
> >>>>>>
> >>>>>> I've gotten rid of using statement timeout.
> >>>>>> But I couldn't come up any idea to testing more simply,
> >>>>>> so the function is remaining as same as before patch.
> >>>>>> Any ideas?
> >>>>>>
> >>>>>> Here is the new patch.
> >>>>>> (against dec40008b3d689911566514614c511********1c0a61327d)
>
> >>>
> >>>>>
> >>>>>>
> >>>>>>
> >>>>>> regards,
> >>>>>> -------------
> >>>>>> NTT Software Corporation
> >>>>>> Tomonari Katsumata
> >>>>>>
> >>>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>> ------------------------------****----------------------------**--**
>
> >>> ------------------
> >>>>> October Webinars: Code for Performance
> >>>>> Free Intel webinars can help you accelerate application performance.
> >>>>> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the
> >>> most
> >>>>> from
> >>>>> the latest Intel processors and coprocessors. See abstracts and
> >>> register >
> >>>>> http://pubads.g.doubleclick.****net/gampad/clk?id=60133471&iu=****
> >>> /4140/ostg.clktrk<http://**pubads.g.doubleclick.net/**
> gampad/clk?id=60133471&iu=/**4140/ostg.clktrk<http://pubads.g.doubleclick.net/gampad/clk?id=60133471&iu=/4140/ostg.clktrk>
> >
>
> >>>>> ______________________________****_________________
> >>>>> Postgres-xc-developers mailing list
> >>>>> Postgres-xc-developers@lists.****sourceforge.net<Postgres-xc-**
> dev...@li...urceforge.**net<Pos...@li...>
> >
> >>>>> https://lists.sourceforge.net/****lists/listinfo/postgres-xc-****<https://lists.sourceforge.net/**lists/listinfo/postgres-xc-**>
> >>> developers<https://lists.**sourceforge.net/lists/**
> listinfo/postgres-xc-**developers<https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers>
> >
>
> >>>>>
> >>>>>
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>
> >>
> >> --
> >> Best Wishes,
> >> Ashutosh Bapat
> >> EnterpriseDB Corporation
> >> The Postgres Database Company
> >>
> >>
> >> ------------------------------**------------------------------**
> ------------------
> >> October Webinars: Code for Performance
> >> Free Intel webinars can help you accelerate application performance.
> >> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most
> >> from
> >> the latest Intel processors and coprocessors. See abstracts and
> register >
> >> http://pubads.g.doubleclick.**net/gampad/clk?id=60133471&iu=**
> /4140/ostg.clktrk<http://pubads.g.doubleclick.net/gampad/clk?id=60133471&iu=/4140/ostg.clktrk>
> >> ______________________________**_________________
> >> Postgres-xc-developers mailing list
> >> Postgres-xc-developers@lists.**sourceforge.net<Pos...@li...>
> >> https://lists.sourceforge.net/**lists/listinfo/postgres-xc-**developers<https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers>
> >>
> >>
> >
>
>
>


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

Re: [Postgres-xc-developers] Fwd: Re: Query Planning bug ?

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

Ashutosh;

I understand that there is no remaining issue in his patch.   If so, I think we should commit it timely.

Best;
---
Koichi Suzuki

On 2013/10/30, at 10:21, Tomonari Katsumata <kat...@po...>
 wrote:

> Hi,
> 
> Almost one month is passing without any
> comments about this thread.
> 
> I need this fix as soon as possible, because
> these kinds queries are executed by
> pg_statsinfo for Postgres-XC.
> 
> Anybody, please commit it.
> 
> regards,
> ------------
> Tomonari Katsumata
> 
> (2013/09/28 10:53), Tomonari Katsumata wrote:
>> Hi Ashotosh,
>> 
>>>   I never said "don't use functions". Here's what I mailed
>>> 
>> Oh, Sorry for bothering you by my misunderstanding.
>> 
>>>>       What is better for this testcase?
>>> 
>>>   May be we should just drop the testcase. Anyway, EXEC DIRECT is not
>> production feature, but debug feature and whosoever wrote this 
> feature has
>> not added any testcases for it anyway.
>>>   If anyone else has objections to dropping the testcase, please 
> suggest
>> Tomonari, a better to write it.
>>> 
>> Past 2 days, any suggestions were not sent to me.
>> So it seems no objections to dropping the testcase.
>> 
>> regards,
>> ----------------
>> NTT Software Corporation
>> Tomonari Katsumata
>> 
>> 
>> 
>> 2013/9/26 Ashutosh Bapat <ash...@en...>
>> 
>>> 
>>> 
>>> 
>>> On Thu, Sep 26, 2013 at 1:29 PM, Tomonari Katsumata <
>>> kat...@po...> wrote:
>>> 
>>>> Hi Ashtosh,
>>>> 
>>>> Thanks for your review!
>>>> 
>>>> Previous patch(revised_fqs_r3.patch in [2013/08/14 19:55])
>>>> doesn't use a shell script.
>>>> Instead, It used test_execute_direct_all_xc_**nodes() function.
>>>> 
>>>> I've got a comment "don't use functions" from you,
>>>> but I could not come up with good idea for the testcase.
>>>> The testcase needs to get all node_names to "EXECUTE DIRECT"
>>>> against all nodes.
>>>> 
>>>> 
>>> I never said "don't use functions". Here's what I mailed
>>> 
>>> "In the test file, isn't there any way, you can add the offending 
> statent
>>> without any wrapper function test_execute_direct_all_xc_
>>> nodes()? We should minimize the test-code to use only the minimal set of
>>> features. Since this statement no more gets into infinite loop, 
> please do
>>> not use the statement timeout."
>>> 
>>> 
>>>> What is better for this testcase?
>>>> 
>>>> 
>>> May be we should just drop the testcase. Anyway, EXEC DIRECT is not
>>> production feature, but debug feature and whosoever wrote this 
> feature has
>>> not added any testcases for it anyway.
>>> 
>>> If anyone else has objections to dropping the testcase, please suggest
>>> Tomonari, a better to write it.
>>> 
>>> 
>>>> regards,
>>>> 
>>>> ----------------
>>>> NTT Software Corporation
>>>> Tomonari Katsumata
>>>> 
>>>> (2013/09/26 15:44), Ashutosh Bapat wrote:
>>>>> Hi Tomonary,
>>>>> The testcase you have written uses a shell script. This is not the
>>>> standard
>>>>> way to add testcases. Can you please see if the testcase can be added
>>>>> directly as SQL? Please send a revised patch ASAP, so that it can be
>>>>> committed. I would have liked to see more testcases for EXEC 
> DIRECT, but
>>>>> since it's a debug only feature (not for production), it gets lesser
>>>>> priority in testing and thus we do not see testcases for it.
>>>>> 
>>>>> 
>>>>> On Thu, Sep 26, 2013 at 8:26 AM, Tomonari Katsumata <
>>>>> kat...@po....**jp <kat...@po...>>
>>>> wrote:
>>>>> 
>>>>>> Hi,
>>>>>> 
>>>>>> Does anyone check these patches?
>>>>>> If there are no comments, please commit it.
>>>>>> 
>>>>>> regards,
>>>>>> 
>>>>>> ----------------------
>>>>>> NTT Software Corporation
>>>>>> Tomonari Katsumata
>>>>>> 
>>>>>> -------- Original Message --------
>>>>>> Subject:     Re: [Postgres-xc-developers] Query Planning bug ?
>>>>>> Date:     Thu, 5 Sep 2013 21:34:32 +0900
>>>>>> From:     Tomonari Katsumata <t.k...@gm...>
>>>>>> To:     Tomonari Katsumata 
> <katsumata.tomonari@po.ntts.****co.jp<http://co.jp>
>>>> <katsumata.tomonari@po.**ntts.co.jp <kat...@po...>>
>>>>>>> 
>>>>>> CC:     Ashutosh Bapat <ashutosh.bapat@enterprisedb.****com<
>>>> ashutosh.bapat@**enterprisedb.com <ash...@en...>>>,
>>>>>> pgxc-hackers mailing list <postgres-xc-developers@lists.****
>>>> 
> sourceforge.net<postgres-xc-**dev...@li...urceforge.**net<pos...@li...>
>>>>> 
>>>> 
>>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> Hi,
>>>>>> 
>>>>>> I'm sorry for long long leaving this thread alone.
>>>>>> I've devided the patch to source part and regression part.
>>>>>> (against f8e0f994bff1faefa9b1fc2f6e584a****1d3de70241)
>>>> 
>>>>>> 
>>>>>> I did not change any process on source part.
>>>>>> (revised_fqs_r4.patch)
>>>>>> 
>>>>>> I added a script into src/test/regress/sql directory for
>>>>>> changing regression part.
>>>>>> (add_test_xc_misc.patch and execute_direct_regress.sh)
>>>>>> When regression test runs, the script is executed instead of
>>>>>> user defined function.
>>>>>> 
>>>>>> Please check and commit it.
>>>>>> 
>>>>>> regards,
>>>>>> ------------------------
>>>>>> NTT Software Corporation
>>>>>> Tomonari Katsumata
>>>>>> 
>>>>>> 
>>>>>> 2013/8/14 Tomonari Katsumata 
> <katsumata.tomonari@po.ntts.****co.jp<http://co.jp>
>>>> <katsumata.tomonari@po.**ntts.co.jp <kat...@po...>>
>>>> 
>>>>>>> 
>>>>>> 
>>>>>>> Hi Ashtosh,
>>>>>>> 
>>>>>>> Thanks for your comment!
>>>>>>> 
>>>>>>> 
>>>>>>>> The code changes are fine, but, the comment in
>>>>>> pgxc_collect_RTE_walker()
>>>>>>>> seems to specific. I think it should read, "create a copy of 
> query's
>>>>>>> range
>>>>>>>> table, so that it can be linked with other RTEs in the collector's
>>>>>>> context."
>>>>>>>> 
>>>>>>> I've revised the comment as you suggested.
>>>>>>> 
>>>>>>> 
>>>>>>>> In the test file, isn't there any way, you can add the offending
>>>>>> statent
>>>>>>>> without any wrapper function test_execute_direct_all_xc_*****
>>>> *nodes()?
>>>> 
>>>>>> We
>>>>>> 
>>>>>>> should
>>>>>>>> minimize the test-code to use only the minimal set of features. 
> Since
>>>>>>> this
>>>>>>>> statement no more gets into infinite loop, please do not use the
>>>>>>> statement
>>>>>>>> timeout.
>>>>>>> 
>>>>>>> I've gotten rid of using statement timeout.
>>>>>>> But I couldn't come up any idea to testing more simply,
>>>>>>> so the function is remaining as same as before patch.
>>>>>>> Any ideas?
>>>>>>> 
>>>>>>> Here is the new patch.
>>>>>>> (against dec40008b3d689911566514614c511******1c0a61327d)
>>>> 
>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>>> regards,
>>>>>>> -------------
>>>>>>> NTT Software Corporation
>>>>>>> Tomonari Katsumata
>>>>>>> 
>>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> ------------------------------**------------------------------**
>>>> ------------------
>>>>>> October Webinars: Code for Performance
>>>>>> Free Intel webinars can help you accelerate application performance.
>>>>>> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the
>>>> most
>>>>>> from
>>>>>> the latest Intel processors and coprocessors. See abstracts and
>>>> register >
>>>>>> http://pubads.g.doubleclick.**net/gampad/clk?id=60133471&iu=**
>>>> 
> /4140/ostg.clktrk<http://pubads.g.doubleclick.net/gampad/clk?id=60133471&iu=/4140/ostg.clktrk>
>>>>>> ______________________________**_________________
>>>>>> Postgres-xc-developers mailing list
>>>>>> 
> Postgres-xc-developers@lists.**sourceforge.net<Pos...@li...>
>>>>>> https://lists.sourceforge.net/**lists/listinfo/postgres-xc-**
>>>> 
> developers<https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers>
>>>>>> 
>>>>>> 
>>>>> 
>>>>> 
>>>> 
>>>> 
>>>> 
>>> 
>>> 
>>> --
>>> Best Wishes,
>>> Ashutosh Bapat
>>> EnterpriseDB Corporation
>>> The Postgres Database Company
>>> 
>>> 
>>> 
> ------------------------------------------------------------------------------
>>> October Webinars: Code for Performance
>>> Free Intel webinars can help you accelerate application performance.
>>> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most
>>> from
>>> the latest Intel processors and coprocessors. See abstracts and 
> register >
>>> 
> http://pubads.g.doubleclick.net/gampad/clk?id=60133471&iu=/4140/ostg.clktrk
>>> _______________________________________________
>>> Postgres-xc-developers mailing list
>>> Pos...@li...
>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>> 
>>> 
>> 
> 
> 
> 
> ------------------------------------------------------------------------------
> Android is increasing in popularity, but the open development platform that
> developers love is also attractive to malware creators. Download this white
> paper to learn more about secure code signing practices that can help keep
> Android apps secure.
> http://pubads.g.doubleclick.net/gampad/clk?id=65839951&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
> 

Re: [Postgres-xc-developers] Fwd: Re: Query Planning bug ?

[Tomonari K. <kat...@po...>]

Hi,

Almost one month is passing without any
comments about this thread.

I need this fix as soon as possible, because
these kinds queries are executed by
pg_statsinfo for Postgres-XC.

Anybody, please commit it.

regards,
------------
Tomonari Katsumata

(2013/09/28 10:53), Tomonari Katsumata wrote:
 > Hi Ashotosh,
 >
 >>    I never said "don't use functions". Here's what I mailed
 >>
 > Oh, Sorry for bothering you by my misunderstanding.
 >
 >>>        What is better for this testcase?
 >>
 >>    May be we should just drop the testcase. Anyway, EXEC DIRECT is not
 > production feature, but debug feature and whosoever wrote this 
feature has
 > not added any testcases for it anyway.
 >>    If anyone else has objections to dropping the testcase, please 
suggest
 > Tomonari, a better to write it.
 >>
 > Past 2 days, any suggestions were not sent to me.
 > So it seems no objections to dropping the testcase.
 >
 > regards,
 > ----------------
 > NTT Software Corporation
 > Tomonari Katsumata
 >
 >
 >
 > 2013/9/26 Ashutosh Bapat <ash...@en...>
 >
 >>
 >>
 >>
 >> On Thu, Sep 26, 2013 at 1:29 PM, Tomonari Katsumata <
 >> kat...@po...> wrote:
 >>
 >>> Hi Ashtosh,
 >>>
 >>> Thanks for your review!
 >>>
 >>> Previous patch(revised_fqs_r3.patch in [2013/08/14 19:55])
 >>> doesn't use a shell script.
 >>> Instead, It used test_execute_direct_all_xc_**nodes() function.
 >>>
 >>> I've got a comment "don't use functions" from you,
 >>> but I could not come up with good idea for the testcase.
 >>> The testcase needs to get all node_names to "EXECUTE DIRECT"
 >>> against all nodes.
 >>>
 >>>
 >> I never said "don't use functions". Here's what I mailed
 >>
 >> "In the test file, isn't there any way, you can add the offending 
statent
 >> without any wrapper function test_execute_direct_all_xc_
 >> nodes()? We should minimize the test-code to use only the minimal set of
 >> features. Since this statement no more gets into infinite loop, 
please do
 >> not use the statement timeout."
 >>
 >>
 >>> What is better for this testcase?
 >>>
 >>>
 >> May be we should just drop the testcase. Anyway, EXEC DIRECT is not
 >> production feature, but debug feature and whosoever wrote this 
feature has
 >> not added any testcases for it anyway.
 >>
 >> If anyone else has objections to dropping the testcase, please suggest
 >> Tomonari, a better to write it.
 >>
 >>
 >>> regards,
 >>>
 >>> ----------------
 >>> NTT Software Corporation
 >>> Tomonari Katsumata
 >>>
 >>> (2013/09/26 15:44), Ashutosh Bapat wrote:
 >>>> Hi Tomonary,
 >>>> The testcase you have written uses a shell script. This is not the
 >>> standard
 >>>> way to add testcases. Can you please see if the testcase can be added
 >>>> directly as SQL? Please send a revised patch ASAP, so that it can be
 >>>> committed. I would have liked to see more testcases for EXEC 
DIRECT, but
 >>>> since it's a debug only feature (not for production), it gets lesser
 >>>> priority in testing and thus we do not see testcases for it.
 >>>>
 >>>>
 >>>> On Thu, Sep 26, 2013 at 8:26 AM, Tomonari Katsumata <
 >>>> kat...@po....**jp <kat...@po...>>
 >>> wrote:
 >>>>
 >>>>> Hi,
 >>>>>
 >>>>> Does anyone check these patches?
 >>>>> If there are no comments, please commit it.
 >>>>>
 >>>>> regards,
 >>>>>
 >>>>> ----------------------
 >>>>> NTT Software Corporation
 >>>>> Tomonari Katsumata
 >>>>>
 >>>>> -------- Original Message --------
 >>>>> Subject:     Re: [Postgres-xc-developers] Query Planning bug ?
 >>>>> Date:     Thu, 5 Sep 2013 21:34:32 +0900
 >>>>> From:     Tomonari Katsumata <t.k...@gm...>
 >>>>> To:     Tomonari Katsumata 
<katsumata.tomonari@po.ntts.****co.jp<http://co.jp>
 >>> <katsumata.tomonari@po.**ntts.co.jp <kat...@po...>>
 >>>>>>
 >>>>> CC:     Ashutosh Bapat <ashutosh.bapat@enterprisedb.****com<
 >>> ashutosh.bapat@**enterprisedb.com <ash...@en...>>>,
 >>>>> pgxc-hackers mailing list <postgres-xc-developers@lists.****
 >>> 
sourceforge.net<postgres-xc-**dev...@li...urceforge.**net<pos...@li...>
 >>>>
 >>>
 >>>>>>
 >>>>>
 >>>>>
 >>>>> Hi,
 >>>>>
 >>>>> I'm sorry for long long leaving this thread alone.
 >>>>> I've devided the patch to source part and regression part.
 >>>>> (against f8e0f994bff1faefa9b1fc2f6e584a****1d3de70241)
 >>>
 >>>>>
 >>>>> I did not change any process on source part.
 >>>>> (revised_fqs_r4.patch)
 >>>>>
 >>>>> I added a script into src/test/regress/sql directory for
 >>>>> changing regression part.
 >>>>> (add_test_xc_misc.patch and execute_direct_regress.sh)
 >>>>> When regression test runs, the script is executed instead of
 >>>>> user defined function.
 >>>>>
 >>>>> Please check and commit it.
 >>>>>
 >>>>> regards,
 >>>>> ------------------------
 >>>>> NTT Software Corporation
 >>>>> Tomonari Katsumata
 >>>>>
 >>>>>
 >>>>> 2013/8/14 Tomonari Katsumata 
<katsumata.tomonari@po.ntts.****co.jp<http://co.jp>
 >>> <katsumata.tomonari@po.**ntts.co.jp <kat...@po...>>
 >>>
 >>>>>>
 >>>>>
 >>>>>> Hi Ashtosh,
 >>>>>>
 >>>>>> Thanks for your comment!
 >>>>>>
 >>>>>>
 >>>>>>> The code changes are fine, but, the comment in
 >>>>> pgxc_collect_RTE_walker()
 >>>>>>> seems to specific. I think it should read, "create a copy of 
query's
 >>>>>> range
 >>>>>>> table, so that it can be linked with other RTEs in the collector's
 >>>>>> context."
 >>>>>>>
 >>>>>> I've revised the comment as you suggested.
 >>>>>>
 >>>>>>
 >>>>>>> In the test file, isn't there any way, you can add the offending
 >>>>> statent
 >>>>>>> without any wrapper function test_execute_direct_all_xc_*****
 >>> *nodes()?
 >>>
 >>>>> We
 >>>>>
 >>>>>> should
 >>>>>>> minimize the test-code to use only the minimal set of features. 
Since
 >>>>>> this
 >>>>>>> statement no more gets into infinite loop, please do not use the
 >>>>>> statement
 >>>>>>> timeout.
 >>>>>>
 >>>>>> I've gotten rid of using statement timeout.
 >>>>>> But I couldn't come up any idea to testing more simply,
 >>>>>> so the function is remaining as same as before patch.
 >>>>>> Any ideas?
 >>>>>>
 >>>>>> Here is the new patch.
 >>>>>> (against dec40008b3d689911566514614c511******1c0a61327d)
 >>>
 >>>>>
 >>>>>>
 >>>>>>
 >>>>>> regards,
 >>>>>> -------------
 >>>>>> NTT Software Corporation
 >>>>>> Tomonari Katsumata
 >>>>>>
 >>>>>>
 >>>>>
 >>>>>
 >>>>>
 >>>>>
 >>>>> ------------------------------**------------------------------**
 >>> ------------------
 >>>>> October Webinars: Code for Performance
 >>>>> Free Intel webinars can help you accelerate application performance.
 >>>>> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the
 >>> most
 >>>>> from
 >>>>> the latest Intel processors and coprocessors. See abstracts and
 >>> register >
 >>>>> http://pubads.g.doubleclick.**net/gampad/clk?id=60133471&iu=**
 >>> 
/4140/ostg.clktrk<http://pubads.g.doubleclick.net/gampad/clk?id=60133471&iu=/4140/ostg.clktrk>
 >>>>> ______________________________**_________________
 >>>>> Postgres-xc-developers mailing list
 >>>>> 
Postgres-xc-developers@lists.**sourceforge.net<Pos...@li...>
 >>>>> https://lists.sourceforge.net/**lists/listinfo/postgres-xc-**
 >>> 
developers<https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers>
 >>>>>
 >>>>>
 >>>>
 >>>>
 >>>
 >>>
 >>>
 >>
 >>
 >> --
 >> Best Wishes,
 >> Ashutosh Bapat
 >> EnterpriseDB Corporation
 >> The Postgres Database Company
 >>
 >>
 >> 
------------------------------------------------------------------------------
 >> October Webinars: Code for Performance
 >> Free Intel webinars can help you accelerate application performance.
 >> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most
 >> from
 >> the latest Intel processors and coprocessors. See abstracts and 
register >
 >> 
http://pubads.g.doubleclick.net/gampad/clk?id=60133471&iu=/4140/ostg.clktrk
 >> _______________________________________________
 >> Postgres-xc-developers mailing list
 >> Pos...@li...
 >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
 >>
 >>
 >

Re: [Postgres-xc-developers] GTM error message to the log

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

I think error logs of communication errors between GTM and GTM standby can be written at GTM master as well.    Please let me allow some more time to submit the patch for it.

---
Koichi Suzuki

On 2013/10/25, at 0:00, Nikhil Sontakke <ni...@st...<mailto:ni...@st...>> wrote:

Seems like a good thing to add in the GTM daemons (covers GTM Standby as well?). So +1 from me.

Regards,
Nikhils


On Thu, Oct 24, 2013 at 6:43 AM, Koichi Suzuki <koi...@gm...<mailto:koi...@gm...>> wrote:
Hi,

Libpq for GTM and GTM_Proxy has a feature to store its own error messages to gtm_conn structure.    Because Libpq is to be linked to any other applications, GTM libpq itself does not call elog().   However, modules calling gtm libpq can do this.

I found we can add this feature to gtm_proxy's proxy_main.c and backend's gtm.c and hope this helps to analyze what's going on in gtm communications.

If I have reasonable approval, I'd like to submit a patch for this.

Regards;
---
Koichi Suzuki

------------------------------------------------------------------------------
October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60135991&iu=/4140/ostg.clktrk
_______________________________________________
Postgres-xc-developers mailing list
Pos...@li...<mailto:Pos...@li...>
https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers




--
StormDB - http://www.stormdb.com<http://www.stormdb.com/>
The Database Cloud
------------------------------------------------------------------------------
October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60135991&iu=/4140/ostg.clktrk_______________________________________________
Postgres-xc-developers mailing list
Pos...@li...
https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers

Re: [Postgres-xc-developers] Potential bug in copy command

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

Thanks.   I wrote a preliminary patch and found it seems to work fine.
I'm now inserting sleep between retries if we have to retry many times.
Will see the patch soon.

Regards;

---
Koichi Suzuki


2013/10/23 Nikhil Sontakke <ni...@st...>

> +1
>
> I had diagnosed this same issue quite a while ago. We should wait for the
> buffer to drain out before adding more into it. The buffer size was going
> beyond 1GB in size pretty quickly!
>
> Regards,
> Nikhils
>
>
> On Wed, Oct 23, 2013 at 10:35 AM, Koichi Suzuki <koi...@gm...>wrote:
>
>> I've found that in copy command, we have a risk to overflow the buffer in
>> coordinator if datanode is very slow (for various reasons).
>>
>> When we issue copy command against tens of gigabytes of data, coordinator
>> send all of then without synch.   When some of the data fails to send, it
>> adds next data and tries to send all of them.   When a datanode is very
>> slow (for any reason), the data will continue to be cashed at the
>> coordinator and finally overflows.
>>
>> The patch avoids this issue.   When amount of unsent data exceeds the
>> criteria, coordinator will keep retry until this amount is below the second
>> criteria.
>>
>> This can be applied to all the releases, as well as the master.
>>
>> Regards;
>> ---
>> Koichi Suzuki
>>
>>
>> ------------------------------------------------------------------------------
>> October Webinars: Code for Performance
>> Free Intel webinars can help you accelerate application performance.
>> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most
>> from
>> the latest Intel processors and coprocessors. See abstracts and register >
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=60135991&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>>
>
>
> --
> StormDB - http://www.stormdb.com
> The Database Cloud
>

Re: [Postgres-xc-developers] GTM error message to the log

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

I think error logs of communication errors between GTM and GTM standby can
be written at GTM master as well.    Please let me allow some more time to
submit the patch for it.

---
Koichi Suzuki


2013/10/25 Nikhil Sontakke <ni...@st...>

> Seems like a good thing to add in the GTM daemons (covers GTM Standby as
> well?). So +1 from me.
>
> Regards,
> Nikhils
>
>
> On Thu, Oct 24, 2013 at 6:43 AM, Koichi Suzuki <koi...@gm...>wrote:
>
>> Hi,
>>
>> Libpq for GTM and GTM_Proxy has a feature to store its own error messages
>> to gtm_conn structure.    Because Libpq is to be linked to any other
>> applications, GTM libpq itself does not call elog().   However, modules
>> calling gtm libpq can do this.
>>
>> I found we can add this feature to gtm_proxy's proxy_main.c and backend's
>> gtm.c and hope this helps to analyze what's going on in gtm communications.
>>
>> If I have reasonable approval, I'd like to submit a patch for this.
>>
>> Regards;
>> ---
>> Koichi Suzuki
>>
>>
>> ------------------------------------------------------------------------------
>> October Webinars: Code for Performance
>> Free Intel webinars can help you accelerate application performance.
>> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most
>> from
>> the latest Intel processors and coprocessors. See abstracts and register >
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=60135991&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>>
>
>
> --
> StormDB - http://www.stormdb.com
> The Database Cloud
>

Re: [Postgres-xc-developers] Minor Fixes

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

On Fri, Oct 25, 2013 at 1:14 AM, Matt Warner <MW...@xi...> wrote:
> Good morning. I didn't see any responses to this.
>
> I know everyone is busy. Were the changes acceptable?
Sorry, I'm quite busy the last couple of weeks, so I got no time to
look at your patches.
-- 
Michael

Re: [Postgres-xc-developers] Minor Fixes

[Matt W. <MW...@xi...>]

Good morning. I didn't see any responses to this.

I know everyone is busy. Were the changes acceptable?

Matt

-----Original Message-----
From: Matt Warner [mailto:MW...@XI...] 
Sent: Wednesday, October 09, 2013 4:29 PM
To: 'Michael Paquier'
Cc: Postgres-XC Developers; Suzuki; Koichi
Subject: Re: [Postgres-xc-developers] Minor Fixes

Below is a summary of the changes made against git pull from this morning.

I don't claim to be the best resource for this, so please do take the time to verify what I've sent. I also welcome comments and suggestions.

Please note that the entire config directory was replaced with the one from Postgres 9.3.0.

Likewise, I used configure.in from Postgres 9.3.0 as the starting point for the updates. autoconf successfully creates the configure file, which is also attached. I've tried to capture all the XC pieces that had been added manually to the previous configure file, but the new configure file should be checked and verified.

In particular, I moved the '-DPGXC' from configure to pgxc_config.h and updated postgres.h to include this new header.

The changes all compile successfully under Solaris 10 using the Solaris Studio compiler. Being that the Solaris compiler seems to be more demanding than gcc about syntax and "correctness", I believe this is a good sign. I also ran this with test data and saw no issues.

FYI,

Matt

% git status
# On branch master
# Changes to be committed:
#   (use "git reset HEAD <file>..." to unstage)
#
#	new file:   src/include/pgxc_config.h
#	new file:   src/include/pgxc_config.h.in
#	new file:   src/include/stamp-ext-h
#
# Changes not staged for commit:
#   (use "git add <file>..." to update what will be committed)
#   (use "git checkout -- <file>..." to discard changes in working directory)
#
#	modified:   config/c-compiler.m4
#	modified:   config/config.guess
#	modified:   config/config.sub
#	modified:   config/install-sh
#	modified:   config/perl.m4
#	modified:   config/programs.m4
#	modified:   config/python.m4
#	modified:   configure
#	modified:   configure.in
#	modified:   src/backend/pgxc/pool/pgxcnode.c
#	modified:   src/include/postgres.h
#

-----Original Message-----
From: Michael Paquier [mailto:mic...@gm...] 
Sent: Tuesday, October 08, 2013 5:43 PM
To: Matt Warner
Cc: 鈴木 幸市; Postgres-XC Developers; Koichi Suzuki
Subject: Re: [Postgres-xc-developers] Minor Fixes

On Wed, Oct 9, 2013 at 9:13 AM, Matt Warner <MW...@xi...> wrote:
> So I owe you a different patch for proxy_main.c as well.
Btw, I can see why it is needed for pgxcnode.h as we use FIONREAD
there, but why is it necessary to have an include to filio.h in
proxy_main.c? I couldn't figure out why so I simply removed that from
the patch committed.
-- 
Michael