Обсуждение: RPM question: Where to put psycopg2 files

Hi,

As of PostgreSQL 9.0+, PostgreSQL RPMs can be installed in parallel,
e.g., you can use 9.0 along with 9.1. I updated most of the packages to
use that layout, but I'm stuck with psycopg2.

psycopg2 is currently installed under this directory:

`python  -c "from distutils.sysconfig import get_python_lib; print
get_python_lib(1)"`/psycopg2

For example,

/usr/lib64/python2.7/site-packages/psycopg2/

If I want to install two different psycopg2 versions which are compiled
against 9.0 and 9.1, they will overwrite each other.

What is the best way to use separate directories for that? Is there a
setup.py parameter for that?

Regards,
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz

On 17/01/2011 07:59, Devrim GÜNDÜZ wrote:
> As of PostgreSQL 9.0+, PostgreSQL RPMs can be installed in parallel,
> e.g., you can use 9.0 along with 9.1. I updated most of the packages to
> use that layout, but I'm stuck with psycopg2.
>
> psycopg2 is currently installed under this directory:
>
> `python  -c "from distutils.sysconfig import get_python_lib; print
> get_python_lib(1)"`/psycopg2
>
> For example,
>
> /usr/lib64/python2.7/site-packages/psycopg2/
>
> If I want to install two different psycopg2 versions which are compiled
> against 9.0 and 9.1, they will overwrite each other.
>
> What is the best way to use separate directories for that? Is there a
> setup.py parameter for that?

You don't need that, because psycopg uses libpq that can connect to
older versions of the backend.

I don't know about RPMs but in Debian we have multiple versions of
postgresql and only *one* libpqXX package (usually it comes from the
most recent stable release). So my suggestion is to have only one
version of psycopg linked to the most recent libpq package.

--
Federico Di Gregorio                         federico.digregorio@dndg.it
Studio Associato Di Nunzio e Di Gregorio                  http://dndg.it
   Abandon the search for Truth; settle for a good fantasy. -- Anonymous

Hi,

As it is no psycopg2 owned problem, instead it is python related there are
python techniques to solve this.

If you want to install two different versions of an Python egg virtualenv
(http://pypi.python.org/pypi/virtualenv) is your best friend. It allows
you to separate your working python from your system python. For each
virtualenv environment a special/separate egg version is supported.

Alexander

>
> Hi,
>
> As of PostgreSQL 9.0+, PostgreSQL RPMs can be installed in parallel,
> e.g., you can use 9.0 along with 9.1. I updated most of the packages to
> use that layout, but I'm stuck with psycopg2.
>
> psycopg2 is currently installed under this directory:
>
> `python  -c "from distutils.sysconfig import get_python_lib; print
> get_python_lib(1)"`/psycopg2
>
> For example,
>
> /usr/lib64/python2.7/site-packages/psycopg2/
>
> If I want to install two different psycopg2 versions which are compiled
> against 9.0 and 9.1, they will overwrite each other.
>
> What is the best way to use separate directories for that? Is there a
> setup.py parameter for that?
>
> Regards,
> --
> Devrim GÜNDÜZ
> PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
> PostgreSQL RPM Repository: http://yum.pgrpms.org
> Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
> http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz
>
>

On Mon, Jan 17, 2011 at 09:05:40AM +0100, Federico Di Gregorio wrote:

> On 17/01/2011 07:59, Devrim GÜNDÜZ wrote:
> > As of PostgreSQL 9.0+, PostgreSQL RPMs can be installed in parallel,
> > e.g., you can use 9.0 along with 9.1. I updated most of the packages to
> > use that layout, but I'm stuck with psycopg2.
> >
> > psycopg2 is currently installed under this directory:
> >
> > `python  -c "from distutils.sysconfig import get_python_lib; print
> > get_python_lib(1)"`/psycopg2
> >
> > For example,
> >
> > /usr/lib64/python2.7/site-packages/psycopg2/
> >
> > If I want to install two different psycopg2 versions which are compiled
> > against 9.0 and 9.1, they will overwrite each other.
> >
> > What is the best way to use separate directories for that? Is there a
> > setup.py parameter for that?
>
> You don't need that, because psycopg uses libpq that can connect to
> older versions of the backend.
>
> I don't know about RPMs but in Debian we have multiple versions of
> postgresql and only *one* libpqXX package (usually it comes from the
> most recent stable release). So my suggestion is to have only one
> version of psycopg linked to the most recent libpq package.

This is not really the final solution.

I've got a Debian/Squeeze machine which runs PG 9.0 from /Unstable.

I've got a second Debian/Squeeze machine running PG 8.4 from /Squeeze.

Both use the same psycopg2 from /Squeeze.

Transferring bytea data from the 9.0 machine to the 8.4 one
does not work (other datatypes work fine).

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On 17/01/2011 14:02, Karsten Hilbert wrote:
>> I don't know about RPMs but in Debian we have multiple versions of
>> > postgresql and only *one* libpqXX package (usually it comes from the
>> > most recent stable release). So my suggestion is to have only one
>> > version of psycopg linked to the most recent libpq package.
> This is not really the final solution.
>
> I've got a Debian/Squeeze machine which runs PG 9.0 from /Unstable.
>
> I've got a second Debian/Squeeze machine running PG 8.4 from /Squeeze.
>
> Both use the same psycopg2 from /Squeeze.
>
> Transferring bytea data from the 9.0 machine to the 8.4 one
> does not work (other datatypes work fine).

Can you please provide a test case? Unless going from 9.0 -> 8.4 is
documented as not possible by PostgreSQL docs psycopg should be able to
convert the data correctly.

federico

--
Federico Di Gregorio                         federico.digregorio@dndg.it
Studio Associato Di Nunzio e Di Gregorio                  http://dndg.it
 And anyone who yells "fork" deserves to get one stuck in them.
                                                          -- Dan Winship

On Mon, Jan 17, 2011 at 02:20:37PM +0100, Federico Di Gregorio wrote:

> On 17/01/2011 14:02, Karsten Hilbert wrote:
> >> I don't know about RPMs but in Debian we have multiple versions of
> >> > postgresql and only *one* libpqXX package (usually it comes from the
> >> > most recent stable release). So my suggestion is to have only one
> >> > version of psycopg linked to the most recent libpq package.
> > This is not really the final solution.
> >
> > I've got a Debian/Squeeze machine which runs PG 9.0 from /Unstable.
> >
> > I've got a second Debian/Squeeze machine running PG 8.4 from /Squeeze.
> >
> > Both use the same psycopg2 from /Squeeze.
> >
> > Transferring bytea data from the 9.0 machine to the 8.4 one
> > does not work (other datatypes work fine).
>
> Can you please provide a test case? Unless going from 9.0 -> 8.4 is
> documented as not possible by PostgreSQL docs psycopg should be able to
> convert the data correctly.

I will try.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Mon, Jan 17, 2011 at 1:02 PM, Karsten Hilbert
<Karsten.Hilbert@gmx.net> wrote:
> On Mon, Jan 17, 2011 at 09:05:40AM +0100, Federico Di Gregorio wrote:
>
>> On 17/01/2011 07:59, Devrim GÜNDÜZ wrote:
>> > As of PostgreSQL 9.0+, PostgreSQL RPMs can be installed in parallel,
>> > e.g., you can use 9.0 along with 9.1. I updated most of the packages to
>> > use that layout, but I'm stuck with psycopg2.
>> >
>> > psycopg2 is currently installed under this directory:
>> >
>> > `python  -c "from distutils.sysconfig import get_python_lib; print
>> > get_python_lib(1)"`/psycopg2
>> >
>> > For example,
>> >
>> > /usr/lib64/python2.7/site-packages/psycopg2/
>> >
>> > If I want to install two different psycopg2 versions which are compiled
>> > against 9.0 and 9.1, they will overwrite each other.
>> >
>> > What is the best way to use separate directories for that? Is there a
>> > setup.py parameter for that?
>>
>> You don't need that, because psycopg uses libpq that can connect to
>> older versions of the backend.
>>
>> I don't know about RPMs but in Debian we have multiple versions of
>> postgresql and only *one* libpqXX package (usually it comes from the
>> most recent stable release). So my suggestion is to have only one
>> version of psycopg linked to the most recent libpq package.
>
> This is not really the final solution.
>
> I've got a Debian/Squeeze machine which runs PG 9.0 from /Unstable.
>
> I've got a second Debian/Squeeze machine running PG 8.4 from /Squeeze.
>
> Both use the same psycopg2 from /Squeeze.
>
> Transferring bytea data from the 9.0 machine to the 8.4 one
> does not work (other datatypes work fine).

Using a psycopg with libpq < 9 to talk with postgres >= 9 yes, there
is this problem. The other way around it works fine: psycopg+libpq 9
passes all the tests even with a 7.4 server.

The problem is that postgres 9 uses by default the hex format instead
of the escape format to represent bytea
(http://www.postgresql.org/docs/9.0/static/datatype-binary.html). A
workaround I was thinking about could be to send "SET bytea_output TO
'escape';" on connection in case of pre9-libpq/post9-server
combination is detected. I hate it because I was trying to drop the
"queries on connection" psycopg does (there used to be 3, now there is
only one and I think the last one can be dropped as well). Of course
bytea_output can be set in the postgresql.conf too and it would make
previous releases of psycopg work fine.

Bottom line is that currently only libpq 9.0 works fine to talk with a
9.0 server, unless bytea_output is set to 'escape' on the server.

-- Daniele

On 17/01/2011 14:35, Daniele Varrazzo wrote:
> The problem is that postgres 9 uses by default the hex format instead
> of the escape format to represent bytea
> (http://www.postgresql.org/docs/9.0/static/datatype-binary.html). A
> workaround I was thinking about could be to send "SET bytea_output TO
> 'escape';" on connection in case of pre9-libpq/post9-server
> combination is detected. I hate it because I was trying to drop the
> "queries on connection" psycopg does (there used to be 3, now there is
> only one and I think the last one can be dropped as well).

Please lets not do that. psycopg can already be used with a lesser libpq
to connect to a newer backend _unless_ changes like the one in this
discussion. Solving this particular problem may seem a good thing but,
IMHO, it is just putting a workaround in the wrong place.

> Of course
> bytea_output can be set in the postgresql.conf too and it would make
> previous releases of psycopg work fine.

That's the correct workaround.

federico

--
Federico Di Gregorio                         federico.digregorio@dndg.it
Studio Associato Di Nunzio e Di Gregorio                  http://dndg.it
                   I came like Water, and like Wind I go. -- Omar Khayam

On Mon, Jan 17, 2011 at 2:00 PM, Harald Armin Massa
<harald@2ndquadrant.com> wrote:
>> Bottom line is that currently only libpq 9.0 works fine to talk with a
>> 9.0 server, unless bytea_output is set to 'escape' on the server.
>>
> to add to your very correct explanation: until 9.1 it is not possible to
> identify the libpq version from a client application. Magnus committed a
> patch to provide a "query-the-libpq-version" for 9.1; which will provide
> do-less-connections-to-identify-capabilities opportunity for psycopg2.

Yes, what we currently do is to identify the libpq version at compile
time. Of course it may be a lie and at runtime we may end up with the
wrong version. But what happens now is just that we don't include the
support for certain features if not available in the libpq at compile
time (e.g. lo_truncate, to name one). If things change at runtime, you
either don't get that feature (if libpq.so is newer) or you get a nice
ImportError (if libpq.so is older). Honestly I don't know if it would
worth the hassle of adding conditional runtime import: probably not
for the current set of conditional features, they are just a few.

Connection-time queries are more related to discovering GUC settings
than libpq capabilities: the only surviving one is to get the server
default_transaction_isolation.

-- Daniele

On Mon, Jan 17, 2011 at 1:53 PM, Federico Di Gregorio
<federico.digregorio@dndg.it> wrote:
> On 17/01/2011 14:35, Daniele Varrazzo wrote:
>> The problem is that postgres 9 uses by default the hex format instead
>> of the escape format to represent bytea
>> (http://www.postgresql.org/docs/9.0/static/datatype-binary.html). A
>> workaround I was thinking about could be to send "SET bytea_output TO
>> 'escape';" on connection in case of pre9-libpq/post9-server
>> combination is detected. I hate it because I was trying to drop the
>> "queries on connection" psycopg does (there used to be 3, now there is
>> only one and I think the last one can be dropped as well).
>
> Please lets not do that. psycopg can already be used with a lesser libpq
> to connect to a newer backend _unless_ changes like the one in this
> discussion. Solving this particular problem may seem a good thing but,
> IMHO, it is just putting a workaround in the wrong place.
>
>> Of course
>> bytea_output can be set in the postgresql.conf too and it would make
>> previous releases of psycopg work fine.
>
> That's the correct workaround.

A different workaround would be to have our own bytea hex format
parser if the libpq doesn't support it. It looks easy enough and
wouldn't be a security risk as it's only to receive data from the
backend: 9.0 servers understands escape format ok with both settings.

My fear is that not everybody would be able to set bytea_output to
escape in the server they have to talk to, and it looks 8.4 on the
clients is there to stay for a long while (referring to ubuntu, the
distro I'm more up-to-date, both the last LTS and the last 10.10 have
only 8.4 in the official repos, and things don't look different in the
upcoming natty - see
<http://packages.ubuntu.com/natty/postgresql-client>). If we decide
not to do anything we should at least document the toxic interaction
in a FAQ.

-- Daniele

On 17/01/2011 16:08, Harald Armin Massa wrote:
>
>     Honestly I don't know if it would
>
>     worth the hassle of adding conditional runtime import: probably not
>     for the current set of conditional features, they are just a few.
>
> I also doubt it would be worth the hassle.
>
> I think it would be very nice to expose the libpq-version (and quality
> of this information as in "asked at compile time" or "asked right now,
> because it is 9.1 or newer and we can do it") to the Python application:
> so the application programmer would be able to issue workarounds like
> the "bytea-encoding to escape".

Exposing the compile version (and in the future the runtime one) would
be extremely useful for client code, IMHO.

federico

--
Federico Di Gregorio                         federico.digregorio@dndg.it
Studio Associato Di Nunzio e Di Gregorio                  http://dndg.it
                      The number of the beast: vi vi vi. -- Delexa Jones

On Mon, Jan 17, 2011 at 3:19 PM, Federico Di Gregorio
<federico.digregorio@dndg.it> wrote:

> Exposing the compile version (and in the future the runtime one) would
> be extremely useful for client code, IMHO.

Added ticket #35.

-- Daniele

On Mon, Jan 17, 2011 at 01:35:38PM +0000, Daniele Varrazzo wrote:

> >> You don't need that, because psycopg uses libpq that can connect to
> >> older versions of the backend.
> >>
> >> I don't know about RPMs but in Debian we have multiple versions of
> >> postgresql and only *one* libpqXX package (usually it comes from the
> >> most recent stable release). So my suggestion is to have only one
> >> version of psycopg linked to the most recent libpq package.
> >
> > This is not really the final solution.
> >
> > I've got a Debian/Squeeze machine which runs PG 9.0 from /Unstable.
> >
> > I've got a second Debian/Squeeze machine running PG 8.4 from /Squeeze.
> >
> > Both use the same psycopg2 from /Squeeze.
> >
> > Transferring bytea data from the 9.0 machine to the 8.4 one
> > does not work (other datatypes work fine).
>
> Using a psycopg with libpq < 9 to talk with postgres >= 9 yes, there
> is this problem. The other way around it works fine: psycopg+libpq 9
> passes all the tests even with a 7.4 server.
>
> The problem is that postgres 9 uses by default the hex format instead
> of the escape format to represent bytea
> (http://www.postgresql.org/docs/9.0/static/datatype-binary.html).

Aha. I missed that.

> A workaround I was thinking about could be to send "SET bytea_output TO
> 'escape';" on connection

I fixed my client to issue that query on new connections.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Mon, Jan 17, 2011 at 02:53:16PM +0100, Federico Di Gregorio wrote:

> On 17/01/2011 14:35, Daniele Varrazzo wrote:
> > The problem is that postgres 9 uses by default the hex format instead
> > of the escape format to represent bytea
> > (http://www.postgresql.org/docs/9.0/static/datatype-binary.html). A
> > workaround I was thinking about could be to send "SET bytea_output TO
> > 'escape';" on connection in case of pre9-libpq/post9-server
> > combination is detected. I hate it because I was trying to drop the
> > "queries on connection" psycopg does (there used to be 3, now there is
> > only one and I think the last one can be dropped as well).
>
> Please lets not do that. psycopg can already be used with a lesser libpq
> to connect to a newer backend _unless_ changes like the one in this
> discussion. Solving this particular problem may seem a good thing but,
> IMHO, it is just putting a workaround in the wrong place.
>
> > Of course
> > bytea_output can be set in the postgresql.conf too and it would make
> > previous releases of psycopg work fine.
>
> That's the correct workaround.

However, the workaround should be as local as possible such
that other, global things stay unchanged. Hence IMO the
correct place for adding the workaround is not in the

    - server (postgresql.conf)
    - database (alter database set ...)
    - role (alter role set ...)
    - adaptor (psycopg2)

configuration but rather in the client code that needs it
(such as GNUmed). Other clients will be just fine with the
newer, more efficient setting with the same server,
database, and role.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Mon, Jan 17, 2011 at 03:01:51PM +0000, Daniele Varrazzo wrote:

> A different workaround would be to have our own bytea hex format
> parser if the libpq doesn't support it. It looks easy enough and
> wouldn't be a security risk as it's only to receive data from the
> backend: 9.0 servers understands escape format ok with both settings.

I think that's a great idea for the reasons you list.

> <http://packages.ubuntu.com/natty/postgresql-client>). If we decide
> not to do anything we should at least document the toxic interaction
> in a FAQ.

IMO it should be documented regardless of what we decide to
do or not do.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346