Обсуждение: [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  
------------------------------+---------------------------------------------
 When I open the properties on a table containing foreign key constraints,
 and then to go the SQL tab to see what will be executed, I notice all
 foreign keys are dropped and then recreated. For example when I open the
 table properties for the table zulook.inv_equipment on my database, the
 SQL pane reads as follow:

 ALTER TABLE zulook.inv_equipment
    DROP CONSTRAINT inv_equipment_company_entity_id_fkey;
 ALTER TABLE zulook.inv_equipment
    DROP CONSTRAINT inv_equipment_item_id_fkey;
 ALTER TABLE zulook.inv_equipment
    ADD CONSTRAINT inv_equipment_company_entity_id_fkey FOREIGN KEY
 (company_entity_id)
        REFERENCES zulook.ab_company (company_entity_id) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY
 IMMEDIATE;
 ALTER TABLE zulook.inv_equipment
    ADD CONSTRAINT inv_equipment_item_id_fkey FOREIGN KEY (item_id)
        REFERENCES zulook.inv_item (item_id) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY
 IMMEDIATE;


 I didn't have this behaviour problem under 1.12.x versions, I've noticed
 it starting with 1.14.x versions, and now it is still present in 1.16.0. I
 use pgAdmin 1.16.0 (Sep 7 2012, rev: REL-1_16_0, from binary) under
 Windows XP SP3, English version but on a Canadian French OS.

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

#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

#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):

 Hi,

 did somebody take a look at this? I'd like to push this fix for Debian if
 it gets approved, as it appears to be pretty serious to me.

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

#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 gleu):

 I took a quick look at it and while it seems to fix the issue, it still
 makes me uncomfortable. I'll try to find some time next week to work on
 this.

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

#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):

 Any news on this?

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