2016-12-28 4:40 GMT+01:00 Craig Ringer <cr...@2ndquadrant.com>: > Fabien, I don't really see the point of "persistent variables". What > benefit do they add over relations? > > You can add a simple function to fetch a tuple if you want it not to > look like a subquery. Do it with heap access in C if you like, save > the (trivial) planning costs. > > > I do see value to two different things discussed here: > > * Pavel's proposal for persistent-declaration, non-persistent-value > session variables with access control. These will be very beneficial > with RLS, where we need very fast lookups. Their purpose is that you > can set up expensive state with SECURITY DEFINER functions, C-level > functions, etc, then test it very cheaply later from RLS and from > other functions. Their advantage over relations is very cheap, fast > access. > > I can maybe see global temporary relations being an alternative to > these, if we optimise by using a tuplestore to back them and only > switch to a relfilenode if the relation grows. The pg_catalog entries > would be persistent so you could GRANT or REVOKE access to them, etc. > Especially if we optimised the 1-row case this could work. It'd be > less like what Oracle does, but might let us re-use more functionality > and have fewer overlapping features. Pavel? >
This is hard question - I have different expectation from variables and from tables. Sure, there can be a optimization for one row global temporary tables, but it fails on first update. And with this optimization we should not to use current heap access functionality - or we increase a complexity of currently lot of complex code. From success global temp tables implementation I am expecting unbloating catalogue and all relation related functionality - indexes, column statistics, usage statistics, MVCC. I can't to imagine a one row optimization there. More it has limit - it is pretty hard implement there expandable types. I see very useful to define variable as memory based NOT ACID, not MVCC storage (always). Tables are ACI(D) MVCC. The minimalism is great, but have to has practical limit - we have variables inside/outside plpgsql - although we can use temporary tables. There are secondary question if we can introduce some better interface for writing getter/setter function, where current relations can be used. > > > * Fabien's earlier mention of transient session / query variables, > a-la MySQL or MS-SQL. They're obviously handy for more general purpose > programming, but our strict division between SQL and plpgsql makes > them a bit less useful than in MS-SQL with T-SQL. I think it's a very > separate topic to this and should be dealt with in a separate thread > if/when someone wants to work on them. > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
