Thread: regdatabase

Every once in a while, I find myself wanting to use regdatabase for
something like current_database()::regdatabase, and I'm always surprised
when I inevitably rediscover that it doesn't exist.  I only found one
reference to the idea in the archives [0].  So, I have two questions:

* Is there an easier way to get the current database's (or any database's)
  OID that I am overlooking (besides "SELECT oid FROM pg_database...")?

* Would anyone object if I put together some patches to add regdatabase?

[0] https://postgr.es/m/20191109220939.jz55zcc33d3g7h7b%40alap3.anarazel.de

-- 
nathan

> Every once in a while, I find myself wanting to use regdatabase for
> something like current_database()::regdatabase, and I'm always surprised
> when I inevitably rediscover that it doesn't exist.  I only found one
> reference to the idea in the archives [0].  So, I have two questions:
> 
> * Is there an easier way to get the current database's (or any database's)
>   OID that I am overlooking (besides "SELECT oid FROM pg_database...")?

I always do "SELECT oid FROM pg_database...". I have no idea other way.

> * Would anyone object if I put together some patches to add regdatabase?

I think regdatabase is a good idea.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

On Wed, May 07, 2025 at 07:28:02AM +0900, Tatsuo Ishii wrote:
> I think regdatabase is a good idea.

I've also found your reference from the lists of 2019 to be the only
one referring to a regdatabase.

If it means that I will type less by not having to do joins with
pg_database, count me in.

The last time I recall we've discussed about a reg shortcut was
regcollation, which was mentioned as useful for dumps.  Even if we've
finished by not using it in the dump, I tend to find these shortcuts
always useful.

For regdatabase, there would be at least two simplications related to
the dump of subscriptions, where we could switch the queries to the
new grammar for backend versions able to support the new grammar,
meaning that we could remove entirely these parts in 11~12 years once
the versions that do not support the new reg shortcut would be out of
scope.  :D
--
Michael

2025年5月7日(水) 4:29 Nathan Bossart <nathandbossart@gmail.com>:
>
> Every once in a while, I find myself wanting to use regdatabase for
> something like current_database()::regdatabase, and I'm always surprised
> when I inevitably rediscover that it doesn't exist.  I only found one
> reference to the idea in the archives [0].  So, I have two questions:
>
> * Is there an easier way to get the current database's (or any database's)
>   OID that I am overlooking (besides "SELECT oid FROM pg_database...")?
>
> * Would anyone object if I put together some patches to add regdatabase?
>
> [0] https://postgr.es/m/20191109220939.jz55zcc33d3g7h7b%40alap3.anarazel.de

Hah, I put together a patch to implement just that a while back, but
then concluded
for some reason that it would likely be rejected so saved myself the
humiliation of
submitting it...

Attaching patch for reference - it's from mid 2020 so no longer applies. I'll
have a crack at cleaning it up if I get a chance.

Regards

Ian Barwick

On Wed, May 07, 2025 at 09:18:28AM +0900, Ian Lawrence Barwick wrote:
> Hah, I put together a patch to implement just that a while back, but
> then concluded
> for some reason that it would likely be rejected so saved myself the
> humiliation of
> submitting it...
> 
> Attaching patch for reference - it's from mid 2020 so no longer applies. I'll
> have a crack at cleaning it up if I get a chance.

Oh, thanks!

-- 
nathan

On Wed, May 07, 2025 at 08:45:15AM +0900, Michael Paquier wrote:
> For regdatabase, there would be at least two simplications related to
> the dump of subscriptions, where we could switch the queries to the
> new grammar for backend versions able to support the new grammar,
> meaning that we could remove entirely these parts in 11~12 years once
> the versions that do not support the new reg shortcut would be out of
> scope.  :D

Let's not be too hasty...

-- 
nathan

Nathan Bossart <nathandbossart@gmail.com> writes:
> * Would anyone object if I put together some patches to add regdatabase?

The original concept of the reg* types was to implement lookups for
cases that are more complicated than "(SELECT oid FROM pg_foo WHERE
fooname = 'whatever')".  As an example, regprocedure would be
somewhere between seriously painful and impossible to do by hand.
But any potentially-schema-qualified object name is complicated
enough to justify having a reg* type, in the original vision.

However, we've broken that of late with regnamespace and regrole.
regdatabase would be of exactly the same complexity as those
cases.  So I don't see a reason to object, if you think it's
worth the trouble.

            regards, tom lane

2025年5月7日(水) 10:47 Nathan Bossart <nathandbossart@gmail.com>:
>
> On Wed, May 07, 2025 at 09:18:28AM +0900, Ian Lawrence Barwick wrote:
> > Hah, I put together a patch to implement just that a while back, but
> > then concluded
> > for some reason that it would likely be rejected so saved myself the
> > humiliation of
> > submitting it...
> >
> > Attaching patch for reference - it's from mid 2020 so no longer applies. I'll
> > have a crack at cleaning it up if I get a chance.
>
> Oh, thanks!

Version which applies/builds against current HEAD attached. I haven't
yet had a chance to look at the code beyond fixing it, however.

Regards

Ian Barwick

On Thu, May 08, 2025 at 10:38:04PM +0900, Ian Lawrence Barwick wrote:
> Revised patch attached which adds coverage of that and also for the
> "constant of the type reg(role|database) cannot be used here" error.

LGTM.  I've marked it as ready-for-committer [0] and will plan on
committing it as soon as v19 development begins.

[0] https://commitfest.postgresql.org/patch/5746/

-- 
nathan