[HACKERS] Forbid use of LF and CR characters in database and role names

2016-08-11 Thread Michael Paquier
Hi all, As CVE-2016-5424 has put recently in light, using LF and CR in database and role names can lead to unexpected problems in the way they are handled in logical backups or generated command lines. There is as well a comment in the code mentioning a potential restriction for that, precisely

Re: [HACKERS] Add hint for function named "is"

2016-08-11 Thread Jim Nasby
On 8/11/16 4:54 PM, Tom Lane wrote: which probably contributes to Jim's confusion. I think what is happening in the trouble case is that since IS has lower precedence than Op, the grammar decides it ought to resolve || as a postfix operator, and then it effectively has ('x' ||) IS ...

Re: [HACKERS] new autovacuum criterion for visible pages

2016-08-11 Thread Jim Nasby
On 8/11/16 10:59 AM, Jeff Janes wrote: On Thu, Aug 11, 2016 at 8:32 AM, Amit Kapila wrote: On Thu, Aug 11, 2016 at 2:09 AM, Jeff Janes wrote: I wanted to create a new relopt named something like autovacuum_vacuum_pagevisible_factor which would

Re: [HACKERS] condition variables

2016-08-11 Thread Robert Haas
On Thu, Aug 11, 2016 at 8:44 PM, Thomas Munro wrote: > In contrast, this proposal leaves it up to client code to get that > right, similarly to the way you need to do things in a certain order > when waiting for state changes with latches. You could say that it's >

Re: [HACKERS] condition variables

2016-08-11 Thread Robert Haas
On Thu, Aug 11, 2016 at 6:37 PM, Peter Geoghegan wrote: > I notice that you acquire a spinlock within the implementation of > condition variables. Is it worth any effort to consolidate the number > of spinlock acquisitions? In other words, maybe the most common idioms > should be

Re: [HACKERS] condition variables

2016-08-11 Thread Robert Haas
On Thu, Aug 11, 2016 at 6:12 PM, Tom Lane wrote: >> But if we replace the io_in_progress locks with >> condition variables, then that doesn't happen any more. Nobody is >> "holding" the condition variable, so it doesn't get "released" when >> the process doing I/O aborts.

Re: [HACKERS] condition variables

2016-08-11 Thread Thomas Munro
On Fri, Aug 12, 2016 at 9:47 AM, Robert Haas wrote: > https://en.wikipedia.org/wiki/Monitor_(synchronization)#Condition_variables_2 > > Basically, a condition variable has three operations: you can wait for > the condition variable; you can signal the condition variable to

Re: [HACKERS] [Patch] New psql prompt substitution %r (m = master, r = replica)

2016-08-11 Thread Michael Paquier
On Fri, Aug 12, 2016 at 12:51 AM, Tom Lane wrote: > Peter Eisentraut writes: >> On 8/11/16 6:04 AM, Aleksander Alekseev wrote: >>> Suggested patch introduces an %r substitution in psql's prompt. This >>> substitution allows to display whether

Re: [HACKERS] new autovacuum criterion for visible pages

2016-08-11 Thread Tom Lane
Michael Paquier writes: > In short, autovacuum will need to scan by itself the VM of each > relation and decide based on that. That seems like a worthwhile approach to pursue. The VM is supposed to be small, and if you're worried it isn't, you could sample a few pages

Re: [HACKERS] [Patch] New psql prompt substitution %r (m = master, r = replica)

2016-08-11 Thread David Fetter
On Thu, Aug 11, 2016 at 01:04:19PM +0300, Aleksander Alekseev wrote: > Hello. > > Suggested patch introduces an %r substitution in psql's prompt. This > substitution allows to display whether user is connected to master or > replica right in a prompt. This is a neat idea, but there are some

Re: [HACKERS] new autovacuum criterion for visible pages

2016-08-11 Thread Michael Paquier
On Thu, Aug 11, 2016 at 4:21 PM, Michael Paquier wrote: > On Thu, Aug 11, 2016 at 3:29 PM, Michael Paquier > wrote: >> On Thu, Aug 11, 2016 at 5:39 AM, Jeff Janes wrote: >>> I wanted to create a new relopt named

Re: [HACKERS] condition variables

2016-08-11 Thread Peter Geoghegan
On Thu, Aug 11, 2016 at 2:47 PM, Robert Haas wrote: > Another approach to the problem is to use a latch wait loop. That > almost works. Interrupts can be serviced, and you can recheck shared > memory to see whether the condition for proceeding is satisfied after > each

Re: [HACKERS] condition variables

2016-08-11 Thread Tom Lane
Robert Haas writes: > I had what I think is a better idea, which is to introduce a notion of > condition variables. Interesting proposal. > ... Using condition variables here seems to > have a couple of advantages. First, it means that a backend waiting > for buffer I/O

Re: [HACKERS] Add hint for function named "is"

2016-08-11 Thread Tom Lane
"David E. Wheeler" writes: > On Aug 11, 2016, at 2:11 PM, Jim Nasby wrote: >> SELECT 'x'||is(); >> ERROR: syntax error at or near "(" > Why does it need quotation marks in this case? It doesn't, if you do something like regression=# select

[HACKERS] condition variables

2016-08-11 Thread Robert Haas
Hi, Some of my EnterpriseDB colleagues and I have been working on various parallel query projects, all of which have been previously disclosed here: https://wiki.postgresql.org/wiki/EnterpriseDB_database_server_roadmap One issue we've encountered is that it's not very easy for one process in a

Re: [HACKERS] Add hint for function named "is"

2016-08-11 Thread David E. Wheeler
On Aug 11, 2016, at 2:11 PM, Jim Nasby wrote: > CREATE FUNCTION pg_temp.is() RETURNS text LANGUAGE sql AS $$SELECT > 'x'::text$$; > SELECT 'x'||is(); > ERROR: syntax error at or near "(" > LINE 1: SELECT 'x'||is(); > > I was finally able to figure out this was

Re: [HACKERS] Allowing GIN array_ops to work on anyarray

2016-08-11 Thread M Enrique
This is awesome. I will build it to start using and testing it in my development environment. Thank you so much for making this change. On Thu, Aug 11, 2016 at 11:33 AM Tom Lane wrote: > In > https://www.postgresql.org/message-id/15293.1466536...@sss.pgh.pa.us > I speculated

Re: [HACKERS] Small issues in syncrep.c

2016-08-11 Thread Alvaro Herrera
Simon Riggs wrote: > Good catch. > > I've updated Julien's patch to reflect Michael's suggestion. > > Looks good to apply immediately. > > 14e8803f1 was only a partial patch for the syncrep code, so I don't > see any reason to keep the code as it currently is in 9.5/9.6. > > Any objections to

[HACKERS] Add hint for function named "is"

2016-08-11 Thread Jim Nasby
CREATE FUNCTION pg_temp.is() RETURNS text LANGUAGE sql AS $$SELECT 'x'::text$$; SELECT 'x'||is(); ERROR: syntax error at or near "(" LINE 1: SELECT 'x'||is(); I was finally able to figure out this was because "is" needs to be quoted; is there a way this could be hinted? FWIW, the real-world

Re: [HACKERS] money type overflow checks

2016-08-11 Thread Peter Eisentraut
On 8/5/16 1:14 PM, Tom Lane wrote: > No, I don't think it's sufficient after a multiplication by 10. That > would be enough to shift some bits clear out of the word, but there's > no certainty that the new sign bit would be 1. > > The scheme used in scanint8 is safe. But I think it was written

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

2016-08-11 Thread Tom Lane
Robert Haas writes: > On Thu, Aug 11, 2016 at 11:35 AM, Tom Lane wrote: >> Well, if it's unqueryable they won't be able to query it no matter how >> hard they try ;-). > Sure, but as this thread already demonstrates, they may also complain > forcefully

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

2016-08-11 Thread Tom Lane
Kevin Grittner writes: > On Wed, Aug 10, 2016 at 5:14 PM, Tom Lane wrote: >> In short, I do not see a good reason to expose ampredlocks at the SQL >> level, and I think there needs to be a darn good reason to expose any of >> this stuff, not just "maybe

[HACKERS] Allowing GIN array_ops to work on anyarray

2016-08-11 Thread Tom Lane
In https://www.postgresql.org/message-id/15293.1466536...@sss.pgh.pa.us I speculated that it might not take too much to replace all the variants of GIN array_ops with a single polymorphic opclass over anyarray. Attached is a proposed patch that does that. There are two bits of added functionality

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

2016-08-11 Thread Joshua D. Drake
On 08/11/2016 10:46 AM, Kevin Grittner wrote: On Wed, Aug 10, 2016 at 5:14 PM, Tom Lane wrote: Kevin Grittner writes: But a DBA who has a problem doesn't care what the truth will be in a year or two -- the interest is in *right now* on one particular

Re: [HACKERS] LWLocks in DSM memory

2016-08-11 Thread Robert Haas
On Thu, Jul 28, 2016 at 6:51 PM, Thomas Munro wrote: > That version didn't actually make LWLock any smaller, because of the > additional offset stored in proclist_head when initialising the list. > Here is a new version that requires callers to provide it when >

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

2016-08-11 Thread Kevin Grittner
On Wed, Aug 10, 2016 at 5:14 PM, Tom Lane wrote: > Kevin Grittner writes: >> Where [whether the AM supports predicate locking at a >> granularity finer than the provided default of index relation] >> would be interesting to know is [ ... ] (when there is

Re: [HACKERS] Heap WARM Tuples - Design Draft

2016-08-11 Thread Claudio Freire
On Thu, Aug 11, 2016 at 11:07 AM, Jim Nasby wrote: > On 8/10/16 12:48 PM, Claudio Freire wrote: >> >> On Tue, Aug 9, 2016 at 11:39 PM, Jim Nasby >> wrote: >>> >>> On 8/9/16 6:44 PM, Claudio Freire wrote: Since we can lookup all

Re: Improved ICU patch - WAS: [HACKERS] Implementing full UTF-8 support (aka supporting 0x00)

2016-08-11 Thread Peter Geoghegan
On Thu, Aug 11, 2016 at 4:22 AM, Palle Girgensohn wrote: > But in your strxfrm code in PostgreSQL, the keys are cached, and represented > as int64:s if I remember correctly, so perhaps there is still a benefit > using the abbreviated keys? More testing is required, I guess...

Re: [HACKERS] 9.6 phrase search distance specification

2016-08-11 Thread Ryan Pedela
On Thu, Aug 11, 2016 at 10:42 AM, Ryan Pedela wrote: > On Thu, Aug 11, 2016 at 9:27 AM, Oleg Bartunov > wrote: > >> On Tue, Aug 9, 2016 at 9:59 PM, Ryan Pedela >> wrote: >> > >> > >> >> > I would say that it is worth it to

Re: [HACKERS] 9.6 phrase search distance specification

2016-08-11 Thread Ryan Pedela
On Thu, Aug 11, 2016 at 9:27 AM, Oleg Bartunov wrote: > On Tue, Aug 9, 2016 at 9:59 PM, Ryan Pedela > wrote: > > > > > > > I would say that it is worth it to have a "phrase slop" operator (Apache > > Lucene terminology). Proximity search is

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

2016-08-11 Thread Greg Stark
On Thu, Aug 11, 2016 at 5:24 PM, Robert Haas wrote: > You argue against > these things on the grounds that they might change later, but the > overwhelming evidence from posts on this list is that people would > prefer to have access to APIs that might not be stable rather

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

2016-08-11 Thread Robert Haas
On Thu, Aug 11, 2016 at 11:35 AM, Tom Lane wrote: > Robert Haas writes: >> On Wed, Aug 10, 2016 at 6:14 PM, Tom Lane wrote: >>> In short, I do not see a good reason to expose ampredlocks at the SQL >>> level, and I think there needs

Re: [HACKERS] new autovacuum criterion for visible pages

2016-08-11 Thread Jeff Janes
On Thu, Aug 11, 2016 at 8:32 AM, Amit Kapila wrote: > On Thu, Aug 11, 2016 at 2:09 AM, Jeff Janes wrote: >> I wanted to create a new relopt named something like >> autovacuum_vacuum_pagevisible_factor which would cause autovacuum to >> vacuum a

Re: [HACKERS] [Patch] New psql prompt substitution %r (m = master, r = replica)

2016-08-11 Thread Tom Lane
Peter Eisentraut writes: > On 8/11/16 6:04 AM, Aleksander Alekseev wrote: >> Suggested patch introduces an %r substitution in psql's prompt. This >> substitution allows to display whether user is connected to master or >> replica right in a prompt. > In the near

Re: [HACKERS] [Patch] New psql prompt substitution %r (m = master, r = replica)

2016-08-11 Thread Peter Eisentraut
On 8/11/16 6:04 AM, Aleksander Alekseev wrote: > Suggested patch introduces an %r substitution in psql's prompt. This > substitution allows to display whether user is connected to master or > replica right in a prompt. In the near future, there will (probably) be a lot more variants about what it

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

2016-08-11 Thread Tom Lane
Robert Haas writes: > On Wed, Aug 10, 2016 at 6:14 PM, Tom Lane wrote: >> In short, I do not see a good reason to expose ampredlocks at the SQL >> level, and I think there needs to be a darn good reason to expose any of >> this stuff, not just "maybe

Re: [HACKERS] improved DefElem list processing

2016-08-11 Thread Peter Eisentraut
On 8/5/16 11:25 AM, Peter Eisentraut wrote: > On 8/4/16 2:21 PM, Tom Lane wrote: >> Forgot to mention: seems like you should have added a location >> argument to makeDefElem. > > I was hesitating to do that lest it break extensions or something, but I > guess we break bigger things than that all

Re: [HACKERS] new autovacuum criterion for visible pages

2016-08-11 Thread Amit Kapila
On Thu, Aug 11, 2016 at 2:09 AM, Jeff Janes wrote: > I wanted to create a new relopt named something like > autovacuum_vacuum_pagevisible_factor which would cause autovacuum to > vacuum a table once less than a certain fraction of the relation's > pages are marked

Re: [HACKERS] 9.6 phrase search distance specification

2016-08-11 Thread Oleg Bartunov
On Tue, Aug 9, 2016 at 9:59 PM, Ryan Pedela wrote: > > > I would say that it is worth it to have a "phrase slop" operator (Apache > Lucene terminology). Proximity search is extremely useful for improving > relevance and phrase slop is one of the tools to achieve that. >

Re: [HACKERS] extract text from XML

2016-08-11 Thread Tobias Bussmann
> I have found a basic use case which is supported by the xml2 module, > but is unsupported by the new XML API. > It is not possible to correctly extract text Indeed. I came accross this shortcomming some months ago myself but still manage an item on my ToDo list to report it here as the

Re: [HACKERS] Logical Replication WIP

2016-08-11 Thread Petr Jelinek
Hi, On 11/08/16 13:34, Stas Kelvich wrote: * max_logical_replication_workers mentioned everywhere in docs, but guc.c defines variable called max_logical_replication_processes for postgresql.conf Ah changed it in code but not in docs, will fix. * Since pg_subscription already shared

Re: [HACKERS] Slowness of extended protocol

2016-08-11 Thread Shay Rojansky
On Thu, Aug 11, 2016 at 1:22 PM, Vladimir Sitnikov < sitnikov.vladi...@gmail.com> wrote: 2) The driver can use safepoints and autorollback to the good "right before > failure" state in case of a known failure. Here's the implementation: > https://github.com/pgjdbc/pgjdbc/pull/477 > As far as I

Re: [HACKERS] Heap WARM Tuples - Design Draft

2016-08-11 Thread Jim Nasby
On 8/10/16 12:48 PM, Claudio Freire wrote: On Tue, Aug 9, 2016 at 11:39 PM, Jim Nasby wrote: On 8/9/16 6:44 PM, Claudio Freire wrote: Since we can lookup all occurrences of k1=a index=0 and k2=a index=0, and in fact we probably did so already as part of the update

Re: [HACKERS] pg_ctl promote wait

2016-08-11 Thread Michael Paquier
On Thu, Aug 11, 2016 at 3:24 AM, Peter Eisentraut wrote: > On 8/7/16 9:44 PM, Michael Paquier wrote: This is not a good >> idea, and the idea of putting a wait argument in get_controlfile does >> not seem a good interface to me. I'd rather see

Re: [HACKERS] Slowness of extended protocol

2016-08-11 Thread Vladimir Sitnikov
Shay>As I said, an error is going to kill the ongoing transaction, how can this be solved without application logic? 1) At least, some well-defined error code should be created for that kind of matter. 2) The driver can use safepoints and autorollback to the good "right before failure" state in

Re: [HACKERS] Surprising behaviour of \set AUTOCOMMIT ON

2016-08-11 Thread David G. Johnston
On Thu, Aug 11, 2016 at 2:11 AM, Venkata Balaji N wrote: > > ​[...] > committing all the previously open transactions > ​[...] > "All"? ​There can only ever be at most one open transaction at any given time... I don't have a fundamental issue with saying "when turning

Re: [HACKERS] Logical Replication WIP

2016-08-11 Thread Stas Kelvich
> On 05 Aug 2016, at 18:00, Petr Jelinek wrote: > > Hi, > > as promised here is WIP version of logical replication patch. Great! Proposed DDL about publication/subscriptions looks very nice to me. Some notes and thoughts about patch: * Clang grumbles at following

Re: Improved ICU patch - WAS: [HACKERS] Implementing full UTF-8 support (aka supporting 0x00)

2016-08-11 Thread Palle Girgensohn
> 11 aug. 2016 kl. 11:15 skrev Palle Girgensohn : > >> >> 11 aug. 2016 kl. 03:05 skrev Peter Geoghegan : >> >> On Wed, Aug 10, 2016 at 1:42 PM, Palle Girgensohn >> wrote: >>> They've been used for the FreeBSD ports since 2005, and

Re: [HACKERS] Slowness of extended protocol

2016-08-11 Thread Shay Rojansky
On Thu, Aug 11, 2016 at 8:39 AM, Vladimir Sitnikov < sitnikov.vladi...@gmail.com> wrote: > Shay: > >> Prepared statements can have very visible effects apart from the speedup >> they provide (e.g. failure because of schema changes) It's not that >> these effects can't be worked around - they can

[HACKERS] [Patch] New psql prompt substitution %r (m = master, r = replica)

2016-08-11 Thread Aleksander Alekseev
Hello. Suggested patch introduces an %r substitution in psql's prompt. This substitution allows to display whether user is connected to master or replica right in a prompt. Usage example: ``` $ cat ~/.psqlrc \set PROMPT1 '%p (%r) =# ' $ psql -p 5432 psql (9.6beta4) Type "help" for help.

Re: [HACKERS] Bug in to_timestamp().

2016-08-11 Thread Artur Zakirov
Hello, On 14.07.2016 12:16, Pavel Stehule wrote: last point was discussed in thread related to to_date_valid function. Regards Pavel Thank you. Here is my patch. It is a proof of concept. Date/Time Formatting There are changes in date/time formatting rules: - now

Re: Improved ICU patch - WAS: [HACKERS] Implementing full UTF-8 support (aka supporting 0x00)

2016-08-11 Thread Palle Girgensohn
> 11 aug. 2016 kl. 03:05 skrev Peter Geoghegan : > > On Wed, Aug 10, 2016 at 1:42 PM, Palle Girgensohn wrote: >> They've been used for the FreeBSD ports since 2005, and have served us well. >> I have of course updated them regularly. In this latest

Re: [HACKERS] Assertion failure in REL9_5_STABLE

2016-08-11 Thread Marko Tiikkaja
On 11/08/16 8:48 AM, Michael Paquier wrote: On Thu, Aug 11, 2016 at 8:09 AM, Marko Tiikkaja wrote: On 2016-08-11 12:09 AM, Alvaro Herrera wrote: BTW this is not a regression, right? It's been broken all along. Or am I mistaken? You're probably right. I just hadn't realized

Re: [HACKERS] Slowness of extended protocol

2016-08-11 Thread Vladimir Sitnikov
Shay: > Prepared statements can have very visible effects apart from the speedup > they provide (e.g. failure because of schema changes) It's not that these > effects can't be worked around - they can be - but programmers can be > surprised by these effects, which can cause difficult-to-diagnose

Re: [HACKERS] Slowness of extended protocol

2016-08-11 Thread Shay Rojansky
Vladimir wrote: Shay> As Tom said, if an application can benefit from preparing, the > developer has the responsibility (and also the best knowledge) to manage > preparation, not the driver. Magical behavior under the hood causes > surprises, hard-to-diagnose bugs etc. > > Why do you do C# then?

Re: [HACKERS] new autovacuum criterion for visible pages

2016-08-11 Thread Michael Paquier
On Thu, Aug 11, 2016 at 3:29 PM, Michael Paquier wrote: > On Thu, Aug 11, 2016 at 5:39 AM, Jeff Janes wrote: >> I wanted to create a new relopt named something like >> autovacuum_vacuum_pagevisible_factor which would cause autovacuum to >> vacuum

Re: [HACKERS] Assertion failure in REL9_5_STABLE

2016-08-11 Thread Michael Paquier
On Thu, Aug 11, 2016 at 8:09 AM, Marko Tiikkaja wrote: > On 2016-08-11 12:09 AM, Alvaro Herrera wrote: >> >> BTW this is not a regression, right? It's been broken all along. Or am >> I mistaken? > > > You're probably right. I just hadn't realized I could run our app against > 9.5

Re: [HACKERS] new autovacuum criterion for visible pages

2016-08-11 Thread Michael Paquier
On Thu, Aug 11, 2016 at 5:39 AM, Jeff Janes wrote: > I wanted to create a new relopt named something like > autovacuum_vacuum_pagevisible_factor which would cause autovacuum to > vacuum a table once less than a certain fraction of the relation's > pages are marked

Re: [HACKERS] regression test for extended query protocol

2016-08-11 Thread Michael Paquier
On Thu, Aug 11, 2016 at 5:33 AM, Alvaro Herrera wrote: > Michael Paquier wrote: >> On Fri, Aug 5, 2016 at 12:21 AM, Alvaro Herrera >> wrote: >> > If somebody had some spare time to devote to this, I would suggest to >> > implement something in

Re: [HACKERS] Surprising behaviour of \set AUTOCOMMIT ON

2016-08-11 Thread Venkata Balaji N
On Thu, Aug 11, 2016 at 2:58 PM, Venkata Balaji N wrote: > > On Tue, Aug 9, 2016 at 1:02 AM, Robert Haas wrote: > >> On Mon, Aug 8, 2016 at 10:10 AM, Rahila Syed >> wrote: >> > Thank you for inputs everyone. >> > >> > The