[pg] Support PgAdmin

[rekby]

• [x] #1717
• [x] #1729
• [x] #2321
• [x] #2354
• [x] #2366
• [x] #2394
• [x] #2427
• [x] #3068

Notes: Disable gssmode for connection

[rekby]

1717 - need table pg_settings in default path

Workaround:

CREATE TABLE pg_settings (
    name text,
    setting text,
    unit text,
    category text,
    short_desc text,
    extra_desc text,
    context text,
    vartype text,
    source text,
    min_val text,
    max_val text,
    enumvals text[],
    boot_val text,
    reset_val text,
    sourcefile text,
    sourceline integer,
    pending_restart boolean, _stub_id Serial PRIMARY KEY
);

[vitstn]

next query after select version is

$3 = SELECT db.oid as did, db.datname, db.datallowconn, pg_encoding_to_char(db.encoding) AS serverencoding, has_database_privilege(db.oid, 'CREATE') as cancreate, datistemplate FROM pg_catalog.pg_database db WHERE db.datname = current_database()

[vitstn]

current_database() crash

[vitstn]

next query

SELECT roles.oid as id, roles.rolname as name, roles.rolsuper as is_superuser, CASE WHEN roles.rolsuper THEN true ELSE roles.rolcreaterole END as can_create_role, CASE WHEN roles.rolsuper THEN true ELSE roles.rolcreatedb END as can_create_db, CASE WHEN 'pg_signal_backend'=ANY(ARRAY(WITH RECURSIVE cte AS ( SELECT pg_roles.oid,pg_roles.rolname FROM pg_roles WHERE pg_roles.oid = roles.oid UNION ALL SELECT m.roleid,pgr.rolname FROM cte cte_1 JOIN pg_auth_members m ON m.member = cte_1.oid JOIN pg_roles pgr ON pgr.oid = m.roleid) SELECT rolname FROM cte)) THEN True ELSE False END as can_signal_backend FROM pg_catalog.pg_roles as roles WHERE rolname = current_user

[rekby]

@vitstn PgAdmin send own application name at start connection:

Frame 41: 170 bytes on wire (1360 bits), 170 bytes captured (1360 bits) on interface sshdump, id 0
Linux cooked capture v2
Internet Protocol Version 6, Src: ::1, Dst: ::1
Transmission Control Protocol, Src Port: 48494, Dst Port: 5432, Seq: 1, Ack: 1, Len: 78
PostgreSQL
    Type: Startup message
    Length: 78
    Protocol major version: 3
    Protocol minor version: 0
    Parameter name: user
    Parameter value: root
    Parameter name: database
    Parameter value: postgres
    Parameter name: application_name
    Parameter value: pgAdmin 4 - DB:postgres

We can detect the connection with "application_name" "pgAdmin 4 - .*" as special case and patch the request special for pg_admin. With send NOTICE notice in response with describe the hack - for people.

[vitstn]

@adameat How can we get application_name from pgwire layer?

[adameat]

@adameat How can we get application_name from pgwire layer?

it is right there: https://github.com/ydb-platform/ydb/blob/main/ydb/core/local_pgwire/local_pgwire_connection.cpp#L47

[vitstn]

with compat hack for AEXPR_OP_ANY -> false, the query is working

--!syntax_pg set ApplicationName="pgAdmin"; SELECT roles.oid as id, roles.rolname as name, roles.rolsuper as is_superuser, CASE WHEN roles.rolsuper THEN true ELSE roles.rolcreaterole END as can_create_role, CASE WHEN roles.rolsuper THEN true ELSE roles.rolcreatedb END as can_create_db, CASE WHEN 'pg_signal_backend'=ANY(ARRAY(WITH RECURSIVE cte AS ( SELECT pg_roles.oid,pg_roles.rolname FROM pg_roles WHERE pg_roles.oid = roles.oid UNION ALL SELECT m.roleid,pgr.rolname FROM cte cte_1 JOIN pg_auth_members m ON m.member = cte_1.oid JOIN pg_roles pgr ON pgr.oid = m.roleid) SELECT rolname FROM cte)) THEN True ELSE False END as can_signal_backend FROM pg_catalog.pg_roles as roles WHERE rolname = current_user

[vitstn]

properties of tablespaces/roles don't work yet

Status: GENERIC_ERROR Issues: : Error: Type annotation, code: 1030 :1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem :5:5: Error: At function: PgCall :5:5: Error: No such proc: shobj_description

{ oid => '1993', descr => 'get description for object id and shared catalog name', proname => 'shobj_description', prolang => 'sql', procost => '100', provolatile => 's', prorettype => 'text', proargtypes => 'oid name', prosrc => 'see system_functions.sql' },

[vitstn]

properties of databases

Status: INTERNAL_ERROR Issues: : Fatal: Optimization, code: 1070 :8:9: Fatal: ydb/library/yql/core/common_opt/yql_co_pgselect.cpp:2974 JoinOuter(): requirement outerInputs.size() == finalExtTypes->Tail().ChildrenSize() failed, code: 1 :1:1: Fatal: ydb/library/yql/core/common_opt/yql_co_pgselect.cpp:704 RewriteSubLinksPartial(): requirement status.Level != IGraphTransformer::TStatus::Error failed, code: 1

[vitstn]

a background query

--!syntax_pg /pga4dash/ SELECT 'session_stats' AS chart_name, pg_catalog.row_to_json(t) AS chart_data FROM (SELECT (SELECT count() FROM pg_catalog.pg_stat_activity WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 3)) AS "Total", (SELECT count() FROM pg_catalog.pg_stat_activity WHERE state = 'active' AND datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 3)) AS "Active", (SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = 'idle' AND datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 3)) AS "Idle" ) t UNION ALL SELECT 'tps_stats' AS chart_name, pg_catalog.row_to_json(t) AS chart_data FROM (SELECT (SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 3)) AS "Transactions", (SELECT sum(xact_commit) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 3)) AS "Commits", (SELECT sum(xact_rollback) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 3)) AS "Rollbacks" ) t UNION ALL SELECT 'ti_stats' AS chart_name, pg_catalog.row_to_json(t) AS chart_data FROM (SELECT (SELECT sum(tup_inserted) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 3)) AS "Inserts", (SELECT sum(tup_updated) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 3)) AS "Updates", (SELECT sum(tup_deleted) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 3)) AS "Deletes" ) t UNION ALL SELECT 'to_stats' AS chart_name, pg_catalog.row_to_json(t) AS chart_data FROM (SELECT (SELECT sum(tup_fetched) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 3)) AS "Fetched", (SELECT sum(tup_returned) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 3)) AS "Returned" ) t UNION ALL SELECT 'bio_stats' AS chart_name, pg_catalog.row_to_json(t) AS chart_data FROM (SELECT (SELECT sum(blks_read) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 3)) AS "Reads", (SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 3)) AS "Hits" ) t

[vitstn]

another query to describe a role

SELECT pg_catalog.array_to_string(array_agg(sql), E'\n\n') AS sql FROM (SELECT '-- Role: ' || pg_catalog.quote_ident(rolname) || E'\n-- DROP ROLE IF EXISTS ' || pg_catalog.quote_ident(rolname) || E';\n\nCREATE ROLE ' || pg_catalog.quote_ident(rolname) || E' WITH\n ' || CASE WHEN rolcanlogin THEN 'LOGIN' ELSE 'NOLOGIN' END || E'\n ' || CASE WHEN rolsuper THEN 'SUPERUSER' ELSE 'NOSUPERUSER' END || E'\n ' || CASE WHEN rolinherit THEN 'INHERIT' ELSE 'NOINHERIT' END || E'\n ' || CASE WHEN rolcreatedb THEN 'CREATEDB' ELSE 'NOCREATEDB' END || E'\n ' || CASE WHEN rolcreaterole THEN 'CREATEROLE' ELSE 'NOCREATEROLE' END || E'\n ' || -- PostgreSQL >= 9.1 CASE WHEN rolreplication THEN 'REPLICATION' ELSE 'NOREPLICATION' END || CASE WHEN rolconnlimit > 0 THEN E'\n CONNECTION LIMIT ' || rolconnlimit ELSE '' END || (SELECT CASE WHEN (rolpassword LIKE 'md5%' or rolpassword LIKE 'SCRAM%') THEN E'\n ENCRYPTED PASSWORD ''' || rolpassword || '''' WHEN rolpassword IS NOT NULL THEN E'\n PASSWORD ''' || rolpassword || '''' ELSE '' END FROM pg_catalog.pg_authid au WHERE au.oid=r.oid) || CASE WHEN rolvaliduntil IS NOT NULL THEN E'\n VALID UNTIL ' || pg_catalog.quote_literal(rolvaliduntil::text) ELSE '' END || ';' AS sql FROM pg_catalog.pg_roles r WHERE r.oid=1::OID UNION ALL (SELECT pg_catalog.array_to_string(array_agg(sql), E'\n') AS sql FROM (SELECT 'GRANT ' || pg_catalog.array_to_string(pg_catalog.array_agg(rolname order by rolname), ', ') || ' TO ' || pg_catalog.quote_ident(pg_catalog.pg_get_userbyid(1::OID)) || CASE WHEN admin_option THEN ' WITH ADMIN OPTION;' ELSE ';' END AS sql FROM (SELECT pg_catalog.quote_ident(r.rolname) AS rolname, m.admin_option AS admin_option FROM pg_catalog.pg_auth_members m LEFT JOIN pg_catalog.pg_roles r ON (m.roleid = r.oid) WHERE m.member=1::OID ORDER BY r.rolname ) a GROUP BY admin_option) s) UNION ALL (SELECT pg_catalog.array_to_string(array_agg(sql), E'\n') AS sql FROM (SELECT 'ALTER ROLE ' || pg_catalog.quote_ident(rolname) || ' SET ' || param || ' TO ' || CASE WHEN param IN ('search_path', 'temp_tablespaces') THEN value ELSE pg_catalog.quote_literal(value) END || ';' AS sql FROM (SELECT rolcanlogin, rolname, pg_catalog.split_part(rolconfig, '=', 1) AS param, pg_catalog.replace(rolconfig, pg_catalog.split_part(rolconfig, '=', 1) || '=', '') AS value FROM (SELECT pg_catalog.unnest(rolconfig) AS rolconfig, rolcanlogin, rolname FROM pg_catalog.pg_roles WHERE oid=1::OID ) r ) a) b) -- PostgreSQL >= 9.0 UNION ALL (SELECT pg_catalog.array_to_string(array_agg(sql), E'\n') AS sql FROM (SELECT 'ALTER ROLE ' || pg_catalog.quote_ident(pg_get_userbyid(1::OID)) || ' IN DATABASE ' || pg_catalog.quote_ident(datname) || ' SET ' || param|| ' TO ' || CASE WHEN param IN ('search_path', 'temp_tablespaces') THEN value ELSE pg_catalog.quote_literal(value) END || ';' AS sql FROM (SELECT datname, pg_catalog.split_part(rolconfig, '=', 1) AS param, pg_catalog.replace(rolconfig, pg_catalog.split_part(rolconfig, '=', 1) || '=', '') AS value FROM (SELECT d.datname, pg_catalog.unnest(c.setconfig) AS rolconfig FROM (SELECT FROM pg_catalog.pg_db_role_setting dr WHERE dr.setrole=1::OID AND dr.setdatabase!=0) c LEFT JOIN pg_catalog.pg_database d ON (d.oid = c.setdatabase) ) a ) b ) d ) UNION ALL (SELECT 'COMMENT ON ROLE ' || pg_catalog.quote_ident(pg_get_userbyid(1::OID)) || ' IS ' || pg_catalog.quote_literal(description) || ';' AS sql FROM (SELECT pg_catalog.shobj_description(1::OID, 'pg_authid') AS description) a WHERE description IS NOT NULL) -- PostgreSQL >= 9.2 UNION ALL (SELECT pg_catalog.array_to_string(array_agg(sql), E'\n') AS sql FROM (SELECT 'SECURITY LABEL FOR ' || provider || E'\n ON ROLE ' || pg_catalog.quote_ident(rolname) || E'\n IS ' || pg_catalog.quote_literal(label) || ';' AS sql FROM (SELECT label, provider, rolname FROM (SELECT FROM pg_catalog.pg_shseclabel sl1 WHERE sl1.objoid=1::OID) s LEFT JOIN pg_catalog.pg_roles r ON (s.objoid=r.oid)) a) b )) AS a

[vitstn]

a query to load casts

--!syntax_pg SELECT ca.oid, pg_catalog.concat(pg_catalog.format_type(st.oid,NULL),'->',pg_catalog.format_type(tt.oid,tt.typtypmod)) as name FROM pg_catalog.pg_cast ca JOIN pg_catalog.pg_type st ON st.oid=castsource JOIN pg_catalog.pg_namespace ns ON ns.oid=st.typnamespace JOIN pg_catalog.pg_type tt ON tt.oid=casttarget JOIN pg_catalog.pg_namespace nt ON nt.oid=tt.typnamespace LEFT JOIN pg_catalog.pg_proc pr ON pr.oid=castfunc LEFT JOIN pg_catalog.pg_namespace np ON np.oid=pr.pronamespace LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=ca.oid AND des.objsubid=0 AND des.classoid='pg_cast'::regclass) WHERE ca.oid > 16383::OID ORDER BY st.typname, tt.typname

[vitstn]

extensions:

SELECT x.oid, pg_catalog.pg_get_userbyid(extowner) AS owner, x.extname AS name, n.nspname AS schema, x.extrelocatable AS relocatable, x.extversion AS version, e.comment FROM pg_catalog.pg_extension x LEFT JOIN pg_catalog.pg_namespace n ON x.extnamespace=n.oid JOIN pg_catalog.pg_available_extensions() e(name, default_version, comment) ON x.extname=e.name ORDER BY x.extname

Issues:

: Fatal: ydb/library/yql/core/yql_expr_type_annotation.cpp:6766 CheckExpectedTypeAndColumnOrder(): requirement IsSameAnnotation(*node.GetTypeAnn(), *it->second) failed, message: Rewrite error, type should be : List>, but it is: List> for node AssumeColumnOrder, code: 1

[vitstn]

fdw:

SELECT fdw.oid, fdwname as name, fdwhandler, fdwvalidator, description, fdwoptions AS fdwoptions, pg_catalog.pg_get_userbyid(fdwowner) as fdwowner, pg_catalog.array_to_string(fdwacl::text[], ', ') as acl, CASE -- EPAS in redwood mode, concatenation of a string with NULL results as the original string WHEN vp.proname IS NULL THEN NULL ELSE pg_catalog.quote_ident(vp_nsp.nspname)||'.'||pg_catalog.quote_ident(vp.proname) END fdwvalue, CASE -- EPAS in redwood mode, concatenation of a string with NULL results as the original string WHEN vh.proname IS NULL THEN NULL ELSE pg_catalog.quote_ident(vh_nsp.nspname)||'.'||pg_catalog.quote_ident(vh.proname) END fdwhan FROM pg_catalog.pg_foreign_data_wrapper fdw LEFT OUTER JOIN pg_catalog.pg_proc vh on vh.oid=fdwhandler LEFT OUTER JOIN pg_catalog.pg_proc vp on vp.oid=fdwvalidator LEFT OUTER JOIN pg_catalog.pg_namespace vh_nsp ON vh_nsp.oid=vh.pronamespace LEFT OUTER JOIN pg_catalog.pg_namespace vp_nsp ON vp_nsp.oid=vp.pronamespace LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=fdw.oid AND des.objsubid=0 AND des.classoid='pg_foreign_data_wrapper'::regclass) ORDER BY fdwname

runtime error Fatal: ERROR: relation "pg_foreign_data_wrapper" does not exist

typecheck is ok

[vitstn]

languages:

--!syntax_pg SELECT lan.oid as oid, lanname as name, lanpltrusted as trusted, pg_catalog.array_to_string(lanacl::text[], ', ') as acl, hp.proname as lanproc, vp.proname as lanval, description, pg_catalog.pg_get_userbyid(lan.lanowner) as lanowner, ip.proname as laninl, (SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabel sl1 WHERE sl1.objoid=lan.oid) AS seclabels FROM pg_catalog.pg_language lan JOIN pg_catalog.pg_proc hp ON hp.oid=lanplcallfoid LEFT OUTER JOIN pg_catalog.pg_proc ip ON ip.oid=laninline LEFT OUTER JOIN pg_catalog.pg_proc vp ON vp.oid=lanvalidator LEFT OUTER JOIN pg_catalog.pg_description des ON ( des.objoid=lan.oid AND des.objsubid=0 AND des.classoid='pg_language'::regclass ) WHERE lanispl IS TRUE ORDER BY lanname

Issues: Error: alternative is not implemented yet : 145

[vitstn]

schemas:

SELECT nsp.oid, nsp.nspname as name, pg_catalog.has_schema_privilege(nsp.oid, 'CREATE') as can_create, pg_catalog.has_schema_privilege(nsp.oid, 'USAGE') as has_usage FROM pg_catalog.pgnamespace nsp WHERE nspname NOT LIKE 'pg!%' escape '!' AND NOT ( (nsp.nspname = 'pg_catalog' AND EXISTS (SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1)) OR (nsp.nspname = 'pgagent' AND EXISTS (SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1)) OR (nsp.nspname = 'information_schema' AND EXISTS (SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1)) )

ORDER BY nspname

issue:

:9:21: Error: At function: PgNot, At function: PgLike

:9:21: Error: Expected pg text, but got name

[vitstn]

types:

SELECT t.oid, t.typname AS name FROM pg_catalog.pg_type t LEFT OUTER JOIN pg_catalog.pg_type e ON e.oid=t.typelem LEFT OUTER JOIN pg_catalog.pg_class ct ON ct.oid=t.typrelid AND ct.relkind <> 'c' LEFT OUTER JOIN pg_catalog.pgnamespace nsp ON nsp.oid = t.typnamespace WHERE t.typtype != 'd' AND t.typname NOT LIKE E'\%' AND t.typnamespace = 1::oid AND ct.oid is NULL ORDER BY t.typname

issues: PgLike

:6:38: Error: Expected pg text, but got name

[vitstn]

procedures:

SELECT pr.oid, CASE WHEN pg_catalog.pg_get_function_identity_arguments(pr.oid) <> '' THEN pr.proname || '(' || pg_catalog.pg_get_function_identity_arguments(pr.oid) || ')' ELSE pr.proname::text END AS name, lanname, pg_catalog.pg_get_userbyid(proowner) AS funcowner, description FROM pg_catalog.pg_proc pr JOIN pg_catalog.pg_type typ ON typ.oid=prorettype JOIN pg_catalog.pg_language lng ON lng.oid=prolang LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass) WHERE pr.prokind = 'p'::char AND pronamespace = 1::oid AND typname NOT IN ('trigger', 'event_trigger') ORDER BY proname

issues: Terminate was called, reason(42): ERROR: relation "pg_proc" does not exist

[vitstn]

operators:

SELECT op.oid, pg_catalog.pg_get_userbyid(op.oprowner) as owner, CASE WHEN lt.typname IS NOT NULL AND rt.typname IS NOT NULL THEN op.oprname || ' (' || pg_catalog.format_type(lt.oid, NULL) || ', ' || pg_catalog.format_type(rt.oid, NULL) || ')' WHEN lt.typname IS NULL AND rt.typname IS NOT NULL THEN op.oprname || ' (' || pg_catalog.format_type(rt.oid, NULL) || ')' WHEN lt.typname IS NOT NULL AND rt.typname IS NULL THEN op.oprname || ' (' || pg_catalog.format_type(lt.oid, NULL) || ')' ELSE op.oprname || '()' END as name, lt.typname as lefttype, rt.typname as righttype FROM pg_catalog.pg_operator op LEFT OUTER JOIN pg_catalog.pg_type lt ON lt.oid=op.oprleft LEFT OUTER JOIN pg_catalog.pg_type rt ON rt.oid=op.oprright JOIN pg_catalog.pg_type et on et.oid=op.oprresult LEFT OUTER JOIN pg_catalog.pg_operator co ON co.oid=op.oprcom LEFT OUTER JOIN pg_catalog.pg_operator ne ON ne.oid=op.oprnegate LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=op.oid AND des.classoid='pg_operator'::regclass) WHERE op.oprnamespace = 1::oid ORDER BY op.oprname;

issues: Terminate was called, reason(46): ERROR: relation "pg_operator" does not exist

[vitstn]

functions:

SELECT pr.oid, pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name, lanname, pg_catalog.pg_get_userbyid(proowner) as funcowner, description FROM pg_catalog.pg_proc pr JOIN pg_catalog.pg_type typ ON typ.oid=prorettype JOIN pg_catalog.pg_language lng ON lng.oid=prolang LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass) WHERE pr.prokind IN ('f', 'w') AND pronamespace = 1::oid AND typname NOT IN ('trigger', 'event_trigger') ORDER BY proname;

Terminate was called, reason(42): ERROR: relation "pg_proc" does not exist

[vitstn]

foreign tables:

"SELECT c.oid, c.relname AS name, pg_catalog.pg_get_userbyid(relowner) AS owner, ftoptions, nspname as basensp, description FROM pg_catalog.pg_class c JOIN pg_catalog.pg_foreign_table ft ON c.oid=ft.ftrelid LEFT OUTER JOIN pg_catalog.pg_namespace nsp ON (nsp.oid=c.relnamespace) LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=c.oid AND des.classoid='pg_class'::regclass) WHERE c.relnamespace = 1::oid ORDER BY c.relname;

Terminate was called, reason(43): ERROR: relation "pg_class" does not exist

[vitstn]

agg funcs:

SELECT aggfnoid::oid as oid, proname || '(' || COALESCE(pg_catalog.pg_get_function_arguments(aggfnoid::oid), '') || ')' AS name, pg_catalog.pg_get_userbyid(proowner) AS owner FROM pg_aggregate ag LEFT OUTER JOIN pg_catalog.pg_proc pr ON pr.oid = ag.aggfnoid LEFT OUTER JOIN pg_catalog.pg_type tt on tt.oid=aggtranstype LEFT OUTER JOIN pg_catalog.pg_type tf on tf.oid=prorettype LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=aggfnoid::oid AND des.classoid='pg_aggregate'::regclass) WHERE pronamespace = 1::oid ORDER BY name

Terminate was called, reason(47): ERROR: relation "pg_aggregate" does not exist

5 0x00000000237fc75a in RangeVarGetRelidExtended (relation=0x7fb6cb0cc300, lockmode=lockmode@entry=0, flags=flags@entry=0, callback=callback@entry=0x0,

callback_arg=callback_arg@entry=0x0) at /home/vvvv/github/ydb/ydb/library/yql/parser/pg_wrapper/postgresql/src/backend/catalog/namespace.c:399

399 ereport(elevel, (gdb) up

6 0x0000000023bbd719 in regclassin (fcinfo=) at /home/vvvv/github/ydb/ydb/library/yql/parser/pg_wrapper/postgresql/src/backend/utils/adt/regproc.c:974

974 result = RangeVarGetRelid(makeRangeVarFromNameList(names), NoLock, false); (gdb) up

7 0x00000000236f95c3 in NYql::TPgCast::ConvertDatum (this=this@entry=0x56ffdd81f80, datum=datum@entry=140423067385952, state=..., typeMod=typeMod@entry=-1)

at /home/vvvv/github/ydb/ydb/library/yql/parser/pg_wrapper/comp_factory.cpp:1675

1675 auto ret = FInfo1.fn_addr(&callInfo1);

[vitstn]

2387 fix for regclass cast

[vitstn]

columns of view:

SELECT DISTINCT att.attname as name, att.attnum as OID, pg_catalog.format_type(ty.oid,NULL) AS datatype, att.attnotnull as not_null, att.atthasdef as has_default_val FROM pg_catalog.pg_attribute att JOIN pg_catalog.pg_type ty ON ty.oid=atttypid JOIN pg_catalog.pg_namespace tn ON tn.oid=ty.typnamespace JOIN pg_catalog.pg_class cl ON cl.oid=att.attrelid JOIN pg_catalog.pg_namespace na ON na.oid=cl.relnamespace LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=ty.typelem LEFT OUTER JOIN pg_catalog.pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum LEFT OUTER JOIN (pg_catalog.pg_depend JOIN pg_catalog.pg_class cs ON classid='pg_class'::regclass AND objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum LEFT OUTER JOIN pg_catalog.pg_namespace ns ON ns.oid=cs.relnamespace LEFT OUTER JOIN pg_catalog.pg_index pi ON pi.indrelid=att.attrelid AND indisprimary WHERE att.attrelid = 13762::oid AND att.attnum > 0 AND att.attisdropped IS FALSE ORDER BY att.attnum

Issues:

:15:5: Error: alternative is not implemented yet : 145 >>> AND att.attisdropped IS FALSE

[vitstn]

https://github.com/ydb-platform/ydb/pull/2892

[vitstn]

--!syntax_pg SELECT (select count(*) from (values (1),(2),(3)) a(x) where a.x=y) FROM (values (4)) b(y)

need to return 0 instead of null

[vitstn]

https://github.com/ydb-platform/ydb/pull/3093

[vitstn]

this fixes resolving of oid -> type for results https://github.com/ydb-platform/ydb/pull/3101

[vitstn]

https://github.com/ydb-platform/ydb/pull/3270

[vitstn]

select oid,tableoid,xmin,cmin,xmax,cmax,ctid from pg_type where typname = 'text';

on original PG returns oid tableoid xmin cmin xmax cmax ctid 25 1247 1 0 0 0 (0, 10)

[vitstn]

https://github.com/ydb-platform/ydb/issues/6792