Thread: Tablespace OID, database OID, relfilenode
Hi!
Quick and to the point:
If i execute
select pg_relation_filepath('pgbench_accounts');
I get
pg_relation_filepath
----------------------
base/5/16398
----------------------
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?
Thanks a ton!
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, 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
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...
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>
>
--
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 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>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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