[HACKERS] [patch] typo in brin.sql

2015-07-05 Thread Christoph Berg
There was a stray s in classes implement*s*. I've also added a
the to make the sentence more readable (at least for me).

Christoph
-- 
c...@df7cb.de | http://www.df7cb.de/
diff --git a/doc/src/sgml/brin.sgml b/doc/src/sgml/brin.sgml
new file mode 100644
index e25f09c..894c269
*** a/doc/src/sgml/brin.sgml
--- b/doc/src/sgml/brin.sgml
*** typedef struct BrinOpcInfo
*** 531,537 
  
To implement these methods in a generic way, the operator class
defines its own internal support functions.
!   (For instance, quotemin/max/ operator classes implements
support functions for the four inequality operators for the data type.)
Additionally, the operator class must supply appropriate
operator entries,
--- 531,537 
  
To implement these methods in a generic way, the operator class
defines its own internal support functions.
!   (For instance, the quotemin/max/ operator classes implement
support functions for the four inequality operators for the data type.)
Additionally, the operator class must supply appropriate
operator entries,

-- 
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] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

2015-07-05 Thread Heikki Linnakangas

On 07/04/2015 07:34 PM, Fabien COELHO wrote:



In summary, the X^1.5 correction seems to work pretty well. It doesn't
completely eliminate the problem, but it makes it a lot better.


I've looked at the maths.

I think that the load is distributed as the derivative of this function,
that is (1.5 * x ** 0.5): It starts at 0 but very quicky reaches 0.5, it
pass the 1.0 (average load) around 40% progress, and ends up at 1.5, that
is the finishing load is 1.5 the average load, just before fsyncing files.
This looks like a recipee for a bad time: I would say this is too large an
overload. I would suggest a much lower value, say around 1.1...


Hmm. Load is distributed as a derivate of that, but probably not the way 
you think. Note that X means the amount of WAL consumed, not time. The 
goal is that I/O is constant over time, but the consumption of WAL over 
time is non-linear, with a lot more WAL consumed in the beginning of a 
checkpoint cycle. The function compensates for that.



The other issue with this function is that it should only degrade
performance by disrupting the write distribution if someone has WAL on a
different disk. As I understand it this thing does only make sense if the
WAL  the data are on the samee disk. This really suggest a guc.


No, the I/O storm caused by full-page-writes is a problem even if WAL is 
on a different disk. Even though the burst of WAL I/O then happens on a 
different disk, the fact that we consume a lot of WAL in the beginning 
of a checkpoint makes the checkpointer think that it needs to hurry up, 
in order to meet the deadline. It will flush a lot of pages in a rush, 
so you get a burst of I/O on the data disk too. Yes, it's even worse 
when WAL and data are on the same disk, but even then, I think the 
random I/O caused by the checkpointer hurrying is more significant than 
the extra WAL I/O, which is sequential.


To illustrate that, imagine that the checkpoint begins now. The 
checkpointer calculates that it has 10 minutes to complete the 
checkpoint (checkpoint_timeout), or until 1 GB of WAL has been generated 
(derived from max_wal_size), whichever happens first. Immediately after 
the Redo-point has been established, in the very beginning of the 
checkpoint, the WAL storm begins. Every backend that dirties a page also 
writes a full-page image. After just 10 seconds, those backends have 
already written 200 MB of WAL. That's 1/5 of the quota, and based on 
that, the checkpointer will quickly flush 1/5 of all buffers. In 
reality, the WAL consumption is not linear, and will slow down as time 
passes and less full-page writes happen. So in reality, the checkpointer 
would have a lot more time to complete the checkpoint - it is 
unnecessarily aggressive in the beginning of the checkpoint.


The correction factor in the patch compensates for that. With the X^1.5 
formula, when 20% of the WAL has already been consumed, the checkpointer 
have flushed only ~ 9% of the buffers, not 20% as without the patch.


The ideal correction formula f(x), would be such that f(g(X)) = X, where:

 X is time, 0 = beginning of checkpoint, 1.0 = targeted end of 
checkpoint (checkpoint_segments), and


 g(X) is the amount of WAL generated. 0 = beginning of checkpoint, 1.0 
= targeted end of checkpoint (derived from max_wal_size).


Unfortunately, we don't know the shape of g(X), as that depends on the 
workload. It might be linear, if there is no effect at all from 
full_page_writes. Or it could be a step-function, where every write 
causes a full page write, until all pages have been touched, and after 
that none do (something like an UPDATE without a where-clause might 
cause that). In pgbench-like workloads, it's something like sqrt(x). I 
picked X^1.5 as a reasonable guess. It's close enough to linear that it 
shouldn't hurt too much if g(x) is linear. But it cuts the worst spike 
at the very beginning, if g(x) is more like sqrt(x).


This is all assuming that the application load is constant. If it's 
not, g(x) can obviously have any shape, and there's no way we can 
predict that. But that's a different story, nothing to do with 
full_page_writes.



I have ran some tests with this patch and the detailed results of the
runs are attached with this mail.


I do not understand really the aggregated figures in the files attached.


Me neither. It looks like Amit measured the time spent in mdread and 
mdwrite, but I'm not sure what conclusions one can draw from that.



I thought the patch should show difference if I keep max_wal_size to
somewhat lower or moderate value so that checkpoint should get triggered
due to wal size, but I am not seeing any major difference in the writes
spreading.


I'm not sure I understand your point. I would say that at full speed
pgbench the disk is always busy writing as much as possible, either
checkpoint writes or wal writes, so the write load as such should not be
that different anyway?

I understood that the point of the patch is to check whether 

Re: [HACKERS] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

2015-07-05 Thread Heikki Linnakangas

On 07/05/2015 08:19 AM, Fabien COELHO wrote:

I am a bit skeptical about this.  We need test scenarios that clearly
show the benefit of having and of not having this behavior. It might be
that doing this always is fine for everyone.


Do you mean I have to proove that there is an actual problem induced from
this patch?


You don't have to do anything if you don't want to. I said myself that 
this needs performance testing of the worst-case scenario, one where we 
would expect this to perform worse than without the patch. Then we can 
look at how bad that effect is, and decide if that's acceptable.


That said, if you could do that testing, that would be great! I'm not 
planning to spend much time on this myself, and it would take me a fair 
amount of time to set up the hardware and tools to test this. I was 
hoping Digoal would have the time to do that, since he started this 
thread, or someone else that has a system ready for this kind of 
testing. If no-one steps up to the plate to test this more, however, 
we'll have to just forget about this.



Having a guc would also help to test the feature with different values
than 1.5, which really seems harmful from a math point of view. I'm not
sure at all that a power formula is the right approach.


Yeah, a GUC would be helpful in testing this. I'm hoping that we would 
come up with a reasonable formula that would work well enough for 
everyone that we wouldn't need to have a GUC in the final patch, though.


- Heikki



--
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] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

2015-07-05 Thread Fabien COELHO



You don't have to do anything if you don't want to.


Sure:-) What I mean is that I think that this patch is not ripe, and I 
understood that some people were suggesting that it could be applied as is 
right away. I'm really disagreeing with that.


I said myself that this needs performance testing of the worst-case 
scenario, one where we would expect this to perform worse than without 
the patch. Then we can look at how bad that effect is, and decide if 
that's acceptable.


Ok, I'm fine with that. It's quite different from looks ok apply now.


That said, if you could do that testing, that would be great!


Hmmm. I was not really planing to. On the other hand, I have some scripts 
and a small setup that I've been using to test checkpointer flushing, and 
it would be easy to start some tests.



Having a guc would also help to test the feature with different values
than 1.5, which really seems harmful from a math point of view. I'm not
sure at all that a power formula is the right approach.


Yeah, a GUC would be helpful in testing this. I'm hoping that we would come 
up with a reasonable formula that would work well enough for everyone that we 
wouldn't need to have a GUC in the final patch, though.


Yep. If it is a guc testing is quite easy and I may run my scripts...

--
Fabien.


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


Re: [HACKERS] Exposing PG_VERSION_NUM in pg_config

2015-07-05 Thread Tom Lane
Michael Paquier michael.paqu...@gmail.com writes:
 On Fri, Jul 3, 2015 at 6:27 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Michael Paquier michael.paqu...@gmail.com writes:
 ... So attached is a patch that adds VERSION_NUM in
 Makefile.global.

 While there was not exactly universal consensus that we need this, the
 patch as given is merely two lines, so it seems awfully cheap to Just
 Do It.  Hence, I've gone ahead and committed it.  If we start getting
 complaints about use-cases this doesn't cover, we can re-discuss whether
 it's worth doing more.

 This looks fine to me. Thanks.

After further thought I started wondering why I hadn't back-patched this.
It's certainly safe/trivial enough for back-patching.  If we leave it just
in HEAD, then extension authors wouldn't be able to use it in the intended
way until 9.5 is old enough that they don't care about supporting 9.5.x
anymore; which is perhaps 5 years away.  If we back-patch all supported
branches then it would be safe to rely on VERSION_NUM for building
extensions within a year or two.

Any objections to 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] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

2015-07-05 Thread Fabien COELHO


Hello Heikki,


I think that the load is distributed as the derivative of this function,
that is (1.5 * x ** 0.5): It starts at 0 but very quicky reaches 0.5, it
pass the 1.0 (average load) around 40% progress, and ends up at 1.5, that
is the finishing load is 1.5 the average load, just before fsyncing files.
This looks like a recipee for a bad time: I would say this is too large an
overload. I would suggest a much lower value, say around 1.1...


Hmm. Load is distributed as a derivate of that, but probably not the way you 
think. Note that X means the amount of WAL consumed, not time.


Interesting point. After a look at IsCheckpointOnSchedule, and if I 
understand the code correctly, it is actually *both*, so it really depends 
whether the checkpoint was xlog or time triggered, and especially which 
one (time/xlog) is proeminent at the beginning of the checkpoint.


If it is time triggered and paced my reasonning is probably right and 
things will go bad/worse in the end, but if it is xlog-triggered and paced 
your line of argument is probably closer to what happens.


This suggest that the corrective function should be applied with more 
care, maybe only for the xlog-based on schedule test, but not the 
time-based check.


The goal is that I/O is constant over time, but the consumption of WAL 
over time is non-linear, with a lot more WAL consumed in the beginning 
of a checkpoint cycle. The function compensates for that.


*If* the checkpointer pacing comes from WAL size, which may or may not be 
the case.



[...]

Unfortunately, we don't know the shape of g(X), as that depends on the 
workload. It might be linear, if there is no effect at all from 
full_page_writes. Or it could be a step-function, where every write causes a 
full page write, until all pages have been touched, and after that none do 
(something like an UPDATE without a where-clause might cause that).


If postgresql is running in its cache (i.e. within shared buffers), the 
usual assumption would be an unknown exponential probability decreasing 
with time while the same pages are hit over and over.


If postgresql is running on memory or disk (effective database size 
greater than shared buffers), pages are statiscally not reused by another 
update before being sent out, so the full page write would be always used 
during the whole checkpoint, there is no WAL storm (or it is always a 
storm, depending on the point of view) and the corrective factor would 
only create issues...


So basically I would say that what to do heavily depends on the database 
size and checkpoint trigger (time vs xlog), which really suggest that a 
guc is indispensible, and maybe that the place the correction is applied 
is currently not the right one.



In pgbench-like workloads, it's something like sqrt(x).


Probably for a small database size?

I picked X^1.5 as a reasonable guess. It's close enough to linear that 
it shouldn't hurt too much if g(x) is linear.


My understanding is still a 50% overload at the end of the checkpoint just 
before issuing fsync... I think that could hurt in some case.


But it cuts the worst spike at the very beginning, if g(x) is more like 
sqrt(x).


Hmmm. It's a balance between saving the 10 first seconds of the checkpoint 
at the price of risking a panic at the end of the checkpoint.


Now the right approach might be for pg to know what is happening by 
collecting statistics while running, and to apply a correction when it is 
needed, for the amount needed.


--
Fabien.


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


Re: [HACKERS] Comfortably check BackendPID with psql

2015-07-05 Thread Julien Rouhaud
On 12/06/2015 06:56, Noah Misch wrote:
 On Thu, Jun 11, 2015 at 04:05:13PM -0500, Jim Nasby wrote:
 On 6/11/15 4:55 AM, Andres Freund wrote:
 On 2015-06-11 09:41:17 +, Naoya Anzai wrote:
 This is a so tiny patch but I think it is very useful for hackers and DBAs.
 When we debug with psql, we frequently use SELECT pg_backend_pid();.
 This can change the input of the 24 characters to the only 4 characters!

 Not a big fan of that abbreviation itself. What I'd wondered about
 instead - and actually had patched into my psql at some point - is
 adding an appropriate escape to psql's PROMPT. I think that'd serve your
 purpose as well?

 +3.14159; that would be hugely helpful when using gdb.
 

+1

 You can get that today.  In ~/.psqlrc:
 
 SELECT pg_catalog.pg_backend_pid() AS backend_pid \gset
 \set PROMPT1 '%m %:backend_pid: %/%R%# '
 
 It doesn't update after \connect, but the overlap between my use of \connect
 and my use of debuggers is tiny.
 
 

Tiny for me too, but I sometimes had the need.

I can't really see any good reason not to add a %p escape to psql's
PROMPT, so I'm attaching a simple patch to implement it. Unless someone
objects, I'll add it to the next commitfest.

-- 
Julien Rouhaud
http://dalibo.com - http://dalibo.org
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
***
*** 3365,3370  testdb=gt; userinputINSERT INTO my_table VALUES (:'content');/userinput
--- 3365,3377 
/varlistentry
  
varlistentry
+ termliteral%p/literal/term
+ listitem
+  paraThe PID of the backend./para
+ /listitem
+   /varlistentry
+ 
+   varlistentry
  termliteral%gt;/literal/term
  listitemparaThe port number at which the database server is listening./para/listitem
/varlistentry
*** a/src/bin/psql/prompt.c
--- b/src/bin/psql/prompt.c
***
*** 34,39 
--- 34,40 
   * %M - database server hostname.domainname, [local] for AF_UNIX
   *		sockets, [local:/dir/name] if not default
   * %m - like %M, but hostname only (before first dot), or always [local]
+  * %p - backend PID
   * % - database server port number
   * %n - database user name
   * %/ - current database
***
*** 161,166  get_prompt(promptStatus_t status)
--- 162,172 
  	if (pset.db)
  		strlcpy(buf, session_username(), sizeof(buf));
  	break;
+ 	/* backend PID */
+ case 'p':
+ 	if (pset.db)
+ 		snprintf(buf, sizeof(buf), %d, PQbackendPID(pset.db));
+ 	break;
  
  case '0':
  case '1':

-- 
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] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

2015-07-05 Thread Robert Haas
On Sun, Jul 5, 2015 at 1:19 AM, Fabien COELHO coe...@cri.ensmp.fr wrote:
 Do you mean I have to proove that there is an actual problem induced from
 this patch?

No, I'm not saying anyone *has* to do anything.  What I'm saying is
that I'm not convinced by your analysis.  I don't think we have enough
evidence at this point to conclude that a GUC is necessary, and I hope
it isn't, because I can't imagine what advice we would be able to give
people about how to set it, other than try all the value and see what
works best, which isn't going to be satisfying.

More broadly, I don't really know how to test this patch and show when
it helps and when it hurts.  And I think we need that, rather than
just a theoretical analysis, to tune the behavior.  Heikki, can you
describe what you think a good test setup would be?  Like, what
workload should we run, and what measurements should we gather to see
what the patch is doing that is good or bad?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [HACKERS] Exposing PG_VERSION_NUM in pg_config

2015-07-05 Thread Andres Freund
On 2015-07-05 10:51:48 -0400, Tom Lane wrote:
 Any objections to doing that?

Nope.


-- 
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] Simple Typo patch.

2015-07-05 Thread Tom Lane
CharSyam chars...@gmail.com writes:
 I just found simple typo error :)
 statment - statement
 excercise - exercise
 They are all in just comments :)

Pushed, thanks.

(For future reference, a patch like this needs to update the
expected-output files too.)

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] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

2015-07-05 Thread Fabien COELHO



No, I'm not saying anyone *has* to do anything.  What I'm saying is
that I'm not convinced by your analysis.


Well, the gist of my analysis is really to say that there are potential 
performance issues with the proposed change, and that it must be tested 
thoroughly. The details may varry:-)


I don't think we have enough evidence at this point to conclude that a 
GUC is necessary, and I hope it isn't, because I can't imagine what 
advice we would be able to give people about how to set it, other than 
try all the value and see what works best, which isn't going to be 
satisfying.


At least for testing, ISTM that a GUC would be really useful.

More broadly, I don't really know how to test this patch and show when 
it helps and when it hurts. And I think we need that, rather than just a 
theoretical analysis, to tune the behavior.


The point of an analysis is to think about how it works and what to test, 
but it is not a substitute for testing, obviously.


--
Fabien.


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


Re: [HACKERS] [patch] typo in brin.sql

2015-07-05 Thread Tom Lane
Christoph Berg c...@df7cb.de writes:
 There was a stray s in classes implement*s*. I've also added a
 the to make the sentence more readable (at least for me).

Pushed, thanks.  (I also tweaked the second part of that sentence a
bit, though I'm not sure it's 100% perfect grammar even yet.)

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] [patch] Simple Typo patch.

2015-07-05 Thread CharSyam
I just found simple typo error :)

statment - statement
excercise - exercise

They are all in just comments :)


typos.patch
Description: Binary data

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


Re: [HACKERS] Exposing PG_VERSION_NUM in pg_config

2015-07-05 Thread Pavel Stehule
2015-07-05 16:51 GMT+02:00 Tom Lane t...@sss.pgh.pa.us:

 Michael Paquier michael.paqu...@gmail.com writes:
  On Fri, Jul 3, 2015 at 6:27 AM, Tom Lane t...@sss.pgh.pa.us wrote:
  Michael Paquier michael.paqu...@gmail.com writes:
  ... So attached is a patch that adds VERSION_NUM in
  Makefile.global.

  While there was not exactly universal consensus that we need this, the
  patch as given is merely two lines, so it seems awfully cheap to Just
  Do It.  Hence, I've gone ahead and committed it.  If we start getting
  complaints about use-cases this doesn't cover, we can re-discuss whether
  it's worth doing more.

  This looks fine to me. Thanks.

 After further thought I started wondering why I hadn't back-patched this.
 It's certainly safe/trivial enough for back-patching.  If we leave it just
 in HEAD, then extension authors wouldn't be able to use it in the intended
 way until 9.5 is old enough that they don't care about supporting 9.5.x
 anymore; which is perhaps 5 years away.  If we back-patch all supported
 branches then it would be safe to rely on VERSION_NUM for building
 extensions within a year or two.

 Any objections to doing that?


+1

Pavel



 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] Comfortably check BackendPID with psql

2015-07-05 Thread Petr Korobeinikov
+1 for Julien's patch.


Re: [HACKERS] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

2015-07-05 Thread Andres Freund
On 2015-07-05 11:05:28 -0400, Robert Haas wrote:
 More broadly, I don't really know how to test this patch and show when
 it helps and when it hurts.  And I think we need that, rather than
 just a theoretical analysis, to tune the behavior.  Heikki, can you
 describe what you think a good test setup would be?  Like, what
 workload should we run, and what measurements should we gather to see
 what the patch is doing that is good or bad?

I think a good start would be to graph the writeout rate over several
checkpoints.  It'd be cool if there were a better way, but it's probably
easiest to just graph the number of bytes written (using iostat) and the
number of dirty bytes in the kernel. That'll unfortunately include WAL,
but I can't immediately see how to avoid that.


-- 
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] auto_explain sample rate

2015-07-05 Thread Julien Rouhaud
On 03/06/2015 15:00, Craig Ringer wrote:
 
 
 On 3 June 2015 at 20:04, Andres Freund and...@anarazel.de
 mailto:and...@anarazel.de wrote:
 
 On 2015-06-03 18:54:24 +0800, Craig Ringer wrote:
  OK, here we go.
 
 Hm. Wouldn't random sampling be better than what you do? If your queries
 have a pattern to them (e.g. you always issue the same 10 queries in
 succession), this will possibly only show a subset of the queries.
 
 I think a formulation in fraction (i.e. a float between 0 and 1) will
 also be easier to understand.
 
 
 Could be, yeah. I was thinking about the cost of generating a random
 each time, but it's going to vanish in the noise compared to the rest of
 the costs in query execution.
 

Hello, I've just reviewed the patch.

I'm not sure if there's a consensus on the sample rate format.  FWIW, I
also think a fraction would be easier to understand.  Any news about
generating a random at each call to avoid the query pattern problem ?

The patch applies without error.  I wonder if there's any reason for
using pg_lrand48() instead of random(), as there's a port for random()
if the system lacks it.

After some quick checks, I found that auto_explain_sample_counter is
always initialized with the same value.  After some digging, it seems
that pg_lrand48() always returns the same values in the same order, at
least on my computer.  Have I missed something?

Otherwise, after replacing the pg_lrand48() call with a random(), it
works just fine.

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


-- 
Julien Rouhaud
http://dalibo.com - http://dalibo.org


-- 
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] Rework the way multixact truncations work

2015-07-05 Thread Robert Haas
On Thu, Jul 2, 2015 at 2:28 PM, Andres Freund and...@anarazel.de wrote:
 On 2015-07-02 13:58:45 -0400, Robert Haas wrote:
 On Thu, Jul 2, 2015 at 11:52 AM, Robert Haas robertmh...@gmail.com wrote:
  Will look at 0003 next.

 +appendStringInfo(buf, offsets [%u, %u), members [%u, %u),

 I don't think we typically use this style for notating intervals.

 I don't think we really have a very consistent style for xlog messages -
 this seems to describe the meaning accurately?

Although I realize this is supposed to be interval notation, I'm not
sure everyone will immediately figure that out.  I believe it has
created some confusion in the past.  I'm not going to spend a lot of
time arguing with you about it, but I'd do something else, like
offsets from %u stop before %u, members %u stop before %u.

 [several good points]

 +(errmsg(performing legacy multixact truncation,
 upgrade master)));

 This message needs work.  I'm not sure exactly what it should say, but
 I'm pretty sure that's not clear enough.

 I seriously, seriously doubt that it is a good idea to perform the
 legacy truncation from MultiXactAdvanceOldest() rather than
 TruncateMultiXact().

 But where should TruncateMultiXact() be called from? I mean, we could
 move the logic from inside MultiXactAdvanceOldest() to some special case
 in the replay routine, but what'd be the advantage?

I think you should call it from where TruncateMultiXact() is being
called from today.  Doing legacy truncations from a different place
than we're currently doing them just gives us more ways to be wrong.

 The checkpoint hasn't really happened at that point yet; you might
 truncate away stuff, then crash before the checkpoint is complete, and
 then we you restart recovery, you've got trouble.

 We're only talking about restartpoints here, right? And I don't see the
 problem - we don't read the slru anymore until the end of recovery, and
 the end of recovery can't happen before reaching the minimum revovery
 location?

You're still going to have to read the SLRU for as long as you are
doing legacy truncations, at least.

 If TruncateMultiXact() fails to acquire MultiXactTruncationLock right
 away, does it need to wait, or could it ConditionalAcquire and bail
 out if the lock isn't obtained?

 That seems like premature optimization to me. And one that's not that
 easy to do correctly - what if the current caller actually has a new,
 lower, minimum mxid?

Doesn't the next truncation just catch up?  But sure, I agree this is
inessential (and maybe better left alone for now).

 I'm not convinced that it's a good idea to remove
 lastCheckpointedOldest and replace it with nothing.  It seems like a
 very good idea to have two separate pieces of state in shared memory:

 - The oldest offset that we think anyone might need to access to make
 a visibility check for a tuple.
 - The oldest offset that we still have on disk.

 The latter would now need to be called something else, not
 lastCheckpointedOldest, but I think it's still good to have it.

 Otherwise, I don't see how you protect against the on-disk state
 wrapping around before you finish truncating, and then maybe
 truncation eats something that was busy getting reused.

 Unless I miss something the stop limits will prevent that from
 happening? SetMultiXactIdLimit() is called only *after* the truncation
 has finished?

Hmm, that might be, I'd have to reread the patch.  The reason we
originally had it this way was because VACUUM was updating the limit
and then checkpoint was truncating, but now I guess vacuum + truncate
happen so close together that you might only need one value.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [HACKERS] Rework the way multixact truncations work

2015-07-05 Thread Andres Freund
(quick answer, off now)

On 2015-07-05 14:20:11 -0400, Robert Haas wrote:
 On Thu, Jul 2, 2015 at 2:28 PM, Andres Freund and...@anarazel.de wrote:
  On 2015-07-02 13:58:45 -0400, Robert Haas wrote:
  I seriously, seriously doubt that it is a good idea to perform the
  legacy truncation from MultiXactAdvanceOldest() rather than
  TruncateMultiXact().
 
  But where should TruncateMultiXact() be called from? I mean, we could
  move the logic from inside MultiXactAdvanceOldest() to some special case
  in the replay routine, but what'd be the advantage?
 
 I think you should call it from where TruncateMultiXact() is being
 called from today.  Doing legacy truncations from a different place
 than we're currently doing them just gives us more ways to be wrong.

The problem with that is that the current location is just plain
wrong. Restartpoints can be skipped (due different checkpoint segments
settings), may not happen at all (pending incomplete actions), and can
just be slowed down.

That's a currently existing bug that's easy to reproduce.

  The checkpoint hasn't really happened at that point yet; you might
  truncate away stuff, then crash before the checkpoint is complete, and
  then we you restart recovery, you've got trouble.
 
  We're only talking about restartpoints here, right? And I don't see the
  problem - we don't read the slru anymore until the end of recovery, and
  the end of recovery can't happen before reaching the minimum revovery
  location?
 
 You're still going to have to read the SLRU for as long as you are
 doing legacy truncations, at least.

I'm not following. Sure, we read the SLRUs as we do today. But, in
contrast to the current positioning in recovery, with the patch they're
done at pretty much the same point on the standby as on the primary
today?

Greetings,

Andres Freund


-- 
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] Rework the way multixact truncations work

2015-07-05 Thread Andres Freund
On July 5, 2015 8:50:57 PM GMT+02:00, Robert Haas robertmh...@gmail.com wrote:
On Sun, Jul 5, 2015 at 2:28 PM, Andres Freund and...@anarazel.de
wrote:
 (quick answer, off now)

 On 2015-07-05 14:20:11 -0400, Robert Haas wrote:
 On Thu, Jul 2, 2015 at 2:28 PM, Andres Freund and...@anarazel.de
wrote:
  On 2015-07-02 13:58:45 -0400, Robert Haas wrote:
  I seriously, seriously doubt that it is a good idea to perform
the
  legacy truncation from MultiXactAdvanceOldest() rather than
  TruncateMultiXact().
 
  But where should TruncateMultiXact() be called from? I mean, we
could
  move the logic from inside MultiXactAdvanceOldest() to some
special case
  in the replay routine, but what'd be the advantage?

 I think you should call it from where TruncateMultiXact() is being
 called from today.  Doing legacy truncations from a different place
 than we're currently doing them just gives us more ways to be wrong.

 The problem with that is that the current location is just plain
 wrong. Restartpoints can be skipped (due different checkpoint
segments
 settings), may not happen at all (pending incomplete actions), and
can
 just be slowed down.

 That's a currently existing bug that's easy to reproduce.

You might be right; I haven't tested that.

On the other hand, in the common case, by the time we perform a
restartpoint, we're consistent: I think the main exception to that is
if we do a base backup that spans multiple checkpoints.  I think that
in the new location, the chances that the legacy truncation is trying
to read inconsistent data is probably higher.

The primary problem isn't that we truncate too early, it's that we delay 
truncation on the standby in comparison to the primary by a considerable 
amount. All the while continuing to replay multi creations. 

I don't see the difference wrt. consistency right now, but I don't have access 
to the code right now. I mean we *have* to do something while inconsistent. A 
start/stop backup can easily span a day or four. 

Andres

--- 
Please excuse brevity and formatting - I am writing this on my mobile phone.


-- 
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] dblink: add polymorphic functions.

2015-07-05 Thread Joe Conway
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/22/2015 10:26 PM, Corey Huinker wrote:
 Changes in this patch: - added polymorphic versions of
 dblink_fetch() - upped dblink version # to 1.2 because of new
 functions - migration 1.1 - 1.2 - DocBook changes for dblink(),
 dblink_get_result(), dblink_fetch()

The previous patch was missing dblink--1.1--1.2.sql and
dblink--1.2.sql. I have added them, so it should apply cleanly against
git master, but not done any actual review yet.


- -- 
Joe Conway
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.22 (GNU/Linux)

iQIcBAEBAgAGBQJVmYTEAAoJEDfy90M199hl4tcQAJVIDiJn/ogFlxPSOxIQ1XRF
hYllqLTCALIyfDWsq5oizVrs3uFF5TpqMrFxpfpLhKbeGgGpnaOhP5rISw3DD1NA
P73MDVbP0/+Q2pwAk174+teXxqFBK3gQi4wgtaq0bC4aTC+LlphYImDbb6ExfrRR
CFlEV4MoC3vFsOKRjGalcv/iaM7HIZSn8lilmynCFx96BDwTgrmZu5vSk17a5MsO
oOc1s+1eIiZ7JpUGcYHwCmunC2Aed8OtcLjCFu3BTKTJEq1xhkbvHPFZvLBI6CtD
CI74SIHdtTg56Rm8lsJFnkg8SM9QW8kEKP/eJedS/ft5d3dFdOwYfORh+2qwmsCo
JOvtriUEVs835HGTatuh47dscwgCt0d6SA0a7rp4UxmoQTmohyt5fN8LW2fJpHd8
bj7du7SUv6n3GwxZT+PBSALkvD011shNl0/AxehOoqXHmL86G+c3qc0vHEF5ulId
jeq/ECCX509Ef+DmjFoCtnotI/oXB74JmgOqy3RtvuuZ4hgsRzzT9kyKWmcpCHQ8
ZsmwmRY5xrjClzf7dsvh8LjyM1nOAoRNQhHLlj9I8lWKupChaWaY1PH0qzAdYm9p
a1kKTV4sfuNDqV57OUk/u33lvHVx1HDyvdCNRPHz+7cyw8Zka8jAGXvzq8vZp+bV
Grl29j/8fGaErff1CNZl
=Bh5j
-END PGP SIGNATURE-
diff --git a/contrib/dblink/Makefile b/contrib/dblink/Makefile
index b8d5157..f31b9f7 100644
*** a/contrib/dblink/Makefile
--- b/contrib/dblink/Makefile
*** PG_CPPFLAGS = -I$(libpq_srcdir)
*** 6,12 
  SHLIB_LINK = $(libpq)
  
  EXTENSION = dblink
! DATA = dblink--1.1.sql dblink--1.0--1.1.sql dblink--unpackaged--1.0.sql
  PGFILEDESC = dblink - connect to other PostgreSQL databases
  
  REGRESS = paths dblink
--- 6,13 
  SHLIB_LINK = $(libpq)
  
  EXTENSION = dblink
! DATA = dblink--1.1.sql dblink--1.0--1.1.sql dblink--unpackaged--1.0.sql \
!dblink--1.2.sql dblink--1.1--1.2.sql
  PGFILEDESC = dblink - connect to other PostgreSQL databases
  
  REGRESS = paths dblink
diff --git a/contrib/dblink/dblink--1.1--1.2.sql b/contrib/dblink/dblink--1.1--1.2.sql
index ...128611d .
*** a/contrib/dblink/dblink--1.1--1.2.sql
--- b/contrib/dblink/dblink--1.1--1.2.sql
***
*** 0 
--- 1,46 
+ /* contrib/dblink/dblink--1.1--1.2.sql */
+ 
+ -- complain if script is sourced in psql, rather than via ALTER EXTENSION
+ \echo Use ALTER EXTENSION dblink UPDATE TO '1.2' to load this file. \quit
+ 
+ CREATE FUNCTION dblink (text, text, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C;
+ 
+ CREATE FUNCTION dblink (text, text, boolean, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C;
+ 
+ CREATE FUNCTION dblink (text, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C;
+ 
+ CREATE FUNCTION dblink (text, boolean, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C;
+ 
+ CREATE FUNCTION dblink_get_result(text, anyelement)
+ RETURNS SETOF anyelement
+ AS 'MODULE_PATHNAME', 'dblink_get_result'
+ LANGUAGE C;
+ 
+ CREATE FUNCTION dblink_get_result(text, bool, anyelement)
+ RETURNS SETOF anyelement
+ AS 'MODULE_PATHNAME', 'dblink_get_result'
+ LANGUAGE C;
+ 
+ CREATE FUNCTION dblink_fetch (text, int, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_fetch'
+ LANGUAGE C;
+ 
+ CREATE FUNCTION dblink_fetch (text, int, boolean, anyelement)
+ RETURNS setof anyelement
+ AS 'MODULE_PATHNAME','dblink_fetch'
+ LANGUAGE C;
+ 
+ 
diff --git a/contrib/dblink/dblink--1.2.sql b/contrib/dblink/dblink--1.2.sql
index ...1d84df2 .
*** a/contrib/dblink/dblink--1.2.sql
--- b/contrib/dblink/dblink--1.2.sql
***
*** 0 
--- 1,275 
+ /* contrib/dblink/dblink--1.2.sql */
+ 
+ -- complain if script is sourced in psql, rather than via CREATE EXTENSION
+ \echo Use CREATE EXTENSION dblink to load this file. \quit
+ 
+ -- dblink_connect now restricts non-superusers to password
+ -- authenticated connections
+ CREATE FUNCTION dblink_connect (text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_connect'
+ LANGUAGE C STRICT;
+ 
+ CREATE FUNCTION dblink_connect (text, text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_connect'
+ LANGUAGE C STRICT;
+ 
+ -- dblink_connect_u allows non-superusers to use
+ -- non-password authenticated connections, but initially
+ -- privileges are revoked from public
+ CREATE FUNCTION dblink_connect_u (text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_connect'
+ LANGUAGE C STRICT SECURITY DEFINER;
+ 
+ CREATE FUNCTION dblink_connect_u (text, text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_connect'
+ LANGUAGE C STRICT SECURITY DEFINER;
+ 
+ REVOKE ALL ON FUNCTION dblink_connect_u (text) FROM public;
+ REVOKE ALL ON FUNCTION dblink_connect_u (text, text) FROM public;
+ 
+ CREATE FUNCTION dblink_disconnect ()
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_disconnect'
+ LANGUAGE C 

Re: [HACKERS] Parallel Seq Scan

2015-07-05 Thread Jeff Davis
On Fri, 2015-07-03 at 17:35 +0530, Amit Kapila wrote:

 Attached, find the rebased version of patch.
 

Comments:


* The heapam.c changes seem a little ad-hoc. Conceptually, which
portions should be affected by parallelism? How do we know we didn't
miss something?
* Why is initscan getting the number of blocks from the structure? Is it
just to avoid an extra syscall, or is there a correctness issue there?
Is initscan expecting that heap_parallelscan_initialize is always called
first (if parallel)? Please add a comment explaining above.
* What's the difference between scan-rs_nblocks and
scan-rs_parallel-phs_nblocks? Same for rs_rd-rd_id and phs_relid.
* It might be good to separate out some fields which differ between the
normal heap scan and the parallel heap scan. Perhaps put rs_ctup,
rs_cblock, and rs_cbuf into a separate structure, which is always NULL
during a parallel scan. That way we don't accidentally use a
non-parallel field when doing a parallel scan.
* Is there a reason that partial scans can't work with syncscan? It
looks like you're not choosing the starting block in the same way, so it
always starts at zero and never does syncscan. If we don't want to mix
syncscan and partial scan, that's fine, but it should be more explicit.

I'm trying to understand where tqueue.c fits in. It seems very closely
tied to the Funnel operator, because any change to the way Funnel works
would almost certainly require changes in tqueue.c. But tqueue is a
generic name for the file, so something seems off. Either we should
explicitly make it the supporting routines for the Funnel operator, or
we should try to generalize it a little.

I still have quite a bit to look at, but this is a start.

Regards,
Jeff Davis






-- 
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] Rework the way multixact truncations work

2015-07-05 Thread Robert Haas
On Sun, Jul 5, 2015 at 2:28 PM, Andres Freund and...@anarazel.de wrote:
 (quick answer, off now)

 On 2015-07-05 14:20:11 -0400, Robert Haas wrote:
 On Thu, Jul 2, 2015 at 2:28 PM, Andres Freund and...@anarazel.de wrote:
  On 2015-07-02 13:58:45 -0400, Robert Haas wrote:
  I seriously, seriously doubt that it is a good idea to perform the
  legacy truncation from MultiXactAdvanceOldest() rather than
  TruncateMultiXact().
 
  But where should TruncateMultiXact() be called from? I mean, we could
  move the logic from inside MultiXactAdvanceOldest() to some special case
  in the replay routine, but what'd be the advantage?

 I think you should call it from where TruncateMultiXact() is being
 called from today.  Doing legacy truncations from a different place
 than we're currently doing them just gives us more ways to be wrong.

 The problem with that is that the current location is just plain
 wrong. Restartpoints can be skipped (due different checkpoint segments
 settings), may not happen at all (pending incomplete actions), and can
 just be slowed down.

 That's a currently existing bug that's easy to reproduce.

You might be right; I haven't tested that.

On the other hand, in the common case, by the time we perform a
restartpoint, we're consistent: I think the main exception to that is
if we do a base backup that spans multiple checkpoints.  I think that
in the new location, the chances that the legacy truncation is trying
to read inconsistent data is probably higher.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [HACKERS] PostgreSQL 9.5 Alpha 1 build fail with perl 5.22

2015-07-05 Thread Andrew Dunstan


On 07/04/2015 11:02 AM, Tom Lane wrote:


I wondered how come we had not seen this problem in the buildfarm,
but the answer appears to be that our only working Cygwin critter
(brolga) doesn't build any of the optional PLs, so it skips these
modules altogether.  Seems like we need to improve that situation.




brolga has been on life support for quite a long time. The reason it 
hasn't been retired is that for a long time I was unable to get a 
buildfarm member running successfully on a more modern Cygwin. That now 
appears to have resolved itself, so in a week or so I will set up a 
Cygwin buildfarm member running on a modern Cygwin on Windows 8.1, and 
build with perl, python, openssl, libxml and libxslt. Note that I have 
only tested 32 bit Cygwin - 64-bit might well be a whole different story.


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] dblink: add polymorphic functions - review

2015-07-05 Thread Joe Conway
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/18/2015 09:57 PM, Pavel Stehule wrote:
 2015-06-19 6:32 GMT+02:00 Thomas Munro
 thomas.mu...@enterprisedb.com 
 mailto:thomas.mu...@enterprisedb.com:
 
 On Fri, Jun 19, 2015 at 4:17 PM, Pavel Stehule 
 pavel.steh...@gmail.com mailto:pavel.steh...@gmail.com wrote:
 I am sorry. I didn't the original mail in my mailbox.
 
 There is a (rather well hidden) way to ask Majordomo to send you a 
 message that arrived before you subscribed, so that you can reply 
 without creating a new thread.  I think you have to subscribe first
 so that you can log into it.  If you start here and browse to the
 message in the archive, you'll see a 'Mail this message to ...'
 link at the bottom:
 
 https://lists.postgresql.org/mj/mj_wwwusr/domain=postgresql.org
 
 I didn't know it. Thank you

Nice tip -- thanks! The patch was missing two new files - I posted a
complete patch using the original thread.


- -- 
Joe Conway
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.22 (GNU/Linux)

iQIcBAEBAgAGBQJVmYWDAAoJEDfy90M199hlT7YP/1rUX6TTJcTT45bRPWagbPSa
VS6NBihFDA4Kc6IDfvr7iS3nyWaHC1TDBrkhHKUfH1HSxXN492GkLCjG95HCSEHQ
AFylW3IarVpMdCxe7qJ2lH1CG69v8pabfmVPleymNZqz6j3KlNDXML9OqYjv+Aqx
G7Z6ciKC673+wQN6g4u9i8bbgJwHmqCdwEGLKgGqNnwErpNyTpxpr3tHZrkAC91B
KY6MBrNkpKyFbhxl5+1CIU4q6CWcYMtQSQ5T8eqKk2sntdHc3iDtrMFeSkK3OBRy
vbfaYrHRpFfk3OnBHNDlmzV0N8zVnGHrSgIkqA2tbFJ9ZMhT1AJavCrjBNeuiuMN
WVzpj+KY7lwbLI4xl3w+EUyqpnheVkNLuEit87EzIxiA2Sus1+XLFpPzE6O9pUN8
olN452ZYP5cjlApKxiEn0Bo0aEe7a+VpUja4/Z6dq3A7V42ci/HKNLwiI2n2iQQn
AiKM91Y3ir3FEcBg67CIbZdjxrrbwrzsMSfRi06INQIKTswMIlDR2A+2wVEtrDe5
kjqgVA1+bXfjlN2lfN9bp5KArXnvRLg+WA9Jj7SLB0I3QbiDgBqC7DS2ght0FxHp
4bY7hIL+kE8dc8vPs8C83WC8LYXxqFzi9vgZDk5kyqNTrQfLMz5YbPDiCQPUybJf
u9nNrqPmS+0BKOSr5o36
=orOZ
-END PGP SIGNATURE-


-- 
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] Foreign join pushdown vs EvalPlanQual

2015-07-05 Thread Kouhei Kaigai
  Also, I don't want to stick on the assumption that relations involved in
  remote join are all managed by same foreign-server no longer.
  The following two ideas introduce possible enhancement of remote join
  feature that involved local relations; replicated table or transformed
  to VALUES() clause.
 
 
 http://www.postgresql.org/message-id/CA+Tgmoai_VUF5h6qVLNLU+FKp0aeBCbnnMT3SC
 vL-HvOpBR=x...@mail.gmail.com
 
 http://www.postgresql.org/message-id/9A28C8860F777E439AA12E8AEA7694F8010F20A
 d...@bpxm15gp.gisp.nec.co.jp
 
 Interesting!
 
  I think add_paths_to_joinrel() is the best location for foreign-join,
  not only custom-join. Relocation to standard_join_search() has larger
  disadvantage than its advantage.
 
 I agree with you that it's important to ensure the expandability, and my
 question is, is it possible that the API call from standard_join_search
 also realize those idea if FDWs can get the join information through the
 root or something like that?

I don't deny its possibility, even though I once gave up to implement to
reproduce join information - which relations and other ones are joined in
this level - using PlannerInfo and RelOptInfo.
However, we need to pay attention on advantages towards the alternatives.
Hooks on add_paths_to_joinrel() enables to implement identical stuff, with
less complicated logic to reproduce left / right relations from RelOptInfo
of the joinrel. (Note that RelOptInfo-fdw_private enables to avoid path-
construction multiple times.)
I'm uncertain why this API change is necessary to fix up the problem
around EvalPlanQual.

Thanks,
--
NEC Business Creation Division / PG-Strom Project
KaiGai Kohei kai...@ak.jp.nec.com

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


[HACKERS] Inconsistent style in pgbench's error messages

2015-07-05 Thread Tom Lane
I made a pass over pgbench's error messages to try to make them meet
project style guidelines.  There was one rather large bit of inconsistency
that I didn't try to fix, though: something like half of the messages
prepend pgbench:  to the front, but the other half don't.  Worse yet,
some of the other half prepend other things like metacommand names, which
seems quite confusing if you're expecting the former convention.

I'd like to see that become consistent, but I am not sure which way we
want to jump.  Personally I think pgbench:  adds very little and so
I'd rather get rid of it, but I'm sure others feel differently.

Another issue here is that when dealing with custom scripts, IMO it would
be really helpful to provide the file name, line number, and perhaps line
text whenever a message is about an identifiable command.  That's mostly
not happening at present, except for the aforesaid inconsistent mentions
of metacommand names.  To make that happen, we'd need to agree on a
formatting convention for attaching the info.  I'd be inclined to put it
on a separate line, but beyond that have no specific thoughts.

Comments?

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] Inconsistent style in pgbench's error messages

2015-07-05 Thread Joshua D. Drake


On 07/05/2015 04:46 PM, Tom Lane wrote:

I made a pass over pgbench's error messages to try to make them meet
project style guidelines.  There was one rather large bit of inconsistency
that I didn't try to fix, though: something like half of the messages
prepend pgbench:  to the front, but the other half don't.  Worse yet,
some of the other half prepend other things like metacommand names, which
seems quite confusing if you're expecting the former convention.

I'd like to see that become consistent, but I am not sure which way we
want to jump.  Personally I think pgbench:  adds very little and so
I'd rather get rid of it, but I'm sure others feel differently.


I would agree that adding pgbench is useless. You know if you ware 
running pgbench. Heck if you don't know, the logs become rather obvious 
regardless.


JD





--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing I'm offended is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
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] raw output from copy

2015-07-05 Thread Pavel Stehule
2015-07-02 17:02 GMT+02:00 Tom Lane t...@sss.pgh.pa.us:

 Andrew Dunstan and...@dunslane.net writes:
  Does the COPY line protocol even support binary data?

 The protocol, per se, just transmits a byte stream.  There is a field
 in the CopyInResponse/CopyOutResponse messages that indicates whether
 a text or binary copy is being done.  One thing we'd have to consider
 is whether raw mode is sufficiently different from binary to justify
 an additional value for this field, and if so whether that constitutes
 a protocol break. sql/plpgsql_check_passive-9.6.sql


 IIRC, psql wouldn't really care; it just transfers the byte stream to or
 from the target file, regardless of text or binary mode.  But there might
 be other client libraries that are smarter and expect binary mode to
 mean the binary file format specified in the COPY reference page.  So
 there may be value in being explicit about raw mode in these messages.


The safe way is create new mode and propagate it on client. It should to
not break any current applications, because no one uses COPY RAW.



 A key point in all this is that people who need raw transfer probably
 need it in both directions, a point that your SELECT proposal cannot
 satisfy, but hacking COPY could.  So I lean towards the latter really.


yes, it has sense. I am not sure, if I'll have time to implement it in this
step, but I'll look on it.

regards

Pavel



 regards, tom lane



Re: [HACKERS] dblink: add polymorphic functions.

2015-07-05 Thread Joe Conway
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/05/2015 12:25 PM, Joe Conway wrote:
 On 02/22/2015 10:26 PM, Corey Huinker wrote:
 Changes in this patch: - added polymorphic versions of 
 dblink_fetch() - upped dblink version # to 1.2 because of new 
 functions - migration 1.1 - 1.2 - DocBook changes for dblink(), 
 dblink_get_result(), dblink_fetch()
 
 The previous patch was missing dblink--1.1--1.2.sql and 
 dblink--1.2.sql. I have added them, so it should apply cleanly
 against git master, but not done any actual review yet.

Looking at the argument handling logic in dblink_fetch() and
dblink_record_internal(), it has been messy for a while, but this
patch serves to make it even worse.

I wonder if it isn't better to just loop through all the args with
get_fn_expr_argtype() every time and then test for the exact signature
match? Another alternative might be to create a wrapper C function for
each variant SQL function, but that seems like it could also get
messy, especially with dblink_record_internal() since we would have to
deal with every variant of all the external facing callers.

Thoughts?


- -- 
Joe Conway
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.22 (GNU/Linux)

iQIcBAEBAgAGBQJVmdM8AAoJEDfy90M199hldowQAJ8F14qSJC9BIGdxKwRIDCS2
bzbWoynYhGiDmvffmD3e3MAUrFwh+zS3QIN5BcJVBrdLnzjZIgHFz83Z1iuH1HVw
39z0sSZVJ0C7DvPV6UiNoDpKnyAJQUu3+A5ebWKj5AYOA0AVunA7J1vSMfXAYThV
zQ6lYEpCOFq0owIyjUFpjvXUSZFs6AHEQC5wQ/UW+EXCZKl0OYQtSf8oxi8m4DFu
xRrjM+bO7LmOrBPa5fPvQOXHr5KJRjq9x7CfU+a8mJaJh4r1MmDRp7iLVxlMae0k
YVdmsLP9FOS+RhAdmmKHsTWiEJIFhffKWqcahBXGdOOWjzUVzih/LAL0BS2z44AU
ygVW/ORg5ua7Y4zxg4PUKbIkvhA+qRs0WUpuY+nYZoYAayP3VqsWP3VvMYb/e/Fb
nyws+/C1wC2aIQxgoF/+Whdfh4eTcFMSK9Qsc0pdMleDizz9O0qauHWzglo89x8X
t+s2zNnmZ0NtWd4PSTcMAcq59v288CoKDME+gjT7A6jthEbBnkx6SdEjh/NLhJba
dKKv99ZP0Rg5v8pFpQ/3TzdBB1UifUz3nd6ubeWNPjAdBbiW1FFD+I6booPmaxh0
EOYLBVkVgJFgaz0bWI5P6bDi55LDQlUQEfLRE3dxoNdffivNNfElOkXly4VShB4i
YlCc/0A1opGZb3iJwo4X
=8VAQ
-END PGP SIGNATURE-


-- 
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] C# reading result from a function

2015-07-05 Thread Ashutosh Bapat
This mail is probably appropriate for some C# mailing list.

I am not familiar with C#, but in the above code, I do not see where is
test_func() being called?

On Sat, Jul 4, 2015 at 1:44 PM, drunken manu...@hotmail.com wrote:

 Hi guys, I have a problem with a sipmle function in postgresql. The func()
 calculates two integers to a result and returns it. The result is in the
 DB,
 but not in the Console Line in C#. So for example when i have par1 = 2 and
 par2 = 5 I get an DB entry with 7 in the result row, but the ConsoleLine is
 empty.

 What is wrong here?

 BEGIN
Result := par1 + par2;
insert into  t1 (par1, par2, res) values (par1, par2, Result);
RETURN Result;
 END;$BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;
 ALTER FUNCTION test_func(integer, integer)
   OWNER TO Test;


 *
 c#:
 .

 DbParameter giveBack = cmd.CreateParameter();
 giveBack.DbType = DbType.Int32;
 giveBack.ParameterName = Result;
 giveBack.Direction = ParameterDirection.ReturnValue;
 cmd.Parameters.Add(giveBack);

 cmd.Parameters[par1].Value = 2;
 cmd.Parameters[par2].Value = 11;
 connection.Open();

 if (connection.State == System.Data.ConnectionState.Open) {
 Console.WriteLine(cmd.Parameters[par1].Value);
 Int32 result = cmd.ExecuteNonQuery();
 Console.WriteLine(Status: {0}: Result: {1}, result,
 cmd.Parameters[Result].Value);
 }

 Console.WriteLine for par1 returns 2. Console.Writline for Result returns
 nothing.



 --
 View this message in context:
 http://postgresql.nabble.com/C-reading-result-from-a-function-tp5856560.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




-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


Re: [HACKERS] Inconsistent style in pgbench's error messages

2015-07-05 Thread Fabien COELHO


Hello Tom,

Personally I think pgbench:  adds very little and so I'd rather get 
rid of it, but I'm sure others feel differently.


I think that one of the reason for this is that once pgbench started to 
run it is desirable to differentiate error messages that come from libpq 
and those that come from pgbench itsef.


Although for some messages this may be obvious, this may not always be the 
case.


For instance invalid variable name, such a message may come from a 
plpgsql function invoked.


Example from psql with a prefix:

  sh psql -p 12345678
  psql: invalid port number: 12345678

Another issue here is that when dealing with custom scripts, IMO it 
would be really helpful to provide the file name, line number, and 
perhaps line text whenever a message is about an identifiable command. 
That's mostly not happening at present, except for the aforesaid 
inconsistent mentions of metacommand names.  To make that happen, we'd 
need to agree on a formatting convention for attaching the info.  I'd be 
inclined to put it on a separate line, but beyond that have no specific 
thoughts.


Just to bootstap something, say:

  pgbench test.sql: error message...

Then maybe:

  LINE 3: content of the line if available...

I'm not sure that the line contents would necessarily be available, it may 
have been tokenized in some cases, if so it may have to be kept around.


I'm not sure how to deal with internal scripts. Maybe:

  pgbench TPC-B (sort-of): ...

--
Fabien.


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


Re: [HACKERS] Parallel Seq Scan

2015-07-05 Thread Amit Kapila
On Mon, Jul 6, 2015 at 3:26 AM, Jeff Davis pg...@j-davis.com wrote:

 On Fri, 2015-07-03 at 17:35 +0530, Amit Kapila wrote:

  Attached, find the rebased version of patch.
 

 Comments:


 * The heapam.c changes seem a little ad-hoc. Conceptually, which
 portions should be affected by parallelism? How do we know we didn't
 miss something?

The main reason of changing heapam.c is that we want to scan blocks
parallely by multiple workers and heapam.c seems to be the best
place to make such a change.  As of now, the changes  are mainly
required to identify the next block to scan by each worker.  So
we can focus on that aspect and see if anything is missing.

 * Why is initscan getting the number of blocks from the structure? Is it
 just to avoid an extra syscall, or is there a correctness issue there?

Yes, there is a correctness issue.  All the parallel workers should see
the same scan information during scan as is seen by master backend.
master backend fills this structure and then that is used by all workers
to avoid any problem.

 Is initscan expecting that heap_parallelscan_initialize is always called
 first (if parallel)? Please add a comment explaining above.

okay.

 * What's the difference between scan-rs_nblocks and
 scan-rs_parallel-phs_nblocks?

scan-rs_parallel-phs_nblocks is once initialized in master
backend and then propagated to all other worker backends and
then worker backends use that value to initialize scan-rs_nblocks
(and if master backend itself is involved in scan, then it also
uses it in same way)

 Same for rs_rd-rd_id and phs_relid.

This is also similar to phs_nblocks.  The basic idea is that parallel
heap scan descriptor is formed in master backend containing all the
necessary members that are required for performing the scan in master
as well as worker backends.  Once we initialize the parallel heap scan
descriptor, it is passed to all the worker backends and used by them
to scan the heap.

 * It might be good to separate out some fields which differ between the
 normal heap scan and the parallel heap scan. Perhaps put rs_ctup,
 rs_cblock, and rs_cbuf into a separate structure, which is always NULL
 during a parallel scan. That way we don't accidentally use a
 non-parallel field when doing a parallel scan.

Or the other way to look at it could be separate out fields which are
required for parallel scan which is done currently by forming a
separate structure ParallelHeapScanDescData.

 * Is there a reason that partial scans can't work with syncscan? It
 looks like you're not choosing the starting block in the same way, so it
 always starts at zero and never does syncscan.

The reason why partial scan can't be mixed with sync scan is that in
parallel
scan, it performs the scan of heap by synchronizing blocks (each parallel
worker
scans a block and then asks for a next block to scan) among parallel
workers.
Now if we try to make sync scans work along with it, the synchronization
among
parallel workers will go for a toss.  It might not be impossible to make
that
work in some way, but not sure if it is important enough for sync scans to
work
along with parallel scan.

 If we don't want to mix
 syncscan and partial scan, that's fine, but it should be more explicit.


makes sense to me, I think in initscan, we should mark syncscan
as false for parallel scan case.

 I'm trying to understand where tqueue.c fits in. It seems very closely
 tied to the Funnel operator, because any change to the way Funnel works
 would almost certainly require changes in tqueue.c.

tqueue.c is mainly designed to pass tuples between parallel workers
and currently it is used in Funnel operator to gather the tuples generated
by all the parallel workers.  I think we can use it for any other operator
which needs tuple communication among parallel workers.

 But tqueue is a
 generic name for the file, so something seems off. Either we should
 explicitly make it the supporting routines for the Funnel operator, or
 we should try to generalize it a little.


It has been designed to be generic way of communication for tuples,
but let me know if you have any specific suggestions.

 I still have quite a bit to look at, but this is a start.


Thanks for the review.


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


Re: [HACKERS] pg_archivecleanup, and backup filename to specify as an argument

2015-07-05 Thread Michael Paquier
On Mon, Jul 6, 2015 at 2:06 PM, Fujii Masao wrote:
 Barring any objection, I will remove the warning from the document.

Nuke it.
-- 
Michael


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


Re: [HACKERS] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

2015-07-05 Thread Amit Kapila
On Sun, Jul 5, 2015 at 1:18 PM, Heikki Linnakangas hlinn...@iki.fi wrote:

 On 07/04/2015 07:34 PM, Fabien COELHO wrote:

 I have ran some tests with this patch and the detailed results of the
 runs are attached with this mail.


 I do not understand really the aggregated figures in the files attached.


 Me neither. It looks like Amit measured the time spent in mdread and
 mdwrite, but I'm not sure what conclusions one can draw from that.


As Heikki has pointed, it is stats data for mdread and mdwrite
between the checkpoints (in the data, you need to search for
checkpoint start/checkpoint done).  In between checkpoint
start and checkpoint done, all the data shows the amount of read/
write done (I am just trying to reproduce what Digoal has reported, so
I am using his script and I also don't understand every thing, but I think
we can look at count between checkpoints to deduce whether the IO
is flattened after patch).  Digoal was seeing a spike at the beginning of
checkpoint (after checkpoint start) in his configuration without this patch
and the spike seems to be reduced after this patch where as in my tests
I don't see the spike immediately after checkpoint (although there are some
spikes in-between) even without patch which means that either I might not
be using the right configuration to measure the IO or there is some other
difference between the way Digoal ran the test and I ran the tests.  I have
done
the setup (even though hardware will not be same, but at least I can run the
tests and collect the data in the format similar to Digoal), so if you guys
have
suggestions about which kind of parameters we should tweek or some tests
to gather the results, I can do that present the results here for further
discussion.


  I thought the patch should show difference if I keep max_wal_size to
 somewhat lower or moderate value so that checkpoint should get triggered
 due to wal size, but I am not seeing any major difference in the writes
 spreading.


 I'm not sure I understand your point. I would say that at full speed
 pgbench the disk is always busy writing as much as possible, either
 checkpoint writes or wal writes, so the write load as such should not be
 that different anyway?

 I understood that the point of the patch is to check whether there is a
 tps dip or not when the checkpoint begins, but I'm not sure how this can
 be infered from the many aggregated data you sent, and from my recent
 tests the tps is very variable anyway on HDD.


Yes, we definitely want to see the effect on TPS at the beginning of
checkpoint,
but even measuring the IO during checkpoint with the way Digoal was
capturing
the data can show the effect of this patch.


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


Re: [HACKERS] Fix broken Install.bat when target directory contains a space

2015-07-05 Thread Michael Paquier
On Sat, Jul 4, 2015 at 3:57 AM, Heikki Linnakangas wrote:
 Hmm. Why is install.bat not like build.bat, i.e. just a thin wrapper that
 just calls install.pl, passing all arguments?

I guess we just haven't noticed it. And indeed it makes everything
more simple, and fixes as well the error reported when install path
contains a space.
-- 
Michael
diff --git a/src/tools/msvc/install.bat b/src/tools/msvc/install.bat
index bed08f1..d03277e 100644
--- a/src/tools/msvc/install.bat
+++ b/src/tools/msvc/install.bat
@@ -1,27 +1,6 @@
 @echo off
 REM src/tools/msvc/install.bat
-
-if NOT %1== GOTO RUN_INSTALL
-
-echo Invalid command line options.
-echo Usage: install.bat path
-echo.
-REM exit fix for pre-2003 shell especially if used on buildfarm
-if %XP_EXIT_FIX% == yes exit 1
-exit /b 1
-
-:RUN_INSTALL
-
-SETLOCAL
-
-IF NOT EXIST buildenv.pl goto nobuildenv
-perl -e require 'buildenv.pl'; while(($k,$v) = each %%ENV) { print qq[\@SET $k=$v\n]; }  bldenv.bat
-CALL bldenv.bat
-del bldenv.bat
-:nobuildenv
-
-perl install.pl %1 %2
-
-REM exit fix for pre-2003 shell especially if used on buildfarm
-if %XP_EXIT_FIX% == yes exit %ERRORLEVEL%
-exit /b %ERRORLEVEL%
+REM all the logic for this now belongs in install.pl. This file really
+REM only exists so you don't have to type perl install.pl
+REM Resist any temptation to add any logic here.
+@perl install.pl %*

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