Can't describe Postgres 12+ database: column r.consrc does not exists.

[rodrigo-web-developer]

Node version: 9.7.0
Sails version: 0.11.4
DB adapter & version: sails-postgresql@0.11.4
Postgres version: 12

---

Error: column r.consrc does not exists.

In lib/adapter.js of sails-postgresql module, the function describe() execute a query that breaks in postgres 12+ because the column consrc of pg_constraint no longer exists, so the "schema" property of connection object will never be populated.

I had read the latest version of sails-postgresql and the same query is applied (but the file is: helpers/describe.js).

What can I do to run postgres 12 with sails?

[sailsbot]

@RodrigoRodriguesX10 Thanks for posting! We'll take a look as soon as possible.

In the mean time, there are a few ways you can help speed things along:

• look for a workaround. (Even if it's just temporary, sharing your solution can save someone else a lot of time and effort.)
• tell us why this issue is important to you and your team. What are you trying to accomplish? (Submissions with a little bit of human context tend to be easier to understand and faster to resolve.)
• make sure you've provided clear instructions on how to reproduce the bug from a clean install.
• double-check that you've provided all of the requested version and dependency information. (Some of this info might seem irrelevant at first, like which database adapter you're using, but we ask that you include it anyway. Oftentimes an issue is caused by a confluence of unexpected factors, and it can save everybody a ton of time to know all the details up front.)
• read the code of conduct.
• if appropriate, ask your business to sponsor your issue. (Open source is our passion, and our core maintainers volunteer many of their nights and weekends working on Sails. But you only get so many nights and weekends in life, and stuff gets done a lot faster when you can work on it during normal daylight hours.)
• let us know if you are using a 3rd party plugin; whether that's a database adapter, a non-standard view engine, or any other dependency maintained by someone other than our core team. (Besides the name of the 3rd party package, it helps to include the exact version you're using. If you're unsure, check out this list of all the core packages we maintain.)

---

Please remember: never post in a public forum if you believe you've found a genuine security vulnerability. Instead, disclose it responsibly.

For help with questions about Sails, click here.

[rodrigo-web-developer]

The columns pg_constraint.consrc and pg_attrdef.adsrc have become deprecated on Postgres 12, it's no longer used. I can just ignore them to run the application.

I solved by overwriting adapter.js file, adding a attribute "isVersion12OrNewer" to config/connections, this parameter will be used to ignore the deprecated fields.

connections.js file:

module.exports.connections = {
	postgres: {
		adapter: 'sails-postgresql',
		host: 'localhost',
		user: 'user',
		password: 'password',
		database: 'my_database',
		isVersion12OrNewer: true // here the new parameter
	}
};

Update the adapter.js file ("describe" function in line 154) by adding 2 lines of code:

describe: function (connectionName, table, cb) {
      spawnConnection(connectionName, function __DESCRIBE__(client, cb) {

        var connectionObject = connections[connectionName];
        var collection = connectionObject.collections[table];
        var tableName = table;
        var schemaName = getSchema(connectionName, table);
        /*Added 2 lines*/
        var is12OrNewer = connectionObject.config.isVersion12OrNewer; // look for parameter
        var compatible = function (s) { return is12OrNewer ? "" : s; }; // ignore the field if v12+

        // Build query to get a bunch of info from the information_schema
        // It's not super important to understand it only that it returns the following fields:
        // [Table, #, Column, Type, Null, Constraint, C, consrc, F Key, Default]
        
        /* Ignore columns consrc and Default if the postgres version is 12+ */
        var query = "SELECT x.nspname || '.' || x.relname as \"Table\", x.attnum as \"#\", x.attname as \"Column\", x.\"Type\"," +
          " case x.attnotnull when true then 'NOT NULL' else '' end as \"NULL\", r.conname as \"Constraint\", r.contype as \"C\", " +
          compatible("r.consrc,") + " fn.nspname || '.' || f.relname as \"F Key\"" + compatible(", d.adsrc as \"Default\"") + "FROM(" +
          "SELECT c.oid, a.attrelid, a.attnum, n.nspname, c.relname, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as \"Type\", " +
          "a.attnotnull FROM pg_catalog.pg_attribute a, pg_namespace n, pg_class c WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = c.oid " +
          "and c.relkind not in ('S','v') and c.relnamespace = n.oid and n.nspname not in ('pg_catalog','pg_toast','information_schema')) x " +
          compatible("left join pg_attrdef d on d.adrelid = x.attrelid and d.adnum = x.attnum ") +
          "left join pg_constraint r on r.conrelid = x.oid and r.conkey[1] = x.attnum " +
          "left join pg_class f on r.confrelid = f.oid " +
          "left join pg_namespace fn on f.relnamespace = fn.oid " +
          "where x.relname = '" + tableName + "' and x.nspname = '" + schemaName + "' order by 1,2;";

... the rest of the function ...

Works for me, but I had to overwrite the module's file.

[steinathan]

@RodrigoRodriguesX10 great, maybe you can send a pull request. 👍👍👍

[whichking]

Hey, @RodrigoRodriguesX10—

Thanks for looking into this! I did a quick test using a new app (latest versions of Sails, Sails PostgreSQL, and PostgreSQL), and I wasn't able to reproduce this error, so I don't think this is an issue when using the most recent version of Sails with PostgreSQL 12+. If you'd be willing to share a minimal repo reproducing this behavior in your version of Sails and Sails PostgreSQL, we'd be happy to look deeper into this issue!

[leooxx]

Hello, i'm using sails v0.12 and sails-postgresql v0.11.4 on an existing project.
This is the last version available for sails 0.12

The solution from @RodrigoRodriguesX10 is working. Is it possible to push this update... v0.11.5 ?

Else, how i can override adapter.js properly in my Sails application ?

Thank you all ;)

[eashaw]

Hi @leooxx, we don't have any plans on updating old versions of sails-postgresql. @RodrigoRodriguesX10 what version did you end up using in production?

[leooxx]

Ok, thanks for the reply. Using postgresql 11 will be my best solution...

[rodrigo-web-developer]

I overwrote the code in adapter.js and using Postgres 12 in production, true be told, there are many publishes for different clients (some using postgres 12 and some using postgres 11), I'm using:

Sails version: 0.11.4
sails-postgresql@0.11.4 (with adapter.js modified)

just adding "isVersion12OrNewer": true solved my problems, but is not a good idea to use modified module's file. We trying to upgrade to Sails v1 as soon as possible.

[eashaw]

@RodrigoRodriguesX10 Glad you're looking into upgrading to Sails v1. We don't recommend changing your node_modules files directly since they're easily overwritten.

[leooxx]

Hi everydone ! Postgresql 11 final release is November 9, 2023 !

To use postgresql 12 and the next versions, you can find a fork of module with the fix here :

https://www.npmjs.com/package/sails-postgresql-super