Re: Adminpack removal
Le 01/07/2024 à 10:07, Daniel Gustafsson a écrit : On 28 Jun 2024, at 09:06, Philippe BEAUDOIN wrote: So just looking in public repo covers probably less than 1% of the code. However, this may give a first idea, especialy if a feature use is already detected. Searching for anything on Github is essentially a dead end since it reports so many duplicates in forks etc. That being said, I did a lot of searching and browsing to find users [0], but came up empty (apart from forks which already maintain their own copy). A more targeted search is the Debian Code search which at the time of removal (and well before then) showed zero occurrences of adminpack functions in any packaged software, and no extensions which had adminpack as a dependency. While not an exhaustive search by any means, it does provide a good hint. Since you list no other extensions using adminpack to support keeping it, I assume you also didn't find any when searching? I just said that there are much much more code in private repos (so not analyzable) than in the public ones. -- Daniel Gustafsson [0] https://www.postgresql.org/message-id/B07CC211-DE35-4AC5-BD4E-0C6466700B06%40yesql.se
Re: Adminpack removal
Le 27/06/2024 à 10:38, Matthias van de Meent a écrit : On Thu, 27 Jun 2024, 07:34 Philippe BEAUDOIN, wrote: Hi, I have just tested PG17 beta1 with the E-Maj solution I maintain. The only issue I found is the removal of the adminpack contrib. In the emaj extension, which is the heart of the solution, and which is written in plpgsql, the code just uses the pg_file_unlink() function to automatically remove files produced by COPY TO statements when no rows have been written. In some specific use cases, it avoids the user to get a few interesting files among numerous empty files in a directory. I have found a workaround. That's a little bit ugly, but it works. So this is not blocking for me. FYI, the project's repo is on github (https://github.com/dalibo/emaj), which was supposed to be scanned to detect potential adminpack usages. The extension at first glance doesn't currently seem to depend on adminpack: it is not included in the control file as dependency, and has not been included as a dependency since the creation of that file. You are right. Even before the adminpack usage removal, the extension was not listed as prerequisite in the control file. In fact, I introduced a new E-Maj feature in the version of last automn, that used the adminpack extension in one specific case. But the user may not install adminpack. In such a case, the feature was limited and a warning message told the user why it reached the limitation. I was waiting for some feedbacks before possibly adding adminpack as a real prerequisite. Where else would you expect us to search for dependencies? The word "adminpack" can be found in the sql source file (sql/emaj--4.4.0.sql), and in 2 documentation source files (in docs/en/*.rst). The pg_file_unlink() function name can be found in the same sql source file. But, I understand that looking for simple strings in all types of files in a lot of repo is costly and may report a lot of noise. More broadly, my feeling is that just looking at public repositories is not enough. The Postgres features usage can be found in: - public tools, visible in repo (in github, gitlab and some other platforms) ; - softwares from commercial vendors, so in close source ; - and a huge number of applications developed in all organizations, and that are not public. So just looking in public repo covers probably less than 1% of the code. However, this may give a first idea, especialy if a feature use is already detected. In this "adminpack" case, it may be interesting to distinguish the pg_logdir_ls() function, which covers a very specific administration feature, and the other functions, which are of a general interest. It wouldn't be surprising that pg_logdir_ls() be really obsolete now that it is not used by pgAdmin anymore, and thus could be removed if nobody complains about that. May be the others functions could be directly integrated into the core (or left in adminpack, with the pgAdmin reference removed from the documentation). Kind Regards. Philippe.
Adminpack removal
Hi, I have just tested PG17 beta1 with the E-Maj solution I maintain. The only issue I found is the removal of the adminpack contrib. In the emaj extension, which is the heart of the solution, and which is written in plpgsql, the code just uses the pg_file_unlink() function to automatically remove files produced by COPY TO statements when no rows have been written. In some specific use cases, it avoids the user to get a few interesting files among numerous empty files in a directory. I have found a workaround. That's a little bit ugly, but it works. So this is not blocking for me. FYI, the project's repo is on github (https://github.com/dalibo/emaj), which was supposed to be scanned to detect potential adminpack usages. Finally, I wouldn't be surprise if some other user projects or applications use adminpack as this is a simple way to get sql functions that write, rename or remove files. Regards.
Re: PostgreSQL and Flashback Database
Hi Didier, Have you ever had a look at the E-Maj extension. Depending on the features you are really looking for, it may fit the needs. Here are some pointers : - github repo for the extension : https://github.com/dalibo/emaj - github repo for the web client : https://github.com/dalibo/emaj_web - online documentation : https://emaj.readthedocs.io/en/latest/ <https://emaj.readthedocs.io/en/v3.4.0/> or even in French, espacially for you ;-) https://emaj.readthedocs.io/fr/latest/ Feel free to contact me to talk about it. Best regards. Philippe. Le 10/02/2021 à 09:56, ROS Didier a écrit : Hi My company is looking for a team of developers to implement the "flashback database" functionality in PostgreSQL. Do you think it's feasible to implement? how many days of development? Thanks in advance Best Regards Didier ROS E.D.F Ce message et toutes les pièces jointes (ci-après le 'Message') sont établis à l'intention exclusive des destinataires et les informations qui y figurent sont strictement confidentielles. Toute utilisation de ce Message non conforme à sa destination, toute diffusion ou toute publication totale ou partielle, est interdite sauf autorisation expresse. Si vous n'êtes pas le destinataire de ce Message, il vous est interdit de le copier, de le faire suivre, de le divulguer ou d'en utiliser tout ou partie. Si vous avez reçu ce Message par erreur, merci de le supprimer de votre système, ainsi que toutes ses copies, et de n'en garder aucune trace sur quelque support que ce soit. Nous vous remercions également d'en avertir immédiatement l'expéditeur par retour du message. Il est impossible de garantir que les communications par messagerie électronique arrivent en temps utile, sont sécurisées ou dénuées de toute erreur ou virus. This message and any attachments (the 'Message') are intended solely for the addressees. The information contained in this Message is confidential. Any use of information contained in this Message not in accord with its purpose, any dissemination or disclosure, either whole or partial, is prohibited except formal approval. If you are not the addressee, you may not copy, forward, disclose or use any part of it. If you have received this message in error, please delete it and all copies from your system and notify the sender immediately by return message. E-mail communication cannot be guaranteed to be timely secure, error or virus-free. <https://www.dalibo.com/> *DALIBO* *L'expertise PostgreSQL* 43, rue du Faubourg Montmartre 75009 Paris *Philippe Beaudoin* *Consultant Avant-Vente* +33 (0)1 84 72 76 11 +33 (0)7 69 14 67 21 philippe.beaud...@dalibo.com Valorisez vos compétences PostgreSQL, faites-vous certifier par Dalibo <https://certification.dalibo.com/> !
Re: proposal: schema variables
The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: not tested Documentation:tested, failed Hi Pavel, I have tested the latest version of your patch. Both issues I reported are now fixed. And you largely applied my proposals. That's great ! I have also spent some time to review more closely the documentation. I will send you a direct mail with an attached file for some minor comments on this topic. Except these documentation remarks to come, I haven't any other issue or suggestion to report. Note that I have not closely looked at the C code itself. But may be some other reviewers have already done that job. If yes, my feeling is that the patch could soon be set as "Ready for commiter". Best regards. Philippe. The new status of this patch is: Waiting on Author
Re: Global temporary tables
Hi all, I am not aware enough in the Postgres internals to give advice about the implementation. But my feeling is that there is another big interest for this feature: simplify the Oracle to PostgreSQL migration of applications that use global termporary tables. And this is quite common when stored procedures are used. In such a case, we currently need to modify the logic of the code, always implementing an ugly solution (either add CREATE TEMP TABLE statements in the code everywhere it is needed, or use a regular table with additional TRUNCATE statements if we can ensure that only a single connection uses the table at a time). So, Konstantin and all, Thanks by advance for all that could be done on this feature :-) Best regards.
Re: proposal: schema variables
The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, failed Spec compliant: not tested Documentation:tested, failed Hi Pavel, First of all, I would like to congratulate you for this great work. This patch is really cool. The lack of package variables is sometimes a blocking issue for Oracle to Postgres migrations, because the usual emulation with GUC is sometimes not enough, in particular when there are security concerns or when the database is used in a public cloud. As I look forward to having this patch commited, I decided to spend some time to participate to the review, although I am not a C specialist and I have not a good knowledge of the Postgres internals. Here is my report. A) Installation The patch applies correctly and the compilation is fine. The "make check" doesn't report any issue. B) Basic usage I tried some simple schema variables use cases. No problem. C) The interface The SQL changes look good to me. However, in the CREATE VARIABLE command, I would replace the "TRANSACTION" word by "TRANSACTIONAL". I have also tried to replace this word by a ON ROLLBACK clause at the end of the statement, like for ON COMMIT, but I have not found a satisfying wording to propose. D) Behaviour I am ok with variables not being transactional by default. That's the most simple, the most efficient, it emulates the package variables of other RDBMS and it will probably fit the most common use cases. Note that I am not strongly opposed to having by default transactional variables. But I don't know whether this change would be a great work. We would have at least to find another keyword in the CREATE VARIABLE statement. Something like "NON-TRANSACTIONAL VARIABLE" ? It is possible to create a NOT NULL variable without DEFAULT. When trying to read the variable before a LET statement, one gets an error massage saying that the NULL value is not allowed (and the documentation is clear about this case). Just for the records, I wondered whether it wouldn't be better to forbid a NOT NULL variable creation that wouldn't have a DEFAULT value. But finally, I think this behaviour provides a good way to force the variable initialisation before its use. So let's keep it as is. E) ACL and Rights I played a little bit with the GRANT and REVOKE statements. I have got an error (Issue 1). The following statement chain: create variable public.sv1 int; grant read on variable sv1 to other_user; drop owned by other_user; reports : ERROR: unexpected object class 4287 I then tried to use DEFAULT PRIVILEGES. Despite this is not documented, I successfuly performed: alter default privileges in schema public grant read on variables to simple_user; alter default privileges in schema public grant write on variables to simple_user; When variables are then created, the grants are properly given. And the psql \ddp command perfectly returns: Default access privileges Owner | Schema | Type |Access privileges --++--+- postgres | public | | simple_user=SW/postgres (1 row) So the ALTER DEFAULT PRIVILEGES documentation chapter has to reflect this new syntax (Issue 2). BTW, in the ACL, the READ privilege is represented by a S letter. A comment in the source reports that the R letter was used in the past for rule privilege. Looking at the postgres sources, I see that this privilege on rules has been suppressed in 8.2, so 13 years ago. As this R letter would be a so much better choice, I wonder whether it couldn't be reused now for this new purpose. Is it important to keep this letter frozen ? F) Extension I then created an extension, whose installation script creates a schema variable and functions that use it. The schema variable is correctly linked to the extension, so that dropping the extension drops the variable. But there is an issue when dumping the database (Issue 3). The script generated by pg_dump includes the CREATE EXTENSION statement as expected but also a redundant CREATE VARIABLE statement for the variable that belongs to the extension. As a result, one of course gets an error at restore time. G) Row Level Security I did a test activating RLS on a table and creating a POLICY that references a schema variable in its USING and WITH CHECK clauses. Everything worked fine. H) psql A \dV meta-command displays all the created variables. I would change a little bit the provided view. More precisely I would: - rename "Constraint" into "Is nullable" and report it as a boolean - rename "Special behave" into "Is transactional" and report it as a boolean - change the order of columns so to have: Schema | Name | Type | Is nullable | Default | Owner | Is transactional | Transaction end action "Is nullable" being aside "Default" I) Performance I just quickly looked at the performance, and
