Re: [HACKERS] Add jsonb_compact(...) for whitespace-free jsonb to text

[Andrew Dunstan]

On 04/29/2016 06:11 PM, Merlin Moncure wrote:



This is a simple matter of removing spaces in the occasional C string
literal in the serialization routines and adding a json_pretty
function.



I spent a few hours on this. See 
 for 
WIP - there are three commits. No regression tests yet for the two new 
functions (json_squash and json_pretty), Otherwise fairly complete. 
Removing whitespace generation was pretty simple for both json and jsonb.


cheers

andrew




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

Re: [HACKERS] New 9.6 external sort guidance around temp_tablespaces and maintenance_work_mem

[Bruce Momjian]

On Sat, Apr 30, 2016 at 04:39:22PM -0700, Peter Geoghegan wrote:
> On Sat, Apr 30, 2016 at 4:26 PM, Bruce Momjian  wrote:
> > On Sat, Apr 30, 2016 at 04:23:00PM -0700, Peter Geoghegan wrote:
> >> Maybe there is a place to emphasize this change in the release notes.
> >> I don't really want to make it about the external sort feature,
> >> though, because enabling higher work_mem settings by making sure that
> >> does not disadvantage external sorts is as much about enabling
> >> HashAggregates as it is about enabling internal sorts.
> >
> > We do often mention in the release notes areas that will need retuning.
> 
> How do you feel about it in this instance?
> 
> As you may have gathered, my perspective is that the external sorting
> patches were more about fixing a problem with tuplesort than about
> improving its performance. The performance characteristics of the old
> approach to sorting runs were all over the place, which made
> increasing work_mem like taking one step forward, then two steps
> backwards.

Yes, we should mention something.

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

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +


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

Re: [HACKERS] New 9.6 external sort guidance around temp_tablespaces and maintenance_work_mem

[Peter Geoghegan]

On Sat, Apr 30, 2016 at 4:26 PM, Bruce Momjian  wrote:
> On Sat, Apr 30, 2016 at 04:23:00PM -0700, Peter Geoghegan wrote:
>> Maybe there is a place to emphasize this change in the release notes.
>> I don't really want to make it about the external sort feature,
>> though, because enabling higher work_mem settings by making sure that
>> does not disadvantage external sorts is as much about enabling
>> HashAggregates as it is about enabling internal sorts.
>
> We do often mention in the release notes areas that will need retuning.

How do you feel about it in this instance?

As you may have gathered, my perspective is that the external sorting
patches were more about fixing a problem with tuplesort than about
improving its performance. The performance characteristics of the old
approach to sorting runs were all over the place, which made
increasing work_mem like taking one step forward, then two steps
backwards.

-- 
Peter Geoghegan


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

Re: [HACKERS] New 9.6 external sort guidance around temp_tablespaces and maintenance_work_mem

[Bruce Momjian]

On Sat, Apr 30, 2016 at 04:23:00PM -0700, Peter Geoghegan wrote:
> Maybe there is a place to emphasize this change in the release notes.
> I don't really want to make it about the external sort feature,
> though, because enabling higher work_mem settings by making sure that
> does not disadvantage external sorts is as much about enabling
> HashAggregates as it is about enabling internal sorts.

We do often mention in the release notes areas that will need retuning.

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

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +


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

Re: [HACKERS] New 9.6 external sort guidance around temp_tablespaces and maintenance_work_mem

[Peter Geoghegan]

On Sat, Apr 30, 2016 at 3:23 PM, Bruce Momjian  wrote:
> Yes, this needs updating.  My point is that there is a whole lot of
> things we don't talk about in this area, and should, but I would like it
> to be of a consistent level of detail for all areas of performancce.

I think that we need to do better generally too, but the existing
handling of performance, such as it is, is not consistent in the level
of detail it goes into. For example, we give far more advice about
setting the value of commit_delay than setting the value of work_mem,
even though that's clearly a niche topic in comparison. You can say
the same thing about effective_io_concurrency. 95%+ of all users don't
use either setting, making that documentation irrelevant to them. I
think that this is simply because it was hard to make a good
recommendation about work_mem, but that's now less true overall. We
don't like equivocating, so we said only the absolute minimum.

ISTM that the area that needs the most attention is planner stuff, and
query workspace memory stuff (e.g. work_mem, temp files). work_mem and
maintenance_work_mem seem like good places to start adding more
practical advise, particularly given we can avoid mentioning sorting
or hashing, and still add value.

Maybe there is a place to emphasize this change in the release notes.
I don't really want to make it about the external sort feature,
though, because enabling higher work_mem settings by making sure that
does not disadvantage external sorts is as much about enabling
HashAggregates as it is about enabling internal sorts.

-- 
Peter Geoghegan


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

Re: [HACKERS] New 9.6 external sort guidance around temp_tablespaces and maintenance_work_mem

[Bruce Momjian]

On Sat, Apr 30, 2016 at 12:19:02PM -0700, Peter Geoghegan wrote:
> Presumably due to the old issues with tuplesort, the closest the docs
> get to recommending higher work_mem or maintenance_work_mem settings
> is: "Larger [maintenance_work_mem] settings might improve performance
> for vacuuming and for restoring database dumps". That's it! Since the
> performance characteristics of external sorting are now roughly in
> line with everything else, why continue to make such a weak statement
> in 9.6? It's not hard to understand why we originally equivocated
> here, but things have changed.

Yes, this needs updating.  My point is that there is a whole lot of
things we don't talk about in this area, and should, but I would like it
to be of a consistent level of detail for all areas of performancce.

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

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +


-- 
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] Windows 7, Visual Studio 2010: building PgAdmin3

[Bruce Momjian]

On Sat, Apr 30, 2016 at 12:11:16AM -0700, zeray87 wrote:
> Hello guys,
> This is my first ever post and here goes my apology for being newbie.
> 
> I have been able to build PgAdmin3  after several days of hassle on building
> PgAdmin3 using build-wxmsw.bat.
> 
> Now i am trying to build PgAdmin3 using Visual Studio 2010. unfortunately,
> the following error appears:
> Error 1   error MSB4175: The task factory "XamlTaskFactory" could not be
> loaded from the assembly "Microsoft.Build.Tasks.v4.0". Unable to create Xaml
> task.  File not found: C:\pgbuild\pgadmin3-jinfroster\pgAdmin3.xml.
> C:\pgbuild\pgadmin3-jinfroster\pgAdmin3.targets   45  6 pgAdmin3

This email list doesn't support pgadmin --- look here, 
http://www.pgadmin.org/support/ .

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

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +


-- 
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] Is pg_control file crashsafe?

[Bruce Momjian]

On Thu, Apr 28, 2016 at 09:58:00PM +, Alex Ignatov wrote:
> Hello everyone!
> We have some issue with truncated pg_control file on Windows after power
> failure.
> My questions is : 
> 1) Is pg_control protected from say , power crash or partial write? 
> 2) How PG update pg_control? By writing in it or writing in some temp file and
> after that rename it to pg_control to be atomic?

We write pg_controldata in one write() OS call:

if (write(fd, buffer, PG_CONTROL_SIZE) != PG_CONTROL_SIZE)

> 3) Can PG have  multiple pg_control copy to be more fault tolerant?
> 
> PS During some experiments we found that at present time there is no any 
> method
> to do crash recovery with "restored" version of pg_control (based on some
> manipulations with pg_resetxlog ).
>  Only by using pg_resetxlog and setting it parameters to values taken from wal
> file (pg_xlogdump)we can at least start PG and saw that PG state is at the
> moment of last check point. But we have no real confidence that PG is in
> consistent state(also docs on pg_resetxlogs told us about it too)

We have talked about improving the reliability of pg_control, but
failures are so rare we have never done anything to improve it.  I know
Tatsuo has talked about making pg_control more reliable, so I am CC'ing
him.

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

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +


-- 
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] Html parsing and inline elements

[Oleg Bartunov]

On Wed, Apr 13, 2016 at 6:57 PM, Marcelo Zabani  wrote:

> Hi, Tom,
>
> You're right, I don't think one can argue that the default parser should
> know HTML.
> How about your suggestion of there being an HTML parser, is it feasible? I
> ask this because I think that a lot of people store HTML documents these
> days, and although there probably aren't lots of HTML with words written
> along multiple inline elements, it would certainly be nice to have a proper
> parser for these use cases.
>
> What do you think?
>

I think it could be useful separate parser. But the problem is how to fully
utilize it to facilitate ranking, for example, words in title could be
considered more important than in the body, etc. Currently, setweight()
functions provides this separately from parser.

Parser outputs tokid and token:

select * from ts_parse('default','parsertext');
 tokid |  token
---+--
13 | 
 1 | parser
13 | 
13 | 
 1 | text
13 | 
(6 rows)

If we change parser to output also rank flag, then we could use it to
assign different weights.



>
> On Wed, Apr 13, 2016 at 11:09 AM, Tom Lane  wrote:
>
>> Marcelo Zabani  writes:
>> > I was here wondering whether HTML parsing should separate tokens that
>> are
>> > not separated by spaces in the original text, but are separated by an
>> > inline element. Let me show you an example:
>>
>> > *SELECT to_tsvector('english', 'Helloneighbor, you are
>> > nice')*
>> > *Results:** "'ce':7 'hello':1 'n':5 'neighbor':2"*
>>
>> > "Hello" and "neighbor" should really be separated, because ** is a
>> block
>> > element, but "nice" should be a single word there, since there is no
>> visual
>> > separation when rendered (** and ** are inline elements).
>>
>> I can't imagine that we want to_tsvector to know that much about HTML.
>> It doesn't, really, even have license to assume that its input *is*
>> HTML.  So even if you see things that look like  and  in the
>> string, it could easily be XML or SGML or some other SGML-like markup
>> format with different semantics for the markup keywords.
>>
>> Perhaps it'd be sane to do something like this as long as the
>> HTML-specific behavior was broken out into a separate function.
>> (Or maybe it could be done within to_tsvector as a separate parser
>> or separate dictionary?)  But I don't think it should be part of
>> the default behavior.
>>
>> regards, tom lane
>>
>
>

Re: [HACKERS] Lets (not) break all the things. Was: [pgsql-advocacy] 9.6 -> 10.0

[Oleg Bartunov]

On Fri, Apr 29, 2016 at 7:40 PM, Joshua D. Drake 
wrote:

> On 04/29/2016 08:44 AM, Bruce Momjian wrote:
>
>> On Tue, Apr 12, 2016 at 11:07:04PM +0300, Oleg Bartunov wrote:
>>
>>> Our roadmap http://www.postgresql.org/developer/roadmap/ is the
>>> problem. We
>>> don't have clear roadmap and that's why we cannot plan future feature
>>> full
>>> release. There are several postgres-centric companies, which have most of
>>> developers, who do all major contributions. All these companies has their
>>> roadmaps, but not the community.
>>>
>>
>> I would be concerned if company roadmaps overtly affected the community
>> roadmap.  In general, I find company roadmaps to be very short-sighted
>> and quickly changed based on the demands of specific users/customers ---
>> something we don't want to imitate.
>>
>> We do want company roadmaps to affect the community roadmap, but in a
>> healthy, long-term way, and I think, in general, that is happening.
>>
>>
> The roadmap is not the problem it is the lack of cooperation. Many
> companies are now developing features in a silo and then presenting them to
> the community. Instead we should be working with those companies to have
> them develop transparently so others can be a part of the process.
>

We are working on our roadmap to have it in form to be presented to the
community. I think we'll publish it somewhere in wiki.


>
> If the feature is going to be submitted to core anyway (or open source)
> why wouldn't we just do that? Why wouldn't EDB develop directly within the
> Pg infrastructure. Why wouldn't we build teams around the best and
> brightest between EDB, 2Q and Citus?
>

This is what I suggested.  Features considered to be open source could be
discussed and developed together.



>
> Egos.
>
> Consider PgLogical, who is working on this outside of 2Q? Where is the git
> repo for it? Where is the bug tracker? Where is the mailing list? Oh, its
> -hackers, except that it isn't, is it?
>
> It used to be that everyone got together and worked together before the
> patch review process. Now it seems like it is a competition between
> companies to see whose ego can get the most inflated via press releases
> because they developed X for Y.
>
>
git log says better than any press releases :)


> If the companies were to come together and truly recognize that profit is
> the reward not the goal then our community would be much stronger for it.


I'd not limited by the companies, individual developes are highly welcome.
I'm afraid there are some.


>
>
> Sincerely,
>
> JD
>
>
> --
> Command Prompt, Inc.  http://the.postgres.company/
> +1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Everyone appreciates your honesty, until you are honest with them.
>

Re: [HACKERS] relocation truncated to fit: citus build failure on s390x

[Christoph Berg]

Re: To Andres Freund 2016-04-28 <20160428080824.ga22...@msg.df7cb.de>
> I'm not an expert in compiler flags, but it seems to me CFLAGS_SL is
> wrong on s390(x) in Makefile.port, it should use -fPIC like sparc.
> 
> (The m68k build has yet to happen, I'd guess it would exhibit the same
> problem.)

Fwiw, the m68k build finished successfully with -fpic:
https://buildd.debian.org/status/fetch.php?pkg=citus=m68k=5.0.1-1=1461978369

Christoph


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

Re: [HACKERS] New 9.6 external sort guidance around temp_tablespaces and maintenance_work_mem

[Peter Geoghegan]

On Sat, Apr 30, 2016 at 9:30 AM, Bruce Momjian  wrote:
>> I think we can expand "21.6. Tablespaces" to describe the implications
>> of these new performance characteristics. I'd like to hear opinions on
>> how to approach that before proposing a patch, though.

> This seems very detailed.  I think we need much broader coverage of how
> the existing GUC variables affect performance before we could cover
> this.

While my justification is very detailed, I think that the practical
guidance itself is fairly simple. In fact, making sure that people
don't miss that things have become *simpler* across the board is the
primary point that I'd like to make to users.

As of 9.6, external sorting finally benefits from very large amounts
of memory, certainly in the multiple gigabyte range, where before
performance steadily declined as work_mem increased past a fairly
small amount (less than 100MB in my tests). So, DBAs had competing,
even contradictory considerations: keep work_mem high enough to make
most sorts internal. But if a sort must be external, make sure that
the work_mem it has available is quite low. Say, less than 100MB. This
set of characteristics is almost impossible to tune for. Other
operations that use temporary memory bound in size by work_mem have
always benefited from increasing work_mem settings in a more or less
predictable, linear way, so the *general* picture about what to do
becomes far clearer (we don't really *need* to talk about sorting at
all).

Presumably due to the old issues with tuplesort, the closest the docs
get to recommending higher work_mem or maintenance_work_mem settings
is: "Larger [maintenance_work_mem] settings might improve performance
for vacuuming and for restoring database dumps". That's it! Since the
performance characteristics of external sorting are now roughly in
line with everything else, why continue to make such a weak statement
in 9.6? It's not hard to understand why we originally equivocated
here, but things have changed.

I hardly realized that the docs are only lukewarm on the idea that
increasing work_mem will ever help *anything* at all. It's easy to
fail to notice that when you're an expert. We provide *zero* guidance
on how to tune work_mem or maintenance_work_mem. Surely we can do
better than that.

A secondary point I'd like to make is that if and when no further
benefit can be observed from increasing work_mem, it's well worth
considering that more I/O bandwidth for temp files will help.
Obviously, this assumes the DBA avoids swapping when using all that
memory, for example by setting vm.swappiness appropriately on Linux,
while also avoiding OOMs. At the point that no further benefit can be
obtained by increasing work_mem, sequential I/O should be strongly
considered as a possible bottleneck to target. That's a nice problem
to have, because you can buy fairly inexpensive HDDs for temp files
that will increase the point at which higher work_mem settings will no
longer help.

So, we can talk about this stuff without necessarily even mentioning
external sorting.

I didn't mention it before now, but as it happens the 2009 ACM article
I linked to already (https://queue.acm.org/detail.cfm?id=1563874)
mentions that they found performance fell sharply past a certain point
when using Postgres for a large aggregate SQL query:

"""
Invoking the DBMS’s built-in EXPLAIN facility revealed the problem:
while the query planner chose a reasonable hash table-based
aggregation strategy for small tables, on larger tables it switched to
sorting by grouping columns—a viable, if suboptimal strategy given a
few million rows, but a very poor one when facing a billion.

"""

I think that this must be describing tuplesort's previous use of
replacement selection to sort runs -- it might have been the case that
an internal sort for the GroupAggregate was "viable, if suboptimal"
then, but an external sort was just unacceptable. 9.6 really blurs the
distinction between internal and external sorts a lot of the time. It
was common to see a big external sort with ~5 runs taking no longer
than 110% of the time of an equivalent internal sort when testing the
9.6 sorting stuff. I would like to help DBAs keep up the benefits when
they have hundreds of gigabytes or even terabytes of data to aggregate
through. If you look at the graph in that article, it isn't that hard
to imagine that a well-tuned implementation could avoid that
superlinear growth in query runtime, because *superlinear* growth
isn't predicted by any theoretical model. The article is incorrect to
state "There is no pathology here". Although, that doesn't undermine
the author's argument too much; I find it rather convincing overall.
The fact that a single node will eventually fall short doesn't mean it
isn't important to push your single node further, to maintain an
unsharded Postgres instance as the right choice for longer (by getting
the full benefit of the hardware). Getting this right could allow
unsharded 

Re: [HACKERS] Lets (not) break all the things. Was: [pgsql-advocacy] 9.6 - > 10.0

[Tomasz Rybak]

I cut many of emails from CC - AFAIR most of you are subscribed to 
pg-hackers.

Dnia 2016-04-30 19:29 Joshua D. Drake napisał(a):

>On 04/29/2016 11:50 AM, Joshua D. Drake wrote:
>> On 04/29/2016 11:36 AM, Simon Riggs wrote:
>>
>>> Egos.
>>>
>>> Consider PgLogical, who is working on this outside of 2Q?
>>>
>>>
>>> Thank you for volunteering to assist. What would you like to work on?
>>
>> You are very welcome. I have been testing as you know. I would be happy
>> to continue that and also was going to look into having a subscriber
>> validate if it is connecting to a subscribed node or not which is the
>> error I ran into.
>>
>> I am also interested in creating user docs (versus reference docs).
>
>So what do you think Simon? How about we put pgLogical under community 
>infrastructure, use the postgresql redmine to track bugs, feature 
>requests and documentation?
>
>I guarantee resources from CMD and I bet we could get others to 
>participate as well. Let's turn this into an awesome community driven 
>extension.
>

I reviewed pglogical-output extension in CF 2016-01. It was the only
patch I reviewed - it was quite big and as I was doing it afternoons
and not during work (other responsibilities) it took me more than half
of month. And while initially response was good, after final review there
was no response, no new patch version - also nothing in CF 2016-03.

At the same time I've seen that pglogical got some love in March - but
I'm not sure whether it is usable without *-output plugin. On the one
hand splitting those two makes review easier, or at least manageable.
OTOH one does not make much sense without the other. I can see that,
at least in theory, pglogical-output could be used independently, but
at the same time, its main user will be pglogical proper.

So, in summary - slightly better management and communication regarding
features and patches (not only this one; this is just the patch with
which I tried to do review) would be beneficial.
For now I'm not sure what is going on with pglogical,
and whether my review even mattered.

Best regards
Tomasz Rybak
-- 
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] pg9.6 segfault using simple query (related to use fk for join estimates)

[Tom Lane]

Julien Rouhaud  writes:
> On 29/04/2016 18:05, Tom Lane wrote:
>> Julien Rouhaud  writes:
>>> The segfault is caused by quals_match_foreign_key() calling get_leftop()
>>> and get_rightop() on a ScalarArrayOpExpr node.
>>>
>>> I'm not sure that assuming this compatibility is the right way to fix
>>> this though.

>> It certainly isn't.

> Agreed. New attached patch handles explicitly each node tag.

No, this is completely nuts.  The problem is that quals_match_foreign_key
is simply assuming that every clause in the list it's given is an OpExpr,
which is quite wrong.  It should just ignore non-OpExpr quals, since it
cannot do anything useful with them anyway.  There's a comment claiming
that non-OpExpr quals were already rejected:

 * Here since 'usefulquals' only contains bitmap indexes for quals
 * of type "var op var" we can safely skip checking this.

but that doesn't appear to have anything to do with current reality.

While this in itself is about a two-line fix, after reviewing 
137805f89acb3611 I'm pretty unhappy that it got committed at all.
I think this obvious bug is a good sign that it wasn't ready.
Other unfinished aspects like invention of an undocumented GUC
don't leave a good impression either.

Moreover, it looks to me like this will add quite a lot of overhead,
probably far more than is justified, because clauselist_join_selectivity
is basically O(N^2) in the relation-footprint of the current join --- and
not with a real small multiplier, either, as the functions it calls
contain about four levels of nested loops themselves.  Maybe that's
unmeasurable on trivial test cases but it's going to be disastrous in
large queries, or for relations with large numbers of foreign keys.

I think this should be reverted and pushed out to 9.7 development.
It needs a significant amount of rewriting to fix the performance
issue, and now's not the time to be doing that.

regards, tom lane


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

Re: [HACKERS] Lets (not) break all the things. Was: [pgsql-advocacy] 9.6 -> 10.0

[Joshua D. Drake]

On 04/29/2016 11:50 AM, Joshua D. Drake wrote:

On 04/29/2016 11:36 AM, Simon Riggs wrote:


Egos.

Consider PgLogical, who is working on this outside of 2Q?


Thank you for volunteering to assist. What would you like to work on?


You are very welcome. I have been testing as you know. I would be happy
to continue that and also was going to look into having a subscriber
validate if it is connecting to a subscribed node or not which is the
error I ran into.

I am also interested in creating user docs (versus reference docs).


So what do you think Simon? How about we put pgLogical under community 
infrastructure, use the postgresql redmine to track bugs, feature 
requests and documentation?


I guarantee resources from CMD and I bet we could get others to 
participate as well. Let's turn this into an awesome community driven 
extension.


Sincerely,

JD



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
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] SPI_exec ERROR in pl/r of R 3.2.4 on PostgreSQL on Windows 7

[Andre Mikulec]

Joe,

"
Who did the compiling? Did you compile everything yourself, or use
binary installers for some of it? If so, which ones?
"

This is really a continuation of the experience I had with Dave Cramer in here.

Postgresql 9.5 support #1
https://github.com/postgres-plr/plr/issues/1


To try to figure out the problem, ( and perhaps? eliminate Microsoft from the 
problem), 
I compiled a PostgreSQL [debug] version myself.
 
C:\Users\AnonymousUser\Desktop\PostgreSQL.9.5.1\App\PgSQL>chcp 1252 > nul && 
"%PGSQL%\bin\psql.exe"
psql (9.5.1)
Type "help" for help.

postgres=# select version();
  version

 PostgreSQL 9.5.1 on i686-pc-mingw32, compiled by gcc.exe 
(x86_64-posix-seh-rev0, Built by MinGW-W64 project) 5.3.0, 64-bit
(1 row)

I also built a non-debug plr.dll/plr myself too.
I modified ( mostly simplified ) 
https://github.com/jconway/plr/blob/master/Makefile
in the Makefile, I eliminated ( by much trial and error ) the OS non_window 
stuff, the pkg-config stuff, and the  PGXS stuff .

Then I did, 
AnonymousUser@ANONYMOUSX /c/Users/AnonymousUser/postgresql-9.4.1/contrib
$ make -C plr clean

AnonymousUser@ANONYMOUSX /c/Users/AnonymousUser/postgresql-9.4.1/contrib
$  make -C plr all

So now I have my own plr.dll.

Then, I followed the instructions ( INSTALL.txt ) found in here. 
http://www.joeconway.com/plr/plr-8.3.0.16-pg9.4-win64.zip

However, I used my own plr.dll/plr
Seems, that in the destination, I had to copy plr.dll to plr, but that seems to 
work fine.

Later, after I finish following "create extension plr;" found in 
http://www.joeconway.com/plr/doc/plr-install.html
 
I do

postgres=# select plr_version();
 plr_version
-
 08.03.00.16
(1 row)

postgres=#   select plr_environ();

 (PGDATA,C:/Users/AnonymousUser/Desktop/PostgreSQL.9.5.1/Data/data)
 (PGDATABASE,postgres)
 
(PGLOCALEDIR,"C:\\Users\\AnonymousUser\\Desktop\\PostgreSQL.9.5.1\\App\\PgSQL\\share\\")
 (PGLOG,"C:\\Users\\AnonymousUser\\Desktop\\PostgreSQL.9.5.1\\Data\\log.txt")
 (PGSQL,"C:\\Users\\AnonymousUser\\Desktop\\PostgreSQL.9.5.1\\App\\PgSQL")
 (PGSYSCONFDIR,C:/Users/AnonymousUser/Desktop/PostgreSQL.9.5.1/App/PgSQL/etc)
 (PGUSER,postgres)

 (R_ARCH,/x64)
 (R_HOME,C:/Users/AnonymousUser/Desktop/R-3.2.4)
 (R_KEEP_PKG_SOURCE,yes)
 (R_LIBS_USER,"C:\\Users\\AnonymousUser\\Documents/R/win-library/3.2")
 (R_USER,"C:\\Users\\AnonymousUser\\Documents")

NOTE: The directory structure is from Postgre 9.4 Portable,  I just use ONLY 
the directory structure.
The one and ONLY file I use is the pgsql.cmd batch startup file ( I did my 
'environment' and 'user friendly modifications.' )

postgres=#

I do this, I get no results, and no error.

postgres=# SELECT * FROM pg_catalog.pg_class WHERE relname = 'plr_modules' AND 
relnamespace = 2200;
 relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode 
| reltablespace | relpages
-+--+-+---+--+---+-+---+--
(0 rows)

But, then this ( R language code ) strangely works.

postgres=# select r_version(); # THIS IS THE 'R LANGUAGE' ( IF THE EXTENSION 
'works' )
r_version
-
 (platform,x86_64-w64-mingw32)
 (arch,x86_64)
 (os,mingw32)
 (system,"x86_64, mingw32")
 (status,"")
 (major,3)
 (minor,2.4)
 (year,2016)
 (month,03)
 (day,10)
 ("svn rev",70301)
 (language,R)
 (version.string,"R version 3.2.4 (2016-03-10)")
 (nickname,"Very Secure Dishes")
(14 rows)

This does not work.
postgres=# select upper(typname) || 'OID' as typename, oid from 
pg_catalog.pg_type where typtype = 'b' order by typname;
ERROR:  could not open file "base/12373/1247": No such file or directory

This ( R language code ) that uses that SQL does not work.

postgres=# select load_r_typenames();
ERROR:  R interpreter expression evaluation error
DETAIL:  Error in pg.spi.exec(sql) :
  error in SQL statement : could not open file "base/12373/1247": No such file 
or directory
CONTEXT:  In R support function pg.spi.exec
In PL/R function load_r_typenames

In real-time ( exactly right now ), I have exactly  PostgreSQL 9.4.1 (and pl/r 
and R.3.1.2 )on Windows 7 running on port 5433.
This Postgre 9.1.1 uses the  same hard disk ( 9.4.1 and 9.5.1 (above) share the 
exact same hard disk.)

C:\Users\AnonymousUser\Desktop\PostgreSQL.9.4.1\App\PgSQL> 
"%PGSQL%\bin\psql.exe" --port 5433
psql (9.4.1)
Type "help" for help.

postgres=# select version();
   version
-
 PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 64-bit
(1 row)

postgres=#  select plr_version();
 plr_version
-
 08.03.00.16
(1 row)

postgres=#  select plr_environ();

 (PGDATA,C:/Users/AnonymousUser/Desktop/PostgreSQL.9.4.1/Data/data)
 

Re: [HACKERS] [sqlsmith] Crash in apply_projection_to_path

[Tom Lane]

Amit Kapila  writes:
> On Fri, Apr 29, 2016 at 7:33 PM, Tom Lane  wrote:
>> make_join_rel is certainly not far enough down the call stack to solve
>> this problem.  It can, and typically will, be invoked multiple times
>> for the same target join relation.
>> 
>> One possible answer is to do it in standard_join_search, just before
>> the set_cheapest call for each join relation.

> Yes, that makes sense to me.

Done that way.

> Yet, another idea could be to create a copy of partial path before passing
> it to create_gather_path()?

That's just a kluge, and I'm not exactly convinced it'd solve the problem
anyway, unless you wanted to recursively copy the entire infrastructure
of the partial path.  What's needed is to set rules for how we build paths
up in an orderly fashion.  Such rules already existed for regular paths
(see the comments I added in c45bf5751), and now they exist for parallel
paths as well.

regards, tom lane


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

Re: [HACKERS] New 9.6 external sort guidance around temp_tablespaces and maintenance_work_mem

[Bruce Momjian]

On Thu, Apr 21, 2016 at 08:37:54PM -0700, Peter Geoghegan wrote:
> Documentation
> =
> 
> I think we can expand "21.6. Tablespaces" to describe the implications
> of these new performance characteristics. I'd like to hear opinions on
> how to approach that before proposing a patch, though. The basic
> guidance should, IMV, be:
> 
> * A temp tablespace with cheaper disks that have good sequential I/O
> performance can speed up external sorts quite a lot. Probably not a
> great idea to have many temp tablespaces. Use RAID0 instead, because
> that performs better, and because it doesn't matter that temp files
> are not recoverable if a disk is faulty.
> 
> * More memory for sorting and hashing is often better in PostgreSQL
> 9.6. Notably, the performance of hash joins that spill will tend to
> degrade less predictably than the performance of sorts that spill as
> less memory is made available. (Perhaps mention the number of external
> sort passes?)
> 
> * Increasing work_mem/maintenance_work_mem may fail to improve
> performance only because sorts then become more I/O bound. When in
> doubt, testing is advised. A balance may need to be found, if only to
> avoid wasting memory.

This seems very detailed.  I think we need much broader coverage of how
the existing GUC variables affect performance before we could cover
this.

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

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +


-- 
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] Support for N synchronous standby servers - take 2

[Tom Lane]

Kyotaro HORIGUCHI  writes:
> At Wed, 27 Apr 2016 18:05:26 -0400, Tom Lane  wrote in 
> <3167.1461794...@sss.pgh.pa.us>
>> My inclination is to just rip out the warning.

> Is there anyone object to removing the warining?

Hearing no objections, done.

regards, tom lane


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

[HACKERS] Windows 7, Visual Studio 2010: building PgAdmin3

[zeray87]

Hello guys,
This is my first ever post and here goes my apology for being newbie.

I have been able to build PgAdmin3  after several days of hassle on building
PgAdmin3 using build-wxmsw.bat.

Now i am trying to build PgAdmin3 using Visual Studio 2010. unfortunately,
the following error appears:
Error   1   error MSB4175: The task factory "XamlTaskFactory" could not be
loaded from the assembly "Microsoft.Build.Tasks.v4.0". Unable to create Xaml
task.  File not found: C:\pgbuild\pgadmin3-jinfroster\pgAdmin3.xml.
C:\pgbuild\pgadmin3-jinfroster\pgAdmin3.targets 45  6   pgAdmin3

I am desperately waiting for your help.
 Best Regards,
Zeray
 




--
View this message in context: 
http://postgresql.nabble.com/Windows-7-Visual-Studio-2010-building-PgAdmin3-tp5901339.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


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

Re: [HACKERS] Rename max_parallel_degree?

[David Rowley]

On 29 April 2016 at 02:41, Robert Haas  wrote:
> On Wed, Apr 27, 2016 at 1:05 PM, Daniel Verite  
> wrote:
>> Robert Haas wrote:
>>> Of course, we could make this value 1-based rather than 0-based, as
>>> Peter Geoghegan suggested a while back.  But as I think I said at the
>>> time, I think that's more misleading than helpful.  The leader
>>> participates in the parallel plan, but typically does far less of the
>>> work beneath the Gather node than the other nodes involved in the
>>> query, often almost none.  In short, the leader is special.
>>> Pretending that it's just another process involved in the parallel
>>> group isn't doing anyone a favor.
>>
>> FWIW, that's not how it looks from the outside (top or vmstat).
>> I'm ignorant about how parallel tasks are assigned in the planner,
>> but when trying various values for max_parallel_degree and running
>> simple aggregates on large tables on a single 4 core CPU doing
>> nothing else, I'm only ever seeing max_parallel_degree+1 processes
>> indiscriminately at work, often in the same state (R running or
>> D waiting for disk).
>
> Right, but they're probably not doing the SAME work.  You can look at
> EXPLAIN (ANALYZE, VERBOSE, BUFFERS) to see.  Of course, all the work
> above the Gather node is being done by the leader, but the stuff below
> the Gather node often has a bit of participation from the leader, but
> is mostly the workers.

Robert, I'd imagine that most of your tests to make you think what you
do would have come from testing parallel seq scan, where perhaps
Daniel's comes from testing something like parallel aggregates, or at
least something that gives the workers a decent amount of work per
tuple returned.

With the setting;

# set max_parallel_degree = 8;

Given a table like;

# create table t1 (num int not null);

Populated with;
# insert into t1 select generate_Series(1,1000);

Given the query;

# explain (analyze, verbose) select count(*) from t1;

if we think about what'll happen here, each worker will go off and
grab all of the tuples it can and aggregate each one, meanwhile the
main process would otherwise be quite idle waiting for the workers to
come back with their partially aggregated results, so instead, to keep
itself busy, goes off and helps them out. We can see this is true in
the explain analyze verbose output;

   QUERY PLAN

 Finalize Aggregate  (cost=80508.54..80508.55 rows=1 width=8) (actual
time=605.019..605.019 rows=1 loops=1)
   Output: pg_catalog.count(*)
   ->  Gather  (cost=80508.13..80508.53 rows=4 width=8) (actual
time=604.799..605.011 rows=5 loops=1)
 Output: (count(*))
 Workers Planned: 4
 Workers Launched: 4
 ->  Partial Aggregate  (cost=79508.13..79508.13 rows=1
width=8) (actual time=585.099..585.100 rows=1 loops=5)
   Output: count(*)
   Worker 0: actual time=579.736..579.736 rows=1 loops=1
   Worker 1: actual time=580.669..580.669 rows=1 loops=1
   Worker 2: actual time=580.512..580.513 rows=1 loops=1
   Worker 3: actual time=580.649..580.649 rows=1 loops=1
   ->  Parallel Seq Scan on public.t1
(cost=0.00..72456.10 rows=2820810 width=0) (actual time=2.310..404.978
rows=200 loops=5)
 Output: num
 Worker 0: actual time=2.231..397.251 rows=1892702 loops=1
 Worker 1: actual time=3.107..403.436 rows=1983602 loops=1
 Worker 2: actual time=3.030..403.082 rows=1952188 loops=1
 Worker 3: actual time=3.135..404.756 rows=2039650 loops=1

If we look at total the number of rows that each of the workers
managed to chew through, and subtract from the total rows in t1;

# select 1000 - (1892702 + 1983602 + 1952188 + 2039650);
 ?column?
--
  2131858
(1 row)

So the main process managed to get through 2131858 rows while waiting
for the helpers finishing their work. Daniel looks right.

Another example, this time no aggregation;

postgres=# explain (analyze,verbose) select * from t1 where num > 950;
 QUERY PLAN

 Gather  (cost=1000.00..126718.15 rows=502200 width=4) (actual
time=454.071..694.967 rows=50 loops=1)
   Output: num
   Workers Planned: 4
   Workers Launched: 4
   ->  Parallel Seq Scan on public.t1  (cost=0.00..76753.65
rows=125550 width=4) (actual time=430.240..451.990 rows=10
loops=5)
 Output: num
 Filter: (t1.num > 950)
 Rows Removed by Filter: 190
 Worker 0: actual time=421.220..449.077 

Re: [HACKERS] [sqlsmith] Failed assertion in BecomeLockGroupLeader

[Andreas Seltenreich]

Amit Kapila writes:

> On Fri, Apr 29, 2016 at 7:15 PM, Tom Lane  wrote:
>> but it might be worth copying over the full query from the parent
>> side.
>
> That would amount to couple of extra cycles considering we need to do it
> for each worker, but OTOH it might be a useful debugging information in the
> cases as reported in this thread.

Maybe only do it in assertion-enabled builds when performance is an
issue?

regards,
andreas


-- 
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] [sqlsmith] Failed assertion in BecomeLockGroupLeader

[Andreas Seltenreich]

Amit Kapila writes:

> On Sat, Apr 30, 2016 at 5:58 AM, Andreas Seltenreich  
> wrote:
>> This sounds like it should work to capture more context when the
>> Assertion fails the next time.
>
> Sounds good.  So can we assume that you will try to get us the new report
> with more information?

Ja.  I do have a busy weekend+week ahead though, so no holding of
breath.

regards
Andreas


-- 
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] [sqlsmith] Failed assertion in BecomeLockGroupLeader

[Amit Kapila]

On Sat, Apr 30, 2016 at 5:58 AM, Andreas Seltenreich 
wrote:
>
> Alvaro Herrera writes:
> > Amit Kapila wrote:
> >> It will be helpful if you can find the offending query or plan
> >> corresponding to it?
> >
> > So I suppose the PID of the process starting the workers should be in
> > the stack somewhere.
>
> Ja, it's right on the top, but long gone by now…
>
> > With that one should be able to attach to that process and get another
> > stack trace.  I'm curious on whether you would need to have started
> > the server with "postgres -T"
>
> This sounds like it should work to capture more context when the
> Assertion fails the next time.
>

Sounds good.  So can we assume that you will try to get us the new report
with more information?


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

Re: [HACKERS] [sqlsmith] Failed assertion in BecomeLockGroupLeader

[Amit Kapila]

On Fri, Apr 29, 2016 at 7:15 PM, Tom Lane  wrote:
>
> Amit Kapila  writes:
> > On Fri, Apr 29, 2016 at 12:01 PM, Andreas Seltenreich <
seltenre...@gmx.de>
> > wrote:
> >> tonight's sqlsmith run yielded another core dump:
> >>
> >> TRAP: FailedAssertion("!(MyProc->lockGroupLeader == ((void *)0))",
File:
> >> "proc.c", Line: 1787)
> >>
> >> I couldn't identifiy a query for it though: debug_query_string is
empty.
> >> Additionally, the offending query was not reported in the error context
> >> as it typically is for non-parallel executor crashes.
>
> > From callstack below, it is clear that the reason for core dump is that
> > Gather node is pushed below another Gather node which makes worker
execute
> > the Gather node.  Currently there is no support in workers to launch
> > another workers and ideally such a plan should not be generated.
>
> It might not be intentional.  The bug we identified from Andreas' prior
> report could be causing this: once a GatherPath's subpath has been freed,
> that palloc chunk could be recycled into another GatherPath, or something
> with a GatherPath in its substructure, leading to a plan of that shape.
>

Yes, thats one possibility.

> > It will
> > be helpful if you can find the offending query or plan corresponding to
it?
>
> I presume the lack of debug_query_string data is because nothing is
> bothering to set debug_query_string in a worker process.  Should that be
> remedied?  At the very least set it to "worker process",
>

Currently for the purpose of query descriptor in worker process, we are
using "" (refer function ExecParallelGetQueryDesc()), so
that seems to be a better choice.

> but it might be
> worth copying over the full query from the parent side.
>

That would amount to couple of extra cycles considering we need to do it
for each worker, but OTOH it might be a useful debugging information in the
cases as reported in this thread.  Do you see any broader use of passing
query string to worker?

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