Tablespace OID, database OID, relfilenode

On Tue, 2023-01-17 at 15:48 +0100, Wiwwo Staff wrote:

Quick and to the point:

If i execute
select pg_relation_filepath('pgbench_accounts');

I get
 pg_relation_filepath
----------------------
 base/5/16398

where "base" is tablespace, but not tablespace OID, nor tablespace name.
How do I link it to get from "base" the tablespace OID?

Or, in other words, given table  pgbench_accounts, how do I get tablespace OID,
database OID and relfilenode so that I can use this info to filter output of pg_waldump?

The tablespace OID is 1663, since "base" is the default tablespace "pg_default".

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Hi Laurenz,
thanks for your answer!
Ok, but I don't think "1663" is a sort of constant value for "base", isn't
it?

Let me clarify my question: how do I get, with a query, from "base" to 1663
(or whatever the value is)?

On Tue, Jan 17, 2023 at 4:01 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Tue, Jan 17, 2023 at 8:36 AM Wiwwo Staff <wiwwo@wiwwo.com> wrote:

Hi Laurenz,
thanks for your answer!
Ok, but I don't think "1663" is a sort of constant value for "base", isn't
it?

Let me clarify my question: how do I get, with a query, from "base" to
1663 (or whatever the value is)?

The system itself just uses an IF block...

https://github.com/postgres/postgres/search?q=DEFAULTTABLESPACE_OID

David J.

On 1/17/23 07:35, Wiwwo Staff wrote:

Hi Laurenz,
thanks for your answer!
Ok, but I don't think "1663" is a sort of constant value for "base",
isn't it?

From source:

backend/catalog/postgres.bki

open pg_tablespace
insert ( 1663 pg_default 10 _null_ _null_ )
insert ( 1664 pg_global 10 _null_ _null_ )

Let me clarify my question: how do I get, with a query, from "base" to
1663 (or whatever the value is)?

select * from pg_tablespace ;
oid | spcname | spcowner | spcacl | spcoptions
------+------------+----------+--------+------------
1663 | pg_default | 10 | NULL | NULL
1664 | pg_global | 10 | NULL | NULL

On Tue, Jan 17, 2023 at 4:01 PM Laurenz Albe <laurenz.albe@cybertec.at
<mailto:laurenz.albe@cybertec.at>> wrote:

On Tue, 2023-01-17 at 15:48 +0100, Wiwwo Staff wrote:

Quick and to the point:

If i execute
select pg_relation_filepath('pgbench_accounts');

I get
 pg_relation_filepath
----------------------
 base/5/16398

where "base" is tablespace, but not tablespace OID, nor

tablespace name.

How do I link it to get from "base" the tablespace OID?

Or, in other words, given table  pgbench_accounts, how do I get

tablespace OID,

database OID and relfilenode so that I can use this info to

filter output of pg_waldump?

The tablespace OID is 1663, since "base" is the default tablespace
"pg_default".

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
<https://www.cybertec-postgresql.com&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

Thank you Adrian, thank you David!
So, it turns out, it *is* indeed an arbitrary constant...
I have been answered many times RTFM, it is actually the first time I have
being answered RTF source code.
(I searched for 1663 in PG docs, it is there, although not really
"intuitive").

Allow me to humbly propose to maybe at least write somewhere in the DOCs in
a clear way: "This is a number you should know, and it is arbitrary, and in
some functions it will be represented as 'base' ".
It is fundamental to know this when one uses pg_waldump, for example.

Not to start a flame war here, just humbly proposing...

Thanks all again!

On Tue, Jan 17, 2023 at 4:44 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 1/17/23 11:49, Wiwwo Staff wrote:

Thank you Adrian, thank you David!
So, it turns out, it *is* indeed an arbitrary constant...
I have been answered many times RTFM, it is actually the first time I
have being answered RTF source code.
(I searched for 1663 in PG docs, it is there, although not really
"intuitive").

Allow me to humbly propose to maybe at least write somewhere in the DOCs
in a clear way: "This is a number you should know, and it is arbitrary,
and in some functions it will be represented as 'base'  ".
It is fundamental to know this when one uses pg_waldump, for example.

Not to start a flame war here, just humbly proposing...

https://www.postgresql.org/docs/current/storage-file-layout.html

"The pg_default tablespace is not accessed through pg_tblspc, but
corresponds to PGDATA/base. Similarly, the pg_global tablespace is not
accessed through pg_tblspc, but corresponds to PGDATA/global."

Thanks all again!

--
Adrian Klaver
adrian.klaver@aklaver.com