RE: Tablespaces proposal

[Alexander Zhdanov]

Hello! This is the second part of the discussion on tablespaces. Getting ready to port tablespaces to FB.
Link to the first part: https://sourceforge.net/p/firebird/mailman/firebird-devel/thread/a87823d8-971f-fb4d-a80d-2637549d8426%40ibphoenix.com/#msg37365238

==============================================================================
PROPOSAL

==============================================================================

SYNTAX

=======

1. TABLESPACE

CREATE TABLESPACE [IF NOT EXISTS] <TS NAME> FILE '/path/to/file'

ALTER TABLESPACE <TS NAME> SET FILE [TO] '/path/to/file'

You can specify either an absolute path or a relative path

DROP TABLESPACE [IF EXISTS] <TS NAME>

The development of the INCLUDING CONTENTS option has been postponed.

For an existing tablespace, it is possible to add a comment using the COMMENT ON statement.
COMMENT ON TABLESPACE <TS NAME> IS {'text' | NULL}

2. TABLE

CREATE TABLE ...
[[IN] TABLESPACE {<TS NAME> | PRIMARY}]

It is also possible to specify a tablespace when creating a column or table constraint:

<column/table constraint> ::= ... UNIQUE ... [[IN] TABLESPACE {<TS NAME> | PRIMARY}] | PRIMARY ... [[IN] TABLESPACE {<TS NAME> | PRIMARY}] | REFERENCES ... [[IN] TABLESPACE {<TS NAME> | PRIMARY}] ...

ALTER TABLE <TABLE NAME> SET TABLESPACE [TO] {<TS NAME> | PRIMARY}

The table data will be moved to the specified tablespace or to the main database.
It is also possible to specify a tablespace when adding column or table constraints.

3. INDEX

CREATE INDEX ...
[[IN] TABLESPACE {<TS NAME> | PRIMARY}]

By default, table indexes are created in the same tablespace as the table itself.

ALTER INDEX ...
[SET TABLESPACE [TO] {<TS NAME> | PRIMARY}]

The index data will be moved to the specified tablespace or to the main database.

ODS CHANGES

=============

A new table RDB$TABLESPACES:

RDB$TABLESPACE_ID - INTEGER # internally it will be pagespaceid.
RDB$TABLESPACE_NAME - CHAR (63) # name of a tablespace
RDB$SECURITY_CLASS - CHAR (63) # security class for tablespace
RDB$SYSTEM_FLAG - SMALLINT # reserved
RDB$DESCRIPTION - BLOB TEXT # description of a tablespace
RDB$OWNER_NAME - CHAR (63) # owner of a tablespace
RDB$FILE_NAME - VARCHAR (255) # file where a tablespace data are located
RDB$OFFLINE - BOOLEAN # reserved for future
RDB$READ_ONLY - BOOLEAN # reserved for future

New field in RDB$INDICES:

RDB$TABLESPACE_NAME - CHAR (63)

New field in RDB$RELATION_FIELDS:

RDB$TABLESPACE_NAME - CHAR (63)

New fields in RDB$RELATIONS:

RDB$TABLESPACE_NAME - CHAR (63)
RDB$POINTER_PAGE - INTEGER # a number of the first pointer page of a relation
RDB$ROOT_PAGE - INTEGER # a number of the root page of a relation

These fields are necessary for reliable implementation of moving data pages to another tablespace.
It's a dfw operation with EX database lock. So there are no concurrent changes.
1) copy all data pages
2) switch RDB$POINTER_PAGE and RDB$ROOT_PAGE transactionally
3) Rebuild RDB$PAGES
4) clear old data pages (as post-dfw operation)
It can be interrupted but not resumed.

UTILITIES

========

Logical backup
--------------------
gbak -b works as usual for now. It gets data from a database
transparently working with tablespaces.

Logical restore
--------------------
gbak -c

-ts_map[ping] <path to file>
option is required for correct database recovery if its backup contains tables or indexes saved in tablespaces. To do this, specify the path to file, which consists of lines with two values: the first column is the name of the tablespace, the second column is the new location of the tablespace. You can specify either an absolute path or a relative path.
TS1 /path/to/tablespace1.dat
TS2 /path/to/tablespace2.dat

-ts <tablespace> <path>
allows you to specify the path for the tablespace. You can specify either an absolute path or a relative path. The option can be used as many times as required. It can also be used together with -ts_map.

-ts_orig[inal_paths]
To restore tablespaces to the original paths they were on when the backup was created. It is still possible to override paths for some tablespaces using the -ts and -ts_map options.
This is an explicit option, not a default action.

If you do not specify the above options, when restoring a database that has tablespaces, an error about the inability to determine the path to restore tablespaces will occur.

NBACKUP
--------------------
ALTER DATABASE {BEGIN | END} BACKUP will put not only the main database file, but also all tablespaces into safe copy mode. A delta file will be created for each tablespace.

SHOW
--------------------
SHOW {TABLESPACES | TABLESPACE <TS NAME>}
Displays a list of all ts names in alphabetical order or information about the specified ts

REPLIACTION

==========

There is an apply_tablespaces_ddl parameter for replication. If this parameter is disabled, tablespaces-related DDL statements and CREATE/ALTER TABLE/INDEX clauses will not be applied to the replica. This is used if the replica has its own set of tablespaces or none at all.

If the parameter is enabled, the following options are available:
1) Specify absolute path when creating TS. Then the replica machine should follow the directory hierarchy.
2) Specify relative path when creating TS.
3) The first two options can be problematic if the master and replica are on the same computer. Then there is the following solution:
Store directory aliases and their corresponding real paths in the configuration file for the master and replica.
For example:
db master {
test_dir = /path/for/master
}
db replica {
test_dir = /path/for/replica
}
Then the CREATE TABLESPACE TS1 FILE 'test_dir/file_name' operation will cause the master to create a tablespace using the path /path/for/master and the replica to use /path/for/replica.

DETAILS

==========

pag_header in every tablespace is reserved and may be replaced by a
new page type.
pag_scns and pag_pip are located in every tablespace.
pag_root is located in the tablespace where a table is located.

CONSTRAINTS / PLANS

==========

It's possible to create up to 253 tablespaces.
Operators to move an index or table to a tablespace require an exclusive database lock.
We have plans to create functionality to move blobs to separate tablespaces.

[Dimitry Sibiryakov]

Alexander Zhdanov wrote 09.09.2024 15:15:
> RDB$TABLESPACE_NAME - CHAR (63) # name of a tablespace

Could you finally end this madness and use VARCHAR()?

> RDB$FILE_NAME - VARCHAR (255) # file where a tablespace data are located

Not enough. Path limit on modern OSes is much bugger.

> RDB$OFFLINE - BOOLEAN # reserved for future
> RDB$READ_ONLY - BOOLEAN # reserved for future

New ODS fields is a minor deal, I see no reason to reserve them ahead.

--
WBR, SD.

[Dmitry Yemanov]

10.09.2024 13:14, 'Dimitry Sibiryakov' wrote:

>> RDB$TABLESPACE_NAME - CHAR (63) # name of a tablespace
>
>   Could you finally end this madness and use VARCHAR()?

Only once the team has an explicit decision about that.

>> RDB$FILE_NAME - VARCHAR (255) # file where a tablespace data are located
>
>   Not enough. Path limit on modern OSes is much bugger.

It's a system domain used in all FB databases for secondary files
(including backups) and nobody complained so far. I don't mind it being
changed, but once again, let's have an explicit agreement about that.


Dmitry

[Alex Peshkoff]

On 9/10/24 13:36, Dmitry Yemanov wrote:
> 10.09.2024 13:14, 'Dimitry Sibiryakov' wrote:
>
>>> RDB$TABLESPACE_NAME - CHAR (63) # name of a tablespace
>>
>>    Could you finally end this madness and use VARCHAR()?
>
> Only once the team has an explicit decision about that.

Certainly VARCHAR is more convenient for use, but changing single field
only to be of that type is strange step.

>
>>> RDB$FILE_NAME - VARCHAR (255) # file where a tablespace data are
>>> located
>>
>>    Not enough. Path limit on modern OSes is much bugger.
>
> It's a system domain used in all FB databases for secondary files
> (including backups) and nobody complained so far. I don't mind it
> being changed, but once again, let's have an explicit agreement about
> that.
>

Same for file name length - this to be decided all around system
metadata, not in single place.

[Dimitry Sibiryakov]

Alex Peshkoff wrote 10.09.2024 12:43:
>>>
>>>    Could you finally end this madness and use VARCHAR()?
>>
>> Only once the team has an explicit decision about that.
>
> Certainly VARCHAR is more convenient for use, but changing single field only to
> be of that type is strange step.

"A journey of a thousand miles begins with a single step." Someone has to
make this one otherwise nothing will ever move.
Firebird 6 got major ODS change. That's a good occasion.

--
WBR, SD.

[Денис Симонов]

>> NBACKUP
>> --------------------
>> ALTER DATABASE {BEGIN | END} BACKUP will put not only the main database file, but also all tablespaces into safe copy mode. A delta file will be created for each tablespace.

We have the following syntax to specify the default delta file name and path for it.

ALTER DATABASE 

       ADD DIFFERENCE FILE 'diff_file'

    |  DROP DIFFERENCE FILE

So it turns out that we have to provide the same capability for each tablespace?

вторник, 10 сентября 2024 г. в 13:46:48 UTC+3, Dimitry Sibiryakov:

[Adriano dos Santos Fernandes]

On 09/09/2024 10:15, Alexander Zhdanov wrote:
>
> New field in RDB$RELATION_FIELDS:
>
> RDB$TABLESPACE_NAME - CHAR (63)
>

Why a field has a tablespace? Is it for blobs? (AFAIU this functionality
is not specified and them should not be "reserved" IMO).

> Logical restore
> --------------------
> gbak -c
>
> -ts_map[ping] <path to file>
> option is required for correct database recovery if its backup contains
> tables or indexes saved in tablespaces. To do this, specify the path to
> file, which consists of lines with two values: the first column is the
> name of the tablespace, the second column is the new location of the
> tablespace. You can specify either an absolute path or a relative path.
> TS1 /path/to/tablespace1.dat
> TS2 /path/to/tablespace2.dat
>
> -ts <tablespace> <path>
> allows you to specify the path for the tablespace. You can specify
> either an absolute path or a relative path. The option can be used as
> many times as required. It can also be used together with -ts_map.
>

Can PRIMARY be moved to external tablespace when restoring?

Is PRIMARY listed in RDB$TABLESPACES?


Adriano

[Alexander Zhdanov]

On Wednesday, September 11, 2024 at 12:45:40 PM UTC+3 Adriano dos Santos Fernandes wrote:

On 09/09/2024 10:15, Alexander Zhdanov wrote:
>
> New field in RDB$RELATION_FIELDS:
>
> RDB$TABLESPACE_NAME - CHAR (63)
>

Why a field has a tablespace? Is it for blobs? (AFAIU this functionality
is not specified and them should not be "reserved" IMO).

It's supposed to locate BLOBs in tablespaces but not in this merge request as well. It's not implemented yet because of impossibility to understand what the table BLOBs belongs to. There are some ideas to solve it but not in the initial implementation.

 

> Logical restore
> --------------------
> gbak -c
>
> -ts_map[ping] <path to file>
> option is required for correct database recovery if its backup contains
> tables or indexes saved in tablespaces. To do this, specify the path to
> file, which consists of lines with two values: the first column is the
> name of the tablespace, the second column is the new location of the
> tablespace. You can specify either an absolute path or a relative path.
> TS1 /path/to/tablespace1.dat
> TS2 /path/to/tablespace2.dat
>
> -ts <tablespace> <path>
> allows you to specify the path for the tablespace. You can specify
> either an absolute path or a relative path. The option can be used as
> many times as required. It can also be used together with -ts_map.
>

Can PRIMARY be moved to external tablespace when restoring?

No. The primary database file will always remain the primary file when restoring.

 

Is PRIMARY listed in RDB$TABLESPACES?

No. Primary is the default setting.
Therefore, for example, in the RDB$TABLESPACE_NAME field in RDB$INDICES, if the index is in the primary database file, the value of the RDB$TABLESPACE_NAME field will be <null>.

 

Adriano

[Jim Starkey]

Why not create a tablespace (whatever that is) specifically for blobs?

--
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-devel/4ed0ab5f-16b9-4947-856a-c76e3dc8d068n%40googlegroups.com.

--
Jim Starkey, AmorphousDB, LLC

[Vlad Khorsun]

11.09.2024 18:05, Jim Starkey:

> Why not create a tablespace (whatever that is) specifically for blobs?

Yes, at table and/or database level, but not for every field.

Regards,
Vlad

[Vlad Khorsun]

09.09.2024 16:15, Alexander Zhdanov:

> Hello! This is the second part of the discussion on tablespaces. Getting ready to port tablespaces to FB.
> Link to the first part: https://sourceforge.net/p/firebird/mailman/firebird-devel/thread/a87823d8-971f-fb4d-
> a80d-2637549d8426%40ibphoenix.com/#msg37365238

Sorry, nobody would like to read this and add something from that and from that, etc.
I expect to see not splitted across weeks of prior discussions proposal here.
So, may be you will not like my comments below, it is OK.

> ==============================================================================
> PROPOSAL
>
> ==============================================================================
>
> SYNTAX
>
> =======
>
> 1. TABLESPACE
>
> CREATE TABLESPACE [IF NOT EXISTS] <TS NAME> FILE '/path/to/file'
>
> ALTER TABLESPACE <TS NAME> SET FILE [TO] '/path/to/file'
>
> You can specify either an absolute path or a relative path

And relative path will be relative to... what ?
Will tablespace get additional properties except of file name ?
Will be there predefined tablespaces ? I see PRIMARY, what about TEMPORARY ?

> DROP TABLESPACE [IF EXISTS] <TS NAME>

What happens with objects resided in dropping tablespace ?

> The development of the INCLUDING CONTENTS option has been postponed.

What ?

> For an existing tablespace, it is possible to add a comment using the COMMENT ON statement.
> COMMENT ON TABLESPACE <TS NAME> IS {'text' | NULL}
>
> 2. TABLE
>
> CREATE TABLE ...
> [[IN] TABLESPACE {<TS NAME> | PRIMARY}]

Would be good to explain what is a PRIMARY before referencing it.

> It is also possible to specify a tablespace when creating a column or table constraint:

Even NOT NULL and CHECK constraints ?

> <column/table constraint> ::= ... UNIQUE ... [[IN] TABLESPACE {<TS NAME> | PRIMARY}] | PRIMARY ... [[IN] TABLESPACE {<TS NAME> |
> PRIMARY}] | REFERENCES ... [[IN] TABLESPACE {<TS NAME> | PRIMARY}] ...
>
> ALTER TABLE <TABLE NAME> SET TABLESPACE [TO] {<TS NAME> | PRIMARY}
>
> The table data will be moved to the specified tablespace or to the main database.
> It is also possible to specify a tablespace when adding column or table constraints.

You speak about ALTER TABLE ADD CONSTRAINT statement here, correct ?

> 3. INDEX
>
> CREATE INDEX ...
> [[IN] TABLESPACE {<TS NAME> | PRIMARY}]
>
> By default, table indexes are created in the same tablespace as the table itself.
>
> ALTER INDEX ...
> [SET TABLESPACE [TO] {<TS NAME> | PRIMARY}]
>
> The index data will be moved to the specified tablespace or to the main database.
>
> ODS CHANGES
>
> =============
>
> A new table RDB$TABLESPACES:
>
> RDB$TABLESPACE_ID - INTEGER # internally it will be pagespaceid.
> RDB$TABLESPACE_NAME - CHAR (63) # name of a tablespace
> RDB$SECURITY_CLASS - CHAR (63) # security class for tablespace

What actions with tablespaces will be secured ? What SQL syntax ?

> RDB$SYSTEM_FLAG - SMALLINT # reserved
> RDB$DESCRIPTION - BLOB TEXT # description of a tablespace
> RDB$OWNER_NAME - CHAR (63) # owner of a tablespace

What is a role of tablespace owner ?

> RDB$FILE_NAME - VARCHAR (255) # file where a tablespace data are located
> RDB$OFFLINE - BOOLEAN # reserved for future
> RDB$READ_ONLY - BOOLEAN # reserved for future

Looks like some additional properties

> New field in RDB$INDICES:
>
> RDB$TABLESPACE_NAME - CHAR (63)

Is it nullable or not ?

> New field in RDB$RELATION_FIELDS:
>
> RDB$TABLESPACE_NAME - CHAR (63)

Looks as not necessary, discussed separately

> New fields in RDB$RELATIONS:
>
> RDB$TABLESPACE_NAME - CHAR (63)

Probably, we could reserve also RDB$BLOB_TABLESPACE here

> RDB$POINTER_PAGE - INTEGER # a number of the first pointer page of a relation
> RDB$ROOT_PAGE - INTEGER # a number of the root page of a relation

In most places in code we use IRT or 'index root page' (Ods::index_root_page),
lets' not break its name. It is also important to not confuse IRT with root page
of index B-Tree.

RDB$ROOT_PAGE points to the some page in relation's tablespace. The IRT contains
root pages of every index (index_root_page::irt_repeat::irt_root). How to get the
each index pagespace ID looking at IRT contents ? Or engine should first read
RDB$INDICES ?

> These fields are necessary for reliable implementation of moving data pages to another tablespace.
> It's a dfw operation with EX database lock. So there are no concurrent changes.
> 1) copy all data pages
> 2) switch RDB$POINTER_PAGE and RDB$ROOT_PAGE transactionally
> 3) Rebuild RDB$PAGES

Could you explain a bit more this point ?
Also, should it be also done "transactionally" ?

> 4) clear old data pages (as post-dfw operation)
> It can be interrupted but not resumed.
>
> UTILITIES
>
> ========
>
> Logical backup
> --------------------
> gbak -b works as usual for now. It gets data from a database
> transparently working with tablespaces.
>
> Logical restore
> --------------------
> gbak -c
>
> -ts_map[ping] <path to file>
> option is required for correct database recovery if its backup contains tables or indexes saved in tablespaces. To do this, specify
> the path to file, which consists of lines with two values: the first column is the name of the tablespace, the second column is the
> new location of the tablespace. You can specify either an absolute path or a relative path.
> TS1 /path/to/tablespace1.dat
> TS2 /path/to/tablespace2.dat
>
> -ts <tablespace> <path>
> allows you to specify the path for the tablespace. You can specify either an absolute path or a relative path. The option can be
> used as many times as required. It can also be used together with -ts_map.

What happens if both options contains same tablespace or file ?

> -ts_orig[inal_paths]
> To restore tablespaces to the original paths they were on when the backup was created. It is still possible to override paths for
> some tablespaces using the -ts and -ts_map options.
> This is an explicit option, not a default action.

Will it overwrite existing file(s) ?

> If you do not specify the above options, when restoring a database that has tablespaces, an error about the inability to determine
> the path to restore tablespaces will occur.

Is there a way to merge (redirect) few tablespaces from backup file into single
tablespace ? For example, put all into single database file ?

> NBACKUP
> --------------------
> ALTER DATABASE {BEGIN | END} BACKUP will put not only the main database file, but also all tablespaces into safe copy mode. A delta
> file will be created for each tablespace.

Where all that delta files will reside ?

> SHOW
> --------------------
> SHOW {TABLESPACES | TABLESPACE <TS NAME>}
> Displays a list of all ts names in alphabetical order or information about the specified ts

This one about isql command, correct ?

> REPLIACTION
>
> ==========
>
> There is an apply_tablespaces_ddl parameter for replication. If this parameter is disabled, tablespaces-related DDL statements and
> CREATE/ALTER TABLE/INDEX clauses will not be applied to the replica. This is used if the replica has its own set of tablespaces or
> none at all.

Looks like replication.conf's parameter for replica side, correct ?
Parameter is per-replica and have a boolean type, correct ?

> If the parameter is enabled, the following options are available:
> 1) Specify absolute path when creating TS. Then the replica machine should follow the directory hierarchy.
> 2) Specify relative path when creating TS.

Again: relative to what ?

> 3) The first two options can be problematic if the master and replica are on the same computer. Then there is the following solution:
> Store directory aliases and their corresponding real paths in the configuration file for the master and replica.
> For example:
> db master {
> test_dir = /path/for/master
> }
> db replica {
> test_dir = /path/for/replica
> }

Not sure I got the configuration syntax above. It definitely can not
work in this way, AFAIU. Perhaps you going to use something like:

database = path
{
...
ts_directory_aliases =
{
test_dir1 = /path1
test_dir2 = /path2
...
}
}

Or you have another idea ?

> Then the CREATE TABLESPACE TS1 FILE 'test_dir/file_name' operation will cause the master to create a tablespace using the path /
> path/for/master and the replica to use /path/for/replica.
>
> DETAILS
>
> ==========
>
> pag_header in every tablespace is reserved and may be replaced by a
> new page type.

Don't understand, sorry. Is it reserved or replaced (by what) ?

> pag_scns and pag_pip are located in every tablespace.
> pag_root is located in the tablespace where a table is located.

What about RDB$PAGES ? I see no changes for it (no tablespace field) thus
I suppose every tablespace will have a local instance of RDB$PAGES ?

> CONSTRAINTS / PLANS
>
> ==========
>
> It's possible to create up to 253 tablespaces.
> Operators to move an index or table to a tablespace require an exclusive database lock.
> We have plans to create functionality to move blobs to separate tablespaces.

Thank you for the proposal and for the job done. But, please, take into account
that not everyone is close to the (not published yet) code and knows every detail.
Description above is very draft (I understand) and raises a lot of questions that
you may consider well known or explicitly known, etc ;)

Regards,
Vlad

[Adriano dos Santos Fernandes]

On 11/09/2024 11:11, Alexander Zhdanov wrote:
>
> Can PRIMARY be moved to external tablespace when restoring?
>
>
> No. The primary database file will always remain the primary file when
> restoring.
>  

I think it's important as well to move (in restore) data from or to
primary too.


Adriano

[Dmitry Yemanov]

12.09.2024 03:20, Adriano dos Santos Fernandes wrote:
>
>> Can PRIMARY be moved to external tablespace when restoring?
>>
>>
>> No. The primary database file will always remain the primary file when
>> restoring.
>>
>
> I think it's important as well to move (in restore) data from or to
> primary too.

Do you mean moving some table/index or some tablespace as a whole?

The former is theoretically possible in both directions, but moving a
tablespace is possible only custom->primary, because some page types
(e.g. PIP, TIP, SCN) are not linked to any tablespace and thus must
reside in the primary one, making it mandatory (i.e. you cannot replace
primary with custom tablespace, and I really doubt we need that).


Dmitry

[Dmitry Yemanov]

11.09.2024 18:37, Vlad Khorsun wrote:

>> Why not create a tablespace (whatever that is) specifically for blobs?
>
>   Yes, at table and/or database level, but not for every field.

While the design allows tablespace per every blob field, I believe this
is not the intention. The idea is to share some tablespace between some
blob fields (possibly located in different tables).

E.g. you want to store document scans (from all tables) in one TS, all
JSON documents - in 2nd TS, other blob fields - in 3nd TS and the table
data - in 4rd TS.


Dmitry

[Dmitry Yemanov]

11.09.2024 19:46, Vlad Khorsun wrote:

>> Hello! This is the second part of the discussion on tablespaces.
>> Getting ready to port tablespaces to FB.
>> Link to the first part: https://sourceforge.net/p/firebird/mailman/
>> firebird-devel/thread/a87823d8-971f-fb4d-
>> a80d-2637549d8426%40ibphoenix.com/#msg37365238
>
>   Sorry, nobody would like to read this and add something from that and
> from that, etc.
> I expect to see not splitted across weeks of prior discussions proposal
> here.

This proposal is not the diff ;-) it's basically the original one with
most of questions raised in the initial discussion addressed. The link
is provided just for convenience if someone wants to refresh their memory.

Anyway, comments are appreciated even if they would duplicate what had
already been discussed before ;-)

> Will be there predefined tablespaces ? I see PRIMARY, what about
> TEMPORARY ?

I like the idea about having TEMPORARY predefined.

>> DROP TABLESPACE [IF EXISTS] <TS NAME>
>
>   What happens with objects resided in dropping tablespace ?
>
>> The development of the INCLUDING CONTENTS option has been postponed.
>
>   What ?

DROP is currently rejected if the tablespace contains data. In the
initial proposal it was suggested that some extra clause (INCLUDING
CONTENTS) could be supported to allow that explicitly. Later during
discussion it was suggested to use CASCADE / RESTRICT for that purpose.
Regardless of how it's named, it's still not implemented yet ;-)

> Is there a way to merge (redirect) few tablespaces from backup file
> into single
> tablespace ? For example, put all into single database file ?

Sounds like a useful feature even if not supported yet.

>   Thank you for the proposal and for the job done. But, please, take
> into account
> that not everyone is close to the (not published yet) code and knows
> every detail.
> Description above is very draft (I understand) and raises a lot of
> questions that
> you may consider well known or explicitly known, etc ;)

The point exactly to have the basic things discussed and agreed before
publishing the PR for deeper review. It's surely OK to raise questions
that are not so obvious without the code knowledge.


Dmitry

[Vlad Khorsun]

12.09.2024 9:27, Dmitry Yemanov:

It is interesting at the first look... only :) I doubt it gives any real
benefits and will be widely used in practice. But, if there is really
requirement to put different blob kinds into own tablespaces (can't find a
reason for that) - one could create separate table(s) for blobs.
This approach - move big blobs out of main table - is more or less used
already, afaik.

Regards,
Vlad

[Vlad Khorsun]

12.09.2024 9:50, Dmitry Yemanov:

> 11.09.2024 19:46, Vlad Khorsun wrote:
>
>>> Hello! This is the second part of the discussion on tablespaces. Getting ready to port tablespaces to FB.
>>> Link to the first part: https://sourceforge.net/p/firebird/mailman/ firebird-devel/thread/a87823d8-971f-fb4d-
>>> a80d-2637549d8426%40ibphoenix.com/#msg37365238
>>
>>    Sorry, nobody would like to read this and add something from that and from that, etc.
>> I expect to see not splitted across weeks of prior discussions proposal here.
>
> This proposal is not the diff ;-) it's basically the original one with most of questions raised in the initial discussion addressed.
> The link is provided just for convenience if someone wants to refresh their memory.

But it looks and reads like addition/continuation of previous discussion,
see below.

> Anyway, comments are appreciated even if they would duplicate what had already been discussed before ;-)
>
>> Will be there predefined tablespaces ? I see PRIMARY, what about TEMPORARY ?
>
> I like the idea about having TEMPORARY predefined.
>
>>> DROP TABLESPACE [IF EXISTS] <TS NAME>
>>
>>    What happens with objects resided in dropping tablespace ?
>>
>>> The development of the INCLUDING CONTENTS option has been postponed.
>>
>>    What ?
>
> DROP is currently rejected if the tablespace contains data. In the initial proposal it was suggested that some extra clause
> (INCLUDING CONTENTS) could be supported to allow that explicitly. Later during discussion it was suggested to use CASCADE / RESTRICT
> for that purpose. Regardless of how it's named, it's still not implemented yet ;-)

This is what I meant above - the message refers to the something (INCLUDING
CONTENTS clause) not even mentioned in the message.

>> Is there a way to merge (redirect) few tablespaces from backup file into single
>> tablespace ? For example, put all into single database file ?
>
> Sounds like a useful feature even if not supported yet.
>
>>    Thank you for the proposal and for the job done. But, please, take into account
>> that not everyone is close to the (not published yet) code and knows every detail.
>> Description above is very draft (I understand) and raises a lot of questions that
>> you may consider well known or explicitly known, etc ;)
>
> The point exactly to have the basic things discussed and agreed before publishing the PR for deeper review. It's surely OK to raise
> questions that are not so obvious without the code knowledge.

Thanks for understanding.

Regards,
Vlad

[Adriano dos Santos Fernandes]

On 12/09/2024 03:17, Dmitry Yemanov wrote:
> 12.09.2024 03:20, Adriano dos Santos Fernandes wrote:
>>
>>>      Can PRIMARY be moved to external tablespace when restoring?
>>>
>>>
>>> No. The primary database file will always remain the primary file when
>>> restoring.
>>>  
>>
>> I think it's important as well to move (in restore) data from or to
>> primary too.
>
> Do you mean moving some table/index or some tablespace as a whole?
>

I mean the whole tablespace.

> The former is theoretically possible in both directions, but moving a
> tablespace is possible only custom->primary, because some page types
> (e.g. PIP, TIP, SCN) are not linked to any tablespace and thus must
> reside in the primary one, making it mandatory (i.e. you cannot replace
> primary with custom tablespace, and I really doubt we need that).
>

I'm talking about GBAK restore, so people can rethink their databases
and recreate them, instead of need to use external utilities.


Adriano

[Mark Rotteveel]

I don't like this. This will make querying the system tables more error
prone because you'll need to use IS NOT DISTINCT FROM instead of =. The
primary tablespace should be named, and exist in the metadata, it should
just not be droppable. No use of NULL if it can be avoided.

Mark
--
Mark Rotteveel

[Karol Bieniaszewski]

+1

 

Regards,

Karol Bieniaszewski

--

You received this message because you are subscribed to the Google Groups "firebird-devel" group.

To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-devel/882d8689-5404-4ade-ae75-1d20975e5d3c%40lawinegevaar.nl.

 

[Денис Симонов]

How about providing the ability to introduce UNDO tablespace in future versions?

Will it be possible to set default tablespaces for tables, indexes, BLOBs at the database or schema level?

ALTER DATABASE SET DEFAULT TABLESPACE <ts_name> FOR { TABLES | INDEXES | BLOBS  }
ALTER SCHEMA <schema_name> SET DEFAULT TABLESPACE <ts_name> FOR {TABLES | INDEXES | BLOBS}

четверг, 12 сентября 2024 г. в 14:02:37 UTC+3, Karol Bieniaszewski:

[Alexander Zhdanov]

On Wednesday, September 11, 2024 at 10:01:18 AM UTC+3 Денис Симонов wrote:

>> NBACKUP
>> --------------------
>> ALTER DATABASE {BEGIN | END} BACKUP will put not only the main database file, but also all tablespaces into safe copy mode. A delta file will be created for each tablespace.

We have the following syntax to specify the default delta file name and path for it.

ALTER DATABASE 

       ADD DIFFERENCE FILE 'diff_file'

    |  DROP DIFFERENCE FILE

So it turns out that we have to provide the same capability for each tablespace?

Yes, there will be an appropriate syntax for tablespaces. 

[Alexander Zhdanov]

On Wednesday, September 11, 2024 at 7:46:50 PM UTC+3 Vlad Khorsun wrote:

09.09.2024 16:15, Alexander Zhdanov:
> Hello! This is the second part of the discussion on tablespaces. Getting ready to port tablespaces to FB.
> Link to the first part: https://sourceforge.net/p/firebird/mailman/firebird-devel/thread/a87823d8-971f-fb4d-
> a80d-2637549d8426%40ibphoenix.com/#msg37365238

> 1. TABLESPACE
>
> CREATE TABLESPACE [IF NOT EXISTS] <TS NAME> FILE '/path/to/file'
>
> ALTER TABLESPACE <TS NAME> SET FILE [TO] '/path/to/file'
>
> You can specify either an absolute path or a relative path

And relative path will be relative to... what ?

Relative to the database file

 

Will tablespace get additional properties except of file name ?

What can you recommend that's important?

 

Will be there predefined tablespaces ? I see PRIMARY, what about TEMPORARY ?

No such thing yet. But it's worth thinking about.

 

> CREATE TABLE ...
> [[IN] TABLESPACE {<TS NAME> | PRIMARY}]

Would be good to explain what is a PRIMARY before referencing it.

The PRIMARY keyword can be used as a tablespace name if you want to reference the main database file.

 

> It is also possible to specify a tablespace when creating a column or table constraint:

Even NOT NULL and CHECK constraints ?

No, tablespace can be specified for constraints such as:
unique, primary key, references

 

> <column/table constraint> ::= ... UNIQUE ... [[IN] TABLESPACE {<TS NAME> | PRIMARY}] | PRIMARY ... [[IN] TABLESPACE {<TS NAME> |
> PRIMARY}] | REFERENCES ... [[IN] TABLESPACE {<TS NAME> | PRIMARY}] ...
>
> ALTER TABLE <TABLE NAME> SET TABLESPACE [TO] {<TS NAME> | PRIMARY}
>
> The table data will be moved to the specified tablespace or to the main database.
> It is also possible to specify a tablespace when adding column or table constraints.

You speak about ALTER TABLE ADD CONSTRAINT statement here, correct ?

Yes

 

> A new table RDB$TABLESPACES:
>
> RDB$TABLESPACE_ID - INTEGER # internally it will be pagespaceid.
> RDB$TABLESPACE_NAME - CHAR (63) # name of a tablespace
> RDB$SECURITY_CLASS - CHAR (63) # security class for tablespace

What actions with tablespaces will be secured ? What SQL syntax ?

Only administrators and users with the “CREATE TABLESPACE” privilege can create tablespaces (CREATE TABLESPACE).
Only administrators and users with the “ALTER ANY TABLESPACE” privilege can change ts file paths (ALTER TABLESPACE <name> SET FILE [TO] <file_name>).
Only administrators, domain owners, or users with the ALTER ANY TABLESPACE privilege can comment (COMMENT ON) tablespaces.
Only administrators and users with the “DROP ANY TABLESPACE” privilege can delete tablespaces (DROP TABLESPACE).

 

> RDB$SYSTEM_FLAG - SMALLINT # reserved
> RDB$DESCRIPTION - BLOB TEXT # description of a tablespace
> RDB$OWNER_NAME - CHAR (63) # owner of a tablespace

What is a role of tablespace owner ?

RDB$OWNER_NAME is more of an information field.
Although there is a situation where a user may have the privilege to create a TS but not to modify it. But in that case, he, as the owner, will still have access to modify / delete TS.

 

> New field in RDB$INDICES:
>
> RDB$TABLESPACE_NAME - CHAR (63)

Is it nullable or not ?

Yes, it can be null if the index is in the default tablespace (the main database file). 

 

> RDB$POINTER_PAGE - INTEGER # a number of the first pointer page of a relation
> RDB$ROOT_PAGE - INTEGER # a number of the root page of a relation

In most places in code we use IRT or 'index root page' (Ods::index_root_page),
lets' not break its name. It is also important to not confuse IRT with root page
of index B-Tree.

RDB$ROOT_PAGE points to the some page in relation's tablespace. The IRT contains
root pages of every index (index_root_page::irt_repeat::irt_root). How to get the
each index pagespace ID looking at IRT contents ? Or engine should first read
RDB$INDICES ?

The irt_page_space_id field in index_root_page::irt_repeat was added for this purpose.

 

> These fields are necessary for reliable implementation of moving data pages to another tablespace.
> It's a dfw operation with EX database lock. So there are no concurrent changes.
> 1) copy all data pages
> 2) switch RDB$POINTER_PAGE and RDB$ROOT_PAGE transactionally
> 3) Rebuild RDB$PAGES

Could you explain a bit more this point ?
Also, should it be also done "transactionally" ?

First, you have to move all the pages to another tablespace:

The main steps are:
 - allocate necessary number of pointer pages by extents.
 - allocate the rest of pointer pages by pages.
 - walking through PPs allocate DPs by pages or extents.
 - fix every PP by correcting DP numbers and ppg_next pointer and build a map
 - walking through the map and copy every DP to the new one by fixing
   b_page and f_page numbers.
At the end of work replace records in RDB$PAGES.
   
Then you need to update first pointer page and root page in rdb$relations transactionally.
   
RDB$POINTER_PAGE and RDB$ROOT_PAGE are updated in a transaction because:
Moving table pages to another TS occurs in DFW. In case of failure, we can get the old values of RDB$POINTER_PAGE and RDB$ROOT_PAGE fields.

Then we delete all from RDB$PAGES about the relation to have an ability to understand that we need to restore pages if transaction won't be able to finish successfully.

Then post commit work will clean up old pages. It must be done exactly after commit. If crash is happend the metadata will point to the old page space and new ones will be garbage. Right after commit old pages will be garbage.

 

>
> Logical restore
> --------------------
> gbak -c
>
> -ts_map[ping] <path to file>
> option is required for correct database recovery if its backup contains tables or indexes saved in tablespaces. To do this, specify
> the path to file, which consists of lines with two values: the first column is the name of the tablespace, the second column is the
> new location of the tablespace. You can specify either an absolute path or a relative path.
> TS1 /path/to/tablespace1.dat
> TS2 /path/to/tablespace2.dat
>
> -ts <tablespace> <path>
> allows you to specify the path for the tablespace. You can specify either an absolute path or a relative path. The option can be
> used as many times as required. It can also be used together with -ts_map.

What happens if both options contains same tablespace or file ?

The ratio of the tablespace to its path that is last written will be applied.
For example:
./gbak -c test1.fbk test2.fdb -ts TS1 ts1_new.dat -ts TS1 ts1_new1.dat
the second path will be applied.

 

> -ts_orig[inal_paths]
> To restore tablespaces to the original paths they were on when the backup was created. It is still possible to override paths for
> some tablespaces using the -ts and -ts_map options.
> This is an explicit option, not a default action.

Will it overwrite existing file(s) ?

No
ERROR:Tablespace "TS1" creation error. File "path/ts1.dat" exists

 

> If you do not specify the above options, when restoring a database that has tablespaces, an error about the inability to determine
> the path to restore tablespaces will occur.

Is there a way to merge (redirect) few tablespaces from backup file into single
tablespace ? For example, put all into single database file ?

There's no such thing at the moment. 

 

> NBACKUP
> --------------------
> ALTER DATABASE {BEGIN | END} BACKUP will put not only the main database file, but also all tablespaces into safe copy mode. A delta
> file will be created for each tablespace.

Where all that delta files will reside ?

The alter tablespace syntax will be extended:
ALTER TABLESPACE ADD DIFFERENCE FILE 'diff_file' | DROP DIFFERENCE FILE.
I think the logic would be the same as with the database.
By default they will be located next to the TS file. Defined with “ADD DIFFERENCE FILE” on a certain path.

 

> SHOW
> --------------------
> SHOW {TABLESPACES | TABLESPACE <TS NAME>}
> Displays a list of all ts names in alphabetical order or information about the specified ts

This one about isql command, correct ?

Yes

 

> REPLIACTION
>
> ==========
>
> There is an apply_tablespaces_ddl parameter for replication. If this parameter is disabled, tablespaces-related DDL statements and
> CREATE/ALTER TABLE/INDEX clauses will not be applied to the replica. This is used if the replica has its own set of tablespaces or
> none at all.

Looks like replication.conf's parameter for replica side, correct ?
Parameter is per-replica and have a boolean type, correct ?

Yes, you're right.

Example:
apply_tablespaces_ddl = true

 

> If the parameter is enabled, the following options are available:
> 1) Specify absolute path when creating TS. Then the replica machine should follow the directory hierarchy.
> 2) Specify relative path when creating TS.

Again: relative to what ?

Relative to the database file.

 

> 3) The first two options can be problematic if the master and replica are on the same computer. Then there is the following solution:
> Store directory aliases and their corresponding real paths in the configuration file for the master and replica.
> For example:
> db master {
> test_dir = /path/for/master
> }
> db replica {
> test_dir = /path/for/replica
> }

Not sure I got the configuration syntax above. It definitely can not
work in this way, AFAIU. Perhaps you going to use something like:

database = path
{
...
ts_directory_aliases =
{
test_dir1 = /path1
test_dir2 = /path2
...
}
}

Or you have another idea ?

There is a directories.conf file.
It says the following:

db master {
test_dir = /path/for/master
}

db replica {
test_dir = /path/for/replica
}

a tablespace is created in the master using
create tablespace ts1 file 'test_dir/ts_name'
The db master in the directories.conf file is accessed. It is verified that the test_dir alias has the path /path/for/master. A tablespace is created on this path.
Next, the command is repeated on the replica
create tablespace ts1 file 'test_dir/ts_name'
The db replica in the directories.conf file is accessed. It is verified that the test_dir alias has the path /path/for/replica. A tablespace is created on this path.

 

>
> DETAILS
>
> ==========
>
> pag_header in every tablespace is reserved and may be replaced by a
> new page type.

Don't understand, sorry. Is it reserved or replaced (by what) ?

Now every tablespace has a pag_header page at the very beginning. That is, it is a null page (offset 0). It is really not used in any way yet
But presumably it will be used later.

 

> pag_scns and pag_pip are located in every tablespace.
> pag_root is located in the tablespace where a table is located.

What about RDB$PAGES ? I see no changes for it (no tablespace field) thus
I suppose every tablespace will have a local instance of RDB$PAGES ?

It was not necessary to change RDB$PAGES, because all pages related to one table are located in the same TS. Therefore, it is redundant to add a field for TS here. It is enough to get only the page number from here, and we already know the TS from RDB$RELATIONS. 
RDB$PAGES is present only in the main database file.

 

> CONSTRAINTS / PLANS
>
> ==========
>
> It's possible to create up to 253 tablespaces.
> Operators to move an index or table to a tablespace require an exclusive database lock.
> We have plans to create functionality to move blobs to separate tablespaces.

Thank you for the proposal and for the job done. But, please, take into account
that not everyone is close to the (not published yet) code and knows every detail.
Description above is very draft (I understand) and raises a lot of questions that
you may consider well known or explicitly known, etc ;)

Okay!

 

[Dimitry Sibiryakov]

Alexander Zhdanov wrote 12.09.2024 17:32:
> Thank you for the proposal and for the job done. But, please, take into account
> that not everyone is close to the (not published yet) code and knows every
> detail.
> Description above is very draft (I understand) and raises a lot of questions
> that
> you may consider well known or explicitly known, etc ;)
>
>
> Okay!

It would help if a full current RFC text of this feature was available
somewhere. Some kind of revision history also could be good.
I think github Wiki is a good place for that.

--
WBR, SD.

[Mark Rotteveel]

On 12/09/2024 17:32, Alexander Zhdanov wrote:
> On Wednesday, September 11, 2024 at 7:46:50 PM UTC+3 Vlad Khorsun wrote:
> 09.09.2024 16:15, Alexander Zhdanov:

> > New field in RDB$INDICES:
> >
> > RDB$TABLESPACE_NAME - CHAR (63)
>
> Is it nullable or not ?
>
>
> Yes, it can be null if the index is in the default tablespace (the main
> database file).

I want to repeat that I disagree with the design to have NULL signify
the primary (default) tablespace. Even the primary should be explicitly
named in the metadata.

Mark
--
Mark Rotteveel

[Adriano dos Santos Fernandes]

Agreed. And RDB$SYSTEM_FLAG = 1 in RDB$TABLESPACES should be used for that.

It's the same with schemas. SYSTEM is represented in RDB$SCHEMAS and
system objects references it instead of have null.

My doubt is about MON$ (and virtual tables) records in RDB$RELATIONS.
Should they reference NULL or SYSTEM tablespace?


Adriano

[Mark Rotteveel]

On 13/09/2024 12:39, Adriano dos Santos Fernandes wrote:
> It's the same with schemas. SYSTEM is represented in RDB$SCHEMAS and
> system objects references it instead of have null.
>
> My doubt is about MON$ (and virtual tables) records in RDB$RELATIONS.
> Should they reference NULL or SYSTEM tablespace?

I'd say they shouldn't be NULL either; they belong to said tablespace,
even if they don't actually store data there, IMHO.

Mark
--
Mark Rotteveel

[Adriano dos Santos Fernandes]

On 09/09/2024 10:15, Alexander Zhdanov wrote:

> 1. TABLESPACE
>
> CREATE TABLESPACE [IF NOT EXISTS] <TS NAME> FILE '/path/to/file'
>

Can tablespaces be defined in raw device?


Adriano

[Alexander Zhdanov]

Okay, I think that's a good idea.

 

Mark
--
Mark Rotteveel

[Alexander Zhdanov]

Yes

 

[Alex Peshkoff]

Yes. I see nothing wong with that.

[Jim Starkey]

For what it's worth, for the last two or three database systems, I've
implemented syntactically equivalent CREATE and UPGRADE commands. 
CREATE does the obvious.  UPGRADE created the object if it doesn't exist
and does whatever is necessary to make the object conform to the
command, which may be nothing.  In practice, however, the CREATE form is
only used when one wants to command to fail if the object exists.

[Alexander Zhdanov]

On Tuesday, September 10, 2024 at 1:02:09 PM UTC+3 Alexander Zhdanov wrote:

Hello! This is the second part of the discussion on tablespaces. Getting ready to port tablespaces to FB.
Link to the first part: https://sourceforge.net/p/firebird/mailman/firebird-devel/thread/a87823d8-971f-fb4d-a80d-2637549d8426%40ibphoenix.com/#msg37365238

==============================================================================
PROPOSAL

==============================================================================

SYNTAX

=======

1. TABLESPACE

CREATE TABLESPACE [IF NOT EXISTS] <TS NAME> FILE '/path/to/file'

ALTER TABLESPACE <TS NAME> SET FILE [TO] '/path/to/file'

You can specify either an absolute path or a relative path

DROP TABLESPACE [IF EXISTS] <TS NAME>

The development of the INCLUDING CONTENTS option has been postponed.

For an existing tablespace, it is possible to add a comment using the COMMENT ON statement.

COMMENT ON TABLESPACE <TS NAME> IS {'text' | NULL}

2. TABLE

CREATE TABLE ...

[[IN] TABLESPACE {<TS NAME> | PRIMARY}]

It is also possible to specify a tablespace when creating a column or table constraint:

<column/table constraint> ::= ... UNIQUE ... [[IN] TABLESPACE {<TS NAME> | PRIMARY}] | PRIMARY ... [[IN] TABLESPACE {<TS NAME> | PRIMARY}] | REFERENCES ... [[IN] TABLESPACE {<TS NAME> | PRIMARY}] ...

ALTER TABLE <TABLE NAME> SET TABLESPACE [TO] {<TS NAME> | PRIMARY}

The table data will be moved to the specified tablespace or to the main database.
It is also possible to specify a tablespace when adding column or table constraints.

3. INDEX

CREATE INDEX ...

[[IN] TABLESPACE {<TS NAME> | PRIMARY}]

By default, table indexes are created in the same tablespace as the table itself.

ALTER INDEX ...
[SET TABLESPACE [TO] {<TS NAME> | PRIMARY}]

The index data will be moved to the specified tablespace or to the main database.

ODS CHANGES

=============

A new table RDB$TABLESPACES:

RDB$TABLESPACE_ID - INTEGER # internally it will be pagespaceid.
RDB$TABLESPACE_NAME - CHAR (63) # name of a tablespace
RDB$SECURITY_CLASS - CHAR (63) # security class for tablespace

RDB$SYSTEM_FLAG - SMALLINT # reserved
RDB$DESCRIPTION - BLOB TEXT # description of a tablespace
RDB$OWNER_NAME - CHAR (63) # owner of a tablespace

RDB$FILE_NAME - VARCHAR (255) # file where a tablespace data are located
RDB$OFFLINE - BOOLEAN # reserved for future
RDB$READ_ONLY - BOOLEAN # reserved for future

New field in RDB$INDICES:

RDB$TABLESPACE_NAME - CHAR (63)

New field in RDB$RELATION_FIELDS:

RDB$TABLESPACE_NAME - CHAR (63)

New fields in RDB$RELATIONS:

RDB$TABLESPACE_NAME - CHAR (63)

RDB$POINTER_PAGE - INTEGER # a number of the first pointer page of a relation
RDB$ROOT_PAGE - INTEGER # a number of the root page of a relation

These fields are necessary for reliable implementation of moving data pages to another tablespace.

It's a dfw operation with EX database lock. So there are no concurrent changes.
1) copy all data pages
2) switch RDB$POINTER_PAGE and RDB$ROOT_PAGE transactionally
3) Rebuild RDB$PAGES

4) clear old data pages (as post-dfw operation)
It can be interrupted but not resumed.

UTILITIES

========

Logical backup
--------------------
gbak -b works as usual for now. It gets data from a database
transparently working with tablespaces.

Logical restore
--------------------
gbak -c

-ts_map[ping] <path to file>
option is required for correct database recovery if its backup contains tables or indexes saved in tablespaces. To do this, specify the path to file, which consists of lines with two values: the first column is the name of the tablespace, the second column is the new location of the tablespace. You can specify either an absolute path or a relative path.
TS1 /path/to/tablespace1.dat
TS2 /path/to/tablespace2.dat

-ts <tablespace> <path>
allows you to specify the path for the tablespace. You can specify either an absolute path or a relative path. The option can be used as many times as required. It can also be used together with -ts_map.

-ts_orig[inal_paths]

To restore tablespaces to the original paths they were on when the backup was created. It is still possible to override paths for some tablespaces using the -ts and -ts_map options.
This is an explicit option, not a default action.

If you do not specify the above options, when restoring a database that has tablespaces, an error about the inability to determine the path to restore tablespaces will occur.

NBACKUP

--------------------
ALTER DATABASE {BEGIN | END} BACKUP will put not only the main database file, but also all tablespaces into safe copy mode. A delta file will be created for each tablespace.

SHOW

--------------------
SHOW {TABLESPACES | TABLESPACE <TS NAME>}
Displays a list of all ts names in alphabetical order or information about the specified ts

REPLIACTION

==========

There is an apply_tablespaces_ddl parameter for replication. If this parameter is disabled, tablespaces-related DDL statements and CREATE/ALTER TABLE/INDEX clauses will not be applied to the replica. This is used if the replica has its own set of tablespaces or none at all.

If the parameter is enabled, the following options are available:

1) Specify absolute path when creating TS. Then the replica machine should follow the directory hierarchy.
2) Specify relative path when creating TS.

3) The first two options can be problematic if the master and replica are on the same computer. Then there is the following solution:
Store directory aliases and their corresponding real paths in the configuration file for the master and replica.
For example:
db master {
test_dir = /path/for/master
}
db replica {
test_dir = /path/for/replica
}

Then the CREATE TABLESPACE TS1 FILE 'test_dir/file_name' operation will cause the master to create a tablespace using the path /path/for/master and the replica to use /path/for/replica.

DETAILS

==========

pag_header in every tablespace is reserved and may be replaced by a
new page type.

pag_scns and pag_pip are located in every tablespace.
pag_root is located in the tablespace where a table is located.

CONSTRAINTS / PLANS

==========

It's possible to create up to 253 tablespaces.
Operators to move an index or table to a tablespace require an exclusive database lock.
We have plans to create functionality to move blobs to separate tablespaces.

During the discussion we decided to do the following functionality as well:
1) Add the main database file to the rdb$tablespaces table. Designate it as PRIMARY.
2) Add an option to transfer TS to the main DB file when restoring.
3) TEMPORARY predefined tablespaces.

Future plans:
1) possibility to introduce UNDO tablespace in future versions
We will definitely think about it, but not in this PR.
2) The ability to set default tablespaces for tables, indexes and BLOBs at the database or schema level.
It's not critical that this doesn't exist. Maybe we will think about it and add it later.
 

[Dimitry Sibiryakov]

Alexander Zhdanov wrote 19.09.2024 17:53:
> 1) possibility to introduce UNDO tablespace in future versions
> We will definitely think about it, but not in this PR.

BTW, what do you mean with it? Firebird undo log is kept in memory or
temporary files AFAIR, it is not written into database.

--
WBR, SD.

[Dmitry Yemanov]

19.09.2024 18:57, 'Dimitry Sibiryakov' via firebird-devel wrote:
>
>> 1) possibility to introduce UNDO tablespace in future versions
>> We will definitely think about it, but not in this PR.
>
>   BTW, what do you mean with it? Firebird undo log is kept in memory or
> temporary files AFAIR, it is not written into database.

This may be changed in the future.


Dmitry

[Денис Симонов]

Dimitry Sibiryakov

18:57 (1 час назад) 

– firebir...@googlegroups.com

> Alexander Zhdanov wrote 19.09.2024 17:53:

>> 1) possibility to introduce UNDO tablespace in future versions
>> We will definitely think about it, but not in this PR.

> BTW, what do you mean with it? Firebird undo log is kept in memory or
> temporary files AFAIR, it is not written into database.

> --
> WBR, SD

We are talking about storing backversions. Currently they are stored either next to the primary versions or on a secondary page.

четверг, 19 сентября 2024 г. в 19:23:37 UTC+3, Dmitry Yemanov:

[Alexander Zhdanov]

Hello!
In the attached post I described what functionality we are planning to do.
I suggest to create a PR so you can see the current code. I don't think it's worth delaying the PR while waiting for the planned improvements to be ready. If the functionality is ready, I will add it to the PR. If not, I will create other PRs. 

[Paul Reeves]

On Tue, 29 Oct 2024 00:23:55 -0700 (PDT)
Alexander Zhdanov <alexander...@gmail.com> wrote:

> It's possible to create up to 253 tablespaces.

I wonder if this is enough? Obviously this is more than enough for a human
being to deal with but combined with schemas and dynamic frameworks this
limit could easily be exceeded.

After all, each tablespace in firebird is just a file. It would not surprise
me in the slightest if users started mapping tablespaces to groups of tables.

A quick check shows that DB2 support 500, while Oracle supports 64K. Postgres
claims not to enforce any limit. SQL Server uses the concept of filegroups
and the limitation there is 32k.

Surely we can allocate two bytes for this?


Paul
--
Paul Reeves
https://www.ibphoenix.com
Supporting users of Firebird

[Dmitry Yemanov]

29.10.2024 11:19, 'Paul Reeves' via firebird-devel wrote:
>
> I wonder if this is enough? Obviously this is more than enough for a human
> being to deal with but combined with schemas and dynamic frameworks this
> limit could easily be exceeded.
>
> After all, each tablespace in firebird is just a file. It would not surprise
> me in the slightest if users started mapping tablespaces to groups of tables.
>
> A quick check shows that DB2 support 500, while Oracle supports 64K. Postgres
> claims not to enforce any limit. SQL Server uses the concept of filegroups
> and the limitation there is 32k.
>
> Surely we can allocate two bytes for this?

IIRC, internally tablespace ID is longer and 255 is a temporary limit
that can be shifted later.


Dmitry

[Alexander Zhdanov]

Hello! Posted a PR:
https://github.com/FirebirdSQL/firebird/pull/8314
Looking forward to your review.

[Alexander Zhdanov]

Hello!
Could you please explain what temporary predefined tablespaces are, what are they for? An example of some kind?
And also please note the PR on tablespaces.

[Alexander Zhdanov]

Hello!
I would like to discuss with you the design of existing NBACKUP improvements and the design of planned improvements that I have questions about.

Safe copy mode:
ALTER DATABASE BEGIN BACKUP - a separate delta file is created for each tablespace file.

FIXUP:
-F(IXUP) <database> [-ts_map mapping_file] [-ts ts_name new_ts_path]
The mapping_file file stores the correspondences: the TS name and the path to the new TS file

BACKUP (NO INCREMENTAL):
-B 0 /database_path [/db_backup_file_path] [-ts_map mapping_file] [-ts ts_name backup_file_path]
The mapping_file file stores the following matches: the name of the TS and the path where the backup file will be created

RESTORE (NO INCREMENTAL):
-R <database> <backup_file> [-ts_info_file file_path] [-ts ts_name backup_file_path new_ts_path]
The ts_info_file flag specifies the path to file where the following information is located for each TS:
tablespace name, tablespace backup file, path to restore tablespace

I have no doubts about the above. It doesn't look entirely complicated.

Incremental backup/restore:

We have two versions about incremental backup:
1) Incrementals are created separately for each TS
2) A single file with increments is created. All increments are placed there through a separating page.
That is, at level 0, full backup files are created for the database file and for the TS.
And at level 1 and further, all increments are written to one file.

The main difficulty of the incremental restore is the question of what the creation/deletion of TS will look like at levels 1+.
This will be discussed below.

Restore from increments must have the following information from the user:
The path to which the database file will be restored
Backup file for level 0 database
and for each TS:
TS name
Backup file for TS level 0
path where the TS will be restored
Then all increment files.
And information for each TS that was created in increments:
TS Name
Path where the TS will be restored

This can be done in the form:
nbackup -R <database> <db_backup_file> [-ts_info_file file] [-ts ts_name ts_backup_file new_ts_path]
and additionally we need the -inc flag which will transfer increment files.
The ts_info_file flag specifies a file with information for each TS: ts_name ts_backup_file new_ts_path

Key question:
The difficulty is that the admin needs to know information about which TS were created at what level of backup.
For example, a TS was created at level 1 and deleted at level 2.
Then when backing up to level 3, the information for its restorer potentially does not need to be known (it should still be studied whether it will be possible to make sure that the file does not need to be restored, if at other levels it will be deleted). And for backup to level 1 you need to know information about it.

Variations of incremental restore:

1) Transmit information about which TS existed in the database. Here you need information about all TS that were on level 1,2,3, etc. Because, for example, at the current level all TS can be deleted. And then it is impossible to understand unambiguously on the actual table RDB$TABLESPACES, which TS should be transferred. Here the list can be huge (and all this to remember also potentially a problem). Restore will ignore records of those TS that it does not need.
If a TS is restored from an increment, only 2 values need to be passed for it: the name of the TS and where we are restoring it to.
So it turns out that the -ts_map file will store different strings. Somewhere 2 values, somewhere 3.
But there may be a more complicated picture here:
Level 0 had no TS, level 1 added a TS, level 2 removed a TS, level 3 added a TS with the same name but different settings. When restore the -ts key should have one value for a level 1 and another value for a level 3.

2)  Pass information only about those TS that existed at the time of creation of the level to which the database is being restored. The problem is that the admin may not remember all of this.
Potentially, this can be alleviated by inserting a line into the RDB$BACKUP_HISTORY table for each backup of a page space. So that you can see for which TS the backup was started and at what level.
If there is no access to the table, then there is a problem.

3) Prohibit making level 1+ increments for TS unless a level 0 backup has been made for them.
This is the limitation that is currently in effect for the database.
Then when creating TS it will always be necessary to make a full copy of the database again, but less hassle. And for us and for the admin. The design of the restore will be more friendly.
Here we will have a simpler restore, but we will deprive it of flexibility. That is, it will be impossible to roll back with the help of the restore to the moment when TS was not created, for example.
If TS are not created very often (for example, with the same frequency as the need to update the level 0 backup), then the limitation is not so bad.

We need to figure out which variation of restore is preferable to implement.
Perhaps someone knows how it is implemented in other DBMS?

I hope I have explained the incremental backup/restore problem clearly. I look forward to your comments!

[Denis Simonov]

It is difficult to find a direct analogy in other DBMS. Incremental backups there are made based on transaction logs (Redo Log), which we do not have.

среда, 19 марта 2025 г. в 11:03:24 UTC+3, Alexander Zhdanov: