Re: [Postgres-xc-developers] Regarding bug id 3607975

[Ashutosh B. <ash...@en...>]

On Fri, May 24, 2013 at 9:01 AM, Abbas Butt <abb...@en...>wrote:

>
>
> On Fri, May 24, 2013 at 7:22 AM, Ashutosh Bapat <
> ash...@en...> wrote:
>
>>
>>
>>
>> On Thu, May 23, 2013 at 9:21 PM, Abbas Butt <abb...@en...>wrote:
>>
>>> Hi,
>>>
>>> While working on test case plancache it was brought up as a review
>>> comment that solving bug id 3607975 should solve the problem of the test
>>> case.
>>> However there is some confusion in the statement of bug id 3607975.
>>>
>>> "When a user does and PREPARE and then EXECUTEs multiple times, the
>>> coordinator keeps on preparing and executing the query on datanode al
>>> times, as against preparing once and executing multiple times. This is
>>> because somehow the remote query is being prepared as an unnamed statement."
>>>
>>> Consider this test case
>>>
>>> A. create table abc(a int, b int);
>>> B. insert into abc values(11, 22);
>>> C. prepare p1 as select * from abc;
>>> D. execute p1;
>>> E. execute p1;
>>> F. execute p1;
>>>
>>> Here are the confusions
>>>
>>> 1. The coordinator never prepares on datanode in response to a prepare
>>> issued by a user.
>>>      In fact step C does nothing on the datanodes.
>>>      Step D simply sends "SELECT a, b FROM abc" to all datanodes.
>>>
>>> 2. In step D, ExecuteQuery calls BuildCachedPlan to build a new generic
>>> plan,
>>>     and steps E and F use the already built generic plan.
>>>     For details see function GetCachedPlan.
>>>     This means that executing a prepared statement again and again does
>>> use cached plans
>>>     and does not prepare again and again every time we issue an execute.
>>>
>>>
>> The problem is not here. The problem is in do_query() where somehow the
>> name of prepared statement gets wiped out and we keep on preparing unnamed
>> statements at the datanode.
>>
>
> We never prepare any named/unnamed statements on the datanode. I spent
> time looking at the code written in do_query and functions called from with
> in do_query to handle prepared statements but the code written in
> pgxc_start_command_on_connection to handle statements prepared on datanodes
> is dead as of now. It is never called during the complete regression run.
> The function ActivateDatanodeStatementOnNode is never called. The way
> prepared statements are being handled now is the same as I described
> earlier in the mail chain with the help of an example.
> The code that is dead was originally added by Mason through commit
> d6d2d3d925f571b0b58ff6b4f6504d88e96bb342, back in December 2010. This code
> has been changed a lot over the last two years. This commit does not
> contain any test cases so I am not sure how did it use to work back then.
>
>

This code wasn't dead, when I worked on prepared statements. So, something
has gone wrong in-between. That's what we need to find out and fix. Not
preparing statements on the datanode is not good for performance either.


>
>>
>>> My conclusion is that the bug ID 3607975 is not reproducible.
>>>
>>>
>> Did you verify it under the debugger? If that would have been the case,
>> we would not have seen this problem if search_path changed in between steps
>> D and E.
>>
>
> If search path is changed between steps D & E, the problem occurs because
> when the remote query node is created, schema qualification is not added in
> the sql statement to be sent to the datanode, but changes in search path do
> get communicated to the datanode. The sql statement is built when execute
> is issued for the first time and is reused on subsequent executes. The
> datanode is totally unaware that the select that it just received is due to
> an execute of a prepared statement that was prepared when search path was
> some thing else.
>
>
Fixing the prepared statements the way I suggested, would fix the problem,
since the statement will get prepared at the datanode, with the same search
path settings, as it would on the coordinator.


>
>
>>
>>
>>>  Comments are welcome.
>>>
>>> --
>>> *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>
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Try New Relic Now & We'll Send You this Cool Shirt
>>> New Relic is the only SaaS-based application performance monitoring
>>> service
>>> that delivers powerful full stack analytics. Optimize and monitor your
>>> browser, app, & servers with just a few lines of code. Try New Relic
>>> and get this awesome Nerd Life shirt!
>>> http://p.sf.net/sfu/newrelic_d2d_may
>>> _______________________________________________
>>> Postgres-xc-developers mailing list
>>> Pos...@li...
>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>
>>>
>>
>>
>> --
>> 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