Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1ehrnJ-0003KR-BW for pgsql-hackers@arkaria.postgresql.org; Sat, 03 Feb 2018 06:59:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ehrnI-0005nA-FV for pgsql-hackers@arkaria.postgresql.org; Sat, 03 Feb 2018 06:59:24 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ehrnH-0005n0-TB for pgsql-hackers@lists.postgresql.org; Sat, 03 Feb 2018 06:59:24 +0000 Received: from mail-wm0-x241.google.com ([2a00:1450:400c:c09::241]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ehrnA-0003Fc-Km for pgsql-hackers@postgresql.org; Sat, 03 Feb 2018 06:59:22 +0000 Received: by mail-wm0-x241.google.com with SMTP id r78so16441487wme.0 for ; Fri, 02 Feb 2018 22:59:15 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=KEsO3ZR8IPbs5sdwhikLPo3VUu4WX3s4LKYlv2vZ3Lo=; b=IA8FCurqpJc6UpPi4rYPcM3OY0YpTS1ymtVMKYQ3APcOLUfBRhHxl+HwRQzp+j3Wdj 1+6qshgE17lhVtIZM4zwsj+GC45+zSP0esdoQUOurzpnNI1s39gwuV868zNGVZ2hVMkH DuVsh2WT8slj8NNNND8JFGfUJAs4xte1cILLmAZuJESwHCWfbNYtxIDn97hQj1xCMMUT zWmw+CPZncasi8miehVO7YY+UUu+ZA0jWtb7EWhQ2AmFKB9VuHCXObWAE1YheHx9s6pT PuWCnf2HPC0PpTKN3NYPqcZN4hkPUn7njNQ0ZyOBzMFGximzD/zzZs8udqe2TSP/UaFK rH4A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=KEsO3ZR8IPbs5sdwhikLPo3VUu4WX3s4LKYlv2vZ3Lo=; b=kBnfQfL8yStC/ICnYKbKh15Jk4ltihTkOIAf/k+nzglzc4SkKF782N8U/+r8Ndr5Jc 8e8GHt+s2rPQAJ/C5w3xikOCRvLBqjsxHXWHcUXl21WgnpyIQnVestW2NlxRHOKG6C9X /kwAL7pvW40EOLdNjTAEIUP6jf+aQ83VCqzSwLGoXCW71GmfS7brjV40ZcfrzX4YgOf9 2144BD53WeMsTkJ3bKArb70gQVOqxFC3cYIME2FEwi91XLvKilAJ5zY6OAhU+n/Hu7qs aKn5B9cDoF0YH+S6+5R8JmK4kDZS7U3HEMXgcxb3NraK3Lw8GpcClLqbf//bclChO2gs 1idQ== X-Gm-Message-State: AKwxytdi0GsYPTmsSR/K0w1r9Uy+kx3Smwm/5FqdhYsgY5wBfIC2OtKP +CusM+WbrH3UGqTyOrQhHOirTINggAdH0zPyhiM= X-Google-Smtp-Source: AH8x225YUqVOyneoOsRS7ybt8O4eK/Vs8XwqFdPdRGQ7ZdKv3CtTngCUw6mrqu4JZtDunRkbPIIvvcMOslBy94Lu2iI= X-Received: by 10.28.232.72 with SMTP id f69mr28301012wmh.110.1517641153990; Fri, 02 Feb 2018 22:59:13 -0800 (PST) MIME-Version: 1.0 Received: by 10.223.196.70 with HTTP; Fri, 2 Feb 2018 22:58:33 -0800 (PST) In-Reply-To: References: <623395617.20171113141500@gf.microolap.com> From: Pavel Stehule Date: Sat, 3 Feb 2018 07:58:33 +0100 Message-ID: Subject: Re: [HACKERS] proposal: schema variables To: "David G. Johnston" Cc: Pavel Golub , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="001a11477e8ae1572e0564495d1c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --001a11477e8ae1572e0564495d1c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi 2018-02-03 1:48 GMT+01:00 David G. Johnston : > =E2=80=8BI've done a non-compilation documentation review, the diff from = the poc > patch and the diff from master are attached. > > Comments are inter-twined in the patch in xml comment format; though I > reiterate (some of?) them below. > > On Fri, Feb 2, 2018 at 3:06 PM, Pavel Stehule > wrote: > >> Hi >> >> I wrote proof concept of schema variables. The patch is not nice, but th= e >> functionality is almost complete (for scalars only) and can be good enou= gh >> for playing with this concept. >> >> I recap a goals (the order is random): >> >> 1. feature like PL/SQL package variables (with similar content life cycl= e) >> 2. available from any PL used by PostgreSQL, data can be shared between >> different PL >> 3. possibility to store short life data in fast secured storage >> > > =E2=80=8BThe generic use of the word secure here bothers me. I'm taking = it to be > "protected by grant/revoke"-based privileges; plus session-locality. > I have not a problem with any other formulation. > > 4. possibility to pass parameters and results to/from anonymous blocks >> 5. session variables with possibility to process static code check >> > > What does "process static code check" means here?=E2=80=8B > It mean the possibility to check validity of code without code execution. You can use plpgsql_check for example. > > >> 6. multiple API available from different environments - SQL commands, SQ= L >> functions, internal functions >> > > I made the public aspect of this explicit in the CREATE VARIABLE doc > (though as noted below it probably belongs in section II) > =E2=80=8B > >> 7. data are stored in binary form >> > > Thoughts during my review: > > There is, for me, a cognitive dissonance between "schema variable" and > "variable value" - I'm partial to the later. Since we use "setting" for > GUCs the term variable here hopefully wouldn't cause ambiguity... > The "schema" is important in this case. 1) it is a analogy to "package variable", 2) not necessary, but probably often it will be used together with PLpgSQL. There are variables too. "Session variables" doesn't well specify the implementation. The session variables can be GUC, psql client variables or some custom implementation in Postgres or package variables in Oracle. > I've noticed that we don't seem to have or enforce any policy on how to > communicate "SQL standards compatibility" to the user... > > We are missing the ability to alter ownership (or at least its > undocumented), and if that brings into existing ALTER VARIABLE we should > probably add ALTER TYPE TO new_type USING (cast) for completeness. > good note. I didn't test it. I am not sure, what variants of ALTER should be supported. Type of variables is interface. Probably we can allow to add new field, but change type or remove field can break other object. So it can be prohibited like we doesn't support ALTER on views. ALTERing is another and pretty complex topic, and I don't think it is necessary to solve it now. This feature can be valuable without ALTER support, and nothing block later ALTER VARIABLE implementation. This design allows lot of interesting features (that can be implemented step by step) 1. support for default expression 2. support for constraints and maybe triggers 3. reset on transaction end 4. initialization of session start - via default expression or triggers it can be way how to start code on session start. > > Its left for the reader to presume that because these are schema > "relations" that namespace resolution via search_path works the same as a= ny > other relation. > > I think I've answered my own question regarding DISCARD in that > "variables" discards values while if TEMP is in effect all temp variables > are dropped. > DISCARD should to remove TEMP variables and should to remove content of all variables. > > Examples abound though it doesn't feel like too much: but saying "The > usage is very simple:" before giving the example in the function section > seems to be outside of our general style. A better preamble than "An > example:" would be nice but the example is so simple I could not think of > anything worth writing. > This doc is just design frame. I invite any enhancing because this feature can be difficult for some people, because mix persistent object with temporal/session content - and term "variable" can be used in relation algebra in different semantic. It is natural for people with stored procedures experience - mainly with Oracle, but for any other can be little bit difficult. I believe so there should be more practical examples - related to RLS for example. > > Its worth considering how both: > > https://www.postgresql.org/docs/10/static/ddl.html > and > https://www.postgresql.org/docs/10/static/queries.html > > could be updated to incorporate the broad picture of schema variables, > with examples, and leave the reference (SQL and functions) sections mainl= y > relegated to syntax and reminders. > > A moderate number of lines changed are for typos and minor grammar edits. > Thank you very much Regards Pavel > David J. > > --001a11477e8ae1572e0564495d1c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

2018-02-03 1:48 GMT+01:00 David G. Johnston &l= t;david.g.j= ohnston@gmail.com>:



I've noticed that we don't seem = to have or enforce any policy on how to communicate "SQL standards com= patibility" to the user...

We are missing the ability to alter ownership (or at least its undocum= ented), and if that brings into existing ALTER VARIABLE we should probably = add ALTER TYPE TO new_type USING (cast) for completeness.
=



=
Its worth considering= how both:

<= /div>
and=C2=A0

could be updated to incorporate the broad picture of schema vari= ables, with examples, and leave the reference (SQL and functions) sections = mainly relegated to syntax and reminders.

A moderate number of lines changed are for typos and = minor grammar edits.

Thank you very much

Regards

Pavel

David J.
<= br>

--001a11477e8ae1572e0564495d1c--