Re: Libpq support to connect to standby server as priority

Hi Hackers, I'd like to submit a new version of a patch that I'd previously submitted but was eventually Returned with Feedback (closed in commitfest 2020-03). The patch enhances the libpq "target_session_attrs" connection parameter by supporting primary/standby/prefer-standby, and I've attempted

Find query characters in respect of optimizer for develop purpose

Hello: Before I want to pay attention to some optimizer features, I want to estimate how much benefits it can create for customers, at least for our current running customer. So I want to have some basic idea what kind of the query is running now in respect of optimizer. My basic is we can track

Re: Fix a typo in slot.c

On Mon, 18 May 2020 at 13:59, Amit Kapila wrote: > > On Fri, May 15, 2020 at 10:08 AM Masahiko Sawada > wrote: > > > > On Fri, 15 May 2020 at 13:26, Amit Kapila wrote: > > > > > > > > > /* > > > - * Allocate and initialize walsender-related shared memory. > > > + * Allocate and initialize repli

Re: PG 13 release notes, first draft

> On 5 May 2020, at 05:16, Bruce Momjian wrote: > > I have committed the first draft of the PG 13 release notes. You can > see them here: Spotted a typo we probably should fix: s/PostgresSQL/PostgreSQL/ =) cheers ./daniel 13relnotes_postgressql.diff Description: Binary data

Re: pg_bsd_indent and -Wimplicit-fallthrough

On Sun, May 17, 2020 at 2:32 AM Michael Paquier wrote: > > On Sat, May 16, 2020 at 11:56:28AM -0400, Tom Lane wrote: > > In the meantime, I went ahead and pushed this to our pg_bsd_indent repo. > > Thanks, Tom. +1, thanks a lot!

Optimizer docs typos

Attached diff fixes two small typos in the optimizer README. cheers ./daniel optimizer_doc_typos.diff Description: Binary data

Re: Optimizer docs typos

On Mon, May 18, 2020 at 11:31 AM Daniel Gustafsson wrote: > Attached diff fixes two small typos in the optimizer README. > Pushed, thanks. -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/

Re: Optimizer docs typos

On Mon, May 18, 2020 at 6:56 PM Magnus Hagander wrote: > On Mon, May 18, 2020 at 11:31 AM Daniel Gustafsson wrote: >> Attached diff fixes two small typos in the optimizer README. > Pushed, thanks. Thank you! Best regards, Etsuro Fujita

Re: [PATCH] Add support to psql for edit-and-execute-command

On 18 May 2020, at 7:08, Oleksandr Shulgin wrote: The only difference from \e is that you don't need to jump to the end of input first, I guess? AIUI, \e will edit the last thing in history or a specific line number from history, whereas the patch will allow the current line to be edited.

Re: [PATCH] Add support to psql for edit-and-execute-command

po 18. 5. 2020 v 12:05 odesílatel Joe Wildish napsal: > On 18 May 2020, at 7:08, Oleksandr Shulgin wrote: > > The only difference from \e is that you don't need to jump to the end of > input first, I guess? > > AIUI, \e will edit the last thing in history or a specific line number > from history,

Re: [PATCH] Add support to psql for edit-and-execute-command

On 18 May 2020, at 11:09, Pavel Stehule wrote: \e is working with not empty line too.You can check select 1\e Your patch just save skip on end line and \e Personally I think so it is good idea Thanks. I did not realise that \e at the end of a line would edit that line. (although you do n

Re: [PATCH] Add support to psql for edit-and-execute-command

po 18. 5. 2020 v 12:16 odesílatel Joe Wildish napsal: > On 18 May 2020, at 11:09, Pavel Stehule wrote: > > \e is working with not empty line too.You can check > > select 1\e > > Your patch just save skip on end line and \e > > Personally I think so it is good idea > > Thanks. I did not realise th

Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions

On Sun, May 17, 2020 at 12:41 PM Dilip Kumar wrote: > > On Fri, May 15, 2020 at 4:04 PM Amit Kapila wrote: > > > > > > Review comments: > > -- > > 1. > > @@ -1762,10 +1952,16 @@ ReorderBufferCommit(ReorderBuffer *rb, > > TransactionId xid, > > } > > > > case REORDE

Re: Optimizer docs typos

In this same README doc, another suspicious typo to me, which happens in section "Optimizer Functions", is in the prefix to query_planner(), we should have three dashes, rather than two, since query_planner() is called within grouping_planner(). diff --git a/src/backend/optimizer/README b/src/back

Re: Performance penalty when requesting text values in binary format

On Sat, 2020-05-16 at 20:12 -0500, Jack Christensen wrote: > I'm the creator of the PostgreSQL driver pgx (https://github.com/jackc/pgx) > for the Go language. > I have found significant performance advantages to using the extended > protocol and binary format > values -- in particular for types

Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions

On Mon, May 18, 2020 at 4:10 PM Amit Kapila wrote: > > On Sun, May 17, 2020 at 12:41 PM Dilip Kumar wrote: > > Few comments on v20-0010-Bugfix-handling-of-incomplete-toast-tuple 1. + /* + * If this is a toast insert then set the corresponding bit. Otherwise, if + * we have toast insert bit set

Re: Spawned Background Process Knows the Exit of Client Process?

On Fri, May 15, 2020 at 11:53 PM Shichao Jin wrote: > > Hi Postgres Hackers, > > I am wondering is there any elegant way for self-spawned background process > (forked by us) to get notified when the regular client-connected process exit > from the current database (switch db or even terminate)?

Re: Spawned Background Process Knows the Exit of Client Process?

Hi Ashutosh, Thank you for your answer. For the first point, as you suggested, we will migrate to table AM sooner or later. For the second point, your description is exactly correct (an independent process to access the storage engine). We can have multiple threads to overcome the performance is

Re: PG 13 release notes, first draft

Thanks, applied. --- On Mon, May 18, 2020 at 11:18:51AM +0200, Daniel Gustafsson wrote: > > On 5 May 2020, at 05:16, Bruce Momjian wrote: > > > > I have committed the first draft of the PG 13 release notes. You can > > s

Re: [PATCH] hs_standby_disallowed test fix

On 2020-05-12 19:35, Tom Lane wrote: Fujii Masao writes: I just wonder why standbycheck regression test doesn't run by default in buildfarm. Which caused us not to notice this issue long time. Maybe because it's difficult to set up hot-standby environment in the regression test? If so, we might

Missing grammar production for WITH TIES

The syntax for FETCH FIRST allows the to be absent (implying 1). We implement this correctly for ONLY, but WITH TIES didn't get the memo. Patch attached. -- Vik Fearing diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 3c78f2d1b5..a24b30f06f 100644 --- a/src/backend/parse

factorial function/phase out postfix operators?

There have been occasional discussions about deprecating or phasing out postfix operators, to make various things easier in the parser. The first step would in any case be to provide alternatives for the existing postfix operators. There is currently one, namely the numeric factorial operator

Re: fill_extraUpdatedCols is done in completely the wrong place

On 2020-05-08 21:05, Tom Lane wrote: I happened to notice $subject while working on the release notes. AFAICS, it is 100% inappropriate for the parser to compute the set of generated columns affected by an UPDATE, because that set could change before execution. It would be really easy to break t

Re: factorial function/phase out postfix operators?

On 5/18/20 4:42 PM, Peter Eisentraut wrote: > There have been occasional discussions about deprecating or phasing out > postfix operators, to make various things easier in the parser. > > The first step would in any case be to provide alternatives for the > existing postfix operators.  There is cu

Vintage unused variables in pg_dump.c

Unless I'm missing something, the g_comment_start and g_comment_end variables in pg_dump.c seems to have been unused since 30ab5bd43d8f2082659191 (in the 7.2 cycle) and can probably be safely removed by now. The attached passes make check. cheers ./daniel pg_dump_unused_vars.patch Description:

Re: Two fsync related performance issues?

Thanks for the replies. On Tue, May 12, 2020 at 2:04 PM Michael Paquier wrote: > On Tue, May 12, 2020 at 12:55:37PM +0900, Fujii Masao wrote: > > On 2020/05/12 9:42, Paul Guo wrote: > >> 1. StartupXLOG() does fsync on the whole data directory early in > >> the crash recovery. I'm wondering if we

proposal - plpgsql - FOR over unbound cursor

Hi Last week I played with dbms_sql extension and some patterns of usage cursor in PL/SQL and PL/pgSQL. I found fact, so iteration over cursor (FOR statement) doesn't support unbound cursors. I think so this limit is not necessary. This statement can open portal for bound cursor or can iterate ove

Re: factorial function/phase out postfix operators?

On Mon, May 18, 2020 at 05:02:34PM +0200, Vik Fearing wrote: > On 5/18/20 4:42 PM, Peter Eisentraut wrote: > > There have been occasional discussions about deprecating or phasing out > > postfix operators, to make various things easier in the parser. > > > > The first step would in any case be to

Re: Add A Glossary

On 17.05.20 17:28, Alvaro Herrera wrote: On 2020-May-17, Erik Rijkers wrote: On 2020-05-17 08:51, Alvaro Herrera wrote: I don't think that's the general understanding of those terms. For all I know, they*are* synonyms, and there's no specific term for "the fluctuating objects" as you call th

Re: Why is pq_begintypsend so slow?

Andres Freund writes: >> FWIW, I've also observed, in another thread (the node func generation >> thing [1]), that inlining enlargeStringInfo() helps a lot, especially >> when inlining some of its callers. Moving e.g. appendStringInfo() inline >> allows the compiler to sometimes optimize away the

Re: Missing grammar production for WITH TIES

On 2020-May-18, Vik Fearing wrote: > The syntax for FETCH FIRST allows the to be > absent (implying 1). > > We implement this correctly for ONLY, but WITH TIES didn't get the memo. Oops, yes. I added a test. Will get this pushed immediately after I see beta1 produced. -- Álvaro Herrera

Re: Vintage unused variables in pg_dump.c

Daniel Gustafsson writes: > Unless I'm missing something, the g_comment_start and g_comment_end variables > in pg_dump.c seems to have been unused since 30ab5bd43d8f2082659191 (in the > 7.2 > cycle) and can probably be safely removed by now. Indeed. (Well, I didn't verify your statement about w

Re: Why is pq_begintypsend so slow?

Em seg., 18 de mai. de 2020 às 13:38, Tom Lane escreveu: > Andres Freund writes: > >> FWIW, I've also observed, in another thread (the node func generation > >> thing [1]), that inlining enlargeStringInfo() helps a lot, especially > >> when inlining some of its callers. Moving e.g. appendStringI

Re: fill_extraUpdatedCols is done in completely the wrong place

Peter Eisentraut writes: > On 2020-05-08 21:05, Tom Lane wrote: >> I happened to notice $subject while working on the release notes. >> AFAICS, it is 100% inappropriate for the parser to compute the >> set of generated columns affected by an UPDATE, because that set >> could change before executio

Re: Why is pq_begintypsend so slow?

Ranier Vilela writes: > Again, I see problems with the types declared in Postgres. > 1. pq_sendint32 (StringInfo buf, uint32 i) > 2. extern void pq_sendbytes (StringInfo buf, const char * data, int > datalen); We could spend the next ten years cleaning up minor discrepancies like that, and have n

Re: Two fsync related performance issues?

Paul Guo writes: > table directories & wal fsync probably dominates the fsync time. Do we > know any possible real scenario that requires table directory fsync? Yes, there are filesystems where that's absolutely required. See past discussions that led to putting in those fsyncs (we did not alway

Re: pgindent && weirdness

Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Thomas Munro writes: > > It seems I cannot. Please go ahead. > > [ yawn... ] It's about bedtime here, but I'll take care of it in the > morning. > > Off the critical path, we oughta figure out why the repo wouldn't > let you commit. What I

Re: Missing grammar production for WITH TIES

On 5/18/20 7:03 PM, Alvaro Herrera wrote: > On 2020-May-18, Vik Fearing wrote: > >> The syntax for FETCH FIRST allows the to be >> absent (implying 1). >> >> We implement this correctly for ONLY, but WITH TIES didn't get the memo. > > Oops, yes. I added a test. Will get this pushed immediately

PostgresSQL project

Hi I am a member of a small UK based team with extensive database experience. We are considering a project using PostgresSQL source code which only uses the insert data capabilities. Is there a contact who we could speak with and discuss our project aims in principal. Thanks Luke

Re: Another modest proposal for docs formatting: catalog descriptions

On 2020-May-06, Alvaro Herrera wrote: > ... oh, okay. I guess I was reporting that the font on the new version > seems to have got smaller. Looking at other pages, it appears that the > font is indeed a lot smaller in all tables, including those Tom has been > editing. So maybe this is desirabl

Re: POC: rational number type (fractions)

On 2020-02-08 05:25, Joe Nelson wrote: Hi hackers, attached is a proof of concept patch adding a new base type called "rational" to represent fractions. It includes arithmetic, simplification, conversion to/from float, finding intermediates with a stern-brocot tree, custom aggregates, and btree/h

Re: PostgresSQL project

On 2020-05-18 18:21, Luke Porter wrote: I am a member of a small UK based team with extensive database experience. We are considering a project using PostgresSQL source code which only uses the insert data capabilities. Is there a contact who we could speak with and discuss our project aims i

Re: POC: rational number type (fractions)

On 05/18/20 17:33, Peter Eisentraut wrote: > The numeric type already stores rational numbers. How is this different? > What's the use? Seems like numeric is a base-1 representation. Will work ok for a rational whose denominator factors into 2s and 5s. Won't ever quite represent, say, 1/3,

Re: POC: rational number type (fractions)

Chapman Flack writes: > On 05/18/20 17:33, Peter Eisentraut wrote: >> The numeric type already stores rational numbers. How is this different? >> What's the use? > Won't ever quite represent, say, 1/3, no matter how big you let it get. There surely are use-cases for true rational arithmetic, b

Re: Missing grammar production for WITH TIES

On 2020-May-18, Alvaro Herrera wrote: > On 2020-May-18, Vik Fearing wrote: > > > The syntax for FETCH FIRST allows the to be > > absent (implying 1). > > > > We implement this correctly for ONLY, but WITH TIES didn't get the memo. > > Oops, yes. I added a test. Will get this pushed immediate

Re: [HACKERS] Restricting maximum keep segments by repslots

BTW while you're messing with checkpointer, I propose this patch to simplify things. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >From 9148a6defa2e8b3fd81b982de53f73584a8b3d10 Mon Sep 17 00:00:00 2001 From: Al

Re: factorial function/phase out postfix operators?

Peter Eisentraut writes: > What are the thoughts about then marking the postfix operator deprecated > and eventually removing it? If we do this it'd require a plan. We'd have to also warn about the feature deprecation in (at least) the CREATE OPERATOR man page, and we'd have to decide how many

Re: BUG #16147: postgresql 12.1 (from homebrew) - pg_restore -h localhost --jobs=2 crashes

On Thu, Mar 05, 2020 at 07:53:35PM -0800, David Zhang wrote: > I can reproduce this pg_restore crash issue (pg_dump crash too when running > with multiple jobs) on MacOS 10.14 Mojave and MacOS 10.15 Catalina using > following steps. Isn't this the same as here? https://www.postgresql.org/message-i

Re: factorial function/phase out postfix operators?

On Mon, May 18, 2020 at 10:03:13PM -0400, Tom Lane wrote: > Peter Eisentraut writes: > > What are the thoughts about then marking the postfix operator deprecated > > and eventually removing it? > > If we do this it'd require a plan. We'd have to also warn about the > feature deprecation in (at

Re: Planning counters in pg_stat_statements (using pgss_store)

Thanks for the excellent extension. I want to add 5 more fields to satisfy the following requirements. int subplan; /* No. of subplan in this query */ int subquery; /* No. of subquery */ int joincnt; /* How many relations are joined */ bool hasagg; /* if we have agg function in this query */

PostgreSQL 13 Beta 1 Release Announcement Draft

Hi, Attached is a draft of the release announcement for the PostgreSQL 13 Beta 1 release this week. The goal of this release announcement is to make people aware of the new features that are introduced in PostgreSQL 13 and, importantly, get them to start testing. I have tried to include a broad a

Re: factorial function/phase out postfix operators?

On 5/19/20 4:03 AM, Tom Lane wrote: > Peter Eisentraut writes: >> What are the thoughts about then marking the postfix operator deprecated >> and eventually removing it? > > If we do this it'd require a plan. We'd have to also warn about the > feature deprecation in (at least) the CREATE OPERAT

Re: xid wraparound danger due to INDEX_CLEANUP false

On Thu, 7 May 2020 at 16:26, Masahiko Sawada wrote: > > On Thu, 7 May 2020 at 15:40, Masahiko Sawada > wrote: > > > > On Thu, 7 May 2020 at 03:28, Peter Geoghegan wrote: > > > > > > On Wed, May 6, 2020 at 2:28 AM Masahiko Sawada > > > wrote: > > > > I've attached the patch fixes this issue. > >

Re: Missing grammar production for WITH TIES

On Mon, May 18, 2020 at 07:30:32PM -0400, Alvaro Herrera wrote: > Done. Thanks! This has been committed just after beta1 has been stamped. So it means that it won't be included in it, right? -- Michael signature.asc Description: PGP signature

Re: SyncRepLock acquired exclusively in default configuration

On Sat, 11 Apr 2020 at 09:30, Masahiko Sawada wrote: > > On Fri, 10 Apr 2020 at 21:52, Fujii Masao wrote: > > > > > > > > On 2020/04/10 20:56, Masahiko Sawada wrote: > > > On Fri, 10 Apr 2020 at 18:57, Fujii Masao > > > wrote: > > >> > > >> > > >> > > >> On 2020/04/10 14:11, Masahiko Sawada wro

Re: [HACKERS] Restricting maximum keep segments by repslots

On Mon, May 18, 2020 at 07:44:59PM -0400, Alvaro Herrera wrote: > BTW while you're messing with checkpointer, I propose this patch to > simplify things. It seems to me that this would have a benefit if we begin to have a code path in CreateCheckpoint() where where it makes sense to let the checkpo

Re: Missing grammar production for WITH TIES

On 5/19/20 4:36 AM, Michael Paquier wrote: > On Mon, May 18, 2020 at 07:30:32PM -0400, Alvaro Herrera wrote: >> Done. Thanks! > > This has been committed just after beta1 has been stamped. So it > means that it won't be included in it, right? Correct. I don't know why there was a delay, but it

Re: Missing grammar production for WITH TIES

On 2020-May-19, Vik Fearing wrote: > On 5/19/20 4:36 AM, Michael Paquier wrote: > > > This has been committed just after beta1 has been stamped. So it > > means that it won't be included in it, right? > > Correct. Right. > I don't know why there was a delay, but it also doesn't bother me. I d

Re: [HACKERS] Restricting maximum keep segments by repslots

On 2020-May-19, Michael Paquier wrote: > On Mon, May 18, 2020 at 07:44:59PM -0400, Alvaro Herrera wrote: > > BTW while you're messing with checkpointer, I propose this patch to > > simplify things. > > It seems to me that this would have a benefit if we begin to have a > code path in CreateCheckp

Re: Missing grammar production for WITH TIES

Michael Paquier writes: > On Mon, May 18, 2020 at 07:30:32PM -0400, Alvaro Herrera wrote: >> Done. Thanks! > This has been committed just after beta1 has been stamped. So it > means that it won't be included in it, right? Right. regards, tom lane

Re: could not stat promote trigger file leads to shutdown

On Wed, Dec 04, 2019 at 11:52:33AM +0100, Peter Eisentraut wrote: > Is it possible to do this in a mostly bullet-proof way? Just because the > directory exists and looks pretty good otherwise, doesn't mean we can read a > file created in it later in a way that doesn't fall afoul of the existing >

Re: Add A Glossary

On Mon, 2020-05-18 at 18:08 +0200, Jürgen Purtz wrote: > cluster/instance: PG (mainly) consists of a group of processes that commonly > act on shared buffers. The processes are very closely related to each other > and with the buffers. They exist altogether or not at all. They use a common > initia

Re: Missing grammar production for WITH TIES

On Tue, May 19, 2020 at 12:41:39AM -0400, Tom Lane wrote: > Michael Paquier writes: >> This has been committed just after beta1 has been stamped. So it >> means that it won't be included in it, right? > > Right. Still, wouldn't it be better to wait until the version is tagged? My understanding

pg_dump dumps row level policies on extension tables

Hi, I noticed that if a row level policy is defined on an extension object, even in the extension creation script, pg_dump dumps a separate CREATE POLICY statement for such policies. That makes the dump unrestorable because the CREATE EXTENSION and CREATE POLICY then conflicts. Here is a simple e

some grammar refactoring

Here is a series of patches to do some refactoring in the grammar around the commands COMMENT, DROP, SECURITY LABEL, and ALTER EXTENSION ... ADD/DROP. In the grammar, these commands (with some exceptions) basically just take a reference to an object and later look it up in C code. Some of tha

Re: Add A Glossary

I think there needs to be a careful analysis of the language and a formal effort to stabilise it for the future. In the context of, say, an Oracle T series, which is partitioned into multiple domains (virtual machines) in it, each of these has multiple CPUs, and can run an instance of the OS which