Re: [Firebird-devel] Tablespaces proposal

[Dimitry Sibiryakov]

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

[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

[Roman Simakov]

вт, 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

[Dimitry Sibiryakov]

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

[Dmitry Yemanov]

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

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

[Roman Simakov]

вт, 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

[Vlad Khorsun]

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

[Roman Simakov]

вт, 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] Partitioning

[Dimitry Sibiryakov]

Dmitry Yemanov wrote 12.10.2021 16:59:
Slightly faster inserts into indices. 


  If local indexes are used, yes. But such indexes require partitioning key in 
queries.
  What will be effect for global indexes? Reference to the partition for a 
record has to be in index node, no?



But it may be possible with partitions in different files and nbackup.


  It would require stable external references (like number of the root page, 
etc), right?


--
  WBR, SD.


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

Re: [Firebird-devel] Tablespaces proposal

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

[Roman Simakov]

вт, 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

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


--
  WBR, SD.


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

Re: [Firebird-devel] Tablespaces proposal

[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] Partitioning (was: Tablespaces proposal)

[Dmitry Yemanov]

12.10.2021 16:21, Dimitry Sibiryakov wrote:


   INSERT:

   Nothing except time wasting to calculation partitioning key and 
creation of a new partition if necessary (ignorable).


Slightly faster inserts into indices.


   UPDATE:
   DELETE:


Faster index GC after them.


   BACKUP:

Backup of separate partition is something nobody asked for so far. At 
least I cannot remember a ticket for gbak accepting filter condition 
inside of a table


It makes zero sense for gbak, because it restores the database as a 
whole and cannot append/replace records in existing database. But it may 
be possible with partitions in different files and nbackup.



Dmitry


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

Re: [Firebird-devel] Tablespaces proposal

[Roman Simakov]

вт, 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

[Roman Simakov]

вт, 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

[Roman Simakov]

вт, 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] Partitioning (was: Tablespaces proposal)

[Dimitry Sibiryakov]

Dmitry Yemanov wrote 12.10.2021 15:02:
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.


  Ok, let's talk about it a little.

  Lets' define partitioning as "a split of table into pieces according to some 
condition". Call this condition "a partitioning key".


  What benefits can it have to database operations?

  SELECT:

  If query condition match partitioning key then natural scan can be reduced to 
subset of pages. This is effect similar to IOT.

  Local index can be used that have smaller depth and size.
  Parallel reading of different partitions... not going to happen.

  INSERT:

  Nothing except time wasting to calculation partitioning key and creation of a 
new partition if necessary (ignorable).


  UPDATE:

  Effectively become INSERT+DELETE if partitioning key is changed, otherwise 
nothing.


  DELETE:

  Can be reduced to TRUNCATE if the only query condition matches partitioning 
condition.


  BACKUP:

  Backup of separate partition is something nobody asked for so far. At least I 
cannot remember a ticket for gbak accepting filter condition inside of a table, 
only filter for whole tables and (since it is already implemented)... is anybody 
aware of it?..


  RESTORE:

  How backup of single partition is supposed to be restored or otherwise used?

PS: The case of slow drives was already commented. It is an internal feature of 
modern hybrid storage systems and Firebird doesn't need another "hand-made RAID".



--
  WBR, SD.


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

Re: [Firebird-devel] Tablespaces proposal

[Dmitry Yemanov]

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

[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

[Dimitry Sibiryakov]

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

[Dmitry Yemanov]

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

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



Dmitry


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

Re: [Firebird-devel] Tablespaces proposal

[Dimitry Sibiryakov]

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

[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

[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

[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

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


--
  WBR, SD.


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

Re: [Firebird-devel] Tablespaces proposal

[Vlad Khorsun]

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

[Vlad Khorsun]

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

[Dimitry Sibiryakov]

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

[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

[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

[Roman Simakov]

пн, 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