Re: Named Prepared statement problems and possible solutions

On 6/8/23 15:57, Dave Cramer wrote: Apparently this is coming in pgbouncer Support of prepared statements by knizhnik · Pull Request #845 · pgbouncer/pgbouncer (github.com) I am quite interested in that patch. Considering how pgbouncer works

Re: Named Prepared statement problems and possible solutions

On 6/8/23 13:31, Dave Cramer wrote: On Thu, 8 Jun 2023 at 11:22, Konstantin Knizhnik > wrote: So it will be responsibility of client to remember text of prepared query to be able to resend it when statement doesn't exists at server? IMHO very strange

Re: Named Prepared statement problems and possible solutions

On 6/8/23 10:56, Dave Cramer wrote: On Thu, 8 Jun 2023 at 10:31, Jan Wieck <mailto:j...@wi3ck.info>> wrote: On 6/8/23 09:53, Jan Wieck wrote: > On 6/8/23 09:21, Dave Cramer wrote: > The server doesn't know about all the clients of the pooler, does it? It

Re: Named Prepared statement problems and possible solutions

On 6/8/23 09:53, Jan Wieck wrote: On 6/8/23 09:21, Dave Cramer wrote: The server doesn't know about all the clients of the pooler, does it? It has no way of telling if/when a client disconnects from the pooler. Another problem that complicates doing it in the server is that the information

Re: Named Prepared statement problems and possible solutions

On 6/8/23 09:21, Dave Cramer wrote: On Thu, Jun 8, 2023 at 8:43 AM Jan Wieck <mailto:j...@wi3ck.info>> wrote: On 6/8/23 02:15, Konstantin Knizhnik wrote: > There is a PR with support of prepared statement support to pgbouncer: > https://github.com/pgbouncer

Re: Named Prepared statement problems and possible solutions

On 6/8/23 02:15, Konstantin Knizhnik wrote: There is a PR with support of prepared statement support to pgbouncer: https://github.com/pgbouncer/pgbouncer/pull/845 any feedback, reviews and suggestions are welcome. I was about to say that the support would have to come from the pooler as it

Re: Add 64-bit XIDs into PostgreSQL 15

On 11/29/22 09:46, Bruce Momjian wrote: As far as I know, all our freeze values are focused on avoiding XID wraparound. If XID wraparound is no longer an issue, we might find that our freeze limits can be much higher than they are now. I'd be careful in that direction as the values together

Re: PL/pgSQL cursors should get generated portal names by default

My comments were in no way meant as an argument for or against the change itself. Only to clearly document the side effect it will have. Regards, Jan On 11/7/22 11:57, Kirk Wolak wrote: On Mon, Nov 7, 2022 at 11:10 AM Jan Wieck <mailto:j...@wi3ck.info>> wrote: On 11/4/22 1

Re: PL/pgSQL cursors should get generated portal names by default

On 11/4/22 19:46, Tom Lane wrote: Jan Wieck writes: I need to do some testing on this. I seem to recall that the naming was originally done because a reference cursor is basically a named cursor that can be handed around between functions and even the top SQL level of the application

Re: PL/pgSQL cursors should get generated portal names by default

On 11/4/22 03:22, Pavel Stehule wrote: Hi st 2. 11. 2022 v 0:39 odesílatel Tom Lane > napsal: There's a complaint at [1] about how you can't re-use the same cursor variable name within a routine called from another routine that's already using that

Re: Limiting memory allocation

On 5/20/22 19:08, Tomas Vondra wrote: Well, we already have the memory-accounting built into the memory context infrastructure. It kinda does the same thing as the malloc() wrapper, except that it does not publish the information anywhere and it's per-context (so we have to walk the context

Re: Limiting memory allocation

On 5/18/22 11:11, Alvaro Herrera wrote: On 2022-May-18, Jan Wieck wrote: Maybe I'm missing something, but what is it that you would actually consider a solution? Knowing your current memory consumption doesn't make the need for allocating some right now go away. What do you envision

Re: Limiting memory allocation

On 5/17/22 18:30, Stephen Frost wrote: Greetings, On Tue, May 17, 2022 at 18:12 Tom Lane <mailto:t...@sss.pgh.pa.us>> wrote: Jan Wieck mailto:j...@wi3ck.info>> writes: > On 5/17/22 15:42, Stephen Frost wrote: >> Thoughts? > Using cgroups one c

Re: Limiting memory allocation

On 5/17/22 15:42, Stephen Frost wrote: Thoughts? Yes. The main and foremost problem is a server that is used for multiple services and they behave differently when it comes to memory allocation. One service just allocates like we have petabytes of RAM, then uses little of it, while another

Re: Change the csv log to 'key:value' to facilitate the user to understanding and processing of logs

On 3/15/22 10:12, Andrew Dunstan wrote: On 3/15/22 09:30, hubert depesz lubaczewski wrote: On Tue, Mar 15, 2022 at 09:31:19AM +0800, lupeng wrote: Dear Hackers When I audit the Postgresql database recently, I found that after configuring the log type as csv, the output log content is as

Re: should we enable log_checkpoints out of the box?

n logic related. An FK violation is app-logic, running checkpoints too often is a resource problem. Allow the DBA to create some filter based on combinations of them and it should work well enough. Regards, Jan Wieck

Re: should we enable log_checkpoints out of the box?

On 11/3/21 09:09, Robert Haas wrote: For better or for worse, the distinction between ERROR, FATAL, and PANIC is entirely based on what we do after printing the message, and NOT on how serious the message is. THAT is a real problem with our error handling and logging system. Often using

Re: should we enable log_checkpoints out of the box?

sit there waiting for the next time it happens." Regards -- Jan Wieck

Re: should we enable log_checkpoints out of the box?

would ever generate if it meant that they could actually find problems when and if they have them. I fully agree. +1 -- Jan Wieck

Re: should we enable log_checkpoints out of the box?

is zero hint in the logs as to why. -- Jan Wieck

Re: PostgreSQL High Precision Support Extension.

rm) going to contribute to this effort? Regards, Jan -- Jan Wieck

Re: pg_upgrade does not upgrade pg_stat_statements properly

to have DO blocks that execute server version dependent sections in the CREATE EXTENSION scripts. However similar the code may be, it is intended for different server versions, so it is not the same version of the extension. Regards, Jan -- Jan Wieck

Re: pg_upgrade does not upgrade pg_stat_statements properly

an otherwise useless API. Now if that API can be useful for other cases or if there are other extensions with similar problems that would be different story. I haven't worked on it for a while, but I think pl_profiler does the same thing, so you are not alone. Jan -- Jan Wieck

Re: pg_upgrade does not upgrade pg_stat_statements properly

that don't need anything done for those upgrades (which I believe is the majority of them) would have to provide empty SQL files. Not necessarily a bad thing, as it actually documents "yes, the extension developer checked this and there is nothing to do here." Regards, Jan -- Jan Wieck

Re: pg_upgrade does not upgrade pg_stat_statements properly

On 7/29/21 2:04 PM, Julien Rouhaud wrote: On Thu, Jul 29, 2021 at 11:46:12AM -0400, Jan Wieck wrote: > I don't have a ready example of such an extension, but if we ever would > convert the backend parts of Slony into an extension, it would be one. FWIW I have an e

Re: pg_upgrade does not upgrade pg_stat_statements properly

. How exactly do you envision that we, the PostgreSQL project, make sure that extension developers provide those mechanisms in the future? Regards, Jan -- Jan Wieck

Re: pg_upgrade does not upgrade pg_stat_statements properly

... CREATE EXTENSION. There are extensions that create objects, like user defined data types and functions, that will be referenced by end user objects like tables and views. Regards, Jan -- Jan Wieck

Re: pg_upgrade does not upgrade pg_stat_statements properly

, but if we ever would convert the backend parts of Slony into an extension, it would be one. Regards, Jan -- Jan Wieck

Re: pg_upgrade does not upgrade pg_stat_statements properly

. Knowing that (the extension is outdated) exactly how? Can you give us an example query, maybe a few SQL snippets explaining what exactly you are talking about? Because at this point you completely lost me. Sorry, Jan -- Jan Wieck

Re: pg_upgrade does not upgrade pg_stat_statements properly

make that exception for an external extension like PostGIS. But I don't think it is valid for one distributed in sync with the core system in the contrib package, like pg_stat_statements. Which happens to be the one named in the subject line of this entire discussion. Regards, Jan -- Jan Wieck

Re: pg_upgrade does not upgrade pg_stat_statements properly

e at once. Including, but not limited to, catalog schema changes, SQL language changes, extension behavior changes and utility command behavior changes. On that principle, you should advocate against using pg_upgrade in the first place. Regards, Jan -- Jan Wieck

Re: pg_upgrade does not upgrade pg_stat_statements properly

On 7/15/21 12:31 PM, Robert Eckhardt wrote:  I don’t know if this is a terrible flaw in pg_upgrade, it is a terrible flaw in the overall Postgres experience. +1 (that is the actual problem here) -- Jan Wieck

Re: pg_upgrade does not upgrade pg_stat_statements properly

experience your opinion is based on? Regards, Jan -- Jan Wieck

Re: Always bump PG_CONTROL_VERSION?

at the beginning of the development cycle. And we make sure this is done how? Regards, Jan -- Jan Wieck Postgres User since 1994

Re: Always bump PG_CONTROL_VERSION?

NOT to bump the version? Thanks, Jan -- Jan Wieck Postgres User since 1994

Re: SELECT INTO deprecation

don't think it should be our business to throw wrenches into their existing applications. Regards, Jan -- Jan Wieck Principle Database Engineer Amazon Web Services

Re: pg_upgrade failing for 200+ million Large Objects

On 3/24/21 12:04 PM, Jan Wieck wrote: In any case I changed the options so that they behave the same way, the existing -o and -O (for old/new postmaster options) work. I don't think it would be wise to have option forwarding work differently between options for postmaster and options for pg_dump

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 4:55 PM, Tom Lane wrote: Jan Wieck writes: Have we even reached a consensus yet on that doing it the way, my patch is proposing, is the right way to go? Like that emitting BLOB TOC entries into SECTION_DATA when in binary upgrade mode is a good thing? Or that bunching all the SQL

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 3:35 PM, Tom Lane wrote: Jan Wieck writes: The problem here is that pg_upgrade itself is invoking a shell again. It is not assembling an array of arguments to pass into exec*(). I'd be a happy camper if it did the latter. But as things are we'd have to add full shell escapeing

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 2:59 PM, Tom Lane wrote: Jan Wieck writes: On 3/23/21 2:35 PM, Tom Lane wrote: If you're passing multiple options, that is --pg-dump-options "--foo=x --bar=y" it seems just horribly fragile. Lose the double quotes and suddenly --bar is a separate option to pg_upgr

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 2:35 PM, Tom Lane wrote: Jan Wieck writes: So the question remains, how do we name this? --pg-dump-options "" --pg-restore-options "" If you're passing multiple options, that is --pg-dump-options "--foo=x --bar=y" it seem

Re: pg_upgrade failing for 200+ million Large Objects

be something like "--whatever[=NUM] [...]" would be something unambiguous. Regards, Jan -- Jan Wieck Principle Database Engineer Amazon Web Services

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 10:56 AM, Bruce Momjian wrote: On Tue, Mar 23, 2021 at 08:51:32AM -0400, Jan Wieck wrote: On 3/22/21 7:18 PM, Jan Wieck wrote: > On 3/22/21 5:36 PM, Zhihong Yu wrote: > > Hi, > > > > w.r.t. pg_upgrade_improvements.v2.diff. > > >

Re: pg_upgrade failing for 200+ million Large Objects

On 3/22/21 7:18 PM, Jan Wieck wrote: On 3/22/21 5:36 PM, Zhihong Yu wrote: Hi, w.r.t. pg_upgrade_improvements.v2.diff. +       blobBatchCount = 0; +       blobInXact = false; The count and bool flag are always reset in tandem. It seems variable blobInXact is not needed. You are right

Re: pg_upgrade failing for 200+ million Large Objects

-- Jan Wieck Principle Database Engineer Amazon Web Services

Re: Fix pg_upgrade to preserve datdba

On 3/21/21 3:56 PM, Tom Lane wrote: Jan Wieck writes: So let's focus on the actual problem of running out of XIDs and memory while doing the upgrade involving millions of small large objects. Right. So as far as --single-transaction vs. --create goes, that's mostly a definitional problem

Re: Fix pg_upgrade to preserve datdba

with this. But that has nothing to do with the actual topic. So let's focus on the actual problem of running out of XIDs and memory while doing the upgrade involving millions of small large objects. Regards, Jan -- Jan Wieck Principle Database Engineer Amazon Web Services

Re: Fix pg_upgrade to preserve datdba

On 3/21/21 1:15 PM, Jan Wieck wrote: On 3/21/21 12:57 PM, Tom Lane wrote: Jan Wieck writes: On 3/20/21 12:39 AM, Jan Wieck wrote: On the way pg_upgrade also mangles the pg_database.datdba (all databases are owned by postgres after an upgrade; will submit a separate patch for that as I

Re: Fix pg_upgrade to preserve datdba

On 3/21/21 12:57 PM, Tom Lane wrote: Jan Wieck writes: On 3/20/21 12:39 AM, Jan Wieck wrote: On the way pg_upgrade also mangles the pg_database.datdba (all databases are owned by postgres after an upgrade; will submit a separate patch for that as I consider that a bug by itself). Patch

Re: pg_upgrade failing for 200+ million Large Objects

inting to large objects and the JDBC driver is mapping those for streaming operations? Yeah, that would explain a lot. Thanks, Jan -- Jan Wieck Principle Database Engineer Amazon Web Services

Fix pg_upgrade to preserve datdba (was: Re: pg_upgrade failing for 200+ million Large Objects)

On 3/20/21 12:39 AM, Jan Wieck wrote: On the way pg_upgrade also mangles the pg_database.datdba (all databases are owned by postgres after an upgrade; will submit a separate patch for that as I consider that a bug by itself). Patch attached. Regards, Jan -- Jan Wieck Principle Database

Re: pg_upgrade failing for 200+ million Large Objects

On 3/20/21 11:23 AM, Tom Lane wrote: Jan Wieck writes: All that aside, the entire approach doesn't scale. Yeah, agreed. When we gave large objects individual ownership and ACL info, it was argued that pg_dump could afford to treat each one as a separate TOC entry because "you wouldn't

Re: pg_upgrade failing for 200+ million Large Objects

le object. Not going to work. But tests are still ongoing ... Regards, Jan -- Jan Wieck Principle Database Engineer Amazon Web Services

Re: Extensibility of the PostgreSQL wire protocol

On 3/4/21 7:38 PM, Hannu Krosing wrote: On Thu, Mar 4, 2021 at 9:55 PM Jan Wieck wrote: but the whole thing was developed that way from the beginning and it is working. I don't have a definitive date when that code will be presented. Kuntal or Prateek may be able to fill in more details

Re: PROXY protocol support

On 3/4/21 3:40 PM, Magnus Hagander wrote: On Thu, Mar 4, 2021 at 9:29 PM Jan Wieck wrote: This looks like it would only need a few extra protocol messages to be understood by the backend. It might be possible to implement that with the loadable wire protocol extensions proposed here: https

Re: Extensibility of the PostgreSQL wire protocol

t way from the beginning and it is working. I don't have a definitive date when that code will be presented. Kuntal or Prateek may be able to fill in more details. Regards, Jan -- Jan Wieck Principle Database Engineer Amazon Web Services

Re: PROXY protocol support

by HAProxy Technologies. --Jacob This looks like it would only need a few extra protocol messages to be understood by the backend. It might be possible to implement that with the loadable wire protocol extensions proposed here: https://commitfest.postgresql.org/32/3018/ Regards, Jan --

Re: Extensibility of the PostgreSQL wire protocol

On 2/19/21 10:13 AM, Jan Wieck wrote: Give the function, that postmaster is calling to accept a connection when a server_fd is ready, a return code that it can use to tell postmaster "forget about it, don't fork or do anything else with it". This function is normally calling Stream

Re: Extensibility of the PostgreSQL wire protocol

. libpq V4 may need to change some of the call signatures, which has happened before. For example, the function to send the command completion message to the frontend (tcop/dest.c EndCommand()) changed from 12 to 13. Regards, Jan -- Jan Wieck Principle Database Engineer Amazon Web Services

Re: Extensibility of the PostgreSQL wire protocol

message-id/CAB_5SReoPJAPO26Z8+WN6ugfBb2UDc3c21rRz9=bzibmcap...@mail.gmail.com -- Jan Wieck Principle Database Engineer Amazon Web Services

Re: Extensibility of the PostgreSQL wire protocol

On 2/19/21 4:36 AM, Kuntal Ghosh wrote: On Thu, Feb 18, 2021 at 9:32 PM Jan Wieck wrote: Few comments in the extension code (although experimental): 1. In telnet_srv.c, + static intpe_port; .. + DefineCustomIntVariable("telnet_srv

Re: Extensibility of the PostgreSQL wire protocol

on a separate channel. Regards, Jan -- Jan Wieck Principle Database Engineer Amazon Web Services

Re: Extensibility of the PostgreSQL wire protocol

etely idle to for example be able to restart the postmaster without forcibly disconnecting all clients. A pool built into a background worker cannot do that. Regards, Jan -- Jan Wieck Principle Database Engineer Amazon Web Services

Re: Extensibility of the PostgreSQL wire protocol

not really good results, both performance wise as with respect to protocol completeness. Regards, Jan -- Jan Wieck Principle Database Engineer Amazon Web Services

Re: Extensibility of the PostgreSQL wire protocol

Thank you Kuntal, On Fri, Feb 19, 2021 at 4:36 AM Kuntal Ghosh wrote: > On Thu, Feb 18, 2021 at 9:32 PM Jan Wieck wrote: > > > Few comments in the extension code (although experimental): > > 1. In telnet_srv.c, > + static intpe_port; > .. > +

Re: Extensibility of the PostgreSQL wire protocol

d >> those things could in theory bring benefits to the community, but >> there are no actual plans to do anything with this that would benefit >> anyone other than Amazon. Which seems to bring us right back to >> expecting the community to maintain things for the benefit of

Re: Extensibility of the PostgreSQL wire protocol

On Wed, Feb 10, 2021 at 11:43 AM Robert Haas wrote: > On Mon, Jan 25, 2021 at 10:07 AM Jan Wieck wrote: > > Our current plan is to create a new set of API calls and hooks that > allow to register additional wire protocols. The existing backend libpq > implementation will be modif

Re: Extensibility of the PostgreSQL wire protocol

Hi Jonah, On Mon, Jan 25, 2021 at 10:18 AM Jonah H. Harris wrote: > On Mon, Jan 25, 2021 at 10:07 AM Jan Wieck wrote: > >> The following is a request for discussion and comments, not a refined >> proposal accompanied by a working patch. >> > > After implemen

Extensibility of the PostgreSQL wire protocol

receiver, which works pretty well in our current code. Regards, Jan -- Jan Wieck Principal Database Engineer Amazon Web Services

Re: Performance improvement of WAL writing?

have no idea what performance or reliability impact that may have. There were reasons we chose to implement WAL segment recycling many years ago. These reasons may no longer be valid on modern filesystems, but it definitely is not a performance question alone. Regards, Jan -- Jan Wieck Senior

Re: lazy detoasting

nge to the snapshot data, namely changing the > 'satisfies' condition to HeapTupleSatisfiesToast ? > > The lsn and whenTaken seem to be fetched from the original data > and stored right back without change. > > -- Jan Wieck Senior Postgres Architect http://pgblog.wi3ck.info