Postgres Pro
Downloads
Home   >   mailing lists

proposal: session server side variables - Mailing list pgsql-hackers

From Pavel Stehule
Subject proposal: session server side variables
Date
Msg-id CAFj8pRCfdTLeJbTSbAFOwhuS-aWaJ61w59XwKLcVYQFAVwfVCw@mail.gmail.com
Whole thread Raw
Responses Re: proposal: session server side variables  (Craig Ringer <craig@2ndquadrant.com>)
Re: proposal: session server side variables  (Thomas Kellerer <spam_eater@gmx.net>)
Re: [HACKERS] proposal: session server side variables  (Fabien COELHO <coelho@cri.ensmp.fr>)
List pgsql-hackers
Tree view
Hi,

long time I working on this topic. Session server side variables are one major missing feature in PLpgSQL. Now I hope, I can summarize requests for implementation in Postgres:

Requirements
==========
1. Should be used in any PL (PLpgSQL, PLPython, PLPerl, ..)

2. Should not block a implementation of  ANSI/SQL SQL modules - the modules and PSM languages are big chapter and should be implemented together and maybe from scratch - isn't easy to inject it to our environment pretty. More the modules are partially redundant with schemas and with our extensions. This is reason, why I don't take functionality described in standard.

3. The usage should be simple, secure and not limited by only PL usage.

------------

I found very good inspiration in PostgreSQL sequences. They can be used anywhere, the access to sequences is secure, the sequence interface is stabilized.

The session variables should be:

1. persistent objects with temporal unshared typed content. The life of content should be limited by session or by transaction. The content is initialized to default (when it is defined) or to NULL when variable is first accessed in variable' time scope (session, transaction).

CREATE VARIABLE [schema.]variable type [DEFAULT default_value] [TRANSACTION|SESION SCOPE]
DROP VARIABLE [schema.]variable

2. accessed with respecting access rights:

GRANT SELECT|UPDATE|ALL ON VARIABLE variable TO role
REVOKE SELECT|UPDATE|ALL ON VARIABLE variable FROM role

The variable is joined with some schema - the access is filtered by schema too - like any other schema object.

3. accessed/updated with special function "getvar", "setvar":

FUNCTION getvar(regclass) RETURNS type
FUNCTION setvar(regclass, type) RETURNS void

These functions are supported by PostgreSQL analyzer - a casting to correct variable type is enforced there. These functions are volatile. Some stable variants can exists too.

4. non transactional  - the metadata are transactional, but the content is not.

------------

This concept doesn't introduce any new visibility or accessibility methods. The session variable is first class object like any others and special rules are not necessary. The access should be controlled by access rights only.

This proposal doesn't propose Oracle's package variables and related behave. When we have not a full ADA environment, then partial implementation should be too complex with strange (foreign) behave in our environment. But Oracle's package variables should be emulated over proposed layer and this emulation should be really secure - no security by obscurity.

Comments, notices?

Regards

Pavel

pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: Change of extension name to new name
Next
From: Michael Paquier
Date:
Subject: Re: pg_dump, pg_dumpall and data durability