Re: [Postgres-xc-developers] Query Planning bug ?

[鈴木 幸市 <ko...@in...>]

Yeah, I found that this command stuck and doing this by direct connection to datanode works.

Regards;
---
Koichi Suzuki



On 2013/06/05, at 18:12, Tomonari Katsumata <kat...@po...> wrote:

> Hi,
> 
> The queries sent by Suzuki-san work fine,
> but my problem is still there.
> Could you try execute the query I sent before?
> 
> ---
> EXECUTE DIRECT ON (data1) $$
> SELECT
> count(*)
> FROM
> (SELECT * FROM pg_locks l LEFT JOIN
> (SELECT * FROM pg_stat_activity) s ON l.database = s.datid) a
> $$
> ---
> 
> I don't change this query because it work with Postgres-XC v1.0.
> 
> regards,
> -------
> NTT Software Corporation
> Tomonari Katsumata
> 
> (2013/06/05 16:20), Tomonari Katsumata wrote:
>> Hi, all
>> 
>> thank you for many responses!
>> 
>> OK, I'll try it with the current master.
>> 
>> It seems that it'll work fine...
>> 
>> Sorry for bothering you.
>> 
>> regards,
>> --------
>> NTT Software Corporation
>> Tomonari Katsumata
>> 
>> 
>> (2013/06/05 13:48), 鈴木 幸市 wrote:
>>> Now snapshot warning is disabled. Michael committed this patch.
>>> 
>>> I tested the query with the current master as of this noon and I got
>> (probably) correct result.
>>> Here's the result:
>>> 
>>> koichi=# execute direct on (datanode1) $$
>>> select count(*) from (select * from pg_locks) l left join
>>> (select * from pg_stat_activity) s on (l.database=s.datid);
>>> $$;
>>> count
>>> -------
>>> 9
>>> (1 row)
>>> 
>>> koichi=# \q
>>> …
>>> 
>>> koichi=# execute direct on (datanode1) $$
>>> koichi$# select count(*) from pg_locks l left join pg_stat_activity s
>>> koichi$# on (l.database=s.datid);
>>> koichi$# $$;
>>> count
>>> -------
>>> 11
>>> (1 row)
>>> 
>>> koichi=#
>>> 
>>> Second statement is simpler version. Anyway, they seem to work find.
>>> 
>>> Katsumata-san, could you try this with the latest head? It is
>> available both from sourceforge and github.
>>> Regards;
>>> ---
>>> Koichi Suzuki
>>> 
>>> 
>>> 
>>> On 2013/06/05, at 13:39, Ashutosh Bapat
>> <ash...@en...> wrote:
>>>> 
>>>> 
>>>> On Wed, Jun 5, 2013 at 9:57 AM, Tomonari Katsumata
>> <kat...@po...> wrote:
>>>> Hi Suzuki-san, Ashutosh,
>>>> 
>>>>> Suzuki-san
>>>> I don't make any user tables.
>>>> As the simple example I sent before, I use only system-catalogs.
>>>> 
>>>>> Ashtosh
>>>> I'm developing database monitor tool and
>>>> I use "EXECUTE DIRECT" to get database statistics data from
>>>> particular coordinator/datanode.
>>>> 
>>>> :) huh
>>>> 
>>>> I think, monitoring tools should directly query the datanodes or
>> coordinators. You will get snapshot warning, but that can be ignored I
>> guess. If they start querying coordinators, there will be performance
>> drop since coordinators directly handle the clients.
>>>> Any other thoughts?
>>>> regard,
>>>> 
>>>> ---------
>>>> NTT Software Corporation
>>>> Tomonari Katsumata
>>>> 
>>>> (2013/06/04 13:10), Ashutosh Bapat wrote:
>>>> Hi Tomonari,
>>>> 
>>>> Thanks for the bug report.
>>>> 
>>>> I am curious to know, what's the purpose of using EXECUTE DIRECT? We
>>>> discourage using Execute Direct in the applications. It's only for
>>>> debugging purposes.
>>>> 
>>>> 
>>>> On Tue, Jun 4, 2013 at 7:28 AM, Koichi Suzuki
>> <koi...@gm...>wrote:
>>>> Thank you Katsumata-san for the report.
>>>> 
>>>> Could you provide CREATE TABLE statement for each table involved with
>> some
>>>> of the data?
>>>> 
>>>> I will ad this to the bug tracker after I recreate the issue.
>>>> 
>>>> Best Regards;
>>>> 
>>>> ----------
>>>> Koichi Suzuki
>>>> 
>>>> 
>>>> 2013/6/4 Tomonari Katsumata <kat...@po...>
>>>> 
>>>> Hi, I have a problem with query executing.
>>>> 
>>>> I cant't have any response when I execute a query.
>>>> This problem occurs when some conditions are met.
>>>> 
>>>> The conditions are below.
>>>> ---------------------------------------------------------------------
>>>> 1. Issuing "EXECUTE DIRECT" to Datanode(ofcourse, via Coordinator).
>>>> 
>>>> 2. The Query Executing on Datanode has subquery on its FROM-clause.
>>>> 
>>>> 3. In the subquery, it has a JOIN clause.
>>>> 
>>>> 4. The Join clause is consisted with another subquery.
>>>> ---------------------------------------------------------------------
>>>> 
>>>> 
>>>> Simple example query is below.
>>>> ---------------------------------------------------------------
>>>> EXECUTE DIRECT ON (data1) $$
>>>> SELECT
>>>> count(*)
>>>> FROM
>>>> (SELECT * FROM pg_locks l LEFT JOIN
>>>> (SELECT * FROM pg_stat_activity) s ON l.database = s.datid) a
>>>> $$
>>>> ---------------------------------------------------------------
>>>> 
>>>> FYI:
>>>> This query works fine with Postgres-XC 1.0.3.
>>>> Is this already known bug ?
>>>> 
>>>> 
>>>> How can I avoid this problem ?
>>>> And what kind of info do you need to investigate it ?
>>>> 
>>>> ----------
>>>> NTT Software Corporation
>>>> Tomonari Katsumata
>>>> 
>>>> 
>>>> 
>>>> 
>>>> 
>> ------------------------------------------------------------------------------
>>>> How ServiceNow helps IT people transform IT departments:
>>>> 1. A cloud service to automate IT design, transition and operations
>>>> 2. Dashboards that offer high-level views of enterprise services
>>>> 3. A single system of record for all IT processes
>>>> http://p.sf.net/sfu/servicenow-d2d-j
>>>> _______________________________________________
>>>> Postgres-xc-developers mailing list
>>>> Pos...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>> 
>>>> 
>>>> 
>>>> 
>> ------------------------------------------------------------------------------
>>>> How ServiceNow helps IT people transform IT departments:
>>>> 1. A cloud service to automate IT design, transition and operations
>>>> 2. Dashboards that offer high-level views of enterprise services
>>>> 3. A single system of record for all IT processes
>>>> http://p.sf.net/sfu/servicenow-d2d-j
>>>> _______________________________________________
>>>> Postgres-xc-developers mailing list
>>>> Pos...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>> 
>>>> 
>>>> 
>>>> 
>>>> 
>>>> --
>>>> --------------------------------------------
>>>> NTTソフトウェア株式会社
>>>> 技術開発センター OSS基盤技術部門
>>>> 勝俣 智成
>>>> TEL：045-212-7665
>>>> FAX：045-662-7856
>>>> E-Mail: kat...@po...
>>>> --------------------------------------------
>>>> 
>>>> 
>>>> 
>>>> 
>>>> 
>>>> --
>>>> Best Wishes,
>>>> Ashutosh Bapat
>>>> EntepriseDB Corporation
>>>> The Postgres Database Company
>>>> 
>> ------------------------------------------------------------------------------
>>>> How ServiceNow helps IT people transform IT departments:
>>>> 1. A cloud service to automate IT design, transition and operations
>>>> 2. Dashboards that offer high-level views of enterprise services
>>>> 3. A single system of record for all IT processes
>>>> 
>> http://p.sf.net/sfu/servicenow-d2d-j_______________________________________________
>>>> Postgres-xc-developers mailing list
>>>> Pos...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>> 
>> 
>> 
>> 
>> ------------------------------------------------------------------------------
>> How ServiceNow helps IT people transform IT departments:
>> 1. A cloud service to automate IT design, transition and operations
>> 2. Dashboards that offer high-level views of enterprise services
>> 3. A single system of record for all IT processes
>> http://p.sf.net/sfu/servicenow-d2d-j
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>> 
> 
> 
> -- 
> --------------------------------------------
> NTTソフトウェア株式会社
>  技術開発センター OSS基盤技術部門
>  勝俣 智成
> TEL：045-212-7665
> FAX：045-662-7856
> E-Mail: kat...@po...
> --------------------------------------------
> 
> 
> 
> ------------------------------------------------------------------------------
> How ServiceNow helps IT people transform IT departments:
> 1. A cloud service to automate IT design, transition and operations
> 2. Dashboards that offer high-level views of enterprise services
> 3. A single system of record for all IT processes
> http://p.sf.net/sfu/servicenow-d2d-j
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>