[pgpointcloud] pg pointcloud with Postgres-XL?

[Paul Ramsey]

You don't want the pointcloud_formats table "distributed" (bits of it
on each node, depending on what primary key value the bit has) you
want it "replicated" (ie, a complete copy of it on every node). So,
whatever magic incantation accomplishes that is what you want. Sounds
like you'll need to cludge the pgpointcloud sql file as you've already
done to add the right keyword, so that magic occurs. Seems very
achievable! Looking forward to hearing your experience, as I've always
thought that for pointcloud-in-db to scale up using a cluster like
PgXL or PgXC would be required.

P.


On Mon, May 30, 2016 at 7:18 AM, Jo Walsh <metazool at gmail.com> wrote:
> Paul suggested offlist that I try dropping the pointcloud_formats table and
> re-creating it adding a DISTRIBUTE BY REPLICATION clause. I wasn't able to
> do this from the console but managed to kludge it into a working state by
> editing pgsql/sql/pointcloud.sql and rebuilding and reloading the pointcloud
> extension.
>
> The thing is, that tables in Postgres-XL do (usually) get distributed by
> default, by one mechanism or another, if no DISTRIBUTE BY clause is
> specified:
>
> "If DISTRIBUTE BY is not specified, columns with UNIQUE constraint will be
> chosen as the distribution key. If no such column is specified, distribution
> column is the first eligible column in the definition. If no such column is
> found, then the table will be distributed by ROUNDROBIN."
>
> http://files.postgres-xl.org/documentation/sql-createtable.html
>
> So i can create tables via ogr2ogr for example, and that works as expected
> as with regular postgres and i see the records being distributed around the
> data nodes. When I CREATE EXTENSION postgis on a coordinator node the tables
> and functions get propagated to the data nodes.
>
> I am still getting to grips with Postgres-XL, my understanding is weak and I
> and still do not see why this particular table wasnt getting distributed,
> something to do with the order of the CHECK constraint and the function
> which it is calling?
>
> Anyway, at least with this kludge I am now able to test out PDAL :) If I
> arrive at a better understanding or a solution I will certainly let you
> know.
>
>
> cheers,
>
>
> Jo
>
>
> On Mon, May 30, 2016 at 3:02 PM, Rémi Cura <remi.cura at gmail.com> wrote:
>>
>> Hey,
>> I often wondered if pgpointcloud would work with postgresXL.
>>
>> I don't have any knwoledge of postgresxl,
>> but I would be very gratefull if you could keep us posted!
>>
>> Cheers,
>> Rémi-C
>>
>> 2016-05-30 11:49 GMT+02:00 Jo Walsh <metazool at gmail.com>:
>>>
>>> I wonder if anyone has tried out and had success with the PG Point Cloud
>>> extension with Postgres-XL, the clustering version of Postgres? I am
>>> running through the basic
>>> exercises and see it all almost, but not quite, working. I tried the
>>> Postgres-XL general list without any luck in response, so thought it worth
>>> re-posting here to see if it's something people have given thought to.
>>>
>>> http://www.postgres-xl.org/overview/
>>>
>>> I am able to CREATE EXTENSION pointcloud on a Postgres-XL coordinator
>>> node
>>> just as straightforwardly as with PostGIS, which works well.
>>>
>>> However, the pgpointcloud creates a table pointcloud_formats which does
>>> not appear to be getting distributed.
>>> The table looks like this:
>>>
>>> testdb=# \d++ pointcloud_formats;
>>>                   Table "public.pointcloud_formats"
>>>  Column |  Type   | Modifiers | Storage  | Stats target | Description
>>> --------+---------+-----------+----------+--------------+-------------
>>>  pcid   | integer | not null  | plain    |              |
>>>  srid   | integer |           | plain    |              |
>>>  schema | text    |           | extended |              |
>>> Indexes:
>>>     "pointcloud_formats_pkey" PRIMARY KEY, btree (pcid)
>>> Check constraints:
>>>     "pointcloud_formats_pcid_check" CHECK (pcid > 0 AND pcid < 65536)
>>>     "pointcloud_formats_schema_check" CHECK (pc_schemaisvalid(schema))
>>>
>>> When I INSERT into it, data does not end up stored in the datanodes, and
>>> when I subsequently try to INSERT into the points table,
>>>
>>> ERROR:  no entry in "pointcloud_formats" for pcid = 1
>>>
>>> Despite the fact that on the coordinator node I can SELECT from
>>> pointcloud_formats and see a row with pcid=1.
>>>
>>> There's more context including log entries in my original post to the
>>> Postgres-XL list here:
>>>
>>> https://sourceforge.net/p/postgres-xl/mailman/message/35119267/
>>>
>>> I know this is a long shot but any illumination would be appreciated,
>>>
>>> Jo
>>>
>>>
>>>
>>> _______________________________________________
>>> pgpointcloud mailing list
>>> pgpointcloud at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/pgpointcloud
>>
>>
>
>
> _______________________________________________
> pgpointcloud mailing list
> pgpointcloud at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgpointcloud