Ошибка субд relation does not exist

From what I’ve read, this error means that you’re not referencing the table name correctly. One common reason is that the table is defined with a mixed-case spelling, and you’re trying to query it with all lower-case.

In other words, the following fails:

CREATE TABLE "SF_Bands" ( ... );

SELECT * FROM sf_bands;  -- ERROR!

Use double-quotes to delimit identifiers so you can use the specific mixed-case spelling as the table is defined.

SELECT * FROM "SF_Bands";

Re your comment, you can add a schema to the «search_path» so that when you reference a table name without qualifying its schema, the query will match that table name by checked each schema in order. Just like PATH in the shell or include_path in PHP, etc. You can check your current schema search path:

SHOW search_path
  "$user",public

You can change your schema search path:

SET search_path TO showfinder,public;

See also http://www.postgresql.org/docs/8.3/static/ddl-schemas.html

Источник

What you had originally was a correct syntax — for tables, not for schemas. As you did not have a table (dubbed ‘relation’ in the error message), it threw the not-found error.

I see you’ve already noticed this — I believe there is no better way of learning than to fix our own mistakes ;)

But there is something more. What you are doing above is too much on one hand, and not enough on the other.

Running the script, you

  1. create a schema
  2. create a role
  3. grant SELECT on all tables in the schema created in (1.) to this new role_
  4. and, finally, grant all privileges (CREATE and USAGE) on the new schema to the new role

The problem lies within point (3.) You granted privileges on tables in replays — but there are no tables in there! There might be some in the future, but at this point the schema is completely empty. This way, the GRANT in (3.) does nothing — this way you are doing too much.

But what about the future tables?

There is a command for covering them: ALTER DEFAULT PRIVILEGES. It applies not only to tables, but:

Currently [as of 9.4], only the privileges for tables (including views and foreign tables), sequences, functions, and types (including domains) can be altered.

There is one important limitation, too:

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

This means that a table created by alice, who is neither you nor a role than you are a member of (can be checked, for example, by using du in psql), will not take the prescribed access rights. The optional FOR ROLE clause is used for specifying the ‘table creator’ role you are a member of. In many cases, this implies it is a good idea to create all database objects using the same role — like mydatabase_owner.

A small example to show this at work:

CREATE ROLE test_owner; -- cannot log in
CREATE SCHEMA replays AUTHORIZATION test_owner;
GRANT ALL ON SCHEMA replays TO test_owner;

SET ROLE TO test_owner; -- here we change the context, 
                        -- so that the next statement is issued as the owner role

ALTER DEFAULT PRIVILEGES IN SCHEMA replays GRANT SELECT ON TABLES TO alice;

CREATE TABLE replays.replayer (r_id serial PRIMARY KEY);

RESET ROLE; -- changing the context back to the original role

CREATE TABLE replays.replay_event (re_id serial PRIMARY KEY);

-- and now compare the two

dp replays.replayer
                                   Access privileges
 Schema  │   Name   │ Type  │       Access privileges       │ Column access privileges 
─────────┼──────────┼───────┼───────────────────────────────┼──────────────────────────
 replays │ replayer │ table │ alice=r/test_owner           ↵│ 
         │          │       │ test_owner=arwdDxt/test_owner │ 

dp replays.replay_event
                               Access privileges
 Schema  │     Name     │ Type  │ Access privileges │ Column access privileges 
─────────┼──────────────┼───────┼───────────────────┼──────────────────────────
 replays │ replay_event │ table │                   │

As you can see, alice has no explicit rights on the latter table. (In this case, she can still SELECT from the table, being a member of the public pseudorole, but I didn’t want to clutter the example by revoking the rights from public.)

Источник

Откуда ноги

Причина данной ошибки в том, что таблицы, либо их отдельные поля, описанные в конфигурации 1с, не соответствуют таблицам в базе данных SQL. Например в новой, обновленной конфигурации 1с существует регистр, а среди таблиц SQL его нет.

Что делать?

Нужно привести таблицы(поля) SQL в соответствие с описанием конфигурации.

Т.е. все таблицы(поля), описанные в конфигурации, должны присутствовать в SQL.

!!! Внимание Если у вас появляется ошибка «schemastorage does not exist» попробуйте сначала провести ТИИ (тестирование и исправление информационной базы), а именно только «реструктуризация БД«. В большинстве случаев она помогает, возможно поможет и при отсутствии других таблиц.

  Лечение

Необходимо, воспользовавшись утилитами, сравнить таблицы SQL с 1с. Описание ошибки сразу выводит на ту таблицу, которую нужно искать.

Далее нужно добавить(исправить) таблицы SQL с тем, чтобы они соответствовали конфигурации 1с.

В приложенном файле показаны примеры исправления.

Размышления

1.Поиск в интернете показал, что наиболее страдают этой ошибкой базы, размещенные на Postgre.

Здесь описано, что эта проблема существует и решена в версиях начиная с 8.3.

Сталкивался трижды с этой проблемой. Во всех случаях это был Postgre 8.4.

2.Есть мнение, что одним из поводов для появления ошибки, является динамическое обновление конфигурации.

3. Данная ошибка не возникает, если в новой конфигурации, относительно старой, не изменяли реквизиты, таблицы. Т.е. при изменении только программного кода, форм  конфигурации, такая ошибка не должна  проявляться, т.к. не изменяется структура таблиц SQL.

На дорожку

При исправлении ошибки, сами работы с таблицами SQL, хотя и не являются сложными, но все же требуют определенной подготовки.

Поэтому — пару рекомендаций, чтобы не пришлось решать описанную проблему:

— Не хочу обижать Postgre, но если база данных небольшая, может использовать MSSQL? Бесплатная версия Express позволяет обслуживать базу размером до 10Гб.

— По возможности избегайте делать динамическое обновление. Хотя фирма 1с периодически сообщает, что ей удалось «победить» эту проблему, но «Пуганая ворона…».

Ну и конечно, прежде чем начать работать с базой данных «по живому», сделайте ее бэкап.

Благодарности:

  • За статью спасибо aspirator23
  • Для анализа конфигурации использовалась обработка Структура хранения таблиц базы данных
Источник

What are you doing?

edit2: Remember folks, when you change your env variables, you have to restart your server/pm2 instance =) This fixed it, although I would expect a more helpful error message when host, port etc. are undefined.

Hey guys,

I am switching my node/express app from mysql to postgresql. Everything was pretty seamless except I had to swap some data types. When I try to run the following command I get an error.

edit: Looks like something else is up. Sequelize throws the same error for all other queries, including relation "users" does not exist. I know this was marked as support, but mysql was working perfectly before changing to postgres, so I imagine it should also work now.

const [ serviceUser, created ] = await ServiceUserAccountModel.findOrCreate({ 
          where: { service_user_id: '123456' },
        });

relation "serviceUserAccounts" does not exist. or with users relation "users" does not exist

const userModel = Sequelize.define('user', {
    // has many ServiceUserAccounts

    id: { 
      type: DataTypes.INTEGER, 
      primaryKey: true, 
      autoIncrement: true 
    },

    email: {
      type: DataTypes.STRING,
      allowNull: false
    },

    age: {
      type: DataTypes.SMALLINT,
      allowNull: false
    },

    gender: {
      type: DataTypes.STRING,
      allowNull: false
    },

    first_name: {
      type: DataTypes.STRING,
      allowNull: true
    },

    last_name: {
      type: DataTypes.STRING,
      allowNull: true
    }
       
  });

    const serviceUserAccountsModel = Sequelize.define('serviceUserAccount', {
      // belongs to User
      
      id: { 
        type: DataTypes.INTEGER, 
        primaryKey: true, 
        autoIncrement: true 
      },

      display_name: {
        type: DataTypes.STRING,
        allowNull: true
      },

      email_address: {
        type: DataTypes.STRING,
        allowNull: true
      },
  
      service_id: { 
        type: DataTypes.SMALLINT,
        allowNull: true,
      },

      service_user_id: { 
        type: DataTypes.STRING,
        allowNull: true,
      },
  
      refresh_token: {
        type: DataTypes.STRING,
        allowNull: true
      },
  
      access_token: {
        type: DataTypes.STRING,
        allowNull: true
      },
  
      token_type: {
        type: DataTypes.STRING,
        allowNull: true
      },
  
      expiration_date: {
        type: DataTypes.INTEGER,
        allowNull: true
      },
  
      storage_limit: {
        type: DataTypes.INTEGER,
        allowNull: true
      },

      storage_usage: {
        type: DataTypes.INTEGER,
        allowNull: true
      },

      trashed_storage_usage: {
        type: DataTypes.INTEGER,
        allowNull: true
      },
      
    });

// Relations

module.exports = function( database ){
  const User = database.models.user.user;
  const ServiceUserAccounts = database.models.user.serviceUserAccounts;
  
  User.hasMany(ServiceUserAccounts);
  ServiceUserAccounts.belongsTo(User);
};

What do you expect to happen?

As it was working perfectly before with mysql dialect, I expect it to also work with Postgresql.

What is actually happening?

relation "serviceUserAccounts" does not exist. I’m able to run the query just fine in pgAdmin, so it must be something with sequelize. What am I missing?

Here’s the gist with the stacktrace
https://gist.github.com/Mk-Etlinger/569093387a0cb97699acfcba3994f59d

Any ideas? I checked my permissions and it came back public, $user.

also looked here but no luck:
https://stackoverflow.com/questions/28844617/sequelize-with-postgres-database-not-working-after-migration-from-mysql

https://stackoverflow.com/questions/946804/find-out-if-user-got-permission-to-select-update-a-table-function-in-pos

Dialect: postgres
Dialect version: XXX
Database version: 9.6.2
Sequelize version: ^4.38.1
Tested with latest release: Yes, 4.39.0

Note : Your issue may be ignored OR closed by maintainers if it’s not tested against latest version OR does not follow issue template.

Источник

Posted on Dec 24, 2021

When you’re running Sequelize code to fetch or manipulate data from a PostgreSQL database, you might encounter an error saying relation <table name> does not exist.

For example, suppose you have a database named User in your PostgreSQL database as shown below:

cakeDB=# dt

          List of relations
 Schema | Name | Type  |    Owner
--------+------+-------+-------------
 public | User | table | nsebhastian
(1 row)

In the above output from psql, the cakeDB database has one table named User that you need to retrieve the data using Sequelize findAll() method.

Next, you create a new connection to the database using Sequelize and create a model for the User table:

const { Sequelize } = require("sequelize");

const sequelize = new Sequelize("cakeDB", "nsebhastian", "", {
  host: "localhost",
  dialect: "postgres",
});

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
});

After that, you write the code to query the User table as follows:

const users = await User.findAll();
console.log(users);

Although the code above is valid, Node will throw an error as follows:

Error
    at Query.run
    ...
  name: 'SequelizeDatabaseError',
  parent: error: relation "Users" does not exist

In PostgreSQL, a relation does not exist error happens when you reference a table name that can’t be found in the database you currently connect to.

In the case above, the error happens because Sequelize is trying to find Users table with an s, while the existing table is named User without an s.

But why does Sequelize refer to Users while we clearly define User in our model above? You can see it in the code below:

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
});

This is because Sequelize automatically pluralizes the model name User as Users to find the table name in your database (reference here)

To prevent Sequelize from pluralizing the table name for the model, you can add the freezeTableName option and set it to true to the model as shown below:

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
},
{
  freezeTableName: true,
});

The freezeTableName option will cause Sequelize to infer the table name as equal to the model name without any modification.

Alternatively, you can also add the tableName option to tell Sequelize directly the table name for the model:

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
},
{
  tableName: "User",
});

Once you add one of the two options above, this error should be resolved.

Please note that the model and table names in Sequelize and PostgreSQL are also case-sensitive, so if you’re table name is User, you will trigger the error when you refer to it as user from Sequelize:

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
},
{
  tableName: "user", // relation "user" does not exist
});

The relation does not exist error in Sequelize always happens when you refer to a PostgreSQL database table that doesn’t exist.

When you encounter this error, the first thing to check is to make sure that the Sequelize code points to the right table name.

This error can also occur in your migration code because you might have migration files that create a relationship between two tables.

Always make sure that you’re referencing the right table, and that you’re using the right letter casing.

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка субд mvarchar
  • Ошибка субд memory allocation failure hresult 80004005
  • Ошибка субд login failed for user sa
  • Ошибка субд interface with hresult 0x00040eda
  • Ошибка субд interface 0c733a7c 2a1c 11ce ade5 00aa0044773d