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

[Ashutosh B. <ash...@en...>]

Hi Tomonari,



On Wed, Jun 12, 2013 at 10:17 AM, Tomonari Katsumata <
kat...@po...> wrote:

> 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.
>
>
I guess, we are talking about EXECUTE DIRECT statement that you have
mentioned earlier. The function pgxc_collect_RTE_walker() is a recursive
function. The condition to end the recursion is if the given node is NULL.
We have to look at if that condition is met and if not why.


> 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
>
>
>
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Windows:
>
> Build for Windows Store.
>
> http://p.sf.net/sfu/windows-dev2dev
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>



-- 
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Postgres Database Company