Re: Identifying user-created objects

On Wed, Feb 26, 2020 at 1:18 PM Masahiko Sawada
<masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
>
> On Thu, 13 Feb 2020 at 17:13, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
> >
> > On Thu, Feb 13, 2020 at 8:32 AM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > >
> > > On Thu, Feb 13, 2020 at 10:30 AM Kyotaro Horiguchi
> > > <horikyota(dot)ntt(at)gmail(dot)com> wrote:
> > > > At Mon, 10 Feb 2020 14:32:44 +0900, Amit Langote <amitlangote09(at)gmail(dot)com> wrote in
> > > > > Agree that ObjectIsUserObject(oid) is easier to read than oid >=
> > > > > FirstNormalObject. I would have not bothered, for example, if it was
> > > > > something like oid >= FirstUserObjectId to begin with.
> > > >
> > > > Aside from the naming, I'm not sure it's sensible to use
> > > > FirstNormalObjectId since I don't see a clear definition or required
> > > > characteristics for "user created objects" is. If we did CREATE
> > > > TABLE, FUNCTION or maybe any objects during single-user mode before
> > > > the first object is created during normal multiuser operation, the
> > > > "user-created(or not?)" object has an OID less than
> > > > FirstNormalObjectId. If such objects are the "user created object", we
> > > > need FirstUserObjectId defferent from FirstNormalObjectId.
> > >
> > > Interesting observation. Connecting to database in --single mode,
> > > whether done using initdb or directly, is always considered
> > > "bootstrapping", so the OIDs from the bootstrapping range are
> > > consumed.
> > >
> > > $ postgres --single -D pgdata postgres
> > >
> > > PostgreSQL stand-alone backend 13devel
> > > backend> create table a (a int);
> > > backend> select 'a'::regclass::oid;
> > > 1: oid (typeid = 26, len = 4, typmod = -1, byval = t)
> > > ----
> > > 1: oid = "14168" (typeid = 26, len = 4, typmod = -1, byval = t)
> > >
> > > Here, FirstBootstrapObjectId < 14168 < FirstNormalObjectId
> >
> > FTR it's also possible to get the same result using binary mode and
> > binary_upgrade_set_next_XXX functions.
> >
> > > Maybe we could document that pg_is_user_object() and its internal
> > > counterpart returns true only for objects that are created during
> > > "normal" multi-user database operation.
> >
> > +1
>
> Agreed.
>
> Attached updated version patch.
>

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?

Regards,
Vignesh
EnterpriseDB: http://www.enterprisedb.com