On Wed, May 21, 2025 at 09:12:54AM +0200, Pavel Stehule wrote: > Last discussion is related to reducing the size of the session variable patch > set. > > I have an idea to use variable's fencing more aggressively from the start, and > then we can reduce it in future. This should not break issues with > compatibility and doesn't need some like version flags. > > The real problem of proposed session variables is possible collisions between > session variables identifiers and table or columns identifiers. I designed > some > tools to minimize the risk of unwanted collisions, but these tools increase > the > size of code and don't reduce the complexity of the patch and tests. The > proposed change probably doesn't reduce a lot of code, but can reduce some > tests, and mainly possible risk of some unwanted impact - at the end it can be > less work for reviewers and less stress for committers - and the > implementation > can be divided to allone workable following steps.
Yes, I remember the discussions about how the creation of server variables could break existing queries. Our scoping rules are already complex, so adding another scope would add a lot of complexity. > Step 1 > ===== > > So the main change is the hard requirement for usage variable's fence > everywhere where collisions are possible - and then in the first step, the > collisions will not be possible, and then we don't need it to solve, and we > don't need to test it. > > CREATE VARIABLE public.foo AS int; > LET foo = 10; > SELECT VARIABLE(foo); Yes, I can see how adding fencing like VARIABLE() would simplify things. > Step 2 > ===== > Necessity of usage variable fencing in PL/pgSQL can be a problem for migration > from PL/SQL. But this can be solved separately by using SPI params hooks - > similar to how PL/pgSQL works with PL/pgSQL variables. In this step we can > push > optimization for fast execution of the LET statement or optimization of usage > variables in queries. Yes, there is already going to be migration requirements in moving from PL/SQL to PL/pgSQL, so the requirement to add VARIABLE() seems minimal. > After this step will be possible: > > DO $$ > BEGIN > RAISE NOTICE '% %', foo, VARIABLE(public.foo); > END; > $$; > > SELECT VARIABLE(foo); > > No other visible change in this step. WIth this step the people who do > migration form Oracle and PL/pgSQL developers will be very happy. They don't > need more. There can be collisions, but the collisions can be limited just to > PL/pgSQL scope, and we can use already implemented mechanisms. > > Step 3 > ===== > We can talk in future about less requirement of usage variable fencing in > queries. This needs to introduce some form of detection collisions and how > they > should be solved (outside PL/pgSQL). > We can talk about other features like temporal, default values, transactional, > etc ... I feel that if we haven't found a good solution to this in 13 years, we should assume it is unsolvable and just accept an imperfect solution. -- Bruce Momjian <br...@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
