2025年5月7日(水) 10:47 Nathan Bossart <nathandboss...@gmail.com>:
>
> On Wed, May 07, 2025 at 09:18:28AM +0900, Ian Lawrence Barwick wrote:
> > Hah, I put together a patch to implement just that a while back, but
> > then concluded
> > for some reason that it would likely be rejected so saved myself the
> > humiliation of
> > submitting it...
> >
> > Attaching patch for reference - it's from mid 2020 so no longer applies.
> > I'll
> > have a crack at cleaning it up if I get a chance.
>
> Oh, thanks!
Version which applies/builds against current HEAD attached. I haven't
yet had a chance to look at the code beyond fixing it, however.
Regards
Ian Barwick
commit 2e20fea416ae6b107ffbe952a951efb145f13ec7
Author: Ian Barwick <barw...@gmail.com>
Date: Wed May 7 17:50:21 2025 +0900
Rebase to HEAD
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 09309ba0390..cab8f0fda93 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4737,6 +4737,10 @@ INSERT INTO mytable VALUES(-1); -- fails
<primary>regconfig</primary>
</indexterm>
+ <indexterm zone="datatype-oid">
+ <primary>regdatabase</primary>
+ </indexterm>
+
<indexterm zone="datatype-oid">
<primary>regdictionary</primary>
</indexterm>
@@ -4878,6 +4882,13 @@ SELECT * FROM pg_attribute
<entry><literal>english</literal></entry>
</row>
+ <row>
+ <entry><type>regdatabase</type></entry>
+ <entry><structname>pg_database</structname></entry>
+ <entry>database name</entry>
+ <entry><literal>template1</literal></entry>
+ </row>
+
<row>
<entry><type>regdictionary</type></entry>
<entry><structname>pg_ts_dict</structname></entry>
@@ -5049,8 +5060,8 @@ WHERE ...
be dropped without first removing the default expression. The
alternative of <literal>nextval('my_seq'::text)</literal> does not
create a dependency.
- (<type>regrole</type> is an exception to this property. Constants of this
- type are not allowed in stored expressions.)
+ (<type>regrole</type> and <type>regdatabase</type> are the only exceptions for the property.
+ Constants of this type are not allowed in such expressions.)
</para>
<para>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index af3d056b992..0323667485e 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -21940,6 +21940,24 @@ SELECT NULLIF(value, '(none)') ...
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>to_regdatabase</primary>
+ </indexterm>
+ <function>to_regdatabase</function> ( <type>text</type> )
+ <returnvalue>regdatabase</returnvalue>
+ </para>
+ <para>
+ Translates a textual database name to its OID. A similar result is
+ obtained by casting the string to type <type>regdatabase</type> (see
+ <xref linkend="datatype-oid"/>); however, this function will return
+ <literal>NULL</literal> rather than throwing an error if the name is
+ not found. Also unlike the cast, this does not accept
+ a numeric OID as input.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml
index cb8e4f5c48a..34689dd8845 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -1103,6 +1103,7 @@ psql --username=postgres --file=script.sql postgres
<simplelist>
<member><type>regcollation</type></member>
<member><type>regconfig</type></member>
+ <member><type>regdatabase</type></member>
<member><type>regdictionary</type></member>
<member><type>regnamespace</type></member>
<member><type>regoper</type></member>
diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c
index 6db864892d0..fc8638c1b61 100644
--- a/src/backend/bootstrap/bootstrap.c
+++ b/src/backend/bootstrap/bootstrap.c
@@ -109,6 +109,8 @@ static const struct typinfo TypInfo[] = {
F_REGROLEIN, F_REGROLEOUT},
{"regnamespace", REGNAMESPACEOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
F_REGNAMESPACEIN, F_REGNAMESPACEOUT},
+ {"regdatabase", REGDATABASEOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
+ F_REGDATABASEIN, F_REGDATABASEOUT},
{"text", TEXTOID, 0, -1, false, TYPALIGN_INT, TYPSTORAGE_EXTENDED, DEFAULT_COLLATION_OID,
F_TEXTIN, F_TEXTOUT},
{"oid", OIDOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 18316a3968b..ca9ac41b92c 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -1841,9 +1841,15 @@ find_expr_references_walker(Node *node,
break;
/*
- * Dependencies for regrole should be shared among all
- * databases, so explicitly inhibit to have dependencies.
+ * Explicitly prohibit dependencies for cluster-wide shared objects
*/
+ case REGDATABASEOID:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("constant of the type %s cannot be used here",
+ "regdatabase")));
+ break;
+
case REGROLEOID:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index 5ee608a2b39..7c010e82008 100644
--- a/src/backend/utils/adt/regproc.c
+++ b/src/backend/utils/adt/regproc.c
@@ -30,6 +30,7 @@
#include "catalog/pg_ts_config.h"
#include "catalog/pg_ts_dict.h"
#include "catalog/pg_type.h"
+#include "commands/dbcommands.h"
#include "lib/stringinfo.h"
#include "mb/pg_wchar.h"
#include "miscadmin.h"
@@ -1763,6 +1764,134 @@ regnamespacesend(PG_FUNCTION_ARGS)
return oidsend(fcinfo);
}
+
+/*
+ * regdatabasein - converts "datname" to database OID
+ *
+ * We also accept a numeric OID, for symmetry with the output routine.
+ *
+ * '-' signifies unknown (OID 0). In all other cases, the input must
+ * match an existing pg_database entry.
+ */
+Datum
+regdatabasein(PG_FUNCTION_ARGS)
+{
+ char *dat_name_or_oid = PG_GETARG_CSTRING(0);
+ Node *escontext = fcinfo->context;
+ Oid result;
+ List *names;
+
+ /* '-' ? */
+ if (strcmp(dat_name_or_oid, "-") == 0)
+ PG_RETURN_OID(InvalidOid);
+
+ /* Numeric OID? */
+ if (dat_name_or_oid[0] >= '0' &&
+ dat_name_or_oid[0] <= '9' &&
+ strspn(dat_name_or_oid, "0123456789") == strlen(dat_name_or_oid))
+ {
+ result = DatumGetObjectId(DirectFunctionCall1(oidin,
+ CStringGetDatum(dat_name_or_oid)));
+ PG_RETURN_OID(result);
+ }
+
+ /* The rest of this wouldn't work in bootstrap mode */
+ if (IsBootstrapProcessingMode())
+ elog(ERROR, "regdatabase values must be OIDs in bootstrap mode");
+
+ /* Normal case: see if the name matches any pg_database entry. */
+ names = stringToQualifiedNameList(dat_name_or_oid, escontext);
+
+ if (list_length(names) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_NAME),
+ errmsg("invalid name syntax")));
+
+ result = get_database_oid(strVal(linitial(names)), false);
+
+ PG_RETURN_OID(result);
+}
+
+/*
+ * to_regdatabase - converts "datname" to database OID
+ *
+ * If the name is not found, we return NULL.
+ */
+Datum
+to_regdatabase(PG_FUNCTION_ARGS)
+{
+ char *dat_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ Node *escontext = fcinfo->context;
+ Oid result;
+ List *names;
+
+ names = stringToQualifiedNameList(dat_name, escontext);
+
+ if (list_length(names) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_NAME),
+ errmsg("invalid name syntax")));
+
+ result = get_database_oid(strVal(linitial(names)), true);
+
+ if (OidIsValid(result))
+ PG_RETURN_OID(result);
+ else
+ PG_RETURN_NULL();
+}
+
+/*
+ * regdatabaseout - converts database OID to "dat_name"
+ */
+Datum
+regdatabaseout(PG_FUNCTION_ARGS)
+{
+ Oid datid = PG_GETARG_OID(0);
+ char *result;
+
+ if (datid == InvalidOid)
+ {
+ result = pstrdup("-");
+ PG_RETURN_CSTRING(result);
+ }
+
+ result = get_database_name(datid);
+
+ if (result)
+ {
+ /* pstrdup is not really necessary, but it avoids a compiler warning */
+ result = pstrdup(quote_identifier(result));
+ }
+ else
+ {
+ /* If OID doesn't match any database, return it numerically */
+ result = (char *) palloc(NAMEDATALEN);
+ snprintf(result, NAMEDATALEN, "%u", datid);
+ }
+
+ PG_RETURN_CSTRING(result);
+}
+
+/*
+ * regdatabaserecv - converts external binary format to regdatabase
+ */
+Datum
+regdatabaserecv(PG_FUNCTION_ARGS)
+{
+ /* Exactly the same as oidrecv, so share code */
+ return oidrecv(fcinfo);
+}
+
+/*
+ * regdatabasesend - converts regdatabase to binary format
+ */
+Datum
+regdatabasesend(PG_FUNCTION_ARGS)
+{
+ /* Exactly the same as oidsend, so share code */
+ return oidsend(fcinfo);
+}
+
/*
* text_regclass: convert text to regclass
*
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index a96b1b9c0bc..7e439af619e 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -4620,6 +4620,7 @@ convert_to_scalar(Datum value, Oid valuetypid, Oid collid, double *scaledvalue,
case REGDICTIONARYOID:
case REGROLEOID:
case REGNAMESPACEOID:
+ case REGDATABASEOID:
*scaledvalue = convert_numeric_to_scalar(value, valuetypid,
&failure);
*scaledlobound = convert_numeric_to_scalar(lobound, boundstypid,
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index 6e3cad454c0..0c3459f730e 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -317,6 +317,7 @@ GetCCHashEqFuncs(Oid keytype, CCHashFN *hashfunc, RegProcedure *eqfunc, CCFastEq
case REGDICTIONARYOID:
case REGROLEOID:
case REGNAMESPACEOID:
+ case REGDATABASEOID:
*hashfunc = int4hashfast;
*fasteqfunc = int4eqfast;
*eqfunc = F_OIDEQ;
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 940fc77fc2e..79a5e66f35e 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -168,6 +168,7 @@ static DataTypesUsageChecks data_types_usage_checks[] =
/* pg_class.oid is preserved, so 'regclass' is OK */
" 'regcollation', "
" 'regconfig', "
+ " 'regdatabase', "
" 'regdictionary', "
" 'regnamespace', "
" 'regoper', "
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index ab46be606f0..fbfd669587f 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -281,6 +281,20 @@
castcontext => 'a', castmethod => 'f' },
{ castsource => 'regnamespace', casttarget => 'int4', castfunc => '0',
castcontext => 'a', castmethod => 'b' },
+{ castsource => 'oid', casttarget => 'regdatabase', castfunc => '0',
+ castcontext => 'i', castmethod => 'b' },
+{ castsource => 'regdatabase', casttarget => 'oid', castfunc => '0',
+ castcontext => 'i', castmethod => 'b' },
+{ castsource => 'int8', casttarget => 'regdatabase', castfunc => 'oid',
+ castcontext => 'i', castmethod => 'f' },
+{ castsource => 'int2', casttarget => 'regdatabase', castfunc => 'int4(int2)',
+ castcontext => 'i', castmethod => 'f' },
+{ castsource => 'int4', casttarget => 'regdatabase', castfunc => '0',
+ castcontext => 'i', castmethod => 'b' },
+{ castsource => 'regdatabase', casttarget => 'int8', castfunc => 'int8(oid)',
+ castcontext => 'a', castmethod => 'f' },
+{ castsource => 'regdatabase', casttarget => 'int4', castfunc => '0',
+ castcontext => 'a', castmethod => 'b' },
# String category
{ castsource => 'text', casttarget => 'bpchar', castfunc => '0',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 62beb71da28..9564a804627 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7466,6 +7466,17 @@
proargnames => '{value,type_name,message,detail,hint,sql_error_code}',
prosrc => 'pg_input_error_info' },
+{ oid => '8602', descr => 'I/O',
+ proname => 'regdatabasein', provolatile => 's', prorettype => 'regdatabase',
+ proargtypes => 'cstring', prosrc => 'regdatabasein' },
+{ oid => '8603', descr => 'I/O',
+ proname => 'regdatabaseout', provolatile => 's', prorettype => 'cstring',
+ proargtypes => 'regdatabase', prosrc => 'regdatabaseout' },
+{ oid => '8604', descr => 'convert database name to regdatabase',
+ proname => 'to_regdatabase', provolatile => 's',
+ prorettype => 'regdatabase', proargtypes => 'text',
+ prosrc => 'to_regdatabase' },
+
{ oid => '1268',
descr => 'parse qualified identifier to array of identifiers',
proname => 'parse_ident', prorettype => '_text', proargtypes => 'text bool',
@@ -8299,7 +8310,6 @@
{ oid => '2455', descr => 'I/O',
proname => 'regtypesend', prorettype => 'bytea', proargtypes => 'regtype',
prosrc => 'regtypesend' },
-
{ oid => '4094', descr => 'I/O',
proname => 'regrolerecv', prorettype => 'regrole', proargtypes => 'internal',
prosrc => 'regrolerecv' },
@@ -8312,6 +8322,12 @@
{ oid => '4088', descr => 'I/O',
proname => 'regnamespacesend', prorettype => 'bytea',
proargtypes => 'regnamespace', prosrc => 'regnamespacesend' },
+{ oid => '8605', descr => 'I/O',
+ proname => 'regdatabaserecv', prorettype => 'regdatabase',
+ proargtypes => 'internal', prosrc => 'regdatabaserecv' },
+{ oid => '8606', descr => 'I/O',
+ proname => 'regdatabasesend', prorettype => 'bytea',
+ proargtypes => 'regdatabase', prosrc => 'regdatabasesend' },
{ oid => '2456', descr => 'I/O',
proname => 'bit_recv', prorettype => 'bit',
proargtypes => 'internal oid int4', prosrc => 'bit_recv' },
diff --git a/src/include/catalog/pg_type.dat b/src/include/catalog/pg_type.dat
index 6dca77e0a22..2e75c9895bf 100644
--- a/src/include/catalog/pg_type.dat
+++ b/src/include/catalog/pg_type.dat
@@ -399,6 +399,11 @@
typinput => 'regnamespacein', typoutput => 'regnamespaceout',
typreceive => 'regnamespacerecv', typsend => 'regnamespacesend',
typalign => 'i' },
+{ oid => '8600', array_type_oid => '8601', descr => 'registered database',
+ typname => 'regdatabase', typlen => '4', typbyval => 't', typcategory => 'N',
+ typinput => 'regdatabasein', typoutput => 'regdatabaseout',
+ typreceive => 'regdatabaserecv', typsend => 'regdatabasesend',
+ typalign => 'i' },
# uuid
{ oid => '2950', array_type_oid => '2951', descr => 'UUID',
diff --git a/src/test/regress/expected/regproc.out b/src/test/regress/expected/regproc.out
index 97b917502ca..5c1bb1c7354 100644
--- a/src/test/regress/expected/regproc.out
+++ b/src/test/regress/expected/regproc.out
@@ -46,6 +46,12 @@ SELECT regcollation('"POSIX"');
"POSIX"
(1 row)
+SELECT regdatabase('template1');
+ regdatabase
+-------------
+ template1
+(1 row)
+
SELECT to_regoper('||/');
to_regoper
------------
@@ -88,6 +94,12 @@ SELECT to_regcollation('"POSIX"');
"POSIX"
(1 row)
+SELECT to_regdatabase('template1');
+ to_regdatabase
+----------------
+ template1
+(1 row)
+
-- with schemaname
SELECT regoper('pg_catalog.||/');
regoper
@@ -243,6 +255,10 @@ SELECT regtype('int3');
ERROR: type "int3" does not exist
LINE 1: SELECT regtype('int3');
^
+SELECT regdatabase('notadatabase');
+ERROR: database "notadatabase" does not exist
+LINE 1: SELECT regdatabase('notadatabase');
+ ^
-- with schemaname
SELECT regoper('ng_catalog.||/');
ERROR: operator does not exist: ng_catalog.||/
@@ -349,6 +365,12 @@ SELECT to_regcollation('notacollation');
(1 row)
+SELECT to_regdatabase('notadatabase');
+ to_regdatabase
+----------------
+
+(1 row)
+
-- with schemaname
SELECT to_regoper('ng_catalog.||/');
to_regoper
diff --git a/src/test/regress/expected/type_sanity.out b/src/test/regress/expected/type_sanity.out
index dd0c52ab08b..1e4860547ed 100644
--- a/src/test/regress/expected/type_sanity.out
+++ b/src/test/regress/expected/type_sanity.out
@@ -738,7 +738,7 @@ SELECT oid, typname, typtype, typelem, typarray
typtype NOT IN ('p', 'c') AND
-- These reg* types cannot be pg_upgraded, so discard them.
oid != ALL(ARRAY['regproc', 'regprocedure', 'regoper',
- 'regoperator', 'regconfig', 'regdictionary',
+ 'regoperator', 'regconfig', 'regdatabase', 'regdictionary',
'regnamespace', 'regcollation']::regtype[]) AND
-- Discard types that do not accept input values as these cannot be
-- tested easily.
diff --git a/src/test/regress/sql/regproc.sql b/src/test/regress/sql/regproc.sql
index 232289ac398..a62e84890d8 100644
--- a/src/test/regress/sql/regproc.sql
+++ b/src/test/regress/sql/regproc.sql
@@ -15,6 +15,7 @@ SELECT regprocedure('abs(numeric)');
SELECT regclass('pg_class');
SELECT regtype('int4');
SELECT regcollation('"POSIX"');
+SELECT regdatabase('template1');
SELECT to_regoper('||/');
SELECT to_regoperator('+(int4,int4)');
@@ -23,6 +24,7 @@ SELECT to_regprocedure('abs(numeric)');
SELECT to_regclass('pg_class');
SELECT to_regtype('int4');
SELECT to_regcollation('"POSIX"');
+SELECT to_regdatabase('template1');
-- with schemaname
@@ -65,6 +67,7 @@ SELECT regproc('know');
SELECT regprocedure('absinthe(numeric)');
SELECT regclass('pg_classes');
SELECT regtype('int3');
+SELECT regdatabase('notadatabase');
-- with schemaname
@@ -100,6 +103,7 @@ SELECT to_regprocedure('absinthe(numeric)');
SELECT to_regclass('pg_classes');
SELECT to_regtype('int3');
SELECT to_regcollation('notacollation');
+SELECT to_regdatabase('notadatabase');
-- with schemaname
diff --git a/src/test/regress/sql/type_sanity.sql b/src/test/regress/sql/type_sanity.sql
index c94dd83d306..914613fe8a0 100644
--- a/src/test/regress/sql/type_sanity.sql
+++ b/src/test/regress/sql/type_sanity.sql
@@ -567,7 +567,7 @@ SELECT oid, typname, typtype, typelem, typarray
typtype NOT IN ('p', 'c') AND
-- These reg* types cannot be pg_upgraded, so discard them.
oid != ALL(ARRAY['regproc', 'regprocedure', 'regoper',
- 'regoperator', 'regconfig', 'regdictionary',
+ 'regoperator', 'regconfig', 'regdatabase', 'regdictionary',
'regnamespace', 'regcollation']::regtype[]) AND
-- Discard types that do not accept input values as these cannot be
-- tested easily.
