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

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

On Fri, May 17, 2013 at 2:26 PM, Ashutosh Bapat <
ash...@en...> wrote:

> This looks good. Are there other ways where we can have UPDATE statement
> somewhere in the query tree list?
>

I checked the UPDATE syntax and skimmed through the test cases in
regression, I did not find any other variation of UPDATE we need to take
care of.


> Do we need to worry about such cases.
>
>
> On Fri, May 17, 2013 at 2:22 PM, Abbas Butt <abb...@en...>wrote:
>
>>
>>
>> On Thu, May 16, 2013 at 2:25 PM, Ashutosh Bapat <
>> ash...@en...> wrote:
>>
>>> Hi Abbas,
>>> Instead of fixing the first issue in pgxc_build_dml_statement(), is it
>>> possible to traverse the Query in validate_part_col_updatable() recursively
>>> to find UPDATE statements and apply partition column check?
>>>
>>
>> Yes. I have attached that patch for your feedback. If you think its ok I
>> can send the updated patch including the rest of the changes.
>>
>>
>>>  That would cover all the possibilities, I guess. That also saves us
>>> much effort in case we come to support distribution column updation.
>>>
>>> I think, we need a generic solution to solve this command id issue, e.g.
>>> punching command id always and efficiently. But for now this suffices.
>>> Please log a bug/feature and put it in 1.2 bucket.
>>>
>>
>> Done.
>> (Artifact 3613498<https://sourceforge.net/tracker/?func=detail&aid=3613498&group_id=311227&atid=1310235>
>> )
>>
>>>
>>>
>>>
>>>
>>> On Wed, May 15, 2013 at 5:31 AM, Abbas Butt <abb...@en...
>>> > wrote:
>>>
>>>> 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>
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> AlienVault Unified Security Management (USM) platform delivers complete
>>>> security visibility with the essential security capabilities. Easily and
>>>> efficiently configure, manage, and operate all of your security controls
>>>> from a single console and one unified framework. Download a free trial.
>>>> http://p.sf.net/sfu/alienvault_d2d
>>>> _______________________________________________
>>>> Postgres-xc-core mailing list
>>>> Pos...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-core
>>>>
>>>>
>>>
>>>
>>> --
>>> Best Wishes,
>>> Ashutosh Bapat
>>> EntepriseDB Corporation
>>> The Postgres Database Company
>>>
>>
>>
>>
>> --
>> --
>> *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>
>>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EntepriseDB Corporation
> The Postgres Database Company
>



-- 
-- 
*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>