Subject: Details about libpq cross-version compatibility

From: Sebastien FLAESCH <[email-masked]>
Date: Wed, 12 Sep 2012 17:21:15 +0200

Hello,

I believe there is some info missing in the page describing libpq programs build:

http://www.postgresql.org/docs/9.2/interactive/libpq-build.html

I am working with PostgreSQL for several years now (since V7), maintaining a set
of "db drivers" for our system, that can be used with the different PostgreSQL
versions.

We need to build our binaries for different versions of PostgreSQL, today it's
from 8.3 to 9.2 ...

What is the compatibility rule regarding this?

Can I compile / link with a PostgreSQL 9.1 environment and deploy on 9.2 ???

Can I compile / link with a PostgreSQL 8.x environment and deploy on 9.x ???

The problem is that we have to ship several versions of the drivers for all
the PostgreSQL versions that are in the support live cycle, today this is
from 8.3 to 9.2 ...

So far, we followed the version policy as described here:

http://www.postgresql.org/support/versioning

I think it's ok to follow this rule, but would feel much better if it was
clearly stated in the "Building libpq Programs" page.

Thanks!
Seb

---

From: Heikki Linnakangas <[email-masked]>
Date: Thu, 13 Sep 2012 10:01:10 +0300

> Can I compile / link with a PostgreSQL 8.x environment and deploy on 9.x

If your binary is just a client application that uses libpq to speak to 
the server, you can use any (supported) version of libpq with any 
(supported) server version. There hasn't been any incompatible protocol 
changes since version 7.4, which introduced the protocol version 3, and 
even that was done in a backwards-compatible fashion so that even 9.2 
server still supports the old version 2 of the protocol.

Now, another question is what happens if you compile your binaries 
against e.g 9.1 version of libpq, but at runtime, you have 9.2 libpq 
installed. I think that should work too, but not vice versa, if you use 
any new functions from the 9.2 libpq library. In general, you should 
have the same or higher version of libpq at runtime that the binaries 
were built with.

The version number bump from 8.x to 9.0 was made because 9.0 introduced 
many new features, but from a compatibility policy point-of-view, 8.4 -> 
9.0 is no different than, say, going from 8.3 to 8.4.

> the PostgreSQL versions that are in the support live cycle, today this is

That really shouldn't be necessary.

> clearly stated in the "Building libpq Programs" page.

Agreed. Either that page, or the libpq top page at 
http://www.postgresql.org/docs/devel/static/libpq.html, or a new 
"cross-version compatibility" section under that. Could you write up a 
few paragraphs for the manual please? That would be much appreciated, 
you'd be in a good position to do that since you're facing the problem 
right now.

- Heikki

---

From: Sebastien FLAESCH <[email-masked]>
Date: Thu, 13 Sep 2012 09:46:15 +0200

Thank you Heikki for your answer!

I can write a paragraph, (hence I did never do that => where can I find
info to contribute on the doc?)

But so far it's not quite clear to me:

You write "That really shouldn't be necessary", when I write that we have
to ship different drivers for each major PostgreSQL version...

Is this what I should document? Based on assumptions?

Further: Today we use a simple naming convention:

   dbmpgs83x => for PostgreSQL 8.3.x
   dbmpgs84x => for PostgreSQL 8.4.x
   dbmpgs90x => for PostgreSQL 9.0.x
   dbmpgs91x => for PostgreSQL 9.1.x
   dbmpgs92x => for PostgreSQL 9.2.x

While we need to ship 5 drivers, this is clear and simple to understand.

If a future major version (for ex 9.3) implements a new strategic feature
that makes libpq headers incompatible with prior versions, what shall we
do with our convention? Shall we ship 9.x (where 0<=x<=2) drivers and 9.3+
drivers? What if a next major version implements again another feature
that makes headers incompatible again?

I think I am asking for is an official statement that clearly says that
libpq headers are only compatible for a given set of minor versions, like
(8.4.1, 8.4.2, 8.4.3, ...) or (9.2.1, 9.2.2, ...)

I believe also that the libpq library should automatically check for
compatiblity, based on PG_MAJORVERSION definition in pg_config.h ...

Seb

---

From: Heikki Linnakangas <[email-masked]>
Date: Thu, 13 Sep 2012 11:19:15 +0300

> info to contribute on the doc?)

Just follow the style and language of the rest of the docs the best that 
you can. If you can submit the changes as a patch against the SGML docs 
in the source repository, that would be great, but I can also easily 
convert it to that format if you just post the suggested text itself in 
an email.

> While we need to ship 5 drivers, this is clear and simple to understand.

What I meant is that it probably isn't necessary to ship 5 drivers. The 
latest version of libpq works against older server versions, so you 
could just ship one version of the dbmpgs driver (the latest), and it 
will work with any supported server and libpq version. If it compiles 
with 8.3 version of libpq, if should work with any newer version at 
runtime. And if you compile it with 9.2 version of libpq, it should 
still work at runtime with an older version of libpq, if you refrain 
from using any new features of libpq.

However, there might be something else in your driver that's dependent 
on the server version, like if you construct SQL queries in the driver 
and you need to use different syntax against different server versions. 
Even that would probably be better to implement as runtime checks rather 
than #ifdefs in the driver, so that the latest driver would work against 
all supported server versions.

> that makes headers incompatible again?

Yes, however, we try hard to avoid doing such incompatible changes to 
libpq. In fact, I don't recall any such changes in the project history. 
Backwards-compatibility of libpq is important.

If you actually use the new features added at a later libpq library, 
then you obviously need a new version of the library, but if you write 
your client program to the lowest common denominator, it will work with 
any version of libpq. For example, the libpq sample programs in that 
come with the server sources in src/test/examples all work with a 
different libpq version. If you take the 9.2 versions of those and run 
them with 8.3 version of libpq, they work, and vice versa.

- Heikki

---

From: Sebastien FLAESCH <[email-masked]>
Date: Thu, 13 Sep 2012 10:59:16 +0200

> convert it to that format if you just post the suggested text itself in

ok...

> from using any new features of libpq.

Ok... Still you write "should" ;-)

I guess cross-version compatibility between the PostgreSQL client and
server versions is not QA tested... is it?

IMHO, if something is stated in the docs, it must be QA tested...

> all supported server versions.

Good point, and in fact, we are not only dependent from the SQL syntax,
we do also rely on C structures for data types. For ex, starting with 8.4,
we use the PG_TYPE_INTERVAL type, while with 8.3 we do not use it.
So we must distinguish 8.3 and 8.4 drivers, and since it's based on C
structures only this cannot be handled at runtime by a unique driver...

Actually our product is a db programming language called Genero, and this
language has an INTERVAL type (comes from Informix 4gl / SQL), and if our
customers want to use such data, they can store values in CHAR(50) with
PGS 8.3, or (the recommended way of course), use native INTERVALs starting
with PGS 8.4...

> them with 8.3 version of libpq, they work, and vice versa.

I see, but as you can imagine with the INTERVAL case, we cannot use the
lowest common denominator...

Further:

Maybe this is a stupid question, but how do you explain that the same
source code of my driver, compiled with different versions of PGS headers,
results in different sizes of the shared object?

   dbmpgs83x.so  102040    <- diff size expected, as we do not use PG_TYPE_INTERVAL
   dbmpgs84x.so  106242    <- starting from here, exactly the same code...
   dbmpgs90x.so  106242
   dbmpgs91x.so  106262
   dbmpgs92x.so  106290    <- why this diff!?

Maybe this has something to do with the linker?

Thanks for your help... really appreciated.
Seb

---

From: Heikki Linnakangas <[email-masked]>
Date: Thu, 13 Sep 2012 14:38:59 +0300

> IMHO, if something is stated in the docs, it must be QA tested...

What's your definition of "QA tested" :-)? There's no official QA 
organization working for the community. There is a suite of automated 
regression tests, but it doesn't do cross-version testing.

I wouldn't worry about it. Historically, there have been very few if any 
bugs that have affected compatibility of a libpq and server of different 
versions. Zero that I can remember, anyway. The protocol has been the 
same for years.

> structures only this cannot be handled at runtime by a unique driver...

Oh, are you using the psqlODBC driver to connect, not plain libpq? The 
only reference to "PG_TYPE_INTERVAL" I could find with google is in 
psqlODBC sources. I cannot comment on the backwards-compatibility of 
psqlODBC, although I would expect it to be similar to plain libpq's. You 
might want to check with the folk at [email-masked] mailing 
list, though, I'm not very familiar with psqlODBC.

PG_TYPE_INTERVAL is just a #define for the OID of the interval type. 
It's numerical value is 1186, which wasn't used for anything before 8.4, 
and it won't be used for anything else in the future, so you could just 
do something like this in your code:

#ifndef PG_TYPE_INTERVAL
#define PG_TYPE_INTERVAL 1186
#endif

If you're going through the ODBC interface, though, you shouldn't need 
the PostgreSQL header files at all. So I'm a bit confused on what 
exactly you're doing.

> PGS 8.3, or (the recommended way of course), use native INTERVALs starting

Well, PostgreSQL 8.3 is going to be EOL'd in Feburary 2013, anyway, so I 
don't think it would be a big deal for you to just drop support for < 
8.4 servers.

> Maybe this has something to do with the linker?

Hmm, dunno. Differences in debug symbols maybe; do you still see a 
difference after "strip dbmpgs*.so"?

- Heikki

---

From: Sebastien FLAESCH <[email-masked]>
Date: Thu, 13 Sep 2012 15:10:23 +0200

> regression tests, but it doesn't do cross-version testing.

By "QA tested", I mean regression tests of course, trying to write and run
as much as possible tests with the different client/server version combined.

> versions. Zero that I can remember, anyway. The protocol has been the

Ok.

> the PostgreSQL header files at all. So I'm a bit confused on what

No no, it's not ODBC, we are using the native libpq API, we always try
to use the closes API provided by the db vendors.

It's a while I did not look deep in our PostgreSQL driver code and I
realize now that we defined PG_TYPE_INTERVAL ourselves in our code.

This is indeed a simple integer and there is not any specific C structure
used either for data: The data is passed and returned as strings.
Sorry for the confusion, this is not usual in other db APIs I am also using
to connect to Informix, Oracle, UDB DB2, SQL Server, etc, where you have
to use structures to hold specific SQL data, as for example in ODBC you
can use structures like TIMESTAMP_STRUCT etc...

So yes, we do:

It knowing this, it's probably possible to have a unique driver code that
adapts dynamically to the PostgreSQL server version...

Note that I always wondered why PostgreSQL does not have these type ids
defined in the header files! Is this not weird? Usually APIs define such
constants for db types...
Or do you officially document the numbers like 1186 as type ids?
Will these numbers never change?

> don't think it would be a big deal for you to just drop support for <

Yep, planed so.

> difference after "strip dbmpgs*.so"?

After a strip *.so, the sizes are the same... Good catch!

Sounds like I could give a try to make a unique driver...

Thanks!
Seb

---

From: Tom Lane <[email-masked]>
Date: Thu, 13 Sep 2012 18:25:07 -0400

> that makes headers incompatible again?

We've never done that AFAIR.  When and if it happens, that would be the
time to deal with it.

> compatiblity, based on PG_MAJORVERSION definition in pg_config.h ...

It seems highly undesirable to me that we should intentionally break
cross-version compatibility, which is what you seem to be proposing.

			regards, tom lane

---

From: Sebastien FLAESCH <[email-masked]>
Date: Fri, 14 Sep 2012 11:43:43 +0200

Hello Tom,

First, we must distinguish (1) cross-version compatibility between PostgreSQL
client and server, from (2) compatibility between the libpq headers used to
compile programs and the client bin library used at runtime...

Regarding (1), I believe this is properly managed, based on the C/S protocol
checking, if I understand correctly. For ex, a 9.2 client can connect to a 8.4
server... right?

Regarding (2), if this is a feature of PostgreSQL libpq, of course it makes no
sense to do release version checking in the lib, as I suggested.

And in fact, this is already enforced by the usage of Linux-style library
version convention (libpq.so.5), if the OS supports this...

$ ldd -r dbmpgs92x.so
	...
	...
	libpq.so.5 => /opt3/dbs/pgs/9.2.0/lib/libpq.so.5 (0xb77cc000)

Here you MUST have libpq.so.5 installed.

(Just don't forget to increment the 5 the day an incompatibility is introduced)

Idea: Maybe a similar internal checking could still be done by libpq, for the
systems that do not use the Linux-style shared lib naming convention?
Should be based on an internal number, (like 5), of course not the release number.
You never know what tricky things people might do to make it work...
   cp libpq.so.5 libpq.so.4
=> run program linked with libpq.so.4

Further, some minimal testing should be done... for ex, run the regression tests
compiled with the 9.0 headers, but using a 9.1 client environment, and for (1),
connecting to a 9.2 server and have another config using a 8.4 server, or a
server using an older protocol...

Then, regarding the doc, both (1) and (2) should be clearly documented.

Best regards,
Seb

On 09/14/2012 12:25 AM, Tom Lane wrote:
> Sebastien FLAESCH<[email-masked]>  writes: