[pg] Support PgAdmin

[rekby]

• [pg] Support set_config search_path #1717
• [pg] select version() should return column version #1729
• [pg] support of row_to_json over table record #2321
• [pg] support of variadic funcs #2354
• No implicit casts from name to pgtext #2366
• fill pg_proc/pg_aggregates/pg_operator tables #2394
• problems with pgadmin 8.3 #2427
• [pg] Add current database to pg_database #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.pg_namespace 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.pg_namespace 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]

#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]

#3093

[vitstn]

this fixes resolving of oid -> type for results
#3101

[vitstn]

#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]

#6792