search

ozum / pg-structure

Get PostgreSQL database structure as a detailed JS Object.
http://www.pg-structure.com
MIT License
357 stars 28 forks source link

Error: 'uuid' cannot be found in CompositeType's name. #90

Open magick93 opened 1 year ago

magick93 commented 1 year ago

Hello

I'm getting the following error. The table in question, organization does use composite types. However, I've tried with tables that do not have composite types or uuid types and I get the same error.

/home/bob/git/bobtech/bobby/node_modules/.pnpm/indexable-array@0.7.0/node_modules/indexable-array/dist/index.js:491
            throw new Error(`'${value}' cannot be found in ${possibleType || ""}${key}.`);
                  ^

Error: 'uuid' cannot be found in CompositeType's name.
    at Proxy.get (/home/bob/git/bobtech/bobby/node_modules/.pnpm/indexable-array@0.7.0/node_modules/indexable-array/dist/index.js:491:19)
    at new Column (/home/bob/git/bobtech/bobby/node_modules/.pnpm/pg-structure@7.15.0/node_modules/pg-structure/dist/pg-structure/column.js:38:128)
    at /home/bob/git/bobtech/bobby/node_modules/.pnpm/pg-structure@7.15.0/node_modules/pg-structure/dist/main.js:184:29
    at Array.forEach (<anonymous>)
    at addColumns (/home/bob/git/bobtech/bobby/node_modules/.pnpm/pg-structure@7.15.0/node_modules/pg-structure/dist/main.js:180:10)
    at addObjects (/home/bob/git/bobtech/bobby/node_modules/.pnpm/pg-structure@7.15.0/node_modules/pg-structure/dist/main.js:333:5)
    at pgStructure (/home/bob/git/bobtech/bobby/node_modules/.pnpm/pg-structure@7.15.0/node_modules/pg-structure/dist/main.js:376:5)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async demo (/home/bob/git/bobtech/bobby/apps/bobby-api/dist/src/pgstruct.js:6:16)

Node.js v18.14.2

Sample

import pgStructure from "pg-structure";

async function demo() {
  // Prefer to use environment variables or ".env" file for the credentials. See the ".env.example" file.
  const db = await pgStructure({ 
    host: "localhost",
    port: 54322, 
    database: "postgres", 
    user: "postgres", 
    password: "postgres" 
    }, 
    { includeSchemas: ["public"] });

  const table = db.get("organization");
//   const columnNames = table.columns.map((c) => c.name);
//   console.log("columnNames", columnNames);
//   const columnTypeName = table.columns.get("options").type.name;
//   const indexColumnNames = table.indexes.get("ix_mail").columns;
//   const relatedTables = table.hasManyTables;
}

demo();
ozum commented 1 year ago

Hi @magick93,

I need the following to investigate and solve the problem:

  1. PostgreSQL version
  2. Creation script (DDL) of a very simple database with the error you reported.

Thanks,

magick93 commented 1 year ago

Hi @ozum

I've pruned my schema down to a single table (from about 400) and a handful of enums. The error has changed slightly.

https://gist.github.com/magick93/688b5c15f019c3549050593986ee39d6

I'm running version PostgreSQL 15.1

Thanks

magick93 commented 1 year ago

I've cleaned up the above ddl - removed all the enums - but the error persists, so dont spend time on this, as the issue is clearly somewhere else.

magick93 commented 1 year ago

After dropping the following extensions, and all tables, then adding one table, it works.

select * from pg_catalog.pg_extension 

drop extension plv8 cascade;
drop extension wrappers cascade;
drop extension hstore cascade;
drop extension unaccent cascade;
drop extension supabase_vault cascade;
drop extension vector cascade;
magick93 commented 1 year ago

I'm still working on getting a small ddl that can reproduce it.

In the meantime, my own debugging, I've found this:

In main.ts > addColumns(), when the row has sqlType:'geometry' - it seems to try looking up composite types, not base types.

magick93 commented 1 year ago

You could use the following query to get any types added from extensions:

SELECT t.typname AS type_name, e.extname AS extension_name
FROM pg_type t
JOIN pg_depend d ON t.oid = d.objid
JOIN pg_extension e ON d.refobjid = e.oid
WHERE t.typtype = 'b';
ozum commented 1 year ago

@magick93, thanks for the tip to get types from extensions.

If the problem is related to types from extensions, it will take more time to provide a solution because I need free time to think about how to add this feature.

I can only tell after you post a DDL that reproduces the problem.

magick93 commented 1 year ago

I'm entirely confident that I have identified the issue, or that the following will reproduce it.

Note - postgis is being installed into the extensions schema. I believe this is causing the problem. If I enable postgis without specifying an explicit schema to install it into, and use a geometry type, it seems fine.


CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA extensions;

-- public."currency_code_list_type" definition

-- DROP TYPE public."currency_code_list_type";

CREATE TYPE public."currency_code_list_type" AS ENUM (
    'AED',
    'AFN',
    'ALL',
    'AMD',
    'ANG',
    'AOA',
    'ARS',
    'AUD',
    'AWG',
    'AZN',
    'BAM',
    'BBD',
    'BDT',
    'BGN',
    'BHD',
    'BIF',
    'BMD',
    'BND',
    'BOB',
    'BOV',
    'BRL',
    'BSD',
    'BTN',
    'BWP',
    'BYR',
    'BZD',
    'CAD',
    'CDF',
    'CHE',
    'CHF',
    'CHW',
    'CLF',
    'CLP',
    'CNY',
    'COP',
    'COU',
    'CRC',
    'CUC',
    'CUP',
    'CVE',
    'CZK',
    'DJF',
    'DKK',
    'DOP',
    'DZD',
    'EGP',
    'ERN',
    'ETB',
    'EUR',
    'FJD',
    'FKP',
    'GBP',
    'GEL',
    'GHS',
    'GIP',
    'GMD',
    'GNF',
    'GTQ',
    'GYD',
    'HKD',
    'HNL',
    'HRK',
    'HTG',
    'HUF',
    'IDR',
    'ILS',
    'INR',
    'IQD',
    'IRR',
    'ISK',
    'JMD',
    'JOD',
    'JPY',
    'KES',
    'KGS',
    'KHR',
    'KMF',
    'KPW',
    'KRW',
    'KWD',
    'KYD',
    'KZT',
    'LAK',
    'LBP',
    'LKR',
    'LRD',
    'LSL',
    'LYD',
    'MAD',
    'MDL',
    'MGA',
    'MKD',
    'MMK',
    'MNT',
    'MOP',
    'MRU',
    'MUR',
    'MVR',
    'MWK',
    'MXN',
    'MXV',
    'MYR',
    'MZN',
    'NAD',
    'NGN',
    'NIO',
    'NOK',
    'NPR',
    'NZD',
    'OMR',
    'PAB',
    'PEN',
    'PGK',
    'PHP',
    'PKR',
    'PLN',
    'PYG',
    'QAR',
    'RON',
    'RSD',
    'RUB',
    'RWF',
    'SAR',
    'SBD',
    'SCR',
    'SDG',
    'SEK',
    'SGD',
    'SHP',
    'SLL',
    'SOS',
    'SRD',
    'SSP',
    'STN',
    'SVC',
    'SYP',
    'SZL',
    'THB',
    'TJS',
    'TMT',
    'TND',
    'TOP',
    'TRY',
    'TTD',
    'TWD',
    'TZS',
    'UAH',
    'UGX',
    'USD',
    'USN',
    'UYI',
    'UYU',
    'UZS',
    'VEF',
    'VND',
    'VUV',
    'WST',
    'XAF',
    'XAG',
    'XAU',
    'XBA',
    'XBB',
    'XBC',
    'XBD',
    'XCD',
    'XDR',
    'XOF',
    'XPD',
    'XPF',
    'XPT',
    'XSU',
    'XTS',
    'XUA',
    'XXX',
    'YER',
    'ZAR',
    'ZMW',
    'ZWL');

-- public.addresscomponent definition

-- DROP TYPE public.addresscomponent;

CREATE TYPE public.addresscomponent AS (
    "type" varchar,
    value varchar);

-- public.address definition

-- Drop table

-- DROP TABLE public.address;

CREATE TABLE public.address (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    line text NULL,
    extended_lines public."_addresscomponent" NULL,
    currency_code_list_type public."currency_code_list_type" NULL,
    country_sub_divisions public."_addresscomponent" NULL,
    city text NULL,
    postal_code text NULL,
    post_office_box text NULL,
    geo_location extensions.geometry NULL,
    formatted_address text NULL,
    address_type_array_id uuid NULL,
    addresses_id uuid NULL,
    CONSTRAINT address_pkey PRIMARY KEY (id)
);
ozum commented 1 year ago

I see now. PostGIS and other similar extensions with types are not supported yet. I need some free time to implement extensibility. I'm sorry that I can't help in a short time for that.

I will keep this issue open, maybe someone from the community may help sooner than I.

magick93 commented 12 months ago

I'm happy to help @ozum

As a suggestion, how does the following sound:

Challenges:

This query can find extension types with their respect schema.


SELECT 
    t.typname AS type_name, 
    e.extname AS extension_name,
    n.nspname AS schema_name
FROM pg_type t
JOIN pg_depend d ON t.oid = d.objid
JOIN pg_extension e ON d.refobjid = e.oid
JOIN pg_namespace n ON e.extnamespace = n.oid
WHERE t.typtype = 'b';
ozum commented 12 months ago

I will think about the solution you suggested, but I won't have free time for a few weeks.

Does the last query return all types for all extensions in all schemas?

If that's the case, I can use that query to add all extension-related types to the pg-structure.

magick93 commented 12 months ago

I will think about the solution you suggested, but I won't have free time for a few weeks.

No problem @ozum - I'm happy to try resolve this. But if you have time to point me in the right direction and offer the odd bit of advice, that would be greatly appreciated.

Does the last query return all types for all extensions in all schemas?

Yes it does.