RE: Blog post series on commitfests and patches
That’s excellent Chris, thanks very much for doing that. Concise, informative and targeted. Is there a mailing list for updates? -- Tim Clarke BSc (Hons), MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 From: Chris Travers Sent: Thursday, March 9, 2023 3:24 AM To: pgsql-generallists.postgresql.org Subject: Blog post series on commitfests and patches Hi all; I have been writing a few blog posts trying to shed some light on the development process of PostgreSQL , what's coming and what I hope we see more of. I would be very much interested in feedback as to whether people (particularly non-Postgres contributors) find this useful or not. The latest entry is at: https://www.timescale.com/blog/a-postgresql-developers-perspective-five-interesting-patches-from-januarys-commitfest/?utm_source=timescaledb_medium=linkedin_campaign=mar-2023-advocacy_content=tsdb-blog -- Best Wishes, Chris Travers [https://i0.wp.com/www.manifest.co.uk/wp-content/uploads/2022/12/Minerva-Analytics-Logo-PORTRAIT.png] [https://i0.wp.com/www.manifest.co.uk/wp-content/uploads/2022/12/esg_finalist.png] Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Watch our latest Minerva Briefings on BrightTALK<https://www.brighttalk.com/channel/18792/?utm_source=brighttalk-sharing_medium=web_campaign=linkshare> Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: General Inquiry
On 05/07/2022 09:49, Cloete, F. (Francois) wrote: Good morning, Can you please confirm if I need to contact the postgresql community fro some assistance can I use this e-mail address or another e-mail ? We keep on seeing the below entries in our postgresql.log file getting written what seems to be every millisecond. [cid:part1.48305BC8.064CE40A@minerva.info] Regards Francois It appears that you have a process running on your Postgres server that is repeatedly trying to connect to the database. Is this running on a server or a workstation? Check your users, cron jobs? Tim Clarke Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Watch our latest Minerva Briefings on BrightTALK<https://www.brighttalk.com/channel/18792/?utm_source=brighttalk-sharing_medium=web_campaign=linkshare> Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: Are stored procedures/triggers common in your industry
On 20/04/2022 20:26, Philip Semanchuk wrote: > We have some business logic in Postgres functions, particularly triggers. Our > apps are written in Python, and we use pytest to exercise our SQL functions > to ensure they're doing what we think they’re doing. It works well for us. > > FWIW, we’re not a Web dev shop. > > Cheers > Philip We have a a great amount of our business logic in triggers; makes for light, multiple and consistent front-ends. It's worked very well for many years and continues to grow. Tim Clarke Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Watch our latest Minerva Briefings on BrightTALK<https://www.brighttalk.com/channel/18792/?utm_source=brighttalk-sharing_medium=web_campaign=linkshare> Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: question about sql comments in postgresql server logs
On 06/08/2021 20:08, Alan Stange wrote: > Hello all, > > In order to track down some bugs, we thought it would be useful to > append some comments to the sql being sent to postgresql like this: > select foo from bar -- a comment with some metadata > however, the postgresql server was not including the appended comment in > the log file. > > If we instead sent > select foo from /* a comment with some metadata */ bar > then the comment was included in the server log file as we had hoped. > > Is there some way can get the statement as sent to the server emitted > into the log file in a case like this? I looked through the server > documentation and don't see an option that would clearly alter this > behavior. > > Thank you, > > Alan Raise info? Tim Clarke MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: How to implement expiration in PostgreSQL?
On 01/04/2021 15:23, Glen Huang wrote: > I wish one cron job could rule them all, but since a person can decide to > join at any time, her expiration (e.g., after 1 hour) can also happen at any > time. So one cron job won’t cut it if a member’s expiration has to bee > accurate. One cron job running every 5 minutes should do? Tim Clarke MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: How to implement expiration in PostgreSQL?
On 01/04/2021 14:47, Glen Huang wrote: > Good suggestion, thanks. > > I ended up running the cron jobs in my app, one for each newly joined member, > to get the smallest granularity possible. (This list bottom-posts by convention) I'd say that was onerous and you could get the same effect with a well-crafted query that targetted only those that might possibly expire. Then you'd have only one cron job to manage. Tim Clarke MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: How to implement expiration in PostgreSQL?
On 01/04/2021 14:28, Glen Huang wrote: >> Possibly keep your count of members updated via a trigger? > But how to evoke the trigger when the count of members should be updated by a > timeout, i.e., the person’s pay is due? I'd run a cron job that triggers a function call which would make the necessary expiry tests and set the status accordingly. Maybe run the cron once an hour or once a day depending on the granularity of your needs? Tim Clarke MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: accessing cross-schema materialized views
On 31/03/2021 23:42, Tom Lane wrote: > The owner of the schema2.usingview is the one who must have > privilege to read the underlying schema1.matview. Our > permissions messages are, I fear, frequently not very good > about saying whose privileges were checked. > > regards, tom lane That nailed it Tom, thanks. Tim Clarke MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: How to implement expiration in PostgreSQL?
On 01/04/2021 02:51, Glen Huang wrote: > Hi, > > I guess this question has been asked a million times, but all solutions I can > find online don’t really work well for my case. I’ll list them here and hope > someone can shed some light. > > My use case is to implement joining clubs that require entrance fee: > > 1. Each clubs only allows maximum number of members. > 2. When a person decides to join a club, the seat is reserved for a limited > amount of time. If that person fails to pay within that period, the seat will > be open again > > I want to write a query that can quickly list all clubs that still have open > seats and #2 is where I want expiration to happen. > > The solutions I find so far: > > 1. Exclude closed clubs in queries and periodically delete expired members > > I can’t come up with a query that can accomplish this in an efficient way. > > WITH seated_member AS ( > SELECT > club_id, > count(member_id) AS num_seated_member > FROM member > WHERE paid OR join_time > now() - ‘1h’::interval > GROUP BY club_id > ), > open_member AS ( > SELECT > club_id, > max_num_member - coalesce(num_seated_member, 0) AS num_open_member > FROM club LEFT JOIN seated_member USING(club_id) > ) > SELECT club_id AS open_club > FROM open_member > WHERE num_open_member > 0 > > This requires going through all seated members, which can potentially be > large and takes a long time. > > I can of course add an num_open_member column to the club table and index it, > but the problem becomes how to automatically update it when a member expires, > which take us back to square one. > > All following solutions assume I add this column and seek to find a way to > update it automatically. > > 2. Run a cron job > > This won’t work because the number is updated only after the cron job is run, > which only happens at intervals. > > 3. Update the column before running any related queries > > This requires I execute DELETE and UPDATE queries before all seat related > queries. It’s hard to manage and seems to slow down all such queries. > > 4. pg_cron > > My environment wouldn’t allow me to install 3rd-party extensions, but even if > I could, it seems pg_cron run cron jobs sequentially. I’m not sure it works > well when I need to add a cron job for each newly joined member. > > — > > I’m not aware any other solutions. But the problem seems banal, and I believe > it has been solved for a long time. Would really appreciate it if someone > could at least point me in the right direction. > > Regards, > Glen > Possibly keep your count of members updated via a trigger? Tim Clarke Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
accessing cross-schema materialized views
We have: create materialized view schema1.matview. grant select on table schema1.matview to mygroup create view schema2.usingview as select ... from schema1.matview grant select on table schema2.using to mygroup and yet we receive "permission denied for materialized view" on a user with the mygroup role selecting from schema2.usingview? The same user can select from schema1.matview without issue? I must be tired and I can't see why that should fail.... :( -- Tim Clarke Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: Is it possible to compare a long text string and fuzzy match only phrases contained in?
On 17/01/2021 23:09, Benedict Holland wrote: You want to do NLP in postgres? I would say that you would need a tool like opennlp to get your tokens and phases, then run a fuzzy matching algorithm. Unless postgres has nlp capabilities but I am not sure I would use them. You actually want something fairly complex. Thanks, Ben On Sun, Jan 17, 2021, 4:55 PM Shaozhong SHI mailto:shishaozh...@gmail.com>> wrote: We are looking for working examples of comparing a long text string and fuzzy-matching multiple words (namely, phrases) contained in. Any such work examples? Regards, David We've had excellent results with https://www.postgresql.org/docs/13/fuzzystrmatch.html and you may find https://www.postgresql.org/docs/13/unaccent.html very useful in this area too Tim Clarke MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: CROSSTAB( .. only one column has values... )
On 05/01/2021 16:12, David G. Johnston wrote: On Tue, Jan 5, 2021 at 8:46 AM Adam Tauno Williams mailto:awill...@whitemice.org>> wrote: Only the first column has values, all the rest are NULL. I assume I am missing something stupid. I think you are assigning the function more intelligence/effort than it puts out. From the documentation: """ The crosstab function produces one output row for each consecutive group of input rows with the same row_name value. It fills the output value columns, left to right, with the value fields from these rows. If there are fewer rows in a group than there are output value columns, the extra output columns are filled with nulls; if there are more rows, the extra input rows are skipped. In practice the SQL query should always specify ORDER BY 1,2 to ensure that the input rows are properly ordered, that is, values with the same row_name are brought together and correctly ordered within the row. Notice that crosstab itself does not pay any attention to the second column of the query result; it's just there to be ordered by, to control the order in which the third-column values appear across the page. """ The fact you don't have an order by, and that there are not an equal number of records per date, suggests to me that you are expecting the function to fill in the blanks when the documentation says it doesn't do that. David J. +1 imho the crosstab() function isn't a good implementation. The biggest failure it has is that you must know exactly how many output columns you will have in the result prior to running it Tim Clarke MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: Accessing Postgres Server and database from other Machine
On 04/12/2020 12:56, Hemil Ruparel wrote: I dont think you can use pgAdmin to do that kind of thing but I may be wrong. Google how to restart a service in Windows and then restart postgres On Fri, Dec 4, 2020 at 5:36 PM Muthukumar.GK mailto:muthanku...@gmail.com>> wrote: No.I did not restart postgres.Please let me know the steps to restart postgres using pgadmin4 tool. On Fri, Dec 4, 2020 at 5:26 PM Hemil Ruparel mailto:hemilruparel2...@gmail.com>> wrote: Are you sure you restarted postgres after the change in configuration? On Fri, Dec 4, 2020 at 3:58 PM Muthukumar.GK mailto:muthanku...@gmail.com>> wrote: Hi Team, I have created Database in postgresqlv13 . My Office colleague needs to access my postgres server and database from his machine. There is a config file called 'Postgresql.conf.sample' in program files folder. Even if I changed the connection settings of listen address from localhost to *( Connection Settings - #Listen address ='*' ) in config file. But still my colleague is not able to connect it. Kindly provide some guidance on this. Regards Muthu It looks to me like the line is still commented out (as it is by default). Remove any "#" (hash or pound) symbol from the beginning of the line then restart Postgres. Tim Clarke MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: Meaning of below statement
On 20/11/2020 08:29, David G. Johnston wrote:
On Friday, November 20, 2020, Srinivasa T N
mailto:seen...@gmail.com>> wrote:
Hi,
I have the following in my log files:
2020-11-20 11:20:46.216 IST [38207] LOG: execute S_1/C_2: SELECT
"gid",encode(ST_AsBinary(ST_Simplify(ST_Force2D("shape"), 14.929338247701526,
true)),'base64') as "shape" FROM "ami_smart_new"."aoi_boundary" WHERE
("sectioncode" IN ('4683', '4587') AND "sectioncode" IS NOT NULL AND "shape" &&
ST_GeomFromText('POLYGON ((683696.123647752 989199.9990667417, 683696.123647752
1000723.135701899, 708574.8226023088 1000723.135701899, 708574.8226023088
989199.9990667417, 683696.123647752 989199.9990667417))', 32643))
2020-11-20 11:20:46.218 IST [38207] LOG: execute S_3: ROLLBACK
Does it mean that there was an error in "SELECT ..." and hence internally
postgres executed ROLLBACK??
If there was an error you would see an error message. Plus, PostgreSQL doesn’t
just internally issue a rollback on its own. That said I’m not sure what is
being shown, or rather where the “execute” stuff comes from.
David J.
Given that PostgreSQL isn't throwing the error, I'd suggest the rollback is
from a decision in the front-end application layer.
Tim Clarke MBCS
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420
Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt:
+49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/
Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom
Copyright: This e-mail may contain confidential or legally privileged
information. If you are not the named addressee you must not use or disclose
such information, instead please report it to
ad...@minerva.info<mailto:ad...@minerva.info>
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd:
Registered in England Number 11260966 & The Manifest Voting Agency Ltd:
Registered in England Number 2920820 Registered Office at above address. Please
Click Here https://www.manifest.co.uk/legal/ for further information.
Re: What's the best way to translate MS generated translations of user input to what the user actually typed prior to insert or update into PG backend table ?
On 30/10/2020 16:03, David Gauthier wrote: psql (11.5, server 11.3) on linux I'm using MS-Access as a Windows front-end to a PG DB table through ODBC (PostgreSQL Unicode ODBC Driver). Seems to be working fine except for when users enter "..." as part of a string, MS (in it's infinite wisdom) decides to translate that to what emacs is describing as... character: … (displayed as …) (codepoint 8230, #o20046, #x2026) preferred charset: unicode (Unicode (ISO10646)) code point in charset: 0x2026 It makes this translation for some users but not others, so I'm assuming it has something to do with how individuals set up their Windows env. No matter, assume we can't have them change their environment in whatever soln we come up with. I want to change the 0x2026 to the 3 periods which the user entered. I was thinking of a pre-insert and pre-update trigger which could make the translation. But I'd rather not try to do this one char at a time... translate "..." today to fix today's issue, then "--" tomorrow when that pops up, then the way MS translates double quotes the next day, etc... . Is there an elegant way to do this ? Thanks In Advance for any help ! IMHO this is the spelling functionality from Office/Access "correcting" your typing, you can probably see it happening as you type and tapping the escape key immediately afterwards undoes the "suggestion". You're right in that it would need either a desktop change per user or a translation table. You can find all the changes though and do them all at once which saves your "next day, next day" issue? Tim Clarke Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: Migration of DB2 java stored procedures to PostgreSQL
On 24/08/2020 18:31, Laurenz Albe wrote: > On Mon, 2020-08-24 at 16:32 +, Dirk Krautschick wrote: >> for a potential migration from DB2 on DB2/z to PostgreSQL I have to take >> care of a whole >> bunch of java stored procedures. Would that be a show stopper here or is a >> migration >> somehow possible or is it anyway the same because Java = Java? >> >> Any experiences/recommendations or helpful ressources? > There is PL/Java, but it is not part of the core PostgreSQL distribution, so > you'd > have to build it yourself. > > If performance is important or you don't want to depend on third-party > modules, > post the code to PL/Python or PL/Perl. If the code is just glue around some > SQL, > PL/pgSQL might be the best choice. > > Yours, > Laurenz Albe +1 for PL/Java, we've been using it for years. Tim Clarke MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: passing linux user to PG server as a variable ?
On 17/08/2020 20:52, David Gauthier wrote: 9.6.7 on linux I need to insert the linux username of a user on the client side into a col using an insert statement. I realize that the server knows nothing about who the linux user was on a client, but I was thinking that I might be able to pass that in somehow through a variable. Looking at psql command line options, I see "-v" (lowercase) which is described as... -v assignment --set=assignment --variable=assignment Perform a variable assignment, like the \set meta-command. Note that you must separate name and value, if any, by an equal sign on the command line. To unset a variable, leave off the equal sign. To set a variable with an empty value, use the equal sign but leave off the value. These assignments are done during a very early stage of start-up, so variables reserved for internal purposes might get overwritten later. So I tried that without success. "-v sysinfo.osuser=foo" failed the connect with... "psql: could not set variable "sysinfo.osuser"" Next I tried... "-v osuser=foo" This didn't fail the connect, but once I got in... "show osuser" gave... "ERROR: unrecognized configuration parameter "osuser"" I don't even know if this approach has any legs or not given what I want to do. I'm just trying ideas hoping something will work. But if it might work, is this setting a variable like this something that can be run unconditionally whenever a linux user connects to the DB on a client server ? If so, where would this be inserved in the connect process ? TO get the linux user, I would just tap $USER or backtick `whoami` or something like that. Of course if there is another way to accomplish my goal, I'm all ears :-) Thanks in advance for any replies/ideas ! How many users do you have to identify? Have them log in to your application and thence to PG then you can pick up the PG CURRENT_USER var? Tim Clarke Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: PG Admin 4
Why would you shun the ease of command line batch control? Tim Clarke MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 On 10/07/2020 17:36, rwest wrote: Oh sorry, should have specified that. We're running on a Windows platform. We're trying to avoid running anything command-line when doing DDL releases and leverage whatever PG Admin 4 can provide us. I'm just wondering why we don't see the results of each CREATE or ALTER statement as the script runs in the tool. That seems very strange to me. Is there some sort of setting or parameter that can force the tool to do this for us? -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: Oracle vs. PostgreSQL - a comment
On 02/06/2020 19:43, Stephen Frost wrote: >> But require a new port, and Enterprises have Processes that must be followed. > Sure they do. Automate them. > > :) > > Thanks, > > Stephen +1 for automation, isoX != slow Tim Clarke MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: Oracle vs. PostgreSQL - a comment
On 02/06/2020 09:22, Ron wrote: The inability to do a point-in-time restoration of a single database in a multi-db cluster is a serious -- and fundamental -- missing feature (never to be implemented because of the fundamental design). In SQL Server, it's trivial to restore -- including differentials and WAL files -- an old copy of a prod database to a different name so that you now have databases FOO and FOO_OLD in the same instance. In Postgres, though, you've got to create a new cluster using a new port number (which in our case means sending a firewall request through channels and waiting two weeks while the RISK team approves opening the port -- and they might decline it because it's non-standard -- and then the Network team creates a change order and then implements it). Bottom line: something I can do in an afternoon with SQL Server takes two weeks for Postgres. This has given Postgres a big, fat black eye with our end users. -- Angular momentum makes the world go 'round. But that's nothing to do with Postgres; it takes two weeks because you have broken procedures imho Tim Clarke Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: Best way to use trigger to email a report ?
Personally I'd have your trigger put the necessary data into a queue to run the report then have some other process take that data off asynchronously. Either pop it in a table and do it yourself or use a robust distributed broker protocol platform like RabbitMQ or Apache ActiveMQ or Kafka. Tim Clarke MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 On 08/05/2020 17:26, David Gauthier wrote: psql (9.6.0, server 11.3) on linux Looking for ideas. I want a trigger to... 1) compose an html report based on DB content 2) email the report to a dist list (dl = value of a table column) If this will involve hybrid coding, I prefer PL/perl. The linux env has both "mail" and "mutt" (if this is of any help). The idea is to send a report to the list when all the data has been collected for a particular job and the final status of the job is updated as a col of a rec of a certain table. Probably a post update trigger. Thanks for any ideas :-) Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: psql show me the : and ask user input, when running one sql file
Arden Your first argument is a JDBC connection string (see here https://jdbc.postgresql.org/documentation/80/connect.html). To provide the details when using the command line psql command, use the -h, -p and -U parameters (https://www.postgresql.org/docs/12/app-psql.html) Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 On 05/04/2020 13:50, arden liu wrote: I am using psql to run this sql file(https://github.com/Arelle/Arelle/blob/master/arelle/plugin/xbrlDB/sql/public/xbrlPublicPostgresDB.ddl) here is my command: /usr/bin/psql postgresql://db_user:dbpassword@localhost:5432/my_db -f /tmp/xbrlPublicPostgresDB.ddl I do not know why it show me the : , which is asking me to input something. Can someone help me? Thanks. Arden Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: R: Postgresql 12.x on Windows (vs Linux)
There's always a good time to re-examine that :D Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 On 02/03/2020 15:52, Ron wrote: > LOL. Double LOL, even. We -- and a huge number of other > organizations -- are completely wrapped in the Windows environment, > from Outlook and Excel to SharePoint to the myriad of 3rd party > programs that *only* work on Windows. > > On 3/2/20 9:37 AM, Tim Clarke wrote: >> Not at all, we found that Linux "expertise" is 1/10 the cost of Windows >> expertise. Time to plan for getting rid of the site license. >> >> Tim Clarke >> IT Director >> Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 >> >> On 02/03/2020 15:32, Ron wrote: >>> Your comment assumes that OP does *not* have have a site license, and >>> *does* have Linux expertise. Neither assumption is always valid. >>> >>> (And, of course, the Windows server might already exist.) >>> >>> On 3/2/20 9:06 AM, Tim Clarke wrote: >>>> But why even bother paying for MS licenses? Postgres runs like a train >>>> on Linux. Save your money. >>>> >>>> Tim Clarke >>>> IT Director >>>> Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 >>>> >>>> On 02/03/2020 15:01, Roberto Della Pasqua wrote: >>>>> Well, >>>>> >>>>> on Windows you should try ReiserFS over a nvme optimized WHQL >>>>> certified hardware. >>>>> >>>>> *Da:* Robert Ford >>>>> *Inviato:* lunedì 2 marzo 2020 15:42 >>>>> *A:*pgsql-general@lists.postgresql.org >>>>> *Oggetto:* Postgresql 12.x on Windows (vs Linux) >>>>> >>>>> I am aware that this might be a broad question, but I am not expecting >>>>> *very *specific answers either: >>>>> >>>>> When it come to running a modern PostgreSQL server, which serves say 1 >>>>> TB of data, are there substantial differences in performance between >>>>> Windows Server 2019 and Linux today? I know there are some issues >>>>> with shared_buffers and windows, but does it manifest in real >>>>> performance issues? >>>>> >>>>> I have searched the web for this question, but is surprised to find >>>>> very few concrete benchmarks on this. Anyone with real world >>>>> experience on this topic? >>>>> >>>>> best regards >>>>> >>>>> RF >>>>> >>>> Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | >>>> Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 >>>> 647 503 2848 >>>> Web:https://www.manifest.co.uk/ >>>> >>>> >>>> >>>> Minerva Analytics Ltd - A Solactive Company >>>> 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United >>>> Kingdom >>>> >>>> >>>> >>>> Copyright: This e-mail may contain confidential or legally privileged >>>> information. If you are not the named addressee you must not use or >>>> disclose such information, instead please report >>>> ittoad...@minerva.info<mailto:ad...@minerva.info> >>>> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: >>>> Registered in England Number 11260966 & The Manifest Voting Agency Ltd: >>>> Registered in England Number 2920820 Registered Office at above address. >>>> Please Click Herehttps://www.manifest.co.uk/legal/ for further >>>> information. >>> -- >>> Angular momentum makes the world go 'round. >> Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | >> Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 >> 503 2848 >> Web:https://www.manifest.co.uk/ >> >> >> >> Minerva Analytics Ltd - A Solactive Company >> 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United >> Kingdom >> >> >> >> Copyright: This e-mail may contain confidential or legally privileged >> information. If you are not the named addressee you must not use or disclose >> such information, instead please report it >> toad...@minerva.info<mailto:ad...@minerva.info> >> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: &
Re: R: Postgresql 12.x on Windows (vs Linux)
Not at all, we found that Linux "expertise" is 1/10 the cost of Windows expertise. Time to plan for getting rid of the site license. Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 On 02/03/2020 15:32, Ron wrote: > Your comment assumes that OP does *not* have have a site license, and > *does* have Linux expertise. Neither assumption is always valid. > > (And, of course, the Windows server might already exist.) > > On 3/2/20 9:06 AM, Tim Clarke wrote: >> But why even bother paying for MS licenses? Postgres runs like a train >> on Linux. Save your money. >> >> Tim Clarke >> IT Director >> Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 >> >> On 02/03/2020 15:01, Roberto Della Pasqua wrote: >>> Well, >>> >>> on Windows you should try ReiserFS over a nvme optimized WHQL >>> certified hardware. >>> >>> *Da:* Robert Ford >>> *Inviato:* lunedì 2 marzo 2020 15:42 >>> *A:*pgsql-general@lists.postgresql.org >>> *Oggetto:* Postgresql 12.x on Windows (vs Linux) >>> >>> I am aware that this might be a broad question, but I am not expecting >>> *very *specific answers either: >>> >>> When it come to running a modern PostgreSQL server, which serves say 1 >>> TB of data, are there substantial differences in performance between >>> Windows Server 2019 and Linux today? I know there are some issues >>> with shared_buffers and windows, but does it manifest in real >>> performance issues? >>> >>> I have searched the web for this question, but is surprised to find >>> very few concrete benchmarks on this. Anyone with real world >>> experience on this topic? >>> >>> best regards >>> >>> RF >>> >> Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | >> Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 >> 503 2848 >> Web:https://www.manifest.co.uk/ >> >> >> >> Minerva Analytics Ltd - A Solactive Company >> 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United >> Kingdom >> >> >> >> Copyright: This e-mail may contain confidential or legally privileged >> information. If you are not the named addressee you must not use or disclose >> such information, instead please report it >> toad...@minerva.info<mailto:ad...@minerva.info> >> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: >> Registered in England Number 11260966 & The Manifest Voting Agency Ltd: >> Registered in England Number 2920820 Registered Office at above address. >> Please Click Herehttps://www.manifest.co.uk/legal/ for further information. > > -- > Angular momentum makes the world go 'round. Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: R: Postgresql 12.x on Windows (vs Linux)
But why even bother paying for MS licenses? Postgres runs like a train on Linux. Save your money. Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 On 02/03/2020 15:01, Roberto Della Pasqua wrote: > > Well, > > on Windows you should try ReiserFS over a nvme optimized WHQL > certified hardware. > > *Da:* Robert Ford > *Inviato:* lunedì 2 marzo 2020 15:42 > *A:* pgsql-general@lists.postgresql.org > *Oggetto:* Postgresql 12.x on Windows (vs Linux) > > I am aware that this might be a broad question, but I am not expecting > *very *specific answers either: > > When it come to running a modern PostgreSQL server, which serves say 1 > TB of data, are there substantial differences in performance between > Windows Server 2019 and Linux today? I know there are some issues > with shared_buffers and windows, but does it manifest in real > performance issues? > > I have searched the web for this question, but is surprised to find > very few concrete benchmarks on this. Anyone with real world > experience on this topic? > > best regards > > RF > Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: MS Access Frontend
On 30/11/2019 19:04, Jason L. Amerson wrote: Thanks Martin. I have decided to go another route. I have nothing but problems whenever I use Microsoft products. I personally think that Microsoft was shit when it started, and it is still shit 35 years later. So, I am just going to take Windows off my computers and put Linux on them and just use a different client. Jason L. Amerson +1 Libreoffice has a quick and easy database front end. Not great but functional and effort free. -- Tim Clarke Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: MS Access Frontend
On 29/11/2019 17:30, Adrian Klaver wrote: > On 11/29/19 9:23 AM, Jason L. Amerson wrote: >> I am trying to setup MS Access as a frontend so that it would be >> easier on my wife and children to interact with PostgreSQL. I looked >> online for some tutorials but the ones I found are out-date or only >> pick up after Access is connected to PostgreSQL. I was wondering if >> someone knew of some updated material that they could point me to or >> maybe walk me through it. I have used Access quite a bit years ago >> and things have changed since then. I know I must install the ODBC >> drivers, which I have already done. I have already setup the DSN and >> I clicked on test and it says everything is fine. I know that my next >> step has something to do with Linked Tables in Access, but I am not >> sure how to set it up. I guess that is where I start to need help. >> The client computers using the frontend will be running Windows 10 >> and Office 365, both are updated to the latest versions. > > This might help: > > https://support.office.com/en-us/article/Manage-linked-tables-1d9346d6-953d-4f85-a9ce-4caec2262797 > > > >> >> Thank you, >> >> Jason L. Amerson >> > > That will help you manage once you have created some linked tables, but to create them: 1) Click "External Data" from the top Access menu, then "New data source" on the left 2) Pick "From other sources" at the bottom and as you correctly identified "ODBC Database". 3) From the dialogue box, change the default of the two options to the bottom one "Linkby creating linked table" 4) Next pick your created DSN from the "Select data source" dialog 5) You should then see the list of tables etc from your Postgres database Troubleshooting; make sure you have an ODBC 32 or 64 bit version matching the 32 or 64 bit MS Access installed. If you use any security at the MS Access level you will need to set up a simultaneously shared central system.mdw file in some repository. Don't try to get more than 6-7 people using this at the same time, it breaks. Good luck. -- Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: Client Computers
The ODBC drivers are generally very useful. Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 On 23/11/2019 12:23, Jason L. Amerson wrote: > > Thanks > > Jason L. Amerson > > *From:* Daniel Baktiar > *Sent:* Saturday, November 23, 2019 07:15 AM > *To:* Jason L. Amerson > *Cc:* PostgreSQL > *Subject:* Re: Client Computers > > Hi Jason, > > AFAIK, you just need the pgadmin installed. > > Regards, > > Daniel > > On Sat, Nov 23, 2019 at 6:26 PM Jason L. Amerson > mailto:drja...@alphagenius.org>> wrote: > > Do my Windows 10 computers that will be clients, need PostgreSQL > installed to connect remotely to my server or is pgAdmin or some > other client all I need? > > Jason L. Amerson > Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: Is my lecturer wrong about PostgreSQL? I think he is!
On 09/10/2019 20:45, Alan Hodgson wrote: > Assuming you're not a troll ... > > On Wed, 2019-10-09 at 20:06 +0100, Pól Ua Laoínecháin wrote: >> 1) Is my lecturer full of it or does he really have a point? > He's more than full of it. PostgreSQL has had a few bugs over the year > that could have resulted in data corruption, but they're pretty rare > and fixed as soon as they're found. PostgreSQL is the most reliable > software I run, and virtually the only major piece I don't hesitate to > upgrade without waiting to see what bugs other people find first. >> 4) What is the OS of choice for *_serious_* PostgreSQL installations? > That's a religious question, not a technical question. I think even > Microsoft makes a decent server OS nowadays. But I expect a large > majority of PostgreSQL installations are running on Linux, as are the > vast majority of all server apps nowadays. Having said that, I don't > run a "serious" PostgreSQL installation; some of the people here run > databases that do tens of thousands of TPS and hold many TiB of data. > You'd have to ask them I guess. Pretty sure if you run through Oracle's back catalogue you'll find a similar data corruption for an entirely unrelated reason. Just guessing but chances are Tim Clarke Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: Web users as database users?
On 20/09/2019 12:50, David Gallagher wrote: > Hi - I’m working on a database that will be accessed via a web app. > I’m used to a setup where there is one account/role that the app would > use to access the database, but in my current scenario I’m interested > in row level security and auditing enforced by the database. In this > scenario, would it make sense to have a user account on the database > to mirror the user account from the web app? Is that an unusual practice? Not at all, we're doing it Tim Clarke Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: Recomended front ends?
On 08/08/2019 17:55, Stuart McGraw wrote: > > I would be a little cautious about Django. Last time I checked, > like many other web frameworks, it treats the database as just another > component, one that provides data persistence for it, and consequently > imposes its own constraints the schemas it will work with. Specifically > IIRC it insists that tables have a single-column primary keys. If the > client's existing database is already designed this way then that may > not be a problem but if it has composite PKs then another option may > be better. > > Flask is another relatively easy to use framework, can be used with or > without Sqlalchemy but doesn't have the wealth of addons available with > Django and being simpler requires more work to build the end application. > There are of course many other framework options (Bottle, Web2Py, etc) > > Although it's been a decade plus since I worked with Microsoft products > I had fairly good luck back then using Microsoft Access / VBA connected > to a Postgresql backend via ODBC. Even back then MS's frontend > development > tools were way more advanced and easy to use than anything available for > free in the Linux world. The downside was having to program in VBA but > things may be much better these days with .NET et.al. We tried Django without any pleasant results. I'd also caution using MS Access, we're desperate to get away from it. Sharing code has challenges and it is horribly aggressive with caching unless you use un-bound forms and write all the CRUD interface code yourself. Tim Clarke Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: Recomended front ends?
All excellent solutions, may I add Lucee to the list. We call it "the best web development system no-one knows about". Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 On 07/08/2019 21:38, Rich Shepard wrote: > On Wed, 7 Aug 2019, Igor Korot wrote: > >> On top of what already been said - make sure that the product you are >> about to start working on will have its requirements clear and concise. > > This is a critical process that needs to be developed in depth. One > criterion that will guide your choice of UI is whether the database > will be > accessed only on the LAN or also remotely. For the former, consider using > Python3 + psycopg + SQLAlchemy. For the latter, consider a web-based > application using Django. > > HTH, > > Rich > > Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: How to run a task continuously in the background
On 12/07/2019 08:08, lilu wrote: > >> 在 2019年7月12日，下午1:06，Dirk Mika > <mailto:dirk.m...@mikatiming.de>> 写道： >> >> A cron job will only run once a minute, not wake up every second. >> Right, that’s an issue. >> But you could write a PHP program that does a one-second sleep before >> checking if there's something to do, and a batch job that runs >> periodically to see if the PHP program is running, and if not, launch >> it. >> I would like to avoid external programs if possible. In the current >> Oracle environment, there are potentially multiple schemas on a >> server in which processing can be active. And processing can be >> started, monitored and stopped from a client application. And only >> for the schema with which the application is connected. >> BR >> Dirk But using a specific program that is good at its job is the unix way and better for it imho. If you have a good scheduler why re-write another one into every application? Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: Restore database into azure PostgreSQL
On 11/07/2019 17:29, Adrian Klaver wrote: > On 7/11/19 9:25 AM, mahesh ravilla wrote: >> Hi, when restore a on promises database into azure PostgreSQL. I am >> ge3a error please help ASAP. >> >> Could not connect to server :connection refused >> Is the server running on host " azure_server_name" (000.000.000.00) >> and accepting >> Tcp/ip connection on port 5432? > > Connection troubleshooting: > > 1) Verify Postgres server is actually running. > > 2) Verify what port it is listening on(5432 is default). > > 3) Verify correct hostname. > > 4) Check to see if there is a firewall blocking the Postgres server port. > >> >> Thanks >> Mahesh Ravilla >> > Looks like that (dns?) symbol azure_server_name points to a broken IP address? Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: Need a DB layout gui
I've been enjoying dbeaver for this Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 On 24/06/2019 23:38, Adrian Klaver wrote: > On 6/24/19 3:25 PM, David Gauthier wrote: >> Hi: >> >> I've been poking around >> https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools to >> see if there is anything that can read PG metadata in and display it >> graphically in a gui. You know, the kind of picture that is 1 block >> per table with 1->many arrows connecting up the primary/foreign keys >> of different tables. SQL Power Architect looked promising, but fails >> to do the linkage for some PK-FK relationships (buggy?) . >> >> Any suggestions on what might do this? > > After my previous post I realized you said GUI:( > > Do you want something that you can interact with? > > Also SQL Workbench/J will display FK relationships: > > http://www.sql-workbench.eu/FkDisplay_png.html > > Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: sequences
There's nothing really "in" a sequence, it just generates numbers for you, generally for unintelligent primary keys. Those statements perfectly create new sequences which will start from 1. Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 On 24/06/2019 10:01, Karl Martin Skoldebrand wrote: > > Hi, > > I’m trying to troubleshoot a case where users are getting data from a > database, despite they having said they are not interested. > > There is a number of sequences in the database in question, but to me > they look “empty”, like stubs. > > *create sequence *db_table_seq; > > *alter sequence *db_table_seq *owner to *dbnameuser; > > (all names obfuscated) > > To me it look like the only thing going is changing the ownership of > the sequence. For unknown reasons. > > What is your impression the intention is with this? > > //Martin S > > > > Disclaimer: This message and the information contained herein is > proprietary and confidential and subject to the Tech Mahindra policy > statement, you may review the policy at > http://www.techmahindra.com/Disclaimer.html externally > http://tim.techmahindra.com/tim/disclaimer.html internally within > TechMahindra. > > > Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Re: logical replication - negative bitmapset member not allowed
On 04/04/2019 22:37, Peter Intrauterine wrote: > On 2019-04-01 23:43, Alvaro Herrera wrote: >> Maybe the replica identity of a table got set to a unique index on oid? >> Or something else involving system columns? (If replication is >> otherwise working, the I suppose there's a separate publication that's >> having the error; the first thing to isolate would be to see what tables >> are involved in that publication). > Looking through the code, the bms_add_member() call in > logicalrep_read_attrs() does not use the usual > FirstLowInvalidHeapAttributeNumber offset, so that seems like a possible > problem. > > However, I can't quite reproduce this. There are various other checks > that prevent this scenario, but it's plausible that with a bit of > whacking around you could hit this error message. > Promise I've not been whacking around.. Tim Clarke Main: +44 (0)1376 503500 | Fax: +44 (0)1376 503550 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | England Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here >><https://www.manifest.co.uk/legal/> for further information.
Re: logical replication - negative bitmapset member not allowed
On 02/04/2019 15:46, Tom Lane wrote: > I'm glad you're out of the woods, but we still have a bug there > waiting to bite the next person. I wonder if you'd be willing to > spend some time trying to develop a reproduction sequence for this > (obviously, working on a test setup not your live servers). > Presumably there's something in the subscription-alteration logic > that needs work, but I don't think we have enough detail here for > somebody else to reproduce the error without a lot of guesswork. > > regards, tom lane I'll do what I can :) Tim Clarke Main: +44 (0)1376 503500 | Fax: +44 (0)1376 503550 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | England Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here >><https://www.manifest.co.uk/legal/> for further information.
Re: logical replication - negative bitmapset member not allowed
On 02/04/2019 14:59, Tom Lane wrote: > Well, that's not much help :-(. Can you provide any info to narrow > down where this is happening? I mean, you haven't even told us whether > it's the primary or the slave that is complaining. Does it seem to > be associated with any particular command? (Turning on log_statement > and/or log_replication_commands would likely help with that.) Does > data seem to be getting transferred despite the complaint? If not, > what's missing on the slave? > > regards, tom lane I've been working to narrow it, the error is being reported on the slave. The only schema changes have been the two primary keys added to two tables. The problem occurred during this cycle: 1) Replication proceeding fine for ~380 tables, all added individually not "all tables". 2) Add primary key on master. 3) Add primary key on slave. 4) Refresh subscription on slave; error starts being reported. I've cleared it by dropping the slave database, re-creating from the live schema then fully replicating. Its all running happily now. Tim Clarke Main: +44 (0)1376 503500 | Fax: +44 (0)1376 503550 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | England Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here >><https://www.manifest.co.uk/legal/> for further information.
Re: logical replication - negative bitmapset member not allowed
Dang. I just replicated ~380 tables. One was missing an index so I paused replication, added a unique key on publisher and subscriber, re-enabled replication and refreshed the subscription. The table has only 7 columns, I added a primary key with a default value from a new sequence. Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 On 01/04/2019 15:02, Tom Lane wrote: > Tim Clarke writes: >> I'm getting this message every 5 seconds on a single-master, >> single-slave replication of PG10.7->PG10.7 both on Centos. Its over the >> 'net but otherwise seems to perform excellently. Any ideas what's >> causing it and how to fix? > That'd certainly be a bug, but we'd need to reproduce it to fix it. > What are you doing that's different from everybody else? Can you > provide any other info to narrow down the problem? > > regards, tom lane Main: +44 (0)1376 503500 | Fax: +44 (0)1376 503550 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | England Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here >><https://www.manifest.co.uk/legal/> for further information.
logical replication - negative bitmapset member not allowed
I'm getting this message every 5 seconds on a single-master, single-slave replication of PG10.7->PG10.7 both on Centos. Its over the 'net but otherwise seems to perform excellently. Any ideas what's causing it and how to fix? -- Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Main: +44 (0)1376 503500 | Fax: +44 (0)1376 503550 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | England Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here >><https://www.manifest.co.uk/legal/> for further information.
Re: Case Insensitive
On 28/03/2019 11:10, Andy Anderson wrote: Would this also select characters with diacritical marks? For example, eid | ename -+--- 1 | aaa 2 | AAA 3 | áäâ 4 | āåȧ — Andy For that you need https://www.postgresql.org/docs/10/unaccent.html Tim Clarke Main: +44 (0)1376 503500 | Fax: +44 (0)1376 503550 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | England Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here >><https://www.manifest.co.uk/legal/> for further information.
Re: POSTGRES/MYSQL
+1 for Postgres here. We moved to it from Oracle 15 years ago and have never looked back for a second. mySQL databases over the same period have needed occasional repairs that Postgres never has. We couldn't possibly choose mySQL over Postgres now; the feature list of mySQL is so inferior we'd need to come up with some major engineering patches. Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 On 11/03/2019 19:20, Gavin Flower wrote: > On 12/03/2019 05:35, Michael Nolan wrote: > [...] >> MySQL is better at isolating users from each other and requires less >> expertise to administer. > > [...] > > I keep reading that MySQL is easier to administer, but never seen any > evidence of that. And in my very limited experience of both, I've > found PostgreSQL easier to set up & administer. > > From what I've read about problems with MySQL, I think that if you > value your data, just don't use MySQL. At least 4 times, since 2001, > I've searched for PostgreSQL vs MySQL comparisons, and each time found > PostgreSQL to be superior in terms of performance, reliability, > robustness, and ease of use. > > There is a definite trend of people moving from MySQL to PostgreSQL, > and its not just because of Oracle (MySQL diehards are moving to > MariaDB). > > I have a lot more confidence in PostgreSQL, than MySQL/MariaDB. > > > Cheers, > Gavin > > > P.S. Don't top post! As commenting at the bottom, is the norm for > PostgreSQL mailing lists. > > Main: +44 (0)1376 503500 | Fax: +44 (0)1376 503550 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | England Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here >><https://www.manifest.co.uk/legal/> for further information.
Re: editable spreadsheet style interface
On 30/10/2018 20:32, Martin Mueller wrote: I have used Aqua Data Studio for several years. Jetbrains recently released a similar product. Academic licensing is affordable (~ $200 a year) and very cheap if considered in terms of the time it saves you. *From: *David Gauthier *Date: *Tuesday, October 30, 2018 at 2:06 PM *To: *"pgsql-gene...@postgresql.org" *Subject: *editable spreadsheet style interface I think I know the answer to this one but I'll ask anyway... Is there a spreadsheet style interface to a PG DB where users can... - lock records - edit records - submit changes (transaction) Is there any after-market tool for PG that does something like this ? Have a look here https://www.postgresql.org/download/product-categories/ under "Administration/Development". I'm using pgAdmin right now. Tim Clarke
Re: archive items not in correct section order
On 28/08/2018 21:51, Tom Lane wrote: Hm, could I trouble you for a self-contained test case? I tried to flesh it out as attached, but I'm not seeing any error with this. So there must be some other moving part ... regards, tom lane Tom, the materialized view in question was dependent on another materialized view (pointlessly). I've changed the underlying view to a simple one and the problem message "archive items not in correct section order" doesn't appear. Simply nesting the materialized views doesn't cause the issue though so I'm no further forward, alas: drop schema rating cascade; drop schema r cascade; create schema rating; create table rating.cy (f1 int, cid int, ye int); create schema r; set search_path = r; create table c(id int); create table f(id int, cid int, ye int, ytext text); create table i(gid int); create table o(id int); create table p(id int); create table wb(cid int, fid int, prop float8); create materialized view rating.d AS SELECT c.id as cid, f.id as fid, COALESCE(( SELECT sum( CASE WHEN i.gid = 3 THEN 1 ELSE 0 END)::double precision / count(p.id)::double precision AS c FROM o, p, i WHERE o.id = c.id AND f.id = p.id AND o.id = i.gid)) AS fp FROM rating.cy, c, f WHERE c.id = f.id AND f.id = cy.cid AND f.ye = cy.ye GROUP BY c.id, f.id; CREATE materialized VIEW rating.b AS SELECT d.cid, f.ytext, min(coalesce( (select case when wb.prop >= 0.333 then 4 when wb.prop >= 0.25 then 3 when wb.prop >= 0.15 then 2 when wb.prop >= 0.1 then 1 else 0 end FROM r.wb where wb.cid = d.cid and wb.fid = f.id), 0)) as score FROM rating.cy, rating.d, f WHERE d.cid = f.cid AND f.cid = cy.cid AND f.ye = cy.ye GROUP BY 1, 2 LIMIT 1; Tim Clarke
Re: PostgreSQL intenal scheduler?
On 05/09/18 16:06, Thiemo Kellner wrote: > > Hi all > > I am designing a framework for historisation implementation (SCD). One > feature I would like to provide is a table in that the actual state of > an entity is put and if this is complete, this history table is > "updated": > > - > ==> | ENTITY_ACT | ==> | ENTITY_HIST | > - > > I plan to use instead-of-triggers on the hist table that read the > actual table and perfoms all necessary inserts und updates on the > history table. If I want the termination of a record version (actually > the record of a specific business key with a specific payload) to get > propagated up and/or down referential integrities (no overlapping > validities) I have to make sure that only one of those processes is > modifying a table. I was thinking of a scheduler queue where the > trigger would put a process request and PostgreSQL would work through. > Is there a scheduler within PostgreSQL? I read the documentation and > searched the web but could not find a hint. But before going another > road or implementing something myself, I ask. Maybe this design is no > good at all. > > Kind regards > > Thiemo > > Why not just call your "do a scheduled run" code from cron? Tim Clarke
Re: archive items not in correct section order
On 27/08/18 15:22, Tom Lane wrote: >> That's not supposed to happen. Can you create a test case, by any chance? >> Presumably, it's triggered by some database schema change you made since >> the last successful dump. >> >> regards, tom lane >> It wasn't the cross-tab/pivot, it was this materialized view: CREATE materialized VIEW r.b AS SELECT c.id, f.ytext, min(coalesce( (select case when wb.prop >= 0.333 then 4 when wb.prop >= 0.25 then 3 when wb.prop >= 0.15 then 2 when wb.prop >= 0.1 then 1 else 0 end FROM r.wb where wb.cid = c.id and wb.fid = f.id), 0)) as score FROM rating.cy, c, f WHERE c.id = f.cid AND f.cid = cy.cid AND f.ye = cy.ye GROUP BY 1, 2 LIMIT 1; I've trimmed it down to even just one row and it still causes the warning "pg_dump: [archiver] WARNING: archive items not in correct section order" It yields this data: id | ytext | score ---+------+--- 5 | 1996 | 0 -- Tim Clarke
Re: archive items not in correct section order
On 27/08/18 15:22, Tom Lane wrote: > Tim Clarke writes: >> Last night for the first time our pg_dump backup threw this error for >> the first time. Repeated runs this morning consistently throw it too. > That's not supposed to happen. Can you create a test case, by any chance? > Presumably, it's triggered by some database schema change you made since > the last successful dump. > > regards, tom lane In answer to Adrian's question, we run: pg_dump -U (user) -C (database) then we pipe that out to gpg. pg_dump still throws the error without the pipe. I'm cutting down to find as brief a test case as possible at the moment; current hot favourite is a materialised view that's a crosstab using the functions from here https://www.postgresql.org/docs/10/static/tablefunc.html -- Tim Clarke
archive items not in correct section order
Last night for the first time our pg_dump backup threw this error for the first time. Repeated runs this morning consistently throw it too. I can see the error in the Postgres source https://doxygen.postgresql.org/pg__backup__archiver_8c_source.html but I can't find any specifics about the cause or cure? Environment: Centos 6.10 kernel 2.6.32-754.2.1.el6.i686 postgresql10.i686 10.5-1PGDG.rhel6 @pgdg10 postgresql10-contrib.i686 10.5-1PGDG.rhel6 @pgdg10 postgresql10-devel.i686 10.5-1PGDG.rhel6 @pgdg10 postgresql10-libs.i686 10.5-1PGDG.rhel6 @pgdg10 postgresql10-odbc.i686 10.03.-1PGDG.rhel6 @pgdg10 postgresql10-server.i686 10.5-1PGDG.rhel6 @pgdg10 -- Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Main: +44 (0)1376 503500 | Fax: +44 (0)1376 503550 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | England Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva-analytics.info Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here >> <https://www.manifest.co.uk/legal/> for further information.
Re: how to install pgcrypto
The psql command is a client - an "interactive terminal". It provides a command-line version of pgAdmin. See here for other client applications: https://www.postgresql.org/docs/10/static/reference-client.html The "create extension..." command is one that you give to either psql or pgAdmin; they execute it against the current database. Tim Clarke On 03/08/18 09:53, Ze Victor Harry wrote: > hello again I have a small problem here can someone tell me briefly > how to do it? I am getting this error > > > When I give ant fresh_install it gives error PostgreSQL 'pgcrypto' > extension installed /up to date? False (not installed) Create > extension pgcrypto > > <https://stackoverflow.com/questions/45408745/when-i-give-ant-fresh-install-it-gives-error-postgresql-pgcrypto-extension-ins>. > > I have tried to look up for solutions and they all say I have to run a > command > |# Login to your ||"dspace"| |database as a superuser| > |psql --username=postgres dspace| > |# Enable the pgcrypto extension on ||this| |database| > |CREATE EXTENSION pgcrypto;| > but where I got confused is from where do I run these commands.is > <http://commands.is/> it from cmd? or in pgadmin query tool? I need > clearly articulated steps >
Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?
Ah thanks, I stand corected! :) Tim Clarke On 16/07/18 15:04, Josef Šimánek wrote: > It is licensed under MIT - https://github.com/Microsoft/vscode. > > 2018-07-16 13:09 GMT+02:00 Tim Clarke : >> -1 for VSC not being open source >> >> Tim Clarke >> >>
Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?
-1 for VSC not being open source Tim Clarke On 16/07/18 11:47, Dmitry Igrishin wrote: > > > пн, 16 июл. 2018 г. в 13:41, Tim Clarke > <mailto:tim.cla...@minerva-analytics.info>>: > > +1 for not re-inventing the wheel - building on Netbeans or the > Eclipse > project would save you heaps of time and effort and provide > cross-platform out of the box. I use Eclipse all the time. > > I agree and don't want to waste my time for reinventing the wheel. And > I'm also > considering Visual Studio Code as the base.
Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?
+1 for not re-inventing the wheel - building on Netbeans or the Eclipse project would save you heaps of time and effort and provide cross-platform out of the box. I use Eclipse all the time. Tim Clarke On 15/07/18 20:41, Chuck Davis wrote: > If you decide to proceed on this project there's no need to reinvent the > wheel. > > I use Netbeans for my development. it has quite a good facility for > working with databases and I use it regularly with Postgres. Since > Netbeans is now licensed under Apache 2 you might find useful code > there. Be assured it uses JDBC for access but JDBC is universally > available and the folks at Postgresql have done quite a nice job with > JDBC drivers. Of course, this already works on all platforms. The > implementation is basic but very useful: i.e. a good starting point. > > On Sun, Jul 15, 2018 at 12:08 PM, Dmitry Igrishin wrote: >> Thank you all for your responses! >> >> Okay, if I decide to start this project, the Linux platform will be >> supported. >> >> As I see it, pgspa (PostgreSQL Server Programming Assistant) should have >> both the command line interface, >> and the GUI for visualizing some aspects of the work. The tool should be >> friendly for your favorite editors (Emacs, >> VSCode etc) for easy integration to the daily workflow. (There is no plans >> to write feature rich text editor inside >> the IDE. It's more rational to provide the integration with the existing >> text editors for professional programmers.) >> What do you think?
Re: Introducing brand-new dbForge Studio for PostgreSQL, a powerful IDE for working with PostgreSQL databases
On 22/03/18 11:53, Steven Hirsch wrote: > On Thu, 22 Mar 2018, Devart wrote: > >> /Devart announced the first release of dbForge Studio for PostgreSQL >> that >> allows to manage and develop database objects in PostgreSQL. This new >> IDE >> offers many robust features like Code Completion, Object Explorer and >> Data >> Editor which help users create, develop and execute queries as well >> as edit >> and adjust the code to their requirements in a convenient and >> user-friendly >> interface./ > > (snip...) > > Windows only. Wake me up when you have a Mac or Linux version. > > +1 Tim Clarke smime.p7s Description: S/MIME Cryptographic Signature
Re: Rationale for PUBLIC having CREATE and USAGE privileges on the schema "public" by default
On 17/02/18 20:48, Olegs Jeremejevs wrote: > Okay, in other words, there's no way to completely defend oneself from > DoS attacks which require having a session? If so, is there a scenario > where some bad actor can create a new user for themselves (to connect > to the database with), and not be able to do anything more damaging > than that? For example, if I can do an SQL injection, then I can do > something more clever than running a CREATE ROLE. And if not, then > there's no point in worrying about privileges in a single-tenant > database? Beyond human error safeguards. > > Olegs How about execution limits, Olegs? Tim Clarke smime.p7s Description: S/MIME Cryptographic Signature
Re: Best non-networked front end for postgresql
On 21/01/18 19:05, Vincenzo Romano wrote: > 2018-01-21 19:57 GMT+01:00 Sherman Willden <operasopra...@gmail.com>: >> Name: Sherman >> >> Single laptop: Compaq 6710b >> >> Operating System: Ubuntu 17.10 >> >> Postgresql: 9.6 >> >> Used for: Just me and my home database >> >> Seeking advice: Best non-networked front-end >> >> Considerations: I am retired and want to create my own database and database >> captures. I have experience with PERL >> >> Basic question 1: Which non-networked front-end would work best for me? >> >> Basic question 2: I am seriously considering HTML fields to capture and >> process the information. So to connect with postgresql what do I need to >> know? Do I need to know javascript, python, and other languages? How is PERL >> for something like this? >> >> I am entering the below values by hand into a functional database. I thought >> that I would create some type of front-end to enter the values and then have >> the front-end enter the values into the postgresql database. >> 01). visit_date >> 02). start_time >> 03). end_time >> 04). venue (This is the casino name) >> 05). city >> 06). state >> 07). limit (4/8 20/40 etc) >> 08). game (7-card-stud etc) >> 09). variant (fixed-limit no-limit etc) >> 10). high-low (mixed-high-low high-only etc) >> 11). buy_in >> 12). cash_out >> >> Thank you; >> >> Sherman > PGAdmin is among the best tools to manage Postgres. > > https://www.pgadmin.org/ > > As far as a front-end program, perl can be used. > As well as a number of other languages ranging from C, C++, Java, PHP. > Almost all languages have a "module" to interact with Postgres databases. > The best one is IMHO the one you know the best. > > P.S. > The differences between a local Unix socket and a TCP one are rather > subtle from your point of view. > +1 for pgadmin - indeed why bother with anything else for one flat table? How many rows of data do you envisage? -- Tim Clarke smime.p7s Description: S/MIME Cryptographic Signature
Re: PostgreSQL suitable?
PG10 is happily in production - in active use here :) Tim Clarke IT Director Manifest Tel: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 | Main: +44 (0)1376 503500 | Fax: +44 (0)1376 503550 Blog: https://blog.manifest.co.uk/ <https://www.manifest.co.uk/> | Web: https://www.manifest.co.uk/ 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | England Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@manifest.info Legal: Manifest is the trading name of: Manifest Information Services Ltd: Registered in England Number 3401145 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here >> <https://www.manifest.co.uk/legal/> for further information. On 19/12/17 14:50, Vincenzo Romano wrote: > I've not tested PG10. But it's not released for production yet! > > Il 19 dic 2017 15:48, "Andreas Kretschmer" <andr...@a-kretschmer.de > <mailto:andr...@a-kretschmer.de>> ha scritto: > > > > Am 19.12.2017 um 15:07 schrieb Kellner Thiemo: > > Hi > > We are developing a data warehouse of which the integration > layer will start with over 100 TB of data. There are not many > entities though we probably can partition and foremost we > should use inheritance for the lab results. I just was > wondering if PostgreSQL was able to cope with. In case it > depends on the modelling kind, we have not yet decided between > classic erd, anchor modelling and data vault. > > Does someone have experience with such a set up? > > Kind regards > > Thiemo > > > > depends at least on the data and the workload. pg10 contains > better solutions for table-partitioning, up to 1000 (maybe more) > child-tables arn't that problem. > We have customers in that range. > > > Regards, Andreas > > -- > 2ndQuadrant - The PostgreSQL Support Company. > www.2ndQuadrant.com <http://www.2ndQuadrant.com> > > smime.p7s Description: S/MIME Cryptographic Signature
