Re: [Postgres-xc-developers] Patch for pg_catalog-based views and pgadmin

[Mason S. <mas...@en...>]

Koichi Suzuki wrote:
> Hi Mason-san;
>
> Thanks a lot for the work.  I'd like to summarize what we can and what
> we cannot with PG-XC and pgadmin.
> ----------
> Koichi Suzuki
>   

With pgadmin, one can connect, browse the schema, tables, columns, view 
and edit data, etc. I just applied Andrei's aggregate patch, so SELECT 
COUNT(*) now works and that error no longer occurs as well.

Pgadmin is not "XC-aware", it just treats it as an 8.4 instance. That 
means you do not get an accurate picture of statistics and such on 
tables. We can later on have ANALYZE and VACUUM perhaps pull some of 
this info up from the data nodes and store the info in the 
coordinator(s) (or make these a view and pull data from the data 
nodes?). Long term we could also add distribution, replication, and 
perhaps even node information.

I did not spend too much time with pgadmin, but it is a smooth 
experience now. One thing that I noticed was it does not show defined 
sequences. Even if these are handled on GTM, we could update pg_catalog 
to view this information. I can create an issue for this on SF tracker.

Regards,

Mason


>
>
> 2010/5/13 Mason Sharp <mas...@en...>:
>   
>> Pavan Deolasee wrote:
>>     
>>> On Wed, May 12, 2010 at 3:19 AM, Mason Sharp
>>> <mas...@en... <mailto:mas...@en...>>
>>> wrote:
>>>
>>>     I started the task to recognize more "Postgres-XC safe" queries.
>>>
>>>     I initially focused on native pg_catalog based views like
>>>     pg_settings. This is because it used by pgadmin. With the attached
>>>     patch, pgadmin no longer complains about such queries because they
>>>     are now supported.
>>>
>>>     The PostgreSQL query rewriter appears to convert the view into a
>>>     subquery. I modified the XC planner to check for pg_catalog table
>>>     usage in the FROM clause (and subqueries thereof).
>>>
>>>
>>> FWIW here are couple of things that I noticed:
>>>
>>> +   foreach(item, rtable)
>>> +   {
>>> +       RangeTblEntry *rte = (RangeTblEntry *) lfirst(item);
>>> +
>>> +       if (rte->rtekind == RTE_RELATION)
>>> +       {
>>> +           if (rte->relid > FirstNormalObjectId)
>>> +               return false;
>>> +       } else if (rte->rtekind == RTE_SUBQUERY)
>>> +           return contains_only_pg_catalog (rte->subquery->rtable);
>>> +   }
>>>
>>> This code will break out of the loop if the subquery contains only
>>> pg_catalog tables. But other rtable entries may contain reference to
>>> non-system tables.
>>>
>>> The other thing I am worried about is whether its safe to just rely on
>>> FirstNormalObjectId to decide if a relation belongs to pg_catalog or
>>> not. A user can create a table in pg_catalog (though I think it
>>> requires changes to GUC) and that may get an OID larger than the
>>> FirstNormalObjectId or we may add a new default schema and create
>>> tables in the schema as part of initdb (we do that routinely in EDBAS
>>> for example). Should we just explicitly lookup the namespace of the
>>> relation instead ?
>>>
>>>       
>> Thanks, I made those changes and committed.
>>
>> Mason
>>
>>     
>>> Thanks,
>>> Pavan
>>>
>>> --
>>> Pavan Deolasee
>>> EnterpriseDB     http://www.enterprisedb.com
>>>       
>> --
>> Mason Sharp
>> EnterpriseDB Corporation
>> The Enterprise Postgres Company
>>
>>
>> This e-mail message (and any attachment) is intended for the use of
>> the individual or entity to whom it is addressed. This message
>> contains information from EnterpriseDB Corporation that may be
>> privileged, confidential, or exempt from disclosure under applicable
>> law. If you are not the intended recipient or authorized to receive
>> this for the intended recipient, any use, dissemination, distribution,
>> retention, archiving, or copying of this communication is strictly
>> prohibited. If you have received this e-mail in error, please notify
>> the sender immediately by reply e-mail and delete this message.
>>
>>
>> ------------------------------------------------------------------------------
>>
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>>     


-- 
Mason Sharp
EnterpriseDB Corporation
The Enterprise Postgres Company


This e-mail message (and any attachment) is intended for the use of
the individual or entity to whom it is addressed. This message
contains information from EnterpriseDB Corporation that may be
privileged, confidential, or exempt from disclosure under applicable
law. If you are not the intended recipient or authorized to receive
this for the intended recipient, any use, dissemination, distribution,
retention, archiving, or copying of this communication is strictly
prohibited. If you have received this e-mail in error, please notify
the sender immediately by reply e-mail and delete this message.