Failed to migrate to postgres database: nerror: duplicate key value violates unique constraint \“pg_namespace_nspname_index\”

Question

I have been trying to set up demux but I keep on getting this error,

{ error: duplicate key value violates unique constraint "pg_namespace_nspname_index"
    at Connection.parseE (/home/nodeos2/demux/node_modules/pg/lib/connection.js:557:11)
    at Connection.parseMessage (/home/nodeos2/demux/node_modules/pg/lib/connection.js:379:19)
    at Socket.<anonymous> (/home/nodeos2/demux/node_modules/pg/lib/connection.js:119:22)
    at Socket.emit (events.js:198:13)
    at addChunk (_stream_readable.js:288:12)
    at readableAddChunk (_stream_readable.js:269:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
  name: 'error',
  length: 236,
  severity: 'ERROR',
  code: '23505',
  detail: 'Key (nspname)=(cyanaudit) already exists.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'pg_catalog',
  table: 'pg_namespace',
  column: undefined,
  dataType: undefined,
  constraint: 'pg_namespace_nspname_index',
  file: 'nbtinsert.c',
  line: '534',
  routine: '_bt_check_unique' }



 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>X
{"name":"demux","hostname":"dev-eosio-1","pid":18069,"level":50,"err":{"message":"The proper initialization has not occurred. Failed to migrate the postgres database.","name":"NotInitializedError","stack":"NotInitializedError: The proper initialization has not occurred. Failed to migrate the postgres database.\n    at MassiveActionHandler.<anonymous> (/home/nodeos2/demux/node_modules/demux-postgres/dist/MassiveActionHandler.js:103:22)\nerror: duplicate key value violates unique constraint \"pg_namespace_nspname_index\"\n    at Connection.parseE (/home/nodeos2/demux/node_modules/pg/lib/connection.js:557:11)\n    at Connection.parseMessage (/home/nodeos2/demux/node_modules/pg/lib/connection.js:379:19)\n    at Socket.<anonymous> (/home/nodeos2/demux/node_modules/pg/lib/connection.js:119:22)\n    at Socket.emit (events.js:198:13)\n    at addChunk (_stream_readable.js:288:12)\n    at readableAddChunk (_stream_readable.js:269:11)\n    at Socket.Readable.push (_stream_readable.js:224:10)\n    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)"},"msg":"The proper initialization has not occurred. Failed to migrate the postgres database.","time":"2019-09-06T04:22:14.504Z","v":0}
{"name":"demux","hostname":"dev-eosio-1","pid":18069,"level":30,"msg":"Indexing unexpectedly paused due to an error.","time":"2019-09-06T04:22:14.505Z","v":0}

I have tried a lot of things.

For example:

1. I tried running it again: program encounters a deadlock. Solved by deleting the existing database and creating it again.
2. Tried deleting the database, schema and creating the database again. Does not work! Get the same issue again.
3. Tried deleting the cyanaudit file, does not work, cyanaudit file does not get generated again.

The problem is that demux is connecting to postgres database, as the tables in the database are being created, but it fails to migrate the database.

Not sure why this is happening.

This is my file for running demux:

const { BaseActionWatcher } = require("demux");
const { MassiveActionHandler } = require("demux-postgres");
const { NodeosActionReader } = require("demux-eos");
const massive = require("demux-postgres/node_modules/massive");

const handlerVersion = require("./handlerVersions/v1");
const migrationSequences = require("./migrationSequences");

const dbConfig = {
  host: 'localhost',
  port: 5432,
  database: 'eos',
  schema: 'public',
  user: 'postgres',
  password: '',
};

massive(dbConfig).then((db) => {
  const actionReader = new NodeosActionReader({
     startAtBlock: 77770000, 
     onlyIrreversible: false, 
     nodeosEndpoint: "https://api.eosnewyork.io"
  });
  const actionHandler = new MassiveActionHandler(
    [handlerVersion],
    db,
    dbConfig.schema,
    migrationSequences
  );
  const actionWatcher = new BaseActionWatcher(actionReader, actionHandler, 500);
  actionWatcher.watch();
})

This is my file for the migrationSequences

const { Migration } = require("demux-postgres");

const createTransferTable = new Migration(
     "createTransferTable", // name
     "public", // schema
     "create_transfer_table.sql", // SQL file
);

// MigrationSequence[]
// See: https://github.com/EOSIO/demux-js-postgres/blob/develop/src/interfaces.ts
module.exports = [{
   migrations: [createTransferTable],
  sequenceName: "init"
}];

This is my file for the sql

CREATE TABLE ${schema~}.transfer (
     id serial PRIMARY KEY,
     txid text,
     act smallint,
     from_ text,
     to_ text,
     amount double precision,
     symbol text,
     memo text
);

The database is creating the following tables

                 List of relations
 Schema |        Name         |   Type   |  Owner   
--------+---------------------+----------+----------
 public | _block_number_txid  | table    | postgres
 public | _index_state        | table    | postgres
 public | _index_state_id_seq | sequence | postgres
 public | _migration          | table    | postgres
 public | _migration_id_seq   | sequence | postgres

Any insights?

Answer 1

Other than the error of the duplicate key I would get a deadlock detected error, if I ever ran the demux after previously running it, or I would get current transaction is aborted, commands ignored until end of transaction block.

I solved this issue, by commenting out the check functions in throwIfNotSetup() function in MigrationRunner.js (line 150 as of September 10, 2019) in my YourDemuxExample/node_modules/demux-postgres/dist/. and letting it go straight to letting set up be true. After I did this the demux ran beautifully. This might be a bug in demux. I will update this answer on further investigation.