Hi,
I felt inclusion of alias types regpublication and regsubscription will
help the logical replication users. This will also help in [1].
The alias types allow simplified lookup of publication oid values for
objects. For example, to examine the pg_publication_rel rows, one could
write:
SELECT prpubid::regpublication, prrelid::regclass FROM pg_publication_rel;
rather than:
SELECT p.pubname, prrelid::regclass FROM pg_publication_rel pr,
pg_publication p WHERE pr.prpubid = p.oid;
Similarly in case of subscription:
For example, to examine the pg_subscription_rel rows, one could write:
SELECT srsubid::regsubscription, srrelid::regclass FROM pg_subscription_rel;
rather than:
SELECT s.subname,srsubid::regclass FROM pg_subscription_rel sr,
pg_subscription s where sr.srsubid = s.oid;
Attached patch has the changes for the same.
Thoughts?
[1] -
https://www.postgresql.org/message-id/flat/CALDaNm0OANxuJ6RXqwZsM1MSY4s19nuH3734j4a72etDwvBETQ%40mail.gmail.com
Regards,
Vignesh
From 3ef5874b4e3b65b81199dbf878f53d820940be96 Mon Sep 17 00:00:00 2001
From: vignesh <vignesh21@gmail.com>
Date: Wed, 19 May 2021 10:53:29 +0530
Subject: [PATCH v1 1/2] Implement type regpublication
It will help in casting publication information and it's also just generally
useful, like the other reg* types.
---
doc/src/sgml/datatype.sgml | 11 +++
doc/src/sgml/func.sgml | 18 ++++
doc/src/sgml/ref/pgupgrade.sgml | 1 +
src/backend/utils/adt/regproc.c | 126 ++++++++++++++++++++++++++
src/bin/pg_upgrade/check.c | 3 +-
src/include/catalog/pg_cast.dat | 14 +++
src/include/catalog/pg_proc.dat | 15 +++
src/include/catalog/pg_type.dat | 4 +
src/test/regress/expected/regproc.out | 55 +++++++++++
src/test/regress/sql/regproc.sql | 16 ++++
10 files changed, 262 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 0e8ef958a9..dd578f3535 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4603,6 +4603,10 @@ INSERT INTO mytable VALUES(-1); -- fails
<primary>regprocedure</primary>
</indexterm>
+ <indexterm zone="datatype-oid">
+ <primary>regpublication</primary>
+ </indexterm>
+
<indexterm zone="datatype-oid">
<primary>regrole</primary>
</indexterm>
@@ -4763,6 +4767,13 @@ SELECT * FROM pg_attribute
<entry><literal>sum(int4)</literal></entry>
</row>
+ <row>
+ <entry><type>regpublication</type></entry>
+ <entry><structname>pg_publication</structname></entry>
+ <entry>publication name</entry>
+ <entry><literal>testpub</literal></entry>
+ </row>
+
<row>
<entry><type>regrole</type></entry>
<entry><structname>pg_authid</structname></entry>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 08b07f561e..50da778b06 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -23596,6 +23596,24 @@ SELECT collation for ('foo' COLLATE "de_DE");
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>to_regpublication</primary>
+ </indexterm>
+ <function>to_regpublication</function> ( <type>text</type> )
+ <returnvalue>regpublication</returnvalue>
+ </para>
+ <para>
+ Translates a textual publication name to its OID. A similar result is
+ obtained by casting the string to type <type>regpublication</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 a83c63cd98..bb2d908da6 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -785,6 +785,7 @@ psql --username=postgres --file=script.sql postgres
<member><type>regoperator</type></member>
<member><type>regproc</type></member>
<member><type>regprocedure</type></member>
+ <member><type>regpublication</type></member>
</simplelist>
(<type>regclass</type>, <type>regrole</type>, and <type>regtype</type> can be upgraded.)
</para>
diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index f998fe2076..dfa826f1c2 100644
--- a/src/backend/utils/adt/regproc.c
+++ b/src/backend/utils/adt/regproc.c
@@ -27,6 +27,7 @@
#include "catalog/pg_collation.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_proc.h"
+#include "catalog/pg_publication.h"
#include "catalog/pg_ts_config.h"
#include "catalog/pg_ts_dict.h"
#include "catalog/pg_type.h"
@@ -1231,6 +1232,131 @@ regcollationsend(PG_FUNCTION_ARGS)
return oidsend(fcinfo);
}
+/*
+ * regpublicationin - converts "publicationname" to publication 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_publication entry.
+ */
+Datum
+regpublicationin(PG_FUNCTION_ARGS)
+{
+ char *publication_name_or_oid = PG_GETARG_CSTRING(0);
+ Oid result = InvalidOid;
+ List *names;
+
+ /* '-' ? */
+ if (strcmp(publication_name_or_oid, "-") == 0)
+ PG_RETURN_OID(InvalidOid);
+
+ /* Numeric OID? */
+ if (publication_name_or_oid[0] >= '0' &&
+ publication_name_or_oid[0] <= '9' &&
+ strspn(publication_name_or_oid, "0123456789") == strlen(publication_name_or_oid))
+ {
+ result = DatumGetObjectId(DirectFunctionCall1(oidin,
+ CStringGetDatum(publication_name_or_oid)));
+ PG_RETURN_OID(result);
+ }
+
+ /* The rest of this wouldn't work in bootstrap mode */
+ if (IsBootstrapProcessingMode())
+ elog(ERROR, "regpublication values must be OIDs in bootstrap mode");
+
+ /*
+ * Normal case: parse the name into components and see if it matches any
+ * pg_publication entries in the current search path.
+ */
+ names = stringToQualifiedNameList(publication_name_or_oid);
+ if (list_length(names) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_NAME),
+ errmsg("invalid name syntax")));
+
+ result = get_publication_oid(strVal(linitial(names)), false);
+
+ PG_RETURN_OID(result);
+}
+
+/*
+ * to_regpublication - converts "publicationname" to publication OID
+ *
+ * If the name is not found, we return NULL.
+ */
+Datum
+to_regpublication(PG_FUNCTION_ARGS)
+{
+ char *publication_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ Oid result;
+ List *names;
+
+ names = stringToQualifiedNameList(publication_name);
+ if (list_length(names) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_NAME),
+ errmsg("invalid name syntax")));
+
+ result = get_publication_oid(strVal(linitial(names)), true);
+
+ if (OidIsValid(result))
+ PG_RETURN_OID(result);
+ else
+ PG_RETURN_NULL();
+}
+
+/*
+ * regpublicationout - converts publication OID to "publication_name"
+ */
+Datum
+regpublicationout(PG_FUNCTION_ARGS)
+{
+ Oid publicationid = PG_GETARG_OID(0);
+ char *result;
+
+ if (publicationid == InvalidOid)
+ {
+ result = pstrdup("-");
+ PG_RETURN_CSTRING(result);
+ }
+
+ result = get_publication_name(publicationid, true);
+ 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 publication, return it numerically */
+ result = (char *) palloc(NAMEDATALEN);
+ snprintf(result, NAMEDATALEN, "%u", publicationid);
+ }
+
+ PG_RETURN_CSTRING(result);
+}
+
+/*
+ * regpublicationrecv - converts external binary format to regpublication
+ */
+Datum
+regpublicationrecv(PG_FUNCTION_ARGS)
+{
+ /* Exactly the same as oidrecv, so share code */
+ return oidrecv(fcinfo);
+}
+
+/*
+ * regpublicationsend - converts regpublication to binary format
+ */
+Datum
+regpublicationsend(PG_FUNCTION_ARGS)
+{
+ /* Exactly the same as oidsend, so share code */
+ return oidsend(fcinfo);
+}
+
/*
* regtypein - converts "typename" to type OID
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 0c47a6b8cc..8037874af5 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -1141,7 +1141,8 @@ check_for_reg_data_type_usage(ClusterInfo *cluster)
" 'regoper', "
" 'regoperator', "
" 'regproc', "
- " 'regprocedure' "
+ " 'regprocedure', "
+ " 'regpublication' "
/* pg_authid.oid is preserved, so 'regrole' is OK */
/* pg_type.oid is (mostly) preserved, so 'regtype' is OK */
" )",
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index 67f73cb6fb..63007d6527 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -207,6 +207,20 @@
castcontext => 'a', castmethod => 'f' },
{ castsource => 'regcollation', casttarget => 'int4', castfunc => '0',
castcontext => 'a', castmethod => 'b' },
+{ castsource => 'oid', casttarget => 'regpublication', castfunc => '0',
+ castcontext => 'i', castmethod => 'b' },
+{ castsource => 'regpublication', casttarget => 'oid', castfunc => '0',
+ castcontext => 'i', castmethod => 'b' },
+{ castsource => 'int8', casttarget => 'regpublication', castfunc => 'oid',
+ castcontext => 'i', castmethod => 'f' },
+{ castsource => 'int2', casttarget => 'regpublication', castfunc => 'int4(int2)',
+ castcontext => 'i', castmethod => 'f' },
+{ castsource => 'int4', casttarget => 'regpublication', castfunc => '0',
+ castcontext => 'i', castmethod => 'b' },
+{ castsource => 'regpublication', casttarget => 'int8', castfunc => 'int8(oid)',
+ castcontext => 'a', castmethod => 'f' },
+{ castsource => 'regpublication', casttarget => 'int4', castfunc => '0',
+ castcontext => 'a', castmethod => 'b' },
{ castsource => 'oid', casttarget => 'regtype', castfunc => '0',
castcontext => 'i', castmethod => 'b' },
{ castsource => 'regtype', casttarget => 'oid', castfunc => '0',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index acbcae4607..11c6967348 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6882,6 +6882,15 @@
proname => 'to_regcollation', provolatile => 's',
prorettype => 'regcollation', proargtypes => 'text',
prosrc => 'to_regcollation' },
+{ oid => '4544', descr => 'I/O',
+ proname => 'regpublicationin', provolatile => 's', prorettype => 'regpublication',
+ proargtypes => 'cstring', prosrc => 'regpublicationin' },
+{ oid => '4545', descr => 'I/O',
+ proname => 'regpublicationout', provolatile => 's', prorettype => 'cstring',
+ proargtypes => 'regpublication', prosrc => 'regpublicationout' },
+{ oid => '4546', descr => 'convert classname to regpublication',
+ proname => 'to_regpublication', provolatile => 's', prorettype => 'regpublication',
+ proargtypes => 'text', prosrc => 'to_regpublication' },
{ oid => '2220', descr => 'I/O',
proname => 'regtypein', provolatile => 's', prorettype => 'regtype',
proargtypes => 'cstring', prosrc => 'regtypein' },
@@ -7715,6 +7724,12 @@
{ oid => '4197', descr => 'I/O',
proname => 'regcollationsend', prorettype => 'bytea',
proargtypes => 'regcollation', prosrc => 'regcollationsend' },
+{ oid => '4547', descr => 'I/O',
+ proname => 'regpublicationrecv', prorettype => 'regpublication',
+ proargtypes => 'internal', prosrc => 'regpublicationrecv' },
+{ oid => '4548', descr => 'I/O',
+ proname => 'regpublicationsend', prorettype => 'bytea',
+ proargtypes => 'regpublication', prosrc => 'regpublicationsend' },
{ oid => '2454', descr => 'I/O',
proname => 'regtyperecv', prorettype => 'regtype', proargtypes => 'internal',
prosrc => 'regtyperecv' },
diff --git a/src/include/catalog/pg_type.dat b/src/include/catalog/pg_type.dat
index 41074c994b..cf2b7d1890 100644
--- a/src/include/catalog/pg_type.dat
+++ b/src/include/catalog/pg_type.dat
@@ -385,6 +385,10 @@
typinput => 'regcollationin', typoutput => 'regcollationout',
typreceive => 'regcollationrecv', typsend => 'regcollationsend',
typalign => 'i' },
+{ oid => '4549', array_type_oid => '4550', descr => 'registered publication',
+ typname => 'regpublication', typlen => '4', typbyval => 't', typcategory => 'N',
+ typinput => 'regpublicationin', typoutput => 'regpublicationout',
+ typreceive => 'regpublicationrecv', typsend => 'regpublicationsend', typalign => 'i' },
{ oid => '2206', array_type_oid => '2211', descr => 'registered type',
typname => 'regtype', typlen => '4', typbyval => 't', typcategory => 'N',
typinput => 'regtypein', typoutput => 'regtypeout',
diff --git a/src/test/regress/expected/regproc.out b/src/test/regress/expected/regproc.out
index e45ff5483f..2801751718 100644
--- a/src/test/regress/expected/regproc.out
+++ b/src/test/regress/expected/regproc.out
@@ -3,6 +3,10 @@
--
/* If objects exist, return oids */
CREATE ROLE regress_regrole_test;
+-- suppress warning that depends on wal_level
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION regress_testpub;
+RESET client_min_messages;
-- without schemaname
SELECT regoper('||/');
regoper
@@ -192,6 +196,18 @@ SELECT regnamespace('"pg_catalog"');
pg_catalog
(1 row)
+SELECT regpublication('regress_testpub');
+ regpublication
+-----------------
+ regress_testpub
+(1 row)
+
+SELECT regpublication('"regress_testpub"');
+ regpublication
+-----------------
+ regress_testpub
+(1 row)
+
SELECT to_regrole('regress_regrole_test');
to_regrole
----------------------
@@ -216,8 +232,21 @@ SELECT to_regnamespace('"pg_catalog"');
pg_catalog
(1 row)
+SELECT to_regpublication('regress_testpub');
+ to_regpublication
+-------------------
+ regress_testpub
+(1 row)
+
+SELECT to_regpublication('"regress_testpub"');
+ to_regpublication
+-------------------
+ regress_testpub
+(1 row)
+
/* If objects don't exist, raise errors. */
DROP ROLE regress_regrole_test;
+DROP PUBLICATION regress_testpub;
-- without schemaname
SELECT regoper('||//');
ERROR: operator does not exist: ||//
@@ -305,6 +334,18 @@ SELECT regnamespace('foo.bar');
ERROR: invalid name syntax
LINE 1: SELECT regnamespace('foo.bar');
^
+SELECT regpublication('Nonexistent');
+ERROR: publication "nonexistent" does not exist
+LINE 1: SELECT regpublication('Nonexistent');
+ ^
+SELECT regpublication('"Nonexistent"');
+ERROR: publication "Nonexistent" does not exist
+LINE 1: SELECT regpublication('"Nonexistent"');
+ ^
+SELECT regpublication('foo.bar');
+ERROR: invalid name syntax
+LINE 1: SELECT regpublication('foo.bar');
+ ^
/* If objects don't exist, return NULL with no error. */
-- without schemaname
SELECT to_regoper('||//');
@@ -435,3 +476,17 @@ SELECT to_regnamespace('"Nonexistent"');
SELECT to_regnamespace('foo.bar');
ERROR: invalid name syntax
+SELECT to_regpublication('Nonexistent');
+ to_regpublication
+-------------------
+
+(1 row)
+
+SELECT to_regpublication('"Nonexistent"');
+ to_regpublication
+-------------------
+
+(1 row)
+
+SELECT to_regpublication('foo.bar');
+ERROR: invalid name syntax
diff --git a/src/test/regress/sql/regproc.sql b/src/test/regress/sql/regproc.sql
index faab0c15ce..df29cfd3e8 100644
--- a/src/test/regress/sql/regproc.sql
+++ b/src/test/regress/sql/regproc.sql
@@ -6,6 +6,11 @@
CREATE ROLE regress_regrole_test;
+-- suppress warning that depends on wal_level
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION regress_testpub;
+RESET client_min_messages;
+
-- without schemaname
SELECT regoper('||/');
@@ -47,15 +52,20 @@ SELECT regrole('regress_regrole_test');
SELECT regrole('"regress_regrole_test"');
SELECT regnamespace('pg_catalog');
SELECT regnamespace('"pg_catalog"');
+SELECT regpublication('regress_testpub');
+SELECT regpublication('"regress_testpub"');
SELECT to_regrole('regress_regrole_test');
SELECT to_regrole('"regress_regrole_test"');
SELECT to_regnamespace('pg_catalog');
SELECT to_regnamespace('"pg_catalog"');
+SELECT to_regpublication('regress_testpub');
+SELECT to_regpublication('"regress_testpub"');
/* If objects don't exist, raise errors. */
DROP ROLE regress_regrole_test;
+DROP PUBLICATION regress_testpub;
-- without schemaname
@@ -86,6 +96,9 @@ SELECT regrole('foo.bar');
SELECT regnamespace('Nonexistent');
SELECT regnamespace('"Nonexistent"');
SELECT regnamespace('foo.bar');
+SELECT regpublication('Nonexistent');
+SELECT regpublication('"Nonexistent"');
+SELECT regpublication('foo.bar');
/* If objects don't exist, return NULL with no error. */
@@ -120,3 +133,6 @@ SELECT to_regrole('foo.bar');
SELECT to_regnamespace('Nonexistent');
SELECT to_regnamespace('"Nonexistent"');
SELECT to_regnamespace('foo.bar');
+SELECT to_regpublication('Nonexistent');
+SELECT to_regpublication('"Nonexistent"');
+SELECT to_regpublication('foo.bar');
--
2.25.1
From 9affe916f84e8ea3bd46bc793773e1281980502f Mon Sep 17 00:00:00 2001
From: vignesh <vignesh21@gmail.com>
Date: Wed, 19 May 2021 12:18:00 +0530
Subject: [PATCH v1 2/2] Implement type regsubscription
It will help in casting subscription information and it's also just generally
useful, like the other reg* types.
---
doc/src/sgml/datatype.sgml | 11 +++
doc/src/sgml/func.sgml | 18 ++++
doc/src/sgml/ref/pgupgrade.sgml | 1 +
src/backend/utils/adt/regproc.c | 125 ++++++++++++++++++++++++++
src/bin/pg_upgrade/check.c | 3 +-
src/include/catalog/pg_cast.dat | 14 +++
src/include/catalog/pg_proc.dat | 15 ++++
src/include/catalog/pg_type.dat | 4 +
src/test/regress/expected/regproc.out | 53 +++++++++++
src/test/regress/sql/regproc.sql | 13 +++
10 files changed, 256 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index dd578f3535..67fbce1a7f 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4607,6 +4607,10 @@ INSERT INTO mytable VALUES(-1); -- fails
<primary>regpublication</primary>
</indexterm>
+ <indexterm zone="datatype-oid">
+ <primary>regsubscription</primary>
+ </indexterm>
+
<indexterm zone="datatype-oid">
<primary>regrole</primary>
</indexterm>
@@ -4774,6 +4778,13 @@ SELECT * FROM pg_attribute
<entry><literal>testpub</literal></entry>
</row>
+ <row>
+ <entry><type>regsubscription</type></entry>
+ <entry><structname>pg_subscription</structname></entry>
+ <entry>subscription name</entry>
+ <entry><literal>testsub</literal></entry>
+ </row>
+
<row>
<entry><type>regrole</type></entry>
<entry><structname>pg_authid</structname></entry>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 50da778b06..e487a02064 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -23614,6 +23614,24 @@ SELECT collation for ('foo' COLLATE "de_DE");
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>to_regsubscription</primary>
+ </indexterm>
+ <function>to_regsubscription</function> ( <type>text</type> )
+ <returnvalue>regsubscritpion</returnvalue>
+ </para>
+ <para>
+ Translates a textual subscription name to its OID. A similar result is
+ obtained by casting the string to type <type>regsubscription</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 bb2d908da6..5616bd28b8 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -786,6 +786,7 @@ psql --username=postgres --file=script.sql postgres
<member><type>regproc</type></member>
<member><type>regprocedure</type></member>
<member><type>regpublication</type></member>
+ <member><type>regsubscription</type></member>
</simplelist>
(<type>regclass</type>, <type>regrole</type>, and <type>regtype</type> can be upgraded.)
</para>
diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index dfa826f1c2..e8e3c6ce9a 100644
--- a/src/backend/utils/adt/regproc.c
+++ b/src/backend/utils/adt/regproc.c
@@ -28,6 +28,7 @@
#include "catalog/pg_operator.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_publication.h"
+#include "catalog/pg_subscription.h"
#include "catalog/pg_ts_config.h"
#include "catalog/pg_ts_dict.h"
#include "catalog/pg_type.h"
@@ -1357,6 +1358,130 @@ regpublicationsend(PG_FUNCTION_ARGS)
return oidsend(fcinfo);
}
+/*
+ * regsubscriptionin - converts "subscriptionname" to subscription 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_subscription entry.
+ */
+Datum
+regsubscriptionin(PG_FUNCTION_ARGS)
+{
+ char *subscription_name_or_oid = PG_GETARG_CSTRING(0);
+ Oid result = InvalidOid;
+ List *names;
+
+ /* '-' ? */
+ if (strcmp(subscription_name_or_oid, "-") == 0)
+ PG_RETURN_OID(InvalidOid);
+
+ /* Numeric OID? */
+ if (subscription_name_or_oid[0] >= '0' &&
+ subscription_name_or_oid[0] <= '9' &&
+ strspn(subscription_name_or_oid, "0123456789") == strlen(subscription_name_or_oid))
+ {
+ result = DatumGetObjectId(DirectFunctionCall1(oidin,
+ CStringGetDatum(subscription_name_or_oid)));
+ PG_RETURN_OID(result);
+ }
+
+ /* The rest of this wouldn't work in bootstrap mode */
+ if (IsBootstrapProcessingMode())
+ elog(ERROR, "regsubscription values must be OIDs in bootstrap mode");
+
+ /*
+ * Normal case: parse the name into components and see if it matches any
+ * pg_subscription entries in the current search path.
+ */
+ names = stringToQualifiedNameList(subscription_name_or_oid);
+ if (list_length(names) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_NAME),
+ errmsg("invalid name syntax")));
+
+ result = get_subscription_oid(strVal(linitial(names)), false);
+
+ PG_RETURN_OID(result);
+}
+
+/*
+ * to_regsubscription - converts "subscriptionname" to subscription OID
+ *
+ * If the name is not found, we return NULL.
+ */
+Datum
+to_regsubscription(PG_FUNCTION_ARGS)
+{
+ char *subscription_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ Oid result;
+ List *names;
+
+ names = stringToQualifiedNameList(subscription_name);
+ if (list_length(names) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_NAME),
+ errmsg("invalid name syntax")));
+
+ result = get_subscription_oid(strVal(linitial(names)), true);
+
+ if (OidIsValid(result))
+ PG_RETURN_OID(result);
+ else
+ PG_RETURN_NULL();
+}
+
+/*
+ * regsubscriptionout - converts subscription OID to "subscription_name"
+ */
+Datum
+regsubscriptionout(PG_FUNCTION_ARGS)
+{
+ Oid subscriptionid = PG_GETARG_OID(0);
+ char *result;
+
+ if (subscriptionid == InvalidOid)
+ {
+ result = pstrdup("-");
+ PG_RETURN_CSTRING(result);
+ }
+
+ result = get_subscription_name(subscriptionid, true);
+ 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 subscription, return it numerically */
+ result = (char *) palloc(NAMEDATALEN);
+ snprintf(result, NAMEDATALEN, "%u", subscriptionid);
+ }
+
+ PG_RETURN_CSTRING(result);
+}
+
+/*
+ * regsubscriptionrecv - converts external binary format to regsubscription
+ */
+Datum
+regsubscriptionrecv(PG_FUNCTION_ARGS)
+{
+ /* Exactly the same as oidrecv, so share code */
+ return oidrecv(fcinfo);
+}
+
+/*
+ * regsubscriptionsend - converts regsubscription to binary format
+ */
+Datum
+regsubscriptionsend(PG_FUNCTION_ARGS)
+{
+ /* Exactly the same as oidsend, so share code */
+ return oidsend(fcinfo);
+}
/*
* regtypein - converts "typename" to type OID
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 8037874af5..ad2a6fd275 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -1142,7 +1142,8 @@ check_for_reg_data_type_usage(ClusterInfo *cluster)
" 'regoperator', "
" 'regproc', "
" 'regprocedure', "
- " 'regpublication' "
+ " 'regpublication', "
+ " 'regsubscription' "
/* pg_authid.oid is preserved, so 'regrole' is OK */
/* pg_type.oid is (mostly) preserved, so 'regtype' is OK */
" )",
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index 63007d6527..a6871f6b33 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -221,6 +221,20 @@
castcontext => 'a', castmethod => 'f' },
{ castsource => 'regpublication', casttarget => 'int4', castfunc => '0',
castcontext => 'a', castmethod => 'b' },
+{ castsource => 'oid', casttarget => 'regsubscription', castfunc => '0',
+ castcontext => 'i', castmethod => 'b' },
+{ castsource => 'regsubscription', casttarget => 'oid', castfunc => '0',
+ castcontext => 'i', castmethod => 'b' },
+{ castsource => 'int8', casttarget => 'regsubscription', castfunc => 'oid',
+ castcontext => 'i', castmethod => 'f' },
+{ castsource => 'int2', casttarget => 'regsubscription', castfunc => 'int4(int2)',
+ castcontext => 'i', castmethod => 'f' },
+{ castsource => 'int4', casttarget => 'regsubscription', castfunc => '0',
+ castcontext => 'i', castmethod => 'b' },
+{ castsource => 'regsubscription', casttarget => 'int8', castfunc => 'int8(oid)',
+ castcontext => 'a', castmethod => 'f' },
+{ castsource => 'regsubscription', casttarget => 'int4', castfunc => '0',
+ castcontext => 'a', castmethod => 'b' },
{ castsource => 'oid', casttarget => 'regtype', castfunc => '0',
castcontext => 'i', castmethod => 'b' },
{ castsource => 'regtype', casttarget => 'oid', castfunc => '0',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 11c6967348..9d68f2dd56 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6891,6 +6891,15 @@
{ oid => '4546', descr => 'convert classname to regpublication',
proname => 'to_regpublication', provolatile => 's', prorettype => 'regpublication',
proargtypes => 'text', prosrc => 'to_regpublication' },
+{ oid => '4551', descr => 'I/O',
+ proname => 'regsubscriptionin', provolatile => 's', prorettype => 'regsubscription',
+ proargtypes => 'cstring', prosrc => 'regsubscriptionin' },
+{ oid => '4552', descr => 'I/O',
+ proname => 'regsubscriptionout', provolatile => 's', prorettype => 'cstring',
+ proargtypes => 'regsubscription', prosrc => 'regsubscriptionout' },
+{ oid => '4553', descr => 'convert classname to regsubscription',
+ proname => 'to_regsubscription', provolatile => 's', prorettype => 'regsubscription',
+ proargtypes => 'text', prosrc => 'to_regsubscription' },
{ oid => '2220', descr => 'I/O',
proname => 'regtypein', provolatile => 's', prorettype => 'regtype',
proargtypes => 'cstring', prosrc => 'regtypein' },
@@ -7730,6 +7739,12 @@
{ oid => '4548', descr => 'I/O',
proname => 'regpublicationsend', prorettype => 'bytea',
proargtypes => 'regpublication', prosrc => 'regpublicationsend' },
+{ oid => '4554', descr => 'I/O',
+ proname => 'regsubscriptionrecv', prorettype => 'regsubscription',
+ proargtypes => 'internal', prosrc => 'regsubscriptionrecv' },
+{ oid => '4555', descr => 'I/O',
+ proname => 'regsubscriptionsend', prorettype => 'bytea',
+ proargtypes => 'regsubscription', prosrc => 'regsubscriptionsend' },
{ oid => '2454', descr => 'I/O',
proname => 'regtyperecv', prorettype => 'regtype', proargtypes => 'internal',
prosrc => 'regtyperecv' },
diff --git a/src/include/catalog/pg_type.dat b/src/include/catalog/pg_type.dat
index cf2b7d1890..61a965510f 100644
--- a/src/include/catalog/pg_type.dat
+++ b/src/include/catalog/pg_type.dat
@@ -389,6 +389,10 @@
typname => 'regpublication', typlen => '4', typbyval => 't', typcategory => 'N',
typinput => 'regpublicationin', typoutput => 'regpublicationout',
typreceive => 'regpublicationrecv', typsend => 'regpublicationsend', typalign => 'i' },
+{ oid => '4556', array_type_oid => '4557', descr => 'registered subscription',
+ typname => 'regsubscription', typlen => '4', typbyval => 't', typcategory => 'N',
+ typinput => 'regsubscriptionin', typoutput => 'regsubscriptionout',
+ typreceive => 'regsubscriptionrecv', typsend => 'regsubscriptionsend', typalign => 'i' },
{ oid => '2206', array_type_oid => '2211', descr => 'registered type',
typname => 'regtype', typlen => '4', typbyval => 't', typcategory => 'N',
typinput => 'regtypein', typoutput => 'regtypeout',
diff --git a/src/test/regress/expected/regproc.out b/src/test/regress/expected/regproc.out
index 2801751718..49fda6444a 100644
--- a/src/test/regress/expected/regproc.out
+++ b/src/test/regress/expected/regproc.out
@@ -6,6 +6,7 @@ CREATE ROLE regress_regrole_test;
-- suppress warning that depends on wal_level
SET client_min_messages = 'ERROR';
CREATE PUBLICATION regress_testpub;
+CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false);
RESET client_min_messages;
-- without schemaname
SELECT regoper('||/');
@@ -208,6 +209,18 @@ SELECT regpublication('"regress_testpub"');
regress_testpub
(1 row)
+SELECT regsubscription('regress_testsub');
+ regsubscription
+-----------------
+ regress_testsub
+(1 row)
+
+SELECT regsubscription('"regress_testsub"');
+ regsubscription
+-----------------
+ regress_testsub
+(1 row)
+
SELECT to_regrole('regress_regrole_test');
to_regrole
----------------------
@@ -244,9 +257,23 @@ SELECT to_regpublication('"regress_testpub"');
regress_testpub
(1 row)
+SELECT to_regsubscription('regress_testsub');
+ to_regsubscription
+--------------------
+ regress_testsub
+(1 row)
+
+SELECT to_regsubscription('"regress_testsub"');
+ to_regsubscription
+--------------------
+ regress_testsub
+(1 row)
+
/* If objects don't exist, raise errors. */
DROP ROLE regress_regrole_test;
DROP PUBLICATION regress_testpub;
+ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
+DROP SUBSCRIPTION regress_testsub;
-- without schemaname
SELECT regoper('||//');
ERROR: operator does not exist: ||//
@@ -346,6 +373,18 @@ SELECT regpublication('foo.bar');
ERROR: invalid name syntax
LINE 1: SELECT regpublication('foo.bar');
^
+SELECT regsubscription('Nonexistent');
+ERROR: subscription "nonexistent" does not exist
+LINE 1: SELECT regsubscription('Nonexistent');
+ ^
+SELECT regsubscription('"Nonexistent"');
+ERROR: subscription "Nonexistent" does not exist
+LINE 1: SELECT regsubscription('"Nonexistent"');
+ ^
+SELECT regsubscription('foo.bar');
+ERROR: invalid name syntax
+LINE 1: SELECT regsubscription('foo.bar');
+ ^
/* If objects don't exist, return NULL with no error. */
-- without schemaname
SELECT to_regoper('||//');
@@ -490,3 +529,17 @@ SELECT to_regpublication('"Nonexistent"');
SELECT to_regpublication('foo.bar');
ERROR: invalid name syntax
+SELECT to_regsubscription('Nonexistent');
+ to_regsubscription
+--------------------
+
+(1 row)
+
+SELECT to_regsubscription('"Nonexistent"');
+ to_regsubscription
+--------------------
+
+(1 row)
+
+SELECT to_regsubscription('foo.bar');
+ERROR: invalid name syntax
diff --git a/src/test/regress/sql/regproc.sql b/src/test/regress/sql/regproc.sql
index df29cfd3e8..19a17b946a 100644
--- a/src/test/regress/sql/regproc.sql
+++ b/src/test/regress/sql/regproc.sql
@@ -9,6 +9,7 @@ CREATE ROLE regress_regrole_test;
-- suppress warning that depends on wal_level
SET client_min_messages = 'ERROR';
CREATE PUBLICATION regress_testpub;
+CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false);
RESET client_min_messages;
-- without schemaname
@@ -54,6 +55,8 @@ SELECT regnamespace('pg_catalog');
SELECT regnamespace('"pg_catalog"');
SELECT regpublication('regress_testpub');
SELECT regpublication('"regress_testpub"');
+SELECT regsubscription('regress_testsub');
+SELECT regsubscription('"regress_testsub"');
SELECT to_regrole('regress_regrole_test');
SELECT to_regrole('"regress_regrole_test"');
@@ -61,11 +64,15 @@ SELECT to_regnamespace('pg_catalog');
SELECT to_regnamespace('"pg_catalog"');
SELECT to_regpublication('regress_testpub');
SELECT to_regpublication('"regress_testpub"');
+SELECT to_regsubscription('regress_testsub');
+SELECT to_regsubscription('"regress_testsub"');
/* If objects don't exist, raise errors. */
DROP ROLE regress_regrole_test;
DROP PUBLICATION regress_testpub;
+ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
+DROP SUBSCRIPTION regress_testsub;
-- without schemaname
@@ -99,6 +106,9 @@ SELECT regnamespace('foo.bar');
SELECT regpublication('Nonexistent');
SELECT regpublication('"Nonexistent"');
SELECT regpublication('foo.bar');
+SELECT regsubscription('Nonexistent');
+SELECT regsubscription('"Nonexistent"');
+SELECT regsubscription('foo.bar');
/* If objects don't exist, return NULL with no error. */
@@ -136,3 +146,6 @@ SELECT to_regnamespace('foo.bar');
SELECT to_regpublication('Nonexistent');
SELECT to_regpublication('"Nonexistent"');
SELECT to_regpublication('foo.bar');
+SELECT to_regsubscription('Nonexistent');
+SELECT to_regsubscription('"Nonexistent"');
+SELECT to_regsubscription('foo.bar');
--
2.25.1
