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

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

Hi,

I've investigated this problem(BUG:3614369).

I caught the cause of it, but I can not
find where to fix.

The problem occurs when "pgxc_collect_RTE_walker" is called infinitely.
It seems that rtable(List of RangeTable) become cyclic List.
I'm not sure where the List is made.

Anybody, please give me your help ?
If not so difficult to fix it, I want to fix soon.

--------
NTT Software Corporation
Tomonari Katsumata

(2013/06/06 13:55), Koichi Suzuki wrote:
 > I added this to the bug tracker with the ID 3614369
 >
 > Regards;
 >
 > ----------
 > Koichi Suzuki
 >
 >
 > 2013/6/5 鈴木 幸市 <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