pg-info not returning certain table objects

[lee-40square]

Not sure if I'm missing something but it's not returning any of the following:

indexes: {}, 
triggers: {},
fkConstraints: {}

[reecebrend]

Hi Lee,

Are you sure you're referring to the pg-diff-sync package?
That returns an array of SQL statements to bridge the gap between two different schemas

The output you've referred to above with indexes, triggers etc is the product of the pg-info package

[lee-40square]

Sorry. Yes you are right. However, when I am in that repo it still brings me back to this list of issues. I don't see any way to raise an issue directly for that repo.

[reecebrend]

No worries, we use lernajs to maintain our repository as a single "monorepo" so it's easier to manage our internal dependancies. That's why the issues are all just under the "Tymly" repo instead of individual packages.

Are you still having the problem you mentioned when you opened up this issue @lee-40square?

[lee-40square]

Yes. Still having the same problem.

As an example.

CREATE TABLE public.aircraft (
	id serial NOT NULL,
	company_id int4 NOT NULL,
	aircraft_type_id int4 NOT NULL,
	call_sign varchar NOT NULL,
	archived bool NULL DEFAULT false,
	aircraft_details jsonb NOT NULL,
	created timestamptz NULL DEFAULT now(),
	ttsn_settings jsonb NULL,
	available bool NULL,
	CONSTRAINT aircraft_pkey PRIMARY KEY (id),
	CONSTRAINT fk_aircraft_aircraft_type FOREIGN KEY (aircraft_type_id) REFERENCES aircraft_type(id) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT fk_aircraft_company FOREIGN KEY (company_id) REFERENCES company(id) ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
	OIDS=FALSE
) ;
CREATE UNIQUE INDEX aircraft_id_key ON public.aircraft USING btree (id) ;

Whereas pg-info using info.schemas.public.aircraft returns the following

{ comment: null,
  pkColumnNames: [ 'id' ],
  columns:
   { id:
      { columnDefault: 'nextval(\'aircraft_id_seq\'::regclass)',
        isNullable: 'NO',
        dataType: 'integer',
        characterMaximumLength: null,
        numericScale: 0,
        comment: null,
        array: false },
     company_id:
      { columnDefault: null,
        isNullable: 'NO',
        dataType: 'integer',
        characterMaximumLength: null,
        numericScale: 0,
        comment: null,
        array: false },
     aircraft_type_id:
      { columnDefault: null,
        isNullable: 'NO',
        dataType: 'integer',
        characterMaximumLength: null,
        numericScale: 0,
        comment: null,
        array: false },
     call_sign:
      { columnDefault: null,
        isNullable: 'NO',
        dataType: 'character varying',
        characterMaximumLength: null,
        numericScale: null,
        comment: null,
        array: false },
     archived:
      { columnDefault: 'false',
        isNullable: 'YES',
        dataType: 'boolean',
        characterMaximumLength: null,
        numericScale: null,
        comment: null,
        array: false },
     aircraft_details:
      { columnDefault: null,
        isNullable: 'NO',
        dataType: 'jsonb',
        characterMaximumLength: null,
        numericScale: null,
        comment: 'all the details about this aircraft',
        array: false },
     created:
      { columnDefault: 'now()',
        isNullable: 'YES',
        dataType: 'timestamp with time zone',
        characterMaximumLength: null,
        numericScale: null,
        comment: null,
        array: false },
     ttsn_settings:
      { columnDefault: null,
        isNullable: 'YES',
        dataType: 'jsonb',
        characterMaximumLength: null,
        numericScale: null,
        comment: null,
        array: false },
     available:
      { columnDefault: null,
        isNullable: 'YES',
        dataType: 'boolean',
        characterMaximumLength: null,
        numericScale: null,
        comment: null,
        array: false } },
  indexes: {},
  triggers: {},
  functions:
   { pg_stat_statements_reset: { dataType: 'void' },
     pg_stat_statements: { dataType: 'record' } },
  fkConstraints: {} }

[timneedham]

Hi @lee-40square, thanks for this.

We'll need to do a quick investigation, but I'm thinking this issue might be down to the table being created in the public schema? All our tests assume things are in a schema, so perhaps there's an oversight we need to resolve there. We'll take a look as soon as we can.

• In the immediate-term, is it possible to move your table into a non-public schema?

Many thanks

Tim

[timneedham]

Yup, replicated!

[timneedham]

Yeah... moving to a planes schema brings back:

{
  "generated": "2018-05-11T11:47:59.954Z",
  "schemas": {
    "planes": {
      "schemaExistsInDatabase": true,
      "comment": null,
      "tables": {
        "aircraft_type": {
          "comment": null,
          "pkColumnNames": [
            "id"
          ],
          "columns": {
            "id": {
              "columnDefault": null,
              "isNullable": "NO",
              "dataType": "integer",
              "characterMaximumLength": null,
              "numericScale": 0,
              "comment": null,
              "array": false
            }
          },
          "indexes": {},
          "triggers": {},
          "functions": {},
          "fkConstraints": {}
        },
        "company": {
          "comment": null,
          "pkColumnNames": [
            "id"
          ],
          "columns": {
            "id": {
              "columnDefault": null,
              "isNullable": "NO",
              "dataType": "integer",
              "characterMaximumLength": null,
              "numericScale": 0,
              "comment": null,
              "array": false
            }
          },
          "indexes": {},
          "triggers": {},
          "functions": {},
          "fkConstraints": {}
        },
        "aircraft": {
          "comment": null,
          "pkColumnNames": [
            "id"
          ],
          "columns": {
            "id": {
              "columnDefault": "nextval('planes.aircraft_id_seq'::regclass)",
              "isNullable": "NO",
              "dataType": "integer",
              "characterMaximumLength": null,
              "numericScale": 0,
              "comment": null,
              "array": false
            },
            "company_id": {
              "columnDefault": null,
              "isNullable": "NO",
              "dataType": "integer",
              "characterMaximumLength": null,
              "numericScale": 0,
              "comment": null,
              "array": false
            },
            "aircraft_type_id": {
              "columnDefault": null,
              "isNullable": "NO",
              "dataType": "integer",
              "characterMaximumLength": null,
              "numericScale": 0,
              "comment": null,
              "array": false
            },
            "call_sign": {
              "columnDefault": null,
              "isNullable": "NO",
              "dataType": "character varying",
              "characterMaximumLength": null,
              "numericScale": null,
              "comment": null,
              "array": false
            },
            "archived": {
              "columnDefault": "false",
              "isNullable": "YES",
              "dataType": "boolean",
              "characterMaximumLength": null,
              "numericScale": null,
              "comment": null,
              "array": false
            },
            "aircraft_details": {
              "columnDefault": null,
              "isNullable": "NO",
              "dataType": "jsonb",
              "characterMaximumLength": null,
              "numericScale": null,
              "comment": null,
              "array": false
            },
            "created": {
              "columnDefault": "now()",
              "isNullable": "YES",
              "dataType": "timestamp with time zone",
              "characterMaximumLength": null,
              "numericScale": null,
              "comment": null,
              "array": false
            },
            "ttsn_settings": {
              "columnDefault": null,
              "isNullable": "YES",
              "dataType": "jsonb",
              "characterMaximumLength": null,
              "numericScale": null,
              "comment": null,
              "array": false
            },
            "available": {
              "columnDefault": null,
              "isNullable": "YES",
              "dataType": "boolean",
              "characterMaximumLength": null,
              "numericScale": null,
              "comment": null,
              "array": false
            }
          },
          "indexes": {
            "aircraft_id_key": {
              "columns": [
                [
                  "id"
                ]
              ],
              "unique": true,
              "method": "btree"
            }
          },
          "triggers": {},
          "functions": {},
          "fkConstraints": {
            "fk_aircraft_aircraft_type": {
              "targetTable": "aircraft_type",
              "sourceColumns": [
                "aircraft_type_id"
              ],
              "targetColumns": [
                "id"
              ],
              "updateAction": "CASCADE",
              "deleteAction": "CASCADE",
              "matchType": "SIMPLE"
            },
            "fk_aircraft_company": {
              "targetTable": "company",
              "sourceColumns": [
                "company_id"
              ],
              "targetColumns": [
                "id"
              ],
              "updateAction": "CASCADE",
              "deleteAction": "CASCADE",
              "matchType": "SIMPLE"
            }
          }
        }
      }
    }
  }
}

[timneedham]

...so there's an issue with things in public.