date:20150321



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 which part of this reasoning you want me to support with 
numbers.


My first argument is qualitative: I'm trying to say that if you are doing 
10 tps, probably the per-transaction detailed measure would be useless 
as such, so there is no point in logging them all. You would rather be 
interested in aggregate figures and should also be interested in extremal 
events (very slow queries) and their frequency.


The second point is that aggregated logs should be cheaper than detailed 
log, it seems more or less self evident to me.



I do agree that fprintf is not cheap, actually when profiling pgbench
it's often the #1 item, but the impact on the measurements is actually
quite small. For example with a small database (scale 10) and read-only
30-second runs (single client), I get this:

  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, writes, ...) the
difference will be much smaller.


Ok. Great!

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 half this time on average, so the performance impact due to 
fprintf locking would be negligeable (1/80 delay occured in 1/40 cases = 
1/3200 time added on the computed average, if I'm not mistaken).


With t threads, I would guess that the collision probability in the first 
order is about 0.5 t (t-1) p, and the performance impact would be ~ (0.5 t 
(t-1) p * 0.5 p) (hmmm... this approximation, if not completely stupid, 
just holds for small p and not too large t).


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 1%)

This suggest that for a pessimistic (ro load) fprintf overhead ratio there 
would be a small impact even with 8 thread doing 2 tps each.



It's true that this might produce large logs, especially when the runs
are long, but that has nothing to do with fprintf. And can be easily
fixed by either using a dedicated client machine, or only sample the
transaction log.


Sure. In which case locking/mutex is not an issue.


Introducing actual synchronization between the threads (by locking
inside fprintf) is however a completely different thing.


It is the issue I'm trying to discuss.


Note that even without mutex fprintf may be considered a heavy
function which is going to slow down the transaction rate
significantly. That could be tested as well.

It is possible to reduce the lock time by preparing the string
(which would mean introducing buffers) and just do a fputs under
mutex. That would not reduce the print time anyway, and that may add
malloc/free operations, though.


I seriously doubt fprintf does the string formatting while holding lock
on the file.


I do not think that the lock is necessarily at the file (OS level), but at 
least it has to hold the FILE structure to manage buffering without 
interference from other threads during string processing and printing.


So by doing this you only simulate what fprintf() does (assuming it's 
thread-safe on your platform) and gain nothing.


Ok, if fprintf is thread safe, I fully agree that there is no need to add 
a mutex or lock! However I do not know how to test that, so putting a 
useless mutex would prevent



The way to know if there's a real problem here is to test it, but
I'd be pretty surprised if there isn't.


Indeed, I think I can contrive a simple example where it is,
basically a more or less empty or read only transaction (eg SELECT
1).


That would be nice, because my quick testing suggests it's not the case.


I do not understand your point. ISTM that your test and my maths suggest
that the performance impact of the fprintf approach is reasonably low.


My opinion is that there is a tradeoff between code simplicity and
later maintenance vs feature benefit.

If threads are assumed and fprintf is used, the feature is much
simpler to implement, and the maintenance is lighter.


I think the if threads are assumed part makes this dead in water
unless someone wants to spend time on getting rid of the thread
emulation.


My suggestion is to skip the feature under thread emulation, not 
necessarily to remove thread emulation, because of the argument you raise 
below.


Removing the code is quite simple, researching whether we

Re: [HACKERS] Abbreviated keys for Numeric

 Peter == Peter Geoghegan p...@heroku.com writes:

  This is simply wrong. The reason why the cost model (in my version)
  tracks non-null values by having its own counter is precisely
  BECAUSE the passed-in memtupcount includes nulls, and therefore the
  code will UNDERESTIMATE the fraction of successfully abbreviated
  values if the comparison is based on memtupcount.

 Peter Oh, right. It's the other way around in your original.

 Peter I don't really buy it, either way. In what sense is a NULL value
 Peter ever abbreviated? It isn't. Whatever about the cost model,
 Peter that's the truth of the matter. There is always going to be a
 Peter sort of tension in any cost model, between whether or not it's
 Peter worth making it more sophisticated, and the extent to which
 Peter tweaking the model is chasing diminishing returns.

Comparisons between nulls and nulls, or between nulls and non-nulls, are
cheap; only comparisons between non-nulls and non-nulls can be
expensive.

The purpose of abbreviation is to replace expensive comparisons by cheap
ones where possible, and therefore the cost model used for abbreviation
should ignore nulls entirely; all that matters is the number of non-null
values and the probability of saving time by abbreviating them.

So if you're sorting a million rows of which 900,000 are null and
100,000 contain 50 different non-null values, then the absolute time
saved (not the proportion) by doing abbreviation should be on the same
order as the absolute time saved by abbreviation when sorting just the
100,000 non-null rows.

But if the cost model does 1,000,000/50 and gets 20,000, and decides
that's worse than my 1 in 10,000 target, I'll abort abbreviations,
then you have sacrificed the time gain for no reason at all.  This is
what I mean by spurious.  This is why the cost model must compute the
fraction as 100,000/50, ignoring the null inputs, if it's going to
perform anything like optimally in the presence of nulls.

 Peter By what objective standard is that spurious?

The objective standard of my wallclock.

Doing a simple count of values abbreviated is not anything that could be
considered complex, and there is nothing at all ad-hoc about it.
Your method is simply incorrect on both logical and performance
grounds.

 Peter Your example has one abbreviated key in it, which is exactly
 Peter worthless among  NULL values.

My example wasn't intended to imply that there would be only one
non-null value in the whole sort, merely to illustrate why nulls need to
be ignored so as not to incorrectly bias the model.

You do not know whether abbreviation will be useful until you have seen
a sufficient number of NON-NULL values.  The problem of the initial
subset of data not necessarily being representative is not relevant to
this.

 Peter I also think that your explanation of the encoding schemes was
 Peter perfunctory.

I'm interested in other opinions on that, because I find your
replacement for it both confusingly organized and a bit misleading (for
example saying the top bit is wasted is wrong, it's reserved because
we need it free for the sign).

(It is true that mine assumes that the reader knows what excess-N
means, or can find out.)

Here's mine, which is given as a single block comment:

+ * Two different representations are used for the abbreviated form, one in
+ * int32 and one in int64, with the int64 one used if USE_FLOAT8_BYVAL is set
+ * (which despite the name is also the flag that says whether int64 is
+ * passed-by-value). In both cases the representation is negated relative to
+ * the original value, because we use the largest negative value for NaN, which
+ * sorts higher than other values. We convert the absolute value of the numeric
+ * to a 31-bit or 63-bit positive value, and then negate it if the original
+ * number was positive.
+ *
+ * The 31-bit value is constructed as:
+ *
+ *  0 + 7bits digit weight + 24 bits digit value
+ *
+ * where the digit weight is in single decimal digits, not digit words, and
+ * stored in excess-44 representation. The 24-bit digit value is the 7 most
+ * significant decimal digits of the value converted to binary. Values whose
+ * weights would fall outside the representable range are rounded off to zero
+ * (which is also used to represent actual zeros) or to 0x7FFF (which
+ * otherwise cannot occur). Abbreviation therefore fails to gain any advantage
+ * where values are outside the range 10^-44 to 10^83, which is not considered
+ * to be a serious limitation, or when values are of the same magnitude and
+ * equal in the first 7 decimal digits, which is considered to be an
+ * unavoidable limitation given the available bits. (Stealing three more bits
+ * to compare another digit would narrow the range of representable weights by
+ * a factor of 8, which starts to look like a real limiting factor.)
+ *
+ * (The value 44 for the excess is essentially arbitrary)
+ *
+ * The 63-bit value is constructed as:
+ *
+ *  0 + 7bits

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

2015-03-21 Thread Andres Freund

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 half this time on average, so the performance impact due to
 fprintf locking would be negligeable (1/80 delay occured in 1/40 cases =
 1/3200 time added on the computed average, if I'm not mistaken).
 
 With t threads, I would guess that the collision probability in the first
 order is about 0.5 t (t-1) p, and the performance impact would be ~ (0.5 t
 (t-1) p * 0.5 p) (hmmm... this approximation, if not completely stupid, just
 holds for small p and not too large t).
 
 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 1%)
 
 This suggest that for a pessimistic (ro load) fprintf overhead ratio there
 would be a small impact even with 8 thread doing 2 tps each.

I think math like this mostly disregards hardware realities. You don't
actually need to have actual lock contention to notice overhead -
frequently acquiring an *uncontended* lock that resides in another
socket's cache and where the cacheline is dirty requires relatively
expensive cross cpu transfers.  That's all besides the overhead of doing
a lock operation itself. A lock; xaddl;, or whatever you end up using,
has a significant cost in itself. It implies a bus lock and cache flush,
which is far from free.

Additionally we're quite possibly talking about more than 8
threads. I've frequently used pgbench with hundreds of threads; for imo
good reasons.

That all said, it's far from guaranteed that there's an actual problem
here. If done right, i.e. the expensive formatting of the string is
separated from the locked output to the kernel, it might end up being
acceptable.

I wonder how bad using unbuffered write + O_APPEND would end up being;
without a lock.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2015-03-21 Thread Pavel Stehule

2015-03-21 0:27 GMT+01:00 Jim Nasby jim.na...@bluetreble.com:

 On 3/20/15 2:48 PM, Pavel Stehule wrote:



 2015-03-20 18:47 GMT+01:00 Tom Lane t...@sss.pgh.pa.us
 mailto:t...@sss.pgh.pa.us:

 Alvaro Herrera alvhe...@2ndquadrant.com
 mailto:alvhe...@2ndquadrant.com writes:
  Pavel Stehule wrote:
  I am thinking, so it is ok - it returns a offset, not position.

  So you can't use it as a subscript?  That sounds unfriendly.  Almost
  every function using this will be subtly broken.

 I concur; perhaps offset was the design intention, but it's wrong.
 The result should be a subscript.


 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.


additional implementation of array_position needs few lines more



 On another note, you mentioned elsewhere that it's not possible to return
 anything other than an integer. Why can't there be a variation of this
 function that returns an array of ndims-1 that is the slice where a value
 was found?


We talked about it, when we talked about MD searching - and we moved it to
next stage.

I am thinking so array_postions can support MD arrays due returning a array

Regards

Pavel




 --
 Jim Nasby, Data Architect, Blue Treble Consulting
 Data in Trouble? Get it in Treble! http://BlueTreble.com

commit 8ceb761fcd4bca3859c0ec371ec783a36795dd49
Author: Pavel Stehule pavel.steh...@gooddata.com
Date:   Sat Mar 21 07:01:51 2015 +0100

initial

diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c
index 57074e0..91e2824 100644
--- a/src/backend/utils/adt/array_userfuncs.c
+++ b/src/backend/utils/adt/array_userfuncs.c
@@ -19,8 +19,8 @@
 #include utils/typcache.h
 
 
-static Datum array_offset_common(FunctionCallInfo fcinfo);
-
+static Datum array_offset_common(FunctionCallInfo fcinfo, bool expected_position);
+static Datum array_offsets_common(FunctionCallInfo fcinfo, bool expected_position);
 
 /*
  * fetch_array_arg_replace_nulls
@@ -669,13 +669,33 @@ array_agg_array_finalfn(PG_FUNCTION_ARGS)
 Datum
 array_offset(PG_FUNCTION_ARGS)
 {
-	return array_offset_common(fcinfo);
+	return array_offset_common(fcinfo, false);
 }
 
 Datum
 array_offset_start(PG_FUNCTION_ARGS)
 {
-	return array_offset_common(fcinfo);
+	return array_offset_common(fcinfo, false);
+}
+
+/*-
+ * array_positiob, array_position_start :
+ *			return the position of a value in an array.
+ *
+ * IS NOT DISTINCT FROM semantics are used for comparisons.  Return NULL when
+ * the value is not found.
+ *-
+ */
+Datum
+array_position(PG_FUNCTION_ARGS)
+{
+	return array_offset_common(fcinfo, true);
+}
+
+Datum
+array_position_start(PG_FUNCTION_ARGS)
+{
+	return array_offset_common(fcinfo, true);
 }
 
 /*
@@ -686,7 +706,7 @@ array_offset_start(PG_FUNCTION_ARGS)
  * They are not strict so we have to test for null inputs explicitly.
  */
 static Datum
-array_offset_common(FunctionCallInfo fcinfo)
+array_offset_common(FunctionCallInfo fcinfo, bool expected_positions)
 {
 	ArrayType  *array;
 	Oid			collation = PG_GET_COLLATION();
@@ -701,6 +721,7 @@ array_offset_common(FunctionCallInfo fcinfo)
 	ArrayMetaState *my_extra;
 	bool		null_search;
 	ArrayIterator array_iterator;
+	intlb = 1;
 
 	if (PG_ARGISNULL(0))
 		PG_RETURN_NULL();
@@ -731,6 +752,8 @@ array_offset_common(FunctionCallInfo fcinfo)
 		null_search = false;
 	}
 
+	lb = expected_positions ? (ARR_LBOUND(array))[0] : 1;
+
 	/* figure out where to start */
 	if (PG_NARGS() == 3)
 	{
@@ -739,7 +762,7 @@ array_offset_common(FunctionCallInfo fcinfo)
 	(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
 	 errmsg(initial offset should not be NULL)));
 
-		offset_min = PG_GETARG_INT32(2);
+		offset_min = PG_GETARG_INT32(2) - lb + 1;
 	}
 	else
 		offset_min = 1;
@@ -818,9 +841,25 @@ array_offset_common(FunctionCallInfo fcinfo)
 	if (!found)
 		PG_RETURN_NULL();
 
-	PG_RETURN_INT32(offset);
+	PG_RETURN_INT32(offset + lb - 1);
+}
+
+/*
+ * simple wrappers over array_offsets_common
+ */
+Datum
+array_offsets(PG_FUNCTION_ARGS)
+{
+	return array_offsets_common(fcinfo, false);
 }
 
+Datum
+array_positions(PG_FUNCTION_ARGS)
+{
+	return array_offsets_common(fcinfo, true);
+}
+
+
 /*-
  * array_offsets :
  *			return an array of offsets of a value in an array.
@@ -833,7 +872,7 @@ array_offset_common(FunctionCallInfo fcinfo)
  *-
  */
 Datum
-array_offsets(PG_FUNCTION_ARGS)
+array_offsets_common(FunctionCallInfo fcinfo, bool expected_positions)
 {
 	ArrayType  *array;
 	Oid			collation = PG_GET_COLLATION();
@@ -847,6 +886,7 @@ array_offsets(PG_FUNCTION_ARGS)
 	bool

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

2015-03-21 Thread Jaime Casanova

On Fri, Mar 20, 2015 at 11:29 PM, Michael Paquier
michael.paqu...@gmail.com wrote:
 On Sat, Mar 21, 2015 at 2:47 AM, Peter Geoghegan p...@heroku.com wrote:
 On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake j...@commandprompt.com 
 wrote:
 There are just as many people that are running with scissors that are now
 running (or attempting to run) our elephant in production. Does it make
 sense to remove fsync (and possibly full_page_writes) from such a visible
 place as postgresql.conf?

 -1

 Anyone turning off fsync without even for a moment considering the
 consequences has only themselves to blame. I can't imagine why you'd
 want to remove full_page_writes or make it less visible either, since
 in principle it ought to be perfectly fine to turn it off in
 production once its verified as safe.

 -1 for its removal as well. It is still useful for developers to
 emulate CPU-bounded loads...

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...
other option is to make it a compile setting, that why if you want to
have it you need to compile and postgres' developers do that routinely
anyway

just my 2c

-- 
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

Stephen Frost sfr...@snowman.net 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 about a big warning around fsync and make it more indepenent from
 the options around it?

Yeah, the main SGML docs are reasonably clear about the risks of fsync,
but postgresql.conf doesn't give you any hint that it's dangerous.  Now
I'm not entirely sure that people who frob postgresql.conf without having
read the docs can be saved from themselves, but we could do something
like this:

 # - Settings -
 
 #wal_level = minimal   # minimal, archive, hot_standby, or 
logical
# (change requires restart)
 #fsync = on# turns forced synchronization on or off
+   # (fsync=off is dangerous, read the
+   # (manual before using it)
 #synchronous_commit = on   # synchronization level;
# off, local, remote_write, or on
 #wal_sync_method = fsync   # the default is the first option
# supported by the operating system:

Also, I think the short description turns forced synchronization on or
off could stand improvement; it really conveys zero information.  Maybe
something like force data to disk when committing?

Also, whatever we do here should be reflected into the description strings
in guc.c.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2015-03-21 Thread David Fetter

On Sat, Mar 21, 2015 at 11:54:00AM -0400, Tom Lane wrote:
 Stephen Frost sfr...@snowman.net 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 about a big warning around fsync and make it more indepenent from
  the options around it?
 
 Yeah, the main SGML docs are reasonably clear about the risks of fsync,
 but postgresql.conf doesn't give you any hint that it's dangerous.  Now
 I'm not entirely sure that people who frob postgresql.conf without having
 read the docs can be saved from themselves, but we could do something
 like this:
 
  # - Settings -
  
  #wal_level = minimal # minimal, archive, hot_standby, or 
 logical
   # (change requires restart)
  #fsync = on  # turns forced synchronization on or off
 +   # (fsync=off is dangerous, read the
 +   # (manual before using it)

I think this will help people who find themselves in that file with
few (or wrong) ideas of what this does.

  #synchronous_commit = on # synchronization level;
   # off, local, remote_write, or on
  #wal_sync_method = fsync # the default is the first option
   # supported by the operating system:
 
 Also, I think the short description turns forced synchronization on or
 off could stand improvement; it really conveys zero information.  Maybe
 something like force data to disk when committing?
 
 Also, whatever we do here should be reflected into the description strings
 in guc.c.

I don't suppose there's a way to have a single point of truth...

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2015-03-21 Thread David G. Johnston

On Sat, Mar 21, 2015 at 8:54 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 Stephen Frost sfr...@snowman.net 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 about a big warning around fsync and make it more indepenent from
  the options around it?

 Yeah, the main SGML docs are reasonably clear about the risks of fsync,
 but postgresql.conf doesn't give you any hint that it's dangerous.  Now
 I'm not entirely sure that people who frob postgresql.conf without having
 read the docs can be saved from themselves, but we could do something
 like this:

  # - Settings -

  #wal_level = minimal   # minimal, archive, hot_standby,
 or logical
 # (change requires restart)
  #fsync = on# turns forced synchronization on
 or off
 +   # (fsync=off is dangerous, read the
 +   # (manual before using it)
  #synchronous_commit = on   # synchronization level;
 # off, local, remote_write, or on
  #wal_sync_method = fsync   # the default is the first option
 # supported by the operating
 system:

 Also, I think the short description turns forced synchronization on or
 off could stand improvement; it really conveys zero information.  Maybe
 something like force data to disk when committing?

 Also, whatever we do here should be reflected into the description strings
 in guc.c.


enables or disables data durability promise of ACID. ?

David J.

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

2015-03-21 Thread Jim Nasby


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 multiple levels 
of caching to avoid both parse costs as well as plan costs. It's always 
impressed me that we didn't have to resort to such shenanigans, but 
perhaps there's only so long we can avoid them.

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2015-03-21 Thread Jeff Janes

On Sat, Mar 21, 2015 at 8:54 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 Stephen Frost sfr...@snowman.net 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 about a big warning around fsync and make it more indepenent from
  the options around it?

 Yeah, the main SGML docs are reasonably clear about the risks of fsync,
 but postgresql.conf doesn't give you any hint that it's dangerous.  Now
 I'm not entirely sure that people who frob postgresql.conf without having
 read the docs can be saved from themselves, but we could do something
 like this:

  # - Settings -

  #wal_level = minimal   # minimal, archive, hot_standby,
 or logical
 # (change requires restart)
  #fsync = on# turns forced synchronization on
 or off
 +   # (fsync=off is dangerous, read the
 +   # (manual before using it)
  #synchronous_commit = on   # synchronization level;
 # off, local, remote_write, or on
  #wal_sync_method = fsync   # the default is the first option
 # supported by the operating
 system:

 Also, I think the short description turns forced synchronization on or
 off could stand improvement; it really conveys zero information.  Maybe
 something like force data to disk when committing?


I agree the current description is lacking, but that proposed wording would
be a better description of synchronous_commit.  It is checkpointing and
flush-WAL-before-data where fsync=off does its damage.

Force data to disk when needed for integrity?

Or just don't describe what it is at all, and refer to the documentation
only.

Cheers,

Jeff

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

Andres Freund and...@2ndquadrant.com 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 your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] GSoC 2015: Extra Jsonb functionality

2015-03-21 Thread Dmitry Dolgov

 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 jim.na...@bluetreble.com wrote:

 On 3/19/15 9:07 AM, Thom Brown wrote:

  jsonb_to_array
  --
 {a, 1, b, c, 2, d, 3, 4}

 Is there a use-case for the example you've given above, where you take
 JSON containing objects and arrays, and flatten them out into a
 one-dimensional array?


 There are a lot of things proposed here that are completely ignoring the
 idea of nested elements, which I think is a big mistake.

 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. If a user wanted things flat they would have just
 started with that in the first place.
 --
 Jim Nasby, Data Architect, Blue Treble Consulting
 Data in Trouble? Get it in Treble! http://BlueTreble.com

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

2015-03-21 Thread Andreas Karlsson


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 satisfied with how the patch looks. Thanks for your work. I 
will mark this as ready for committeer now but not expect any committer 
to look at it until the commitfest starts.


--
Andreas Karlsson


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 index is in a build state or not.
 Basically, when entering in ginEntryInsert@gininsert.c GinBtree built
 via ginPrepareEntryScan does not have its flag isBuild set up
 properly. I think that it should be set as follows to let this code
 path be aware that index is in build state:
 btree.isBuild = (buildStats != NULL);

 Note that the entry insertion code does nothing with isBuild yet, so
 it does not really impact back-branches. However, if in the future we
 fix a bug in this area and need to make distinction between a fresh
 index and an old one well there will be problems. For those reasons,
 this correctness fix should be perhaps master-only for now (perhaps
 even 9.4 stuff as well).

 Where did we leave this?

I recall Heikki mentioning me that the code paths where
ginPrepareEntryScan is called do not make use of isBuild, so it does
not matter much now to not fix it... But *if* there is a new feature
implemented in gin that makes use of the flag isBuild there will be
problems, so I am of the opinion to push a fix for correctness.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] assessing parallel-safety

2015-03-21 Thread Amit Kapila

On Fri, Mar 20, 2015 at 7:54 PM, Thom Brown t...@linux.com wrote:

 On 20 March 2015 at 13:55, Thom Brown t...@linux.com wrote:
  On 20 March 2015 at 13:16, Alvaro Herrera alvhe...@2ndquadrant.com
wrote:
  Thom Brown wrote:
  On 18 March 2015 at 16:01, Robert Haas robertmh...@gmail.com wrote:
   On Tue, Mar 17, 2015 at 9:48 AM, Robert Haas robertmh...@gmail.com
wrote:
   On Tue, Mar 17, 2015 at 2:26 AM, Noah Misch n...@leadboat.com
wrote:
   Neither that rule, nor its variant downthread, would hurt
operator authors too
   much.  To make the planner categorically parallel-safe, though,
means limiting
   evaluate_function() to parallel-safe functions.  That would
dramatically slow
   selected queries.  It's enough for the PL scenario if planning a
parallel-safe
   query is itself parallel-safe.  If the planner is parallel-unsafe
when
   planning a parallel-unsafe query, what would suffer?
  
   Good point.  So I guess the rule can be that planning a
parallel-safe
   query should be parallel-safe.  From there, it follows that
estimators
   for a parallel-safe operator must also be parallel-safe.  Which
seems
   fine.
  
   More work is needed here, but for now, here is a rebased patch, per
   Amit's request.
 
  This no longer applies due to changes in commit
  13dbc7a824b3f905904cab51840d37f31a07a9ef.
 
  You should be able to drop the pg_proc.h changes and run the supplied
  perl program.  (I'm not sure that sending the patched pg_proc.h
together
  with this patch is all that useful, really.)
 
  Thanks.  All patches applied and building okay.

 Okay, breakage experienced, but not sure which thread this belongs on.


I think if you face the issue issue after applying parallel_seqscan patch,
then you can report on that thread and if it turns out to be something
related to other thread, then we can shift the discussion of resolution
on that thread.

 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 = 200)) INHERITS
 (pgbench_accounts);


I managed to reproduce the Assertion reported by you as:

#2  0x007a053a in ExceptionalCondition
(conditionName=conditionName@entry=0x813a4b
!(IsInParallelMode()), errorType=errorType@entry=0x7da1d6
FailedAssertion, fileName=fileName@entry=0x81397d parallel.c,
lineNumber=lineNumber@entry=123) at assert.c:54
#3  0x004cd5ba in CreateParallelContext (entrypoint=entrypoint@entry
=0x659d2c ParallelQueryMain, nworkers=nworkers@entry=8) at parallel.c:123

The reason is that CreateParallelContext() expects to be called
in ParallelMode and we enter into parallel-mode after InitPlan()
in standard_ExecutorStart().  So the probable fix could be
to EnterParallelMode before initializing the plan.

I still could not reproduce the crash you have reported as:
 #0  0x00770843 in pfree ()
 #1  0x005a382f in ExecEndFunnel ()
 #2  0x0059fe75 in ExecEndAppend ()
 #3  0x005920bd in standard_ExecutorEnd ()


Could you let me know which all patches you have tried
and on top of which commit.

I am trying on the commit as mentioned in mail[1]. Basically
have you tried the versions mentioned in that mail:

HEAD Commit-id : 8d1f2390
parallel-mode-v8.1.patch [2]
assess-parallel-safety-v4.patch [1]
parallel-heap-scan.patch [3]
parallel_seqscan_v11.patch (Attached with this mail)

If something else, could you let me know the same so that I can try
that to reproduce the issue reported by you.

[1]
http://www.postgresql.org/message-id/CAA4eK1JSSonzKSN=l-dwucewdlqkbmujvfpe3fgw2tn2zpo...@mail.gmail.com


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

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

2015-03-21 Thread didier

Hi,

On Sat, Mar 21, 2015 at 10:37 AM, Fabien COELHO coe...@cri.ensmp.fr 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, writes, ...) the
 difference will be much smaller.


 Ok. Great!

 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 half this time on average, so the performance impact due to
 fprintf locking would be negligeable (1/80 delay occured in 1/40 cases =
 1/3200 time added on the computed average, if I'm not mistaken).
If  threads run more or less the same code with the same timing after
a while they will lockstep  on synchronization primitives and your
collision probability will be very close to 1.

Moreover  they will write to the same cache lines for every fprintf
and this is very very bad even without atomic operations.

Regards
Didier


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 br...@momjian.us 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.com
 PostgreSQL version: 9.3.5
 Operating system:   Windows 7 Pro x64
 Description:

 pg_ctl on Windows during service start/shutdown should notify service
 manager about it's status by increment dwCheckPoint and call to
 SetServiceStatus/pgwin32_SetServiceStatus.

 However during shutdown there is a missing call to SetServiceStatus.
 See src\bin\pg_ctl\pg_ctl.c:

 [ thread moved to hackers ]

 Can a Windows person look into this issue?


 http://www.postgresql.org/message-id/20141028070241.2593.58...@wrigleys.postgresql.org

 The thread includes a patch.  I need a second person to verify its
 validity.  Thanks.

FWIW, it looks sane to me to do so, ServiceMain declaration is in
charge to start the service, and to wait for the postmaster to stop,
and indeed process may increment dwcheckpoint in -w mode, and it
expects for process to wait for 12 times but this promise is broken.
The extra calls to SetServiceStatus are also welcome to let the SCM
know the current status in more details.

A back-patch would be good as well...
Regards,
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 format asciidoc
Output format is asciidoc.
=# \d

.List of relations
[options=header,cols=l,l,l,l,frame=none]
|
^l|Schema ^l|Name ^l|Type ^l|Owner
|public|5 2.2+^.^|table|ioltas
|


(1 row)


I think that we should really put additional spaces on the left side
of the column separators |. For example, this line:
|public|5 2.2+^.^|table|ioltas
should become that:
|public |5 2.2+^.^ |table |ioltas
And there is no problem.
Regards,
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2015-03-21 Thread Julien Tachoires

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
 reduce the amount of SET default_tablespace. What do you think?

 Yes, you're right, some useless SET default_tablespace were added for
 each ALTER TABLE SET TOAST TABLESPACE statement. It's now fixed with
 this new patch. Thanks.
 
 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.

--
Julien


set_toast_tablespace_v0.17.patch.gz
Description: application/gzip

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 Postgres that are
nowhere close to being DBAs and making it easy for them to munch their
data on accident doesn't help anyone.


Exactly.

JD



--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc

Now I get it: your service is designed for a customer
base that grew up with Facebook, watches Japanese seizure
robot anime, and has the attention span of a gnat.
I'm not that user., Tyler Riddle



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 users don't need a separate 
config. They can create their own or use alter system.


JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc

Now I get it: your service is designed for a customer
base that grew up with Facebook, watches Japanese seizure
robot anime, and has the attention span of a gnat.
I'm not that user., Tyler Riddle



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] assessing parallel-safety

2015-03-21 Thread Thom Brown

On 21 March 2015 at 14:28, Amit Kapila amit.kapil...@gmail.com wrote:

 On Fri, Mar 20, 2015 at 7:54 PM, Thom Brown t...@linux.com 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 = 200)) INHERITS
  (pgbench_accounts);
 

 I managed to reproduce the Assertion reported by you as:

 #2  0x007a053a in ExceptionalCondition
 (conditionName=conditionName@entry=0x813a4b !(IsInParallelMode()),
 errorType=errorType@entry=0x7da1d6 FailedAssertion,
 fileName=fileName@entry=0x81397d parallel.c, lineNumber=lineNumber@entry
 =123) at assert.c:54
 #3  0x004cd5ba in CreateParallelContext
 (entrypoint=entrypoint@entry=0x659d2c ParallelQueryMain,
 nworkers=nworkers@entry=8) at parallel.c:123

 The reason is that CreateParallelContext() expects to be called
 in ParallelMode and we enter into parallel-mode after InitPlan()
 in standard_ExecutorStart().  So the probable fix could be
 to EnterParallelMode before initializing the plan.

 I still could not reproduce the crash you have reported as:
  #0  0x00770843 in pfree ()
  #1  0x005a382f in ExecEndFunnel ()
  #2  0x0059fe75 in ExecEndAppend ()
  #3  0x005920bd in standard_ExecutorEnd ()


 Could you let me know which all patches you have tried
 and on top of which commit.

 I am trying on the commit as mentioned in mail[1]. Basically
 have you tried the versions mentioned in that mail:

 HEAD Commit-id : 8d1f2390
 parallel-mode-v8.1.patch [2]
 assess-parallel-safety-v4.patch [1]
 parallel-heap-scan.patch [3]
 parallel_seqscan_v11.patch (Attached with this mail)

 If something else, could you let me know the same so that I can try
 that to reproduce the issue reported by you.


Looks like one of the patches I applied is newer than the one in your list:

HEAD Commit-id: 13a10c0ccd984643ef88997ac177da7c4b7e46a6
parallel-mode-v9.patch
assess-parallel-safety-v4.patch
parallel-heap-scan.patch
parallel_seqscan_v11.patch

-- 
Thom

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

2015-03-21 Thread Gavin Flower


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 various
capabilities. I think/hope that no open source project will try to
emulate that meme!


I don't agree with this at all. On the one hand:

Postgres: So many settings people have no idea where to start (unless 
they have background)


vs

Postgres: Only the settings that are needed for 95% of installations.

JD



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?



Cheers,
Gavin


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2015-03-21 Thread Gavin Flower


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 
go down that path again. Consider, power users don't need a separate 
config. They can create their own or use alter system.


JD


How about This file must not be changed by children, unless under 
competent adult supervision?


Yeah, I know, that will never happen.  (Tempting as it might be!)


Cheers,
Gavin


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 1%)

This suggest that for a pessimistic (ro load) fprintf overhead ratio there
would be a small impact even with 8 thread doing 2 tps each.


I think math like this mostly disregards hardware realities.


Hmmm. In my mind, doing the maths helps understand what may be going on.

Note that it does not preclude to check afterwards that it does indeed 
correspond to reality:-)


The key suggestion of the maths is that if p*t  1 all is (seems) well.

You don't actually need to have actual lock contention to notice 
overhead.


The overhead assumed is 1/40 of the transaction time from Tomas' measures. 
Given the ~ 18000 tps (we are talking of an in-memory read-only load 
probably on the same host), transaction time for pgbench seems to be about 
0.06 ms, and fprintf seems to be about 0.0015 ms (1.5 µs).


- frequently acquiring an *uncontended* lock that resides in another 
socket's cache and where the cacheline is dirty requires relatively 
expensive cross cpu transfers. That's all besides the overhead of doing 
a lock operation itself. A lock; xaddl;, or whatever you end up using, 
has a significant cost in itself. It implies a bus lock and cache flush, 
which is far from free.


Ok, I did not assume an additional lock cost. Do you have a figure? A 
quick googling suggested figure for lightweight mutexes around 100 ns, 
but the test conditions were unclear. If it is oky, then it is does not 
change much the above maths to add that overhead.


Additionally we're quite possibly talking about more than 8 threads. 
I've frequently used pgbench with hundreds of threads; for imo good 
reasons.


Good for you. I do not have access to a host on which this would make 
sense:-)



That all said, it's far from guaranteed that there's an actual problem
here. If done right, i.e. the expensive formatting of the string is
separated from the locked output to the kernel, it might end up being
acceptable.


That is what I would like to assess. Indeed, probably snprinf (to avoid 
mallocing anything) and then fputs/write/whatever would indeed help reduce 
the contention probability, if not the actual overhead.


--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 source project will try to
emulate that meme!


I don't agree with this at all. On the one hand:

Postgres: So many settings people have no idea where to start (unless 
they have background)


vs

Postgres: Only the settings that are needed for 95% of installations.

JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc

Now I get it: your service is designed for a customer
base that grew up with Facebook, watches Japanese seizure
robot anime, and has the attention span of a gnat.
I'm not that user., Tyler Riddle



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 cases =
1/3200 time added on the computed average, if I'm not mistaken).



If  threads run more or less the same code with the same timing after
a while they will lockstep  on synchronization primitives and your
collision probability will be very close to 1.


I'm not sure I understand. If transaction times were really constant, then 
after a while the mutexes would be synchronised so as to avoid contention, 
i.e. the collision probability would be 0?



Moreover  they will write to the same cache lines for every fprintf
and this is very very bad even without atomic operations.


We're talking of transactions that involve network messages and possibly 
disk IOs on the server, so some cache issues issues within pgbench would 
not be a priori the main performance driver.


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 adequate 'adult' supervision!


Some? I make my living babysitting, even some of our best clients have 
some ruby on rails developer constantly saying, Dude, like the DB 
doesn't matter man... we can just (takes long sip of organic stock) 
horizontally partition this stuff.


Sincerely,

JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc

Now I get it: your service is designed for a customer
base that grew up with Facebook, watches Japanese seizure
robot anime, and has the attention span of a gnat.
I'm not that user., Tyler Riddle



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2015-03-21 Thread Jaime Casanova

El mar 21, 2015 2:00 AM, Mark Kirkwood mark.kirkw...@catalyst.net.nz
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 if you want to
 have it you need to compile and postgres' developers do that routinely
 anyway


 -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 source project will try to emulate that meme!


That ship has already sailed.

http://www.postgresql.org/docs/9.4/static/runtime-config-developer.html

--
Jaime Casanova
2ndQuadrant Consultant
Your PostgreSQL partner

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

2015-03-21 Thread Gavin Flower


On 22/03/15 05:42, David G. Johnston wrote:
On Sat, Mar 21, 2015 at 8:54 AM, Tom Lane t...@sss.pgh.pa.us 
mailto:t...@sss.pgh.pa.uswrote:


Stephen Frost sfr...@snowman.net mailto:sfr...@snowman.net 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 about a big warning around fsync and make it more indepenent
from
 the options around it?

Yeah, the main SGML docs are reasonably clear about the risks of
fsync,
but postgresql.conf doesn't give you any hint that it's
dangerous.  Now
I'm not entirely sure that people who frob postgresql.conf without
having
read the docs can be saved from themselves, but we could do something
like this:

 # - Settings -

 #wal_level = minimal   # minimal, archive,
hot_standby, or logical
# (change requires restart)
 #fsync = on# turns forced
synchronization on or off
+   # (fsync=off is dangerous,
read the
+   # (manual before using it)
 #synchronous_commit = on   # synchronization level;
# off, local,
remote_write, or on
 #wal_sync_method = fsync   # the default is the first
option
# supported by the
operating system:

Also, I think the short description turns forced synchronization
on or
off could stand improvement; it really conveys zero information. 
Maybe

something like force data to disk when committing?

Also, whatever we do here should be reflected into the description
strings
in guc.c.


 enables or disables data durability promise of ACID. ?

David J.



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 adequate 'adult' supervision!



Cheers,
Gavin


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 ship it completely
empty of comments with only those settings that initdb sets and
nothing else.  I'd be OK a coherent policy change in this area, but
just removing one or two setting seems like it will be confusing
rather than helpful.


I would agree with this. I imagine there is only about a dozen, dozen 
and a half that need to be there by default.


JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc

Now I get it: your service is designed for a customer
base that grew up with Facebook, watches Japanese seizure
robot anime, and has the attention span of a gnat.
I'm not that user., Tyler Riddle



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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


That is the original proposal. I am not suggesting that it not be an 
option. I am suggesting that it is not in postgresql.conf by default.


JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc

Now I get it: your service is designed for a customer
base that grew up with Facebook, watches Japanese seizure
robot anime, and has the attention span of a gnat.
I'm not that user., Tyler Riddle



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 the string as you suggested may be desirable.


Note that posix 2001 *does* guarantee that FILE* style IO is thread
safe:
All functions that reference (FILE *) objects, except those with names
ending in _unlocked, shall behave as if they use flockfile() and
funlockfile() internally to obtain ownership of these (FILE *) objects.

Hilariously that tidbit hidden in the documentation about
flockfile. Very, err, easy to find:
http://pubs.opengroup.org/onlinepubs/9699919799/functions/flockfile.html


Thanks for the pointer!

--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2015-03-21 Thread Jaime Casanova

On Sat, Mar 21, 2015 at 2:33 PM, Joshua D. Drake j...@commandprompt.com 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.conf? as a
 side note, i wonder why trace_sort is not in postgresql.conf...


 That is the original proposal. I am not suggesting that it not be an option.
 I am suggesting that it is not in postgresql.conf by default.



you're right, i misunderstood... anyway i don't feel there's a need to
avoid people putting in there...
just don't ship with the guc in there, if someone puts it by himself
that's completely another thing

-- 
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2015-03-21 Thread Миша Тюрин



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 dba's medium, and what dba 
really have to do -- is to accept ugly bottom storage properties and DO 
properly PITR/standby.
and if we have PITR then fsync or not fsync in master host doesn't matter so 
much. and could matter providing fsync in archive host. but doing fsync in 
archive -- it is workaround for archive_command realization.
in conclusion -- imho, full-page writes is more sensual than fsync when we 
guarantee PITR.
-- misha

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

2015-03-21 Thread Josh Berkus

On 03/20/2015 09:29 PM, Michael Paquier wrote:
On Sat, Mar 21, 2015 at 2:47 AM, Peter Geoghegan p...@heroku.com wrote:
On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake j...@commandprompt.com
wrote:
There are just as many people that are running with scissors that are now
running (or attempting to run) our elephant in production. Does it make
sense to remove fsync (and possibly full_page_writes) from such a visible
place as postgresql.conf?

-1

Anyone turning off fsync without even for a moment considering the
consequences has only themselves to blame. I can't imagine why you'd
want to remove full_page_writes or make it less visible either, since
in principle it ought to be perfectly fine to turn it off in
production once its verified as safe.

-1 for its removal as well. It is still useful for developers to
emulate CPU-bounded loads...

Coincidentally, I am just at this moment performance testing running
with scissors mode for PostgreSQL on AWS. When intentional, this mode
is useful for spinning up lots of read-only replicas which are intended
mainly as cache support, something I've done at various dot-coms. So,
-1 on removing the setting; it is useful to some users.

Further, full_page_writes=off is supposedly safe on any copy-on-write
filesystem, such as ZFS. Since that can cut fsync time by as much as
30%, -1 to remove/hide that setting either.

The proposal that we make certain settings only available via ALTER
SYSTEM also doesn't make sense; ALTER SYSTEM isn't capable of writing
any settings which aren't available in postgresql.conf.

Now, I have *long* been an advocate that we should ship a stripped
PostgreSQL.conf which has only the most commonly used settings, and
leave the rest of the settings in the docs and
share/postgresql/postgresql.conf.advanced. Here's my example of such a
file, tailored to PostgreSQL 9.3:

https://github.com/pgexperts/accidentalDBA/blob/master/vagrant/setup/postgres/postgresql.conf

While we likely wouldn't want to ship all of the advice in the comments
in that file (the calculations, in particular, have been questioned
since they were last tested with PostgreSQL 8.3), that gives you an
example of what a simple/mainstream pg.conf could look like. I would
further advocate that that file be broken up into segments (resources,
logging, connects, etc.) and placed in conf.d/

All that being said, what *actually* ships with PostgreSQL is up to the
packagers, so anything we do with pg.conf will have a limited impact
unless we get them on board with the idea.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] INT64_MIN and _MAX

2015-03-21 Thread Petr Jelinek


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 uses the INT64_MIN expression directly inline.

On the third hand, INT64_MIN etc. would typically be defined in stdint.h
if it exists.

So wouldn't it make more sense to move these definitions into c.h and
standardize their usage?



I was thinking the same when I've seen Peter's version of Numeric 
abbreviations patch. So +1 for that.


--
 Petr Jelinek  http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] INT64_MIN and _MAX

 Petr == Petr Jelinek p...@2ndquadrant.com 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 (irc:RhodiumToad)

diff --git a/contrib/btree_gist/btree_ts.c b/contrib/btree_gist/btree_ts.c
index b9c2b49..d472d49 100644
--- a/contrib/btree_gist/btree_ts.c
+++ b/contrib/btree_gist/btree_ts.c
@@ -153,7 +153,7 @@ ts_dist(PG_FUNCTION_ARGS)
 		p-day = INT_MAX;
 		p-month = INT_MAX;
 #ifdef HAVE_INT64_TIMESTAMP
-		p-time = INT64CONST(0x7FFF);
+		p-time = INT64_MAX;
 #else
 		p-time = DBL_MAX;
 #endif
@@ -181,7 +181,7 @@ tstz_dist(PG_FUNCTION_ARGS)
 		p-day = INT_MAX;
 		p-month = INT_MAX;
 #ifdef HAVE_INT64_TIMESTAMP
-		p-time = INT64CONST(0x7FFF);
+		p-time = INT64_MAX;
 #else
 		p-time = DBL_MAX;
 #endif
diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index 706fdf5..822adfd 100644
--- a/contrib/pgbench/pgbench.c
+++ b/contrib/pgbench/pgbench.c
@@ -49,10 +49,6 @@
 #include sys/resource.h		/* for getrlimit */
 #endif
 
-#ifndef INT64_MAX
-#define INT64_MAX	INT64CONST(0x7FFF)
-#endif
-
 #ifndef M_PI
 #define M_PI 3.14159265358979323846
 #endif
@@ -453,7 +449,7 @@ strtoint64(const char *str)
 		 */
 		if (strncmp(ptr, 9223372036854775808, 19) == 0)
 		{
-			result = -INT64CONST(0x7fff) - 1;
+			result = INT64_MIN;
 			ptr += 19;
 			goto gotdigits;
 		}
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index e2d187f..656d55b 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -1408,7 +1408,7 @@ WALInsertLockAcquireExclusive(void)
 	{
 		LWLockAcquireWithVar(WALInsertLocks[i].l.lock,
 			 WALInsertLocks[i].l.insertingAt,
-			 UINT64CONST(0x));
+			 UINT64_MAX);
 	}
 	LWLockAcquireWithVar(WALInsertLocks[i].l.lock,
 		 WALInsertLocks[i].l.insertingAt,
diff --git a/src/backend/utils/adt/int8.c b/src/backend/utils/adt/int8.c
index 56d909a..b3a1191 100644
--- a/src/backend/utils/adt/int8.c
+++ b/src/backend/utils/adt/int8.c
@@ -78,7 +78,7 @@ scanint8(const char *str, bool errorOK, int64 *result)
 		 */
 		if (strncmp(ptr, 9223372036854775808, 19) == 0)
 		{
-			tmp = -INT64CONST(0x7fff) - 1;
+			tmp = INT64_MIN;
 			ptr += 19;
 			goto gotdigits;
 		}
diff --git a/src/backend/utils/adt/numutils.c b/src/backend/utils/adt/numutils.c
index d77799a..585da1e 100644
--- a/src/backend/utils/adt/numutils.c
+++ b/src/backend/utils/adt/numutils.c
@@ -190,7 +190,7 @@ pg_lltoa(int64 value, char *a)
 	 * Avoid problems with the most negative integer not being representable
 	 * as a positive integer.
 	 */
-	if (value == (-INT64CONST(0x7FFF) - 1))
+	if (value == INT64_MIN)
 	{
 		memcpy(a, -9223372036854775808, 21);
 		return;
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 723c670..33e859d 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -44,14 +44,6 @@
 
 #define SAMESIGN(a,b)	(((a)  0) == ((b)  0))
 
-#ifndef INT64_MAX
-#define INT64_MAX	INT64CONST(0x7FFF)
-#endif
-
-#ifndef INT64_MIN
-#define INT64_MIN	(-INT64CONST(0x7FFF) - 1)
-#endif
-
 /* Set at postmaster start */
 TimestampTz PgStartTime;
 
diff --git a/src/backend/utils/adt/txid.c b/src/backend/utils/adt/txid.c
index f973ef9..31f8033 100644
--- a/src/backend/utils/adt/txid.c
+++ b/src/backend/utils/adt/txid.c
@@ -34,7 +34,7 @@
 
 
 /* txid will be signed int8 in database, so must limit to 63 bits */
-#define MAX_TXID   UINT64CONST(0x7FFF)
+#define MAX_TXID   ((uint64) INT64_MAX)
 
 /* Use unsigned variant internally */
 typedef uint64 txid;
diff --git a/src/include/c.h b/src/include/c.h
index 7447218..e3ed527 100644
--- a/src/include/c.h
+++ b/src/include/c.h
@@ -284,6 +284,17 @@ typedef unsigned long long int uint64;
 #define UINT64CONST(x) ((uint64) x)
 #endif
 
+/* should be defined in stdint.h */
+#ifndef INT64_MIN
+#define INT64_MIN	(-INT64CONST(0x7FFF) - 1)
+#endif
+#ifndef INT64_MAX
+#define INT64_MAX	INT64CONST(0x7FFF)
+#endif
+#ifndef UINT64_MAX
+#define UINT64_MAX	UINT64CONST(0x)
+#endif
+
 /* snprintf format strings to use for 64-bit integers */
 #define INT64_FORMAT % INT64_MODIFIER d
 #define UINT64_FORMAT % INT64_MODIFIER u
diff --git a/src/include/datatype/timestamp.h b/src/include/datatype/timestamp.h
index 6dfaf23..d3450d6 100644
--- a/src/include/datatype/timestamp.h
+++ b/src/include/datatype/timestamp.h
@@ -119,8 +119,8 @@ typedef struct
  * DT_NOBEGIN represents timestamp -infinity; DT_NOEND represents +infinity
  */
 #ifdef HAVE_INT64_TIMESTAMP
-#define DT_NOBEGIN		(-INT64CONST(0x7fff) - 1)
-#define DT_NOEND		(INT64CONST(0x7fff))
+#define DT_NOBEGIN		INT64_MIN
+#define

[HACKERS] PATCH: numeric timestamp in log_line_prefix

2015-03-21 Thread Tomas Vondra

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 thing as %t and %m, but formatting the value as a number.

Patch attached, I'll add it to CF 2015-06.

regards

-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index b30c68d..7f39b18 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -4636,11 +4636,21 @@ local0.*/var/log/postgresql
  entryno/entry
 /row
 row
+ entryliteral%T/literal/entry
+ entryTime stamp without milliseconds (as a numer)/entry
+ entryno/entry
+/row
+row
  entryliteral%m/literal/entry
  entryTime stamp with milliseconds/entry
  entryno/entry
 /row
 row
+ entryliteral%M/literal/entry
+ entryTime stamp with milliseconds (as a number)/entry
+ entryno/entry
+/row
+row
  entryliteral%i/literal/entry
  entryCommand tag: type of session's current command/entry
  entryyes/entry
diff --git a/src/backend/utils/error/elog.c b/src/backend/utils/error/elog.c
index b952c7c..abafdd9 100644
--- a/src/backend/utils/error/elog.c
+++ b/src/backend/utils/error/elog.c
@@ -2428,6 +2428,19 @@ log_line_prefix(StringInfo buf, ErrorData *edata)
 else
 	appendStringInfoString(buf, formatted_log_time);
 break;
+			case 'M':
+{
+	struct timeval tv;
+	char timestamp_str[FORMATTED_TS_LEN];
+
+	gettimeofday(tv, NULL);
+
+	sprintf(timestamp_str, %ld.%.03d,
+			tv.tv_sec, (int)(tv.tv_usec / 1000));
+
+	appendStringInfoString(buf, timestamp_str);
+}
+break;
 			case 't':
 {
 	pg_time_t	stamp_time = (pg_time_t) time(NULL);
@@ -2442,6 +2455,18 @@ log_line_prefix(StringInfo buf, ErrorData *edata)
 		appendStringInfoString(buf, strfbuf);
 }
 break;
+			case 'T':
+{
+	struct timeval tv;
+	char timestamp_str[FORMATTED_TS_LEN];
+
+	gettimeofday(tv, NULL);
+
+	sprintf(timestamp_str, %ld, tv.tv_sec);
+
+	appendStringInfoString(buf, timestamp_str);
+}
+break;
 			case 's':
 if (formatted_start_time[0] == '\0')
 	setup_formatted_start_time();
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 110983f..e448dd0 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -425,7 +425,9 @@
 	#   %h = remote host
 	#   %p = process ID
 	#   %t = timestamp without milliseconds
+	#   %T = timestamp without milliseconds (as a number)
 	#   %m = timestamp with milliseconds
+	#   %M = timestamp with milliseconds (as a number)
 	#   %i = command tag
 	#   %e = SQL state
 	#   %c = session ID

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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/mailpref/pgsql-hackers

[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 inline.

On the third hand, INT64_MIN etc. would typically be defined in stdint.h
if it exists.

So wouldn't it make more sense to move these definitions into c.h and
standardize their usage?

-- 
Andrew (irc:RhodiumToad)


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[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;
 QUERY PLAN 

 Update on public.pt1  (cost=0.00..321.05 rows=3541 width=46)
   Foreign Update on public.ft1
 Remote SQL: UPDATE public.ref1 SET c1 = $2 WHERE ctid = $1
   Foreign Update on public.ft2
 Remote SQL: UPDATE public.ref2 SET c1 = $2 WHERE ctid = $1
   -  Seq Scan on public.pt1  (cost=0.00..0.00 rows=1 width=46)
 Output: (pt1.c1 + 1), pt1.c2, pt1.c3, pt1.ctid
   -  Foreign Scan on public.ft1  (cost=100.00..148.03 rows=1170 width=46)
 Output: (ft1.c1 + 1), ft1.c2, ft1.c3, ft1.ctid
 Remote SQL: SELECT c1, c2, c3, ctid FROM public.ref1 FOR UPDATE
   -  Foreign Scan on public.ft2  (cost=100.00..148.03 rows=1170 width=46)
 Output: (ft2.c1 + 1), ft2.c2, ft2.c3, ft2.ctid
 Remote SQL: SELECT c1, c2, c3, ctid FROM public.ref2 FOR UPDATE
   -  Seq Scan on public.child3  (cost=0.00..25.00 rows=1200 width=46)
 Output: (child3.c1 + 1), child3.c2, child3.c3, child3.ctid
(15 rows)

which seems fairly messy to me because you have to guess at which of
the child plan subtrees goes with which Remote SQL item.

In a green field we might choose to solve this by refactoring the output
so that it's logically

Multi-Table Update
[
  Update Target: pt1
  Plan: (seq scan on pt1 here)
]
[
  Update Target: ft1
  Remote SQL: UPDATE ref1 ...
  Plan: (foreign scan on ft1 here)
]
[
  Update Target: ft2
  Remote SQL: UPDATE ref2 ...
  Plan: (foreign scan on ft2 here)
]
[
  Update Target: child3
  Plan: (seq scan on child3 here)
]

but I think that ship has sailed.  Changing the logical structure of
EXPLAIN output like this would break clients that know what's where in
JSON/YAML/XML formats, which is exactly what we said we wouldn't do with
those output formats.

What I'm imagining instead is that when there's more than one
target relation, we produce output like

Multi-Table Update
Relation Name: pt1  -- this is the *nominal* target
Target Relations:
  [
Relation Name: pt1  -- first actual target
Schema: public
Alias: pt1
  ]
  [
Relation Name: ft1
Schema: public
Alias: ft1
Remote SQL: UPDATE ref1 ...
  ]
  [
Relation Name: ft2
Schema: public
Alias: ft2
Remote SQL: UPDATE ref2 ...
  ]
  [
Relation Name: child3
Schema: public
Alias: child3
  ]
Plans:
  Plan: (seq scan on pt1 here)
  Plan: (foreign scan on ft1 here)
  Plan: (foreign scan on ft2 here)
  Plan: (seq scan on child3 here)

That is, there'd be a new subnode of ModifyTable (which existing clients
would ignore), and that would fully identify *each* target table not only
foreign ones.  The text-mode output might look like

 Update on public.pt1  (cost=0.00..321.05 rows=3541 width=46)
   Update on public.pt1
   Foreign Update on public.ft1
 Remote SQL: UPDATE public.ref1 SET c1 = $2 WHERE ctid = $1
   Foreign Update on public.ft2
 Remote SQL: UPDATE public.ref2 SET c1 = $2 WHERE ctid = $1
   Update on public.child3
   -  Seq Scan on public.pt1  (cost=0.00..0.00 rows=1 width=46)
 Output: (pt1.c1 + 1), pt1.c2, pt1.c3, pt1.ctid
   ... etc ...

where there would always now be as many target tables listed as
there are child plan trees.

Thoughts, better ideas?

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PATCH: numeric timestamp in log_line_prefix

2015-03-21 Thread Tomas Vondra

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 trivial, because of timezones etc.

 I propose adding two new log_line_prefix escape sequences - %T and %M,
 doing the same thing as %t and %m, but formatting the value as a number.

 Patch attached, I'll add it to CF 2015-06.
 
 Uh, I think you mean number here:
 
  entryTime stamp without milliseconds (as a numer)/entry
   -

Oh, right, that's a stupid typo.

 
 Also, what number do you mean?  Unix time since 1970?

Yes, the usual unix timestamp.

-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2015-03-21 Thread Jerry Sievers

Joshua D. Drake j...@commandprompt.com 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
 go down that path again. Consider, power users don't need a separate
 config. They can create their own or use alter system.


10 years ago I was already feeling like postgresql.conf was unwieldy
and have on many occasions stripped them down only to live lines without
any comments whatsoever.

In fact I'd argue that due to  how verbose the file is presently as
shipped, it makes it that much more likely  that someone looking at is
isn't going to notice  something as alarming as fsync=off :-)

 JD


 -- 
 Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
 PostgreSQL Support, Training, Professional Services and Development
 High Availability, Oracle Conversion, @cmdpromptinc

 Now I get it: your service is designed for a customer
 base that grew up with Facebook, watches Japanese seizure
 robot anime, and has the attention span of a gnat.
 I'm not that user., Tyler Riddle

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.1 is released and I am home for a while, I am ready to
 address this.
...
 float4/8 are padding to the internal precision, while int4/numeric are
 padding based on the requested precision.  This is inconsistent.
 
 The first attached patch fixes this, and also zeros the junk digits
 which exceed the precision of the underlying type:

Patch applied.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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:
  
   entryTime stamp without milliseconds (as a numer)/entry
-
 
 Oh, right, that's a stupid typo.
 
  
  Also, what number do you mean?  Unix time since 1970?
 
 Yes, the usual unix timestamp.

I think you need to find out where we reference that and use the same
wording.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PATCH: numeric timestamp in log_line_prefix

2015-03-21 Thread Alvaro Herrera

Bruce Momjian wrote:
 On Sun, Mar 22, 2015 at 02:41:44AM +0100, Tomas Vondra wrote:
   Uh, I think you mean number here:
   
entryTime stamp without milliseconds (as a numer)/entry
 -
  
  Oh, right, that's a stupid typo.
  
   
   Also, what number do you mean?  Unix time since 1970?
  
  Yes, the usual unix timestamp.
 
 I think you need to find out where we reference that and use the same
 wording.

We use Unix epoch in various places.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 something else in the future, so it should be improved
where feasible.
   
   Meh. We don't put in effort into code that doesn't matter just because
   it might get used elsewhere some day. By that argument we'd need to
   performance optimize a lot of code. And actually, using that code
   somewhere else is more of a counter indication than a pro
   argument. MAP_NOSYNC isn't a general purpose flag.
  
  The key is that this is platform-specific behavior, so if we should use
  a flag to use it right, we should.  You are right that optimizing
  rarely used code with generic calls isn't a good use of time.
 
 I have adjusted Sean's mmap() options patch to match our C layout and
 plan to apply this to head, as it is from August.

Patch applied.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 pretty relevant to the formula we started this
 thread with:
 
 regression=# select (1234::numeric/1235) * 1235; 
  ?column?  
 ---
  1234.0100
 (1 row)
 
 regression=# select (1234::float8/1235) * 1235; 
  ?column? 
 --
  1234
 (1 row)
 
 What it boils down to is that numeric is great for storing given decimal
 inputs exactly, and it can do exact addition/subtraction/multiplication
 on those too, but as soon as you get into territory where the result is
 fundamentally inexact it is *not* promised to be better than float8.
 In fact, it's designed to be more or less the same as float8; see the
 comments in select_div_scale.

Based on the analysis above, I have written the attached patch to the
NUMERIC docs to mention this.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
new file mode 100644
index edf636b..4a65971
*** a/doc/src/sgml/datatype.sgml
--- b/doc/src/sgml/datatype.sgml
***
*** 498,508 
  
  para
   The type typenumeric/type can store numbers with a
!  very large number of digits and perform calculations exactly. It is
!  especially recommended for storing monetary amounts and other
!  quantities where exactness is required. However, arithmetic on
!  typenumeric/type values is very slow compared to the integer
!  types, or to the floating-point types described in the next section.
  /para
  
  para
--- 498,510 
  
  para
   The type typenumeric/type can store numbers with a
!  very large number of digits. It is especially recommended for
!  storing monetary amounts and other quantities where exactness is
!  required.  Calculations with typenumeric/type values yield exact
!  results where possible, e.g.  addition, subtraction, multiplication.
!  However, calculations on typenumeric/type values is very slow
!  compared to the integer types, or to the floating-point types
!  described in the next section.
  /para
  
  para

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] NUMERIC private methods?

 Bruce == Bruce Momjian br...@momjian.us writes:

  !  However, calculations on typenumeric/type 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://www.postgresql.org/mailpref/pgsql-hackers

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,l,l,l,frame=none]
 |
 ^l|Schema ^l|Name ^l|Type ^l|Owner
 |public|5 2.2+^.^|table|ioltas
 |
 
 
 (1 row)
 
 
 I think that we should really put additional spaces on the left side
 of the column separators |. For example, this line:
 |public|5 2.2+^.^|table|ioltas
 should become that:
 |public |5 2.2+^.^ |table |ioltas
 And there is no problem.

I have updated the attached patch to do as you suggested.  Please also
test the \x output.  Thanks.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
new file mode 100644
index a637001..82a91ec
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*** lo_import 152801
*** 2092,2099 
literalaligned/literal, literalwrapped/literal,
literalhtml/literal,
literallatex/literal (uses literaltabular/literal),
!   literallatex-longtable/literal, or
!   literaltroff-ms/literal.
Unique abbreviations are allowed.  (That would mean one letter
is enough.)
/para
--- 2092,2099 
literalaligned/literal, literalwrapped/literal,
literalhtml/literal,
literallatex/literal (uses literaltabular/literal),
!   literallatex-longtable/literal,
!   literaltroff-ms/literal, or literalasciidoc/literal.
Unique abbreviations are allowed.  (That would mean one letter
is enough.)
/para
*** lo_import 152801
*** 2120,2126 
  
para
The literalhtml/, literallatex/,
!   literallatex-longtable/literal, and literaltroff-ms/
formats put out tables that are intended to
be included in documents using the respective mark-up
language. They are not complete documents! This might not be
--- 2120,2127 
  
para
The literalhtml/, literallatex/,
!   literallatex-longtable/literal, literaltroff-ms/,
!   and literalasciidoc/
formats put out tables that are intended to
be included in documents using the respective mark-up
language. They are not complete documents! This might not be
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
new file mode 100644
index 7c9f28d..a96f0ef
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*** _align2string(enum printFormat in)
*** 2257,2262 
--- 2257,2265 
  		case PRINT_TROFF_MS:
  			return troff-ms;
  			break;
+ 		case PRINT_ASCIIDOC:
+ 			return asciidoc;
+ 			break;
  	}
  	return unknown;
  }
*** do_pset(const char *param, const char *v
*** 2330,2338 
  			popt-topt.format = PRINT_LATEX_LONGTABLE;
  		else if (pg_strncasecmp(troff-ms, value, vallen) == 0)
  			popt-topt.format = PRINT_TROFF_MS;
  		else
  		{
! 			psql_error(\\pset: allowed formats are unaligned, aligned, wrapped, html, latex, troff-ms\n);
  			return false;
  		}
  
--- 2333,2343 
  			popt-topt.format = PRINT_LATEX_LONGTABLE;
  		else if (pg_strncasecmp(troff-ms, value, vallen) == 0)
  			popt-topt.format = PRINT_TROFF_MS;
+ 		else if (pg_strncasecmp(asciidoc, value, vallen) == 0)
+ 			popt-topt.format = PRINT_ASCIIDOC;
  		else
  		{
! 			psql_error(\\pset: allowed formats are unaligned, aligned, wrapped, html, latex, troff-ms, asciidoc\n);
  			return false;
  		}
  
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
new file mode 100644
index ac0dc27..93a517e
*** a/src/bin/psql/help.c
--- b/src/bin/psql/help.c
*** helpVariables(unsigned short int pager)
*** 351,357 
  	fprintf(output, _(  expanded (or x)toggle expanded output\n));
  	fprintf(output, _(  fieldsep   field separator for unaligned output (default '|')\n));
  	fprintf(output, _(  fieldsep_zero  set field separator in unaligned mode to zero\n));
! 	fprintf(output, _(  format set output format [unaligned, aligned, wrapped, html, latex, ..]\n));
  	fprintf(output, _(  footer enable or disable display of the table footer [on, off]\n));
  	fprintf(output, _(  linestyle  set the border line drawing style [ascii, old-ascii, unicode]\n));
  	fprintf(output, _(  null   set the string to be printed in place of a null value\n));
--- 351,357 
  	fprintf(output, _(  expanded (or x)toggle expanded output\n));
  	fprintf(output, _(  fieldsep   field separator for unaligned output (default '|')\n));
  	fprintf(output, _(  fieldsep_zero  set field separator in unaligned mode to zero\n));
!

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 adding two new log_line_prefix escape sequences - %T and %M,
 doing the same thing as %t and %m, but formatting the value as a number.
 
 Patch attached, I'll add it to CF 2015-06.

Uh, I think you mean number here:

 entryTime stamp without milliseconds (as a numer)/entry
  -

Also, what number do you mean?  Unix time since 1970?

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 adds a TOAST table to the shared table
 pg_shseclabel for use with long labels.  The new query output shows the
 shared and non-shared seclabel tables now both have TOAST tables:
 
   test= SELECT oid::regclass, reltoastrelid FROM pg_class WHERE relname 
 IN ('pg_seclabel', 'pg_shseclabel');
 oid  | reltoastrelid
   ---+---
pg_seclabel   |  3598
pg_shseclabel |  4060
   (2 rows)
 
 Previously pg_shseclabel was zero.

Patch applied.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 vadi...@yahoo.com
  Date:   Fri Sep 26 14:55:21 1997 +
  
  Start with performance suite.
 
 Any objection if I remove the src/test/performance directory and all its
 files?

Done.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[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 a position to recompile. Also, as I showed at some
length earlier, even recompiling with an apparently irrelevant change
can have enough of an effect on performance to make investigation more
complex.

So if these debugging elogs are to be kept at all, I propose that rather
than being compile-time options they should be controlled by a
debug_sortsupport GUC. Opinions?

-- 
Andrew (irc:RhodiumToad)


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Add transforms feature

2015-03-21 Thread Pavel Stehule

2015-03-22 3:55 GMT+01:00 Peter Eisentraut pete...@gmx.net:

 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 pete...@gmx.net
  mailto:pete...@gmx.net:
 
  On 3/12/15 8:12 AM, Pavel Stehule wrote:
   1. fix missing semicolon pg_proc.h
  
   Oid protrftypes[1]; /* types for which to apply
   transforms */
 
  Darn, I thought I had fixed that.

 Fixed.

   2. strange load lib by in sql scripts:
  
   DO '' LANGUAGE plperl;
   SELECT NULL::hstore;
  
   use load plperl; load hstore; instead
 
  OK

 The reason I had actually not used LOAD is that LOAD requires a file
 name, and the file name of those extensions is an implementation detail.
  So it is less of a violation to just execute something from those
 modules rather than reach in and deal with the file directly.

 It's not terribly pretty either way, I admit.  A proper fix would be to
 switch to lazy symbol resolution, but that would be a much bigger change.

   3. missing documentation for new contrib modules,
 
  OK

 They actually are documented as part of the hstore and ltree modules
 already.

   4. pg_dump - wrong comment
  
   +---/*
   +--- * protrftypes was added at v9.4
   +--- */
 
  OK

 Fixed.

   4. Why guc-use-transforms? Is there some possible negative side
 effect
   of transformations, so we have to disable it? If somebody don't
 would to
   use some transformations, then he should not to install some
 specific
   transformation.
 
  Well, there was extensive discussion last time around where people
  disagreed with that assertion.
 
 
  I don't like it, but I can accept it - it should not to impact a
  functionality.

 Removed.

   5. I don't understand to motivation for introduction of
 protrftypes in
   pg_proc and TRANSFORM clause for CREATE FUNCTION - it is not clean
 from
   documentation, and examples in contribs works without it. Is it
 this
   functionality really necessary? Missing tests, missing examples.
 
  Again, this came out from the last round of discussion that people
  wanted to select which transforms to use and that the function needs
 to
  remember that choice, so it doesn't depend on whether a transform
  happens to be installed or not.  Also, it's in the SQL standard that
 way
  (by analogy).
 
 
  I am sorry, I didn't discuss this topic and I don't agree so it is good
  idea. I looked to standard, and I found CREATE TRANSFORM part there. But
  nothing else.
 
  Personally I am thinking, so it is terrible wrong idea, unclean,
  redundant. If we define TRANSFORM, then we should to use it. Not prepare
  bypass in same moment.
 
  Can be it faster, safer with it? I don't think.

 Well, I don't think there is any point in reopening this discussion.
 This is a safety net of sorts that people wanted.  You can argue that it
 would be more fun without it, but nobody else would agree.  There is
 really no harm in keeping it.  All the function lookup is mostly cached
 anyway.  The only time this is really important is for pg_dump to be
 able to accurately restore function behavior.


1. It add attribute to pg_proc, so impact is not minimal

2. Minimally it is not tested - there are no any test for this functionality

3. I'll reread a discuss about this design - Now I am thinking so this
duality (in design) is wrong - worse in relatively critical part of
Postgres.

I can mark this patch as ready for commiter with objection - It is task
for commiter, who have to decide.

Regards

Pavel

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

2015-03-21 Thread David Rowley

On 22 March 2015 at 14:46, Bruce Momjian br...@momjian.us 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() does zero-padding for float4/8 values.
  Now that 9.4.1 is released and I am home for a while, I am ready to
  address this.
 ...
  float4/8 are padding to the internal precision, while int4/numeric are
  padding based on the requested precision.  This is inconsistent.
 
  The first attached patch fixes this, and also zeros the junk digits
  which exceed the precision of the underlying type:

 Patch applied.


This seems to have broken jacana.  Looks like MSVC by default has a 3 digit
exponent.

Going by this:
https://msdn.microsoft.com/en-us/library/0fatw238(v=vs.80).aspx it seems
that it can quite easily be set back to 2.

I've attached a patch which seems to fix the issue.

Regards

David Rowley
diff --git a/src/backend/main/main.c b/src/backend/main/main.c
index 2f07a58..51f0884 100644
--- a/src/backend/main/main.c
+++ b/src/backend/main/main.c
@@ -262,8 +262,16 @@ startup_hacks(const char *progname)
/* In case of general protection fault, don't show GUI popup 
box */
SetErrorMode(SEM_FAILCRITICALERRORS | SEM_NOGPFAULTERRORBOX);
}
+
 #endif   /* WIN32 */
 
+#ifdef _MSC_VER
+
+   /* By default MSVC has a 3 digit exponent. */
+   _set_output_format(_TWO_DIGIT_EXPONENT);
+
+#endif   /* _MSC_VER */
+
/*
 * Initialize dummy_spinlock, in case we are on a platform where we have
 * to use the fallback implementation of pg_memory_barrier().

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

On Sun, Mar 22, 2015 at 12:32 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Andres Freund and...@2ndquadrant.com 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 the exponential precision: e+000 instead of e+00.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] INT64_MIN and _MAX

 Andrew == Andrew Gierth and...@tao11.riddles.org.uk writes:

 Petr == Petr Jelinek p...@2ndquadrant.com 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.

Hm, it looks like the same could be said for INT32_MIN and _MAX; some
places use INT_MIN etc., others say we shouldn't assume int = int32
and use (-0x7fff - 1) or whatever instead.

-- 
Andrew (irc:RhodiumToad)


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

On Sun, Mar 22, 2015 at 2:17 PM, Michael Paquier
michael.paqu...@gmail.com wrote:
 On Sun, Mar 22, 2015 at 12:32 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Andres Freund and...@2ndquadrant.com 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 the exponential precision: e+000 instead of e+00.

Useless noise from my side, the error is in the test windows.sql like here:
http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=jacanadt=2015-03-21%2003%3A01%3A21
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2015-03-21 Thread David Rowley

On 22 March 2015 at 18:17, Michael Paquier michael.paqu...@gmail.com
wrote:

 On Sun, Mar 22, 2015 at 12:32 AM, Tom Lane t...@sss.pgh.pa.us wrote:
  Andres Freund and...@2ndquadrant.com 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 the exponential precision: e+000 instead of e+00.


It looks like there's some other problems there too, but for the
exponential issue, I posted a patch here:

http://www.postgresql.org/message-id/caaphdvordz8kcdfyzgrcpdeflmqk0f6u_78nj-jajxyj7uf...@mail.gmail.com

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