Re: [HACKERS] pg_basebackups and slots

2016-12-15 Thread Feike Steenbergen
> but -X stream is, then we use a temporary slot always. This seems even more useful with -X fetch to me, as with fetch we are sure we are not immediately receiving the WAL. So, I'd propose to use it whenever -X is specified, regardless of which method is specified. > (I still think we should

Re: pg_authid.rolpassword format (was Re: [HACKERS] Password identifiers, protocol aging and SCRAM protocol)

2016-12-15 Thread Stephen Frost
* Heikki Linnakangas (hlinn...@iki.fi) wrote: > On 12/14/2016 04:57 PM, Stephen Frost wrote: > >* Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote: > >>On 12/14/16 5:15 AM, Michael Paquier wrote: > >>>I would be tempted to suggest adding the verifier type as a new column > >>>of pg_authid

Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages] [and 1 more messages]

2016-12-15 Thread Kevin Grittner
On Thu, Dec 15, 2016 at 6:09 AM, Ian Jackson wrote: > However, in that example, as you seem to allude to, there is still a > complete serialisation of all the transactions, even the failed T3: > T1,T2,T3. The database has detected the problem before returning data >

Re: [HACKERS] Logical Replication WIP

2016-12-15 Thread Petr Jelinek
On 15/12/16 13:06, Craig Ringer wrote: > On 15 Dec. 2016 18:19, "Petr Jelinek" > wrote: > > On 13/12/16 21:42, Peter Eisentraut wrote: > > On 12/10/16 2:48 AM, Petr Jelinek wrote: > >> Attached new version with your

Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages] [and 1 more messages]

2016-12-15 Thread Kevin Grittner
On Thu, Dec 15, 2016 at 9:53 AM, Ian Jackson wrote: > I don't think "set max_pred_locks_per_transaction generously" is a > practical thing to write in the documentation, because the application > programmer, or admin, has no sensible way to calculate what a >

Re: [HACKERS] Re: [COMMITTERS] pgsql: Build HTML documentation using XSLT stylesheets by default

2016-12-15 Thread Tom Lane
Alexander Law writes: > Hello Alvaro, > It's caused by the condition > ... > in the simple.xlink template > (docbook/stylesheet/docbook-xsl/xhtml/inline.xsl). (This test executed > for each xlink (~ 9 times)). > Yes, it's inefficient but it doesn't affect build time

Re: [HACKERS] Hash Indexes

2016-12-15 Thread Amit Kapila
On Wed, Dec 14, 2016 at 10:47 PM, Robert Haas wrote: > On Wed, Dec 14, 2016 at 4:27 AM, Amit Kapila wrote: >> >> Yeah, it will fix the problem in hashbucketcleanup, but there are two >> other problems that need to be fixed for which I can send a

[HACKERS] Proposal : composite type not null constraints

2016-12-15 Thread Wesley Massuda
I would like to propose extending composite types with constraints. Currently there is a preliminar patch for not null constraints. === Changes : -Use the parser from create table to get the constraints 'OptTableElementList' instead of 'OptTableFuncElementList'. - Add a new transformation

Re: [HACKERS] Missing newlines in error messages

2016-12-15 Thread Tom Lane
Magnus Hagander writes: > While poking around the pg_basebackup code, I noticed there are a lot of > error messages are missing the \n at the end. Most of them are very > unlikely to happen, but in general I believe all error messages we print to > stderr from those binaries

Re: [HACKERS] Proposal : composite type not null constraints

2016-12-15 Thread Tom Lane
Wesley Massuda writes: > I would like to propose extending composite types with constraints. I'm not convinced we should go there, *particularly* not for not-null constraints. Data-type-related not-null constraints are broken by design, because they are fundamentally

[HACKERS] Missing newlines in error messages

2016-12-15 Thread Magnus Hagander
While poking around the pg_basebackup code, I noticed there are a lot of error messa -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/

Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages] [and 1 more messages]

2016-12-15 Thread Ian Jackson
Kevin Grittner writes ("Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages] [and 1 more messages]"): > On Thu, Dec 15, 2016 at 6:09 AM, Ian Jackson > wrote: > > [...] Are there other reasons, > > besides previously

Re: [HACKERS] Missing newlines in error messages

2016-12-15 Thread Magnus Hagander
(oops, accidental send button press) While poking around the pg_basebackup code, I noticed there are a lot of error messages are missing the \n at the end. Most of them are very unlikely to happen, but in general I believe all error messages we print to stderr from those binaries should have a

Re: [HACKERS] Declarative partitioning - another take

2016-12-15 Thread Dmitry Ivanov
Hi everyone, Looks like "sql_inheritance" GUC is affecting partitioned tables: explain (costs off) select * from test; QUERY PLAN -- Append -> Seq Scan on test -> Seq Scan on test_1 -> Seq Scan on test_2 -> Seq Scan on test_1_1 ->

Re: [HACKERS] Missing newlines in error messages

2016-12-15 Thread Magnus Hagander
On Thu, Dec 15, 2016 at 4:38 PM, Tom Lane wrote: > Magnus Hagander writes: > > While poking around the pg_basebackup code, I noticed there are a lot of > > error messages are missing the \n at the end. Most of them are very > > unlikely to happen, but in

Re: [HACKERS] postgres_fdw bug in 9.6

2016-12-15 Thread Tom Lane
Etsuro Fujita writes: > On 2016/12/13 23:13, Ashutosh Bapat wrote: >> On Tue, Dec 13, 2016 at 4:15 AM, Tom Lane wrote: >>> One way that we could make things better is to rely on the knowledge >>> that EPQ isn't asked to evaluate joins for more

Re: [HACKERS] Slow I/O can break throttling of base backup

2016-12-15 Thread Antonin Houska
It seems to be my bug. I'll check tomorrow. Magnus Hagander wrote: > Running pg_basebackup with a throttling of say 10M runs it into the risk of > the I/O on the server actually being slower than pg_basebackup (I have > preproduced similar issues on fake-slow disks with

Re: [HACKERS] Make pg_basebackup -x stream the default

2016-12-15 Thread Magnus Hagander
On Thu, Dec 15, 2016 at 7:36 PM, Joshua D. Drake wrote: > On 12/15/2016 10:23 AM, Peter Eisentraut wrote: > >> On 11/8/16 12:45 PM, Magnus Hagander wrote: >> >>> Per some discussions with a number of different people at pgconfeu, here >>> is a patch that changes the

Re: [HACKERS] WIP: Faster Expression Processing and Tuple Deforming (including JIT)

2016-12-15 Thread Bruce Momjian
On Tue, Dec 6, 2016 at 11:10:59AM -0800, Andres Freund wrote: > > I concur with your feeling that hand-rolled JIT is right out. But > > I'm not sure that whatever performance gain we might get in this > > direction is worth the costs. > > Well, I'm not impartial, but I don't think we do our

Re: [HACKERS] Make pg_basebackup -x stream the default

2016-12-15 Thread Magnus Hagander
On Thu, Dec 15, 2016 at 7:23 PM, Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > On 11/8/16 12:45 PM, Magnus Hagander wrote: > > Per some discussions with a number of different people at pgconfeu, here > > is a patch that changes the default mode of pg_basebackup to be > > streaming

Re: [HACKERS] Make pg_basebackup -x stream the default

2016-12-15 Thread Peter Eisentraut
On 12/15/16 1:27 PM, Magnus Hagander wrote: > On Thu, Dec 15, 2016 at 7:23 PM, Peter Eisentraut > > wrote: > > On 11/8/16 12:45 PM, Magnus Hagander wrote: > > Per some discussions with a number of different

Re: [HACKERS] Make pg_basebackup -x stream the default

2016-12-15 Thread Peter Eisentraut
On 11/8/16 12:45 PM, Magnus Hagander wrote: > Per some discussions with a number of different people at pgconfeu, here > is a patch that changes the default mode of pg_basebackup to be > streaming the wal, as this is what most users would want -- and those > that don't want it have to make other

Re: [HACKERS] Proposal for changes to recovery.conf API

2016-12-15 Thread Magnus Hagander
On Thu, Dec 15, 2016 at 1:11 AM, Bruce Momjian wrote: > On Wed, Dec 14, 2016 at 02:29:07PM -0800, Josh Berkus wrote: > > On 12/14/2016 08:06 AM, Bruce Momjian wrote: > > > On Fri, Dec 9, 2016 at 09:46:44AM +0900, Michael Paquier wrote: > > My own take on it is that the

Re: [HACKERS] Make pg_basebackup -x stream the default

2016-12-15 Thread Joshua D. Drake
On 12/15/2016 10:23 AM, Peter Eisentraut wrote: On 11/8/16 12:45 PM, Magnus Hagander wrote: Per some discussions with a number of different people at pgconfeu, here is a patch that changes the default mode of pg_basebackup to be streaming the wal, as this is what most users would want -- and

Re: [HACKERS] Proposal for changes to recovery.conf API

2016-12-15 Thread Josh Berkus
On 12/15/2016 12:54 PM, Tom Lane wrote: > Magnus Hagander writes: >> On Thu, Dec 15, 2016 at 1:11 AM, Bruce Momjian wrote: >>> You are saying this is more massive than any other change we have made >>> in the past? In general, what need to be documented? >

Re: [HACKERS] postgres_fdw bug in 9.6

2016-12-15 Thread Etsuro Fujita
On 2016/12/16 1:39, Tom Lane wrote: Etsuro Fujita writes: On 2016/12/13 23:13, Ashutosh Bapat wrote: A possible short-term fix may be this: instead of picking any random path to stick into fdw_outerpath, we choose a path which covers the pathkeys of ForeignPath.

Re: [HACKERS] [sqlsmith] Failed assertion in TS_phrase_execute

2016-12-15 Thread Tom Lane
I wrote: >> but I notice that some normalization seems to be getting done by >> tsqueryin: >> regression=# select $$( 'sanct' & 'peter' ) <-> ( 'sanct' & 'peter' >> )$$::tsquery; >> tsquery >>

Re: pg_authid.rolpassword format (was Re: [HACKERS] Password identifiers, protocol aging and SCRAM protocol)

2016-12-15 Thread Michael Paquier
On Thu, Dec 15, 2016 at 9:48 PM, Heikki Linnakangas wrote: > The only way to distinguish, is to know about every verifier kind there is, > and check whether rolpassword looks valid as anything else than a plaintext > password. And we already got tripped by a bug-of-omission on

Re: [HACKERS] New design for FK-based join selectivity estimation

2016-12-15 Thread Tom Lane
I wrote: > ronan.dunk...@dalibo.com writes: >> If I understand it correctly and the above is right, I think we should ignore >> SEMI or ANTI joins altogether when considering FKs, and keep the >> corresponding >> restrictinfos for later processing since they are already special-cased later >> on.

Re: [HACKERS] Proposal for changes to recovery.conf API

2016-12-15 Thread Tom Lane
Magnus Hagander writes: > On Thu, Dec 15, 2016 at 1:11 AM, Bruce Momjian wrote: >> You are saying this is more massive than any other change we have made >> in the past? In general, what need to be documented? > I don't necessarily think it's because it's

Re: [HACKERS] Crash on promotion when recovery.conf is renamed

2016-12-15 Thread Michael Paquier
On Thu, Dec 15, 2016 at 11:25:10AM +0200, Heikki Linnakangas wrote: > On 12/15/2016 10:44 AM, Magnus Hagander wrote: > > I wonder if there might be more corner cases like this, but in this > > particular one it seems easy enough to just say that failing to rename > > recovery.conf because it

Re: [HACKERS] pg_basebackups and slots

2016-12-15 Thread Magnus Hagander
On Dec 16, 2016 07:27, "Michael Paquier" wrote: On Thu, Dec 15, 2016 at 7:28 PM, Magnus Hagander wrote: > So here's a patch that does this, for discussion. It implements the > following behavior for -X: > > * When used with <10.0 servers, behave

Re: [HACKERS] pg_basebackups and slots

2016-12-15 Thread Michael Paquier
On Thu, Dec 15, 2016 at 7:28 PM, Magnus Hagander wrote: > So here's a patch that does this, for discussion. It implements the > following behavior for -X: > > * When used with <10.0 servers, behave just like before. > * When -S is specified, behave just like before (use an

Re: [HACKERS] Quorum commit for multiple synchronous replication.

2016-12-15 Thread Michael Paquier
On Thu, Dec 15, 2016 at 6:08 PM, Masahiko Sawada wrote: > Attached latest v12 patch. > I changed behavior of "N (standby_list)" to use the priority method > and incorporated some review comments so far. Please review it. Some comments... +Another example of

Re: [HACKERS] pg_basebackups and slots

2016-12-15 Thread Abhijit Menon-Sen
At 2016-12-16 07:32:24 +0100, mag...@hagander.net wrote: > > I really think the default should be "what most people want", and not > "whatever is compatible with a mode that was necessary because we > lacked infrastructure". Very much agreed. -- Abhijit -- Sent via pgsql-hackers mailing list

Re: [HACKERS] [PROPOSAL] Temporal query processing with range types

2016-12-15 Thread David Fetter
On Wed, Dec 07, 2016 at 03:57:33PM +0100, Peter Moser wrote: > Am 05.12.2016 um 06:11 schrieb Haribabu Kommi: > > > > > > On Tue, Oct 25, 2016 at 8:44 PM, Peter Moser > > wrote: > > > > > > We decided to follow your recommendation and add

Re: [HACKERS] pg_basebackups and slots

2016-12-15 Thread Michael Paquier
On Fri, Dec 16, 2016 at 3:32 PM, Magnus Hagander wrote: > I don't really know how to write a good test for it. I mean, we could run it > with the parameter, but how to we make it actually verify the slot? Make a > really big database to make it guaranteed to be slow enough

Re: [HACKERS] Transaction oddity with list partition of a list partition

2016-12-15 Thread David Fetter
On Thu, Dec 15, 2016 at 06:20:04PM +0900, Amit Langote wrote: > > Hi David, > > On 2016/12/15 18:09, David Fetter wrote: > > Per Thomas Munro, could it be that the CREATE ... PARTITION OF ... > > code fails to run CacheInvalidateRelcache on its parent(s)? > > Thomas's right. There is a patch

[HACKERS] Slow I/O can break throttling of base backup

2016-12-15 Thread Magnus Hagander
Running pg_basebackup with a throttling of say 10M runs it into the risk of the I/O on the server actually being slower than pg_basebackup (I have preproduced similar issues on fake-slow disks with lower rate limits). What happens in this case in basebackup.c is that the value for "sleep" comes

Re: [HACKERS] pg_basebackups and slots

2016-12-15 Thread Magnus Hagander
On Thu, Dec 15, 2016 at 10:26 AM, Magnus Hagander wrote: > > > On Thu, Dec 15, 2016 at 10:04 AM, Magnus Hagander > wrote: > >> I've started work on a patch to make pg_basebackup use the temporary >> slots feature that has been committed (thanks Petr!!).

Re: [HACKERS] Logical Replication WIP

2016-12-15 Thread Petr Jelinek
On 13/12/16 21:42, Peter Eisentraut wrote: > On 12/10/16 2:48 AM, Petr Jelinek wrote: >> Attached new version with your updates and rebased on top of the current >> HEAD (the partitioning patch produced quite a few conflicts). > > I have attached a few more "fixup" patches, mostly with some

Re: [HACKERS] [PATCH] Rename pg_switch_xlog to pg_switch_wal

2016-12-15 Thread Vladimir Rusinov
On Wed, Dec 14, 2016 at 11:51 PM, Michael Paquier wrote: > > On Wed, Dec 14, 2016 at 4:07 PM, Peter Eisentraut > > wrote: > > Others will follow later in separate patches. Or is it preferred to have > one > > huge patch submitted? >

Re: [HACKERS] postgres_fdw bug in 9.6

2016-12-15 Thread Etsuro Fujita
On 2016/12/13 23:13, Ashutosh Bapat wrote: On Tue, Dec 13, 2016 at 4:15 AM, Tom Lane wrote: I believe there are probably more problems here, or at least if there aren't, it's not clear why not. Because of GetExistingLocalJoinPath's lack of curiosity about what's underneath

Re: pg_authid.rolpassword format (was Re: [HACKERS] Password identifiers, protocol aging and SCRAM protocol)

2016-12-15 Thread Heikki Linnakangas
On 12/14/2016 04:57 PM, Stephen Frost wrote: * Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote: On 12/14/16 5:15 AM, Michael Paquier wrote: I would be tempted to suggest adding the verifier type as a new column of pg_authid Yes please. This discussion seems to continue to come up

Re: [HACKERS] Transaction oddity with list partition of a list partition

2016-12-15 Thread Amit Langote
Hi David, On 2016/12/15 18:09, David Fetter wrote: > Per Thomas Munro, could it be that the CREATE ... PARTITION OF ... code > fails to run CacheInvalidateRelcache on its parent(s)? Thomas's right. There is a patch posted for this issue [1]; I'm sending an updated version of the patch later

Re: [HACKERS] pg_basebackups and slots

2016-12-15 Thread Magnus Hagander
On Thu, Dec 15, 2016 at 10:04 AM, Magnus Hagander wrote: > I've started work on a patch to make pg_basebackup use the temporary slots > feature that has been committed (thanks Petr!!). The reason for this is to > avoid the cases where a burst of traffic on the master during

Re: [HACKERS] Crash on promotion when recovery.conf is renamed

2016-12-15 Thread Heikki Linnakangas
On 12/15/2016 10:44 AM, Magnus Hagander wrote: I wonder if there might be more corner cases like this, but in this particular one it seems easy enough to just say that failing to rename recovery.conf because it didn't exist is safe. Yeah. It's unexpected though, so I think erroring out is

[HACKERS] new set of psql patches for loading (saving) data from (to) text, binary files

2016-12-15 Thread Pavel Stehule
Hi I am sending set of patches - for simpler testing these patches are independent in this moment. These patches are replacement of my previous patches in this area: COPY RAW and fileref variables. 1. parametrized queries support - the psql variables can be passed as query parameters 2.

Re: [HACKERS] Logical Replication WIP

2016-12-15 Thread Petr Jelinek
On 13/12/16 01:33, Andres Freund wrote: > > On 2016-12-12 09:18:48 -0500, Peter Eisentraut wrote: >> On 12/8/16 4:10 PM, Petr Jelinek wrote: >>> On 08/12/16 20:16, Peter Eisentraut wrote: On 12/6/16 11:58 AM, Peter Eisentraut wrote: > On 12/5/16 6:24 PM, Petr Jelinek wrote: >> I

Re: [HACKERS] Hang in pldebugger after git commit : 98a64d0

2016-12-15 Thread Amit Kapila
On Thu, Dec 15, 2016 at 10:04 AM, Andres Freund wrote: > Hi, > > On 2016-12-12 16:46:38 +0900, Michael Paquier wrote: >> Ashutosh, could you try it and see if it improves things? > > So what's the theory of why this triggers pldebugger hangs, but > apparently causes not many

[HACKERS] Transaction oddity with list partition of a list partition

2016-12-15 Thread David Fetter
Folks, I'm having some trouble understanding what's going on here. When I \i the file in 55caaaeba877eac1feb6481fb413fa04ae9046ac without starting a transaction explicitly, it produces the expected results. When I \i it after a BEGIN, not so much. What's going on? Best, David.

[HACKERS] Crash on promotion when recovery.conf is renamed

2016-12-15 Thread Magnus Hagander
I had a system where the recovery.conf file was renamed "out of the way" at some point, and then the system was promoted. This is obviously operator error, but it seems like something we should handle. What happens now is that the non-existance of recovery.conf is a FATAL error. I wonder if it

Re: [HACKERS] Quorum commit for multiple synchronous replication.

2016-12-15 Thread Masahiko Sawada
On Thu, Dec 15, 2016 at 3:06 PM, Kyotaro HORIGUCHI wrote: > At Thu, 15 Dec 2016 14:20:53 +0900, Masahiko Sawada > wrote in >> On Thu, Dec 15, 2016 at 11:23 AM, Michael

Re: [HACKERS] Transaction oddity with list partition of a list partition

2016-12-15 Thread David Fetter
On Thu, Dec 15, 2016 at 12:23:24AM -0800, David Fetter wrote: > Folks, > > I'm having some trouble understanding what's going on here. When I \i > the file in 55caaaeba877eac1feb6481fb413fa04ae9046ac without starting > a transaction explicitly, it produces the expected results. When I \i > it

[HACKERS] Re: [COMMITTERS] pgsql: Build HTML documentation using XSLT stylesheets by default

2016-12-15 Thread Alexander Law
Hello Alvaro, It's caused by the condition ... in the simple.xlink template (docbook/stylesheet/docbook-xsl/xhtml/inline.xsl). (This test executed for each xlink (~ 9 times)). Yes, it's inefficient but it doesn't affect build time (for me). You can try to apply the attached patch and

[HACKERS] bigint vs txid user confusion

2016-12-15 Thread Craig Ringer
Hi all I recently had a real world case of a user confused by the (non)relationship between txid_current()'s output and that of the xid type, specifically pg_stat_replication.backend_xmin . I quote: " > What should we look for to determine normal? I thought maybe it would > compare to

[HACKERS] pg_basebackups and slots

2016-12-15 Thread Magnus Hagander
I've started work on a patch to make pg_basebackup use the temporary slots feature that has been committed (thanks Petr!!). The reason for this is to avoid the cases where a burst of traffic on the master during the backup can cause the receive log part of the basebackup to fall far enough behind

Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages] [and 1 more messages]

2016-12-15 Thread Ian Jackson
Kevin Grittner writes ("Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages] [and 1 more messages]"): > As Robert pointed out, a read-only transaction cannot normally be > aborted by a serialization failure on COMMIT. Under exceptional >

Re: pg_authid.rolpassword format (was Re: [HACKERS] Password identifiers, protocol aging and SCRAM protocol)

2016-12-15 Thread Heikki Linnakangas
On 12/15/2016 03:00 AM, Michael Paquier wrote: On Wed, Dec 14, 2016 at 8:33 PM, Heikki Linnakangas wrote: But, a password stored in plaintext works with either MD5 or SCRAM, or any future authentication mechanism. So as soon as we have SCRAM authentication, it becomes somewhat

Re: [HACKERS] Logical Replication WIP

2016-12-15 Thread Craig Ringer
On 15 Dec. 2016 18:19, "Petr Jelinek" wrote: On 13/12/16 21:42, Peter Eisentraut wrote: > On 12/10/16 2:48 AM, Petr Jelinek wrote: >> Attached new version with your updates and rebased on top of the current >> HEAD (the partitioning patch produced quite a few

Re: [HACKERS] proposal: session server side variables

2016-12-15 Thread Pavel Stehule
Hi Most important features: >> >> 1. the values are stored in native types >> 2. access to content is protected by ACL - like the content of tables >> 3. the content is not MVCC based - no any cost of UPDATE >> 4. simple API allows access to content of variables from any supported >> environment.