Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread David Fetter
On Fri, Jul 07, 2017 at 10:29:26AM +0900, Amit Langote wrote: > Hi Mark, > > On 2017/07/07 9:02, Mark Kirkwood wrote: > > I've been trying out the new partitioning in version 10. Firstly, I must > > say this is excellent - so much nicer than the old inheritance based method! > > Thanks. :) > >

Re: [HACKERS] Revisiting NAMEDATALEN

2017-07-07 Thread Mark Dilger
> On Jul 7, 2017, at 2:53 AM, Emrul wrote: > > Tom, thank you for that pointer. I get now that it is not free and therefore > why its not something that should be changed by default. > > I guess the problem is 'build your own copy' (i.e. compiling from source) is > something

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread Robert Haas
On Fri, Jul 7, 2017 at 8:20 AM, Tom Lane wrote: > Robert Haas writes: >> I don't have a strong view on whether partitions should be hidden by >> default, although I lean slightly against it (say, -0.25). But if we >> do decide to hide them by default,

Re: [HACKERS] pg_stop_backup(wait_for_archive := true) on standby server

2017-07-07 Thread Masahiko Sawada
On Fri, Jul 7, 2017 at 3:48 PM, Michael Paquier wrote: > On Wed, Jul 5, 2017 at 4:57 PM, Michael Paquier > wrote: >> Why not refactoring a bit do_pg_stop_backup() so as the wait phase >> happens even if a backup is started in recovery? Now

Re: [HACKERS] Rust bindings to pgtypes lib

2017-07-07 Thread Kaare Rasmussen
I agree with Michael Meskes that duplicating the code is probably not a good idea. Now I see other rust crates just depend on the right libraries to be installed by the developer, so I'll punt the problem until it bites me for real. This confuses me, though > Indeed. I'm quite strongly

Re: [HACKERS] [WIP] Zipfian distribution in pgbench

2017-07-07 Thread Peter Geoghegan
On Fri, Jul 7, 2017 at 12:45 AM, Alik Khilazhev wrote: > On scale = 10(1 million rows) it gives following results on machine with 144 > cores(with synchronous_commit=off): > nclientstps > 1 8842.401870 > 2

Re: [HACKERS] SCRAM auth and Pgpool-II

2017-07-07 Thread Robert Haas
On Wed, Jul 5, 2017 at 9:32 PM, Michael Paquier wrote: > On Thu, Jul 6, 2017 at 10:03 AM, Tatsuo Ishii wrote: >> For Pgpool-II, things would go as follows: >> >> 1) clients sends user name to Pgpool-II. >> 2) Pgpool-II forwards it to PostgreSQL

Re: [HACKERS] SCRAM auth and Pgpool-II

2017-07-07 Thread David Fetter
On Thu, Jul 06, 2017 at 10:03:37AM +0900, Tatsuo Ishii wrote: > Hi PostgreSQL hackers, > > I would like to hear ideas how Pgpool-II can deal with SCRAM auth > which will be in PostgreSQL 10. > > For those who are not familiar with Pgpool-II[1], it is an external > OSS project to provide some

Re: [HACKERS] [WIP] Zipfian distribution in pgbench

2017-07-07 Thread Peter Geoghegan
On Fri, Jul 7, 2017 at 5:17 AM, Robert Haas wrote: > How is that possible? In a Zipfian distribution, no matter how big > the table is, almost all of the updates will be concentrated on a > handful of rows - and updates to any given row are necessarily > serialized, or so

Re: [HACKERS] [WIP] Zipfian distribution in pgbench

2017-07-07 Thread Alvaro Herrera
Peter Geoghegan wrote: > Here is the query: > > with recursive index_details as ( > select > 'pgbench_accounts_pkey'::text idx > ), [...] Hmm, this seems potentially very useful. Care to upload it to https://wiki.postgresql.org/wiki/Category:Snippets ? -- Álvaro Herrera

Re: [HACKERS] [WIP] Zipfian distribution in pgbench

2017-07-07 Thread Peter Geoghegan
On Fri, Jul 7, 2017 at 12:59 PM, Alvaro Herrera wrote: > Hmm, this seems potentially very useful. Care to upload it to > https://wiki.postgresql.org/wiki/Category:Snippets ? Sure. I've added it here, under "index maintenance":

Re: [HACKERS] More race conditions in logical replication

2017-07-07 Thread Alvaro Herrera
Petr Jelinek wrote: > So best idea I could come up with is to make use of the new condition > variable API. That lets us wait for variable which can be set per slot. > > It's not backportable however, I am not sure how big problem that is > considering the lack of complaints until now (maybe we

Re: [HACKERS] SCRAM auth and Pgpool-II

2017-07-07 Thread Stephen Frost
Tatsuo, * Tatsuo Ishii (is...@sraoss.co.jp) wrote: > > I recall vaguely Ishii-san mentioning me at PGcon that pgpool was > > actually cheating, but my memories are a bit fuzzy for this part. > > What I meant by "cheating" was, Pgpool-II behaves as if PostgreSQL > server in md5 auth. > > For

Re: [HACKERS] SCRAM auth and Pgpool-II

2017-07-07 Thread Stephen Frost
Michael, * Michael Paquier (michael.paqu...@gmail.com) wrote: > On Sat, Jul 8, 2017 at 1:24 AM, Robert Haas wrote: > > IIUC, things will get even worse once channel binding is committed, > > presumably for PostgreSQL 11. The point of channel binding is to > > guarantee

Re: [HACKERS] More race conditions in logical replication

2017-07-07 Thread Petr Jelinek
On 06/07/17 18:20, Petr Jelinek wrote: > On 06/07/17 17:33, Petr Jelinek wrote: >> On 03/07/17 01:54, Tom Lane wrote: >>> I noticed a recent failure that looked suspiciously like a race condition: >>> >>> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=hornet=2017-07-02%2018%3A02%3A07 >>>

Re: [HACKERS] More race conditions in logical replication

2017-07-07 Thread Tom Lane
Alvaro Herrera writes: > I'll next update this on or before Monday 10th at 19:00 CLT. Schedule note --- we'll be wrapping beta2 on Monday, a couple hours before that. Although it'd be great to have this issue fixed before beta2, jamming in a patch just a few hours

Re: [HACKERS] Rust bindings to pgtypes lib

2017-07-07 Thread Andres Freund
On 2017-07-07 12:54:33 +0200, Michael Meskes wrote: > > Some people use http://libpqtypes.esilo.com/ > > Never before saw this. It does not seem to have more in common than the > name, though. It has binary to text conversion functionality for various types. I don't exactly know what Kaare

Re: [HACKERS] SCRAM auth and Pgpool-II

2017-07-07 Thread Michael Paquier
On Sat, Jul 8, 2017 at 1:24 AM, Robert Haas wrote: > On Wed, Jul 5, 2017 at 9:32 PM, Michael Paquier > wrote: >> On Thu, Jul 6, 2017 at 10:03 AM, Tatsuo Ishii wrote: >>> For Pgpool-II, things would go as follows: >>> >>> 1)

[HACKERS] Subscription code improvements

2017-07-07 Thread Petr Jelinek
Hi, I have done some review of subscription handling (well self-review) and here is the result of that (It's slightly improved version from another thread [1]). I split it into several patches: 0001 - Makes subscription worker exit nicely when there is subscription missing (ie was removed while

Re: [HACKERS] SCRAM auth and Pgpool-II

2017-07-07 Thread Tatsuo Ishii
> I think it is. From a security point of view, the fact that the same > salt is always used for the same username is a weakness of md5 > authentication which SCRAM corrects. In my understanding, md5 does not always use the same salt for the same username. PostgreSQL keeps md5(password+username)

Re: [HACKERS] SCRAM auth and Pgpool-II

2017-07-07 Thread Tatsuo Ishii
> I recall vaguely Ishii-san mentioning me at PGcon that pgpool was > actually cheating, but my memories are a bit fuzzy for this part. What I meant by "cheating" was, Pgpool-II behaves as if PostgreSQL server in md5 auth. For more details what Pgpool-II actually does in md5 auth, please see:

Re: [HACKERS] hash index on unlogged tables doesn't behave as expected

2017-07-07 Thread Amit Kapila
On Sat, Jul 8, 2017 at 9:08 AM, Robert Haas wrote: > On Fri, Jul 7, 2017 at 11:36 PM, Amit Kapila wrote: >> I think we should do that as a separate patch (I can write the same as >> well) because that is not new behavior introduced by this patch,

Re: [HACKERS] SCRAM auth and Pgpool-II

2017-07-07 Thread Álvaro Hernández Tortosa
On 06/07/17 04:03, Tatsuo Ishii wrote: Hi PostgreSQL hackers, I would like to hear ideas how Pgpool-II can deal with SCRAM auth which will be in PostgreSQL 10. For those who are not familiar with Pgpool-II[1], it is an external OSS project to provide some additional features to PostgreSQL,

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread Mark Kirkwood
On 07/07/17 19:54, Michael Banck wrote: On Fri, Jul 07, 2017 at 07:40:55PM +1200, Mark Kirkwood wrote: On 07/07/17 13:29, Amit Langote wrote: Someone complained about this awhile back [1]. And then it came up again [2], where Noah appeared to take a stance that partitions should be visible

Re: [HACKERS] hash index on unlogged tables doesn't behave as expected

2017-07-07 Thread Amit Kapila
On Sat, Jul 8, 2017 at 8:52 AM, Robert Haas wrote: > On Thu, Jul 6, 2017 at 5:54 AM, Kyotaro HORIGUCHI > wrote: >> Check for INIT_FORKNUM appears both accompanied and not >> accompanied by check for RELPER.._UNLOGGED, so I'm not sure which

Re: [HACKERS] hash index on unlogged tables doesn't behave as expected

2017-07-07 Thread Robert Haas
On Fri, Jul 7, 2017 at 11:36 PM, Amit Kapila wrote: > I think we should do that as a separate patch (I can write the same as > well) because that is not new behavior introduced by this patch, ... True, although maybe hash indexes are the only way that happens today? --

Re: [HACKERS] SCRAM auth and Pgpool-II

2017-07-07 Thread Tatsuo Ishii
>> For more details what Pgpool-II actually does in md5 auth, please see: >> >> https://pgpool.net/mediawiki/index.php/FAQ#How_does_pgpool-II_handle_md5_authentication.3F > > I see. In short, that's not going to be able to work with SCRAM. That's my understanding with SCRAM too. > We also

Re: [HACKERS] hash index on unlogged tables doesn't behave as expected

2017-07-07 Thread Robert Haas
On Thu, Jul 6, 2017 at 5:54 AM, Kyotaro HORIGUCHI wrote: > Check for INIT_FORKNUM appears both accompanied and not > accompanied by check for RELPER.._UNLOGGED, so I'm not sure which > is the convention here. Checking only for INIT_FORKNUM seems logical to me.

Re: [HACKERS] hash index on unlogged tables doesn't behave as expected

2017-07-07 Thread Robert Haas
On Wed, Jul 5, 2017 at 11:02 PM, Noah Misch wrote: > [Action required within three days. This is a generic notification.] > > The above-described topic is currently a PostgreSQL 10 open item. Robert, > since you committed the patch believed to have created it, you own this

[HACKERS] Authentication mechanisms categorization

2017-07-07 Thread Álvaro Hernández Tortosa
There has been some prior discussion, that we recently continued at pgday.ru, about what to do if a client wants to use a "strong" authentication mechanism but a rogue server forces the client to use a weaker authentication mechanism. This is the case if the client expects SCRAM to be

Re: [HACKERS] Postgres process invoking exit resulting in sh-QUIT core

2017-07-07 Thread Craig Ringer
On 7 July 2017 at 15:41, K S, Sandhya (Nokia - IN/Bangalore) < sandhya@nokia.com> wrote: > Hi Craig, > > > > The scenario is lock and unlock of the system for 30 times. During this > scenario 5 sh-QUIT core is generated. GDB of 5 core is pointing to > different locations. > > I have attached

Re: [HACKERS] Multi column range partition table

2017-07-07 Thread Dean Rasheed
On 7 July 2017 at 03:21, Amit Langote wrote: > The patch looks generally good, although I found and fixed some minor > issues (typos and such). Please find attached the updated patch. > Thanks for the review. Those changes all look good. I also see that I missed

Re: [HACKERS] Revisiting NAMEDATALEN

2017-07-07 Thread Emrul
Tom, thank you for that pointer. I get now that it is not free and therefore why its not something that should be changed by default. I guess the problem is 'build your own copy' (i.e. compiling from source) is something that sends most DB teams running into the hills. A solution might be to

Re: [HACKERS] Oddity in error handling of constraint violation in ExecConstraints for partitioned tables

2017-07-07 Thread Amit Langote
Fujita-san, On 2017/07/06 16:06, Etsuro Fujita wrote: > Here is an example: > > postgres=# create table col_desc (a int, b int) partition by list (a); > postgres=# create table col_desc_1 partition of col_desc for values in (1); > postgres=# alter table col_desc_1 add check (b > 0); > postgres=#

Re: [HACKERS] Fix header comment of streamutil.c

2017-07-07 Thread Magnus Hagander
On Fri, Jul 7, 2017 at 8:31 AM, Masahiko Sawada wrote: > Hi, > > While reading source code, I found that the header comment of > streamutil.c is not correct. I guess pg_receivelog is a mistake of > pg_receivexlog and it's an oversight when changing xlog to wal. > >

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread Tom Lane
Robert Haas writes: > I don't have a strong view on whether partitions should be hidden by > default, although I lean slightly against it (say, -0.25). But if we > do decide to hide them by default, then I definitely want an > easy-to-use modifier that overrides that

Re: [HACKERS] Revisiting NAMEDATALEN

2017-07-07 Thread Tom Lane
Robert Haas writes: > On Fri, Jul 7, 2017 at 5:53 AM, Emrul wrote: >> A solution might be to make NAMEDATALEN configurable without having to >> recompile source (perhaps a config variable or an initdb parameter). When I >> have some free time I will

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread Simon Riggs
On 7 July 2017 at 13:20, Tom Lane wrote: > Robert Haas writes: >> I don't have a strong view on whether partitions should be hidden by >> default, although I lean slightly against it (say, -0.25). But if we >> do decide to hide them by default, then I

Re: [HACKERS] Revisiting NAMEDATALEN

2017-07-07 Thread Robert Haas
On Fri, Jul 7, 2017 at 5:53 AM, Emrul wrote: > Tom, thank you for that pointer. I get now that it is not free and therefore > why its not something that should be changed by default. > > I guess the problem is 'build your own copy' (i.e. compiling from source) is > something

Re: [HACKERS] Postgres process invoking exit resulting in sh-QUIT core

2017-07-07 Thread K S, Sandhya (Nokia - IN/Bangalore)
Hi Craig, The scenario is lock and unlock of the system for 30 times. During this scenario 5 sh-QUIT core is generated. GDB of 5 core is pointing to different locations. I have attached output for 2 such instance. Regards, Sandhya From: Craig Ringer [mailto:cr...@2ndquadrant.com] Sent:

Re: [HACKERS] [WIP] Zipfian distribution in pgbench

2017-07-07 Thread Robert Haas
On Fri, Jul 7, 2017 at 3:45 AM, Alik Khilazhev wrote: > PostgreSQL shows very bad results in YCSB Workload A (50% SELECT and 50% > UPDATE of random row by PK) on benchmarking with big number of clients using > Zipfian distribution. MySQL also has decline but it is

Re: [HACKERS] WIP Patch: Pgbench Serialization and deadlock errors

2017-07-07 Thread Alexander Korotkov
On Thu, Jun 15, 2017 at 10:16 PM, Andres Freund wrote: > On 2017-06-14 11:48:25 +0300, Marina Polyakova wrote: > > Advanced options: > > - mostly for testing built-in scripts: you can set the default > transaction > > isolation level by the appropriate benchmarking option

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread Robert Haas
On Fri, Jul 7, 2017 at 3:54 AM, Michael Banck wrote: > +1. > > Or maybe just 'partition' is enough if 'partition table' would widen the > column output unnecessarily. Internally to the source code, the parent is called a "partitioned table" and the child is called a

Re: [HACKERS] pgsql 10: hash indexes testing

2017-07-07 Thread Amit Kapila
On Fri, Jul 7, 2017 at 8:22 AM, AP wrote: > On Thu, Jul 06, 2017 at 05:19:59PM +0530, Amit Kapila wrote: >> I think if you are under development, it is always advisable to create >> indexes after initial bulk load. That way it will be faster and will >> take lesser space atleast

Re: [HACKERS] Rust bindings to pgtypes lib

2017-07-07 Thread Michael Meskes
> Indeed. I'm quite strongly against exposing / using pgtypeslib in more > places. If anything it should be phased out. Because that code is Feel free to come up with a replacement. :) > definitely not always kept up2date, and it's a lot of work to do so. If > anything the code should be

Re: [HACKERS] [WIP] Zipfian distribution in pgbench

2017-07-07 Thread Fabien COELHO
Hello Alik, PostgreSQL shows very bad results in YCSB Workload A (50% SELECT and 50% UPDATE of random row by PK) on benchmarking with big number of clients using Zipfian distribution. MySQL also has decline but it is not significant as it is in PostgreSQL. MongoDB does not have decline at

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread Simon Riggs
On 7 July 2017 at 08:54, Michael Banck wrote: > On Fri, Jul 07, 2017 at 07:40:55PM +1200, Mark Kirkwood wrote: >> On 07/07/17 13:29, Amit Langote wrote: >> >Someone complained about this awhile back [1]. And then it came up again >> >[2], where Noah appeared to take a

Re: [HACKERS] WIP patch: distinguish selectivity of < from <= and > from >=

2017-07-07 Thread Tom Lane
I wrote: > I looked at that again and realized that one of the answers I was missing > lies in the behavior of analyze.c's compute_scalar_stats(). I updated the patch's comments based on this. I'll just attach the selfuncs.c part of the patch, since nothing else changed.

Re: [HACKERS] pg_stop_backup(wait_for_archive := true) on standby server

2017-07-07 Thread Michael Paquier
On Wed, Jul 5, 2017 at 4:57 PM, Michael Paquier wrote: > Why not refactoring a bit do_pg_stop_backup() so as the wait phase > happens even if a backup is started in recovery? Now wait_for_archive > is ignored because no wait is happening and the stop point is directly >

Re: [HACKERS] pg_stop_backup(wait_for_archive := true) on standby server

2017-07-07 Thread Masahiko Sawada
On Wed, Jul 5, 2017 at 4:57 PM, Michael Paquier wrote: > On Wed, Jul 5, 2017 at 10:19 AM, Masahiko Sawada > wrote: >> I feel that since we cannot switch the WAL forcibly on the standby >> server we need to find a new way to do so. I'm not sure

Re: [HACKERS] Race conditions with WAL sender PID lookups

2017-07-07 Thread Michael Paquier
On Tue, Jul 4, 2017 at 1:34 PM, Noah Misch wrote: > Bundling code cleanup into commits that also do something else is strictly > worse than bundling whitespace cleanup, which is itself bad: > https://postgr.es/m/flat/20160113144826.gb3379...@tornado.leadboat.com FWIW, I agree

Re: [HACKERS] pg_stop_backup(wait_for_archive := true) on standby server

2017-07-07 Thread Michael Paquier
On Fri, Jul 7, 2017 at 4:06 PM, Masahiko Sawada wrote: > I agree with this idea. I've tried to make it wait for archiving but > it seems to me that there are other two issues we need to deal with: > the timeline ID on standby server is always reset after created a > restart

Re: [HACKERS] Postgres process invoking exit resulting in sh-QUIT core

2017-07-07 Thread Craig Ringer
On 7 July 2017 at 15:10, K S, Sandhya (Nokia - IN/Bangalore) < sandhya@nokia.com> wrote: > Hi Craig, > > > > You were right about the restore_command. > This all makes sense then. PostgreSQL sends SIGQUIT for immediate shutdown to its children. So the restore_command would get signalled

Re: [HACKERS] Error while copying a large file in pg_rewind

2017-07-07 Thread Kuntal Ghosh
On Fri, Jul 7, 2017 at 7:49 AM, Michael Paquier wrote: > On Thu, Jul 6, 2017 at 8:51 PM, Dilip Kumar wrote: >> On Thu, Jul 6, 2017 at 4:18 PM, Kuntal Ghosh >> wrote: >>> But, I'm little concerned/doubt regarding the

Re: [HACKERS] Error while copying a large file in pg_rewind

2017-07-07 Thread Michael Paquier
On Fri, Jul 7, 2017 at 4:31 PM, Kuntal Ghosh wrote: > On Fri, Jul 7, 2017 at 7:49 AM, Michael Paquier > wrote: > I don't have any more inputs on this patch and it looks good to me. > So, I'm moving the status to ready for committer. Thanks!

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread Mark Kirkwood
On 07/07/17 13:29, Amit Langote wrote: Someone complained about this awhile back [1]. And then it came up again [2], where Noah appeared to take a stance that partitions should be visible in views / output of commands that list "tables". Although I too tend to prefer not filling up the \d

[HACKERS] [WIP] Zipfian distribution in pgbench

2017-07-07 Thread Alik Khilazhev
Hello! PostgreSQL shows very bad results in YCSB Workload A (50% SELECT and 50% UPDATE of random row by PK) on benchmarking with big number of clients using Zipfian distribution. MySQL also has decline but it is not significant as it is in PostgreSQL. MongoDB does not have decline at all. And

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread Michael Banck
On Fri, Jul 07, 2017 at 07:40:55PM +1200, Mark Kirkwood wrote: > On 07/07/17 13:29, Amit Langote wrote: > >Someone complained about this awhile back [1]. And then it came up again > >[2], where Noah appeared to take a stance that partitions should be > >visible in views / output of commands that

Re: [HACKERS] pg_stop_backup(wait_for_archive := true) on standby server

2017-07-07 Thread Masahiko Sawada
On Fri, Jul 7, 2017 at 4:21 PM, Michael Paquier wrote: > On Fri, Jul 7, 2017 at 4:06 PM, Masahiko Sawada wrote: >> I agree with this idea. I've tried to make it wait for archiving but >> it seems to me that there are other two issues we need to