Thread: Constraint Bug

Trevor McGing wrote:

> Hi,
>  
> Platform Windows XP
>  
> Distribution you used (Binary pgAdmin 3 )
>  
> I have been using pgAdmin 2 under dbexperts postgresql 7.2 for 
> windows.  Where I created a database.
>  
> I have just upgraded to dbexperts Postgresql 7.3.4 for windows, 
> migrated the database from one version to the other and I am now using 
> pgAdmin 3.  Everything seamed to be OK until I looked at the FK 
> constraints.  At first I thought it was a problem with the migration, 
> so I re-created the Contraints using pgAdmin 3, which there was no 
> problem in doing.  However I then went back to pg Admin 2 to see if it 
> would show the contraints as a check, it compained about duplicate 
> contriaints being present.  After removing the Constraints that I had 
> just added in pgAdmin 3, pgAdmin 2 allowed me to see the contraints 
> without any errors.
>  
> To sum up pgAdmin 3 does not seam to be displaying the FK contraint 
> information at all, either in the dialog boxes or the SQL code, and 
> does not complain if you re-add a constraint that is already there.
>
This problem is not known. Can you supply a small ddl script of some 
tables and FKs so we can have a look at it?
The problem might result from an old style FK representation (actually 
no constraint, but a trigger), which should be converted to new style 
(I'm no expert for <= 7.2, so it's just a guess)

Regards,
Andreas

For the record, I have also experienced this exact problem when upgrading from PGSQL 7.2 to PGSQL 7.3 (and pgAdminII to
pgAdminIIIat the same time).
 

My FK constraints are still there, but are totally invisible in pgAdminIII.

Andreas Pflug <pgadmin@pse-consulting.de> wrote :

> Trevor McGing wrote:
> 
> > Hi,
> >  
> > Platform Windows XP
> >  
> > Distribution you used (Binary pgAdmin 3 )
> >  
> > I have been using pgAdmin 2 under dbexperts postgresql 7.2 for 
> > windows.  Where I created a database.
> >  
> > I have just upgraded to dbexperts Postgresql 7.3.4 for windows, 
> > migrated the database from one version to the other and I am now using 
> > pgAdmin 3.  Everything seamed to be OK until I looked at the FK 
> > constraints.  At first I thought it was a problem with the migration, 
> > so I re-created the Contraints using pgAdmin 3, which there was no 
> > problem in doing.  However I then went back to pg Admin 2 to see if it 
> > would show the contraints as a check, it compained about duplicate 
> > contriaints being present.  After removing the Constraints that I had 
> > just added in pgAdmin 3, pgAdmin 2 allowed me to see the contraints 
> > without any errors.
> >  
> > To sum up pgAdmin 3 does not seam to be displaying the FK contraint 
> > information at all, either in the dialog boxes or the SQL code, and 
> > does not complain if you re-add a constraint that is already there.
> >
> This problem is not known. Can you supply a small ddl script of some 
> tables and FKs so we can have a look at it?
> The problem might result from an old style FK representation (actually 
> no constraint, but a trigger), which should be converted to new style 
> (I'm no expert for 
> 
> Regards,
> Andreas
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

--Matt

Trevor McGing wrote:

>Please find attached three files, 2 pg_dump files from both posgres versions, and a schema genorated by pg Admin 2, i
couldnot find away to do this in version 3. The schema was created before the migration, I tried to produce one after,
butpgAdmin 2 had problems with postgres 7.3.
 
>
>I get the same issue, pgAdmin 2 shows the constrants, and pgAdmin 3 does not.
>
>If I create a constraint with pgAdmin 3, pgAdmin 2 reflects that change.
>
>Hope these files help you.
>  
>

OK Trevor,
the 7.2 dump works for me. It uses constraint triggers, as I suspected. 
pgAdmin3 suppresses constraint triggers, because they're working behind 
the scene and  are not intended to be modified by users (for 7.3 and up).
When I run the script against 7.4, the script succeeds, and no 
constraint is shown because there is no pg_constraint entry. There's the 
script contrib/adddepend, which adds the missing rows.

I updated the FAQs.

Regards,
Andreas