Re: [HACKERS] PostgreSQL for VAX on NetBSD/OpenBSD

[Tom Lane]

I wrote:
 On further reflection, there seems little reason not to change it: it's
 pretty silly to imagine that selectivity estimates produced via this
 technique would have anything like 14 decimal places of precision anyhow.

I've done something about both this and the bipartite_match issue in HEAD.
I'd be curious to see all the remaining regression differences on VAX.

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] minor typo in trigger.c

[Kevin Grittner]

Merlin Moncure mmonc...@gmail.com wrote:

 -* Forget the query stack and constrant-related state information.  As
 +* Forget the query stack and constraint-related state information.  As


Pushed.  Thanks!


--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] PostgreSQL for VAX on NetBSD/OpenBSD

[Tom Lane]

I wrote:
 Oh, interesting.  The largest possible value of base is 256, and the
 code limits the amount of string it'll scan to 20 bytes, which means
 denom could reach at most 256^21 = 3.7e50.  Perfectly fine with
 IEEE-math doubles, but not so much with other arithmetics.

 We could hold the worst-case value to within the VAX range if we
 considered only about 14 bytes instead of 20.  Probably that wouldn't
 lose much in terms of estimation accuracy, but given the lack of
 complaints to date, I'm not sure we should change it ...

On further reflection, there seems little reason not to change it: it's
pretty silly to imagine that selectivity estimates produced via this
technique would have anything like 14 decimal places of precision anyhow.
We've already stripped off any common prefix of the strings we're
comparing, so the strings are certain to differ at the first byte.
Reducing the maximum number of bytes considered will save cycles and I
really doubt that it would cost anything in estimation accuracy.

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] PostgreSQL for VAX on NetBSD/OpenBSD

[Tom Lane]

I wrote:
 I think we should replace the whole mess with, say, uint32 for float and
 UINT_MAX for infinity.  That will be more portable, probably faster, and
 it will work correctly up to substantially *larger* peak distances than
 the existing code.

After studying the logic a bit more, I realized that the finite
distances computed by the algorithm can actually never exceed u_size,
which we're already constraining to be less than SHRT_MAX so that the
adjacency arrays can be short.  So I made it use short storage for
distances too, with SHRT_MAX as the infinity value.  If we ever find a
need to work with graphs exceeding 32K nodes, it will be trivial to
s/short/int/g in this code.

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] Performance improvement for joins where outer side is unique

[Tomas Vondra]

Hi,

I did some initial performance evaluation of this patch today, and I see 
a clear improvement on larger joins. The scenario I've chosen for the 
experiments is a simple fact-dimension join, i.e. a small table 
referenced by a large table. So effectively something like this:


CREATE TABLE dim  (id INT PRIMARY KEY, ...);
CREATE TABLE fact (dim_d INT REFERENCES dim(id), ...);

except that I haven't used the foreign key constraint. In all the 
experiments I've used a fact table 10x the size of the dimension, but I 
believe what really matters most is the size of the dimension (and how 
the hash table fits into the L2/L3 cache).


The query tested is very simple:

select count(1) from (
select * from f join d on (f.fact_id = d.dim_id)
) foo;

The outer aggregation is intentional - the join produces many rows and 
formatting them as string would completely eliminate any gains from the 
patch (even with \o /dev/null or such).


The following numbers come current master, running on E5-2630 v3 
(2.40GHz), 64GB of RAM and this configuration:


 checkpoint_timeout = 15min
 effective_cache_size = 48GB
 maintenance_work_mem = 1GB
 max_wal_size = 4GB
 min_wal_size = 1GB
 random_page_cost = 1.5
 shared_buffers = 4GB
 work_mem = 1GB

all the other values are set to default.

I did 10 runs for each combination of sizes - the numbers seem quite 
consistent and repeatable. I also looked at the median values.



dim 100k rows, fact 1M rows
---

 master patched
--- ---
   1286.184 265.489
   2284.827 264.961
   3281.040 264.768
   4280.926 267.720
   5280.984 261.348
   6280.878 261.463
   7280.875 261.338
   8281.042 261.265
   9281.003 261.236
  10280.939 261.185
--- ---
 med280.994 261.406 (-7%)


dim 1M rows, fact 10M rows
--

 master patched
   
   1   4316.2353690.373
   2   4399.5393738.097
   3   4360.5513655.602
   4   4359.7633626.142
   5   4361.8213621.941
   6   4359.2053654.835
   7   4371.4383631.212
   8   4361.8573626.237
   9   4357.3173676.651
  10   4359.5613641.830
   
 med   4360.1573648.333 (-17%)


dim 10M rows, fact 100M rows


 master patched
   
   1  46246.467   39561.597
   2  45982.937   40083.352
   3  45818.118   39674.661
   4  45716.281   39616.585
   5  45651.117   40463.966
   6  45979.036   41395.390
   7  46045.400   41358.047
   8  45978.698   41253.946
   9  45801.343   41156.440
  10  45720.722   41374.688
  -   -
 med  45898.408   40810.203 (-10%)


So the gains seem quite solid - it's not something that would make the 
query an order of magnitude faster, but it's well above the noise.


Of course, in practice the queries will be more complicated, making the 
improvement less significant, but I don't think that's a reason not to 
apply it.


Two minor comments on the patch:

1) the 'subquery' variable in specialjoin_is_unique_join is unused

2) in the explain output, there should probably be a space before the
   '(inner unique)' text, so

 Hash Join (inner unique) ...

   instead of

 Hash Join(inner unique)

but that's just nitpicking at this point. Otherwise the patch seems 
quite solid to me.


regard

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


unijoins-test.sql
Description: application/sql


unijoins-queries.sql
Description: application/sql

-- 
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] checkpointer continuous flushing

[Fabien COELHO]

Hello Amit,


I have tried your scripts and found some problem while using avg.py
script.
grep 'progress:' test_medium4_FW_off.out | cut -d' ' -f4 | ./avg.py
--limit=10 --length=300
: No such file or directory



I didn't get chance to poke into avg.py script (the command without
avg.py works fine). Python version on the m/c, I planned to test is
Python 2.7.5.


Strange... What does /usr/bin/env python say? Can the script be started 
on its own at all? I think that the script should work both with python2 
and python3, at least it does on my laptop...



Today while reading the first patch (checkpoint-continuous-flush-10-a),
I have given some thought to below part of patch which I would like
to share with you.

+ * Select a tablespace depending on the current overall progress.
+ *
+ * The progress ratio of each unfinished tablespace is compared to
+ * the overall progress ratio to find one with is not in advance
+ * (i.e. overall ratio  tablespace ratio,
+ *  i.e. tablespace written/to_write  overall written/to_write



Here, I think above calculation can go for toss if backend or bgwriter
starts writing buffers when checkpoint is in progress.  The tablespace
written parameter won't be able to consider the one's written by backends
or bgwriter.


Sure... This is *already* the case with the current checkpointer, the 
schedule is performed with respect to the initial number of buffers it 
think it will have to write, and if someone else writes these buffers then 
the schedule is skewed a little bit, or more... I have not changed this 
logic, but I extended it to handle several tablespaces.


If this (the checkpointer progress evaluation used for its schedule is 
sometimes wrong because of other writes) is proven to be a major 
performance issue, then the processes which writes the checkpointed 
buffers behind its back should tell the checkpointer about it, probably 
with some shared data structure, so that the checkpointer can adapt its 
schedule.


This is an independent issue, that may be worth to address some day. My 
opinion is that when the bgwriter or backends quick in to write buffers, 
they are basically generating random I/Os on HDD and killing tps and 
latency, so it is a very bad time anyway, thus I'm not sure that this is 
the next problem to address to improve pg performance and responsiveness.


Now it may not big thing to worry but I find Heikki's version worth 
considering, he has not changed the overall idea of this patch, but the 
calculations are somewhat simpler and hence less chance of going wrong.


I do not think that Heikki version worked wrt to balancing writes over 
tablespaces, and I'm not sure it worked at all. However I reused some of 
his ideas to simplify and improve the code.


--
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] pg_dump quietly ignore missing tables - is it bug?

[Pavel Stehule]

Hi

2015-08-22 0:09 GMT+02:00 Jim Nasby jim.na...@bluetreble.com:

 The following review has been posted through the commitfest application:
 make installcheck-world:  tested, passed
 Implements feature:   tested, failed
 Spec compliant:   not tested
 Documentation:not tested

 The feature doesn't seem to work:
 pg_dump -t t -t 'ii*' --strict-names
 pg_dump: unrecognized option `--strict-names'
 Try pg_dump --help for more information.
 decibel@decina:[16:58]~/git/postgres/i
 (pg_dump-strict-names-7.patch=)$bin/p


sorry - there was wrong strict-mode

fixed



 The documentation could use some improvements.

 +para
 + Require that table and/or schema match at least one entity each.
 + Without any entity in the database to be dumped, an error message
 + is printed and dump is aborted.
 +/para

 Would be clearer as

 Require that each schema (-n / --schema) and table (-t / --table)
 qualifier match at least one schema/table in the database to be dumped.
 Note that if none of the schema/table qualifiers find matches pg_dump will
 generate an error even without --strict-names.

 +para
 + This option has no effect on the exclude table and schema
 patterns
 + (and also option--exclude-table-data/): not matching any
 entities
 + isn't considered an error.

 Rewrite:
 This option has no effect on -N/--exclude-schema, -T/--exclude_table or
 --exclude-table-date. An exclude pattern failing to match any objects is
 not considered an error.


fixed

Regards

Pavel



 The new status of this patch is: Waiting on Author


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

diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 7467e86..eaa006d 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -545,6 +545,23 @@ PostgreSQL documentation
  /varlistentry
 
  varlistentry
+  termoption--strict-names//term
+  listitem
+   para
+Require that each schema (-n / --schema) and table (-t / --table)
+qualifier match at least one schema/table in the database to be dumped.
+Note that if none of the schema/table qualifiers find matches pg_dump
+will generate an error even without --strict-names.
+   /para
+   para
+This option has no effect on -N/--exclude-schema, -T/--exclude_table
+or --exclude-table-date. An exclude pattern failing to match
+any bjects is not considered an error.
+   /para
+  /listitem
+ /varlistentry
+
+ varlistentry
   termoption-T replaceable class=parametertable/replaceable/option/term
   termoption--exclude-table=replaceable class=parametertable/replaceable/option/term
   listitem
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 97e3420..a5a9394 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -432,6 +432,16 @@
  /varlistentry
 
  varlistentry
+  termoption--strict-names//term
+  listitem
+   para
+Require that each schema (-n / --schema) and table (-t / --table)
+qualifier match at least one schema/table in the backup file.
+   /para
+  /listitem
+ /varlistentry
+
+ varlistentry
   termoption-T replaceable class=parametertrigger/replaceable/option/term
   termoption--trigger=replaceable class=parametertrigger/replaceable/option/term
   listitem
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
index d7506e1..52b2b98 100644
--- a/src/bin/pg_dump/dumputils.c
+++ b/src/bin/pg_dump/dumputils.c
@@ -1220,6 +1220,7 @@ simple_string_list_append(SimpleStringList *list, const char *val)
 		pg_malloc(offsetof(SimpleStringListCell, val) +strlen(val) + 1);
 
 	cell-next = NULL;
+	cell-touched = false;
 	strcpy(cell-val, val);
 
 	if (list-tail)
@@ -1237,7 +1238,23 @@ simple_string_list_member(SimpleStringList *list, const char *val)
 	for (cell = list-head; cell; cell = cell-next)
 	{
 		if (strcmp(cell-val, val) == 0)
+		{
+			cell-touched = true;
 			return true;
+		}
 	}
 	return false;
 }
+
+const char *
+simple_string_list_not_touched(SimpleStringList *list)
+{
+	SimpleStringListCell *cell;
+
+	for (cell = list-head; cell; cell = cell-next)
+	{
+		if (!cell-touched)
+			return cell-val;
+	}
+	return NULL;
+}
diff --git a/src/bin/pg_dump/dumputils.h b/src/bin/pg_dump/dumputils.h
index b176746..9f31bbc 100644
--- a/src/bin/pg_dump/dumputils.h
+++ b/src/bin/pg_dump/dumputils.h
@@ -38,6 +38,8 @@ typedef struct SimpleOidList
 typedef struct SimpleStringListCell
 {
 	struct SimpleStringListCell *next;
+	bool		touched;/* true, when this string was searched
+  and touched */
 	char		val[FLEXIBLE_ARRAY_MEMBER];		/* null-terminated string here */
 } SimpleStringListCell;
 
@@ -103,5 

Re: [HACKERS] proposal: function parse_ident

[Pavel Stehule]

Hi

2015-08-21 7:15 GMT+02:00 Pavel Stehule pavel.steh...@gmail.com:



 2015-08-20 21:16 GMT+02:00 Jim Nasby jim.na...@bluetreble.com:

 On 8/19/15 7:22 PM, Tom Lane wrote:

 Jim Nasby jim.na...@bluetreble.com writes:

 Don't say parse names for things other than tables.  Only a minority
 of the types of objects used in the database have names that meet this
 specification.


 Really? My impression is that almost everything that's not a shared
 object allows for a schema...


 Tables meet this naming spec.  Columns, functions, operators, operator
 classes/families, collations, constraints, and conversions do not (you
 need more data to name them).  Schemas, databases, languages, extensions,
 and some other things also do not, because you need *less* data to name
 them.  Types also don't really meet this naming spec, because you need to
 contend with special cases like int[] or timestamp with time zone.
 So this proposal doesn't seem very carefully thought-through to me,
 or at least the use case is much narrower than it could be.

 Also, if object does not exist isn't supposed to be an error case,
 what of name is not correctly formatted?  It seems a bit arbitrary
 to me to throw an error in one case but not the other.


 I think the important point here is this is *parse*_ident(). It's not
 meant to guarantee an object actually exists, what kind of object it is, or
 whether it's syntactically correct. It's meant only to separate an
 identifier into it's 3 (or in some cases 2) components. If this was as
 simple as string_to_array(foo, '.') then it'd be a bit pointless, but it's
 obviously a lot more complex than that.


 parsing composite identifier is pretty complex - and almost all work is
 done - it just need SQL envelope only


here is the patch

It is really trivial - all heavy work was done done before.

Regards

Pavel


 Pavel



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



commit 1546ec8d173c4fa91e08012af7fbfe0d64585ef0
Author: Pavel Stehule pavel.steh...@gmail.com
Date:   Sun Aug 23 17:43:20 2015 +0200

initial

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 617d0b3..5d678bc 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1707,6 +1707,23 @@
   row
entry
 indexterm
+ primaryparse_ident/primary
+/indexterm
+literalfunctionparse_ident(parameterqualified_identifier/parameter typetext/type,
+OUT parameterdbname/parameter typetext/type, OUT parameterschemaname/parameter typetext/type,
+OUT parameterobjectname/parameter typetext/type)/function/literal
+   /entry
+   entrytyperecord/type/entry
+   entrySplit parameterqualified identifier/parameter to parts parameterdbname/parameter,
+parameterschemaname/parameter and parameterobjectname/parameter.
+   /entry
+   entryliteralparse_ident('SomeSchema.someTable')/literal/entry
+   entryliteral(,SomeSchema,sometable)/literal/entry
+  /row
+
+  row
+   entry
+indexterm
  primarypg_client_encoding/primary
 /indexterm
 literalfunctionpg_client_encoding()/function/literal
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index c0495d9..19f87db 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -21,7 +21,9 @@
 #include unistd.h
 
 #include access/sysattr.h
+#include access/htup_details.h
 #include catalog/catalog.h
+#include catalog/namespace.h
 #include catalog/pg_tablespace.h
 #include catalog/pg_type.h
 #include commands/dbcommands.h
@@ -598,3 +600,59 @@ pg_column_is_updatable(PG_FUNCTION_ARGS)
 
 	PG_RETURN_BOOL((events  REQ_EVENTS) == REQ_EVENTS);
 }
+
+/*
+ * parse_ident - decompose text identifier to basic three parts
+ */
+Datum
+parse_ident(PG_FUNCTION_ARGS)
+{
+	text		*qualname = PG_GETARG_TEXT_PP(0);
+	List		*name_list;
+	RangeVar	*rv;
+	Datum		values[3];
+	bool		nulls[3];
+	TupleDesc	tupdesc;
+
+	name_list = stringToQualifiedNameList(text_to_cstring(qualname));
+	rv = makeRangeVarFromNameList(name_list);
+
+	/* Prepare result tuple desc */
+	tupdesc = CreateTemplateTupleDesc(3, false);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 1, dbname,
+	TEXTOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 2, schemaname,
+	TEXTOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 3, objectname,
+	TEXTOID, -1, 0);
+
+	BlessTupleDesc(tupdesc);
+
+	/* Fill returned values */
+	if (rv-catalogname != NULL)
+	{
+		values[0] = CStringGetTextDatum(rv-catalogname);
+		nulls[0] = false;
+	}
+	else
+		nulls[0] = true;
+
+	if (rv-schemaname != NULL)
+	{
+		values[1] = CStringGetTextDatum(rv-schemaname);
+		nulls[1] = false;
+	}
+	else
+		nulls[1] = true;
+
+	if (rv-relname != NULL)
+	{
+		values[2] = CStringGetTextDatum(rv-relname);
+		nulls[2] = false;
+	}
+	else
+		nulls[2] = true;
+
+	PG_RETURN_DATUM(HeapTupleGetDatum(
+		

Re: [HACKERS] PATCH: numeric timestamp in log_line_prefix

[Fabien COELHO]

1) fix the docs (explicitly say that it's a Unix epoch)


I would add the word numeric in front of timestamp both in the doc and
in the postgresql.conf.sample, as it justifies the chosen %n.


I think we're already using 'unix epoch' in the docs without explicitly 
stating that it's a numeric value, so I don't think we should use it here as 
it'd be inconsistent.


The point was to justify the choice of 'n' somehow.


2) handle 'padding' properly


Hmmm, I'm not entirely sure how exactly the padding is supposed to work (IIRC 
I've never used it), and I thought it behaved correctly. But maybe not - I 
think the safest thing is copy what 't' does, so I've done that in attached 
v3 of the patch.


Ok. Version 3 applies and compiles, and padding now works as expected.

Here is a v4 that I also tested, and where I just removed a spurious '.' 
in the millisecond format.


--
Fabien.diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index e3dc23b..3ced399 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -4630,6 +4630,11 @@ local0.*/var/log/postgresql
  entryno/entry
 /row
 row
+ entryliteral%n/literal/entry
+ entryTime stamp with milliseconds (as a Unix epoch)/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 088c714..9114c55 100644
--- a/src/backend/utils/error/elog.c
+++ b/src/backend/utils/error/elog.c
@@ -2438,6 +2438,20 @@ log_line_prefix(StringInfo buf, ErrorData *edata)
 		appendStringInfoString(buf, strfbuf);
 }
 break;
+			case 'n':
+{
+	struct	timeval tv;
+	char	strfbuf[128];
+
+	gettimeofday(tv, NULL);
+	sprintf(strfbuf, %ld.%03d, tv.tv_sec, (int)(tv.tv_usec / 1000));
+
+	if (padding != 0)
+		appendStringInfo(buf, %*s, padding, strfbuf);
+	else
+		appendStringInfoString(buf, strfbuf);
+}
+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 695a88f..c33e585 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -425,6 +425,7 @@
 	#   %p = process ID
 	#   %t = timestamp without milliseconds
 	#   %m = timestamp with milliseconds
+	#   %n = timestamp with milliseconds (as a Unix epoch)
 	#   %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] PostgreSQL for VAX on NetBSD/OpenBSD

[Tom Lane]

Greg Stark st...@mit.edu writes:
 On 22 Aug 2015 18:02, Tom Lane t...@sss.pgh.pa.us wrote:
 The hang is actually in the groupingset tests in
 bipartite_match.c:hk_breadth_search().

 Is it that function itself that's hanging, or is the problem that its
 caller expects it to ultimately return true, and it never does?

 I think it never exits that function but I'll try it again.

I looked at that some more, and so far as I can see, its dependence on
Infinity, or really its use of float arithmetic at all, is a dumb-ass
idea.  The distances are integers, and not very large ones either.
Which is fortunate, because if they did get large, you'd be having
problems with lost precision (ie, x+1 == x) somewhere around 2^24, long
before you got anywhere near exceeding the range of float or even int.
I think we should replace the whole mess with, say, uint32 for float and
UINT_MAX for infinity.  That will be more portable, probably faster, and
it will work correctly up to substantially *larger* peak distances than
the existing code.

 I'm surprised that any of the hacks in src/port/isinf.c compile on Vax
 at all --- did you invent a new one?
 
 Also, I'd have thought that both get_floatX_infinity and get_floatX_nan
 would be liable to produce SIGFPE on non-IEEE machines.  Did you mess
 with those?

 I didn't do anything. There's no isinf.o in that directory so I don't
 think anything got compiled. There are other files in src/port but not
 that.

Some googling produced NetBSD man pages saying that isinf() and isnan()
are not supported on VAX.  Given that your build is evidently finding
system-provided versions of them, it's a good bet that they are hard-wired
to produce 0.  That would mean hk_breadth_search() necessarily returns
true, which would put its sole caller into an infinite loop.  So quite
aside from VAX, this coding is utterly dependent on the assumption that
get_float4_infinity() produces something that isinf() will return true
for.  I do not believe we have a hard dependency on that anywhere else.

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

[Tomas Vondra]

On 08/23/2015 09:28 AM, Fabien COELHO wrote:



1) fix the docs (explicitly say that it's a Unix epoch)


I would add the word numeric in front of timestamp both in the doc and
in the postgresql.conf.sample, as it justifies the chosen %n.


I think we're already using 'unix epoch' in the docs without
explicitly stating that it's a numeric value, so I don't think we
should use it here as it'd be inconsistent.


The point was to justify the choice of 'n' somehow.


2) handle 'padding' properly


Hmmm, I'm not entirely sure how exactly the padding is supposed to
work (IIRC I've never used it), and I thought it behaved correctly.
But maybe not - I think the safest thing is copy what 't' does, so
I've done that in attached v3 of the patch.


Ok. Version 3 applies and compiles, and padding now works as expected.

Here is a v4 that I also tested, and where I just removed a spurious '.'
in the millisecond format.


Thanks for spotting that.

regards

--
Tomas Vondra  http://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

[HACKERS] pgbench progress with timestamp

[Fabien COELHO]

It is not easy to compare events on a pgbench runs (oops, the tps is down) 
with for instance events in postgres log, so as to figure out what may 
have cause a given glitch.


This patches adds an option to replace the time since pgbench run 
started with a timestamp in the progress report so that it is easier to 
compare timelines.


Use milliseconds for consistency with the '%n' log_prefix patch currently 
submitted by Tomas Vondra in the CF.


  sh ./pgbench -P 1 -N -T 100 -c 2
  starting vacuum...end.
  progress: 1.0 s, 546.0 tps, lat 3.619 ms stddev 4.426
  progress: 2.0 s, 575.0 tps, lat 3.480 ms stddev 1.705

  sh ./pgbench -P 1 --progress-timestamp -N -T 100 -c 2
  starting vacuum...end.
  progress: 1440328800.064 s, 549.0 tps, lat 3.602 ms stddev 1.698
  progress: 1440328801.064 s, 570.0 tps, lat 3.501 ms stddev 1.704
  ...

--
Fabien.diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 2517a3a..dc7e55f 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -429,6 +429,18 @@ pgbench optional replaceableoptions/ /optional replaceabledbname/
  /varlistentry
 
  varlistentry
+  termoption--progress-timestamp/option/term
+  listitem
+   para
+When showing progress (option option-P/), use a millisecond
+timestamp (Unix epoch) intead of the number of second since the
+beginning of the run.
+This helps compare logs generated by various tools.
+   /para
+  /listitem
+ /varlistentry
+
+ varlistentry
   termoption-r/option/term
   termoption--report-latencies/option/term
   listitem
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 30e8d2a..6cab4a3 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -165,6 +165,7 @@ bool		use_quiet;			/* quiet logging onto stderr */
 int			agg_interval;		/* log aggregates instead of individual
  * transactions */
 int			progress = 0;		/* thread progress report every this seconds */
+bool		progress_timestamp = false;
 int			progress_nclients = 0;		/* number of clients for progress
 		 * report */
 int			progress_nthreads = 0;		/* number of threads for progress
@@ -389,6 +390,7 @@ usage(void)
 		 -v, --vacuum-all vacuum all four standard tables before tests\n
 		 --aggregate-interval=NUM aggregate data over NUM seconds\n
 		 --sampling-rate=NUM  fraction of transactions to log (e.g. 0.01 for 1%%)\n
+		 --progress-timestamp use a ms timestamp for progress\n
 		   \nCommon options:\n
 		 -d, --debug  print debugging output\n
 	-h, --host=HOSTNAME  database server host or socket directory\n
@@ -2774,6 +2776,7 @@ main(int argc, char **argv)
 		{aggregate-interval, required_argument, NULL, 5},
 		{rate, required_argument, NULL, 'R'},
 		{latency-limit, required_argument, NULL, 'L'},
+		{progress-timestamp, no_argument, NULL, 6},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -3110,6 +3113,10 @@ main(int argc, char **argv)
 }
 #endif
 break;
+			case 6:
+progress_timestamp = true;
+benchmarking_option_set = true;
+break;
 			default:
 fprintf(stderr, _(Try \%s --help\ for more information.\n), progname);
 exit(1);
@@ -3748,6 +3755,7 @@ threadRun(void *arg)
 			sqlat,
 			lag,
 			stdev;
+char		tbuf[64];
 
 /*
  * Add up the statistics of all threads.
@@ -3780,10 +3788,16 @@ threadRun(void *arg)
 stdev = 0.001 * sqrt(sqlat - 100.0 * latency * latency);
 lag = 0.001 * (lags - last_lags) / (count - last_count);
 
+if (progress_timestamp)
+	sprintf(tbuf, %.03f s,
+			INSTR_TIME_GET_MILLISEC(now_time) / 1000.0);
+else
+	sprintf(tbuf, %.1f s, total_run);
+
 fprintf(stderr,
-		progress: %.1f s, %.1f tps, 
-		lat %.3f ms stddev %.3f,
-		total_run, tps, latency, stdev);
+		progress: %s, %.1f tps, lat %.3f ms stddev %.3f,
+		tbuf, tps, latency, stdev);
+
 if (throttle_delay)
 {
 	fprintf(stderr, , lag %.3f ms, lag);

-- 
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] New functions

[Michael Paquier]

On Sun, Aug 23, 2015 at 10:21 PM, Michael Paquier wrote:
 By the way, perhaps it would be worth doing similar things for the
 other calls of BIO_free and BIO_new, no? I have attached a second
 patch.

And... This second patch had a stupid mistake making for example
ssl_client_dn() fail, so here they are again.
-- 
Michael
From eabb75a8cdaa81303de8c74b8d097bf3e0138d38 Mon Sep 17 00:00:00 2001
From: Michael Paquier mich...@otacoo.com
Date: Sun, 23 Aug 2015 21:24:22 +0900
Subject: [PATCH 1/2] Add function for SSL extension information in sslinfo

This is done with the addition of a new function called ssl_extension_info.
---
 contrib/sslinfo/Makefile   |   3 +-
 contrib/sslinfo/sslinfo--1.0--1.1.sql  |  13 ++
 .../sslinfo/{sslinfo--1.0.sql = sslinfo--1.1.sql} |  12 +-
 contrib/sslinfo/sslinfo.c  | 169 -
 contrib/sslinfo/sslinfo.control|   2 +-
 doc/src/sgml/sslinfo.sgml  |  19 +++
 6 files changed, 212 insertions(+), 6 deletions(-)
 create mode 100644 contrib/sslinfo/sslinfo--1.0--1.1.sql
 rename contrib/sslinfo/{sslinfo--1.0.sql = sslinfo--1.1.sql} (81%)

diff --git a/contrib/sslinfo/Makefile b/contrib/sslinfo/Makefile
index 86cbf05..f6c1472 100644
--- a/contrib/sslinfo/Makefile
+++ b/contrib/sslinfo/Makefile
@@ -4,7 +4,8 @@ MODULE_big = sslinfo
 OBJS = sslinfo.o $(WIN32RES)
 
 EXTENSION = sslinfo
-DATA = sslinfo--1.0.sql sslinfo--unpackaged--1.0.sql
+DATA = sslinfo--1.0--1.1.sql sslinfo--1.1.sql \
+	sslinfo--unpackaged--1.0.sql
 PGFILEDESC = sslinfo - information about client SSL certificate
 
 ifdef USE_PGXS
diff --git a/contrib/sslinfo/sslinfo--1.0--1.1.sql b/contrib/sslinfo/sslinfo--1.0--1.1.sql
new file mode 100644
index 000..c98a3ae
--- /dev/null
+++ b/contrib/sslinfo/sslinfo--1.0--1.1.sql
@@ -0,0 +1,13 @@
+/* contrib/sslinfo/sslinfo--1.0--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use ALTER EXTENSION sslinfo UPDATE TO '1.1' to load this file. \quit
+
+CREATE OR REPLACE FUNCTION ssl_extension_info(
+OUT name text,
+OUT value text,
+OUT iscritical boolean
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'ssl_extension_info'
+LANGUAGE C STRICT;
diff --git a/contrib/sslinfo/sslinfo--1.0.sql b/contrib/sslinfo/sslinfo--1.1.sql
similarity index 81%
rename from contrib/sslinfo/sslinfo--1.0.sql
rename to contrib/sslinfo/sslinfo--1.1.sql
index 79ce656..d63ddd5 100644
--- a/contrib/sslinfo/sslinfo--1.0.sql
+++ b/contrib/sslinfo/sslinfo--1.1.sql
@@ -1,4 +1,4 @@
-/* contrib/sslinfo/sslinfo--1.0.sql */
+/* contrib/sslinfo/sslinfo--1.1.sql */
 
 -- complain if script is sourced in psql, rather than via CREATE EXTENSION
 \echo Use CREATE EXTENSION sslinfo to load this file. \quit
@@ -38,3 +38,13 @@ LANGUAGE C STRICT;
 CREATE FUNCTION ssl_issuer_dn() RETURNS text
 AS 'MODULE_PATHNAME', 'ssl_issuer_dn'
 LANGUAGE C STRICT;
+
+/* new in 1.1 */
+CREATE OR REPLACE FUNCTION ssl_extension_info(
+OUT name text,
+OUT value text,
+OUT iscritical boolean
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'ssl_extension_info'
+LANGUAGE C STRICT;
diff --git a/contrib/sslinfo/sslinfo.c b/contrib/sslinfo/sslinfo.c
index da201bd..959c628 100644
--- a/contrib/sslinfo/sslinfo.c
+++ b/contrib/sslinfo/sslinfo.c
@@ -9,13 +9,18 @@
 
 #include postgres.h
 #include fmgr.h
-#include utils/numeric.h
-#include libpq/libpq-be.h
+#include funcapi.h
 #include miscadmin.h
-#include utils/builtins.h
+
+#include access/htup_details.h
+#include catalog/pg_type.h
+#include libpq/libpq-be.h
 #include mb/pg_wchar.h
+#include utils/builtins.h
+#include utils/numeric.h
 
 #include openssl/x509.h
+#include openssl/x509v3.h
 #include openssl/asn1.h
 
 PG_MODULE_MAGIC;
@@ -24,6 +29,13 @@ static Datum X509_NAME_field_to_text(X509_NAME *name, text *fieldName);
 static Datum X509_NAME_to_text(X509_NAME *name);
 static Datum ASN1_STRING_to_text(ASN1_STRING *str);
 
+/*
+ * Function context for data persisting over repeated calls.
+ */
+typedef struct
+{
+	TupleDesc   tupdesc;
+} SSLExtensionInfoContext;
 
 /*
  * Indicates whether current session uses SSL
@@ -354,3 +366,154 @@ ssl_issuer_dn(PG_FUNCTION_ARGS)
 		PG_RETURN_NULL();
 	return X509_NAME_to_text(X509_get_issuer_name(MyProcPort-peer));
 }
+
+
+/*
+ * Returns information about available SSL extensions.
+ *
+ * Returns setof record made of the following values:
+ * - name of the extension.
+ * - value of the extension.
+ * - critical status of the extension.
+ */
+PG_FUNCTION_INFO_V1(ssl_extension_info);
+Datum
+ssl_extension_info(PG_FUNCTION_ARGS)
+{
+	X509	   *cert = MyProcPort-peer;
+	FuncCallContext 		   *funcctx;
+	STACK_OF(X509_EXTENSION)   *ext_stack = NULL;
+	int 		call_cntr;
+	int 		max_calls;
+	MemoryContextoldcontext;
+	SSLExtensionInfoContext	   *fctx;  /* User function context. */
+
+	if (SRF_IS_FIRSTCALL())
+	{
+
+		TupleDesc   tupdesc;
+
+		/* create a function context for 

Re: [HACKERS] New functions

[Michael Paquier]

On Sat, Aug 22, 2015 at 11:24 PM, Heikki Linnakangas hlinn...@iki.fi wrote:
 On 07/08/2015 01:15 PM, Дмитрий Воронин wrote:

 07.07.2015, 18:34, Michael Paquier michael.paqu...@gmail.com:

   Speaking of which, I have rewritten the patch as attached. This looks
   way cleaner than the previous version submitted. Dmitry, does that
   look fine for you?
   I am switching this patch as Waiting on Author.


 Michael, hello. I'm looking your variant of patch. You create
 function ssl_extensions_info(), that gives information of SSL
 extensions in more informative view. So, it's cool.


 Should check the return value of every OpenSSL call for errors. At least
 BIO_new() could return NULL, but check all the docs of the others too.

Right, agreed for BIO_new(). BIO_write and BIO_get_mem_data can return
negative error code, but that's not necessarily the indication of an
error by looking at the docs, so I'd rather let them as-is.
X509V3_EXT_print is not documented but it can return = 0 state code
if the operation fails so I guess that it makes sense to elog an
ERROR. sk_X509_EXTENSION_value and X509_EXTENSION_get_object return
NULL in case of failure (looking at the code tree of openssl), and
OBJ_obj2nid will return NID_undef in this case, so I think the code
as-is is fine. Another interesting thing is that BIO_free can fail and
we don't track that.

By the way, perhaps it would be worth doing similar things for the
other calls of BIO_free and BIO_new, no? I have attached a second
patch.

 Are all the functions used in the patch available in all the versions of
 OpenSSL we support?

We support openssl down to 0.9.7, right? And those functions are
present there (I recall vaguely checking that when looking at this
patch, and I just rechecked in case I missed something).

 Other than those little things, looks good to me.

Thanks!
-- 
Michael
From eabb75a8cdaa81303de8c74b8d097bf3e0138d38 Mon Sep 17 00:00:00 2001
From: Michael Paquier mich...@otacoo.com
Date: Sun, 23 Aug 2015 21:24:22 +0900
Subject: [PATCH 1/2] Add function for SSL extension information in sslinfo

This is done with the addition of a new function called ssl_extension_info.
---
 contrib/sslinfo/Makefile   |   3 +-
 contrib/sslinfo/sslinfo--1.0--1.1.sql  |  13 ++
 .../sslinfo/{sslinfo--1.0.sql = sslinfo--1.1.sql} |  12 +-
 contrib/sslinfo/sslinfo.c  | 169 -
 contrib/sslinfo/sslinfo.control|   2 +-
 doc/src/sgml/sslinfo.sgml  |  19 +++
 6 files changed, 212 insertions(+), 6 deletions(-)
 create mode 100644 contrib/sslinfo/sslinfo--1.0--1.1.sql
 rename contrib/sslinfo/{sslinfo--1.0.sql = sslinfo--1.1.sql} (81%)

diff --git a/contrib/sslinfo/Makefile b/contrib/sslinfo/Makefile
index 86cbf05..f6c1472 100644
--- a/contrib/sslinfo/Makefile
+++ b/contrib/sslinfo/Makefile
@@ -4,7 +4,8 @@ MODULE_big = sslinfo
 OBJS = sslinfo.o $(WIN32RES)
 
 EXTENSION = sslinfo
-DATA = sslinfo--1.0.sql sslinfo--unpackaged--1.0.sql
+DATA = sslinfo--1.0--1.1.sql sslinfo--1.1.sql \
+	sslinfo--unpackaged--1.0.sql
 PGFILEDESC = sslinfo - information about client SSL certificate
 
 ifdef USE_PGXS
diff --git a/contrib/sslinfo/sslinfo--1.0--1.1.sql b/contrib/sslinfo/sslinfo--1.0--1.1.sql
new file mode 100644
index 000..c98a3ae
--- /dev/null
+++ b/contrib/sslinfo/sslinfo--1.0--1.1.sql
@@ -0,0 +1,13 @@
+/* contrib/sslinfo/sslinfo--1.0--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use ALTER EXTENSION sslinfo UPDATE TO '1.1' to load this file. \quit
+
+CREATE OR REPLACE FUNCTION ssl_extension_info(
+OUT name text,
+OUT value text,
+OUT iscritical boolean
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'ssl_extension_info'
+LANGUAGE C STRICT;
diff --git a/contrib/sslinfo/sslinfo--1.0.sql b/contrib/sslinfo/sslinfo--1.1.sql
similarity index 81%
rename from contrib/sslinfo/sslinfo--1.0.sql
rename to contrib/sslinfo/sslinfo--1.1.sql
index 79ce656..d63ddd5 100644
--- a/contrib/sslinfo/sslinfo--1.0.sql
+++ b/contrib/sslinfo/sslinfo--1.1.sql
@@ -1,4 +1,4 @@
-/* contrib/sslinfo/sslinfo--1.0.sql */
+/* contrib/sslinfo/sslinfo--1.1.sql */
 
 -- complain if script is sourced in psql, rather than via CREATE EXTENSION
 \echo Use CREATE EXTENSION sslinfo to load this file. \quit
@@ -38,3 +38,13 @@ LANGUAGE C STRICT;
 CREATE FUNCTION ssl_issuer_dn() RETURNS text
 AS 'MODULE_PATHNAME', 'ssl_issuer_dn'
 LANGUAGE C STRICT;
+
+/* new in 1.1 */
+CREATE OR REPLACE FUNCTION ssl_extension_info(
+OUT name text,
+OUT value text,
+OUT iscritical boolean
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'ssl_extension_info'
+LANGUAGE C STRICT;
diff --git a/contrib/sslinfo/sslinfo.c b/contrib/sslinfo/sslinfo.c
index da201bd..959c628 100644
--- a/contrib/sslinfo/sslinfo.c
+++ b/contrib/sslinfo/sslinfo.c
@@ -9,13 +9,18 @@
 
 #include postgres.h
 #include fmgr.h
-#include utils/numeric.h
-#include libpq/libpq-be.h
+#include 

Re: [HACKERS] PostgreSQL for VAX on NetBSD/OpenBSD

[Greg Stark]

On Sun, Aug 23, 2015 at 8:18 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 I've done something about both this and the bipartite_match issue in HEAD.
 I'd be curious to see all the remaining regression differences on VAX.

I'll run a git pull overnight :)


-- 
greg


-- 
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] Performance improvement for joins where outer side is unique

[David Rowley]

On 24 August 2015 at 12:19, Tom Lane t...@sss.pgh.pa.us wrote:

 David Rowley david.row...@2ndquadrant.com writes:
  On 24 August 2015 at 07:31, Tomas Vondra tomas.von...@2ndquadrant.com
  wrote:
  2) in the explain output, there should probably be a space before the
  '(inner unique)' text, so
 
  Hash Join (inner unique) ...
 
  instead of
 
  Hash Join(inner unique)
 
  but that's just nitpicking at this point. Otherwise the patch seems
 quite
  solid to me.

  The attached fixes these two issues.

 Please, no.  Randomly sticking additional bits of information into Join
 Type is a good way to render EXPLAIN output permanently unintelligible,
 not only to humans but to whatever programs are still trying to read the
 text output format (I think explain.depesz.com still does).  It is also
 not a great idea to put more semantic distance between the text and
 non-text output representations.

 I am not exactly convinced that this behavior needs to be visible in
 EXPLAIN output at all, but if it does, it should be a separate field.


I have to admit I don't much like it either, originally I had this as an
extra property that was only seen in EXPLAIN VERBOSE.

-  Nested Loop
  Output: a.ctid, wcte.*
+ Unique Join: No

There was a debate somewhere about this and it ended up the way it is now,
I didn't feel the need to argue for the EXPLAIN VERBOSR field as I thought
that a committer would likely change it anyway. However, if I remove all
changes to explain.c, then it makes it very difficult to write regression
tests which ensure the new code is doing what it's meant to.

What do you think of the extra EXPLAIN VERBOSE field?

Regards

David Rowley

--
 David Rowley   http://www.2ndQuadrant.com/
http://www.2ndquadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services

Re: [HACKERS] AIX 7.1 compile and initdb error TRAP: FailedAssertion

[Tom Lane]

xpNitin nitin.pa...@ca.com writes:
 Tried this a few times.  I am trying to compile and run PostgreSQL 9.4.0 on
 AIX 7.1.

Is there a particular reason you're trying to build 9.4.0, and not the
current release 9.4.4?

 But DB fails to start.  I get this error:

 creating system views ... TRAP: FailedAssertion(!(const
 Node*)(field1))-type) == T_String)), File: parse_expr.c, Line: 602)

That's odd.  Could you get a stack trace out of the core file for that?
(You might need to run initdb with --noclean to have the core file
survive long enough to be looked at.)

FWIW, we do have reason to believe that PG will work on AIX 7.1, since
this buildfarm member works:
http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=hornetdt=2015-08-22%2004%3A48%3A32
But I'm not sure which post-9.4.0 fixes may be relevant to that.

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] Performance improvement for joins where outer side is unique

[David Rowley]

On 24 August 2015 at 07:31, Tomas Vondra tomas.von...@2ndquadrant.com
wrote:


 dim 100k rows, fact 1M rows
 ---

  master patched
 --- ---

..


  med280.994 261.406 (-7%)


 dim 1M rows, fact 10M rows
 --

  master patched


..


  med   4360.1573648.333 (-17%)


 dim 10M rows, fact 100M rows
 

  master patched


..

  med  45898.408   40810.203 (-10%)


 So the gains seem quite solid - it's not something that would make the
 query an order of magnitude faster, but it's well above the noise.

 Of course, in practice the queries will be more complicated, making the
 improvement less significant, but I don't think that's a reason not to
 apply it.


Many thanks for doing that performance testing.


 Two minor comments on the patch:

 1) the 'subquery' variable in specialjoin_is_unique_join is unused

 2) in the explain output, there should probably be a space before the
'(inner unique)' text, so

  Hash Join (inner unique) ...

instead of

  Hash Join(inner unique)

 but that's just nitpicking at this point. Otherwise the patch seems quite
 solid to me.


The attached fixes these two issues.

Regards

David Rowley

--
 David Rowley   http://www.2ndQuadrant.com/
http://www.2ndquadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


unique_joins_2015-08-24.patch
Description: Binary data

-- 
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] exposing pg_controldata and pg_config as functions

[Michael Paquier]

On Sun, Aug 23, 2015 at 7:50 AM, Joe Conway m...@joeconway.com wrote:
 1) The syntax is a bit different than what Andrew proposed:

 8
 select setting ~ '--with-libxml' as has_xml
 from pg_config
 where name = 'CONFIGURE';
  has_xml
 - -
  t
 (1 row)
 8

 In particular note that the name values are all upper case to be
 consistent with pg_config, and at least currently there is no version
 of the function which accepts a name as an argument (didn't seem
 worthwhile to me).

Compatibility by default with the binary pg_config makes sense, users
could just wrap an SQL with lower() or upper() if needed.

 2) No docs or related regression test yet. I will do that if there is
 enough interest in this getting committed. So far no one except Andrew
 and I have chimed in.

I think that's a good thing to have, now I have concerns about making
this data readable for non-superusers. Cloud deployments of Postgres
are logically going to block the access of this view.

 4) The static function cleanup_path() was borrowed from

 src/bin/pg_config/pg_config.c

cleanup_path is perhaps a candidate for src/port/path.c?


 It is a small and stable function (no change since 2010 AFAICS), so
 maybe not worth the effort, but I was wondering if it should be moved
 to src/common somewhere and shared.

 I will add this to the next commitfest. Comments/feedback encouraged.

+ Datum pg_config(PG_FUNCTION_ARGS);
+
+ PG_FUNCTION_INFO_V1(pg_config);

The declaration of the function is not needed, PG_FUNCTION_INFO_V1
takes care of it.
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

[HACKERS] AIX 7.1 compile and initdb error TRAP: FailedAssertion

[xpNitin]

Hello,
Tried this a few times.  I am trying to compile and run PostgreSQL 9.4.0 on
AIX 7.1.  I was able to compile with following settings:
CC=xlc_r -q64 -qnoansialias
CFLAGS=-g -O0
AR=ar -X64
OBJECT_MODE=64
./configure --enable-cassert --enable-debug
-with-includes=/opt/freeware/include --with-libraries=/opt/freeware/lib
--disable-thread-safety --prefix=/opt/postgres  gmake
make install

But DB fails to start.  I get this error:
./initdb -D /opt/postgresql/data
The files belonging to this database system will be owned by user
postgres.
This user must also own the server process.
The database cluster will be initialized with locale C.
The default database encoding has accordingly been set to SQL_ASCII.
The default text search configuration will be set to english.

Data page checksums are disabled.

fixing permissions on existing directory /opt/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /opt/postgresql/data/base/1 ... ok
initializing pg_authid ... oke
initializing dependencies ... ok
creating system views ... TRAP: FailedAssertion(!(const
Node*)(field1))-type) == T_String)), File: parse_expr.c, Line: 602)
child process was terminated by signal 6
initdb: removing contents of data directory /opt/postgresql/data”

Thank you,
Nitin Pande



--
View this message in context: 
http://postgresql.nabble.com/AIX-7-1-compile-and-initdb-error-TRAP-FailedAssertion-tp5863098.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] Performance improvement for joins where outer side is unique

[Tom Lane]

David Rowley david.row...@2ndquadrant.com writes:
 On 24 August 2015 at 07:31, Tomas Vondra tomas.von...@2ndquadrant.com
 wrote:
 2) in the explain output, there should probably be a space before the
 '(inner unique)' text, so
 
 Hash Join (inner unique) ...
 
 instead of
 
 Hash Join(inner unique)
 
 but that's just nitpicking at this point. Otherwise the patch seems quite
 solid to me.

 The attached fixes these two issues.

Please, no.  Randomly sticking additional bits of information into Join
Type is a good way to render EXPLAIN output permanently unintelligible,
not only to humans but to whatever programs are still trying to read the
text output format (I think explain.depesz.com still does).  It is also
not a great idea to put more semantic distance between the text and
non-text output representations.

I am not exactly convinced that this behavior needs to be visible in
EXPLAIN output at all, but if it does, it should be a separate field.

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] Declarative partitioning

[Josh Berkus]

On 08/21/2015 08:34 PM, Jim Nasby wrote:
 On 8/18/15 12:31 PM, Josh Berkus wrote:
 Also this would be useful for range
 partitions:

 CREATE PARTITION ON parent_table USING ( start_value );

 ... where start_value is the start range of the new partition.  Again,
 easier for users to get correct.
 
 Instead of that, I think it would be more foolproof to do
 
 CREATE PARTITION ON parent_table FOR ( value1, ... );
 
 instead of trusting the user to get the exact start value correct.
 
 Though... I guess there could be value in allowing an exact start value
 but throwing an error if it doesn't sit exactly on a boundary. Might
 make it less likely to accidentally create the wrong partition.

Well, I'm figuring that most people would use CREATE NEXT PARTITION
instead.

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

[HACKERS] psql - better support pipe line

[Pavel Stehule]

Hi

I found so a set psql and xargs is pretty strong. But I miss a psql
option for simple returning current database and continuing in pipeline.

What I am doing:

psql postgres -At -c select datname from pg_database |
xargs -P 3 -I % psql % -At -c select current_databe() from
pg_stat_all_tables
where relname = 'pg_attribute' and n_dead_tup  10 |
xargs -P 3 -I % sh -c psql % -q -c 'analyze pg_attribute'; echo % |
xargs -P 3 -I % psql % -At -c select curren_database() from ...

it works perfectly - but the line

xargs -P 3 -I % sh -c psql % -q -c 'analyze pg_attribute'; echo %

is little bit ugly - with some psql option it can be cleaned to

xargs -P3 -I % psql % -q --echo-db -c analyze pg_attribute | ...

--echo-db requires -q option

What are you thinking about this idea?

Regards

Pavel

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

[Tom Lane]

David Rowley david.row...@2ndquadrant.com writes:
 On 24 August 2015 at 12:19, Tom Lane t...@sss.pgh.pa.us wrote:
 I am not exactly convinced that this behavior needs to be visible in
 EXPLAIN output at all, but if it does, it should be a separate field.

 I have to admit I don't much like it either, originally I had this as an
 extra property that was only seen in EXPLAIN VERBOSE.

Seems like a reasonable design from here.  (Note that for non-text output,
I'd say the field should come out unconditionally.  We only care about
abbreviating in text mode.)

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] checkpointer continuous flushing

[Amit Kapila]

On Sun, Aug 23, 2015 at 12:33 PM, Fabien COELHO coe...@cri.ensmp.fr wrote:


 Hello Amit,

 I have tried your scripts and found some problem while using avg.py
 script.
 grep 'progress:' test_medium4_FW_off.out | cut -d' ' -f4 | ./avg.py
 --limit=10 --length=300
 : No such file or directory


 I didn't get chance to poke into avg.py script (the command without
 avg.py works fine). Python version on the m/c, I planned to test is
 Python 2.7.5.


 Strange... What does /usr/bin/env python say?


Python 2.7.5 (default, Apr  9 2015, 11:07:29)
[GCC 4.8.3 20140911 (Red Hat 4.8.3-9)] on linux2
Type help, copyright, credits or license for more information.



 Can the script be started on its own at all?


I have tried like below which results in same error, also I tried few
other variations but could not succeed.
./avg.py
: No such file or directory



 Here, I think above calculation can go for toss if backend or bgwriter
 starts writing buffers when checkpoint is in progress.  The tablespace
 written parameter won't be able to consider the one's written by backends
 or bgwriter.


 Sure... This is *already* the case with the current checkpointer, the
 schedule is performed with respect to the initial number of buffers it
 think it will have to write, and if someone else writes these buffers then
 the schedule is skewed a little bit, or more... I have not changed this
 logic, but I extended it to handle several tablespaces.


I don't know how good or bad it is to build  further on somewhat skewed
logic, but the point is that unless it is required why to use it.


 I do not think that Heikki version worked wrt to balancing writes over
 tablespaces,


I also think that it doesn't balances over tablespaces, but the question
is why do we need to balance over tablespaces, can we reliably
predict in someway which indicates that performing balancing over
tablespace can help the workload.  I think here we are doing more
engineering than required for this patch.


 and I'm not sure it worked at all.


Okay, his version might have some bugs, but then those could be
fixed as well.


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

Re: [HACKERS] Archiving done right

[Michael Paquier]

On Fri, Aug 21, 2015 at 11:25 PM, David Steele da...@pgmasters.net wrote:
 In the current scheme, if the standby has not been archiving and then
 gets promoted, won't it try to push the older WAL segments because the
 .done files do not exist in archive_status?  Or does the archiver not
 cross timelines?

The segments are marked with .done on a standby once their reception
is finished at recovery.
If, by current, you mean = 9.5, it is also mentioned in the docs that
when archive_mode = on and the standby is promoted, the
now-master-standby will not archive segments it did not generate
itself:
http://www.postgresql.org/docs/devel/static/warm-standby.html#CONTINUOUS-ARCHIVING-IN-STANDBY
-- 
Michael


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