"temporal values cannot overlap on time" after atGeometry about mobilitydb

[mschoema]

Hi Wendell,

It seems that this is indeed a bug with the atGeometry function in the latest Beta.
I also tested this query on our develop branch, and the bug seems to be resolved.

Currently, if you need to use the atGeometry function, I would suggest using the latest version of the Develop branch.
We are preparing a new release that contains these modifications, but the next release date is not yet decided.

If you need help installing MobilityDB from the latest development branch, please let me know, I will be happy to help.

Maxime

[wendellwt]

Maxime,

This doesn't happen often in my dataset, and it is not critical to fix, but yes, I would like to try out the Develop branch. I think that would be fun. I just want to make sure I can install the new one without causing PosgGreSQL to delete current tgeogpoint columns. (I found out the hard way that DROP EXTENSION MobilityDB does that, which turned out to be a bad thing to do.) I suppose the updgrade would be something like:

cmake ..
make
<stop postgresql>
sudo make install
<start postgresql>
psql -c 'ALTER EXTENSION MobilityDB UPDATE'

• Q1) is it ok to do the make install and replace the existing libMobilityDB.so with the new one? I don't have to re-compile PostGreSQL or PostGIS do I?
• Q2) when I run the "ALTER EXTENSION" command, the old .so file has already been replaced with the new one. Is that the right way to do it?
• Q3) The PostGreSQL doc says "The extension must supply a suitable update script". Is that in place? Will the 'ALTER EXTENSION' know where that script is and how to run it?

I just want to be careful when I do this update.

Thanks,
Wendell

[mschoema]

This would be the right way to do it, but unfortunately, we do not have a suitable update script.
Currently, the only way to change versions is by dropping and re-creating the extension.

cmake ..
make
<stop postgresql>
sudo make install
<start postgresql>
psql -c 'DROP EXTENSION MobilityDB CASCADE'
psql -c 'CREATE EXTENSION MobilityDB CASCADE'

This will indeed require you to re-create the tgeogpoint and tgeompoint columns in the database.
And to answer Q1, no you do not need to re-compile PostgreSQL and PostGIS, running the sudo make install is sufficient.

One solution to not lose the tgeompoint columns completely is to store them as bytes in a temporary column, and to then re-create the initial columns in the new version of the extension.

<before dropping the extension>
ALTER TABLE table_name ADD COLUMN temp_name bytea;
UPDATE table_name SET temp_name = asEWKB(column_name);

<after dropping and re-creating the extension>
ALTER TABLE table_name ADD COLUMN column_name tgeompoint;
UPDATE table_name SET column_name = fromEWKB(temp_name);
ALTER TABLE table_name DROP COLUMN temp_name;

The function 'fromEWKB' only exists for tgeompoint currently, so you will need to cast your tgeogpoints into tgeompoints and back to use this technique for tgeogpoint columns.

Maxime

[estebanzimanyi]

We introduced input/output functions for all formats (Text, EWKT, MF-JSON, Binary, EWKB, HexWKB). They will be included in the next beta. Many thanks for helping us to develop this new functionality.