Thread: Wrong OID for Primary Key and UNIQUE constraints shown in property pane

Hello,<br /><br />I tried to get the definition of unique and primary key constraints via<br /><br />select
pg_get_constraintdef(<oidofconstraint>)<br/><br />as oidofconstraint I put the OID displayed in pg_admins
propertypain when selecting a constraint. <br /><br />This failed with <br clear="all" /><br />ERROR: could not find
tuplefor constraint 36099<br />SQL state: XX000<br /><br />when I took the OID from UNIQUE and PRIMARY KEY
constraints.<br/><br />Then I moved on and took the OID from FOREIGN KEY constraints, and got results... <br /><br
/><br/>digging in pg_catalog.pg_constraints made me learn that pg_admin shows those OIDs for UNIQUE and PRIMARY key
constraints"off by one", that is:<br /><br />if  I do<br /><br />select pg_get_constraintdef(36100) <br /><br />instead
of<br/><br />select pg_get_constraintdef(36099)<br /><br />I get the needed result....<br /><br />Please find that "-1"
andadd it again :)<br /><br />Best wishes,<br /><br />Harald<br /><br /><br />-- <br />GHUM Harald Massa <br
/>persuadereet programmare<br />Harald Armin Massa<br />Reinsburgstraße 202b<br />70197 Stuttgart<br />0173/9409607<br
/>fx01212-5-13695179 <br />-<br />Python: the only language with more web frameworks than keywords.  

Harald Armin Massa wrote:
> Hello,
> 
> I tried to get the definition of unique and primary key constraints via
> 
> select pg_get_constraintdef(<oidofconstraint>)
> 
> as oidofconstraint I put the OID displayed in pg_admins property pain
> when selecting a constraint.
> 
> This failed with
> 
> ERROR: could not find tuple for constraint 36099
> SQL state: XX000
> 
> when I took the OID from UNIQUE and PRIMARY KEY constraints.
> 
> Then I moved on and took the OID from FOREIGN KEY constraints, and got
> results...
> 
> 
> digging in pg_catalog.pg_constraints made me learn that pg_admin shows
> those OIDs for UNIQUE and PRIMARY key constraints "off by one", that is:
> 
> if  I do
> 
> select pg_get_constraintdef(36100)
> 
> instead of
> 
> select pg_get_constraintdef(36099)
> 
> I get the needed result....
> 
> Please find that "-1" and add it again :)

Thanks Harald - it was showing the OID of the index, not the constraint
record. I've fixed that, and modified the code to display the index OID
as a seperate property.

Regards, Dave

Dave,



--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
fx 01212-5-13695179
-
Python: the only language with more web frameworks than keywords.

Harald Armin Massa wrote:
> Dave,
> 
> 
>     Thanks Harald - it was showing the OID of the index, not the constraint
>     record. I've fixed that, and modified the code to display the index OID
>     as a seperate property.
> 
> 
> thanks for fixing. One question: why did it work correctly on foreign 
> key constraints? (not to be nitpicking, just to make sure there is no 
> other bug hidden)

No problem - we like to spread the knowledge!

It worked for fkeys because they aren't index based constraints. The 
pkey and unique constraint code is actually derived from the pgIndexBase 
class, so 95% of the code used is the same as pgIndex. fkeys are a whole 
seperate class.

Regards, Dave