Version 3.14.0 and later Cannot Compile against Postgres 9.2 or earlier

[theory]

Looks like 751f5ca may have broken DBD::Pg for 9.2 and lower. Was it the intention to drop support prior to 9.3, or was the intent to support pg_lo_tell64 and friends only on 9.3 but still support 9.2 and earlier overall?

Spotted as I was adding tests for older versions of Postgres to Sqitch, where 9.2 failed with this error among others:

dbdimp.c:43:14: error: conflicting types for ‘lo_lseek64’
 unsigned int lo_lseek64(PGconn *conn, int fd, unsigned int offset, int whence);
               ^~~~~~~~~~
 In file included from Pg.h:35,
                 from dbdimp.c:14:
/usr/include/postgresql/libpq-fe.h:620:17: note: previous declaration of ‘lo_lseek64’ was here

[ilmari]

There was no lo_lseek64 in libpq-fe.h in 9.2, so it looks like Makefile.PL finds pg_config from 9.2, but libpq and headers are actually a newer version.

I see the CI job uses Debian and installs postgresql-server-dev-9.2. While the Debian packages ship server and client binaries for every version, libpq is by default only provided from the latest version, since that installs into /usr/{lib,include}/ and is protocol-compatible with older servers and ABI-compatible with older binaries. However, the /usr/bin/pg_config wrapper from postgresql-common prefers the pg_config from postgresql-sever-dev-* over the one from libpq-dev, hence the version confusion.

To get older libpq versions you need to add the version-specific suite to the sources.list line e.g.

deb http://apt.postgresql.org/pub/repos/apt/ sid-pgdg-testing main 9.2

and install the specific version with apt install libq{5,-dev}=9.2.*.

[theory]

I didn't realize apt.postgresql.org.sh would install the Debian-packaged pg_config or libpq. The host does have libpq and pg_config in /usr/include/postgresql/9.2; would it work if the PATH included /usr/include/postgresql/9.2/bin?

[theory]

@df7cb can you comment on this? The pg-start script uses

apt.postgresql.org.sh -i -v 9.2

to instal the version. Is there a way for that script to also install libpq such that it's easily findable? Or do I need to figure out how to add /usr/include/postgresql/9.2/bin to the path?

[ilmari]

There's no need to add /usr/include/postgresql/9.2/bin to $PATH, the /usr/bin/pg_config wrapper will find the most recent one in /usr/lib/postgresql/*/bin and delegate to that. The crucial thing is that libpq{5,-dev} (which is installed in /usr/{lib,include}) must match that version.

[theory]

Okay. A little surprised that apt.postgresql.org.sh doesn't install them; maybe it could? I believe it sets up the apt repo, but maybe not, since I ran apt install libq{5,-dev}=9.2.* and it couldn't find them. Apparently they're in /etc/apt/sources.list.d/pgdg.list tho; it looks like this:

deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg main
#deb-src http://apt.postgresql.org/pub/repos/apt/ buster-pgdg main

Do I need to so something special to get apt-get to use that source?

[ilmari]

As I mentioned in my original comment:

To get older libpq versions you need to add the version-specific suite to the sources.list line e.g.

deb http://apt.postgresql.org/pub/repos/apt/ sid-pgdg-testing main 9.2

and install the specific version with apt install libq{5,-dev}=9.2.*.

Note the 9.2 after main.

[df7cb]

Can't you use PG_VERSION_NUM instead? That will point to the more recent libpq version (at least in /usr/include/postgresql/pg_config.h), even if pg_config is older.

[theory]

Where would I use it?

[theory]

If the expectation is that setting as an environment variable will do the trick, I tried it with pg_config and it made no difference:

# pg_config --includedir
/usr/include/postgresql
# /usr/lib/postgresql/9.2/bin/pg_config --includedir
/usr/include/postgresql
# PG_VERSION_NUM=90200 pg_config --includedir
/usr/include/postgresql
# export PG_VERSION_NUM=90200
# pg_config --includedir
/usr/include/postgresql

I also tried installing DBD::Pg with it set, but no joy.

[theory]

Setting up the new apt source also does not work; it cannot find the specified packages to install:

$ echo deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg-testing main 9.2  >> /etc/apt/sources.list
$ cat /etc/apt/sources.list
# deb http://snapshot.debian.org/archive/debian/20211011T000000Z buster main
deb http://deb.debian.org/debian buster main
# deb http://snapshot.debian.org/archive/debian-security/20211011T000000Z buster/updates main
deb http://security.debian.org/debian-security buster/updates main
# deb http://snapshot.debian.org/archive/debian/20211011T000000Z buster-updates main
deb http://deb.debian.org/debian buster-updates main
deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg-testing main 9.2
$ apt install libq{5,-dev}=9.2.*
Reading package lists... Done
Building dependency tree       
Reading state information... Done
E: Unable to locate package libq5
E: Unable to locate package libq-dev
$ apt-get update
Hit:1 http://security.debian.org/debian-security buster/updates InRelease
Hit:2 http://deb.debian.org/debian buster InRelease
Hit:3 http://deb.debian.org/debian buster-updates InRelease
Get:4 http://apt.postgresql.org/pub/repos/apt buster-pgdg-testing InRelease [110 kB]
Hit:5 http://apt.postgresql.org/pub/repos/apt buster-pgdg InRelease
Get:6 http://apt.postgresql.org/pub/repos/apt buster-pgdg-testing/main amd64 Packages [256 kB]
Get:7 http://apt.postgresql.org/pub/repos/apt buster-pgdg-testing/9.2 amd64 Packages [1802 B]
Fetched 368 kB in 2s (228 kB/s)                 
Reading package lists... Done
$ apt install libq{5,-dev}=9.2.*
Reading package lists... Done
Building dependency tree       
Reading state information... Done
E: Unable to locate package libq5
E: Unable to locate package libq-dev

[theory]

And finally, there is only one copy of libpq-fe.h on the system:

 find / -name libpq-fe.h
/usr/include/postgresql/libpq-fe.h

That's the default latest version that @ilmari mentioned; to his point, there needs to be a way to install the client headers and libraries that go with the version installed by apt.postgresql.org.sh. Ideally there would be an option to apt.postgresql.org.sh that would install them.

FWIW, I'm also getting signature errors when I add buster-pgdg or buster-pgdg-testing:

$ echo deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg-testing main 9.2  >> /etc/apt/sources.list
$ apt-get update
Get:1 http://security.debian.org/debian-security buster/updates InRelease [65.4 kB]
Get:2 http://deb.debian.org/debian buster InRelease [122 kB]                    
Get:3 http://deb.debian.org/debian buster-updates InRelease [51.9 kB]
Get:4 http://apt.postgresql.org/pub/repos/apt buster-pgdg-testing InRelease [110 kB]
Get:5 http://security.debian.org/debian-security buster/updates/main amd64 Packages [309 kB]
Get:6 http://deb.debian.org/debian buster/main amd64 Packages [7906 kB]
Get:7 http://deb.debian.org/debian buster-updates/main amd64 Packages [15.2 kB]
Err:4 http://apt.postgresql.org/pub/repos/apt buster-pgdg-testing InRelease
  The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 7FCC7D46ACCC4CF8
Reading package lists... Done
W: GPG error: http://apt.postgresql.org/pub/repos/apt buster-pgdg-testing InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 7FCC7D46ACCC4CF8
E: The repository 'http://apt.postgresql.org/pub/repos/apt buster-pgdg-testing InRelease' is not signed.
N: Updating from such a repository can't be done securely, and is therefore disabled by default.
N: See apt-secure(8) manpage for repository creation and user configuration details.