PostgreSQL sequence counters do not increment correctly with rt-importer

Peter_van_Zetten · November 26, 2013, 4:25pm

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):

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.

Alex_Vandiver · November 26, 2013, 8:01pm

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

github.com

bestpractical/rt-extension-assets-import-csv/blob/master/lib/RT/Extension/Assets/Import/CSV.pm#L230


      
                  } else {
                      RT->Logger->error("Failed to create asset for row $i: $msg");
                  }
              }
          }
          
          unless ($unique) {
              # Update Asset sequence; mysql and SQLite do this implicitly
              my $dbtype = RT->Config->Get('DatabaseType');
              my $dbh = RT->DatabaseHandle->dbh;
              if ( $dbtype eq "Pg" ) {
                  $dbh->do("SELECT setval('assets_id_seq', (SELECT MAX(id) FROM Assets))");
              } elsif ( $dbtype eq "Oracle" ) {
                  my ($max) = $dbh->selectrow_array("SELECT MAX(id) FROM Assets");
                  my ($cur) = $dbh->selectrow_array("SELECT Assets_seq.nextval FROM dual");
                  if ($max > $cur) {
                      $dbh->do("ALTER SEQUENCE Assets_seq INCREMENT BY ". ($max - $cur));
                      # The next command _must_ be a select, and not a ->do,
                      # or Oracle doesn't actually fetch from the sequence.
                      $dbh->selectrow_array("SELECT Assets_seq.nextval FROM dual");
                      $dbh->do("ALTER SEQUENCE Assets_seq INCREMENT BY 1");

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