Thread: Actual expression of a constraint
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@aircomp.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@aircomp.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@aircomp.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@aircomp.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@aircomp.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@yahoo.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@sss.pgh.pa.us> wrote: > CG <cgg007@yahoo.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@yahoo.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