Re: [Firebird-devel] Tablespaces proposal
11.10.2021 22:22, Lucas Schatz wrote: Just to clarify, the use of tablespace will be optional, right? Sure. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
11.10.2021 21:23, Roman Simakov wrote:
пн, 11 окт. 2021 г. в 18:42, Vlad Khorsun mailto:hv...@optima.com.ua>>:
11.10.2021 15:17, Roman Simakov wrote:
> SYNTAX
> ===
>
> Note: *MAIN* - is a name of the basic database file.
Please, use *DEFAULT* for default (main) tablespace at "main" database
file.
It is much more consistent with SQL and allows to avoid new unnecessary
keyword.
I'd be happy to agree. Actually we took a look at Oracle syntax. The fact is that DEFAULT means different things. For example,
DEFAULT tablespace for indices is the tablespace of its table. That's why DEFAULT is not such an obvious name as we want it to be.
This is matter of documentation, IMHO. BTW, why you don't like ORACLE's way ?
It looks logical for me. If you want to avoid ambiguity we could introduce
special syntax for the table's sub-objects (blob fields, indices, constraints),
say use keyword TABLE or PARENT as tablespace name, for ex:
CREATE INDEX … AT TABLESPACE { | DEFAULT | TABLE}, or more natural
CREATE INDEX … AT DEFAULT | TABLE TABLESPACE
CREATE INDEX … AT TABLESPACE
in this case, when table's table space is changed, all dependent object should
be changed accordingly
// let me use AT until we agreed to use IN ;)
But MAIN exactly specifies the database itself. We especially have removed DEFAULT from the new version of the proposal because it's
better to explicitly require a tablespace name in the beginning. Later we can add defaults.
I hope you don't require to use TABLESPACE clause every time ? If yes, you
should define defaults anyway ;)
But if you have a good idea how to resolve this issue we will be happy to use
it in the proposal.
See above.
> 1. *CREATE TABLESPACE FILE '/path/to/file'*
>
> 2. *ALTER TABLESPACE FILE '/path/to/file'*
In DDL, ALTER usually combined with ADD | SET | DROP, so let follow this
convention. I.e. ALTER TABLESPACE SET FILE '/path/to/file'
Oracle syntax:
ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
'/u02/oracle/rbdb1/user2.dbf'
TO '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf';
I don't like it and we have only one data file actually and have no preferences
here. We can make [SET] optional.
ALTER somethings SET property TO value - looks as natural way to speak.
Yes, we may introduce RENAME and so on for every property of every altering
object, but it looks too noisy for me.
> 4. CREATE TABLE ...
> (
> ...
> FIELD TYPE CONSTRAINT ... USING INDEX ... *TABLESPACE { |
MAIN}* -- field constraint tablespace
> ...
> CONSTRAINT ... USING INDEX ... *TABLESPACE { | MAIN}* -- table
constraint tablespace
> ...
> )
> *TABLESPACE *
Should we consider optional AT before TABLESPACE in all CREATE
[AT] TABLESPACE
statements ? And, perhaps, optional TO in ALTER SET TABLESPACE
[TO] .
Below I'll use both AT and TO, but not insist on it.
Oracle doesn't use prepositions here.
I already said that I'm not insist on it. BTW, in your case for (3) ORACLE
uses RENAME ... TO :)
...
> SOME DETAILS
> =
...
> It's possible to create up to 253 tablespaces.
Two ID's is reserved for DEFAULT and TEMPORARY tablespaces, correct ?
Currently it's like
const USHORT DB_PAGE_SPACE = 1;
// .. here all tablespace IDs. Keep TRANS_PAGE_SPACE rightafter DB_PAGE_SPACE
const USHORT TRANS_PAGE_SPACE = 255; // is not used for tablespace id
const USHORT TEMP_PAGE_SPACE= 256;
This code was no relation with ODS. And it could be changed as required.
I would reserve some more ID's for future system usage. I don't see it as
limitation for end users.
I see no problem with it. At least for the first version we can use 63
tablespaces for example (64 including the main database) .
We add another constant for the border of the available IDs.
After DY's statement re. tablespace per partition, we should consider
ability to create much more tablespaces.
Regards,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
Roman Simakov wrote 11.10.2021 20:23: > pag_header in every tablespace is reserved and may be replaced by a > new page type. You mean page zero, which is currently always pag_header. I see no reason to change this, so far. Header page uses to describe properties of database and could be extended to describe pagespace when necessary. I have no objections. I suppose it will be used for nbackup implementation at least. BTW, how are you going to prevent a tablespace file from being declared in two or more databases simultaneously? -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
11.10.2021 19:24, Dmitry Yemanov wrote:
11.10.2021 18:41, Vlad Khorsun wrote:
2. *ALTER TABLESPACE FILE '/path/to/file'*
In DDL, ALTER usually combined with ADD | SET | DROP, so let follow this
convention. I.e. ALTER TABLESPACE SET FILE '/path/to/file'
I'm not so sure about "usually", e.g. ALTER INDEX INACTIVE, ALTER DOMAIN TYPE X, etc. As for me, SET is better for cases where DROP
may also be applied. But in fact we have both kinds of SET usage in the grammar and I cannot say which is better.
I said about regular usage, when some property of object is changed.
Of course there is some special cases with special syntax. Many of them
was introduced long time ago, btw ;)
Should we consider optional AT before TABLESPACE in all CREATE [AT]
TABLESPACE statements ?
I'd rather consider IN instead of AT, but also not insisting.
Question for native speaker ;)
5. ALTER TABLE *ALTER TABLESPACE { | MAIN}*
Data of the table will be moved to the specified tablespace or the main
database.
This statement not alters tablespace itself, i.e. there should be SET (and
DEFAULT):
ALTER TABLE SET TABLESPACE [TO] | DEFAULT
+1
ALTER TABLE ALTER COLUMN ... SET TABLESPACE [TO] { | DEFAULT}
Do you mean blobs or vertical partitioning here?
Blobs, as discussed earlier. I'm not consider partitioning, so far.
It's possible to create up to 253 tablespaces.
Two ID's is reserved for DEFAULT and TEMPORARY tablespaces, correct ?
I would reserve some more ID's for future system usage. I don't see it as
limitation for end users.
For regular tablespaces (created explicitly) - sure. But if we think about automatically created partitions, even 253 tablespaces
may become a sad limit.
I see no reason to create tablespace for every partition, but if we going to
support such scenario, we should use larger type for tablespace ID, of course.
nbackup support is postponed.
At this stage I agree, but this must be 1st goal after initial
implementation, IMO.
The first PR may come without it, but I'm against releasing it until nbackup is
supported.
Sure.
Page size is identical for every tablespace and the main database.
Support for many page sizes requires changes in page cache management and should
be considered together. I don't see it as "must have" feature, btw.
Me neither.
Good.
Regards,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
Just to clarify, the use of tablespace will be optional, right?
Anyway it would be a great improvement to Fb!
Thanks!
On Wed, Oct 6, 2021 at 12:33 PM Roman Simakov
wrote:
> Hello, team!
>
> As you might know Red Soft has implemented Tablespace support for
> RedDatabase 4 which is based on Firebird 4 code base.
> I hope we start working on a merge request for Firebird 5 or later but
> previously I would like to get agreement about basic user visible
> things like the syntax and ODS.
>
> PROPOSAL==
> GOALS
> ==
> 1) Extend the current limits on database size
> 2) Keep non active parts of a database on slow disks (having big volume)
> 3) Split indices from the database
> etc
>
> SYNTAX
> ===
>
> 1. CREATE TABLESPACE FILE '/path/to/file'
>
> 2. ALTER TABLESPACE FILE '/path/to/file'
>
> 3. DROP TABLESPACE [INCLUDING CONTENTS]
>
> If the tablespace contains some database objects the behaviour depends
> on INCLUDING CONTENTS clause. if it is specified all database objects
> in the tablespace will be dropped as well. Otherwise there will be an
> error.
>
> 4. CREATE TABLE …
> TABLESPACE
>
> 5. ALTER TABLE ALTER TABLESPACE
>
> Data of the table will be moved to the specified tablespace.
>
> 6. ALTER TABLE DROP TABLESPACE
>
> Data of the table will be moved to the main database.
>
> 7. CREATE INDEX … TABLESPACE { | DEFAULT}
>
> The index will be created:
> - in the main database file if TABLESPACE is omitted.
> - in the table tablespace for TABLESPACE DEFAULT.
> - or in the specified tablespace.
>
> 8. ALTER INDEX ALTER TABLESPACE
>
> Data of the index will be moved to the specified tablespace.
>
> 9. ALTER INDEX DROP TABLESPACE
>
> Data of the index will be moved to the main database.
>
> ODS CHANGES
> =
>
> A new table RDB$TABLESPACES:
>
> RDB$TABLESPACE_ID - SMALLINT
> RDB$TABLESPACE_NAME - CHAR (63)
> RDB$SECURITY_CLASS - CHAR (63)
> RDB$SYSTEM_FLAG - SMALLINT
> RDB$DESCRIPTION - BLOB SUBTYPE TEXT SEGMENT SIZE 80
> RDB$OWNER_NAME - CHAR (63)
> RDB$FILE_NAME - VARCHAR (255)
> RDB$OFFLINE - SMALLINT
> RDB$READ_ONLY - SMALLINT
>
> A new field in RDB$INDICES:
> RDB$TABLESPACE_NAME - CHAR (63)
>
> A 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
> RDB$ROOT_PAGE - INTEGER
>
> UTILITIES
>
>
> Logical backup
>
> gbak -b works as usual for now. It gets data from a database
> transparently working with tablespaces.
>
> Logical restore
>
> gbak -c -TABLESPACE_MAP(PING_FILE) /path/to/file/map/tablespaces
>
> The option TABLESPACE_MAP(PING_FILE) specifies a path to the file
> which maps tablespace names on file names. For example,
> TS1 /path/to/tablespace1.dat
> TS2 /path/to/tablespace2.dat
>
> It allows you to restore tablespace contents to new places.
> If the option is not specified gbak will use old locations for every
> tablespace.
>
> SOME 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
>
> It's possible to create up to 253 tablespaces.
> ==END=
>
> First of all, please let me know whether you agree or not with SYNTAX
> and ODS parts. Other opinions and suggestions are welcome as well.
>
> --
> Roman Simakov
> https://reddatabase.ru
>
>
> Firebird-Devel mailing list, web interface at
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
Roman Simakov wrote 11.10.2021 20:23: > Note: *MAIN* - is a name of the basic database file. Please, use *DEFAULT* for default (main) tablespace at "main" database file. It is much more consistent with SQL and allows to avoid new unnecessary keyword. I'd be happy to agree. Actually we took a look at Oracle syntax. The fact is that DEFAULT means different things. For example, DEFAULT tablespace for indices is the tablespace of its table. That's why DEFAULT is not such an obvious name as we want it to be. But MAIN exactly specifies the database itself. We especially have removed DEFAULT from the new version of the proposal because it's better to explicitly require a tablespace name in the beginning. Later we can add defaults. But if you have a good idea how to resolve this issue we will be happy to use it in the proposal. May I suggest to use word "PRIMARY" then?.. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
> > > >Two ID's is reserved for DEFAULT and TEMPORARY tablespaces, correct ? > > I would reserve some more ID's for future system usage. I don't see it as > > limitation for end users. > > For regular tablespaces (created explicitly) - sure. But if we think > about automatically created partitions, even 253 tablespaces may become > a sad limit. > We can wait partitions) > > >> nbackup support is postponed. > > > >At this stage I agree, but this must be 1st goal after initial > > implementation, IMO. > > The first PR may come without it, but I'm against releasing it until > nbackup is supported. > Good! -- Roman Simakov Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
пн, 11 окт. 2021 г. в 18:42, Vlad Khorsun :
> 11.10.2021 15:17, Roman Simakov wrote:
> > SYNTAX
> > ===
> >
> > Note: *MAIN* - is a name of the basic database file.
>
>Please, use *DEFAULT* for default (main) tablespace at "main" database
> file.
> It is much more consistent with SQL and allows to avoid new unnecessary
> keyword.
>
I'd be happy to agree. Actually we took a look at Oracle syntax. The fact
is that DEFAULT means different things. For example, DEFAULT tablespace for
indices is the tablespace of its table. That's why DEFAULT is not such an
obvious name as we want it to be. But MAIN exactly specifies the database
itself. We especially have removed DEFAULT from the new version of the
proposal because it's better to explicitly require a tablespace name in the
beginning. Later we can add defaults.
But if you have a good idea how to resolve this issue we will be happy to
use it in the proposal.
>
> > 1. *CREATE TABLESPACE FILE '/path/to/file'*
> >
> > 2. *ALTER TABLESPACE FILE '/path/to/file'*
>
>In DDL, ALTER usually combined with ADD | SET | DROP, so let follow this
> convention. I.e. ALTER TABLESPACE SET FILE '/path/to/file'
>
Oracle syntax:
ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
'/u02/oracle/rbdb1/user2.dbf'
TO '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf';
I don't like it and we have only one data file actually and have no
preferences here. We can make [SET] optional.
> > 4. CREATE TABLE ...
> > (
> > ...
> > FIELD TYPE CONSTRAINT ... USING INDEX ... *TABLESPACE { |
> MAIN}* -- field constraint tablespace
> > ...
> > CONSTRAINT ... USING INDEX ... *TABLESPACE { | MAIN}* -- table
> constraint tablespace
> > ...
> > )
> > *TABLESPACE *
>
>Should we consider optional AT before TABLESPACE in all CREATE
> [AT] TABLESPACE
> statements ? And, perhaps, optional TO in ALTER SET
> TABLESPACE [TO] .
> Below I'll use both AT and TO, but not insist on it.
>
Oracle doesn't use prepositions here.
> > if the tablespace name is not specified *MAIN* will be used as default
> tablespace for the table data.
>
>DEFAULT, not MAIN, please.
>
See above.
> > A constraint will use the tablespace of the table if *TABLESPACE* is
> omitted.
> >
> > 5. ALTER TABLE *ALTER TABLESPACE { | MAIN}*
> >
> > Data of the table will be moved to the specified tablespace or the main
> database.
>
>This statement not alters tablespace itself, i.e. there should be SET
> (and DEFAULT):
>
>ALTER TABLE SET TABLESPACE [TO] | DEFAULT
>
Oracle uses ALTER TABLE MOVE TABLESPACE and without
any prepositions as well.
However I agree. SET looks more appropriate here than ALTER. But I still
see no point in prepositions.
Regarding DEFAULT see above.
>
> > 6. CREATE INDEX … *TABLESPACE { | MAIN}*
> >
> > The index will be created in the specified tablespace or the main
> database.
> > If tablespace is omitted the index will be created in the tablespace of
> the table.
>
>CREATE INDEX … [AT] TABLESPACE { | DEFAULT}
>
The same reference to Oracle.
> > 7. ALTER INDEX *ALTER TABLESPACE { | MAIN}*
> >
> > Data of the index will be moved to the specified tablespace or the main
> database.
>
>ALTER INDEX SET TABLESPACE [TO] { | DEFAULT}
>
Agreed about SET.
Prepositions and DEFAULT see above.
>
> It seems we missed
>
>ALTER TABLE ADD CONSTRAINT ... [AT] TABLESPACE { | DEFAULT},
> and
>ALTER TABLE ALTER COLUMN ... SET TABLESPACE [TO] { | DEFAULT}
>
Thank you. I agree with the same remarks.
>
> > SOME DETAILS
> > =
> >
> > pag_header in every tablespace is reserved and may be replaced by a
> > new page type.
>
>You mean page zero, which is currently always pag_header. I see no
> reason
> to change this, so far. Header page uses to describe properties of database
> and could be extended to describe pagespace when necessary.
>
I have no objections. I suppose it will be used for nbackup implementation
at least.
> > pag_scns and pag_pip are located in every tablespace
> > pag_root is located in the tablespace where a table is located
> >
> > It's possible to create up to 253 tablespaces.
>
>Two ID's is reserved for DEFAULT and TEMPORARY tablespaces, correct ?
>
Currently it's like
const USHORT DB_PAGE_SPACE = 1;
// .. here all tablespace IDs. Keep TRANS_PAGE_SPACE right after DB_PAGE_SPACE
const USHORT TRANS_PAGE_SPACE = 255; // is not used for tablespace id
I would reserve some more ID's for future system usage. I don't see it as
> limitation for end users.
>
I see no problem with it. At least for the first version we can use 63
tablespaces for example (64 including the main database) .
We add another constant for the border of the available IDs.
--
Roman Simakov
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
Dmitry Yemanov wrote 11.10.2021 18:24: It's possible to create up to 253 tablespaces. Two ID's is reserved for DEFAULT and TEMPORARY tablespaces, correct ? I would reserve some more ID's for future system usage. I don't see it as limitation for end users. For regular tablespaces (created explicitly) - sure. But if we think about automatically created partitions, even 253 tablespaces may become a sad limit. PageSpaceID currently defined as USHORT and in index root page there is no point to increase size of irp_repeat in any case it will be expanded by four bytes. Besides, proposed RDB$NAMESPACE_ID is SMALLINT. So why this limits is so low? -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Charset of replicated SQL
11.10.2021 18:11, Dimitry Sibiryakov wrote: In which case charset received by IReplTransaction::executeSqlIntl() can be different from charset of attachment received by IReplicatedSession::init()? Ideally, never. I can imagine only the case of cascade replication when the Applier hacks replicator's connection charset. Yes, in this case it may be possible. And I cannot imagine other cases so far. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
11.10.2021 18:41, Vlad Khorsun wrote:
2. *ALTER TABLESPACE FILE '/path/to/file'*
In DDL, ALTER usually combined with ADD | SET | DROP, so let follow this
convention. I.e. ALTER TABLESPACE SET FILE '/path/to/file'
I'm not so sure about "usually", e.g. ALTER INDEX INACTIVE, ALTER DOMAIN
TYPE X, etc. As for me, SET is better for cases where DROP may also be
applied. But in fact we have both kinds of SET usage in the grammar and
I cannot say which is better.
Should we consider optional AT before TABLESPACE in all CREATE
[AT] TABLESPACE statements ?
I'd rather consider IN instead of AT, but also not insisting.
5. ALTER TABLE *ALTER TABLESPACE { | MAIN}*
Data of the table will be moved to the specified tablespace or the
main database.
This statement not alters tablespace itself, i.e. there should be SET
(and DEFAULT):
ALTER TABLE SET TABLESPACE [TO] | DEFAULT
+1
ALTER TABLE ALTER COLUMN ... SET TABLESPACE [TO] { | DEFAULT}
Do you mean blobs or vertical partitioning here?
It's possible to create up to 253 tablespaces.
Two ID's is reserved for DEFAULT and TEMPORARY tablespaces, correct ?
I would reserve some more ID's for future system usage. I don't see it as
limitation for end users.
For regular tablespaces (created explicitly) - sure. But if we think
about automatically created partitions, even 253 tablespaces may become
a sad limit.
nbackup support is postponed.
At this stage I agree, but this must be 1st goal after initial
implementation, IMO.
The first PR may come without it, but I'm against releasing it until
nbackup is supported.
Page size is identical for every tablespace and the main database.
Support for many page sizes requires changes in page cache management
and should
be considered together. I don't see it as "must have" feature, btw.
Me neither.
Dmitry
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
11.10.2021 15:17, Roman Simakov wrote:
Here is the second version of the proposal. It's taken into account all agreements we made during discussion and we'll do it in this
way if there are no objections.
PROPOSAL==
GOALS
==
1) Extend the current limits on database size
2) Keep non active parts of a database on slow disks (having big volume)
3) Split indices from the database
etc
SYNTAX
===
Note: *MAIN* - is a name of the basic database file.
Please, use *DEFAULT* for default (main) tablespace at "main" database file.
It is much more consistent with SQL and allows to avoid new unnecessary keyword.
1. *CREATE TABLESPACE FILE '/path/to/file'*
2. *ALTER TABLESPACE FILE '/path/to/file'*
In DDL, ALTER usually combined with ADD | SET | DROP, so let follow this
convention. I.e. ALTER TABLESPACE SET FILE '/path/to/file'
FILE can contain either an absolute path or a relative to the main database
file.
3. *DROP TABLESPACE [INCLUDING CONTENTS]*
If the tablespace contains some database objects the behaviour depends
on INCLUDING CONTENTS clause. if it is specified all database objects
in the tablespace will be dropped as well. Otherwise there will be an
error.
4. CREATE TABLE ...
(
...
FIELD TYPE CONSTRAINT ... USING INDEX ... *TABLESPACE { | MAIN}* --
field constraint tablespace
...
CONSTRAINT ... USING INDEX ... *TABLESPACE { | MAIN}* -- table
constraint tablespace
...
)
*TABLESPACE *
Should we consider optional AT before TABLESPACE in all CREATE [AT]
TABLESPACE
statements ? And, perhaps, optional TO in ALTER SET TABLESPACE [TO]
.
Below I'll use both AT and TO, but not insist on it.
if the tablespace name is not specified *MAIN* will be used as default
tablespace for the table data.
DEFAULT, not MAIN, please.
A constraint will use the tablespace of the table if *TABLESPACE* is omitted.
5. ALTER TABLE *ALTER TABLESPACE { | MAIN}*
Data of the table will be moved to the specified tablespace or the main
database.
This statement not alters tablespace itself, i.e. there should be SET (and
DEFAULT):
ALTER TABLE SET TABLESPACE [TO] | DEFAULT
6. CREATE INDEX … *TABLESPACE { | MAIN}*
The index will be created in the specified tablespace or the main database.
If tablespace is omitted the index will be created in the tablespace of the
table.
CREATE INDEX … [AT] TABLESPACE { | DEFAULT}
7. ALTER INDEX *ALTER TABLESPACE { | MAIN}*
Data of the index will be moved to the specified tablespace or the main
database.
ALTER INDEX SET TABLESPACE [TO] { | DEFAULT}
It seems we missed
ALTER TABLE ADD CONSTRAINT ... [AT] TABLESPACE { | DEFAULT}, and
ALTER TABLE ALTER COLUMN ... SET TABLESPACE [TO] { | DEFAULT}
ODS CHANGES
=
A new table RDB$TABLESPACES:
RDB$TABLESPACE_ID - SMALLINT
RDB$TABLESPACE_NAME - CHAR (63)
RDB$SECURITY_CLASS - CHAR (63)
RDB$DESCRIPTION - BLOB SUBTYPE TEXT SEGMENT SIZE 80
RDB$OWNER_NAME - CHAR (63)
RDB$FILE_NAME - VARCHAR (255)
A new field in RDB$INDICES:
RDB$TABLESPACE_NAME - CHAR (63)
A 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
RDB$ROOT_PAGE - INTEGER
Add page space id to page number in ods.h:index_root_page.
UTILITIES
Logical backup
gbak -b works as usual for now. It gets data from a database
transparently working with tablespaces.
Logical restore
gbak -c -TABLESPACE_MAP(PING_FILE) /path/to/file/map/tablespaces
The option TABLESPACE_MAP(PING_FILE) specifies a path to the file
which maps tablespace names on file names. For example,
TS1 /path/to/tablespace1
TS2 /path/to/tablespace2
or directly in the command line
gbak -c -ts =/path/to/tablespace1 -ts =...
It allows you to restore tablespace contents to new places.
If the option is not specified gbak will use old locations for every tablespace.
The initial implementation requires explicitly specifying all tablespace files. Later we can add merging tablespaces or default
original paths.
SOME DETAILS
=
pag_header in every tablespace is reserved and may be replaced by a
new page type.
You mean page zero, which is currently always pag_header. I see no reason
to change this, so far. Header page uses to describe properties of database
and could be extended to describe pagespace when necessary.
pag_scns and pag_pip are located in every tablespace
pag_root is located in the tablespace where a table is located
It's possible to create up to 253 tablespaces.
Two ID's is reserved for DEFAULT and TEMPORARY tablespaces, correct ?
I would reserve some more ID's for future system usage. I don't see it as
limitation for end users.
nbackup support is postponed.
At this stage I agree, but this must be 1st goal after initial
implementation, IMO.
Location of BLOB fields is postponed.
Less
[Firebird-devel] Charset of replicated SQL
Hello All. In which case charset received by IReplTransaction::executeSqlIntl() can be different from charset of attachment received by IReplicatedSession::init()? I can imagine only the case of cascade replication when the Applier hacks replicator's connection charset. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
On 11/10/2021 09:17, Roman Simakov wrote: > > nbackup support is postponed. What do you mean? Both features are supposed to be more used by big databases, so they must work together. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
Roman Simakov wrote 11.10.2021 15:23: Cannot be tablespace identification loaded from RDB$INDEXES? Adding it into irp will increase size of irt_repeat by 1/3 decreasing limit of indexes per table, no? I'm sure the answer is the same as why the root page cannot be loaded from RDB$INDEXES (idx_root). And the answer is... -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
пн, 11 окт. 2021 г. в 16:14, Dimitry Sibiryakov : > > Roman Simakov wrote 11.10.2021 14:52: > >>> Add page space id to page number in ods.h:index_root_page. > >>> pag_root is located in the tablespace where a table is located > >> Why is this difference? What does prevent you from putting irp into the > >> tablespace where index is located? > > IRP describes every index of a table and belongs to the table. > >Cannot be tablespace identification loaded from RDB$INDEXES? >Adding it into irp will increase size of irt_repeat by 1/3 decreasing limit > of indexes per table, no? I'm sure the answer is the same as why the root page cannot be loaded from RDB$INDEXES (idx_root). -- Roman Simakov Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
Roman Simakov wrote 11.10.2021 14:52: Add page space id to page number in ods.h:index_root_page. pag_root is located in the tablespace where a table is located Why is this difference? What does prevent you from putting irp into the tablespace where index is located? IRP describes every index of a table and belongs to the table. Cannot be tablespace identification loaded from RDB$INDEXES? Adding it into irp will increase size of irt_repeat by 1/3 decreasing limit of indexes per table, no? -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
пн, 11 окт. 2021 г. в 15:27, Dimitry Sibiryakov :
>
> Roman Simakov wrote 11.10.2021 14:17:
> > FIELD TYPE CONSTRAINT ... USING INDEX ... TABLESPACE { | MAIN} --
> > field constraint tablespace
>
>What's the point of using "MAIN" here? Whole TABLESPACE clause cannot be
> omitted?
>
> > Add page space id to page number in ods.h:index_root_page.
>
> > pag_root is located in the tablespace where a table is located
>Why is this difference? What does prevent you from putting irp into the
> tablespace where index is located?
IRP describes every index of a table and belongs to the table.
--
Roman Simakov
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
Dimitry Sibiryakov wrote 11.10.2021 14:26: What's the point of using "MAIN" here? Nevemind, I've found the answer. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
Roman Simakov wrote 11.10.2021 14:17:
FIELD TYPE CONSTRAINT ... USING INDEX ... TABLESPACE { | MAIN} --
field constraint tablespace
What's the point of using "MAIN" here? Whole TABLESPACE clause cannot be
omitted?
Add page space id to page number in ods.h:index_root_page.
pag_root is located in the tablespace where a table is located
Why is this difference? What does prevent you from putting irp into the
tablespace where index is located?
--
WBR, SD.
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
Here is the second version of the proposal. It's taken into account all
agreements we made during discussion and we'll do it in this way if there
are no objections.
PROPOSAL==
GOALS
==
1) Extend the current limits on database size
2) Keep non active parts of a database on slow disks (having big volume)
3) Split indices from the database
etc
SYNTAX
===
Note: *MAIN* - is a name of the basic database file.
1. *CREATE TABLESPACE FILE '/path/to/file'*
2. *ALTER TABLESPACE FILE '/path/to/file'*
FILE can contain either an absolute path or a relative to the main database
file.
3. *DROP TABLESPACE [INCLUDING CONTENTS]*
If the tablespace contains some database objects the behaviour depends
on INCLUDING CONTENTS clause. if it is specified all database objects
in the tablespace will be dropped as well. Otherwise there will be an
error.
4. CREATE TABLE ...
(
...
FIELD TYPE CONSTRAINT ... USING INDEX ... *TABLESPACE { | MAIN}*
-- field constraint tablespace
...
CONSTRAINT ... USING INDEX ... *TABLESPACE { | MAIN}* -- table
constraint tablespace
...
)
*TABLESPACE *
if the tablespace name is not specified *MAIN* will be used as default
tablespace for the table data.
A constraint will use the tablespace of the table if *TABLESPACE* is
omitted.
5. ALTER TABLE *ALTER TABLESPACE { | MAIN}*
Data of the table will be moved to the specified tablespace or the main
database.
6. CREATE INDEX … *TABLESPACE { | MAIN}*
The index will be created in the specified tablespace or the main database.
If tablespace is omitted the index will be created in the tablespace of the
table.
7. ALTER INDEX * ALTER TABLESPACE { | MAIN}*
Data of the index will be moved to the specified tablespace or the main
database.
ODS CHANGES
=
A new table RDB$TABLESPACES:
RDB$TABLESPACE_ID - SMALLINT
RDB$TABLESPACE_NAME - CHAR (63)
RDB$SECURITY_CLASS - CHAR (63)
RDB$DESCRIPTION - BLOB SUBTYPE TEXT SEGMENT SIZE 80
RDB$OWNER_NAME - CHAR (63)
RDB$FILE_NAME - VARCHAR (255)
A new field in RDB$INDICES:
RDB$TABLESPACE_NAME - CHAR (63)
A 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
RDB$ROOT_PAGE - INTEGER
Add page space id to page number in ods.h:index_root_page.
UTILITIES
Logical backup
gbak -b works as usual for now. It gets data from a database
transparently working with tablespaces.
Logical restore
gbak -c -TABLESPACE_MAP(PING_FILE) /path/to/file/map/tablespaces
The option TABLESPACE_MAP(PING_FILE) specifies a path to the file
which maps tablespace names on file names. For example,
TS1 /path/to/tablespace1
TS2 /path/to/tablespace2
or directly in the command line
gbak -c -ts =/path/to/tablespace1 -ts =...
It allows you to restore tablespace contents to new places.
If the option is not specified gbak will use old locations for every
tablespace.
The initial implementation requires explicitly specifying all tablespace
files. Later we can add merging tablespaces or default original paths.
SOME 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
It's possible to create up to 253 tablespaces.
nbackup support is postponed.
Location of BLOB fields is postponed.
Page size is identical for every tablespace and the main database.
==END=
--
Roman Simakov
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
Den 2021-10-07 kl. 10:56, skrev Molnár Attila: "Tablespaces has meaning for large databases only that don't fit into single storage (terrabytes)." That is not true. It has meaning whatever the programmers meant to use it. It might not be about read performance, but e.g. logical data serparation, backup speedup, etc... Also you should not just thinking in a single huge database, but hundreds or thousands mid-to-large databases as well. That could also occupy huge amount of disk space. (cloud service) In our case our database (~300 Gbyte) has a couple of tables that are orders of magnitude larger than most others (meaning number of records). If tablespaces will support different page sizes in different tablespaces, then we would probably(?) benefit from being able to have larger page size for these tables and their indices, and a smaller one for the smaller tables. Regards, Kjell <>Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB3] result type of negate operation
On 11-10-2021 07:43, Kovalenko Dmitry wrote: The question is whether result of negation should keep the type of source or can it be expanded if needed. Then it would need to expand for all types. And what would be an -INT128? Result type must be same as result of expression "0-source"? No, it is not. The SQL:2016 standards, section 6.29 expression> says: """ Format ::= | | ::= | | ::= [ ] ::= | Syntax Rules 1) Case: [..] c) Otherwise, the declared type of both operands of a dyadic arithmetic operator is exact numeric and the declared type of the result is an implementation-defined exact numeric type, with precision and scale determined as follows: i) Let S1 and S2 be the scale of the first and second operands respectively. ii) The precision of the result of addition and subtraction is implementation-defined, and the scale is the maximum of S1 and S2. [..] 2) The declared type of a is that of the immediately contained . """ Rule 1 establishes that the result of addition and subtraction has an exact numeric type with implementation defined precision, while rule 2 establishes that the type of a negation is the type of the negated expression. In other words -(smallintvalue) must be a SMALLINT, while 0 - (smaillintvalue) can an implementation defined exact numeric type with implementation defined precision (BIGINT in the case of dialect 3). Note that in Dialect 1: 0-smallint -> integer 0-integer -> double Behaviour for dialect 1 can be ignored, because dialect 1 has been deprecated for 20+ years. In dialect 3, subtraction between SMALLINT, INTEGER and BIGINT all results in BIGINT. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
