Preserve index names

[dnd]

Is there an option when importing from a mysql database to preserve index names? Right now the indexes all get renamed to be prefixed with idx_#####_. I need the index names to remain the same because my ORM expects them in a specific format.

[dimitri]

The problem is that both MySQL and MSSQL manage the index namespace per-relation, meaning that two different tables may have each its own index and both indexes can share the same name exactly. PostgreSQL's index namespace is the schema, same as any other relation, thus we would have conflicts if we did it the way you're asking for.

While it is certainly possible to refrain pgloader from uniquifying the index names, can you double check that your schema would import (a catalog query will return you all duplicate index names) and more importantly check what your ORM is doing when working with PostgreSQL as compared to another database system?

[dnd]

The index format used is index_<table>_on_<column-1>_<column-n>, so within a given schema I shouldn't have any duplicate index names. I queried the indexes to double-check and there are for sure no duplicates.

You said it's possible to prevent pgloader from uniquifying the index names. Is that a current option, or must it be added?

[dimitri]

It's not possible currently, but could be added. Given what you said, it's an ORM compat' option, important enough to warrant a new development here. Stay tuned, should be available by next week or abouts.

[Raven24]

I came across the same problem while trying to migrate a Ruby on Rails app from mysql to postgres. Implementing an option to preserve index names would be helpful, indeed.

[dimitri]

Tonight seemed like the perfect opportunity to close off this one, please test and report back ;-)

Note that if you're using the command line only you may prefer adding the switch --with preserve index names rather than writing a full command.

[ngoonee]

Tested this today, got the error that "Could not parse --with ("preserve index names"). Is this still working?

[dimitri]

Yes, I just tried to confirm. As you can see in the database I'm using the same index name is used in several tables, so we have errors when we use the option. This confirms that the option is working correctly tho.

Please review your spelling of the option, in particular I can see extra parens used in your bug report.

$ pgloader --with "preserve index names" mysql://root@localhost/f1db pgsql:///plop
2017-07-17T10:41:41.283000+02:00 ERROR PostgreSQL Database error 42P07: relation "url" already exists
QUERY: CREATE UNIQUE INDEX url ON f1db.drivers (url);
2017-07-17T10:41:41.892000+02:00 ERROR PostgreSQL Database error 42P07: relation "url" already exists
QUERY: CREATE UNIQUE INDEX url ON f1db.races (url);
2017-07-17T10:41:41.892000+02:00 ERROR PostgreSQL Database error 42P07: relation "url" already exists
QUERY: CREATE UNIQUE INDEX url ON f1db.seasons (url);
2017-07-17T10:41:43.910000+02:00 ERROR PostgreSQL Database error 42P07: relation "raceid" already exists
QUERY: CREATE INDEX raceId ON f1db.laptimes (raceid);
2017-07-17T10:41:44.920000+02:00 LOG report summary reset
               table name       read   imported     errors      total time
-------------------------  ---------  ---------  ---------  --------------
          fetch meta data         33         33          0          0.402s 
           Create Schemas          0          0          0          0.044s 
         Create SQL Types          0          0          0          0.005s 
            Create tables         26         26          0          0.071s 
           Set Table OIDs         13         13          0          0.004s 
-------------------------  ---------  ---------  ---------  --------------
            f1db.circuits         73         73          0          0.062s 
  f1db.constructorresults      11011      11011          0          0.124s 
        f1db.constructors        208        208          0          0.036s 
             f1db.drivers        841        841          0          0.089s 
f1db.constructorstandings      11766      11766          0          0.198s 
            f1db.laptimes     413578     413578          0          2.556s 
     f1db.driverstandings      31420      31420          0          0.413s 
            f1db.pitstops       5796       5796          0          0.154s 
               f1db.races        976        976          0          0.083s 
             f1db.seasons         68         68          0          0.048s 
          f1db.qualifying       7257       7257          0          0.139s 
             f1db.results      23514      23514          0          0.388s 
              f1db.status        133        133          0          0.046s 
-------------------------  ---------  ---------  ---------  --------------
  COPY Threads Completion          4          4          0          3.114s 
           Create Indexes         20         16          4          0.881s 
   Index Build Completion         20         20          0          0.553s 
          Reset Sequences         10         10          0          0.061s 
             Primary Keys         13         13          0          0.009s 
      Create Foreign Keys          0          0          0          0.000s 
          Create Triggers          0          0          0          0.001s 
         Install Comments          0          0          0          0.000s 
-------------------------  ---------  ---------  ---------  --------------
        Total import time     506641     506641          0          4.924s 

[ngoonee]

Odd, this is my line

/home/data/Downloads/pgloader-bundle-3.4.1/bin/pgloader --with "preserve index names" mysqlitedb postgresql:///mydb

And this is the error I get:-

2017-07-17T16:45:51.020000+08:00 ERROR Could not parse --with ("preserve index names"):
2017-07-17T16:45:51.029000+08:00 ERROR At

preserve index names
^ (Line 1, Column 0, Position 0)

In context SQLITE-OPTION:

While parsing SQLITE-OPTION. Expected:

the character Tab
or the character Newline
or the character Return
or the character Space
or the string "--"
or the string "/*"
or the string "batch"
or the string "concurrency"
or the string "create"
or the string "data"
or the string "disable"
or the string "drop"
or the string "encoding"
or the string "foreign"
or the string "include"
or the string "max"
or the string "no"
or the string "on"
or the string "prefetch"
or the string "reset"
or the string "schema"
or the string "truncate"
or the string "work

Could not parse the command line: see above.

[dimitri]

Oh your source is SQLite. Yes this options is currently not supported with SQLite.

[ngoonee]

Ah. Right that explains it then. Thanks, I ended up doing a workaround (use pgloader for data migration, then pg_dump/pqsl to import the data to another table which has the right structure).

[dimitri]

So if you want to, given the latest patch, you can build pgloader from sources and use the option with SQLite.

[maikbrunner]

Hi Dimitri, I am currently using pgloader to migrate a MS SQL database to PostgreSQL. pgloader is version 3.4.1 running on Ubuntu 18.04 LTS. Migration now runs smooth after having some configuration issues with FreeTDS. Unfortunately indexes are renamed during migration. Is the option "preserve index names" also available for MS SQL data sources? Regards, Maik

[dimitri]

Hi @maikbrunner ; that's the perfect time to ask for that. See #902 where we just fixed it. Please build from fresh sources and it should work just fine.