On Wed, 4 Mar 2020 at 15:28, vignesh C <vignes...@gmail.com> wrote:
>
> On Wed, Mar 4, 2020 at 9:02 AM Masahiko Sawada
> <masahiko.saw...@2ndquadrant.com> wrote:
> >
> > On Tue, 3 Mar 2020 at 23:33, vignesh C <vignes...@gmail.com> wrote:
> > >
> > > Should we add some check if object exists or not here:
> > > +Datum
> > > +pg_is_user_object(PG_FUNCTION_ARGS)
> > > +{
> > > + Oid oid = PG_GETARG_OID(0);
> > > +
> > > + PG_RETURN_BOOL(ObjectIsUserObject(oid));
> > > +}
> > >
> > > I was trying some scenarios where we pass an object which does not exist:
> > > postgres=# SELECT pg_is_user_object(0);
> > > pg_is_user_object
> > > -------------------
> > > f
> > > (1 row)
> > > postgres=# SELECT pg_is_user_object(222222);
> > > pg_is_user_object
> > > -------------------
> > > t
> > > (1 row)
> > > SELECT pg_is_user_object('pg_class1'::regclass);
> > > ERROR: relation "pg_class1" does not exist
> > > LINE 1: SELECT pg_is_user_object('pg_class1'::regclass);
> > > ^
> > > I felt these behavior seems to be slightly inconsistent.
> > > Thoughts?
> > >
> >
> > Hmm I'm not sure we should existing check in that function. Main use
> > case would be passing an oid of a tuple of a system catalog to that
> > function to check if the given object was created while multi-user
> > mode. So I think this function can assume that the given object id
> > exists. And if we want to do that check, we will end up with checking
> > if the object having that oid in all system catalogs, which is very
> > high cost I think.
> >
> > I suspect perhaps the function name pg_is_user_object led that
> > confusion. That name looks like it checks if the given 'object' is
> > created while multi-user mode. So maybe we can improve it either by
> > renaming to pg_is_user_object_id (or pg_is_user_oid?) or leaving the
> > name but describing in the doc (based on Amit's suggestion in previous
> > mail):
>
> I liked pg_is_user_oid over pg_is_user_object_id but this liking may
> vary from person to person, so I'm still ok if you don't change the
> name. I'm fine about adding the information in the document unless
> someone else feels that this check is required in this function.
>
Attached updated patch that incorporated comments from Amit and Vignesh.
Regards,
--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 323366feb6..3a2c7fa6a6 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18752,6 +18752,10 @@ SELECT collation for ('foo' COLLATE "de_DE");
<primary>pg_get_object_address</primary>
</indexterm>
+ <indexterm>
+ <primary>pg_is_user_object</primary>
+ </indexterm>
+
<para>
<xref linkend="functions-info-object-table"/> lists functions related to
database object identification and addressing.
@@ -18785,6 +18789,14 @@ SELECT collation for ('foo' COLLATE "de_DE");
<entry><parameter>classid</parameter> <type>oid</type>, <parameter>objid</parameter> <type>oid</type>, <parameter>objsubid</parameter> <type>integer</type></entry>
<entry>get address of a database object from its external representation</entry>
</row>
+ <row>
+ <entry><literal><function>pg_is_user_object(<parameter>oid</parameter>)</function></literal></entry>
+ <entry><type>bool</type></entry>
+ <entry>
+ true for OIDs assigned while database is operating in normal multi-user
+ mode, as opposed to single-user mode (see <xref linkend="app-postgres"/>).
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index c5eea7af3f..7888e00d03 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -106,7 +106,7 @@ is_publishable_class(Oid relid, Form_pg_class reltuple)
return reltuple->relkind == RELKIND_RELATION &&
!IsCatalogRelationOid(relid) &&
reltuple->relpersistence == RELPERSISTENCE_PERMANENT &&
- relid >= FirstNormalObjectId;
+ ObjectIsUserObject(relid);
}
/*
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 567eab1e01..e4f00fc309 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -204,7 +204,7 @@ json_categorize_type(Oid typoid,
/* It's probably the general case ... */
*tcategory = JSONTYPE_OTHER;
/* but let's look for a cast to json, if it's not built-in */
- if (typoid >= FirstNormalObjectId)
+ if (ObjectIsUserObject(typoid))
{
Oid castfunc;
CoercionPathType ctype;
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index fea4335951..6869ea9954 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -690,7 +690,7 @@ jsonb_categorize_type(Oid typoid,
* but first let's look for a cast to json (note: not to
* jsonb) if it's not built-in.
*/
- if (typoid >= FirstNormalObjectId)
+ if (ObjectIsUserObject(typoid))
{
Oid castfunc;
CoercionPathType ctype;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 158784474d..15b1ad13eb 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -3202,6 +3202,16 @@ pg_get_function_arg_default(PG_FUNCTION_ARGS)
PG_RETURN_TEXT_P(string_to_text(str));
}
+/*
+ * Return true if the given oid is normal user object
+ */
+Datum
+pg_is_user_object(PG_FUNCTION_ARGS)
+{
+ Oid oid = PG_GETARG_OID(0);
+
+ PG_RETURN_BOOL(ObjectIsUserObject(oid));
+}
/*
* deparse_expression - General utility for deparsing expressions
diff --git a/src/include/access/transam.h b/src/include/access/transam.h
index 6a947b958b..ba79098ed6 100644
--- a/src/include/access/transam.h
+++ b/src/include/access/transam.h
@@ -140,6 +140,9 @@ FullTransactionIdAdvance(FullTransactionId *dest)
#define FirstBootstrapObjectId 12000
#define FirstNormalObjectId 16384
+/* Return true if the oid is assigned during normal multiuser operation */
+#define ObjectIsUserObject(oid) (((Oid) oid) >= FirstNormalObjectId)
+
/*
* VariableCache is a data structure in shared memory that is used to track
* OID and XID assignment state. For largely historical reasons, there is
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 07a86c7b7b..d2f4480c96 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3640,6 +3640,10 @@
proname => 'pg_get_function_arg_default', provolatile => 's',
prorettype => 'text', proargtypes => 'oid int4',
prosrc => 'pg_get_function_arg_default' },
+{ oid => '4192', descr => 'identify normal user object',
+ proname => 'pg_is_user_object', provolatile => 's',
+ prorettype => 'bool', proargtypes => 'oid',
+ prosrc => 'pg_is_user_object' },
{ oid => '1686', descr => 'list of SQL keywords',
proname => 'pg_get_keywords', procost => '10', prorows => '400',
diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out
index d6d1470156..c9f117ac38 100644
--- a/src/test/regress/expected/object_address.out
+++ b/src/test/regress/expected/object_address.out
@@ -492,6 +492,19 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*,
publication relation | | | addr_nsp.gentable in publication addr_pub | t
(49 rows)
+-- identity if the object is built-in or user-defined
+SELECT pg_is_user_object('pg_class'::regclass);
+ pg_is_user_object
+-------------------
+ f
+(1 row)
+
+SELECT pg_is_user_object('trig'::regproc);
+ pg_is_user_object
+-------------------
+ t
+(1 row)
+
---
--- Cleanup resources
---
diff --git a/src/test/regress/sql/object_address.sql b/src/test/regress/sql/object_address.sql
index 8e06248eb5..3b8f0316fe 100644
--- a/src/test/regress/sql/object_address.sql
+++ b/src/test/regress/sql/object_address.sql
@@ -210,6 +210,10 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*,
pg_get_object_address(typ, nms, ioa.args) as addr2
ORDER BY addr1.classid, addr1.objid, addr1.objsubid;
+-- identity if the object is built-in or user-defined
+SELECT pg_is_user_object('pg_class'::regclass);
+SELECT pg_is_user_object('trig'::regproc);
+
---
--- Cleanup resources
---
