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 1tCX6a-005BLU-2Q for pgsql-hackers@arkaria.postgresql.org; Sun, 17 Nov 2024 04:41: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 1tCX6W-00Ggry-JN for pgsql-hackers@arkaria.postgresql.org; Sun, 17 Nov 2024 04:41:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tCX6V-00Ggro-US for pgsql-hackers@lists.postgresql.org; Sun, 17 Nov 2024 04:41:44 +0000 Received: from mail-yb1-xb29.google.com ([2607:f8b0:4864:20::b29]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tCX6S-002Iv2-Uk for pgsql-hackers@lists.postgresql.org; Sun, 17 Nov 2024 04:41:43 +0000 Received: by mail-yb1-xb29.google.com with SMTP id 3f1490d57ef6-e38938a55e7so125632276.2 for ; Sat, 16 Nov 2024 20:41:40 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731818499; x=1732423299; 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=FDXCZu1kWbg47A01lpKqojFHLtLZ7LquSdELUb4Ap5w=; b=O+dAdNC/gjyyvrqoixVTDabWvKzq/fsn1eKWPd60xTvefOhL/wVSvgGI6mJN8nahwI 1gKcdc5i3Wfpi6jsQIci+ADtnfFjFE5ZagIqiTcwwcTu4mMR12aLKbtIOHwsHg7obdvq X0L3uDMDrEpQiE2ofWSKCf3YAqUiER5sAykoXsCW/ca0LswFH8Np/QYvhQ4qCVHN/bSF xtIVIoWj8i3gvGmvSAAHGqPWuOcws60f+SyMbHFe+h30K+ForvOrf3jVHnpnzOOAl9ag DtIdrgNJ9wgrMisIMUullNhoXNR0ExgiSY5G4Xuru3PHBkFHhXTjzpa+mtvzfMUGPU/Y 4YSg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731818499; x=1732423299; 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=FDXCZu1kWbg47A01lpKqojFHLtLZ7LquSdELUb4Ap5w=; b=nD4RJy13ycaJCDT4vcOolD2sAma1J7KHvit60aIMF01jTrMwVryFkgSGBrq6YRfE8L CyneJjCvPThLJ7FUczFsbtWMz1FH3oidvkQQ4qHP8L1gI24nT2qS3dC1otkeHVQL5Pa9 rWgAhMmJouGz6zXMu0LT52M91Ss3RLbX9fx3xc1XyIC1BZ29ZBfyTGx4Dst6c6WnxIR2 uD8psXKCKLflH9XzQmO0UlsKpDO/WdJQENlebQhP9276bOmfI53Lr50gOVUrOp8g0VPx sY05CTny6aY8tTgH7YOFpJr5apYpAoEbFXOjvVkRI5La2ZXTAq6qA7FZ66SSLfHvrvLb YcGA== X-Forwarded-Encrypted: i=1; AJvYcCXOudW4ee7HgXK/qMFa3DYNvKKRKdH3eIdkjK9DASLKnUkxlJNTke+yArNfxXC6J3hJSFPsqwZweSB0HeRv@lists.postgresql.org X-Gm-Message-State: AOJu0Yx/SQvrQfe4Xcdpcpst/urUdfiwJlgOOyVEqZSrHGXz1Cdcy9+O 9OIQs5fqhZ9Kd4rfPnW9DiuBWUl/zTTfSi04/GNynJjI2qZ393xFfCdUAxzUdEUiANjZfUnrh5/ eBecEsjDQkwu++n+CH2hhku4js0E= X-Google-Smtp-Source: AGHT+IHDg1+bMt/9qhsBR1RJCQTFGx8FnFKP2FPURIqjGbDxjZhjdkK8hs2hNbLeNkfxlAbG833LXL6isfcAZN2woHY= X-Received: by 2002:a05:6902:2890:b0:e30:dfdb:19a6 with SMTP id 3f1490d57ef6-e38263a85f0mr5715499276.38.1731818499409; Sat, 16 Nov 2024 20:41:39 -0800 (PST) MIME-Version: 1.0 References: <3chredgnjcmccym2kczawfih226b4ac6co7p6z4jeofevrcosi@mrsxkx2x2c65> In-Reply-To: From: Pavel Stehule Date: Sun, 17 Nov 2024 05:41:00 +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="0000000000007914410627146ac3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007914410627146ac3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable so 16. 11. 2024 v 23:49 odes=C3=ADlatel Pavel Stehule napsal: > > > so 16. 11. 2024 v 15:27 odes=C3=ADlatel Dmitry Dolgov <9erthalion6@gmail.= com> > napsal: > >> > On Sat, Nov 16, 2024 at 07:10:31AM GMT, Pavel Stehule wrote: >> >> Sorry, got distracted. Let me try to answer step by step. >> >> > > As far as I recall, last time this topic was discussed in hackers, t= wo >> > > options were proposed: the one with VARIABLE(name), what you mention >> > > here; and another one with adding variables to the FROM clause. The >> > > VARIABLE(...) syntax didn't get much negative feedback, so I guess w= hy >> > > not -- if you find it fitting, it would be interesting to see the >> > > implementation. >> > > >> > > I'm afraid it should not be just an alternative syntax, but the only >> one >> > > allowed, because otherwise I don't see how scenarious like "drop a >> > > column with the same name" could be avoided. As in the previous >> thread: >> > > >> > > -- we've got a variable b at the same time >> > > SELECT a, b FROM table1; >> > > >> > >> > I am sorry, but I am in very strong opposition against this idea. >> Nobody >> > did reply to my questions, that can change my opinion. >> >> From your reply it's not quite clear, are you opposed to have a mandator= y >> VARIABLE syntax, or having variables in the FROM clause? My main proposa= l >> was >> about the former, but the points that are following seems to talk about >> the >> latter. I think it's fine to reject the idea about the FROM clause, as >> long as >> you got some reasonable arguments. >> > > I am against a requirement to specify a variable in the FROM clause. > > >> >> > > Then dropping the column b, but everything still works beause the >> > > variable b got silently picked up. But if it would be required to sa= y >> > > VARIABLE(b), then all fine. >> > >> > but same risk you have any time in plpgsql - all time. I don't remembe= r >> any >> > bug report related to this issue. >> >> Which exactly scenario about plpgsql do you have in mind? Just have trie= d >> to >> declare a variable inside a plpgsql function with the same name as a tab= le >> column, and got an error about an ambiguous reference. >> > > Until you execute the query, you cannot know if there is a conflict or > not. So you can change table structure and you can change the procedure's > code, and there can be an invisible conflict until execution and query > evaluation. The conflict between PL/pgSQL and SQL raises an error. The > conflict between session variables and SQL raises warnings. The issue is > detected. > > > >> >> > Theoretically, variables can have the same names as tables. The table >> > overshadows the variable, so it can work. But when somebody drops the >> > variable, then the query still can work. So requirement of usage >> variable >> > in FROM clause protects us just against drop column, but not against >> > dropping table. In Postgres the dropping table is possibly risky due >> > search_path (that introduces shadowing concept) without introduction >> > variables. There is a possibility of this issue, but how common is thi= s >> > issue? >> >> This sounds to me like an argument against allowing name clashing betwee= n >> variables and tables. It makes even more sense, since session variables >> are in >> many ways similar to tables. >> >> > It doesn't help too much. It can fix just one issue. But you can have > tables with the same name in different schemas inside schemas from > search_path. Unique table names solve nothing. > the combination of pg_class and pg_attribute cannot describe scalar variables (without hacks). Then you need to enhance pg_class, which can be confusing. And on the second hand almost all columns in pg_class have no sense for variables. And when variables and tables are in different tables, you cannot ensure a unique name. Variables are similar to tables only in possibility to hold a value. That is all. But variables don't store data to file, don't store data in pages, don't allow usage of other storages or formats, and don't support foreign storage. The similarity between variables and tables is like the similarity between horses and cars. Both can help with moving. > >> > I think this issue can be partially similar to creating two equally >> named >> > tables in different schemas (both schemas are in search path). When yo= u >> > drop one table, the query will work, but the result is different. It i= s >> the >> > same issue. The SQL has no concept of shadowing and on the base line i= t >> is >> > not necessary. >> >> The point is that most of users are aware about schemas and search path >> dangers. But to me such a precedent is not an excuse to introduce a new >> feature >> with similar traps, which folks would have to learn by making mistakes. >> Judging >> from the feedback to this patch over time, I've got an impression that >> lots of >> people are also not fans of that. >> > > Unfortunately - I don't believe so there is some syntax without traps. Yo= u > can check all implementations in other databases. These designs are very > different, and all have some issues and all have some limits. It is nativ= e > - you are trying to join the procedural and functional world. > > I understand the risks. These risks are there. But there is no silver > bullet - all proposed designs fixed just one case, and not others, and th= en > I don't see a strong enough benefit to introduce design that is far from > common usage. Maybe I have a different experience, because I am a man fro= m > the stored procedures area, and the risk of collisions is a known issue > well solved by common conventions and in postgres by > plpgsql.variable_conflict setting. The proposed patch set has very simila= r > functionality. I think the introduction of VARIABLE(xx) syntax and safe > syntax guard warning the usage of variables can be safe in how it is > possible. But still I want to allow "short" "usual" usage to people who u= se > a safe convention. There is no risk when you use a safe prefix or safe > schema. > > > >> >> > > Then dropping the column b, but everything still works beause the >> > > variable b got silently picked up. But if it would be required to sa= y >> > > VARIABLE(b), then all fine. >> > > >> > >> > In this scenario you will get a warning related to variable shadowing >> > (before you drop a column). >> > >> > [...] >> > >> > What do you think about the following design? I can implement a warni= ng >> > "variable_usage_guard" when the variable is accessed without using >> > VARIABLE() syntax. We can discuss later if this warning can be enabled >> by >> > default or not. There I am open to any variant. >> >> I don't follow what are you winning by that? In the context of problem >> above >> (i.e. dropping a column), such a warning is functionally equivalend to a >> warning about shadowing. >> >> The problem is that it doesn't sound very appealing to have a feature, >> which >> requires some extra efforts to be used in a right way (e.g. put >> everything into >> a special vars schema, or keep an eye on logs). Most certainly there are >> such >> bits in PostgreSQL today, with all the best practices, crowd wisdom, etc= . >> But >> the bar for new features in this sense is much higher, you can see it >> from the >> feedback to this patch. Thus I believe it makes sense, from purely >> tactical >> reasons, to not try to convince half of the community to lower the bar, >> but >> instead try to modify the feature to make it more acceptable, even if so= me >> parts you might not like. >> >> Btw, could you repeat, what was exactly your argument against mandatory >> VARIABLE() syntax? It's somehow scattered across many replies, would be >> great >> to summarize it in a couple of phrases. >> >> > Shadowing by self is not an issue, probably, but it is a signal of cod= e >> > quality problems. >> >> Agree, but I'm afraid code quality of an average application using >> PostgreSQL >> is quite low, so here we are. >> >> As a side note, I've recently caught myself thinking "it would be cool t= o >> have >> session variables here". The use case was preparing a policy for RLS, >> based on >> some session-level data set by an application. This session-level data i= s >> of a >> composite data type, so simple current_setting is cumbersome to use, and= a >> temporary table will be dropped at the end, taking the policy with it du= e >> to >> the recorded dependency between them. Thus a session variable of some >> composite >> type sounds like a good fit. >> > --0000000000007914410627146ac3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
so 16. 11. 2024 v=C2=A023:49 odes=C3= =ADlatel Pavel Stehule <pavel= .stehule@gmail.com> napsal:


so 16. 11. 2024 v= =C2=A015:27 odes=C3=ADlatel Dmitry Dolgov <9erthalion6@gmail.com> napsal:
> On Sat, Nov 16, 20= 24 at 07:10:31AM GMT, Pavel Stehule wrote:

Sorry, got distracted. Let me try to answer step by step.

> > As far as I recall, last time this topic was discussed in hackers= , two
> > options were proposed: the one with VARIABLE(name), what you ment= ion
> > here; and another one with adding variables to the FROM clause. T= he
> > VARIABLE(...) syntax didn't get much negative feedback, so I = guess why
> > not -- if you find it fitting, it would be interesting to see the=
> > implementation.
> >
> > I'm afraid it should not be just an alternative syntax, but t= he only one
> > allowed, because otherwise I don't see how scenarious like &q= uot;drop a
> > column with the same name" could be avoided. As in the previ= ous thread:
> >
> >=C2=A0 =C2=A0 =C2=A0-- we've got a variable b at the same time=
> >=C2=A0 =C2=A0 =C2=A0SELECT a, b FROM table1;
> >
>
> I am sorry, but I am in very strong opposition=C2=A0 against this idea= . Nobody
> did reply to my questions, that can change my opinion.