postgres-xc-developers

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

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

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).

In addition, pgadmin was sending "SET client_encoding to 'UNICODE'". It 
was being swallowed and proxied by Postgres-XC, but no response was sent 
back because it did not recognize the 'S' message response. I have now 
added support for that, too.

Note that we have planned a whole separate task planned for proper SET 
handling. These commands will now be processed, but the user should not 
assume that these will remain set, due to Postgres-XC pooler. Again, we 
will address this in the future.

With this patch pgadmin runs much more smoothly when connected to 
Postgres-XC. Users can view schema information and issue SQL 
statements.  There is still an error window that pops up when clicking 
on a table because it issues a "SELECT count(*) FROM table", which is 
not yet supported in Postgres-XC. This problem will disappear soon 
however, as a patch is almost ready for basic aggregate support.

Thanks,

Mason


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

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

[Pavan D. <pav...@gm...>]

On Wed, May 12, 2010 at 3:19 AM, Mason Sharp
<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,
Pavan

-- 
Pavan Deolasee
EnterpriseDB     http://www.enterprisedb.com

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

[Mason S. <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.

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

[Koichi S. <koi...@gm...>]

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



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
>

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.