2

The documentation says "delete cannot work with partial keys". What is your recommendation how to solve it. For example create new index, use cycle delete or any way?

Ivan Medvedev
  • 63
  • 1
  • 4

2 Answers2

2

You can delete values in a loop using a primary key.

#!/usr/bin/env tarantool

local json = require('json')

local function key_from_tuple(tuple, key_parts)
    local key = {}
    for _, part in ipairs(key_parts) do
        table.insert(key, tuple[part.fieldno] or box.NULL)
    end
    return key
end

box.cfg{}

box.once('init', function()
    box.schema.space.create('s')
    box.space.s:create_index('pk')
    box.space.s:create_index('sk', {
        unique = false,
        parts = {
            {2, 'number'},
            {3, 'number'},
        }
    })
end)

box.space.s:truncate()
box.space.s:insert{1, 1, 1}
box.space.s:insert{2, 1, 1}

print('before delete')
print('---')
box.space.s:pairs():each(function(tuple)
    print(json.encode(tuple))
end)
print('...')

local key_parts = box.space.s.index.pk.parts
for _, tuple in box.space.s.index.sk:pairs({1}) do
    local key = key_from_tuple(tuple, key_parts)
    box.space.s.index.pk:delete(key)
end

print('after delete')
print('---')
box.space.s:pairs():each(function(tuple)
    print(json.encode(tuple))
end)
print('...')

os.exit()

In the example above a common case is handled using key_from_tuple function. Things may be simpler when you know which fields form a primary key. Say, if it is the first field:

for _, tuple in box.space.s.index.sk:pairs({1}) do
    box.space.s.index.pk:delete(tuple[1])
end

The new key_def module that was added in tarantool-2.2.0-255-g22db9c264 (not released yet, but availiable from our 2.2 repository) simplifies extracting a key from a tuple, especially in case of json path indexes:

#!/usr/bin/env tarantool

local json = require('json')
local key_def_lib = require('key_def')

box.cfg{}

box.once('init', function()
    box.schema.space.create('s')
    box.space.s:create_index('pk')
    box.space.s:create_index('sk', {
        unique = false,
        parts = {
            {2, 'number', path = 'a'},
            {2, 'number', path = 'b'},
        }
    })
end)

box.space.s:truncate()
box.space.s:insert{1, {a = 1, b = 1}}
box.space.s:insert{2, {a = 1, b = 2}}

print('before delete')
print('---')
box.space.s:pairs():each(function(tuple)
    print(json.encode(tuple))
end)
print('...')

local key_def = key_def_lib.new(box.space.s.index.pk.parts)
for _, tuple in box.space.s.index.sk:pairs({1}) do
    local key = key_def:extract_key(tuple)
    box.space.s.index.pk:delete(key)
end

print('after delete')
print('---')
box.space.s:pairs():each(function(tuple)
    print(json.encode(tuple))
end)
print('...')

os.exit()

(source of the code)

Alexander Turenko
  • 415
  • 3
  • 9
  • Thanks! What is the difference between pairs() and select()? – Ivan Medvedev Jun 03 '19 at 22:44
  • select() fetches all matching tuples into the Lua memory at once that can cause a Lua error if the Lua memory limit will be reached (2 GiB AFAIR). pairs() creates an iterator that can be traversed with `for` tuple-by-tuple. General recommendation here is to use select() only when you are sure that the result set will be small. – Alexander Turenko Jun 04 '19 at 14:15
1

Starting from Tarantool 2.1, you can use SQL syntax for that ('delete from ... where ...').

However, be aware that Tarantool will try to perfrom this in a transaction, so if you're trying to delete too many tuples, it will lock transaction thread for some time.

Dmitry Sharonov
  • 471
  • 2
  • 12