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

Re: [Postgres-xc-developers] connection to server was lost

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

Hi David,
It might be due to the large amount of data sent from the datanode to the
connector. When you see message "connection to client lost" at datanode, it
means that the connection to the coordinator was lost. In XC, coordinators
act as client to the datanode. Further, no message in the coordinator log
implies that there wasn't any segfault or error on the coordinator which
can result in loosing client (to the datanode). One way to verify this is
to check what happens for smaller amounts of the data. There is still some
code in executor, which saves data from datanode in a linked list and
because of large amount of data that process runs out of memory. You may
find something in system logs, if that is true.

Please do the following,
Run explain verbose on the query which showed this behavior and in that
output you will find what query is being sent to the datanode
Reduce your data on the datanode such that, that particular query returns
may be a few thousand rows to the coordinator. BTW, I have seen millions of
rows being exchanged between the coordinator and datanode without problem.
But still there is a case where large data would be a problem.
Now, see if the query runs without problem.



On Fri, Feb 28, 2014 at 6:23 AM, David E. Wheeler <da...@ju...>wrote:

> PGXC Hakers,
>
> I have finally loaded up my testing PGXC four-node cluster with a nice
> beefy database similar to a PostgreSQL database we use for long-running
> reporting queries. I gathered up one of our slower-running queries (26.6m
> run) and ran it on XC. Alas, after a while, it died with this error:
>
>     psql:slow.sql:73: connection to server was lost
>
> The coordinator log was not much help: nothing was logged. So I trolled
> through the logs on the data nodes. All four had these messages:
>
> > 2014-02-27 15:45:51 PST dwheeler 10.4.34.1(56968) 22213 530fc838.56c5
> LOG:  could not send data to client: Connection reset by peer
> > 2014-02-27 15:45:51 PST dwheeler 10.4.34.1(56968) 22213 530fc838.56c5
> STATEMENT:  SELECT subscriber_cmd_id, rule_reason, rule_score, txn_uuid,
> txn_timestamp_utc FROM ONLY subscriber_482900.transactions_rule tma WHERE
> (subscriber_id = 482900)
> > 2014-02-27 15:45:51 PST dwheeler 10.4.34.1(56968) 22213 530fc838.56c5
> FATAL:  connection to client lost
> > 2014-02-27 15:45:51 PST dwheeler 10.4.34.1(56968) 22213 530fc838.56c5
> STATEMENT:  SELECT subscriber_cmd_id, rule_reason, rule_score, txn_uuid,
> txn_timestamp_utc FROM ONLY subscriber_482900.transactions_rule tma WHERE
> (subscriber_id = 482900)
>
> No reason given for the dropped connection. I ran the query on the
> coordinator box, so psql should have connected via a socket rather than
> TCP. Out of curiosity, I looked at the logs for the other three
> coordinators. None had any error messages, either.
>
> So, no idea what's timing out; statement_timeout is set to 0. Here are the
> settings from my coordinator's postgreql.conf:
>
>     max_connections = 100
>     shared_buffers = 32MB
>     log_destination = 'stderr'
>     logging_collector = on
>     log_directory = 'pg_log'
>     log_filename = 'postgresql-%a.log'
>     log_truncate_on_rotation = on
>     log_rotation_age = 1d
>     log_rotation_size = 0
>     log_line_prefix = '< %m >'
>     log_timezone = 'US/Pacific'
>     datestyle = 'iso, mdy'
>     timezone = 'US/Pacific'
>     lc_messages = 'en_US.UTF-8'
>     lc_monetary = 'en_US.UTF-8'
>     lc_numeric = 'en_US.UTF-8'
>     lc_time = 'en_US.UTF-8'
>     default_text_search_config = 'pg_catalog.english'
>     pgxc_node_name = 'node1'
>     port = 5432
>     listen_addresses = '*'
>     shared_buffers = 250MB
>     work_mem = 128MB
>     maintenance_work_mem = 128MB
>     effective_cache_size = 8GB
>     log_line_prefix = '%t %u %r %p %c '
>     timezone = 'UTC'
>     gtm_host = 'node1.example.com'
>
> And from one of the data nodes (only the names differ on the others):
>
>     max_connections = 100
>     shared_buffers = 32MB
>     log_destination = 'stderr'
>     logging_collector = on
>     log_directory = 'pg_log'
>     log_filename = 'postgresql-%a.log'
>     log_truncate_on_rotation = on
>     log_rotation_age = 1d
>     log_rotation_size = 0
>     log_line_prefix = '< %m >'
>     log_timezone = 'US/Pacific'
>     datestyle = 'iso, mdy'
>     timezone = 'US/Pacific'
>     lc_messages = 'en_US.UTF-8'
>     lc_monetary = 'en_US.UTF-8'
>     lc_numeric = 'en_US.UTF-8'
>     lc_time = 'en_US.UTF-8'
>     default_text_search_config = 'pg_catalog.english'
>     pgxc_node_name = 'node1'
>     port = 15432
>     listen_addresses = '*'
>     shared_buffers = 750MB
>     work_mem = 128MB
>     maintenance_work_mem = 128MB
>     effective_cache_size = 23GB
>     log_line_prefix = '%t %u %r %p %c '
>     timezone = 'UTC'
>     gtm_host = 'node1.iovationnp.com'
>
> Thoughts? What could be timing out?
>
> Thanks,
>
> David
>
>
>
>
> ------------------------------------------------------------------------------
> Flow-based real-time traffic analytics software. Cisco certified tool.
> Monitor traffic, SLAs, QoS, Medianet, WAAS etc. with NetFlow Analyzer
> Customize your own dashboards, set traffic alerts and generate reports.
> Network behavioral analysis & security monitoring. All-in-one tool.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=126839071&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>



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

[Postgres-xc-developers] connection to server was lost

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

PGXC Hakers,

I have finally loaded up my testing PGXC four-node cluster with a nice beefy database similar to a PostgreSQL database we use for long-running reporting queries. I gathered up one of our slower-running queries (26.6m run) and ran it on XC. Alas, after a while, it died with this error:

    psql:slow.sql:73: connection to server was lost

The coordinator log was not much help: nothing was logged. So I trolled through the logs on the data nodes. All four had these messages:

> 2014-02-27 15:45:51 PST dwheeler 10.4.34.1(56968) 22213 530fc838.56c5 LOG:  could not send data to client: Connection reset by peer
> 2014-02-27 15:45:51 PST dwheeler 10.4.34.1(56968) 22213 530fc838.56c5 STATEMENT:  SELECT subscriber_cmd_id, rule_reason, rule_score, txn_uuid, txn_timestamp_utc FROM ONLY subscriber_482900.transactions_rule tma WHERE (subscriber_id = 482900)
> 2014-02-27 15:45:51 PST dwheeler 10.4.34.1(56968) 22213 530fc838.56c5 FATAL:  connection to client lost
> 2014-02-27 15:45:51 PST dwheeler 10.4.34.1(56968) 22213 530fc838.56c5 STATEMENT:  SELECT subscriber_cmd_id, rule_reason, rule_score, txn_uuid, txn_timestamp_utc FROM ONLY subscriber_482900.transactions_rule tma WHERE (subscriber_id = 482900)

No reason given for the dropped connection. I ran the query on the coordinator box, so psql should have connected via a socket rather than TCP. Out of curiosity, I looked at the logs for the other three coordinators. None had any error messages, either.

So, no idea what’s timing out; statement_timeout is set to 0. Here are the settings from my coordinator’s postgreql.conf:

    max_connections = 100
    shared_buffers = 32MB
    log_destination = 'stderr'
    logging_collector = on
    log_directory = 'pg_log'
    log_filename = 'postgresql-%a.log'
    log_truncate_on_rotation = on
    log_rotation_age = 1d
    log_rotation_size = 0
    log_line_prefix = '< %m >'
    log_timezone = 'US/Pacific'
    datestyle = 'iso, mdy'
    timezone = 'US/Pacific'
    lc_messages = 'en_US.UTF-8'
    lc_monetary = 'en_US.UTF-8'
    lc_numeric = 'en_US.UTF-8'
    lc_time = 'en_US.UTF-8'
    default_text_search_config = 'pg_catalog.english'
    pgxc_node_name = 'node1'
    port = 5432
    listen_addresses = '*'
    shared_buffers = 250MB
    work_mem = 128MB
    maintenance_work_mem = 128MB
    effective_cache_size = 8GB
    log_line_prefix = '%t %u %r %p %c '
    timezone = 'UTC'
    gtm_host = 'node1.example.com'

And from one of the data nodes (only the names differ on the others):

    max_connections = 100			
    shared_buffers = 32MB			
    log_destination = 'stderr'
    logging_collector = on	
    log_directory = 'pg_log'
    log_filename = 'postgresql-%a.log'
    log_truncate_on_rotation = on	
    log_rotation_age = 1d			
    log_rotation_size = 0			
    log_line_prefix = '< %m >'		
    log_timezone = 'US/Pacific'
    datestyle = 'iso, mdy'
    timezone = 'US/Pacific'
    lc_messages = 'en_US.UTF-8'		
    lc_monetary = 'en_US.UTF-8'		
    lc_numeric = 'en_US.UTF-8'		
    lc_time = 'en_US.UTF-8'			
    default_text_search_config = 'pg_catalog.english'
    pgxc_node_name = 'node1'	
    port = 15432
    listen_addresses = '*'
    shared_buffers = 750MB
    work_mem = 128MB
    maintenance_work_mem = 128MB
    effective_cache_size = 23GB
    log_line_prefix = '%t %u %r %p %c '
    timezone = 'UTC'
    gtm_host = 'node1.iovationnp.com'

Thoughts? What could be timing out?

Thanks,

David

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

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

On Feb 27, 2014, at 3:29 PM, Koichi Suzuki <koi...@gm...> wrote:

> It is not correct.  1.0  and 1.1 are maintained.    Fix of the issue may not be easy though.

So, maintained, but no plan to release new versions? Sure, some bugs may be too much trouble for a maintained branch, but as Abbas says there are no plans to release new versions, it sounds an awful lot to me like no issues will be fixed, at least not in a release.

Best,

David

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

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

It is not correct.  1.0  and 1.1 are maintained.    Fix of the issue may
not be easy though.

Regards
---
Koichi Suzuki
2014/02/28 2:09 "David E. Wheeler" <da...@ju...>:

> On Feb 27, 2014, at 8:35 AM, Abbas Butt <abb...@en...>
> wrote:
>
> > Sorry for the confusion. We have currently no plans to release 1.1.1.
> Between 1.1 and 1.2 we have re-factored code in many areas and bug fixes
> are not easily back portable.
>
> Okay, so 1.0 and 1.1 are no longer maintained or supported. Good to know.
>
> Thank you,
>
> David
>
> ------------------------------------------------------------------------------
> Flow-based real-time traffic analytics software. Cisco certified tool.
> Monitor traffic, SLAs, QoS, Medianet, WAAS etc. with NetFlow Analyzer
> Customize your own dashboards, set traffic alerts and generate reports.
> Network behavioral analysis & security monitoring. All-in-one tool.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=126839071&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>

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

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

On Feb 27, 2014, at 8:35 AM, Abbas Butt <abb...@en...> wrote:

> Sorry for the confusion. We have currently no plans to release 1.1.1. Between 1.1 and 1.2 we have re-factored code in many areas and bug fixes are not easily back portable.

Okay, so 1.0 and 1.1 are no longer maintained or supported. Good to know.

Thank you,

David

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

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

On Wed, Feb 19, 2014 at 12:46 AM, David E. Wheeler <da...@ju...>wrote:

> On Feb 16, 2014, at 11:13 PM, 鈴木 幸市 <ko...@in...> wrote:
>
> > 1.1 is not suffered by this.    The bug was introduced during planner
> code change to deal with PG planner code change (mainly from automatic
> updatable views).
>
> I don’t understand. I get this error on 1.1, and Abbas says it is not
> present in 1.2. That seems like an issue in 1.1 that might be fixable in a
> 1.1.1 release, should there ever be one. Am I missing something?
>

Sorry for the confusion. We have currently no plans to release 1.1.1.
Between 1.1 and 1.2 we have re-factored code in many areas and bug fixes
are not easily back portable.


> Best,
>
> David




-- 
-- 
*Abbas*
 Architect

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

*Follow us on Twitter*
@EnterpriseDB

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

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

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

I may be wrong.   The key issue is XC planner may choose CTID as a key
to update/delete replicated table rows when whole statement cannot
push down to datanodes.   I understood this was introduced in 1.2 but
this could have been an issue in 1.1 as well.

Ashutosh, Abbas, could you clarify this?

Regards;
---
Koichi Suzuki


2014-02-19 4:46 GMT+09:00 David E. Wheeler <da...@ju...>:
> On Feb 16, 2014, at 11:13 PM, 鈴木 幸市 <ko...@in...> wrote:
>
>> 1.1 is not suffered by this.    The bug was introduced during planner code change to deal with PG planner code change (mainly from automatic updatable views).
>
> I don’t understand. I get this error on 1.1, and Abbas says it is not present in 1.2. That seems like an issue in 1.1 that might be fixable in a 1.1.1 release, should there ever be one. Am I missing something?
>
> Best,
>
> David
> ------------------------------------------------------------------------------
> Managing the Performance of Cloud-Based Applications
> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
> Read the Whitepaper.
> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers

Re: [Postgres-xc-developers] pgbench of Postgres-XC 1.2 is broken.

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

Thanks Tmonari;

I believe that your team found another issue and its fix will be
available soon.   I'd like to put this together with another one.

Regards;
---
Koichi Suzuki


2014-02-20 19:05 GMT+09:00 Tomonari Katsumata <t.k...@gm...>:
> Hi,
>
> I've noticed that pgbench included in Postgres-XC 1.2 is
> broken and I can not work it fine.
>
> [compiling]
> pgbench.c: In function 'init':
> pgbench.c:1530: warning: excess elements in struct initializer
> pgbench.c:1530: warning: (near initialization for 'DDLs[0]')
> pgbench.c:1532: warning: excess elements in struct initializer
> pgbench.c:1532: warning: (near initialization for 'DDLs[0]')
>
> Above messages are printed.
>
> [initializing]
> $ pgbench -i bench
> NOTICE:  table "pgbench_branches" does not exist, skipping
> NOTICE:  table "pgbench_tellers" does not exist, skipping
> NOTICE:  table "pgbench_accounts" does not exist, skipping
> creating tables...
> 100000 of 100000 tuples (100%) done (elapsed 0.27 s, remaining 0.00 s).
> vacuum...
> ERROR:  relation "pgbench_history" does not exist
>
>
> I could not create pgbench_history table.
>
> I attached a patch to fix this problem.
> Please fix by formal relase of Postgres-XC 1.2.
>
> regards,
> ---------------
> Tomonari Katsumata
>
> ------------------------------------------------------------------------------
> Managing the Performance of Cloud-Based Applications
> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
> Read the Whitepaper.
> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&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

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

Here are the results of DBT-1 runs on the buildfarm.
The patch solves the duplicate key error problem that was reported and the
added test cases behave as expected.

ID 115 : Without the patch.
ID 114 : With the patch.

For reference see the datanode log for id 115, it shows the duplicate key
errors where as the same log in ID 114 does not show any such errors.

The links showing the logs are as follows
https://61.197.200.11/files/115/dbt1/pg_log/node01/datanode_dn1/postgresql-2014-02-25_112551.log
https://61.197.200.11/files/114/dbt1/pg_log/node01/datanode_dn1/postgresql-2014-02-25_102910.log



I have however observed a problem that has nothing to do with the current
patch.

Consider the TPS graphs of ID 112 and 102
The links are as follows:
https://61.197.200.11/tps_chart/index/112
https://61.197.200.11/tps_chart/index/102

ID 112 is using commit ID 9b6be5b8ba66eb9e083a4a80669e7d41a2cea3dc, which
is a recent commit to branch REL1_2_STABLE, whereas ID 102 is using commit
ID ea3dbab2dbebe770c0e90132281dbb37ae31fdcc which is an older commit to
branch master_pg93_merge.

Notice that the TPS for ID 102 stays stable at 13000 whereas the TPS for ID
112 deteriorates over time and the max achieved TPS is also reduced  to
7000 only.

Knowing that the branch REL1_2_STABLE was created using master_pg93_merge
this needs to be investigated further to find which commit/step is causing
this deterioration.

Best Regards



On Tue, Feb 25, 2014 at 9:33 AM, Abbas Butt <abb...@en...>wrote:

> Hi,
> Attached please find revised and updated patch that performs
> updates/deletes to replicated tables based on either primary key or unique
> index under the following conditions
> 1. The replicated table has either a primary key or a unique index defined.
> 2. The query is not changing the primary key itself.
> Otherwise ctid is used, like we were using previously.
>
> Getting a clean regression with the patch was not easy. Lot of issues had
> to be fixed especially with deletes.
> Regression and DBT-1 now work cleanly after that patch.
> Test cases are added in the regression to make sure the
> added functionality is working fine.
>
> The attached patch does not include the cursors approach as of now.
> The patch to use cursors in case when primary key cannot be used is not
> complete yet. The changes in the planner are done, but the changes in
> executor are still to do. The current executor does not work with a fetch
> and update plan, and needs some re factoring.
>
> Best Regards
>
>
>
>
> On Mon, Feb 24, 2014 at 10:12 PM, Ahsan Hadi <ahs...@en...>wrote:
>
>> Abbas,
>> Please give us an update on progress?
>>
>>
>> On Thu, Feb 20, 2014 at 3:38 PM, Abbas Butt <abb...@en...>wrote:
>>
>>>
>>>
>>>
>>> On Thu, Feb 20, 2014 at 3:25 PM, Ahsan Hadi <ahs...@en...
>>> > wrote:
>>>
>>>>
>>>>
>>>>
>>>> On Wed, Feb 19, 2014 at 3:56 PM, Abbas Butt <
>>>> abb...@en...> wrote:
>>>>
>>>>> Lets do this
>>>>> 1. If the replicated table has a unique key defined , then use Mason's
>>>>> patch after testing and refinement if required.
>>>>> 2. If the replicated table has no key, then we can use cursors idea
>>>>> proposed by Ashutosh.
>>>>>
>>>>> Does every body agree?
>>>>>
>>>>
>>>> Yes.
>>>>
>>>> Can you provide a patch for the above before monday?
>>>>
>>>
>>> I will try my best to.
>>>
>>>
>>>>
>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Wed, Feb 19, 2014 at 12:15 PM, Koichi Suzuki <koi...@gm...
>>>>> > wrote:
>>>>>
>>>>>> 2014-02-19 16:00 GMT+09:00 Ahsan Hadi <ahs...@en...>:
>>>>>> >
>>>>>> >
>>>>>> >
>>>>>> > On Tue, Feb 18, 2014 at 11:26 PM, Amit Khandekar
>>>>>> > <ami...@en...> wrote:
>>>>>> >>
>>>>>> >>
>>>>>> >>
>>>>>> >>
>>>>>> >> On 13 February 2014 11:54, Ashutosh Bapat
>>>>>> >> <ash...@en...> wrote:
>>>>>> >>>
>>>>>> >>> One more solution would be to use cursors for replicated tables.
>>>>>> The idea
>>>>>> >>> is to open cursors on all the copies of the table and append the
>>>>>> query with
>>>>>> >>> an ORDER BY clause on all the columns. Thus we are sure that the
>>>>>> current of
>>>>>> >>> each of these cursors point to same row on all the copies. While
>>>>>> fetching a
>>>>>> >>> row from a replicated table, we fetch from all the cursors and
>>>>>> choose only
>>>>>> >>> one row for the data processing. While updating or deleting we
>>>>>> send UPDATE
>>>>>> >>> or DELETE with WHERE CURRENT OF. The down side of this approach
>>>>>> is that, if
>>>>>> >>> there are coordinator quals, we will end up locking more rows than
>>>>>> >>> necessary, increasing the probability of the deadlock but at
>>>>>> least there
>>>>>> >>> won't be a necessary restriction of having primary or unique key
>>>>>> and we
>>>>>> >>> won't break backward compatibility.
>>>>>> >>>
>>>>>> >>> If there two identical rows, we might mix the update from
>>>>>> different
>>>>>> >>> nodes, but then who knew which of them were corresponded across
>>>>>> the nodes to
>>>>>> >>> start with.
>>>>>> >>>
>>>>>> >>
>>>>>> >> Locking all rows doesn't look good especially because we are
>>>>>> looking for a
>>>>>> >> permanent long term solution. If we can come up with some other
>>>>>> solution
>>>>>> >> that avoids this, we better avoid this compromise. For a
>>>>>> replicated table
>>>>>> >> with 10000 rows, all concurrent updates will be serialized even if
>>>>>> they are
>>>>>> >> updating a different row.
>>>>>> >>
>>>>>> >> Other thing is datanode performance impact for ORDER BY all
>>>>>> columns,
>>>>>> >> especially with many large size columns. I had also mentioned
>>>>>> about ORDER BY
>>>>>> >> in approach A. I am not sure whether there is some kind of
>>>>>> optimization in
>>>>>> >> the sort, such as: if we find unique rows with the first n
>>>>>> columns, it does
>>>>>> >> not compare the rest of the columns.
>>>>>> >>
>>>>>> >> I think declaring cursors is a cool idea in general for DMLs but it
>>>>>> >> requires refactoring of DML planning, and also it requires ORDER
>>>>>> BY. There
>>>>>> >> is a concurrent update issue #398 for which we do require a
>>>>>> refactor of DML
>>>>>> >> handling. While doing that it will be clearer whether declaring
>>>>>> cursor is
>>>>>> >> really beneficial or if it's not feasible. For ORDER BY, again,
>>>>>> for long
>>>>>> >> term, we should have a primary key or an internal unique key so
>>>>>> that rows
>>>>>> >> can be ordered on that single column as against all columns. So
>>>>>> again, we
>>>>>> >> still are better of with a new system column.
>>>>>> >>
>>>>>> >>
>>>>>> >> As regards to approach C, if we find a way to uniquely generate a
>>>>>> new row
>>>>>> >> id independently, then the task of generating rowid will be pretty
>>>>>> >> lightweight. We won't require any other table to store it or
>>>>>> generate it.
>>>>>> >> The coordinator will generate it at each insert (both fqs and
>>>>>> non-fqs), or
>>>>>> >> may be datanodes themselves find a way to generate a new rowid
>>>>>> which is
>>>>>> >> always the same regardless of the datanode. A combination of gxid,
>>>>>> timestamp
>>>>>> >> and cmd id can be used to construct a unique rowid at the
>>>>>> coordinator.
>>>>>> >>
>>>>>> >> I think one action plan can be :
>>>>>> >> 1. Use Mason's patch and tweak it so that it needs very little
>>>>>> >> modification later on if and when we add the system rowid column.
>>>>>> >> 2. Check in the patch but let it not error out if the primary key
>>>>>> is not
>>>>>> >> there. This way we would at least make the replicated tables with
>>>>>> primary
>>>>>> >> keys work without data issues, but continue to work as it is now
>>>>>> for tables
>>>>>> >> without primary key.
>>>>>> >> 3. Lastly support the new system row id implementation, and do an
>>>>>> >> incremental change in Mason's checked in changes to use this id
>>>>>> instead of
>>>>>> >> primary key.
>>>>>> >>
>>>>>> >
>>>>>> > Sounds good but as i understand 3 is not targeted for 1.2?
>>>>>>
>>>>>> I agree on this too.   System column will benefit not only this, but
>>>>>> also WCO, more variety of the cursor, and Triggers.   I agree this is
>>>>>> targeted to 1.3 or later.
>>>>>>
>>>>>> Regards;
>>>>>> ---
>>>>>> Koichi Suzuki
>>>>>>
>>>>>> >
>>>>>> >>
>>>>>> >>
>>>>>> >>>
>>>>>> >>> On Thu, Feb 13, 2014 at 9:45 AM, Koichi Suzuki <
>>>>>> koi...@gm...>
>>>>>> >>> wrote:
>>>>>> >>>>
>>>>>> >>>> Hi,
>>>>>> >>>>
>>>>>> >>>> I tested the patch and found that primary key is mandatory.   We
>>>>>> need
>>>>>> >>>> to modify regression test considerably to give each replicated
>>>>>> table
>>>>>> >>>> primary keys.
>>>>>> >>>>
>>>>>> >>>> I think this patch helps but I'm not afraid this is good,
>>>>>> especially
>>>>>> >>>> when we try to take XC features back to PG.
>>>>>> >>>>
>>>>>> >>>> Did you post another patch to use all column values if primary
>>>>>> key is
>>>>>> >>>> not available?
>>>>>> >>>>
>>>>>> >>>> I think better way is as follows:
>>>>>> >>>>
>>>>>> >>>> 1) If primary key is defined, use it,
>>>>>> >>>> 2) If not, create a primary key as system column, the size
>>>>>> should be
>>>>>> >>>> 64bit.
>>>>>> >>>> 3) If primary key is added to a replicated table, remove system
>>>>>> primary
>>>>>> >>>> key.
>>>>>> >>>>
>>>>>> >>>> The value of primary key can be obtained as follows:
>>>>>> >>>>
>>>>>> >>>> 1) add new column to pgxc_class catalog to represent maximum
>>>>>> value of
>>>>>> >>>> the system primary key,
>>>>>> >>>> 2) when first "insert" is done to the primary node, system
>>>>>> primary key
>>>>>> >>>> value is taken from 1) and 1) is updated.  The value is returned
>>>>>> to
>>>>>> >>>> the coordinator to be propagated to other nodes.
>>>>>> >>>> 3) when subsequent "insert" is being done, system primary key
>>>>>> value is
>>>>>> >>>> added to the column value.   In this case, each datanode updates
>>>>>> 1)
>>>>>> >>>> column value if it is larger than the current maximum value.
>>>>>> >>>>
>>>>>> >>>> 3) is important to change primary node to another.   This is
>>>>>> needed to
>>>>>> >>>> carry over the primary node to another.
>>>>>> >>>>
>>>>>> >>>> ALTER TABLE should take care of them.
>>>>>> >>>>
>>>>>> >>>> Other issues are:
>>>>>> >>>>
>>>>>> >>>> 4) pg_dump/pg_dumpall should not include this system column
>>>>>> value,
>>>>>> >>>> 5) cluster may need to handle this too to repack system primary
>>>>>> key
>>>>>> >>>> value (not now but at least in 1.3 or later).
>>>>>> >>>>
>>>>>> >>>> Regards;
>>>>>> >>>> ---
>>>>>> >>>> Koichi Suzuki
>>>>>> >>>>
>>>>>> >>>>
>>>>>> >>>> 2013-11-02 9:26 GMT+09:00 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
>>>>>> >>>> >
>>>>>> >>>>
>>>>>> >>>>
>>>>>> >>>>
>>>>>> ------------------------------------------------------------------------------
>>>>>> >>>> Android apps run on BlackBerry 10
>>>>>> >>>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>>>>>> >>>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>>>>>> >>>> Get your Android app in front of a whole new audience.  Start
>>>>>> now.
>>>>>> >>>>
>>>>>> >>>>
>>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>>>>>> >>>> _______________________________________________
>>>>>> >>>> Postgres-xc-developers mailing list
>>>>>> >>>> Pos...@li...
>>>>>> >>>>
>>>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>>> >>>
>>>>>> >>>
>>>>>> >>>
>>>>>> >>>
>>>>>> >>> --
>>>>>> >>> Best Wishes,
>>>>>> >>> Ashutosh Bapat
>>>>>> >>> EnterpriseDB Corporation
>>>>>> >>> The Postgres Database Company
>>>>>> >>>
>>>>>> >>>
>>>>>> >>>
>>>>>> ------------------------------------------------------------------------------
>>>>>> >>> Android apps run on BlackBerry 10
>>>>>> >>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>>>>>> >>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>>>>>> >>> Get your Android app in front of a whole new audience.  Start now.
>>>>>> >>>
>>>>>> >>>
>>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>>>>>> >>> _______________________________________________
>>>>>> >>> Postgres-xc-developers mailing list
>>>>>> >>> Pos...@li...
>>>>>> >>>
>>>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>>> >>>
>>>>>> >>
>>>>>> >>
>>>>>> >>
>>>>>> >>
>>>>>> ------------------------------------------------------------------------------
>>>>>> >> Managing the Performance of Cloud-Based Applications
>>>>>> >> Take advantage of what the Cloud has to offer - Avoid Common
>>>>>> Pitfalls.
>>>>>> >> Read the Whitepaper.
>>>>>> >>
>>>>>> >>
>>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>>>>>> >>
>>>>>> >> _______________________________________________
>>>>>> >> Postgres-xc-developers mailing list
>>>>>> >> Pos...@li...
>>>>>> >>
>>>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>>> >>
>>>>>> >
>>>>>> >
>>>>>> >
>>>>>> > --
>>>>>> > Ahsan Hadi
>>>>>> > Snr Director Product Development
>>>>>> > EnterpriseDB Corporation
>>>>>> > The Enterprise Postgres Company
>>>>>> >
>>>>>> > Phone: +92-51-8358874
>>>>>> > Mobile: +92-333-5162114
>>>>>> >
>>>>>> > Website: www.enterprisedb.com
>>>>>> > EnterpriseDB Blog: http://blogs.enterprisedb.com/
>>>>>> > Follow us on Twitter: http://www.twitter.com/enterprisedb
>>>>>> >
>>>>>> > This e-mail message (and any attachment) is intended for the use of
>>>>>> the
>>>>>> > individual or entity to whom it is addressed. This message contains
>>>>>> > information from EnterpriseDB Corporation that may be privileged,
>>>>>> > confidential, or exempt from disclosure under applicable law. If
>>>>>> you are not
>>>>>> > the intended recipient or authorized to receive this for the
>>>>>> intended
>>>>>> > recipient, any use, dissemination, distribution, retention,
>>>>>> archiving, or
>>>>>> > copying of this communication is strictly prohibited. If you have
>>>>>> received
>>>>>> > this e-mail in error, please notify the sender immediately by reply
>>>>>> e-mail
>>>>>> > and delete this message.
>>>>>> >
>>>>>> >
>>>>>> ------------------------------------------------------------------------------
>>>>>> > Managing the Performance of Cloud-Based Applications
>>>>>> > Take advantage of what the Cloud has to offer - Avoid Common
>>>>>> Pitfalls.
>>>>>> > Read the Whitepaper.
>>>>>> >
>>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>>>>>> > _______________________________________________
>>>>>> > Postgres-xc-developers mailing list
>>>>>> > Pos...@li...
>>>>>> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>>> >
>>>>>>
>>>>>>
>>>>>> ------------------------------------------------------------------------------
>>>>>> Managing the Performance of Cloud-Based Applications
>>>>>> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
>>>>>> Read the Whitepaper.
>>>>>>
>>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>>>>>> _______________________________________________
>>>>>> Postgres-xc-developers mailing list
>>>>>> Pos...@li...
>>>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> --
>>>>> *Abbas*
>>>>>  Architect
>>>>>
>>>>> Ph: 92.334.5100153
>>>>>  Skype ID: gabbasb
>>>>> www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>
>>>>>
>>>>> *Follow us on Twitter*
>>>>> @EnterpriseDB
>>>>>
>>>>> Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community>
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Ahsan Hadi
>>>> Snr Director Product Development
>>>> EnterpriseDB Corporation
>>>> The Enterprise Postgres Company
>>>>
>>>> Phone: +92-51-8358874
>>>> Mobile: +92-333-5162114
>>>>
>>>> Website: www.enterprisedb.com
>>>> EnterpriseDB Blog: http://blogs.enterprisedb.com/
>>>> Follow us on Twitter: http://www.twitter.com/enterprisedb
>>>>
>>>> This e-mail message (and any attachment) is intended for the use of the
>>>> individual or entity to whom it is addressed. This message contains
>>>> information from EnterpriseDB Corporation that may be privileged,
>>>> confidential, or exempt from disclosure under applicable law. If you are
>>>> not the intended recipient or authorized to receive this for the intended
>>>> recipient, any use, dissemination, distribution, retention, archiving, or
>>>> copying of this communication is strictly prohibited. If you have received
>>>> this e-mail in error, please notify the sender immediately by reply e-mail
>>>> and delete this message.
>>>>
>>>
>>>
>>>
>>> --
>>> --
>>> *Abbas*
>>>  Architect
>>>
>>> Ph: 92.334.5100153
>>>  Skype ID: gabbasb
>>> www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>
>>>
>>> *Follow us on Twitter*
>>> @EnterpriseDB
>>>
>>> Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community>
>>>
>>
>>
>>
>> --
>> Ahsan Hadi
>> Snr Director Product Development
>> EnterpriseDB Corporation
>> The Enterprise Postgres Company
>>
>> Phone: +92-51-8358874
>> Mobile: +92-333-5162114
>>
>> Website: www.enterprisedb.com
>> EnterpriseDB Blog: http://blogs.enterprisedb.com/
>> Follow us on Twitter: http://www.twitter.com/enterprisedb
>>
>> This e-mail message (and any attachment) is intended for the use of the
>> individual or entity to whom it is addressed. This message contains
>> information from EnterpriseDB Corporation that may be privileged,
>> confidential, or exempt from disclosure under applicable law. If you are
>> not the intended recipient or authorized to receive this for the intended
>> recipient, any use, dissemination, distribution, retention, archiving, or
>> copying of this communication is strictly prohibited. If you have received
>> this e-mail in error, please notify the sender immediately by reply e-mail
>> and delete this message.
>>
>
>
>
> --
> --
> *Abbas*
>  Architect
>
> Ph: 92.334.5100153
>  Skype ID: gabbasb
> www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>
>
> *Follow us on Twitter*
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community>
>



-- 
-- 
*Abbas*
 Architect

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

*Follow us on Twitter*
@EnterpriseDB

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

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

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

Hi,
Attached please find revised and updated patch that performs
updates/deletes to replicated tables based on either primary key or unique
index under the following conditions
1. The replicated table has either a primary key or a unique index defined.
2. The query is not changing the primary key itself.
Otherwise ctid is used, like we were using previously.

Getting a clean regression with the patch was not easy. Lot of issues had
to be fixed especially with deletes.
Regression and DBT-1 now work cleanly after that patch.
Test cases are added in the regression to make sure the
added functionality is working fine.

The attached patch does not include the cursors approach as of now.
The patch to use cursors in case when primary key cannot be used is not
complete yet. The changes in the planner are done, but the changes in
executor are still to do. The current executor does not work with a fetch
and update plan, and needs some re factoring.

Best Regards




On Mon, Feb 24, 2014 at 10:12 PM, Ahsan Hadi <ahs...@en...>wrote:

> Abbas,
> Please give us an update on progress?
>
>
> On Thu, Feb 20, 2014 at 3:38 PM, Abbas Butt <abb...@en...>wrote:
>
>>
>>
>>
>> On Thu, Feb 20, 2014 at 3:25 PM, Ahsan Hadi <ahs...@en...>wrote:
>>
>>>
>>>
>>>
>>> On Wed, Feb 19, 2014 at 3:56 PM, Abbas Butt <abb...@en...
>>> > wrote:
>>>
>>>> Lets do this
>>>> 1. If the replicated table has a unique key defined , then use Mason's
>>>> patch after testing and refinement if required.
>>>> 2. If the replicated table has no key, then we can use cursors idea
>>>> proposed by Ashutosh.
>>>>
>>>> Does every body agree?
>>>>
>>>
>>> Yes.
>>>
>>> Can you provide a patch for the above before monday?
>>>
>>
>> I will try my best to.
>>
>>
>>>
>>>
>>>>
>>>>
>>>>
>>>> On Wed, Feb 19, 2014 at 12:15 PM, Koichi Suzuki <koi...@gm...>wrote:
>>>>
>>>>> 2014-02-19 16:00 GMT+09:00 Ahsan Hadi <ahs...@en...>:
>>>>> >
>>>>> >
>>>>> >
>>>>> > On Tue, Feb 18, 2014 at 11:26 PM, Amit Khandekar
>>>>> > <ami...@en...> wrote:
>>>>> >>
>>>>> >>
>>>>> >>
>>>>> >>
>>>>> >> On 13 February 2014 11:54, Ashutosh Bapat
>>>>> >> <ash...@en...> wrote:
>>>>> >>>
>>>>> >>> One more solution would be to use cursors for replicated tables.
>>>>> The idea
>>>>> >>> is to open cursors on all the copies of the table and append the
>>>>> query with
>>>>> >>> an ORDER BY clause on all the columns. Thus we are sure that the
>>>>> current of
>>>>> >>> each of these cursors point to same row on all the copies. While
>>>>> fetching a
>>>>> >>> row from a replicated table, we fetch from all the cursors and
>>>>> choose only
>>>>> >>> one row for the data processing. While updating or deleting we
>>>>> send UPDATE
>>>>> >>> or DELETE with WHERE CURRENT OF. The down side of this approach is
>>>>> that, if
>>>>> >>> there are coordinator quals, we will end up locking more rows than
>>>>> >>> necessary, increasing the probability of the deadlock but at least
>>>>> there
>>>>> >>> won't be a necessary restriction of having primary or unique key
>>>>> and we
>>>>> >>> won't break backward compatibility.
>>>>> >>>
>>>>> >>> If there two identical rows, we might mix the update from different
>>>>> >>> nodes, but then who knew which of them were corresponded across
>>>>> the nodes to
>>>>> >>> start with.
>>>>> >>>
>>>>> >>
>>>>> >> Locking all rows doesn't look good especially because we are
>>>>> looking for a
>>>>> >> permanent long term solution. If we can come up with some other
>>>>> solution
>>>>> >> that avoids this, we better avoid this compromise. For a replicated
>>>>> table
>>>>> >> with 10000 rows, all concurrent updates will be serialized even if
>>>>> they are
>>>>> >> updating a different row.
>>>>> >>
>>>>> >> Other thing is datanode performance impact for ORDER BY all columns,
>>>>> >> especially with many large size columns. I had also mentioned about
>>>>> ORDER BY
>>>>> >> in approach A. I am not sure whether there is some kind of
>>>>> optimization in
>>>>> >> the sort, such as: if we find unique rows with the first n columns,
>>>>> it does
>>>>> >> not compare the rest of the columns.
>>>>> >>
>>>>> >> I think declaring cursors is a cool idea in general for DMLs but it
>>>>> >> requires refactoring of DML planning, and also it requires ORDER
>>>>> BY. There
>>>>> >> is a concurrent update issue #398 for which we do require a
>>>>> refactor of DML
>>>>> >> handling. While doing that it will be clearer whether declaring
>>>>> cursor is
>>>>> >> really beneficial or if it's not feasible. For ORDER BY, again, for
>>>>> long
>>>>> >> term, we should have a primary key or an internal unique key so
>>>>> that rows
>>>>> >> can be ordered on that single column as against all columns. So
>>>>> again, we
>>>>> >> still are better of with a new system column.
>>>>> >>
>>>>> >>
>>>>> >> As regards to approach C, if we find a way to uniquely generate a
>>>>> new row
>>>>> >> id independently, then the task of generating rowid will be pretty
>>>>> >> lightweight. We won't require any other table to store it or
>>>>> generate it.
>>>>> >> The coordinator will generate it at each insert (both fqs and
>>>>> non-fqs), or
>>>>> >> may be datanodes themselves find a way to generate a new rowid
>>>>> which is
>>>>> >> always the same regardless of the datanode. A combination of gxid,
>>>>> timestamp
>>>>> >> and cmd id can be used to construct a unique rowid at the
>>>>> coordinator.
>>>>> >>
>>>>> >> I think one action plan can be :
>>>>> >> 1. Use Mason's patch and tweak it so that it needs very little
>>>>> >> modification later on if and when we add the system rowid column.
>>>>> >> 2. Check in the patch but let it not error out if the primary key
>>>>> is not
>>>>> >> there. This way we would at least make the replicated tables with
>>>>> primary
>>>>> >> keys work without data issues, but continue to work as it is now
>>>>> for tables
>>>>> >> without primary key.
>>>>> >> 3. Lastly support the new system row id implementation, and do an
>>>>> >> incremental change in Mason's checked in changes to use this id
>>>>> instead of
>>>>> >> primary key.
>>>>> >>
>>>>> >
>>>>> > Sounds good but as i understand 3 is not targeted for 1.2?
>>>>>
>>>>> I agree on this too.   System column will benefit not only this, but
>>>>> also WCO, more variety of the cursor, and Triggers.   I agree this is
>>>>> targeted to 1.3 or later.
>>>>>
>>>>> Regards;
>>>>> ---
>>>>> Koichi Suzuki
>>>>>
>>>>> >
>>>>> >>
>>>>> >>
>>>>> >>>
>>>>> >>> On Thu, Feb 13, 2014 at 9:45 AM, Koichi Suzuki <
>>>>> koi...@gm...>
>>>>> >>> wrote:
>>>>> >>>>
>>>>> >>>> Hi,
>>>>> >>>>
>>>>> >>>> I tested the patch and found that primary key is mandatory.   We
>>>>> need
>>>>> >>>> to modify regression test considerably to give each replicated
>>>>> table
>>>>> >>>> primary keys.
>>>>> >>>>
>>>>> >>>> I think this patch helps but I'm not afraid this is good,
>>>>> especially
>>>>> >>>> when we try to take XC features back to PG.
>>>>> >>>>
>>>>> >>>> Did you post another patch to use all column values if primary
>>>>> key is
>>>>> >>>> not available?
>>>>> >>>>
>>>>> >>>> I think better way is as follows:
>>>>> >>>>
>>>>> >>>> 1) If primary key is defined, use it,
>>>>> >>>> 2) If not, create a primary key as system column, the size should
>>>>> be
>>>>> >>>> 64bit.
>>>>> >>>> 3) If primary key is added to a replicated table, remove system
>>>>> primary
>>>>> >>>> key.
>>>>> >>>>
>>>>> >>>> The value of primary key can be obtained as follows:
>>>>> >>>>
>>>>> >>>> 1) add new column to pgxc_class catalog to represent maximum
>>>>> value of
>>>>> >>>> the system primary key,
>>>>> >>>> 2) when first "insert" is done to the primary node, system
>>>>> primary key
>>>>> >>>> value is taken from 1) and 1) is updated.  The value is returned
>>>>> to
>>>>> >>>> the coordinator to be propagated to other nodes.
>>>>> >>>> 3) when subsequent "insert" is being done, system primary key
>>>>> value is
>>>>> >>>> added to the column value.   In this case, each datanode updates
>>>>> 1)
>>>>> >>>> column value if it is larger than the current maximum value.
>>>>> >>>>
>>>>> >>>> 3) is important to change primary node to another.   This is
>>>>> needed to
>>>>> >>>> carry over the primary node to another.
>>>>> >>>>
>>>>> >>>> ALTER TABLE should take care of them.
>>>>> >>>>
>>>>> >>>> Other issues are:
>>>>> >>>>
>>>>> >>>> 4) pg_dump/pg_dumpall should not include this system column value,
>>>>> >>>> 5) cluster may need to handle this too to repack system primary
>>>>> key
>>>>> >>>> value (not now but at least in 1.3 or later).
>>>>> >>>>
>>>>> >>>> Regards;
>>>>> >>>> ---
>>>>> >>>> Koichi Suzuki
>>>>> >>>>
>>>>> >>>>
>>>>> >>>> 2013-11-02 9:26 GMT+09:00 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
>>>>> >>>> >
>>>>> >>>>
>>>>> >>>>
>>>>> >>>>
>>>>> ------------------------------------------------------------------------------
>>>>> >>>> Android apps run on BlackBerry 10
>>>>> >>>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>>>>> >>>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>>>>> >>>> Get your Android app in front of a whole new audience.  Start now.
>>>>> >>>>
>>>>> >>>>
>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>>>>> >>>> _______________________________________________
>>>>> >>>> Postgres-xc-developers mailing list
>>>>> >>>> Pos...@li...
>>>>> >>>>
>>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>> >>>
>>>>> >>>
>>>>> >>>
>>>>> >>>
>>>>> >>> --
>>>>> >>> Best Wishes,
>>>>> >>> Ashutosh Bapat
>>>>> >>> EnterpriseDB Corporation
>>>>> >>> The Postgres Database Company
>>>>> >>>
>>>>> >>>
>>>>> >>>
>>>>> ------------------------------------------------------------------------------
>>>>> >>> Android apps run on BlackBerry 10
>>>>> >>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>>>>> >>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>>>>> >>> Get your Android app in front of a whole new audience.  Start now.
>>>>> >>>
>>>>> >>>
>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>>>>> >>> _______________________________________________
>>>>> >>> Postgres-xc-developers mailing list
>>>>> >>> Pos...@li...
>>>>> >>>
>>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>> >>>
>>>>> >>
>>>>> >>
>>>>> >>
>>>>> >>
>>>>> ------------------------------------------------------------------------------
>>>>> >> Managing the Performance of Cloud-Based Applications
>>>>> >> Take advantage of what the Cloud has to offer - Avoid Common
>>>>> Pitfalls.
>>>>> >> Read the Whitepaper.
>>>>> >>
>>>>> >>
>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>>>>> >>
>>>>> >> _______________________________________________
>>>>> >> Postgres-xc-developers mailing list
>>>>> >> Pos...@li...
>>>>> >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>> >>
>>>>> >
>>>>> >
>>>>> >
>>>>> > --
>>>>> > Ahsan Hadi
>>>>> > Snr Director Product Development
>>>>> > EnterpriseDB Corporation
>>>>> > The Enterprise Postgres Company
>>>>> >
>>>>> > Phone: +92-51-8358874
>>>>> > Mobile: +92-333-5162114
>>>>> >
>>>>> > Website: www.enterprisedb.com
>>>>> > EnterpriseDB Blog: http://blogs.enterprisedb.com/
>>>>> > Follow us on Twitter: http://www.twitter.com/enterprisedb
>>>>> >
>>>>> > This e-mail message (and any attachment) is intended for the use of
>>>>> the
>>>>> > individual or entity to whom it is addressed. This message contains
>>>>> > information from EnterpriseDB Corporation that may be privileged,
>>>>> > confidential, or exempt from disclosure under applicable law. If you
>>>>> are not
>>>>> > the intended recipient or authorized to receive this for the intended
>>>>> > recipient, any use, dissemination, distribution, retention,
>>>>> archiving, or
>>>>> > copying of this communication is strictly prohibited. If you have
>>>>> received
>>>>> > this e-mail in error, please notify the sender immediately by reply
>>>>> e-mail
>>>>> > and delete this message.
>>>>> >
>>>>> >
>>>>> ------------------------------------------------------------------------------
>>>>> > Managing the Performance of Cloud-Based Applications
>>>>> > Take advantage of what the Cloud has to offer - Avoid Common
>>>>> Pitfalls.
>>>>> > Read the Whitepaper.
>>>>> >
>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>>>>> > _______________________________________________
>>>>> > Postgres-xc-developers mailing list
>>>>> > Pos...@li...
>>>>> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>> >
>>>>>
>>>>>
>>>>> ------------------------------------------------------------------------------
>>>>> Managing the Performance of Cloud-Based Applications
>>>>> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
>>>>> Read the Whitepaper.
>>>>>
>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>>>>> _______________________________________________
>>>>> Postgres-xc-developers mailing list
>>>>> Pos...@li...
>>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> --
>>>> *Abbas*
>>>>  Architect
>>>>
>>>> Ph: 92.334.5100153
>>>>  Skype ID: gabbasb
>>>> www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>
>>>>
>>>> *Follow us on Twitter*
>>>> @EnterpriseDB
>>>>
>>>> Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community>
>>>>
>>>
>>>
>>>
>>> --
>>> Ahsan Hadi
>>> Snr Director Product Development
>>> EnterpriseDB Corporation
>>> The Enterprise Postgres Company
>>>
>>> Phone: +92-51-8358874
>>> Mobile: +92-333-5162114
>>>
>>> Website: www.enterprisedb.com
>>> EnterpriseDB Blog: http://blogs.enterprisedb.com/
>>> Follow us on Twitter: http://www.twitter.com/enterprisedb
>>>
>>> This e-mail message (and any attachment) is intended for the use of the
>>> individual or entity to whom it is addressed. This message contains
>>> information from EnterpriseDB Corporation that may be privileged,
>>> confidential, or exempt from disclosure under applicable law. If you are
>>> not the intended recipient or authorized to receive this for the intended
>>> recipient, any use, dissemination, distribution, retention, archiving, or
>>> copying of this communication is strictly prohibited. If you have received
>>> this e-mail in error, please notify the sender immediately by reply e-mail
>>> and delete this message.
>>>
>>
>>
>>
>> --
>> --
>> *Abbas*
>>  Architect
>>
>> Ph: 92.334.5100153
>>  Skype ID: gabbasb
>> www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>
>>
>> *Follow us on Twitter*
>> @EnterpriseDB
>>
>> Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community>
>>
>
>
>
> --
> Ahsan Hadi
> Snr Director Product Development
> EnterpriseDB Corporation
> The Enterprise Postgres Company
>
> Phone: +92-51-8358874
> Mobile: +92-333-5162114
>
> Website: www.enterprisedb.com
> EnterpriseDB Blog: http://blogs.enterprisedb.com/
> Follow us on Twitter: http://www.twitter.com/enterprisedb
>
> This e-mail message (and any attachment) is intended for the use of the
> individual or entity to whom it is addressed. This message contains
> information from EnterpriseDB Corporation that may be privileged,
> confidential, or exempt from disclosure under applicable law. If you are
> not the intended recipient or authorized to receive this for the intended
> recipient, any use, dissemination, distribution, retention, archiving, or
> copying of this communication is strictly prohibited. If you have received
> this e-mail in error, please notify the sender immediately by reply e-mail
> and delete this message.
>



-- 
-- 
*Abbas*
 Architect

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

*Follow us on Twitter*
@EnterpriseDB

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

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

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

Abbas,
Please give us an update on progress?


On Thu, Feb 20, 2014 at 3:38 PM, Abbas Butt <abb...@en...>wrote:

>
>
>
> On Thu, Feb 20, 2014 at 3:25 PM, Ahsan Hadi <ahs...@en...>wrote:
>
>>
>>
>>
>> On Wed, Feb 19, 2014 at 3:56 PM, Abbas Butt <abb...@en...>wrote:
>>
>>> Lets do this
>>> 1. If the replicated table has a unique key defined , then use Mason's
>>> patch after testing and refinement if required.
>>> 2. If the replicated table has no key, then we can use cursors idea
>>> proposed by Ashutosh.
>>>
>>> Does every body agree?
>>>
>>
>> Yes.
>>
>> Can you provide a patch for the above before monday?
>>
>
> I will try my best to.
>
>
>>
>>
>>>
>>>
>>>
>>> On Wed, Feb 19, 2014 at 12:15 PM, Koichi Suzuki <koi...@gm...>wrote:
>>>
>>>> 2014-02-19 16:00 GMT+09:00 Ahsan Hadi <ahs...@en...>:
>>>> >
>>>> >
>>>> >
>>>> > On Tue, Feb 18, 2014 at 11:26 PM, Amit Khandekar
>>>> > <ami...@en...> wrote:
>>>> >>
>>>> >>
>>>> >>
>>>> >>
>>>> >> On 13 February 2014 11:54, Ashutosh Bapat
>>>> >> <ash...@en...> wrote:
>>>> >>>
>>>> >>> One more solution would be to use cursors for replicated tables.
>>>> The idea
>>>> >>> is to open cursors on all the copies of the table and append the
>>>> query with
>>>> >>> an ORDER BY clause on all the columns. Thus we are sure that the
>>>> current of
>>>> >>> each of these cursors point to same row on all the copies. While
>>>> fetching a
>>>> >>> row from a replicated table, we fetch from all the cursors and
>>>> choose only
>>>> >>> one row for the data processing. While updating or deleting we send
>>>> UPDATE
>>>> >>> or DELETE with WHERE CURRENT OF. The down side of this approach is
>>>> that, if
>>>> >>> there are coordinator quals, we will end up locking more rows than
>>>> >>> necessary, increasing the probability of the deadlock but at least
>>>> there
>>>> >>> won't be a necessary restriction of having primary or unique key
>>>> and we
>>>> >>> won't break backward compatibility.
>>>> >>>
>>>> >>> If there two identical rows, we might mix the update from different
>>>> >>> nodes, but then who knew which of them were corresponded across the
>>>> nodes to
>>>> >>> start with.
>>>> >>>
>>>> >>
>>>> >> Locking all rows doesn't look good especially because we are looking
>>>> for a
>>>> >> permanent long term solution. If we can come up with some other
>>>> solution
>>>> >> that avoids this, we better avoid this compromise. For a replicated
>>>> table
>>>> >> with 10000 rows, all concurrent updates will be serialized even if
>>>> they are
>>>> >> updating a different row.
>>>> >>
>>>> >> Other thing is datanode performance impact for ORDER BY all columns,
>>>> >> especially with many large size columns. I had also mentioned about
>>>> ORDER BY
>>>> >> in approach A. I am not sure whether there is some kind of
>>>> optimization in
>>>> >> the sort, such as: if we find unique rows with the first n columns,
>>>> it does
>>>> >> not compare the rest of the columns.
>>>> >>
>>>> >> I think declaring cursors is a cool idea in general for DMLs but it
>>>> >> requires refactoring of DML planning, and also it requires ORDER BY.
>>>> There
>>>> >> is a concurrent update issue #398 for which we do require a refactor
>>>> of DML
>>>> >> handling. While doing that it will be clearer whether declaring
>>>> cursor is
>>>> >> really beneficial or if it's not feasible. For ORDER BY, again, for
>>>> long
>>>> >> term, we should have a primary key or an internal unique key so that
>>>> rows
>>>> >> can be ordered on that single column as against all columns. So
>>>> again, we
>>>> >> still are better of with a new system column.
>>>> >>
>>>> >>
>>>> >> As regards to approach C, if we find a way to uniquely generate a
>>>> new row
>>>> >> id independently, then the task of generating rowid will be pretty
>>>> >> lightweight. We won't require any other table to store it or
>>>> generate it.
>>>> >> The coordinator will generate it at each insert (both fqs and
>>>> non-fqs), or
>>>> >> may be datanodes themselves find a way to generate a new rowid which
>>>> is
>>>> >> always the same regardless of the datanode. A combination of gxid,
>>>> timestamp
>>>> >> and cmd id can be used to construct a unique rowid at the
>>>> coordinator.
>>>> >>
>>>> >> I think one action plan can be :
>>>> >> 1. Use Mason's patch and tweak it so that it needs very little
>>>> >> modification later on if and when we add the system rowid column.
>>>> >> 2. Check in the patch but let it not error out if the primary key is
>>>> not
>>>> >> there. This way we would at least make the replicated tables with
>>>> primary
>>>> >> keys work without data issues, but continue to work as it is now for
>>>> tables
>>>> >> without primary key.
>>>> >> 3. Lastly support the new system row id implementation, and do an
>>>> >> incremental change in Mason's checked in changes to use this id
>>>> instead of
>>>> >> primary key.
>>>> >>
>>>> >
>>>> > Sounds good but as i understand 3 is not targeted for 1.2?
>>>>
>>>> I agree on this too.   System column will benefit not only this, but
>>>> also WCO, more variety of the cursor, and Triggers.   I agree this is
>>>> targeted to 1.3 or later.
>>>>
>>>> Regards;
>>>> ---
>>>> Koichi Suzuki
>>>>
>>>> >
>>>> >>
>>>> >>
>>>> >>>
>>>> >>> On Thu, Feb 13, 2014 at 9:45 AM, Koichi Suzuki <
>>>> koi...@gm...>
>>>> >>> wrote:
>>>> >>>>
>>>> >>>> Hi,
>>>> >>>>
>>>> >>>> I tested the patch and found that primary key is mandatory.   We
>>>> need
>>>> >>>> to modify regression test considerably to give each replicated
>>>> table
>>>> >>>> primary keys.
>>>> >>>>
>>>> >>>> I think this patch helps but I'm not afraid this is good,
>>>> especially
>>>> >>>> when we try to take XC features back to PG.
>>>> >>>>
>>>> >>>> Did you post another patch to use all column values if primary key
>>>> is
>>>> >>>> not available?
>>>> >>>>
>>>> >>>> I think better way is as follows:
>>>> >>>>
>>>> >>>> 1) If primary key is defined, use it,
>>>> >>>> 2) If not, create a primary key as system column, the size should
>>>> be
>>>> >>>> 64bit.
>>>> >>>> 3) If primary key is added to a replicated table, remove system
>>>> primary
>>>> >>>> key.
>>>> >>>>
>>>> >>>> The value of primary key can be obtained as follows:
>>>> >>>>
>>>> >>>> 1) add new column to pgxc_class catalog to represent maximum value
>>>> of
>>>> >>>> the system primary key,
>>>> >>>> 2) when first "insert" is done to the primary node, system primary
>>>> key
>>>> >>>> value is taken from 1) and 1) is updated.  The value is returned to
>>>> >>>> the coordinator to be propagated to other nodes.
>>>> >>>> 3) when subsequent "insert" is being done, system primary key
>>>> value is
>>>> >>>> added to the column value.   In this case, each datanode updates 1)
>>>> >>>> column value if it is larger than the current maximum value.
>>>> >>>>
>>>> >>>> 3) is important to change primary node to another.   This is
>>>> needed to
>>>> >>>> carry over the primary node to another.
>>>> >>>>
>>>> >>>> ALTER TABLE should take care of them.
>>>> >>>>
>>>> >>>> Other issues are:
>>>> >>>>
>>>> >>>> 4) pg_dump/pg_dumpall should not include this system column value,
>>>> >>>> 5) cluster may need to handle this too to repack system primary key
>>>> >>>> value (not now but at least in 1.3 or later).
>>>> >>>>
>>>> >>>> Regards;
>>>> >>>> ---
>>>> >>>> Koichi Suzuki
>>>> >>>>
>>>> >>>>
>>>> >>>> 2013-11-02 9:26 GMT+09:00 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
>>>> >>>> >
>>>> >>>>
>>>> >>>>
>>>> >>>>
>>>> ------------------------------------------------------------------------------
>>>> >>>> Android apps run on BlackBerry 10
>>>> >>>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>>>> >>>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>>>> >>>> Get your Android app in front of a whole new audience.  Start now.
>>>> >>>>
>>>> >>>>
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>>>> >>>> _______________________________________________
>>>> >>>> Postgres-xc-developers mailing list
>>>> >>>> Pos...@li...
>>>> >>>>
>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>> >>>
>>>> >>>
>>>> >>>
>>>> >>>
>>>> >>> --
>>>> >>> Best Wishes,
>>>> >>> Ashutosh Bapat
>>>> >>> EnterpriseDB Corporation
>>>> >>> The Postgres Database Company
>>>> >>>
>>>> >>>
>>>> >>>
>>>> ------------------------------------------------------------------------------
>>>> >>> Android apps run on BlackBerry 10
>>>> >>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>>>> >>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>>>> >>> Get your Android app in front of a whole new audience.  Start now.
>>>> >>>
>>>> >>>
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>>>> >>> _______________________________________________
>>>> >>> Postgres-xc-developers mailing list
>>>> >>> Pos...@li...
>>>> >>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>> >>>
>>>> >>
>>>> >>
>>>> >>
>>>> >>
>>>> ------------------------------------------------------------------------------
>>>> >> Managing the Performance of Cloud-Based Applications
>>>> >> Take advantage of what the Cloud has to offer - Avoid Common
>>>> Pitfalls.
>>>> >> Read the Whitepaper.
>>>> >>
>>>> >>
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>>>> >>
>>>> >> _______________________________________________
>>>> >> Postgres-xc-developers mailing list
>>>> >> Pos...@li...
>>>> >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>> >>
>>>> >
>>>> >
>>>> >
>>>> > --
>>>> > Ahsan Hadi
>>>> > Snr Director Product Development
>>>> > EnterpriseDB Corporation
>>>> > The Enterprise Postgres Company
>>>> >
>>>> > Phone: +92-51-8358874
>>>> > Mobile: +92-333-5162114
>>>> >
>>>> > Website: www.enterprisedb.com
>>>> > EnterpriseDB Blog: http://blogs.enterprisedb.com/
>>>> > Follow us on Twitter: http://www.twitter.com/enterprisedb
>>>> >
>>>> > This e-mail message (and any attachment) is intended for the use of
>>>> the
>>>> > individual or entity to whom it is addressed. This message contains
>>>> > information from EnterpriseDB Corporation that may be privileged,
>>>> > confidential, or exempt from disclosure under applicable law. If you
>>>> are not
>>>> > the intended recipient or authorized to receive this for the intended
>>>> > recipient, any use, dissemination, distribution, retention,
>>>> archiving, or
>>>> > copying of this communication is strictly prohibited. If you have
>>>> received
>>>> > this e-mail in error, please notify the sender immediately by reply
>>>> e-mail
>>>> > and delete this message.
>>>> >
>>>> >
>>>> ------------------------------------------------------------------------------
>>>> > Managing the Performance of Cloud-Based Applications
>>>> > Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
>>>> > Read the Whitepaper.
>>>> >
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>>>> > _______________________________________________
>>>> > Postgres-xc-developers mailing list
>>>> > Pos...@li...
>>>> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>> >
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> Managing the Performance of Cloud-Based Applications
>>>> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
>>>> Read the Whitepaper.
>>>>
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>>>> _______________________________________________
>>>> Postgres-xc-developers mailing list
>>>> Pos...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>
>>>
>>>
>>>
>>> --
>>> --
>>> *Abbas*
>>>  Architect
>>>
>>> Ph: 92.334.5100153
>>>  Skype ID: gabbasb
>>> www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>
>>>
>>> *Follow us on Twitter*
>>> @EnterpriseDB
>>>
>>> Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community>
>>>
>>
>>
>>
>> --
>> Ahsan Hadi
>> Snr Director Product Development
>> EnterpriseDB Corporation
>> The Enterprise Postgres Company
>>
>> Phone: +92-51-8358874
>> Mobile: +92-333-5162114
>>
>> Website: www.enterprisedb.com
>> EnterpriseDB Blog: http://blogs.enterprisedb.com/
>> Follow us on Twitter: http://www.twitter.com/enterprisedb
>>
>> This e-mail message (and any attachment) is intended for the use of the
>> individual or entity to whom it is addressed. This message contains
>> information from EnterpriseDB Corporation that may be privileged,
>> confidential, or exempt from disclosure under applicable law. If you are
>> not the intended recipient or authorized to receive this for the intended
>> recipient, any use, dissemination, distribution, retention, archiving, or
>> copying of this communication is strictly prohibited. If you have received
>> this e-mail in error, please notify the sender immediately by reply e-mail
>> and delete this message.
>>
>
>
>
> --
> --
> *Abbas*
>  Architect
>
> Ph: 92.334.5100153
>  Skype ID: gabbasb
> www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>
>
> *Follow us on Twitter*
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community>
>



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

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

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

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

[Postgres-xc-developers] pgbench of Postgres-XC 1.2 is broken.

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

Yesterday I sent below mail from gmail.
But it seems the mail does not send properly, I send it again.
------
Hi,

I've noticed that pgbench included in Postgres-XC 1.2 is
broken and I can not work it fine.

[compiling]
pgbench.c: In function 'init':
pgbench.c:1530: warning: excess elements in struct initializer
pgbench.c:1530: warning: (near initialization for 'DDLs[0]')
pgbench.c:1532: warning: excess elements in struct initializer
pgbench.c:1532: warning: (near initialization for 'DDLs[0]')

Above messages are printed.

[initializing]
$ pgbench -i bench
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
NOTICE: table "pgbench_accounts" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.27 s, remaining 0.00 s).
vacuum...
ERROR: relation "pgbench_history" does not exist


I could not create pgbench_history table.

I attached a patch to fix this problem.
Please fix by formal relase of Postgres-XC 1.2.

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

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

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

On Thu, Feb 20, 2014 at 3:25 PM, Ahsan Hadi <ahs...@en...>wrote:

>
>
>
> On Wed, Feb 19, 2014 at 3:56 PM, Abbas Butt <abb...@en...>wrote:
>
>> Lets do this
>> 1. If the replicated table has a unique key defined , then use Mason's
>> patch after testing and refinement if required.
>> 2. If the replicated table has no key, then we can use cursors idea
>> proposed by Ashutosh.
>>
>> Does every body agree?
>>
>
> Yes.
>
> Can you provide a patch for the above before monday?
>

I will try my best to.


>
>
>>
>>
>>
>> On Wed, Feb 19, 2014 at 12:15 PM, Koichi Suzuki <koi...@gm...>wrote:
>>
>>> 2014-02-19 16:00 GMT+09:00 Ahsan Hadi <ahs...@en...>:
>>> >
>>> >
>>> >
>>> > On Tue, Feb 18, 2014 at 11:26 PM, Amit Khandekar
>>> > <ami...@en...> wrote:
>>> >>
>>> >>
>>> >>
>>> >>
>>> >> On 13 February 2014 11:54, Ashutosh Bapat
>>> >> <ash...@en...> wrote:
>>> >>>
>>> >>> One more solution would be to use cursors for replicated tables. The
>>> idea
>>> >>> is to open cursors on all the copies of the table and append the
>>> query with
>>> >>> an ORDER BY clause on all the columns. Thus we are sure that the
>>> current of
>>> >>> each of these cursors point to same row on all the copies. While
>>> fetching a
>>> >>> row from a replicated table, we fetch from all the cursors and
>>> choose only
>>> >>> one row for the data processing. While updating or deleting we send
>>> UPDATE
>>> >>> or DELETE with WHERE CURRENT OF. The down side of this approach is
>>> that, if
>>> >>> there are coordinator quals, we will end up locking more rows than
>>> >>> necessary, increasing the probability of the deadlock but at least
>>> there
>>> >>> won't be a necessary restriction of having primary or unique key and
>>> we
>>> >>> won't break backward compatibility.
>>> >>>
>>> >>> If there two identical rows, we might mix the update from different
>>> >>> nodes, but then who knew which of them were corresponded across the
>>> nodes to
>>> >>> start with.
>>> >>>
>>> >>
>>> >> Locking all rows doesn't look good especially because we are looking
>>> for a
>>> >> permanent long term solution. If we can come up with some other
>>> solution
>>> >> that avoids this, we better avoid this compromise. For a replicated
>>> table
>>> >> with 10000 rows, all concurrent updates will be serialized even if
>>> they are
>>> >> updating a different row.
>>> >>
>>> >> Other thing is datanode performance impact for ORDER BY all columns,
>>> >> especially with many large size columns. I had also mentioned about
>>> ORDER BY
>>> >> in approach A. I am not sure whether there is some kind of
>>> optimization in
>>> >> the sort, such as: if we find unique rows with the first n columns,
>>> it does
>>> >> not compare the rest of the columns.
>>> >>
>>> >> I think declaring cursors is a cool idea in general for DMLs but it
>>> >> requires refactoring of DML planning, and also it requires ORDER BY.
>>> There
>>> >> is a concurrent update issue #398 for which we do require a refactor
>>> of DML
>>> >> handling. While doing that it will be clearer whether declaring
>>> cursor is
>>> >> really beneficial or if it's not feasible. For ORDER BY, again, for
>>> long
>>> >> term, we should have a primary key or an internal unique key so that
>>> rows
>>> >> can be ordered on that single column as against all columns. So
>>> again, we
>>> >> still are better of with a new system column.
>>> >>
>>> >>
>>> >> As regards to approach C, if we find a way to uniquely generate a new
>>> row
>>> >> id independently, then the task of generating rowid will be pretty
>>> >> lightweight. We won't require any other table to store it or generate
>>> it.
>>> >> The coordinator will generate it at each insert (both fqs and
>>> non-fqs), or
>>> >> may be datanodes themselves find a way to generate a new rowid which
>>> is
>>> >> always the same regardless of the datanode. A combination of gxid,
>>> timestamp
>>> >> and cmd id can be used to construct a unique rowid at the coordinator.
>>> >>
>>> >> I think one action plan can be :
>>> >> 1. Use Mason's patch and tweak it so that it needs very little
>>> >> modification later on if and when we add the system rowid column.
>>> >> 2. Check in the patch but let it not error out if the primary key is
>>> not
>>> >> there. This way we would at least make the replicated tables with
>>> primary
>>> >> keys work without data issues, but continue to work as it is now for
>>> tables
>>> >> without primary key.
>>> >> 3. Lastly support the new system row id implementation, and do an
>>> >> incremental change in Mason's checked in changes to use this id
>>> instead of
>>> >> primary key.
>>> >>
>>> >
>>> > Sounds good but as i understand 3 is not targeted for 1.2?
>>>
>>> I agree on this too.   System column will benefit not only this, but
>>> also WCO, more variety of the cursor, and Triggers.   I agree this is
>>> targeted to 1.3 or later.
>>>
>>> Regards;
>>> ---
>>> Koichi Suzuki
>>>
>>> >
>>> >>
>>> >>
>>> >>>
>>> >>> On Thu, Feb 13, 2014 at 9:45 AM, Koichi Suzuki <
>>> koi...@gm...>
>>> >>> wrote:
>>> >>>>
>>> >>>> Hi,
>>> >>>>
>>> >>>> I tested the patch and found that primary key is mandatory.   We
>>> need
>>> >>>> to modify regression test considerably to give each replicated table
>>> >>>> primary keys.
>>> >>>>
>>> >>>> I think this patch helps but I'm not afraid this is good, especially
>>> >>>> when we try to take XC features back to PG.
>>> >>>>
>>> >>>> Did you post another patch to use all column values if primary key
>>> is
>>> >>>> not available?
>>> >>>>
>>> >>>> I think better way is as follows:
>>> >>>>
>>> >>>> 1) If primary key is defined, use it,
>>> >>>> 2) If not, create a primary key as system column, the size should be
>>> >>>> 64bit.
>>> >>>> 3) If primary key is added to a replicated table, remove system
>>> primary
>>> >>>> key.
>>> >>>>
>>> >>>> The value of primary key can be obtained as follows:
>>> >>>>
>>> >>>> 1) add new column to pgxc_class catalog to represent maximum value
>>> of
>>> >>>> the system primary key,
>>> >>>> 2) when first "insert" is done to the primary node, system primary
>>> key
>>> >>>> value is taken from 1) and 1) is updated.  The value is returned to
>>> >>>> the coordinator to be propagated to other nodes.
>>> >>>> 3) when subsequent "insert" is being done, system primary key value
>>> is
>>> >>>> added to the column value.   In this case, each datanode updates 1)
>>> >>>> column value if it is larger than the current maximum value.
>>> >>>>
>>> >>>> 3) is important to change primary node to another.   This is needed
>>> to
>>> >>>> carry over the primary node to another.
>>> >>>>
>>> >>>> ALTER TABLE should take care of them.
>>> >>>>
>>> >>>> Other issues are:
>>> >>>>
>>> >>>> 4) pg_dump/pg_dumpall should not include this system column value,
>>> >>>> 5) cluster may need to handle this too to repack system primary key
>>> >>>> value (not now but at least in 1.3 or later).
>>> >>>>
>>> >>>> Regards;
>>> >>>> ---
>>> >>>> Koichi Suzuki
>>> >>>>
>>> >>>>
>>> >>>> 2013-11-02 9:26 GMT+09:00 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
>>> >>>> >
>>> >>>>
>>> >>>>
>>> >>>>
>>> ------------------------------------------------------------------------------
>>> >>>> Android apps run on BlackBerry 10
>>> >>>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>>> >>>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>>> >>>> Get your Android app in front of a whole new audience.  Start now.
>>> >>>>
>>> >>>>
>>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>>> >>>> _______________________________________________
>>> >>>> Postgres-xc-developers mailing list
>>> >>>> Pos...@li...
>>> >>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>> >>>
>>> >>>
>>> >>>
>>> >>>
>>> >>> --
>>> >>> Best Wishes,
>>> >>> Ashutosh Bapat
>>> >>> EnterpriseDB Corporation
>>> >>> The Postgres Database Company
>>> >>>
>>> >>>
>>> >>>
>>> ------------------------------------------------------------------------------
>>> >>> Android apps run on BlackBerry 10
>>> >>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>>> >>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>>> >>> Get your Android app in front of a whole new audience.  Start now.
>>> >>>
>>> >>>
>>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>>> >>> _______________________________________________
>>> >>> Postgres-xc-developers mailing list
>>> >>> Pos...@li...
>>> >>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>> >>>
>>> >>
>>> >>
>>> >>
>>> >>
>>> ------------------------------------------------------------------------------
>>> >> Managing the Performance of Cloud-Based Applications
>>> >> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
>>> >> Read the Whitepaper.
>>> >>
>>> >>
>>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>>> >>
>>> >> _______________________________________________
>>> >> Postgres-xc-developers mailing list
>>> >> Pos...@li...
>>> >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>> >>
>>> >
>>> >
>>> >
>>> > --
>>> > Ahsan Hadi
>>> > Snr Director Product Development
>>> > EnterpriseDB Corporation
>>> > The Enterprise Postgres Company
>>> >
>>> > Phone: +92-51-8358874
>>> > Mobile: +92-333-5162114
>>> >
>>> > Website: www.enterprisedb.com
>>> > EnterpriseDB Blog: http://blogs.enterprisedb.com/
>>> > Follow us on Twitter: http://www.twitter.com/enterprisedb
>>> >
>>> > This e-mail message (and any attachment) is intended for the use of the
>>> > individual or entity to whom it is addressed. This message contains
>>> > information from EnterpriseDB Corporation that may be privileged,
>>> > confidential, or exempt from disclosure under applicable law. If you
>>> are not
>>> > the intended recipient or authorized to receive this for the intended
>>> > recipient, any use, dissemination, distribution, retention, archiving,
>>> or
>>> > copying of this communication is strictly prohibited. If you have
>>> received
>>> > this e-mail in error, please notify the sender immediately by reply
>>> e-mail
>>> > and delete this message.
>>> >
>>> >
>>> ------------------------------------------------------------------------------
>>> > Managing the Performance of Cloud-Based Applications
>>> > Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
>>> > Read the Whitepaper.
>>> >
>>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>>> > _______________________________________________
>>> > Postgres-xc-developers mailing list
>>> > Pos...@li...
>>> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>> >
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Managing the Performance of Cloud-Based Applications
>>> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
>>> Read the Whitepaper.
>>>
>>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>>> _______________________________________________
>>> Postgres-xc-developers mailing list
>>> Pos...@li...
>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>
>>
>>
>>
>> --
>> --
>> *Abbas*
>>  Architect
>>
>> Ph: 92.334.5100153
>>  Skype ID: gabbasb
>> www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>
>>
>> *Follow us on Twitter*
>> @EnterpriseDB
>>
>> Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community>
>>
>
>
>
> --
> Ahsan Hadi
> Snr Director Product Development
> EnterpriseDB Corporation
> The Enterprise Postgres Company
>
> Phone: +92-51-8358874
> Mobile: +92-333-5162114
>
> Website: www.enterprisedb.com
> EnterpriseDB Blog: http://blogs.enterprisedb.com/
> Follow us on Twitter: http://www.twitter.com/enterprisedb
>
> This e-mail message (and any attachment) is intended for the use of the
> individual or entity to whom it is addressed. This message contains
> information from EnterpriseDB Corporation that may be privileged,
> confidential, or exempt from disclosure under applicable law. If you are
> not the intended recipient or authorized to receive this for the intended
> recipient, any use, dissemination, distribution, retention, archiving, or
> copying of this communication is strictly prohibited. If you have received
> this e-mail in error, please notify the sender immediately by reply e-mail
> and delete this message.
>



-- 
-- 
*Abbas*
 Architect

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

*Follow us on Twitter*
@EnterpriseDB

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

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

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

On Wed, Feb 19, 2014 at 3:56 PM, Abbas Butt <abb...@en...>wrote:

> Lets do this
> 1. If the replicated table has a unique key defined , then use Mason's
> patch after testing and refinement if required.
> 2. If the replicated table has no key, then we can use cursors idea
> proposed by Ashutosh.
>
> Does every body agree?
>

Yes.

Can you provide a patch for the above before monday?


>
>
>
> On Wed, Feb 19, 2014 at 12:15 PM, Koichi Suzuki <koi...@gm...>wrote:
>
>> 2014-02-19 16:00 GMT+09:00 Ahsan Hadi <ahs...@en...>:
>> >
>> >
>> >
>> > On Tue, Feb 18, 2014 at 11:26 PM, Amit Khandekar
>> > <ami...@en...> wrote:
>> >>
>> >>
>> >>
>> >>
>> >> On 13 February 2014 11:54, Ashutosh Bapat
>> >> <ash...@en...> wrote:
>> >>>
>> >>> One more solution would be to use cursors for replicated tables. The
>> idea
>> >>> is to open cursors on all the copies of the table and append the
>> query with
>> >>> an ORDER BY clause on all the columns. Thus we are sure that the
>> current of
>> >>> each of these cursors point to same row on all the copies. While
>> fetching a
>> >>> row from a replicated table, we fetch from all the cursors and choose
>> only
>> >>> one row for the data processing. While updating or deleting we send
>> UPDATE
>> >>> or DELETE with WHERE CURRENT OF. The down side of this approach is
>> that, if
>> >>> there are coordinator quals, we will end up locking more rows than
>> >>> necessary, increasing the probability of the deadlock but at least
>> there
>> >>> won't be a necessary restriction of having primary or unique key and
>> we
>> >>> won't break backward compatibility.
>> >>>
>> >>> If there two identical rows, we might mix the update from different
>> >>> nodes, but then who knew which of them were corresponded across the
>> nodes to
>> >>> start with.
>> >>>
>> >>
>> >> Locking all rows doesn't look good especially because we are looking
>> for a
>> >> permanent long term solution. If we can come up with some other
>> solution
>> >> that avoids this, we better avoid this compromise. For a replicated
>> table
>> >> with 10000 rows, all concurrent updates will be serialized even if
>> they are
>> >> updating a different row.
>> >>
>> >> Other thing is datanode performance impact for ORDER BY all columns,
>> >> especially with many large size columns. I had also mentioned about
>> ORDER BY
>> >> in approach A. I am not sure whether there is some kind of
>> optimization in
>> >> the sort, such as: if we find unique rows with the first n columns, it
>> does
>> >> not compare the rest of the columns.
>> >>
>> >> I think declaring cursors is a cool idea in general for DMLs but it
>> >> requires refactoring of DML planning, and also it requires ORDER BY.
>> There
>> >> is a concurrent update issue #398 for which we do require a refactor
>> of DML
>> >> handling. While doing that it will be clearer whether declaring cursor
>> is
>> >> really beneficial or if it's not feasible. For ORDER BY, again, for
>> long
>> >> term, we should have a primary key or an internal unique key so that
>> rows
>> >> can be ordered on that single column as against all columns. So again,
>> we
>> >> still are better of with a new system column.
>> >>
>> >>
>> >> As regards to approach C, if we find a way to uniquely generate a new
>> row
>> >> id independently, then the task of generating rowid will be pretty
>> >> lightweight. We won't require any other table to store it or generate
>> it.
>> >> The coordinator will generate it at each insert (both fqs and
>> non-fqs), or
>> >> may be datanodes themselves find a way to generate a new rowid which is
>> >> always the same regardless of the datanode. A combination of gxid,
>> timestamp
>> >> and cmd id can be used to construct a unique rowid at the coordinator.
>> >>
>> >> I think one action plan can be :
>> >> 1. Use Mason's patch and tweak it so that it needs very little
>> >> modification later on if and when we add the system rowid column.
>> >> 2. Check in the patch but let it not error out if the primary key is
>> not
>> >> there. This way we would at least make the replicated tables with
>> primary
>> >> keys work without data issues, but continue to work as it is now for
>> tables
>> >> without primary key.
>> >> 3. Lastly support the new system row id implementation, and do an
>> >> incremental change in Mason's checked in changes to use this id
>> instead of
>> >> primary key.
>> >>
>> >
>> > Sounds good but as i understand 3 is not targeted for 1.2?
>>
>> I agree on this too.   System column will benefit not only this, but
>> also WCO, more variety of the cursor, and Triggers.   I agree this is
>> targeted to 1.3 or later.
>>
>> Regards;
>> ---
>> Koichi Suzuki
>>
>> >
>> >>
>> >>
>> >>>
>> >>> On Thu, Feb 13, 2014 at 9:45 AM, Koichi Suzuki <koi...@gm...
>> >
>> >>> wrote:
>> >>>>
>> >>>> Hi,
>> >>>>
>> >>>> I tested the patch and found that primary key is mandatory.   We need
>> >>>> to modify regression test considerably to give each replicated table
>> >>>> primary keys.
>> >>>>
>> >>>> I think this patch helps but I'm not afraid this is good, especially
>> >>>> when we try to take XC features back to PG.
>> >>>>
>> >>>> Did you post another patch to use all column values if primary key is
>> >>>> not available?
>> >>>>
>> >>>> I think better way is as follows:
>> >>>>
>> >>>> 1) If primary key is defined, use it,
>> >>>> 2) If not, create a primary key as system column, the size should be
>> >>>> 64bit.
>> >>>> 3) If primary key is added to a replicated table, remove system
>> primary
>> >>>> key.
>> >>>>
>> >>>> The value of primary key can be obtained as follows:
>> >>>>
>> >>>> 1) add new column to pgxc_class catalog to represent maximum value of
>> >>>> the system primary key,
>> >>>> 2) when first "insert" is done to the primary node, system primary
>> key
>> >>>> value is taken from 1) and 1) is updated.  The value is returned to
>> >>>> the coordinator to be propagated to other nodes.
>> >>>> 3) when subsequent "insert" is being done, system primary key value
>> is
>> >>>> added to the column value.   In this case, each datanode updates 1)
>> >>>> column value if it is larger than the current maximum value.
>> >>>>
>> >>>> 3) is important to change primary node to another.   This is needed
>> to
>> >>>> carry over the primary node to another.
>> >>>>
>> >>>> ALTER TABLE should take care of them.
>> >>>>
>> >>>> Other issues are:
>> >>>>
>> >>>> 4) pg_dump/pg_dumpall should not include this system column value,
>> >>>> 5) cluster may need to handle this too to repack system primary key
>> >>>> value (not now but at least in 1.3 or later).
>> >>>>
>> >>>> Regards;
>> >>>> ---
>> >>>> Koichi Suzuki
>> >>>>
>> >>>>
>> >>>> 2013-11-02 9:26 GMT+09:00 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
>> >>>> >
>> >>>>
>> >>>>
>> >>>>
>> ------------------------------------------------------------------------------
>> >>>> Android apps run on BlackBerry 10
>> >>>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>> >>>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>> >>>> Get your Android app in front of a whole new audience.  Start now.
>> >>>>
>> >>>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>> >>>> _______________________________________________
>> >>>> Postgres-xc-developers mailing list
>> >>>> Pos...@li...
>> >>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>> >>>
>> >>>
>> >>>
>> >>>
>> >>> --
>> >>> Best Wishes,
>> >>> Ashutosh Bapat
>> >>> EnterpriseDB Corporation
>> >>> The Postgres Database Company
>> >>>
>> >>>
>> >>>
>> ------------------------------------------------------------------------------
>> >>> Android apps run on BlackBerry 10
>> >>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>> >>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>> >>> Get your Android app in front of a whole new audience.  Start now.
>> >>>
>> >>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>> >>> _______________________________________________
>> >>> Postgres-xc-developers mailing list
>> >>> Pos...@li...
>> >>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>> >>>
>> >>
>> >>
>> >>
>> >>
>> ------------------------------------------------------------------------------
>> >> Managing the Performance of Cloud-Based Applications
>> >> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
>> >> Read the Whitepaper.
>> >>
>> >>
>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>> >>
>> >> _______________________________________________
>> >> Postgres-xc-developers mailing list
>> >> Pos...@li...
>> >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>> >>
>> >
>> >
>> >
>> > --
>> > Ahsan Hadi
>> > Snr Director Product Development
>> > EnterpriseDB Corporation
>> > The Enterprise Postgres Company
>> >
>> > Phone: +92-51-8358874
>> > Mobile: +92-333-5162114
>> >
>> > Website: www.enterprisedb.com
>> > EnterpriseDB Blog: http://blogs.enterprisedb.com/
>> > Follow us on Twitter: http://www.twitter.com/enterprisedb
>> >
>> > This e-mail message (and any attachment) is intended for the use of the
>> > individual or entity to whom it is addressed. This message contains
>> > information from EnterpriseDB Corporation that may be privileged,
>> > confidential, or exempt from disclosure under applicable law. If you
>> are not
>> > the intended recipient or authorized to receive this for the intended
>> > recipient, any use, dissemination, distribution, retention, archiving,
>> or
>> > copying of this communication is strictly prohibited. If you have
>> received
>> > this e-mail in error, please notify the sender immediately by reply
>> e-mail
>> > and delete this message.
>> >
>> >
>> ------------------------------------------------------------------------------
>> > Managing the Performance of Cloud-Based Applications
>> > Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
>> > Read the Whitepaper.
>> >
>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>> > _______________________________________________
>> > Postgres-xc-developers mailing list
>> > Pos...@li...
>> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>> >
>>
>>
>> ------------------------------------------------------------------------------
>> Managing the Performance of Cloud-Based Applications
>> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
>> Read the Whitepaper.
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>
>
>
> --
> --
> *Abbas*
>  Architect
>
> Ph: 92.334.5100153
>  Skype ID: gabbasb
> www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>
>
> *Follow us on Twitter*
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community>
>



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

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

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

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

[Postgres-xc-developers] pgbench of Postgres-XC 1.2 is broken.

[Tomonari K. <t.k...@gm...>]

Hi,

I've noticed that pgbench included in Postgres-XC 1.2 is
broken and I can not work it fine.

[compiling]
pgbench.c: In function 'init':
pgbench.c:1530: warning: excess elements in struct initializer
pgbench.c:1530: warning: (near initialization for 'DDLs[0]')
pgbench.c:1532: warning: excess elements in struct initializer
pgbench.c:1532: warning: (near initialization for 'DDLs[0]')

Above messages are printed.

[initializing]
$ pgbench -i bench
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.27 s, remaining 0.00 s).
vacuum...
ERROR:  relation "pgbench_history" does not exist


I could not create pgbench_history table.

I attached a patch to fix this problem.
Please fix by formal relase of Postgres-XC 1.2.

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

Re: [Postgres-xc-developers] preproc.h Missing

[Michael M. <me...@po...>]

On Wed, Feb 19, 2014 at 05:35:09PM -0800, David E. Wheeler wrote:
>     keywords.c:20:21: error: preproc.h: No such file or directory
>  ...
>     	rm -f preproc.y preproc.c preproc.h pgc.c

Simply remove the other three and rebuild. All four will be re-created automatically.

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
Jabber: michael.meskes at gmail dot com
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL

Re: [Postgres-xc-developers] preproc.h Missing

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

Very sorry for this.   Part of this was already reported.   I will upload improved tarball this weekend.
---
Koichi Suzuki

2014/02/20 10:35、David E. Wheeler <da...@ju...> のメール：

> PGXC Hackers,
> 
> I am trying to build 1.2.0beta on CentOS 6.2 but get this error:
> 
>    keywords.c:20:21: error: preproc.h: No such file or directory
> 
> Sure enough, preproc.h does not exist in src/interfaces/ecpg/preproc/. Looking at the Makefile, I see this at the bottom:
> 
>    # `make distclean' must not remove preproc.y, preproc.c, preproc.h, or pgc.c
>    # since we want to ship those files in the distribution for people with
>    # inadequate tools.  Instead, `make maintainer-clean' will remove them.
>    maintainer-clean: distclean
>    	rm -f preproc.y preproc.c preproc.h pgc.c
> 
> All of those are present except for preproc.h. Did it somehow get omitted from the release? Missing from a manifest or something? Someone got a copy of it so I can add it to my download and continue building for now?
> 
> Thanks,
> 
> David
> 
> 
> ------------------------------------------------------------------------------
> Managing the Performance of Cloud-Based Applications
> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
> Read the Whitepaper.
> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&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

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

That’s fine with me.
---
Koichi Suzuki

2014/02/19 19:56、Abbas Butt <abb...@en...<mailto:abb...@en...>> のメール：

Lets do this
1. If the replicated table has a unique key defined , then use Mason's patch after testing and refinement if required.
2. If the replicated table has no key, then we can use cursors idea proposed by Ashutosh.

Does every body agree?



On Wed, Feb 19, 2014 at 12:15 PM, Koichi Suzuki <koi...@gm...<mailto:koi...@gm...>> wrote:
2014-02-19 16:00 GMT+09:00 Ahsan Hadi <ahs...@en...<mailto:ahs...@en...>>:
>
>
>
> On Tue, Feb 18, 2014 at 11:26 PM, Amit Khandekar
> <ami...@en...<mailto:ami...@en...>> wrote:
>>
>>
>>
>>
>> On 13 February 2014 11:54, Ashutosh Bapat
>> <ash...@en...<mailto:ash...@en...>> wrote:
>>>
>>> One more solution would be to use cursors for replicated tables. The idea
>>> is to open cursors on all the copies of the table and append the query with
>>> an ORDER BY clause on all the columns. Thus we are sure that the current of
>>> each of these cursors point to same row on all the copies. While fetching a
>>> row from a replicated table, we fetch from all the cursors and choose only
>>> one row for the data processing. While updating or deleting we send UPDATE
>>> or DELETE with WHERE CURRENT OF. The down side of this approach is that, if
>>> there are coordinator quals, we will end up locking more rows than
>>> necessary, increasing the probability of the deadlock but at least there
>>> won't be a necessary restriction of having primary or unique key and we
>>> won't break backward compatibility.
>>>
>>> If there two identical rows, we might mix the update from different
>>> nodes, but then who knew which of them were corresponded across the nodes to
>>> start with.
>>>
>>
>> Locking all rows doesn't look good especially because we are looking for a
>> permanent long term solution. If we can come up with some other solution
>> that avoids this, we better avoid this compromise. For a replicated table
>> with 10000 rows, all concurrent updates will be serialized even if they are
>> updating a different row.
>>
>> Other thing is datanode performance impact for ORDER BY all columns,
>> especially with many large size columns. I had also mentioned about ORDER BY
>> in approach A. I am not sure whether there is some kind of optimization in
>> the sort, such as: if we find unique rows with the first n columns, it does
>> not compare the rest of the columns.
>>
>> I think declaring cursors is a cool idea in general for DMLs but it
>> requires refactoring of DML planning, and also it requires ORDER BY. There
>> is a concurrent update issue #398 for which we do require a refactor of DML
>> handling. While doing that it will be clearer whether declaring cursor is
>> really beneficial or if it's not feasible. For ORDER BY, again, for long
>> term, we should have a primary key or an internal unique key so that rows
>> can be ordered on that single column as against all columns. So again, we
>> still are better of with a new system column.
>>
>>
>> As regards to approach C, if we find a way to uniquely generate a new row
>> id independently, then the task of generating rowid will be pretty
>> lightweight. We won't require any other table to store it or generate it.
>> The coordinator will generate it at each insert (both fqs and non-fqs), or
>> may be datanodes themselves find a way to generate a new rowid which is
>> always the same regardless of the datanode. A combination of gxid, timestamp
>> and cmd id can be used to construct a unique rowid at the coordinator.
>>
>> I think one action plan can be :
>> 1. Use Mason's patch and tweak it so that it needs very little
>> modification later on if and when we add the system rowid column.
>> 2. Check in the patch but let it not error out if the primary key is not
>> there. This way we would at least make the replicated tables with primary
>> keys work without data issues, but continue to work as it is now for tables
>> without primary key.
>> 3. Lastly support the new system row id implementation, and do an
>> incremental change in Mason's checked in changes to use this id instead of
>> primary key.
>>
>
> Sounds good but as i understand 3 is not targeted for 1.2?

I agree on this too.   System column will benefit not only this, but
also WCO, more variety of the cursor, and Triggers.   I agree this is
targeted to 1.3 or later.

Regards;
---
Koichi Suzuki

>
>>
>>
>>>
>>> On Thu, Feb 13, 2014 at 9:45 AM, Koichi Suzuki <koi...@gm...<mailto:koi...@gm...>>
>>> wrote:
>>>>
>>>> Hi,
>>>>
>>>> I tested the patch and found that primary key is mandatory.   We need
>>>> to modify regression test considerably to give each replicated table
>>>> primary keys.
>>>>
>>>> I think this patch helps but I'm not afraid this is good, especially
>>>> when we try to take XC features back to PG.
>>>>
>>>> Did you post another patch to use all column values if primary key is
>>>> not available?
>>>>
>>>> I think better way is as follows:
>>>>
>>>> 1) If primary key is defined, use it,
>>>> 2) If not, create a primary key as system column, the size should be
>>>> 64bit.
>>>> 3) If primary key is added to a replicated table, remove system primary
>>>> key.
>>>>
>>>> The value of primary key can be obtained as follows:
>>>>
>>>> 1) add new column to pgxc_class catalog to represent maximum value of
>>>> the system primary key,
>>>> 2) when first "insert" is done to the primary node, system primary key
>>>> value is taken from 1) and 1) is updated.  The value is returned to
>>>> the coordinator to be propagated to other nodes.
>>>> 3) when subsequent "insert" is being done, system primary key value is
>>>> added to the column value.   In this case, each datanode updates 1)
>>>> column value if it is larger than the current maximum value.
>>>>
>>>> 3) is important to change primary node to another.   This is needed to
>>>> carry over the primary node to another.
>>>>
>>>> ALTER TABLE should take care of them.
>>>>
>>>> Other issues are:
>>>>
>>>> 4) pg_dump/pg_dumpall should not include this system column value,
>>>> 5) cluster may need to handle this too to repack system primary key
>>>> value (not now but at least in 1.3 or later).
>>>>
>>>> Regards;
>>>> ---
>>>> Koichi Suzuki
>>>>
>>>>
>>>> 2013-11-02 9:26 GMT+09:00 Mason Sharp <ms...@tr...<mailto: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<http://www.translattice.com/>
>>>> > Distributed and Clustered Database Solutions
>>>> >
>>>> >
>>>> > ------------------------------------------------------------------------------
>>>> > November Webinars for C, C++, Fortran Developers
>>>> > Accelerate application performance with scalable programming models.
>>>> > Explore
>>>> > techniques for threading, error checking, porting, and tuning. Get the
>>>> > most
>>>> > from the latest Intel processors and coprocessors. See abstracts and
>>>> > register
>>>> >
>>>> > http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
>>>> > _______________________________________________
>>>> > Postgres-xc-developers mailing list
>>>> > Pos...@li...<mailto:Pos...@li...>
>>>> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>> >
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> Android apps run on BlackBerry 10
>>>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>>>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>>>> Get your Android app in front of a whole new audience.  Start now.
>>>>
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>>>> _______________________________________________
>>>> Postgres-xc-developers mailing list
>>>> Pos...@li...<mailto:Pos...@li...>
>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>
>>>
>>>
>>>
>>> --
>>> Best Wishes,
>>> Ashutosh Bapat
>>> EnterpriseDB Corporation
>>> The Postgres Database Company
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Android apps run on BlackBerry 10
>>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>>> Get your Android app in front of a whole new audience.  Start now.
>>>
>>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>>> _______________________________________________
>>> Postgres-xc-developers mailing list
>>> Pos...@li...<mailto:Pos...@li...>
>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Managing the Performance of Cloud-Based Applications
>> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
>> Read the Whitepaper.
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>>
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...<mailto:Pos...@li...>
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>
>
>
> --
> Ahsan Hadi
> Snr Director Product Development
> EnterpriseDB Corporation
> The Enterprise Postgres Company
>
> Phone: +92-51-8358874<tel:%2B92-51-8358874>
> Mobile: +92-333-5162114<tel:%2B92-333-5162114>
>
> Website: www.enterprisedb.com<http://www.enterprisedb.com/>
> EnterpriseDB Blog: http://blogs.enterprisedb.com/
> Follow us on Twitter: http://www.twitter.com/enterprisedb
>
> This e-mail message (and any attachment) is intended for the use of the
> individual or entity to whom it is addressed. This message contains
> information from EnterpriseDB Corporation that may be privileged,
> confidential, or exempt from disclosure under applicable law. If you are not
> the intended recipient or authorized to receive this for the intended
> recipient, any use, dissemination, distribution, retention, archiving, or
> copying of this communication is strictly prohibited. If you have received
> this e-mail in error, please notify the sender immediately by reply e-mail
> and delete this message.
>
> ------------------------------------------------------------------------------
> Managing the Performance of Cloud-Based Applications
> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
> Read the Whitepaper.
> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...<mailto:Pos...@li...>
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>

------------------------------------------------------------------------------
Managing the Performance of Cloud-Based Applications
Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
Read the Whitepaper.
http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
_______________________________________________
Postgres-xc-developers mailing list
Pos...@li...<mailto:Pos...@li...>
https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers



--
--
Abbas
Architect

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

Follow us on Twitter
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community> and more<http://www.enterprisedb.com/resources-community>
------------------------------------------------------------------------------
Managing the Performance of Cloud-Based Applications
Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
Read the Whitepaper.
http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk_______________________________________________
Postgres-xc-developers mailing list
Pos...@li...
https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers

[Postgres-xc-developers] preproc.h Missing

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

PGXC Hackers,

I am trying to build 1.2.0beta on CentOS 6.2 but get this error:

    keywords.c:20:21: error: preproc.h: No such file or directory

Sure enough, preproc.h does not exist in src/interfaces/ecpg/preproc/. Looking at the Makefile, I see this at the bottom:

    # `make distclean' must not remove preproc.y, preproc.c, preproc.h, or pgc.c
    # since we want to ship those files in the distribution for people with
    # inadequate tools.  Instead, `make maintainer-clean' will remove them.
    maintainer-clean: distclean
    	rm -f preproc.y preproc.c preproc.h pgc.c

All of those are present except for preproc.h. Did it somehow get omitted from the release? Missing from a manifest or something? Someone got a copy of it so I can add it to my download and continue building for now?

Thanks,

David

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

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

Lets do this
1. If the replicated table has a unique key defined , then use Mason's
patch after testing and refinement if required.
2. If the replicated table has no key, then we can use cursors idea
proposed by Ashutosh.

Does every body agree?



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

> 2014-02-19 16:00 GMT+09:00 Ahsan Hadi <ahs...@en...>:
> >
> >
> >
> > On Tue, Feb 18, 2014 at 11:26 PM, Amit Khandekar
> > <ami...@en...> wrote:
> >>
> >>
> >>
> >>
> >> On 13 February 2014 11:54, Ashutosh Bapat
> >> <ash...@en...> wrote:
> >>>
> >>> One more solution would be to use cursors for replicated tables. The
> idea
> >>> is to open cursors on all the copies of the table and append the query
> with
> >>> an ORDER BY clause on all the columns. Thus we are sure that the
> current of
> >>> each of these cursors point to same row on all the copies. While
> fetching a
> >>> row from a replicated table, we fetch from all the cursors and choose
> only
> >>> one row for the data processing. While updating or deleting we send
> UPDATE
> >>> or DELETE with WHERE CURRENT OF. The down side of this approach is
> that, if
> >>> there are coordinator quals, we will end up locking more rows than
> >>> necessary, increasing the probability of the deadlock but at least
> there
> >>> won't be a necessary restriction of having primary or unique key and we
> >>> won't break backward compatibility.
> >>>
> >>> If there two identical rows, we might mix the update from different
> >>> nodes, but then who knew which of them were corresponded across the
> nodes to
> >>> start with.
> >>>
> >>
> >> Locking all rows doesn't look good especially because we are looking
> for a
> >> permanent long term solution. If we can come up with some other solution
> >> that avoids this, we better avoid this compromise. For a replicated
> table
> >> with 10000 rows, all concurrent updates will be serialized even if they
> are
> >> updating a different row.
> >>
> >> Other thing is datanode performance impact for ORDER BY all columns,
> >> especially with many large size columns. I had also mentioned about
> ORDER BY
> >> in approach A. I am not sure whether there is some kind of optimization
> in
> >> the sort, such as: if we find unique rows with the first n columns, it
> does
> >> not compare the rest of the columns.
> >>
> >> I think declaring cursors is a cool idea in general for DMLs but it
> >> requires refactoring of DML planning, and also it requires ORDER BY.
> There
> >> is a concurrent update issue #398 for which we do require a refactor of
> DML
> >> handling. While doing that it will be clearer whether declaring cursor
> is
> >> really beneficial or if it's not feasible. For ORDER BY, again, for long
> >> term, we should have a primary key or an internal unique key so that
> rows
> >> can be ordered on that single column as against all columns. So again,
> we
> >> still are better of with a new system column.
> >>
> >>
> >> As regards to approach C, if we find a way to uniquely generate a new
> row
> >> id independently, then the task of generating rowid will be pretty
> >> lightweight. We won't require any other table to store it or generate
> it.
> >> The coordinator will generate it at each insert (both fqs and non-fqs),
> or
> >> may be datanodes themselves find a way to generate a new rowid which is
> >> always the same regardless of the datanode. A combination of gxid,
> timestamp
> >> and cmd id can be used to construct a unique rowid at the coordinator.
> >>
> >> I think one action plan can be :
> >> 1. Use Mason's patch and tweak it so that it needs very little
> >> modification later on if and when we add the system rowid column.
> >> 2. Check in the patch but let it not error out if the primary key is not
> >> there. This way we would at least make the replicated tables with
> primary
> >> keys work without data issues, but continue to work as it is now for
> tables
> >> without primary key.
> >> 3. Lastly support the new system row id implementation, and do an
> >> incremental change in Mason's checked in changes to use this id instead
> of
> >> primary key.
> >>
> >
> > Sounds good but as i understand 3 is not targeted for 1.2?
>
> I agree on this too.   System column will benefit not only this, but
> also WCO, more variety of the cursor, and Triggers.   I agree this is
> targeted to 1.3 or later.
>
> Regards;
> ---
> Koichi Suzuki
>
> >
> >>
> >>
> >>>
> >>> On Thu, Feb 13, 2014 at 9:45 AM, Koichi Suzuki <koi...@gm...>
> >>> wrote:
> >>>>
> >>>> Hi,
> >>>>
> >>>> I tested the patch and found that primary key is mandatory.   We need
> >>>> to modify regression test considerably to give each replicated table
> >>>> primary keys.
> >>>>
> >>>> I think this patch helps but I'm not afraid this is good, especially
> >>>> when we try to take XC features back to PG.
> >>>>
> >>>> Did you post another patch to use all column values if primary key is
> >>>> not available?
> >>>>
> >>>> I think better way is as follows:
> >>>>
> >>>> 1) If primary key is defined, use it,
> >>>> 2) If not, create a primary key as system column, the size should be
> >>>> 64bit.
> >>>> 3) If primary key is added to a replicated table, remove system
> primary
> >>>> key.
> >>>>
> >>>> The value of primary key can be obtained as follows:
> >>>>
> >>>> 1) add new column to pgxc_class catalog to represent maximum value of
> >>>> the system primary key,
> >>>> 2) when first "insert" is done to the primary node, system primary key
> >>>> value is taken from 1) and 1) is updated.  The value is returned to
> >>>> the coordinator to be propagated to other nodes.
> >>>> 3) when subsequent "insert" is being done, system primary key value is
> >>>> added to the column value.   In this case, each datanode updates 1)
> >>>> column value if it is larger than the current maximum value.
> >>>>
> >>>> 3) is important to change primary node to another.   This is needed to
> >>>> carry over the primary node to another.
> >>>>
> >>>> ALTER TABLE should take care of them.
> >>>>
> >>>> Other issues are:
> >>>>
> >>>> 4) pg_dump/pg_dumpall should not include this system column value,
> >>>> 5) cluster may need to handle this too to repack system primary key
> >>>> value (not now but at least in 1.3 or later).
> >>>>
> >>>> Regards;
> >>>> ---
> >>>> Koichi Suzuki
> >>>>
> >>>>
> >>>> 2013-11-02 9:26 GMT+09:00 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
> >>>> >
> >>>>
> >>>>
> >>>>
> ------------------------------------------------------------------------------
> >>>> Android apps run on BlackBerry 10
> >>>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
> >>>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
> >>>> Get your Android app in front of a whole new audience.  Start now.
> >>>>
> >>>>
> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
> >>>> _______________________________________________
> >>>> Postgres-xc-developers mailing list
> >>>> Pos...@li...
> >>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
> >>>
> >>>
> >>>
> >>>
> >>> --
> >>> Best Wishes,
> >>> Ashutosh Bapat
> >>> EnterpriseDB Corporation
> >>> The Postgres Database Company
> >>>
> >>>
> >>>
> ------------------------------------------------------------------------------
> >>> Android apps run on BlackBerry 10
> >>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
> >>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
> >>> Get your Android app in front of a whole new audience.  Start now.
> >>>
> >>>
> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
> >>> _______________________________________________
> >>> Postgres-xc-developers mailing list
> >>> Pos...@li...
> >>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
> >>>
> >>
> >>
> >>
> >>
> ------------------------------------------------------------------------------
> >> Managing the Performance of Cloud-Based Applications
> >> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
> >> Read the Whitepaper.
> >>
> >>
> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
> >>
> >> _______________________________________________
> >> Postgres-xc-developers mailing list
> >> Pos...@li...
> >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
> >>
> >
> >
> >
> > --
> > Ahsan Hadi
> > Snr Director Product Development
> > EnterpriseDB Corporation
> > The Enterprise Postgres Company
> >
> > Phone: +92-51-8358874
> > Mobile: +92-333-5162114
> >
> > Website: www.enterprisedb.com
> > EnterpriseDB Blog: http://blogs.enterprisedb.com/
> > Follow us on Twitter: http://www.twitter.com/enterprisedb
> >
> > This e-mail message (and any attachment) is intended for the use of the
> > individual or entity to whom it is addressed. This message contains
> > information from EnterpriseDB Corporation that may be privileged,
> > confidential, or exempt from disclosure under applicable law. If you are
> not
> > the intended recipient or authorized to receive this for the intended
> > recipient, any use, dissemination, distribution, retention, archiving, or
> > copying of this communication is strictly prohibited. If you have
> received
> > this e-mail in error, please notify the sender immediately by reply
> e-mail
> > and delete this message.
> >
> >
> ------------------------------------------------------------------------------
> > Managing the Performance of Cloud-Based Applications
> > Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
> > Read the Whitepaper.
> >
> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
> > _______________________________________________
> > Postgres-xc-developers mailing list
> > Pos...@li...
> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
> >
>
>
> ------------------------------------------------------------------------------
> Managing the Performance of Cloud-Based Applications
> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
> Read the Whitepaper.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>



-- 
-- 
*Abbas*
 Architect

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

*Follow us on Twitter*
@EnterpriseDB

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

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

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

2014-02-19 16:00 GMT+09:00 Ahsan Hadi <ahs...@en...>:
>
>
>
> On Tue, Feb 18, 2014 at 11:26 PM, Amit Khandekar
> <ami...@en...> wrote:
>>
>>
>>
>>
>> On 13 February 2014 11:54, Ashutosh Bapat
>> <ash...@en...> wrote:
>>>
>>> One more solution would be to use cursors for replicated tables. The idea
>>> is to open cursors on all the copies of the table and append the query with
>>> an ORDER BY clause on all the columns. Thus we are sure that the current of
>>> each of these cursors point to same row on all the copies. While fetching a
>>> row from a replicated table, we fetch from all the cursors and choose only
>>> one row for the data processing. While updating or deleting we send UPDATE
>>> or DELETE with WHERE CURRENT OF. The down side of this approach is that, if
>>> there are coordinator quals, we will end up locking more rows than
>>> necessary, increasing the probability of the deadlock but at least there
>>> won't be a necessary restriction of having primary or unique key and we
>>> won't break backward compatibility.
>>>
>>> If there two identical rows, we might mix the update from different
>>> nodes, but then who knew which of them were corresponded across the nodes to
>>> start with.
>>>
>>
>> Locking all rows doesn't look good especially because we are looking for a
>> permanent long term solution. If we can come up with some other solution
>> that avoids this, we better avoid this compromise. For a replicated table
>> with 10000 rows, all concurrent updates will be serialized even if they are
>> updating a different row.
>>
>> Other thing is datanode performance impact for ORDER BY all columns,
>> especially with many large size columns. I had also mentioned about ORDER BY
>> in approach A. I am not sure whether there is some kind of optimization in
>> the sort, such as: if we find unique rows with the first n columns, it does
>> not compare the rest of the columns.
>>
>> I think declaring cursors is a cool idea in general for DMLs but it
>> requires refactoring of DML planning, and also it requires ORDER BY. There
>> is a concurrent update issue #398 for which we do require a refactor of DML
>> handling. While doing that it will be clearer whether declaring cursor is
>> really beneficial or if it's not feasible. For ORDER BY, again, for long
>> term, we should have a primary key or an internal unique key so that rows
>> can be ordered on that single column as against all columns. So again, we
>> still are better of with a new system column.
>>
>>
>> As regards to approach C, if we find a way to uniquely generate a new row
>> id independently, then the task of generating rowid will be pretty
>> lightweight. We won't require any other table to store it or generate it.
>> The coordinator will generate it at each insert (both fqs and non-fqs), or
>> may be datanodes themselves find a way to generate a new rowid which is
>> always the same regardless of the datanode. A combination of gxid, timestamp
>> and cmd id can be used to construct a unique rowid at the coordinator.
>>
>> I think one action plan can be :
>> 1. Use Mason's patch and tweak it so that it needs very little
>> modification later on if and when we add the system rowid column.
>> 2. Check in the patch but let it not error out if the primary key is not
>> there. This way we would at least make the replicated tables with primary
>> keys work without data issues, but continue to work as it is now for tables
>> without primary key.
>> 3. Lastly support the new system row id implementation, and do an
>> incremental change in Mason's checked in changes to use this id instead of
>> primary key.
>>
>
> Sounds good but as i understand 3 is not targeted for 1.2?

I agree on this too.   System column will benefit not only this, but
also WCO, more variety of the cursor, and Triggers.   I agree this is
targeted to 1.3 or later.

Regards;
---
Koichi Suzuki

>
>>
>>
>>>
>>> On Thu, Feb 13, 2014 at 9:45 AM, Koichi Suzuki <koi...@gm...>
>>> wrote:
>>>>
>>>> Hi,
>>>>
>>>> I tested the patch and found that primary key is mandatory.   We need
>>>> to modify regression test considerably to give each replicated table
>>>> primary keys.
>>>>
>>>> I think this patch helps but I'm not afraid this is good, especially
>>>> when we try to take XC features back to PG.
>>>>
>>>> Did you post another patch to use all column values if primary key is
>>>> not available?
>>>>
>>>> I think better way is as follows:
>>>>
>>>> 1) If primary key is defined, use it,
>>>> 2) If not, create a primary key as system column, the size should be
>>>> 64bit.
>>>> 3) If primary key is added to a replicated table, remove system primary
>>>> key.
>>>>
>>>> The value of primary key can be obtained as follows:
>>>>
>>>> 1) add new column to pgxc_class catalog to represent maximum value of
>>>> the system primary key,
>>>> 2) when first "insert" is done to the primary node, system primary key
>>>> value is taken from 1) and 1) is updated.  The value is returned to
>>>> the coordinator to be propagated to other nodes.
>>>> 3) when subsequent "insert" is being done, system primary key value is
>>>> added to the column value.   In this case, each datanode updates 1)
>>>> column value if it is larger than the current maximum value.
>>>>
>>>> 3) is important to change primary node to another.   This is needed to
>>>> carry over the primary node to another.
>>>>
>>>> ALTER TABLE should take care of them.
>>>>
>>>> Other issues are:
>>>>
>>>> 4) pg_dump/pg_dumpall should not include this system column value,
>>>> 5) cluster may need to handle this too to repack system primary key
>>>> value (not now but at least in 1.3 or later).
>>>>
>>>> Regards;
>>>> ---
>>>> Koichi Suzuki
>>>>
>>>>
>>>> 2013-11-02 9:26 GMT+09:00 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
>>>> >
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> Android apps run on BlackBerry 10
>>>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>>>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>>>> Get your Android app in front of a whole new audience.  Start now.
>>>>
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>>>> _______________________________________________
>>>> Postgres-xc-developers mailing list
>>>> Pos...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>
>>>
>>>
>>>
>>> --
>>> Best Wishes,
>>> Ashutosh Bapat
>>> EnterpriseDB Corporation
>>> The Postgres Database Company
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Android apps run on BlackBerry 10
>>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>>> Get your Android app in front of a whole new audience.  Start now.
>>>
>>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>>> _______________________________________________
>>> Postgres-xc-developers mailing list
>>> Pos...@li...
>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Managing the Performance of Cloud-Based Applications
>> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
>> Read the Whitepaper.
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
>>
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>
>
>
> --
> Ahsan Hadi
> Snr Director Product Development
> EnterpriseDB Corporation
> The Enterprise Postgres Company
>
> Phone: +92-51-8358874
> Mobile: +92-333-5162114
>
> Website: www.enterprisedb.com
> EnterpriseDB Blog: http://blogs.enterprisedb.com/
> Follow us on Twitter: http://www.twitter.com/enterprisedb
>
> This e-mail message (and any attachment) is intended for the use of the
> individual or entity to whom it is addressed. This message contains
> information from EnterpriseDB Corporation that may be privileged,
> confidential, or exempt from disclosure under applicable law. If you are not
> the intended recipient or authorized to receive this for the intended
> recipient, any use, dissemination, distribution, retention, archiving, or
> copying of this communication is strictly prohibited. If you have received
> this e-mail in error, please notify the sender immediately by reply e-mail
> and delete this message.
>
> ------------------------------------------------------------------------------
> Managing the Performance of Cloud-Based Applications
> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
> Read the Whitepaper.
> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&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

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

On Tue, Feb 18, 2014 at 11:26 PM, Amit Khandekar <
ami...@en...> wrote:

>
>
>
> On 13 February 2014 11:54, Ashutosh Bapat <ash...@en...
> > wrote:
>
>> One more solution would be to use cursors for replicated tables. The idea
>> is to open cursors on all the copies of the table and append the query with
>> an ORDER BY clause on all the columns. Thus we are sure that the current of
>> each of these cursors point to same row on all the copies. While fetching a
>> row from a replicated table, we fetch from all the cursors and choose only
>> one row for the data processing. While updating or deleting we send UPDATE
>> or DELETE with WHERE CURRENT OF. The down side of this approach is that, if
>> there are coordinator quals, we will end up locking more rows than
>> necessary, increasing the probability of the deadlock but at least there
>> won't be a necessary restriction of having primary or unique key and we
>> won't break backward compatibility.
>>
>> If there two identical rows, we might mix the update from different
>> nodes, but then who knew which of them were corresponded across the nodes
>> to start with.
>>
>>
> Locking all rows doesn't look good especially because we are looking for a
> permanent long term solution. If we can come up with some other solution
> that avoids this, we better avoid this compromise. For a replicated table
> with 10000 rows, all concurrent updates will be serialized even if they are
> updating a different row.
>
> Other thing is datanode performance impact for ORDER BY all columns,
> especially with many large size columns. I had also mentioned about ORDER
> BY in approach A. I am not sure whether there is some kind of optimization
> in the sort, such as: if we find unique rows with the first n columns, it
> does not compare the rest of the columns.
>
> I think declaring cursors is a cool idea in general for DMLs but it
> requires refactoring of DML planning, and also it requires ORDER BY. There
> is a concurrent update issue #398 for which we do require a refactor of DML
> handling. While doing that it will be clearer whether declaring cursor is
> really beneficial or if it's not feasible. For ORDER BY, again, for long
> term, we should have a primary key or an internal unique key so that rows
> can be ordered on that single column as against all columns. So again, we
> still are better of with a new system column.
>
>
> As regards to approach C, if we find a way to uniquely generate a new row
> id independently, then the task of generating rowid will be pretty
> lightweight. We won't require any other table to store it or generate it.
> The coordinator will generate it at each insert (both fqs and non-fqs), or
> may be datanodes themselves find a way to generate a new rowid which is
> always the same regardless of the datanode. A combination of gxid,
> timestamp and cmd id can be used to construct a unique rowid at the
> coordinator.
>
> I think one action plan can be :
> 1. Use Mason's patch and tweak it so that it needs very little
> modification later on if and when we add the system rowid column.
> 2. Check in the patch but let it not error out if the primary key is not
> there. This way we would at least make the replicated tables with primary
> keys work without data issues, but continue to work as it is now for tables
> without primary key.
> 3. Lastly support the new system row id implementation, and do an
> incremental change in Mason's checked in changes to use this id instead of
> primary key.
>
>
Sounds good but as i understand 3 is not targeted for 1.2?


>
>
>> On Thu, Feb 13, 2014 at 9:45 AM, Koichi Suzuki <koi...@gm...>wrote:
>>
>>> Hi,
>>>
>>> I tested the patch and found that primary key is mandatory.   We need
>>> to modify regression test considerably to give each replicated table
>>> primary keys.
>>>
>>> I think this patch helps but I'm not afraid this is good, especially
>>> when we try to take XC features back to PG.
>>>
>>> Did you post another patch to use all column values if primary key is
>>> not available?
>>>
>>> I think better way is as follows:
>>>
>>> 1) If primary key is defined, use it,
>>> 2) If not, create a primary key as system column, the size should be
>>> 64bit.
>>> 3) If primary key is added to a replicated table, remove system primary
>>> key.
>>>
>>> The value of primary key can be obtained as follows:
>>>
>>> 1) add new column to pgxc_class catalog to represent maximum value of
>>> the system primary key,
>>> 2) when first "insert" is done to the primary node, system primary key
>>> value is taken from 1) and 1) is updated.  The value is returned to
>>> the coordinator to be propagated to other nodes.
>>> 3) when subsequent "insert" is being done, system primary key value is
>>> added to the column value.   In this case, each datanode updates 1)
>>> column value if it is larger than the current maximum value.
>>>
>>> 3) is important to change primary node to another.   This is needed to
>>> carry over the primary node to another.
>>>
>>> ALTER TABLE should take care of them.
>>>
>>> Other issues are:
>>>
>>> 4) pg_dump/pg_dumpall should not include this system column value,
>>> 5) cluster may need to handle this too to repack system primary key
>>> value (not now but at least in 1.3 or later).
>>>
>>> Regards;
>>> ---
>>> Koichi Suzuki
>>>
>>>
>>> 2013-11-02 9:26 GMT+09:00 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
>>> >
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Android apps run on BlackBerry 10
>>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>>> Get your Android app in front of a whole new audience.  Start now.
>>>
>>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>>> _______________________________________________
>>> Postgres-xc-developers mailing list
>>> Pos...@li...
>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>
>>
>>
>>
>> --
>> Best Wishes,
>> Ashutosh Bapat
>> EnterpriseDB Corporation
>> The Postgres Database Company
>>
>>
>> ------------------------------------------------------------------------------
>> Android apps run on BlackBerry 10
>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>> Get your Android app in front of a whole new audience.  Start now.
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>>
>
>
> ------------------------------------------------------------------------------
> Managing the Performance of Cloud-Based Applications
> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
> Read the Whitepaper.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
>


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

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

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

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

Re: [Postgres-xc-developers] Random failures in the regression test.

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

2014-02-14 18:35 GMT+09:00 Andrei Martsinchyk <and...@gm...>:
>
>
>
> 2014-02-14 9:04 GMT+02:00 Masataka Saito <pg...@gm...>:
>
>> Thank you for your clever suggestion.
>>
>> > - Make Cancel more selective and affect only specific query. That means
>> > an ID for each query to introduce, that should be known to client and way to
>> > deliver it.
>> > - Introduce procedure of changing backend key. Old cancel won't affect
>> > such backend.
>>
>> I prefer the 2nd idea. But these ideas seem to require touching libpq
>> infrastructure and if I understand correctly, they are used not only
>> the inter node communication but also a coordinator and a frontend
>> communication. Unless we can separate them, I think better not to
>> change it.
>>
> XC is already extending PG client-server protocol and use the extension in
> internode communications. The suggested feature do not have to be available
> to external client and therefore no need to be supported by libpq.
>
>>
>> > - Before starting new query, check if there is pending cancel and remove
>> > it. It sounds ridiculous "cancel cancel" but may work, if queries and
>> > cancels are issued synchronously from single source.
>>
>> I'm afraid of the wrong hypothesis. As I suggested first, cancel and
>> subsequent request are not serialized at the target node. It means
>> that if the query started with no pending cancel, it could be
>> interrupted by cancel request.
>>
>
> I am not sure how exactly Cancel request is handled. If server creates a
> session and sends back an acknowledgement before PGcancel returns it is
> synchronous enough. Node sends next command after the PGcancel returns, so
> the respective session either already placed the interrupt request or can be
> found in the Proc array. Either can be cleaned. If the Cancel is not
> synchronous enough, OK - just another bad idea, ignore it.

We may be able to implement this by adding new lock to synchronize
them, adding a command through libpq to handle this.   Adding a lock
can bring additional issues so I think we should be careful and take a
time to show it's safe too.

On the other hand, we're long suffered from this mainly in the regression.

Masataka's idea could be a quick hack but looks useful too.

Regards;
---
Koichi Suzuki

>
>
>>
>> Regards.
>>
>>
>> On 14 February 2014 14:06, Andrei Martsinchyk
>> <and...@gm...> wrote:
>> >
>> > You are right, the temp objects are problem.
>> > On the one hand if we run a long query and there was an error on one
>> > node we want to cancel it on others to avoid unnecessary waiting. On the
>> > other hand the query may be near its natural end and the cancel may be late
>> > and hit the next query.
>> > Just throwing out ideas:
>> > - Make Cancel more selective and affect only specific query. That means
>> > an ID for each query to introduce, that should be known to client and way to
>> > deliver it.
>> > - Introduce procedure of changing backend key. Old cancel won't affect
>> > such backend.
>> > - Before starting new query, check if there is pending cancel and remove
>> > it. It sounds ridiculous "cancel cancel" but may work, if queries and
>> > cancels are issued synchronously from single source.
>> >
>> > 14.02.2014 4:07 пользователь "Koichi Suzuki" <koi...@gm...>
>> > написал:
>> >
>> >> I misunderstand the implication.   Anyway additional wait is separate
>> >> from your suggestion.
>> >>
>> >> Disconnecting the connection as you suggested will bring another
>> >> problem such as TEMPORARY object in the subsequent queries.   We do
>> >> not support TEMPORARY object but I believe we should be consistent on
>> >> this for future releases.
>> >>
>> >> Thoughts?
>> >> ---
>> >> Koichi Suzuki
>> >>
>> >>
>> >> 2014-02-14 2:30 GMT+09:00 Andrei Martsinchyk
>> >> <and...@gm...>:
>> >> > Hello,
>> >> >
>> >> > Postgres establishes separate connection to deliver Cancel command to
>> >> > the
>> >> > target session.
>> >> > On a heavily loaded node it may take fairly long. Longer sleep would
>> >> > help
>> >> > out, but it means longer recovery after an error.
>> >> > Better solution is to remove canceled connection from the pool and
>> >> > therefore
>> >> > do not use it to handle subsequent queries.
>> >> >
>> >> >
>> >> >
>> >> > 2014-02-13 11:10 GMT+02:00 Koichi Suzuki <koi...@gm...>:
>> >> >>
>> >> >> I think it hits the point.   I tested this patch several times and
>> >> >> it
>> >> >> seems to work fine.   The delay time (at present 10ms) is short
>> >> >> enough
>> >> >> and it is applied only when we need to cancel a statement.
>> >> >>
>> >> >> We should check this into all the master and STABLE branches
>> >> >> improving
>> >> >> magic number with some meaningful name.
>> >> >>
>> >> >> Any thoughts?
>> >> >> ---
>> >> >> Koichi Suzuki
>> >> >>
>> >> >>
>> >> >> 2014-01-24 18:25 GMT+09:00 Masataka Saito <pg...@gm...>:
>> >> >> > Hello,
>> >> >> >
>> >> >> > As I've been exasperated by random failures, I'm willing to whip
>> >> >> > the
>> >> >> > cause
>> >> >> > of the issue.
>> >> >> >
>> >> >> > This issue is related to cancel of the failed query.
>> >> >> > When a datanode reports an error of a query, a coordinator sends a
>> >> >> > cancel
>> >> >> > request to non-idle nodes, waits the node to get ready and
>> >> >> > requests
>> >> >> > nodes to
>> >> >> > rollback the transaction.
>> >> >> >
>> >> >> > Where's the problem? Consider the next case.
>> >> >> > 1. Datanode A (PID 1) reports an error to coordinator A. ([1] 'E'
>> >> >> > message)
>> >> >> > 2. Coordinator A receives [1] and reports an error to a frontend.
>> >> >> > ([2]
>> >> >> > 'E'
>> >> >> > message)
>> >> >> > 3. Coordinator A starts aborting process and it thinks datanode A
>> >> >> > (PID
>> >> >> > 1) is
>> >> >> > not idle.
>> >> >> > 4. Coordinator A sends a cancel request about PID 1 to datanode A
>> >> >> > (PID
>> >> >> > 2).
>> >> >> > ([3] cancel message)
>> >> >> > 5. Datanode A (PID 1) reports ready to coordinator A. ([4] 'Z'
>> >> >> > message)
>> >> >> > 6. Coordinator A receives [4] and sends "ROLLBACK TRANSACTION"
>> >> >> > immediately.
>> >> >> > ([5] 'Q' message)
>> >> >> > 7. Datanode A (PID 1) receives [5] and starts processing the
>> >> >> > query.
>> >> >> > 8. Datanode A (PID 2) receives [3].
>> >> >> > 9. Datanode A (PID 2) notify PID 1 of [3].
>> >> >> > 10. Datanode A (PID 1) cancel processing [5] and reports an error
>> >> >> > to
>> >> >> > Coordinator A. ([6] 'E' message)
>> >> >> > 11. Coordinator A receives [6] and reports an error to a frontend.
>> >> >> > ([7]
>> >> >> > 'E'
>> >> >> > message)
>> >> >> >
>> >> >> > [7] makes unexpected output and a test fails.
>> >> >> >
>> >> >> > Saying an extreme thing, it could occur that the next query of [5]
>> >> >> > is
>> >> >> > cancelled by [3].
>> >> >> >
>> >> >> > As far as I know, there's no way to know when to the cancel
>> >> >> > request get
>> >> >> > to
>> >> >> > be processed, I think we can't not wait an experimental duration
>> >> >> > after
>> >> >> > cancelling like the attached patch.
>> >> >> >
>> >> >> > Does anyone have another cool idea to solve this issue?
>> >> >> >
>> >> >> > Regards.
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > ------------------------------------------------------------------------------
>> >> >> > CenturyLink Cloud: The Leader in Enterprise Cloud Services.
>> >> >> > Learn Why More Businesses Are Choosing CenturyLink Cloud For
>> >> >> > Critical Workloads, Development Environments & Everything In
>> >> >> > Between.
>> >> >> > Get a Quote or Start a Free Trial Today.
>> >> >> >
>> >> >> >
>> >> >> > http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
>> >> >> > _______________________________________________
>> >> >> > Postgres-xc-developers mailing list
>> >> >> > Pos...@li...
>> >> >> >
>> >> >> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >> >> ------------------------------------------------------------------------------
>> >> >> Android apps run on BlackBerry 10
>> >> >> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>> >> >> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>> >> >> Get your Android app in front of a whole new audience.  Start now.
>> >> >>
>> >> >>
>> >> >> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>> >> >>
>> >> >> _______________________________________________
>> >> >> Postgres-xc-developers mailing list
>> >> >> Pos...@li...
>> >> >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Andrei Martsinchyk
>> >> >
>> >> > StormDB - http://www.stormdb.com
>> >> > The Database Cloud
>> >> >
>> >
>> >
>> >
>> > ------------------------------------------------------------------------------
>> > Android apps run on BlackBerry 10
>> > Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>> > Now with support for Jelly Bean, Bluetooth, Mapview and more.
>> > Get your Android app in front of a whole new audience.  Start now.
>> >
>> > http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>> > _______________________________________________
>> > Postgres-xc-developers mailing list
>> > Pos...@li...
>> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>> >
>
>
>
>
> --
> Andrei Martsinchyk
>
> StormDB - http://www.stormdb.com
> The Database Cloud
>

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

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

2014-02-19 3:26 GMT+09:00 Amit Khandekar <ami...@en...>:
>
>
>
> On 13 February 2014 11:54, Ashutosh Bapat <ash...@en...>
> wrote:
>>
>> One more solution would be to use cursors for replicated tables. The idea
>> is to open cursors on all the copies of the table and append the query with
>> an ORDER BY clause on all the columns. Thus we are sure that the current of
>> each of these cursors point to same row on all the copies. While fetching a
>> row from a replicated table, we fetch from all the cursors and choose only
>> one row for the data processing. While updating or deleting we send UPDATE
>> or DELETE with WHERE CURRENT OF. The down side of this approach is that, if
>> there are coordinator quals, we will end up locking more rows than
>> necessary, increasing the probability of the deadlock but at least there
>> won't be a necessary restriction of having primary or unique key and we
>> won't break backward compatibility.
>>
>> If there two identical rows, we might mix the update from different nodes,
>> but then who knew which of them were corresponded across the nodes to start
>> with.
>>
>
> Locking all rows doesn't look good especially because we are looking for a
> permanent long term solution. If we can come up with some other solution
> that avoids this, we better avoid this compromise. For a replicated table
> with 10000 rows, all concurrent updates will be serialized even if they are
> updating a different row.
>
> Other thing is datanode performance impact for ORDER BY all columns,
> especially with many large size columns. I had also mentioned about ORDER BY
> in approach A. I am not sure whether there is some kind of optimization in
> the sort, such as: if we find unique rows with the first n columns, it does
> not compare the rest of the columns.
>
> I think declaring cursors is a cool idea in general for DMLs but it requires
> refactoring of DML planning, and also it requires ORDER BY. There is a
> concurrent update issue #398 for which we do require a refactor of DML
> handling. While doing that it will be clearer whether declaring cursor is
> really beneficial or if it's not feasible. For ORDER BY, again, for long
> term, we should have a primary key or an internal unique key so that rows
> can be ordered on that single column as against all columns. So again, we
> still are better of with a new system column.
>
>
> As regards to approach C, if we find a way to uniquely generate a new row id
> independently, then the task of generating rowid will be pretty lightweight.
> We won't require any other table to store it or generate it. The coordinator
> will generate it at each insert (both fqs and non-fqs), or may be datanodes
> themselves find a way to generate a new rowid which is always the same
> regardless of the datanode. A combination of gxid, timestamp and cmd id can
> be used to construct a unique rowid at the coordinator.
>
> I think one action plan can be :
> 1. Use Mason's patch and tweak it so that it needs very little modification
> later on if and when we add the system rowid column.
> 2. Check in the patch but let it not error out if the primary key is not
> there. This way we would at least make the replicated tables with primary
> keys work without data issues, but continue to work as it is now for tables
> without primary key.
> 3. Lastly support the new system row id implementation, and do an
> incremental change in Mason's checked in changes to use this id instead of
> primary key.

+1 for this.   This can fix many ctid issues especially at replicated
tables.   This also provide stable row identification in distributed
tables.   I hope this helps in adding general global constraint.

Regards;
---
Koichi Suzuki

>
>
>>
>> On Thu, Feb 13, 2014 at 9:45 AM, Koichi Suzuki <koi...@gm...>
>> wrote:
>>>
>>> Hi,
>>>
>>> I tested the patch and found that primary key is mandatory.   We need
>>> to modify regression test considerably to give each replicated table
>>> primary keys.
>>>
>>> I think this patch helps but I'm not afraid this is good, especially
>>> when we try to take XC features back to PG.
>>>
>>> Did you post another patch to use all column values if primary key is
>>> not available?
>>>
>>> I think better way is as follows:
>>>
>>> 1) If primary key is defined, use it,
>>> 2) If not, create a primary key as system column, the size should be
>>> 64bit.
>>> 3) If primary key is added to a replicated table, remove system primary
>>> key.
>>>
>>> The value of primary key can be obtained as follows:
>>>
>>> 1) add new column to pgxc_class catalog to represent maximum value of
>>> the system primary key,
>>> 2) when first "insert" is done to the primary node, system primary key
>>> value is taken from 1) and 1) is updated.  The value is returned to
>>> the coordinator to be propagated to other nodes.
>>> 3) when subsequent "insert" is being done, system primary key value is
>>> added to the column value.   In this case, each datanode updates 1)
>>> column value if it is larger than the current maximum value.
>>>
>>> 3) is important to change primary node to another.   This is needed to
>>> carry over the primary node to another.
>>>
>>> ALTER TABLE should take care of them.
>>>
>>> Other issues are:
>>>
>>> 4) pg_dump/pg_dumpall should not include this system column value,
>>> 5) cluster may need to handle this too to repack system primary key
>>> value (not now but at least in 1.3 or later).
>>>
>>> Regards;
>>> ---
>>> Koichi Suzuki
>>>
>>>
>>> 2013-11-02 9:26 GMT+09:00 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
>>> >
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Android apps run on BlackBerry 10
>>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>>> Get your Android app in front of a whole new audience.  Start now.
>>>
>>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>>> _______________________________________________
>>> Postgres-xc-developers mailing list
>>> Pos...@li...
>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>>
>>
>>
>> --
>> Best Wishes,
>> Ashutosh Bapat
>> EnterpriseDB Corporation
>> The Postgres Database Company
>>
>>
>> ------------------------------------------------------------------------------
>> Android apps run on BlackBerry 10
>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
>> Now with support for Jelly Bean, Bluetooth, Mapview and more.
>> Get your Android app in front of a whole new audience.  Start now.
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>

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

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

On Feb 16, 2014, at 11:13 PM, 鈴木 幸市 <ko...@in...> wrote:

> 1.1 is not suffered by this.    The bug was introduced during planner code change to deal with PG planner code change (mainly from automatic updatable views).

I don’t understand. I get this error on 1.1, and Abbas says it is not present in 1.2. That seems like an issue in 1.1 that might be fixable in a 1.1.1 release, should there ever be one. Am I missing something?

Best,

David