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

[Peter Gulutzan <pgulutzan@ocelot.ca>]

Hi,

On 2019-12-17 3:11 p.m., Alexander Turenko wrote:
 > Mergen,
 >
 > I don't have enough context here, but have questions about this
 > proposal. Sorry if they were already discussed and I missed it.
 >
 > Most important question is that I don't understand why we don't want to
 > provide language specific APIs for sessions settings. It looks both more
 > convenient and more performant.
 >
 > See this and other questions and notes below.
 >
 > (CCed Peter, because he may have some opinion how SQL API should look.)
 >
 > WBR, Alexader Turenko.
 >
 > ----
 >
 > AFAIR discussions around a space / view for session settings arose from
 > Kostya O. proposal to move toward support of standard information schema
 > views (please, correct me, if I remember it wrongly). Then it becomes
 > out of scope somehow. But okay, let it being out.
 >

I don't know whether you are wrong, but I have looked at the dev thread
that Imeev Mergen started on September 12,
"[dev] Replacing control pragmas by SET".
It seemed to me that Imeev Mergen + Nikita Pettik + Konstantin Osipov
all collaborated about having a system space.


 > (BTW, I looked over SQL/Schemata 2011 and don't found anything like
 > MySQL's GLOBAL_VARIABLES and SESSION_VARIABLES tables. It seems there is
 > no standard table for session variables. I don't sure however.)
 >

You are right, there is no standard table.

 > ----
 >
 > We have two basic variants:
 >
 > * Implement an API for session settings for each supported language
 >   (C, Lua, SQL) and a protocol for connectors.
 > * Provide a system view / space (this is proposed by Mergen).
 >
 > First, a space / view is not most convenient way to operate on session
 > settings from a language. Let's compare.
 >
 > Lua:
 >
 >  | box.space._vsession_settings:get({'sql_default_engine'}).value
 >  | box.space._vsession_settings:update({'sql_default_engine'},
 >  |                                     {{'=', 'value', 'vinyl'}})
 >  |
 >  | box.session.settings:get('sql_default_engine')
 >  | box.session.settings:set('sql_default_engine', 'vinyl')
 >
 > SQL:
 >
 >  | SELECT "value" FROM "_vsession_settings" WHERE "name" = 
'sql_default_engine'
 >  | UPDATE "_vsession_settings" SET "value" = 'vinyl' \
 >  |                             WHERE "name" = 'sql_default_engine'
 >  |
 >  | SESSION GET 'sql_default_engine'
 >  | SESSION SET 'sql_default_engine' = 'vinyl'

 >

 From Issue#4511 "sql: replace PRAGMA by SET for some pragmas"
I gather that Imeev Mergen is already working on something very close to
your UPDATE "_vsession_settings" example.
But Imeev Mergen says I can't SELECT from this space, I don't know why not.
Anyway, if a space exists, SESSION GET or SESSION SET would be redundant.

 > C (sketchy):
 >
 >  | /* Read from a _vsession_settings. */
 >  |
 >  | enum {
 >  |         BOX_VSESSION_SETTINGS_VALUE_ID = 2
 >  | };
 >  |
 >  | char key[32];
 >  | char *key_end = key;
 >  | key_end = mp_encode_array(key_end, 1);
 >  | key_end = mp_encode_str(key_end, "sql_default_engine",
 >  |                         sizeof("sql_default_engine") - 1);
 >  |
 >  | box_tuple_t *tuple;
 >  | box_iterator_t *it = box_index_iterator(BOX_VSESSION_SETTINGS_ID, 
0, ITER_EQ,
 >  |                                         key, key_end);
 >  | box_iterator_next(it, &tuple);
 >  | const char *buf = box_tuple_field(tuple, 
BOX_VSESSION_SETTINGS_VALUE_ID);
 >  |
 >  | uint32_t engine_len;
 >  | const char *engine = mp_decode_str(&buf, &engine_len);
 >  |
 >  | box_iterator_free(it);
 >  |
 >  | /* Update a value in _vsession_settings. */
 >  |
 >  | <I'll skip it.>
 >  |
 >  | /* Get and set with a language aware API. */
 >  |
 >  | uint32_t engine_len;
 >  | const char *engine = box_session_get_str(SESSION_SQL_DEFAULT_ENGINE,
 >  |                                          &engine_len);
 >  |
 >  | box_session_set_str(SESSION_SQL_DEFAULT_ENGINE, "vinyl",
 >  |                     sizeof("vinyl") - 1);
 >
 > Languare-aware APIs above are just examples. I propose to implement such
 > APIs, but not how they should look exactly.
 >
 > To sum the examples up: it seems for me that language aware APIs are a
 > way more simple for a user.

To me, the SQL looks way more simple. But I admit I am prejudiced.

 >
 > Second, all tuples are msgpack encoded (at least now). So any get/set
 > operation on _vspace_settings will require to encode and decode msgpack
 > (yep, both encode and decode at once). It will be surely less performant
 > then a hashmap lookup (session id -> struct session_settings) plus a
 > field access.
 >
 > So, language aware API can be implemented in more performant way then
 > general space-like one.
 >
 > It seems that we anyway need an API for connectors. So we can provide
 > the proposed view, but don't use it internally to implement language
 > specific APIs (for performance).
 > ----
 >
 > Re SET / SHOW, GET / SELECT -- I don't think it really matters so much.
 > Maybe we should look for SQL/PSM and catch some syntax to make things
 > look consistent in a future. Maybe we should add a keyword SESSION (as
 > in my SQL API examples) to avoid possible future incompatibilities (say,
 > with SQL/PSM).
 >
 > ----

Oracle has a variety. For example changing the encryption wallet is done
with special statements (ALTER SYSTEM etc.) and reading is done
with special tables (V$WALLET etc.).
https://www.morganslibrary.org/reference/wallet.html
Here I believe we're getting into an area where trying for compatibility
would be difficult and not rewarding. But I also believe that by
using a system table we'd be slightly more compatible than we are now.

 >
 > Console session settings (like statements delimiter, input language,
 > output format) are out of scope here?

I have documented PRAGMA in the version-2.3 manual.
If you believe it should be removed before this becomes the master manual,
please discuss with Roman Khabibov.

Peter Gulutzan