Re: [HACKERS] SQL/JSON in PostgreSQL

Hi I am checking the JSONPath related code Questions, notes: 1. jsonpath operators are not consistent with any other .. json, xml .. I am missing ?, @> operátors 2. documentation issue - there is "'{"a":[1,2,3,4,5]}'::json *? '$.a[*] ? (@ > 2)'" - operator *? doesn't exists 3. operator @~ looks

Re: January CommitFest is underway!

On Sat, Jan 6, 2018 at 9:58 AM, Ryan Murphy wrote: >> BTW, for those who aren't already aware of it, I'd like to point out >> this very handy resource: >> >> http://commitfest.cputube.org > > Wow, that makes it way easier to see what's going on! Thanks Tom. Thomas Munro has created that. -- Mic

Re: [HACKERS] SQL/JSON in PostgreSQL

2018-01-03 17:04 GMT+01:00 Oleg Bartunov : > > > On 3 Jan 2018 00:23, "Andrew Dunstan" > wrote: > > > > On 01/02/2018 05:04 PM, Nikita Glukhov wrote: > > > > I have removed all extra features from the patch set, they can be > > found in our > > github repository: > > https://github.com/postgrespr

Re: Invalid pg_upgrade error message during live check

On Fri, Jan 5, 2018 at 04:58:39PM -0500, Bruce Momjian wrote: > Pg_upgrade is able to run in --check mode when the old server is still > running. Unfortunately, all supported versions of pg_upgrade generate > an incorrect (but harmless) "failure" message when doing this: Based on recent discussi

Re: pgsql: pg_upgrade: simplify code layout in a few places

On Fri, Jan 5, 2018 at 08:39:46PM -0500, Tom Lane wrote: > Andres Freund writes: > > On 2018-01-05 18:57:55 -0500, Bruce Momjian wrote: > >> On Fri, Jan 5, 2018 at 03:51:15PM -0800, Andres Freund wrote: > >>> Also, leaving translatability aside, why was *any* of this backpatched? > > >> Tom has

Re: pgsql: pg_upgrade: simplify code layout in a few places

Andres Freund writes: > On 2018-01-05 18:57:55 -0500, Bruce Momjian wrote: >> On Fri, Jan 5, 2018 at 03:51:15PM -0800, Andres Freund wrote: >>> Also, leaving translatability aside, why was *any* of this backpatched? >> Tom has preferred that I backpatch all safe patches so we keep that code >> c

Re: January CommitFest is underway!

> BTW, for those who aren't already aware of it, I'd like to point out > this very handy resource: > > http://commitfest.cputube.org > > Wow, that makes it way easier to see what's going on! Thanks Tom.

Re: [HACKERS] Timeline ID in backup_label file

On Sat, Jan 6, 2018 at 9:54 AM, Michael Paquier wrote: > On Fri, Jan 5, 2018 at 11:13 PM, Simon Riggs wrote: >> On 27 November 2017 at 14:06, David Steele wrote: >> >>> I have tested and get an error as expected when I munge the backup_label >>> file: >>> >>> FATAL: invalid data in file "backup

Re: [HACKERS] Timeline ID in backup_label file

On Fri, Jan 5, 2018 at 11:13 PM, Simon Riggs wrote: > On 27 November 2017 at 14:06, David Steele wrote: > >> I have tested and get an error as expected when I munge the backup_label >> file: >> >> FATAL: invalid data in file "backup_label" >> DETAIL: Timeline ID parsed is 2, but expected 1 >> >

Re: pgsql: Implement channel binding tls-server-end-point for SCRAM

On Sat, Jan 6, 2018 at 2:56 AM, Alvaro Herrera wrote: > Peter Eisentraut wrote: >> On 1/5/18 09:28, Michael Paquier wrote: >> > In order to do things cleanly, we should make this TAP test >> > conditional on the version of OpenSSL. >> >> How about looking into pg_config.h, like in the attached pat

Re: pgsql: pg_upgrade: simplify code layout in a few places

On 2018-01-05 18:57:55 -0500, Bruce Momjian wrote: > On Fri, Jan 5, 2018 at 03:51:15PM -0800, Andres Freund wrote: > > On 2018-01-05 14:20:59 -0500, Robert Haas wrote: > > > On Fri, Jan 5, 2018 at 2:11 PM, Bruce Momjian wrote: > > > > pg_upgrade: simplify code layout in a few places > > > > > >

Re: pgsql: pg_upgrade: simplify code layout in a few places

On Fri, Jan 5, 2018 at 03:51:15PM -0800, Andres Freund wrote: > On 2018-01-05 14:20:59 -0500, Robert Haas wrote: > > On Fri, Jan 5, 2018 at 2:11 PM, Bruce Momjian wrote: > > > pg_upgrade: simplify code layout in a few places > > > > > > Backpatch-through: 9.4 (9.3 didn't need improving) > > > >

Re: pgsql: pg_upgrade: simplify code layout in a few places

On 2018-01-05 14:20:59 -0500, Robert Haas wrote: > On Fri, Jan 5, 2018 at 2:11 PM, Bruce Momjian wrote: > > pg_upgrade: simplify code layout in a few places > > > > Backpatch-through: 9.4 (9.3 didn't need improving) > > Hmm. We don't normally do things like this, because it breaks > translatab

Re: [HACKERS] Proposal: Local indexes for partitioned table

Robert Haas wrote: > On Fri, Jan 5, 2018 at 4:57 PM, Peter Eisentraut > wrote: > > On 1/4/18 23:08, David Rowley wrote: > >> I admit to not having had a chance to look at any code with this yet, > >> but I'm just thinking about a case like the following. > >> > >> CREATE TABLE part (a INT, b INT)

Re: [HACKERS] Proposal: Local indexes for partitioned table

On Fri, Jan 5, 2018 at 4:57 PM, Peter Eisentraut wrote: > On 1/4/18 23:08, David Rowley wrote: >> On 5 January 2018 at 11:01, Alvaro Herrera wrote: >>> (The more I think of this, the more I believe that pg_inherits is a >>> better answer. Opinions?) >> >> I admit to not having had a chance to lo

Re: [HACKERS] Proposal: Local indexes for partitioned table

On Thu, Jan 4, 2018 at 5:01 PM, Alvaro Herrera wrote: > Tangentially: I didn't like very much that I added a new index to > pg_index to support this feature. I thought maybe it'd be better to > change the index on indrelid to be on (indrelid,indparentidx) instead, > but that doesn't seem great ei

Invalid pg_upgrade error message during live check

Pg_upgrade is able to run in --check mode when the old server is still running. Unfortunately, all supported versions of pg_upgrade generate an incorrect (but harmless) "failure" message when doing this: $ pg_upgrade -b /u/pgsql.old/bin -B /u/pgsql/bin \ -d /u/pgsql.old/da

Re: [HACKERS] Proposal: Local indexes for partitioned table

On 1/4/18 23:08, David Rowley wrote: > On 5 January 2018 at 11:01, Alvaro Herrera wrote: >> (The more I think of this, the more I believe that pg_inherits is a >> better answer. Opinions?) > > I admit to not having had a chance to look at any code with this yet, > but I'm just thinking about a c

Re: [HACKERS] Removing useless DISTINCT clauses

On Mon, Nov 6, 2017 at 1:16 AM, David Rowley wrote: > In [1] we made a change to process the GROUP BY clause to remove any > group by items that are functionally dependent on some other GROUP BY > items. > > This really just checks if a table's PK columns are entirely present > in the GROUP BY cl

Re: [HACKERS] Transaction control in procedures

A merge conflict has arisen, so for simplicity, here is an updated patch. On 12/20/17 10:08, Peter Eisentraut wrote: > Updated patch attached. > > I have addressed the most recent review comments I believe. > > The question about what happens to cursor loops in PL/Perl and PL/Python > would be a

Re: Condition variable live lock

On Sat, Jan 6, 2018 at 6:33 AM, Tom Lane wrote: > As I feared, the existing regression tests are not really adequate for > this: gcov testing shows that the sentinel-inserting code path is > never entered, meaning ConditionVariableBroadcast never sees more > than one waiter. What's more, it's now

Re: [HACKERS] UPDATE of partition key

On Fri, Jan 5, 2018 at 7:12 AM, Amit Khandekar wrote: > The above patch is to be applied over the last remaining preparatory > patch, now named (and attached) : > 0001-Refactor-CheckConstraint-related-code.patch Committed that one, too. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com T

Buildfarm Client Bugfix Release 6.1

I have released version 6.1 of the PostgreSQL Buildfarm client. It is available at This release fixes a couple of bugs that became apparent in yesterday's release. The first was a relatively minor one where the verbosity was

Re: Condition variable live lock

Robert Haas writes: > On Fri, Jan 5, 2018 at 2:38 PM, Tom Lane wrote: >> * I think the Asserts in ConditionVariablePrepareToSleep and >> ConditionVariableSleep ought to be replaced by full-fledged test and >> elog(ERROR), so that they are enforced even in non-assert builds. >> I don't have a lot

Re: setting estate in ExecInitNode() itself

On Fri, Jan 5, 2018 at 2:50 PM, Andres Freund wrote: > Agreed on that. But I also think there's quite some room for > centralizing some of the work done in the init routines. Not quite sure > how, but I do dislike the amount of repetition both in code and > comments. +1. I *assume* that if you r

Re: setting estate in ExecInitNode() itself

Andres Freund writes: > On 2018-01-05 10:36:19 -0500, Tom Lane wrote: >> Ashutosh Bapat writes: >>> I am wondering why don't we instead save estate in ExecInitNode() itself >>> like >>> result->estate = estate; >> That would only work if there were no situation where the field needed to >> be a

Re: Condition variable live lock

On Fri, Jan 5, 2018 at 2:38 PM, Tom Lane wrote: > * I think the Asserts in ConditionVariablePrepareToSleep and > ConditionVariableSleep ought to be replaced by full-fledged test and > elog(ERROR), so that they are enforced even in non-assert builds. > I don't have a lot of confidence that corner c

Re: setting estate in ExecInitNode() itself

On 2018-01-05 10:36:19 -0500, Tom Lane wrote: > Ashutosh Bapat writes: > > Looking at ExecInitXYZ() functions, we can observe that every such > > function has a statement like > > XYZstate->ps.state = estate; > > where it saves estate in the PlanState. > > Yeah ... > > > I am wondering why don't

Re: pgsql: pg_upgrade: simplify code layout in a few places

On Fri, Jan 5, 2018 at 02:37:58PM -0500, Robert Haas wrote: > On Fri, Jan 5, 2018 at 2:32 PM, Alvaro Herrera > wrote: > > Bruce Momjian wrote: > >> On Fri, Jan 5, 2018 at 02:20:59PM -0500, Robert Haas wrote: > >> > On Fri, Jan 5, 2018 at 2:11 PM, Bruce Momjian wrote: > >> > > pg_upgrade: simp

Re: pgsql: pg_upgrade: simplify code layout in a few places

On Fri, Jan 5, 2018 at 02:31:51PM -0500, Robert Haas wrote: > On Fri, Jan 5, 2018 at 2:24 PM, Bruce Momjian wrote: > > On Fri, Jan 5, 2018 at 02:20:59PM -0500, Robert Haas wrote: > >> On Fri, Jan 5, 2018 at 2:11 PM, Bruce Momjian wrote: > >> > pg_upgrade: simplify code layout in a few places >

Re: Condition variable live lock

I wrote: > As I feared, the existing regression tests are not really adequate for > this: gcov testing shows that the sentinel-inserting code path is > never entered, meaning ConditionVariableBroadcast never sees more > than one waiter. Hmm ... adding tracing log printouts in BarrierArriveAndWait

Re: pgsql: pg_upgrade: simplify code layout in a few places

On Fri, Jan 5, 2018 at 2:32 PM, Alvaro Herrera wrote: > Bruce Momjian wrote: >> On Fri, Jan 5, 2018 at 02:20:59PM -0500, Robert Haas wrote: >> > On Fri, Jan 5, 2018 at 2:11 PM, Bruce Momjian wrote: >> > > pg_upgrade: simplify code layout in a few places >> > > >> > > Backpatch-through: 9.4 (9.3

Re: [HACKERS] Pluggable storage

On Fri, Jan 5, 2018 at 7:20 PM, Robert Haas wrote: > I do not like the way that this patch set uses the word "storage". In > current usage, storage is a thing that certain kinds of relations > have. Plain relations (a.k.a. heap tables) have storage, indexes have > storage, materialized views ha

Re: pgsql: pg_upgrade: simplify code layout in a few places

Bruce Momjian wrote: > On Fri, Jan 5, 2018 at 02:20:59PM -0500, Robert Haas wrote: > > On Fri, Jan 5, 2018 at 2:11 PM, Bruce Momjian wrote: > > > pg_upgrade: simplify code layout in a few places > > > > > > Backpatch-through: 9.4 (9.3 didn't need improving) > > > > Hmm. We don't normally do th

Re: pgsql: pg_upgrade: simplify code layout in a few places

On Fri, Jan 5, 2018 at 2:24 PM, Bruce Momjian wrote: > On Fri, Jan 5, 2018 at 02:20:59PM -0500, Robert Haas wrote: >> On Fri, Jan 5, 2018 at 2:11 PM, Bruce Momjian wrote: >> > pg_upgrade: simplify code layout in a few places >> > >> > Backpatch-through: 9.4 (9.3 didn't need improving) >> >> Hmm

Re: BUGFIX: standby disconnect can corrupt serialized reorder buffers

I think this should use ReadDirExtended with an elevel less than ERROR, and do nothing. Why have strcmp(.) and strcmp(..)? These are going to be skipped by the comparison to "xid" prefix anyway. Looks like strcmp processing power waste. Please don't use bare sprintf() -- upgrade to snprintf. W

Re: pgsql: pg_upgrade: simplify code layout in a few places

On Fri, Jan 5, 2018 at 02:20:59PM -0500, Robert Haas wrote: > On Fri, Jan 5, 2018 at 2:11 PM, Bruce Momjian wrote: > > pg_upgrade: simplify code layout in a few places > > > > Backpatch-through: 9.4 (9.3 didn't need improving) > > Hmm. We don't normally do things like this, because it breaks

Re: pgsql: pg_upgrade: simplify code layout in a few places

On Fri, Jan 5, 2018 at 2:11 PM, Bruce Momjian wrote: > pg_upgrade: simplify code layout in a few places > > Backpatch-through: 9.4 (9.3 didn't need improving) Hmm. We don't normally do things like this, because it breaks translatability. -- Robert Haas EnterpriseDB: http://www.enterprisedb.co

Re: Speeding up pg_upgrade

On Thu, Dec 7, 2017 at 11:28 AM, Justin Pryzby wrote: > On Tue, Dec 05, 2017 at 09:01:35AM -0500, Bruce Momjian wrote: > > As part of PGConf.Asia 2017 in Tokyo, we had an unconference topic about > > zero-downtime upgrades. ... we discussed speeding up pg_upgrade. > > > > There are clusters that

Re: User defined data types in Logical Replication

hash_seq_init in logicalrep_typmap_invalidate_cb is useless after your patch. If you remove it, the function becomes empty, so why is it there an invalidation callback at all? Are we now leaking memory if types keep repeatedly being re-created in the origin? I suppose it's not a common use patte

Re: [HACKERS] Runtime Partition Pruning

Hello, On Fri, Jan 5, 2018 at 6:24 AM, David Rowley wrote: > On 5 January 2018 at 05:37, Alvaro Herrera wrote: >> I tried this patch (applying it on Amit's last current version on top of >> 4e2970f8807f which is the latest it applies to) and regression tests >> fail with the attached diff; in al

Re: [HACKERS] path toward faster partition pruning

Hi David, On 01/04/2018 09:21 PM, David Rowley wrote: On 5 January 2018 at 07:16, Jesper Pedersen wrote: Could you share your thoughts on 1) if the generic plan mechanics should know about the pruning and hence give a lower planner cost I think the problem here is that cached_plan_cost() is

Re: pgsql: Implement channel binding tls-server-end-point for SCRAM

Peter Eisentraut wrote: > On 1/5/18 09:28, Michael Paquier wrote: > > In order to do things cleanly, we should make this TAP test > > conditional on the version of OpenSSL. > > How about looking into pg_config.h, like in the attached patch? I suppose if this starts to spread, we'll come up with a

Re: Condition variable live lock

I wrote: > It's a question of how complicated you're willing to make this > logic, and whether you trust that we'll be able to test all the > code paths. Attached is a patch incorporating all the ideas mentioned in this thread, except that I think in HEAD we should change both ConditionVariableSig

Re: [HACKERS] Pluggable storage

I do not like the way that this patch set uses the word "storage". In current usage, storage is a thing that certain kinds of relations have. Plain relations (a.k.a. heap tables) have storage, indexes have storage, materialized views have storage, TOAST tables have storage, and sequences have sto

Re: [PATCH] Logical decoding of TRUNCATE

Hi, I've found some SGML errors in the version v10 of the patch. I've fixed it in version v11 that is attached. Regards, Marco -- Marco Nenciarini - 2ndQuadrant Italy PostgreSQL Training, Services and Support marco.nenciar...@2ndquadrant.it | www.2ndQuadrant.it diff --git a/contrib/test_decodin

Re: setting estate in ExecInitNode() itself

Ashutosh Bapat writes: > Looking at ExecInitXYZ() functions, we can observe that every such > function has a statement like > XYZstate->ps.state = estate; > where it saves estate in the PlanState. Yeah ... > I am wondering why don't we instead save estate in ExecInitNode() itself like > result->

Re: Failed to delete old ReorderBuffer spilled files

atorikoshi wrote: > > FYI "make check" in contrib/test_decoding fails a couple of isolation > > tests, one with an assertion failure for my automatic patch tester[1]. > > Same result on my laptop: > > > > test ondisk_startup ... FAILED (test process exited with exit > > code 1) > > tes

Re: Condition variable live lock

Thomas Munro writes: > Could we install the sentinel and pop the first entry at the same > time, so that we're not adding an extra spinlock acquire/release? Hm, maybe. Other ideas in that space: * if queue is empty when we first acquire the spinlock, we don't have to do anything at all. * if q

Re: pgsql: Implement channel binding tls-server-end-point for SCRAM

On 1/5/18 09:28, Michael Paquier wrote: > In order to do things cleanly, we should make this TAP test > conditional on the version of OpenSSL. How about looking into pg_config.h, like in the attached patch? -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7

Re: Failed to delete old ReorderBuffer spilled files

Thomas Munro wrote: > On Wed, Nov 22, 2017 at 12:27 AM, atorikoshi > wrote: > > [set_final_lsn_2.patch] > > Hi Torikoshi-san, > > FYI "make check" in contrib/test_decoding fails a couple of isolation > tests, one with an assertion failure for my automatic patch tester[1]. > Same result on my lap

Re: pgsql: Implement channel binding tls-server-end-point for SCRAM

On Fri, Jan 5, 2018 at 10:47 PM, Robert Haas wrote: > The SSL tests on chipmunk failed in the last run. I assume that's > probably the fault of this patch, or one of the follow-on commits: Thanks for the heads-up, Robert. I did not notice the failure. That's the fault of 054e8c6c. Raspbian is us

Re: [HACKERS] Creating backup history files for backups taken from standbys

On 19 September 2017 at 00:33, David Steele wrote: > On 9/18/17 7:26 PM, Michael Paquier wrote: >> On Tue, Sep 19, 2017 at 8:14 AM, David Steele wrote: >>> On 8/31/17 11:56 PM, Michael Paquier wrote: Here is an updated patch with refreshed documentation, as a result of 449338c which was

Re: Fix permissions check on pg_stat_get_wal_senders

On 23 December 2017 at 10:56, Michael Paquier wrote: > On Fri, Dec 22, 2017 at 07:49:34AM +0100, Feike Steenbergen wrote: >> On 21 December 2017 at 14:11, Michael Paquier >> wrote: >> > You mean a WAL receiver here, not a WAL sender. >> >> Fixed, thanks > > [nit] > /* > -

Re: [HACKERS] Timeline ID in backup_label file

On 27 November 2017 at 14:06, David Steele wrote: > I have tested and get an error as expected when I munge the backup_label > file: > > FATAL: invalid data in file "backup_label" > DETAIL: Timeline ID parsed is 2, but expected 1 > > Everything else looks good so I will mark it ready for commit

Re: Contributing with code

Antonio Belloni wrote: > Sorry, but I did not want to start a flaming war against the TODO list with > my first message. In all the other open source projects I have contributed > code, the TODO list is always a start point to newcomers. There's no > explicit message in the Postgresql TODO list say

Re: [Patch] Make block and file size for WAL and relations defined at cluster creation

On Fri, Jan 5, 2018 at 7:54 AM, Michael Paquier wrote: > On Fri, Jan 5, 2018 at 9:42 PM, Robert Haas wrote: >> - run make check-world at all the supposedly-supported block sizes and >> see if it passes. > > Last time I tried that with a 16kB-size block, which was some months > back, make check co

Re: pgsql: Implement channel binding tls-server-end-point for SCRAM

On Thu, Jan 4, 2018 at 4:09 PM, Thomas Munro wrote: > On Fri, Jan 5, 2018 at 9:36 AM, Peter Eisentraut wrote: >> Implement channel binding tls-server-end-point for SCRAM > > FYI some BF animals are saying: > > libpq/be-secure-openssl.o: In function `be_tls_get_certificate_hash': > /home/pgbuildfa

Re: Contributing with code

Sorry, but I did not want to start a flaming war against the TODO list with my first message. In all the other open source projects I have contributed code, the TODO list is always a start point to newcomers. There's no explicit message in the Postgresql TODO list saying that the projects there are

Re: [Patch] Make block and file size for WAL and relations defined at cluster creation

On Fri, Jan 5, 2018 at 9:42 PM, Robert Haas wrote: > - run make check-world at all the supposedly-supported block sizes and > see if it passes. Last time I tried that with a 16kB-size block, which was some months back, make check complained about some plan inconsistencies. Perhaps that's somethin

Re: [Patch] Make block and file size for WAL and relations defined at cluster creation

On Thu, Jan 4, 2018 at 5:15 PM, Remi Colinet wrote: > Block size does not boils down only to performance. > > For instance, having a larger block size allows: > - to avoid toasting tuples. Rows with sizes larger that the default block > size can justify larger block sizes. > - to reduce fragmentat

Re: Enhance pg_stat_wal_receiver view to display connected host

Haribabu Kommi wrote: > And also not returning "default host" details, because for the conninfo > without any host details, the return value must be NULL. But this change > may break the backward compatibility of the function. I wouldn't want to have to fight that battle. > or > > write two new

Re: [HACKERS] Issues with logical replication

> On 3 Jan 2018, at 23:35, Alvaro Herrera wrote: > > Pushed. Will you (Konstantin, Stas, Masahiko) please verify that after > this commit all the problems reported with logical replication are > fixed? Checked that with and without extra sleep in AssignTransactionId(). In both cases patch work

Re: Condition variable live lock

Tom Lane wrote: > Obviously, this trades a risk of loss of wakeup for a risk > of spurious wakeup, but presumably the latter is something we can > cope with. I wonder if it'd be useful to have a test mode for condition variables that spurious wakups happen randomly, to verify that every user is p

Re: [Patch] Make block and file size for WAL and relations defined at cluster creation

On Wed, January 3, 2018 4:04 pm, Robert Haas wrote: > On Wed, Jan 3, 2018 at 3:43 PM, Remi Colinet > wrote: >> Justifications are: > > I think this is all missing the point. If varying the block size (or > whatever) is beneficial, then having it configurable at initdb is > clearly useful. But,

Re: Condition variable live lock

On Fri, Jan 5, 2018 at 7:42 PM, Tom Lane wrote: > Indeed, it looks like no other caller is paying attention to the result. > We could live with the uncertainty in the back branches, and redefine > ConditionVariableSignal as returning void in master. +1 Could we install the sentinel and pop the f