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

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

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
>