Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: make CREATE TABLE truly transactional #4086

Closed
kyukhin opened this issue Mar 29, 2019 · 2 comments
Closed

sql: make CREATE TABLE truly transactional #4086

kyukhin opened this issue Mar 29, 2019 · 2 comments
Assignees
@Gerold103
Labels
ddl feature A new functionality sql
Milestone
2.2.0

Comments

@kyukhin
Copy link
Contributor

kyukhin commented Mar 29, 2019

When #4083 is implemented, it will be possible not to
store DDL operations in artificial list, but do CREATE TABLE
inside a transaction.
@kyukhin kyukhin added feature A new functionality sql ddl labels Mar 29, 2019
@kyukhin kyukhin added this to the 2.2.0 milestone Mar 29, 2019
@kyukhin kyukhin added the blocked Not ready to be implemented label Mar 29, 2019
@Gerold103 Gerold103 removed the blocked Not ready to be implemented label Jul 15, 2019
@Gerold103
Copy link
Collaborator

Blocked by #4364 - SQL uses savepoints as transactions inside transactions, for all multirow statements, including forthcoming transactional DDL.

@Gerold103
Copy link
Collaborator

Blocked by #4365.

Gerold103 added a commit that referenced this issue Jul 19, 2019
@Gerold103
alter: do not access space in drop ck commit trigger
4e8379b 
When transactional DDL is introduced, 'DROP TABLE' will remove a
space and its cks in one transaction. At the moment of that
transaction commit the space is already removed from _space and
from space cache, and can't be accessed from other triggers.

This patch makes all space-related actions in on_replace.

Part of #4086
Gerold103 added a commit that referenced this issue Jul 19, 2019
@Gerold103
sql: transactional DDL
5863bb4 
Box recently added support of transactional DDL allowing to do
any number of non-yielding DDL operations atomically. This is
really a big relief of one of the biggest pains of SQL. Before
this patch each multirow SQL DDL statement needed to prepare its
own rollback procedure for a case if something would go wrong.

Now with box support SQL wraps each DDL statement into a
transaction, and doesn't need own escape-routes in a form of
'struct save_record' and others.

Closes #4086
Gerold103 added a commit that referenced this issue Jul 22, 2019
@Gerold103
sql: transactional DDL
364040c 
Box recently added support of transactional DDL allowing to do
any number of non-yielding DDL operations atomically. This is
really a big relief of one of the biggest pains of SQL. Before
this patch each multirow SQL DDL statement needed to prepare its
own rollback procedure for a case if something would go wrong.

Now with box support SQL wraps each DDL statement into a
transaction, and doesn't need own escape-routes in a form of
'struct save_record' and others.

Closes #4086

@TarantoolBot document
Title: SQL DDL is transactional

SQL DDL operations are atomic now. For example, if a CREATE TABLE
request fails somewhere in the middle, it won't leave any
garbage. Like a space without indexes, or unused sequences. Even
if the instance is powered off during the request.

Also, SQL DDL can be manually included into transactions, with
certain limitations - such a transaction can't yield.

For example, this is legal:

    START TRANSACTION;
    CREATE TABLE test(a INTEGER PRIMARY KEY, b INTEGER);
    CREATE INDEX test_a ON test(a);
    COMMIT;

If you want to test it in the console, then wrap it into a
function to do not get a rollback by yield, because the console
yields after each command:

    function create()
        box.execute('START TRANSACTION;')
        box.execute('CREATE TABLE test(a INTEGER PRIMARY KEY, b INTEGER);')
        box.execute('CREATE INDEX test_a ON test(a);')
        box.execute('COMMIT;')
    end

    create()

But the following example is illegal and you will get an error:

    box.execute('CREATE TABLE test(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER);')
    box.execute('INSERT INTO test VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);')

    function index()
        box.execute('START TRANSACTION;')
        box.execute('CREATE INDEX test_b ON test(b);')
        box.execute('CREATE INDEX test_c ON test(c);')
        box.execute('COMMIT;')
    end

    tarantool> index()
    ---
    - error: Can not perform index build in a multi-statement transaction
    ...

The error is because an attempt to build an index on a non-empty
space leads to immediate yield.
@kyukhin kyukhin added ready for review and removed blocked Not ready to be implemented labels Jul 24, 2019
Gerold103 added a commit that referenced this issue Jul 24, 2019
@Gerold103
sql: transactional DDL
80514eb 
Box recently added support of transactional DDL allowing to do
any number of non-yielding DDL operations atomically. This is
really a big relief of one of the biggest pains of SQL. Before
this patch each multirow SQL DDL statement needed to prepare its
own rollback procedure for a case if something would go wrong.

Now with box support SQL wraps each DDL statement into a
transaction, and doesn't need own escape-routes in a form of
'struct save_record' and others.

Closes #4086

@TarantoolBot document
Title: SQL DDL is transactional

SQL DDL operations are atomic now. For example, if a CREATE TABLE
request fails somewhere in the middle, it won't leave any
garbage. Like a space without indexes, or unused sequences. Even
if the instance is powered off during the request.

Also, SQL DDL can be manually included into transactions, with
certain limitations - such a transaction can't yield.

For example, this is legal:

    START TRANSACTION;
    CREATE TABLE test(a INTEGER PRIMARY KEY, b INTEGER);
    CREATE INDEX test_a ON test(a);
    COMMIT;

If you want to test it in the console, then wrap it into a
function to do not get a rollback by yield, because the console
yields after each command:

    function create()
        box.execute('START TRANSACTION;')
        box.execute('CREATE TABLE test(a INTEGER PRIMARY KEY, b INTEGER);')
        box.execute('CREATE INDEX test_a ON test(a);')
        box.execute('COMMIT;')
    end

    create()

But the following example is illegal and you will get an error:

    box.execute('CREATE TABLE test(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER);')
    box.execute('INSERT INTO test VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);')

    function index()
        box.execute('START TRANSACTION;')
        box.execute('CREATE INDEX test_b ON test(b);')
        box.execute('CREATE INDEX test_c ON test(c);')
        box.execute('COMMIT;')
    end

    tarantool> index()
    ---
    - error: Can not perform index build in a multi-statement transaction
    ...

The error is because an attempt to build an index on a non-empty
space leads to immediate yield.
Gerold103 added a commit that referenced this issue Jul 30, 2019
@Gerold103
alter: do not access space in drop ck commit trigger
aa3baf8 
When transactional DDL is introduced, 'DROP TABLE' will remove a
space and its cks in one transaction. At the moment of that
transaction commit the space is already removed from _space and
from space cache, and can't be accessed from other triggers.

This patch makes all space-related actions in on_replace.

Part of #4086
Gerold103 added a commit that referenced this issue Jul 30, 2019
@Gerold103
sql: transactional DDL
84a3fd1 
Box recently added support of transactional DDL allowing to do
any number of non-yielding DDL operations atomically. This is
really a big relief of one of the biggest pains of SQL. Before
this patch each multirow SQL DDL statement needed to prepare its
own rollback procedure for a case if something would go wrong.

Now with box support SQL wraps each DDL statement into a
transaction, and doesn't need own escape-routes in a form of
'struct save_record' and others.

Closes #4086

@TarantoolBot document
Title: SQL DDL is transactional

SQL DDL operations are atomic now. For example, if a CREATE TABLE
request fails somewhere in the middle, it won't leave any
garbage. Like a space without indexes, or unused sequences. Even
if the instance is powered off during the request.

Also, SQL DDL can be manually included into transactions, with
certain limitations - such a transaction can't yield.

For example, this is legal:

    START TRANSACTION;
    CREATE TABLE test(a INTEGER PRIMARY KEY, b INTEGER);
    CREATE INDEX test_a ON test(a);
    COMMIT;

If you want to test it in the console, then wrap it into a
function to do not get a rollback by yield, because the console
yields after each command:

    function create()
        box.execute('START TRANSACTION;')
        box.execute('CREATE TABLE test(a INTEGER PRIMARY KEY, b INTEGER);')
        box.execute('CREATE INDEX test_a ON test(a);')
        box.execute('COMMIT;')
    end

    create()

But the following example is illegal and you will get an error:

    box.execute('CREATE TABLE test(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER);')
    box.execute('INSERT INTO test VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);')

    function index()
        box.execute('START TRANSACTION;')
        box.execute('CREATE INDEX test_b ON test(b);')
        box.execute('CREATE INDEX test_c ON test(c);')
        box.execute('COMMIT;')
    end

    tarantool> index()
    ---
    - error: Can not perform index build in a multi-statement transaction
    ...

The error is because an attempt to build an index on a non-empty
space leads to immediate yield.
kyukhin pushed a commit that referenced this issue Jul 31, 2019
@Gerold103 @kyukhin
alter: do not access space in drop ck commit trigger
e094965 
When transactional DDL is introduced, 'DROP TABLE' will remove a
space and its cks in one transaction. At the moment of that
transaction commit the space is already removed from _space and
from space cache, and can't be accessed from other triggers.

This patch makes all space-related actions in on_replace.

Part of #4086
@kyukhin kyukhin added the tmp label Aug 2, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@Gerold103 Gerold103

Labels
ddl feature A new functionality sql
Projects
None yet
Milestone
2.2.0
Development

No branches or pull requests
3 participants
@Totktonada @Gerold103 @kyukhin