Re: [HACKERS] proposal: schema variables

Hello Pavel,

> here is updated patch - I wrote some transactional support
>
> I am not sure how these new features are understandable and if these
> features does it better or not.

> There are possibility to reset to default value when
>
> a) any transaction is finished - the scope of value is limited by
> transaction
>
> CREATE VARIABLE foo int ON TRANSACTION END RESET;

With this option I understand that it is a "within a transactionnal" 
variable, i.e. when the transaction ends, whether commit or rollback, the 
variable is reset to a default variable. It is not really a "session" 
variable anymore, each transaction has its own value.

  -- begin session
  -- foo has default value, eg NULL
  BEGIN;
     LET foo = 1;
  COMMIT/ROLLBACK;
  -- foo has default value again, NULL

> b) when transaction finished by rollback
>
> CREATE VARIABLE foo int ON ROLLBACK RESET

That is a little bit safer and you are back to a SESSION-scope variable, 
which is reset to the default value if the (any) transaction fails?

   -- begin session
   -- foo has default value, eg NULL
   BEGIN;
     LET foo = 1;
   COMMIT;
   -- foo has value 1
   BEGIN;
     -- foo has value 1...
   ROLLBACK;
   -- foo has value NULL

c) A more logical (from a transactional point of view - but not necessary 
simple to implement, I do not know) feature/variant would be to reset the 
value to the one it had at the beginning of the transaction, which is not 
necessarily the default.

   -- begin session
   -- foo has default value, eg NULL
   BEGIN;
     LET foo = 1;
   COMMIT;
   -- foo has value 1
   BEGIN;
     LET foo = 2; (*)
     -- foo has value 2
   ROLLBACK;
   -- foo has value 1 back, change (*) has been reverted

> Now, when I am thinking about it, the @b is simple, but not too practical -
> when some fails, then we lost a value (any transaction inside session can
> fails).

Indeed.

> The @a has sense - the behave is global value (what is not possible
> in Postgres now), but this value is destroyed by any unhandled exceptions,
> and it cleaned on transaction end. The @b is just for information and for
> discussion, but I'll remove it - because it is obscure.

Indeed.

> The open question is syntax. PostgreSQL has already ON COMMIT xxx . It is
> little bit unclean, because it has semantic "on transaction end", but if I
> didn't implement @b, then ON COMMIT syntax can be used.

I was more arguing on the third (c) option, i.e. on rollback the value is 
reverted to its value at the beginning of the rollbacked transaction.

At the minimum, ISTM that option (b) is enough to implement the audit 
pattern, but it would mean that any session which has a rollback, for any 
reason (deadlock, serialization...), would have to be reinitialized, which 
would be a drawback.

The to options could be non-transactional session variables "ON ROLLBACK 
DO NOT RESET/DO NOTHING", and somehow transactional session variables "ON 
ROLLBACK RESET TO DEFAULT" (b) or "ON ROLLBACK RESET TO INITIAL" (c).

-- 
Fabien.