Re: [Postgres-xc-developers] how to fix EXECUTE DIRECT?

[Mason S. <mas...@en...>]

On 12/16/10 7:18 PM, Koichi Suzuki wrote:
> Hmm...  I thought it will be reasonable enough just to allow SELECT 
> (and COMMIT/ABORT) statement in EXECUTE DIRECT semantics.   Also, 
> because we've changed the infrastructure of aggregate functions, I 
> agree it will not safe enough to run such functions just in the 
> coordinator.  We need an infrastructure as Benny pointed out:
>
> SELECT count(*) from A, A;
>
> Because EXECUTE DIRECT is just for housekeeping usage, I think it will 
> also be reasonable to put some restriction which is sufficient for the 
> dedicated use.
>
> In this case, because 2PC recovery does not need aggregate, I think we 
> can have this as is.
>
Aggregate was an example. I am not sure, there may be other unexpected 
side effects, I just stumbled upon this when I started testing. I think 
we should really keep this simple and simply pass down the statement as 
is down to the nodes. That is intuitive. The results I see are kind of 
weird. It is not simply passing down the statement but somehow trying to 
parallelize it.  I don't think that is what we want, and I am worried 
about unexpected results for other statements. I really think we should 
change this.

Thanks,

Mason


> Regards;
> ---
> Koichi
>
> (2010年12月17日 07:09), Mason Sharp wrote:
>> On 12/16/10 1:51 AM, Michael Paquier wrote:
>>> Hi all,
>>>
>>> I extended the patch so as to be able to launch utilities on targeted
>>> nodes (datanodes and Coordinators).
>>> EXECUTE DIRECT is still restricted for UPDATE and DELETE.
>>> And it is still not possible to launch a query on the local
>>> Coordinator without spreading it to the other nodes.
>>>
>>> With this patch, in the case of a 2PC transaction that is partially
>>> committed or partially aborted in the cluster,
>>> EXECUTE DIRECT can be used to target specific nodes where to send a
>>> COMMIT PREPARED or ABORT PREPARED.
>>>
>>> This is definitely useful for HA features and recovery also.
>>>
>>
>> Michael,
>>
>> in pgxc_planner(), is that block of code for only when executing on a
>> local coordinator?  Could it be safely handled above the switch()
>> statement? I mean, if it is EXECUTE DIRECT, we just want to pass down
>> the SQL string and have it executed as is.
>>
>> I ran some brief tests.
>>
>> DBT1=# EXECUTE DIRECT on NODE 1 'select count(*) from orders';
>>    count
>> -------
>>     1269
>> (1 row)
>>
>> DBT1=# EXECUTE DIRECT on NODE 2 'select count(*) from orders';
>>    count
>> -------
>>     1332
>> (1 row)
>>
>> DBT1=# EXECUTE DIRECT on NODE 1,2 'select count(*) from orders';
>>    count
>> -------
>>     2601
>> (1 row)
>>
>>
>> For this last one, I expected to see two rows. That is, it passes down
>> the exact SQL string, then shows the results of each. It looks like it
>> is hooking into our general planning. We don't want the aggregate
>> managed on the coordinator (hmmm, although it may open up interesting
>> ideas in the future...).
>>
>> Similarly, something is not quite right with group by:
>>
>> DBT1=# EXECUTE DIRECT on NODE 1,2 'select o_status, count(*) from orders
>> group by o_status';
>> ERROR:  unrecognized node type: 656
>>
>>
>> DBT1=# EXECUTE DIRECT on NODE 2 'select o_status, count(*) from orders
>> group by o_status';
>>    o_status | count
>> ----------+-------
>>             |  1332
>> (1 row)
>>
>> Here, too, I think we should just get the results as if 'select
>> o_status, count(*) from orders group by o_status' was executed on each
>> node, all thrown together in the results (long term we could add an
>> optional NODE column, like GridSQL).
>>
>> Perhaps this helps simplify things a bit.
>>
>> Thanks,
>>
>> Mason
>>
>>
>>> Thanks,
>>>
>>> -- 
>>> Michael Paquier
>>> http://michaelpq.users.sourceforge.net
>>>
>>
>>
>


-- 
Mason Sharp
EnterpriseDB Corporation
The Enterprise Postgres Company


This e-mail message (and any attachment) is intended for the use of
the individual or entity to whom it is addressed. This message
contains information from EnterpriseDB Corporation that may be
privileged, confidential, or exempt from disclosure under applicable
law. If you are not the intended recipient or authorized to receive
this for the intended recipient, any use, dissemination, distribution,
retention, archiving, or copying of this communication is strictly
prohibited. If you have received this e-mail in error, please notify
the sender immediately by reply e-mail and delete this message.