Re: [HACKERS] procost for to_tsvector

[Tom Lane]

Andrew Gierth  writes:
> "Tom" == Tom Lane  writes:
>>> In the OP, he suggested "on the order of 100".  Maybe we could just
>>> go with 100.

>  Tom> I'm OK with that in view of <87h9trs0zm@news-spur.riddles.org.uk>

> Note that the results from that post suggest 100 as a bare minimum,
> higher values would be quite reasonable.

I'm not entirely convinced that your experiments disentangled the CPU cost
of to_tsvector itself from the costs of detoasting its input, which is an
issue that we ought to address separately.  In particular, comparing to
textlen() is unreliable for this purpose since in single-byte encodings
textlen() does not have to dereference a TOAST pointer at all.

It is possible to prove that to_tsvector() is much more expensive per-byte
than, say, md5():

regression=# select sum(length((repeat('xyzzy ', i from 
generate_series(1,1) i;
sum
---
 30003
(1 row)

Time: 360.423 ms
regression=# select sum(length(md5(repeat('xyzzy ', i from 
generate_series(1,1) i;
  sum   

 32
(1 row)

Time: 1339.806 ms
regression=# select sum(length(to_tsvector(repeat('xyzzy ', i from 
generate_series(1,1) i;
  sum  
---
 1
(1 row)

Time: 78564.333 ms

These numbers put md5() at about 3.3 nsec/input byte on my machine, and
to_tsvector() with the 'english' configuration at about 260 nsec/byte.
It's certainly possible that lots of repetitions of 'xyzzy ' isn't a very
representative sample of typical to_tsvector input; but at least this
test does not involve any toasted-value access.  So, as I said, I'm okay
with costing to_tsvector() at 100x the cost of md5().  I'm not convinced
that any factor above that is to_tsvector's fault.

>  Tom> and some experiments of my own, but I wonder why we are only
>  Tom> thinking of to_tsvector.  Isn't to_tsquery, for example, just
>  Tom> about as expensive?  What of other text search functions?

> Making the same change for to_tsquery and plainto_tsquery would be
> reasonable; that would help with the seqscan cost for cases like
> to_tsvector('config',col) @@ to_tsquery('blah') where the non-immutable
> form of to_tsquery is used.

Works for me.

> I don't recall seeing cases of any of the other functions figuring into
> planner decisions.

It's not so much "are they popular" as "do they involve parsing raw
text".  Once you've got the tsvector or tsquery, later steps are
(I think) much more efficient.

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] Auditing extension for PostgreSQL (Take 2)

[Peter Eisentraut]

On 4/30/15 6:05 AM, Fujii Masao wrote:
> The specification of "session audit logging" seems to be still unclear to me.

As I had mentioned previously, I would prefer session audit logging to
be integrated with the normal statement logging configuration.


-- 
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] feature freeze and beta schedule

[Amit Kapila]

On Fri, May 1, 2015 at 10:07 PM, Andres Freund  wrote:
>
> On 2015-04-30 08:39:45 -0400, Peter Eisentraut wrote:
> > If you have spare cycles, there are a number of relevant patches still
> > open in the commit fest.
>
> I was wondering what the actual state of the commitfest is. I'm thus
> going through all the open items.

Thanks.

> Here's my thoughts:
>
>
>
> * Parallel Seq scan
>   In my opinion the topic has progressed greatly. But at the same time
>   it doesn't seem like it's in a state we should consider for 9.5.
>   => Return?
>

I intend to work on this patch for next CF, so accordingly I have moved
it.


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

Re: [HACKERS] procost for to_tsvector

[Andrew Gierth]

> "Tom" == Tom Lane  writes:

 >> In the OP, he suggested "on the order of 100".  Maybe we could just
 >> go with 100.

 Tom> I'm OK with that in view of <87h9trs0zm@news-spur.riddles.org.uk>

Note that the results from that post suggest 100 as a bare minimum,
higher values would be quite reasonable.

 Tom> and some experiments of my own, but I wonder why we are only
 Tom> thinking of to_tsvector.  Isn't to_tsquery, for example, just
 Tom> about as expensive?  What of other text search functions?

Making the same change for to_tsquery and plainto_tsquery would be
reasonable; that would help with the seqscan cost for cases like
to_tsvector('config',col) @@ to_tsquery('blah') where the non-immutable
form of to_tsquery is used. It doesn't seem to have shown up as an issue
in reports so far because the common usage patterns don't tend to have
it evaluated for each row (either the immutable form is used, or the
to_tsquery is evaluated in a different from-clause item).

I don't recall seeing cases of any of the other functions figuring into
planner decisions.

-- 
Andrew (irc:RhodiumToad)


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

Re: [HACKERS] feature freeze and beta schedule

[Peter Geoghegan]

On Fri, May 1, 2015 at 5:27 PM, Andrew Gierth
 wrote:
> Also as I've pointed out, it's not even clear that there is a regression
> at all, since I've already shown that changes of several percent in
> timings of sort operations can be caused by irrelevant noise factors.
> To actually show a performance regression of less than 10% or so would
> require, at a minimum, showing two different timings using the same data
> and the same binary, though even that is subject to noise; to really
> prove it you'd have to show a statistically significant difference
> between sets of binaries with random padding sizes (see the graph I
> posted on this point).

I think the issue is somewhat confused by the fact that there was
performance investigation work done on the thread, and a regression
was investigated (a regression that has since been fixed). This was a
problem that had nothing in particular to do with the Datum tuplesort
abbreviation patch, though.

-- 
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] feature freeze and beta schedule

[Andrew Gierth]

> "Andres" == Andres Freund  writes:

 Andres> * Abbreviated key support for Datum sorts
 Andres>   Unfortunately the discussion about potential performance
 Andres>   regression has been largely sidestepped by bickering over
 Andres>   minutiae.
 Andres>   => ?

There isn't a "potential performance regression" that is in any respect
different from the already-committed changes for non-Datum sorts.

Also as I've pointed out, it's not even clear that there is a regression
at all, since I've already shown that changes of several percent in
timings of sort operations can be caused by irrelevant noise factors.
To actually show a performance regression of less than 10% or so would
require, at a minimum, showing two different timings using the same data
and the same binary, though even that is subject to noise; to really
prove it you'd have to show a statistically significant difference
between sets of binaries with random padding sizes (see the graph I
posted on this point).

-- 
Andrew (irc:RhodiumToad)


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

Re: [HACKERS] feature freeze and beta schedule

[Petr Jelinek]

On 01/05/15 18:37, Andres Freund wrote:

I was wondering what the actual state of the commitfest is. I'm thus
going through all the open items. Here's my thoughts:



Cool.


* Sequence Access Method
   There's been some back and forth between Petr and Heikki on this
   lately.
   => Maybe there's still a chance for 9.5?



I think it depends mainly on if we are happy with the proposed API or 
not (especially the storage API). AFAIK all the comments Heikki had were 
addressed, but public API like this can always use more eyes...




* mogrify and indent for jsonb
   Committer's patch marked as ready for commit.
   => Should get committed ;)



It's only partly a committer's patch but I agree that it's now in a 
state where it should be committed.




* Deparsing utility commands
   IIUc Alvaro intends to commit a minimal version soon.



Amit had few comments that need to be addressed but other than that the 
infrastructure part is definitely committable. The current approach is 
really good.


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


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

Re: [HACKERS] CTE optimization fence on the todo list?

[David G. Johnston]

On Fri, May 1, 2015 at 3:30 PM, Tom Lane  wrote:

> Assuming that that sketch is accurate, it would take more code to provide
> a new user-visible knob to enable/disable the behavior than it would to
> implement the optimization, which makes me pretty much -1 on providing
> such a knob.  We should either do it or not.  If we do, people who want
> optimization fences should use the traditional "OFFSET 0" hack.
>
> (A possible compromise position would be to offer a new GUC to
> enable/disable the optimization globally; that would add only a reasonably
> small amount of control code, and people who were afraid of the change
> breaking their apps would probably want a global disable anyway.)


​+1 to both.  The default should be to allow the user to choose between CTE
and inline subqueries for style reasons alone - as much as possible since
you cannot have a correlated CTE nor a recursive subquery.

Trust in the planner, the planner is good.  If it isn't then requiring
OFFSET 0 as the only means to create an optimization fence seems reasonable.

I like the GUC as an cheap means to keep the status-quo for those who
desire it.

While the idea of overriding the status-quo on a per-query basis has some
appeal the apparent cost-benefit ratio doesn't seem convincing.

David J.​

Re: [HACKERS] CTE optimization fence on the todo list?

[Josh Berkus]

On 05/01/2015 03:30 PM, Tom Lane wrote:
> Assuming that that sketch is accurate, it would take more code to provide
> a new user-visible knob to enable/disable the behavior than it would to
> implement the optimization, which makes me pretty much -1 on providing
> such a knob.  We should either do it or not.  If we do, people who want
> optimization fences should use the traditional "OFFSET 0" hack.

Yes.

> 
> (A possible compromise position would be to offer a new GUC to
> enable/disable the optimization globally; that would add only a reasonably
> small amount of control code, and people who were afraid of the change
> breaking their apps would probably want a global disable anyway.)

We'd need the GUC.  I know of a lot of cases where people are using WITH
clauses specifically to override the query planner, and requiring them
to edit all of their queries in order to enable the old behavior would
become an upgrade barrier.

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


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

Re: [HACKERS] Improving replay of XLOG_BTREE_VACUUM records

[Jim Nasby]

On 5/1/15 11:19 AM, Vladimir Borodin wrote:

There are situations in which vacuuming big btree index causes stuck in
WAL replaying on hot standby servers for quite a long time. I’ve
described the problem in more details in this thread [0]. Below in that
thread Kevin Grittner proposed a good way for improving btree scans so
that btree vacuuming logic could be seriously simplified. Since I don’t
know when that may happen I’ve done a patch that makes some improvement
right now. If Kevin or someone else would expand [1] for handling all
types of btree scans, I suppose, my patch could be thrown away and
vacuuming logic should be strongly rewritten.


This looks like a good way to address this until the more significant 
work can be done.


I'm not a fan of "RBM_ZERO_NO_BM_VALID"; how about RBM_ZERO_BM_INVALID? 
or BM_NOT_VALID? Or maybe I'm just trying to impose too much English on 
the code; I see the logic to NO_BM_VALID...


+ * RBM_ZERO_NO_BM_VALID is the same as RBM_ZERO_AND_LOCK, but does not set
+ * BM_VALID bit before returning buffer so that noone could pin it.

It would be better to explain why we want that mode. How about:

RBM_ZERO_NO_BM_VALID is the same as RBM_ZERO_AND_LOCK but does not set 
BM_VALID before returning the buffer. This is done to ensure that no one 
can pin the buffer without actually reading the buffer contents in. This 
is necessary while replying XLOG_BTREE_VACUUM records in hot standby.


+   if (mode == RBM_ZERO_NO_BM_VALID)
+   TerminateBufferIO(bufHdr, false, 0);
+   else
+   TerminateBufferIO(bufHdr, false, BM_VALID);

Simply passing in a 0 seems a bit odd to me; is there anywhere else we 
do that?

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


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

Re: [HACKERS] CTE optimization fence on the todo list?

[David Steele]

On 5/1/15 6:32 PM, Peter Geoghegan wrote:
> On Fri, May 1, 2015 at 3:30 PM, Tom Lane  wrote:
>> Assuming that that sketch is accurate, it would take more code to provide
>> a new user-visible knob to enable/disable the behavior than it would to
>> implement the optimization, which makes me pretty much -1 on providing
>> such a knob.  We should either do it or not.  If we do, people who want
>> optimization fences should use the traditional "OFFSET 0" hack.
> 
> +1

Not sure if I'm thrilled with the "OFFSET 0" hack but I guess it's not
much different from the CTE hack I've been using.

An "enable_cte_optimization" GUC would serve to keep old code from
breaking while giving new users/queries the advantage of optimization.

I'm not sure it's worth adding the complexity, though.  In my experience
not that many developers use CTEs.

-- 
- David Steele
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] Reducing tuple overhead

[Jim Nasby]

On 4/30/15 7:37 AM, Robert Haas wrote:

On Thu, Apr 30, 2015 at 8:05 AM, Simon Riggs  wrote:

A much better idea is to work out how to avoid index bloat at cause. If we
are running an UPDATE and we cannot get a cleanup lock, we give up and do a
non-HOT update, causing the index to bloat. It seems better to wait for a
short period to see if we can get the cleanup lock. The short period is
currently 0, so lets start there and vary the duration of wait upwards
proportionally as the index gets more bloated.


That only happens if there already wasn't enough space on the page so we 
need to Defrag, yes? If there is enough space we can HOT update without 
the cleanup lock.


What would be useful to know is how often we abort a HOT update because 
of lack of free space; that would indicate to a DBA that a lower fill 
factor may be in oredr. What would be useful to -hackers would be stats 
on how often an update would have been HOT if only the page had been pruned.



What I'd be worried about there is that it would be very hard to tune
the wait time, and that the operating system scheduling granularity
(10ms?) would be way too long.


[1] indicates between 0.75 and 6ms by default on Linux. I think FBSD 
still uses a 1000Hz scheduler (1ms), but it's not as clear.


What might be more promising is ways to avoid holding a pin for a long 
time (like the outer side of a nested loop), or being more aggressive 
about attempting the lock (IE: lower the threshold to trigger cleaning).


There's also a (in hindsight) questionable bit of logic in 
heap_page_prune_opt(); once we get the cleanup lock we check the page 
free space a second time. If we managed to actually get the lock, we 
should probably just clean it anyway.



But I'm in vigorous agreement with you on one point: the solution to
index bloat (and probably heap bloat, too) is not to clean it up
faster but to create less of it in the first place.  Making more
updates HOT is one way to do that.


+1.


1: 
http://stackoverflow.com/questions/16401294/how-to-know-linux-scheduler-time-slice

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


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

Re: [HACKERS] CTE optimization fence on the todo list?

[Peter Geoghegan]

On Fri, May 1, 2015 at 3:30 PM, Tom Lane  wrote:
> Assuming that that sketch is accurate, it would take more code to provide
> a new user-visible knob to enable/disable the behavior than it would to
> implement the optimization, which makes me pretty much -1 on providing
> such a knob.  We should either do it or not.  If we do, people who want
> optimization fences should use the traditional "OFFSET 0" hack.

+1


-- 
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] CTE optimization fence on the todo list?

[Tom Lane]

David Steele  writes:
> On 5/1/15 5:39 PM, Tom Lane wrote:
>> I doubt that the spec says anything about it one way or another.
>> However, there are a lot of cases where we definitely can't push
>> constraints into a WITH:
>> * Data-modifying query in the WITH, eg UPDATE RETURNING --- pushing
>> outer constraints into it would change the set of rows updated.
>> * Multiply-referenced WITH item (unless the outer query applies
>> identical constraints to each reference, which seems silly and not
>> worth the cycles to check for).
>> * Recursive WITH item (well, maybe in some cases you could push down a
>> clause and not change the results, but it seems very hard to analyze).
>> 
>> So initially we just punted and didn't consider flattening WITHs at
>> all.  I'm not sure to what extent people are now expecting that behavior
>> and would be annoyed if we changed it.

> I use CTEs for both organizational purposes and as optimization barriers
> (in preference to using temp tables, when possible).

> I'd definitely prefer to keep the barriers in place by default, perhaps
> with a keyword to allow optimization across boundaries when appropriate.
>  However, when I really need optimization across boundaries I just use a
> subquery.

FWIW, a bit of thought suggests that it would not take a lot of code to
handle this: you'd just have to check the conditions mentioned above
and then convert the RTE_CTE item into an RTE_SUBQUERY, much like
inline_set_returning_functions does with RTE_FUNCTION items.

Assuming that that sketch is accurate, it would take more code to provide
a new user-visible knob to enable/disable the behavior than it would to
implement the optimization, which makes me pretty much -1 on providing
such a knob.  We should either do it or not.  If we do, people who want
optimization fences should use the traditional "OFFSET 0" hack.

(A possible compromise position would be to offer a new GUC to
enable/disable the optimization globally; that would add only a reasonably
small amount of control code, and people who were afraid of the change
breaking their apps would probably want a global disable anyway.)

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] CTE optimization fence on the todo list?

[David Steele]

On 5/1/15 5:39 PM, Tom Lane wrote:
> Jim Nasby  writes:
>> On 4/30/15 6:35 AM, Robert Haas wrote:
>>> On Thu, Apr 30, 2015 at 12:44 AM, Chris Rogers  wrote:
 I could really use the ability to optimize across CTE boundaries, and it
 seems like a lot of other people could too.
> 
>>> I'm not aware that anyone is working on it.
> 
>> ISTR a comment to the effect of the SQL standard effectively requires 
>> current behavior.
> 
> I doubt that the spec says anything about it one way or another.
> However, there are a lot of cases where we definitely can't push
> constraints into a WITH:
> * Data-modifying query in the WITH, eg UPDATE RETURNING --- pushing
> outer constraints into it would change the set of rows updated.
> * Multiply-referenced WITH item (unless the outer query applies
> identical constraints to each reference, which seems silly and not
> worth the cycles to check for).
> * Recursive WITH item (well, maybe in some cases you could push down a
> clause and not change the results, but it seems very hard to analyze).
> 
> So initially we just punted and didn't consider flattening WITHs at
> all.  I'm not sure to what extent people are now expecting that behavior
> and would be annoyed if we changed it.

I use CTEs for both organizational purposes and as optimization barriers
(in preference to using temp tables, when possible).

I'd definitely prefer to keep the barriers in place by default, perhaps
with a keyword to allow optimization across boundaries when appropriate.
 However, when I really need optimization across boundaries I just use a
subquery.

It doesn't seem like there's much to be gained in terms of net
functionality.

-- 
- David Steele
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] CTE optimization fence on the todo list?

[Peter Geoghegan]

On Fri, May 1, 2015 at 2:36 PM, Robert Haas  wrote:
> On Fri, May 1, 2015 at 4:53 PM, Jim Nasby  wrote:
>> ISTR a comment to the effect of the SQL standard effectively requires
>> current behavior.
>
> I'd be astonished.  The SQL standard doesn't even know that there is
> such a thing as an index, so I presume it doesn't dictate the behavior
> of the query planner either.

I agree. Somehow, the idea that this is within the standard caught on,
but I'm almost certain it's false.


-- 
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] CTE optimization fence on the todo list?

[Tom Lane]

Jim Nasby  writes:
> On 4/30/15 6:35 AM, Robert Haas wrote:
>> On Thu, Apr 30, 2015 at 12:44 AM, Chris Rogers  wrote:
>>> I could really use the ability to optimize across CTE boundaries, and it
>>> seems like a lot of other people could too.

>> I'm not aware that anyone is working on it.

> ISTR a comment to the effect of the SQL standard effectively requires 
> current behavior.

I doubt that the spec says anything about it one way or another.
However, there are a lot of cases where we definitely can't push
constraints into a WITH:
* Data-modifying query in the WITH, eg UPDATE RETURNING --- pushing
outer constraints into it would change the set of rows updated.
* Multiply-referenced WITH item (unless the outer query applies
identical constraints to each reference, which seems silly and not
worth the cycles to check for).
* Recursive WITH item (well, maybe in some cases you could push down a
clause and not change the results, but it seems very hard to analyze).

So initially we just punted and didn't consider flattening WITHs at
all.  I'm not sure to what extent people are now expecting that behavior
and would be annoyed if we changed it.

regards, tom lane


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

Re: [HACKERS] CTE optimization fence on the todo list?

[Robert Haas]

On Fri, May 1, 2015 at 4:53 PM, Jim Nasby  wrote:
> ISTR a comment to the effect of the SQL standard effectively requires
> current behavior.

I'd be astonished.  The SQL standard doesn't even know that there is
such a thing as an index, so I presume it doesn't dictate the behavior
of the query planner either.

-- 
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] Broken handling of NULLs in TG_ARGV

[Jim Nasby]

On 4/30/15 6:44 PM, Tom Lane wrote:

There isn't any such thing as a genuine SQL NULL argument; the examples
you provided are just text strings, not SQL NULLs.  In order to make them
be actual nulls, we would have to redefine the arguments as being
expressions of some sort, which is problematic for backwards-compatibility
reasons.  It also seems like rather a lot of new mechanism to add for
something with (evidently) near-zero user demand.


Ahh, I thought the array started life as an actual array, not char **. 
So yeah, not nearly as easy to fix. :(

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


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

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

[Peter Geoghegan]

On Fri, May 1, 2015 at 7:47 AM, Heikki Linnakangas  wrote:
> Hmm, so it was stuck for half an hour at that point? Why do you think it was
> a livelock?
>
>> This is the same server that I shared credentials with you for. Feel
>> free to ssh in and investigate it yourself.
>
>
> I logged in, but the system seems very unresponsive in general. I just
> started "apt-get install gdb" on it, to investigate what the backends are
> stuck at. It's been running for about 30 minutes now, and I'm still
> waiting...

To be honest, I just assumed it was a livelock - maybe I should have
avoided the word. It's possible that there was an issue on the AWS
instance, too. It was disconcerting how each run of the test could
take 4 seconds or 30 seconds, with no particular pattern to it. This
was something I saw consistently.

-- 
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] Loss of some parts of the function definition

[Jim Nasby]

On 4/30/15 6:44 AM, Sergey Grinko wrote:

Now create a script in the application of its function parameters and
return values can be declared using %TYPE.
However, when you save the script is stored inside the server only what
is considered his body. Thus, we obtain:

...

We actually mung things a lot worse when it comes to views, so I'm 
curious why you're only worried about the problems with stored functions?


FWIW, I think the best 'solution' to this right now is to actually keep 
your original definitions as files in your VCS and use something like 
sqitch for deployment. Taken to it's logical extreme, that means that 
the only thing you ever 'patch' is an actual table (via ALTER TABLE), or 
indexes. Everything else essentially gets treated like regular code.


That's still not terribly satisfying since unlike other forms of 
software you now have all that definition both in your VCS and the 
database itself, but ISTM that's a much bigger problem than the small 
amount of info we lose from stored functions...

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


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

Re: [HACKERS] CTE optimization fence on the todo list?

[Jim Nasby]

On 4/30/15 6:35 AM, Robert Haas wrote:

On Thu, Apr 30, 2015 at 12:44 AM, Chris Rogers  wrote:

Has there been any movement on this in the last couple years?

I could really use the ability to optimize across CTE boundaries, and it
seems like a lot of other people could too.


I'm not aware that anyone is working on it.


ISTR a comment to the effect of the SQL standard effectively requires 
current behavior.


I'd still love to see a way around that though, even if it means some 
kind of additional syntax; WITH is a lot nicer way to factor a query 
than 10 nested subselects...

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


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

Re: [HACKERS] Manipulating complex types as non-contiguous structures in-memory

[Tom Lane]

Pavel Stehule  writes:
> Test for 3000 elements:

>Original Patch
> Integer55sec  8sec
> Numeric   341sec  8sec

> Quicksort is about 3x faster -- so a benefit of this patch is clear.

Yeah, the patch should pretty much blow the doors off any case that's
heavily dependent on access or update of individual array elements ...
especially for arrays with variable-length element type, such as numeric.

What I'm concerned about is that it could make things *slower* for
scenarios where that isn't the main thing being done with the arrays,
as a result of useless conversions between "flat" and "expanded"
array formats.  So what we need is to try to benchmark some cases
that don't involve single-element operations but rather whole-array
operations (on arrays that are plpgsql variables), and see if those
cases have gotten noticeably worse.

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] Manipulating complex types as non-contiguous structures in-memory

[Pavel Stehule]

2015-05-01 20:53 GMT+02:00 Pavel Stehule :

>
>
> 2015-05-01 20:11 GMT+02:00 Tom Lane :
>
>> Pavel Stehule  writes:
>> > I am looking on this patch, but it cannot be applied now.
>>
>> > lxml2 -lssl -lcrypto -lrt -lcrypt -ldl -lm -o postgres
>> > utils/fmgrtab.o:(.rodata+0x2678): undefined reference to `array_append'
>> > utils/fmgrtab.o:(.rodata+0x2698): undefined reference to `array_prepend'
>>
>> What are you trying to apply it to?  I see array_append() in
>> src/backend/utils/adt/array_userfuncs.c in HEAD.  Also, are
>> you checking the 1.1 version of the patch?
>>
>
> I tested old version. 1.1. looks well.
>

It is hard to believe how it is fast

I use buble sort for plpgsql benchmarking. Following variant is suboptimal
(but it is perfect for this test)

CREATE OR REPLACE FUNCTION public.buble(a anyarray, OUT r anyarray)
 RETURNS anyarray
 LANGUAGE plpgsql
AS $function$
DECLARE
  aux r%type;
  sorted bool := false;
BEGIN
  r := a;
  WHILE NOT sorted
  LOOP
sorted := true;
FOR i IN array_lower(a,1) .. array_upper(a,1) - 1
LOOP
  IF r[i] > r[i+1] THEN
sorted := false;
aux[1] := r[i];
r[i] := r[i+1]; r[i+1] := aux[1];
  END IF;
END LOOP;
  END LOOP;
END;
$function$

CREATE OR REPLACE FUNCTION public.array_generator(integer, anyelement, OUT
r anyarray)
 RETURNS anyarray
 LANGUAGE plpgsql
AS $function$
BEGIN
  r := (SELECT ARRAY(SELECT random()*$2 FROM generate_series(1,$1)));
END;
$function$

Test for 3000 elements:

   Original Patch
Integer55sec  8sec
Numeric341sec  8sec

Quicksort is about 3x faster -- so a benefit of this patch is clear.

Regards

Pavel


>
> Regards
>
> Pavel
>
>
>>
>> regards, tom lane
>>
>
>

Re: [HACKERS] Manipulating complex types as non-contiguous structures in-memory

[Pavel Stehule]

2015-05-01 20:11 GMT+02:00 Tom Lane :

> Pavel Stehule  writes:
> > I am looking on this patch, but it cannot be applied now.
>
> > lxml2 -lssl -lcrypto -lrt -lcrypt -ldl -lm -o postgres
> > utils/fmgrtab.o:(.rodata+0x2678): undefined reference to `array_append'
> > utils/fmgrtab.o:(.rodata+0x2698): undefined reference to `array_prepend'
>
> What are you trying to apply it to?  I see array_append() in
> src/backend/utils/adt/array_userfuncs.c in HEAD.  Also, are
> you checking the 1.1 version of the patch?
>

I tested old version. 1.1. looks well.

Regards

Pavel


>
> regards, tom lane
>

Re: [HACKERS] Implementing SQL ASSERTION

[Robert Haas]

On Thu, Apr 30, 2015 at 6:36 PM, Joe Wildish
 wrote:
> I’m wondering if there are other people out there working on implementing SQL 
> ASSERTION functionality?
>
> I’ve recently spent a bit of time looking to implement the execution models 
> described in “Applied Mathematics for Database Professionals” by Toon 
> Koppelaars and Lex de Haan.  I’ve gotten as far as execution model 3 and am 
> now looking at deriving polarity of involved tables to do EM4 (described in 
> some detail in “Deriving Production Rules for Constraint Maintenance”, Ceri & 
> Widom, VLDB Conference 1990, p555-577). EM5 & EM6 look rather more difficult 
> but I’m intending to try and implement those, too.
>
> If there are other people working on this stuff it would be great to 
> collaborate.

I don't know of anyone working on this.  It sounds very difficult.

-- 
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] Manipulating complex types as non-contiguous structures in-memory

[Tom Lane]

Andres Freund  writes:
> On 2015-05-01 11:11:14 -0700, Tom Lane wrote:
>> What are you trying to apply it to?  I see array_append() in
>> src/backend/utils/adt/array_userfuncs.c in HEAD.  Also, are
>> you checking the 1.1 version of the patch?

> That's very likely due to the transforms patch, with added another
> column to pg_proc...

No, my patch doesn't touch pg_proc.h.  I'm certainly prepared to believe
it's suffered bit rot in the last couple of weeks, but I don't understand
how it would apply successfully and then generate a complaint about
array_append not being there.  array_append *is* there in HEAD, and has
been for awhile.

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

[Tom Lane]

Bernd Helmle  writes:
> While looking into a customer performance problem, i saw this in
> costsize.c, cost_index() (9.3.6, but it looks the same in HEAD):
> ...
> What i'm wondering is the else branch, where the baserel row estimate is
> assigned to the
> IndexPath. However, it occurs to me that in conjunction with a partial
> index, the overall row estimate will be constrained by the row estimate
> from the partial index itself, no?

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

What could potentially make sense is to detect applicability of partial or
unique indexes earlier than we do now, and use that knowledge to adjust
the relation's rows estimate overall, for all paths.  But I'm not sure
how to do that without either (a) making the code a lot messier than it
is now or (b) duplicating a lot of work or (c) both.

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] Manipulating complex types as non-contiguous structures in-memory

[Andres Freund]

On 2015-05-01 11:11:14 -0700, Tom Lane wrote:
> Pavel Stehule  writes:
> > I am looking on this patch, but it cannot be applied now.
> 
> > lxml2 -lssl -lcrypto -lrt -lcrypt -ldl -lm -o postgres
> > utils/fmgrtab.o:(.rodata+0x2678): undefined reference to `array_append'
> > utils/fmgrtab.o:(.rodata+0x2698): undefined reference to `array_prepend'
> 
> What are you trying to apply it to?  I see array_append() in
> src/backend/utils/adt/array_userfuncs.c in HEAD.  Also, are
> you checking the 1.1 version of the patch?

That's very likely due to the transforms patch, with added another
column to pg_proc...

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] Manipulating complex types as non-contiguous structures in-memory

[Tom Lane]

Pavel Stehule  writes:
> I am looking on this patch, but it cannot be applied now.

> lxml2 -lssl -lcrypto -lrt -lcrypt -ldl -lm -o postgres
> utils/fmgrtab.o:(.rodata+0x2678): undefined reference to `array_append'
> utils/fmgrtab.o:(.rodata+0x2698): undefined reference to `array_prepend'

What are you trying to apply it to?  I see array_append() in
src/backend/utils/adt/array_userfuncs.c in HEAD.  Also, are
you checking the 1.1 version of the patch?

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] procost for to_tsvector

[Tom Lane]

Robert Haas  writes:
> In the OP, he suggested "on the order of 100".  Maybe we could just go with 
> 100.

I'm OK with that in view of <87h9trs0zm@news-spur.riddles.org.uk> and
some experiments of my own, but I wonder why we are only thinking of
to_tsvector.  Isn't to_tsquery, for example, just about as expensive?
What of other text search functions?

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] feature freeze and beta schedule

[Kevin Grittner]

Stephen Frost  wrote:
> Andres Freund (and...@anarazel.de) wrote:

>> * Allow "snapshot too old" error, to prevent bloat
>> http://archives.postgresql.org/message-id/1361166406.1897609.1424371443904.JavaMail.yahoo%40mail.yahoo.com
>>  talked about a new version that afaics never materialized
>>  => Returned with feedback
>
> As mentioned earlier, this is a committer's patch and if Kevin
> shows up with a new patch based on that discussion which others
> can live with, then I'm for having the capability over not.  As
> such, not sure if RFW is the right state for it to be in at this
> point.

Unfortunately, other emergencies have cut into my time for
finishing this for 9.5, and it seems more appropriate for a "start
of release cycle" patch than a "just before beta" patch; so look
for that in the first CF for the next release.  (Of course, if
someone wants to run with it and wants to argue for 9.5, I'll do
what I can to ensure it is solid.)

The current status, as I see it, are that there are two things that
need to be done:

(1)  The GUC needs to be changed from number of transactions to
time-based.  This is partly done, and could probably be finished in
a couple full-time days.

(2)  Every index AM needs to insert a TestForOldSnapshot() call
after each BufferGetPage() call which is used for a search or scan.
(Those used to position for inserts or other internal purposes
don't need this treatment.)  The btree AM is already done, but no
other AM has been started.

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


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

Re: [HACKERS] Manipulating complex types as non-contiguous structures in-memory

[Pavel Stehule]

2015-05-01 18:35 GMT+02:00 Tom Lane :

> Andres Freund  writes:
> > On 2015-03-28 17:24:36 -0400, Tom Lane wrote:
> >> This is overdue for a rebase; attached.  No functional changes, but some
> >> of what was in the original patch has already been merged, and other
> parts
> >> were superseded.
>
> > What are your plans with this WRT 9.5?
>
> I'd like to get it committed into 9.5.  I've been hoping somebody would do
> a performance review.
>

I am looking on this patch, but it cannot be applied now.

lxml2 -lssl -lcrypto -lrt -lcrypt -ldl -lm -o postgres
utils/fmgrtab.o:(.rodata+0x2678): undefined reference to `array_append'
utils/fmgrtab.o:(.rodata+0x2698): undefined reference to `array_prepend'
collect2: error: ld returned 1 exit status
Makefile:57: recipe for target 'postgres' failed
make[2]: *** [postgres] Error 1
make[2]: Leaving directory '/home/pavel/src/postgresql/src/backend'
Makefile:34: recipe for target 'all-backend-recurse' failed
make[1]: *** [all-backend-recurse] Error 2
make[1]: Leaving directory '/home/pavel/src/postgresql/src'
GNUmakefile:11: recipe for target 'all-src-recurse' failed


Regards

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] Help needed for PL/Ruby

[Szymon Guz]

On 29 April 2015 at 21:45, Szymon Guz  wrote:

> Hi Devrim,
> I will take a look at this.
>
> regards,
> Szymon
>
> On 29 April 2015 at 18:24, Devrim Gündüz  wrote:
>
>>
>> Hi,
>>
>> Anyone? :)
>>
>> Regards, Devrim
>>
>> On Wed, 2015-03-18 at 15:19 +0200, Devrim Gündüz wrote:
>> > Hi,
>> >
>> > Background info first: PL/Ruby was originally maintained by Guy Decoux,
>> > who passed away in 2008: https://www.ruby-forum.com/topic/166658 .
>> After
>> > his death, Akinori MUSHA forked the project and maintained it until
>> > 2010: https://github.com/knu/postgresql-plruby . Last release was on
>> Jan
>> > 2010, and recent distros started throwing build errors.
>> >
>> > I was having similar build issues while trying to RPMify PL/Ruby, and
>> > finally stepped up the plate and tried to fix those build issues by
>> > forking the project:
>> >
>> > https://github.com/devrimgunduz/postgresql-plruby/
>> >
>> > I mainly applied patches from Fedora, and also did some basic cleanup.
>> > However, I don't know Ruby and have limited C skills, so I need some
>> > help on these:
>> >
>> > * Complete the extension support: I committed initial infrastructure for
>> > it, but I don't think it is ready yet.
>> >
>> > * Fix the FIXME:
>> >
>> https://github.com/devrimgunduz/postgresql-plruby/blob/master/src/plpl.c#L844
>> >
>> > * Documentation review and update: The recent example is against
>> > PostgreSQL 8.0. A recent Ruby example would be good, and also we need
>> > updates for creating the language.
>> >
>> > Any contributions are welcome. I recently released 0.5.5 that at least
>> > is ready for testing.
>> >
>> > I want to remind that I am not a Ruby guy, so this is really a community
>> > stuff for me.
>> >
>> > Thanks by now.
>> >
>> > Regards,
>>
>>
>> --
>> Devrim GÜNDÜZ
>> Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
>> PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
>> Twitter: @DevrimGunduz , @DevrimGunduzTR
>>
>>
>
>

Hi Devrim,
I checked the code, and it seems like there is a lot of work to make this
plruby stuff working with the new postgres.

I think we need to get rid of the code for supporting the older postgres
versions than 8.4. Or even 9.0 (if there is any code for that) as this
version get obsolete in September this year, and I'm sure that I'm alone
will not be able to make all the changes till that time.

Compiling the code gives me lots of warnings, which also should be cleared.

The previous release was made long time ago, so it is not up to date with
the last postgres changes - a lot of work too.

So, I will work on it, however fixing and updating the code will take some
time.

Oh, and documentation of course.

At this moment I will do all the work on my github account.

regards,
Szymon

Re: [HACKERS] pg_xlog_replay_resume() considered armed and dangerous

[Bruce Momjian]

On Thu, Mar 12, 2015 at 04:08:02PM +0100, Andres Freund wrote:
> Hi,
> 
> I think it's quite confusing that a function named
> pg_xlog_replay_resume() can cause a node to be promoted.
> 
> That this is happened is kind of documented in the recovery.conf section
> of the manual:
> "The intended use of the pause setting is to allow queries to be executed
> against the database to check if this recovery target is the most
> desirable point for recovery. The paused state can be resumed by using
> pg_xlog_replay_resume() (see Table 9-69), which then causes recovery to
> end. If this recovery target is not the desired stopping point, then
> shut down the server, change the recovery target settings to a later
> target and restart to continue recovery."
> 
> But it's not mentioned at all in the section about the functions:
> http://www.postgresql.org/docs/devel/static/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE
> 
> Promotion only happens when a node is paused due to a recovery target
> setting, and not when it's stopped due to pg_xlog_replay_pause().
> 
> I think this, at the very least, needs a big caveat in the documentation
> of the resume function. But a different API would probably be
> better. I'd actually argue that for now pg_xlog_replay_resume() should
> refuse to work if paused due to a recovery target. Promotion should be
> done via the normal promotion methods.

Where are we on this?

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

  + Everyone has their own god. +


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

Re: [HACKERS] feature freeze and beta schedule

[Kevin Grittner]

 



- Original Message -
From: Stephen Frost 
To: Andres Freund 
Cc: Peter Eisentraut ; pgsql-hackers 

Sent: Friday, May 1, 2015 12:16 PM
Subject: Re: [HACKERS] feature freeze and beta schedule

Andres,

* Andres Freund (and...@anarazel.de) wrote:
> On 2015-04-30 08:39:45 -0400, Peter Eisentraut wrote:
> > If you have spare cycles, there are a number of relevant patches still
> > open in the commit fest.
> 
> I was wondering what the actual state of the commitfest is. I'm thus
> going through all the open items. Here's my thoughts:

Thanks!

> * fsync $PGDATA recursively at startup
>   Bugfix, i.e. not really tied to CF

We really need to segregate the two..  By that what I mean is this: I
want an "always-open" "bugfix" CF, which allows us to keep track of
bugfix patches.  Having something about "applies to versions X, Y, Z"
would be nice too...

/me prods Magnus

> * Async execution of postgres_fdw.
> 
>   Later iterations of the patch haven't gotten much review yet. The
>   original version of the patch is just from 2014-12-15.
>   => Should imo be moved to the next CF

I'd love to see this happen, but I haven't got cycles to spend on it
between now and feature freeze. :/

> * INNER JOIN removals
>   Seem far to controversial to consider comitting in 9.5.
>   => Returned (or even rejected :()

I'd really like to see this too, but I agree it's not something for 9.5
at this point.

> * Aggregate State Combine Support
>   I think we pretty clearly need something roughly like this. It seems
>   equally clear that this isn't going to happen in 9.5
>   => Returned with feedback

Agreed.

> * Allow "snapshot too old" error, to prevent bloat
>  
> http://archives.postgresql.org/message-id/1361166406.1897609.1424371443904.JavaMail.yahoo%40mail.yahoo.com
>   talked about a new version that afaics never materialized
>   => Returned with feedback

As mentioned earlier, this is a committer's patch and if Kevin shows up
with a new patch based on that discussion which others can live with,
then I'm for having the capability over not.  As such, not sure if RFW
is the right state for it to be in at this point.

> * Sending WAL receiver feedback regularly even if the receiver is under
>   heavy load
>   Imo more of a bugfix than a feature. I.e. doesn't really concern the
>   CF scheduling.

Another one for that "bugfix" CF...

> * Auditing extension
>   I'm unclear on the status here. Abhijit said he'll have a look.

It was recently set to ready-for-committer by Sawada (he and David have
been going back and forth with testing, fixing, etc).  Fujii took a look
and had a few comments which David responded to.  As is probably
obvious, it's certainly something that I'd like to see happen as it's an
oft-requested feature.  Certainly would be great if Abhijit could look
at it.

> * Parallel Seq scan
>   In my opinion the topic has progressed greatly. But at the same time
>   it doesn't seem like it's in a state we should consider for 9.5.
>   => Return?

I'd certainly love to see it happen but I've not been following the
recent discussion and so I'm not really sure if it's ready or not.

> * RLS: row-level security, more review
>   More of a placeholder item. There seem to various open items.

I've addressed a few of them lately..  If there are more then I've lost
track of what they are and would certainly welcome folks reminding me.
In particular, the issue from Craig about the misleading error has been
addressed and I believe we've worked out the RLS+INSERT ... ON CONFLICT
questions.

> * Deparsing utility commands
>   IIUc Alvaro intends to commit a minimal version soon.

+1

> * INSERT ... ON CONFLICT {UPDATE | IGNORE}
>   Heikki, Peter and I have spent a fair amount of time on this. I believe
>   we can commit it early next week.

Yay!

> * Additional role attributes
>   I agree with Robert's point in
>  
> http://archives.postgresql.org/message-id/CA%2BTgmobH4tdccajn7VmPT-1RqBdzLYcAz5jUz4bJ%3Drkqs_gADA%40mail.gmail.com
>   and thus think that this patch isn't ready for 9.5.

I agree it needs more discussion and am planning on posting both a much
simpler patch (which removes the complicated changes to pg_dump) and
putting it on the new thread to get some real discussion about it.
Robert's point that these changes need discussion is certainly spot on
and that may lead to it not getting into 9.5, but I'm not quite ready to
punt on it yet given that we've been working on it for going on 6(?)
months now.

> * catalog view to pg_hba.conf file
>   Greg is marked as a comitter here.

I'm happy to help with this also.

> * pg_file_settings view: To know detail of config files via SQL
>   Seems to be ready.

I'm looking at this too.


> * Add pg_settings.pending_restart column
>   Looks like it coudl quickly be committed.

No objection here.

Thanks!


Stephen 


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

Re: [HACKERS] recovery_target_action = pause & hot_standby = off

[Bruce Momjian]

On Mon, Mar 16, 2015 at 11:39:26AM +0100, Andres Freund wrote:
> On 2015-03-16 07:52:20 +, Simon Riggs wrote:
> > On 15 March 2015 at 22:38, Andres Freund  wrote:
> > 
> > > Sorry, I don't buy this. If I have "recovery_target_action = 'pause'" in
> > > the config file, I want it to pause.
> > 
> > You want it to enter a state where you cannot perform any action other
> > than shutdown?
> > 
> > Why would anyone want that?
> 
> You actually still could promote. But I'd be perfectly happy if postgres
> said
> ERROR: recovery_target_action = 'pause' in "%s" cannot be used without 
> hot_standby
> DETAIL: Recovery pauses cannot be resumed without SQL level access.
> HINT: Configure hot_standby and try again.
> 
> or something roughly like that. If postgres tells me what to change to
> achieve what I want, I have a much higher chance of getting
> there. Especially if it just does something else otherwise.

Where are we on this?

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

  + Everyone has their own god. +


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

Re: [HACKERS] cache invalidation for PL/pgsql functions

[Tom Lane]

Robert Haas  writes:
> On Fri, May 1, 2015 at 9:09 AM, Marko Tiikkaja  wrote:
>> We recently hit a similar case in our production environment.  What was
>> annoying about it is that there didn't seem to be a way for the application
>> to fix the issue by itself, short of reconnecting; even DISCARD ALL doesn't
>> help.  If we can't fix the underlying issue, can we at least provide a way
>> for apps to invalidate these caches themselves, for example in the form of a
>> DISCARD option?

> It's been discussed before and I am in favor of it.

I'm not.  We should fix the problem not expect applications to band-aid
around it.  This would be particularly ill-advised because there are so
many applications that just blindly do DISCARD ALL when changing contexts.

Having said that, I'm not sure that it's easy to get to a solution :-(

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] feature freeze and beta schedule

[Stephen Frost]

Andres,

* Andres Freund (and...@anarazel.de) wrote:
> On 2015-04-30 08:39:45 -0400, Peter Eisentraut wrote:
> > If you have spare cycles, there are a number of relevant patches still
> > open in the commit fest.
> 
> I was wondering what the actual state of the commitfest is. I'm thus
> going through all the open items. Here's my thoughts:

Thanks!

> * fsync $PGDATA recursively at startup
>   Bugfix, i.e. not really tied to CF

We really need to segregate the two..  By that what I mean is this: I
want an "always-open" "bugfix" CF, which allows us to keep track of
bugfix patches.  Having something about "applies to versions X, Y, Z"
would be nice too...

/me prods Magnus

> * Async execution of postgres_fdw.
> 
>   Later iterations of the patch haven't gotten much review yet. The
>   original version of the patch is just from 2014-12-15.
>   => Should imo be moved to the next CF

I'd love to see this happen, but I haven't got cycles to spend on it
between now and feature freeze. :/

> * INNER JOIN removals
>   Seem far to controversial to consider comitting in 9.5.
>   => Returned (or even rejected :()

I'd really like to see this too, but I agree it's not something for 9.5
at this point.

> * Aggregate State Combine Support
>   I think we pretty clearly need something roughly like this. It seems
>   equally clear that this isn't going to happen in 9.5
>   => Returned with feedback

Agreed.

> * Allow "snapshot too old" error, to prevent bloat
>   
> http://archives.postgresql.org/message-id/1361166406.1897609.1424371443904.JavaMail.yahoo%40mail.yahoo.com
>   talked about a new version that afaics never materialized
>   => Returned with feedback

As mentioned earlier, this is a committer's patch and if Kevin shows up
with a new patch based on that discussion which others can live with,
then I'm for having the capability over not.  As such, not sure if RFW
is the right state for it to be in at this point.

> * Sending WAL receiver feedback regularly even if the receiver is under
>   heavy load
>   Imo more of a bugfix than a feature. I.e. doesn't really concern the
>   CF scheduling.

Another one for that "bugfix" CF...

> * Auditing extension
>   I'm unclear on the status here. Abhijit said he'll have a look.

It was recently set to ready-for-committer by Sawada (he and David have
been going back and forth with testing, fixing, etc).  Fujii took a look
and had a few comments which David responded to.  As is probably
obvious, it's certainly something that I'd like to see happen as it's an
oft-requested feature.  Certainly would be great if Abhijit could look
at it.

> * Parallel Seq scan
>   In my opinion the topic has progressed greatly. But at the same time
>   it doesn't seem like it's in a state we should consider for 9.5.
>   => Return?

I'd certainly love to see it happen but I've not been following the
recent discussion and so I'm not really sure if it's ready or not.

> * RLS: row-level security, more review
>   More of a placeholder item. There seem to various open items.

I've addressed a few of them lately..  If there are more then I've lost
track of what they are and would certainly welcome folks reminding me.
In particular, the issue from Craig about the misleading error has been
addressed and I believe we've worked out the RLS+INSERT ... ON CONFLICT
questions.

> * Deparsing utility commands
>   IIUc Alvaro intends to commit a minimal version soon.

+1

> * INSERT ... ON CONFLICT {UPDATE | IGNORE}
>   Heikki, Peter and I have spent a fair amount of time on this. I believe
>   we can commit it early next week.

Yay!

> * Additional role attributes
>   I agree with Robert's point in
>   
> http://archives.postgresql.org/message-id/CA%2BTgmobH4tdccajn7VmPT-1RqBdzLYcAz5jUz4bJ%3Drkqs_gADA%40mail.gmail.com
>   and thus think that this patch isn't ready for 9.5.

I agree it needs more discussion and am planning on posting both a much
simpler patch (which removes the complicated changes to pg_dump) and
putting it on the new thread to get some real discussion about it.
Robert's point that these changes need discussion is certainly spot on
and that may lead to it not getting into 9.5, but I'm not quite ready to
punt on it yet given that we've been working on it for going on 6(?)
months now.

> * catalog view to pg_hba.conf file
>   Greg is marked as a comitter here.

I'm happy to help with this also.

> * pg_file_settings view: To know detail of config files via SQL
>   Seems to be ready.

I'm looking at this too.

> * Add pg_settings.pending_restart column
>   Looks like it coudl quickly be committed.

No objection here.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] feature freeze and beta schedule

[Andres Freund]

On 2015-05-01 09:49:50 -0700, Peter Geoghegan wrote:
> On Fri, May 1, 2015 at 9:37 AM, Andres Freund  wrote:
> > * Abbreviated key support for Datum sorts
> >   Unfortunately the discussion about potential performance regression
> >   has been largely sidestepped by bickering over minutiae.
> >   => ?
> 
> There really is no discussion about performance regressions, because
> there doesn't have to be. It's a straightfroward case of making what
> already works for the heap tuple and B-Tree tuple sort cases work for
> the Datum case. The costs and the benefits are the same.
> 
> It was marked "ready for committer" some time ago.

Why is
http://www.postgresql.org/message-id/54e79f9c.4090...@2ndquadrant.com
not worth a discussion?  I don't see your response in
http://www.postgresql.org/message-id/cam3swzty9fmrn9q8umsqn9bnedtjk68fkitomhhsxy2koyv...@mail.gmail.com
really debating why it's definitely worth the cost.  I'm not saying it's
*not* worth it, just that it has to be considered.

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] feature freeze and beta schedule

[Andres Freund]

On 2015-05-01 13:05:19 -0400, Simon Riggs wrote:
> On 1 May 2015 at 12:37, Andres Freund  wrote:
> > * fastbloat
> >   Not too big, I think it should be easy to commit this.
> >   => Keep in 'ready for committer'
> >
> 
> Will commit soon

Cool.


> > * Allow "snapshot too old" error, to prevent bloat
> >
> > http://archives.postgresql.org/message-id/1361166406.1897609.1424371443904.JavaMail.yahoo%40mail.yahoo.com
> >   talked about a new version that afaics never materialized
> >   => Returned with feedback

> Would like to review this

Kevin hasn't posted that new version yet, has he?
> * Sending WAL receiver feedback regularly even if the receiver is under
> 
> >   heavy load
> >   Imo more of a bugfix than a feature. I.e. doesn't really concern the
> >   CF scheduling.
> >
> 
> Agreed, bug fix. Will do if Fujii doesn't
> 
> 
> > * Auditing extension
> >   I'm unclear on the status here. Abhijit said he'll have a look.
> >
> 
> Maybe me, but running out of time
> 
> 
> > * TABLESAMPLE clause
> >   Doesn't seem very far from being done. Some questions about including
> >   (or not) DDL and contrib modules seem to remain.
> >
> 
> Will commit this soon

Cool.

Thanks for the update,

Andres


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

Re: [HACKERS] pg_dump: CREATE TABLE + CREATE RULE vs. relreplident

[Andres Freund]

On 2015-05-01 13:03:39 -0400, Bruce Momjian wrote:
> Applied and backpatched to 9.4.

Ah. Forgot about that. Thanks!

Andres


.oO(bugtracker?)


-- 
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] Use outerPlanState() consistently in executor code

[Qingqing Zhou]

On Thu, Apr 30, 2015 at 5:04 PM, Tom Lane  wrote:
>
> I think I'd have done many of these as
>
> +   PlanState   *outerPlan = outerPlanState(node);
>
> rather than finding assorted random places to initialize the variables.
>

Agreed. Attached patch is revision along this line. Except for a few
that delayed assignments  does not look a random kludge, I moved most
of others together with the declaration.

Regards,
Qingqing
diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c
old mode 100644
new mode 100755
index 9ff0eff..fe3920e
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -2053,10 +2053,10 @@ void
 ExecReScanAgg(AggState *node)
 {
ExprContext *econtext = node->ss.ps.ps_ExprContext;
+   PlanState   *outerPlan = outerPlanState(node);
int aggno;
 
node->agg_done = false;
-
node->ss.ps.ps_TupFromTlist = false;
 
if (((Agg *) node->ss.ps.plan)->aggstrategy == AGG_HASHED)
@@ -2075,7 +2075,7 @@ ExecReScanAgg(AggState *node)
 * parameter changes, then we can just rescan the existing hash 
table;
 * no need to build it again.
 */
-   if (node->ss.ps.lefttree->chgParam == NULL)
+   if (outerPlan->chgParam == NULL)
{
ResetTupleHashIterator(node->hashtable, 
&node->hashiter);
return;
@@ -2133,8 +2133,8 @@ ExecReScanAgg(AggState *node)
 * if chgParam of subnode is not null then plan will be re-scanned by
 * first ExecProcNode.
 */
-   if (node->ss.ps.lefttree->chgParam == NULL)
-   ExecReScan(node->ss.ps.lefttree);
+   if (outerPlan->chgParam == NULL)
+   ExecReScan(outerPlan);
 }
 
 
diff --git a/src/backend/executor/nodeBitmapHeapscan.c 
b/src/backend/executor/nodeBitmapHeapscan.c
old mode 100644
new mode 100755
index 8ea8b9f..6502841
--- a/src/backend/executor/nodeBitmapHeapscan.c
+++ b/src/backend/executor/nodeBitmapHeapscan.c
@@ -449,6 +449,8 @@ ExecBitmapHeapScan(BitmapHeapScanState *node)
 void
 ExecReScanBitmapHeapScan(BitmapHeapScanState *node)
 {
+   PlanState   *outerPlan;
+
/* rescan to release any page pin */
heap_rescan(node->ss.ss_currentScanDesc, NULL);
 
@@ -469,8 +471,9 @@ ExecReScanBitmapHeapScan(BitmapHeapScanState *node)
 * if chgParam of subnode is not null then plan will be re-scanned by
 * first ExecProcNode.
 */
-   if (node->ss.ps.lefttree->chgParam == NULL)
-   ExecReScan(node->ss.ps.lefttree);
+   outerPlan = outerPlanState(node);
+   if (outerPlan->chgParam == NULL)
+   ExecReScan(outerPlan);
 }
 
 /* 
diff --git a/src/backend/executor/nodeGroup.c b/src/backend/executor/nodeGroup.c
old mode 100644
new mode 100755
index 83d562e..b0d5442
--- a/src/backend/executor/nodeGroup.c
+++ b/src/backend/executor/nodeGroup.c
@@ -280,6 +280,8 @@ ExecEndGroup(GroupState *node)
 void
 ExecReScanGroup(GroupState *node)
 {
+   PlanState   *outerPlan;
+   
node->grp_done = FALSE;
node->ss.ps.ps_TupFromTlist = false;
/* must clear first tuple */
@@ -289,7 +291,7 @@ ExecReScanGroup(GroupState *node)
 * if chgParam of subnode is not null then plan will be re-scanned by
 * first ExecProcNode.
 */
-   if (node->ss.ps.lefttree &&
-   node->ss.ps.lefttree->chgParam == NULL)
-   ExecReScan(node->ss.ps.lefttree);
+   outerPlan = outerPlanState(node);
+   if (outerPlan->chgParam == NULL)
+   ExecReScan(outerPlan);
 }
diff --git a/src/backend/executor/nodeMaterial.c 
b/src/backend/executor/nodeMaterial.c
old mode 100644
new mode 100755
index 1158825..8ff4352
--- a/src/backend/executor/nodeMaterial.c
+++ b/src/backend/executor/nodeMaterial.c
@@ -317,6 +317,8 @@ ExecMaterialRestrPos(MaterialState *node)
 void
 ExecReScanMaterial(MaterialState *node)
 {
+   PlanState   *outerPlan = outerPlanState(node);
+
ExecClearTuple(node->ss.ps.ps_ResultTupleSlot);
 
if (node->eflags != 0)
@@ -339,13 +341,13 @@ ExecReScanMaterial(MaterialState *node)
 * Otherwise we can just rewind and rescan the stored output. 
The
 * state of the subnode does not change.
 */
-   if (node->ss.ps.lefttree->chgParam != NULL ||
+   if (outerPlan->chgParam != NULL ||
(node->eflags & EXEC_FLAG_REWIND) == 0)
{
tuplestore_end(node->tuplestorestate);
node->tuplestorestate = NULL;
-   if (node->ss.ps.lefttree->chgParam == NULL)
-   ExecReScan(node->ss.ps.lefttree);
+   if (outerPlan->chgParam == NULL)
+   ExecReScan(

Re: [HACKERS] feature freeze and beta schedule

[Simon Riggs]

On 1 May 2015 at 12:37, Andres Freund  wrote:


> * fastbloat
>   Not too big, I think it should be easy to commit this.
>   => Keep in 'ready for committer'
>

Will commit soon


> * Turning off HOT for larger SQL queries
>   Seems to have degenerated into a discussion of not really related
>   things. I personally would vote for committing something close to what
>   Simon proposed last *directly at the beginning* of the next cycle.
>   => Move?
>

Yes, move


> * Allow "snapshot too old" error, to prevent bloat
>
> http://archives.postgresql.org/message-id/1361166406.1897609.1424371443904.JavaMail.yahoo%40mail.yahoo.com
>   talked about a new version that afaics never materialized
>   => Returned with feedback
>

Would like to review this


> * Sequence Access Method
>   There's been some back and forth between Petr and Heikki on this
>   lately.
>   => Maybe there's still a chance for 9.5?
>

Hope so

* Sending WAL receiver feedback regularly even if the receiver is under

>   heavy load
>   Imo more of a bugfix than a feature. I.e. doesn't really concern the
>   CF scheduling.
>

Agreed, bug fix. Will do if Fujii doesn't


> * Auditing extension
>   I'm unclear on the status here. Abhijit said he'll have a look.
>

Maybe me, but running out of time


> * TABLESAMPLE clause
>   Doesn't seem very far from being done. Some questions about including
>   (or not) DDL and contrib modules seem to remain.
>

Will commit this soon

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] pg_dump: CREATE TABLE + CREATE RULE vs. relreplident

[Bruce Momjian]

On Thu, Mar 12, 2015 at 03:14:48PM +0100, Andres Freund wrote:
> Hi,
> 
> On 2015-03-12 14:25:24 +0100, Marko Tiikkaja wrote:
> > My colleague Per Lejontand brought to my attention that when dumping views
> > with circular dependencies from a postgres version older than 9.4 using a
> > recent pg_dump, the SQL looks something like the following:
> > 
> >   create table qwr();
> >   create rule "_RETURN" as on select to qwr do instead select;
> > 
> > In this case the relreplident column in pg_class for the view ends up being
> > 'd', instead of the 'n' normally used for views.  Patch to update
> > relreplident when turning a table into a view is attached; this makes sure
> > that the identity is NOTHING regardless of how the view was created.
> 
> I think that's a good idea.
> 
> > I consider this a bug fix, and suggest back patching to 9.4.
> 
> I agree on backpatching it. Arguably we could additionally avoid
> emitting the ALTER TABLE ... REPLICA IDENTITY for views that have
> already been created with identity set like this. But I doubt it's worth
> it.

Applied and backpatched to 9.4.


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

  + Everyone has their own god. +


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

Re: [HACKERS] feature freeze and beta schedule

[Peter Geoghegan]

On Fri, May 1, 2015 at 9:37 AM, Andres Freund  wrote:
> * Abbreviated key support for Datum sorts
>   Unfortunately the discussion about potential performance regression
>   has been largely sidestepped by bickering over minutiae.
>   => ?

There really is no discussion about performance regressions, because
there doesn't have to be. It's a straightfroward case of making what
already works for the heap tuple and B-Tree tuple sort cases work for
the Datum case. The costs and the benefits are the same.

It was marked "ready for committer" some time ago.
-- 
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] Replication, am I missing something

[Joshua D. Drake]

On 05/01/2015 09:28 AM, Andres Freund wrote:


On 2015-05-01 09:24:17 -0700, Joshua D. Drake wrote:

Origin: select pg_start_backup('my_backup',TRUE);
Subscriber: rsync -auvk db1:/var/lib/pgsql/data data
Origin: select pg_stop_backup();
Subscriber: remove backup_label
Subscriber: start postgresql


Ignore the noise here. I realize I missed a step and added a step.

Sorry about that.

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] Manipulating complex types as non-contiguous structures in-memory

[Andres Freund]

On 2015-05-01 09:35:08 -0700, Tom Lane wrote:
> Andres Freund  writes:
> > What are your plans with this WRT 9.5?
> 
> I'd like to get it committed into 9.5.  I've been hoping somebody would do
> a performance review.

Ok. I'll try to have a look, but it'll be the second half of next week.

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] feature freeze and beta schedule

[Andres Freund]

On 2015-04-30 08:39:45 -0400, Peter Eisentraut wrote:
> If you have spare cycles, there are a number of relevant patches still
> open in the commit fest.

I was wondering what the actual state of the commitfest is. I'm thus
going through all the open items. Here's my thoughts:


* fsync $PGDATA recursively at startup
  Bugfix, i.e. not really tied to CF

* EvalPlanQual behaves oddly for FDW queries involving system columns
  Tom wants to take care of it next week.
  => Keep in 'ready for committer'

* Do not vacuum pgbench tables if they do not exist when pgbench -f given
  Robert was recently sending an iteration of a patch, so I think this
  is being taken care of.
  => Keep in 'ready for committer'

* Multivariate statistics
  This is not intended to be committed this CF.
  => I'd like to mark this as returned with (little) feedback.

* fastbloat
  Not too big, I think it should be easy to commit this.
  => Keep in 'ready for committer'

* Avoiding plan disasters with LIMIT
  I'm not enthused by the approach, it's disabled by default though. So
  it might not be too bad to just do it. Would probably have been a good
  idea to discuss the patch in a separate thread.
  => ?

* Turning off HOT for larger SQL queries
  Seems to have degenerated into a discussion of not really related
  things. I personally would vote for committing something close to what
  Simon proposed last *directly at the beginning* of the next cycle.
  => Move?

* BRIN inclusion operator class
  This seems to be a mishmash of bugfix and feature. The bugfixes should
  be applied pdq...
  => ?
* Async execution of postgres_fdw.

  Later iterations of the patch haven't gotten much review yet. The
  original version of the patch is just from 2014-12-15.
  => Should imo be moved to the next CF

* PageRepairFragmentation optimization
  Hm. Heikki hasn't replied recently. It's a committer's patch though,
  so it probably doesn't matter that much.
  => Move?

* Abbreviated key support for Datum sorts
  Unfortunately the discussion about potential performance regression
  has been largely sidestepped by bickering over minutiae.
  => ?

* Unique Joins
  This seems to require more work and came in pretty late
  => Returned with feedback.

* INNER JOIN removals
  Seem far to controversial to consider comitting in 9.5.
  => Returned (or even rejected :()

* Aggregate State Combine Support
  I think we pretty clearly need something roughly like this. It seems
  equally clear that this isn't going to happen in 9.5
  => Returned with feedback

* Allow "snapshot too old" error, to prevent bloat
  
http://archives.postgresql.org/message-id/1361166406.1897609.1424371443904.JavaMail.yahoo%40mail.yahoo.com
  talked about a new version that afaics never materialized
  => Returned with feedback

* KNN-GiST with recheck
  Unfortunately the last version hasn't gotten feedback. The topic is
  somewhat old, so I'd be ugly to not do somethign about it this CF. I
  can't judge the state of the patch right now.
  => ?

* GIN fillfactor
  Seems like a simple enough idea.
  => I guess somebody should review and commit it?

* Manipulating complex types as non-contiguous structures in-memory
  Pretty large for this state and being proposed late. On the other hand
  it's Tom. Looks like it could use some review tho.
  => ?

* Optimization for updating foreign tables in Postgres FDW
  Hm. The last, significantly redone, iteration doesn't seem to have
  gotten much code level review.
  => ?

* iteration over record in plpgsql
  To me this still seems to be in the in the design phase.
  => Return?

* Sequence Access Method
  There's been some back and forth between Petr and Heikki on this
  lately.
  => Maybe there's still a chance for 9.5?

* archive_mode=shared/always
  Hasn't really gotten code level review yet. Heikki?

* Sending WAL receiver feedback regularly even if the receiver is under
  heavy load
  Imo more of a bugfix than a feature. I.e. doesn't really concern the
  CF scheduling.

* Auditing extension
  I'm unclear on the status here. Abhijit said he'll have a look.

* Parallel Seq scan
  In my opinion the topic has progressed greatly. But at the same time
  it doesn't seem like it's in a state we should consider for 9.5.
  => Return?

* ctidscan as an example of custom-scan
  Hasn't really gotten sufficient review.
  => Move

* logical column ordering (WIP)
  This pretty clearly isn't 9.5 material.
  => Return

* parallel mode/contexts
  The largest part of this has just been committed. There's more
  contentious/complicated bits around locking left. I have a hard time
  believing we have the cycles to resolve those for 9.5. It'd be neat if
  we could, but I think "older" stuff has a bit of precedence.

* Support ORDER BY in CREATE FUNCTION for Set Returning Functions
  Uhm. I think the outcome of the discussion so far wasn't really
  favorable to the idea s proposed.
  => Rejected

* RLS: row-level security, more review
  More of a placeholder item. There 

Re: [HACKERS] Manipulating complex types as non-contiguous structures in-memory

[Tom Lane]

Andres Freund  writes:
> On 2015-03-28 17:24:36 -0400, Tom Lane wrote:
>> This is overdue for a rebase; attached.  No functional changes, but some
>> of what was in the original patch has already been merged, and other parts
>> were superseded.

> What are your plans with this WRT 9.5?

I'd like to get it committed into 9.5.  I've been hoping somebody would do
a performance review.

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] Providing catalog view to pg_hba.conf file - Patch submission

[Andres Freund]

On 2015-04-08 19:19:29 +0100, Greg Stark wrote:
> I'm not sure what the best way to handle the hand-off from patch
> contribution to reviewer/committer. If I start tweaking things then
> you send in a new version it's actually more work to resolve the
> conflicts. I think at this point it's easiest if I just take it from
> here.

Are you intending to commit this?

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] Replication, am I missing something

[Andres Freund]

On 2015-05-01 09:24:17 -0700, Joshua D. Drake wrote:
> Origin: select pg_start_backup('my_backup',TRUE);
> Subscriber: rsync -auvk db1:/var/lib/pgsql/data data
> Origin: select pg_stop_backup();
> Subscriber: remove backup_label
> Subscriber: start postgresql

You remove backup_label? Huh? That'll pretty directly lead to a
corrupted database? Why are you doing that?

> So if we aren't willing to backport the 9.4 fixes (which I do understand),
> what do we do about this? Do we need a PSA?

Which 9.4 fixes are you referring to?

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

[HACKERS] Replication, am I missing something

[Joshua D. Drake]

-hackers,

In the flurry of releases, I want to make sure I haven't missed 
something. Here is an example of how I will take a base backup:


Origin: select pg_start_backup('my_backup',TRUE);
Subscriber: rsync -auvk db1:/var/lib/pgsql/data data
Origin: select pg_stop_backup();
Subscriber: remove backup_label
Subscriber: start postgresql

Now I know there are nuances here but the above should provide a valid, 
start able, postgresql on the subscriber, yes?


The reason I ask goes back to the btree errors I mentioned previously. I 
have in the last month experienced corrupt indexes, as well as corrupt 
indexes/duplicates in tables on multiple customer instances.


This is multiple customers, all running 9.l or 9.2 (STABLE). There is a 
mix of AWS/EC2, HyperV and Bare metal. They are all running Linux.


In fact, at this point I can consistently produce a base backup that has 
corrupt indexes. I can consistently produce a base backup that has these 
issues if it is on bare metal, virtualized or network filesystem.


So if we aren't willing to backport the 9.4 fixes (which I do 
understand), what do we do about this? Do we need a PSA?


If this was an isolated instance, it would be one thing but I am 
certainly not the only person running into this.


Sincerely,

Joshua D. Drake



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

[HACKERS] Improving replay of XLOG_BTREE_VACUUM records

[Vladimir Borodin]

Hi all.There are situations in which vacuuming big btree index causes stuck in WAL replaying on hot standby servers for quite a long time. I’ve described the problem in more details in this thread [0]. Below in that thread Kevin Grittner proposed a good way for improving btree scans so that btree vacuuming logic could be seriously simplified. Since I don’t know when that may happen I’ve done a patch that makes some improvement right now. If Kevin or someone else would expand [1] for handling all types of btree scans, I suppose, my patch could be thrown away and vacuuming logic should be strongly rewritten.The idea of the patch is not to read pages from disk to make sure they are unpinned (like btree_xlog_vacuum does it right now). This is done with creating a new ReadBufferMode which returns locked buffer without setting BM_VALID flag on it. I don’t know if that is the right way of doing that but it seems to work well.Testing it my environment gives a good win [2] - green is unpatched replica, blue is replica with a patch, two spikes are results of calling manual vacuuming of big table. Since the picture could be unavailable I’ll write here that:	1. replication delay reduced from ~1250 MB to 200 MB of replay_location lag,	2. patched replica caught master in less than a minute against 12 minutes of unpatched replica,	3. Physical I/O load on patched replica didn’t change compared with the normal workload while unpatched replica did lots of reads from PGDATA during spikes.There is still a stuck in WAL replay but much smaller that right now. Also this change seems not to affect any other scenarios.I’ll add it to 2015-06 commitfest.[0] http://www.postgresql.org/message-id/058c9d59-9200-45fd-a565-0e4431a6f...@simply.name[1] http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2ed5b87f96d473962ec5230fd820abfeaccb2069[2] https://yadi.sk/i/l13PZUNhgNB8u

btree_vacuum_v1.patch
Description: Binary data

--May the force be with you…https://simply.name

Re: [HACKERS] transforms vs. CLOBBER_CACHE_ALWAYS

[Andrew Dunstan]

On 05/01/2015 08:57 AM, Andrew Dunstan wrote:


On 04/30/2015 09:09 PM, Christian Ullrich wrote:

* Andrew Dunstan:


friarbird is a FreeBSD buildfarm animal running with
-DCLOBBER_CACHE_ALWAYS. It usually completes a run in about 6.5 hours.
However, it's been stuck since Monday running the plpython regression
tests. The only relevant commit seems to be the transforms feature.
Here's what it's been doing:



query| SELECT cursor_plan();


Same here, on jaguarundi. I actually killed it intentionally this 
morning, hoping that whatever the problem was might have been fixed 
already. No such luck.


I would suspect that it might have something to do with the OS, if 
all the other CCA animals weren't lining up nicely behind in the 
buildfarm status page.






Yeah, this happened again this morning, so it seems to be quite 
reliably reproducible. I killed it and I've set friarbird to build 
without python for now, but this is clearly an issue that needs to be 
resolved.



And I have confirmed that it's not an OS problem - I have reproduced it 
on a modern Linux instance (it's still running, in fact). So it's quite 
clearly a bug that needs to be fixed.


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] Manipulating complex types as non-contiguous structures in-memory

[Andres Freund]

On 2015-03-28 17:24:36 -0400, Tom Lane wrote:
> I wrote:
> > [ expanded-arrays-1.0.patch ]
> 
> This is overdue for a rebase; attached.  No functional changes, but some
> of what was in the original patch has already been merged, and other parts
> were superseded.

What are your plans with this WRT 9.5?

Andres


-- 
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] cache invalidation for PL/pgsql functions

[Fabrízio de Royes Mello]

Em sexta-feira, 1 de maio de 2015, Robert Haas 
escreveu:

> On Fri, May 1, 2015 at 9:09 AM, Marko Tiikkaja  > wrote:
> > On 2015-04-28 19:43, Robert Haas wrote:
> >> I guess
> >> the root of the problem is that PL/plgsql's cache invalidation logic
> >> only considers the pg_proc row's TID and xmin when deciding whether to
> >> recompile.  For base types that's probably OK, but for composite
> >> types, not so much.
> >>
> >> Thoughts?
> >
> > We recently hit a similar case in our production environment.  What was
> > annoying about it is that there didn't seem to be a way for the
> application
> > to fix the issue by itself, short of reconnecting; even DISCARD ALL
> doesn't
> > help.  If we can't fix the underlying issue, can we at least provide a
> way
> > for apps to invalidate these caches themselves, for example in the form
> of a
> > DISCARD option?
>
> It's been discussed before and I am in favor of it.  However the
> implementation is a bit challenging.  The DISCARD command doesn't know
> what PLs may have decided to cache, nonwithstanding the fact that they
> all cache basically the same stuff using basically the same method.  I
> think the PL interface will need to be extended in some way to support
> a new callback.
>
>
IMHO we need a way to DISCARD run a cleanup code for each installed
extension. Maybe with a new option like DISCARD EXTENSIONS. So each
extension could have and register your own cleanup code.

Regards,



-- 
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

[Stephen Frost]

* Robert Haas (robertmh...@gmail.com) wrote:
> OK.  In that case, I'm a lot less sure what the right decision is.  It
> seems weird for both the BEFORE INSERT and BEFORE UPDATE triggers to
> get a crack at the same tuple, so your way might be better after all.
> But on the other hand, the BEFORE INSERT trigger might have had side
> effects, so we can't just pretend it didn't happen.

I agree that having the before-insert and before-update triggers both
fire is a bit odd, but I also think it's the right thing to do.  If the
statements were independent instead of an INSERT .. ON CONFLICT, then
both sets of before triggers would very clearly fire.

> One idea is to decide that an INSERT with an ON CONFLICT UPDATE
> handler is still an INSERT.  Period.  So the INSERT triggers run, the
> UPDATE triggers don't, and that's it.

Another option would be to have an independent "INSERT-ON-CONFLICT"
before trigger which fires..  but, for my 2c, I'm happy to just fire
both.

I definitely feel that the EXCLUDED tuple should refer to the post
before-insert trigger; having it refer to a tuple that may not have
actually conflicted doesn't seem correct to me.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] initdb start server recommendation

[Andrew Dunstan]

On 05/01/2015 10:14 AM, Bruce Momjian wrote:

Currently initdb outputs suggested text on starting the server:

Success. You can now start the database server using:

/u/pgsql/bin/postgres -D /u/pgsql/data
or
/u/pgsql/bin/pg_ctl -D /u/pgsql/data -l logfile start

I am now thinking pg_ctl should be recommended first.  At the time this
text was written pg_ctl was new.




Frankly, I would just output the pg_ctl line. Anyone who actually needs 
this message should probably be using pg_ctl anyway.


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] initdb start server recommendation

[Euler Taveira]

On 01-05-2015 11:14, Bruce Momjian wrote:
> Currently initdb outputs suggested text on starting the server:
> 
>   Success. You can now start the database server using:
>   
>   /u/pgsql/bin/postgres -D /u/pgsql/data
>   or
>   /u/pgsql/bin/pg_ctl -D /u/pgsql/data -l logfile start
> 
> I am now thinking pg_ctl should be recommended first.  At the time this
> text was written pg_ctl was new.
> 
+1.

BTW, why are we advocating postgres binary use at all? AFAICS the main
postgres (or postmaster) uses are (i) startup script (which also
advocate for 'pg_ctl -w') and (ii) disaster/debugging purposes. None of
those use cases are intended for general users. Let's make it simple and
drop 'postgres' line.


-- 
   Euler Taveira   Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento


-- 
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] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

[Robert Haas]

On Fri, May 1, 2015 at 10:49 AM, Andres Freund  wrote:
>> One idea is to decide that an INSERT with an ON CONFLICT UPDATE
>> handler is still an INSERT.  Period.  So the INSERT triggers run, the
>> UPDATE triggers don't, and that's it.
>
> I think that'd be much worse.

OK.  Well, in that case, I guess I'm inclined to think that we
shouldn't throw away the tuple the BEFORE trigger constructs.

> One question, that I was wondering about earlier, that also might
> influence this discussion, is what happens if you change the key we're
> using for resolution during either the BEFORE trigger or the ON CONFLICT
> ... SET.  Right now the conflict will be on the version *after* the
> BEFORE INSERT, which I think think is the only reasonable option.
>
> And if you're mad enough to change the key in the ON CONFLICT ... SET
> ... you'll possibly get conflicts. I was, over IM, arguing for that to
> be forbidden to protect users against themselves, but Heikki said people
> might e.g. want to set a column in a key to NULL in that case.

I'm not a big fan of trying to protect users against themselves.  I'm
fine with stupid user decisions resulting in errors.

-- 
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] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

[Andres Freund]

On 2015-05-01 10:39:35 -0400, Robert Haas wrote:
> On Fri, May 1, 2015 at 10:24 AM, Andres Freund  wrote:
> > The BEFORE UPDATE would catch things in this case.
> 
> OK.  In that case, I'm a lot less sure what the right decision is.  It
> seems weird for both the BEFORE INSERT and BEFORE UPDATE triggers to
> get a crack at the same tuple, so your way might be better after all.
> But on the other hand, the BEFORE INSERT trigger might have had side
> effects, so we can't just pretend it didn't happen.

Well, I think it's pretty unavoidable to fire both. On that part I think
were pretty lengthy discussions a year or so back.

> One idea is to decide that an INSERT with an ON CONFLICT UPDATE
> handler is still an INSERT.  Period.  So the INSERT triggers run, the
> UPDATE triggers don't, and that's it.

I think that'd be much worse.


One question, that I was wondering about earlier, that also might
influence this discussion, is what happens if you change the key we're
using for resolution during either the BEFORE trigger or the ON CONFLICT
... SET.  Right now the conflict will be on the version *after* the
BEFORE INSERT, which I think think is the only reasonable option.

And if you're mad enough to change the key in the ON CONFLICT ... SET
... you'll possibly get conflicts. I was, over IM, arguing for that to
be forbidden to protect users against themselves, but Heikki said people
might e.g. want to set a column in a key to NULL in that case.

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] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

[Heikki Linnakangas]

On 04/30/2015 11:09 PM, Peter Geoghegan wrote:

I've been unable to reproduce the unprincipled deadlock using the same
test case as before. However, the exclusion constraint code now
livelocks. Here is example output from a stress-testing session:

...

[Fri May  1 04:45:35 2015] normal exit at 1430455535 after 128000
items processed at count_upsert_exclusion.pl line 192.
trying 128 clients:
[Fri May  1 04:45:58 2015] NOTICE:  extension "btree_gist" already
exists, skipping
[Fri May  1 04:45:58 2015] init done at count_upsert_exclusion.pl line 106.


(I ssh into server, check progress). Then, due to some issue with the
scheduler or something, progress continues:

[Fri May  1 05:17:57 2015] sum is 462
[Fri May  1 05:17:57 2015] count is 8904
[Fri May  1 05:17:58 2015] normal exit at 1430457478 after 128000
items processed at count_upsert_exclusion.pl line 192.
trying 128 clients:


Hmm, so it was stuck for half an hour at that point? Why do you think it 
was a livelock?



This is the same server that I shared credentials with you for. Feel
free to ssh in and investigate it yourself.


I logged in, but the system seems very unresponsive in general. I just 
started "apt-get install gdb" on it, to investigate what the backends 
are stuck at. It's been running for about 30 minutes now, and I'm still 
waiting...


- 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] Reducing tuple overhead

[Amit Kapila]

On Thu, Apr 30, 2015 at 5:35 PM, Simon Riggs  wrote:
>
> On 25 April 2015 at 01:12, Amit Kapila  wrote:
>>
>> On Sat, Apr 25, 2015 at 1:58 AM, Jim Nasby 
wrote:
>> >
>> > On 4/23/15 10:40 PM, Amit Kapila wrote:
>> >>
>> >> I agree with you and what I think one of the major reasons of bloat
is that
>> >> Index segment doesn't have visibility information due to which
clearing of
>> >> Index needs to be tied along with heap.  Now if we can move
transaction
>> >> information at page level, then we can even think of having it in
Index
>> >> segment as well and then Index can delete/prune it's tuples on it's
own
>> >> which can reduce the bloat in index significantly and there is a
benefit
>> >> to Vacuum as well.
>> >
>> >
>> > I don't see how putting visibility at the page level helps indexes at
all. We could already put XMIN in indexes if we wanted, but it won't help,
because...
>> >
>>
>> We can do that by putting transaction info at tuple level in index as
>> well but that will be huge increase in size of index unless we devise
>> a way to have variable index tuple header rather than a fixed.
>>
>> >> Now this has some downsides as well like Delete
>> >> needs to traverse Index segment as well to Delete mark the tuples, but
>> >> I think the upsides of reducing bloat can certainly outweigh the
downsides.
>> >
>> >
>> > ... which isn't possible. You can not go from a heap tuple to an index
tuple.
>>
>> We will have the access to index value during delete, so why do you
>> think that we need linkage between heap and index tuple to perform
>> Delete operation?  I think we need to think more to design Delete
>> .. by CTID, but that should be doable.
>
>
> I see some assumptions here that need to be challenged.
>
> We can keep xmin and/or xmax on index entries. The above discussion
assumes that the information needs to be updated synchronously. We already
store visibility information on index entries using the lazily updated
killtuple mechanism, so I don't see much problem in setting the xmin in a
similar lazy manner. That way when we use the index if xmax is set we use
it, if it is not we check the heap. (And then you get to freeze indexes as
well ;-( )
> Anyway, I have no objection to making index AM pass visibility
information to indexes that wish to know the information, as long as it is
provided lazily.
>

Providing such an information lazily can help to an extent, but I think
it won't help much in bloat reduction. For example, when an
insert tries to insert a row in index page and found that there is no
space, it can't kill or overwrite any tuple (that is actually dead unless
updated lazily by that time) which is I think one of the main reasons for
index bloat.

> The second assumption is that if we had visibility information in the
index that it would make a difference to bloat. Since as I mention, we
already do have visibility information, I don't see that adding xmax or
xmin would make any difference at all to bloat. So -1 to adding it **for
that reason**.
>

The visibility information is only updated when such an index item
is accessed (lazy updation) and by that time already the new space
for index insertion would be used whereas if the information is provided
synchronously the dead space could be reclaimed much earlier (for
insertions on page which has dead tuples) and will reduce the chances
of bloat.

>
> A much better idea is to work out how to avoid index bloat at cause. If
we are running an UPDATE and we cannot get a cleanup lock, we give up and
do a non-HOT update, causing the index to bloat. It seems better to wait
for a short period to see if we can get the cleanup lock. The short period
is currently 0, so lets start there and vary the duration of wait upwards
proportionally as the index gets more bloated.
>

I think this is a separate and another good way of avoiding the
bloat, but independent of this having something like what we
discussed above will even reduce the chances of bloat for a
non-HOT update in a scenario described by you.

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

Re: [HACKERS] initdb -S and tablespaces

[Abhijit Menon-Sen]

At 2015-05-01 09:57:28 -0400, robertmh...@gmail.com wrote:
>
> If you don't object to this version, I'll commit it.

Looks fine to me, thank you.

As for the non-backpatchable 0002, I agree with Andres that it should be
included in 9.5; but I take it you're still not convinced? Should I add
that to the CF separately for discussion, or what?

-- Abhijit


-- 
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] initdb start server recommendation

[Robert Haas]

On Fri, May 1, 2015 at 10:14 AM, Bruce Momjian  wrote:
> Currently initdb outputs suggested text on starting the server:
>
> Success. You can now start the database server using:
>
> /u/pgsql/bin/postgres -D /u/pgsql/data
> or
> /u/pgsql/bin/pg_ctl -D /u/pgsql/data -l logfile start
>
> I am now thinking pg_ctl should be recommended first.  At the time this
> text was written pg_ctl was new.

Sure, works for me.

-- 
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] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

[Robert Haas]

On Fri, May 1, 2015 at 10:24 AM, Andres Freund  wrote:
>> > Well, it's a BEFORE INSERT trigger, not a BEFORE UPDATE, that's why I'm
>> > not so sure that argument applies.
>>
>> Would the BEFORE UPDATE trigger even fire in this case?
>
> BEFORE UPDATE triggers fire for INSERT ... ON CONFLICT UPDATE iff
> there's a conflict, yes.
>
>> The thing is, suppose somebody puts a BEFORE INSERT trigger and a
>> BEFORE UPDATE trigger on the table, and each of those triggers does
>> this:
>>
>> NEW.last_updated_time = clock_timestamp();
>> return NEW;
>>
>> That should work, and should cover all cases, even if you're using UPSERT.
>
> The BEFORE UPDATE would catch things in this case.

OK.  In that case, I'm a lot less sure what the right decision is.  It
seems weird for both the BEFORE INSERT and BEFORE UPDATE triggers to
get a crack at the same tuple, so your way might be better after all.
But on the other hand, the BEFORE INSERT trigger might have had side
effects, so we can't just pretend it didn't happen.

One idea is to decide that an INSERT with an ON CONFLICT UPDATE
handler is still an INSERT.  Period.  So the INSERT triggers run, the
UPDATE triggers don't, and that's it.

Not sure.

-- 
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] pg_rewind test race condition..?

[Stephen Frost]

Heikki,

* Heikki Linnakangas (hlinn...@iki.fi) wrote:
> On 04/29/2015 06:03 AM, Stephen Frost wrote:
> >Forcing a checkpoint in the regression tests and then providing a better
> >error message sounds reasonable to me.  I agree that it's very unlikely
> >to happen in the real world, even when you're bouncing between systems
> >for upgrades, etc, you're unlikely to do it fast enough for this issue
> >to exhibit itself, and a better error message would help any users who
> >manage to run into this (perhaps during their own testing).
> 
> I've committed this simple fix for now.

Looks good, I'm no longer seeing the failures.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] One question about security label command

[Stephen Frost]

Alvaro,

* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote:
> Stephen Frost wrote:
> > * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote:
> 
> > > Could you provide a buildfarm animal that runs the sepgsql test in all
> > > branches on a regular basis?
> > 
> > Would be great if KaiGai can, of course, but I'm planning to stand one
> > up here soon in any case.
> 
> I don't think this is done, is it?

No.  We've been testing and working with sepgsql internally but haven't
set up a buildfarm member yet.  It's still on my todo list though.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

[Andres Freund]

On 2015-05-01 10:21:27 -0400, Robert Haas wrote:
> On Fri, May 1, 2015 at 10:10 AM, Andres Freund  wrote:
> > On 2015-05-01 10:06:42 -0400, Robert Haas wrote:
> >> On Fri, May 1, 2015 at 9:58 AM, Andres Freund  wrote:
> >> > would you rather have EXCLUDED.data refer to the tuple version from
> >> > VALUES (or a SELECT or ...) or to version from the BEFORE trigger?
> >>
> >> I think it would be completely shocking if it didn't refer to the
> >> version returned by the BEFORE trigger.  My interpretation of the
> >> semantics of BEFORE triggers is that, once the trigger has fired and
> >> returned a new tuple, things should proceed just as if that new tuple
> >> were the one originally provided by the user.
> >
> > Well, it's a BEFORE INSERT trigger, not a BEFORE UPDATE, that's why I'm
> > not so sure that argument applies.
> 
> Would the BEFORE UPDATE trigger even fire in this case?

BEFORE UPDATE triggers fire for INSERT ... ON CONFLICT UPDATE iff
there's a conflict, yes.

> The thing is, suppose somebody puts a BEFORE INSERT trigger and a
> BEFORE UPDATE trigger on the table, and each of those triggers does
> this:
> 
> NEW.last_updated_time = clock_timestamp();
> return NEW;
> 
> That should work, and should cover all cases, even if you're using UPSERT.

The BEFORE UPDATE would catch things in this case.

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] INSERT ... ON CONFLICT syntax issues

[Stephen Frost]

* Andres Freund (and...@anarazel.de) wrote:
> On the other hand it's way more convenient to specify a single
> constraint name than several columns and a predicate. I'm pretty sure
> there's situations where I a) rather live with a smaller chance of error
> during a replacement of the constraint b) if we get concurrently
> replaceable constraints the naming should be doable too.
> 
> I don't see your argument strong enough to argue against allowing this
> *as an alternative*.

Agreed.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

[Robert Haas]

On Fri, May 1, 2015 at 10:10 AM, Andres Freund  wrote:
> On 2015-05-01 10:06:42 -0400, Robert Haas wrote:
>> On Fri, May 1, 2015 at 9:58 AM, Andres Freund  wrote:
>> > would you rather have EXCLUDED.data refer to the tuple version from
>> > VALUES (or a SELECT or ...) or to version from the BEFORE trigger?
>>
>> I think it would be completely shocking if it didn't refer to the
>> version returned by the BEFORE trigger.  My interpretation of the
>> semantics of BEFORE triggers is that, once the trigger has fired and
>> returned a new tuple, things should proceed just as if that new tuple
>> were the one originally provided by the user.
>
> Well, it's a BEFORE INSERT trigger, not a BEFORE UPDATE, that's why I'm
> not so sure that argument applies.

Would the BEFORE UPDATE trigger even fire in this case?

The thing is, suppose somebody puts a BEFORE INSERT trigger and a
BEFORE UPDATE trigger on the table, and each of those triggers does
this:

NEW.last_updated_time = clock_timestamp();
return NEW;

That should work, and should cover all cases, even if you're using UPSERT.

-- 
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] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

[Petr Jelinek]

On 01/05/15 16:10, Andres Freund wrote:

On 2015-05-01 10:06:42 -0400, Robert Haas wrote:

On Fri, May 1, 2015 at 9:58 AM, Andres Freund  wrote:

would you rather have EXCLUDED.data refer to the tuple version from
VALUES (or a SELECT or ...) or to version from the BEFORE trigger?


I think it would be completely shocking if it didn't refer to the
version returned by the BEFORE trigger.  My interpretation of the
semantics of BEFORE triggers is that, once the trigger has fired and
returned a new tuple, things should proceed just as if that new tuple
were the one originally provided by the user.


Well, it's a BEFORE INSERT trigger, not a BEFORE UPDATE, that's why I'm
not so sure that argument applies.

Peter also leaned in "your" direction and you apparently have a strong
opinion on it... So I guess that'll be it unless somebody else weighs
in.



FWIW I am also leaning towards what Robert says.

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


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

Re: [HACKERS] procost for to_tsvector

[Andres Freund]

On 2015-05-01 10:03:01 -0400, Robert Haas wrote:
> Maybe we could just go with 100.

+1

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

[HACKERS] initdb start server recommendation

[Bruce Momjian]

Currently initdb outputs suggested text on starting the server:

Success. You can now start the database server using:

/u/pgsql/bin/postgres -D /u/pgsql/data
or
/u/pgsql/bin/pg_ctl -D /u/pgsql/data -l logfile start

I am now thinking pg_ctl should be recommended first.  At the time this
text was written pg_ctl was new.

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

  + Everyone has their own god. +


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

Re: [HACKERS] procost for to_tsvector

[Bruce Momjian]

On Fri, May  1, 2015 at 10:03:01AM -0400, Robert Haas wrote:
> On Fri, May 1, 2015 at 10:01 AM, Bruce Momjian  wrote:
> >> Andrew did the research to support a higher value, but even 10 should
> >> be an improvement over what we have now.
> >
> > Yes, I saw that, but I didn't see him recommend an actual number.  Can
> > someone recommend a number now?   Tom initially recommended 10, but
> > Andrew's tests suggest something > 100.  Tom didn't do any tests so I
> > tend to favor Andrew's suggestion, if he has one.
> 
> In the OP, he suggested "on the order of 100".  Maybe we could just go with 
> 100.

OK, I will go with 100 unless I hear otherwise.

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

  + Everyone has their own god. +


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

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

[Andres Freund]

On 2015-05-01 10:06:42 -0400, Robert Haas wrote:
> On Fri, May 1, 2015 at 9:58 AM, Andres Freund  wrote:
> > would you rather have EXCLUDED.data refer to the tuple version from
> > VALUES (or a SELECT or ...) or to version from the BEFORE trigger?
> 
> I think it would be completely shocking if it didn't refer to the
> version returned by the BEFORE trigger.  My interpretation of the
> semantics of BEFORE triggers is that, once the trigger has fired and
> returned a new tuple, things should proceed just as if that new tuple
> were the one originally provided by the user.

Well, it's a BEFORE INSERT trigger, not a BEFORE UPDATE, that's why I'm
not so sure that argument applies.

Peter also leaned in "your" direction and you apparently have a strong
opinion on it... So I guess that'll be it unless somebody else weighs
in.

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] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

[Robert Haas]

On Fri, May 1, 2015 at 9:58 AM, Andres Freund  wrote:
> Right now this, besides cleanup, docs and syntax leaves only one real
> issue I know of. Which is the question what EXCLUDED actually refers to.
>
> Consider a table
> blarg(key int primary key, data text); with a BEFORE INSERT
> trigger that modifies 'data'. For the statement
>
> INSERT INTO blarg(key, data) VALUES(1, 'whatever')
> ON CONFLICT (key) DO UPDATE SET data = EXCLUDED.data;
>
> would you rather have EXCLUDED.data refer to the tuple version from
> VALUES (or a SELECT or ...) or to version from the BEFORE trigger?

I think it would be completely shocking if it didn't refer to the
version returned by the BEFORE trigger.  My interpretation of the
semantics of BEFORE triggers is that, once the trigger has fired and
returned a new tuple, things should proceed just as if that new tuple
were the one originally provided by the user.

-- 
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] initdb -S and tablespaces

[Andres Freund]

Hi,

I agree that splitting the patch into two separate ones is a good one.

On 2015-05-01 09:57:28 -0400, Robert Haas wrote:
> If you don't object to this version, I'll commit it.  I believe this
> part *should* be back-patched, but Tom seemed to disagree, for reasons
> I'm not really clear on.  This is a potential data corrupting bug as
> legitimate as any other, so a back-patch seems right to me.

Agreed. Especially for WAL files this seems to be a pretty clear
correctness issue to me.

I unsurprisingly think the other patch is a good idea too. But it's
clearly *not* something for the back branches.

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] procost for to_tsvector

[Robert Haas]

On Fri, May 1, 2015 at 10:01 AM, Bruce Momjian  wrote:
>> Andrew did the research to support a higher value, but even 10 should
>> be an improvement over what we have now.
>
> Yes, I saw that, but I didn't see him recommend an actual number.  Can
> someone recommend a number now?   Tom initially recommended 10, but
> Andrew's tests suggest something > 100.  Tom didn't do any tests so I
> tend to favor Andrew's suggestion, if he has one.

In the OP, he suggested "on the order of 100".  Maybe we could just go with 100.

-- 
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] procost for to_tsvector

[Bruce Momjian]

On Fri, May  1, 2015 at 09:39:43AM -0400, Robert Haas wrote:
> On Fri, May 1, 2015 at 9:13 AM, Bruce Momjian  wrote:
> > On Fri, May  1, 2015 at 07:57:27AM -0400, Robert Haas wrote:
> >> On Thu, Apr 30, 2015 at 9:34 PM, Bruce Momjian  wrote:
> >> > On Wed, Mar 11, 2015 at 02:40:16PM +, Andrew Gierth wrote:
> >> >> An issue that comes up regularly on IRC is that text search queries,
> >> >> especially on relatively modest size tables or for relatively
> >> >> non-selective words, often misplan as a seqscan based on the fact that
> >> >> to_tsvector has procost=1.
> >> >>
> >> >> Clearly this cost number is ludicrous.
> >> >>
> >> >> Getting the right cost estimate would obviously mean taking the cost of
> >> >> detoasting into account, but even without doing that, there's a strong
> >> >> argument that it should be increased to at least the order of 100.
> >> >> (With the default cpu_operator_cost that would make each to_tsvector
> >> >> call cost 0.25.)
> >> >>
> >> >> (The guy I was just helping on IRC was seeing a slowdown of 100x from a
> >> >> seqscan in a query that selected about 50 rows from about 500.)
> >> >
> >> > Where are we on setting increasing procost for to_tsvector?
> >>
> >> We're waiting for you to commit the patch.
> >
> > OK, I have to write the patch first, so patch attached, using the cost
> > of 10.  I assume to_tsvector() is the ony one needing changes.  The
> > patch will require a catalog bump too.
> 
> Andrew did the research to support a higher value, but even 10 should
> be an improvement over what we have now.

Yes, I saw that, but I didn't see him recommend an actual number.  Can
someone recommend a number now?   Tom initially recommended 10, but
Andrew's tests suggest something > 100.  Tom didn't do any tests so I
tend to favor Andrew's suggestion, if he has one.

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

  + Everyone has their own god. +


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

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

[Andres Freund]

On 2015-04-26 18:02:06 -0700, Peter Geoghegan wrote:
> Remaining challenges
> =

So I did the executor changes I'd mentioned downthread, and Peter agreed
that it'd quite workable.

Right now this, besides cleanup, docs and syntax leaves only one real
issue I know of. Which is the question what EXCLUDED actually refers to.

Consider a table
blarg(key int primary key, data text); with a BEFORE INSERT
trigger that modifies 'data'. For the statement

INSERT INTO blarg(key, data) VALUES(1, 'whatever')
ON CONFLICT (key) DO UPDATE SET data = EXCLUDED.data;

would you rather have EXCLUDED.data refer to the tuple version from
VALUES (or a SELECT or ...) or to version from the BEFORE trigger?


To me it seems better to have it refer to version *not* affected by the
trigger (which will be called either way). I think it'd be slightly
easier to understand and more flexible.  But I could live with either
decision.

This isn't a technical problem, we just have to decide what we want to
do.

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] initdb -S and tablespaces

[Robert Haas]

On Fri, May 1, 2015 at 8:42 AM, Abhijit Menon-Sen  wrote:
> At 2015-05-01 08:10:16 -0400, robertmh...@gmail.com wrote:
>> It seems to me that, at a minimum, it would be good to split those
>> controversial and definitely not-back-patchable changes into their
>> own patch.
>
> OK, split here (0002*).
>
>> I do mind putting it into xlog.c instead of some place that's actually
>> appropriate.
>
> OK, moved to storage/file/fd.c (0001*).

Here's a revised version of your 0001 patch which I am comfortable
with.  I changed some of the comments, and I moved the fsync_pgdata()
call slightly later, so that we don't do a (possibly long) set of
fsyncs before printing out the first log message that tells the user
what is going on.

If you don't object to this version, I'll commit it.  I believe this
part *should* be back-patched, but Tom seemed to disagree, for reasons
I'm not really clear on.  This is a potential data corrupting bug as
legitimate as any other, so a back-patch seems right to me.

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


fsync-pgdata-rmh.patch
Description: binary/octet-stream

-- 
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] cache invalidation for PL/pgsql functions

[Robert Haas]

On Fri, May 1, 2015 at 9:09 AM, Marko Tiikkaja  wrote:
> On 2015-04-28 19:43, Robert Haas wrote:
>> I guess
>> the root of the problem is that PL/plgsql's cache invalidation logic
>> only considers the pg_proc row's TID and xmin when deciding whether to
>> recompile.  For base types that's probably OK, but for composite
>> types, not so much.
>>
>> Thoughts?
>
> We recently hit a similar case in our production environment.  What was
> annoying about it is that there didn't seem to be a way for the application
> to fix the issue by itself, short of reconnecting; even DISCARD ALL doesn't
> help.  If we can't fix the underlying issue, can we at least provide a way
> for apps to invalidate these caches themselves, for example in the form of a
> DISCARD option?

It's been discussed before and I am in favor of it.  However the
implementation is a bit challenging.  The DISCARD command doesn't know
what PLs may have decided to cache, nonwithstanding the fact that they
all cache basically the same stuff using basically the same method.  I
think the PL interface will need to be extended in some way to support
a new callback.

-- 
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] procost for to_tsvector

[Robert Haas]

On Fri, May 1, 2015 at 9:13 AM, Bruce Momjian  wrote:
> On Fri, May  1, 2015 at 07:57:27AM -0400, Robert Haas wrote:
>> On Thu, Apr 30, 2015 at 9:34 PM, Bruce Momjian  wrote:
>> > On Wed, Mar 11, 2015 at 02:40:16PM +, Andrew Gierth wrote:
>> >> An issue that comes up regularly on IRC is that text search queries,
>> >> especially on relatively modest size tables or for relatively
>> >> non-selective words, often misplan as a seqscan based on the fact that
>> >> to_tsvector has procost=1.
>> >>
>> >> Clearly this cost number is ludicrous.
>> >>
>> >> Getting the right cost estimate would obviously mean taking the cost of
>> >> detoasting into account, but even without doing that, there's a strong
>> >> argument that it should be increased to at least the order of 100.
>> >> (With the default cpu_operator_cost that would make each to_tsvector
>> >> call cost 0.25.)
>> >>
>> >> (The guy I was just helping on IRC was seeing a slowdown of 100x from a
>> >> seqscan in a query that selected about 50 rows from about 500.)
>> >
>> > Where are we on setting increasing procost for to_tsvector?
>>
>> We're waiting for you to commit the patch.
>
> OK, I have to write the patch first, so patch attached, using the cost
> of 10.  I assume to_tsvector() is the ony one needing changes.  The
> patch will require a catalog bump too.

Andrew did the research to support a higher value, but even 10 should
be an improvement over what we have now.

-- 
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] proposal: disallow operator "=>" and use it for named parameters

[Robert Haas]

On Thu, Apr 30, 2015 at 9:11 PM, Bruce Momjian  wrote:
> On Tue, Mar 10, 2015 at 02:51:30PM -0400, Robert Haas wrote:
>> On Tue, Mar 10, 2015 at 2:32 PM, Pavel Stehule  
>> wrote:
>> > 1. funcname_signature_string
>> > 2. get_rule_expr
>>
>> Thanks.  Patch attached.  I'll commit this if there are no objections.
>
> Robert, are you going to apply this?

Good catch.  I had totally forgotten about this.  Committed now, thanks.

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

[HACKERS] postgres_fdw join pushdown (was Re: Custom/Foreign-Join-APIs)

[Robert Haas]

On Mon, Apr 27, 2015 at 5:07 AM, Shigeru Hanada
 wrote:
> 2015-04-27 11:00 GMT+09:00 Kouhei Kaigai :
>> Hanada-san, could you adjust your postgres_fdw patch according to
>> the above new (previous?) definition.
>
> The attached v14 patch is the revised version for your v13 patch.  It also 
> contains changed for Ashutosh’s comments.

We should probably move this discussion to a new thread now that the
other patch is committed.  Changing subject line accordingly.

Generally, there's an awful lot of changes in this patch - it is over
2000 insertions and more than 450 deletions - and it's not awfully
obvious why all of those changes are there.  I think this patch needs
a detailed README to accompany it explaining what the various changes
in the patch are and why those things got changed; or maybe there is a
way to break it up into multiple patches so that we can take a more
incremental approach.  I am really suspicious of the amount of
wholesale reorganization of code that this patch is doing.  It's
really hard to validate that a reorganization like that is necessary,
or that it's correct, and it's gonna make back-patching noticeably
harder in the future.  If we really need this much code churn it needs
careful justification; if we don't, we shouldn't do it.

+SET enable_mergejoin = off; -- planner choose MergeJoin even it has
higher costs, so disable it for testing.

This seems awfully strange.  Why would the planner choose a plan if it
had a higher cost?

-* If the table or the server is configured to use remote estimates,
-* identify which user to do remote access as during planning.  This
+* Identify which user to do remote access as during planning.  This
 * should match what ExecCheckRTEPerms() does.  If we fail due
to lack of
 * permissions, the query would have failed at runtime anyway.
 */
-   if (fpinfo->use_remote_estimate)
-   {
-   RangeTblEntry *rte = planner_rt_fetch(baserel->relid, root);
-   Oid userid = rte->checkAsUser ?
rte->checkAsUser : GetUserId();
-
-   fpinfo->user = GetUserMapping(userid, fpinfo->server->serverid);
-   }
-   else
-   fpinfo->user = NULL;
+   rte = planner_rt_fetch(baserel->relid, root);
+   fpinfo->userid = rte->checkAsUser ? rte->checkAsUser : GetUserId();

So, wait a minute, remote estimates aren't optional any more?

-- 
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] proposal: disallow operator "=>" and use it for named parameters

[Petr Jelinek]

On 01/05/15 15:17, Bruce Momjian wrote:

On Fri, May  1, 2015 at 03:13:28PM +0200, Petr Jelinek wrote:

On 01/05/15 15:01, Bruce Momjian wrote:

On Fri, May  1, 2015 at 05:25:53AM +0200, Pavel Stehule wrote:

It is done


Uh, I am not sure why you say that as I don't see any commit related to
this.  Can you show me the commit?



865f14a2d31af23a05bbf2df04c274629c5d5c4d


But that doesn't touch these:

1. funcname_signature_string
2. get_rule_expr

which is what Robert's later patch did:


http://www.postgresql.org/message-id/ca+tgmobcmf7f50+fejpclr8e_lyv45ayxbsdiog-ns7vluf...@mail.gmail.com



Oh, now I see what you mean, yeah that does not appear to have been 
committed.


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


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

Re: [HACKERS] proposal: disallow operator "=>" and use it for named parameters

[Bruce Momjian]

On Fri, May  1, 2015 at 03:13:28PM +0200, Petr Jelinek wrote:
> On 01/05/15 15:01, Bruce Momjian wrote:
> >On Fri, May  1, 2015 at 05:25:53AM +0200, Pavel Stehule wrote:
> >>It is done
> >
> >Uh, I am not sure why you say that as I don't see any commit related to
> >this.  Can you show me the commit?
> >
> 
> 865f14a2d31af23a05bbf2df04c274629c5d5c4d

But that doesn't touch these:

1. funcname_signature_string
2. get_rule_expr

which is what Robert's later patch did:


http://www.postgresql.org/message-id/ca+tgmobcmf7f50+fejpclr8e_lyv45ayxbsdiog-ns7vluf...@mail.gmail.com

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

  + Everyone has their own god. +


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

Re: [HACKERS] proposal: disallow operator "=>" and use it for named parameters

[Petr Jelinek]

On 01/05/15 15:01, Bruce Momjian wrote:

On Fri, May  1, 2015 at 05:25:53AM +0200, Pavel Stehule wrote:

It is done


Uh, I am not sure why you say that as I don't see any commit related to
this.  Can you show me the commit?



865f14a2d31af23a05bbf2df04c274629c5d5c4d


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


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

Re: [HACKERS] procost for to_tsvector

[Bruce Momjian]

On Fri, May  1, 2015 at 07:57:27AM -0400, Robert Haas wrote:
> On Thu, Apr 30, 2015 at 9:34 PM, Bruce Momjian  wrote:
> > On Wed, Mar 11, 2015 at 02:40:16PM +, Andrew Gierth wrote:
> >> An issue that comes up regularly on IRC is that text search queries,
> >> especially on relatively modest size tables or for relatively
> >> non-selective words, often misplan as a seqscan based on the fact that
> >> to_tsvector has procost=1.
> >>
> >> Clearly this cost number is ludicrous.
> >>
> >> Getting the right cost estimate would obviously mean taking the cost of
> >> detoasting into account, but even without doing that, there's a strong
> >> argument that it should be increased to at least the order of 100.
> >> (With the default cpu_operator_cost that would make each to_tsvector
> >> call cost 0.25.)
> >>
> >> (The guy I was just helping on IRC was seeing a slowdown of 100x from a
> >> seqscan in a query that selected about 50 rows from about 500.)
> >
> > Where are we on setting increasing procost for to_tsvector?
> 
> We're waiting for you to commit the patch.

OK, I have to write the patch first, so patch attached, using the cost
of 10.  I assume to_tsvector() is the ony one needing changes.  The
patch will require a catalog bump too.

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

  + Everyone has their own god. +
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
new file mode 100644
index 55c246e..72d8c8c
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*** DESCR("generate headline");
*** 4653,4665 
  DATA(insert OID = 3755 (  ts_headline	PGNSP PGUID 12 1 0 0 0 f f f f t f s 2 0 25 "25 3615" _null_ _null_ _null_ _null_ _null_ ts_headline _null_ _null_ _null_ ));
  DESCR("generate headline");
  
! DATA(insert OID = 3745 (  to_tsvector		PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3614 "3734 25" _null_ _null_ _null_ _null_ _null_ to_tsvector_byid _null_ _null_ _null_ ));
  DESCR("transform to tsvector");
  DATA(insert OID = 3746 (  to_tsquery		PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3615 "3734 25" _null_ _null_ _null_ _null_ _null_ to_tsquery_byid _null_ _null_ _null_ ));
  DESCR("make tsquery");
  DATA(insert OID = 3747 (  plainto_tsquery	PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3615 "3734 25" _null_ _null_ _null_ _null_ _null_ plainto_tsquery_byid _null_ _null_ _null_ ));
  DESCR("transform to tsquery");
! DATA(insert OID = 3749 (  to_tsvector		PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 3614 "25" _null_ _null_ _null_ _null_ _null_ to_tsvector _null_ _null_ _null_ ));
  DESCR("transform to tsvector");
  DATA(insert OID = 3750 (  to_tsquery		PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 3615 "25" _null_ _null_ _null_ _null_ _null_ to_tsquery _null_ _null_ _null_ ));
  DESCR("make tsquery");
--- 4653,4665 
  DATA(insert OID = 3755 (  ts_headline	PGNSP PGUID 12 1 0 0 0 f f f f t f s 2 0 25 "25 3615" _null_ _null_ _null_ _null_ _null_ ts_headline _null_ _null_ _null_ ));
  DESCR("generate headline");
  
! DATA(insert OID = 3745 (  to_tsvector		PGNSP PGUID 12 10 0 0 0 f f f f t f i 2 0 3614 "3734 25" _null_ _null_ _null_ _null_ _null_ to_tsvector_byid _null_ _null_ _null_ ));
  DESCR("transform to tsvector");
  DATA(insert OID = 3746 (  to_tsquery		PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3615 "3734 25" _null_ _null_ _null_ _null_ _null_ to_tsquery_byid _null_ _null_ _null_ ));
  DESCR("make tsquery");
  DATA(insert OID = 3747 (  plainto_tsquery	PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3615 "3734 25" _null_ _null_ _null_ _null_ _null_ plainto_tsquery_byid _null_ _null_ _null_ ));
  DESCR("transform to tsquery");
! DATA(insert OID = 3749 (  to_tsvector		PGNSP PGUID 12 10 0 0 0 f f f f t f s 1 0 3614 "25" _null_ _null_ _null_ _null_ _null_ to_tsvector _null_ _null_ _null_ ));
  DESCR("transform to tsvector");
  DATA(insert OID = 3750 (  to_tsquery		PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 3615 "25" _null_ _null_ _null_ _null_ _null_ to_tsquery _null_ _null_ _null_ ));
  DESCR("make tsquery");

-- 
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] cache invalidation for PL/pgsql functions

[Marko Tiikkaja]

On 2015-04-28 19:43, Robert Haas wrote:

I guess
the root of the problem is that PL/plgsql's cache invalidation logic
only considers the pg_proc row's TID and xmin when deciding whether to
recompile.  For base types that's probably OK, but for composite
types, not so much.

Thoughts?


We recently hit a similar case in our production environment.  What was 
annoying about it is that there didn't seem to be a way for the 
application to fix the issue by itself, short of reconnecting; even 
DISCARD ALL doesn't help.  If we can't fix the underlying issue, can we 
at least provide a way for apps to invalidate these caches themselves, 
for example in the form of a DISCARD option?



.m


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

Re: [HACKERS] proposal: disallow operator "=>" and use it for named parameters

[Bruce Momjian]

On Fri, May  1, 2015 at 05:25:53AM +0200, Pavel Stehule wrote:
> It is done

Uh, I am not sure why you say that as I don't see any commit related to
this.  Can you show me the commit?

---


> 
> Dne 1.5.2015 3:11 napsal uživatel "Bruce Momjian" :
> 
> On Tue, Mar 10, 2015 at 02:51:30PM -0400, Robert Haas wrote:
> > On Tue, Mar 10, 2015 at 2:32 PM, Pavel Stehule 
> wrote:
> > > 1. funcname_signature_string
> > > 2. get_rule_expr
> >
> > Thanks.  Patch attached.  I'll commit this if there are no objections.
> 
> Robert, are you going to apply this?
> 
> --
>   Bruce Momjian          http://momjian.us
>   EnterpriseDB                             http://enterprisedb.com
> 
>   + Everyone has their own god. +
> 

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

  + Everyone has their own god. +


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

Re: Custom/Foreign-Join-APIs (Re: [HACKERS] [v9.5] Custom Plan API)

[Robert Haas]

On Thu, Apr 30, 2015 at 5:21 PM, Kouhei Kaigai  wrote:
> I wanted to submit the v14 after the above items get clarified.
> The attached patch (v14) includes all what you suggested in the previous
> message.

Committed, after heavily working over the documentation, and with some
more revisions to the comments as well.

-- 
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] transforms vs. CLOBBER_CACHE_ALWAYS

[Andrew Dunstan]

On 04/30/2015 09:09 PM, Christian Ullrich wrote:

* Andrew Dunstan:


friarbird is a FreeBSD buildfarm animal running with
-DCLOBBER_CACHE_ALWAYS. It usually completes a run in about 6.5 hours.
However, it's been stuck since Monday running the plpython regression
tests. The only relevant commit seems to be the transforms feature.
Here's what it's been doing:



query| SELECT cursor_plan();


Same here, on jaguarundi. I actually killed it intentionally this 
morning, hoping that whatever the problem was might have been fixed 
already. No such luck.


I would suspect that it might have something to do with the OS, if all 
the other CCA animals weren't lining up nicely behind in the buildfarm 
status page.



I imagine it was in some sort of infinite loop. gdb says it's all in
src/backend/utils/cache/plancache.c, although not the same line each
time I run it.


I ktrace'd it this morning, but cleverly did not keep the dump. It 
looked much the same to me, though, it was reading the same filenode 
over and over again.





Yeah, this happened again this morning, so it seems to be quite reliably 
reproducible. I killed it and I've set friarbird to build without python 
for now, but this is clearly an issue that needs to be resolved.


Side thought - maybe we need some sort of timeout mechanism for the 
buildfarm to try to stop it from hanging. There is actually some timeout 
code in there from back in the CVS days when occasionally CVS would 
hang. It could be adapted to timeout other steps.


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] initdb -S and tablespaces

[Abhijit Menon-Sen]

At 2015-05-01 08:10:16 -0400, robertmh...@gmail.com wrote:
>
> It seems to me that, at a minimum, it would be good to split those
> controversial and definitely not-back-patchable changes into their
> own patch.

OK, split here (0002*).

> I do mind putting it into xlog.c instead of some place that's actually
> appropriate.

OK, moved to storage/file/fd.c (0001*).

-- Abhijit
>From 088b80eb0825339eca688e4347a4ef547edcadbb Mon Sep 17 00:00:00 2001
From: Abhijit Menon-Sen 
Date: Thu, 6 Nov 2014 00:45:56 +0530
Subject: Recursively fsync PGDATA at startup after a crash

This is so that we don't lose older unflushed writes in the event of
a power failure after crash recovery, where more recent writes are
preserved.

See 20140918083148.ga17...@alap3.anarazel.de for details.
---
 src/backend/access/transam/xlog.c |  49 +
 src/backend/storage/file/fd.c | 112 ++
 src/include/storage/fd.h  |   2 +
 3 files changed, 163 insertions(+)

diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 6cf4415..084174d 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -845,6 +845,8 @@ static void WALInsertLockAcquireExclusive(void);
 static void WALInsertLockRelease(void);
 static void WALInsertLockUpdateInsertingAt(XLogRecPtr insertingAt);
 
+static void fsync_pgdata(char *datadir);
+
 /*
  * Insert an XLOG record represented by an already-constructed chain of data
  * chunks.  This is a low-level routine; to construct the WAL record header
@@ -5878,6 +5880,25 @@ StartupXLOG(void)
 		ereport(FATAL,
 (errmsg("control file contains invalid data")));
 
+	/*
+	 * If we need to perform crash recovery, we issue an fsync on the
+	 * data directory and its contents to try to ensure that any data
+	 * written before the crash are flushed to disk. Otherwise a power
+	 * failure in the near future might cause earlier unflushed writes
+	 * to be lost, even though more recent data written to disk from
+	 * here on would be persisted.
+	 *
+	 * We also do this if the control file indicates that fsync was
+	 * disabled at some point while the server was running earlier.
+	 */
+
+	if (enableFsync &&
+		(ControlFile->state != DB_SHUTDOWNED &&
+		 ControlFile->state != DB_SHUTDOWNED_IN_RECOVERY))
+	{
+		fsync_pgdata(data_directory);
+	}
+
 	if (ControlFile->state == DB_SHUTDOWNED)
 	{
 		/* This is the expected case, so don't be chatty in standalone mode */
@@ -11123,3 +11144,31 @@ SetWalWriterSleeping(bool sleeping)
 	XLogCtl->WalWriterSleeping = sleeping;
 	SpinLockRelease(&XLogCtl->info_lck);
 }
+
+/*
+ * Issue fsync recursively on PGDATA and all its contents.
+ */
+static void
+fsync_pgdata(char *datadir)
+{
+	if (!enableFsync)
+		return;
+
+	/*
+	 * If possible, hint to the kernel that we're soon going to fsync
+	 * the data directory and its contents.
+	 */
+#if defined(HAVE_SYNC_FILE_RANGE) || \
+	(defined(USE_POSIX_FADVISE) && defined(POSIX_FADV_DONTNEED))
+	walkdir(datadir, pre_sync_fname);
+#endif
+
+	/*
+	 * Now we do the fsync()s in the same order.
+	 *
+	 * It's important to fsync the destination directory itself as individual
+	 * file fsyncs don't guarantee that the directory entry for the file is
+	 * synced.
+	 */
+	walkdir(datadir, fsync_fname);
+}
diff --git a/src/backend/storage/file/fd.c b/src/backend/storage/file/fd.c
index f796717..aba12ca 100644
--- a/src/backend/storage/file/fd.c
+++ b/src/backend/storage/file/fd.c
@@ -2439,3 +2439,115 @@ looks_like_temp_rel_name(const char *name)
 		return false;
 	return true;
 }
+
+/*
+ * Hint to the OS that it should get ready to fsync() this file.
+ *
+ * Adapted from pre_sync_fname in initdb.c
+ */
+void
+pre_sync_fname(char *fname, bool isdir)
+{
+	int			fd;
+
+	fd = open(fname, O_RDONLY | PG_BINARY);
+
+	/*
+	 * Some OSs don't allow us to open directories at all (Windows returns
+	 * EACCES)
+	 */
+	if (fd < 0 && isdir && (errno == EISDIR || errno == EACCES))
+		return;
+
+	if (fd < 0)
+		ereport(FATAL,
+(errmsg("could not open file \"%s\" before fsync",
+		fname)));
+
+	pg_flush_data(fd, 0, 0);
+
+	close(fd);
+}
+
+/*
+ * walkdir: recursively walk a directory, applying the action to each
+ * regular file and directory (including the named directory itself)
+ * and following symbolic links.
+ */
+void
+walkdir(char *path, void (*action) (char *fname, bool isdir))
+{
+	DIR		   *dir;
+	struct dirent *de;
+
+	dir = AllocateDir(path);
+	while ((de = ReadDir(dir, path)) != NULL)
+	{
+		char		subpath[MAXPGPATH];
+		struct stat fst;
+
+		CHECK_FOR_INTERRUPTS();
+
+		if (strcmp(de->d_name, ".") == 0 ||
+			strcmp(de->d_name, "..") == 0)
+			continue;
+
+		snprintf(subpath, MAXPGPATH, "%s/%s", path, de->d_name);
+
+		if (lstat(subpath, &fst) < 0)
+			ereport(ERROR,
+	(errcode_for_file_access(),
+	 errmsg("could not stat file \"%s\": %m", subpath)));
+
+		if (S_ISREG(fst.st_mode))
+			(*action) (subpath, false);
+		else if (S_ISDIR(fst.

Re: [HACKERS] Auditing extension for PostgreSQL (Take 2)

[David Steele]

On 5/1/15 5:58 AM, Sawada Masahiko wrote:
> On Fri, May 1, 2015 at 6:24 AM, David Steele  wrote:
>>
>> May 15th is the feature freeze, so that does give a little time.  It's
>> not clear to me what a "self-contained" part of the patch would be.  If
>> you have specific ideas on what could be broken out I'm interested to
>> hear them.
>>
>> Patch v11 is attached with the changes discussed here plus some other
>> improvements to the documentation suggested by Erik.
>>
> 
> For now, since pg_audit patch has a pg_audit_ddl_command_end()
> function which uses part of un-committed "deparsing utility commands"
> patch API,
> pg_audit patch is completely depend on that patch.
> If API name, interface are changed, it would affect for pg_audit patch.
> I'm not sure about progress of "deparsing utility command" patch but
> you have any idea if that patch is not committed into 9.5 until May
> 15?

Currently the deparse dependent code is ifdef'd so pg_audit compiles and
operates just fine against master.  Having the deparse code is nice
because it allows less common object types to log with full-qualified
names but it is in not a requirement for pg_audit.

I'd like to see at least patch 0001 of deparse committed.  Not only
because it provides the functionality that deparse uses, but because it
makes event triggers truly useful in pl/pgsql.

-- 
- David Steele
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] initdb -S and tablespaces

[Robert Haas]

On Thu, Apr 30, 2015 at 11:29 PM, Abhijit Menon-Sen  
wrote:
>> 2. I don't know why it's part of this patch.
>
> In 20150115133245.gg5...@awork2.anarazel.de, Andres explained his
> rationale as follows:
>
> «What I am thinking of is that, currently, if you start the server
> for initial loading with fsync=off, and then restart it, you're open
> to data loss. So when the current config file setting is changed
> from off to on, we should fsync the data directory. Even if there
> was no crash restart.»

That's awfully clever, but I'm not sure I like the idea of trying to
be that clever.  I think if users temporarily disable fsync, they
should be responsible for using initdb -S after if that is needed in
their situation, and this should be documented.

It seems to me that, at a minimum, it would be good to split those
controversial and definitely not-back-patchable changes into their own
patch.

>> Also, it seems awfully unfortunate to me that we're duplicating a
>> whole pile of code into xlog.c here.
>
> I have pointed out and discussed the duplication several times. I did it
> this way only because we were considering backporting the changes, and
> at the time it seemed better to do this and fix the duplication in a
> separate patch.

As I've mentioned a few times, I don't mind duplicating the code if we
have frontend and backend versions that are materially different.  But
I do mind putting it into xlog.c instead of some place that's actually
appropriate.

-- 
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] Proposal : REINDEX xxx VERBOSE

[Robert Haas]

On Thu, Apr 30, 2015 at 11:05 PM, Sawada Masahiko  wrote:
> VACUUM has both syntax: with parentheses and without parentheses.
> I think we should have both syntax for REINDEX like VACUUM does
> because it would be pain to put parentheses whenever we want to do
> REINDEX.
> Are the parentheses optional in REINDEX command?

No.  The unparenthesized VACUUM syntax was added back before we
realized that that kind of syntax is a terrible idea.  It requires
every option to be a keyword, and those keywords have to be in a fixed
order.  I believe the intention is to keep the old VACUUM syntax
around for backward-compatibility, but not to extend it.  Same for
EXPLAIN and COPY.

I agree that it would be nice if the grammar problems could be solved
without adding parentheses.  But there was a period during which many
good ideas for new EXPLAIN options died on the vine because we were
using an inextensible syntax for EXPLAIN options.   I'm not keen to
repeat that experience.

-- 
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] procost for to_tsvector

[Robert Haas]

On Thu, Apr 30, 2015 at 9:34 PM, Bruce Momjian  wrote:
> On Wed, Mar 11, 2015 at 02:40:16PM +, Andrew Gierth wrote:
>> An issue that comes up regularly on IRC is that text search queries,
>> especially on relatively modest size tables or for relatively
>> non-selective words, often misplan as a seqscan based on the fact that
>> to_tsvector has procost=1.
>>
>> Clearly this cost number is ludicrous.
>>
>> Getting the right cost estimate would obviously mean taking the cost of
>> detoasting into account, but even without doing that, there's a strong
>> argument that it should be increased to at least the order of 100.
>> (With the default cpu_operator_cost that would make each to_tsvector
>> call cost 0.25.)
>>
>> (The guy I was just helping on IRC was seeing a slowdown of 100x from a
>> seqscan in a query that selected about 50 rows from about 500.)
>
> Where are we on setting increasing procost for to_tsvector?

We're waiting for you to commit the patch.

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