Re: Christopher Browne
On Wed, Nov 4, 2020 at 6:29 PM Steve Singer wrote: > > > It is with much sadness that I am letting the community know that Chris > Browne passed away recently. > > Chris had been a long time community member and was active on various > Postgresql mailing lists. He was a member of the funds committee, the PgCon > program committee and served on the board of the PostgreSQL Community > Association of Canada. Chris was a maintainer of the Slony > replication system and worked on various PG related tools. > > I worked with Chris for over 10 years and anytime someone had a problem he > would jump at the chance to help and propose solutions. He > always had time to listen to your problem and offer ideas or explain how > something worked. > > I will miss Chris > Chris is one of the folks whom I got to meet online very early in my time with the project, who was there in Toronto at the first developer's conference [1], and was one of the folks I always looked forward to seeing in Ottawa, even if it was just to quickly catch-up. I suspect his contributions to the project are understated, but a lot of time, troubleshooting, and tutoring came from Chris to many others of us around Slony (back when Slony was the main game in town) and around plpgsql. Of course, he knew his way around an OS just as well as he knew Postgres. I think my fondest memory of Chris was one, many years back, when I just happened to be in Toronto for non-postgres related business, and we coordinated to meet up for a quick drink and to catch-up. He was kind enough to offer me an invitation to a private "computer users" dinner that he often frequented at a hole-in-the-wall Hungarian place. I'm always game for a bit of adventure and I'm so glad I was because ended up being a truly special night, learning much more about Chris away from the database stuff, with a bunch of great food (still don't know half of what it was), and the realization that this small group of friends included several luminaries in the computer science field, (as one example Henry Spencer, who wrote the "regex" software library for regular expressions), who I can't imagine ever having the opportunity to meet otherwise. Most of us are not nearly as open and as kind as he was, and he will indeed be missed. [1] https://ic.pics.livejournal.com/obartunov/24248903/36575/36575_original.jpg, he is the one in the back, holding up the sign. Robert Treat https://xzilla.net
Re: LwLocks contention
On Mon, Apr 25, 2022 at 10:33 AM Michael Lewis wrote: > > On Thu, Apr 21, 2022 at 6:17 AM Chris Bisnett wrote: >> >> We're working to update our application so that we can >> take advantage of the pruning. Are you also using native partitioning? > > > No partitioned tables at all, but we do have 1800 tables and some very > complex functions, some trigger insanity, huge number of indexes, etc etc. > > There are lots of things to fix, but I just do not yet have a good sense of > the most important thing to address right now to reduce the odds of this type > of traffic jam occurring again. I very much appreciate you sharing your > experience. If I could reliably reproduce the issue or knew what data points > to start collecting going forward, that would at least give me something to > go on, but it feels like I am just waiting for it to happen again and hope > that some bit of information makes itself known that time. > > Perhaps I should have posted this to the performance list instead of general. In my experience lwlock contention (especially around buffer_mapping) is more about concurrent write activity than any particular number of tables/partitions. The first recommendation I would have is to install pg_buffercache and see if you can capture some snapshots of what the buffer cache looks like, especially looking for pinning_backends. I'd also spend some time capturing pg_stat_activity output to see what relations are in play for the queries that are waiting on said lwlocks (especially trying to map write queries to tables/indexes). Robert Treat https://xzilla.net
Re: Asking for existence of a GUI frame work similar to Oracle APEX for PostgreSQL
On Fri, Oct 21, 2022 at 1:49 PM Dionisis Kontominas wrote: > It depends on whether you keep the GUI in Oracle APEX. If so then yes, as > APEX and tomcat and ORDS are free and need only the basic license for the > Oracle DB. Otherwise if a free/low cost low code tool/framework is found then > they can be abolished. > It's been several years since I have looked at APEX, but my recollection was that it relied heavily on Oracle packages and functions, and the few attempts I saw towards porting any of that to Postgres were abandoned pretty quickly. This generally resulted in most migrations becoming full-on rewrites into a new platform. Once you've given that up, its most a matter of finding a new platform that you want to work with... most of the companies I worked with moved to things like django or rails, but I suspect something like budibase and/or similar alternatives might be more what you are angling towards. Hope that helps. Robert Treat https://xzilla.net
Re: CVE-2019-9193 about COPY FROM/TO PROGRAM
On Fri, Apr 5, 2019 at 8:35 AM Jeff Janes wrote: > On Tue, Apr 2, 2019 at 11:31 AM Andres Freund wrote: >> On 2019-04-02 07:35:02 -0500, Brad Nicholson wrote: >> >> > A blog post would be nice, but it seems to me have something about this >> > clearly in the manual would be best, assuming it's not there already. I >> > took a quick look, and couldn't find anything. >> >> https://www.postgresql.org/docs/devel/sql-copy.html >> >> "Note that the command is invoked by the shell, so if you need to pass >> any arguments to shell command that come from an untrusted source, you >> must be careful to strip or escape any special characters that might >> have a special meaning for the shell. For security reasons, it is best >> to use a fixed command string, or at least avoid passing any user input >> in it." >> >> "Similarly, the command specified with PROGRAM is executed directly by >> the server, not by the client application, must be executable by the >> PostgreSQL user. COPY naming a file or command is only allowed to >> database superusers or users who are granted one of the default roles >> pg_read_server_files, pg_write_server_files, or >> pg_execute_server_program, since it allows reading or writing any file >> or running a program that the server has privileges to access." >> >> Those seem reasonable to me? > > > Yes, but I think that the use of the phrase "default roles" here is > unfortunate. I know it means that the role exists by default, but it is easy > to read that to mean they are granted by default. They should probably be > called something like 'built-in roles' or 'system roles'. > > And even with the understanding that we are referring to existence, not grant > status, "default roles" is still not really correct. If it exists by default, > that means I can make it not exist by taking action. But these roles cannot > be dropped. > > We don't have 'default functions' or 'default types' in the user-facing > documentation. We shouldn't call these 'default roles'. > As someone who likes to break systems in interesting ways, I do find it interesting that you can actually remove all superuser roles and/or the superuser bit from all roles (not that I would recommend that to anyone) but that these roles cannot be removed without some serious heavy lifting. Given that, I think I would tend to agree, describing them more consistently as "system roles" is probably warranted. Robert Treat https://xzilla.net https://credativ.com
Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?
On Thu, Mar 7, 2024 at 11:26 AM Pavel Stehule wrote: > čt 7. 3. 2024 v 16:59 odesílatel Christophe Pettus napsal: >> > On Mar 7, 2024, at 06:56, Achilleas Mantzios - cloud >> > wrote: >> > So, I ask, have there been any efforts to bring PL/PGSQL to the terminal? >> >> Strictly speaking, of course, you can use PL/pgSQL from the terminal >> already: just use psql, connect to the database, and create and run >> functions and procedures as much as you like. >> >> If the question is, "Have there been any efforts to implement a PL/pgSQL >> interpreter without PostgreSQL?", that's a different and much more complex >> problem. PL/pgSQL uses the PostgreSQL query execution machinery to run >> pretty much anything that is not a control structure, and the language is >> very focused on interacting with the database. I doubt it would be worth >> anyone's time to try to build some kind of minimal framework that implements >> the SPI to allow PL/pgSQL to operate without PostgreSQL. > > > yes > > plpgsql cannot exist without Postgres. PL/pgSQL is strongly reduced > interpreted Ada language. The gcc compiler supports Ada language. > > I found https://bush.sourceforge.net/bushref.html - it is interpret with Ada > syntax, but it is better to learn Python - it is easy - with a pretty big > library. > > free pascal https://www.freepascal.org/ is good compiler and you can write > terminal applications too - with Turbo Vision > Of course there's a certain amount of personal preference with all this stuff. I started with basic and really liked it, and then had to learn pascal and hated it so much that I decided to eschew programming for years. If you are just trying to learn for fun, I see no reason why SQL, paired with data in a database, wouldn't be worth spending time on. Once you're comfortable with that, I like ruby on the command line and it interacts nicely with databases, and also works well within the rails console. That said, my son liked lua when he was a kid, so yeah, there's lots of options, even if plpgsql on the command line isn't strictly one of them. Robert Treat https://xzilla.net
Re: Seeing high query planning time on Azure Postgres Single Server version 11.
It'd be worth checking that your default_statistics_target isn't set to anything wild, but beyond that, it'd be interesting to look at the output of vacuum verbose on some of the system catalogs as istm you might have catalog bloat. I should also mention that you're running a non-longer-supported version of Postgres (v11) and not even the latest release of said EOL version. And if I am not mistaken, "Azure Postgres single server version" is also deprecated, so you should really focus on getting upgraded to something more modern. Robert Treat https://xzilla.net On Sat, Mar 9, 2024 at 8:12 AM hassan rafi wrote: > > Postgres version: PostgreSQL 11.18, compiled by Visual C++ build 1800, 64-bit > relname > |relpages|reltuples|relallvisible|relkind|relnatts|relhassubclass|reloptions|pg_table_size| > -++-+-+---++--+--+-+ > store_seller_products|16007942|843460096| 797033|r | 16|false > |NULL | 131980795904| > > > relname > |relpages|reltuples|relallvisible|relkind|relnatts|relhassubclass|reloptions|pg_table_size| > ++-+-+---++--+--+-+ > products_inventory_delta| 2847202|259351648| 1606201|r | > 4|false |NULL | 23330758656| > > Peak load (write): 3000 TPS (mostly updates). > Peak load (read): 800 TPS. > > > On Sat, Mar 9, 2024 at 5:58 PM Ron Johnson wrote: >> >> On Sat, Mar 9, 2024 at 7:18 AM hassan rafi wrote: >>> >>> Hi team, >>> >>> We are seeing unusually high query planning times on our Postgres server. I >>> am attaching a few query plans. >> >> >> Postgresql version number? >> Rows in the tables? >> System load?
Re: Question about PostgreSQL upgrade from version 12 to version 15
On Thu, Mar 21, 2024 at 7:48 AM Alvaro Herrera wrote: > On 2024-Mar-21, Joseph Kennedy wrote: > > I'm planning to upgrade my PostgreSQL database from version 12 to > > version 15 using pg_upgrade. After completing the upgrade process, I'm > > curious to know whether it's necessary to reindex the database. > > > > Could anyone please clarify whether reindexing is required after > > completing the upgrade process from PostgreSQL version 12 to version > > 15 using pg_upgrade? > > A reindex(*) is necessary for indexes on textual columns(**), and only > if you're also changing the underlying OS version(***) such that the > collation behavior changes. If you're keeping the database on the same > OS version, there's no need to reindex anything. > > (*) More than reindex actually: you may need to refresh materialized > views and consider carefully any partition bounds you may have, if you > have any partition keys that include textual columns. Even worse: if > you have FDWs on a Postgres server that queries a table from another > Postgres server with different collation libraries, it could bit you > there too. > > (**) textual column in this case means anything that is affected by > collation changes; typically that's things like varchar, text, citext, > etc, for which a collation other than "C" is explicit or implied. You > don't need to do anything for indexes on numbers, dates, geometries, > etc, nor for textual columns where the index is defined with the C > collation. > > (***) the underlying C library changes collation rules rather frequently > (especially glibc), because the developers of said libraries don't > consider that this has any important, permanent impact (but it does > impact indexes for Postgres). Most such changes are innocuous, but from > time to time they make changes that wreak havoc. If you're using ICU > collations with your Postgres 12 databases, you may also be affected if > you upgrade from one ICU version to another. > > > Joe Conway gave a very good presentation on this topic recently: > https://www.postgresql.eu/events/fosdem2024/schedule/session/5127-collation-challenges-sorting-it-out/ > As a bonus, if you do decide to reindex, you'll also benefit from the index deduplication work that was introduced in v13, which should help reduce disk space and make queries a little faster. Robert Treat https://xzilla.net
Re: PostgreSQL as advanced job queuing system
On Fri, Mar 22, 2024 at 8:05 AM Dominique Devienne wrote: > > On Fri, Mar 22, 2024 at 12:58 PM ushi wrote: >> >> i am playing with the idea to implement a job queuing system using >> PostgreSQL. > > > FYI, two bookmarks I have on this subject, that I plan to revisit eventually: > * https://news.ycombinator.com/item?id=20020501 > * > https://www.enterprisedb.com/blog/listening-postgres-how-listen-and-notify-syntax-promote-high-availability-application-layer > > If others have links to good articles on this subject, or good input to give > in this thread, I'd be interested. Thanks, --DD This is a well worn topic within the postgres community, with a number of different implementations, but a couple of links that are probably worth looking at would be: - https://wiki.postgresql.org/wiki/PGQ_Tutorial, probably the first queue system that gained wide adoption - https://brandur.org/river, a new queue system based on postgres/go, which also has a link to an article about why the authors had ditched postgres based queueing in favor of redis some years before which is worth a read to understand some of the issues that Postgres has as the basis for a queue system. And yeah, I suspect this may become a hot topic again now that Redis is moving away from open source: https://redis.com/blog/redis-adopts-dual-source-available-licensing/ Robert Treat https://xzilla.net
Re: Planet Postgres and the curse of AI
On Tue, Aug 20, 2024 at 8:33 AM Greg Sabino Mullane wrote: > > On Tue, Jul 23, 2024 at 12:45 PM Avinash Vallarapu > wrote: >> >> However, I do agree with Lawrence that it is impossible to prove whether it >> is written by AI or a human. >> AI can make mistakes and it might mistakenly point out that a blog is >> written by AI (which I know is difficult to implement). > > > Right - I am not interested in "proving" things, but I think a policy to > discourage overuse of AI is warranted. > >> People may also use AI generated Images in their blogs, and they may be >> meaningful for their article. >> Is it only the content or also the images ? It might get too complicated >> while implementing some rules. > > > Only the content, the images are perfectly fine. Even expected, these days. > >> >> Ultimately, Humans do make mistakes and we shouldn't discourage people >> assuming it is AI that made that mistake. > > > Humans make mistakes. AI confidently hallucinates. > I think this is a key point, and one that we could focus on for purposes of discouragement. Ie. "Blogs that are found to repeatedly post incorrect information and/or AI style hallucinations may be restricted from contributing to the planet postgres feed. This will be determined on a case by case basis." While it is likely impossible to come up with a set of rules that will satisfy some of the more legalistic folks among us, this would be a simple warning that would at least encourage folks to make sure they aren't posting bad information and leave a door open for enforcement if needed. And yes, this assumes that the folks running planet will enforce if needed, though I don't think it requires heavy policing at this point. Robert Treat https://xzilla.net
Re: impact of version upgrade on fts
On Sun, Apr 25, 2021 at 11:27 AM Adrian Klaver wrote: > > On 4/25/21 5:28 AM, Malik Rumi wrote: > > Greetings. > > > > I am about to do a long overdue upgrade to the latest version, which I > > believe is 13.2. However, I have full text search in my current install > > (9.4) and I am wondering if there are any special provisions I need to > > take to make sure that is not lost in the transition? If this would be > > true for any other packages, please advise and or point me to the place > > in the docs where I can read up on this. I looked and did not see > > anything, which just proves I didn't know what to look for. Thanks. > > FTS used to be a separate extension(tsearch2) before version Postgres > version 8.3. Since then it has been integrated into the core code, so it > would not be lost. That being said it would be advisable to read the > release notes for 9.5 --> 13 to see what changed in the core code. > You should also check the release notes / process of any "non-core" extensions you might be using, for example PostGIS has had a number of changes and you'll need to upgrade the extension itself to work in the new version of Postgres. Specifics around that will also depend on how you instead to run your upgrade process. Robert Treat https://xzilla.net
