Package does not compile under PostgreSQL 11

[pjledge]

Just tried to compile the package under the new release, and ran into compilation problems.

Within versioning.c: At least one new include will be needed, utils/rangetypes.h. There are some issues with FormData_pg_attribute and Form_pg_attribute usage as well. If I get some time, I'll see if I can figure out the remainder of the issues and submit a pull request.

[astaluego]

I've got the same problem:

versioning.c:249:14: error: assigning to 'Form_pg_attribute' (aka 'struct FormData_pg_attribute *') from incompatible type 'FormData_pg_attribute' (aka 'struct FormData_pg_attribute'); take the address
      with &
        period_attr = tupdesc->attrs[period_attnum - 1];
                    ^ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                      &
versioning.c:502:8: error: assigning to 'Form_pg_attribute' (aka 'struct FormData_pg_attribute *') from incompatible type 'FormData_pg_attribute' (aka 'struct FormData_pg_attribute'); take the address
      with &
                attr = tupdesc->attrs[i];
                     ^ ~~~~~~~~~~~~~~~~~
                       &
versioning.c:514:16: error: assigning to 'Form_pg_attribute' (aka 'struct FormData_pg_attribute *') from incompatible type 'FormData_pg_attribute' (aka 'struct FormData_pg_attribute'); take the address
      with &
                history_attr = history_tupdesc->attrs[history_attnum - 1];
                             ^ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                               &
versioning.c:762:18: warning: implicit declaration of function 'DatumGetRangeType' is invalid in C99 [-Wimplicit-function-declaration]
        system_period = DatumGetRangeType(datum);
                        ^
versioning.c:762:16: warning: incompatible integer to pointer conversion assigning to 'RangeType *' from 'int' [-Wint-conversion]
        system_period = DatumGetRangeType(datum);
                      ^ ~~~~~~~~~~~~~~~~~~~~~~~~
versioning.c:880:24: warning: implicit declaration of function 'RangeTypeGetDatum' is invalid in C99 [-Wimplicit-function-declaration]
        Datum            values[1] = { RangeTypeGetDatum(range) };

[mlt]

This is a duplicate of #38

[majestique]

I'm not seeing any resolution to this issue (and I went through all the referenced cases which lead me to homebrew).

I'm on CentOS7 and I can't upgrade to pg11 because this extension doesn't support it yet.

It looks like the owner hasn't been active for at least a year: https://github.com/arkhipov

[mlt]

It is an OSS after all... Just apply the patch manually or use my repo directly for now.

[ThorkilG12]

Anyone having a windons 64bit version for PG11 ?

[mlt]

@ThorkilG12 Follow CI check link on PR page that goes to AppVeyor, find your configuration there, then go to Artifacts tab. https://ci.appveyor.com/api/buildjobs/ed0efwlw9uj82ly5/artifacts/temporal_tables-07fcf01d-pg11-x64.zip

[ThorkilG12]

Hi

Thanks for taking your time to help me. This line: Follow CI check link on PR page that goes to AppVeyor, find your configuration there, then go to Artifacts tab is totally Greek for me. I have worked with IT for +35 years, but i have never gotten into this Linux Lingo that hardcore developers use :-)

I opened the zip file from the link in your mail, but it contains exactly the same files that I have from version 1.2

And I get the same message: CREATE EXTENSION temporal_tables; ERROR: incompatible library "C:/Program Files/PostgreSQL/11/lib/temporal_tables.dll": version mismatch DETAIL: Server is version 11, library is version 10.

Venlig hilsen

Thorkil Johansen Tel: +45 4070 6547

Den tor. 21. feb. 2019 kl. 17.43 skrev Mikhail Titov < notifications@github.com>:

@ThorkilG12 https://github.com/ThorkilG12 Follow CI check link on PR page that goes to AppVeyor, find your configuration there, then go to Artifacts tab.

https://ci.appveyor.com/api/buildjobs/ed0efwlw9uj82ly5/artifacts/temporal_tables-07fcf01d-pg11-x64.zip

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/arkhipov/temporal_tables/issues/43#issuecomment-466072390, or mute the thread https://github.com/notifications/unsubscribe-auth/ALjw1fxyA5uwG7b_mJIiBHRlByxa342uks5vPsyagaJpZM4YfxY0 .

[mlt]

That was more of a GitHub lingo... as I was lazy typing in Continuous Integration tests for my Pull Request from the phone:-)

That is quite odd as I do use the very same zip with 11. Stop the server, delete/move DLL you have and unpack that zip again.

select version(), extversion from pg_catalog.pg_extension where extname='temporal_tables'

version	extversion
PostgreSQL 11.2, compiled by Visual C++ build 1914, 64-bit	1.2.0

[ThorkilG12]

Hi I downloaded the zip once more. Cleaned everything out. Copied once more. Restarted the server task. And voila... Everything is ok.

In some unknown way I have fumbled around with new and old zip...

Sorry for having waisted you time.

And thank you so much for helping. Have a nice summer :-)

Regards from Copenhagen

Venlig hilsen

Thorkil Johansen Tel: +45 4070 6547

Den tor. 21. feb. 2019 kl. 20.19 skrev Mikhail Titov < notifications@github.com>:

That was more of a GitHub lingo... as I was lazy typing in Continuous Integration tests for my Pull Request from the phone:-)

That is quite odd as I do use the very same zip with 11. Stop the server, delete/move DLL you have and unpack that zip again.

select version(), extversion from pg_catalog.pg_extension where extname='temporal_tables'

version extversion PostgreSQL 11.2, compiled by Visual C++ build 1914, 64-bit 1.2.0

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/arkhipov/temporal_tables/issues/43#issuecomment-466130385, or mute the thread https://github.com/notifications/unsubscribe-auth/ALjw1cCuYWZldnGgiTjgyavHSpKaOfSFks5vPvFbgaJpZM4YfxY0 .

[lamuertepeluda]

I'm trying to add this extension to postgis on docker

FROM mdillon/postgis:10

RUN apt-get update && apt-get install -y python-pip python-psycopg2 libpq-dev postgresql-server-dev-10 postgresql-common
RUN pip install pgxnclient
RUN pgxn install temporal_tables

Using 10 or 9.6 works, with postgresql-server-dev-10 and postgresql-server-dev-9.6 respectively.
Using postgresql-server-dev-all does not. Nor does 11 😞 .

Any hint on this? I'd really love to use the latest postgres version/image.

[mlt]

Don't use pgxn. This project has not been updated for a while. Either use patched up repo or consider using periods for new projects.

[lamuertepeluda]

@mlt thanks for the advices. I tried compiling your patched repo with Docker and mdillon/postgis:11 and it soon became a compilation nightmare 😅
I've spent several hours figuring out how to compile it in Docker, and I was on the edge of giving the f**k up, when I finally made it work!

I understand that pgxn maybe rusty/outdated, and I'm not thrilled at all to have to bring in python and pip stuff in my container for installing a single extension, but I'd really love to have some straightforward alternative procedure like the one for installing the extension for PG9.4 to 10 in my previous post. Maybe having a debian/ubuntu package or some other linux binary instead of makefile?

Periods looks really interesting, but I fear it is even less documented than this one, and therefore it is a little harder for me to figure out well how it works. I really hope the integration of SQL:2016 standard for validity passes soon into next PG versions, but I guess it will take some time. I'll watch how periods and this extension evolve, and use your patch in the meanwhile.

Here's my Dockerfile

FROM mdillon/postgis:11
LABEL maintainer="Vito Macchia <vito.macchia@linksfoundation.com>"

ARG PGUSER=postgres 
ENV PG_USER_env=$PGUSER

# Clone patch compatible with PG11+
RUN echo "user =${PG_USER_env}"
RUN apt-get update && apt-get install -y build-essential libpq-dev postgresql-server-dev-11 postgresql-common git ca-certificates

RUN mkdir -p /usr/share/
RUN git clone https://github.com/mlt/temporal_tables.git /usr/share/temporal_tables

# Compile TTE extensiom
WORKDIR /usr/share/temporal_tables
RUN make -f GNUmakefile
RUN make install
# installcheck must be done lately, when server is up, with postgres user
RUN chown -R postgres /usr/share/temporal_tables
WORKDIR /

# Install initialization file
RUN mkdir -p /docker-entrypoint-initdb.d
COPY ./create_extension_temporal_table.sh /docker-entrypoint-initdb.d/postgis.sh

then here's my create_extension_temporal_table.sh

#!/bin/sh

# modified from https://github.com/appropriate/docker-postgis/blob/master/11-2.5/initdb-postgis.sh

set -e

# Perform all actions as $POSTGRES_USER
export PGUSER="$POSTGRES_USER"

WORKDIR=$(pwd)

# This step must be executed once the postgresql server is up & running,
# using the PGUSER
cd /usr/share/temporal_tables
make installcheck PGUSER=${PGUSER}

cd ${WORKDIR}

# Create the 'template_postgis' template db
"${psql[@]}" <<-'EOSQL'
    CREATE DATABASE template_postgis;
    UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template_postgis';
EOSQL

# Load PostGIS into both template_database and $POSTGRES_DB
# See also See https://github.com/arkhipov/temporal_tables#installation
for DB in template_postgis "$POSTGRES_DB"; do
    echo "Loading Postgis and Temporal Table extensions into $DB"
    "${psql[@]}" --dbname="$DB" <<-'EOSQL'
        CREATE EXTENSION IF NOT EXISTS postgis;
        CREATE EXTENSION IF NOT EXISTS postgis_topology;
        CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
        CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;
        CREATE EXTENSION IF NOT EXISTS temporal_tables;
    EOSQL
done

If you use docker-compose, place the Dockerfile and the shell script into images/temp_postgis, then write this in your docker-compose.yml file:

version: "3.7"
services:
  my-pgsql:
    container_name: 'my-pgsql-${ENVTYPE:-experimental}'
    # image: mdillon/postgis:latest
    build: ./images/temp_postgis
    volumes:
      - ./db/init_database.sql:/docker-entrypoint-initdb.d/init.sql # init DB
      - mydbdata:/var/lib/postgresql/data
    networks:
      - my-net
    environment:
      POSTGRES_PASSWORD: ${PGPASSWORD:-secret}
      POSTGRES_DB: ${PGDATABASE:-tte_test}
      PGDATABASE: ${PGDATABASE:-tte_test}
      PGUSER: ${PGUSER:-postgres}
    ports:
      - '${PGPORT:-35432}:5432'
    restart: unless-stopped

volumes:
  mydbdata:

networks:
  my-net:
    name: my-net-${ENVTYPE:-experimental}

and this in db/init_database.sql

-- add temporal_tables extension
CREATE EXTENSION IF NOT EXISTS temporal_tables CASCADE;

-- add postgis extension
CREATE EXTENSION IF NOT EXISTS postgis CASCADE;

SELECT
    postgis_full_version ();

-- SQL Interpolation
-- https://www.postgresql.org/docs/9.5/app-psql.html#APP-PSQL-INTERPOLATION
-- TODO find a way to use env variables when interpolating
\set dbname 'tte_test'

-- Create a Read-only user (https://tableplus.io/blog/2018/04/postgresql-how-to-create-read-only-user.html)
-- a read only user is useful for visualization tools
CREATE ROLE readonly_user WITH LOGIN ENCRYPTED PASSWORD 'readonly_user' NOSUPERUSER;

GRANT CONNECT ON DATABASE :dbname TO readonly_user;

GRANT USAGE ON SCHEMA public TO readonly_user;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT
SELECT
    ON TABLES TO readonly_user;

then type docker-compose up --build in your shell

you will find your db ready at localhost:35432 with username postgres and password secret

then you can start playing with your (postgis-enabled) temporal tables 😄

[mlt]

@lamuertepeluda FYI, periods IS the WIP for SQL 2016 implementation into PG. There is a Debian package available apt-cache show postgresql-11-periods. The main dev can be found on PG IRC channel and is extremely open to comments and feedback.

[lamuertepeluda]

Thanks @mlt, I will surely give a try also to periods as soon as possible. I hope the SQL:2016 grammar for periods and validity (the standard one reported in the examples) will also be adopted by PG after merging periods extensions

[arkhipov]

Just created GitHub Actions with all PostgreSQL versions up to 15.