2016-12-24 12:19 GMT+01:00 Fabien COELHO <coe...@cri.ensmp.fr>: > > Hello Pavel, > > Hmmm. Switching role within a transaction. I never did need that... but >>> that is a use case. >>> >> >> Any application with security definer functions - depends on different >> communities - it is used sometimes strongly. >> > > Hmmm. So I understand that you would like to do something like: > > - call a secure function which sets a session variable with restricted > permissions > - do some things which cannot access or change the variable > - call another secure function which can access, update, remove the > variable... > > Probably we have different expectation from variables. I don't expect so >> variable can be changed by any rollback. >> > > Indeed, it seems that we do not have the same expectations. > > What is use case for transactional variables? I miss any experience - I >> wrote lot plpgsql lines and newer would it. >> > > Here are two use cases, which are neither good nor bad, but that I have in > mind when I'm argumenting. > > (1) First use case I'm thinking of is software update, with persistent > transactional variables, eg: > > -- let assume we have application_version = 1 > BEGIN; > -- lock things up > -- update application schema and data to version 2 > -- set application_version = 2 > -- unlock things > COMMIT; > > I would not want the application_version to remain at 2 if the COMMIT > fails, obviously. This is usually implemented with a one-row table, but > some kind of variable syntax could be quite elegant. For this use case, a > variable should be persistant, it does not it to be efficient, it should > have permissions and should be transactional. > > > (2) Second use case I'm thinking of is some kind of large batch management. > > -- variable batch_1_is_done = false > BEGIN; > -- try to do large batch 1... > -- set batch_1_is_done = true > COMMIT; > -- then test whether it worked, do some cleanup if not... > -- there are some discussions to get some \if in psql... > > For this second example, I would not like batch_is_done to be true if the > commit failed, but I do not think that any permissions would be useful, and > it would be fine if it is just accessible from a session only. >
On server side you can use PLpgSQL and handling exception. On client side you can use technique used in MSSQL, where variables are not transactional too. BEGIN -- servar state 1 statement; -- servar state 2 statement; COMMIT We should to introduce client side session variable :STATUS \if eq(:STATUS, 'ok') ... > > > When I remove ACID, and allow only one value - then the implementation can >> be simple and fast - some next step can be support of expandable types. >> Sure - anybody can use temporary tables now and in future. But it is slow >> - >> more now, because we doesn't support global temporary tables. But ACID >> needs lot of CPU times, needs possible VACUUM, ... >> > > Yep, but if you need persistant and transactional then probably you can > accept less performant... > When you accept less performance, then you can use temporary tables. You can easy wrap it by few polymorphic functions. > No ACID variables are simple to implement, simple to directly accessible >> from any PL (although I am thinking about better support in 2nd phase for >> PLpgSQL). >> > > ACID may be simple to implement with some kind of underlying table, or > maybe a row in a table. How efficient it could be is another question, but > then if the feature does not allow some use cases, and it not so > interesting to have it. That is why I think that it is worth discussing > "silly" semantics and syntax. > > The namespace issue is unclear to me. Would a variable name clash with a > table name? It should if you want to be able write "SELECT stuff FROM > variablename", which may or may not be a good idea. It is based on history and experience - one fundamental issue of languages for stored procedures is a conflict of variables and SQL identifiers. When variables are based on pg_class, there are not possibility to any new conflict. More I can use a security related to schema - It is partial coverage of package variables. Regards Pavel > > -- > Fabien. >
