Re: [HACKERS] Pluggable storage

2017-10-19 Thread Amit Kapila
On Sat, Oct 14, 2017 at 1:09 AM, Alexander Korotkov wrote: > On Fri, Oct 13, 2017 at 9:41 PM, Robert Haas wrote: >> >> On Fri, Oct 13, 2017 at 1:59 PM, Peter Geoghegan wrote: >> >> Fully agreed. >> > >> > If we implement that

Re: [HACKERS] Pluggable storage

2017-10-19 Thread Amit Kapila
On Fri, Oct 13, 2017 at 1:58 PM, Haribabu Kommi wrote: > > On Fri, Oct 13, 2017 at 11:55 AM, Robert Haas wrote: >> >> On Thu, Oct 12, 2017 at 8:00 PM, Haribabu Kommi >> wrote: >> > Currently I added a snapshot_satisfies

[HACKERS] per-sesson errors after interrupting CLUSTER pg_attrdef

2017-10-19 Thread Justin Pryzby
This was briefly scary but seems to have been limited to my psql session (no other errors logged). Issue with catcache (?) I realized that the backup job I'd kicked off was precluding the CLUSTER from running, but that CLUSTER was still holding lock and stalling everything else under the sun.

[HACKERS] Queuing all tables for analyze after recovery

2017-10-19 Thread Tomasz Ostrowski
Hi. Some (maybe all) row statistics are lost after the database has recovered after a failover. So it's recommended to ANALYZE all databases in a cluster after recovery. Amazon's AWS RDS (their managed SQL databases service) even sends an email "consider running analyze if your database is

Re: [HACKERS] CUBE seems a bit confused about ORDER BY

2017-10-19 Thread Alexander Korotkov
Hi! On Fri, Oct 20, 2017 at 12:52 AM, Tomas Vondra wrote: > I've noticed this suspicious behavior of "cube" data type with ORDER BY, > which I believe is a bug in the extension (or the GiST index support). > The following example comes directly from regression

[HACKERS] CUBE seems a bit confused about ORDER BY

2017-10-19 Thread Tomas Vondra
Hi, I've noticed this suspicious behavior of "cube" data type with ORDER BY, which I believe is a bug in the extension (or the GiST index support). The following example comes directly from regression tests added by 33bd250f (so CC Teodor and Stas, who are mentioned in the commit). This query

Re: [HACKERS] Queuing all tables for analyze after recovery

2017-10-19 Thread Vik Fearing
On 10/19/2017 11:26 PM, Tom Lane wrote: > Vik Fearing writes: >> On 10/19/2017 10:54 PM, Tom Lane wrote: >>> Uh ... recommended by whom? pg_statistic has exactly the same reliability >>> guarantees as the rest of the system catalogs. > >> For data statistics, sure.

Re: [HACKERS] Queuing all tables for analyze after recovery

2017-10-19 Thread Tom Lane
Vik Fearing writes: > On 10/19/2017 10:54 PM, Tom Lane wrote: >> Uh ... recommended by whom? pg_statistic has exactly the same reliability >> guarantees as the rest of the system catalogs. > For data statistics, sure. One thing I'm unhappy about is that >

Re: [HACKERS] Queuing all tables for analyze after recovery

2017-10-19 Thread Tomasz Ostrowski
On 10/19/2017 10:54 PM, Tom Lane wrote: Uh ... recommended by whom? pg_statistic has exactly the same reliability guarantees as the rest of the system catalogs. Actually I'm not exactly sure what is lost and what is preserved. I'm pretty sure that pg_stat_all_tables and similar views turn

Re: [HACKERS] Queuing all tables for analyze after recovery

2017-10-19 Thread Vik Fearing
On 10/19/2017 10:54 PM, Tom Lane wrote: > Tomasz Ostrowski writes: >> Some (maybe all) row statistics are lost after the database has >> recovered after a failover. So it's recommended to ANALYZE all databases >> in a cluster after recovery. > > Uh ... recommended by

Re: [HACKERS] Queuing all tables for analyze after recovery

2017-10-19 Thread Tom Lane
Tomasz Ostrowski writes: > Some (maybe all) row statistics are lost after the database has > recovered after a failover. So it's recommended to ANALYZE all databases > in a cluster after recovery. Uh ... recommended by whom? pg_statistic has exactly the same

Re: [HACKERS] Domains and arrays and composites, oh my

2017-10-19 Thread Tom Lane
I wrote: > Andrew Dunstan writes: >> On 09/28/2017 01:02 PM, Tom Lane wrote: I do think that treating a function returning a domain-over-composite differently from one returning a base composite is a POLA. We'd be very hard put to explain the reasons

Re: [HACKERS] [COMMITTERS] pgsql: Fix traversal of half-frozen update chains

2017-10-19 Thread Peter Geoghegan
On Thu, Oct 19, 2017 at 9:03 AM, Peter Geoghegan wrote: >> /me studies the problem for a while. >> >> What's bothering me about this is: how is cutoff_xid managing to be a >> new enough transaction ID for this to happen in the first place? The >> cutoff XID should certainly be

[HACKERS] Queuing all tables for analyze after recovery

2017-10-19 Thread Tomasz Ostrowski
Hi. Some (maybe all) row statistics are lost after the database has recovered after a failover. So it's recommended to ANALYZE all databases in a cluster after recovery. Amazon's AWS RDS (their managed SQL databases service) even sends an email "consider running analyze if your database is

[HACKERS] WIP: BRIN bloom indexes

2017-10-19 Thread Tomas Vondra
Hi, The BRIN minmax opclasses work well only for data where the column is somewhat correlated to physical location in a table. So it works great for timestamps in append-only log tables, for example. When that is not the case (non-correlated columns) the minmax ranges get very "wide" and we end

Re: [HACKERS] [PATCH] Add ALWAYS DEFERRED option for constraints

2017-10-19 Thread Nico Williams
Rebased (there were conflicts in the SGML files). Nico -- >From 80d284ecefa22945d507d2822f1f1a195e2af751 Mon Sep 17 00:00:00 2001 From: Nicolas Williams Date: Tue, 3 Oct 2017 00:33:09 -0500 Subject: [PATCH] Add ALWAYS DEFERRED option for CONSTRAINTs and CONSTRAINT

[HACKERS] What is the point of setrefs.c's is_converted_whole_row_reference?

2017-10-19 Thread Tom Lane
AFAICS, setrefs.c's special treatment of "converted whole row references" is completely pointless. Why aren't they just treated by the regular "non var" code paths, thus saving code space and cycles? regards, tom lane -- Sent via pgsql-hackers mailing list

Re: [HACKERS] [PATCH] Add recovery_min_apply_delay_reconnect recovery option

2017-10-19 Thread Eric Radman
On Tue, Oct 17, 2017 at 12:34:17PM +0900, Michael Paquier wrote: > On Tue, Oct 17, 2017 at 12:51 AM, Eric Radman wrote: > > This administrative compromise is necessary because the WalReceiver is > > not resumed after a network interruption until all records are read, > >

Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

2017-10-19 Thread Nico Williams
A bit more about why I want this. Suppose you have an app like PostgREST (a RESTful, Haskell-coded, HTTP front-end for PostgreSQL). PostgREST basically a proxy for PG access. Users authenticate to the proxy. The proxy authenticates to PG with its own credentials, then it does something like

Re: [HACKERS] Fix performance degradation of contended LWLock on NUMA

2017-10-19 Thread Andres Freund
On 2017-10-19 14:36:56 +0300, Sokolov Yura wrote: > > > + init_local_spin_delay(); > > > > The way you moved this around has the disadvantage that we now do this - > > a number of writes - even in the very common case where the lwlock can > > be acquired directly. > > Excuse me, I don't

Re: [HACKERS] [COMMITTERS] pgsql: Fix traversal of half-frozen update chains

2017-10-19 Thread Peter Geoghegan
On Thu, Oct 19, 2017 at 7:21 AM, Robert Haas wrote: > The commit message for a5736bf7 doesn't say anything about a race; it > just claims that it is fixing traversal of half-frozen update chains, > without making any reference to how such a thing as a half-frozen > update

Re: [HACKERS] Supporting Windows SChannel as OpenSSL replacement

2017-10-19 Thread Robert Haas
On Thu, Oct 19, 2017 at 1:15 AM, Satyanarayana Narlapuram wrote: > Tom, Robert, Microsoft is interested in supporting windows SChannel for > Postgres. Please let know how we can help taking this forward. We would love > contributing to this either by

Re: [HACKERS] Cursor With_Hold Performance Workarounds/Optimization

2017-10-19 Thread Tom Lane
Leon Winter writes: > The loops are more complex in reality of course, more like: > open cursor for select from table1 > loop > { fetch some entries from cursor > call some external application > do some crazy complicated calculations based on some user input in the

Re: [HACKERS] Cursor With_Hold Performance Workarounds/Optimization

2017-10-19 Thread Geoff Winkless
On 19 October 2017 at 15:06, Leon Winter wrote: > The calculations inside the loop are written in some dynamic high-level > language and cannot easily be translated into SQL. > ​Can you not simply create a second connection to perform the updates? ​ Geoff

Re: [HACKERS] [COMMITTERS] pgsql: Fix traversal of half-frozen update chains

2017-10-19 Thread Robert Haas
On Wed, Oct 18, 2017 at 5:52 PM, Peter Geoghegan wrote: > There is a race where we cannot prune the page, though. That's why we > had to revisit what I suppose was a tacit assumption, and address its > problems in the commit that started this thread (commit a5736bf7). The commit

Re: [HACKERS] Cursor With_Hold Performance Workarounds/Optimization

2017-10-19 Thread David Fetter
On Thu, Oct 19, 2017 at 04:06:47PM +0200, Leon Winter wrote: > > What other things did you try, and how did they fail? In particular, > > what happened when you used > > > > UPDATE table2 > > SET [things based on table1] > > FROM table1 [qualified] JOIN table2 ON ([conditions]) > >

Re: [HACKERS] Cursor With_Hold Performance Workarounds/Optimization

2017-10-19 Thread Leon Winter
> What other things did you try, and how did they fail? In particular, > what happened when you used > > UPDATE table2 > SET [things based on table1] > FROM table1 [qualified] JOIN table2 ON ([conditions]) well, it is not the ideal way of doing things but then again this SQL is

Re: [HACKERS] Cursor With_Hold Performance Workarounds/Optimization

2017-10-19 Thread David Fetter
On Thu, Oct 19, 2017 at 03:20:48PM +0200, Leon Winter wrote: > Hi, > > I originally brought up this issue on the pgsql-performance mailing list [^] > to > no avail so I am trying again here. > > During implementation of a runtime environment and the adjoining database > abstraction layer I

[HACKERS] Cursor With_Hold Performance Workarounds/Optimization

2017-10-19 Thread Leon Winter
Hi, I originally brought up this issue on the pgsql-performance mailing list [^] to no avail so I am trying again here. During implementation of a runtime environment and the adjoining database abstraction layer I noticed (like many before me [0] and as correctly mentioned in the documentation)

Re: [HACKERS] [PATCH] Tests for reloptions

2017-10-19 Thread Alvaro Herrera
Oh, one more thing: be careful when editing parallel_schedule. There are constraints on the number of entries in each group; you had added a 20th entry after the comment that the group can only have 19. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7

Re: [HACKERS] [PATCH] Tests for reloptions

2017-10-19 Thread Alvaro Herrera
Nikolay Shaplov wrote: > В письме от 3 октября 2017 11:48:43 пользователь Michael Paquier написал: > I've been thinking a lot, and rereading the patch. When I reread it I've been > thinking that I would like to add more tests to it now... ;-) > > If the only purpose of tests is to get better

Re: [HACKERS] Fix performance degradation of contended LWLock on NUMA

2017-10-19 Thread Sokolov Yura
On 2017-10-19 02:28, Andres Freund wrote: On 2017-06-05 16:22:58 +0300, Sokolov Yura wrote: Algorithm for LWLockWaitForVar is also refactored. New version is: 1. If lock is not held by anyone, it immediately exit. 2. Otherwise it is checked for ability to take WaitList lock, because variable

Re: [HACKERS] A handful of typos in allpaths.c

2017-10-19 Thread Magnus Hagander
On Wed, Oct 18, 2017 at 4:45 AM, David Rowley wrote: > A small patch to fix these is attached. > Applied, thanks. I backpatched the actual error message typo fix. Left the comment alone in backbranches because it conflicted, so it didn't seem worth it. --

Re: [HACKERS] Fix a typo in libpq/auth.c

2017-10-19 Thread Magnus Hagander
On Thu, Oct 19, 2017 at 12:05 PM, Masahiko Sawada wrote: > Hi, > > Attached a patch for $subject. > > s/RAIDUS/RADIUS/ > Applied, thanks. -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/

Re: [HACKERS] I've just started working on Full Text Search with version 10 on Ubuntu 16

2017-10-19 Thread Aleksandr Parfenov
On Wed, 18 Oct 2017 22:53:16 -0400 Ronald Jewell wrote: > and I'm getting error ... > > ERROR: could not open extension control file > "/usr/share/postgresql/10/extension/tsearch2.control": No such file or > directory > > when I try to create the tsearch2 extension. Hi,

Re: [HACKERS] Fix performance degradation of contended LWLock on NUMA

2017-10-19 Thread Sokolov Yura
Hi, On 2017-10-19 03:03, Andres Freund wrote: Hi, On 2017-09-08 22:35:39 +0300, Sokolov Yura wrote: /* * Internal function that tries to atomically acquire the lwlock in the passed - * in mode. + * in mode. If it could not grab the lock, it doesn't puts proc into wait + * queue. * -

[HACKERS] I've just started working on Full Text Search with version 10 on Ubuntu 16

2017-10-19 Thread Ronald Jewell
and I'm getting error ... ERROR: could not open extension control file "/usr/share/postgresql/10/extension/tsearch2.control": No such file or directory when I try to create the tsearch2 extension. thanks Ron

Re: [HACKERS] Supporting Windows SChannel as OpenSSL replacement

2017-10-19 Thread Satyanarayana Narlapuram
Tom, Robert, Microsoft is interested in supporting windows SChannel for Postgres. Please let know how we can help taking this forward. We would love contributing to this either by enhancing the original patch provided by Heikki, or test the changes on Windows. Thanks, Satya -Original

Re: [HACKERS] show "aggressive" or not in autovacuum logs

2017-10-19 Thread Alvaro Herrera
Kyotaro HORIGUCHI wrote: > How about the followings? > > "automatic [agressive ]vacuum of table \"%s..." > "[aggressive ]vacuuming \"%s..." That form of log message seems acceptable to me (first one is missing a 'g'). In any case, please do not construct the sentence with %s expanding the

[HACKERS] Fix a typo in libpq/auth.c

2017-10-19 Thread Masahiko Sawada
Hi, Attached a patch for $subject. s/RAIDUS/RADIUS/ Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center fix_typo_in_auth_c.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

Re: [HACKERS] show "aggressive" or not in autovacuum logs

2017-10-19 Thread Masahiko Sawada
On Tue, Sep 5, 2017 at 3:41 PM, Kyotaro HORIGUCHI wrote: > Thank you for the opinions. > > At Tue, 29 Aug 2017 15:00:57 +0900, Masahiko Sawada > wrote in

Re: [HACKERS] Parallel Append implementation

2017-10-19 Thread Amit Khandekar
On 13 October 2017 at 00:29, Robert Haas wrote: > On Wed, Oct 11, 2017 at 8:51 AM, Amit Khandekar > wrote: >> [ new patch ] > > + parallel_append > + Waiting to choose the next subplan during Parallel Append > plan > +

Re: [HACKERS] 64-bit queryId?

2017-10-19 Thread Julien Rouhaud
On Thu, Oct 19, 2017 at 1:08 AM, Michael Paquier wrote: > On Thu, Oct 19, 2017 at 4:12 AM, Robert Haas wrote: >> On Wed, Oct 18, 2017 at 9:20 AM, Julien Rouhaud wrote: >>> WIth current pgssHashKey definition, there shouldn't