subject:"\[Firebird\-devel\] Tablespaces proposal"


Vlad Khorsun wrote 13.10.2021 14:39:

Due to this, I see no reason to replicate physical
layout of master database to the replica. I.e. TABLESPACE-related statements
should not be replicated at all, IMO. Other opinions ?


  I fully agree with not replicated "CREATE/ALTER/DROP TABLESPACE" but what to 
do with "CREATE TABLE ... IN TABLESPACE"?


--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-13 Thread Vlad Khorsun


13.10.2021 13:14, Dimitry Sibiryakov wrote:
   About moving of tablespaces and objects between tablespaces: on primary site it succeeded, on synchronous replica it failed (and 
vice versa). How such situation is going to be handled?


  Tablespace is a concept about physical layout of data. It is closely bound
to the database host. Due to this, I see no reason to replicate physical
layout of master database to the replica. I.e. TABLESPACE-related statements
should not be replicated at all, IMO. Other opinions ?

Regards,
Vlad

PS does anyone know how it is handled by other DBMS ?


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-13 Thread Roman Simakov

ср, 13 окт. 2021 г. в 15:25, Dimitry Sibiryakov :

> Roman Simakov wrote 13.10.2021 14:20:
> > I suppose you mean async replication. I guess like other errors, no?
>
>No, I meant exactly synch replication. "Other errors" in such cases
> leads to
> transaction rollback on both sides. Can tablespace moving to be rolled
> back?
>

For moving relation data pages I think yes. The transaction will be rolled
back and the old data pages continue using. I'm not sure about index data.
We'll check it.

-- 
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 13.10.2021 14:20:

I suppose you mean async replication. I guess like other errors, no?


  No, I meant exactly synch replication. "Other errors" in such cases leads to 
transaction rollback on both sides. Can tablespace moving to be rolled back?


--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-13 Thread Roman Simakov

ср, 13 окт. 2021 г. в 13:15, Dimitry Sibiryakov :

>About moving of tablespaces and objects between tablespaces: on primary
> site
> it succeeded, on synchronous replica it failed (and vice versa). How such
> situation is going to be handled?
>

I suppose you mean async replication. I guess like other errors, no?

-- 
Roman Simakov
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-13 Thread Kjell Rilbe


Den 2021-10-12 kl. 14:05, skrev Dmitry Yemanov:

12.10.2021 13:36, Kjell Rilbe wrote:


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.


That's the feature that our DB would benefit most from probably, 
since some tables are orders of magnitude(s) larger than most others, 
so to be able to have a larger page size for those tables' indices 
only would probably be nice.


Given that FB4 supports 32KB page size, are those indices still deeper 
than 3 levels?


Well, I thought I had upgraded to 32 kbyte page size, but apparently 
not. It's still at 16 kbyte. I'll add to my to do list...


At 16 kbyte page size I seem to have a single index with depth 4. 
Thought there were more, but apparently not.


Thanks for "forcing" me to take a look. :-)

Regards,
Kjell

<>Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

  About moving of tablespaces and objects between tablespaces: on primary site 
it succeeded, on synchronous replica it failed (and vice versa). How such 
situation is going to be handled?


--
  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 12.10.2021 16:30:

It allows you to restore tablespace contents to new places.
If the option is not specified gbak will use old locations for every
tablespace.

I still think this should be an explicit option as well, otherwise
surprising things - for the user - can happen when restoring a backup of
a database you didn't know had tablespaces.

In other words due restore we require file names for the database and
for every tablespace. Sounds reasonable.


  How is it going to work with DatabaseAccess=None setting? Complete 
prohibition of tablespaces in this case?


--
  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 12.10.2021 17:51:> More details you will find in PR and then 
we will be able to discuss it.


  Actually we don't discuss. I just beg you for explanation why it was done 
this way, which alternatives were considered and why they were found 
unacceptable or worse.
  Such explanations indeed should be written in comments inside of the code and 
if they really are there - ok, I'll wait to see them.


--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-12 Thread Omacht András

Hi, 

From: Dimitry Sibiryakov [mailto:s...@ibphoenix.com] 

> 2) Keep non active parts of a database on slow disks (having big 
> volume)

   This part is meaningless because good storages provide storage tiering at 
block level.




For You maybe meaningless, but believe me a lot of users will love it


András


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

вт, 12 окт. 2021 г. в 18:27, Dimitry Sibiryakov :
>
> Roman Simakov wrote 12.10.2021 17:16:
> > I'm not sure that keeping page numbers in a transactional relation is
> > a really excellent idea.
>
>But you added it into RDB$RELATIONS table, no?..

It does not make sense to discuss it without code. Shortly, IRT
changes after moving index data pages. RDB$PAGES changes after moving
relation data pages. RDB$PAGES is the only system table handling in
the system transaction. But it was necessary to have a way for
transactional reading where the moved data is located. RDB$RELATION is
that transactional storage.

More details you will find in PR and then we will be able to discuss it.

-- 
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 12.10.2021 17:34:

They're modified in user transaction(s).


  Nevertheless users have no right for direct modifications so they cannot mess 
with the page number value. If we exclude the field from the query inside of 
AlterXXX methods it should to be safe, right?


--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal


12.10.2021 18:26, Dimitry Sibiryakov wrote:


System tables are operated in system transaction


They're modified in user transaction(s).


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal


Roman Simakov wrote 12.10.2021 17:16:

I'm not sure that keeping page numbers in a transactional relation is
a really excellent idea.


  But you added it into RDB$RELATIONS table, no?..
  System tables are operated in system transaction which effectively uses TIL 
dirty read so there is no real versioning here AFAIK. Besides this data is 
loaded into metadata cache during initial database scan and not going to be 
saved back ever.


--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

вт, 12 окт. 2021 г. в 18:03, Dimitry Sibiryakov :
>
> Roman Simakov wrote 12.10.2021 16:55:
> >> If this field is needed at all. Still there is no answer why tablespace
> >> cannot be identified by name only except "it was done this way 40 years 
> >> ago".
> > Why not to suggest PR for this refactoring?
>
>I understand that you insist on merging your existing code "as is" but I
> think that it is better to write a right code from the beginning than refactor
> it later.

I do not insist on including "as is" and we will adjust our code after
this discussion to be as close as possible (ideally equal) in syntax,
ODS and other. That's why we are discussing it.

But I see no problem with the current implementation. You see. I
assume that's historically but I have no answer why it was done in
such way. I'm sure such experiments quite reasonable but let's not mix
everything in one PR.

I'm not sure that keeping page numbers in a transactional relation is
a really excellent idea.

-- 
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

12.10.2021 13:57, Kjell Rilbe wrote:

Den 2021-10-12 kl. 08:09, skrev Roman Simakov:

пн, 11 окт. 2021 г. в 23:03, Vlad Khorsun mailto:hv...@optima.com.ua>>:

    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 

I had such an idea but didn't want to make up our own way.
If we go Oracle way and use DEFAULT we won't be able to move index data to the main database for indices for a table at;) a 
tablespace. I.e. we can move either to a named tablespace or to a default (table's) tablespace.
It seems Oracle uses the name SYSTEM for the main database. Do you like it? Anyway the main database tablespace has to have a 
name. The question is what name?

MAIN
PRIMARY
SYSTEM
DATABASE TABLESPACE
DATABASE
but definitely it could not be DEFAULT because DEFAULT meaning depends on the 
context.

I think PRIMARY is good because it's already a reserved word and has an 
appropriate meaning.

  Ok, seems we have wide agreement on it.

    // let me use AT until we agreed to use IN ;)

I'd like to get an answer from native speakers, but I think it's like a 
database or file (in a database, in a file).

I'm not a native speaker but I consider myself to be pretty good at English, 
and I'm pretty sure IN is the best word here.

  Let it be IN then, thanks.

Regards,
Vlad

Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

вт, 12 окт. 2021 г. в 18:07, Vlad Khorsun :
>
> 12.10.2021 17:53, Roman Simakov wrote:
> > вт, 12 окт. 2021 г. в 13:11, Vlad Khorsun :
> >>
> >> 12.10.2021 9:09, Roman Simakov wrote:
> >>> пн, 11 окт. 2021 г. в 23:03, Vlad Khorsun  >>> >:
>
> ...
>
> >>>   > 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 ;)
> >>>
> >>>
> >>> Definitely not.
> >>
> >> Hmm... when object is creating and tablespace was not specified, we 
> >> must use something
> >> (by default). Obvious choice is to use 'SYSTEM' tablespace, correct ?
> >
> > For tablespace yes.
>
>For tables, perhaps ?

Yes. Sorry.

> > For indices the default tablespace is a tablespace
> > of its table.
>
>Sure. I meant tables (and other "independent" objects, if any).
>
> ...
>
> >>>  After DY's statement re. tablespace per partition, we should 
> >>> consider
> >>>  ability to create much more tablespaces.
> >>>
> >>>
> >>> I see no problem with increasing the limit. I see problems with reducing 
> >>> it (someone may use them). So let's start from a small
> >>> number 63. When we implement partitions we increase it more consciously.
> >>
> >> I speak about data type used in ODS for tablespace ID. It seems INT 
> >> should be used,
> >> not SMALLINT.
> >
> > You suggest extending it in the PR or we can put it off?
>
>In the PR. It costs nothing but allows to avoid additional ODS changes.

OK.

-- 
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

12.10.2021 17:53, Roman Simakov wrote:

вт, 12 окт. 2021 г. в 13:11, Vlad Khorsun :

12.10.2021 9:09, Roman Simakov wrote:

пн, 11 окт. 2021 г. в 23:03, Vlad Khorsun mailto:hv...@optima.com.ua>>:

...

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

Definitely not.

Hmm... when object is creating and tablespace was not specified, we must 
use something
(by default). Obvious choice is to use 'SYSTEM' tablespace, correct ?

For tablespace yes. 

  For tables, perhaps ?

For indices the default tablespace is a tablespace
of its table.

  Sure. I meant tables (and other "independent" objects, if any).

...

 After DY's statement re. tablespace per partition, we should consider
 ability to create much more tablespaces.

I see no problem with increasing the limit. I see problems with reducing it 
(someone may use them). So let's start from a small
number 63. When we implement partitions we increase it more consciously.

I speak about data type used in ODS for tablespace ID. It seems INT should 
be used,
not SMALLINT.

You suggest extending it in the PR or we can put it off?

  In the PR. It costs nothing but allows to avoid additional ODS changes.

Regards,
Vlad

Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

вт, 12 окт. 2021 г. в 18:01, Adriano dos Santos Fernandes :
>
> In the context of Firebird, IMO it seems weird to have a SYSTEM
> tablespace where user objects are put.
>
> It makes it appear that it would be related to system objects.
>
> I prefer PRIMARY.

I see that most people prefer PRIMARY so I think we include it in the
next version of the proposal.

-- 
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 12.10.2021 16:55:

If this field is needed at all. Still there is no answer why tablespace
cannot be identified by name only except "it was done this way 40 years ago".

Why not to suggest PR for this refactoring?


  I understand that you insist on merging your existing code "as is" but I 
think that it is better to write a right code from the beginning than refactor 
it later.


--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-12 Thread Adriano dos Santos Fernandes

On 12/10/2021 11:53, Roman Simakov wrote:
>>
>>SYSTEM (best) or PRIMARY, imho.
> 
> Oracle's SYSTEM tablespace contains server-wide objects but not only
> database ones. But for now I agree we have two the most suitable
> options: SYSTEM, PRIMARY.
> 

In the context of Firebird, IMO it seems weird to have a SYSTEM
tablespace where user objects are put.

It makes it appear that it would be related to system objects.

I prefer PRIMARY.

Adriano

Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

вт, 12 окт. 2021 г. в 13:17, Dimitry Sibiryakov :
>
> Vlad Khorsun wrote 12.10.2021 12:10:
> >> I see no problem with increasing the limit. I see problems with reducing it
> >> (someone may use them). So let's start from a small number 63. When we
> >> implement partitions we increase it more consciously.
> >
> >I speak about data type used in ODS for tablespace ID. It seems INT 
> > should be
> > used, not SMALLINT.
>
>If this field is needed at all. Still there is no answer why tablespace
> cannot be identified by name only except "it was done this way 40 years ago".

Why not to suggest PR for this refactoring?

-- 
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

вт, 12 окт. 2021 г. в 13:11, Vlad Khorsun :
>
> 12.10.2021 9:09, Roman Simakov wrote:
> > пн, 11 окт. 2021 г. в 23:03, Vlad Khorsun  > >:
> >
> > 11.10.2021 21:23, Roman Simakov wrote:
> >  > 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 
> >
> >
> > I had such an idea but didn't want to make up our own way.
> > If we go Oracle way and use DEFAULT we won't be able to move index data to 
> > the main database for indices for a table at;) a
> > tablespace. I.e. we can move either to a named tablespace or to a default 
> > (table's) tablespace.
>
>Now I understand you better, thanks. But I still against word MAIN :)

OK) It won't be MAIN)

> > It seems Oracle uses the name SYSTEM for the main database. Do you like it? 
> > Anyway the main database tablespace has to have a name.
>
>'SYSTEM' is good choice. All system relations is here. So, engine will 
> always create
> tablespace with name 'SYSTEM', and put all system relations and TIP here, 
> correct ?
> 'SYSTEM' tablespace can't be renamed and could (should?) be marked as system 
> one.
>
> > The question is what name?
> > MAIN
> > PRIMARY
> > SYSTEM
> > DATABASE TABLESPACE
> > DATABASE
>
>SYSTEM (best) or PRIMARY, imho.

Oracle's SYSTEM tablespace contains server-wide objects but not only
database ones. But for now I agree we have two the most suitable
options: SYSTEM, PRIMARY.

> > in this case, when table's table space is changed, all dependent object 
> > should
> > be changed accordingly
> >
> >
> > What do you mean saying "changed"? Now we explicitly set the tablespace 
> > name for an index and when a table is moving leave the index
> > where it was. So subobjects are not bind to the parent. So does Oracle. Do 
> > you suggest moving all dependent objects implicitly? So
> > the question is to bind or not to bind?
>
>Yes. I assumed sub-objects placed in the same tablespace as object itself 
> should be
> moved all together (i.e. bound). But now I think there should be option to 
> [not]move
> sub-objects when object moved into new tablespace.

I think the option is good. We'll add in in the 3th version of the proposal.

> >  > 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 ;)
> >
> >
> > Definitely not.
>
>Hmm... when object is creating and tablespace was not specified, we must 
> use something
> (by default). Obvious choice is to use 'SYSTEM' tablespace, correct ?

For tablespace yes. For indices the default tablespace is a tablespace
of its table.

(Definitely not related to "I hope you don't require to use TABLESPACE
clause every time")

> > The point is that we cannot use DEFAULT as a name for the main database. If 
> > so I decided not to introduce DEFAULT
> > keyword at all. We can add it when we understand how it works and what 
> > defaults are useful.
>
>Ok. So, we should remove all mentions of MAIN in your next version of 
> proposal, correct ?
> If one need to place\move object into main database file (tablespace) name 
> 'SYSTEM' should
> be used explicitly (so far).

Exactly!

> > After DY's statement re. tablespace per partition, we should 
> > consider
> > ability to create much more tablespaces.
> >
> >
> > I see no problem with increasing the limit. I see problems with reducing it 
> > (someone may use them). So let's start from a small
> > number 63. When we implement partitions we increase it more consciously.
>
>I speak about data type used in ODS for tablespace ID. It seems INT should 
> be used,
> not SMALLINT.

You suggest extending it in the PR or we can put it off?

-- 
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

вт, 12 окт. 2021 г. в 11:56, Mark Rotteveel :
>
> On 11-10-2021 14:17, Roman Simakov wrote:
> [..]
>
> > SYNTAX
> > ===
> [..]
> > 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.
>
> I want to repeat my objection against using [INCLUDING CONTENTS] instead
> of {CASCADE | RESTRICT}:
>
> """
> I think using {CASCADE | RESTRICT} instead of [INCLUDING CONTENTS] would
> be better, as that fits with similar definitions in the SQL standard
> (e.g. 11.2 , and Feature F032 "CASCADE drop
> behaviour").
>
> That is, you always have to specify either CASCADE (delete everything
> contained in it), or RESTRICT (only delete if empty).
>
> However, the SQL standard also specifies that all objects then need to
> be dropped with CASCADE (so dependents are dropped as well), and
> Firebird doesn't provide such feature (Feature F032 "CASCADE drop
> behaviour") at the moment, and it is probably necessary for this to work
> correctly, otherwise objects with dependents in other tablespaces will
> result in the drop failing anyway.
>
> Personally, I could live with not providing such feature for now, and
> instead requiring the tablespace to be empty before dropping it.
> """

Sorry, but I think I've answered that question and explained.

> > 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.
>
> I assume you mean the entire clause is option, so: [TABLESPACE
> { | MAIN}]

Sure.

> > 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.
>
> I assume you mean the entire clause is option, so: [TABLESPACE
> { | MAIN}]

Sure.

> > 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 restore
> > 
> [..]
> > It allows you to restore tablespace contents to new places.
> > If the option is not specified gbak will use old locations for every
> > tablespace.
>
> I still think this should be an explicit option as well, otherwise
> surprising things - for the user - can happen when restoring a backup of
> a database you didn't know had tablespaces.

In other words due restore we require file names for the database and
for every tablespace. Sounds reasonable.

-- 
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal


12.10.2021 15:26, Dimitry Sibiryakov wrote:


In this case your vision of partitioning is quite special because in 
others' implementations it has nothing to do with multiple files


Partition is a page set. Different page sets may be surely stored inside 
a single database file, but they may also be stored in different files. 
It makes a lot of sense to nbackup only some partition(s), or store them 
on slow drives, etc -- the same as for tablespaces.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-12 Thread Alex Peshkoff via Firebird-devel


On 10/11/21 10:07 PM, Dimitry Sibiryakov wrote:

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


+1




Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal


Dmitry Yemanov wrote 12.10.2021 14:19:
There's also 4th goal: provide internal infrastructure (splitting page spaces 
into multiple files) to support partitioning later. I expect these two features 
to share a lot.


  In this case your vision of partitioning is quite special because in others' 
implementations it has nothing to do with multiple files and aimed to reduce 
amount of records in index or (sub-)table.


--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal


12.10.2021 14:52, Dimitry Sibiryakov wrote:



GOALS
==
1) Extend the current limits on database size


   Current limit is 16 TB and can be extended without explicit 
tablespace managing by something similar to OS memory mapping technique 
effectively adding some external-sourced bits to current 32 bits page 
number.



2) Keep non active parts of a database on slow disks (having big volume)


   This part is meaningless because good storages provide storage 
tiering at block level.



3) Split indices from the database


   It is useful only if tablespace can have bigger page size reducing 
index depth.


There's also 4th goal: provide internal infrastructure (splitting page 
spaces into multiple files) to support partitioning later. I expect 
these two features to share a lot.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal


12.10.2021 13:36, Kjell Rilbe wrote:


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.


That's the feature that our DB would benefit most from probably, since 
some tables are orders of magnitude(s) larger than most others, so to be 
able to have a larger page size for those tables' indices only would 
probably be nice.


Given that FB4 supports 32KB page size, are those indices still deeper 
than 3 levels?



Dmitry


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:

GOALS
==
1) Extend the current limits on database size


  Current limit is 16 TB and can be extended without explicit tablespace 
managing by something similar to OS memory mapping technique effectively adding 
some external-sourced bits to current 32 bits page number.



2) Keep non active parts of a database on slow disks (having big volume)


  This part is meaningless because good storages provide storage tiering at 
block level.



3) Split indices from the database


  It is useful only if tablespace can have bigger page size reducing index 
depth.

--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-12 Thread Kjell Rilbe


Den 2021-10-11 kl. 21:07, skrev Dimitry Sibiryakov:

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


+1
Good thinking Dimitry! Using "default" seems to me like a really bad 
idea, for the reasons pointed out.


Regards,
Kjell

<>Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-12 Thread Kjell Rilbe

Den 2021-10-12 kl. 08:09, skrev Roman Simakov:
пн, 11 окт. 2021 г. в 23:03, Vlad Khorsun >:

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 

I had such an idea but didn't want to make up our own way.
If we go Oracle way and use DEFAULT we won't be able to move index 
data to the main database for indices for a table at;) a tablespace. 
I.e. we can move either to a named tablespace or to a default 
(table's) tablespace.
It seems Oracle uses the name SYSTEM for the main database. Do you 
like it? Anyway the main database tablespace has to have a name. The 
question is what name?

MAIN
PRIMARY
SYSTEM
DATABASE TABLESPACE
DATABASE
but definitely it could not be DEFAULT because DEFAULT meaning depends 
on the context.

I think PRIMARY is good because it's already a reserved word and has an 
appropriate meaning.

// let me use AT until we agreed to use IN ;)

I'd like to get an answer from native speakers, but I think it's like 
a database or file (in a database, in a file).

I'm not a native speaker but I consider myself to be pretty good at 
English, and I'm pretty sure IN is the best word here.

   ALTER somethings SET property TO value - looks as natural way
to speak.

I don't mind but native speakers - say your words :)

I see nothing wrong with it and I don't have any better suggestion.

   Yes, we may introduce RENAME and so on for every property of
every altering
object, but it looks too noisy for me.

+1. I hope you can avoid extra words like that.

/Kjell

<>Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-12 Thread Kjell Rilbe


Den 2021-10-11 kl. 17:41, skrev Vlad Khorsun:

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.


Good point, but from a natural language perspective, "default" seems to 
indicate the same as if that clause is left out. For indices in tables, 
that would be the table's tablespace, not the main database file. So the 
keyword "default" is not the best choice here i.m.h.o.


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.
Again, from a natural language perspective, you put something IN 
(inside) a space, not AT (next to?) a space. So, if optional 
prepositions are introduced, I would recommend "in" rather than "at" in 
this case. But maybe this will cause problems considering other uses of 
the keyword "in" in SQL?



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.


That's the feature that our DB would benefit most from probably, since 
some tables are orders of magnitude(s) larger than most others, so to be 
able to have a larger page size for those tables' indices only would 
probably be nice.


Regards,
Kjell

<>Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal


Vlad Khorsun wrote 12.10.2021 12:10:
I see no problem with increasing the limit. I see problems with reducing it 
(someone may use them). So let's start from a small number 63. When we 
implement partitions we increase it more consciously.


   I speak about data type used in ODS for tablespace ID. It seems INT should be 
used, not SMALLINT.


  If this field is needed at all. Still there is no answer why tablespace 
cannot be identified by name only except "it was done this way 40 years ago".


--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal


12.10.2021 12:02, Mark Rotteveel wrote:

On 11-10-2021 17:41, Vlad Khorsun wrote:

11.10.2021 15:17, Roman Simakov wrote:

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.


Technically, I think MAIN doesn't have to be a keyword, it can be the object name of the tablespace that is the first database file. 


  Agree. I just don't like word MAIN for this purpose :)


It will require some extra handling though, because you can't alter MAIN 
opposed to other tablespaces.


  Sure.

Regards,
Vlad


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

12.10.2021 9:09, Roman Simakov wrote:

пн, 11 окт. 2021 г. в 23:03, Vlad Khorsun mailto:hv...@optima.com.ua>>:

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 

I had such an idea but didn't want to make up our own way.
If we go Oracle way and use DEFAULT we won't be able to move index data to the main database for indices for a table at;) a 
tablespace. I.e. we can move either to a named tablespace or to a default (table's) tablespace.

  Now I understand you better, thanks. But I still against word MAIN :)

It seems Oracle uses the name SYSTEM for the main database. Do you like it? Anyway the main database tablespace has to have a name. 

  'SYSTEM' is good choice. All system relations is here. So, engine will always 
create
tablespace with name 'SYSTEM', and put all system relations and TIP here, 
correct ?
'SYSTEM' tablespace can't be renamed and could (should?) be marked as system 
one.

The question is what name?
MAIN
PRIMARY
SYSTEM
DATABASE TABLESPACE
DATABASE

  SYSTEM (best) or PRIMARY, imho.

but definitely it could not be DEFAULT because DEFAULT meaning depends on the 
context.

  Ok.

in this case, when table's table space is changed, all dependent object 
should
be changed accordingly

What do you mean saying "changed"? Now we explicitly set the tablespace name for an index and when a table is moving leave the index 
where it was. So subobjects are not bind to the parent. So does Oracle. Do you suggest moving all dependent objects implicitly? So 
the question is to bind or not to bind?

  Yes. I assumed sub-objects placed in the same tablespace as object itself 
should be
moved all together (i.e. bound). But now I think there should be option to 
[not]move
sub-objects when object moved into new tablespace.

// let me use AT until we agreed to use IN ;)

I'd like to get an answer from native speakers, but I think it's like a 
database or file (in a database, in a file).

  No problem :)

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

Definitely not. 

  Hmm... when object is creating and tablespace was not specified, we must use 
something
(by default). Obvious choice is to use 'SYSTEM' tablespace, correct ?

The point is that we cannot use DEFAULT as a name for the main database. If so I decided not to introduce DEFAULT 
keyword at all. We can add it when we understand how it works and what defaults are useful.

  Ok. So, we should remove all mentions of MAIN in your next version of 
proposal, correct ?
If one need to place\move object into main database file (tablespace) name 
'SYSTEM' should
be used explicitly (so far).

...

    After DY's statement re. tablespace per partition, we should consider
ability to create much more tablespaces.

I see no problem with increasing the limit. I see problems with reducing it (someone may use them). So let's start from a small 
number 63. When we implement partitions we increase it more consciously.

  I speak about data type used in ODS for tablespace ID. It seems INT should be 
used,
not SMALLINT.

Regards,
Vlad

Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal


Mark Rotteveel wrote 12.10.2021 10:55:

It allows you to restore tablespace contents to new places.
If the option is not specified gbak will use old locations for every tablespace.


I still think this should be an explicit option as well, otherwise surprising 
things - for the user - can happen when restoring a backup of a database you 
didn't know had tablespaces.


  "Surprising" is a weak word. Imagine a backup of a database with absolute 
file paths restored to the same host as original database. People often do that 
to check backup's consistency.
  What is worse, imagine a malicious backup with forged tablespace names like 
/etc/shadow.


--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-12 Thread Mark Rotteveel


On 11-10-2021 17:41, Vlad Khorsun wrote:

11.10.2021 15:17, Roman Simakov wrote:

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.


Technically, I think MAIN doesn't have to be a keyword, it can be the 
object name of the tablespace that is the first database file. It will 
require some extra handling though, because you can't alter MAIN opposed 
to other tablespaces.


Mark
--
Mark Rotteveel


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-12 Thread Mark Rotteveel


On 11-10-2021 14:17, Roman Simakov wrote:
[..]


SYNTAX
===

[..]

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.


I want to repeat my objection against using [INCLUDING CONTENTS] instead 
of {CASCADE | RESTRICT}:


"""
I think using {CASCADE | RESTRICT} instead of [INCLUDING CONTENTS] would 
be better, as that fits with similar definitions in the SQL standard 
(e.g. 11.2 , and Feature F032 "CASCADE drop 
behaviour").


That is, you always have to specify either CASCADE (delete everything 
contained in it), or RESTRICT (only delete if empty).


However, the SQL standard also specifies that all objects then need to 
be dropped with CASCADE (so dependents are dropped as well), and 
Firebird doesn't provide such feature (Feature F032 "CASCADE drop 
behaviour") at the moment, and it is probably necessary for this to work 
correctly, otherwise objects with dependents in other tablespaces will 
result in the drop failing anyway.


Personally, I could live with not providing such feature for now, and 
instead requiring the tablespace to be empty before dropping it.

"""


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.


I assume you mean the entire clause is option, so: [TABLESPACE 
{ | MAIN}]



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.


I assume you mean the entire clause is option, so: [TABLESPACE 
{ | MAIN}]



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 restore


[..]

It allows you to restore tablespace contents to new places.
If the option is not specified gbak will use old locations for every 
tablespace.


I still think this should be an explicit option as well, otherwise 
surprising things - for the user - can happen when restoring a backup of 
a database you didn't know had tablespaces.


Mark
--
Mark Rotteveel


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

пн, 11 окт. 2021 г. в 23:03, Vlad Khorsun :

> 11.10.2021 21:23, Roman Simakov wrote:
> > пн, 11 окт. 2021 г. в 18:42, Vlad Khorsun  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 
>

I had such an idea but didn't want to make up our own way.
If we go Oracle way and use DEFAULT we won't be able to move index data to
the main database for indices for a table at;) a tablespace. I.e. we can
move either to a named tablespace or to a default (table's) tablespace.
It seems Oracle uses the name SYSTEM for the main database. Do you like it?
Anyway the main database tablespace has to have a name. The question is
what name?
MAIN
PRIMARY
SYSTEM
DATABASE TABLESPACE
DATABASE
but definitely it could not be DEFAULT because DEFAULT meaning depends on
the context.


>
> in this case, when table's table space is changed, all dependent object
> should
> be changed accordingly
>

What do you mean saying "changed"? Now we explicitly set the tablespace
name for an index and when a table is moving leave the index where it was.
So subobjects are not bind to the parent. So does Oracle. Do you suggest
moving all dependent objects implicitly? So the question is to bind or not
to bind?


>
> // let me use AT until we agreed to use IN ;)
>

I'd like to get an answer from native speakers, but I think it's like a
database or file (in a database, in a file).


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

Definitely not. The point is that we cannot use DEFAULT as a name for the
main database. If so I decided not to introduce DEFAULT keyword at all. We
can add it when we understand how it works and what defaults are useful.


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

I don't mind but native speakers - say your words :)


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

Yes. Unfortunately, Oracle syntax is far from ideal. I think we both agree
on that.


> > const USHORT DB_PAGE_SPACE = 1;
> >
> > // .. here all tablespace IDs. Keep

Re: [Firebird-devel] Tablespaces proposal

2021-10-11 Thread Dmitry Yemanov


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

2021-10-11 Thread Vlad Khorsun

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

2021-10-11 Thread Vlad Khorsun

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

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

2021-10-11 Thread Lucas Schatz

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] Tablespaces proposal

2021-10-11 Thread Dmitry Yemanov


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

2021-10-11 Thread Vlad Khorsun


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

Re: [Firebird-devel] Tablespaces proposal

2021-10-11 Thread Adriano dos Santos Fernandes

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

2021-10-11 Thread Kjell Rilbe


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] Tablespaces proposal

чт, 7 окт. 2021 г. в 17:12, Mark Rotteveel :
>
> On 07-10-2021 15:54, Roman Simakov wrote:
> > чт, 7 окт. 2021 г. в 11:14, Mark Rotteveel :
> >> I think it will save a lot of headaches if ALTER DATABASE {BEGIN|END}
> >> BACKUP can do that for all tablespaces at once. It would be a lot
> >> simpler than having to arrange that per tablespace. That is not to say
> >> there might be a use case to do it per tablespace file, but I think
> >> simple options should be preferred initially.
> >
> > Perhaps. It depends of what we will have to backup up: the whole set
> > of tablespaces or not. I think there must be options.
>
> Backing up only one tablespace could lead to inconsistent restores
> though. To me, that sounds like a power-feature that doesn't belong in a
> first version.

Restoring one tablespace makes no sense. But it might be useful to
skip some tablespaces: indices, logs, unimportant tables.

-- 
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal


Mark Rotteveel wrote 07.10.2021 16:11:

Backing up only one tablespace could lead to inconsistent restores though.


  From practical POV it is not different from current partial backup including 
only subset of tables.


--
  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 07.10.2021 16:08:

Let's eat an elephant one bite at a time


  Yes, that's exactly my point: no need to reserve fields if implementation 
that use them is not clear yet and better no feature than provisory hack.


--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-07 Thread Mark Rotteveel


On 07-10-2021 15:54, Roman Simakov wrote:

чт, 7 окт. 2021 г. в 11:14, Mark Rotteveel :

I think it will save a lot of headaches if ALTER DATABASE {BEGIN|END}
BACKUP can do that for all tablespaces at once. It would be a lot
simpler than having to arrange that per tablespace. That is not to say
there might be a use case to do it per tablespace file, but I think
simple options should be preferred initially.


Perhaps. It depends of what we will have to backup up: the whole set
of tablespaces or not. I think there must be options.


Backing up only one tablespace could lead to inconsistent restores 
though. To me, that sounds like a power-feature that doesn't belong in a 
first version.


Mark
--
Mark Rotteveel


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

чт, 7 окт. 2021 г. в 11:47, Dimitry Sibiryakov :
>
> Roman Simakov wrote 06.10.2021 21:49:
> >> How are data moved?
> >>
> >> Under transaction control?
> >>
> >> In background (and interruptible) like db crypt?
> > it's a dfw operation with EX database lock. I hope to relax this
> > limitation later. 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. I'm afraid it starts from the 
> > beginning.
> >
> > I guess it's better to discuss in a context of PR.
>
>Tablespaces has meaning for large databases only that don't fit into single
> storage (terrabytes). For such volumes this algorithm is no-go. Better not to
> have move option in the initial implementation at all.

Let's eat an elephant one bite at a time

-- 
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

чт, 7 окт. 2021 г. в 11:45, Dimitry Sibiryakov :
>
> Roman Simakov wrote 07.10.2021 8:14:
> > ср, 6 окт. 2021 г. в 23:57, Dimitry Sibiryakov:
> >> Roman Simakov wrote 06.10.2021 22:44:
> >>> Sorry I was wrong. TABLESPACE_ID is used as pagespace ID internally
> >>> but this id presents at index root page for example.
> >> I don't see it in ods.h:index_root_page.
> > Maybe because I haven't made a PR yet)
>
>So you didn't describe all ODS changes you are going to make. What else did
> you miss?

I hope that's it.

> >   RDB$SYSTEM_FLAG - SMALLINT   # reserved
> >   RDB$OFFLINE - SMALLINT  # reserved for future
> > implementation offline tablespaces
> >   RDB$READ_ONLY - SMALLINT# reserved for future
> > implementation read only tablespaces
>
>These fields can be added later when (if) implementation require them. I'm
> sure that "read only" flag belongs to the file header, not TS description
> (unless you are going to create them in already RO state which is pointless).

It's hard to predict the full set of fields for future use. I try to
reduce a number of ODS changes. But I do not insist on these fields.
Let's remove them from the initial implementation.

-- 
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

чт, 7 окт. 2021 г. в 11:14, Mark Rotteveel :
>
> On 2021-10-06 21:37, Roman Simakov wrote:
> > ср, 6 окт. 2021 г. в 19:29, Adriano dos Santos Fernandes
> > :
>
> >> And what about nbackup? Will it create a .delta file per tablespace?
> >
> > Our implementation does not support nbackup yet. At first glance it
> > might be a special DDL operation like
> > ALTER TABLESPACE  {BEGIN|END} BACKUP
>
> I think it will save a lot of headaches if ALTER DATABASE {BEGIN|END}
> BACKUP can do that for all tablespaces at once. It would be a lot
> simpler than having to arrange that per tablespace. That is not to say
> there might be a use case to do it per tablespace file, but I think
> simple options should be preferred initially.

Perhaps. It depends of what we will have to backup up: the whole set
of tablespaces or not. I think there must be options.

-- 
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

чт, 7 окт. 2021 г. в 11:11, Mark Rotteveel :
>
> On 2021-10-06 21:27, Roman Simakov wrote:
> > ср, 6 окт. 2021 г. в 19:13, Mark Rotteveel :
> >> On 06-10-2021 17:32, Roman Simakov wrote:
> >> > 9. ALTER INDEX  DROP TABLESPACE
> >> >
> >> > Data of the index will be moved to the main database.
> >>
> >> How will this work for indexes backing constraints?
> >
> > I see no problems with it. Could you explain what you mean?
>
> Sorry, I think I should have been more explicit. Is there an option to
> specify the tablespace for the constraint when defining constraints,
> instead of having to alter this after the fact (with potential issues of
> having to either find the generated index name, or making sure you name
> things explicitly).

Now it's clear. Alex mentioned that. Now there is no way but it should be added.

> >> I also think that using the old locations should be an explicit
> >> option,
> >> so if a database backup has tablespaces, and no option is specified to
> >> define the tablespace mapping, or the mapping is incomplete, restore
> >> should fail *before it started to write anything*.
> >
> > or we can restore the whole backup in the single database file without
> > tablespaces at all. I.e. merge every unmapped TS into the main
> > database.
>
> That is an interesting option as well, but I think that should be an
> explicit option as well. That is, I think trying to restore a
> tablespaced database should fail unless explicit configuration is in
> place that specifies how to handle it (restore with filenames in backup,
> explicit mapping, or 'restore into single file').

I agree. More strict rules are better in the beginning as a rule) It's
easier to add defaults later than remove them)


-- 
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal


Molnár Attila wrote 07.10.2021 10:56:

It might not be about read performance, but e.g. logical data serparation, 
backup speedup, etc...


  Logical data separation is task for namespaces, not tablespaces.
  Physical backup of single tablespace is pointless because it has no TIP so 
without main database it is just a pile of junk. Logical backup is single-table 
backup, no relations to tablespaces at all.



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 this case tablespaces make situation even worse. Whole system become 
unmaintainable.


--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-07 Thread 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)



-Eredeti üzenet-
Feladó: Dimitry Sibiryakov [mailto:s...@ibphoenix.com] 
Küldve: 2021. október 7., csütörtök 10:47
Címzett: For discussion among Firebird Developers 

Tárgy: Re: [Firebird-devel] Tablespaces proposal

Roman Simakov wrote 06.10.2021 21:49:
>> How are data moved?
>>
>> Under transaction control?
>>
>> In background (and interruptible) like db crypt?
> it's a dfw operation with EX database lock. I hope to relax this
> limitation later. 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. I'm afraid it starts from the 
> beginning.
> 
> I guess it's better to discuss in a context of PR.

   Tablespaces has meaning for large databases only that don't fit into single 
storage (terrabytes). For such volumes this algorithm is no-go. Better not to 
have move option in the initial implementation at all.

-- 
   WBR, SD.


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 06.10.2021 21:49:

How are data moved?

Under transaction control?

In background (and interruptible) like db crypt?

it's a dfw operation with EX database lock. I hope to relax this
limitation later. 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. I'm afraid it starts from the beginning.

I guess it's better to discuss in a context of PR.


  Tablespaces has meaning for large databases only that don't fit into single 
storage (terrabytes). For such volumes this algorithm is no-go. Better not to 
have move option in the initial implementation at all.


--
  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 07.10.2021 8:14:

ср, 6 окт. 2021 г. в 23:57, Dimitry Sibiryakov:

Roman Simakov wrote 06.10.2021 22:44:

Sorry I was wrong. TABLESPACE_ID is used as pagespace ID internally
but this id presents at index root page for example.

I don't see it in ods.h:index_root_page.

Maybe because I haven't made a PR yet)


  So you didn't describe all ODS changes you are going to make. What else did 
you miss?



  RDB$SYSTEM_FLAG - SMALLINT   # reserved
  RDB$OFFLINE - SMALLINT  # reserved for future
implementation offline tablespaces
  RDB$READ_ONLY - SMALLINT# reserved for future
implementation read only tablespaces


  These fields can be added later when (if) implementation require them. I'm 
sure that "read only" flag belongs to the file header, not TS description 
(unless you are going to create them in already RO state which is pointless).


--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-07 Thread Mark Rotteveel


On 2021-10-06 21:37, Roman Simakov wrote:
ср, 6 окт. 2021 г. в 19:29, Adriano dos Santos Fernandes 
:



And what about nbackup? Will it create a .delta file per tablespace?


Our implementation does not support nbackup yet. At first glance it
might be a special DDL operation like
ALTER TABLESPACE  {BEGIN|END} BACKUP


I think it will save a lot of headaches if ALTER DATABASE {BEGIN|END} 
BACKUP can do that for all tablespaces at once. It would be a lot 
simpler than having to arrange that per tablespace. That is not to say 
there might be a use case to do it per tablespace file, but I think 
simple options should be preferred initially.


Mark


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-07 Thread Mark Rotteveel

On 2021-10-06 21:27, Roman Simakov wrote:

ср, 6 окт. 2021 г. в 19:13, Mark Rotteveel :

On 06-10-2021 17:32, Roman Simakov wrote:
> 9. ALTER INDEX  DROP TABLESPACE
>
> Data of the index will be moved to the main database.

How will this work for indexes backing constraints?

I see no problems with it. Could you explain what you mean?

Sorry, I think I should have been more explicit. Is there an option to 
specify the tablespace for the constraint when defining constraints, 
instead of having to alter this after the fact (with potential issues of 
having to either find the generated index name, or making sure you name 
things explicitly).

I also think that using the old locations should be an explicit 
option,

so if a database backup has tablespaces, and no option is specified to
define the tablespace mapping, or the mapping is incomplete, restore
should fail *before it started to write anything*.

or we can restore the whole backup in the single database file without
tablespaces at all. I.e. merge every unmapped TS into the main
database.

That is an interesting option as well, but I think that should be an 
explicit option as well. That is, I think trying to restore a 
tablespaced database should fail unless explicit configuration is in 
place that specifies how to handle it (restore with filenames in backup, 
explicit mapping, or 'restore into single file').

Mark

Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-07 Thread Omacht András

It was a suggestion, not expected to appear in 5.0.

Anyway, I could also imagine that due to more frequent releases, new features 
would appear in the main releases with an experimental flag and configuration 
setting (turned off by default). (As in the linux kernel.)
Anyone who wants to test can do so. Anyone who wants to use it after the test 
can do so at their own risk.

But this is the decision of the core team.

András

-Original Message-
From: Simonov Denis via Firebird-devel 
[mailto:firebird-devel@lists.sourceforge.net] 
Sent: Thursday, October 7, 2021 9:47 AM
To: firebird-devel@lists.sourceforge.net
Cc: Simonov Denis 
Subject: Re: [Firebird-devel] Tablespaces proposal

Omacht András  писал(а) в своём письме Thu, 07 Oct
2021 09:18:26 +0300:

> Hi Roman!
>
> First of all, thanks for the development and offering it to Firebird.
>
> Do you plan to partition the data depending on field value?
>
> Oracle range partitioning example:
>

It seems to me that PARTITION support should be postponed until the next major 
version, otherwise the release of Firebird 5.0 will be delayed as always. It is 
necessary to release a release more often than once 4-5 years.

--
Simonov Denis



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

2021-10-07 Thread Simonov Denis via Firebird-devel

Omacht András  писал(а) в своём письме Thu, 07 Oct  
2021 09:18:26 +0300:



Hi Roman!

First of all, thanks for the development and offering it to Firebird.

Do you plan to partition the data depending on field value?

Oracle range partitioning example:



It seems to me that PARTITION support should be postponed until the next  
major version, otherwise the release of Firebird 5.0 will be delayed as  
always. It is necessary to release a release more often than once 4-5  
years.


--
Simonov Denis



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

чт, 7 окт. 2021 г. в 09:19, Omacht András :
>
> Hi Roman!
>
> First of all, thanks for the development and offering it to Firebird.

Thanks)

> Do you plan to partition the data depending on field value?

Yes. We do. I hope we'll do something in the next version of
RedDatabase and then share with Firebird as well.


-- 
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-07 Thread Omacht András

Hi Roman!

First of all, thanks for the development and offering it to Firebird.

Do you plan to partition the data depending on field value?

Oracle range partitioning example:

CREATE TABLE sales_range 
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
sales_dateDATE)
PARTITION BY RANGE(sales_date) 
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/'))
);

Oracle list partitioning example:
CREATE TABLE sales_list
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20),
sales_amount  NUMBER(10), 
sales_dateDATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT)
);

https://docs.oracle.com/cd/B19306_01/server.102/b14220/partconc.htm#i468016

ERP systems usually contain data from several years in one table (e.g. general 
ledger), but after a year-end, the data from the previous years are rarely 
queried / modified, so they do not necessarily have to be on a quick-access 
disk.

András

-Original Message-
From: Roman Simakov [mailto:roman.sima...@gmail.com] 
Sent: Wednesday, October 6, 2021 5:32 PM
To: For discussion among Firebird Developers 

Subject: [Firebird-devel] Tablespaces proposal

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

Re: [Firebird-devel] Tablespaces proposal

ср, 6 окт. 2021 г. в 23:57, Dimitry Sibiryakov :
>
> Roman Simakov wrote 06.10.2021 22:44:
> > Sorry I was wrong. TABLESPACE_ID is used as pagespace ID internally
> > but this id presents at index root page for example.
>
>I don't see it in ods.h:index_root_page.

Maybe because I haven't made a PR yet)

-- 
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-06 Thread Dimitry Sibiryakov


Roman Simakov wrote 06.10.2021 22:44:

Sorry I was wrong. TABLESPACE_ID is used as pagespace ID internally
but this id presents at index root page for example.


  I don't see it in ods.h:index_root_page.

--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

> >ID is not referenced anywhere and seems to have no purpose at all.
>
> Probably it could be removed and pagespace IDs will be generated internally.

Sorry I was wrong. TABLESPACE_ID is used as pagespace ID internally
but this id presents at index root page for example. Now I'm not sure
if it's easy to break the relationship between TABLESPACE_ID and
TABLESPACE_NAME. It's an effective way to refer to a tablespace.

-- 
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

ср, 6 окт. 2021 г. в 22:17, Dimitry Sibiryakov :
>
> Roman Simakov wrote 06.10.2021 20:43:
> > Let me not describe every field. I hope most of them are obvious.
>
>No, they aren't.

ok

A new table RDB$TABLESPACES # keeps metadata of tablespaces.

  RDB$TABLESPACE_ID - SMALLINT # internally it will be
pagespaceid. probably can be avoided.
  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 SUBTYPE TEXT SEGMENT SIZE 80 #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 - SMALLINT  # reserved for future
implementation offline tablespaces
  RDB$READ_ONLY - SMALLINT# reserved for future
implementation read only tablespaces

A new field in RDB$INDICES:
  RDB$TABLESPACE_NAME - CHAR (63)  # name of a tablespace

A new field in RDB$RELATION_FIELDS:
  RDB$TABLESPACE_NAME - CHAR (63)  # name of a tablespace

New fields in RDB$RELATIONS:
  RDB$TABLESPACE_NAME - CHAR (63)  # name of a tablespace
  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

>ID is not referenced anywhere and seems to have no purpose at all.

Probably it could be removed and pagespace IDs will be generated internally.

>TABLESPACE_NAME being CHAR is pointless. CHAR was used in ancient times but
> new fields should prefer VARCHAR.

It's equal to other SQL_IDENTIFIERs

>FILE_NAME limited to 255 characters is strange in modern world at least.

It refers to the existing fld_file_name. I don't mind increasing it
but think it's not a topic for tablespaces.

> > RDB$SYSTEM_FLAG is not currently used but I suppose it would be useful one 
> > day.
>
>What for? There isn't going to be an automatically created tablespace for
> internal purposes, right? Where in host filesystem would it be created?..

I've not designed it and am not designing it now.

> > OFFLINE and READ_ONLY are a kind of physical properties and supposed
> > to be used but not in this merge request anycase.
>
>Mostly I wanted to know the reason why they are SMALLINT instead of 
> BOOLEAN.

I think you are right and it must be BOOLEAN.

> > These fields are necessary for reliable implementation of moving data
> > pages to another tablespace. You'll be able to understand their
> > meaning in code.
>
>Documentation also must contain the code?..

After documentation you will ask me why they are needed) For
documentation purposes I've provided a short description above.

--
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

ср, 6 окт. 2021 г. в 22:48, Dimitry Sibiryakov :
>
> Roman Simakov wrote 06.10.2021 21:27:
> > So am I. Currently tablespace headers are just not used.
>
>In this case how do you determine page size inside of tablespace file? 
> Having
> it different from main one is an essential feature for indexes.

All pages share the same bdb cache. That's why they must have the same
page size.

-- 
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

ср, 6 окт. 2021 г. в 22:01, Adriano dos Santos Fernandes :
>
> On 06/10/2021 15:43, Roman Simakov wrote:
> >
> >>>   RDB$POINTER_PAGE - INTEGER
> >>>   RDB$ROOT_PAGE - INTEGER
> >>
> >>What these are for?
> >
> > These fields are necessary for reliable implementation of moving data
> > pages to another tablespace. You'll be able to understand their
> > meaning in code.
> >
>
> How are data moved?
>
> Under transaction control?
>
> In background (and interruptible) like db crypt?

it's a dfw operation with EX database lock. I hope to relax this
limitation later. 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. I'm afraid it starts from the beginning.

I guess it's better to discuss in a context of PR.

-- 
Roman Simakov

Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-06 Thread Dimitry Sibiryakov


Roman Simakov wrote 06.10.2021 21:27:

So am I. Currently tablespace headers are just not used.


  In this case how do you determine page size inside of tablespace file? Having 
it different from main one is an essential feature for indexes.


--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

ср, 6 окт. 2021 г. в 19:29, Alex Peshkoff via Firebird-devel
:
>
> On 10/6/21 6:32 PM, Roman Simakov wrote:
>
> > 4. CREATE TABLE …
> > TABLESPACE 
>
> Given syntax does not let specify tablespace for constraints-related
> indexes and separate fields. That's serious limitation. At least for
> blobs that's IMHO needed.

Originally we thought about specifying tablespaces for BLOB fields. It
will be implemented later.
But constraints-related fields were missed.
We'll try to add it to the syntax. Thanks.

-- 
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

ср, 6 окт. 2021 г. в 19:29, Adriano dos Santos Fernandes :
>
> On 06/10/2021 13:12, Mark Rotteveel wrote:
> >>
> >> 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 would be good to also have relative paths support, in relation to
> main database file.
>
> In this case, restore will also restore the relative paths to the new
> location.

Good point. We'll do it such way.

> And what about nbackup? Will it create a .delta file per tablespace?

Our implementation does not support nbackup yet. At first glance it
might be a special DDL operation like
ALTER TABLESPACE  {BEGIN|END} BACKUP

-- 
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

ср, 6 окт. 2021 г. в 19:13, Mark Rotteveel :
>
> On 06-10-2021 17:32, Roman Simakov wrote:
> > 3. DROP TABLESPACE  [INCLUDING CONTENTS]
>
> I think using {CASCADE | RESTRICT} instead of [INCLUDING CONTENTS] would
> be better, as that fits with similar definitions in the SQL standard
> (e.g. 11.2 , and Feature F032 "CASCADE drop
> behaviour").
>
> That is, you always have to specify either CASCADE (delete everything
> contained in it), or RESTRICT (only delete if empty).
>
> However, the SQL standard also specifies that all objects then need to
> be dropped with CASCADE (so dependents are dropped as well), and
> Firebird doesn't provide such feature (Feature F032 "CASCADE drop
> behaviour") at the moment, and it is probably necessary for this to work
> correctly, otherwise objects with dependents in other tablespaces will
> result in the drop failing anyway.
>
> Personally, I could live with not providing such feature for now, and
> instead requiring the tablespace to be empty before dropping it.

Original syntax and semantics have been taken from Oracle
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/DROP-TABLESPACE.html

It means that CASCADE handles objects dependent on objects in the
dropping tablespace or such drop can be failed as well, in spite of
INCLUDING CONTENT.

I think it would be implemented as you described like DROP ... CASCADE
but later. I see no problem with proposed syntax.

> > 6. ALTER TABLE  DROP TABLESPACE
> >
> > Data of the table will be moved to the main database.
>
> Does this require a separate DROP TABLESPACE option? The same could be
> achieved with ALTER TABLE  ALTER TABLESPACE ...
>
> where ... might be DEFAULT, MAIN or the explicit name of the main
> tablespace (assuming it has a name).
>
> Using ALTER TABLESPACE also makes sense, because that is what's actually
> happening.

I have no objections but it would be useful to know other opinions.

> > 9. ALTER INDEX  DROP TABLESPACE
> >
> > Data of the index will be moved to the main database.
>
> How will this work for indexes backing constraints?

I see no problems with it. Could you explain what you mean?

> > 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.
>
> I'd prefer if there is (also) an option to define the mapping on the
> commandline without requiring users to first create the mapping file.

Good point. I think it would be like
gbak ... -ts TS1=/path/to/tablespace1.dat -ts TS2=/path/to/tablespace2.dat

> I also think that using the old locations should be an explicit option,
> so if a database backup has tablespaces, and no option is specified to
> define the tablespace mapping, or the mapping is incomplete, restore
> should fail *before it started to write anything*.

or we can restore the whole backup in the single database file without
tablespaces at all. I.e. merge every unmapped TS into the main
database.

> As an aside, we really need to look at how we handle commandline
> options, because having really long commandline options with no simple
> one or two character abbreviations is not very friendly IMHO. Maybe we
> should consider switching to `-v` vs `--verbose-names` convention (with
> backwards compatibility for existing options).
>
> > SOME DETAILS
> > =
> >
> > pag_header in every tablespace is reserved and may be replaced by a
> > new page type.
>
> I think it should be a separate page type.

So am I. Currently tablespace headers are just not used.

--
Roman Simakov


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-06 Thread Dimitry Sibiryakov


Roman Simakov wrote 06.10.2021 20:43:

Let me not describe every field. I hope most of them are obvious.


  No, they aren't.
  ID is not referenced anywhere and seems to have no purpose at all.
  TABLESPACE_NAME being CHAR is pointless. CHAR was used in ancient times but 
new fields should prefer VARCHAR.

  FILE_NAME limited to 255 characters is strange in modern world at least.


RDB$SYSTEM_FLAG is not currently used but I suppose it would be useful one day.


  What for? There isn't going to be an automatically created tablespace for 
internal purposes, right? Where in host filesystem would it be created?..



OFFLINE and READ_ONLY are a kind of physical properties and supposed
to be used but not in this merge request anycase.


  Mostly I wanted to know the reason why they are SMALLINT instead of BOOLEAN.


These fields are necessary for reliable implementation of moving data
pages to another tablespace. You'll be able to understand their
meaning in code.


  Documentation also must contain the code?..

--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

2021-10-06 Thread Adriano dos Santos Fernandes

On 06/10/2021 15:43, Roman Simakov wrote:
> 
>>>   RDB$POINTER_PAGE - INTEGER
>>>   RDB$ROOT_PAGE - INTEGER
>>
>>What these are for?
> 
> These fields are necessary for reliable implementation of moving data
> pages to another tablespace. You'll be able to understand their
> meaning in code.
> 

How are data moved?

Under transaction control?

In background (and interruptible) like db crypt?


Adriano


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal