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/[email protected]/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/[email protected]/node_modules/indexable-array/dist/index.js:491:19)
    at new Column (/home/bob/git/bobtech/bobby/node_modules/.pnpm/[email protected]/node_modules/pg-structure/dist/pg-structure/column.js:38:128)
    at /home/bob/git/bobtech/bobby/node_modules/.pnpm/[email protected]/node_modules/pg-structure/dist/main.js:184:29
    at Array.forEach (<anonymous>)
    at addColumns (/home/bob/git/bobtech/bobby/node_modules/.pnpm/[email protected]/node_modules/pg-structure/dist/main.js:180:10)
    at addObjects (/home/bob/git/bobtech/bobby/node_modules/.pnpm/[email protected]/node_modules/pg-structure/dist/main.js:333:5)
    at pgStructure (/home/bob/git/bobtech/bobby/node_modules/.pnpm/[email protected]/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();

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,

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

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.

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;

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.

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';

@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.

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)
);

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.

I'm happy to help @ozum

As a suggestion, how does the following sound:

• while a dynamic, query-based, solution would be nice - initially create a new record set - extensionTypes and merge it builtinTypeAliases.

Challenges:

• Postgres uses SEARCH_PATH to look through schemeas to find types, functions etc - if the schema isnt explicitly specified, and required schemas are excluded, it could cause issues.
• The below query may help

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';

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.

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.