Re: Tablespace OID, database OID, relfilenode

[Adrian Klaver]

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.kla...@aklaver.com

Re: Tablespace OID, database OID, relfilenode

[Wiwwo Staff]

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 
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  > > 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
> > 
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Tablespace OID, database OID, relfilenode

[Adrian Klaver]

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 > 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





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Tablespace OID, database OID, relfilenode

[David G. Johnston]

On Tue, Jan 17, 2023 at 8:36 AM 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?
>
> 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.

Re: Tablespace OID, database OID, relfilenode

[Wiwwo Staff]

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 
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
>

Re: Tablespace OID, database OID, relfilenode

[Laurenz Albe]

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