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

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

I understood the situation.   So there's three areas we need to fix.

1) Trigger,
2) FQS-able updates/deletes.

I'm afraid we've not concluded how to solve these two yet.

3) Non-FQS cursor may work.   It will be nice if 1) and 2) solution
can be shred here too.

Abbas, could you summarize these three cases?

Regards;
---
Koichi Suzuki


2014-02-14 16:02 GMT+09:00 Ashutosh Bapat <ash...@en...>:
>
>
>
> On Fri, Feb 14, 2014 at 12:22 PM, Abbas Butt <abb...@en...>
> wrote:
>>
>> The case that we are trying to solve here is when the user statement is
>> not ship-able and has to be evaluated at the coordinator. If it was
>> ship-able, there is no problem in that case, it would get shipped like you
>> are suggesting. However if it is not ship-able, then we will have a step in
>> the query plan to first select the row to be updated and then a step to
>> update that row (update being a two step process). What Ashutosh is
>> suggesting is to have a cursor with an order by for all rows and the quals
>> that the user query had, and then update the row using WCO.
>> The reason of using cursors is to base the update on where the cursor
>> currently points to, rather than the ctid of the row, which could be
>> different on the datanodes.
>>
>
> Very well explained. Thank you. Hope that clears the doubts.
>
>>
>>
>> On Fri, Feb 14, 2014 at 11:22 AM, Koichi Suzuki <koi...@gm...>
>> wrote:
>>>
>>> If we can use the same ORDER BY clause, I don't understand why we need
>>> cursor.   We can just ship statements.
>>> ---
>>> Koichi Suzuki
>>>
>>>
>>> 2014-02-14 15:20 GMT+09:00 Abbas Butt <abb...@en...>:
>>> >
>>> >
>>> >
>>> > On Fri, Feb 14, 2014 at 10:58 AM, Koichi Suzuki <koi...@gm...>
>>> > wrote:
>>> >>
>>> >> 2014-02-14 14:55 GMT+09:00 Abbas Butt <abb...@en...>:
>>> >> >
>>> >> >
>>> >> >
>>> >> > On Fri, Feb 14, 2014 at 10:48 AM, Ashutosh Bapat
>>> >> > <ash...@en...> wrote:
>>> >> >>
>>> >> >>
>>> >> >>
>>> >> >>
>>> >> >> On Fri, Feb 14, 2014 at 7:25 AM, Abbas Butt
>>> >> >> <abb...@en...>
>>> >> >> wrote:
>>> >> >>>
>>> >> >>>
>>> >> >>>
>>> >> >>>
>>> >> >>> On Thu, Feb 13, 2014 at 11:24 AM, 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.
>>> >> >>>
>>> >> >>>
>>> >> >>> Thanks for the suggestion but we currently do not support WCO and
>>> >> >>> we
>>> >> >>> were
>>> >> >>> thinking of fixing this issue before we declare 1.2 beta is
>>> >> >>> generally
>>> >> >>> available.
>>> >> >>>
>>> >> >>
>>> >> >>
>>> >> >> Abbas, WCO doesn't work from the coordinator, but there is no
>>> >> >> reason
>>> >> >> why
>>> >> >> it shouldn't work at the datanode. So internally between
>>> >> >> coordinator
>>> >> >> and the
>>> >> >> datanode, we can always use WCO.
>>> >> >
>>> >> >
>>> >> > True, Thanks for the clarification.
>>> >>
>>> >> Again, there are no guarantee that all cursors for a replicated table
>>> >> returns rows in the same order.   It is as dangerous as ctid.
>>> >
>>> >
>>> > Could you please explain a little further, how would a query that has
>>> > all
>>> > table columns in the ORDER BY clause return rows in different order
>>> > when run
>>> > on the datanodes?
>>> >
>>> >
>>> >>
>>> >>
>>> >> >
>>> >> >>
>>> >> >>
>>> >> >>>>
>>> >> >>>>
>>> >> >>>>
>>> >> >>>> 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
>>> >> >>>>
>>> >> >>>
>>> >> >>>
>>> >> >>>
>>> >> >>> --
>>> >> >>> --
>>> >> >>> Abbas
>>> >> >>> Architect
>>> >> >>>
>>> >> >>> Ph: 92.334.5100153
>>> >> >>> Skype ID: gabbasb
>>> >> >>> www.enterprisedb.com
>>> >> >>>
>>> >> >>> Follow us on Twitter
>>> >> >>> @EnterpriseDB
>>> >> >>>
>>> >> >>> Visit EnterpriseDB for tutorials, webinars, whitepapers and more
>>> >> >>
>>> >> >>
>>> >> >>
>>> >> >>
>>> >> >> --
>>> >> >> Best Wishes,
>>> >> >> Ashutosh Bapat
>>> >> >> EnterpriseDB Corporation
>>> >> >> The Postgres Database Company
>>> >> >
>>> >> >
>>> >> >
>>> >> >
>>> >> > --
>>> >> > --
>>> >> > Abbas
>>> >> > Architect
>>> >> >
>>> >> > Ph: 92.334.5100153
>>> >> > Skype ID: gabbasb
>>> >> > www.enterprisedb.com
>>> >> >
>>> >> > Follow us on Twitter
>>> >> > @EnterpriseDB
>>> >> >
>>> >> > Visit EnterpriseDB for tutorials, webinars, whitepapers and more
>>> >
>>> >
>>> >
>>> >
>>> > --
>>> > --
>>> > Abbas
>>> > Architect
>>> >
>>> > Ph: 92.334.5100153
>>> > Skype ID: gabbasb
>>> > www.enterprisedb.com
>>> >
>>> > Follow us on Twitter
>>> > @EnterpriseDB
>>> >
>>> > Visit EnterpriseDB for tutorials, webinars, whitepapers and more
>>
>>
>>
>>
>> --
>> --
>> Abbas
>> Architect
>>
>> Ph: 92.334.5100153
>> Skype ID: gabbasb
>> www.enterprisedb.com
>>
>> Follow us on Twitter
>> @EnterpriseDB
>>
>> Visit EnterpriseDB for tutorials, webinars, whitepapers and more
>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company