Re: [HACKERS] Performance improvement for joins where outer side is unique

On 20 March 2015 at 21:11, David Rowley wrote: > > I can continue working on your patch if you like? Or are you planning to > go further with it? > > I've been working on this more over the weekend and I've re-factored things to allow LEFT JOINs to be properly marked as unique. I've also made cha

Re: [HACKERS] proposal: searching in array function - array_position

Hi here is updated patch with array_position, array_positions implementation. It is based on committed code - so please, revert commit 13dbc7a824b3f905904cab51840d37f31a07a9ef and apply this patch Regards Pavel 2015-03-20 18:29 GMT+01:00 Alvaro Herrera : > Pavel Stehule wrote: > > 2015-03-20

Re: [HACKERS] Zero-padding and zero-masking fixes for to_char(float)

David Rowley writes: > This seems to have broken jacana. Looks like MSVC by default has a 3 digit > exponent. jacana was broken before this patch; but some other Windows critters are now unhappy as well. > Going by this: > https://msdn.microsoft.com/en-us/library/0fatw238(v=vs.80).aspx it seems

Re: [HACKERS] Using 128-bit integers for sum, avg and statistics aggregates

On 22 March 2015 at 18:17, Michael Paquier wrote: > On Sun, Mar 22, 2015 at 12:32 AM, Tom Lane wrote: > > Andres Freund writes: > >> Pushed with that additional change. Let's see if the buildfarm thinks. > > > > jacana, apparently alone among buildfarm members, does not like it. > > All the win

Re: [HACKERS] Using 128-bit integers for sum, avg and statistics aggregates

On Sun, Mar 22, 2015 at 2:17 PM, Michael Paquier wrote: > On Sun, Mar 22, 2015 at 12:32 AM, Tom Lane wrote: >> Andres Freund writes: >>> Pushed with that additional change. Let's see if the buildfarm thinks. >> >> jacana, apparently alone among buildfarm members, does not like it. > > All the wi

Re: [HACKERS] INT64_MIN and _MAX

> "Andrew" == Andrew Gierth writes: > "Petr" == Petr Jelinek writes: >>> So wouldn't it make more sense to move these definitions into c.h and >>> standardize their usage? Petr> I was thinking the same when I've seen Peter's version of Numeric Petr> abbreviations patch. So +1 for t

Re: [HACKERS] Using 128-bit integers for sum, avg and statistics aggregates

On Sun, Mar 22, 2015 at 12:32 AM, Tom Lane wrote: > Andres Freund writes: >> Pushed with that additional change. Let's see if the buildfarm thinks. > > jacana, apparently alone among buildfarm members, does not like it. All the windows nodes don't pass tests with this patch, the difference is in

Re: [HACKERS] Zero-padding and zero-masking fixes for to_char(float)

On 22 March 2015 at 14:46, Bruce Momjian wrote: > On Wed, Mar 18, 2015 at 05:52:44PM -0400, Bruce Momjian wrote: > > In September, while researching the to_char() buffer overflow bugs fixed > > in 9.4.1 (commit 0150ab567bcf5e5913e2b62a1678f84cc272441f), I found an > > inconsistency in how to_char

Re: [HACKERS] [PATCH] Add transforms feature

2015-03-22 3:55 GMT+01:00 Peter Eisentraut : > Here is an updated patch. > > On 3/17/15 1:11 AM, Pavel Stehule wrote: > > 2015-03-17 2:51 GMT+01:00 Peter Eisentraut > >: > > > > On 3/12/15 8:12 AM, Pavel Stehule wrote: > > > 1. fix missing semicolon pg_proc.h > >

Re: [HACKERS] NUMERIC private methods?

> "Bruce" == Bruce Momjian writes: > ! However, calculations on numeric values is very slow arithmetic ... is, but calculations ... are -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://w

Re: [HACKERS] NUMERIC private methods?

On Thu, Dec 18, 2014 at 11:51:12PM -0500, Tom Lane wrote: > If you're doing any sort of higher math or statistics, I stand by my > statement that you'd better think rather than just blindly assume that > numeric is going to be better for you. A moment's fooling about finds > this example, which is

[HACKERS] debug_sortsupport GUC?

The text abbreviation code has a compile-time option to emit DEBUGn elogs. I made no attempt to add these to the numeric abbreviation code because I find such things completely unhelpful; when you need to investigate such things other than in initial development, it's unlikely that you will be in

Re: [HACKERS] Lets delete src/test/performance

On Thu, Mar 19, 2015 at 09:57:05PM -0400, Bruce Momjian wrote: > > commit cf76759f34a172d424301cfa3723baee37f4a7ce > > Author: Vadim B. Mikheev > > Date: Fri Sep 26 14:55:21 1997 + > > > > Start with performance suite. > > Any objection if I remove the src/test/performance directory an

Re: [HACKERS] No toast table for pg_shseclabel but for pg_seclabel

On Thu, Mar 19, 2015 at 11:50:36AM -0400, Bruce Momjian wrote: > > Then there's the other discussion about using the security labels > > structure for more than just security labels, which could end up with a > > lot of other use-cases where the "label" is even larger. > > OK, the attached patch a

Re: [HACKERS] [PATCH] PostgreSQL 9.4 mmap(2) performance regression on FreeBSD...

On Thu, Mar 19, 2015 at 12:16:07PM -0400, Bruce Momjian wrote: > On Mon, Oct 13, 2014 at 11:35:18AM -0400, Bruce Momjian wrote: > > On Mon, Oct 13, 2014 at 05:21:32PM +0200, Andres Freund wrote: > > > > If we have it, we should improve it, or remove it. We might want to use > > > > this code for s

Re: [HACKERS] PATCH: numeric timestamp in log_line_prefix

Bruce Momjian wrote: > On Sun, Mar 22, 2015 at 02:41:44AM +0100, Tomas Vondra wrote: > > > Uh, I think you mean "number" here: > > > > > > Time stamp without milliseconds (as a numer) > > > - > > > > Oh, right, that's a stupid typ

Re: [HACKERS] PATCH: numeric timestamp in log_line_prefix

On Sun, Mar 22, 2015 at 02:41:44AM +0100, Tomas Vondra wrote: > > Uh, I think you mean "number" here: > > > > Time stamp without milliseconds (as a numer) > > - > > Oh, right, that's a stupid typo. > > > > > Also, what "number"

Re: [HACKERS] Zero-padding and zero-masking fixes for to_char(float)

On Wed, Mar 18, 2015 at 05:52:44PM -0400, Bruce Momjian wrote: > In September, while researching the to_char() buffer overflow bugs fixed > in 9.4.1 (commit 0150ab567bcf5e5913e2b62a1678f84cc272441f), I found an > inconsistency in how to_char() does zero-padding for float4/8 values. > Now that 9.4.

Re: [HACKERS] PATCH: numeric timestamp in log_line_prefix

On 22.3.2015 02:35, Bruce Momjian wrote: > On Sun, Mar 22, 2015 at 12:47:12AM +0100, Tomas Vondra wrote: >> Hi, >> >> from time to time I need to correlate PostgreSQL logs to other logs, >> containing numeric timestamps - a prime example of that is pgbench. With >> %t and %m that's not quite trivia

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

"Joshua D. Drake" writes: > On 03/21/2015 12:45 PM, Gavin Flower wrote: > >>> >> How about 2 config files? >> >> One marked adult^H^H^H^H^H power users only, or some such, with the >> really dangerous or unusual options? >> > > That has come up before in many threads. I don't know that we need to

Re: [HACKERS] PATCH: numeric timestamp in log_line_prefix

On Sun, Mar 22, 2015 at 12:47:12AM +0100, Tomas Vondra wrote: > Hi, > > from time to time I need to correlate PostgreSQL logs to other logs, > containing numeric timestamps - a prime example of that is pgbench. With > %t and %m that's not quite trivial, because of timezones etc. > > I propose add

Re: [HACKERS] printing table in asciidoc with psql

On Sat, Mar 21, 2015 at 09:20:03PM +0900, Michael Paquier wrote: > This does not work: > =# create table "5 2.2+^.^" (); > CREATE TABLE > =# \pset format asciidoc > Output format is asciidoc. > =# \d > > .List of relations > [options="header",cols=" | > ^l|Schema ^l|Name ^l|Type ^l|Owner > |pu

[HACKERS] Display of multi-target-table Modify plan nodes in EXPLAIN

I've gotten the foreign table inheritance patch to a state where I'm almost ready to commit it, but there's one thing that's bothering me, which is what it does for EXPLAIN. As it stands you might get something like regression=# explain (verbose) update pt1 set c1=c1+1;

[HACKERS] PATCH: numeric timestamp in log_line_prefix

Hi, from time to time I need to correlate PostgreSQL logs to other logs, containing numeric timestamps - a prime example of that is pgbench. With %t and %m that's not quite trivial, because of timezones etc. I propose adding two new log_line_prefix escape sequences - %T and %M, doing the same thi

Re: [HACKERS] INT64_MIN and _MAX

> "Petr" == Petr Jelinek writes: >> So wouldn't it make more sense to move these definitions into c.h and >> standardize their usage? Petr> I was thinking the same when I've seen Peter's version of Numeric Petr> abbreviations patch. So +1 for that. Suggested patch attached. -- Andrew

Re: [HACKERS] INT64_MIN and _MAX

On 21/03/15 23:45, Andrew Gierth wrote: A couple of places (adt/timestamp.c and pgbench.c) have this: #ifndef INT64_MAX #define INT64_MAX INT64CONST(0x7FFF) #endif #ifndef INT64_MIN #define INT64_MIN (-INT64CONST(0x7FFF) - 1) #endif On the other hand, int8.c

[HACKERS] INT64_MIN and _MAX

A couple of places (adt/timestamp.c and pgbench.c) have this: #ifndef INT64_MAX #define INT64_MAX INT64CONST(0x7FFF) #endif #ifndef INT64_MIN #define INT64_MIN (-INT64CONST(0x7FFF) - 1) #endif On the other hand, int8.c uses the INT64_MIN expression directly in

Re: [HACKERS] Abbreviated keys for Numeric

Was there some reason why you added #include "utils/memutils.h"? Because I don't see anything in your patch that actually needs it. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

On 03/20/2015 09:29 PM, Michael Paquier wrote: > On Sat, Mar 21, 2015 at 2:47 AM, Peter Geoghegan wrote: >> On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake >> wrote: >>> There are just as many people that are running with scissors that are now >>> running (or attempting to run) our elephant in

[HACKERS] Re[2]: [HACKERS] Remove fsync ON/OFF as a visible option?

why does we take so many attention to fsync issue? but there are also table spaces in tmpfs, wal in tmpfs, disks with cache without bbu, writeback writes and fs without ordering and journal, any CLOUDS, etc etc... in our real world installations. more over not all of these issues are usually in

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

On Sat, Mar 21, 2015 at 2:33 PM, Joshua D. Drake wrote: > > On 03/20/2015 11:28 PM, Jaime Casanova wrote: >> >> > >> I fought to remove fsync before so i understand JD concerns. and yes, >> i have seen fsync=off in the field too... >> >> what about not removing it but not showing it in postgresql.

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

On 22/03/15 08:48, Joshua D. Drake wrote: On 03/21/2015 12:45 PM, Gavin Flower wrote: How about 2 config files? One marked adult^H^H^H^H^H power users only, or some such, with the really dangerous or unusual options? That has come up before in many threads. I don't know that we need to

Re: [HACKERS] PATCH: pgbench - merging transaction logs

Well, fprintf() doesn't have to acquire the lock for the entirety of it's operation - just for the access to the stream buffer. Yep. If it is implemented by appending stuff to the stream as the format is processed, this would still mean the whole time of its operation. Hence preprocessing t

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

On 03/21/2015 12:45 PM, Gavin Flower wrote: How about 2 config files? One marked adult^H^H^H^H^H power users only, or some such, with the really dangerous or unusual options? That has come up before in many threads. I don't know that we need to go down that path again. Consider, power us

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

On 22/03/15 08:34, Joshua D. Drake wrote: On 03/21/2015 12:00 AM, Mark Kirkwood wrote: -1 Personally I'm against hiding *any* settings. Choosing sensible defaults - yes! Hiding them - that reeks of secret squirrel nonsense and overpaid Oracle dbas that knew the undocumented settings for vari

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

On 03/21/2015 12:32 PM, Gavin Flower wrote: What does ACID mean??? I don't want to trip out on acid, and if I do, I don't want it hanging around. Safer to set this to off!!! I actual do know what ACID means, but some 'children' have write access to a the postgresql.conf file without adequat

Re: [HACKERS] PATCH: pgbench - merging transaction logs

Hello Didier, If fprintf takes p = 0.025 (1/40) of the time, then with 2 threads the collision probability would be about 1/40 and the delayed thread would be waiting for half this time on average, so the performance impact due to fprintf locking would be negligeable (1/80 delay occured in 1/40

Re: [HACKERS] PATCH: pgbench - merging transaction logs

Hello Andres, With your worst-case figure and some rounding, it seems to look like: #threadscollision probabilityperformance impact 2 1/401/3200 4 1/7 1/533 8 0.7 < 0.01 (about

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

On 03/21/2015 12:00 AM, Mark Kirkwood wrote: -1 Personally I'm against hiding *any* settings. Choosing sensible defaults - yes! Hiding them - that reeks of secret squirrel nonsense and overpaid Oracle dbas that knew the undocumented settings for various capabilities. I think/hope that no open

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

On 03/20/2015 11:28 PM, Jaime Casanova wrote: I fought to remove fsync before so i understand JD concerns. and yes, i have seen fsync=off in the field too... what about not removing it but not showing it in postgresql.conf? as a side note, i wonder why trace_sort is not in postgresql.conf..

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

On 22/03/15 05:42, David G. Johnston wrote: On Sat, Mar 21, 2015 at 8:54 AM, Tom Lane >wrote: Stephen Frost mailto:sfr...@snowman.net>> writes: > At the moment, one could look at our default postgresql.conf and the > "turns forced synchronization on or off"

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

On 03/20/2015 04:09 PM, Robert Haas wrote: Thus far, the rule for postgresql.conf has been that pretty much everything goes in there, and that's a defensible position. Other reasonable options would be to ship the file with a small handful of settings in it and leave everything else, or to shi

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

On 03/20/2015 04:11 PM, Jim Nasby wrote: As for why; Postgres already has a big reputation for being "hard to use" and "hard to setup". Leaving footguns laying around that could easily be warned about is part of the reason for that reputation. Reality is that there are a lot of people using Pos

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

El mar 21, 2015 2:00 AM, "Mark Kirkwood" escribió: > > On 21/03/15 19:28, Jaime Casanova wrote: >> >> what about not removing it but not showing it in postgresql.conf? as a >> side note, i wonder why trace_sort is not in postgresql.conf... >> other option is to make it a compile setting, that why

Re: [HACKERS] assessing parallel-safety

On 21 March 2015 at 14:28, Amit Kapila wrote: > On Fri, Mar 20, 2015 at 7:54 PM, Thom Brown wrote: > > createdb pgbench > > pgbench -i -s 200 pgbench > > > > CREATE TABLE pgbench_accounts_1 (CHECK (bid = 1)) INHERITS > (pgbench_accounts); > > ... > > CREATE TABLE pgbench_accounts_200 (CHECK (bid

Re: [HACKERS] GSoC 2015: Extra Jsonb functionality

> Frankly, I think the whole proposal needs to be rethought with an eye towards supporting and preserving nested elements instead of trying to just flatten everything out. Can you pls show me few examples what do you mean exactly? On 21 March 2015 at 06:51, Jim Nasby wrote: > On 3/19/15 9:07 AM

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

On Sat, Mar 21, 2015 at 8:54 AM, Tom Lane wrote: > Stephen Frost writes: > > At the moment, one could look at our default postgresql.conf and the > > "turns forced synchronization on or off" and think it's something akin > > or somehow related to synchronous_commit (which is completely different

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

On Sat, Mar 21, 2015 at 8:54 AM, Tom Lane wrote: > Stephen Frost writes: > > At the moment, one could look at our default postgresql.conf and the > > "turns forced synchronization on or off" and think it's something akin > > or somehow related to synchronous_commit (which is completely different

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

On Sat, Mar 21, 2015 at 11:54:00AM -0400, Tom Lane wrote: > Stephen Frost writes: > > At the moment, one could look at our default postgresql.conf and the > > "turns forced synchronization on or off" and think it's something akin > > or somehow related to synchronous_commit (which is completely di

Re: [HACKERS] Future directions for inheritance-hierarchy statistics

On 3/18/15 8:26 AM, Robert Haas wrote: In fact, EnterpriseDB has run into a number of customer situations where planning time even for non-inheritance queries is substantially higher than, shall we say, a competing commercial product. If it's the commercial product I'm thinking of, they use mul

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

Stephen Frost writes: > At the moment, one could look at our default postgresql.conf and the > "turns forced synchronization on or off" and think it's something akin > or somehow related to synchronous_commit (which is completely different, > but the options are right next to each other..). > How

Re: [HACKERS] Using 128-bit integers for sum, avg and statistics aggregates

Andres Freund writes: > Pushed with that additional change. Let's see if the buildfarm thinks. jacana, apparently alone among buildfarm members, does not like it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to

Re: [HACKERS] PATCH: pgbench - merging transaction logs

Hi, On Sat, Mar 21, 2015 at 10:37 AM, Fabien COELHO wrote: >> no logging: 18672 18792 18667 18518 18613 18547 >> with logging: 18170 18093 18162 18273 18307 18234 >> >> So on average, that's 18634 vs. 18206, i.e. less than 2.5% difference. >> And with more expensive transactions (larger scale,

Re: [HACKERS] assessing parallel-safety

On Fri, Mar 20, 2015 at 7:54 PM, Thom Brown wrote: > > On 20 March 2015 at 13:55, Thom Brown wrote: > > On 20 March 2015 at 13:16, Alvaro Herrera wrote: > >> Thom Brown wrote: > >>> On 18 March 2015 at 16:01, Robert Haas wrote: > >>> > On Tue, Mar 17, 2015 at 9:48 AM, Robert Haas wrote: > >>>

Re: [HACKERS] Re: [BUGS] BUG #11805: Missing SetServiceStatus call during service shutdown in pg_ctl (Windows only)

On Fri, Mar 20, 2015 at 9:48 PM, Bruce Momjian wrote: > On Tue, Oct 28, 2014 at 07:02:41AM +, krystian.bi...@gmail.com wrote: >> The following bug has been logged on the website: >> >> Bug reference: 11805 >> Logged by: Krystian Bigaj >> Email address: krystian.bi...@gmail.c

Re: [HACKERS] patch : Allow toast tables to be moved to a different tablespace

On 03/21/2015 01:19 PM, Julien Tachoires wrote: I am confused by your fix. Wouldn't cleaner fix be to use tbinfo->reltablespace rather than tbinfo->reltoasttablespace when calling ArchiveEntry()? Yes, doing this that way is cleaner. Here is a new version including your fix. Thanks. I am now s

Re: [HACKERS] printing table in asciidoc with psql

On Fri, Mar 20, 2015 at 11:10 PM, Bruce Momjian wrote: > I was able to fix all the reported problems with the attached patch. > I used this for testing the output: > > https://asciidoclive.com/ > > Is it OK now? This does not work: =# create table "5 2.2+^.^" (); CREATE TABLE =# \pset form

Re: [HACKERS] patch : Allow toast tables to be moved to a different tablespace

On 20/03/2015 00:33, Andreas Karlsson wrote: > On 03/19/2015 04:55 PM, Julien Tachoires wrote: >> On 18/03/2015 19:54, Andreas Karlsson wrote: >>> Looks good but I think one minor improvement could be to set the table >>> space of the toast entires to the same as the tablespace of the table to >>>

Re: [HACKERS] GIN code managing entry insertion not able to differentiate fresh and old indexes

On Sat, Mar 21, 2015 at 7:27 AM, Bruce Momjian wrote: > On Thu, Nov 20, 2014 at 05:22:02PM +0900, Michael Paquier wrote: >> While playing with the GIN code for an upcoming patch, I noticed that >> when inserting a new entry in a new index, this code path is not able >> to make the difference if the

Re: [HACKERS] PATCH: pgbench - merging transaction logs

On 2015-03-17 11:50:28 -0400, Robert Haas wrote: > On Tue, Mar 17, 2015 at 11:27 AM, Fabien COELHO wrote: > > The fprintf we are talking about occurs at most once per pgbench > > transaction, possibly much less when aggregation is activated, and this > > transaction involves networks exchanges and

Re: [HACKERS] PATCH: pgbench - merging transaction logs

On 2015-03-21 10:37:05 +0100, Fabien COELHO wrote: > > Hello Tomas, > Let us take this as a worst-case figure and try some maths. > > If fprintf takes p = 0.025 (1/40) of the time, then with 2 threads the > collision probability would be about 1/40 and the delayed thread would be > waiting for ha

Re: [HACKERS] PATCH: pgbench - merging transaction logs

Hello Tomas, My point is that if there are many threads and tremendous TPS, the *detailed* per-transaction log (aka simple log) is probably a bad choice anyway, and the aggregated version is the way to go. I disagree with this reasoning. Can you provide numbers supporting it? I'm not sure w

Re: [HACKERS] proposal: searching in array function - array_position

>>> do you have any idea about name for this function? array_position is ok? >> >> +1 on array_position. It's possible at some point we'll actually want >> array_offset that does what it claims. > +1 for array_position. -1 for keeping array_offset. I'm not convinced that there are sufficient use

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

On 21/03/15 19:28, Jaime Casanova wrote: On Fri, Mar 20, 2015 at 11:29 PM, Michael Paquier wrote: On Sat, Mar 21, 2015 at 2:47 AM, Peter Geoghegan wrote: On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake wrote: There are just as many people that are running with scissors that are now running