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.