#21 postgresql producer creates duplicate indices

[Markus Grabner]

The PostgreSQL producer uses the column name as the index name in the "CREATE INDEX" statement, therefore indices on columns "table1.column" and "table2.column" are both named "column", which is an error. The attached patch uses the less ambiguous name "${table_name}_${name}_idx" as suggested by the examples in the documentation of "CREATE INDEX".

[Markus Grabner]

patch to fix ambiguous index name

[Jonathan Yu]

Looking at the code for the Producer, it seems that the index name is generated using next_unused_name:

sub next_unused_name {
my $name = shift || '';
if ( !defined( $used_names{$name} ) ) {
$used_names{$name} = $name;
return $name;
}

my $i = 2;
while ( defined( $used_names{ $name . $i } ) ) {
++$i;
}
$name .= $i;
$used_names{$name} = $name;
return $name;
}

My understanding of this code is that it simply adds numbers after the column name until one that doesn't conflict with anything else is found.

Can you provide a generated PostgreSQL schema so we can validate this?

An alternate solution in the interim is to specify indexes manually using the sqlt_deploy_hook

[Markus Grabner]

Yes, the duplicate index problem has been solved by in version 0.09001, but the current implementation (with next_unused_name) has a different issue which I discussed in the following mailing list thread:

https://sourceforge.net/mailarchive/forum.php?thread_name=200809221411.18508.grabner%40icg.tugraz.at&forum_name=sqlfairy-developers

As I just noticed, I accidently sent my reply to Jess Robinson's address instead of the list, so I repeat this message here:

> I'm slightly curious as to how that works. Are the update files created by
> diffing the original with the new schema using SQLT?. And there the index
> name thing doesnt take into account the tables it ignored because they are
> the same?
The MySQL upgrade files are created by hand, the problem is the conversion to
PostgreSQL. Consider the following example:

software version 1.0 database initialisation script (MySQL):

CREATE TABLE a (id INT NOT NULL, ..., PRIMARY KEY(id))
CREATE TABLE b (id INT NOT NULL, ..., PRIMARY KEY(id))

software version 1.1 database initialisation script (MySQL):

CREATE TABLE a (id INT NOT NULL, ..., PRIMARY KEY(id))
CREATE TABLE b (id INT NOT NULL, ..., PRIMARY KEY(id))
CREATE TABLE c (id INT NOT NULL, ..., PRIMARY KEY(id))

Now if you upgrade from 1.0 to 1.1, you obviously need the following upgrade
script (assuming tables "a" and "b" are unmodified):

CREATE TABLE c (id INT NOT NULL, ..., PRIMARY KEY(id))

If the database init scripts are converted to PostgreSQL with
SQL-Translator-0.09001, the following indices are created:

software version 1.0 database initialisation script (PostgreSQL):

CREATE INDEX "id" on "a" ("id");
CREATE INDEX "id2" on "b" ("id");

software version 1.1 database initialisation script (PostgreSQL):

CREATE INDEX "id" on "a" ("id");
CREATE INDEX "id2" on "b" ("id");
CREATE INDEX "id3" on "c" ("id");

These two scripts differ by index "id3", but if you run the converter on the
upgrade script (which only contains table "c"), you get the following:

CREATE INDEX "id" on "c" ("id");

Running this against a database initialised with the 1.0 init script results
in an error since index "id" has already been defined. So instead of running
the converter on the (MySQL) upgrade script, one would have to manually
extract the differences from the full (PostgreSQL) init scripts to create the
(PostgreSQL) upgrade scripts, which is clearly undesirable.

> Either way part of our problem was maintaining backwards compatibility
> with existing files that SQLT has produced, which makes this tricky.
>
> Mind reminding me what the index-name-guessing used to do that worked for
> you?
The proposal was to use unique index names containing both table and column
names, so in the example above, it would create indices

a_id_idx
b_id_idx
c_id_idx

instead of

id
id2
id3

i.e., it doesn't depend on the order the tables are processed or on the
existence of previously created tables and/or indices, see also

http://sourceforge.net/tracker/index.php?func=detail&aid=2006145&group_id=47619&atid=450049

Do you think this proposal can be incorporated into the next release? Maybe
this behaviour can be made optional if you are concerned about backwards
compatibility.

Thanks & kind regards,
                Markus

[Markus Grabner]

build unique index name from table and column names

[Markus Grabner]

BTW, I upgraded the initially proposed patch to version 0.09002 (using table/column names instead of "next_unused_name" to disambiguate index names).
File Added: SQL-Translator-psql-index.patch