Re: proposal: session server side variables

Hello Craig,

>> As for "slow", I have just tested overheads with pgbench, comparing a direct
>> arithmetic operation (as a proxy to a fast session variable consultation) to
>> constant returning plpgsql functions with security definer and security
>> invoker, on a direct socket connection, with prepared statements:
>>
>> select 1 + 0 : 0.020 ms
>> select one_sd() : 0.024 ms
>> select one_si() : 0.024 ms
>
> That's one call per executor run. Not really an effective test.

I really did 10 calls per transaction. For one call it was 24 ms vs 28 ms.
However I'm not sure of the respective overheads of the protocol, planer
and executor, though.

> Consider cases like row security where you're testing 10000 rows.

Another test: calling 1,000,000 times one_sd() or one_si() in a plpgsql
loops seems to cost about 1.1 seconds on my laptop. I'd say that the
function call is about 2/3 of that time, the rest is on the loop and exit
test.

SELECT NOW();
DO LANGUAGE plpgsql $$
DECLARE count INT DEFAULT 0;
BEGIN
LOOP count := count + ONE_SD() ;
EXIT WHEN count = 1000000;
END LOOP;
END; $$;
SELECT NOW();

Based on these evidences, I continue to think that there is no significant
performance issue with calling simple security definer functions.

> Hopefully the planner will inline the test if it's a function declared
> stable, but it may not.

Indeed they are, so the planner should factor out the test when possible.

>>> * On what basis do you _oppose_ persistently defining variables in the
>>> catalogs as their own entities?
>>
>> In understand that you are speaking of "persistent session variables".
>>
>> For me a database is about persistence (metadata & data) with safety
>> (transactions) and security (permissions)... and maybe performance:-)
>>
>> Pavel's proposal creates a new object with 2 (secure metadata-persistence)
>> out of 4 properties... I'm not a ease with introducting a new half-database
>> concept in a database.
>
> I strongly disagree. If you want "all-database" properties ... use tables.

Sure. I am not sure about what are you disagreeing with, as I'm just
describing Pavel's proposal...

> We generally add new features when that's not sufficient to achieve
> something. Most notably SEQUENCEs, which deliberately violate
> transaction isolation and atomicity in order to deliver a compelling
> benefit not otherwise achieveable.

Yes, sure.

>> On the other hand there are dynamic session variables (mysql, mssql, oracle
>> have some variants) which are useful on their own without pretending to be
>> database objects (no CREATE/ALTER/DROP, GRANT/REVOKE).
>
> We have precent here for sequences. Yes, they do confuse users, but
> they're also VERY useful, and the properties of variables would be
> clearer IMO.

Yep. But my point is that before adding a new strange object type I would
prefer that there is no other solution.

> I'm not especially attached to doing them as database objects; I'm
> just as happy with something declared at session start by some
> function that then intends to set and use the variable. But I don't
> think your argument against a DDL-like approach holds water.

I have expectations about objects hold by a database, and these new object
fails them.

If you do not have expectations, then all is fine.

>> (1) Having some kind of variable, especially in interactive mode, allows to
>> manipulate previous results and reuse them later, without having to resort
>> to repeated sub-queries or to retype non trivial values.
>>
>> Client side psql :-variables are untyped and unescaped, thus not very
>> convenient for this purpose.
>
> You can currently (ab)use user defined GUCs for this.

How? It seems that I have missed the syntax to assign the result of a
query to a user-defined guc, and to reuse it simply in a query.

--
Fabien.