Re: proposal: alternative psql commands quit and exit

On Fri, Dec 8, 2017 at 2:10 AM, Michael Paquier wrote: > > I think that you are going to need better reasons than just being more > friendly with other database clients ... > This is not about other database clients. This is about users considering migration to

Re: New committers announced at PGCon 2018

сб, 2 июня 2018 г. в 1:10, Teodor Sigaev : > > > Etsuro Fujita > > Peter Geoghegan > > Amit Kapila > > Alexander Korotkov > > Thomas Munro > > Michael Paquier > > Tomas Vondra > > > > Congratulations to all! > > +7! +7, and +2 to you and Oleg with progress in building your Postgres team

Re: Built-in connection pooling

On Fri, Apr 13, 2018 at 2:59 AM, Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > > Development in built-in connection pooling will be continued in > https://github.com/postgrespro/postgresql.builtin_pool.git > I am not going to send new patches to hackers mailing list any more. > Why?

Re: Built-in connection pooling

from various users, that Postgres needs “default”/official pooling tool. вт, 17 апр. 2018 г. в 0:44, Konstantin Knizhnik <k.knizh...@postgrespro.ru>: > > > On 13.04.2018 19:07, Nikolay Samokhvalov wrote: > > On Fri, Apr 13, 2018 at 2:59 AM, Konstantin Knizhnik < > k.kni

Re: idea - custom menu

On Sat, Mar 3, 2018 at 1:08 PM, Pavel Stehule wrote: > Hi > Hi Pavel, I don't know how I missed this email, but finally I found it :-) > I am looking on project https://github.com/NikolayS/postgres_dba > > Nice idea, and perfect publicity of typical PostgreSQL

Using old master as new replica after clean switchover

Currently, the documentation explicitly states, that after failover, the old master must be recreated from scratch, or pg_rewind should be used (requiring wal_log_hints to be on, which is off by default): > The former standby is now the primary, but the former primary is down and might stay down.

Re: Using old master as new replica after clean switchover

On Thu, Oct 25, 2018 at 6:03 AM Jehan-Guillaume de Rorthais wrote: > What about logging the shutdown checkpoint on the old master? > On the standby side, we could cross-check it with a function confirming: > 1/ the very last XLogRecord received was the old master shutdown checkpoint > 2/ the

Re: New GUC to sample log queries

On Thu, Nov 29, 2018 at 1:49 PM Alvaro Herrera wrote: > Thanks! I pushed this with two changes -- one was to reword the docs a > bit more, and the other was to compute in_sample only if it's going to > be used (when exceeded is true). I hope this won't upset any compilers ... > This is a

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

Hello On Sat, Mar 16, 2019 at 7:32 AM Robert Haas wrote: > Also, I think this is now the third independent request to expose > query ID in pg_stat_statements. I think we should give the people > what they want. > Count me as the 4th. This would be a very important feature for automated query

Re: PostgreSQL and Real Application Testing (RAT)

On Tue, Aug 27, 2019 at 3:47 AM ROS Didier wrote: > Hi > > > > In my business, one of the things blocking the migration from Oracle to > PostgreSQL is not having the equivalent of Oracle Real Application Testing . > > This product captures a charge in production and replay it in a test >

Re: Backport "WITH ... AS MATERIALIZED" syntax to <12?

+1 for the configuration option. Otherwise, migration is a nightmare -- so many CTEs were written specifically to use the "optimization fence" behavior. The lack of such configuration options is now a "migration fence". On Sat, Oct 19, 2019 at 2:49 AM Colin Watson wrote: > On Sat, Oct 19, 2019

Re: Backport "WITH ... AS MATERIALIZED" syntax to <12?

On Sat, Oct 19, 2019 at 8:11 AM Isaac Morland wrote: > That embeds a temporary hack in the application code indefinitely. > Or postpone the migration indefinitely. I saw so many times how migration in large companies was postponed because of similar "small" issues -- when the code base is

Re: [PATCH] Increase the maximum value track_activity_query_size

Here is what ORMs do: select length('SELECT "column_name_1001", "column_name_1002", "column_name_1003", "column_name_1004", "column_name_1005", "column_name_1006", "column_name_1007", "column_name_1008", "column_name_1009", "column_name_1010", "column_name_1011", "column_name_1012",

Re: Default gucs for EXPLAIN

This is a very good improvement! Using information about buffers is my favorite way to optimize queries. Not having BUFFERS enabled by default means that in most cases, when asking for help, people send execution plans without buffers info. And it's simply in on event to type "(ANALYZE,

Re: Default gucs for EXPLAIN

On Mon, May 25, 2020 at 6:36 PM, Bruce Momjian < br...@momjian.us > wrote: > > > > I am not excited about this new feature. Why do it only for EXPLAIN? That > is a log of GUCs. I can see this becoming a feature creep disaster. > > > > How about changing the default behavior, making

Re: Add important info about ANALYZE after create Functional Index

On Mon, Oct 26, 2020 at 3:46 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > It would seem preferable to call the lack of auto-analyzing after these > operations a bug and back-patch a fix that injects an analyze side-effect > just before their completion. It doesn't have to be smart

Re: Add important info about ANALYZE after create Functional Index

On Mon, Oct 26, 2020 at 7:03 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Monday, October 26, 2020, Nikolay Samokhvalov > wrote: >> >> Although, this triggers a question – should ANALYZE be automated in, say, >> pg_restore as well? >> >

Re: Add important info about ANALYZE after create Functional Index

ix dor the docs. From 7e846e6864b48be51bb0afee455f853debe10cb9 Mon Sep 17 00:00:00 2001 From: Nikolay Samokhvalov Date: Tue, 27 Oct 2020 06:26:50 + Subject: [PATCH 1/2] Rebuilding indexes on expressions requires ANALYZE It is critically important to run ANALYZE before dropping the old index. This is only relevant to Post

Re: Postgres is not able to handle more than 4k tables!?

Hi Konstantin, a silly question: do you consider the workload you have as well-optimized? Can it be optimized further? Reading this thread I have a strong feeling that a very basic set of regular optimization actions is missing here (or not explained): query analysis and optimization based on

Re: Postgres is not able to handle more than 4k tables!?

Great idea. In addition to this, it would be good to consider another optimization for the default transaction isolation level: making autovacuum to clean dead tuples in relations that are not currently used in any transaction and when there are no IN_PROGRESS transactions running at RR or S

Re: pg_stat_statements oddity with track = all

On Tue, Dec 1, 2020 at 8:05 PM Julien Rouhaud wrote: > Hi, > > Someone raised an interested point recently on pg_stat_kcache extension for > handling nested statements, which also applies to pg_stat_statements. > ... > The only idea I have for that is to add a new field to entry key, for >

Re: Commitfest 2020-11 is closed

On Wed, Dec 2, 2020 at 2:36 PM Andrew Dunstan wrote: > > On 12/2/20 5:13 PM, Thomas Munro wrote: > > > > I'm experimenting with Github's built in CI. All other ideas welcome. > > > I'd look very closely at gitlab. > +1. Why: - having a great experience for more than 2 years - if needed, there

Re: pg_stat_statements oddity with track = all

On Tue, Dec 1, 2020 at 10:32 PM Julien Rouhaud wrote: > On Tue, Dec 01, 2020 at 10:08:06PM -0800, Nikolay Samokhvalov wrote: > > If all top-level records in pg_stat_statements have "true" in the new > > column (is_toplevel), how would this lead to the need to increase &

Re: pspg pager is finished

On Fri, Mar 19, 2021 at 20:35 Pavel Stehule wrote: > Hi > > I finished work on pspg. > > https://github.com/okbob/pspg > Thank you, Pavel. I use it always when possible, and highly recommend it to others. > > Nik

Re: Subtransactions + a long-running transaction leading to performance degradation on standbys

On Thu, Aug 19, 2021 at 10:04 PM Andrey Borodin wrote: > I just want to note, that on your screenshot unpatched version runs 400K > tps, while patched runs 280K tps. I see the dates are different and this > effect is not observed in [0]. Probably, you run tests on different > machines.

Re: log_autovacuum in Postgres 14 -- ordering issue

On Wed, Aug 25, 2021 at 10:34 AM Peter Geoghegan wrote: > It would be a lot clearer if the "buffer usage" line was simply moved > down. I think that it should appear after the lines that are specific > to the table's indexes -- just before the "avg read rate" line. That > way we'd group the

Re: Release 14 Schedule

Observability-related improvements are also good and very important for the future of DBA operations -- compute_query_id, new pg_stat_**, etc. Things like new knob idle_session_timeout and restore_command change not requiring a restart will be very noticeable too. On Sun, Sep 19, 2021 at 2:45 PM

Re: amcheck verification for GiST and GIN

Hello, First of all, thank you all -- Andrey, Peter, Heikki and others -- for this work, GIN support in amcheck is *really* needed, especially for OS upgrades such as from Ubuntu 16.04 (which is EOL now) to 18.04 or 20.04 I was trying to check a bunch of GINs on some production after switching

Re: amcheck verification for GiST and GIN

Thank you, v5 didn't find any issues at all. One thing: for my 29 indexes, the tool generated output 3.5 GiB. I guess many INFO messages should be downgraded to something like DEBUG1? On Fri, Jul 30, 2021 at 2:35 AM Heikki Linnakangas wrote: > On 29/07/2021 21:34, Nikolay Samokhvalov wr

Re: Add create and update timestamp to all objects

On Sun, Sep 26, 2021 at 1:11 PM Efrain J. Berdecia wrote: > Are there any plans to add a create and last updated time stamp field to > any and all objects in postgres? > > Possibly even adding a updated_by documenting which role created and last > updated the object. > > All done natively and

BUFFERS enabled by default in EXPLAIN (ANALYZE)

Re-reading the thread [1] (cannot answer there – don't have those emails in my box anymore), I see that there was strong support for enabling BUFFERS in EXPLAIN ANALYZE by default. And there were patches. Commitfest entry [2] was marked Rejected because there were questions to the implementation

Re: Commitfest 2021-11 Patch Triage - Part 2

On Tue, Nov 9, 2021 at 7:02 AM Tom Lane wrote: > Daniel Gustafsson writes: > > 2773: libpq compression > > === > > This patch intended to provide libpq connection compression to "replace > SSL > > compression" which was doomed when the patch was written, and have since >

Re: lastOverflowedXid does not handle transaction ID wraparound

On Mon, Nov 1, 2021 at 11:55 PM Nikolay Samokhvalov wrote: > The following review has been posted through the commitfest application: > make installcheck-world: tested, failed Please ignore this – I didn't understand the UI.

Re: lastOverflowedXid does not handle transaction ID wraparound

On Mon, Oct 25, 2021 at 11:41 AM Nikolay Samokhvalov wrote: > On Thu, Oct 21, 2021 at 07:21 Stan Hu wrote: > >> On Wed, Oct 20, 2021 at 9:01 PM Kyotaro Horiguchi >> wrote: >> > >> > lastOverflowedXid is the smallest subxid that possibly exists but >>

Re: lastOverflowedXid does not handle transaction ID wraparound

The following review has been posted through the commitfest application: make installcheck-world: tested, failed Implements feature: tested, failed Spec compliant: not tested Documentation:not tested The fix is trivial and works as expected, solving the problem

Re: should we enable log_checkpoints out of the box?

On Tue, Nov 2, 2021 at 11:50 AM Robert Haas wrote: > I almost proposed 1m rather than 10m, but then I thought the better of > it. I think it's unlikely that an autovacuum that takes 1 minute is > really the cause of some big problem you're having on your system. > Typical problem cases I see are

Re: should we enable log_checkpoints out of the box?

On Tue, Nov 2, 2021 at 8:55 AM Robert Haas wrote: > I think shipping with log_checkpoints=on and > log_autovacuum_min_duration=10m or so would be one of the best things > we could possibly do to allow ex-post-facto troubleshooting of > system-wide performance issues. > Fully agree, it would be

Re: should we enable log_checkpoints out of the box?

On Tue, Nov 2, 2021 at 5:02 PM Tom Lane wrote: > I'm still of the position that the default ought to be that a > normally-functioning server generates no ongoing log output. > Only people who have got Nagios watching their logs, or some > such setup, are going to want anything different. And

Re: lastOverflowedXid does not handle transaction ID wraparound

On Thu, Oct 21, 2021 at 07:21 Stan Hu wrote: > On Wed, Oct 20, 2021 at 9:01 PM Kyotaro Horiguchi > wrote: > > > > lastOverflowedXid is the smallest subxid that possibly exists but > > possiblly not known to the standby. So if all top-level transactions > > older than lastOverflowedXid end, that

Re: Add sub-transaction overflow status in pg_stat_activity

On Mon, Dec 6, 2021 at 8:16 PM Dilip Kumar wrote: > If the subtransaction cache is overflowed in some of the transactions > then it will affect all the concurrent queries as they need to access > the SLRU for checking the visibility of each tuple. But currently > there is no way to identify

Re: Observability in Postgres

On Mon, Feb 14, 2022 at 10:15 Greg Stark wrote: > > For now my approach is to implement a background worker that listens > on a new port and is basically its own small web server with shared > memory access This reminds me bg_mon (included into Spilo, docker image used by Zalando

Re: Transaction timeout

On Wed, Sep 6, 2023 at 1:16 AM Fujii Masao wrote: > With the v4 patch, I found that timeout errors no longer occur during the > idle in > transaction phase. Instead, they occur when the next statement is executed. > Is this > the intended behavior? I thought some users might want to use the

Re: Amcheck verification of GiST and GIN

On Wed, Jun 22, 2022 at 11:35 AM Andrey Borodin wrote: > > On 30 May 2022, at 12:40, Andrey Borodin wrote: > > > > What do you think? > > Hi Andrey! > Hi Andrey! Since you're talking to yourself, just wanted to support you – this is an important thing, definitely should be very useful for

Re: Transaction timeout

On Thu, Jan 12, 2023 at 11:47 AM Andrey Borodin wrote: > On Thu, Jan 12, 2023 at 11:24 AM Nathan Bossart > wrote: > > > > On Sun, Dec 18, 2022 at 12:53:31PM -0800, Andrey Borodin wrote: > > > I've rewritten this part to correctly report all timeouts that did > > > happen. However there's now a

Re: Transaction timeout

On Fri, Jan 13, 2023 at 10:16 AM Andrey Borodin wrote: > > – it seems we could (should) have one more successful "1s wait, 3s > sleep" iteration here, ~727ms somehow wasted in a loop, quite a lot. > > I think big chunk from these 727ms were spent between "BEGIN" and > "select now(),

Re: Transaction timeout

On Fri, Dec 2, 2022 at 9:18 PM Andrey Borodin wrote: > Hello, > > We have statement_timeout, idle_in_transaction_timeout, > idle_session_timeout and many more! But we have no > transaction_timeout. I've skimmed thread [0,1] about existing timeouts > and found no contraindications to implement

Re: Transaction timeout

The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: not tested Documentation:not tested Tested, works as expected; documentation is not yet added

Re: Transaction timeout

On Sat, Dec 3, 2022 at 9:41 AM Andrey Borodin wrote: > Fixed. Added test for this. > Thanks! Tested (gitpod: https://gitpod.io/#https://gitlab.com/NikolayS/postgres/tree/transaction_timeout-v2 ), works as expected.

Re: [PATCH] Add pretty-printed XML output option

On Mon, Feb 20, 2023 at 3:06 PM Jim Jones wrote: > As suggested by Peter and Nikolay, v15 now removes the xmlformat > function from the catalog and adds the [NO] INDENT option to > xmlserialize, as described in X069.\ > Great. I'm checking this patch and it seems, indentation stops working if

Re: [PATCH] Add pretty-printed XML output option

On Thu, Feb 9, 2023 at 2:31 AM Peter Eisentraut < peter.eisentr...@enterprisedb.com> wrote: > I suggest we call it "xmlformat", which is an established term for this. > Some very-very old, rusted memory told me that there was something in standard – and indeed, it seems it described an optional

Re: [PATCH] Add pretty-printed XML output option

On Fri, Feb 17, 2023 at 1:14 AM Jim Jones wrote: > After your comment I'm studying the possibility to extend the existing > xmlserialize function to add the indentation feature. If so, how do you > think it should look like? An extra parameter? e.g. > > SELECT xmlserialize(DOCUMENT '42'::XML AS

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

On Wed, Feb 22, 2023 at 9:55 AM Tom Lane wrote: > On the whole I'd rather not eat more of the limited namespace for > psql prompt codes for this. > It depends on personal preferences. When I work on a large screen, I can afford to spend some characters in prompts, if it gives convenience – and

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

On Wed, Feb 22, 2023 at 9:18 AM Kirk Wolak wrote: > Proposal: Simply add the %T (PROMPT variable) to output the current time > (HH24:MI:SS) into the prompt. This has been in sqlplus since I can > remember, and I find it really useful when I forgot to time something, or > to review for Time

Re: pg_upgrade and logical replication

On Fri, Feb 17, 2023 at 7:35 AM Julien Rouhaud wrote: > > Any table later added in the > publication is either already fully replicated until that LSN on the upgraded > node, so only the delta is needed, or has been created after that LSN. In the > latter case, the entirety of the table will be

Re: pg_upgrade and logical replication

On Tue, Feb 28, 2023 at 4:43 PM Julien Rouhaud wrote: > > On Tue, Feb 28, 2023 at 08:02:13AM -0800, Nikolay Samokhvalov wrote: > > 0. Temporarily, forbid running any DDL on the source cluster. > > This is (at least for me) a non starter, as I want an approach that doesn't &g

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

On Thu, Feb 23, 2023 at 9:05 AM Maciek Sakrejda wrote: > I think Heikki's solution is probably more practical since (1) .. Note that these ideas target two *different* problems: - what was the duration of the last query - when was the last query executed So, having both solved would be ideal.

Re: Amcheck verification of GiST and GIN

On Thu, Feb 2, 2023 at 12:15 PM Peter Geoghegan wrote: > On Thu, Feb 2, 2023 at 11:51 AM Peter Geoghegan wrote: > ... > Admittedly there is some value in seeing multiple WARNINGs to true > experts that are performing some kind of forensic analysis, but that > doesn't seem worth it to me -- I'm

Re: Amcheck verification of GiST and GIN

On Thu, Feb 2, 2023 at 12:43 PM Peter Geoghegan wrote: > I agree that this matters at the level of whole indexes. > I already realized my mistake – indeed, having multiple errors for 1 index doesn't seem to be super practically helpful. > I think that that problem should be solved at a higher

Prevent accidental whole-table DELETEs and UPDATEs

In many cases, a DELETE or UPDATE not having a WHERE clause (or having it with a condition matching all rows in the table) is a sign of some kind of mistake, leading to accidental data loss, performance issues, producing a lot of dead tuples, and so on. Recently, this topic was again discussed [1]

Re: pg_upgrade instructions involving "rsync --size-only" might lead to standby corruption?

On Fri, Jul 7, 2023 at 6:31 AM Stephen Frost wrote: > * Nikolay Samokhvalov (n...@postgres.ai) wrote: > > But this can happen with anyone who follows the procedure from the docs > as > > is and doesn't do any additional steps, because in step 9 "Prepare for >

Re: pg_upgrade instructions involving "rsync --size-only" might lead to standby corruption?

n be running at this point or fully stopped. > > "or be fully stopped." I think. > > > + If they > > + are still running, you can stop, upgrade, and start them one by > one; this > > + can be useful to

Configurable FP_LOCK_SLOTS_PER_BACKEND

be 64, 2) or even make it configurable – a new GUC. Thanks, Nikolay Samokhvalov Founder, Postgres.ai

Re: UUID v7

On Tue, Feb 14, 2023 at 6:13 AM Kyzer Davis (kydavis) wrote: > I am happy to see others interested in the improvements provided by UUIDv7! Thank you for providing the details! Some small updates as I see them: - there is revision 7 now in https://github.com/ietf-wg-uuidrev/rfc4122bis - noticing

pg_upgrade instructions involving "rsync --size-only" might lead to standby corruption?

eplication, so I'm not 100% sure I'm right suspecting the original procedure in having standby corruption risks.) Thanks, Nikolay Samokhvalov Founder, Postgres.ai

Re: pg_upgrade instructions involving "rsync --size-only" might lead to standby corruption?

re execution of pg_upgrade -k + rsync --size-only, then those writes are going to be silently lost on standbys. I wonder, if we ensure that standbys are fully caught up before upgrading the primary, if we check the latest checkpoint positions, are we good to use "rsync --size-only", or t

Re: Fix bug with indexes on whole-row expressions

On Wed, Dec 13, 2023 at 7:01 AM Tom Lane wrote: > ywgrit writes: > > I forbid to create indexes on whole-row expression in the following > patch. > > I'd like to hear your opinions. > > As I said in the previous thread, I don't think this can possibly > be acceptable. Surely there are people

Re: Set log_lock_waits=on by default

On Thu, Dec 21, 2023 at 05:29 Laurenz Albe wrote: > Here is a patch to implement this. > Being stuck behind a lock for more than a second is almost > always a problem, so it is reasonable to turn this on by default. I think it's a very good idea. On all heavily loaded systems I have observed

Re: UUID v7

On Fri, Jan 19, 2024 at 10:07 AM Andrey Borodin wrote: > > > > On 19 Jan 2024, at 13:25, Andrey Borodin wrote: > > > > Also, I've added some documentation on all functions. > > Here's v12. Changes: > 1. Documentation improvements > 2. Code comments > 3. Better commit message and reviews list >

Re: UUID v7

The following review has been posted through the commitfest application: make installcheck-world: not tested Implements feature: tested, passed Spec compliant: not tested Documentation:tested, passed Manually tested uuidv7(), uuid_extract_time() – they work as

Re: UUID v7

On Wed, Jan 24, 2024 at 1:52 PM Sergey Prokhorenko < sergeyprokhore...@yahoo.com.au> wrote: > That's right! There is no point in waiting for the official approval of > the new RFC, which obviously will not change anything. I have been a > contributor to this RFC >

Re: UUID v7

On Wed, Jan 24, 2024 at 8:40 PM Nikolay Samokhvalov wrote: > On Wed, Jan 24, 2024 at 1:52 PM Sergey Prokhorenko < > sergeyprokhore...@yahoo.com.au> wrote: > >> That's right! There is no point in waiting for the official approval of >> the new RFC, which obviously will

Re: mxid_age() and age(xid) appear undocumented

On Mon, Nov 13, 2023 at 5:01 PM Peter Geoghegan wrote: > > On Mon, Nov 13, 2023 at 4:43 PM Bruce Momjian wrote: > > I looked into this and all the 4-byte xid functions are marked as > > deprecated for the 8-byte variants. I don't think documenting 4-byte > > mxid_age() and age(xid) makes sense