Re: [Postgres-xc-developers] Fix for test case with.sql

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

Adding developers mailing list.

On Wed, May 15, 2013 at 4:57 AM, Abbas Butt <abb...@en...>wrote:

> Hi,
> Attached please find a patch to fix test case with.
> There were two issues making the test to fail.
> 1. Updates to partition column were possible using syntax like
>     WITH t AS (UPDATE y SET a=a+1 RETURNING *) SELECT * FROM t
>     The patch blocks this syntax.
>
> 2.  For a WITH query that updates a table in the main query and
>      inserts a row in the same table in the WITH query we need to use
>      command ID communication to remote nodes in order to
>      maintain global data visibility.
>      For example
>      CREATE TEMP TABLE tab (id int,val text) DISTRIBUTE BY REPLICATION;
>      INSERT INTO tab VALUES (1,'p1');
>      WITH wcte AS (INSERT INTO tab VALUES(42,'new') RETURNING id AS newid)
>      UPDATE tab SET id = id + newid FROM wcte;
>      The last query gets translated into the following multi-statement
>      transaction on the primary datanode
>      (a) START TRANSACTION ISOLATION LEVEL read committed READ WRITE
>      (b) INSERT INTO tab (id, val) VALUES ($1, $2) RETURNING id --
> (42,'new)'
>      (c) SELECT id, val, ctid FROM ONLY tab WHERE true
>      (d) UPDATE ONLY tab tab SET id = $1 WHERE (tab.ctid = $3) --
> (43,(0,1)]
>      (e) COMMIT TRANSACTION
>      The command id of the select in step (c), should be such that
>      it does not see the insert of step (b)
>
> Comments are welcome.
>
> Regards
>
> --
> *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>