From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: References: <20191206113711.ctzr6x7sqbpr3xkd@tarantool.org> <20191206135009.GA6394@tarantool.org> <20191217221143.parwzoyb3e327sw5@tkn_work_nb> From: Peter Gulutzan Message-ID: <77dd60db-ba26-da21-9502-19f089f1559c@ocelot.ca> Date: Tue, 17 Dec 2019 19:39:18 -0700 MIME-Version: 1.0 In-Reply-To: <20191217221143.parwzoyb3e327sw5@tkn_work_nb> Content-Type: text/plain; charset="utf-8"; format="flowed" Content-Transfer-Encoding: 8bit Content-Language: en-US Subject: Re: [Tarantool-patches] [PATCH v3 0/5] Replace control pragmas by SET List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: Alexander Turenko , Mergen Imeev Cc: v.shpilevoy@tarantool.org, tarantool-discussions@dev.tarantool.org, tarantool-patches@dev.tarantool.org 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. */ >  | >  | >  | >  | /* 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