Re: [HACKERS] Logical decoding on standby

On 19 March 2017 at 21:12, Craig Ringer wrote: > Rebased attached. Patch1 looks good to go. I'll correct a spelling mistake in the tap test when I commit that later today. Patch2 has a couple of points 2.1 Why does call to ReplicationSlotAcquire() move earlier in pg_logical_slot_get_changes_gu

[HACKERS] Re: [COMMITTERS] pgsql: Add TAP tests for password-based authentication methods.

On 03/18/2017 02:55 PM, Michael Paquier wrote: On Fri, Mar 17, 2017 at 6:37 PM, Heikki Linnakangas wrote: Add TAP tests for password-based authentication methods. Tests all combinations of users with MD5, plaintext and SCRAM verifiers stored in pg_authid, with plain 'password', 'md5' and 'scra

Re: [HACKERS] Patch to improve performance of replay of AccessExclusiveLock

On 17 March 2017 at 00:04, Amit Kapila wrote: > On Thu, Mar 16, 2017 at 7:22 AM, David Rowley > wrote: >> On 16 March 2017 at 13:31, Simon Riggs wrote: >>> >>> On 8 March 2017 at 10:02, David Rowley >>> wrote: >>> > On 8 March 2017 at 01:13, Simon Riggs wrote: >>> >> Don't understand this. I'm

Re: [HACKERS] Patch to improve performance of replay of AccessExclusiveLock

On 18 March 2017 at 21:52, Simon Riggs wrote: > 2. In LogAccessExclusiveLock() we can use GetCurrentTransactionId() > rather than GetTopTransactionId(), so that we assign the lock to the > subxid rather than the top xid. That could increase lock traffic, but > less likely. It also solves the probl

Re: [HACKERS] [PATCH] Transaction traceability - txid_status(bigint)

On 14 March 2017 at 19:57, Robert Haas wrote: > On Mon, Mar 13, 2017 at 10:22 PM, Craig Ringer wrote: >> I'll introduce a new LWLock, ClogTruncationLock, which will be held >> from when we advance the new clogOldestXid field through to when clog >> truncation completes. > > +1. OK, here's the re

Re: [HACKERS] WIP: Faster Expression Processing v4

Hi, On 2017-03-19 23:55:50 -0400, Tom Lane wrote: > Andres Freund writes: > > I've been pondering if we can't entirely get rid of CaseTest etc, the > > amount of hackery required seems not like a good thing. One way I'd > > prototyped was to replace them with PARAM_EXEC nodes - then the whole >

Re: [HACKERS] pageinspect and hash indexes

On Sat, Mar 18, 2017 at 5:13 PM, Ashutosh Sharma wrote: > On Sat, Mar 18, 2017 at 1:34 PM, Amit Kapila wrote: >> On Sat, Mar 18, 2017 at 12:12 AM, Ashutosh Sharma >> wrote: >>> On Fri, Mar 17, 2017 at 10:54 PM, Jeff Janes wrote: While trying to figure out some bloating in the newly logged

Re: [HACKERS] Adding support for Default partition in partitioning

Hello, Please find attached a rebased patch with support for pg_dump. I am working on the patch to handle adding a new partition after a default partition by throwing an error if conflicting rows exist in default partition and adding the partition successfully otherwise. Will post an updated patch

Re: [HACKERS] WIP: Faster Expression Processing v4

Andres Freund writes: > On 2017-03-16 11:15:16 -0400, Tom Lane wrote: >> The thing that actually made the ExecEvalCase code into a bug was that >> we were using ExprContext-level fields to store the current caseValue, >> allowing aliasing to occur across nested CASEs. I think that in >> this impl

Re: [HACKERS] WIP: Faster Expression Processing v4

Hi, On 2017-03-16 11:15:16 -0400, Tom Lane wrote: > The thing that actually made the ExecEvalCase code into a bug was that > we were using ExprContext-level fields to store the current caseValue, > allowing aliasing to occur across nested CASEs. I think that in > this implementation, it ought to

Re: [HACKERS] Determine if an error is transient by its error code.

Craig Ringer writes: > On 20 March 2017 at 10:26, Dominick O'Dierno wrote: >> Essentially I want to determine by the error code if it is worth retrying >> the call (transient) or if the error was due to a bad query or programmer >> error, in which case don't retry. > In general you'll need class

Re: [HACKERS] Determine if an error is transient by its error code.

On 20 March 2017 at 10:26, Dominick O'Dierno wrote: > Hello folks, > > I'm trying to define a transient fault detection strategy for a client > application when calling a postgres database. > > Essentially I want to determine by the error code if it is worth retrying > the call (transient) or if t

Re: [HACKERS] Speed up Clog Access by increasing CLOG buffers

On Fri, Mar 17, 2017 at 2:30 AM, Amit Kapila wrote: >> I was wondering about doing an explicit test: if the XID being >> committed matches the one in the PGPROC, and nsubxids matches, and the >> actual list of XIDs matches, then apply the optimization. That could >> replace the logic that you've

Re: [HACKERS] Re: Improve OR conditions on joined columns (common star schema problem)

On 3/19/17 2:32 PM, Tom Lane wrote: Jim Nasby writes: Having thought about it, I share Tom's concerns. And now I'm worried about what happens if there are multiple separate OR clauses. I guess those would be handled by separate UNIONs? As proposed, the patch would try to optimize by splitting

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Fri, Mar 17, 2017 at 8:10 PM, Robert Haas wrote: > While I was studying what you did with reparameterize_path_by_child(), > I started to wonder whether reparameterize_path() doesn't need to > start handling join paths. I think it only handles scan paths right > now because that's the only thin

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Sun, Mar 19, 2017 at 12:15 AM, Rafia Sabih wrote: > I was trying to play around with this patch and came across following > case when without the patch query completes in 9 secs and with it in > 15 secs. Theoretically, I tried to capture the case when each > partition is having good amount of r

[HACKERS] Determine if an error is transient by its error code.

Hello folks, I'm trying to define a transient fault detection strategy for a client application when calling a postgres database. Essentially I want to determine by the error code if it is worth retrying the call (transient) or if the error was due to a bad query or programmer error, in which cas

Re: [HACKERS] \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless)

Tom Lane wrote: > I'm not entirely convinced that function-per-command is an improvement > though. Seems like it would only help to the extent that you could do a > simple "return" to implement early exit, and it looks to me like that > doesn't work in a lot of places because you still have to cl

Re: [HACKERS] PATCH: Batch/pipelining support for libpq

On Fri, Mar 17, 2017 at 12:37 AM, Daniel Verite wrote: > Vaishnavi Prabakaran wrote: > > > So, attached the alternative fix for this issue. > > Please share me your thoughts. > > I assume you prefer the alternative fix because it's simpler. > I would like add one more reason for this fix

Re: [HACKERS] Parallel tuplesort (for parallel B-Tree index creation)

On Sun, Mar 12, 2017 at 3:05 PM, Peter Geoghegan wrote: > I attach my V9 of the patch. I came up some stuff for the design of > resource management that I think meets every design goal that we have > for shared/unified BufFiles: Commit 2609e91fc broke the parallel CREATE INDEX cost model. I shoul

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

On 3/18/17 16:12, Magnus Hagander wrote: > But given that we are in the process of breaking a lot of other scripts > for 10, I don't think the xlog renaming really affects this area. > perhaps we should rename it to pg_createuser? I'm not keen on doing that now, but if we were to do it at some p

Re: [HACKERS] createlang/droplang deprecated

On 3/18/17 09:00, Peter Eisentraut wrote: > I just noticed that createlang and droplang have been listed as > deprecated since PG 9.1. > > Do we dare remove them? Patch -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Serv

[HACKERS] Re: [COMMITTERS] pgsql: Add TAP tests for password-based authentication methods.

On 3/17/17 05:37, Heikki Linnakangas wrote: > Add TAP tests for password-based authentication methods. > > Tests all combinations of users with MD5, plaintext and SCRAM verifiers > stored in pg_authid, with plain 'password', 'md5' and 'scram' > authentication methods. This is missing an entry for

Re: [HACKERS] ICU integration

On 03/15/2017 05:33 PM, Peter Eisentraut wrote: Updated patch attached. Ok, I applied to patch again and ran the tests. I get a test failure on make check-world in the pg_dump tests but it can be fixed with the below. diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_

Re: [HACKERS] [COMMITTERS] pgsql: Fix and simplify check for whether we're running as Windows serv

Heikki Linnakangas writes: > On 03/17/2017 07:57 PM, Tom Lane wrote: >> This seems to have broken narwhal: > It's not very nice to change the requirements in a minor version, but I > don't think this would be a real problem for anyone. Not many people > build PostgreSQL using MinGW, let alone w

[HACKERS] Re: [COMMITTERS] pgsql: Fix and simplify check for whether we're running as Windows serv

On 03/17/2017 07:57 PM, Tom Lane wrote: Heikki Linnakangas writes: Fix and simplify check for whether we're running as Windows service. This seems to have broken narwhal: Creating library file: libpostgres.a ../../src/port/libpgport_srv.a(win32security_srv.o)(.text+0x109): In function `pgwi

Re: [HACKERS] \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless)

On Sun, Mar 19, 2017 at 1:18 PM, Fabien COELHO wrote: > > Hello Corey, > > v24 highlights: >> > > The v24 patch is twice larger that the previous submission. Sigh. > > If I'm reading headers correctly, it seems that it adds an > "expected/psql-on-error-stop.out" file without a corresponding test

Re: [HACKERS] \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless)

On Sun, Mar 19, 2017 at 4:23 PM, Fabien COELHO wrote: > > Hello Tom, > > I'm not entirely convinced that function-per-command is an improvement >> though. [...] >> > > I don't have a definite opinion on that core question yet, since I've not >> read this version of the patch. Anybody else want t

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

On 03/19/2017 01:01 PM, Tom Lane wrote: Andreas Karlsson writes: As for if we should have backwards compatibility for the old names I am leaning weakly for providing it in the case of createuser. I can see end users being pissed off that the createuser command is suddenly gone without any warni

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

Andres Freund writes: > I wish 'pg' wasn't an already used binary name. It'd be much nicer if > we had a '/usr/bin/pg' wrapper binary in the git style, that we could easily > expand over time, without hitting new conflicts. I'd even consider a > '/usr/bin/pgsql' that has commands for all our bin

Re: [HACKERS] \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless)

Hello Tom, I'm not entirely convinced that function-per-command is an improvement though. [...] I don't have a definite opinion on that core question yet, since I've not read this version of the patch. Anybody else want to give an opinion? My 0.02€: I've already provided my view... Pers

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

Stephen Frost writes: > If we take your approach to its logical conclustion then we should be > planning to maintain all user-facing deprecated features for as long as > there is a version where it exists in a non-deprecated fashion, in other > words for 5 years, if we assume that script authors o

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

On 2017-03-18 16:23:17 -0400, Tom Lane wrote: > Magnus Hagander writes: > > createuser, dropuser - definitely pollutes the namespace, people do > > sometimes try them for the wrong thing. Unlike the db ones they do add > > value though -- I don't think we have a psql way of in a single command > >

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

Andreas Karlsson writes: > As for if we should have backwards compatibility for the old names I am > leaning weakly for providing it in the case of createuser. I can see end > users being pissed off that the createuser command is suddenly gone > without any warning when they upgrade. On the fli

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

Greetings, * Andreas Karlsson (andr...@proxel.se) wrote: > On 03/19/2017 07:35 PM, Stephen Frost wrote: > >* Tom Lane (t...@sss.pgh.pa.us) wrote: > >>Stephen Frost writes: > >>(Or in other words, we've been getting along fine with these script names > >>for circa twenty years, so what's the rush

Re: [HACKERS] Re: Improve OR conditions on joined columns (common star schema problem)

I wrote: > Consider > SELECT count(*) > FROM a FULL JOIN b ON (a.id = b.id) > WHERE (a.x = 42 OR b.y = 43); > and suppose that a and b have mutual FK constraints guaranteeing that > every non-null a.id value has exactly one match in b and vice versa. Oh, that was sloppy of me. J

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

On 03/19/2017 07:35 PM, Stephen Frost wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost writes: (Or in other words, we've been getting along fine with these script names for circa twenty years, so what's the rush to change them RIGHT NOW?) To be clear, I'm not in any particular rush

Re: [HACKERS] Re: Improve OR conditions on joined columns (common star schema problem)

Jim Nasby writes: > On 3/16/17 11:54 AM, David Steele wrote: >> On 2/14/17 4:03 PM, Tom Lane wrote: >>> Well, the key point is whether it's really OK to de-dup on the basis >>> of only the CTIDs that are not eliminated in any UNION arm. I was >>> feeling fairly good about that until I thought of

Re: [HACKERS] Create replication slot in pg_basebackup if requested and not yet present

Hi, On Sun, Mar 19, 2017 at 05:01:23PM +0100, Magnus Hagander wrote: > On Sun, Mar 19, 2017 at 11:21 AM, Michael Banck > wrote: > > So I propose the attached tiny patch to just create the slot (if it does > > not exist already) in pg_basebackup, somewhat similar to what > > pg_receivewal does, al

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > >> It's not a "half measure", it's providing a sane upgrade path. > > > I really don't see it that way. We're talking about existing scripts > > which will break if the binary is renamed.

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> It's not a "half measure", it's providing a sane upgrade path. > I really don't see it that way. We're talking about existing scripts > which will break if the binary is renamed. That means that, today, > they're using createlang

Re: [HACKERS] \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless)

Alvaro Herrera writes: > The reason this is so large is that there is an entangled refactoring > patch, splitting the exec_command() function from one giant switch() > into one routine for each command. It's up to the committer whether to > do it all in one patch, or to request this to be split i

Re: [HACKERS] [PATCH] SortSupport for macaddr type

On Sun, Mar 19, 2017 at 8:40 AM, Greg Stark wrote: >> Out of idle curiosity, I decided to generate disassembly of both >> macaddr_cmp_internal(), and the patch's abbreviated comparator. The >> former consists of 49 x86-64 instructions at -02 on my machine, >> totaling 135 bytes of object code. The

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Magnus Hagander (mag...@hagander.net) wrote: > >> On Sat, Mar 18, 2017 at 9:23 PM, Tom Lane wrote: > >>> I think a more reasonable way to proceed is to install symlinks > >>> pg_createuser -> createuser (or the other direction),

Re: [HACKERS] \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless)

Fabien COELHO wrote: > > Hello Corey, > > > v24 highlights: > > The v24 patch is twice larger that the previous submission. Sigh. The reason this is so large is that there is an entangled refactoring patch, splitting the exec_command() function from one giant switch() into one routine for each

Re: [HACKERS] \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless)

Hello Corey, v24 highlights: The v24 patch is twice larger that the previous submission. Sigh. If I'm reading headers correctly, it seems that it adds an "expected/psql-on-error-stop.out" file without a corresponding test source in "sql/". Is this file to be simply ignored, or is a source

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

Stephen Frost writes: > * Magnus Hagander (mag...@hagander.net) wrote: >> On Sat, Mar 18, 2017 at 9:23 PM, Tom Lane wrote: >>> I think a more reasonable way to proceed is to install symlinks >>> pg_createuser -> createuser (or the other direction), mark the older names >>> as deprecated, and anno

Re: [HACKERS] increasing the default WAL segment size

On 3/17/17 4:56 PM, Tom Lane wrote: Peter Eisentraut writes: On 3/17/17 16:20, Peter Eisentraut wrote: I think we would have to extend restore_command with an additional placeholder that communicates the segment size, and add a new pg_standby option to accept that size somehow. And specifyi

Re: [HACKERS] createlang/droplang deprecated

On 3/18/17 9:00 AM, Peter Eisentraut wrote: I just noticed that createlang and droplang have been listed as deprecated since PG 9.1. Do we dare remove them? +1 -- -David da...@pgmasters.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscri

Re: [HACKERS] Create replication slot in pg_basebackup if requested and not yet present

On Sun, Mar 19, 2017 at 11:21 AM, Michael Banck wrote: > Hi, > > with the default configuration improvements so far for 10, it seems to > be very easy to setup streaming replication (at least locally): > > $ initdb --pgdata=data1 > $ pg_ctl --pgdata=data1 start > $ pg_basebackup --pgdata=data2 --

Re: [HACKERS] [PATCH] SortSupport for macaddr type

On 18 March 2017 at 22:22, Peter Geoghegan wrote: > > Out of idle curiosity, I decided to generate disassembly of both > macaddr_cmp_internal(), and the patch's abbreviated comparator. The > former consists of 49 x86-64 instructions at -02 on my machine, > totaling 135 bytes of object code. The la

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

* Magnus Hagander (mag...@hagander.net) wrote: > On Sat, Mar 18, 2017 at 9:23 PM, Tom Lane wrote: > > Magnus Hagander writes: > > > createuser, dropuser - definitely pollutes the namespace, people do > > > sometimes try them for the wrong thing. Unlike the db ones they do add > > > value though -

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

On Sat, Mar 18, 2017 at 9:23 PM, Tom Lane wrote: > Magnus Hagander writes: > > createuser, dropuser - definitely pollutes the namespace, people do > > sometimes try them for the wrong thing. Unlike the db ones they do add > > value though -- I don't think we have a psql way of in a single comman

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

On Sun, Mar 19, 2017 at 1:44 PM, Andreas Karlsson wrote: > On 03/18/2017 09:12 PM, Magnus Hagander wrote: > >> createdb, dropdb - also not clear they're about postgres, more likely to >> be used by mistake but not that bad. That said, do they add any *value* >> beyond what you can do with psql -c

Re: [HACKERS] Logical decoding on standby

Hi, I don't know how well I can review the 0001 (the TAP infra patch) but it looks okay to me. I don't really have any complaints about 0002 either. I like that it's more or less one self-contained function and there are no weird ifdefs anymore like in 9.6 version (btw your commit message talks a

Re: [HACKERS] mat views stats

> > > But that seems pretty ugly. Given the lack of previous reports, I'm > personally content to leave this unfixed in the back branches. > > Comments? > > Most instances of this I've seen out in the field have worked around this by just running analyze in the scheduled jobs after the refresh so

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On 19/03/17 12:32, Pavel Stehule wrote: > > > 2017-03-18 19:30 GMT+01:00 Petr Jelinek >: > > On 16/03/17 17:15, David Steele wrote: > > On 2/1/17 3:59 PM, Pavel Stehule wrote: > >> Hi > >> > >> 2017-01-24 21:33 GMT+01:00 Pavel Stehule

Re: [HACKERS] logical decoding of two-phase transactions

On 17/03/17 03:34, Craig Ringer wrote: > On 17 March 2017 at 08:10, Stas Kelvich wrote: > >> While working on this i’ve spotted quite a nasty corner case with aborted >> prepared >> transaction. I have some not that great ideas how to fix it, but maybe i >> blurred my >> view and missed somethi

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

On 03/18/2017 09:12 PM, Magnus Hagander wrote: createdb, dropdb - also not clear they're about postgres, more likely to be used by mistake but not that bad. That said, do they add any *value* beyond what you can do with psql -c "CREATE DATABASE"? I don't really see one, so I'd suggest dropping th

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

On 03/18/2017 01:12 PM, Magnus Hagander wrote: Magnus Hagander mailto:mag...@hagander.net>> writes: 2017-03-18 14:00 GMT+01:00 Peter Eisentraut mailto:peter.eisentr...@2ndquadrant.com>>: createuser, dropuser - definitely pollutes the namespace, people do sometimes try them for the wrong thing

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-03-18 19:30 GMT+01:00 Petr Jelinek : > On 16/03/17 17:15, David Steele wrote: > > On 2/1/17 3:59 PM, Pavel Stehule wrote: > >> Hi > >> > >> 2017-01-24 21:33 GMT+01:00 Pavel Stehule >> >: > >> > >> Perhaps that's as simple as renaming all the existi

[HACKERS] Create replication slot in pg_basebackup if requested and not yet present

Hi, with the default configuration improvements so far for 10, it seems to be very easy to setup streaming replication (at least locally): $ initdb --pgdata=data1 $ pg_ctl --pgdata=data1 start $ pg_basebackup --pgdata=data2 --write-recovery-conf $ sed -i -e 's/^#port.=.5432/port = 5433/' \ > -e '

Re: [HACKERS] Logical decoding on standby

On 13 March 2017 at 10:56, Craig Ringer wrote: > On 7 March 2017 at 21:08, Simon Riggs wrote: > >> Patch 4 committed. Few others need rebase. > > Since this patch series Patch 1 fails since feature has already been applied. If other reason, let me know. -- Simon Riggshttp://www

Re: [HACKERS] PinBuffer() no longer makes use of strategy

On 4 February 2017 at 09:33, Andres Freund wrote: > For pretty much else that logic seems worse, > because it essentially prevents any buffers ever staying in s_b when > only ringbuffer accesses are performed. That was exactly its intent. The ringbuffer was designed to avoid cache spoiling by la

Re: [HACKERS] ANALYZE command progress checker

Hi, I didn't find any major issues with the patch. It works as expected. However, I have few minor comments that I would like to share, + + Total number of sample rows. The sample it reads is taken randomly. + Its size depends on the default_statistics_target parameter value. +

Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)

On Thu, Mar 16, 2017 at 12:53 PM, Robert Haas wrote: > On Wed, Mar 15, 2017 at 3:44 PM, Pavan Deolasee > wrote: > > I couldn't find a better way without a lot of complex infrastructure. > Even > > though we now have ability to mark index pointers and we know that a > given > > pointer either poi