Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e97D9-0004ao-0L for pgsql-hackers@arkaria.postgresql.org; Mon, 30 Oct 2017 10:22:27 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e97D8-0005F0-JB for pgsql-hackers@arkaria.postgresql.org; Mon, 30 Oct 2017 10:22:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e8l8P-0003wR-C8 for pgsql-hackers@postgresql.org; Sun, 29 Oct 2017 10:48:05 +0000 Received: from mail-pg0-x241.google.com ([2607:f8b0:400e:c05::241]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1e8l8L-0008Is-Ey for pgsql-hackers@postgresql.org; Sun, 29 Oct 2017 10:48:04 +0000 Received: by mail-pg0-x241.google.com with SMTP id r25so8876372pgn.4 for ; Sun, 29 Oct 2017 03:48:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=CWE04y/lLnI8iPjsPHVvnzhyzBiRKVYGAQEACtNxZwM=; b=VPYvtgrK9epfnWV3bpD2rbz9/nbFw3A+Eoo/faVpHPIp5C9Z6qeCYqYgYKUW2fUgkU kmovRYKm3UKYu0TGprT9vBdNoiRcN/lOuB/qn2oKoCBNcPwC9Hi4LTFKKYQMqA5zirJS rPgrAlbEQTlu9EgNFrXk14aQ6xeh9hV0CSexf3xdLSb5QYnaYHTJwfKZqre37JIP8eqF MRAFtekmNf5g8duWJotepWKp9SAsgEmBYAjfsIPakVRG2c1boQkZ5V4mZ1AsgeSdJ1Bq opWXmK6T6MPGw92iuvOYCLukk0PRhSXNu73+0RxPKyLwHA/5Sq8IzuqBo22pM1tmLTx9 T0Yw== 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=CWE04y/lLnI8iPjsPHVvnzhyzBiRKVYGAQEACtNxZwM=; b=nS1uYCRVnL8PODUpHlygnkvcv1HL8TU1SCwVo2hbOKzP0j8z0OCyLDENnGZ7/eUPCa t1nI10i2d4xcrUZ5nWf+/LuNzuxEol6gG4z4imYk2dZV0jQX5ZdGRQJEkZfG0AtDdajK f8adA1oBN19BG6yl8/7mZwfIxF5TkaTSEH4dRLD/WhNNNn7JqU8ixLvSHQNx86CiUzZH +2CMhHMqwxfwCLy5HqytcE/ICBZOuxuuLhMsX1zGSun1C8q5JT7qUGtLndiVTEGrUytk U7bqcAU8AdVam+0YHftjyCPHXqjkWcykqxnhfd6uynKl/Hcvu5QFWxomntOIUgetNZpj pvAA== X-Gm-Message-State: AMCzsaVqZax7FqIJ2hHIQi2b8rwfGudpBzXff5PoKxCUQiUanTSK6w7s 0h+m9XD/9uVHE5kNKBWxSc1c/mdH4CTDYjm6DcyzGg== X-Google-Smtp-Source: ABhQp+RYhSgc4nrTZ0jB6fscR5A3SwPGRQIyK+Ve6X3DSCg3dumNf/lB5hpVwHtvQhMBfomIZ+QtGYRYgqkYXKyqgoE= X-Received: by 10.84.168.5 with SMTP id e5mr4645054plb.150.1509274078751; Sun, 29 Oct 2017 03:47:58 -0700 (PDT) MIME-Version: 1.0 Received: by 10.100.159.132 with HTTP; Sun, 29 Oct 2017 03:47:58 -0700 (PDT) In-Reply-To: References: From: Hannu Krosing Date: Sun, 29 Oct 2017 11:47:58 +0100 Message-ID: Subject: Re: proposal: schema variables To: Chris Travers Cc: Pavel Stehule , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="94eb2c13ec8e556579055cad4105" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org --94eb2c13ec8e556579055cad4105 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable but you can always do with a (id, value) as ( values (1, 'foo'), (2, 'bar'), (3, 'baz') ) select set_config('custom.value',(select value from a where id =3D 2),true)= ; if you are worried about the evaluation order On 29 October 2017 at 09:51, Chris Travers wrote= : > > > On Sat, Oct 28, 2017 at 4:56 PM, Pavel Stehule > wrote: > >> >>> >> The creating database objects and necessary infrastructure is the most >> simple task of this project. I'll be more happy if there are zero >> intersection because variables and GUC are designed for different purpos= es. >> But due SET keyword the intersection there is. >> >> When I thinking about it, I have only one, but important reason, why I >> prefer design new type of database object -the GUC are stack based with >> different default granularity - global, database, user, session, functio= n. >> This can be unwanted behave for variables - it can be source of hard to >> detected bugs. I afraid so this behave can be too messy for usage as >> variables. >> >> @1 I have not clean opinion about it - not sure if rights are good enoug= h >> - probably some user limits can be more practical - but can be hard to >> choose result when some user limits and GUC will be against >> > > I was mostly thinking that users can probably set things like work_mem an= d > possibly this might be a problem. > > >> @2 With variables typed custom GUC are not necessary >> > > I don't know about that. For example with the geoip2lookup extension it > is nice that you could set the preferred language for translation on a pe= r > user basis or the mmdb path on a per-db basis. > > >> @3 Why you need it? It is possible with set_config function now. >> > > Yeah you could do it safely with set_config and a CTE, but suppose I have= : > > with a (Id, value) as (values (1::Int, 'foo'), (2, 'bar'), (3, 'baz')) > SELECT set_config('custom_val', value) from a where id =3D 2; > > What is the result out of this? I would *expect* that this would probabl= y > run set_config 3 times and filter the output. > > >> >> Regards >> >> Pavel >> >> >> >> >>> >>> >>>> regards >>>> >>>> Pavel >>>> >>>> >>>> >>> >>> >>> -- >>> Best Regards, >>> Chris Travers >>> Database Administrator >>> >>> Tel: +49 162 9037 210 <+49%20162%209037210> | Skype: einhverfr | >>> www.adjust.com >>> Saarbr=C3=BCcker Stra=C3=9Fe 37a, 10405 Berlin >>> >>> >>> >> > > > -- > Best Regards, > Chris Travers > Database Administrator > > Tel: +49 162 9037 210 <+49%20162%209037210> | Skype: einhverfr | > www.adjust.com > Saarbr=C3=BCcker Stra=C3=9Fe 37a, 10405 Berlin > > > --94eb2c13ec8e556579055cad4105 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
but you can always do=C2=A0

with a (id, value)= as (
=C2=A0 values (1, 'foo'), (2, 'bar'), (3, &= #39;baz')
)
select set_config('custom.value'= ;,(select value from a where id =3D 2),true);

if you ar= e worried about the evaluation order

On 29 October 2017 at 09:51, Chris Travers <chris.travers@adjust.com> wrote:


On Sat, Oct 28, 2017 at 4:56 PM, Pavel Stehule= <pavel.stehule@gmail.com> wrote:


<= /div>
The creating database objects and necessary infrastructure = is the most simple task of this project. I'll be more happy if there ar= e zero intersection because variables and GUC are designed for different pu= rposes. But due SET keyword the intersection there is.

=
When I thinking about it, I have only one, but important reason, why I= prefer design new type of database object -the GUC are stack based with di= fferent default granularity - global, database, user, session, function. Th= is can be unwanted behave for variables - it can be source of hard to detec= ted bugs. I afraid so this behave can be too messy for usage as variables. =

@1 I have not clean opinion about it - not su= re if rights are good enough - probably some user limits can be more practi= cal - but can be hard to choose result when some user limits and GUC will b= e against

<= div>I was mostly thinking that users can probably set things like work_mem = and possibly this might be a problem.
=C2=A0
@2 With variables typed custom GUC a= re not necessary

<= /span>
I don't know about that.=C2=A0 For example with the geoip2lo= okup extension it is nice that you could set the preferred language for tra= nslation on a per user basis or the mmdb path on a per-db basis.
=C2=A0
@3 Why yo= u need it? It is possible with set_config function now.

Yeah you could do it safe= ly with set_config and a CTE, but suppose I have:

= with a (Id, value) as (values (1::Int, 'foo'), (2, 'bar'), = (3, 'baz'))
SELECT set_config('custom_val', value= ) from a where id =3D 2;

What is the result out of= this?=C2=A0 I would *expect* that this would probably run set_config 3 tim= es and filter the output.
=C2=A0

Regards

Pavel


=C2=A0
=
=


regards

Pavel
<= div>


<= /font>


<= br>
--
Best Regards,
Chris = Travers
Database Administrator

Tel:=C2=A0+49 162 9037 210= =C2=A0| Skype: einhverfr= |=C2=A0www.adjust.com=C2=A0

<= /div>




<= /div>--
Best Regards,
Chris Travers
Database Administrator


<= /div>

--94eb2c13ec8e556579055cad4105--