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

[Tomonari K. <kat...@po...>]

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