Re: Re: proposal: schema variables

Hi

so 28. 12. 2024 v 11:35 odesílatel jian he <jian(dot)universality(at)gmail(dot)com>
napsal:

> hi.
>
> src9=# select 'XLogRecPtr'::regtype;
> ERROR: type "xlogrecptr" does not exist
> LINE 1: select 'XLogRecPtr'::regtype;
> ^
> so
> + <structfield>varcreate_lsn</structfield> <type>XLogRecPtr</type>
> should be
> <structfield>varcreate_lsn</structfield> <type>pg_lsn</type>
> ?
>

done

>
> also
> + <row>
> + <entry role="catalog_table_entry"><para role="column_definition">
> + <structfield>varcreate_lsn</structfield> <type>XLogRecPtr</type>
> + </para>
> + <para>
> + LSN of the transaction where the variable was created.
> + <structfield>varcreate_lsn</structfield> and
> + <structfield>oid</structfield> together form the all-time unique
> + identifier (<structfield>oid</structfield> alone is not enough,
> since
> + object identifiers can get reused).
> + </para></entry>
> + </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 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
> <term><literal>session_variable</literal></term>
> should be
> <term><replaceable class="parameter">session_variable</replaceable></term>
>

done

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

done