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

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

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