[Postgres-xl-developers] DML in plpgsql functions

Roarke Wells roarke at roarkew.com
Fri Sep 12 12:06:51 PDT 2014

Thanks Mason,
We are having to use the execute direct workaround to force execution to
the coordinator in our functions (i.e. previous questions about pushing
functions to the coordinator).  My example queries were to show that the
information_schema did not appear to be accessible from the datanodes.  We
ran into an issue with a cursor in a function that executed fine against
PostgreSQL using the information_schema.  However it was failing in
Postgres-XL.  I tested the cursor query against both the coordinator and
the datanode.  It ran fine against the coordinator but failed against the
datanode so the execute direct was just to debug the underlying query.  The
pg_catalog schema appears to work fine for both coordinator and datanode so
I have converted it to use that syntax.  The cursor was querying for tables
with a specific naming scheme and then retrieving the column names.  Since
we declared the tables to be partitioned across all nodes it should be safe
to execute in a function at the node level.

On Fri, Sep 12, 2014 at 11:55 AM, Mason Sharp <msharp at translattice.com>

> On Fri, Sep 12, 2014 at 8:11 AM, Roarke Wells <roarke at roarkew.com> wrote:
>> Thanks for quick follow up.  Another in the continuing flood of
>> questions....
>> It appears that the information_schema is only available at the
>> coordinator level - i.e. this executes and returns the correct table data:
>> execute direct on (coord1) 'select * from information_schema.columns'
>> However, the following throws a "sql_identifier does not exist" error
>> execute direct on (node1) 'select * from information_schema.columns'
>> Does the information_schema only exist on the coordinator with no data
>> maintained on a node (even for tables that are partitioned to a specific
>> node)?
> EXECUTE DIRECT originally was really for debugging and allowing the DBA to
> troubleshoot problems. It was not intended for developer use.
> There should be no need to use EXECUTE DIRECT to query information_schema
> at the coordinator level; you can query it directly.
> In terms of why it is not working at the datanode level, I am not sure,
> that would take some more digging.  You can query pg_catalog tables like
> pg_class though as a work-around.
> Regards,
> --
> Mason Sharp
> TransLattice - http://www.translattice.com
> TransLattice Elastic Database (TED)
> Postgres-XL Support
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-developers-postgres-xl.org/attachments/20140912/5fc6e53e/attachment.htm>

More information about the Postgres-xl-developers mailing list