Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

2017-05-25 Thread Regina Obe
> Did something change with how exclusion constraints are handled? I'm trying to troubleshoot a regression we are having with PostGIS raster support. > As best I can guess, it's because exclusion constraints that used to work in past versions are failing in PostgreSQL 10 with an error something

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

2017-05-25 Thread Robert Haas
On Thu, May 25, 2017 at 5:06 PM, Peter Eisentraut wrote: > They are the same cases. > > a) Create object in information_schema. > > b) Create another object elsewhere that depends on it. > > c) pg_dump will dump (b) but not (a). > > So the fix, if any, would be

Re: retry shm attach for windows (WAS: Re: [HACKERS] OK, so culicidae is *still* broken)

2017-05-25 Thread Amit Kapila
On Thu, May 25, 2017 at 8:01 PM, Tom Lane wrote: > Amit Kapila writes: >> Yes, I also share this opinion, the shm attach failures are due to >> randomization behavior, so sleep won't help much. So, I will change >> the patch to use 100 retries unless

Re: retry shm attach for windows (WAS: Re: [HACKERS] OK, so culicidae is *still* broken)

2017-05-25 Thread Tsunakawa, Takayuki
From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Amit Kapila > Yes, I also share this opinion, the shm attach failures are due to > randomization behavior, so sleep won't help much. So, I will change the > patch to use 100 retries unless people

Re: [HACKERS] Create subscription with `create_slot=false` and incorrect slot name

2017-05-25 Thread Petr Jelinek
On 25/05/17 23:26, Peter Eisentraut wrote: > On 5/24/17 21:41, Robert Haas wrote: >>> This came up in a previous thread. It is up to the publishing end what >>> slot names it accepts. So running the validation locally is incorrect. >> >> That argument seems pretty tenuous; surely both ends are

Re: [HACKERS] No parameter values checking while creating Alter subscription...Connection

2017-05-25 Thread Petr Jelinek
On 25/05/17 23:18, Andres Freund wrote: > On 2017-05-25 17:08:57 -0400, Peter Eisentraut wrote: >> On 5/25/17 10:18, Masahiko Sawada wrote: postgres=# alter subscription c1 connection 'port=4000'; ALTER SUBSCRIPTION postgres=# alter subscription c1 connection 'dbname=cc'; ALTER

Re: [HACKERS] Create subscription with `create_slot=false` and incorrect slot name

2017-05-25 Thread Peter Eisentraut
On 5/24/17 21:41, Robert Haas wrote: >> This came up in a previous thread. It is up to the publishing end what >> slot names it accepts. So running the validation locally is incorrect. > > That argument seems pretty tenuous; surely both ends are PostgreSQL, > and the rules for valid slot names

Re: [HACKERS] Renaming a table to an array's autogenerated name

2017-05-25 Thread Tom Lane
Vik Fearing writes: > In commit 9aa3c782c93, Tom fixed a bug in which creating a table _foo > when an array type of that name already existed would make the array > type change its name to get out of the way. But it missed a trick in > that renaming a table would

Re: [HACKERS] No parameter values checking while creating Alter subscription...Connection

2017-05-25 Thread Andres Freund
On 2017-05-25 17:08:57 -0400, Peter Eisentraut wrote: > On 5/25/17 10:18, Masahiko Sawada wrote: > >> postgres=# alter subscription c1 connection 'port=4000'; > >> ALTER SUBSCRIPTION > >> postgres=# alter subscription c1 connection 'dbname=cc'; > >> ALTER SUBSCRIPTION > >> > > CREATE SUBSCRIPTION

[HACKERS] Channel binding support for SCRAM-SHA-256

2017-05-25 Thread Michael Paquier
Hi all, Please find attached a patch to add support for channel binding for SCRAM, to mitigate MITM attacks using this protocol. Per RFC5802 (https://tools.ietf.org/html/rfc5802), servers supporting channel binding need to add support for tls-unique, and this is what this patch does. As defined

Re: [HACKERS] No parameter values checking while creating Alter subscription...Connection

2017-05-25 Thread Peter Eisentraut
On 5/25/17 10:18, Masahiko Sawada wrote: >> postgres=# alter subscription c1 connection 'port=4000'; >> ALTER SUBSCRIPTION >> postgres=# alter subscription c1 connection 'dbname=cc'; >> ALTER SUBSCRIPTION >> > CREATE SUBSCRIPTION tries to connect to publisher to create > replication slot or to get

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

2017-05-25 Thread Peter Eisentraut
On 5/25/17 09:55, Robert Haas wrote: > On Thu, May 25, 2017 at 8:32 AM, Peter Eisentraut > wrote: >>> Well, I think if it's not going to work, it should be prohibited, >>> rather than seeming to work but then not actually working. >> >> Here is a similar case

[HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change?

2017-05-25 Thread Regina Obe
Did something change with how exclusion constraints are handled? I'm trying to troubleshoot a regression we are having with PostGIS raster support. As best I can guess, it's because exclusion constraints that used to work in past versions are failing in PostgreSQL 10 with an error something like

[HACKERS] fix side-effect in get_qual_for_list()

2017-05-25 Thread Jeevan Ladhe
Hi, While working on one of the crash reported on default partition for list partitioning table[1] I found some strange behavior in get_qual_for_list() while I tried to call it from the new code I wrote for default partition. After debugging, I noticed that the function get_qual_for_list() is

[HACKERS] Renaming a table to an array's autogenerated name

2017-05-25 Thread Vik Fearing
In commit 9aa3c782c93, Tom fixed a bug in which creating a table _foo when an array type of that name already existed would make the array type change its name to get out of the way. But it missed a trick in that renaming a table would still cause a conflict. Steps to reproduce: postgres=#

Re: [HACKERS] CREATE STATISTICS statistic_type documentation

2017-05-25 Thread Alvaro Herrera
Tom Lane wrote: > Jeff Janes writes: > > On Thu, May 25, 2017 at 9:28 AM, Tom Lane wrote: > > If we invent more types in the future, would we expect those to be > > defaulted to as well? > > I might be wrong, but my impression is that the plan is to

Re: [HACKERS] Surjective functional indexes

2017-05-25 Thread Konstantin Knizhnik
On 25.05.2017 19:37, Tom Lane wrote: Konstantin Knizhnik writes: My proposal is to check value of function for functional indexes instead of just comparing set of effected attributes. Obviously, for some complex functions it may have negative effect on update

Re: [HACKERS] Surjective functional indexes

2017-05-25 Thread Andres Freund
On 2017-05-25 12:37:40 -0400, Tom Lane wrote: > Konstantin Knizhnik writes: > > My proposal is to check value of function for functional indexes instead > > of just comparing set of effected attributes. > > Obviously, for some complex functions it may have negative

Re: [HACKERS] CREATE STATISTICS statistic_type documentation

2017-05-25 Thread Tom Lane
Jeff Janes writes: > On Thu, May 25, 2017 at 9:28 AM, Tom Lane wrote: >> Where would you expect to find that? > Probably at the end of the paragraph: > "A statistic type to be computed in this statistics object. Currently > supported types are

Re: [HACKERS] CREATE STATISTICS statistic_type documentation

2017-05-25 Thread Jeff Janes
On Thu, May 25, 2017 at 9:28 AM, Tom Lane wrote: > Jeff Janes writes: > > The docs for CREATE STATISTICS does not say what happens if the > > statistic_type clause is omitted. It should probably say that the > default > > action is to create both

Re: [HACKERS] Surjective functional indexes

2017-05-25 Thread Tom Lane
Konstantin Knizhnik writes: > My proposal is to check value of function for functional indexes instead > of just comparing set of effected attributes. > Obviously, for some complex functions it may have negative effect on > update speed. > This is why I have added

[HACKERS] Surjective functional indexes

2017-05-25 Thread Konstantin Knizhnik
Right now Postgres determines whether update operation touch index or not based only on set of the affected columns. But in case of functional indexes such policy quite frequently leads to unnecessary index updates. For example, functional index are widely use for indexing JSON data:

Re: [HACKERS] CREATE STATISTICS statistic_type documentation

2017-05-25 Thread Tom Lane
Jeff Janes writes: > The docs for CREATE STATISTICS does not say what happens if the > statistic_type clause is omitted. It should probably say that the default > action is to create both ndistinct and dependencies. Hmm, I coulda sworn that it did say that somewhere.

[HACKERS] CREATE STATISTICS statistic_type documentation

2017-05-25 Thread Jeff Janes
The docs for CREATE STATISTICS does not say what happens if the statistic_type clause is omitted. It should probably say that the default action is to create both ndistinct and dependencies. Cheers, Jeff

Re: [HACKERS] Cached plans and statement generalization

2017-05-25 Thread Konstantin Knizhnik
On 10.05.2017 19:11, Konstantin Knizhnik wrote: Based on the Robert's feedback and Tom's proposal I have implemented two new versions of autoprepare patch. First version is just refactoring of my original implementation: I have extracted common code into prepare_cached_plan and

[HACKERS] Walsender timeouts and large transactions

2017-05-25 Thread Petr Jelinek
Hi, We have had issue with walsender timeout when used with logical decoding and the transaction is taking long time to be decoded (because it contains many changes) I was looking today at the walsender code and realized that it's because if the network and downstream are fast enough, we'll

Re: [HACKERS] Server ignores contents of SASLInitialResponse

2017-05-25 Thread Michael Paquier
On Thu, May 25, 2017 at 10:52 AM, Michael Paquier wrote: > Actually, I don't think that we are completely done here. Using the > patch of upthread to enforce a failure on SASLInitialResponse, I see > that connecting without SSL causes the following error: > psql: FATAL:

Re: [HACKERS] [POC] hash partitioning

2017-05-25 Thread Robert Haas
On Mon, May 22, 2017 at 1:49 AM, Ashutosh Bapat wrote: > The prologue is arranged as one paragraph (with a new line) per > member. Within each paragraph explanation for each partitioning > strategy starts on its own line. One paragraph per member is more >

Re: [HACKERS] Fix performance of generic atomics

2017-05-25 Thread Sokolov Yura
Hello, Tom. I agree that lonely semicolon looks bad. Applied your suggestion for empty loop body (/* skip */). Patch in first letter had while(true), but I removed it cause I think it is uglier: - `while(true)` was necessary for grouping read with `if`, - but now there is single statement in a

Re: [HACKERS] Fix performance of generic atomics

2017-05-25 Thread Aleksander Alekseev
Hi Yura, > Attached patch contains patch for all generic atomic > functions, and also __sync_fetch_and_(or|and) for gcc, cause > I believe GCC optimize code around intrinsic better than > around inline assembler. > (final performance is around 86000tps, but difference between > 83000tps and

Re: [HACKERS] Server ignores contents of SASLInitialResponse

2017-05-25 Thread Michael Paquier
On Thu, May 25, 2017 at 9:32 AM, Michael Paquier wrote: > On Thu, May 25, 2017 at 8:51 AM, Heikki Linnakangas wrote: >> On 05/24/2017 11:33 PM, Michael Paquier wrote: >>> I have noticed today that the server ignores completely the contents >>> of

Re: [HACKERS] Fix performance of generic atomics

2017-05-25 Thread Tom Lane
Sokolov Yura writes: @@ -382,12 +358,8 @@ static inline uint64 pg_atomic_fetch_and_u64_impl(volatile pg_atomic_uint64 *ptr, uint64 and_) { uint64 old; - while (true) - { - old = pg_atomic_read_u64_impl(ptr); - if

Re: retry shm attach for windows (WAS: Re: [HACKERS] OK, so culicidae is *still* broken)

2017-05-25 Thread Tom Lane
Amit Kapila writes: > Yes, I also share this opinion, the shm attach failures are due to > randomization behavior, so sleep won't help much. So, I will change > the patch to use 100 retries unless people have other opinions. Sounds about right to me.

Re: [HACKERS] No parameter values checking while creating Alter subscription...Connection

2017-05-25 Thread Masahiko Sawada
On Thu, May 25, 2017 at 9:43 AM, tushar wrote: > Hi, > > We usually check connection parameter values while creating create > subscription > > \\port is WRONG > > postgres=# create subscription c1 connection 'port=4000 ' publication pub; > ERROR: could not connect

[HACKERS] Fix performance of generic atomics

2017-05-25 Thread Sokolov Yura
Good day, everyone. I've been played with pgbench on huge machine. (72 cores, 56 for postgresql, enough memory to fit base both into shared_buffers and file cache) (pgbench scale 500, unlogged tables, fsync=off, synchronous commit=off, wal_writer_flush_after=0). With 200 clients performance is

Re: retry shm attach for windows (WAS: Re: [HACKERS] OK, so culicidae is *still* broken)

2017-05-25 Thread Amit Kapila
On Thu, May 25, 2017 at 8:41 AM, Noah Misch wrote: > On Thu, May 25, 2017 at 11:41:19AM +0900, Michael Paquier wrote: > >> Indeed, pgrename() does so with a 100ms sleep time between each >> iteration. Perhaps we could do that and limit to 50 iterations? > > pgrename() is

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

2017-05-25 Thread Robert Haas
On Thu, May 25, 2017 at 8:32 AM, Peter Eisentraut wrote: >> Well, I think if it's not going to work, it should be prohibited, >> rather than seeming to work but then not actually working. > > Here is a similar case that pg_dump fails on: > > create table

[HACKERS] No parameter values checking while creating Alter subscription...Connection

2017-05-25 Thread tushar
Hi, We usually check connection parameter values while creating create subscription \\port is WRONG postgres=# create subscription c1 connection 'port=4000 ' publication pub; ERROR: could not connect to the publisher: could not connect to server: No such file or directory Is the

Re: [HACKERS] Fix performance of generic atomics

2017-05-25 Thread Sokolov Yura
A bit cleaner version of a patch. Sokolov Yura писал 2017-05-25 15:22: Good day, everyone. I've been played with pgbench on huge machine. (72 cores, 56 for postgresql, enough memory to fit base both into shared_buffers and file cache) (pgbench scale 500, unlogged tables, fsync=off, synchronous

Re: [HACKERS] Server ignores contents of SASLInitialResponse

2017-05-25 Thread Michael Paquier
On Thu, May 25, 2017 at 8:51 AM, Heikki Linnakangas wrote: > On 05/24/2017 11:33 PM, Michael Paquier wrote: >> I have noticed today that the server ignores completely the contents >> of SASLInitialResponse. ... Attached is a patch to fix the problem. > > Fixed, thanks! Thanks

[HACKERS] Re: Alter subscription..SET - NOTICE message is coming for table which is already removed

2017-05-25 Thread tushar
On 05/25/2017 03:38 PM, tushar wrote: While performing - Alter subscription..SET , I found that NOTICE message is coming duplicate next time , which is not needed anymore. There is an another example - where i am getting "ERROR: subscription table 16435 in subscription 16684 does not exist"

Re: [HACKERS] Server ignores contents of SASLInitialResponse

2017-05-25 Thread Heikki Linnakangas
On 05/24/2017 11:33 PM, Michael Paquier wrote: I have noticed today that the server ignores completely the contents of SASLInitialResponse. ... Attached is a patch to fix the problem. Fixed, thanks! - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

2017-05-25 Thread Peter Eisentraut
On 5/24/17 21:36, Robert Haas wrote: > On Wed, May 24, 2017 at 7:16 PM, Peter Eisentraut > wrote: >> On 5/22/17 07:42, Kuntal Ghosh wrote: >>> pg_dump ignores anything created under object name "pg_*" or >>> "information_schema". >> >> Publications have a

[HACKERS] Allow GiST opcalsses without compress\decompres functions

2017-05-25 Thread Andrew Borodin
Hi, hackers! Currently, GiST stores each attribute in a compressed form. That is, each time attribute is written it's calling compress function, and when the attribute is accessed the decompress functions is called. Some types can't get any advantage out of this technique since the context of one

Re: [HACKERS] Alter subscription..SET - NOTICE message is coming for table which is already removed

2017-05-25 Thread tushar
On 05/25/2017 04:40 PM, Masahiko Sawada wrote: I think you did ALTER SUBSCRIPTION while table sync for 100 tables is running, right? Yes, i didn't wait too much while executing the commands. -- regards,tushar EnterpriseDB https://www.enterprisedb.com/ The Enterprise PostgreSQL Company --

Re: [HACKERS] Alter subscription..SET - NOTICE message is coming for table which is already removed

2017-05-25 Thread Masahiko Sawada
On Thu, May 25, 2017 at 6:08 AM, tushar wrote: > Hi, > > While performing - Alter subscription..SET , I found that NOTICE message is > coming duplicate next time , which is not needed anymore. > > X cluster= > create 100 tables > create publication ( create

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

2017-05-25 Thread Tomas Vondra
Hi, On 5/25/17 6:03 AM, Robert Haas wrote: On Thu, Apr 6, 2017 at 4:37 PM, Tomas Vondra wrote: Which brings me to the slightly suspicious bit. On 9.5, there's no difference between GROUP and GROUP+LIKE cases - the estimates are exactly the same in both cases.

[HACKERS] Alter subscription..SET - NOTICE message is coming for table which is already removed

2017-05-25 Thread tushar
Hi, While performing - Alter subscription..SET , I found that NOTICE message is coming duplicate next time , which is not needed anymore. X cluster= create 100 tables create publication ( create publication pub for all tables;) Y cluster= create 100 tables create subscription ( create

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

2017-05-25 Thread Jeevan Ladhe
Forgot to attach the patch. PFA. On Thu, May 25, 2017 at 3:02 PM, Jeevan Ladhe wrote: > Hi Rajkumar, > > postgres=# CREATE TEMP TABLE temp_list_part (a int) PARTITION BY LIST (a); >> CREATE TABLE >> postgres=# CREATE TEMP TABLE temp_def_part (a int); >> CREATE

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

2017-05-25 Thread Jeevan Ladhe
Hi Rajkumar, postgres=# CREATE TEMP TABLE temp_list_part (a int) PARTITION BY LIST (a); > CREATE TABLE > postgres=# CREATE TEMP TABLE temp_def_part (a int); > CREATE TABLE > postgres=# ALTER TABLE temp_list_part ATTACH PARTITION temp_def_part > DEFAULT; > server closed the connection unexpectedly

Re: [HACKERS] wal_level > WAL_LEVEL_LOGICAL

2017-05-25 Thread Neha Khatri
On Wed, 24 May 2017 at 10:29 pm, Robert Haas wrote: > On Mon, May 22, 2017 at 9:08 AM, Neha Khatri > wrote: > > As per my understabding, current postgres server supports only three > > values for wal_level i.e. 'minimal' , 'replica' or 'logical'.

Re: [HACKERS] Get stuck when dropping a subscription during synchronizing table

2017-05-25 Thread tushar
On 05/25/2017 12:44 AM, Petr Jelinek wrote: There is still outstanding issue that sync worker will keep running inside the long COPY because the invalidation messages are also not processed until it finishes but all the original issues reported here disappear for me with the attached patches

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

2017-05-25 Thread Rajkumar Raghuwanshi
On Thu, May 25, 2017 at 12:10 PM, Jeevan Ladhe < jeevan.la...@enterprisedb.com> wrote: > PFA. > Hi I have applied v13 patch, got a crash when trying to attach default temp partition. postgres=# CREATE TEMP TABLE temp_list_part (a int) PARTITION BY LIST (a); CREATE TABLE postgres=# CREATE TEMP

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

2017-05-25 Thread Jeevan Ladhe
Hi, I started looking into Rahila's default_partition_v11.patch, and reworked on few things as below: - I tried to cover all the review comments posted on the thread. Do let me know if something is missing. - Got rid of the functions get_qual_for_default() and generate_qual_for_defaultpart().

Re: [HACKERS] Broken hint bits (freeze)

2017-05-25 Thread Vladimir Borodin
> 24 мая 2017 г., в 15:44, Robert Haas написал(а): > > On Wed, May 24, 2017 at 7:27 AM, Dmitriy Sarafannikov > wrote: >> It seems like replica did not replayed corresponding WAL records. >> Any thoughts? > > heap_xlog_freeze_page() is a pretty