Deleting documents

Deleting is only supported for:

• real-time tables,
• percolate tables
• distributed tables that contain only RT tables as agents

You can delete existing rows (documents) from an existing table based on ID or conditions.

Deleting documents is supported via SQL and HTTP interfaces.

SQL response for successful operation will show the number of rows deleted.

json/delete is an HTTP endpoint for deleting. The server will respond with a JSON object stating if the operation was successful or not and the number of rows deleted.

To delete all documents from a table it’s recommended to use instead the table truncation as it’s a much faster operation.

• SQL
• JSON

SQL JSON

DELETE FROM table WHERE where_condition

• table is a name of the table from which the row should be deleted.
• where_condition for SQL has the same syntax as in the SELECT statement.

POST /delete -d '
    {
     "index": "test",
     "id": 1
    }'
POST /delete -d '
    {
        "index": "test",
        "query":
        {
            "match": { "*": "apple" }
        }
    }'

• id for JSON is the row id which should be deleted.
• query for JSON is the full-text condition and has the same syntax as in the JSON/update.
• cluster for JSON is cluster name property and should be set along with table property to delete a row from a table which is inside a replication cluster.

In this example we are deleting all documents that match full-text query dummy from table named test:

• SQL
• JSON
• PHP
• Python
• javascript
• Java

SQL JSON PHP Python javascript Java

SELECT * FROM TEST;
DELETE FROM TEST WHERE MATCH ('dummy');
SELECT * FROM TEST;

POST /delete -d '
    {
        "index":"test",
        "query":
        {
            "match": { "*": "dummy" }
        }
    }'

$index->deleteDocuments(new Match('dummy','*'));

indexApi.delete({"index" : "products", "query": { "match": { "*": "dummy" }}})

res = await indexApi.delete({"index" : "products", "query": { "match": { "*": "dummy" }}});

DeleteDocumentRequest deleteRequest = new DeleteDocumentRequest();
query = new HashMap<String,Object>();
query.put("match",new HashMap<String,Object>(){{
    put("*","dummy");
}});
deleteRequest.index("products").setQuery(query); 
indexApi.delete(deleteRequest);

Response

+------+------+-------------+------+
| id   | gid  | mva1        | mva2 |
+------+------+-------------+------+
|  100 | 1000 | 100,201     | 100  |
|  101 | 1001 | 101,202     | 101  |
|  102 | 1002 | 102,203     | 102  |
|  103 | 1003 | 103,204     | 103  |
|  104 | 1004 | 104,204,205 | 104  |
|  105 | 1005 | 105,206     | 105  |
|  106 | 1006 | 106,207     | 106  |
|  107 | 1007 | 107,208     | 107  |
+------+------+-------------+------+
8 rows in set (0.00 sec)
Query OK, 2 rows affected (0.00 sec)
+------+------+-------------+------+
| id   | gid  | mva1        | mva2 |
+------+------+-------------+------+
|  100 | 1000 | 100,201     | 100  |
|  101 | 1001 | 101,202     | 101  |
|  102 | 1002 | 102,203     | 102  |
|  103 | 1003 | 103,204     | 103  |
|  104 | 1004 | 104,204,205 | 104  |
|  105 | 1005 | 105,206     | 105  |
+------+------+-------------+------+
6 rows in set (0.00 sec)

    {
        "_index":"test",
        "deleted":2,
    }

Array(
    [_index] => test
    [deleted] => 2
)

{'deleted': 2, 'id': None, 'index': 'products', 'result': None}

{"_index":"products","deleted":2}

class DeleteResponse {
    index: products
    deleted: 2
    id: null
    result: null
}

Here - deleting a document with id 100 from table named test:

• SQL
• JSON
• PHP
• Python
• javascript
• Java

SQL JSON PHP Python javascript Java

DELETE FROM TEST WHERE id=100;
SELECT * FROM TEST;

POST /delete -d '
    {
        "index":"test",
        "id": 100
    }'

$index->deleteDocument(100);

indexApi.delete({"index" : "products", "id" : 1})

res = await indexApi.delete({"index" : "products", "id" : 1});

DeleteDocumentRequest deleteRequest = new DeleteDocumentRequest();
deleteRequest.index("products").setId(1L); 
indexApi.delete(deleteRequest);

Response

Query OK, 1 rows affected (0.00 sec)
+------+------+-------------+------+
| id   | gid  | mva1        | mva2 |
+------+------+-------------+------+
|  101 | 1001 | 101,202     | 101  |
|  102 | 1002 | 102,203     | 102  |
|  103 | 1003 | 103,204     | 103  |
|  104 | 1004 | 104,204,205 | 104  |
|  105 | 1005 | 105,206     | 105  |
+------+------+-------------+------+
5 rows in set (0.00 sec)

    {
        "_index":"test",
        "_id":100,
        "found":true,
        "result":"deleted"      
    }

Array(
    [_index] => test
    [_id] => 100
    [found] => true
    [result] => deleted
)

{'deleted': None, 'id': 1, 'index': 'products', 'result': 'deleted'}

{"_index":"products","_id":1,"result":"deleted"}

class DeleteResponse {
    index: products
    _id: 1
    result: deleted
}

Manticore SQL allows to use complex conditions for the DELETE statement.

For example here we are deleting documents that match full-text query dummy and have attribute mva1 with a value greater than 206 or mva1 values 100 or 103 from table named test:

• SQL

SQL

DELETE FROM TEST WHERE MATCH ('dummy') AND ( mva1>206 or mva1 in (100, 103) );
SELECT * FROM TEST;

Response

Query OK, 4 rows affected (0.00 sec)
+------+------+-------------+------+
| id   | gid  | mva1        | mva2 |
+------+------+-------------+------+
|  101 | 1001 | 101,202     | 101  |
|  102 | 1002 | 102,203     | 102  |
|  104 | 1004 | 104,204,205 | 104  |
|  105 | 1005 | 105,206     | 105  |
+------+------+-------------+------+
6 rows in set (0.00 sec)

Here is an example of deleting documents in cluster nodes4‘s table test:

• SQL
• JSON
• PHP
• Python
• javascript
• Java

SQL JSON PHP Python javascript Java

delete from nodes4:test where id=100;

POST /delete -d '
    {
      "cluster":"nodes4",
      "index":"test",
      "id": 100
    }'

$index->setCluster('nodes4');
$index->deleteDocument(100);

indexApi.delete({"cluster":"nodes4","index" : "products", "id" : 100})

indexApi.delete({"cluster":"nodes4","index" : "products", "id" : 100})

DeleteDocumentRequest deleteRequest = new DeleteDocumentRequest();
deleteRequest.cluster("nodes4").index("products").setId(100L); 
indexApi.delete(deleteRequest);

Response

Array(
    [_index] => test
    [_id] => 100
    [found] => true
    [result] => deleted
)

{'deleted': None, 'id': 100, 'index': 'products', 'result': 'deleted'}

{"_index":"products","_id":100,"result":"deleted"}

class DeleteResponse {
    index: products
    _id: 100
    result: deleted
}

Transactions

Manticore supports basic transactions when performing deleting and insertion into real-time and percolate tables. That is: each change to a table is first saved into an internal changeset, and then is actually committed to the table. By default each command is wrapped into an individual automatic transaction, making it transparent: you just ‘insert’ something, and can see the inserted result after it completes, having no care about transactions. However that behaviour can be explicitly managed by starting and committing transactions manually.

Transactions are supported for the following commands:

• INSERT
• REPLACE
• DELETE

Transactions are not supported for:

• UPDATE (which is different from the REPLACE in that it does in-place attribute update).
• ALTER - for updating table schema
• TRUNCATE - for emptying a real-time table
• ATTACH - for attaching a plain table to a real-time table
• CREATE - for creating a table
• DROP - for deleting a table

Please also note, that transactions in Manticore do not aim to provide isolation. The idea of transactions in Manticore is to let you accumulate a number of writes and run them at once at commit or rollback them all if needed. Transactions are integrated with binary log which gives durability and consistency.

Automatic and manual mode

SET AUTOCOMMIT = {0 | 1}

SET AUTOCOMMIT controls the autocommit mode in the active session. AUTOCOMMIT is set to 1 by default. With default you have not to care about transactions, since every statement that performs any changes on any table is implicitly wrapped into separate transaction. Setting it to 0 allows you to manage transactions manually. I.e., they will not be visible until you explicitly commit them.

Transactions are limited to a single RT or percolate table, and also limited in size. They are atomic, consistent, overly isolated, and durable. Overly isolated means that the changes are not only invisible to the concurrent transactions but even to the current session itself.

BEGIN, COMMIT, and ROLLBACK

START TRANSACTION | BEGIN
COMMIT
ROLLBACK

BEGIN statement (or its START TRANSACTION alias) forcibly commits pending transaction, if any, and begins a new one.

COMMIT statement commits the current transaction, making all its changes permanent.

ROLLBACK statement rolls back the current transaction, canceling all its changes.

Examples

Automatic commits (default)

insert into indexrt (id, content, title, channel_id, published) values (1, 'aa', 'blabla', 1, 10);
Query OK, 1 rows affected (0.00 sec)
select * from indexrt where id=1;
+------+------------+-----------+--------+
| id   | channel_id | published | title  |
+------+------------+-----------+--------+
|    1 |          1 |        10 | blabla |
+------+------------+-----------+--------+
1 row in set (0.00 sec)

Inserted value immediately visible by following ‘select’ statement.

Manual commits (autocommit=0)

set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
insert into indexrt (id, content, title, channel_id, published) values (3, 'aa', 'bb', 1, 1);
Query OK, 1 row affected (0.00 sec)
insert into indexrt (id, content, title, channel_id, published) values (4, 'aa', 'bb', 1, 1);
Query OK, 1 row affected (0.00 sec)
select * from indexrt where id=3;
Empty set (0.01 sec)
select * from indexrt where id=4;
Empty set (0.00 sec)

Here changes is NOT automatically committed. So, insertion is not visible even in the same session, since they’re not committed. Also, despite absent BEGIN statement, transaction is implicitly started.

So, let’s finally commit it:

commit;
Query OK, 0 rows affected (0.00 sec)
select * from indexrt where id=4;
+------+------------+-----------+-------+
| id   | channel_id | published | title |
+------+------------+-----------+-------+
|    4 |          1 |         1 | bb    |
+------+------------+-----------+-------+
1 row in set (0.00 sec)
select * from indexrt where id=3;
+------+------------+-----------+-------+
| id   | channel_id | published | title |
+------+------------+-----------+-------+
|    3 |          1 |         1 | bb    |
+------+------------+-----------+-------+
1 row in set (0.00 sec)

Now it is finished and visible.

Manual transaction

Using BEGIN and COMMIT you can define bounds of transaction explicitly, so no need to care about autocommit in this case.

begin;
Query OK, 0 rows affected (0.00 sec)
insert into indexrt (id, content, title, channel_id, published) values (2, 'aa', 'bb', 1, 1);
Query OK, 1 row affected (0.00 sec)
select * from indexrt where id=2;
Empty set (0.01 sec)
commit;
Query OK, 0 rows affected (0.01 sec)
select * from indexrt where id=2;
+------+------------+-----------+-------+
| id   | channel_id | published | title |
+------+------------+-----------+-------+
|    2 |          1 |         1 | bb    |
+------+------------+-----------+-------+
1 row in set (0.01 sec)

SET TRANSACTION

SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SERIALIZABLE }

SET TRANSACTION statement does nothing. It was implemented to maintain compatibility with 3rd party MySQL client libraries, connectors, and frameworks that may need to run this statement when connecting. They just goes across syntax parser and then returns ‘ok’. Nothing usable for your own programs, just a stubs to make third-party clients happy.

mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

✔ Searching