[rt-users] PostgreSQL sequence counters do not increment correctly with rt-importer

[Alex Vandiver]

On Tue, 2013-11-26 at 17:25 +0100, Peter van Zetten wrote:
> I've been looking at migrating from MySQL to PostgreSQL with the
> rt-serializer/importer scripts. This is a precursor to merging two RT
> instances, but the initial migration of the main database is being
> done with the "--clone" flag to rt-serializer.

> The problem is that when rt-importer runs on the empty psql schema,
> none of the sequence values are being updated. Then any action using
> the new database fails because of duplicate key constraints.

Semi-known bug.  When we've imported into Pg in the past, we've set the
sequences by hand afterwards.  The importer should absolutely grow the
SETVAL() calls to update the sequences after import; patches accepted. 
It should look like a more general form of
https://github.com/bestpractical/rt-extension-assets-import-csv/blob/master/lib/RT/Extension/Assets/Import/CSV.pm#L230

> So it looks like somehow this sequence isn't being hit at all. Perhaps
> because the 'id' of each object is set in the export and is being
> inserted directly it doesn't trigger an increment. I'm still finding
> my way around postgres so I'm just guessing at the moment.

Correct; mysql automatically bumps its AUTO_INCREMENT if you insert
with an explicit id, but neither Oracle nor Pg adjust their sequences.
 - Alex