2016-12-31 1:16 GMT+01:00 Craig Ringer <cr...@2ndquadrant.com>: > On 30 December 2016 at 21:00, Fabien COELHO <coe...@cri.ensmp.fr> wrote: > > > 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. > > Consider cases like row security where you're testing 10000 rows. > Hopefully the planner will inline the test if it's a function declared > stable, but it may not. > > > > However the one-row property is just hoped for, and on principle a > database > > is about declaring constraints that are enforced afterwards. > > > > I see two clean solutions to this use case: declaring tables as one row, > or > > having scalar objects. > > > I agree that's a common issue. > > The unique partial index on 1 hack in postgres works, though it's ugly. > > Adding a whole new different storage concept seems like massive > overkill for this problem, which is minor and already easily solved. > Someone could make 1-row tables prettier with a new constraint type > instead maybe, if it's really considered that ugly. Personally I'd > just document the unique expression index hack. > > CREATE UNIQUE INDEX onerow ON mytable((1)); > > >> * 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. > > 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. > > Similarly for advisory locking. > > > 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. > > 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. > > >> (My own objection is that "temporary variables" would make our existing > >> catalog bloat issues for temp objects even worse). > > > > > > I do agree that inefficient temporary variables are worthless, but ISTM > that > > Pavel's proposal is not exactly about temporary variables, it is about > > temporary-valued permanent-variables. So there is no temporary (on the > fly) > > variable as such, and if it is extended for this purpose then indeed the > > catalog costs look expensive. > > I meant that we'd certainly want CREATE TEMPORARY VARIABLE for ones > that go away at end of session, if we were going to have > catalog-object-like variables. Which would result in catalog bloat. >
Because our catalog is MVCC, then bloating is unremovable - but if we implement global temporary tables, then metadata of temporary objects can be stored there - the main catalogue can be stable. But the question? When you would to use local temporary variables? When you cannot to use global variables? Probably in adhoc scripts, in interactive work, ... It is minimal impact on catalogue. The performance problems can be in PL usage, or intensive application usage - and there can be used global variables. Analogy with our temporary tables - if we can use global temporary tables in critical PL, then local temporary tables can be nice feature perfect for interactive work, and nobody have to fix a catalogue bloat. Design of possibility to do local temporary variable is minimal work. I don't afraid about performance when developers can use global variables as option Regards Pavel > > (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. Ugly, but > effective, and honestly something we could bless into general use if > we decided to. It's not that bad. > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
