[Tarantool-discussions] [Tarantool-patches] [PATCH v3 0/5] Replace control pragmas by SET

Peter Gulutzan pgulutzan at ocelot.ca
Wed Dec 18 20:39:58 MSK 2019


I think that there still might be a few small SQL-specific issues.
I pulled today from branch imeevma/gh-4511-pragma-replaced-by-set.


The type of "value" is 'any', which is shown in metadata as 'string':
tarantool> box.execute([[SELECT typeof("value") FROM 
"_vsession_settings" LIMIT 1;]])
- metadata:
   - name: typeof("value")
     type: string
   - ['any']

But that means that searches of "value" will usually fail, thus:
tarantool> box.execute([[SELECT "name", "value" FROM 
"_vsession_settings" where "value" = 'vinyl';]])
- null
- 'Type mismatch: can not convert text to boolean'

Why not scalar?


You wrote this comment on issue#4511:
@pgulutzan @kostja It seems that we are going to solve the problem in a 
different way: we will create a special space that will contain the 
settings. This space will only allow updates. So, to change the setting 
value, you should do something like this:
box.execute([[UPDATE "_session_settings" SET "value" = 'vinyl' WHERE 
"name" = 'sql_default_engine']])
box.space._session_settings:update('sql_default_engine', {{'=', 'value', 
This will allow us to use the same method to change any settings in any 
front-end. Also after that we can simply remove the control p
But at this moment _session_settings does not exist.
The only thing that works is
box.execute([[SET sql_default_engine = 'vinyl';]])
which is what I and Konstantin Osipov were raising questions about.
In fact I can do it without 'write' privileges on anything.

This is just temporary, right?


VALUE happens to be a reserved word in DB2 and Oracle.
Although we have no plans to reserve it, well, it's a micro-issue for 
You might of course dismiss this because our column name is "value" not 
But suppose that some user agrees totally with Konstantin Osipov (alas),
and decides to use a method that allows access to the data without quote 
tarantool> box.execute([[CREATE VIEW vsession_settings AS SELECT "value" 
AS value, "name" AS name FROM "_vsession_settings";]])
- row_count: 1

tarantool> box.execute([[SELECT * FROM vsession_settings WHERE name = 
- metadata:
   - name: VALUE
     type: any
   - name: NAME
     type: string
   - ['vinyl', 'sql_default_engine']
I think this means that some users will have columns named VALUE,
so we will cause trouble if someday we reserve it.


This suggestion was accepted according to Issue#4511:
_session_settings has columns 'name' and 'value', so:
UPDATE "session_settings" SET "value" = 'vinyl' WHERE "name" = 
This suggestion (made by N. Pettik in the thread) was rejected:
_session_settings has many columns including 'sql_default_engine' and 
'value', so
UPDATE "session_settings" SET "sql_default_engine" = 'vinyl';

Okay, but ...

If we someday support GRANT, and sql_default_engine was a column, we 
could say:
GRANT UPDATE ON "_session_settings" ("sql_default_engine");
that is, we could be very specific about what you can update.
But there is no equivalent GRANT statement, with #4511,
unless one adds a non-standard extension like
GRANT UPDATE on "_session_settings" ("value") WHERE "name" = 

I think that a few other things are simpler if sql_default_engine is a 
CHECK ("sql_default_engine" IN ('memtx','vinyl')) versus
CHECK ("name" <> 'sql_default_engine' OR "value" IN ('memtx','vinyl'))
SELECT "sql_default_engine", "parser_trace" FROM "_vsession_settings"; 
SELECT "value" AS sql_default_engine FROM "_vsession_settings" WHERE 
"name" = 'sql_default_engine'
SELECT "value" AS parser_trace FROM "_vsession_settings" WHERE "name" = 
(I say "I think" and admit that there may be better solutions I didn't 
think about.)


The name _session_settings hints that, if I change the setting, I only 
affect my own session.
However, as you know, if I change sql_compound_select_limit, I affect 
all sessions.
So perhaps it should be in a new different space, _vglobal_settings?

Peter Gulutzan

More information about the Tarantool-discussions mailing list