New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Show indexes query on Postgres fails to return functional indexes #3911

Closed
basco-johnkevin opened this Issue Jun 10, 2015 · 16 comments

Comments

5 participants
@basco-johnkevin

basco-johnkevin commented Jun 10, 2015

Model:

module.exports = function(sequelize, DataTypes) {
  var Model = sequelize.define('Company', {
    name: {
      type: DataTypes.STRING,
      unique: true
    }
  }, {
    indexes: [
      { name: 'unique_name', unique: true, fields: [sequelize.fn('lower', sequelize.col('name'))] }
    ]
  });

  return Model;
};

It works when the database is empty. But when I stop the server and then run it again. It throws this error:

Possibly unhandled SequelizeDatabaseError: relation "unique_name" already exists
    at module.exports.Query.formatError (/Users/johnkevinmbasco/webapps/helpdesk/node_modules/sequelize/lib/dialects/postgres/query.js:415:16)
    at null.<anonymous> (/Users/johnkevinmbasco/webapps/helpdesk/node_modules/sequelize/lib/dialects/postgres/query.js:110:21)
    at emit (events.js:95:17)
    at Query.handleError (/Users/johnkevinmbasco/webapps/helpdesk/node_modules/pg/lib/query.js:99:8)
    at null.<anonymous> (/Users/johnkevinmbasco/webapps/helpdesk/node_modules/pg/lib/client.js:166:26)
    at emit (events.js:95:17)
    at Socket.<anonymous> (/Users/johnkevinmbasco/webapps/helpdesk/node_modules/pg/lib/connection.js:109:12)
    at Socket.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:764:14)
    at Socket.emit (events.js:92:17)

The contents of my /bin/www file is similar to this - https://github.com/sequelize/express-example/blob/master/bin/www

@mickhansen mickhansen added the bug label Jun 10, 2015

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Jun 10, 2015

It looks like it expects the table to be empty (like it would be with a force: true}) and not taking existing indexes into account, although i believe we had code for that @janmeier?

@janmeier

This comment has been minimized.

Member

janmeier commented Jun 10, 2015

Hmm, we should be checking if an index already exists before trying to create it. Which version are you on?

@basco-johnkevin

This comment has been minimized.

basco-johnkevin commented Jun 10, 2015

@mickhansen @jamonkko Oh, I forgot to tell you guys that I'm passing {force: false} in the sync() method. Anyway, I'm using version 2.0.4

@basco-johnkevin

This comment has been minimized.

basco-johnkevin commented Jun 10, 2015

Found a related issue - #2162 . It's already closed though.

@janmeier

This comment has been minimized.

Member

janmeier commented Jun 10, 2015

Whats the output of sequelize.QueryInterface.showIndex(Model.getTableName()). Seems like it does not find the existing index correctly.

Are you using schemas?

@basco-johnkevin

This comment has been minimized.

basco-johnkevin commented Jun 10, 2015

@janmeier There's a typo, QueryInterface is undefined. queryInterface exists though. Here's the output:

{ _bitField: 0,
  _fulfillmentHandler0: undefined,
  _rejectionHandler0: undefined,
  _progressHandler0: undefined,
  _promise0: undefined,
  _receiver0: undefined,
  _settledValue: undefined,
  _boundTo: undefined,
  '$sql': [] }

What do you mean by "Are you using schemas?". If you're referring to Migrations / Manual schema changes, nope. No migrations yet.

@janmeier

This comment has been minimized.

Member

janmeier commented Jun 10, 2015

Sorry, I meant, whats the output by the actual query (.then(function (indices ..)

schema as in postgres schemas. But that's a no then ;)

@basco-johnkevin

This comment has been minimized.

basco-johnkevin commented Jun 10, 2015

@janmeier

models.sequelize.queryInterface.showIndex(models.Company.getTableName()).then(function(result) {
  console.log(result);
});

Output:

[ { name: 'Companies_name_key',
    primary: false,
    unique: true,
    indkey: '2',
    definition: 'CREATE UNIQUE INDEX "Companies_name_key" ON "Companies" USING btree (name)',
    fields: [ [Object] ] },
  { name: 'Companies_pkey',
    primary: true,
    unique: true,
    indkey: '1',
    definition: 'CREATE UNIQUE INDEX "Companies_pkey" ON "Companies" USING btree (id)',
    fields: [ [Object] ] } ]
@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Jun 10, 2015

Hmm, unique_name is a bit generic, and index names for PG are global i believe - I believe we might only prefix auto generated names?

@janmeier

This comment has been minimized.

Member

janmeier commented Jun 10, 2015

We do indeed only prefix for auto generated. But since it works the first time I don't think there can be a collision..

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Jun 10, 2015

Hmm, right

@janmeier

This comment has been minimized.

Member

janmeier commented Jun 10, 2015

Just tested locally, and it does indeed seem like the query fails to identify the index .. weird..

@janmeier

This comment has been minimized.

Member

janmeier commented Jun 10, 2015

Seems to be related to using functions in indices - without UPPER it works fine.

@janmeier janmeier changed the title from Error when calling sequelize.sync() when Model definition has indexes to Show indexes query on Postgres fails to return functional indexes Jul 2, 2015

@sankethkatta

This comment has been minimized.

Contributor

sankethkatta commented Jul 2, 2015

I ran into this same problem, and explained a bit on what I found investigating in #4047.
Looks like Postgres does not associate functional indexes with any specific columns, but rather just the table as a whole.

The show indexes query in Sequelize uses pg_index.indkey. The Postgres documentation explains that the indkey array describes which table columns are part of the index. However it also states:

A zero in this array indicates that the corresponding index attribute is an expression over the table columns, rather than a simple column reference.

Therefore functional indexes with UPPER/LOWER get a zero in indkey. Sequelize's query filters out zeros when it joins on pg_attribute.attnum, which Postgres docs describes as:

The number of the column. Ordinary columns are numbered from 1 up.

Currently it seems that Sequelize depends on knowing which columns returned indexes are on (not sure about this). If it were possible for Sequelize to use just the name of the index, then the query could be simplified tremendously (and solve this bug!).

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Jul 2, 2015

Matching on the name first (if defined by user) and fields afterwards would probably be the way to go.

@sorin

This comment has been minimized.

Contributor

sorin commented Aug 14, 2015

I have the PR here #4328 but it's failing for some random reasons:

  1. [MARIADB] Transaction row locking supports for share:
    SequelizeDatabaseError: ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction

@janmeier janmeier closed this Aug 14, 2015

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment