Hi,
I need to filter out any system catalog objects from SQL,
and I've learned it's not possible to simply filter based on namespace name,
since there are objects such as pg_am that don't have any namespace belonging,
except indirectly via their handler, but since you can define a new access method
using an existing handler from the system catalog, there is no way to distinguish
your user created access handler from the system catalog access handlers
only based on namespace name based filtering.
After some digging I found this
#define FirstNormalObjectId 16384
in src/include/access/transam.h, which pg_dump.c and 14 other files are using at 27 different places in the sources.
Seems to be a popular and important fellow.
I see this value doesn't change often, it was added back in 2005-04-13 in commit 2193a856a229026673cbc56310cd0bddf7b5ea25.
Is it safe to just hard-code in application code needing to know this cut-off value?
Or will we have a Bill Gates "640K ought to be enough for anybody" moment in the foreseeable future,
where this limit needs to be increased?
If there is a risk we will, then maybe we should add a function such as $SUBJECT to expose this value to SQL users who needs it?
I see there has been a related discussion in the thread "Identifying user-created objects"
However, this thread focused on security and wants to know if a specific oid is user defined or not.
I think pg_get_first_normal_oid() would be more useful than pg_is_user_object(oid),
since with pg_get_first_normal_oid() you could do filtering based on oid indexes.
Compare e.g.:
SELECT * FROM pg_class WHERE oid >= pg_get_first_normal_oid()
with..
SELECT * FROM pg_class WHERE pg_is_user_object(oid) IS TRUE
The first query could use the index on pg_class.oid,
whereas I'm not mistaken, the second query would need a seq_scan to evaluate pg_is_user_object() for each oid.
/Joel
