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

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

EXPLAIN throws the same error.

On Thu, May 30, 2013 at 11:14 AM, Ashutosh Bapat <
ash...@en...> wrote:

>
>
>
> On Thu, May 30, 2013 at 2:10 AM, Abbas Butt <abb...@en...>wrote:
>
>> I have compared the expected output changes with the expected output file
>> in PG and found one more case where we needed to exchange command ids,
>> accommodated that case as well and committed the patch.
>>
>> I compared the sql files of PG and XC, and found that there are some test
>> cases missing in XC, however for some reason the syntax used in the new
>> statements is not yet supported by XC.
>> e.g.
>> On PG we get
>> test=# WITH outermost(x) AS (
>> test(#   SELECT 1
>> test(#   UNION (WITH innermost as (SELECT 2)
>> test(#          SELECT * FROM innermost
>> test(#          UNION SELECT 3)
>> test(# )
>> test-# SELECT * FROM outermost;
>>  x
>> ---
>>  1
>>  2
>>  3
>> (3 rows)
>>
>> where as on XC we get a syntax error
>>
>> test=# WITH outermost(x) AS (
>> test(#   SELECT 1
>> test(#   UNION (WITH innermost as (SELECT 2)
>> test(#          SELECT * FROM innermost
>> test(#          UNION SELECT 3)
>> test(# )
>> test-# SELECT * FROM outermost;
>> ERROR:  relation "innermost" does not exist
>> LINE 4:          SELECT * FROM innermost
>>                                ^
>> I have added a bug ID (3614136) in SF to track this issue.
>>
>>
> This isn't a syntax error. It looks to be a problem with WITH support.
> Somewhere it's not resolving innermost correctly (probably while sending
> the query to the datanodes). Can you please check the EXPLAIN output? Amit
> added support for WITH (I guess). Can you please assign it to Amit, if
> that's correct?
>
>
>>
>>
>> On Mon, May 27, 2013 at 7:53 AM, Abbas Butt <abb...@en...>wrote:
>>
>>>
>>>
>>> On Fri, May 24, 2013 at 7:54 AM, Ashutosh Bapat <
>>> ash...@en...> wrote:
>>>
>>>>
>>>>
>>>>
>>>> On Thu, May 23, 2013 at 10:23 PM, Ashutosh Bapat <
>>>> ash...@en...> wrote:
>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Thu, May 23, 2013 at 10:19 PM, Abbas Butt <
>>>>> abb...@en...> wrote:
>>>>>
>>>>>>
>>>>>>
>>>>>> On Fri, May 17, 2013 at 1:58 PM, Ashutosh Bapat <
>>>>>> ash...@en...> wrote:
>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Fri, May 17, 2013 at 2:23 PM, Abbas Butt <
>>>>>>> abb...@en...> wrote:
>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> On Thu, May 16, 2013 at 3:13 PM, Ashutosh Bapat <
>>>>>>>> ash...@en...> wrote:
>>>>>>>>
>>>>>>>>> Hi Abbas,
>>>>>>>>> I am also seeing a lot of changes in the expected output where the
>>>>>>>>> rows output have changed. What are these changes?
>>>>>>>>>
>>>>>>>>
>>>>>>>> These changes are a result of blocking partition column updates
>>>>>>>>
>>>>>>>
>>>>>>> Are those in sync with PG expected output?
>>>>>>>
>>>>>>
>>>>>> No, in PG the update does not fail, in XC it fails.
>>>>>>
>>>>>>
>>>>>>>  Why did we change the original expected output in first place?
>>>>>>>
>>>>>>
>>>>>> Do you mean that the changes in expected output due to blocking of
>>>>>> partition column updates should only be done in alternate expected output
>>>>>> file?
>>>>>>
>>>>>>
>>>>>
>>>>> yes, of course.
>>>>>
>>>>>
>>>>
>>>> This response can be confusing. If you are talking about the changing
>>>> of table distribution, then that has to be changed everywhere. But, I do
>>>> not understand, why should we see those many changes. The original output
>>>> file must have preserved the correct output, right?
>>>>
>>>
>>> Unfortunately the results in original output were incorrect, especially
>>> for the cases where it was possible to update partition column using WITH
>>> syntax.
>>> The patch fixes two issues
>>> 1. Block partition column updates using WITH syntax
>>> 2. WITH query that updates a table in the main query and inserts a row
>>> in the same table in the WITH query
>>> Hence there are more changes in the output files.
>>>
>>>
>>>>
>>>>
>>>>>
>>>>>>>
>>>>>>>> and changing the distribution of tables to replication.
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>> That's acceptable.
>>>>>>>
>>>>>>>
>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Thu, May 16, 2013 at 2:55 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?
>>>>>>>>>> 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.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> 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
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>>> ------------------------------------------------------------------------------
>>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> --
>>>>>> *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
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> 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>
>>>
>>
>>
>>
>> --
>> --
>> *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>