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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
