libpq version in rpm packages

Brian Mathis wrote:

I just set up a new server and would like to use rpms to manage the
software on this one. I've done the compile from source thing most of
the time, but over time it seems to get messy.

I'm using CentOS 4.2, which only has packages for postgres 7.4, but I
very much want to use 8.1. I installed the 8.1 RPMs, but when I try
to install packages like perl-DBD-Pg, or php-pgsql, rpm complains
because they want libpq.so.3, and postgres 8.1 comes with libpq.so.4.

Does anyone have a workaround for this? I've thought about forcing
the rpms to install, or even installing the postgres libs from 7.4.

I did a rpmbuild --rebuld of those two packages (perl and php) after I
installed 8.1. This worked OK, but I can't run a nightly yum update
because it complains that it can't install the update for the php package.

Is there some type of compat rpm that we can install so that these
packages will find the required version of libpq?

Matt

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

On Wed, 9 Nov 2005, Brian Mathis wrote:

I'm using CentOS 4.2, which only has packages for postgres 7.4, but I very
much want to use 8.1. I installed the 8.1 RPMs, but when I try to install
packages like perl-DBD-Pg, or php-pgsql, rpm complains because they want
libpq.so.3, and postgres 8.1 comes with libpq.so.4.

Does anyone have a workaround for this? I've thought about forcing the rpms
to install, or even installing the postgres libs from 7.4.

We have a compat RPM to solve that issue:

http://developer.postgresql.org/~devrim/rpms/compat/

There are RPMs for x86 and x86_64; as well as a srpm. This RPM includes
libs from older PostgreSQL versions and these libs satisfy the
dependencies.

Regards,
- --
Devrim GUNDUZ
Kivi Bili�im Teknolojileri - http://www.kivi.com.tr
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFDckwF4zE8DGqpiZARAvUvAKCfhBwx1OJ3KrpCBU1ODTnBaFwb3ACgqPdO
OJ9hxlkksO7dHtAy1rd/tDo=
=mcse
-----END PGP SIGNATURE-----

From pgsql-general-owner@postgresql.org Wed Nov 9 15:28:37 2005

X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id D709BDA0FE
for <pgsql-general-postgresql.org@localhost.postgresql.org>; Wed, 9 Nov 2005 15:28:33 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 37878-10
for <pgsql-general-postgresql.org@localhost.postgresql.org>;
Wed, 9 Nov 2005 19:28:26 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25])
by svr1.postgresql.org (Postfix) with ESMTP id 895BBD9E58
for <pgsql-general@postgresql.org>; Wed, 9 Nov 2005 15:28:25 -0400 (AST)
Received: from wimpy.net.nih.gov (mailfwd.nih.gov [128.231.88.106])
by svr2.postgresql.org (Postfix) with ESMTP id 32BF1F0FFA
for <pgsql-general@postgresql.org>; Wed, 9 Nov 2005 16:22:36 +0000 (GMT)
Received: from wimpy.net.nih.gov (localhost [127.0.0.1])
by wimpy.net.nih.gov (8.12.11/8.11.7) with ESMTP id jA9GMWHt023095
for <pgsql-general@postgresql.org>; Wed, 9 Nov 2005 11:22:33 -0500 (EST)
Received: from [128.231.145.14] (holmes.nhgri.nih.gov [128.231.145.14])
by wimpy.net.nih.gov (8.12.11/8.11.7) with ESMTP id jA9GMWvQ023090;
Wed, 9 Nov 2005 11:22:32 -0500 (EST)
User-Agent: Microsoft-Entourage/10.1.4.030702.0
Date: Wed, 09 Nov 2005 11:22:41 -0500
Subject: Re: Question on Insert / Update
From: Sean Davis <sdavis2@mail.nih.gov>
To: Alex <alex@meerkatsoft.com>, <pgsql-general@postgresql.org>
Message-ID: <BF978C81.1288F%sdavis2@mail.nih.gov>
In-Reply-To: <43720B9A.10907@meerkatsoft.com>
Mime-version: 1.0
Content-type: text/plain; charset="US-ASCII"
Content-transfer-encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.013 required=5 tests=[AWL=0.013]
X-Spam-Score: 0.013
X-Spam-Level:
X-Archive-Number: 200511/485
X-Sequence-Number: 86629

On 11/9/05 9:45 AM, "Alex" <alex@meerkatsoft.com> wrote:

Hi,
have just a general question...

I have a table of 10M records, unique key on 5 fields.
I need to update/insert 200k records in one go.

I could do a select to check for existence and then either insert or update.
Or simply insert, check on the error code an update if required.

The 2nd seems to be to logical choice, but will it actually be faster
and moreover is that the right way to do it?

Probably the fastest and most robust way to go about this if you have the
records in the form of a tab-delimited file is to COPY or \copy (in psql)
them into a separate loader table and then use SQL to manipulate the records
(check for duplicates, etc) for final insertion into the table.

Sean

Thanks for that rpm.

I noticed that the x86_64 version of the compat libs installs the files in
/usr/lib, while the x86_64 postgresql-libs rpm uses /usr/lib64. Is there are
reason for that? Otherwise, I'd prefer to keep all the 64bit libs in lib64.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

On Wed, 9 Nov 2005, Brian Mathis wrote:

I noticed that the x86_64 version of the compat libs installs the files in
/usr/lib, while the x86_64 postgresql-libs rpm uses /usr/lib64. Is there are
reason for that? Otherwise, I'd prefer to keep all the 64bit libs in lib64.

Good catch! It seems that I used an errorneus .rpmmacros file in the
x86_64 server :(

The RPM is fixed now and will be uploaded in an hour. Until then, you can
do a

rpmbuild --rebuild

with the srpm and build the correct RPM for yourself.

Regards,
- --
Devrim GUNDUZ
Kivi Bili�im Teknolojileri - http://www.kivi.com.tr
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFDcl5S4zE8DGqpiZARAn/PAJ9zsfpK7Z85fMgGzqB8nedmiYFCswCgkyWU
H9nHr7MruNoResv+S8YBWdU=
=MfHS
-----END PGP SIGNATURE-----

From pgsql-general-owner@postgresql.org Wed Nov 9 17:00:46 2005

X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id A5273D77CC
for <pgsql-general-postgresql.org@localhost.postgresql.org>; Wed, 9 Nov 2005 16:56:28 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 84909-06
for <pgsql-general-postgresql.org@localhost.postgresql.org>;
Wed, 9 Nov 2005 20:56:25 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2])
by svr1.postgresql.org (Postfix) with ESMTP id B31AFDA282
for <pgsql-general@postgresql.org>; Wed, 9 Nov 2005 16:56:25 -0400 (AST)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
id B5A1A31058; Wed, 9 Nov 2005 21:56:26 +0100 (MET)
From: "Andrus" <eetasoft@online.ee>
X-Newsgroups: pgsql.general
Subject: Re: Best way to use indexes for partial match at beginning
Date: Wed, 9 Nov 2005 22:46:27 +0200
Organization: Hub.Org Networking Services
Lines: 34
Message-ID: <dktnpk$2jrl$1@news.hub.org>
References: <dklr9d$781$1@news.hub.org> <20051108194837.GO19551@pervasive.com> <dksjma$2pkc$1@news.hub.org> <20051109110116.GC21585@svana.org> <dktj18$1buk$1@news.hub.org> <437251A3.9000707@archonet.com>
X-Complaints-To: usenet@news.hub.org
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Response
To: pgsql-general@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.526 required=5 tests=[AWL=0.526]
X-Spam-Score: 0.526
X-Spam-Level:
X-Archive-Number: 200511/495
X-Sequence-Number: 86639

CREATE INDEX i1 ON foo(col1);

cannot be used to optimize queries of type "get all rows where first n
charaters of col1 are known" in Postgres.

Of course it will! Any btree based index will let you do that. Re-read the
previous answers and make sure you pay attention to the bit about
text_pattern_ops and LIKE in non-C locales.

Richard,

thank you. I try to formulate my problem more presicely.
I have table

CREATE TABLE foo ( bar CHAR(10) PRIMARY KEY);

Cluster locale is non-C. Database encoding is UTF-8. Postgres vers is 8.1

PRIMARY KEY clause creates btree based index so the index exists on bar.

I want to run fast queries by knowing first characters of bar like :

1. Select records from foo where first character of bar is A
2. Select records from foo where first character of bar is B
3. Select records from foo where first two characters of bar are BC
4. Select records from foo where first three characters of bar are ABC
etc.

Can you write sample WHERE clause which can use existing primary key index
for those queries ?

Andrus.