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

[Peter van Zetten]

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.

When the same steps are run (from the same serialised data dump) with a
MySQL database, the Auto_increment values are set as expected (as checked
by "SHOW TABLE STATUS LIKE 'Tickets';").

Short psql example showing the problem (I'd expect 'last_value' to be
something other than 1):

rt4=# \d tickets_id_seq
       Sequence "public.tickets_id_seq"
    Column     |  Type   |        Value
---------------+---------+---------------------
 sequence_name | name    | tickets_id_seq
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f

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.

However since the same serialised output produces valid results in MySQL I
guess the problem is somewhere in rt-importer or my PostgreSQL
configuration. I'll start digging into rt-importer shortly, but I thought
I'd throw this out and see if anyone else has experienced (and maybe even
fixed) this problem.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20131126/7b96b582/attachment.htm>