On Wed, Feb 10, 2021 at 4:43 PM Joel Jacobson <j...@compiler.org> wrote: > > 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" > > > https://www.postgresql.org/message-id/flat/CA%2Bfd4k7Zr%2ByQLYWF3O_KjAJyYYUZFBZ_dFchfBvq5bMj9GgKQw%40mail.gmail.com
As mentioned in that thread, it's still hard to distinguish between user objects and system objects using only OID since we can create objects with OID lower than FirstNormalObjectId by creating objects in single-user mode. It was not enough for security purposes. I think providing concrete use cases of the function would support this proposal. > > 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. Yes. I've also considered the former approach but I prioritized readability and extensibility; it requires prior knowledge for users that OIDs greater than the first normal OID are used during normal multi-user operation. Also in a future if we have similar functions for other OID bounds such as FirstGenbkiObjectId and FirstBootstrapObjectId we will end up doing like 'WHERE oid >= pg_get_first_bootstrap_oid() and oid < pg_get_first_normal_oid()', which is not intuitive. Regards, -- Masahiko Sawada EDB: https://www.enterprisedb.com/
