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 1tRadh-00AWBJ-Sv for pgsql-hackers@arkaria.postgresql.org; Sat, 28 Dec 2024 17:30:14 +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 1tRadf-001LvZ-Dv for pgsql-hackers@arkaria.postgresql.org; Sat, 28 Dec 2024 17:30:11 +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 1tRadf-001LvR-2v for pgsql-hackers@lists.postgresql.org; Sat, 28 Dec 2024 17:30:10 +0000 Received: from mail-yb1-xb2d.google.com ([2607:f8b0:4864:20::b2d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tRadY-0024FW-9I for pgsql-hackers@lists.postgresql.org; Sat, 28 Dec 2024 17:30:10 +0000 Received: by mail-yb1-xb2d.google.com with SMTP id 3f1490d57ef6-e3c9ec344efso8229305276.2 for ; Sat, 28 Dec 2024 09:30:04 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1735407003; x=1736011803; 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=+jEwdnXW20gfLQdvGUJbz4du2SpmaSyLcOfU7iTu0ro=; b=NzcX1u4P/2HCiulPyFOq5uAlDDPyyiSTlACah5Rq6SaX6Q1nknxH5NlasHDO+aKjKV xHQwOJAD+QY8a0y87RkpJQpIbIxevXNmC/e0QPCl8TmttzYkFXQm5PicrznxyLmmJYG7 1N/u1e5t9zkEwP19FKl2pvM7IDPyEU135C9a8hli0JZMS7kRakiMvPHiUUlhARlUTFqM NBIvFTDwwoxp9XCeP1xiRxW4q2FQbQMc5tHwYI7dlrAwxFozfbYqNKFTlvT7w8zG4aQm Fg7NNXA65egx3P62NGo9jfQ8aD7LwliqoOXGDiTmDXD0EbG3PBdBTGwyKbCKvrZUSXCe z+WA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1735407003; x=1736011803; 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=+jEwdnXW20gfLQdvGUJbz4du2SpmaSyLcOfU7iTu0ro=; b=AT8zMuNx7cPUkp7EdubM83AemGUiok+o/xaMdiKilUjwb1hyk4gn1gKHwYrbptZhVG jVDKdo0u6Ia/UdR8mNjGA++Xv3UtzvjqQ4FltyEvKlw+IW2eicrNNY6M5PWbuc+1vjYV 9NBg1XRcD/pTyc54kaFJbtEaWrns1IK+I+kytvfg3XBwjV7Cbc9SkXHr5thB9XxBFJA8 xs00m2ayfDTtD4Gw9gqcpk7c9nr8YbsYz1Mf82yfnpr6z/wQpTFJvDc2Vd6A+50rNgLd TlSRgBzFMju3GCj9wyHFglJpyUcCdEMBBFfnqFSng93Mhv/t9dK9uuKbwCMVxFGKMwe4 M4tA== X-Forwarded-Encrypted: i=1; AJvYcCUNmy6W6U66KHvzkSk8O12Rn/wi0xAWxIGN0asnnG93bllvGNrAWt30w2Ov0TDBOjsXQ5F5D8sigMz4vBES@lists.postgresql.org X-Gm-Message-State: AOJu0Yygooo0r4iQZRko7tJ5C9+NgAnStqlPTHueoYFeRidkOTf7l25e 96JGc6IPSRD5ONa1OGF6mMc5q4JQMuqLon4Q2OUrUC5JF4GcjGDhipTRCZvjfJbU1yZwZ09A74M O1rV6/j3gf3FJXBjc2xb9YKkPEOg= X-Gm-Gg: ASbGncu2ZeZPvuqJPKSzg18LF91i7xofAiPLg03nO2gD/OmpLV24kgBq0ybkmxShlRE sO19B1iVtmgQZQ8U/zsUwvS3oadD1qDYZ6ShJ0cl1qkPYxCPnaEMQ2RjFA0ug5uX6eHh7FyA= X-Google-Smtp-Source: AGHT+IE8HEjCqVsD1xikMLgkx+t0Sf+Iw8wZwiTC6ZdNroj0OhsRL/qbBlyaQY6/CKSjmSt+9P3AP3XPFd9I1Gtksro= X-Received: by 2002:a05:690c:64c6:b0:6ef:63cb:61d0 with SMTP id 00721157ae682-6f3f8115f87mr199382617b3.10.1735407002882; Sat, 28 Dec 2024 09:30:02 -0800 (PST) MIME-Version: 1.0 References: <3chredgnjcmccym2kczawfih226b4ac6co7p6z4jeofevrcosi@mrsxkx2x2c65> <20241120201313.t4wbhld4ktgielaf@erthalion.local> In-Reply-To: From: Pavel Stehule Date: Sat, 28 Dec 2024 18:29:26 +0100 Message-ID: Subject: Re: Re: proposal: schema variables To: jian he Cc: Dmitry Dolgov <9erthalion6@gmail.com>, Laurenz Albe , Erik Rijkers , Michael Paquier , Amit Kapila , DUVAL REMI , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="000000000000f29a3f062a57ed9e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f29a3f062a57ed9e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi so 28. 12. 2024 v 11:35 odes=C3=ADlatel jian he napsal: > hi. > > src9=3D# select 'XLogRecPtr'::regtype; > ERROR: type "xlogrecptr" does not exist > LINE 1: select 'XLogRecPtr'::regtype; > ^ > so > + varcreate_lsn XLogRecPtr > should be > varcreate_lsn pg_lsn > ? > done > > also > + > + > + varcreate_lsn XLogRecPtr > + > + > + LSN of the transaction where the variable was created. > + varcreate_lsn and > + oid together form the all-time unique > + identifier (oid alone is not enough, > since > + object identifiers can get reused). > + > + > + > we have "pg_variable_oid_index" PRIMARY KEY, btree (oid) > for table pg_variable. > so I am confused by saying the column "oid" itself is not enough to > prove unique. > The session variable is stored in memory until the end of the session. Theoretically, some sessions with used session variables can be opened for a very long time without any activity - so it is not possible to process sinval message. Other sessions can drop and create a lot of session variables (this is very possible with temporary session variables). Oid in Postgres can overflow, and postgres can reuse used oid of dropped objects (oid is only 32bit integer). And after some time, the session with the used variable can be activated, and the session variable can be used. Before usage the session variable is rechecked against pg_variable, and theoretically the variable with the same oid can be there (although it is a different variable with possibly different type). The implementation should protect against this scenario. The stored value must not be used in this case - the usage of old value is dangerous - the calculations with the variable can lose sense or can crash postgres. LSN is forever unique - it is 64bit integer - so it is our safeguard so we can detect obsolete values (stored in memory) although there are variables with the same oid. oid in pg_variable ensures a unique identifier for any session variable in one moment. Compound key [oid, varcreate_lsn] is a unique identifier for ever. > in let.sgml > session_variable > should be > session_variable > done > > sql_expression > should be > sql_expression > done --000000000000f29a3f062a57ed9e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

so 28. 12. 2024 v=C2=A011:35 o= des=C3=ADlatel jian he <jian.universality@gmail.com> napsal:
hi.

src9=3D# select 'XLogRecPtr'::regtype;
ERROR:=C2=A0 type "xlogrecptr" does not exist
LINE 1: select 'XLogRecPtr'::regtype;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^
so
+ <structfield>varcreate_lsn</structfield> <type>XLogRecP= tr</type>
should be
<structfield>varcreate_lsn</structfield> <type>pg_lsn<= /type>
?

done
=C2=A0

also
+=C2=A0 =C2=A0 =C2=A0<row>
+=C2=A0 =C2=A0 =C2=A0 <entry role=3D"catalog_table_entry">&= lt;para role=3D"column_definition">
+=C2=A0 =C2=A0 =C2=A0 =C2=A0<structfield>varcreate_lsn</structfiel= d> <type>XLogRecPtr</type>
+=C2=A0 =C2=A0 =C2=A0 </para>
+=C2=A0 =C2=A0 =C2=A0 <para>
+=C2=A0 =C2=A0 =C2=A0 =C2=A0LSN of the transaction where the variable was c= reated.
+=C2=A0 =C2=A0 =C2=A0 =C2=A0<structfield>varcreate_lsn</structfiel= d> and
+=C2=A0 =C2=A0 =C2=A0 =C2=A0<structfield>oid</structfield> toge= ther form the all-time unique
+=C2=A0 =C2=A0 =C2=A0 =C2=A0identifier (<structfield>oid</structfi= eld> alone is not enough, since
+=C2=A0 =C2=A0 =C2=A0 =C2=A0object identifiers can get reused).
+=C2=A0 =C2=A0 =C2=A0 </para></entry>
+=C2=A0 =C2=A0 =C2=A0</row>
+
we have "pg_variable_oid_index" PRIMARY KEY, btree (oid)
for table pg_variable.
so I am confused by saying the column "oid" itself is not enough = to
prove unique.

The session variable is s= tored in memory until the end of the session. Theoretically, some sessions = with used session variables can be opened for a very long time without any = activity - so it is not possible to process sinval message. Other sessions = can drop and create a lot of session variables (this is very possible with = temporary session variables). Oid in Postgres can overflow, and postgres ca= n reuse used oid of dropped objects (oid is only 32bit integer). And after = some time, the session with the used variable can be activated, and the ses= sion variable can be used. Before usage the session variable is rechecked a= gainst pg_variable, and theoretically the variable with the same oid can be= there (although it is a different variable with possibly different type). = The implementation should protect against this scenario. The stored value m= ust not be used in this case - the usage of old value is dangerous - the ca= lculations with the variable can lose sense or can crash postgres. LSN is f= orever unique - it is 64bit integer - so it is our safeguard so we can dete= ct obsolete values (stored in memory) although there are variables with the= same oid.

oid in pg_variable ensures a unique ide= ntifier for any session variable in one moment. Compound key [oid, varcreat= e_lsn] is a unique identifier for ever.


=

in let.sgml
<term><literal>session_variable</literal></term> should be
<term><replaceable class=3D"parameter">session_variab= le</replaceable></term>

don= e
=C2=A0

<term><literal>sql_expression</literal></term>
should be
<term><replaceable class=3D"parameter">sql_expression= </replaceable></term>

done<= /div>
=C2=A0
--000000000000f29a3f062a57ed9e--