Running grate on a postgres database which had previously been migrated using roundhouse does not work. #3

[cocytus]

Reproducing bug:

Run RH on Postgres DB (on Windows). Works. Tables created.

Run Grate on same DB using correct --schema. Grate renames/recreates the scriptsrun table to ScriptsRun.
No errors because of no schema changes.

Create a new sql script for grate to run in up folder. Error:

Update ("up"):
--------------------------------------------------------------------------------
0053_TheScript.sql: 23505: duplicate key value violates unique constraint "roundhouse_scriptsrun_pk"
(0,0): UPDATE "MyTable"
      ^ 23505: duplicate key value violates unique constraint "roundhouse_scriptsrun_pk"
Exception: Database migration (Update) failed!, exiting.. 1

Reason: ScriptsRun id column has
SELECT nextval('roundhouse.scriptsrun_id_seq'::regclass) default value. This value is not set to the correct MAX(id)+1 after the RH->Grate migration.

[narchibald]

I have run into this issue as well. It seems even though you specify the schema as 'roundhouse' to match the existing schema it does not find the existing tables. From looking into the schema it turns out it creates the tables it needs in this schema but they prefix with "Grate". Is there a way to tell the tool not to prefix the tables? Maybe the tool should not prefix the tables if they are created in a schema.

[erikbra]

Hi, guys!

Grate creates two sets of migration tables:

1. The ones prefixed with "Grate" is for internal Grate use, and handles versioning of the migration tables themselves
2. The ones without prefix, which are used for the users' tables

The ones in 2) should be compatible without any changes. But there might of course be bugs.
I don't have access to any computer to run RoundhousE on now, as I don't own a Windows computer. But, do you have an example or two, which you can share, with the text_of_script and text_hash from running of both RoundhousE and grate, and if they are different? It's difficult to fix any incompatibilities without having any examples to compare with, to verify that we are able to produce the exact same hash in grate as in RoundhousE

[erikbra]

Hi again, @cocytus and @narchibald , could you please see if this comment seems to be the case for you too?

#550 (comment)

And, does it work if you do as suggested in the next comment, #550 (comment)?

If this is the case, I would recommend that we just change grate so that we don't bother with the script_name at all when checking if a script is equal? Or, at least, accept both. I like the new feature in grate that the directory name is included, but it seems like this is causing issues using grate as a drop-in replacement for RoundhousE

[arrty]

Hi! I have to run into same problem, but after some investigation i found out that RoundhousE don't use autoincrement when inserts record into "version", "scriptrun", "scriptrunerrors"
So I used this script before first migration with grate:

select setval('roundhouse.version_id_seq'::regclass, (select max(id) + 1 from roundhouse.version));
select setval('roundhouse.scriptsrunerrors_id_seq'::regclass, (select max(id) + 1 from roundhouse.scriptsrunerrors));
select setval('roundhouse.version_id_seq'::regclass, (select max(id) + 1 from roundhouse.version));

btw i have to use --schema=roundhouseoption (exact casing)