[pgpointcloud] Upgrading pointcloud extension

[Trey Stafford]

Hello,

We have a rather large pointcloud-enabled database (~1.7 TB) that we are 
looking to upgrade to the newest version of the pointcloud extension 
(v1.2.0).

Our database started as a prototype, and has expanded to a limited 
production environment. At the time the database was initialized, the 
pointcloud extension was build with code checked out at commit 
102f366c5ef0ff8ed55b9cdc9f58c20e2ef9db38 (merge of PR 155, between 
versions v0.1.0 and v1.1.0).

I have attempted to upgrade from that code to v1.2.0, but have run into 
some issues. I am hoping that somebody here can point me in the right 
direction/toward the best approach!

My first attempt was simply to install the v1.2.0 code over the existing 
pointcloud installation. I issued the "ALTER EXTENSION pointcloud UPDATE 
TO '1.2.0'" command with psql, and the command successfully completed.

However, the 'pc_' functions are not working as expected. For example:

```

valkyrie=# select pc_PatchMin(pa, 'DateTime') from atm1b limit 2;

ERROR:  function pc_patchmin(pcpatch, unknown) is not unique

LINE 1: select pc_PatchMin(pa, 'DateTime') from atm1b limit 2;

                ^

HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

```


Moreover, I attempted to upgrade the "pointcloud_postgis" extension 
without success:

```

valkyrie=# ALTER EXTENSION pointcloud_postgis UPDATE TO '1.2.0';

ERROR:  extension "pointcloud_postgis" has no update path from version "1.0" to version "1.2.0"

```


Does anyone know of a reliable way to upgrade these two extensions, and 
avoid the issues I have run into? I fear that we may need to dump the 
database contents (e.g., with pg_dump), create a new database with 
pointcloud v1.2.0 installed, and then do a pg_restore.

Unfortunately, I have also had some problems with that approach as well. 
After dumping the data from our existing database with pg_dump, I 
attempted to restore only to get the following error:

```

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 3636; 0 17955 TABLE DATA atm1b valkyrie

pg_restore: [archiver (db)] COPY failed for table "atm1b": ERROR:  pc_patch_lazperf_from_wkb: lazperf support is not enabled

CONTEXT:  COPY atm1b, line 1, column pa: "0101000000020000009001000003BC03000078DA85966B884D5114C7356992346912F27E4792E6C33449DA4942681A6A9234..."

```


I see that lazpref is an optional dependency for storing patches with 
LAZ compression, according to the pointcloud README. Our database uses 
dimensional compression, not LAZ, so this is a bit confusing to me. To 
make sure pg_dump and pg_restore were actually working as expected, I 
tried to restore the dumped data into a fresh database with the same 
version of pointcloud installed that we have been using. I was able to 
restore to that database without any issue.

Worst-case scenario is that we have to re-ingest all of our data into a 
new instance of the database. That would take quite a while, and I would 
like to avoid doing that if at all possible. I am still investigating 
alternative solutions, but any feedback or suggestions would be of 
tremendous help!

Thanks,

Trey Stafford

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgpointcloud/attachments/20181102/b301debb/attachment.html>