[HACKERS] Minor comment issue in receivelog.c

[Bernd Helmle]

Please find a minor comment fix for receivelog.c, HandleCopyStream().

The comments talks about a START_STREAMING command, but i think
START_REPLICATION is what's meant here.


Bernd
diff --git a/src/bin/pg_basebackup/receivelog.c b/src/bin/pg_basebackup/receivelog.c
index 888458f4a9..befbbb092b 100644
--- a/src/bin/pg_basebackup/receivelog.c
+++ b/src/bin/pg_basebackup/receivelog.c
@@ -763,7 +763,7 @@ ReadEndOfStreamingResult(PGresult *res, XLogRecPtr *startpos, uint32 *timeline)
 
 /*
  * The main loop of ReceiveXlogStream. Handles the COPY stream after
- * initiating streaming with the START_STREAMING command.
+ * initiating streaming with the START_REPLICATION command.
  *
  * If the COPY ends (not necessarily successfully) due a message from the
  * server, returns a PGresult and sets *stoppos to the last byte written.

-- 
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] parallel worker (PID ) exited with exit code 1

[Bernd Helmle]

Am Freitag, den 06.10.2017, 17:49 +0530 schrieb tushar:
> I got some few queries after running sqlsmith against PG HEAD , where
> i 
> am getting LOG message like - "parallel worker (PID) exited with
> exit 
> code 1"
> 
> set force_parallel_mode =1;
>   select
>pg_catalog.pg_wal_replay_pause() as c0,
>ref_0.ev_type as c1
>  from
>pg_catalog.pg_rewrite as ref_0
>  where ref_0.ev_enabled > ref_0.ev_type
>  limit 53;

Looks like pg_wal_replay_pause() is marked as parallel safe:

SELECT proparallel FROM pg_proc WHERE proname = 'pg_wal_replay_pause';
-[ RECORD 1 ]--
proparallel | s

Bernd



-- 
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] reorder tablespaces in basebackup tar stream for backup_label

[Bernd Helmle]

Am Freitag, den 03.03.2017, 15:44 +0900 schrieb Michael Paquier:
> So, the main directory is located at the end on purpose. When using
> --wal-method=fetch the WAL segments are part of the main tarball, so
> if you send the main tarball first you would need to generate a
> second
> tarball with the WAL segments that have been generated between the
> moment the main tarball has finished until the end of the last
> tablespace taken if you want to have a consistent backup. Your patch
> would work with the stream mode though.

Ah right, i assumed there must be something, otherwise the comment
won't be there ;)

We could special case that part to distinguish fetch/stream mode, but i
fear that leads to more confusion than it wants to fix. The other
option of a separate tar file looks awkward from a usability point of
view.


-- 
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] Make subquery alias optional in FROM clause

[Bernd Helmle]

Am Mittwoch, den 22.02.2017, 22:17 -0500 schrieb Tom Lane:
> [ shrug... ]  Well, I won't resist this hard as long as it's done
> competently, which to me means "the subquery name doesn't conflict
> with
> anything else".  Not "it doesn't conflict unless you're unlucky
> enough
> to have used the same name elsewhere".  There are a couple ways we
> could
> achieve that result, but the submitted patch fails to.

Right, i'm going to give it a try then. Currently i see these options:

* Validate any generated alias against a list of explicit alias names.

This means we have to collect explicit alias names in, say a hashtable,
and validate a generated name against potential collisions and retry.
Or better, generate the name in a way that doesn't produce a collision
with this list.

* Don't force/generate an alias at all.

I've no idea for this yet and Tom already was concerned what this might
break. There are several places in the transform phase where the
refnames are required (e.g. isLockedRefname()).


Thanks

Bernd


-- 
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] Make subquery alias optional in FROM clause

[Bernd Helmle]

On Wed, 2017-02-22 at 08:13 -0700, David G. Johnston wrote:
> I'll contribute to the popular demand aspect but given that the error
> is
> good and the fix is very simple its not exactly a strong desire.

In one project i've recently seen, for some reasons, they need to
maintain an application twice, one for Oracle and the other for
Postgres for years. To be honest, subqueries aren't the only problem,
but having this solved in the backend itself would help to decrease the
amount of maintenance efforts in such projects.

I thought that this would be another thing to make the migration pains
more less, without being too invasive, given that there were already
some thoughts about relaxing alias usage.



-- 
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] Make subquery alias optional in FROM clause

[Bernd Helmle]

On Wed, 2017-02-22 at 10:08 -0500, Tom Lane wrote:
> 
> Indeed.  When I wrote the comment you're referring to, quite a few
> years
> ago now, I thought that popular demand might force us to allow
> omitted
> aliases.  But the demand never materialized.  At this point it seems
> clear to me that there isn't really good reason to exceed the spec
> here.
> It just encourages people to write unportable SQL code.
> 

Years ago i didn't hear anything about it either. But in the last few
months i've heard such a demand several times, so i thought we should
give it another try.

> > The patch generates an auto-alias for subqueries in the format
> > *SUBQUERY_* for subqueries and *VALUES_* for values
> > expressions.  is the range table index it gets during
> > transformRangeSubselect().
> 
> This is not a solution, because it does nothing to avoid conflicts
> with
> table names elsewhere in the FROM clause.  If we were going to relax
> this
> --- which, I repeat, I'm against --- we'd have to come up with
> something
> that would thumb through the whole query and make sure what it was
> generating didn't already appear somewhere else.  

I've thought about this already. One thing that came into my mind was
to maintain a lookup list of aliasnames during the transform phase and
throw an ereport as soon as the generated string has any duplicate. Not
sure about the details, but i was worried about the performance impact
in this area...

> Or else not generate
> a name at all, in which case there simply wouldn't be a way to refer
> to
> the subquery by name; I'm not sure what that might break though.
> 

Hmm, maybe that's an option. Though, i think parts of the code aren't
prepared to deal with empty (or even NULL) aliases. That's likely much
more invasive.




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

[HACKERS] Make subquery alias optional in FROM clause

[Bernd Helmle]

>From time to time, especially during migration projects from Oracle to
PostgreSQL, i'm faced with people questioning why the alias in the FROM
clause for subqueries in PostgreSQL is mandatory. The default answer
here is, the SQL standard requires it.

This also is exactly the comment in our parser about this topic:

/*
 * The SQL spec does not permit a subselect
 * () without an alias clause,
 * so we don't either.  This avoids the problem
 * of needing to invent a unique refname for it.
 * That could be surmounted if there's sufficient
 * popular demand, but for now let's just implement
 * the spec and see if anyone complains.
 * However, it does seem like a good idea to emit
 * an error message that's better than "syntax error".
 */

So i thought i'm the one standing up for voting to relax this and
making the alias optional.

The main problem, as mentioned in the parser's comment, is to invent a
machinery to create an unique alias for each of the subquery/values
expression in the from clause. I pondered a little about it and came to
the attached result.

The patch generates an auto-alias for subqueries in the format
*SUBQUERY_* for subqueries and *VALUES_* for values
expressions.  is the range table index it gets during
transformRangeSubselect().

Doc patch and simple regression tests included.
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 211e4c3..f2d21aa 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -51,7 +51,7 @@ SELECT [ ALL | DISTINCT [ ON ( expressiontable_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
 [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
-[ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
+[ LATERAL ] ( select ) [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
 with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
 [ LATERAL ] function_name ( [ argument [, ...] ] )
 [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
@@ -408,8 +408,7 @@ TABLE [ ONLY ] table_name [ * ]
 output were created as a temporary table for the duration of
 this single SELECT command.  Note that the
 sub-SELECT must be surrounded by
-parentheses, and an alias must be
-provided for it.  A
+parentheses.  An optional alias can be used to name the subquery.  A
  command
 can also be used here.

@@ -1891,6 +1890,31 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
   
 
   
+   Optional subquery alias names in FROM clauses
+
+   
+PostgreSQL allows one to omit
+alias names for subqueries used in FROM-clauses, which
+are required in the SQL standard. Thus, the following SQL is valid in PostgreSQL:
+
+SELECT * FROM (VALUES(1), (2), (3));
+ column1 
+-
+   1
+   2
+   3
+(3 rows)
+
+SELECT * FROM (SELECT 1, 2, 3);
+ ?column? | ?column? | ?column? 
+--+--+--
+1 |2 |3
+(1 row)
+
+   
+  
+
+  
ONLY and Inheritance
 

diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 6c6d21b..865b3ce 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11302,30 +11302,13 @@ table_ref:	relation_expr opt_alias_clause
 	/*
 	 * The SQL spec does not permit a subselect
 	 * () without an alias clause,
-	 * so we don't either.  This avoids the problem
-	 * of needing to invent a unique refname for it.
-	 * That could be surmounted if there's sufficient
-	 * popular demand, but for now let's just implement
-	 * the spec and see if anyone complains.
-	 * However, it does seem like a good idea to emit
-	 * an error message that's better than "syntax error".
+	 * but PostgreSQL isn't that strict here. We
+	 * provide an unique, auto-generated alias
+	 * name instead, which will be done through
+	 * the transform/analyze phase later. See
+	 * parse_clause.c, transformRangeSubselect() for
+	 * details.
 	 */
-	if ($2 == NULL)
-	{
-		if (IsA($1, SelectStmt) &&
-			((SelectStmt *) $1)->valuesLists)
-			ereport(ERROR,
-	(errcode(ERRCODE_SYNTAX_ERROR),
-	 errmsg("VALUES in FROM must have an alias"),
-	 errhint("For example, FROM (VALUES ...) [AS] foo."),
-	 parser_errposition(@1)));
-		else
-			ereport(ERROR,
-	(errcode(ERRCODE_SYNTAX_ERROR),
-	 errmsg("subquery in FROM must have an alias"),
-	 errhint("For example, FROM (SELECT ...) [AS] foo."),
-	 parser_errposition(@1)));
-	}
 	$$ = (Node *) n;
 }
 			| LATERAL_P select_with_parens opt_alias_clause
@@ -11335,22 +11318,6 @@ table_ref:	relation_expr opt_alias_clause
 	n->subquery = $2;
 	n->alias = $3;
 	/* same comment as above */
-	if ($3 == NULL)
-	{
-		if (IsA($2, SelectStmt) &&
-			((SelectStmt *) 

Re: [HACKERS] [patch] reorder tablespaces in basebackup tar stream for backup_label

[Bernd Helmle]

Am Dienstag, den 21.02.2017, 11:17 +0100 schrieb Michael Banck:
> However, third party tools using the BASE_BACKUP command might want
> to
> extract the backup_label, e.g. in order to figure out the START WAL
> LOCATION. If they make a big tarball for the whole cluster
> potentially
> including all external tablespaces, then the backup_label file is
> somewhere in the middle of it and it takes a long time for tar to
> extract it.
> 
> So I am proposing the attached patch, which sends the base tablespace
> first, and then all the other external tablespaces afterwards, thus
> having base_backup be the first file in the tar in all cases. Does
> anybody see a problem with that?
> 
> 
> Michael
> 
> [1] Chapter 52.3 of the documentation says "one or more CopyResponse
> results will be sent, one for the main data directory and one for
> each
> additional tablespace other than pg_default and pg_global.", which
> makes
> it sound like the main data directory is first, but in my testing,
> this
> is not the case.

The comment in the code says explicitely to add the base directory to
the end of the list, not sure if that is out of a certain reason.

I'd say this is an oversight in the implementation. I'm currently
working on a tool using the streaming protocol directly and i've
understood it exactly the way, that the default tablespace is the first
one in the stream.

So +1 for the patch.


-- 
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] LWLock optimization for multicore Power machines

[Bernd Helmle]

Am Dienstag, den 14.02.2017, 15:53 +0300 schrieb Alexander Korotkov:
> +1
> And you could try to use pg_wait_sampling
>  to sampling of wait
> events.

I've tried this with your example from your blog post[1] and got this:

(pgbench scale 1000)

pgbench -Mprepared -S -n -c 100 -j 100 -T 300 -P2 pgbench2

SELECT-only:

SELECT * FROM profile_log ;
 ts |  event_type   | event | count 
+---+---+---
 2017-02-21 15:21:52.45719  | LWLockNamed   | ProcArrayLock | 8
 2017-02-21 15:22:11.19594  | LWLockTranche | lock_manager  | 1
 2017-02-21 15:22:11.19594  | LWLockNamed   | ProcArrayLock |24
 2017-02-21 15:22:31.220803 | LWLockNamed   | ProcArrayLock | 1
 2017-02-21 15:23:01.255969 | LWLockNamed   | ProcArrayLock | 1
 2017-02-21 15:23:11.272254 | LWLockNamed   | ProcArrayLock | 2
 2017-02-21 15:23:41.313069 | LWLockNamed   | ProcArrayLock | 1
 2017-02-21 15:24:31.37512  | LWLockNamed   | ProcArrayLock |19
 2017-02-21 15:24:41.386974 | LWLockNamed   | ProcArrayLock | 1
 2017-02-21 15:26:41.530399 | LWLockNamed   | ProcArrayLock | 1
(10 rows)

writes pgbench runs have far more events logged, see the attached text
file. Maybe this is of interest...


[1] http://akorotkov.github.io/blog/2016/03/25/wait_monitoring_9_6/ ts |  event_type   |event | count 
+---+--+---
 2017-02-21 15:45:18.875842 | Lock  | tuple|18
 2017-02-21 15:45:18.875842 | LWLockTranche | lock_manager |35
 2017-02-21 15:45:18.875842 | LWLockTranche | buffer_mapping   | 1
 2017-02-21 15:45:18.875842 | LWLockTranche | buffer_content   |   402
 2017-02-21 15:45:18.875842 | LWLockTranche | wal_insert   |  1880
 2017-02-21 15:45:18.875842 | Lock  | transactionid|   716
 2017-02-21 15:45:18.875842 | LWLockNamed   | ProcArrayLock|   644
 2017-02-21 15:45:18.875842 | LWLockNamed   | CLogControlLock  |   255
 2017-02-21 15:45:18.875842 | LWLockNamed   | XidGenLock   |   104
 2017-02-21 15:45:18.875842 | Lock  | extend   |   647
 2017-02-21 15:45:28.889785 | Lock  | tuple|65
 2017-02-21 15:45:28.889785 | LWLockTranche | lock_manager |96
 2017-02-21 15:45:28.889785 | LWLockTranche | buffer_content   |   811
 2017-02-21 15:45:28.889785 | LWLockTranche | wal_insert   |  2542
 2017-02-21 15:45:28.889785 | Lock  | transactionid|  1686
 2017-02-21 15:45:28.889785 | LWLockNamed   | ProcArrayLock|  1194
 2017-02-21 15:45:28.889785 | LWLockNamed   | CLogControlLock  |  1244
 2017-02-21 15:45:28.889785 | LWLockNamed   | XidGenLock   |   401
 2017-02-21 15:45:28.889785 | Lock  | extend   |   818
 2017-02-21 15:45:38.904761 | Lock  | tuple|54
 2017-02-21 15:45:38.904761 | LWLockTranche | lock_manager |87
 2017-02-21 15:45:38.904761 | LWLockTranche | buffer_content   |   756
 2017-02-21 15:45:38.904761 | LWLockTranche | wal_insert   |  2161
 2017-02-21 15:45:38.904761 | Lock  | transactionid|  1624
 2017-02-21 15:45:38.904761 | LWLockNamed   | ProcArrayLock|  1154
 2017-02-21 15:45:38.904761 | LWLockNamed   | CLogControlLock  |  1418
 2017-02-21 15:45:38.904761 | LWLockNamed   | XidGenLock   |   450
 2017-02-21 15:45:38.904761 | Lock  | extend   |   650
 2017-02-21 15:45:48.917774 | Lock  | tuple|51
 2017-02-21 15:45:48.917774 | LWLockTranche | lock_manager |85
 2017-02-21 15:45:48.917774 | LWLockTranche | buffer_content   |   947
 2017-02-21 15:45:48.917774 | LWLockTranche | wal_insert   |  2050
 2017-02-21 15:45:48.917774 | Lock  | transactionid|  1625
 2017-02-21 15:45:48.917774 | LWLockNamed   | ProcArrayLock|  1094
 2017-02-21 15:45:48.917774 | LWLockNamed   | CLogControlLock  |  2575
 2017-02-21 15:45:48.917774 | LWLockNamed   | XidGenLock   |   372
 2017-02-21 15:45:48.917774 | Lock  | extend   |   528
 2017-02-21 15:45:58.930732 | Lock  | tuple|63
 2017-02-21 15:45:58.930732 | LWLockTranche | lock_manager |86
 2017-02-21 15:45:58.930732 | LWLockTranche | buffer_content   |  1026
 2017-02-21 15:45:58.930732 | LWLockTranche | wal_insert   |  1543
 2017-02-21 15:45:58.930732 | Lock  | transactionid|  1794
 2017-02-21 15:45:58.930732 | LWLockNamed   | ProcArrayLock|  1154
 2017-02-21 15:45:58.930732 | LWLockNamed   | CLogControlLock  |  3850
 2017-02-21 15:45:58.930732 | LWLockNamed   | XidGenLock   |   315
 2017-02-21 15:45:58.930732 | Lock  | extend   |   

Re: [HACKERS] LWLock optimization for multicore Power machines

[Bernd Helmle]

Am Dienstag, den 14.02.2017, 15:53 +0300 schrieb Alexander Korotkov:
> +1
> And you could try to use pg_wait_sampling
>  to sampling of wait
> events.

Okay, i'm going to try this. Currently Tomas' scripts are still
running, i'll provide updates as soon as they are finished.


-- 
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] Should we cacheline align PGXACT?

[Bernd Helmle]

Am Montag, den 13.02.2017, 16:55 +0300 schrieb Alexander Korotkov:
> 
> Thank you for testing.
> 
> Yes, influence seems to be low.  But nevertheless it's important to
> insure
> that there is no regression here.
> Despite pg_prewarm'ing and running tests 300s, there is still
> significant
> variation.
> For instance, with clients count = 80:
>  * pgxact-result-2.txt – 474704
>  * pgxact-results.txt – 574844

> Could some background processes influence the tests?  Or could it be
> another virtual machine?
> Also, I wonder why I can't see this variation on the graphs.
> Another issue with graphs is that we can't see details of read and
> write

Whoops, good catch. I've mistakenly copied the wrong y-axis for these
results in the gnuplot script, shame on me. New plots attached.

You're right, the 2nd run with the pgxact alignment patch is notable.
I've realized that there was a pgbouncer instance running from a
previous test, but not sure if that could explain the difference.

> TPS variation on the same scale, because write TPS values are too
> low.  I
> think you should draw write benchmark on the separate graph.
> 

The Linux LPAR is the only one used atm. We got some more time for
Linux now and i'm going to prepare Tomas' script to run. Not sure i can
get to it today, though.



-- 
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] LWLock optimization for multicore Power machines

[Bernd Helmle]

Am Samstag, den 11.02.2017, 15:42 +0300 schrieb Alexander Korotkov:
> Thus, I see reasons why in your tests absolute results are lower than
> in my
> previous tests.
> 1.  You use 28 physical cores while I was using 32 physical cores.
> 2.  You run tests in PowerVM while I was running test on bare metal.
> PowerVM could have some overhead.
> 3.  I guess you run pgbench on the same machine.  While in my tests
> pgbench
> was running on another node of IBM E880.
> 

Yeah, pgbench was running locally. Maybe we can get some resources to
run them remotely. Interesting side note: If you run a second postgres
instance with the same pgbench in parallel, you get nearly the same
transaction throughput as a single instance.

Short side note:

If you run two Postgres instances concurrently with the same pgbench
parameters, you get nearly the same transaction throughput for both
instances each as when running against a single instance, e.g.


- single

transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 112
number of threads: 112
duration: 300 s
number of transactions actually processed: 121523797
latency average = 0.276 ms
latency stddev = 0.096 ms
tps = 405075.282309 (including connections establishing)
tps = 405114.299174 (excluding connections establishing)

instance-1/instance-2 concurrently run:

transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 112
number of threads: 56
duration: 300 s
number of transactions actually processed: 120645351
latency average = 0.278 ms
latency stddev = 0.158 ms
tps = 402148.536087 (including connections establishing)
tps = 402199.952824 (excluding connections establishing)

transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 112
number of threads: 56
duration: 300 s
number of transactions actually processed: 121959772
latency average = 0.275 ms
latency stddev = 0.110 ms
tps = 406530.139080 (including connections establishing)
tps = 406556.658638 (excluding connections establishing)

So it looks like the machine has plenty of power, but PostgreSQL is
limiting somewhere.

> Therefore, having lower absolute numbers in your tests, win of LWLock
> optimization is also lower.  That is understandable.  But win of
> LWLock
> optimization is clearly visible definitely exceeds variation.
> 
> I think it would make sense to run more kinds of tests.  Could you
> try set
> of tests provided by Tomas Vondra?
> If even we wouldn't see win some of the tests, it would be still
> valuable
> to see that there is no regression there.

Unfortunately there are some test for AIX scheduled, which will assign
resources to that LPAR...i've just talked to the people responsible for
the machine and we can get more time for the Linux tests ;)



-- 
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] Should we cacheline align PGXACT?

[Bernd Helmle]

Am Samstag, den 11.02.2017, 00:28 +0100 schrieb Tomas Vondra:
> Comparing averages of tps, measured on 5 runs (each 5 minutes long),
> the 
> difference between master and patched master is usually within 2%,
> which 
> is pretty much within noise.
> 
> I'm attaching spreadsheets with summary of the results, so that we
> have 
> it in the archives. As usual, the scripts and much more detailed
> results 
> are available here:

I've done some benchmarking of this patch against the E850/ppc64el
Ubuntu LPAR we currently have access to and got the attached results.
pg_prewarm as recommended by Alexander was used, the tests run 300s
secs, scale 1000, each with a testrun before. The SELECT-only pgbench
was run twice each, the write tests only once.

Looks like the influence of this patch isn't that big, at least on this
machine.

We're going to reassign the resources to an AIX LPAR soon, which
doesn't give me enough time to test with Tomas' test scripts again.
>> clients 10 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 10
number of threads: 10
duration: 300 s
number of transactions actually processed: 39931683
latency average = 0.075 ms
tps = 133105.478637 (including connections establishing)
tps = 133109.428803 (excluding connections establishing)
>> clients 20 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 20
number of threads: 20
duration: 300 s
number of transactions actually processed: 78852558
latency average = 0.076 ms
tps = 262841.340316 (including connections establishing)
tps = 262855.469408 (excluding connections establishing)
>> clients 40 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 40
number of threads: 40
duration: 300 s
number of transactions actually processed: 118612968
latency average = 0.101 ms
tps = 395375.595262 (including connections establishing)
tps = 395432.166071 (excluding connections establishing)
>> clients 80 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 80
number of threads: 80
duration: 300 s
number of transactions actually processed: 170171627
latency average = 0.141 ms
tps = 567235.428660 (including connections establishing)
tps = 567322.181229 (excluding connections establishing)
>> clients 100 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 100
duration: 300 s
number of transactions actually processed: 168267642
latency average = 0.178 ms
tps = 560888.338862 (including connections establishing)
tps = 561076.995776 (excluding connections establishing)
>> clients 160 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 160
number of threads: 160
duration: 300 s
number of transactions actually processed: 146675068
latency average = 0.327 ms
tps = 488911.857866 (including connections establishing)
tps = 489155.251547 (excluding connections establishing)
>> clients 240 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 240
number of threads: 240
duration: 300 s
number of transactions actually processed: 139558941
latency average = 0.516 ms
tps = 465184.240782 (including connections establishing)
tps = 466046.696168 (excluding connections establishing)
>> clients 280 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 280
number of threads: 280
duration: 300 s
number of transactions actually processed: 137419242
latency average = 0.611 ms
tps = 458052.555736 (including connections establishing)
tps = 459795.543770 (excluding connections establishing)
>> clients 10 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 10
number of threads: 10
duration: 300 s
number of transactions actually processed: 40994263
latency average = 0.073 ms
tps = 136647.383534 (including connections establishing)
tps = 136649.850380 (excluding connections establishing)
>> clients 20 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 20
number of threads: 20
duration: 300 s
number of transactions actually processed: 71023846
latency average = 0.084 ms
tps = 236745.708077 (including connections establishing)
tps = 236752.743230 (excluding connections establishing)
>> clients 40 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 40
number of threads: 40
duration: 300 s
number of transactions actually processed: 120060357
latency average = 0.100 ms
tps = 400200.095159 (including connections establishing)
tps = 400238.563079 (excluding connections establishing)
>> clients 80 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 80
number of threads: 80
duration: 300 s
number of transactions actually processed: 170353955
latency average = 0.141 ms
tps = 567843.510912 (including connections establishing)
tps = 

Re: [HACKERS] LWLock optimization for multicore Power machines

[Bernd Helmle]

Am Dienstag, den 07.02.2017, 16:48 +0300 schrieb Alexander Korotkov:
> But win isn't
> as high as I observed earlier.  And I wonder why absolute numbers are
> lower
> than in our earlier experiments.  We used IBM E880 which is actually
> two

Did you run your tests on bare metal or were they also virtualized?

> nodes with interconnect.  However interconnect is not fast enough to
> make
> one PostgreSQL instance work on both nodes.  Thus, used half of IBM
> E880
> which has 4 sockets and 32 physical cores.  While you use IBM E850
> which is
> really single node with 4 sockets and 48 physical cores.  Thus, it
> seems
> that you have lower absolute numbers on more powerful hardware.  That
> makes
> me uneasy and I think we probably don't get the best from this
> hardware.
> Just in case, do you use SMT=8?

Yes, SMT=8 was used.

The machine has 4 sockets, 8 Core each, 3.7 GHz clock frequency. The
Ubuntu LPAR running on PowerVM isn't using all physical cores,
currently 28 cores are assigned (=224 SMT Threads). The other cores are
dedicated to the PowerVM hypervisor and a (very) small AIX LPAR.

I've done more pgbenches this morning with SMT-4, too, fastest result
with master was 

SMT-4

transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 100
duration: 300 s
number of transactions actually processed: 167306423
latency average = 0.179 ms
latency stddev = 0.072 ms
tps = 557685.144912 (including connections establishing)
tps = 557835.683204 (excluding connections establishing)

compared with SMT-8:

transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 100
duration: 300 s
number of transactions actually processed: 173476449
latency average = 0.173 ms
latency stddev = 0.059 ms
tps = 578250.676019 (including connections establishing)
tps = 578412.159601 (excluding connections establishing)

and retried with lwlocks-power-3, SMT-4:

transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 100
duration: 300 s
number of transactions actually processed: 185991995
latency average = 0.161 ms
latency stddev = 0.059 ms
tps = 619970.030069 (including connections establishing)
tps = 620112.263770 (excluding connections establishing)
credativ@iicl183:~/git/postgres$ 

...and SMT-8

transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 100
duration: 300 s
number of transactions actually processed: 185878717
latency average = 0.161 ms
latency stddev = 0.047 ms
tps = 619591.476154 (including connections establishing)
tps = 619655.867280 (excluding connections establishing)

Interestingly the lwlocks patch seems to decrease the SMT influence
factor.

Side note: the system makes around 2 Mio Context Switches during the
benchmarks, e.g.

awk '{print $12;}' /tmp/vmstat.out 

cs
10
2153533
2134864
2141623
2126845
2128330
2127454
2145325
2126769
2134492
2130246
2130071
2142660
2136077
2126783
2126107
2125823
2136511
2137752
2146307
2141127

I've also tried a more recent kernel this morning (4.4 vs. 4.8), but
this didn't change the picture. Is there anything more i can do?




-- 
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] LWLock optimization for multicore Power machines

[Bernd Helmle]

Am Montag, den 06.02.2017, 22:44 +0300 schrieb Alexander Korotkov:
>    2. Also could you run each test longer: 3-5 mins, and run them
> with
> 
>    variety of clients count?

So here are some other results. I've changed max_connections to 300.
The bench was prewarmed and run 300s each.
I could run more benches, if necessary.

>> clients 10 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 10
number of threads: 10
duration: 300 s
number of transactions actually processed: 47435397
latency average = 0.063 ms
tps = 158117.858124 (including connections establishing)
tps = 158121.264103 (excluding connections establishing)
>> clients 20 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 20
number of threads: 20
duration: 300 s
number of transactions actually processed: 85894461
latency average = 0.070 ms
tps = 286314.516993 (including connections establishing)
tps = 286325.924487 (excluding connections establishing)
>> clients 80 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 80
number of threads: 80
duration: 300 s
number of transactions actually processed: 173185067
latency average = 0.139 ms
tps = 577280.609099 (including connections establishing)
tps = 577482.251431 (excluding connections establishing)
>> clients 100 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 100
duration: 300 s
number of transactions actually processed: 182337951
latency average = 0.165 ms
tps = 607788.998215 (including connections establishing)
tps = 607879.190538 (excluding connections establishing)
>> clients 140 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 140
number of threads: 140
duration: 300 s
number of transactions actually processed: 167425515
latency average = 0.251 ms
tps = 558080.131402 (including connections establishing)
tps = 558230.505974 (excluding connections establishing)
>> clients 200 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 200
number of threads: 200
duration: 300 s
number of transactions actually processed: 150990552
latency average = 0.397 ms
tps = 503290.086636 (including connections establishing)
tps = 503486.345115 (excluding connections establishing)
>> clients 10 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 10
number of threads: 10
duration: 300 s
number of transactions actually processed: 49190440
latency average = 0.061 ms
tps = 163967.958649 (including connections establishing)
tps = 163970.305002 (excluding connections establishing)
>> clients 20 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 20
number of threads: 20
duration: 300 s
number of transactions actually processed: 81753716
latency average = 0.073 ms
tps = 272511.936932 (including connections establishing)
tps = 272518.387577 (excluding connections establishing)
>> clients 80 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 80
number of threads: 80
duration: 300 s
number of transactions actually processed: 173734287
latency average = 0.138 ms
tps = 579110.743767 (including connections establishing)
tps = 579171.270968 (excluding connections establishing)
>> clients 100 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 100
duration: 300 s
number of transactions actually processed: 183013962
latency average = 0.164 ms
tps = 610042.164629 (including connections establishing)
tps = 610124.661236 (excluding connections establishing)
>> clients 140 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 140
number of threads: 140
duration: 300 s
number of transactions actually processed: 169105547
latency average = 0.248 ms
tps = 563679.957119 (including connections establishing)
tps = 563925.230369 (excluding connections establishing)
>> clients 200 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 200
number of threads: 200
duration: 300 s
number of transactions actually processed: 152377373
latency average = 0.394 ms
tps = 507918.997085 (including connections establishing)
tps = 508259.090735 (excluding connections establishing)
>> clients 10 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 10
number of threads: 10
duration: 300 s
number of transactions actually processed: 49556864
latency average = 0.061 ms
tps = 165189.402451 (including connections establishing)
tps = 165191.717347 (excluding connections establishing)
>> clients 20 <
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 20
number of threads: 20
duration: 300 s
number of transactions actually processed: 82017648
latency average = 0.073 ms
tps = 273391.829687 

Re: [HACKERS] LWLock optimization for multicore Power machines

[Bernd Helmle]

On Mon, 2017-02-06 at 22:44 +0300, Alexander Korotkov wrote:
> Results looks strange for me.  I wonder why there is difference
> between
> lwlock-power-1.patch and lwlock-power-3.patch?  From my intuition, it
> shouldn't be there because it's not much difference between them. 
> Thus, I
> have following questions.
> 
> 

Yeah, i've realized that as well.

>    1. Have you warm up database?  I.e. could you do "SELECT sum(x.x)
> FROM
>    (SELECT pg_prewarm(oid) AS x FROM pg_class WHERE relkind IN ('i',
> 'r')
>    ORDER BY oid) x;" before each run?
>    2. Also could you run each test longer: 3-5 mins, and run them
> with
>    variety of clients count?

The results i've posted were the last 3 run of 9 in summary. I hoped
that should be enough to prewarm the system. I'm going to repeat the
tests with the changes you've requested, though.



-- 
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] LWLock optimization for multicore Power machines

[Bernd Helmle]

Am Montag, den 06.02.2017, 16:45 +0300 schrieb Alexander Korotkov:
> I tried lwlock-power-2.patch on multicore Power machine we have in
> PostgresPro.
> I realized that using labels in assembly isn't safe.  Thus, I removed
> labels and use relative jumps instead (lwlock-power-2.patch).
> Unfortunately, I didn't manage to make any reasonable benchmarks. 
> This
> machine runs AIX, and there are a lot of problems which prevents
> PostgreSQL
> to show high TPS.  Installing Linux there is not an option too,
> because
> that machine is used for tries to make Postgres work properly on AIX.
> So, benchmarking help is very relevant.  I would very appreciate
> that.

Okay, so here are some results. The bench runs against
current PostgreSQL master, 24 GByte shared_buffers configured (128
GByte physical RAM), max_wal_size=8GB and effective_cache_size=100GB.

I've just discovered that max_connections was accidently set to 601,
normally i'd have set something near 110 or so...



transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 100
duration: 30 s
number of transactions actually processed: 16910687
latency average = 0.177 ms
tps = 563654.968585 (including connections establishing)
tps = 563991.459659 (excluding connections establishing)
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 100
duration: 30 s
number of transactions actually processed: 16523247
latency average = 0.182 ms
tps = 550744.748084 (including connections establishing)
tps = 552069.267389 (excluding connections establishing)
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 100
duration: 30 s
number of transactions actually processed: 16796056
latency average = 0.179 ms
tps = 559830.986738 (including connections establishing)
tps = 560333.682010 (excluding connections establishing)



transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 100
duration: 30 s
number of transactions actually processed: 14563500
latency average = 0.206 ms
tps = 485420.764515 (including connections establishing)
tps = 485720.606371 (excluding connections establishing)
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 100
duration: 30 s
number of transactions actually processed: 14618457
latency average = 0.205 ms
tps = 487246.817758 (including connections establishing)
tps = 488117.718816 (excluding connections establishing)
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 100
duration: 30 s
number of transactions actually processed: 14522462
latency average = 0.207 ms
tps = 484052.194063 (including connections establishing)
tps = 485434.771590 (excluding connections establishing)



transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 100
duration: 30 s
number of transactions actually processed: 17946058
latency average = 0.167 ms
tps = 598164.841490 (including connections establishing)
tps = 598582.503248 (excluding connections establishing)
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 100
duration: 30 s
number of transactions actually processed: 17719648
latency average = 0.169 ms
tps = 590621.671588 (including connections establishing)
tps = 591093.333153 (excluding connections establishing)
transaction type: 
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 100
duration: 30 s
number of transactions actually processed: 17722941
latency average = 0.169 ms
tps = 590728.715465 (including connections establishing)
tps = 591619.817043 (excluding connections establishing)


-- 
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] LWLock optimization for multicore Power machines

[Bernd Helmle]

On Fri, 2017-02-03 at 20:11 +0300, Alexander Korotkov wrote:
> On Fri, Feb 3, 2017 at 8:01 PM, Alexander Korotkov <
> a.korot...@postgrespro.ru> wrote:
> 
> > Unfortunately, I have no big enough Power machine at hand to
> > reproduce
> > that results.  Actually, I have no Power machine at hand at
> > all.  So,
> > lwlock-power-2.patch was written "blindly".  I would very
> > appreciate if
> > someone would help me with testing and benchmarking.
> > 
> 
> UPD: It appears that Postgres Pro have access to big Power machine
> now.
> So, I can do testing/benchmarking myself.
> 

Hi Alexander,

We currently also have access to a LPAR on an E850 machine with 4
sockets POWER8 running a Ubuntu 16.04 LTS Server ppc64el OS. I can do
some tests next week, if you need to verify your findings.

Thanks,

Bernd


-- 
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] Deadlock in XLogInsert at AIX

[Bernd Helmle]

Hi Konstantin,

We had observed exactly the same issues on a customer system with the
same environment and PostgreSQL 9.5.5. Additionally, we've tested on
Linux with XL/C 12 and 13 with exactly the same deadlock behavior. 

So we assumed that this is somehow a compiler issue.

Am Dienstag, den 24.01.2017, 19:26 +0300 schrieb Konstantin Knizhnik:
> More information about the problem - Postgres log contains several
> records:
> 
> 2017-01-24 19:15:20.272 MSK [19270462] LOG:  request to flush past
> end 
> of generated WAL; request 6/AAEBE000, currpos 6/AAEBC2B0
> 
> and them correspond to the time when deadlock happen.

Yeah, the same logs here:

LOG:  request to flush past end of generated WAL; request 1/1F4C6000,
currpos 1/1F4C40E0
STATEMENT:  UPDATE pgbench_accounts SET abalance = abalance + -2653
WHERE aid = 3662494;


> There is the following comment in xlog.c concerning this message:
> 
>  /*
>   * No-one should request to flush a piece of WAL that hasn't
> even been
>   * reserved yet. However, it can happen if there is a block with
> a 
> bogus
>   * LSN on disk, for example. XLogFlush checks for that situation
> and
>   * complains, but only after the flush. Here we just assume that
> to 
> mean
>   * that all WAL that has been reserved needs to be finished. In
> this
>   * corner-case, the return value can be smaller than 'upto'
> argument.
>   */
> 
> So looks like it should not happen.
> The first thing to suspect is spinlock implementation which is
> different 
> for GCC and XLC.
> But ... if I rebuild Postgres without spinlocks, then the problem is 
> still reproduced.

Before we got the results from XLC on Linux (where Postgres show the
same behavior) i had a look into the spinlock implementation. If i got
it right, XLC doesn't use the ppc64 specific ones, but the fallback
implementation (system monitoring on AIX also has shown massive calls
for signal(0)...). So i tried the following patch:

diff --git a/src/include/port/atomics/arch-ppc.h
b/src/include/port/atomics/arch-ppc.h
new file mode 100644
index f901a0c..028cced
*** a/src/include/port/atomics/arch-ppc.h
--- b/src/include/port/atomics/arch-ppc.h
***
*** 23,26 
--- 23,33 
  #define pg_memory_barrier_impl()  __asm__ __volatile__ ("sync" :
: :
"memory")
  #define pg_read_barrier_impl()__asm__ __volatile__
("lwsync" : : : "memory")
  #define pg_write_barrier_impl()   __asm__ __volatile__
("lwsync" : : : "memory")
+
+ #elif defined(__IBMC__) || defined(__IBMCPP__)
+
+ #define pg_memory_barrier_impl()  __asm__ __volatile__ (" sync
\n"
::: "memory")
+ #define pg_read_barrier_impl()__asm__ __volatile__ ("
lwsync \n" ::: "memory")
+ #define pg_write_barrier_impl()   __asm__ __volatile__ ("
lwsync \n" ::: "memory")
+
  #endif

This didn't change the picture, though.



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

[HACKERS] recovery_min_apply_delay vs. synchronous_commit

[Bernd Helmle]

Current PostgreSQL Documentation on recovery.conf has this about
recovery_min_apply_delay[1]:

---<---

This parameter is intended for use with streaming replication deployments;
however, if the parameter is specified it will be honored in all cases.
Synchronous replication is not affected by this setting because there is
not yet any setting to request synchronous apply of transaction commits.

--->---

If i understand correctly, this is not true anymore with 9.6, where
remote_apply will have exactly the behavior the paragraph above wants to
contradict: any transaction executed with synchronous_commit=remote_apply
will wait at least recovery_min_apply_delay to finish. Given that
synchronous_commit can be controlled by any user, this might be dangerous
if someone doesn't take care enough.

I think we need a doc patch for that at least, see attached patch against
master, but 9.6 should have a corrected one, too.

[1] <https://www.postgresql.org/docs/devel/static/standby-settings.html>

-- 
Mit freundlichen Grüßen

    Bernd Helmle


recovery-config-doc.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

[HACKERS] recovery_min_apply-delay and remote_apply

[Bernd Helmle]

Current PostgreSQL Documentation on recovery.conf has this about
recovery_min_apply_delay[1]:

---<---

This parameter is intended for use with streaming replication deployments;
however, if the parameter is specified it will be honored in all cases.
Synchronous replication is not affected by this setting because there is
not yet any setting to request synchronous apply of transaction commits.

--->---

If i understand correctly, this is not true anymore with 9.6, where
remote_apply will have exactly the behavior the paragraph above wants to
contradict: any transaction executed with synchronous_commit=remote_apply
will wait at least recovery_min_apply_delay to finish. Given that
synchronous_commit can be controlled by any user, this might be dangerous
if someone doesn't take care enough.

I think we need a doc patch for that at least, see attached patch against
master, but 9.6 should have a corrected one, too.

[1] 


-- 
Thanks

Bernd

recovery-config-doc.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] standalone backend PANICs during recovery

[Bernd Helmle]

--On 3. September 2016 um 02:05:00 -0400 Tom Lane  wrote:

>> Well, the "use case" was a crashed hot standby at a customer site (it
>> kept PANICing after restarting), where i decided to have a look through
>> the recovery process with gdb. The exact PANIC was
> 
>> 2016-03-29 15:12:40 CEST [16097]: [26-1] user=,db=,xid=0,vxid=1/0 FATAL:
>> unexpected GIN leaf action: 0
> 
> BTW, that didn't happen to be on big-endian hardware did it?

You're right, this was RHEL7 on a POWER7 machine.

-- 
Thanks

Bernd


-- 
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] standalone backend PANICs during recovery

[Bernd Helmle]

--On 20. August 2016 12:41:48 -0400 Tom Lane  wrote:

> So at this point I'm pretty baffled as to what the actual use-case is
> here.  I am tempted to say that a standalone backend should refuse to
> start at all if a recovery.conf file is present.  If we do want to
> allow the case, we need some careful thought about what it should do.

Well, the "use case" was a crashed hot standby at a customer site (it kept
PANICing after restarting), where i decided to have a look through the
recovery process with gdb. The exact PANIC was

2016-03-29 15:12:40 CEST [16097]: [26-1] user=,db=,xid=0,vxid=1/0 FATAL:
unexpected GIN leaf action: 0

I had the idea that it was quick and dirty to use a single backend. I was
surprised that this time it PANIC'ed differently

That said, i'm okay if --single is not intended to bring up a hot standby.
There are many other ways to debug such problems.

-- 
Thanks

Bernd


-- 
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] PSA: Systemd will kill PostgreSQL

[Bernd Helmle]

--On 11. Juli 2016 13:25:51 +0800 Craig Ringer 
wrote:

> Perhaps by uid threshold in login.defs?

systemd's configure.ac has this:

AC_ARG_WITH(system-uid-max,
AS_HELP_STRING([--with-system-uid-max=UID]
[Maximum UID for system users]),
[SYSTEM_UID_MAX="$withval"],
[SYSTEM_UID_MAX="`awk 'BEGIN { uid=999 } /^\s*SYS_UID_MAX\s+/ {
uid=$2 } END { print uid }' /etc/login.defs 2>/dev/null || echo 999`"])

so yes, it's the definition from there.

> But then what happens for people
> who're managing users via a directory, who need to avoid conflicting with
> host-local UIDs, but also need some of those users to have systemd
> "system user" like behaviour?

We had this in the past in some setups and this would add another reason
for unexpected headaches...

-- 
Thanks

Bernd


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

[HACKERS] pg_dump vs. idle_in_transaction_session_timeout

[Bernd Helmle]

Currently pg_dump doesn't turn off idle_in_transaction_session_timeout.

Okay, the window of failure here is very narrow (on my machine it breaks
with an insane setting of 1ms only), but for the sake of reliable backups
and protection against over motivated DBA it looks better to me to turn
that off, no?

--
 
Thanks

Bernd


-- 
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 FDW AddForeignUpdateTargets for a hidden pseudo-column

[Bernd Helmle]

--On 14. Juni 2016 10:32:13 + Albe Laurenz 
wrote:

> I first thought of using the internal ROWID column that's probably
> similar to your case, but that wouldn't fit into a tid's 6 bytes, and I
> found that I could only add resjunk columns for existing columns of the
> table.
> Making the internal ROWID an explicit column in the foreign table seemed
> just too ugly.

The Informix FDW uses SelfItemPointerAttributeNumber. Luckily the Informix
ROWID is a 4 byte encoded identifier (3 first significant bytes are the
logical page number, last significant bytes is the slot number within that
page). Maybe you can find a way of logically addressing your data, too? It
only needs to fit within 6 bytes, afaik.

-- 
Thanks

Bernd


-- 
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] standalone backend PANICs during recovery

[Bernd Helmle]

--On 5. April 2016 21:50:17 -0400 Robert Haas  wrote:

> If nobody's going to commit this right away, this should be added to
> the next CommitFest so we don't forget it.

Done.

-- 
Thanks

Bernd


-- 
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] Problems with huge_pages and IBM Power8

[Bernd Helmle]

--On 12. April 2016 13:20:10 -0700 Andres Freund  wrote:

>> When working with huge_pages, we initially got this error.
>> 
>> munmap(0x3efbe400) failed: Invalid argument
> 
> *munmap*, not mmap failed? that's odd; because there the hugepagesize
> shouldn't have much of an influence. If something fails it should be the
> initial mmap.  Could you show a strace of a failed start with an
> unmodified postgres?

Well, the manpage on the the buildfarm member chub says this:


The address addr must be a multiple of the page size.  All pages containing
a part of the indicated range are unmapped,  and
   subsequent  references to these pages will generate SIGSEGV.  It is
not an error if the indicated range does not contain any
   mapped pages.


There are other production machines running on this POWER8 machine, all
have huge pages activated and i've seen this message several times, but
afair only during initdb and when stopping the server. No crashes. 

Indeed, I had the exactly same patch than Reiner, when investigating this,
but i couldn't find an easy way to make it somehow automatic for other page
sizes (e.g. on large Intel machines you can have even 1G Pages).

Though, we haven't encountered any problems on the production machines so
far.

-- 
Thanks

Bernd


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

[HACKERS] standalone backend PANICs during recovery

[Bernd Helmle]

While investigating a problem on a streaming hot standby instance at a
customer site, i got the following when using a standalone backend:

PANIC:  btree_xlog_delete_get_latestRemovedXid: cannot operate with
inconsistent data
CONTEXT:  xlog redo delete: index 1663/65588/65625; iblk 11, heap
1663/65588/65613;

The responsible PANIC is triggered here:

src/backend/access/nbtree/nbtxlog.c:555

btree_xlog_delete_get_latestRemovedXid(XLogReaderState *record)
{

[...]
if (!reachedConsistency)
elog(PANIC, "btree_xlog_delete_get_latestRemovedXid: cannot 
operate with
inconsistent data");
[...]

}

There's already an "optimization" before, exiting with InvalidTransactionId
in case a HS doesn't count any active backends. In standalone mode however,
CountDBBackends() will always return 1 afaik. It looks like
btree_xlog_delete_get_latestRemovedXid() needs an additional check for
standalone backends, so i came up with the attached patch. This allowed the
standalone backend to recover without any problems.

-- 
Thanks

Bernd

standalone_recover.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] Disabling START TRANSACTION for a SuperUser

[Bernd Helmle]

--On 27. Oktober 2015 14:07:06 + Kevin Grittner 
wrote:

>  It would be a boon to big shops if they could
> declare (preferably with the option to set it at a role level) that
> specific default_transaction_* settings could not be overridden.

A while ago i was faced with exactly the same problem. Thinking about it
again, i think that this also applies to various other parameters a DBA
wants to restrict to its roles. E.g. resource consumption limits (work_mem,
...), session constraints like the discussed transaction modes or even not
allowing to change the application_name.

afaicr, Oracle has a CREATE PROFILE which is primilarily intended to add
resource or password restrictions to users. Maybe this can be seen as a
blueprint to introduce the concept of GUC profiles to postgres, where a set
with maybe restrictions on the allowed values for certain GUCs can be
attached to roles. That for sure is a huge project.

Another idea (and maybe not that invasive like the profile idea) might be
to just introduce a concept of "read only" GUCs. A role would get a list of
GUCs which it is not allowed to change if given with ALTER ROLE...SET
(maybe restricted to PGC_USERSET). That could be stored along the same way
like pg_db_role_settings. However, i haven't checked how complicated this
would be to incorporate into the guc assign hooks, but maybe its doable
somehow.

-- 
Thanks

Bernd


-- 
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] Avoid full page images in streaming replication?

[Bernd Helmle]

--On 22. Oktober 2015 22:23:58 -0300 Alvaro Herrera
 wrote:

>> You can? The xlog format between 9.4 and 9.5 changed, so I can't see how
>> that'd work?
> 
> Oh, crap.  Must have been some other cross-version trial run I did,
> then.  I would hope it's at least not terribly difficult to back-patch
> that commit locally, anyway.

You might want to try 



Adrian created it basically to track down archive replay performance issues
a while ago. Maybe it's useful somehow.

-- 
Thanks

Bernd


-- 
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] Avoid full page images in streaming replication?

[Bernd Helmle]

--On 23. Oktober 2015 00:03:30 +0200 Andres Freund 
wrote:

> 
> Note that FPIs are often pretty good for replay performance, avoiding
> lots of synchronous random reads.

That's a very import argument, i think. The difference can be significant,
even if you have a decent storage, rendering a replica unusable. We had
examples in the past where only optimizing aggressive writes of FPIs lead
to an acceptable lag of the replica.

-- 
Thanks

Bernd


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

[HACKERS] Duplicated assignment of slot_name in walsender.c

[Bernd Helmle]

walsender.c, CreateReplicationSlot() currently has this:

slot_name = NameStr(MyReplicationSlot->data.name);

if (cmd->kind == REPLICATION_KIND_LOGICAL)
{
[...]
}
else if (cmd->kind == REPLICATION_KIND_PHYSICAL && cmd->reserve_wal)
{
[...]
}

slot_name = NameStr(MyReplicationSlot->data.name);

The 2nd assignment to slot_name looks unnecessary?

-- 
Thanks

Bernd


-- 
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] btreecheck extension

[Bernd Helmle]

--On 16. Juni 2014 18:47:30 -0700 Peter Geoghegan  wrote:

> Attached prototype patch adds contrib extension, btreecheck. This
> extension provides SQL-callable functions for checking these
> conditions on nbtree indexes on live systems.

What's the current state of this module? I see you are interested in stress
testing, but i'm not sure how far this all is gone? 

This tool actually served a very good job during identifying index
corruption due to collation issues[1].

[1]


-- 
Thanks

Bernd


-- 
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] 9.3.9 and pg_multixact corruption

[Bernd Helmle]

...and here it is ;)

--On 10. September 2015 19:45:46 -0300 Alvaro Herrera
 wrote:

> Anyway, can you please request pg_controldata to be run on the failed
> cluster and paste it here?

pg_control version number:937
Catalog version number:   201306121
Database system identifier:   5995776571405068134
Database cluster state:   in archive recovery
pg_control last modified: Di 08 Sep 2015 14:58:36 CEST
Latest checkpoint location:   1A52/3CFAF758
Prior checkpoint location:1A52/3CFAF758
Latest checkpoint's REDO location:1A52/2313FEF8
Latest checkpoint's REDO WAL file:00011A520023
Latest checkpoint's TimeLineID:   1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:  0/2896610102
Latest checkpoint's NextOID:  261892
Latest checkpoint's NextMultiXactId:  1068223816
Latest checkpoint's NextMultiOffset:  2147460090
Latest checkpoint's oldestXID:2693040605
Latest checkpoint's oldestXID's DB:   16400
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1012219584
Latest checkpoint's oldestMulti's DB: 16400
Time of latest checkpoint:Di 08 Sep 2015 00:47:01 CEST
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location: 1A52/2313FEF8
Min recovery ending loc's timeline:   1
Backup start location:1A52/2313FEF8
Backup end location:  0/0
End-of-backup record required:no
Current wal_level setting:archive
Current max_connections setting:  500
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:   8
Database block size:  8192
Blocks per segment of large relation: 131072
WAL block size:   8192
Bytes per WAL segment:16777216
Maximum length of identifiers:64
Maximum columns in an index:  32
Maximum size of a TOAST chunk:1996
Date/time type storage:   64-bit integers
Float4 argument passing:  by value
Float8 argument passing:  by value
Data page checksum version:   0

-- 

Bernd



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

[HACKERS] 9.3.9 and pg_multixact corruption

[Bernd Helmle]

A customer had a severe issue with a PostgreSQL 9.3.9/sparc64/Solaris 11
instance.

The database crashed with the following log messages:

2015-09-08 00:49:16 CEST [2912] PANIC:  could not access status of
transaction 1068235595
2015-09-08 00:49:16 CEST [2912] DETAIL:  Could not open file
"pg_multixact/members/5FC4": No such file or directory.
2015-09-08 00:49:16 CEST [2912] STATEMENT:  delete from StockTransfer
where oid = $1 and tanum = $2 

When they called us later, it turned out that the crash happened during a
base backup, leaving a backup_label behind which prevented the database
coming up again with a invalid checkpoint location. However, removing the
backup_label still didn't let the database through recovery, it failed
again with the former error, this time during recovery:

2015-09-08 11:40:04 CEST [27047] LOG:  database system was interrupted
while in recovery at 2015-09-08 11:19:44 CEST
2015-09-08 11:40:04 CEST [27047] HINT:  This probably means that some data
is corrupted and you will have to use the last backup for recovery.
2015-09-08 11:40:04 CEST [27047] LOG:  database system was not properly
shut down; automatic recovery in progress
2015-09-08 11:40:05 CEST [27047] LOG:  redo starts at 1A52/2313FEF8
2015-09-08 11:40:47 CEST [27082] FATAL:  the database system is starting up
2015-09-08 11:40:59 CEST [27047] FATAL:  could not access status of
transaction 1068235595
2015-09-08 11:40:59 CEST [27047] DETAIL:  Could not seek in file
"pg_multixact/members/5FC4" to offset 4294950912: Invalid argument.
2015-09-08 11:40:59 CEST [27047] CONTEXT:  xlog redo create mxid 1068235595
offset 2147483648 nmembers 2: 2896635220 (upd) 2896635510 (keysh) 
2015-09-08 11:40:59 CEST [27045] LOG:  startup process (PID 27047) exited
with exit code 1
2015-09-08 11:40:59 CEST [27045] LOG:  aborting startup due to startup
process failure

Some side notes:

An additional recovery from a base backup and archive recovery yield to the
same error, as soon as the affected tuple was touched with a DELETE. The
affected table was fully dumpable via pg_dump, though.

We also have a core dump, but no direct access to the machine. If there's
more information  required (and i believe it is), let me know where to dig
deeper. I also would like to request a backtrace from the existing core
dump, but in the absence of a sparc64 machine here we need to ask the
customer to get one.

-- 
Thanks

Bernd


-- 
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] Disabling trust/ident authentication configure option

[Bernd Helmle]

--On 6. Mai 2015 16:28:43 -0400 Andrew Dunstan and...@dunslane.net wrote:

 Single user sessions would work, but the peer authentication is also 
 still available and should be the preferred method to reset passwords 
 when trust is disabled, so this should not be an issue.
 
 (Personally I think there's a very good case for completely ripping out
 RFC1413 ident auth. I've not seen it used in a great long while, and it's
 always been a security risk.)

I have the same feeling. I haven't seen it in the last 6+ years used
anywhere and I personally think it's a relict...so +1.

-- 
Thanks

Bernd


-- 
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] Disabling trust/ident authentication configure option

[Bernd Helmle]

--On 30. April 2015 08:00:23 -0400 Robert Haas robertmh...@gmail.com
wrote:

 But... the user could use password authentication with the password
 set to x and that would be insecure, too, yet not prevented by any
 of this.  I think it's pretty hard to prevent someone who has
 filesystem-level access to the database server from configuring it
 insecurely.

Sure. But I think the point is to make their engineers to think about what
they're doing. Typing in a password gives you at least a hint, that you are
probably should use something safe.

I agree that you couldn't really make that bullet proof from just this
excluded functionality, but i could imagine that this makes sense in a more
system-wide context.

 
 Of course, it's fine for people to make changes like this in their own
 copies of PostgreSQL, but I'm not in favor of incorporating those
 changes into core.  I don't think there's enough general utility to
 this to justify that, and more to the point, I think different people
 will want different things.  We haven't, for example, ever had a
 request for this specific thing before.

Well, i found at least one of such a proposal here:

http://www.postgresql.org/message-id/CAN2Y=umt7cpkxzhaufw7szeckdwcwsuulmh4xphuxkqbtdu...@mail.gmail.com


-- 
Thanks

Bernd


-- 
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] cost_index() and path row estimate.

[Bernd Helmle]

--On 1. Mai 2015 11:30:51 -0700 Tom Lane t...@sss.pgh.pa.us wrote:

 No.  The non-parameterized paths for a given relation must all have the
 same rowcount estimates; otherwise the path comparison logic fails
 fundamentally.  Another way to look at it is that every correct path
 will yield the same number of rows in reality; so it would be wrong to
 give a path that makes use of a partial index a rowcount advantage over
 a path that is not using the partial index but nonetheless is enforcing
 exactly the same set of scan restriction clauses.

Thanks for the explanation. 

-- 
Thanks

Bernd


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

[HACKERS] cost_index() and path row estimate.

[Bernd Helmle]

While looking into a customer performance problem, i saw this in
costsize.c, cost_index() (9.3.6, but it looks the same in HEAD):

/* Mark the path with the correct row estimate */
if (path-path.param_info)
{
path-path.rows = path-path.param_info-ppi_rows;
/* also get the set of clauses that should be enforced by the 
scan */
allclauses = 
list_concat(list_copy(path-path.param_info-ppi_clauses),
 
baserel-baserestrictinfo);
}
else
{
path-path.rows = baserel-rows;
/* allclauses should just be the rel's restriction clauses */
allclauses = baserel-baserestrictinfo;
}

What i'm wondering is the else branch, where the baserel row estimate is
assigned to the
IndexPath. However, it occurs to me that in conjunction with a partial
index, the overall row estimate will be constrained by the row estimate
from the partial index itself, no? I see that this doesn't have an impact
on the cost estimation of the index scan itself, since cost_index() does
this later:

/* estimate number of main-table tuples fetched */
tuples_fetched = clamp_row_est(indexSelectivity * baserel-tuples);

I stumpled across this, since i see heavy misestimates in the EXPLAIN
output, where the estimated row count from the partial index vs. the real
row count heavily differs. In the customers case, there are two tables,
where one of the relation has many tuples in the JOIN condition which are
NULLs, like:

CREATE TABLE a2(id integer);
CREATE TABLE b2(id integer);

INSERT INTO a2 SELECT NULL FROM generate_series(1, 9800) AS t(id);
INSERT INTO a2 SELECT t.id FROM generate_series(1, 200) AS t(id);
INSERT INTO b2 SELECT t.id FROM generate_series(1, 200) AS t(id);

CREATE INDEX ON a2(id) WHERE id IS NOT NULL;
CREATE INDEX ON b2(id);

Here i get the following plan:

EXPLAIN ANALYZE SELECT * FROM b2 INNER JOIN a2 ON a2.id = b2.id ;

 Merge Join  (cost=10.79..12.63 rows=4 width=8) (actual time=0.084..0.291
rows=200 loops=1)
   Merge Cond: (b2.id = a2.id)
   -  Sort  (cost=10.64..11.14 rows=200 width=4) (actual time=0.069..0.082
rows=200 loops=1)
 Sort Key: b2.id
 Sort Method: quicksort  Memory: 35kB
 -  Seq Scan on b2  (cost=0.00..3.00 rows=200 width=4) (actual
time=0.010..0.027 rows=200 loops=1)
   -  Index Only Scan using a2_id_idx on a2  (cost=0.14..15.14 rows=1
width=4) (actual time=0.012..0.074 rows=200 loops=1)
 Heap Fetches: 200
 Total runtime: 0.350 ms

EXPLAIN ANALYZE SELECT * FROM b2 INNER JOIN a2 ON a2.id = b2.id WHERE a2.id
IS NOT NULL;

 Merge Join  (cost=10.79..12.12 rows=1 width=8) (actual time=0.080..0.281
rows=200 loops=1)
   Merge Cond: (b2.id = a2.id)
   -  Sort  (cost=10.64..11.14 rows=200 width=4) (actual time=0.063..0.070
rows=200 loops=1)
 Sort Key: b2.id
 Sort Method: quicksort  Memory: 35kB
 -  Seq Scan on b2  (cost=0.00..3.00 rows=200 width=4) (actual
time=0.010..0.034 rows=200 loops=1)
   -  Index Only Scan using a2_id_idx on a2  (cost=0.14..15.64 rows=200
width=4) (actual time=0.012..0.052 rows=200 loops=1)
 Index Cond: (id IS NOT NULL)
 Heap Fetches: 200
 Total runtime: 0.335 ms

With the partial index predicate explicitly specified the row estimate is
accurate, without the predicate the row estimate of the index scan on
a2_id_idx assumes 1.

It's very likely i miss something really important here, could someone shed
some light on this?

-- 
Thanks

Bernd


-- 
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] [COMMITTERS] pgsql: Move pg_upgrade from contrib/ to src/bin/

[Bernd Helmle]

--On 15. April 2015 15:02:05 -0400 Andrew Dunstan and...@dunslane.net
wrote:

 We've handled the buildfarm being red for a few days before. People are
 usually good about applying fixes fairly quickly.

Took me some time to get that due to my mail backlog, but i've done the
hotfix for dotterel and forced a run for all branches on this box.

-- 
Thanks

Bernd


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

[HACKERS] Disabling trust/ident authentication configure option

[Bernd Helmle]

We have a customer using a patch to harden their PostgreSQL installation
(see attached) they would like to contribute. This patch adds the ability
to disable trust and ident authentication at compile time via configure
options, thus making it impossible to use these authentication methods for
sloppy configuration purposes. This is critical to their software
deployment, as stated in their use case description:

snip
PostgreSQL is deployed as part of a larger technical solution (e.g. a
Telecommunication system) and a field engineer has to install/upgrade this
solution. The engineer is a specialist in the Telco domain and has only
little knowledge of databases and especially PostgreSQL setup.

We now want to provide these kinds of users with pre-hardened packages that
make it very hard to accidentally expose their database. For this purpose
the patch allows to optionally disable the trust and ident
authentication methods. Especially the trust mechanism is very critical
as it might actually provide useful functionality for our user. Think of an
engineer who has to do a night shift upgrade with a customer breathing down
his neck to get the system back online. Circumventing all these
authentication configuration issues by just enabling trust is very easy
and looks well supported and documented. (the documentation states the
dangers but there are no *big red flags* or something). After finishing the
upgrade the engineer forgets to restore the secure configuration, and a
malicious attacker later uses this access method to gain access to the
database.
/snip

Currently the patch adds new configure options --without-trust-auth and
--without-ident-auth and makes peer authentication default when these
options are set. My testing shows that regression tests (which are normally
using trust) are still working. This works as far as it goes to Linux and
friends, Windows currently is not adressed yet. Maybe its worth to consider
making sspi the default on this platform instead.

There was a discussion some time ago ([1]), but i think the reasoning
behind this patch is a little bit different than discussed there. 

[1]
http://www.postgresql.org/message-id/CAN2Y=umt7cpkxzhaufw7szeckdwcwsuulmh4xphuxkqbtdu...@mail.gmail.com

-- 
Thanks

Bernd

disable_trust_ident_configure.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] Re: [COMMITTERS] pgsql: Disable -faggressive-loop-optimizations in gcc 4.8+ for pre-9.2

[Bernd Helmle]

--On 20. Januar 2015 17:15:01 +0100 Andres Freund and...@2ndquadrant.com 
wrote:



I personally think that being able to at least compile/make check old
versions a bit longer is a good idea.


+1 from me for this idea.

--
Thanks

Bernd


--
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] [GENERAL] ON_ERROR_ROLLBACK

[Bernd Helmle]

--On 29. Dezember 2014 12:55:11 -0500 Tom Lane t...@sss.pgh.pa.us wrote:


Given the lack of previous complaints, this probably isn't backpatching
material, but it sure seems like a bit of attention to consistency
would be warranted here.


Now that i read it i remember a client complaining about this some time 
ago. I forgot about it, but i think there's value in it to backpatch.


--
Thanks

Bernd


--
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] Amazon Redshift

[Bernd Helmle]

--On 5. November 2014 23:36:03 +0100 philip taylor philta...@mail.com 
wrote:



Date Functions

http://docs.aws.amazon.com/redshift/latest/dg/r_ADD_MONTHS.html
http://docs.aws.amazon.com/redshift/latest/dg/r_DATEADD_function.html
http://docs.aws.amazon.com/redshift/latest/dg/r_DATEDIFF_function.html
http://docs.aws.amazon.com/redshift/latest/dg/r_LAST_DAY.html
http://docs.aws.amazon.com/redshift/latest/dg/r_MONTHS_BETWEEN_function.h
tml http://docs.aws.amazon.com/redshift/latest/dg/r_NEXT_DAY.html


fyi, except DATE_DIFF() all of these functions can be installed by using 
the orafce extension i believe.


--
Thanks

Bernd


--
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] COPY TO returning empty result with parallel ALTER TABLE

[Bernd Helmle]

--On 3. November 2014 18:15:04 +0100 Sven Wegener 
sven.wege...@stealer.net wrote:



I've check git master and 9.x and all show the same behaviour. I came up
with the patch below, which is against curent git master. The patch
modifies the COPY TO code to create a new snapshot, after acquiring the
necessary locks on the source tables, so that it sees any modification
commited by other backends.


Well, i have the feeling that there's nothing wrong with it. The ALTER 
TABLE command has rewritten all tuples with its own XID, thus the current 
snapshot does not see these tuples anymore. I suppose that in 
SERIALIZABLE or REPEATABLE READ transaction isolation your proposal still 
doesn't return the tuples you'd like to see.


--
Thanks

Bernd


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

Re: [GENERAL] Re: [BUGS] [HACKERS] COPY TO returning empty result with parallel ALTER TABLE

[Bernd Helmle]

--On 4. November 2014 17:18:14 -0500 Tom Lane t...@sss.pgh.pa.us wrote:


Yeah, and I think that it's entirely reasonable for rewriting ALTER TABLEs
to update the xmin of the rewritten tuples; after all, the output data
could be arbitrarily different from what the previous transactions put
into the table.  But that is not the question here.  If the COPY blocks
until the ALTER completes --- as it must --- why is its execution snapshot
not taken *after* the lock is acquired?


COPY waits in DoCopy() but its snapshot gets acquired in PortalRunUtility() 
earlier. SELECT has it's lock already during transform/analyse phase and 
its snapshot is taken much later.  Looks like we need something that 
analyses a utility statement to get its lock before executing.


--
Thanks

Bernd


--
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] Better support of exported snapshots with pg_dump

[Bernd Helmle]

--On 1. September 2014 17:00:32 +0900 Michael Paquier 
michael.paqu...@gmail.com wrote:



Currently pg_dump does not allow a user to specify an exported snapshot
name that he would like to use for a dump using SET TRANSACTION SNAPSHOT
(now pg_export_snapshot is only used for parallel pg_dump within it). I
imagine that this would be handy to take a consistent dump of a given
database after creating a logical replication slot on it. Thoughts?


There was a discussion of this kind of feature some time ago here:

http://www.postgresql.org/message-id/ca+u5nmk9+ttcff_-4mfdxwhnastauhuq7u7uedd57vay28a...@mail.gmail.com

Not sure if all the arguments holds still true with the appearance of MVCC 
catalog scans.


--
Thanks

Bernd


--
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] Hardening pg_upgrade

[Bernd Helmle]

--On 21. August 2014 22:08:58 +0200 Magnus Hagander mag...@hagander.net 
wrote:



I vote for discarding 8.3 support in pg_upgrade.  There are already
enough limitations on pg_upgrade from pre-8.4 to make it of questionable
value; if it's going to create problems like this, it's time to cut the
rope.


+1. 8.3 has been unsupported for a fairly long time now, and you can
still do a two-step upgrade if you're on that old a version.


Also +1 from my side. I've seen some old 8.3 installations at customers, 
still, but they aren't large and can easily be upgraded with a two step 
upgrade.


--
Thanks

Bernd


--
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] SSL information view

[Bernd Helmle]

--On 12. Juli 2014 15:08:01 +0200 Magnus Hagander mag...@hagander.net 
wrote:



Before doing that, however, I'd like to ask for opinions :) The hack
currently exposes a separate view that you can join to
pg_stat_activity (or pg_stat_replication) on the pid -- this is sort
of the same way that pg_stat_replication works in the first place. Do
we want something similar to that for a builtin SSL view as well, or
do we want to include the fields directly in pg_stat_activity and
pg_stat_replication?


I've heard more than once the wish to get this information without 
contrib..especially for the SSL version used (client and server likewise). 
So ++1 for this feature.


I'd vote for a special view, that will keep the information into a single 
place and someone can easily join extra information together.


--
Thanks

Bernd


--
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] Clarification of FDW API Documentation

[Bernd Helmle]

--On 13. Juni 2014 13:46:38 -0400 Tom Lane t...@sss.pgh.pa.us wrote:


Imagine if `BeginForeignScan` set up a remote cursor and
`IterateForeignScan` just fetched _one tuple at a time_ (unlike the
current behavior where they are fetched in batches). The tuple would be
passed to `ExecForeignDelete` (as is required), but the remote cursor
would remain pointing at that tuple. Couldn't `ExecForeignDelete` just
call `DELETE FROM table WHERE CURRENT OF cursor` to then delete that
tuple?


No.  This is not guaranteed (or even likely) to work in join cases: the
tuple to be updated/deleted might no longer be the current one of the
scan. You *must* arrange for the scan to return enough information to
uniquely identify the tuple later, and that generally means adding some
resjunk columns.


Yeah, this is exactly the trap i ran into while implementing the 
informix_fdw driver. It used an updatable cursor to implement the modify 
actions as you proposed first. Consider a query like


UPDATE remote SET f1 = t.id FROM local t WHERE t.id = f1

The planner might choose a hash join where the hash table is built by 
forwarding the cursor via the foreign scan. You'll end up with the cursor 
positioned at the end and you have no way to get it back in sync when the 
modify action is actually called.


--
Thanks

Bernd


--
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] tab completion for setting search_path

[Bernd Helmle]

--On 3. Mai 2014 10:11:33 +0200 Andres Freund and...@2ndquadrant.com 
wrote:



diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
new file mode 100644
index 6d26ffc..dec3d4a
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
*** psql_completion(const char *text, int st
*** 3230,3235 
--- 3230,3242 

COMPLETE_WITH_LIST(my_list);
}
+   else if (pg_strcasecmp(prev2_wd, search_path) == 0)
+   {
+   COMPLETE_WITH_QUERY(Query_for_list_of_schemas
+AND nspname not 
like 'pg\\_%%' 
+AND nspname not 
like 'information_schema' 
+UNION SELECT 
'DEFAULT' );
+   }


Why should we exclude system schemata? That seems more likely to be
confusing than helpful? I can see a point in excluding another backend's
temp tables, but otherwise?


I put my hands on this a while ago, too, but had a different notion in 
mind, which schema the completion should select. I came up with the 
following:


http://git.postgresql.org/gitweb/?p=users/bernd/postgres.git;a=commitdiff;h=03fd00cd190e8b529efeec1a1bb038454fb0b05f

Just complete to a schema someone has CREATE or USAGE privs. However, the 
reason i stopped working on it was that i really want to have a completion 
to a list of schemas as well and i couldn't figure a good and easy way to 
do this atm.


--
Thanks

Bernd


--
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] Sending out a request for more buildfarm animals?

[Bernd Helmle]

It's a POWER 7 machine. 

On 3. Mai 2014 10:31:34 MESZ, Dave Page dave.p...@enterprisedb.com wrote:
Hamid@EDB; Can you please have someone configure anole to build git
head as well as the other branches? Thanks.

Andres, Andrew; I think the only other gap EDB could fill at the
moment is RHEL6 on Power7 (though we do have a couple of Power8 boxes
on order that should be here pretty soon). Dotterel is building some
branches (including head). I'm not sure what generation of Power CPU
that box has. Bernd?

On Fri, May 2, 2014 at 4:04 PM, Andres Freund and...@2ndquadrant.com
wrote:
 Hi,

 There's pretty little coverage of non mainstream platforms/compilers
in
 the buildfarm atm. Maybe we should send an email on -announce asking
for
 new ones?
 There's no coverage for OS-wise;
 * AIX (at all)
 * HP-UX (for master at least)
 (* Tru64)
 (* UnixWare)

 Architecture wise there's no coverage for:
 * some ARM architecture varians
 * mips
 * s390/x
 * sparc 32bit
 (* s390)
 (* alpha)
 (* mipsel)
 (* M68K)

 A couple of those aren't that important (my opinion indicated by ()),
 but the other ones really should be covered or desupported.

 Greetings,

 Andres Freund

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



-- 
Dave Page
Chief Architect, Tools  Installers
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

-- 
Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.

Re: [HACKERS] Should pg_stat_bgwriter.buffers_backend_fsync be removed?

[Bernd Helmle]

--On 26. April 2014 19:42:47 -0700 Peter Geoghegan p...@heroku.com wrote:


I suggest removing it for 9.5, and instead logging individual
occurrences of backend fsync requests within ForwardFsyncRequest(). It
seems fair to treat that as an anomaly to draw particular attention
to.


But wouldn't that make it more complicated/unlikely to discover cases, 
where it still doesn't work?


--
Thanks

Bernd


--
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] Including replication slot data in base backups

[Bernd Helmle]

--On 1. April 2014 11:26:08 -0400 Robert Haas robertmh...@gmail.com wrote:



As a general comment, I think that replication slots, while a great
feature, have more than the usual potential for self-inflicted injury.
 A replication slot prevents the global xmin from advancing (so your
tables will bloat) and WAL from being removed (so your pg_xlog
directory will fill up and take down the server).  The very last thing
you want to do is to keep around a replication slot that should have
been dropped, and I suspect a decent number of users are going to make
that mistake, just as they do with prepared transactions and backends
left idle in transaction.


Oh yes, i saw this happening uncountless times now by customers when 
restoring a basebackup with in-progress prepared xacts (and was indeed 
fooled myself a few times, too). I always was under the impression that 
there should be a big big warning at least in the logs to hint the user to 
check any remaining prepared xacts...


--
Thanks

Bernd


--
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] Selecting large tables gets killed

[Bernd Helmle]

--On 20. Februar 2014 14:49:28 +0530 Ashutosh Bapat 
ashutosh.ba...@enterprisedb.com wrote:



If I set some positive value for this variable, psql runs smoothly with
any size of data. But unset that variable, and it gets killed. But it's
nowhere written explicitly that psql can run out of memory while
collecting the result set. Either the documentation or the behaviour
should be modified.


Maybe somewhere in the future we should consider single row mode for psql, 
see


http://www.postgresql.org/docs/9.3/static/libpq-single-row-mode.html

However, i think nobody has tackled this yet, afair.

--
Thanks

Bernd


--
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] Selecting large tables gets killed

[Bernd Helmle]

--On 20. Februar 2014 09:51:47 -0500 Tom Lane t...@sss.pgh.pa.us wrote:


Yeah.  The other reason that you can't just transparently change the
behavior is error handling: people are used to seeing either all or
none of the output of a query.  In single-row mode that guarantee
fails, since some rows might get output before the server detects
an error.


That's true. I'd never envisioned to this transparently either, exactly of 
this reason. However, i find to have single row mode somewhere has some 
attractiveness, be it only to have some code around that shows how to do it 
right. But i fear we might complicate things in psql beyond what we really 
want.


--
Thanks

Bernd


--
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: [COMMITTERS] pgsql: Add a GUC to report whether data page checksums are enabled.

[Bernd Helmle]

--On 18. Februar 2014 22:23:59 +0200 Heikki Linnakangas hlinn...@iki.fi 
wrote:



I considered it a new feature, so not back-patching was the default. If
you want to back-patch it, I won't object.


That was my original feeling, too, but +1 for backpatching.

--
Thanks

Bernd


--
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] Fixing pg_basebackup with tablespaces found in $PGDATA

[Bernd Helmle]

--On 1. Januar 2014 23:53:46 +0100 Dimitri Fontaine 
dimi...@2ndquadrant.fr wrote:



Hi,

As much as I've seen people frown upon $subject, it still happens in the
wild, and Magnus seems to agree that the current failure mode of our
pg_basebackup tool when confronted to the situation is a bug.

So here's a fix, attached.


I've seen having tablespaces under PGDATA as a policy within several data 
centres in the past. The main reasoning behind this seems that they 
strictly separate platform and database administration and for database 
inventory reasons. They are regularly surprised if you tell them to not use 
tablespaces in such a way, since they absorbed this practice over the years 
from other database systems. So +1 for fixing this.


--
Thanks

Bernd


--
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] Wait free LW_SHARED acquisition

[Bernd Helmle]

--On 27. September 2013 09:57:07 +0200 Andres Freund 
and...@2ndquadrant.com wrote:



Ok, was free:

padding + 16 partitions:
tps = 147884.648416

padding + 32 partitions:
tps = 141777.841125

padding + 64 partitions:
tps = 141561.539790

padding + 16 partitions + new lwlocks
tps = 601895.580903 (yeha, still reproduces after some sleep!)


Hmm, out of interest and since i have access to a (atm) free DL580 G7 (4x 
E7-4800 10core) i've tried your bench against this machine and got this 
(best of three):


HEAD (default):

tps = 181738.607247 (including connections establishing)
tps = 182665.993063 (excluding connections establishing)

HEAD (padding + 16 partitions + your lwlocks patch applied):

tps = 269328.259833 (including connections establishing)
tps = 270685.666091 (excluding connections establishing)

So, still an improvement but far away from what you got. Do you have some 
other tweaks in your setup?


--
Thanks

Bernd


--
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] Wait free LW_SHARED acquisition

[Bernd Helmle]

--On 30. September 2013 19:00:06 +0200 Andres Freund 
and...@2ndquadrant.com wrote:



HEAD (default):

tps = 181738.607247 (including connections establishing)
tps = 182665.993063 (excluding connections establishing)

HEAD (padding + 16 partitions + your lwlocks patch applied):

tps = 269328.259833 (including connections establishing)
tps = 270685.666091 (excluding connections establishing)

So, still an improvement but far away from what you got. Do you have some
other tweaks in your setup?


The only relevant setting changed was -c shared_buffers=1GB, no other
patches applied. At which scale did you pgbench -i?


I've used a scale factor of 10 (i recall you've mentioned using the same 
upthread...).


Okay, i've used 2GB shared buffers, repeating with your setting i get a far 
more noticable speedup:


tps = 346292.008580 (including connections establishing)
tps = 347997.073595 (excluding connections establishing)

Here's the perf output:

+   4.34%  207112  postgres  postgres [.] 
AllocSetAlloc

+   4.07%  194476  postgres  libc-2.13.so [.] 0x127b33
+   2.59%  123471  postgres  postgres [.] 
SearchCatCache

+   2.49%  118974   pgbench  libc-2.13.so [.] 0x11aaef
+   2.48%  118263  postgres  postgres [.] 
GetSnapshotData
+   2.46%  117646  postgres  postgres [.] 
base_yyparse
+   2.02%   96546  postgres  postgres [.] 
MemoryContextAllocZeroAligned
+   1.58%   75326  postgres  postgres [.] 
AllocSetFreeIndex
+   1.23%   58587  postgres  postgres [.] 
hash_search_with_hash_value

+   1.01%   48391  postgres  postgres [.] palloc
+   0.93%   44258  postgres  postgres [.] 
LWLockAttemptLock

+   0.91%   43575   pgbench  libc-2.13.so [.] free
+   0.89%   42484  postgres  postgres [.] 
nocachegetattr

+   0.89%   42378  postgres  postgres [.] core_yylex
+   0.88%   42001  postgres  postgres [.] 
_bt_compare
+   0.84%   39997  postgres  postgres [.] 
expression_tree_walker
+   0.76%   36533  postgres  postgres [.] 
ScanKeywordLookup

+   0.74%   35515   pgbench  libc-2.13.so [.] malloc
+   0.64%   30715  postgres  postgres [.] 
LWLockRelease
+   0.56%   26779  postgres  postgres [.] 
fmgr_isbuiltin

+   0.54%   25681   pgbench  [kernel.kallsyms][k] _spin_lock
+   0.48%   22836  postgres  postgres [.] new_list
+   0.48%   22700  postgres  postgres [.] hash_any
+   0.47%   22378  postgres  postgres [.] 
FunctionCall2Coll

+   0.46%   22095  postgres  postgres [.] pfree
+   0.44%   20929  postgres  postgres [.] palloc0
+   0.43%   20592  postgres  postgres [.] 
AllocSetFree

+   0.40%   19495  postgres  [unknown][.] 0x81cf2f
+   0.40%   19247  postgres  postgres [.] 
hash_uint32

+   0.38%   18227  postgres  postgres [.] PinBuffer
+   0.38%   18022   pgbench  [kernel.kallsyms][k] do_select

--
Thanks

Bernd


--
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] ENABLE/DISABLE CONSTRAINT NAME

[Bernd Helmle]

--On 13. September 2013 20:17:19 -0400 Robert Haas robertmh...@gmail.com 
wrote:



You're missing the point.  Peter wasn't worried that your patch throws
an error; he's concerned about the fact that it doesn't.

In PostgreSQL, you can only create the following view because test1
has a primary key over column a:

= create table test1 (a int constraint pk primary key, b text);
= create view test2 as select a, b from test1 group by a;
= alter table test1 drop constraint pk;

The reason that, if the primary key weren't there, it would be
ambiguous which row should be returned as among multiple values where
a is equal and b is not.  If you can disable the constraint, then you
can create precisely that problem.


Hmm not sure i understand this argument either: this patch doesn't allow 
disabling a primary key. It only supports FKs and CHECK constraints 
explicitly.


--
Thanks

Bernd


--
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] psql should show disabled internal triggers

[Bernd Helmle]

--On 18. September 2013 13:52:29 +0200 Andres Freund 
and...@2ndquadrant.com wrote:



If you do ALTER TABLE ... DISABLE TRIGGER ALL; and then individually
re-enable the disabled triggers it's easy to miss internal triggers.
A \d+ tablename will not show anything out of the ordinary for that
situation since we don't show internal triggers. But foreign key checks
won't work.
So, how about displaying disabled internal triggers in psql?


Hi had exactly the same concerns this morning while starting to look at the 
ENABLE/DISABLE constraint patch. However, i wouldn't display them as 
triggers, but maybe more generally as disabled constraints or such.


--
Thanks

Bernd


--
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] psql should show disabled internal triggers

[Bernd Helmle]

--On 18. September 2013 15:19:27 +0200 Andres Freund 
and...@2ndquadrant.com wrote:



Well, that will lead the user in the wrong direction, won't it? They
haven't disabled the constraint but the trigger. Especially as we
already have NOT VALID and might grow DISABLED for constraint
themselves...



Valid point. But it is also nice to know in detail, which constraints 
stopped working. Ok, it is documented which constraints are affected and 
maybe i'm lost within too much detail atm, but i find people getting 
confused about this internal trigger thingie sometimes. Won't they get 
confused about a suddenly appearing RI_ConstraintTrigger_a_54015, too?


--
Thanks

Bernd


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

[HACKERS] GUC for data checksums

[Bernd Helmle]

Attached is a small patch to add a new GUC to report wether data checksums 
for a particular cluster are enabled. The only way to get this info afaik 
is to look into pg_control and the version number used, but i'd welcome a 
way to access this remotely, too. If there aren't any objections i'll add 
this to the CF.


--
Thanks

Bernd

data_checksums_guc.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] GUC for data checksums

[Bernd Helmle]

--On 15. September 2013 00:25:34 +0200 Andres Freund 
and...@2ndquadrant.com wrote:



Looks like a good idea to me. The implementation looks sane as well,
except that I am not sure if we really need to introduce that faux
variable. If the variable cannot be set and we have a SHOW hook, do we
need it?


It's along the line with the other informational variables like block_size 
et al. Do you want to have a function instead or what's your intention?


One benefit is to have 'em all in SHOW ALL which can be used to compare 
database/cluster settings, to mention one use case i have in mind.


--
Thanks

Bernd


--
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] psql tab completion for updatable foreign tables

[Bernd Helmle]

--On 8. Juli 2013 16:04:31 + Dean Rasheed dean.a.rash...@gmail.com 
wrote:



* pg_relation_is_updatable is only available in 9.3, whereas psql may
connect to older servers, so it needs to guard against that.



Oh of course, i forgot about this. Thanks for pointing out.


* If we're doing this, I think we should do it for auto-updatable
views at the same time.

There was concern that pg_relation_is_updatable() would end up opening
every relation in the database, hammering performance. I now realise
that these tab-complete queries have a limit (1000 by default) so I
don't think this is such an issue. I tested it by creating 10,000
randomly named auto-updatable views on top of a table, and didn't see
any performance problems.

Here's an updated patch based on the above points.


Okay, are you adding this to the september commitfest?

--
Thanks

Bernd


--
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 tab completion for updatable foreign tables

[Bernd Helmle]

Recently i got annoyed that psql doesn't tab complete to updatable foreign 
tables.


Attached is a patch to address this. I'm using the new 
pg_relation_is_updatable() function to accomplish this. The function could 
also be used for the trigger based stuff in the query, but i haven't 
touched this part of the query. The patch ist against HEAD, but maybe it's 
worth to apply this to REL9_3_STABLE, too, but not sure what our policy is 
at this state...


--
Thanks

Bernd

psql_tab_complete_updatable_fdw.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] Passing fdw_private data from PlanForeignScan to PlanForeignModify

[Bernd Helmle]

--On 13. Juni 2013 18:12:05 -0400 Tom Lane t...@sss.pgh.pa.us wrote:


What i tried before was to access (in PlanForeignModify) the RelOptInfo
structure through PlannerInfo-simple_rel_array, assuming the the
resultRelation index points to the right array member. However, that
didn't  work, the fdw_private List is not the one filled by
GetForeignPlan...is  there another way to get back the RelOptInfo worked
on earlier?


It should work ... *if* there was in fact a RelOptInfo worked on
earlier.  There sometimes isn't.  You might need to do something like
what make_modifytable() has to do to call you in the first place:


Sorry for the late feedback, didn't manage to get back to this earlier.

This works indeed, the RelOptInfo structure stored in simple_rel_array (if 
present) allows
a FDW to access its earlier scan state, assigned in GetForeignPlan() for 
example.


Thanks for the hints!

--

Bernd



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

[HACKERS] Passing fdw_private data from PlanForeignScan to PlanForeignModify

[Bernd Helmle]

I'm currently implementing DML actions in the informix FDW module and 
trying to understand how UPDATE/DELETE actions interact with the various 
structures, especially how states between the associated ForeignScan and 
ForeignModify actions could be transmitted. As far as i understood, with 
e.g. an UPDATE action on a foreign table you get the following sequence of 
callbacks:


GetForeignRelSize
GetForeignPaths
GetForeignPlan

PlanForeignModify

BeginForeignScan

BeginForeignModify

repeats
IterateForeignScan

ExecForeignUpdate

/repeats

EndForeignScan

EndForeignModify

During the planning phase, the Informix FDW currently plans a server side 
cursor, which identifier i'd like to pass into the planning phase of the 
modify action to reuse it in conjunction with UPDATE ... WHERE CURRENT OF.


I understand that the ModifyTable structure passed to PlanForeignModify has 
a list of associated plan nodes, from which i can access the ForeignScan 
plan node associated with the current modify action, thus having access to 
the fdw_private data generated during the planning phase of the ForeigScan 
node. However, it's a list and i currently don't understand on how to 
reference the associated ForeignScan node reliably, given that there are 
cases with more than one node in this list.


Any pointers, someone?

--
Thanks

Bernd


--
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] Passing fdw_private data from PlanForeignScan to PlanForeignModify

[Bernd Helmle]

--On 13. Juni 2013 11:06:31 -0400 Tom Lane t...@sss.pgh.pa.us wrote:


My
recommendation would be to see if you can't save the relevant info in
the RelOptInfo node for the relation, probably during GetForeignPlan,
and then get it from there in PlanForeignModify instead of digging in
the plan tree.  (You can use the fdw_private field of RelOptInfo for
whatever you need in this line.)


Hmm, I tried this already, but maybe i'm doing something entirely wrong 
here.


What i tried before was to access (in PlanForeignModify) the RelOptInfo 
structure through PlannerInfo-simple_rel_array, assuming the the 
resultRelation index points to the right array member. However, that didn't 
work, the fdw_private List is not the one filled by GetForeignPlan...is 
there another way to get back the RelOptInfo worked on earlier?


--
Thanks

Bernd


--
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] Hard limit on WAL space used (because PANIC sucks)

[Bernd Helmle]

--On 6. Juni 2013 16:25:29 -0700 Josh Berkus j...@agliodbs.com wrote:


Archiving
-

In some ways, this is the simplest case.  Really, we just need a way to
know when the available WAL space has become 90% full, and abort
archiving at that stage.  Once we stop attempting to archive, we can
clean up the unneeded log segments.

What we need is a better way for the DBA to find out that archiving is
falling behind when it first starts to fall behind.  Tailing the log and
examining the rather cryptic error messages we give out isn't very
effective.


Slightly OT, but i always wondered wether we could create a function, say

pg_last_xlog_removed()

for example, returning a value suitable to be used to calculate the 
distance to the current position. An increasing value could be used to 
instruct monitoring to throw a warning if a certain threshold is exceeded.


I've also seen people creating monitoring scripts by looking into 
archive_status and do simple counts on the .ready files and give a warning, 
if that exceeds an expected maximum value.


I haven't looked at the code very deep, but i think we already store the 
position of the last removed xlog in shared memory already, maybe this can 
be used somehow. Afaik, we do cleanup only during checkpoints, so this all 
has too much delay...


--
Thanks

Bernd


--
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] background worker and normal exit

[Bernd Helmle]

--On 26. Mai 2013 11:38:55 +0900 Michael Paquier 
michael.paqu...@gmail.com wrote:




This flag makes a worker not to restart only in case of a crash. To solve
your problem, you could as well allow your process to restart and put it
in indefinite sleep if server is not in recovery such it it will do
nothing in your case.


Hmm so you can't have workers just doing something once and exit? I have 
to admit, i didn't follow bgworkers closely in the past, but could you give 
a short insight on why this is currently not possible?


--
Thanks

Bernd


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

[Bernd Helmle]

--On 25. April 2013 23:19:14 -0400 Tom Lane t...@sss.pgh.pa.us wrote:


I think I've heard of scripts grepping the output of pg_controldata for
this that or the other.  Any rewording of the labels would break that.
While I'm not opposed to improving the labels, I would vote against your
second, abbreviated scheme because it would make things ambiguous for
simple grep-based scripts.


I had exactly this kind of discussion just a few days ago with a customer, 
who wants to use the output in their scripts and was a little worried about 
the compatibility between major versions.


I don't think we do guarantuee any output format compatibility between 
corresponding symbols in major versions explicitly, but given that 
pg_controldata seems to have a broad use case here, we should maybe 
document it somewhere wether to discourage or encourage people to rely on 
it?


--
Thanks

Bernd


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

[HACKERS] pg_dump in current master segfaults when dumping 9.2/9.1 databases

[Bernd Helmle]

My current master segfaults with pg_dump when dumping a 9.1 or 9.2 database:

$ LC_ALL=en_US.utf8 pg_dump -s -p 5448
pg_dump: column number -1 is out of range 0..22
zsh: segmentation fault  LC_ALL=en_US.utf8 pg_dump -s -p 5448

The reason seems to be that getTables() in pg_dump.c forget to select 
relpages in the query for releases = 90100. The error message comes from 
PQgetvalue(res, i, i_relpages), which complains about i_relpages being -1, 
which will then return NULL...


--
Thanks

Bernd


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

[HACKERS] Materialized View patch broke pg_dump

[Bernd Helmle]

It looks like the recent matview patch broke pg_dump in a way, which make 
it impossible to dump 9.1 and 9.2 databases.


it fails with

pg_dump: [Archivierer (DB)] query failed: ERROR:  function 
pg_relation_is_scannable(oid) does not exist


Looking into this issue, it seems the version check in getTables() of 
pg_dump.c is wrong. Shouldn't the check be


if (fout-remoteVersion = 90300)
{

}

since this is where pg_relation_is_scannable() is introduced?

--
Thanks

Bernd


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

[HACKERS] PGXS contrib builds broken?

[Bernd Helmle]

Looks like the recent refactoring of code into common/ stopped
PGXS builds within the PostgreSQL source tree from working, i get

/Users/bernd/pgsql-dev/install/HEAD/include/server/postgres_fe.h:27:32: 
fatal error: common/fe_memutils.h: No such file or directory


when trying to build pg_upgrade, pgbench, pg_xlogdump, ... with PGXS.

I don't see common/fe_memutils getting installed in include/, so i suppose 
either some bug between chair and keyboard or some inconsistencies here.


--
Thanks

Bernd


--
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] Hanging backends and possible index corruption

[Bernd Helmle]

--On 25. Januar 2013 20:37:32 -0500 Tom Lane t...@sss.pgh.pa.us wrote:



Don't know how careful pgbtreecheck is.  The pg_filedump output isn't
very helpful because you filtered away the flags, so we can't tell if
any of these pages are deleted.  (If they are, the duplicate-looking
links might not be errors, since we intentionally don't reset a deleted
page's left/right links when deleting it.)



Ah, wasn't aware of this.


Could we see the whole special-space dump for each of the pages you're
worried about?



Attached


One thought that occurs to me is that POWER is a weak-memory-ordering
architecture, so that it's a tenable idea that this has something to do
with changing page links while not holding sufficient lock on the page.
I don't see btree doing that anywhere, but ...

BTW, how long has this installation been around, and when did you start
seeing funny behavior?  Can you say with reasonable confidence that the
bug was *not* present in any older PG versions?



This machine started in production around august last year with (afair) 
9.1.5. There also were performance and stress tests on this machine before 
it went into production, with no noticable problems.


However, what i missed before is that there were some trouble with the 
storage multipaths. Seems early january the machine lost some of it's paths 
to the SAN, but they were recovered a few seconds later...so i cannot 
exclude this as the cause anymore, though the paths are redundant. What 
strikes me is that the index was recreated in the meantime after this 
issue...


We will watch this machine the next couple of weeks more closely, if the 
issue comes back again.


--
Thanks

Bernd

dump.txt.bz2
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

[HACKERS] Hanging backends and possible index corruption

[Bernd Helmle]

We are currently analyzing an issue at one of our customers PostgreSQL 
database.


The current version is 9.1.6 (update to 9.1.7 is scheduled for next monday, 
no downtime possible before). It runs on POWER7 (pSeries 740) on an RHEL6.3 
64-bit LPAR. The packages are built from PGDG SVN sources, no special 
tweaks added. We saw no hardware related errors on this machine, nor any 
crashes.


What currently happens on this machine are hanging statements (SELECTs and 
INSERTs occasionally) with 100% CPU. After some investigation it turned out 
that the corresponding backends are seeking within an index file over and 
over again in a loop. Looking into the hanging queries i've recognized 
certain keys which seems to have the problem, other keys used in the WHERE 
condition run smoothly. Turning off index and bitmap index scans caused the 
suspicious keys to return results, too.


So i've saved the index file (normal BTree index with a single bigint 
column), did a REINDEX and the problem was gone. Looking at the index file 
with pg_filedump and pgbtreecheck from Alvaro gave me the following output:


pgbtreecheck gives warnings about pages' parents and then loops visiting 
the same pages over and over again:


NOTICE: fast root: block 290 at level 2
NOTICE: setting page parents
WARNING: block 12709 already had a parent (8840); new parent is 12177
WARNING: block 12710 already had a parent (12439); new parent is 10835
NOTICE: done setting parents
NOTICE: Checking forward scan of level 0, starting at block 1

-- loop starts

WARNING: right sibling 12710 does not point left to me (11680); points to 
10924 instead


Looking into the relevant pages and their prev and next pointers give me 
the following:


pg_filedump -i -R 11680 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (12651)  Next (12710)  Level (0)  CycleId (0)

pg_filedump -i -R 10924 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (10923)  Next (12710)  Level (0)  CycleId (0)

pg_filedump -i -R 12710 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (10924)  Next (10925)  Level (0)  CycleId (0)

pg_filedump -i -R 10925 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (12710)  Next (10926)  Level (0)  CycleId (0)

pg_filedump -i -R 12709 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (8849)  Next (8850)  Level (0)  CycleId (0)

pg_filedump -i -R 8840 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (8555)  Next (9125)  Level (1)  CycleId (0)

pg_filedump -i -R 12439 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (11405)  Next (11690)  Level (1)  CycleId (0)

$ pg_filedump -i -R 12177 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (11690)  Next (0)  Level (1)  CycleId (0)

$ pg_filedump -i -R 10835 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (10550)  Next (11120)  Level (1)  CycleId (0)

This symptom happened three times in the last couple of weeks now. Looking 
at the numbers doesn't give me the impression that some flaky hardware 
could be involved. What else can we do to track down this problem, any 
suggestions?


--
Thanks

Bernd


--
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] Hanging backends and possible index corruption

[Bernd Helmle]

--On 25. Januar 2013 16:28:16 +0100 Andres Freund and...@2ndquadrant.com 
wrote:



Did you reindex after upgrading to 9.1.6? Did you ever have any crashes
or failovers before upgrading to 9.1.6?
I have seen pretty similar symptoms caused by Fix persistence marking
of shared buffers during WAL replay in 9.1.6.


Hmm it happened only on a single heavily used table for now and this table 
was REINDEXed twice after updating to 9.1.6 (every time the issue occured).


--
Thanks

Bernd


--
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: Store timestamptz of database creation on pg_database

[Bernd Helmle]

--On 2. Januar 2013 23:04:43 -0500 Robert Haas robertmh...@gmail.com 
wrote:



TBH, I don't think anyone has any business changing the creation
timestamp.  Ever.  For me, the fact that pg_dump wouldn't preserve
this information would be a feature, not a bug.  I mostly meant to
point out that someone could bypass it if they cared enough, not to
recommend it.  Honestly, I'd probably *rather* store this information
someplace where it couldn't be changed via SQL *at all*.  But I don't
think we have such a place, so I'm happy enough to store it in the
catalogs, with the associated risks of catalog hackery that entails.


This is exactly what Informix does, it stores creation or modification 
dates of a table in its system catalog (systables.created, to be specific). 
Any export/import of tables doesn't preserve the dates, if you restore a 
database (or table), the creation date is adjusted. I'm not aware of any 
SQL interface to influence this.


--
Thanks

Bernd


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

[HACKERS] Scheduled back-branch releases?

[Bernd Helmle]

Do we have a schedule for when next back-branch releases are packaged (i hope i
didn't miss any announcement...)?

-- 
Thanks

Bernd

-- 
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 pg_is_in_backup()

[Bernd Helmle]

--On 3. Februar 2012 13:21:11 +0900 Fujii Masao masao.fu...@gmail.com wrote:


It seems to be more user-friendly to introduce a view like pg_stat_backup
rather than the function returning an array.


I like this idea. A use case i saw for monitoring backup_label's in the past, 
was mainly to discover a forgotten exclusive pg_stop_backup() (e.g. due to 
broken backup scripts). If the view would be able to distinguish both, 
exclusive and non-exclusive backups, this would be great.


--
Thanks

Bernd

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

[Bernd Helmle]

--On 2. Februar 2012 17:12:11 +0900 Fujii Masao masao.fu...@gmail.com wrote:


If only core developer is interested in this view, ISTM that short
description for
each WAL record is not required because he or she can know the meaning of each
WAL record by reading the source code. No? Adding short descriptions for every
WAL records seems to be overkill.


Yes, for a developer option alone adding all these *_short_desc functions looks
too much code for too less benefit. However, if someone with less code affinity
is interested to debug his server during recovery, it might be easier for him 
to interpret the statistic counters.


Unfortunately i didn't manage to do it this week, but what i'm also interested 
in is how large the performance regression is if the track_recovery variable is 
activated. Not sure wether it really makes a big difference, but maybe 
debugging recovery from a large archive could slow down to a degree, where you 
want the GUC but can't afford it?


And, for display purposes, when this is intended for developers only, shouldn't 
it be treated like all the other debug options as a DEVELOPER_OPTION, too?


--
Thanks

Bernd

--
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 pg_is_in_backup()

[Bernd Helmle]

--On 2. Februar 2012 18:47:35 +0100 Gilles Darold gilles.dar...@dalibo.com 
wrote:



For now the internal function BackupInProgress() only return a boolean.
I like the idea that pg_is_in_backup() just works as pg_is_in_recovery()
do. I mean it doesn't return the timestamp of the recovery starting time
but only true or false.

Maybe we can have an other function that will return all information
available in the backup_label file ?


I've seen customers using pg_read_file() to do exactly this. E.g. they are 
searching for the START TIME value, if backup_label is present, to report the 
backup start.


--
Thanks

Bernd

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

[Bernd Helmle]

--On 15. Januar 2012 02:50:00 -0500 Jaime Casanova ja...@2ndquadrant.com 
wrote:



Attached is a patch thats implements a pg_stat_recovery view that
keeps counters about processed wal records. I just notice that it
still lacks documentation but i will add it during the week.


Hi Jaime,

do you have an updated patch? The current v1 patch doesn't apply cleanly 
anymore, and before i go and rebase the patch i thought i'm asking...


--
Thanks

Bernd

--
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] Measuring relation free space

[Bernd Helmle]

--On 6. November 2011 01:08:11 -0200 Greg Smith g...@2ndquadrant.com wrote:


Attached patch adds a new function to the pageinspect extension for measuring
total free space, in either tables or indexes.


I wonder if that should be done in the pgstattuple module, which output some 
similar numbers.


--
Thanks

Bernd

--
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] Update on documentation builds on OSX w/ macports

[Bernd Helmle]

--On 20. Oktober 2011 02:02:09 +0200 Florian Pflug f...@phlo.org wrote:


In the mean time, the modified ports are all contained in the
attached tar.bz2, should any of ye fellow OSX users want to try them
out before that.

Simply extract that archive, and add
  file://Absolute path to the extracted archive
to /opt/local/etc/macports/sources.conf. After that,
  port install openjade docbook-sgml-4.2
should give you a working docbook SGML environment.


Very cool! Will test it tomorrow...

--
Thanks

Bernd

--
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 to improve reliability of postgresql on linux nfs

[Bernd Helmle]

--On 9. September 2011 10:27:22 -0400 Tom Lane t...@sss.pgh.pa.us wrote:


On the whole I think you'd be better off lobbying your NFS implementors
to provide something closer to the behavior of every other filesystem on
the planet.  Or checking to see if you need to adjust your NFS
configuration, as the other responders mentioned.


You really need at least mount options 'hard' _and_ 'nointr' on NFS mounts, 
otherwise you are out of luck. Oracle and DB2 guys recommend those settings and 
without them any millisecond of network glitch could disturb things 
unreasonably.


--
Thanks

Bernd

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

[HACKERS] Informix FDW - anybody working on this?

[Bernd Helmle]

Out of curiosity,

is anybody working on $subject? I'm currently planning to work on such a driver,
but given the current stream of new drivers i want to make sure to not 
duplicate any efforts...


--
Thanks

Bernd

--
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] pgstat wait timeout warnings

[Bernd Helmle]

--On 10. August 2011 21:54:06 +0300 Heikki Linnakangas 
heikki.linnakan...@enterprisedb.com wrote:



So my theory is that if the I/O is really busy, write() on the stats file
blocks for more than 5 seconds, and you get the timeout.


I've seen it on customer instances with very high INSERT peak loads (several 
dozens backends INSERTing/UPDATEing data concurrently). We are using a RAM disk 
for stats_temp_directory now for a while, and the timeout never occured again.


--
Thanks

Bernd

--
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] Another issue with invalid XML values

[Bernd Helmle]

--On 20. Juli 2011 13:06:17 -0400 Tom Lane t...@sss.pgh.pa.us wrote:


I've committed this patch with the discussed changes and some other
editorialization.  I have to leave for an appointment and can't write
anything now about the changes, but feel free to ask questions if you
have any.


Hmm, when building against libxml2 2.7.8 i get reproducible failing regression 
tests on OSX 10.6.7. It is griping with


WARNING:  libxml error handling state is out of sync with xml.c

all over the place.

A quick check with compiling against the libxml2 shipped with OSX (which seems 
libxml2 2.7.3) causes everything to work as expected, however.


--
Thanks

Bernd

--
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] Another issue with invalid XML values

[Bernd Helmle]

--On 25. Juli 2011 19:07:50 +0200 Florian Pflug f...@phlo.org wrote:


Hm, I have libxml2 2.7.8, installed via Mac Ports, and I cannot reproduce
this. Maybe Mac Ports uses a modified libxml2, though. I'll check that.

Where did you obtain libxml2 from?


This is MacPorts, too:

% port installed libxml2
The following ports are currently installed:
 libxml2 @2.7.8_0 (active)

I've reduced my configure line to the least required options

./configure --with-libxml --with-includes=/opt/local/include 
--with-libraries=/opt/local/lib


but still get the WARNINGs in the regression.diffs. Which settings do you use?

--
Thanks

Bernd

--
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] Another issue with invalid XML values

[Bernd Helmle]

--On 25. Juli 2011 19:57:40 +0200 Florian Pflug f...@phlo.org wrote:


I got a theory. We do distinguish between libxml2 versions for which
the structured and the generic error context handler share the error
context (older ones), and those with don't (newer ones). Our configure
scripts checks for the availability of xmlStructuredErrorContext, and
defined HAVE_XMLSTRUCTUREDERRORCONTEXT if it is. Now, if for some reason
that test fails on your machine, even though libxml *does* provide
xmlStructuredErrorContext, then the safety-check in the error handler
would check whether xmlGenericErrorContext is set as expected, when
it really should check xmlStructuredErrorContext.

Could you check if configure defines that macro? You should find
it in the pg_config.h generated by configure.


This is what pg_config.h says:

% grep HAVE_XMLSTRUCTUREDERRORCONTEXT src/include/pg_config.h
/* #undef HAVE_XMLSTRUCTUREDERRORCONTEXT */

Ah, but i got now what's wrong here: configure is confusing both libxml2 
installations, and a quick look into config.log proves that: it uses the 
xml2-config from the OSX libs (my $PATH has /usr in front of the bindir of 
MacPorts, though i seem to recall to have changed this in the past).


So, all i need to do is

XML2_CONFIG=/opt/local/bin/xml2-config ./configure --with-libxml 
--with-includes=/opt/local/include/ --with-libraries=/opt/local/lib


and everything is smooth:

% grep HAVE_XMLSTRUCTUREDERRORCONTEXT src/include/pg_config.h#define 
HAVE_XMLSTRUCTUREDERRORCONTEXT 1


Regression tests passes now. This was too obvious...

--
Thanks

Bernd

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

Initial Review: JSON contrib modul was: Re: [HACKERS] Another swing at JSON

[Bernd Helmle]

--On 18. Juni 2011 12:29:38 +0200 Bernd Helmle maili...@oopsware.de wrote:


Similar problems occur with a couple other modules I tried (hstore,
intarray).


Hmm, works for me. Seems you have messed up your installation in some way
(build against current -HEAD but running against a 9.1?).

I'm going to review in the next couple of days again.


A bit later than expected, but here is my summary on the patch:

-- Patch Status

The patch applies cleanly. Since it's primarily targeted as an addition to 
contrib/, it doesn't touch the backend source tree (besides documentation TOC 
entries), but adds a new subdirectory in contrib/json. The patch is in context 
diff as requested.


-- Functionality

The patch as is provides a basic implementation for a JSON datatype. It 
includes normalization and validation of JSON strings. It adds the datatype 
implementation as an extension.


The implementation focus on the datatype functionality only, there is no 
additional support for special operators. Thus, i think the comments in the 
control file (and docs) promises actually more than the contrib module will 
deliver:


+comment = 'data type for storing and manipulating JSON content'

I'm not sure, if manipulating is a correct description. Maybe i missed it, 
but i didn't see functions to manipulate JSON strings directly, for example, 
adding an object to a JSON string, ...


-- Coding

The JSON datatype is implemented as a variable length character type, thus 
allows very large JSON strings to be stored. It transparently decides when to 
escape unicode code points depending on the selected client- and 
server-encoding.


Validation is done through its own JSON parser. The parser itself is a 
recursive descent parser. It is noticable that the parser seems to define its 
own is_space() and is_digit() functions, e.g.:


+#define is_space(c) ((c) == '\t' || (c) == '\n' || (c) == '\r' || (c) == ' ')

Wouldn't it be more clean to use isspace() instead?

This code block in function json_escape_unicode() looks suspicious:

+   /* Convert to UTF-8, if necessary. */
+   {
+   const char *orig = json;
+   json = (const char *)
+   pg_do_encoding_conversion((unsigned char *) json, length,
+ GetDatabaseEncoding(), PG_UTF8);
+   if (json != orig)
+   length = strlen(json);
+   }

Seems it *always* wants to convert the string. Isn't there a if condition 
missing?


There's a commented code fragment missing, which should be removed (see last 
two lines of the snippet below):


+static unsigned int
+read_hex16(const char *in)
+{
+   unsigned int i;
+   unsigned int tmp;
+   unsigned int ret = 0;
+
+   for (i = 0; i  4; i++)
+   {
+   char c = *in++;
+
+   Assert(is_hex_digit(c));
+
+   if (c = '0'  c = '9')
+   tmp = c - '0';
+   else if (c = 'A'  c = 'F')
+   tmp = c - 'A' + 10;
+   else /* if (c = 'a'  c = 'f') */
+   tmp = c - 'a' + 10;

json.c introduces new appendStringInfo* functions, e.g.
appendStringInfoEscape() and appendStringInfoUtf8(). Maybe it's better
to name them to something different, since it may occur someday that the backend
will introduce the same notion with a different meaning. They are static,
but why not naming them into something like jsonAppendStringInfoUtf8() or 
similar?


-- Regression Tests

The patch includes a fairly complete regression test suite, which covers much
of the formatting, validating and input functionality of the JSON datatype. It 
also tests UNICODE sequences and input encoding with other server encoding than 
UTF-8.



--
Thanks

Bernd

--
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: starting to review the Extend NOT NULL representation to pg_constraint patch

[Bernd Helmle]

--On 24. Juni 2011 12:06:17 -0400 Robert Haas robertmh...@gmail.com wrote:


Uh, really?  pg_upgrade uses SQL commands to recreate the contents of
the system catalogs, so if these entries aren't getting created
automatically, that sounds like a bug in the patch...


AFAIR, i've tested it and it worked as expected.

--
Thanks

Bernd

--
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] Another swing at JSON

[Bernd Helmle]

--On 17. Juni 2011 18:06:58 -0400 Joseph Adams joeyadams3.14...@gmail.com 
wrote:



Done.  Note that this module builds, tests, and installs successfully
with USE_PGXS=1.  However, building without USE_PGXS=1 produces the
following:

CREATE EXTENSION json;
ERROR:  incompatible library /usr/lib/postgresql/json.so: version
mismatch DETAIL:  Server is version 9.1, library is version 9.2.

Similar problems occur with a couple other modules I tried (hstore, intarray).


Hmm, works for me. Seems you have messed up your installation in some way 
(build against current -HEAD but running against a 9.1?).


I'm going to review in the next couple of days again.

--
Thanks

Bernd

--
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] Another swing at JSON

[Bernd Helmle]

--On 16. Juni 2011 17:38:07 -0400 Tom Lane t...@sss.pgh.pa.us wrote:


After reading Joseph's comment upthread, I don't see any consensus
wether the existing pre-9.1 support is required or even desired. Maybe
i missed it, but do we really expect an extension (or contrib module)
to be backwards compatible to earlier major releases, when shipped in
contrib/ ?


No, we don't.  You won't find any attempt in any contrib module to build
against prior releases.  There's not much point, since they're shipped
with a specific release of the core.


Okay, then we should remove this code. It doesn't do any complicated, but it 
seems a waste of code in this case (and from a maintenance point of view).


Joseph, are you able to remove the compatibility code for this CF?

--
Thanks

Bernd

--
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 - Debug builds without optimization

[Bernd Helmle]

--On 16. Juni 2011 14:30:27 +0200 Radosław Smogura rsmog...@softperience.eu 
wrote:



 Hello,

 I'm sending following patch which disables optimization when  --enable-debug
is passed. It was nasty (for me, at least) that debug  build required passing
of CFLAGS with -O0 to get nice traceable code.



-O0 hides bugs in your code (e.g. look at 
http://archives.postgresql.org/message-id/9714F5232AB2C4FCFCB392D5@amenophis 
and replies for an example to do it better). Doing this automatically on debug 
builds would be a step backwards.


--
Thanks

Bernd

--
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] procpid?

[Bernd Helmle]

--On 16. Juni 2011 15:33:35 + Greg Sabino Mullane g...@turnstep.com wrote:


No, this is clearly connections, not sessions. At least based on the items
in the postgresql.conf file, especially max_connections (probably one of the
items most closely associated with pg_stat_activity)


Well, but it doesn't show database connection(s) only, it also shows what 
actions are currently performed through the various connections on the 
databases and state information about them. I'm not a native english speaker, 
but i have the feeling that sessions is better suited for this kind of 
interactive monitoring. I believe Oracle also has a v$session view to query 
various information about what's going on.


--
Thanks

Bernd

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