Thread: removing datlastsysoid

Hi,

While reviewing another patch, I noticed that it slightly adjusted the
treatment of datlastsysoid. That made me wonder what datlastsysoid is
used for, so I started poking around and discovered that the answer,
at least insofar as I can determine, is "nothing". The documentation
claims that the value is useful "particularly to pg_dump," which turns
out not to be true any more. Tom's recent commit,
30e7c175b81d53c0f60f6ad12d1913a6d7d77008, to remove pg_dump/pg_dumpall
support for dumping from pre-9.2 servers, removed all remaining uses
of this value from the source tree. It's still maintained. We just
don't do anything with it.

Since that doesn't seem like an especially good idea, PFA a patch to
remove it. Note that, even prior to that commit, it wasn't being used
for anything when dumping modern servers, so it would still have been
OK to remove it from the current system catalog structure. Now,
though, we can remove all references to it.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

Robert Haas <robertmhaas@gmail.com> writes:
> Since that doesn't seem like an especially good idea, PFA a patch to
> remove it. Note that, even prior to that commit, it wasn't being used
> for anything when dumping modern servers, so it would still have been
> OK to remove it from the current system catalog structure. Now,
> though, we can remove all references to it.

+1.  Another reason to get rid of it is that it has nothing to do
with the system OID ranges defined in access/transam.h.

            regards, tom lane

On Mon, Jan 17, 2022 at 3:43 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> +1.  Another reason to get rid of it is that it has nothing to do
> with the system OID ranges defined in access/transam.h.

Agreed. Thanks for looking. Committed.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

On 5/16/22 9:43 AM, Dave Page wrote:
> 
> 
> On Thu, 20 Jan 2022 at 14:03, Robert Haas <robertmhaas@gmail.com 
> <mailto:robertmhaas@gmail.com>> wrote:
> 
>     On Mon, Jan 17, 2022 at 3:43 PM Tom Lane <tgl@sss.pgh.pa.us
>     <mailto:tgl@sss.pgh.pa.us>> wrote:
>      > +1.  Another reason to get rid of it is that it has nothing to do
>      > with the system OID ranges defined in access/transam.h.
> 
>     Agreed. Thanks for looking. Committed.
> 
> 
> So we just ran into this whilst updating pgAdmin to support PG15. How is 
> one supposed to figure out what the last system OID is now from an 
> arbitrary database? pgAdmin uses that value in well over 300 places in 
> its source.

We ran into the same issue in pgBackRest. The old query that initdb used 
to generate these values is no good for PG15 since the template 
databases now have fixed low oids.

Out solution was to use the constant:

#define FirstNormalObjectId        16384

And treat anything below that as a system oid. This constant has not 
changed in a very long time (if ever) but we added it to our list of 
constants to recheck with each release.

We used the initdb query to provide backward compatibility for older 
versions of pgbackrest using PG <= 14, but are using FirstNormalObjectId 
going forward.

See 
https://github.com/pgbackrest/pgbackrest/commit/692fe496bdb5fa6dcffeb9f85b6188ceb1df707a 
for details.

Regards,
-- 
-David
david@pgmasters.net

Dave Page <dpage@pgadmin.org> writes:
> On Mon, 16 May 2022 at 15:06, David Steele <david@pgmasters.net> wrote:
>> Out solution was to use the constant:
>> 
>> #define FirstNormalObjectId             16384
>> 
>> And treat anything below that as a system oid. This constant has not
>> changed in a very long time (if ever) but we added it to our list of
>> constants to recheck with each release.

> Yes, that seems reasonable. Changing that value would very likely break
> pg_upgrade I can imagine, so I suspect it'll stay as it is for a while
> longer.

Yeah, raising that would be extremely painful for pg_upgrade.

I think that when we approach the point where the system OID range
is saturated, we'll give up the principle of system OIDs being
globally unique instead of doing that.  There's no fundamental
reason why unique-per-catalog wouldn't be good enough, and letting
that be the standard would give us many more years of breathing room.

            regards, tom lane

On 5/16/22 10:26 AM, Tom Lane wrote:
> Dave Page <dpage@pgadmin.org> writes:
>> On Mon, 16 May 2022 at 15:06, David Steele <david@pgmasters.net> wrote:
>>> Out solution was to use the constant:
>>>
>>> #define FirstNormalObjectId             16384
>>>
>>> And treat anything below that as a system oid. This constant has not
>>> changed in a very long time (if ever) but we added it to our list of
>>> constants to recheck with each release.
> 
>> Yes, that seems reasonable. Changing that value would very likely break
>> pg_upgrade I can imagine, so I suspect it'll stay as it is for a while
>> longer.
> 
> Yeah, raising that would be extremely painful for pg_upgrade.
> 
> I think that when we approach the point where the system OID range
> is saturated, we'll give up the principle of system OIDs being
> globally unique instead of doing that.  There's no fundamental
> reason why unique-per-catalog wouldn't be good enough, and letting
> that be the standard would give us many more years of breathing room.
I'm in favor of global IDs since they help prevent incorrect joins, but 
agree that what you propose would likely be the least painful solution.

Regards,
-- 
-David
david@pgmasters.net

On 2022-May-16, David Steele wrote:

> On 5/16/22 10:26 AM, Tom Lane wrote:

> > I think that when we approach the point where the system OID range
> > is saturated, we'll give up the principle of system OIDs being
> > globally unique instead of doing that.  There's no fundamental
> > reason why unique-per-catalog wouldn't be good enough, and letting
> > that be the standard would give us many more years of breathing room.
>
> I'm in favor of global IDs since they help prevent incorrect joins, but
> agree that what you propose would likely be the least painful solution.

I just had that property alert me of a bug last week, so yeah.  I wish
there was a way to keep that at least partially -- say use an individual
OID counter for pg_proc (the most populous OID-bearing catalog) and keep
a shared one for all other catalogs.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"No tengo por qué estar de acuerdo con lo que pienso"
                             (Carlos Caszeli)

On 5/16/22 11:19 AM, Alvaro Herrera wrote:
> On 2022-May-16, David Steele wrote:
> 
>> On 5/16/22 10:26 AM, Tom Lane wrote:
> 
>>> I think that when we approach the point where the system OID range
>>> is saturated, we'll give up the principle of system OIDs being
>>> globally unique instead of doing that.  There's no fundamental
>>> reason why unique-per-catalog wouldn't be good enough, and letting
>>> that be the standard would give us many more years of breathing room.
>>
>> I'm in favor of global IDs since they help prevent incorrect joins, but
>> agree that what you propose would likely be the least painful solution.
> 
> I just had that property alert me of a bug last week, so yeah.  I wish
> there was a way to keep that at least partially -- say use an individual
> OID counter for pg_proc (the most populous OID-bearing catalog) and keep
> a shared one for all other catalogs.

I have used a similar strategy before. For example, a global sequence 
for all dimension tables and then a per-table sequence for large fact 
tables.

This is not exactly that scenario, but what you are proposing would keep 
most of the benefit of a global ID. pg_proc is not a very commonly 
joined table for users in my experience.

Now we just need to remember all this ten years from now...

Regards,
-- 
-David
david@pgmasters.net