From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from cmta19.telus.net (cmta19.telus.net [209.171.16.92]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 873864696C3 for ; Wed, 18 Dec 2019 20:40:04 +0300 (MSK) From: Peter Gulutzan References: <20191206113711.ctzr6x7sqbpr3xkd@tarantool.org> <20191206135009.GA6394@tarantool.org> <20191217221143.parwzoyb3e327sw5@tkn_work_nb> <77dd60db-ba26-da21-9502-19f089f1559c@ocelot.ca> Message-ID: <67f8badf-2c98-83de-b2aa-781953be5a21@ocelot.ca> Date: Wed, 18 Dec 2019 10:39:58 -0700 MIME-Version: 1.0 In-Reply-To: <77dd60db-ba26-da21-9502-19f089f1559c@ocelot.ca> 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: Mergen Imeev Cc: tarantool-patches@dev.tarantool.org, v.shpilevoy@tarantool.org, tarantool-discussions@dev.tarantool.org Hi, 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. 1. 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   rows:   - ['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? 2. 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']]) or box.space._session_settings:update('sql_default_engine', {{'=', 'value', 'vinyl'}}) 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? 3. 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 compatibility. You might of course dismiss this because our column name is "value" not value. 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 marks: " 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 = 'sql_default_engine';]]) --- - metadata:   - name: VALUE     type: any   - name: NAME     type: string   rows:   - ['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. 4. This suggestion was accepted according to Issue#4511: _session_settings has columns 'name' and 'value', so: UPDATE "session_settings" SET "value" = 'vinyl' WHERE "name" = 'sql_default_engine'; 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" = 'sql_default_engine'; I think that a few other things are simpler if sql_default_engine is a column: CHECK ("sql_default_engine" IN ('memtx','vinyl')) versus CHECK ("name" <> 'sql_default_engine' OR "value" IN ('memtx','vinyl')) and SELECT "sql_default_engine", "parser_trace" FROM "_vsession_settings"; versus SELECT "value" AS sql_default_engine FROM "_vsession_settings" WHERE "name" = 'sql_default_engine' UNION SELECT "value" AS parser_trace FROM "_vsession_settings" WHERE "name" = 'parser_trace'; (I say "I think" and admit that there may be better solutions I didn't think about.) 5. 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