[Postgres-xl-developers] Ticket #13 Allow custom distribution functions

Petr Chmelar petr.chmelar at greycortex.com
Wed Aug 31 07:41:19 PDT 2016

Hi Developers-XL,

we would like to migrate from pgpool-II to Postgres-XL, since their 
parallel mode become obsolete 
in version 3.5.

The main issue for us is that we must use geographically distributed 
architecture and the lines interconnecting them are not always suitable 
for DISTRIBUTE BY alternatives you provide. We need PLpg/SQL or plain 
SQL function like "prefer_local" so the Coordinator may be able to 
determine the exact location of target row from incoming statement. 
Thus, we propose to develop the extension within GreyCortex.

We have investigated the possibility to add the locate_whatever_insert 
and locate_whatever_select functions into src/include/pgxc/locator.h and 
src/backend/pgxc/locator/locator.c and we have the following questions:

  * How to modify your SQL modifications, eg. DISTRIBUTE BY
    FUNCTION(custom_fun(column)) ?
  * What is the difference between DISTRIBUTE BY and DISTRIBUTED - it is
    not well explained in
    however it seems to use HASH by default.
  * How is it possible to pass the custom function name to the functions
    according to your SQL modifications? Can we use name or
    regprocedure/regproc (pg_proc), please?

We have also identified a show stopper concerning our deployment of 
PgXL. According to 
http://files.postgres-xl.org/documentation/queries-with.html you can do:

EXECUTE DIRECT ON (datanode_1)
    'WITH deleted AS (
           DELETE FROM ONLY table
           WHERE timestamp < now()
           RETURNING *
     SELECT *
     FROM deleted';

However, it says both with or without EXECUTE DIRECT on both Node and 
ERROR: INSERT/UPDATE/DELETE is not supported in subquery

We have found this in the planner.c (#ifdef XCP if (root->query_level > 
1) ereport ...). Does this mean PgXL doesn't support subqueries somehow?

So, the questions are:

  * Can we use standard PostgreSQL for data nodes, please?
  * Otherwise, can we allow INSERT/UPDATE/DELETE in subquery in case we
    perform this on a Node only?

  * Can we discuss our involvement in the development via Skype or
    phone, please?
  * Or should we contact someone from 2ndQuadrant, please?

We'll be happy to join the community. Thank you and cheers,

Michal Merta
DB Developer, GreyCortex

Petr Chmelar
CTO, GreyCortex
+420 602 54 2468
skype: petr.chmelar

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-developers-postgres-xl.org/attachments/20160831/ca1478d5/attachment.htm>

More information about the Postgres-xl-developers mailing list