Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tACNg-00EInV-1H for pgsql-hackers@arkaria.postgresql.org; Sun, 10 Nov 2024 18:09:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tACNd-00840L-GE for pgsql-hackers@arkaria.postgresql.org; Sun, 10 Nov 2024 18:09:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tACNd-00840D-4k for pgsql-hackers@lists.postgresql.org; Sun, 10 Nov 2024 18:09:45 +0000 Received: from mail-yb1-xb35.google.com ([2607:f8b0:4864:20::b35]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tACNa-001EKk-4v for pgsql-hackers@lists.postgresql.org; Sun, 10 Nov 2024 18:09:45 +0000 Received: by mail-yb1-xb35.google.com with SMTP id 3f1490d57ef6-e2e2baf1087so3577889276.2 for ; Sun, 10 Nov 2024 10:09:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731262182; x=1731866982; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=klApwgh9aX0uGDruvkUJLr66L7sNMoR/cfhnueE+I+g=; b=WfMmORrdE1UCO5o9J8Kb7w2LUCm8qOE3lcr/yaGIsvOVWAh+rd5fmUiIQjpBaFjo4/ CpiezctrqYhkT4I/4lHhz6QVlzTDlBw3lV7Cj7i/Wy3eDXIsbXv4ImF7QHxA6vTMqPLV 9Jrd2WPu0hLZMdAlPI06u7kX+67klHn8rElfKk3VsKwKayMuMrmbyjby0xkPkWf8jxsy oT6lgcGsIDJceoWlZoe3wGsghApgFNf//ZxCdqP/dtkLAw4nAsHDR8ExBEt3JnMV7bdz 2G/w1T2b91cfdX9gK4PMzdqtJQb1HODNzPRloaUyw1oYgsPz72o5JtqUOT8A3l38NVbQ LnFQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731262182; x=1731866982; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=klApwgh9aX0uGDruvkUJLr66L7sNMoR/cfhnueE+I+g=; b=WPKMRFrvneRU3B82eq0f0qT1QQMhYqyIbZvwxUOyps/NODTGLhK9Po21tBjIKUKgmU 52DJhLQRu4/DDq81ZVTfLSjYM3LxCztX/qlkS2AI74rzHJBflIMDcR4vnB2eqDN7dKfA XoUjgRYEvPV/hu0OT8uNP0Fi4Oo+Z7YuXijwVJyKePiWNi03wPXOYL8fg2SgcraUFjOk CUkFAohpbDw+MsrLdI7KUoZDauKaBdxZ/K6ZPVKr5GyHw8IAy8Cqo3sKM1vLtgIqQQcx lHX/l8upvRo9SuXa47hrT9mzoa1u5DXjKGQD9IKUc8odXSQanEG2kssPssW0O47JnXYa mR/g== X-Forwarded-Encrypted: i=1; AJvYcCWHU2ahH8DEorAHgMZ2wdNngl3kryWwBqrm1idNLQEa6nv64bfTpIsob09m79+KZws4BsjksBbX9X6BMwJG@lists.postgresql.org X-Gm-Message-State: AOJu0YzRFkKblYRX9AXBiwhj2KPUnO/jBuTeBkRBSyG9eSp2SNd27exx 2+Wc/e+zAPkq7XvXpTXSdcAx8aY7K5SX7souGz8wjBI0gPzdyPXyV7ntD7OElO/TigEb20EmZO0 LGvZuxmE8XQdTrb7KTwck3RRN2nM= X-Google-Smtp-Source: AGHT+IGY1Dl3XWFNmUH58PW/bha+UjmLCIGu3Bx2klepKtmFGUjk4Q9V0E8lohMl6NqHDDO+ZjDSilYEy5ycpXi7sDE= X-Received: by 2002:a05:690c:7308:b0:6e3:3521:88ff with SMTP id 00721157ae682-6eaddd9507amr101366087b3.18.1731262181817; Sun, 10 Nov 2024 10:09:41 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Pavel Stehule Date: Sun, 10 Nov 2024 19:09:04 +0100 Message-ID: Subject: Re: proposal: schema variables To: Dmitry Dolgov <9erthalion6@gmail.com> Cc: Laurenz Albe , Erik Rijkers , Michael Paquier , Amit Kapila , DUVAL REMI , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="0000000000005c4aba062692e3b2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005c4aba062692e3b2 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable ne 10. 11. 2024 v 18:51 odes=C3=ADlatel Pavel Stehule napsal: > > > ne 10. 11. 2024 v 17:19 odes=C3=ADlatel Pavel Stehule > napsal: > >> >> >> ne 10. 11. 2024 v 16:24 odes=C3=ADlatel Dmitry Dolgov <9erthalion6@gmail= .com> >> napsal: >> >>> Hi folks, >>> >>> Thanks for continuing this work. As a side note, I would like to mentio= n >>> how strange the situation in this CF item is. If I understand correctly= , >>> there are two hackers threads discussing the same patch, with recent >>> patches posted in both of them. This is obviously confusing, e.g. the >>> main concern from another thread, about names clashing, wasn't even >>> mentioned in this one. Is it possible to reconcile development in one >>> thread? >>> >> >> This is probably my error. I don't try to organize threads, just I'll tr= y >> to reply in the thread where I got a question. >> > > I thought a lot of time about better solutions for identifier collisions > and I really don't think so there is some consistent user friendly syntax= . > Personally I think there is an easy already implemented solution - > convention - just use a dedicated schema for variables and this schema > should not be in the search path. Or use secondary convention - like usin= g > prefix "__" for session variables. Common convention is using "_" for > PLpgSQL variables. I searched how this issue is solved in other databases= , > or in standard, and I found nothing special. The Oracle and SQL/PSM has a > concept of visibility - the variables are not visible outside packages or > modules, but Postgres has nothing similar. It can be emulated by a > dedicated schema without inserting a search path, but it is less strong. > There can be more collisions in Oracle, because the functions without arguments don't need parentheses. Postgres is safer, because this syntax is not allowed. > > I think we can introduce an alternative syntax, that will not be user > friendly or readable friendly, but it can be without collisions - or can > decrease possible risks. > > It is nothing new - SQL does it with old, "new" syntax of inner joins, or > in Postgres we can > > where salary < 40000 > > or > > where pg_catalog.int4lt(salary, 40000); > > > or some like we use for operators OPERATOR(*schema*.*operatorname*) > > So introducing VARIABLE(schema.variablename) syntax as an alternative > syntax for accessing variables I really like. I strongly prefer to use th= is > as only alternative (secondary) syntax, because I don't think it is > friendly syntax or writing friendly, but it is safe, and I can imagine > tools that can replace generic syntax to this special, or that detects > generic syntax and shows some warning. Then users can choose what they > prefer. Two syntaxes - generic and special can be good enough for all - a= nd > this can be perfectly consistent with current Postgres. > > Regards > > Pavel > > >> Regards >> >> Pavel >> >> > --0000000000005c4aba062692e3b2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
ne 10. 11. 2024 v=C2=A018:51 odes=C3= =ADlatel Pavel Stehule <pavel= .stehule@gmail.com> napsal:


ne 10. 11. 2024 v= =C2=A017:19 odes=C3=ADlatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
=


ne 10. 11. 2024 v=C2=A016:24 odes=C3=ADlatel Dmitry Dolg= ov <9erthalio= n6@gmail.com> napsal:
Hi folks,

Thanks for continuing this work. As a side note, I would like to mention how strange the situation in this CF item is. If I understand correctly, there are two hackers threads discussing the same patch, with recent
patches posted in both of them. This is obviously confusing, e.g. the
main concern from another thread, about names clashing, wasn't even
mentioned in this one. Is it possible to reconcile development in one
thread?

This is probably my error. I do= n't try to organize threads, just I'll try to reply in the thread w= here I got a question.

I = thought a lot of time about better solutions for identifier collisions and = I really don't think so there is some consistent user friendly syntax. = Personally I think there is an easy already implemented solution - conventi= on - just use a dedicated schema for variables and this schema should not b= e in the search path. Or use secondary convention - like using prefix "= ;__" for session variables. Common convention is using "_" f= or PLpgSQL variables. I searched how this issue is solved in other database= s, or in standard, and I found nothing special. The Oracle and SQL/PSM has = a concept of visibility - the variables are not visible outside packages or= modules, but Postgres has nothing similar. It can be emulated by a dedicat= ed schema without inserting a search path, but it is less strong.
=

There can be more collisions i= n Oracle, because the functions without arguments don't need parenthese= s. Postgres is safer, because this syntax is not allowed.
=C2= =A0

I think we c= an introduce an alternative syntax, that will not be user friendly or reada= ble friendly, but it can be without collisions - or can decrease possible r= isks.

It is nothing new - SQL does it with old, &q= uot;new" syntax of inner joins, or in Postgres we can 
where s=
alary < 40000 
or

where pg_catalog.int4lt(salary, 40000);
=
or some like we use for operators OPERATOR(<= code>schema.operatorname)=

So introducing VARIABLE(schema.variablename) syntax as an alternative sy= ntax for accessing variables I really like. I strongly prefer to use this a= s only alternative (secondary) syntax, because I don't think it is frie= ndly syntax or writing friendly, but it is safe, and I can imagine tools th= at can replace generic syntax to this special, or that detects generic synt= ax and shows some warning. Then users can choose what they prefer. Two synt= axes - generic and special can be good enough for all - and this can be per= fectly consistent with current Postgres.

Regards

Pavel


Regards

Pavel
=C2= =A0
--0000000000005c4aba062692e3b2--