Cartodb: User mover error with rasters + overlays

[javitonino]

STR:

• Import a raster into CARTO, and create some overviews (CARTO automatically creates one for level 1)
• Try to move the user

The check _overview_constraint for the overview can fail. The issues is that with parallel execution, it is possible that the overlay data will be imported before the raster itself, and this check will fail.

Workarounds:

• Run pg_restore in order (remove -j X) <-- this one doesn't always work.
• ~Disable the check before export, reeenable after import. e.g~ (DON'T DO THIS, see further comment about using a pg_restore index):

SELECT DropOverviewConstraints('schema'::name, 'o_16_tablename'::name, 'the_raster_webmercator'::name);
SELECT AddOverviewConstraints('schema'::name, 'o_16_tablename'::name, 'the_raster_webmercator'::name, 'schema'::name, 'tablename'::name, 'the_raster_webmercator'::name, 16);

As this is an uncommon case, I would suggest making -j parametrizable and just passing that parameter when the raster situation is detected. Slower import but it works.

cc @juanignaciosl

[juanignaciosl]

We can probably come up with an automatic detection of that situation, isn't it? That way we could add -j only if needed.

[javitonino]

🤔 Detection during the import part of the process might be tricky. But maybe by listing the contents of the dump and looking for tables following the naming schema of raster overlays. Although it is an heuristic approach, it might be good enough. Worst case, we import a bit slower. Sounds like a good idea!

I'd like to have configurable number of workers anyway (app_config), because we might want to change this depending on which server we are running the migration from.

[juanignaciosl]

What about detecting it at export time and add it as metadata?

[javitonino]

Increases coupling between import and export, which I've been trying to keep to a minimum. I mean, we can add it if we find it necessary but I'd like to avoid adding more parameters as a general guideline.

[juanignaciosl]

This can be seen as a trick but I think that it's legit: the metadata would be something indicating that the user has raster data. Then, the export won't parallelize the import if the user has raster data 😁

[javitonino]

It's not exactly a trick, but it is not ideal either. I'd be more comfortable adding it to the json generated by user-mover than to the migration models.

Anyway, I just realised that we can properly detect it on import, by checking if the dump file has an _overview_constraint in it (pg_restore -l and some grepping should do it). So no, you are not adding more fields to my lean models! Stop trying to convince me! :P

[juanignaciosl]

_Disclaimer: read the full comment before doing anything based on this._

For future reference, the -j1 hack didn't work at CartoDB/tech-ops/issues/292. The error (check the comment) was weird because the constraint didn't appear at the index file, so I chose a different manual alternative.

This would've been the "normal manual" approach:

1. Given the exported dump, run pg_restore with -l so it outputs an index. That output must be redirected to a file. Example: pg_restore -l /tmp/user_export_<id>/data/org_<id>.dump > /tmp/org.dump.index
2. Comment (with ;) the lines with overview constraints.
3. Run the import (the "rake import", not the pg_restore) with -L so it skips the overviews.
4. Add the missing constraints.

Nevertheless, I had to do this:

1. Given the exported dump, I run pg_restore with -l so it outputs an index. That output must be redirected to a file. Example: pg_restore -l /tmp/user_export_<id>/data/org_<id>.dump > /tmp/org.dump.index
2. I commented (with ;) the lines with TABLE DATA from overviews (lines containing TABLE DATA on an overview table, which are the ones that begin with o_).
3. Then, I patched services/user-mover/import_user.rb, adding -L /tmp/org.dump.index to pg_restore command.
4. Then I run the import so it skips the overview data tables.
5. Aftwerwards, I opened /tmp/org.dump.index, deleted everything but the commented lines and uncommented those.
6. Then I run pg_restore (you have to download and unzip the S3 file again, using the dump path for this comment:

/tmp/user_imports$ /usr/lib/postgresql/9.5/bin/pg_restore -e --verbose --disable-triggers -L /tmp/org.dump.index -Fc /tmp/user_imports/data/org_<id>.dump -U postgres -h <IP> -p 5432 -d cartodb_user_<id>_db

This also failed, same error 🎉

So, let's drop the constraint and copy the data:

SELECT DropOverviewConstraints('<schema>'::name, 'o_16_<table_name>'::name, 'the_raster_webmercator'::name);
SELECT DropOverviewConstraints('<schema>'::name, 'o_2_<table_name>'::name, 'the_raster_webmercator'::name);
SELECT DropOverviewConstraints('<schema>'::name, 'o_4_<table_name>'::name, 'the_raster_webmercator'::name);
SELECT DropOverviewConstraints('<schema>'::name, 'o_8_<table_name>'::name, 'the_raster_webmercator'::name);

Uncomment the lines of the index and run again so it just imports the data tables:

/tmp/user_imports$ /usr/lib/postgresql/9.5/bin/pg_restore -e --verbose --disable-triggers -L /tmp/org.dump.index -Fc /tmp/user_imports/data/org_<id>.dump -U postgres -h <IP> -p 5432 -d cartodb_user_<id>_db

Recreate the overviews:

SELECT AddOverviewConstraints('<schema>'::name, 'o_16_<table_name>'::name, 'the_raster_webmercator'::name, '<schema>'::name, '<table_name>'::name, 'the_raster_webmercator'::name, 16);
SELECT AddOverviewConstraints('<schema>'::name, 'o_2_<table_name>'::name, 'the_raster_webmercator'::name, '<schema>'::name, '<table_name>'::name, 'the_raster_webmercator'::name, 2);
SELECT AddOverviewConstraints('<schema>'::name, 'o_4_<table_name>'::name, 'the_raster_webmercator'::name, '<schema>'::name, '<table_name>'::name, 'the_raster_webmercator'::name, 4);
SELECT AddOverviewConstraints('<schema>'::name, 'o_8_<table_name>'::name, 'the_raster_webmercator'::name, '<schema>'::name, '<table_name>'::name, 'the_raster_webmercator'::name, 8);

This approach is better than dropping + recreating the constraints because it doesn't require touching the source DB. Nevertheless, in this case we could've considered doing that instead, because it's really strange that the index didn't contain the failing constraints.