Re: [HACKERS] proposal: regrole type?
Hello, Pavel. You wrote: PS Hello PS Can we implement REGROLE type, that simplify role name - oid transformations? +1 from me. My old wish. PS Regards PS Pavel -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Temporal features in PostgreSQL
Hi all, Currently I'm working on a large enterprise project that heavily uses temporal features. We are using PostgreSQL database for data storage. Now we are using PL/pgSQL trigger-based and application-based solutions to handle with temporal data. However we would like to see this functionality in PostgreSQL core, especially in SQL 2011 syntax. There were some discussions several months ago on temporal support and audit logs: http://archives.postgresql.org/pgsql-hackers/2012-05/msg00765.php http://archives.postgresql.org/pgsql-hackers/2012-08/msg00680.php But currently it seems that there is no active work in this area (am I wrong?) Now I'm rewriting our temporal solutions into an extension that is based on C-language triggers to get a better sense of the problem space and various use cases. There are two aspects that temporal features usually include: system-time (aka transaction-time) and application-time (aka valid-time or business-time). The topics above discussed only the first one. However there is also another one, which includes application-time periods, partial updated/deletes queries, querying for a portion of application time etc. Details can be found here http://metadata-standards.org/Document-library/Documents-by-number/WG2-N1501-N1550/WG2_N1536_koa046-Temporal-features-in-SQL-standard.pdf or in the SQL-2011 Standard Draft which is available freely on the network. It's hard to create a convenient extension for application-time periods because it needs the parser to be changed (however an extension may be useful for referential integrity checks for application-time period temporal tables). I created a simple solution for system-time period temporal tables, that consist of only one trigger (it resembles SPI/timetravel trigger but is based on new range types that were introduced in PostgreSQL 9.2 and it's closer to the SQL-2011 approach for implementation of temporal features). http://pgxn.org/dist/temporal_tables/1.0.0/ I'm not a PostgreSQL expert, so I would appreciate if someone could review the code briefly. There are some places I'm not sure I use some functions properly. Also there are some slight problems with the design that I would like to discuss if anyone is interested in. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Event Triggers: adding information
Robert Haas robertmh...@gmail.com writes: That's a good idea. I only started quite late in that patch to work on the ObjectID piece of information, that's why I didn't split it before doing so. That's fine. I've committed that part. I think the remainder of the patch is really several different features that ought to be split apart and considered independently. We may want some but not others, or some may be ready to go in but not others. Thank you for this partial commit, and Simon and Andres to fill in the gaps. I should mention that the missing header parts were all in my patch, and that headers hacking is proving suprisingly uneasy. I've been having several rounds of problems with the gcc tool chain when modifying headers. Worst case has been a successful compiling followed by random errors. Then gdb was not giving any clue (botched memory when returned from a function, palloc'ed memory not freed yet). After spending more time on it that I care to admit, I did a `make maintainer-clean`, built again and the problem disappeared all by itself. The gcc tool chain is not my favorite developer environment. Also, keep in mind we want the ObjectID in all CREATE, ALTER and DROP statements, so my current patch is still some bricks shy of a load… (I added ObjectID only in the commands I added rewrite support for, apart from DROP). I shall rely on you to provide those bricks which are still missing. Cool, will prepare a separate patch implementing that API now, and base the following patches on top of that. My thinking is to do as following: - API patch to get Oid from all CREATE/ALTER commands - API patch for getting the Oid(s) in (before) DROP commands - Event Trigger Infos patch, depending on the previous ones - Command String Rewrite and its publishing in Event Triggers Of course for the DROP parts, we'd better have the Oid and use it before the command runs through completion or it will not be usable from the event trigger (the target is ddl_command_start in that case). We might be able to have a better way of testing the feature here, but I tried to stay into the realms of what I know pg_regress able to do. I was thinking that we might need to go beyond what pg_regress can do in this case. For example, one idea would be to install an audit trigger that records all the DDL that happens during the regression tests. Then, afterwards, replay that DDL into a new database. Then do a schema-only dump of the old and new databases and diff the dump files. That's a little wacky by current community standards but FWIW EDB has a bunch of internal tests that we run to check our proprietary stuff; some of them work along these lines and it's pretty effective at shaking out bugs. Are you in a position to contribute those parts to the community? Implementing them again then having to support two different variants of them does not look like the best use of both our times. Hmm. I have to study that more, maybe. I certainly agree that if you can look at the catalogs, you should be able to reliably reconstruct what happened - which isn't possible just looking at the parse tree. Well in fact we're looking at the parsetree once edited to host the exact data that goes into the catalogs. In most cases that data is easily available to further consumption, or it's possible to use the transform API without touching catalogs again. In some places I'm doing the same processing twice, which I don't like very much, but it's only happening if an Event Trigger is going to fire so I though we could optimize that later. The aim here has been to limit the changes to review, it looks like we have enough of them already. Concerned parts are dealing with raw and cooked expressions for CHECK and DEFAULT and WHERE clauses (create table, create index, alter table, create constraint, create domain). However, it feels weird to me to do something that's partly based on the parse tree and partly based on the catalog contents. Moreover, the current pre-create hook isn't the right place to gather information from the catalogs anyway as it happens before locks have been taken. So all the information is derived from the parse tree. The trick is that this information is only valid once it has hit the catalogs (think of a CHECK constraint in a CREATE TABLE statement, referencing some column attribute, same with a DEFAULT expression depending on another col). The case where we should certainly prefer looking at the catalog caches are when we want to know the actual schema where the object has been created. The current patch is deriving that information mostly from the parse tree, using the first entry of the search_path when the schema is not given in the command. It's ok because we are still in the same transaction and no command has been able to run in between the user command and our lookup, but I could easily get convinced to look up the catalogs instead, even more so once we have the
Re: [HACKERS] proposal: regrole type?
Pavel Stehule pavel.steh...@gmail.com writes: Can we implement REGROLE type, that simplify role name - oid transformations? Why? It's not any more complicated than it is for the other object types that lack REGxxx pseudotypes. Generally speaking, we've only bothered with pseudotypes for the cases where lookup is not trivial, eg because there are search path considerations. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: regrole type?
Hello 2012/12/25 Pavel Golub pa...@microolap.com: Hello, Pavel. You wrote: PS Hello PS Can we implement REGROLE type, that simplify role name - oid transformations? +1 from me. My old wish. I started implementation. I found a two points, that should be solved before. we operate over roles with (without) fictive role public. So we need two datatypes :( I have no idea about second name :( - there should be difference if type enables or disallow public. second issue is value of ACL_ID_PUBLIC, that is zero - and there is not difference from InvalidOid - what should be acceptable via - symbol. Any ideas? Regards Pavel PS Regards PS Pavel -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Event Triggers: adding information
Robert Haas robertmh...@gmail.com writes: Also, keep in mind we want the ObjectID in all CREATE, ALTER and DROP statements, so my current patch is still some bricks shy of a load… (I added ObjectID only in the commands I added rewrite support for, apart from DROP). I shall rely on you to provide those bricks which are still missing. Please find attached a patch to change most functions called from standard_ProcessUtility() to return an Oid (passes `make maintainer-clean; configure; make install check`). Most of them only, because it only make sense for functions touching an object that exists in the catalogs and have a distinct Oid. That completes ALTER and CREATE ObjectID support, I did nothing about the DROP case in the attached. The way I intend to solve that problem is using get_object_address() and do an extra lookup from within the Event Trigger code path. Yes that's an extra lookup, the only alternative that I see would be another giant refactoring so that all and any DROP support function is split in a lookup and lock part first, then the actual DROP. I don't see that as a particular win either, as the advantage of doing a separate (extra) lookup in the ddl_command_start Event Trigger is that the actual DROP code then will check that the target still exists. The attached patch is known to miss support for ExecCreateTableAs because I wanted to keep the ball rolling and couldn't figure out where to find the target relation id in time. If you feel like filling that gap that would be awesome, if not I will take care of that later, either in a version v1 of that patch, or in a follow-up patch, or embedded into the next patch to come, the cleaned up Event Trigger Info patch without Normalized Command String support. As you see fit. Merry Christmas All, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support oid-api.0.patch.gz Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: regrole type?
2012/12/25 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: Can we implement REGROLE type, that simplify role name - oid transformations? Why? It's not any more complicated than it is for the other object types that lack REGxxx pseudotypes. Generally speaking, we've only bothered with pseudotypes for the cases where lookup is not trivial, eg because there are search path considerations. my first motivation was a cosiness, but a second view shows so this type(s) can be useful: * It is relative natural - and can simplify and speed up some kind of queries, because it directly access to cache. * We can reduce to half lot of functions \df has_* (84 functions) pg_catalog | pg_has_role | boolean | name, name, text| normal pg_catalog | pg_has_role | boolean | name, oid, text | normal pg_catalog | pg_has_role | boolean | name, text | normal pg_catalog | pg_has_role | boolean | oid, name, text | normal pg_catalog | pg_has_role | boolean | oid, oid, text | normal pg_catalog | pg_has_role | boolean | oid, text | normal these function should be replaced with more semantics descriptive headers pg_has_role(regrole, regrole, text) pg_has_role(regrole, text) pg_has_role(text) so we can drop (42 functions from catalog) * this new datatype can be used in custom functions with implicit validity checking - and if I can sort a importance of some internal objects - then the roles are relative on high position. Best regards Pavel p.s. A implementation should be little bit harder than I expected due specific role public, but I am thinking so it can has a some value. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: regrole type?
Pavel Stehule pavel.steh...@gmail.com writes: * We can reduce to half lot of functions \df has_* (84 functions) Not without breaking existing queries. A function taking regrole might look like it substitutes for one taking a text-string user name as long as you only pass literal constants to it, but as soon as you pass non-constants you'll find out different. (Unless your plan is to also create an implicit cast from text to regrole, which strikes me as a seriously bad idea.) The reason we've not been more aggressive about using the OID-alias pseudotypes is exactly that they're not a cure-all. Otherwise we would already have about a dozen more of them. I don't think it's really worth it: the notational savings is pretty marginal and the impact on application namespace should not be ignored. (Keep in mind that any new system type causes problems for similarly-named user tables.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: regrole type?
2012/12/25 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: * We can reduce to half lot of functions \df has_* (84 functions) Not without breaking existing queries. A function taking regrole might look like it substitutes for one taking a text-string user name as long as you only pass literal constants to it, but as soon as you pass non-constants you'll find out different. (Unless your plan is to also create an implicit cast from text to regrole, which strikes me as a seriously bad idea.) understand The reason we've not been more aggressive about using the OID-alias pseudotypes is exactly that they're not a cure-all. yes, I agree. But this type can has sense without propagation to current functionality - and current functions can be marked as obsolete in future. I believe so it can clean little bit this are - mainly can solve task, where can be used pseudo role public and it is more accurate and semantic design and can be used in new functions and system views. Otherwise we would already have about a dozen more of them. I don't think it's really worth it: the notational savings is pretty marginal and the impact on application namespace should not be ignored. (Keep in mind that any new system type causes problems for similarly-named user tables.) 9.3 has no problem postgres=# create table regtype(a int); CREATE TABLE postgres=# create table regclass(a int); CREATE TABLE Regards Pavel regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: regrole type?
2012/12/25 Pavel Stehule pavel.steh...@gmail.com: Hello 2012/12/25 Pavel Golub pa...@microolap.com: Hello, Pavel. You wrote: PS Hello PS Can we implement REGROLE type, that simplify role name - oid transformations? +1 from me. My old wish. I started implementation. I found a two points, that should be solved before. we operate over roles with (without) fictive role public. So we need two datatypes :( I have no idea about second name :( - there should be difference if type enables or disallow public. second issue is value of ACL_ID_PUBLIC, that is zero - and there is not difference from InvalidOid - what should be acceptable via - symbol. Any ideas? one idea regrole - defined only for real roles - support InvalidOid - doesn't support public regaclrole - defined for any roles, that can be used for ACL (with public), doesn't support InvalidOid Regards Pavel Regards Pavel PS Regards PS Pavel -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
