Schema search for default operator classes (was: [ADMIN] Cross schema Primary Key Indexes problem with datatype in the public schema)

Hi,

I know I could dig this out of the manuals so don't be too hard on me,
I'd like to know how to query the actual text of the expression of a
constraint. I know the name of the constraint and of the table.

Thanks,
L Rotger

lrotger <lrotger(at)aircomp(dot)aero> writes:
> I know I could dig this out of the manuals so don't be too hard on me,
> I'd like to know how to query the actual text of the expression of a
> constraint. I know the name of the constraint and of the table.

Something like this:

regression=# create table t1 (f1 int constraint c1 check (f1 > 0));
CREATE TABLE
regression=# select pg_get_constraintdef(c.oid)
regression-# from pg_constraint c join pg_class t on c.conrelid = t.oid
regression-# where t.relname = 't1' and c.conname = 'c1';
pg_get_constraintdef
----------------------
CHECK ((f1 > 0))
(1 row)

You can probably also get it from the information_schema.

regards, tom lane

Tom Lane wrote:
> lrotger <lrotger(at)aircomp(dot)aero> writes:
>
>>I know I could dig this out of the manuals so don't be too hard on me,
>>I'd like to know how to query the actual text of the expression of a
>>constraint. I know the name of the constraint and of the table.
>
>
> Something like this:
>
> regression=# create table t1 (f1 int constraint c1 check (f1 > 0));
> CREATE TABLE
> regression=# select pg_get_constraintdef(c.oid)
> regression-# from pg_constraint c join pg_class t on c.conrelid = t.oid
> regression-# where t.relname = 't1' and c.conname = 'c1';
> pg_get_constraintdef
> ----------------------
> CHECK ((f1 > 0))
> (1 row)
>
> You can probably also get it from the information_schema.
>
> regards, tom lane
>

I forgot to mention that my version is 7.2.1. I replaced the column
names for the ones I see in my installation hoping it would work but the
function, pg_get_conbstraintdef() doesn't exist.

I see that information_schema was introduced in 8.0 too.

Anyway I found out that \d table shows the table definition so it's solved.

Thanks a lot
L Rotger

lrotger <lrotger(at)aircomp(dot)aero> writes:
> I forgot to mention that my version is 7.2.1.

Egad, don't tell me you're still using 7.2.1 for anything important :-(
The list of bugs fixed since then would curl your toes.
http://developer.postgresql.org/docs/postgres/release.html

regards, tom lane

Tom Lane wrote:
> lrotger <lrotger(at)aircomp(dot)aero> writes:
>
>>I forgot to mention that my version is 7.2.1.
>
>
> Egad, don't tell me you're still using 7.2.1 for anything important :-(
> The list of bugs fixed since then would curl your toes.
> http://developer.postgresql.org/docs/postgres/release.html
>
> regards, tom lane
>

Well it probably would, changelogs are scary and I really appreciate the
work of the postgres hackers, but you know the old adage: "if it's not
broken, don't fix it"; it works wonderfully for me and it's accessed in
a controlled environment by a single application done by me. Should I be
worried?

Anyway I'll have a look at the successive changelogs there and possibly
I'll find enough reasons to schedule an upgrade.

Thanks a lot
L Rotger

lrotger <lrotger(at)aircomp(dot)aero> writes:
> Tom Lane wrote:
>> Egad, don't tell me you're still using 7.2.1 for anything important :-(

> Well it probably would, changelogs are scary and I really appreciate the
> work of the postgres hackers, but you know the old adage: "if it's not
> broken, don't fix it"; it works wonderfully for me and it's accessed in
> a controlled environment by a single application done by me. Should I be
> worried?

Yes, you should. Someday one of those bugs will eat your data. The
fact that it hasn't happened yet does not mean you're not at risk;
a lot of them are simply race conditions with very small windows.

regards, tom lane

Let me preface this by disclosing that I'm a novice when it comes to 64bit
computing, so please be kind. :)

I've wrapped a 32bit closed-source .so library in a C program which exposes
it's functionality to PostgreSQL. Compiling the wrapper with gcc64 fails, but
compiling the wrapper with gcc32 seems to work fine. PostgreSQL, which was
built using the 64bit compiler, won't allow me to reference the library when I
create the function:

data=# CREATE OR REPLACE FUNCTION getvalue(text,text)
data-# returns text
data-# as '$libdir/wrapper'
data-# language 'C';
ERROR: could not load library "/usr/local/pgsql-8.1.2/lib/wrapper.so":
/usr/local/pgsql-8.1.2/lib/wrapper.so: cannot open shared object file: No such
file or directory
data=#

Of course, wrapper.so is in the correct spot.

Being the novice that I am, I thought that 32bit and 64bit binary code could be
used interchangeably. Is there some incompatibility of which I am unaware? Is
there perhaps some type of compatibility flag that I should be using, or a
wrapper for my wrapper? It is important that the overall operation of
PostgreSQL is as fast and as powerful as possible. While the functionality in
the library that I want to use from PostgreSQL is important, it is not
mission-critical.

Please advise! Thanks!

CG

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

CG <cgg007(at)yahoo(dot)com> writes:
> Being the novice that I am, I thought that 32bit and 64bit binary code
> could be used interchangeably. Is there some incompatibility of which
> I am unaware?

AFAIK you can't mix the two in the same executable image. If you are
stuck using a 32-bit-only library, you'll have to build all of the
Postgres server as 32-bit. Note that this is probably not going to
cost all that much performance-wise, so I wouldn't recommend expending
any great amount of sweat to avoid it.

regards, tom lane

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> CG <cgg007(at)yahoo(dot)com> writes:
> > Being the novice that I am, I thought that 32bit and 64bit binary code
> > could be used interchangeably. Is there some incompatibility of which
> > I am unaware?
>
> AFAIK you can't mix the two in the same executable image. If you are
> stuck using a 32-bit-only library, you'll have to build all of the
> Postgres server as 32-bit. Note that this is probably not going to
> cost all that much performance-wise, so I wouldn't recommend expending
> any great amount of sweat to avoid it.

Hm. It looks like building 32-bit Postgres is going to be even uglier what with
all the libraries that will have to be compiled into lib32. It thought it would
be easy enough to CC=gcc32 ./configure --with-all-my-options ... Should I have
started out with a 32-bit Linux distro?

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

CG <cgg007(at)yahoo(dot)com> writes:
> Hm. It looks like building 32-bit Postgres is going to be even uglier
> what with all the libraries that will have to be compiled into
> lib32. It thought it would be easy enough to CC=gcc32 ./configure
> --with-all-my-options ... Should I have started out with a 32-bit
> Linux distro?

Not sure what other distros are doing, but at least for Red Hat you
should be able to get 32-bit versions of all the libraries in the
standard distro, and even install them in parallel with 64-bit.

Also, you probably want to use setarch rather than fooling piecemeal
with CC etc.

regards, tom lane

lrotger wrote:

> Well it probably would, changelogs are scary and I really appreciate the
> work of the postgres hackers, but you know the old adage: "if it's not
> broken, don't fix it";

The problem is that it _is_ broken, so it needs fixing. You have just
been lucky, or haven't yet detected that your data has problems.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Tom Lane wrote:
>
>
> Yes, you should. Someday one of those bugs will eat your data. The
> fact that it hasn't happened yet does not mean you're not at risk;
> a lot of them are simply race conditions with very small windows.
>
> regards, tom lane

I read some of the logs up until 7.3 (from my old 7.2.1); I should
benefit from upgrading but there are issues to consider, for example, I
see 'timestamp' was changed from with to without time zone in 7.3, I'll
have to follow these changes carefully to upgrade without problems.

Thanks,
L Rotger

lrotger wrote:
> I read some of the logs up until 7.3 (from my old 7.2.1); I should
> benefit from upgrading but there are issues to consider, for example, I
> see 'timestamp' was changed from with to without time zone in 7.3, I'll
> have to follow these changes carefully to upgrade without problems.

Tom's major recommendation is to move up to the latest 7.2 release -
which, while currently unsupported, contains a number of BIG bugfixes
relating to data integrity. (IIRC the version number is 7.2.5). You
should be able to install packages or build from source and install in
place without any changes to your code. Getting a dump of your data
beforehand is always a good idea Just In Case, but it shouldn't be
necessary most of the time.

Jumping between "major" versions (eg, 7.2->7.3) is where there are
changes to data types and the back-end storage; for a major version
change the usual recommendation is to dump and reload.

Check back through the list archives; this general recommendation ("For
PG version x.x.n, always run the latest point-release (n) for any given
major version (x.x)") has been posted quite a few times over this past
year alone.

-kgd

PostgreSQL 8.1

I'm using the uniqueidentifier datatype for a primary key in these tables. The
uniqueidentifier datatype is specified in the public schema. There is a default
operator class defined for "btree" in the public schema. These table_# tables
are in a different schema, second on the list in the search path. I can issue
the command from psql "ALTER TABLE ONLY table_1 ADD CONSTRAINT
table_1_luuid_pkey PRIMARY KEY (luuid);" and the SQL statement runs fine.
Here's the output from pg_restore...

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2982; 2606 44309156 CONSTRAINT
table_1_luuid_pkey postgres
pg_restore: [archiver (db)] could not execute query: ERROR: data type
public.uniqueidentifier has no default operator class for access method "btree"
HINT: You must specify an operator class for the index or define a default
operator class for the data type.
Command was: ALTER TABLE ONLY table_1
ADD CONSTRAINT table_1_luuid_pkey PRIMARY KEY (luuid);
pg_restore: creating CONSTRAINT table_2_luuid_pkey
pg_restore: [archiver (db)] Error from TOC entry 2988; 2606 44309158 CONSTRAINT
table_2_luuid_pkey postgres
pg_restore: [archiver (db)] could not execute query: ERROR: data type
public.uniqueidentifier has no default operator class for access method "btree"
HINT: You must specify an operator class for the index or define a default
operator class for the data type.
Command was: ALTER TABLE ONLY table_2
ADD CONSTRAINT table_2_luuid_pkey PRIMARY KEY (luuid);
pg_restore: creating CONSTRAINT table_3_luuid_pkey
pg_restore: [archiver (db)] Error from TOC entry 2990; 2606 44309160 CONSTRAINT
table_3_luuid_pkey postgres
pg_restore: [archiver (db)] could not execute query: ERROR: data type
public.uniqueidentifier has no default operator class for access method "btree"
HINT: You must specify an operator class for the index or define a default
operator class for the data type.
Command was: ALTER TABLE ONLY table_3
ADD CONSTRAINT table_3_luuid_pkey PRIMARY KEY (luuid);
pg_restore: creating CONSTRAINT table_3_puuid_pkey
pg_restore: [archiver (db)] Error from TOC entry 2960; 2606 44309162 CONSTRAINT
table_4_puuid_pkey postgres
pg_restore: [archiver (db)] could not execute query: ERROR: data type
public.uniqueidentifier has no default operator class for access method "btree"
HINT: You must specify an operator class for the index or define a default
operator class for the data type.
Command was: ALTER TABLE ONLY table_4
ADD CONSTRAINT table_4_puuid_pkey PRIMARY KEY (puuid);
pg_restore: creating CONSTRAINT table_5_puuid_pkey
pg_restore: [archiver (db)] Error from TOC entry 2998; 2606 44309164 CONSTRAINT
table_5_puuid_pkey postgres
pg_restore: [archiver (db)] could not execute query: ERROR: data type
public.uniqueidentifier has no default operator class for access method "btree"
HINT: You must specify an operator class for the index or define a default
operator class for the data type.
Command was: ALTER TABLE ONLY table_5
ADD CONSTRAINT table_5_puuid_pkey PRIMARY KEY (puuid);
pg_restore: creating CONSTRAINT table_6_puuid_pkey
pg_restore: [archiver (db)] Error from TOC entry 2962; 2606 44309166 CONSTRAINT
table_6_puuid_pkey postgres
pg_restore: [archiver (db)] could not execute query: ERROR: data type
public.uniqueidentifier has no default operator class for access method "btree"
HINT: You must specify an operator class for the index or define a default
operator class for the data type.
Command was: ALTER TABLE ONLY table_6
ADD CONSTRAINT table_6_puuid_pkey PRIMARY KEY (puuid);

I'm not sure what to make of this.

I'm puzzled and need some help figuring this out!

CG

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

http://archives.postgresql.org/pgsql-admin/2006-02/msg00084.php
reports a problem with default btree operator classes that are
not in pg_catalog: you can create a UNIQUE or PRIMARY KEY constraint
that depends on such an opclass, but then when you pg_dump and
try to reload, you get something like

pg_restore: [archiver (db)] could not execute query: ERROR: data type
public.uniqueidentifier has no default operator class for access method "btree"
HINT: You must specify an operator class for the index or define a default
operator class for the data type.
Command was: ALTER TABLE ONLY table_1
ADD CONSTRAINT table_1_luuid_pkey PRIMARY KEY (luuid);

The problem is that pg_dump sets up a restrictive search path during the
restore, basically just the schema of the object being restored (plus
the implicit reference to pg_catalog). There are good reasons for that
behavior and I'm disinclined to mess with it --- but meanwhile,
GetDefaultOpClass only looks at operator classes that are in the current
search path. So if the desired opclass is not in pg_catalog and also
not in the same schema as the table being restored, you lose.

Given that we only allow one default opclass for a datatype regardless
of schema (see DefineOpClass), it's not really necessary for
GetDefaultOpClass to restrict its search. I can think of some corner
cases involving multiple binary-compatible-datatype matches where the
restriction might give a unique answer when an unrestricted search would
not, but I kinda doubt this would ever arise in practice.

The only other solution I can see is to extend the ADD CONSTRAINT syntax
to allow explicit specification of an opclass for each column. This
might be a good thing to do in itself, but it looks like a new feature
to me, rather than something we could reasonably apply as a bug fix.
It would certainly be a much larger code change (affecting both pg_dump
and the backend) than changing the behavior of GetDefaultOpClass. And
it'd not fix the problem for existing dump files, either.

So I'm leaning towards removing the search-path dependency of
GetDefaultOpClass. Comments?

regards, tom lane

On Wed, Feb 08, 2006 at 09:04:46PM -0500, Tom Lane wrote:
> http://archives.postgresql.org/pgsql-admin/2006-02/msg00084.php
> reports a problem with default btree operator classes that are
> not in pg_catalog: you can create a UNIQUE or PRIMARY KEY constraint
> that depends on such an opclass, but then when you pg_dump and
> try to reload, you get something like

<snip>

> The only other solution I can see is to extend the ADD CONSTRAINT syntax
> to allow explicit specification of an opclass for each column. This
> might be a good thing to do in itself, but it looks like a new feature
> to me, rather than something we could reasonably apply as a bug fix.
> It would certainly be a much larger code change (affecting both pg_dump
> and the backend) than changing the behavior of GetDefaultOpClass. And
> it'd not fix the problem for existing dump files, either.
>
> So I'm leaning towards removing the search-path dependency of
> GetDefaultOpClass. Comments?

I'm for. IMHO, if you give someone has access to the type they should
have access to the supporting machinary. Whoever created the type
probably also created the operator class and intended it to be used.
For a comparison, we don't check the schema on looking up type
input/output functions (well, we don't need to because we have the oid,
but the idea is important).

W.R.T. the other option (per column opclass specification), if we ever
do COLLATE users will be allowed to specify it on a per-column basis
anyway. Then specifying opclasses becomes redundant. I've been
seriously neglecting this patch but hope to get back to it soon...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Tom Lane wrote:

> Given that we only allow one default opclass for a datatype regardless
> of schema (see DefineOpClass), it's not really necessary for
> GetDefaultOpClass to restrict its search. I can think of some corner
> cases involving multiple binary-compatible-datatype matches where the
> restriction might give a unique answer when an unrestricted search would
> not, but I kinda doubt this would ever arise in practice.

How about doing the constrained search first, and revert to the
unconstrained behavior if it doesn't find the desired opclass?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Tom Lane wrote:
>> Given that we only allow one default opclass for a datatype regardless
>> of schema (see DefineOpClass), it's not really necessary for
>> GetDefaultOpClass to restrict its search.

> How about doing the constrained search first, and revert to the
> unconstrained behavior if it doesn't find the desired opclass?

Seems like rather a lot of work to preserve a behavior that (AFAICS)
isn't even documented anywhere.

regards, tom lane