[HACKERS] Authentication mechanisms categorization

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] New partitioning - some feedback

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] SCRAM auth and Pgpool-II

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] hash index on unlogged tables doesn't behave as expected

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] hash index on unlogged tables doesn't behave as expected

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] hash index on unlogged tables doesn't behave as expected

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

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

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

Re: [HACKERS] SCRAM auth and Pgpool-II

>> 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] SCRAM auth and Pgpool-II

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

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] Rust bindings to pgtypes lib

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

> 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] SCRAM auth and Pgpool-II

> 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

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)

Re: [HACKERS] More race conditions in logical replication

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] More race conditions in logical replication

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] [WIP] Zipfian distribution in pgbench

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

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 >>>

[HACKERS] Subscription code improvements

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] [WIP] Zipfian distribution in pgbench

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

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] [WIP] Zipfian distribution in pgbench

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] SCRAM auth and Pgpool-II

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] SCRAM auth and Pgpool-II

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] New partitioning - some feedback

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] Rust bindings to pgtypes lib

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] pg_stop_backup(wait_for_archive := true) on standby server

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] Revisiting NAMEDATALEN

> 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

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] WIP patch: distinguish selectivity of < from <= and > from >=

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] New partitioning - some feedback

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

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] WIP Patch: Pgbench Serialization and deadlock errors

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] pgsql 10: hash indexes testing

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] New partitioning - some feedback

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] [WIP] Zipfian distribution in pgbench

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] New partitioning - some feedback

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] Fix header comment of streamutil.c

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

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] Zipfian distribution in pgbench

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] Postgres process invoking exit resulting in sh-QUIT core

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] Revisiting NAMEDATALEN

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] Rust bindings to pgtypes lib

> 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] Revisiting NAMEDATALEN

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

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] Multi column range partition table

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] Postgres process invoking exit resulting in sh-QUIT core

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] pg_stop_backup(wait_for_archive := true) on standby server

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

Re: [HACKERS] New partitioning - some feedback

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

[HACKERS] [WIP] Zipfian distribution in pgbench

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

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

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

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] Error while copying a large file in pg_rewind

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] Postgres process invoking exit resulting in sh-QUIT core

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] pg_stop_backup(wait_for_archive := true) on standby server

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] pg_stop_backup(wait_for_archive := true) on standby server

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] pg_stop_backup(wait_for_archive := true) on standby server

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] Race conditions with WAL sender PID lookups

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