CODEWITHSUNDEEP
hibernatepostgresqlgrails
Noxville
Noxville

Reputation: 544

Grails generating

I'm busy doing a fairly complex data migration from MySQL to Postgres on a Grails project.

I'm using GORM to generate the schema in PostGres and then doing MySQL -> mysqldump, some data manipulation and then importing into Postgres.

Obviously the thing that everyone coming from MySQL to PSQL takes for granted is auto_inc on id columns. As a result, I've added the following mapping to each domain object (as per various sites and guides instructions), which should manage all the sequencing for the id column:

static mapping = {
        id generator: 'sequence'
}

However doing a \d+ account I see the following line:

public.product
----------------------+-----------------------------+-----------+----------+-
 id                   | bigint                      | not null  | plain    |

... which is somewhat different to what I'd expect (I'd expect it to be a serial or bigserial). So, anyway, I migrate the data, and then start the application, and then test this by adding a product via the /product/create action. No dice, I can see a hibernate exception popping up, complaining about unique keys (the insert statement tried to use an id of 2. I refresh the page a few times (making more failures, but increasing the nextval nonetheless). Eventually it gets to 142 (there are currently 141 products in the table), and then it succeeds. So obviously I've got to update the nextval on each of the id sequences used by the tables. Sadly, I can't really tell wtf Grails is using as the sequence as it's not referenced by the table, nor are there any new tables in the DB.

I've read the tips at (How to reset postgres' primary key sequence when it falls out of sync?) but that's not really suitable here since there's no serial on the primary key.

I could possibly use the min_lo value (if thesequence method even considers that lower-bound for non-hilo strategies), but ideally I'd like something I can just run some SQL to fix post-migration.

Cheers.

EDIT: PostGreSQL 9.1, Grails 2.0.3 (yes, this'll be updated post-migration).

Upvotes: 0

Views: 464

Answers (1)

James Kleeh
James Kleeh

Reputation: 12228

See this documentation. You'll have to update the sequence before you start the app.

http://www.postgresql.org/docs/9.1/static/functions-sequence.html

currval(regclass)                   bigint  Return value most recently obtained with nextval for specified sequence
lastval()                           bigint  Return value most recently obtained with nextval for any sequence
nextval(regclass)                   bigint  Advance sequence and return new value
setval(regclass, bigint)            bigint  Set sequence's current value
setval(regclass, bigint, boolean)   bigint  Set sequence's current value and is_called flag

Upvotes: 1

Related Questions