Re: [pgAdmin III] #378: Editing table properties drops all foreign keys and recreates them

#378: Editing table properties drops all foreign keys and recreates them
------------------------------+---------------------------------------------
Reporter: Patrice Lefebvre | Owner: dpage
Type: bug | Status: new
Priority: minor | Milestone:
Component: pgadmin | Version: trunk
Keywords: foreignkeys | Platform: all
------------------------------+---------------------------------------------

Comment(by mbanck):

First of all, I disagree with categorizing this bug as "minor".
Recreating the foreign key constraint will hold an exclusive lock on both
the local and the foreign table while the foreign key constraint is
verified (see http://archives.postgresql.org/pgsql-
hackers/2012-11/msg00497.php). Depending on how big the tables are, the
locks can be held for a long time.

At one client, we had a production database grind to a halt because the
DBA wanted to add a GRANT to a table and did not inspect the SQL tab
closely, resulting in the above scenario.

Thankfully, the client allowed us to look at the problem on their time. It
appears this bug is a regression introduced with
http://git.postgresql.org/gitweb/?p=pgadmin3.git;a=patch;h=9280f9654714872816ac3b1a40455536c754ea4d;hp=b80b851a35fdd090b0d5fe9ed43d5563a4f8bd3d

I debugged PGAdmin3 (version 1.14.2) and it appears the problem is as
follows: when comparing the constraints around line 820 of
pgadmin/dlg/dlgTable.cpp, the content of the wxStringArray
constraintsDefinition contains additional whitespace and newlines compared
to the content of previousConstraints (and thus tmpDef, in which the
definition is searched for), resulting in no match (index = -1) and the
subsequent deletion of the constraint.

It appears the culprit is when adding the existing constraints to
constraintsDefinition and lstConstraints in dlgTable::Go. For the
PGM_FOREIGNKEY case around line 355 in pgadmin/dlg/dlgTable.cpp,
whitespace and newlines get stripped off for lstConstraints but not for
constraintsDefinition, resulting in different strings. It is unclear why
the whitespace is removed for PGM_FOREIGNKEY but not for any of the other
constraints types, and git annotate does not help either as this code got
last touched during a reindent run.

I propose to remove that stripping of whitespace from lstConstraints, this
fixes the problem for me. However, I do not have a Windows development
environment in order to check whether the original bug as reported by
Vjacheslav Vjacheslav in http://archives.postgresql.org/pgadmin-
support/2011-12/msg00010.php is still fixed, but I suppose so.

Best regards,

Michael

--
Ticket URL: <http://code.pgadmin.org/trac/ticket/378#comment:1>
pgAdmin III <http://code.pgadmin.org/trac/>
pgAdmin III