Menu
▾
▴
[Bacula-bugs] [bacula 0001623]: PostgreSQL table updater attempts to create an index that already existed
|
From: Mantis B. T. <no...@bu...> - 2010-09-07 15:53:57
|
A NOTE has been added to this issue. ====================================================================== http://bugs.bacula.org/view.php?id=1623 ====================================================================== Reported By: jgoerzen Assigned To: ====================================================================== Project: bacula Issue ID: 1623 Category: Director Reproducibility: sometimes Severity: minor Priority: normal Status: feedback ====================================================================== Date Submitted: 2010-08-20 20:55 BST Last Modified: 2010-09-07 16:53 BST ====================================================================== Summary: PostgreSQL table updater attempts to create an index that already existed Description: In update_postgresql_tables.in, there is: CREATE INDEX file_jpfid_idx on File (JobId, PathId, FilenameId); And at http://bugs.debian.org/591293 I received a bug report indicating that upgrades were failing with this message: ERROR: relation "file_jpfid_idx" already exists Now, investigating a bit... In the 5.0.2 source tree, updatedb/update_postgresql_tables_7_to_8 contains this line: create index file_jpfid_idx on file (jobid, pathid, filenameid); In the 3.0.3 source tree, src/cats/make_postgresql_tables.in containts this line: CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid); However, it was commented out in your distribution in 2.4.4 (but uncommented for Debian). So it seems like people that installed fresh with 3.0.0 or later will already have the index and thus the upgrade will fail. Those that started with 2.4.4 will not have the index and will get it. But, of course, those that started with 1.34 will also fail because it was added for them with the upgrade to 1.35. It seems that there may need to be some automated validation of these schema updates; see for instance also http://bugs.bacula.org/view.php?id=1498 ====================================================================== ---------------------------------------------------------------------- (0005566) jgoerzen (reporter) - 2010-08-20 20:56 http://bugs.bacula.org/view.php?id=1623#c5566 ---------------------------------------------------------------------- So I guess I should add I think the proper step here is to remove the file_jpfid_idx from update_postgresql_tables.in. ---------------------------------------------------------------------- (0005583) ebollengier (administrator) - 2010-08-31 13:30 http://bugs.bacula.org/view.php?id=1623#c5583 ---------------------------------------------------------------------- Can you explain me what is needed to simplify the upgrade process? When saying "remove the file_jpfid_idx from update_postgresql_tables.in", are you refering to updatedb/update_postgresql_tables_7_to_8.in ? Doing smooth and smart database schema upgrade is always a pity, it requires lots of work and we prefer let the user adjust the upgrade script to their setup. It should be possible to add a couple of "IF EXISTS/IF NOT EXISTS" in index creation, but I'm not sure that all postgresql version handle them. ---------------------------------------------------------------------- (0005586) jgoerzen (reporter) - 2010-08-31 19:20 http://bugs.bacula.org/view.php?id=1623#c5586 ---------------------------------------------------------------------- No, I meant src/cats/update_postgresql_tables.in ---------------------------------------------------------------------- (0005591) ebollengier (administrator) - 2010-09-07 16:53 http://bugs.bacula.org/view.php?id=1623#c5591 ---------------------------------------------------------------------- This index wasn't present in 3.0.0, 3.0.1 and 3.0.2, so users that upgrade from those versions need it, and PostgreSQL doesn't have any elegant way to test it before 9.0... We can probably use a SQL command to see if this index is present before creating it, but it won't fit very well in you packaging. (that uses SQL files instead of shell scripts) Issue History Date Modified Username Field Change ====================================================================== 2010-08-20 20:55 jgoerzen New Issue 2010-08-20 20:56 jgoerzen Issue Monitored: jgoerzen 2010-08-20 20:56 jgoerzen Note Added: 0005566 2010-08-31 13:30 ebollengier Note Added: 0005583 2010-08-31 13:30 ebollengier Status new => feedback 2010-08-31 19:20 jgoerzen Note Added: 0005586 2010-09-07 16:53 ebollengier Note Added: 0005591 ====================================================================== |