I have a system where users can make predictions on events. There are about two events a week. When the event ends the system scores all the predictions and updates this in the database. Here is an approximate representation of my current DB strucutre: Users table | ID | username | score | |----|-----------|--------| | 1 | alice | 12 | | 2 | bob | 22 | Games table | ID | gameTitle | gameTime | questions | answers | |----|-----------------|----------------------|------------|----------| | 1 | Football match | 2019-08-11 14:00:00 | {json} | {json} | | 2 | Hockey game | 2019-07-11 15:00:00 | {json} | {json} | Predictions table: | ID | gameID (FK) | userID (FK) | prediction | score | rank | |----|--------------|--------------|-------------|--------|-------| | 1 | 1 | 1 | {json} | 6 | 1 | | 2 | 1 | 2 | {json} | 4 | 2 | | 3 | 2 | 1 | {json} | 2 | 2 | So initially 'score' and 'rank' in predictions are NULL, and then after the event ends I run a script that goes through all entries for that game and scores it for the user and generates a rank. With this structure I can do things like: - List the highest scoring users of all time (SELECT from USERS, score descending) - List the rankings for each event (SELECT rank from PREDICTIONS where gameID = X, descending) However, what I now want to do is be able to get time-constrained rankings. So I want to be able to see which users scored highest for all events in the current month, or for the month of February etc. I know I could do this manually by going through the Games table and getting all gameIDs that are in the given month, and then going through the Predictions table for all predictions with these IDs, and adding up the score for each user and then sending this. However, that seems crazily inefficient and resource-intensive. Especially considering that this will be an API call I'm therefore wondering how I can accommodate this in my current database - if it's possible and/or advised, or if I should use some other technology. - One idea would be have to generate a new table for each month, and update and pull from this when relevant. - Another would be to have a 'monthly score' column in the User table, that resets each month. However, this wouldn't have historical data, which would be nice (though I guess could always be generated) I feel like there might be some database paradigm trick that I'm missing, so it'd be great to hear a proposed solution for this.

export default class Folder extends Model, InferCreationAttributes> {
  
  @Attribute(DataTypes.INTEGER)
  @PrimaryKey
  @AutoIncrement
  declare id: CreationOptional;

  @Attribute(DataTypes.STRING)
  @NotNull
  declare title: string;

  @Attribute(DataTypes.INTEGER)
  declare parentId: number;

  declare parent?: NonAttribute;
  declare children?: NonAttribute;

  @HasMany(() => Folder, {
    // as: 'children',
    foreignKey: 'parentId',
    inverse: {
      as: 'parent'
    },
  })

For the HasMany association, if I don't include as: 'children' property, I get the following errors from sequelize : - SequelizeAssociationError: Defining HasMany association "parent" from _Folder to _Folder failed - Caused by: SequelizeAssociationError: Both options "as" and "inverse.as" must be defined for hasMany self-associations, and their value must be different. However, if I include the as: 'children' property, I get the following errors from typescript & sequelize : - Object literal may only specify known properties, and 'as' does not exist in type 'Omit, "as">'.ts(2353) - Error: The "as" option is not allowed when using association decorators. The name of the decorated field is used as the association name. How do I resolve this & get the self-association to work ?

I created a project on Node js(Express js) with sequelize ORM and earlier, I used sequelize-cli to create and migrate database and tables respectively. this is my package.json: { "name": "library-management", "version": "0.0.0", "private": true, "scripts": { "start": "nodemon app.js", "install-and-migrate": "npm install && sequelize-cli db:create && sequelize-cli db:migrate" }, "dependencies": { "bcrypt": "^5.1.1", "dotenv": "^16.3.1", "express": "^4.18.2", "jsonwebtoken": "^9.0.2", "nodemon": "^3.0.1", "sequelize": "^6.32.1", "sequelize-cli": "^6.6.1" }, "devDependencies": { "mysql2": "^3.6.0" } } and this is my config.js file after changing it from config.json: const dotenv = require('dotenv'); dotenv.config(); const config = { dialect: process.env.DB_DIALECT || "mysql", host: process.env.DB_HOST || "127.0.0.1", port: process.env.DB_PORT || 3306, username: process.env.DB_USERNAME || "root", password: process.env.DB_PASSWORD, }; // console.log(config); module.exports = { url: process.env.DB_CONNECTION_URL, development: { ...config, database: process.env.DB_DEVELOPMENT_NAME }, test: { ...config, database: process.env.DB_TEST_NAME }, production: { ...config, database: process.env.DB_PROD_NAME }, }; and .sequelizerc: const path = require('path'); module.exports = { 'config': path.resolve('config', 'config.js'), 'models-path': path.resolve('models'), // 'seeders-path': path.resolve('seeders'), 'migrations-path': path.resolve('migrations') }; and my app.js: require('dotenv').config(); const express = require('express'); const app = express() const userRoutes = require("./routes/userRoutes") const adminRoutes = require("./routes/adminRoutes") const bookRoutes = require("./routes/bookRoutes") const authorRoutes = require("./routes/authorRoutes") app.use(express.json()) app.use('/users',userRoutes) app.use('/book',bookRoutes) app.use('/author',authorRoutes) app.use('/admin',adminRoutes) app.listen(3000); When I started the deployment operation, it showed me that: Nov 23 10:46:38 AM Sequelize CLI [Node: 14.17.0, CLI: 6.6.2, ORM: 6.35.1] Nov 23 10:46:38 AM Nov 23 10:46:38 AM Loaded configuration file "config/config.js". Nov 23 10:46:38 AM Using environment "development". Nov 23 10:46:41 AM Database bzkjl4pacusbj3w7ymkv created. Nov 23 10:46:41 AM Nov 23 10:46:41 AM Sequelize CLI [Node: 14.17.0, CLI: 6.6.2, ORM: 6.35.1] Nov 23 10:46:41 AM Nov 23 10:46:41 AM Loaded configuration file "config/config.js". Nov 23 10:46:41 AM Using environment "development". Nov 23 10:46:44 AM No migrations were executed, database schema was already up to date. But when I started inserting the endpoints using PostMan, it's telling me that: Nov 23 12:43:37 PM original: Error: No database selected Nov 23 12:43:37 PM at Packet.asError (/opt/render/project/src/Server/node_modules/mysql2/lib/packets/packet.js:728:17) Nov 23 12:43:37 PM at Query.execute (/opt/render/project/src/Server/node_modules/mysql2/lib/commands/command.js:29:26) Nov 23 12:43:37 PM at Connection.handlePacket (/opt/render/project/src/Server/node_modules/mysql2/lib/connection.js:478:34) Nov 23 12:43:37 PM at PacketParser.onPacket (/opt/render/project/src/Server/node_modules/mysql2/lib/connection.js:97:12) Nov 23 12:43:37 PM at PacketParser.executeStart (/opt/render/project/src/Server/node_modules/mysql2/lib/packet_parser.js:75:16) Nov 23 12:43:37 PM at Socket. (/opt/render/project/src/Server/node_modules/mysql2/lib/connection.js:104:25) Nov 23 12:43:37 PM at Socket.emit (events.js:376:20) Nov 23 12:43:37 PM at addChunk (internal/streams/readable.js:309:12) Nov 23 12:43:37 PM at readableAddChunk (internal/streams/readable.js:284:9) Nov 23 12:43:37 PM at Socket.Readable.push (internal/streams/readable.js:223:10) { Nov 23 12:43:37 PM code: 'ER_NO_DB_ERROR', Nov 23 12:43:37 PM errno: 1046, Nov 23 12:43:37 PM sqlState: '3D000', Nov 23 12:43:37 PM sqlMessage: 'No database selected', Nov 23 12:43:37 PM sql: 'SELECT id, first_name, last_name, role, username, email, password, age, createdAt, updatedAt FROM Users AS User;', Nov 23 12:43:37 PM parameters: undefined Nov 23 12:43:37 PM }, Nov 23 12:43:37 PM sql: 'SELECT id, first_name, last_name, role, username, email, password, age, createdAt, updatedAt FROM Users AS User;', Nov 23 12:43:37 PM parameters: {} Nov 23 12:43:37 PM } I have shared my concerns in their community room but I still cannot figure out whether is this happening from my side or from theirs. because its still working in the localhost. I am using cleaver-cloud to create a mysql add-on so that I can create a live database.

Hello everyone this is my first time here but I'm a quite desperate with this situation, I hope you can help me. I have a nodejs server with express and sequelize. When I was in development enviroment I didn't have any problem then in production enviroment sometimes the entire app throw this error in everyrequest ConnectionError [SequelizeConnectionError]: connect ETIMEDOUT for a few minutes then the server starts to work normally. In the server I use pm2 cluster mode(8 instances) to handle all the requests. Is the first that I manage a server and a DB and I read some articles, I've tried changing mysql enviroment variables and the sequelize pool options but the problem persists. I'm not sure if the problem is in the variables or the server specs or pm2 config. I hope someone can give a hint of the problem. ## mysql variables ##

skip_name_resolve = 1

join_buffer_size = 50M

tmp_table_size = 2G

max_heap_table_size = 2G

thread_cache_size = 40

max_connections=1600

key_buffer_size=195k

innodb_buffer_pool_size = 4G

innodb_log_file_size = 512M

key_buffer_size = 1M

innodb_buffer_pool_instances = 4

## sequelize configuration ##

{
    host: config.bd_host,
    dialect: 'mysql',
    logging: false,
    pool: {
      max: 100,
      min: 0,
      acquire: 1000000,
      idle: 100000,
      evict: 2000,
    },
    dialectOptions: {
      decimalNumbers: true,
    },
  }

## Server specifications ## - 64gb RAM - 16 cores proccesor - ubuntu 18.04.5 lts - mysql version 14.14 Distrib 5.7.40, for Linux (x86_64) using EditLine wrapper - express 4.17.1 - sequelize 6.19.1 - node version 14.17.2

I'm trying to seed my Heroku production database and got the following error message: > ERROR: insert or update on table "Posts" violate foreign key constraint "Posts_userId_fkey The post migration file:

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Posts', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      userId: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'Users'
        }
      },
      content: {
        type: Sequelize.TEXT,
        allowNull: false
      },
      title: {
        type: Sequelize.STRING(50),
        allowNull: false
      },
      photo: {
        type: Sequelize.TEXT,
        allowNull: false
      },
      categoryId: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'Categories'
        }
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.fn('now')
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.fn('now')
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Posts');
  }
};

user model file:

User.associate = function(models) {
    // associations can be defined here
    User.hasMany(models.Post, { foreignKey: 'userId' })
  };

my config file:

production: {
    use_env_variable: 'DATABASE_URL',
    dialect: 'postgres',
    seederStorage: 'sequelize',
    dialectOptions: {
      ssl: {
        require: true,
        rejectUnauthorized: false
      }
    }
  }

my post seeders:

return queryInterface.bulkInsert(
      "Posts",
      [
        {
          userId: 4,
          content: "Cream cheese halloumi camembert de normandie. Queso emmental melted cheese cream cheese cheese triangles the big cheese emmental blue castello. When the cheese comes out everybody's happy gouda queso fromage camembert de normandie stinking bishop rubber cheese rubber cheese. Edam cheese triangles pecorino babybel stilton. Parmesan hard cheese smelly cheese. Cheese triangles fondue macaroni cheese port-salut taleggio chalk and cheese brie cheesecake. Bavarian bergkase emmental taleggio dolcelatte fondue roquefort cheeseburger cheese slices. Fromage frais.Cheesecake cheese and wine fromage frais. Roquefort cheese triangles fromage frais stilton paneer mascarpone chalk and cheese dolcelatte. Dolcelatte who moved my cheese croque monsieur manchego taleggio cheese on toast hard cheese bocconcini. Cow everyone loves monterey jack cheesy grin smelly cheese cauliflower cheese bocconcini the big cheese. Hard cheese hard cheese.Cheese slices smelly cheese cheese on toast. Cheese strings chalk and cheese camembert de normandie cheese and biscuits red leicester cow brie cut the cheese. Cheese on toast melted cheese stilton pecorino brie st. agur blue cheese manchego cheese strings. Boursin who moved my cheese stilton paneer cheese triangles lancashire cow who moved my cheese. Who moved my cheese cauliflower cheese mascarpone say cheese.",
          title: 'First blog',
          photo: '',
          categoryId: 1
        },
        {
          userId: 4,
          content: "Cream cheese halloumi camembert de normandie. Queso emmental melted cheese cream cheese cheese triangles the big cheese emmental blue castello. When the cheese comes out everybody's happy gouda queso fromage camembert de normandie stinking bishop rubber cheese rubber cheese. Edam cheese triangles pecorino babybel stilton. Parmesan hard cheese smelly cheese. Cheese triangles fondue macaroni cheese port-salut taleggio chalk and cheese brie cheesecake. Bavarian bergkase emmental taleggio dolcelatte fondue roquefort cheeseburger cheese slices. Fromage frais.Cheesecake cheese and wine fromage frais. Roquefort cheese triangles fromage frais stilton paneer mascarpone chalk and cheese dolcelatte. Dolcelatte who moved my cheese croque monsieur manchego taleggio cheese on toast hard cheese bocconcini. Cow everyone loves monterey jack cheesy grin smelly cheese cauliflower cheese bocconcini the big cheese. Hard cheese hard cheese.Cheese slices smelly cheese cheese on toast. Cheese strings chalk and cheese camembert de normandie cheese and biscuits red leicester cow brie cut the cheese. Cheese on toast melted cheese stilton pecorino brie st. agur blue cheese manchego cheese strings. Boursin who moved my cheese stilton paneer cheese triangles lancashire cow who moved my cheese. Who moved my cheese cauliflower cheese mascarpone say cheese.",
          title: 'Cat Ipsum',
          photo: '',
          categoryId: 2
        },
        {
          userId: 4,
          content: "Cream cheese halloumi camembert de normandie. Queso emmental melted cheese cream cheese cheese triangles the big cheese emmental blue castello. When the cheese comes out everybody's happy gouda queso fromage camembert de normandie stinking bishop rubber cheese rubber cheese. Edam cheese triangles pecorino babybel stilton. Parmesan hard cheese smelly cheese. Cheese triangles fondue macaroni cheese port-salut taleggio chalk and cheese brie cheesecake. Bavarian bergkase emmental taleggio dolcelatte fondue roquefort cheeseburger cheese slices. Fromage frais.Cheesecake cheese and wine fromage frais. Roquefort cheese triangles fromage frais stilton paneer mascarpone chalk and cheese dolcelatte. Dolcelatte who moved my cheese croque monsieur manchego taleggio cheese on toast hard cheese bocconcini. Cow everyone loves monterey jack cheesy grin smelly cheese cauliflower cheese bocconcini the big cheese. Hard cheese hard cheese.Cheese slices smelly cheese cheese on toast. Cheese strings chalk and cheese camembert de normandie cheese and biscuits red leicester cow brie cut the cheese. Cheese on toast melted cheese stilton pecorino brie st. agur blue cheese manchego cheese strings. Boursin who moved my cheese stilton paneer cheese triangles lancashire cow who moved my cheese. Who moved my cheese cauliflower cheese mascarpone say cheese.",
          title: 'Cheesecake Party',
          photo: 'https://media.istockphoto.com/photos/cheesecake-slice-with-strawberries-picture-id1205169550?k=20&m=1205169550&s=612x612&w=0&h=QqJDIpCEpGEXBFU2c-aoZKEgtU5tfFGxKxrBu1bHYww= ',
          categoryId: 2
        },
        {
          userId: 5,
          content: "Cream cheese halloumi camembert de normandie. Queso emmental melted cheese cream cheese cheese triangles the big cheese emmental blue castello. When the cheese comes out everybody's happy gouda queso fromage camembert de normandie stinking bishop rubber cheese rubber cheese. Edam cheese triangles pecorino babybel stilton. Parmesan hard cheese smelly cheese. Cheese triangles fondue macaroni cheese port-salut taleggio chalk and cheese brie cheesecake. Bavarian bergkase emmental taleggio dolcelatte fondue roquefort cheeseburger cheese slices. Fromage frais.Cheesecake cheese and wine fromage frais. Roquefort cheese triangles fromage frais stilton paneer mascarpone chalk and cheese dolcelatte. Dolcelatte who moved my cheese croque monsieur manchego taleggio cheese on toast hard cheese bocconcini. Cow everyone loves monterey jack cheesy grin smelly cheese cauliflower cheese bocconcini the big cheese. Hard cheese hard cheese.Cheese slices smelly cheese cheese on toast. Cheese strings chalk and cheese camembert de normandie cheese and biscuits red leicester cow brie cut the cheese. Cheese on toast melted cheese stilton pecorino brie st. agur blue cheese manchego cheese strings. Boursin who moved my cheese stilton paneer cheese triangles lancashire cow who moved my cheese. Who moved my cheese cauliflower cheese mascarpone say cheese.",
          title: 'Tell Me A Joke',
          photo: '',
          categoryId: 1
        },
      ],
      {}
    );
  },

Why is it producing that message?

I have 2 tables, agents and candidates. The candidates table has a foreign key "agent_id". I want to get all agents and aggregate the candidate counts on each agent object; the different candidate counts have different conditions to meet. I am using nodejs, sequelize v4 and postgresql The SQL query below throws an error that I do not understand. **QUERY**

SELECT "Agent"."id", "Agent"."first_name", "Agent"."middle_name", "Agent"."last_name", "Agent"."phone", "Agent"."active", "Agent"."team_id", COUNT("candidates"."id") AS "total_count", COUNT("candidates"."active" = 'false') AS "pending_count", COUNT("candidates"."active" = 'true') AS "verified_count", "candidates"."id" AS "candidates.id", "candidates"."active" AS "candidates.active", "candidates"."client_approved" AS "candidates.client_approved", "candidates"."rejected" AS "candidates.rejected", "candidates"."date_disbursed" AS "candidates.date_disbursed", "candidates"."date_cashedout" AS "candidates.date_cashedout", "candidates"."agent_id" AS "candidates.agent_id" FROM "agents" AS "Agent" LEFT OUTER JOIN "candidates" AS "candidates" ON "Agent"."id" = "candidates"."agent_id" AND ("candidates"."deleted_at" > '2020-09-17 11:53:28.012 +00:00' OR "candidates"."deleted_at" IS NULL) WHERE ("Agent"."deleted_at" > '2020-09-17 11:53:28.012 +00:00' OR "Agent"."deleted_at" IS NULL) GROUP BY "Agent"."id" LIMIT '50';

**ERROR** column \"candidates.id\" must appear in the GROUP BY clause or be used in an aggregate function **ERROR DETAIL**

"name":"SequelizeDatabaseError","parent":{"name":"error","length":177,"severity":"ERROR","code":"42803","position":"303","file":"parse_agg.c","line":"1388","routine":"check_ungrouped_columns_walker","sql":"SELECT \"Agent\".\"id\", \"Agent\".\"first_name\", \"Agent\".\"middle_name\", \"Agent\".\"last_name\", \"Agent\".\"phone\", \"Agent\".\"active\", \"Agent\".\"team_id\", COUNT(\"candidates\".\"id\") AS \"total_count\", COUNT(\"candidates\".\"active\" = 'false') AS \"pending_count\", COUNT(\"candidates\".\"active\" = 'true') AS \"verified_count\", \"candidates\".\"id\" AS \"candidates.id\", \"candidates\".\"active\" AS \"candidates.active\", \"candidates\".\"client_approved\" AS \"candidates.client_approved\", \"candidates\".\"rejected\" AS \"candidates.rejected\", \"candidates\".\"date_disbursed\" AS \"candidates.date_disbursed\", \"candidates\".\"date_cashedout\" AS \"candidates.date_cashedout\", \"candidates\".\"agent_id\" AS \"candidates.agent_id\" FROM \"agents\" AS \"Agent\" LEFT OUTER JOIN \"candidates\" AS \"candidates\" ON \"Agent\".\"id\" = \"candidates\".\"agent_id\" AND (\"candidates\".\"deleted_at\" > '2020-09-17 11:53:28.012 +00:00' OR \"candidates\".\"deleted_at\" IS NULL) WHERE (\"Agent\".\"deleted_at\" > '2020-09-17 11:53:28.012 +00:00' OR \"Agent\".\"deleted_at\" IS NULL) GROUP BY \"Agent\".\"id\" LIMIT '50';"},"original":{"name":"error","length":177,"severity":"ERROR","code":"42803","position":"303","file":"parse_agg.c","line":"1388","routine":"check_ungrouped_columns_walker","sql":"SELECT \"Agent\".\"id\", \"Agent\".\"first_name\", \"Agent\".\"middle_name\", \"Agent\".\"last_name\", \"Agent\".\"phone\", \"Agent\".\"active\", \"Agent\".\"team_id\", COUNT(\"candidates\".\"id\") AS \"total_count\", COUNT(\"candidates\".\"active\" = 'false') AS \"pending_count\", COUNT(\"candidates\".\"active\" = 'true') AS \"verified_count\", \"candidates\".\"id\" AS \"candidates.id\", \"candidates\".\"active\" AS \"candidates.active\", \"candidates\".\"client_approved\" AS \"candidates.client_approved\", \"candidates\".\"rejected\" AS \"candidates.rejected\", \"candidates\".\"date_disbursed\" AS \"candidates.date_disbursed\", \"candidates\".\"date_cashedout\" AS \"candidates.date_cashedout\", \"candidates\".\"agent_id\" AS \"candidates.agent_id\" FROM \"agents\" AS \"Agent\" LEFT OUTER JOIN \"candidates\" AS \"candidates\" ON \"Agent\".\"id\" = \"candidates\".\"agent_id\" AND (\"candidates\".\"deleted_at\" > '2020-09-17 11:53:28.012 +00:00' OR \"candidates\".\"deleted_at\" IS NULL) WHERE (\"Agent\".\"deleted_at\" > '2020-09-17 11:53:28.012 +00:00' OR \"Agent\".\"deleted_at\" IS NULL) GROUP BY \"Agent\".\"id\" LIMIT '50';"},"sql":"SELECT \"Agent\".\"id\", \"Agent\".\"first_name\", \"Agent\".\"middle_name\", \"Agent\".\"last_name\", \"Agent\".\"phone\", \"Agent\".\"active\", \"Agent\".\"team_id\", COUNT(\"candidates\".\"id\") AS \"total_count\", COUNT(\"candidates\".\"active\" = 'false') AS \"pending_count\", COUNT(\"candidates\".\"active\" = 'true') AS \"verified_count\", \"candidates\".\"id\" AS \"candidates.id\", \"candidates\".\"active\" AS \"candidates.active\", \"candidates\".\"client_approved\" AS \"candidates.client_approved\", \"candidates\".\"rejected\" AS \"candidates.rejected\", \"candidates\".\"date_disbursed\" AS \"candidates.date_disbursed\", \"candidates\".\"date_cashedout\" AS \"candidates.date_cashedout\", \"candidates\".\"agent_id\" AS \"candidates.agent_id\" FROM \"agents\" AS \"Agent\" LEFT OUTER JOIN \"candidates\" AS \"candidates\" ON \"Agent\".\"id\" = \"candidates\".\"agent_id\" AND (\"candidates\".\"deleted_at\" > '2020-09-17 11:53:28.012 +00:00' OR \"candidates\".\"deleted_at\" IS NULL) WHERE (\"Agent\".\"deleted_at\" > '2020-09-17 11:53:28.012 +00:00' OR \"Agent\".\"deleted_at\" IS NULL) GROUP BY \"Agent\".\"id\" LIMIT '50';","error@context":{}}

If I add the "candidates.id" to the group by clause it doesn't throw an error but it also doesn’t return the result I want. Please I would like to know why this error is happening and how to get my intended result without add the unnecessary grouping.

This is related to https://dba.stackexchange.com/questions/248484/debugging-a-hanging-session-lock Every so often (~1-2 times a month) we have our database lock up because there is one query that doesnt finish, and that has requested locks that block all others. This is on a table that is core to our business, so it results in downtime. The query in question is one of a couple of candidates, all of them are just simple UPDATEs running on just 1 row. The best lead I have so far (I am open to other avenues) is from RDS performance insights I see that while all other queries are locked on "tuple", there is always 1 query that is locked on "ClientRead". From my research, this appears to be Postgres waiting on the client lib to send it the bound parameters or similar. So my theory is that there is one query that randomly sometimes ends up in this state, and this blocks all others after it. Is this a reasonable evaluation? If this is the case, how can I go into investigating and resolving it? We use sequelize 5.9.4 (node-js). We did upgrade it recently, ~1-2 months back, so I am wondering if there might be a bug in this version which might cause a error on the sequelize side when processing a query, is that possible? It could be a bug in node-postgres as well, this came up in my searching https://github.com/brianc/node-postgres/issues/1952 . I will try to update with a screenshot of RDS performance insights next time it happens, its retention was shorter than anticipated.

My understanding is that Postgres supports UUID data type out-of-the-box. I'm running Postgres on Amazon RDS with Engine version 10.6 and am scratching my head as to why the following commands are not behaving as expected:

CREATE TABLE IF NOT EXISTS "Tenants" 
(
    "id" UUID NOT NULL , 
    "domain" VARCHAR(255) NOT NULL UNIQUE, 
    "name" VARCHAR(255) NOT NULL, 
    "schema" VARCHAR(255) NOT NULL UNIQUE, 
    "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, 
    "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id")
);

The critical part here is that I'm creating "id" UUID NOT NULL. So far, so good. Or so I thought. Next, I'm trying to add an entry:

INSERT INTO "Tenants" ("id","domain","name","schema","createdAt","updatedAt") 
VALUES 
(
    '3b6b220b-2690-4279-8343-cbe6167f7596',
    'test1.stage-domain.io',
    'Test1','test1',
    '2019-02-25 14:29:33.475 +00:00',
    '2019-02-25 14:29:33.475 +00:00') 
RETURNING *;

and surprisingly the following error shows up: > SequelizeDatabaseError: invalid input syntax for integer: > "3b6b220b-2690-4279-8343-cbe6167f7596 Sure enough, if I go to PGAdmin, I'm seeing that the column has not been set to UUID, rather it has been set to an integer: The dropdown shown in this picture does not even contain a UUID type. The weirdest part is that I created a different Postgresql server on RDS last night and everything actually worked just fine in that instance. **My question:** How can I get my database to recognize the UUID type when creating the Tenants table?