From 5bbee34d9f2e6097247ace3ebe1dde1f6aa80287 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 20 Dec 2018 15:58:58 -0500 Subject: [PATCH] Avoid producing over-length specific_name outputs in information_schema. information_schema output columns that are declared as being type sql_identifier are supposed to conform to the implementation's rules for valid identifiers, in particular the identifier length limit. Several places potentially violated this limit by concatenating a function's name and OID. (The OID is added to ensure name uniqueness within a schema, since the spec doesn't expect function name overloading.) Simply truncating the concatenation result to fit in "name" won't do, since losing part of the OID might wind up giving non-unique results. Instead, let's truncate the function name as necessary. The most practical way to do that is to do it in a C function; the information_schema.sql script doesn't have easy access to the value of NAMEDATALEN, nor does it have an easy way to truncate on the basis of resulting byte-length rather than number of characters. (There are still a couple of places that cast concatenation results to sql_identifier, but as far as I can see they are guaranteed not to produce over-length strings, at least with the normal value of NAMEDATALEN.) Discussion: https://postgr.es/m/23817.1545283477@sss.pgh.pa.us --- src/backend/catalog/information_schema.sql | 20 +++++++------ src/backend/utils/adt/name.c | 35 ++++++++++++++++++++++ src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.dat | 4 +++ 4 files changed, 51 insertions(+), 10 deletions(-) diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 0fbcfa8bf1..b30da5f31b 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -416,7 +416,7 @@ CREATE VIEW check_constraint_routine_usage AS CAST(c.conname AS sql_identifier) AS constraint_name, CAST(current_database() AS sql_identifier) AS specific_catalog, CAST(np.nspname AS sql_identifier) AS specific_schema, - CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name + CAST(nameconcatoid(p.proname, p.oid) AS sql_identifier) AS specific_name FROM pg_namespace nc, pg_constraint c, pg_depend d, pg_proc p, pg_namespace np WHERE nc.oid = c.connamespace AND c.contype = 'c' @@ -1115,7 +1115,7 @@ GRANT SELECT ON key_column_usage TO PUBLIC; CREATE VIEW parameters AS SELECT CAST(current_database() AS sql_identifier) AS specific_catalog, CAST(n_nspname AS sql_identifier) AS specific_schema, - CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name, + CAST(nameconcatoid(proname, p_oid) AS sql_identifier) AS specific_name, CAST((ss.x).n AS cardinal_number) AS ordinal_position, CAST( CASE WHEN proargmodes IS NULL THEN 'IN' @@ -1320,7 +1320,7 @@ CREATE VIEW routine_privileges AS CAST(grantee.rolname AS sql_identifier) AS grantee, CAST(current_database() AS sql_identifier) AS specific_catalog, CAST(n.nspname AS sql_identifier) AS specific_schema, - CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name, + CAST(nameconcatoid(p.proname, p.oid) AS sql_identifier) AS specific_name, CAST(current_database() AS sql_identifier) AS routine_catalog, CAST(n.nspname AS sql_identifier) AS routine_schema, CAST(p.proname AS sql_identifier) AS routine_name, @@ -1409,7 +1409,7 @@ GRANT SELECT ON role_routine_grants TO PUBLIC; CREATE VIEW routines AS SELECT CAST(current_database() AS sql_identifier) AS specific_catalog, CAST(n.nspname AS sql_identifier) AS specific_schema, - CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name, + CAST(nameconcatoid(p.proname, p.oid) AS sql_identifier) AS specific_name, CAST(current_database() AS sql_identifier) AS routine_catalog, CAST(n.nspname AS sql_identifier) AS routine_schema, CAST(p.proname AS sql_identifier) AS routine_name, @@ -1964,7 +1964,7 @@ CREATE VIEW transforms AS CAST(t.typname AS sql_identifier) AS udt_name, CAST(current_database() AS sql_identifier) AS specific_catalog, CAST(np.nspname AS sql_identifier) AS specific_schema, - CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name, + CAST(nameconcatoid(p.proname, p.oid) AS sql_identifier) AS specific_name, CAST(l.lanname AS sql_identifier) AS group_name, CAST('FROM SQL' AS character_data) AS transform_type FROM pg_type t JOIN pg_transform x ON t.oid = x.trftype @@ -1980,7 +1980,7 @@ CREATE VIEW transforms AS CAST(t.typname AS sql_identifier) AS udt_name, CAST(current_database() AS sql_identifier) AS specific_catalog, CAST(np.nspname AS sql_identifier) AS specific_schema, - CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name, + CAST(nameconcatoid(p.proname, p.oid) AS sql_identifier) AS specific_name, CAST(l.lanname AS sql_identifier) AS group_name, CAST('TO SQL' AS character_data) AS transform_type FROM pg_type t JOIN pg_transform x ON t.oid = x.trftype @@ -2495,7 +2495,7 @@ CREATE VIEW view_routine_usage AS CAST(v.relname AS sql_identifier) AS table_name, CAST(current_database() AS sql_identifier) AS specific_catalog, CAST(np.nspname AS sql_identifier) AS specific_schema, - CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name + CAST(nameconcatoid(p.proname, p.oid) AS sql_identifier) AS specific_name FROM pg_namespace nv, pg_class v, pg_depend dv, pg_depend dp, pg_proc p, pg_namespace np @@ -2712,7 +2712,8 @@ CREATE VIEW element_types AS UNION ALL /* parameters */ - SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier), + SELECT pronamespace, + CAST(nameconcatoid(proname, oid) AS sql_identifier), 'ROUTINE'::text, (ss.x).n, (ss.x).x, 0 FROM (SELECT p.pronamespace, p.proname, p.oid, _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x @@ -2721,7 +2722,8 @@ CREATE VIEW element_types AS UNION ALL /* result types */ - SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier), + SELECT p.pronamespace, + CAST(nameconcatoid(p.proname, p.oid) AS sql_identifier), 'ROUTINE'::text, 0, p.prorettype, 0 FROM pg_proc p diff --git a/src/backend/utils/adt/name.c b/src/backend/utils/adt/name.c index 0cf8b9d46b..eb35188e06 100644 --- a/src/backend/utils/adt/name.c +++ b/src/backend/utils/adt/name.c @@ -364,3 +364,38 @@ current_schemas(PG_FUNCTION_ARGS) PG_RETURN_POINTER(array); } + +/* + * SQL-function nameconcatoid(name, oid) returns name + * + * This is used in the information_schema to produce specific_name columns, + * which are supposed to be unique per schema. We achieve that (in an ugly + * way) by appending the object's OID. The result is the same as + * ($1::text || '_' || $2::text)::name + * except that, if it would not fit in NAMEDATALEN, we make it do so by + * truncating the name input (not the oid). + */ +Datum +nameconcatoid(PG_FUNCTION_ARGS) +{ + Name nam = PG_GETARG_NAME(0); + Oid oid = PG_GETARG_OID(1); + Name result; + char suffix[20]; + int suflen; + int namlen; + + suflen = snprintf(suffix, sizeof(suffix), "_%u", oid); + namlen = strlen(NameStr(*nam)); + + /* Truncate oversize input by truncating name part, not suffix */ + if (namlen + suflen >= NAMEDATALEN) + namlen = pg_mbcliplen(NameStr(*nam), namlen, NAMEDATALEN - 1 - suflen); + + /* We use palloc0 here to ensure result is zero-padded */ + result = (Name) palloc0(NAMEDATALEN); + memcpy(NameStr(*result), NameStr(*nam), namlen); + memcpy(NameStr(*result) + namlen, suffix, suflen); + + PG_RETURN_NAME(result); +} diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 821bf5ba67..b6a66db139 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201812192 +#define CATALOG_VERSION_NO 201812201 #endif diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index d0a571ef95..acb0154048 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -716,6 +716,10 @@ proname => 'bttextnamecmp', proleakproof => 't', prorettype => 'int4', proargtypes => 'text name', prosrc => 'bttextnamecmp' }, +{ oid => '266', descr => 'concatenate name and oid', + proname => 'nameconcatoid', prorettype => 'name', proargtypes => 'name oid', + prosrc => 'nameconcatoid' }, + { oid => '274', descr => 'current date and time - increments during transactions', proname => 'timeofday', provolatile => 'v', prorettype => 'text', -- 2.40.0