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

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Snapshot warning comes when you connect directly to the datanode and not
when an execute direct is issued for a datanode. That warning is still
there.

    if (IS_PGXC_DATANODE && !isRestoreMode &&
        snapshot_source == SNAPSHOT_UNDEFINED &&
        IsPostmasterEnvironment &&
        IsNormalProcessingMode() &&
        !IsAutoVacuumLauncherProcess())
    {
        elog(WARNING, "Do not have a GTM snapshot available");
    }

On Wed, Jun 5, 2013 at 9:48 AM, 鈴木 幸市 <ko...@in...> 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 <katsumata.tomonari@po.ntts.**co.jp<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<http://p.sf.net/sfu/servicenow-d2d-j>
>>>>> ______________________________**_________________
>>>>> Postgres-xc-developers mailing list
>>>>> Postgres-xc-developers@lists.**sourceforge.net<Pos...@li...>
>>>>> https://lists.sourceforge.net/**lists/listinfo/postgres-xc-**
>>>>> developers<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<http://p.sf.net/sfu/servicenow-d2d-j>
>>>> ______________________________**_________________
>>>> Postgres-xc-developers mailing list
>>>> Postgres-xc-developers@lists.**sourceforge.net<Pos...@li...>
>>>> https://lists.sourceforge.net/**lists/listinfo/postgres-xc-**developers<https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers>
>>>>
>>>>
>>>>
>>>
>>
>> --
>> ------------------------------**--------------
>> NTTソフトウェア株式会社
>>   技術開発センター OSS基盤技術部門
>>   勝俣 智成
>> TEL：045-212-7665
>> FAX：045-662-7856
>> E-Mail: kat...@po....**jp<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
>
>

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