Re: [HACKERS] UPDATE of partition key

[David G. Johnston]

On Friday, February 24, 2017, Robert Haas  wrote:

> On Mon, Feb 20, 2017 at 2:58 PM, Amit Khandekar  > wrote:
> > I am inclined to at least have some option for the user to decide the
> > behaviour. In the future we can even consider support for walking
> > through the ctid chain across multiple relfilenodes. But till then, we
> > need to decide what default behaviour to keep. My inclination is more
> > towards erroring out in an unfortunate even where there is an UPDATE
> > while the row-movement is happening. One option is to not get into
> > finding whether the DELETE was part of partition row-movement or it
> > was indeed a DELETE, and always error out the UPDATE when
> > heap_update() returns HeapTupleUpdated, but only if the table is a
> > leaf partition. But this obviously will cause annoyance because of
> > chances of getting such errors when there are concurrent updates and
> > deletes in the same partition. But we can keep a table-level option
> > for determining whether to error out or silently lose the UPDATE.
>
> I'm still a fan of the "do nothing and just document that this is a
> weirdness of partitioned tables" approach, because implementing
> something will be complicated, will ensure that this misses this
> release if not the next one, and may not be any better for users.  But
> probably we need to get some more opinions from other people, since I
> can imagine people being pretty unhappy if the consensus happens to be
> at odds with my own preferences.
>
>
For my own sanity - the move update would complete successfully and break
every ctid chain that it touches.  Any update lined up behind it in the
lock queue would discover their target record has been deleted and
would experience whatever behavior their isolation level dictates for such
a situation.  So multi-partition update queries will fail to update some
records if they happen to move between partitions even if they would
otherwise match the query's predicate.

Is there any difference in behavior between this and a SQL writeable CTE
performing the same thing via delete-returning-insert?

David J.

Re: [HACKERS] FYI: git worktrees as replacement for "rsync the CVSROOT"

[Craig Ringer]

On 16 January 2017 at 05:01, Jim Nasby  wrote:
> Not sure how many people still use [1], as referenced by our git wiki[2],
> but it appears git worktrees are a viable replacement for that technique. In
> short, if you're already in your checkout:
>
> git worktree add ../9.6 REL9_6_STABLE
>
> would give you a checkout of 9.6 in the ../9.6 directory.
>
> BTW, I learned about this from this "git year in review" article[3].

Looks handy enough to merit adding to the Pg developer FAQ. Please?

It looks cleaner than my current approach of doing a local clone or
re-cloning from upstream with a local repo as a --reference .

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

[Craig Ringer]

On 23 February 2017 at 22:20, Tom Lane  wrote:

>> * Don't force/generate an alias at all.
>
>> I've no idea for this yet and Tom already was concerned what this might
>> break. There are several places in the transform phase where the
>> refnames are required (e.g. isLockedRefname()).
>
> Yeah.  This would be cleaner in some sense but also a lot more delicate.
> Not sure it's worth the trouble.

Personally I think we need to generate one, if nothing else for error
messages where we try to emit qualified names of columns.

But I don't see that the name needs to be anything we can refer to
elsewhere or anything faintly sane to type. Something like:

  ""

in line with our current generation of refcursor names.

We could reserve use of these names elsewhere, and probably should do
so to stop them leaking out if something manages to get hold of them
and re-use them via CREATE TABLE AS or something. But it seems
unlikely enough that I'm not too fussed if we don't explicitly reserve
them. Nobody is going to have a ref named ""
already, I'm not concerned about BC if we do this.

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

[Robert Haas]

On Thu, Feb 23, 2017 at 4:08 PM, Pantelis Theodosiou  wrote:
> Question: Will the patch be removed if and when Oracle decides to be
> compatible with the standard and forbids non-aliased derived tables?
>
> (I know it's a rather theoretical question. Unlikely that Oracle breaks
> backwards compatibility on that.)

Even if they did, so what?

First of all, our project's aim is not to copy Oracle slavishly but to
build a good database.  Sometimes that involves making things work in
ways similar to Oracle and sometimes it doesn't.  For example, I have
no urge to get rid of transactional DDL just because Oracle doesn't
have it.  I have no feeling that NULL should behave in the completely
unprincipled way that it does in Oracle.  And I don't think that
PostGIS needs to try to go be more like Oracle Spatial.

Secondly, extensions to the standard that let reasonable things work
which the standard doesn't permit are generally a good idea.  We don't
want to let things work that really deserve to fail - for example
because the meaning is ambiguous - nor do we want to implement
standard syntax with non-standard semantics.  However, neither of
those problems exists for this case.  I don't see the point in making
things fail that could just as easily do what was wanted; that seems
pedantic.  I don't think it's only Oracle that allows omitting the
alias; I think there are a number of other systems that behave
similarly.

-- 
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] Poor memory context performance in large hash joins

[Andres Freund]

On 2017-02-24 01:59:01 -0500, Tom Lane wrote:
> Andres Freund  writes:
> > On 2017-02-23 17:28:26 -0500, Tom Lane wrote:
> >> Maybe it's time to convert that to a doubly-linked list.
>
> > Yes, I do think so. Given that we only have that for full blocks, not
> > for small chunks, the cost seems neglegible.
> > That would also, partially, address the performance issue
> > http://archives.postgresql.org/message-id/d15dff83-0b37-28ed-0809-95a5cc7292ad%402ndquadrant.com
> > addresses, in a more realistically backpatchable manner.
>
> Yeah, I was wondering if we could get away with back-patching such a
> change.  In principle, nothing outside aset.c should know what's in the
> header of an AllocBlock, but ...

You'd need to go through a fair amount of intentional pain to be
affected by a change AllocBlockData's structure.  We could add the
->prev pointer to the end of AllocBlockData's definition to make it less
likely that one would be affected in that unlikely case - but I'm a bit
doubtful it's worth the trouble.

- 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] increasing the default WAL segment size

[Beena Emerson]

On Mon, Feb 20, 2017 at 2:47 PM, Beena Emerson 
wrote:

> Hello,
>
> On Thu, Feb 16, 2017 at 3:37 PM, Kuntal Ghosh 
> wrote:
>
>> On Mon, Feb 6, 2017 at 11:09 PM, Beena Emerson 
>> wrote:
>> >
>> > Hello,
>> > PFA the updated patches.
>> I've started reviewing the patches.
>> 01-add-XLogSegmentOffset-macro.patch looks clean to me. I'll post my
>> detailed review after looking into the second patch. But, both the
>> patches needs a rebase based on the commit 85c11324cabaddcfaf3347df7
>> (Rename user-facing tools with "xlog" in the name to say "wal").
>>
>>
>
> PFA the rebased patches.
>
>
> Hello,

The recent commit  c29aff959dc64f7321062e7f33d8c6ec23db53d has again
changed the code and the second patch cannot be applied cleanly. Please
find attached the rebased 02 patch. 01 patch is the same .



-- 
Thank you,

Beena Emerson

Have a Great Day!


01-add-XLogSegmentOffset-macro.patch
Description: Binary data


02-initdb-walsegsize-v2.patch
Description: Binary data

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

Re: [HACKERS] Make subquery alias optional in FROM clause

[Robert Haas]

On Thu, Feb 23, 2017 at 7:50 PM, Tom Lane  wrote:
> Maybe the best answer is to not do it immediately when transforming the
> subselect's RTE, but to go back after we've finished transforming the
> entire FROM clause and add aliases to any RTEs that lack them.  I think
> probably at that point you could just examine the RTE list and not worry
> about any jointree data structures at all.  It might be worth adding a
> flag to ParseState to remember whether any aliases require assignment, so
> that you could skip looking in the standard case.

Right, something along these lines makes sense to me.  If we try to
generate an alias before we know what all the things that it might
later conflict with are, then somebody will always be able to write a
query where it breaks.

>> I've no idea for this yet and Tom already was concerned what this might
>> break. There are several places in the transform phase where the
>> refnames are required (e.g. isLockedRefname()).
>
> Yeah.  This would be cleaner in some sense but also a lot more delicate.
> Not sure it's worth the trouble.

It sounds like a lot of trouble.  It does also sound nicer.  I suppose
whether it's worth the trouble is for the person doing the work to
decide.

-- 
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] UPDATE of partition key

[Robert Haas]

On Mon, Feb 20, 2017 at 2:58 PM, Amit Khandekar  wrote:
> I am inclined to at least have some option for the user to decide the
> behaviour. In the future we can even consider support for walking
> through the ctid chain across multiple relfilenodes. But till then, we
> need to decide what default behaviour to keep. My inclination is more
> towards erroring out in an unfortunate even where there is an UPDATE
> while the row-movement is happening. One option is to not get into
> finding whether the DELETE was part of partition row-movement or it
> was indeed a DELETE, and always error out the UPDATE when
> heap_update() returns HeapTupleUpdated, but only if the table is a
> leaf partition. But this obviously will cause annoyance because of
> chances of getting such errors when there are concurrent updates and
> deletes in the same partition. But we can keep a table-level option
> for determining whether to error out or silently lose the UPDATE.

I'm still a fan of the "do nothing and just document that this is a
weirdness of partitioned tables" approach, because implementing
something will be complicated, will ensure that this misses this
release if not the next one, and may not be any better for users.  But
probably we need to get some more opinions from other people, since I
can imagine people being pretty unhappy if the consensus happens to be
at odds with my own preferences.

> Another option I was thinking : When the UPDATE is on a partition key,
> acquire ExclusiveLock (not AccessExclusiveLock) only on that
> partition, so that the selects will continue to execute, but
> UPDATE/DELETE will wait before opening the table for scan. The UPDATE
> on partition key is not going to be a very routine operation, it
> sounds more like a DBA maintenance operation; so it does not look like
> it would come in between usual transactions.

I think that's going to make users far more unhappy than breaking the
EPQ behavior ever would.

-- 
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] Poor memory context performance in large hash joins

[Thomas Munro]

On Fri, Feb 24, 2017 at 12:17 PM, Andres Freund  wrote:
> Jeff, do you have a handy demonstrator?

If you want to hit ExecHashIncreaseNumBatches a bunch of times, see
the "ugly" example in the attached.

-- 
Thomas Munro
http://www.enterprisedb.com


hj-test-queries.sql
Description: Binary data

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

Re: [HACKERS] Poor memory context performance in large hash joins

[Tom Lane]

Andres Freund  writes:
> On 2017-02-23 17:28:26 -0500, Tom Lane wrote:
>> Maybe it's time to convert that to a doubly-linked list.

> Yes, I do think so. Given that we only have that for full blocks, not
> for small chunks, the cost seems neglegible.
> That would also, partially, address the performance issue
> http://archives.postgresql.org/message-id/d15dff83-0b37-28ed-0809-95a5cc7292ad%402ndquadrant.com
> addresses, in a more realistically backpatchable manner.

Yeah, I was wondering if we could get away with back-patching such a
change.  In principle, nothing outside aset.c should know what's in the
header of an AllocBlock, but ...

> Jeff, do you have a handy demonstrator?

A solid test case would definitely help to convince people that it was
worth taking some risk here.

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] \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless)

[Fabien COELHO]

About v17:

Patch applies, "make check" & psql "make check" ok.


... '@' [...] I noticed that it takes precedence over '!'. [...]


My reasoning was this: if you're in a false block, and you're not connected
to a db, the \c isn't going to work for you until you get out of the false
block, so right now being in a false block is a bigger problem than not
being connected to a db. [...]


Ok.


It could be nice to keep test cases that show what may happen?


Restored. It looks weird now, but it fixes the unterminated quoted 
string.


Typo "unterminted".

I think I found an issue while testing in interactive:

 calvin=# \if false
 calvin@#   \if false
 calvin@# \echo false-false
   command ignored, use \endif or Ctrl-C to exit current branch.
 calvin@#   \endif
 calvin=#   \echo in false
   in false

The \if within the \if false branch is not tallied properly? Am I missing 
something?


Maybe more test cases should be added to check that nesting checks do work 
properly?



Maybe the documentation could add some kind of warning about that?


I changed the paragraph to



   Lines within false branches are parsed normally, however, any completed
   queries are not sent to the server, and any completed commands other
   than conditionals (\if, \elif,
   \else, \endif) are ignored.


I'm not sure about the ", however, " commas, but I'm sure that I do not 
know English punctuation rules:-)


Maybe the sentence could be cut in shorter pieces.

I think that the fact that "if" commands are checked for proper nesting 
could be kept in the explanation.



There's no mention that psql variables AREN'T expanded, so the user has
every expectation that they are.


Ok.

--
Fabien.


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

Re: [HACKERS] Should we cacheline align PGXACT?

[Ashutosh Sharma]

On Fri, Feb 24, 2017 at 10:41 AM, Simon Riggs  wrote:
> On 24 February 2017 at 04:41, Ashutosh Sharma  wrote:
>>
>> Okay. As suggested by Alexander, I have changed the order of reading and
>> doing initdb for each pgbench run. With these changes, I got following
>> results at 300 scale factor with 8GB of shared buffer.
>
>
> Would you be able to test my patch also please?
>

Sure, I can do that and share the results by early next week. Thanks.


-- 
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] Poor memory context performance in large hash joins

[Andres Freund]

On 2017-02-23 17:28:26 -0500, Tom Lane wrote:
> Jeff Janes  writes:
> > The number of new chunks can be almost as as large as the number of old
> > chunks, especially if there is a very popular value.  The problem is that
> > every time an old chunk is freed, the code in aset.c around line 968 has to
> > walk over all the newly allocated chunks in the linked list before it can
> > find the old one being freed.  This is an N^2 operation, and I think it has
> > horrible CPU cache hit rates as well.
> 
> Maybe it's time to convert that to a doubly-linked list.

Yes, I do think so. Given that we only have that for full blocks, not
for small chunks, the cost seems neglegible.

That would also, partially, address the performance issue
http://archives.postgresql.org/message-id/d15dff83-0b37-28ed-0809-95a5cc7292ad%402ndquadrant.com
addresses, in a more realistically backpatchable manner.

Jeff, do you have a handy demonstrator?


> Although if the
> hash code is producing a whole lot of requests that are only a bit bigger
> than the separate-block threshold, I'd say It's Doing It Wrong.  It should
> learn to aggregate them into larger requests.

That's probably right, but we can't really address that in the
back-branches.  And to me this sounds like something we should address
in the branches, not just in master.  Even if we'd also fix the
hash-aggregation logic, I think such an O(n^2) behaviour in the
allocator is a bad idea in general, and we should fix it anyway.

Regards,

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] Proposal: GetOldestXminExtend for ignoring arbitrary vacuum flags

[Craig Ringer]

On 14 February 2017 at 14:19, Seki, Eiji  wrote:
> Hi all,
>
> I propose the patch that adds a function GetOldestXminExtended that is like 
> GetOldestXmin but can ignore arbitrary vacuum flags. And then, rewrite 
> GetOldestXmin to use it. Note that this is done so as not to change the 
> behavior of GetOldestXmin.

FWIW, I have changes in the logical decoding on standby patch that
also need to extend GetOldestXmin. Specifically, I need to be able to
return the catalog_xmin separately, rather than the current behaviour
of returning what's effectively min(xmin,catalog_xmin).

It's only loosely related to this change, but may be relevant.

-- 
 Craig Ringer   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] case_preservation_and_insensitivity = on

[Robert Haas]

On Thu, Feb 23, 2017 at 6:59 PM, Tom Lane  wrote:
> I think these are straw-man arguments, really.  Consider the actual use
> case for such a feature: it's for porting some application that was not
> written against Postgres to begin with.

I'm not sure that's totally true.  I think at least some requests for
this feature are intended at satisfying somebody's sense of
aesthetics.  But I'll agree that the biggest use case is probably
porting applications from other systems.  Even then, though, people
end up using PG-specific features as a way of working around stuff
that other systems support using different syntax - e.g. T-SQL that
works on SQL server might get turned into a DO block for PostgreSQL,
and it's surprising if that causes you to suddenly get different
case-folding rules.  Also, people sometimes start by doing a port, and
then later start using PG-specific features, which introduces the same
kinds of problems.

> The odds that such an app uses DO
> blocks are exactly zero.  It may well make use of server-side functions,
> but those are going to require significant porting effort in any case.
> So I think a case-folding function that could be interposed in front of
> libpq or JDBC or $favorite_driver could well go a long way towards solving
> that sort of problem.  It wouldn't be 100%, sure, but if you hold your
> breath and wait for a 100% solution to appear on the server side, you're
> going to be quite blue.

True, but that doesn't make a leaky abstraction not a hack.  It just
makes having a hack a practical improvement over not having one -- and
I'm perfectly happy to concede that hacks are sometimes useful.

-- 
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] Documentation improvements for partitioning

[Robert Haas]

On Fri, Feb 24, 2017 at 9:53 AM, Simon Riggs  wrote:
> The good news is that logical replication DOES work with partitioning,
> but only for a Publication with PUBLISH INSERT, pushing from a normal
> table to a partitioned one. Which is useful enough for first release.
>
> The work on having UPDATE work between partitions can be used to make
> updates and deletes work in logical replication. That might yet be
> made to work in this release, and I think we should look into it, but
> I think it can be left til next release if we try.

Are you speaking of the case where you want to replicate from an
unpartitioned table to a partitioned table?  I would imagine that if
you are replicating between two identical partitioning hierarchies, it
would just work.  (If not, that's probably a bug, though I don't know
whose bug.)

>> You don't get to show up more than two
>> months after the feature is committed and start complaining that it
>> doesn't include all the things you want.  Which things ought to be
>> included in the initial patch was under active discussion about a year
>> ago this time.
>
> I've provided lots of input across many years, for example my
> explanation of how to do tuple routing is very clearly the basis of
> the design of the current feature. You *knew* I would have feedback
> and if it it hadn't appeared yet you knew it would come.

Not really.  I can't always predict who will take an interest in a
patch.  I'm not surprised that you have feedback on it, and if that
feedback were phrased as "let's try to do X, Y, and Z in future
releases" I'd have no problem with it.  What I'm reacting against is
really the idea that any of this should be done in v10 (and also the
idea, which may be an overly defensive reading of your emails, that my
original commit was somehow not up to the mark).

> The "two months after the feature is committed" is a direct result of
> the lack of docs. Note that within hours of reading the docs I'd given
> feedback. How could I give feedback earlier? Well, I tried.

Quite a few other people managed to give feedback earlier, so it
evidently wasn't totally impossible.

> I've flown
> to Japan to ensure I could talk to Amit in person about this feature.
> Your absence at two consecutive developer meetings hasn't helped
> there. It is you that needs to show up more. I'm sure we both have
> family and work reasons not to attend them.

We used to have one developer meeting a year and I have attended it
every year I was invited.  Then it went up to two per year and I kept
attending one of them per year.  Now it seems to be three per year and
I plan to keep attending one of them per year, unless one of the
others happens to be scheduled together with an event I'm planning to
attend anyway.  As you say, for both family and work reasons, it's
hard to commit to doing multiple such events per year.

> The need for design feedback is exactly why the docs for logical
> replication were published in June, six months before logical
> replication was committed.

Sure, I think that's great, but there's never been an obligation to
write the documentation before the code in the PostgreSQL community.
It's not a bad idea and I'm happy if people do it, but I'm not going
to start refusing to commit the patches of people who don't do it.
Probably 25% of patches have no documentation at all in the first
version and that gets added later once some consensus is reached and
the feature gets closer to commit; I think that's just fine.  For
larger features, the documentation often gets expanded after the
initial commit; I think that's also fine.  Unlike you, I actually
don't think it's very hard to follow the discussion about the design
of these features in most cases, and it speeds up development if every
change in the proposed design doesn't require an adjustment to both
the documentation and the code.  I think the way logical replication
was done was reasonable, and I think the way that partitioning was
done was also reasonable.

> With repect, you are making a few mistakes. The first is to imagine
> that review comments are negative or complaints; with the right
> viewpoint they could easily be seen as helping people to see what has
> been missed, yet you repeatedly see them as personal attacks and throw
> words back. Oh sure, I've done that myself in earlier days.

Sure.

> The second
> is to imagine that discussing things on Hackers in multiple threads,
> spanning many months with long, detailed emails and rapid replies is
> something that anybody could have followed if they wished.

I don't really see how that's a mistake.  It might take more time than
someone's willing to put in -- I have that problem too, on some
threads -- but if someone has the time and is willing to spend it,
then they can follow that discussion.

> And the
> third is to imagine I have no right to review; I will watch and see if
> you deploy this same "You don't get to show up.." argument against Tom
> 

[HACKERS] utility commands benefiting from parallel plan

[Haribabu Kommi]

Hi Hackers,

Here I attached an implementation patch that allows
utility statements that have queries underneath such as
CREATE TABLE AS, CREATE MATERIALIZED VIEW
and REFRESH commands to benefit from parallel plan.

These write operations not performed concurrently by the
parallel workers, but the underlying query that is used by
these operations are eligible for parallel plans.

Currently the write operations are implemented for the
tuple dest types DestIntoRel and DestTransientRel.

Currently I am evaluating other write operations that can
benefit with parallelism without side effects in enabling them.

comments?

Regards,
Hari Babu
Fujitsu Australia


utlity_write_with_query_parallel_1.patch
Description: Binary data

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

Re: [HACKERS] Enabling parallelism for queries coming from SQL or other PL functions

[Dilip Kumar]

On Fri, Feb 24, 2017 at 10:06 AM, Amit Kapila  wrote:
> We have a below check in standard_planner() (!IsParallelWorker())
> which should prohibit generating parallel plan inside worker, if that
> is what you are seeing, then we might need a similar check at other
> places.
>
> if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
> IsUnderPostmaster &&
> dynamic_shared_memory_type != DSM_IMPL_NONE &&
> parse->commandType == CMD_SELECT &&
> !parse->hasModifyingCTE &&
> max_parallel_workers_per_gather > 0 &&
> !IsParallelWorker() &&
> !IsolationIsSerializable())
> {
> /* all the cheap tests pass, so scan the query tree */
> glob->maxParallelHazard = max_parallel_hazard(parse);
> glob->parallelModeOK = (glob->maxParallelHazard != PROPARALLEL_UNSAFE);
> }

Ok, I see. But, the problem with PL functions is that plan might have
already generated in previous execution of the function and during
that time outer query might not be running in parallel. So I think we
may need some check during execution time as well?

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.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] Range Partitioning behaviour - query

[Venkata B Nagothi]

On Fri, Feb 24, 2017 at 12:38 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Feb 23, 2017 at 6:17 PM, Amit Langote <
> langote_amit...@lab.ntt.co.jp> wrote:
>
>> On 2017/02/24 8:38, Venkata B Nagothi wrote:
>> > On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote wrote:
>> >> Upper bound of a range partition is an exclusive bound.  A note was
>> added
>> >> recently to the CREATE TABLE page to make this clear.
>> >>
>> >> https://www.postgresql.org/docs/devel/static/sql-createtable.html
>> >
>> >
>> > Thanks. Actually, my confusion was that the upper bound value would be
>> > included when "TO" clause is used in the syntax.
>>
>> Hmm, TO sounds like it implies inclusive.
>>
>
> ​I think most common usage of the word ends up being inclusive but the
> word itself doesn't really care.​
>
> Dictionary.com has a good example:
>
> "We work from nine to five." - you leave at the beginning of the 5 o'clock
> hour (I'm going for casual usage here)
>

True.


> Since our implementation of ranges is half-open the usage here is
> consistent with that concept.  That it doesn't match BETWEEN is actually
> somewhat nice since you can use ranges for half-open and BETWEEN if you
> want to be concise with fully-closed endpoints.  But it is one more thing
> to remember.
>

Agreed.

Regards,

Venkata B N
Database Consultant

Re: [HACKERS] Range Partitioning behaviour - query

[Venkata B Nagothi]

On Fri, Feb 24, 2017 at 1:01 PM, Amit Langote  wrote:

> On 2017/02/24 10:38, David G. Johnston wrote:
> > On Thu, Feb 23, 2017 at 6:17 PM, Amit Langote <
> langote_amit...@lab.ntt.co.jp
> >> wrote:
> >
> >> On 2017/02/24 8:38, Venkata B Nagothi wrote:
> >>> On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote wrote:
>  Upper bound of a range partition is an exclusive bound.  A note was
> >> added
>  recently to the CREATE TABLE page to make this clear.
> 
>  https://www.postgresql.org/docs/devel/static/sql-createtable.html
> >>>
> >>>
> >>> Thanks. Actually, my confusion was that the upper bound value would be
> >>> included when "TO" clause is used in the syntax.
> >>
> >> Hmm, TO sounds like it implies inclusive.
> >>
> >
> > ​I think most common usage of the word ends up being inclusive but the
> word
> > itself doesn't really care.​
> >
> > Dictionary.com has a good example:
> >
> > "We work from nine to five." - you leave at the beginning of the 5
> o'clock
> > hour (I'm going for casual usage here)
>
> Thanks for that example.
>
> One problem I've seen people mention is one of cognitive dissonance of
> having to define partition_y2013 as FROM ('2013-01-01') TO ('2014-01-01'),
> given that that's the only way to get what one needs. But we concluded
> that that's a reasonable compromise.
>

Agreed. I do see the similar approach adopted across other traditional
RDBMS products as well.

Regards,

Venkata B N
Database Consultant

Re: [HACKERS] Should we cacheline align PGXACT?

[Simon Riggs]

On 24 February 2017 at 04:41, Ashutosh Sharma  wrote:

> Okay. As suggested by Alexander, I have changed the order of reading and
> doing initdb for each pgbench run. With these changes, I got following
> results at 300 scale factor with 8GB of shared buffer.
>

Would you be able to test my patch also please?

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

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

Re: [HACKERS] Enabling parallelism for queries coming from SQL or other PL functions

[Amit Kapila]

On Thu, Feb 23, 2017 at 9:20 PM, Dilip Kumar  wrote:
> On Thu, Feb 23, 2017 at 8:58 PM, Dilip Kumar  wrote:
>> Few more comments.
>> 1.I don't see any check in the code which will prevent the parallel
>> execution of the query inside a function if its called from a DML
>> statement.
>> e.g. If we use a function in the update statement's which has the
>> select statement.
>
> Having said that, I am thinking do we really need to block such cases?
> It just looks fine to me that an update statement calls a function (in
> targetlist or condition), which launches a bunch of workers for the
> internal query inside PL; finishes the work and shutdown them, only
> after this, the update will change any record. So basically I want to
> make a point that between the worker launch and shutdown there is no
> change in the database state.
>

+1.  I also think you are right that there should not be a problem in
such a case.



-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.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] Should we cacheline align PGXACT?

[Ashutosh Sharma]

Okay. As suggested by Alexander, I have changed the order of reading and
doing initdb for each pgbench run. With these changes, I got following
results at 300 scale factor with 8GB of shared buffer.

*pgbench settings:*
pgbench -i -s 300 postgres
pgbench -M prepared -c $thread -j $thread -T $time_for_reading  postgres

where, time_for_reading = 30mins

*non default GUC param*
shared_buffers=8GB
max_connections=300

pg_xlog is located in SSD.

CLIENT COUNT TPS (HEAD) TPS (PATCH) % IMPROVEMENT
4 2803 2843 1.427042455
8 5315 5225 -1.69332079
32 19755 19669 -0.4353328271
64 28679 27980 -2.437323477
128 28887 28008 -3.042891266
156 27465 26728 -2.683415256
180 27425 26697 -2.654512306
196 28826 27396 -4.960799278
256 29787 28107 -5.640044315
The test machine details are as follows,

*Machine details:*
Architecture:  x86_64
CPU op-mode(s):32-bit, 64-bit
Byte Order:Little Endian
CPU(s):128
On-line CPU(s) list:   0-127
Thread(s) per core:2
Core(s) per socket:8
Socket(s): 8
NUMA node(s):  8
Vendor ID: GenuineIntel
CPU family:6
Model: 47
Model name:Intel(R) Xeon(R) CPU E7- 8830  @ 2.13GHz

Also, Excel sheet (results-readwrite-300-SF) containing the results for all
the 3 runs is attached.

-- 
With Regards,
Ashutosh Sharma
EnterpriseDB:*http://www.enterprisedb.com *


On Thu, Feb 23, 2017 at 2:44 AM, Alvaro Herrera 
wrote:

> I wonder if this "perf c2c" tool with Linux 4.10 might be useful in
> studying this problem.
> https://joemario.github.io/blog/2016/09/01/c2c-blog/
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>


results-readwrite-300-SF.xlsx
Description: MS-Excel 2007 spreadsheet

-- 
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: GetOldestXminExtend for ignoring arbitrary vacuum flags

[Simon Riggs]

On 16 February 2017 at 05:24, Seki, Eiji  wrote:
> Simon Riggs  wrote:
>> Please persuade us with measurements that allowing this impact on
>> ANALYZE would really improve performance at least in your case, and
>> also examine the effect of this on the accuracy and usefulness of the
>> gathered statistics.
>
> I explain results of the test that Haribabu mentioned in [1].

Thanks for the tests. I can see the performance is affected by this.


> Please let me know if you need any further information.

...you didn't comment at all on the accuracy and usefulness of the
gathered statistics, when the sample is biased towards non-updated
data.

I'm wondering whether this should be an additional table-level option.

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] Enabling parallelism for queries coming from SQL or other PL functions

[Amit Kapila]

On Thu, Feb 23, 2017 at 8:58 PM, Dilip Kumar  wrote:
> On Thu, Feb 23, 2017 at 12:11 PM, Rafia Sabih
>  wrote:
>
> 2. How are you protecting, if the outer select is running in parallel,
> then the function called from there should not run anything in
> parallel? This may allow worker launching another set of workers.  Am
> I missing something?
>

We have a below check in standard_planner() (!IsParallelWorker())
which should prohibit generating parallel plan inside worker, if that
is what you are seeing, then we might need a similar check at other
places.

if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
IsUnderPostmaster &&
dynamic_shared_memory_type != DSM_IMPL_NONE &&
parse->commandType == CMD_SELECT &&
!parse->hasModifyingCTE &&
max_parallel_workers_per_gather > 0 &&
!IsParallelWorker() &&
!IsolationIsSerializable())
{
/* all the cheap tests pass, so scan the query tree */
glob->maxParallelHazard = max_parallel_hazard(parse);
glob->parallelModeOK = (glob->maxParallelHazard != PROPARALLEL_UNSAFE);
}



-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.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] Documentation improvements for partitioning

[Simon Riggs]

On 24 February 2017 at 02:36, Robert Haas  wrote:

>> While its true that the patch had syntax documentation, there was no
>> user design documentation which explained how it worked to allow
>> objective review. Had I been able to provide input without reading
>> every email message, I would have done so earlier.
>
> But I don't agree that it was impossible for you to provide input
> earlier without reading every email message, nor do I agree that it is
> unreasonable to expect to people who want to provide input to read the
> relevant threads.

>> The features I consider very important in the first release are
>> 1. Declarative syntax (we have this!)
>> 2. Tuple routing on INSERT/COPY (we have this!)
>> 3. O(1) partition elimination for simple = queries
>> 4. PRIMARY KEY defined using a) distinct set of UNIQUE constraints on
>> each partition, b) partition key
>>
>> 2 and 3 are intimately connected because they would both use the same
>> in-memory data for bsearch, so the code should be almost identical.
>>
>> 4 is important for Foreign Keys and Logical Replication
>>
>> As missing items, features 3 and 4 seem achievable in this release,
>> potentially in restricted form.
>
> Simon, this is ridiculous.  We're 4 or 5 days away from the start of
> the last CommitFest. We have time to fix bugs and improve
> documentation and maybe tweak things here and there, but 3 and 4 are
> significant development projects.  There isn't time to do that stuff
> right now and get it right.

Agreed, you beat me to it. I've spent the last few hours trying to see
what I could personally pull out of the fire to assist. Those things
aren't that big, but they are bigger than we have time for now. Had I
known about that a few months back... well, we are where we are.

The good news is that logical replication DOES work with partitioning,
but only for a Publication with PUBLISH INSERT, pushing from a normal
table to a partitioned one. Which is useful enough for first release.

The work on having UPDATE work between partitions can be used to make
updates and deletes work in logical replication. That might yet be
made to work in this release, and I think we should look into it, but
I think it can be left til next release if we try.


> You don't get to show up more than two
> months after the feature is committed and start complaining that it
> doesn't include all the things you want.  Which things ought to be
> included in the initial patch was under active discussion about a year
> ago this time.

I've provided lots of input across many years, for example my
explanation of how to do tuple routing is very clearly the basis of
the design of the current feature. You *knew* I would have feedback
and if it it hadn't appeared yet you knew it would come.

The "two months after the feature is committed" is a direct result of
the lack of docs. Note that within hours of reading the docs I'd given
feedback. How could I give feedback earlier? Well, I tried. I've flown
to Japan to ensure I could talk to Amit in person about this feature.
Your absence at two consecutive developer meetings hasn't helped
there. It is you that needs to show up more. I'm sure we both have
family and work reasons not to attend them.

The need for design feedback is exactly why the docs for logical
replication were published in June, six months before logical
replication was committed.

With repect, you are making a few mistakes. The first is to imagine
that review comments are negative or complaints; with the right
viewpoint they could easily be seen as helping people to see what has
been missed, yet you repeatedly see them as personal attacks and throw
words back. Oh sure, I've done that myself in earlier days. The second
is to imagine that discussing things on Hackers in multiple threads,
spanning many months with long, detailed emails and rapid replies is
something that anybody could have followed if they wished. And the
third is to imagine I have no right to review; I will watch and see if
you deploy this same "You don't get to show up.." argument against Tom
or Noah when they point out holes in late reviews, though we already
both know you won't. I see you using that yourself, objecting
frequently against patches large and small if they do not meet your
exacting standards, yet you have spoken multiple times against my
right to do that.

Perhaps we'll have time to scowl at each other in India. I'll look
forward to that. ;-)

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] Proposal: GetOldestXminExtend for ignoring arbitrary vacuum flags

[Seki, Eiji]

On 2017-02-15 17:27:11  Robert Haas wrote:
> On Tue, Feb 14, 2017 at 1:38 PM, Jim Nasby 
>  wrote:
> > On 2/14/17 3:13 AM, Seki, Eiji wrote:
> >>   +extern TransactionId GetOldestXmin(Relation rel, uint8 ignoreFlags);
> >
> >
> > My impression is that most other places that do this sort of thing just call
> > the argument 'flags', so as not to "lock in" a single idea of what the flags
> > are for. I can't readily think of another use for flags in GetOldestXmin,
> > but ISTM it's better to just go with "flags" instead of "ignoreFlags".
> 
> I agree; also, many years ago a guy named Tom Lane told me that flags
> argument should typically be declared as type "int".  I've followed
> that advice ever since.

Thank you for your comments.

I reflected these comments to the attached patch. And I renamed IGNORE_XXX 
flags to PROCARRAY_XXX flags.

--
Regards,
Eiji Seki
Fujitsu




get_oldest_xmin_with_ignore_flags_v2.patch
Description: get_oldest_xmin_with_ignore_flags_v2.patch

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

Re: [HACKERS] A typo in mcxt.c

[Tom Lane]

Andres Freund  writes:
> On 2017-02-23 14:26:07 -0600, Jim Nasby wrote:
>> On 2/23/17 6:38 AM, Thomas Munro wrote:
>>> That is an archaic way of contracting the same words differently:

>> Given the number of non-native English speakers we have, it's probably worth
>> changing it...

> I'm a non-native speaker and I actually like discovering new language
> "features" every now and then. I think as long as it's not inhibiting
> understanding to much - which doesn't seem to be the case here - it's
> fine to keep things like this.

While I don't recall it specifically, git blame says that comment is mine.
I'm pretty sure it's not a typo, but that the allusion to Hamlet was
intentional.  I think it's good to have a bit of levity and external
references in our comments; cuts down on the boredom of reading totally
dry code.

(But see commit d2783bee3 for one hazard of this sort of thing.)

regards, tom lane


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

[HACKERS] timeouts in PostgresNode::psql

[Craig Ringer]

Hi all

While writing some tests I noticed that in newer IPC::Run or Perl
versions (haven't dug extensively to find out which), perl appends the
location to the extension, so 'ne' doesn't match the passed exception
string.

Pattern-match the exception string to handle this.

Bugfix, should be applied to 9.6 and master.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
From ba803ba3c94624ce5e6044e14272b2db2793252c Mon Sep 17 00:00:00 2001
From: Craig Ringer 
Date: Fri, 24 Feb 2017 11:43:30 +0800
Subject: [PATCH] Fix timeouts in PostgresNode::psql

Newer Perl or IPC::Run versions default to appending the filename to string
exceptions, e.g. the exception

psql timed out

 is thrown as

psql timed out at /usr/share/perl5/vendor_perl/IPC/Run.pm line 2961.

To handle this, match exceptions with !~ rather than ne.
---
 src/test/perl/PostgresNode.pm | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/src/test/perl/PostgresNode.pm b/src/test/perl/PostgresNode.pm
index 9b712eb..d0a996d 100644
--- a/src/test/perl/PostgresNode.pm
+++ b/src/test/perl/PostgresNode.pm
@@ -1116,7 +1116,7 @@ sub psql
 			# IPC::Run::run threw an exception. re-throw unless it's a
 			# timeout, which we'll handle by testing is_expired
 			die $exc_save
-			  if (blessed($exc_save) || $exc_save ne $timeout_exception);
+			  if (blessed($exc_save) || $exc_save !~ /^$timeout_exception.*/);
 
 			$ret = undef;
 
-- 
2.5.5


-- 
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] A typo in mcxt.c

[Andres Freund]

On 2017-02-23 14:26:07 -0600, Jim Nasby wrote:
> On 2/23/17 6:38 AM, Thomas Munro wrote:
> > > I'm not so confident, but the "'tis" seems to me to be a typo of
> > > "it's".
> > That is an archaic way of contracting the same words differently:
> 
> Given the number of non-native English speakers we have, it's probably worth
> changing it...

I'm a non-native speaker and I actually like discovering new language
"features" every now and then. I think as long as it's not inhibiting
understanding to much - which doesn't seem to be the case here - it's
fine to keep things like this.

- 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] GUC for cleanup indexes threshold.

[Amit Kapila]

On Thu, Feb 23, 2017 at 5:15 PM, Masahiko Sawada  wrote:
> On Wed, Feb 22, 2017 at 12:01 AM, Amit Kapila  wrote:
>
>> I understand that there could be some delay in reclaiming dead pages
>> but do you think it is such a big deal that we completely scan the
>> index for such cases or even try to change the metapage format?
>
> IIUC, I think that we need to have the number of half-dead pages in meta page.
>

Don't you think we need to consider backward compatibility if we want
to do that?

> Isn't it a problem that the freespace map of btree index is not
> vacuumed if all vacuums skip the second pass?
>

AFAIU, you want to skip only when there is no dead tuple removal, if
so what is the need to update freespace map of btree index?

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.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] Automatic cleanup of oldest WAL segments with pg_receivexlog

[Jim Nasby]

On 2/23/17 9:01 PM, Michael Paquier wrote:

An idea here would be to add in the long header of the segment a
timestamp of when it was created. This is inherent to only the server
generating the WAL.


ISTM it'd be reasonable (maybe even wise) for WAL files to contain info 
about the first and last LSN, commit xid, timestamps, etc.



That could be made performance
wise with an archive command. With pg_receivexlog you could make use
of the end-segment command to scan the completely written segment for
this data before moving on to the next one. At least it gives an
argument for having such a command. David Steele mentioned that he
could make use of such a thing.

BTW, I'm not opposed to an end-segment command; I'm just saying I don't
think having it would really help users very much.

Thanks. Yes that's hard to come up here with something that would
satisfy enough users without giving much maintenance penalty.


Yeah, I think it'd be a decent (though hopefully not huge) amount of work.

As I see it, we got away for years with no replication, but eventually 
realized that we were really leaving a hole in our capabilities by not 
having built-in binary rep. I think we're nearing a similar point with 
handling PITR backups. People have written some great tools to help with 
this, but at some point (PG 11? 13?) there should probably be some 
strong included tools.


I suspect that a huge improvement on the internal tools could be had for 
1/2 or less the effort that's been spent on all the external ones. Of 
course, much of that is because the external tools have helped prove out 
what works and what doesn't.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] Idea on how to simplify comparing two sets

[Jim Nasby]

On 2/23/17 3:33 PM, Corey Huinker wrote:

I've had to do it with temp tables any time the environment is different
between control/experiment, which is the case when you're developing a
drop-in replacement for an SRF or view or whatever.


FWIW I'd recommend temp views, to give the planner the most latitude. 
Less load from (presumably) pointless copying too.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] Automatic cleanup of oldest WAL segments with pg_receivexlog

[Michael Paquier]

On Fri, Feb 24, 2017 at 11:56 AM, Jim Nasby  wrote:
> On 2/23/17 8:47 PM, Michael Paquier wrote:
>>
>> Anything else than measured in bytes either requires a lookup at the
>> file timestamp, which is not reliable with noatime or a lookup at WAL
>> itself to decide when is the commit timestamp that matches the oldest
>> point in time of the backup policy.
>
> An indication that it'd be nice to have a better way to store this
> information as part of a base backup, or the archived WAL files.

An idea here would be to add in the long header of the segment a
timestamp of when it was created. This is inherent to only the server
generating the WAL.

>> That could be made performance
>> wise with an archive command. With pg_receivexlog you could make use
>> of the end-segment command to scan the completely written segment for
>> this data before moving on to the next one. At least it gives an
>> argument for having such a command. David Steele mentioned that he
>> could make use of such a thing.
>
> BTW, I'm not opposed to an end-segment command; I'm just saying I don't
> think having it would really help users very much.

Thanks. Yes that's hard to come up here with something that would
satisfy enough users without giving much maintenance penalty.
-- 
Michael


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

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

[Jim Nasby]

On 2/23/17 8:52 PM, Michael Paquier wrote:

OK, I forgot a bit about this past discussion. So let's say that we
have a command, why not also allow users to use at will a marker %f to
indicate the file name just completed? One use case here is to scan
the file for the oldest and/or newest timestamps of the segment just
finished to do some retention policy with something else in charge of
the cleanup.


Why not provide % replacements that contain that info? pg_receivexlog 
has a much better shot at doing that correctly than some random user tool...


(%f could certainly be useful for other things)
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] btree_gin and btree_gist for enums

[Andrew Dunstan]

On 02/23/2017 08:34 PM, Tom Lane wrote:
> Andrew Dunstan  writes:
>> On 02/23/2017 04:41 PM, Tom Lane wrote:
>>> BTW, while I'm looking at this ... isn't gin_enum_cmp broken on its face?
>> Yes, that's what I'm trying to fix.
> I'd forgotten where this thread started.  For a minute there I thought
> we had a live bug, rather than a deficiency in code-under-development.
>
> After thinking about that, I believe that enum_cmp_internal is a rather
> considerable hazard.  It might not be our only function that requires
> fcinfo->flinfo cache space just some of the time not all the time, but
> I don't recall anyplace else that could so easily undergo a reasonable
> amount of testing without *ever* reaching the case where it requires
> that cache space.  So I'm now worried that it wouldn't be too hard for
> such a bug to get past us.
>
> I think we should address that by adding a non-bypassable Assert that
> the caller did provide flinfo, as in the attached.
>
>   


Looks sane. I don't believe there is anywhere in the core code that
calls this without fcinfo->flinfo, But obviously I have tickled that
with my original patch for the extension.

cheers

andrew

-- 
Andrew Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



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

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

[Jim Nasby]

On 2/23/17 8:47 PM, Michael Paquier wrote:

Anything else than measured in bytes either requires a lookup at the
file timestamp, which is not reliable with noatime or a lookup at WAL
itself to decide when is the commit timestamp that matches the oldest
point in time of the backup policy.


An indication that it'd be nice to have a better way to store this 
information as part of a base backup, or the archived WAL files.



That could be made performance
wise with an archive command. With pg_receivexlog you could make use
of the end-segment command to scan the completely written segment for
this data before moving on to the next one. At least it gives an
argument for having such a command. David Steele mentioned that he
could make use of such a thing.


BTW, I'm not opposed to an end-segment command; I'm just saying I don't 
think having it would really help users very much.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] Automatic cleanup of oldest WAL segments with pg_receivexlog

[Michael Paquier]

On Fri, Feb 24, 2017 at 1:10 AM, Magnus Hagander  wrote:
> I'm not sure this logic belongs in pg_receivexlog. If we put the decision
> making there, then we lock ourselves into one "type of policy".
>
> Wouldn't this one, along with some other scenarios, be better provided by
> the "run command at end of segment" function that we've talked about before?
> And then that external command could implement whatever aging logic would be
> appropriate for the environment?

OK, I forgot a bit about this past discussion. So let's say that we
have a command, why not also allow users to use at will a marker %f to
indicate the file name just completed? One use case here is to scan
the file for the oldest and/or newest timestamps of the segment just
finished to do some retention policy with something else in charge of
the cleanup.

The option name would be --end-segment-command? Any better ideas of names?
-- 
Michael


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

Re: [HACKERS] Documentation improvements for partitioning

[Robert Haas]

On Fri, Feb 24, 2017 at 8:06 AM, Robert Haas  wrote:
> Simon, this is ridiculous.  We're 4 or 5 days away from the start of
> the last CommitFest.  We have time to fix bugs and improve
> documentation and maybe tweak things here and there, but 3 and 4 are
> significant development projects.  There isn't time to do that stuff
> right now and get it right.  You don't get to show up more than two
> months after the feature is committed and start complaining that it
> doesn't include all the things you want.  Which things ought to be
> included in the initial patch was under active discussion about a year
> ago this time.

I take that back.  You can complain as much as you like; everybody has
a right to complain.  But it's not reasonable to expect Amit (or
anyone else) to go fix the things you're complaining about in time for
v10, or really ever.  He doesn't have to write any more partitioning
patches ever, and if he does decide to do so, he doesn't have to write
the ones you or I or anyone other than his employer wants him to write
(and he only has to listen to his employer if he doesn't want to get
fired).

Also, I'm very much against against any major tinkering with this
feature in this release.  We've got enough work to do stabilizing
what's already been committed in this area, and the last things we
need is a bunch of patches that significant change it showing up at
the eleventh hour without time for adequate reflection and discussion.
Most if not all significant patches for this release should already
have been submitted; again, the last CommitFest will be starting
shortly, and we should have seen those patches in the previous
CommitFest.  We should be focusing on getting all the good patches
that have already been written committed, not creating new ones at the
last minute.

Contrary to what you may think, neither changing the way partition
pruning works nor inventing a system for indexes to roll down to
partition children is a minor fix.  Even if you restrict the scope to
simple cases, there's still got to be a level of design agreement so
that we know we're not boxing ourselves into a corner for the future,
and the patch quality still has to be good.  That's not going to
happen in the next couple of days, barring a dramatic reversal of how
the development process in this community has always worked before.

-- 
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] bytea_output output of base64

[Jim Nasby]

On 2/23/17 8:22 PM, Bruce Momjian wrote:

I was just curious because it seems more compact than hex and many
exchange formats use it, like SSL certificates and keys.  I know you can
encode() but I thought it might help make pg_dump output smaller.


It undoubtedly would make pg_dump smaller, though I'm not sure how much 
that's worth since if you care at all about that you'll gzip it.


But, the other thing it might do is speed up COPY, especially on input. 
Some performance tests of that might be interesting.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] Automatic cleanup of oldest WAL segments with pg_receivexlog

[Michael Paquier]

On Fri, Feb 24, 2017 at 5:37 AM, Jim Nasby  wrote:
> ISTM what's really needed is a good way for users to handle retention for
> both WAL as well as base backups. A tool that did that would need to
> understand what WAL is required to safely restore a base backup. It should
> be possible for users to have a separate retention policy for just base
> backups as well as backups that support full PITR. You'd also need an easy
> way to deal with date ranges (so you can do things like "delete all backups
> more than 1 year old").

Anything else than measured in bytes either requires a lookup at the
file timestamp, which is not reliable with noatime or a lookup at WAL
itself to decide when is the commit timestamp that matches the oldest
point in time of the backup policy. That could be made performance
wise with an archive command. With pg_receivexlog you could make use
of the end-segment command to scan the completely written segment for
this data before moving on to the next one. At least it gives an
argument for having such a command. David Steele mentioned that he
could make use of such a thing.

> Perhaps a good starting point would be a tool that lets you list what base
> backups you have, what WAL those backups need, when the backups were taken,
> etc.

pg_rman? barman?
-- 
Michael


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

Re: [HACKERS] Documentation improvements for partitioning

[Jim Nasby]

On 2/23/17 8:36 PM, Robert Haas wrote:

We're 4 or 5 days away from the start of
the last CommitFest.  We have time to fix bugs and improve
documentation and maybe tweak things here and there, but 3 and 4 are
significant development projects.  There isn't time to do that stuff
right now and get it right.


It might be possible to provide some temporary work-arounds for some of 
this, which would be nice. But I agree that there's definitely not 
enough time to implement *good* solutions to even just automatic index 
creation, let alone somehow handling uniqueness.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] Logical Replication and Character encoding

[Shinoda, Noriyoshi]

>From: Peter Eisentraut [mailto:peter.eisentr...@2ndquadrant.com] 
>Sent: Friday, February 24, 2017 1:32 AM
>To: Petr Jelinek ; Kyotaro HORIGUCHI 
>
>Cc: cr...@2ndquadrant.com; Shinoda, Noriyoshi ; 
>pgsql-hackers@postgresql.org
>Subject: Re: [HACKERS] Logical Replication and Character encoding
>
>On 2/17/17 10:14, Peter Eisentraut wrote:
>> Well, it is sort of a libpq connection, and a proper libpq client 
>> should set the client encoding, and a proper libpq server should do 
>> encoding conversion accordingly.  If we just play along with this, it 
>> all works correctly.
>> 
>> Other output plugins are free to ignore the encoding settings (just 
>> like libpq can send binary data in some cases).
>> 
>> The attached patch puts it all together.
>
>committed

Hi, 

Thank you very much for making a new patch. I tried a new committed version. 
In the case of PUBLICATION(EUC_JP) and SUBSCRIPTION(UTF-8) environment, it 
worked as expected. Great!.
However, in the case of PUBLICATION(UTF-8) and SUBSCRIOTION(EUC_JP) 
environment, the following error was output and the process went down.

- PUBLICATION (UTF-8)
postgres=> INSERT INTO encode1 VALUES (1, 'ascii') ;
INSERT 0 1
postgres=> INSERT INTO encode1 VALUES (2, '漢') ; -- Expect UTF-8 Character 
0xE6BCA2 will be convert EUC_JP 0xB4C1
INSERT 0 1

- SUBSCRIPTION (EUC_JP)
postgres=> SELECT * FROM encode1;
 c1 |  c2
+---
  1 | ascii
(1 row)

$ tail data.euc/pg_log/postgresql.log
LOG:  starting logical replication worker for subscription "sub1"
LOG:  logical replication apply for subscription "sub1" has started
ERROR:  insufficient data left in message
LOG:  worker process: logical replication worker for subscription 16439 (PID 
22583) exited with exit code 1

Snapshot:
  https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.gz 
2017-02-24 00:28:58 
Operating System: 
  Red Hat Enterprise Linux 7 Update 2 (x86-64)

Regards.



-- 
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] Documentation improvements for partitioning

[Robert Haas]

On Thu, Feb 23, 2017 at 10:00 PM, Simon Riggs  wrote:
> You seem a little defensive about some reasonable review comments.

I am prone to that from time to time, and this may be an instance of it.

> While its true that the patch had syntax documentation, there was no
> user design documentation which explained how it worked to allow
> objective review. Had I been able to provide input without reading
> every email message, I would have done so earlier.

But I don't agree that it was impossible for you to provide input
earlier without reading every email message, nor do I agree that it is
unreasonable to expect to people who want to provide input to read the
relevant threads.

> The features I consider very important in the first release are
> 1. Declarative syntax (we have this!)
> 2. Tuple routing on INSERT/COPY (we have this!)
> 3. O(1) partition elimination for simple = queries
> 4. PRIMARY KEY defined using a) distinct set of UNIQUE constraints on
> each partition, b) partition key
>
> 2 and 3 are intimately connected because they would both use the same
> in-memory data for bsearch, so the code should be almost identical.
>
> 4 is important for Foreign Keys and Logical Replication
>
> As missing items, features 3 and 4 seem achievable in this release,
> potentially in restricted form.

Simon, this is ridiculous.  We're 4 or 5 days away from the start of
the last CommitFest.  We have time to fix bugs and improve
documentation and maybe tweak things here and there, but 3 and 4 are
significant development projects.  There isn't time to do that stuff
right now and get it right.  You don't get to show up more than two
months after the feature is committed and start complaining that it
doesn't include all the things you want.  Which things ought to be
included in the initial patch was under active discussion about a year
ago this time.

-- 
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] dropping partitioned tables without CASCADE

[Amit Langote]

On 2017/02/24 1:33, Simon Riggs wrote:
> On 23 February 2017 at 06:40, Ashutosh Bapat
>  wrote:
> 
>> I think this is ready for committer.
> 
> Thanks for writing and reviewing this. I'll be happy to review and
> commit. Please add to CF.

Thanks.  I've added it to CF: https://commitfest.postgresql.org/13/1030/

Regards,
Amit




-- 
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] bytea_output output of base64

[Bruce Momjian]

On Thu, Feb 23, 2017 at 07:09:57PM -0500, Andrew Dunstan wrote:
> 
> 
> On 02/23/2017 06:52 PM, David Fetter wrote:
> > On Thu, Feb 23, 2017 at 05:55:37PM -0500, Bruce Momjian wrote:
> >> On Thu, Feb 23, 2017 at 04:08:58PM -0500, Tom Lane wrote:
> >>> Bruce Momjian  writes:
>  Is there a reason we don't support base64 as a bytea_output output
>  option, except that no one has implemented it?
> >>> How about "we already have one too many bytea output formats"?
> >>> I don't think forcing code to try to support still another one
> >>> is a great thing ... especially not if it couldn't be reliably
> >>> distinguished from the hex format.
> >> Is there a reason we chose hex over base64?
> > Whether there was or not, there's not a compelling reason now to break
> > people's software.  When people want compression, methods a LOT more
> > effective than base64 are common.  Gzip, for example.
> >
> 
> 
> What's the use case anyway? It's already supported by the encode() and
> decode() functions if you need that format.

I was just curious because it seems more compact than hex and many
exchange formats use it, like SSL certificates and keys.  I know you can
encode() but I thought it might help make pg_dump output smaller. 

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

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


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

Re: [HACKERS] Range Partitioning behaviour - query

[Amit Langote]

On 2017/02/24 10:38, David G. Johnston wrote:
> On Thu, Feb 23, 2017 at 6:17 PM, Amit Langote > wrote:
> 
>> On 2017/02/24 8:38, Venkata B Nagothi wrote:
>>> On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote wrote:
 Upper bound of a range partition is an exclusive bound.  A note was
>> added
 recently to the CREATE TABLE page to make this clear.

 https://www.postgresql.org/docs/devel/static/sql-createtable.html
>>>
>>>
>>> Thanks. Actually, my confusion was that the upper bound value would be
>>> included when "TO" clause is used in the syntax.
>>
>> Hmm, TO sounds like it implies inclusive.
>>
> 
> ​I think most common usage of the word ends up being inclusive but the word
> itself doesn't really care.​
> 
> Dictionary.com has a good example:
> 
> "We work from nine to five." - you leave at the beginning of the 5 o'clock
> hour (I'm going for casual usage here)

Thanks for that example.

One problem I've seen people mention is one of cognitive dissonance of
having to define partition_y2013 as FROM ('2013-01-01') TO ('2014-01-01'),
given that that's the only way to get what one needs. But we concluded
that that's a reasonable compromise.

> Since our implementation of ranges is half-open the usage here is
> consistent with that concept.  That it doesn't match BETWEEN is actually
> somewhat nice since you can use ranges for half-open and BETWEEN if you
> want to be concise with fully-closed endpoints.  But it is one more thing
> to remember.

Agreed.

Thanks,
Amit




-- 
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] Range Partitioning behaviour - query

[David G. Johnston]

On Thu, Feb 23, 2017 at 6:17 PM, Amit Langote  wrote:

> On 2017/02/24 8:38, Venkata B Nagothi wrote:
> > On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote wrote:
> >> Upper bound of a range partition is an exclusive bound.  A note was
> added
> >> recently to the CREATE TABLE page to make this clear.
> >>
> >> https://www.postgresql.org/docs/devel/static/sql-createtable.html
> >
> >
> > Thanks. Actually, my confusion was that the upper bound value would be
> > included when "TO" clause is used in the syntax.
>
> Hmm, TO sounds like it implies inclusive.
>

​I think most common usage of the word ends up being inclusive but the word
itself doesn't really care.​

Dictionary.com has a good example:

"We work from nine to five." - you leave at the beginning of the 5 o'clock
hour (I'm going for casual usage here)

Since our implementation of ranges is half-open the usage here is
consistent with that concept.  That it doesn't match BETWEEN is actually
somewhat nice since you can use ranges for half-open and BETWEEN if you
want to be concise with fully-closed endpoints.  But it is one more thing
to remember.

David J.

Re: [HACKERS] btree_gin and btree_gist for enums

[Tom Lane]

Andrew Dunstan  writes:
> On 02/23/2017 04:41 PM, Tom Lane wrote:
>> BTW, while I'm looking at this ... isn't gin_enum_cmp broken on its face?

> Yes, that's what I'm trying to fix.

I'd forgotten where this thread started.  For a minute there I thought
we had a live bug, rather than a deficiency in code-under-development.

After thinking about that, I believe that enum_cmp_internal is a rather
considerable hazard.  It might not be our only function that requires
fcinfo->flinfo cache space just some of the time not all the time, but
I don't recall anyplace else that could so easily undergo a reasonable
amount of testing without *ever* reaching the case where it requires
that cache space.  So I'm now worried that it wouldn't be too hard for
such a bug to get past us.

I think we should address that by adding a non-bypassable Assert that
the caller did provide flinfo, as in the attached.

regards, tom lane

diff --git a/src/backend/utils/adt/enum.c b/src/backend/utils/adt/enum.c
index 8110ee2..b1d2a6f 100644
*** a/src/backend/utils/adt/enum.c
--- b/src/backend/utils/adt/enum.c
*** enum_cmp_internal(Oid arg1, Oid arg2, Fu
*** 263,268 
--- 263,277 
  {
  	TypeCacheEntry *tcache;
  
+ 	/*
+ 	 * We don't need the typcache except in the hopefully-uncommon case that
+ 	 * one or both Oids are odd.  This means that cursory testing of code that
+ 	 * fails to pass flinfo to an enum comparison function might not disclose
+ 	 * the oversight.  To make such errors more obvious, Assert that we have a
+ 	 * place to cache even when we take a fast-path exit.
+ 	 */
+ 	Assert(fcinfo->flinfo != NULL);
+ 
  	/* Equal OIDs are equal no matter what */
  	if (arg1 == arg2)
  		return 0;
*** enum_cmp(PG_FUNCTION_ARGS)
*** 381,392 
  	Oid			a = PG_GETARG_OID(0);
  	Oid			b = PG_GETARG_OID(1);
  
! 	if (a == b)
! 		PG_RETURN_INT32(0);
! 	else if (enum_cmp_internal(a, b, fcinfo) > 0)
! 		PG_RETURN_INT32(1);
! 	else
! 		PG_RETURN_INT32(-1);
  }
  
  /* Enum programming support functions */
--- 390,396 
  	Oid			a = PG_GETARG_OID(0);
  	Oid			b = PG_GETARG_OID(1);
  
! 	PG_RETURN_INT32(enum_cmp_internal(a, b, fcinfo));
  }
  
  /* Enum programming support functions */

-- 
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] Range Partitioning behaviour - query

[Amit Langote]

On 2017/02/24 8:38, Venkata B Nagothi wrote:
> On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote wrote:
>> Upper bound of a range partition is an exclusive bound.  A note was added
>> recently to the CREATE TABLE page to make this clear.
>>
>> https://www.postgresql.org/docs/devel/static/sql-createtable.html
> 
> 
> Thanks. Actually, my confusion was that the upper bound value would be
> included when "TO" clause is used in the syntax.

Hmm, TO sounds like it implies inclusive.

> Also, there are no options like "<" or "LESS THAN" clauses available. So,
> "TO" translates to "<". That is what i wanted to confirm.

Yes, that's it.

Thanks,
Amit




-- 
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] Automatic cleanup of oldest WAL segments with pg_receivexlog

[Michael Paquier]

On Thu, Feb 23, 2017 at 10:54 PM, Stephen Frost  wrote:
> Micahel,
>
> * Michael Paquier (michael.paqu...@gmail.com) wrote:
>> Is there any interest for a feature like that? I have a non-polished
>> patch at hand but I can work on that for the upcoming CF if there are
>> voices in favor of such a feature. The feature could be simply
>> activated with a dedicated switch, like --clean-oldest-wal,
>> --clean-tail-wal, or something like that.
>
> This sounds interesting, though I wouldn't base it on the amount of free
> space on the partition but rather some user-set value (eg:
> --max-archive-size=50GB or something).

Doable. This part is not that hard to use for pg_receivexlog kicked as
a service if the parameter passed is an environment variable.

> I am a bit dubious about it in general though.  WAL that you don't have
> a base backup for or a replica which needs it is really of very limited
> value.  I understand your suggestion that it could be used for
> 'debugging', but that really seems like a stretch to me.

Putting your hands on what happens in the database at page level
helps. I have used that once to look at page-level data to see that
the page actually got corrupted by an incorrect failover flow (page
got flushed and this node was incorrectly reused as a standby
afterwards).

> I would also
> be concerned that people would set up their systems using this without
> fully understanding it, or being prepared to handle what happens when it
> kicks in and starts removing WAL that maybe they should have kept for a
> base backup or a replica. At least if we start failing when the
> partition is full then they have alerts telling them that the partition
> is full and they have a base backup and WAL to bring it forward to
> almost current.

Sure. Documentation is really key here. No approaches are perfect,
each one has its own value. I am of course not suggesting to make any
of that enabled. In my case not getting a failure because of a full
partition mattered more.
-- 
Michael


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

Re: [HACKERS] btree_gin and btree_gist for enums

[Andrew Dunstan]

On 02/23/2017 04:41 PM, Tom Lane wrote:
> Andrew Dunstan  writes:
>> I'm not entirely sure how I should replace those DirectFunctionCall2 calls.
> Basically what we want is for the called function to be able to use the
> fcinfo->flinfo->fn_extra and fcinfo->flinfo->fn_mcxt fields of the
> FmgrInfo struct that GIN has set up for the btree_gin support function.
>
> The fast but somewhat scary way to do it would just be to pass through
> the flinfo pointer as-is.  Imagine that fmgr.c grows a set of functions
> like
>
>  [...]



Here's a POC for btree_gin based on my original patch. I just made your
function static in btree_gin.c at least for now. I stayed with the
DirectFunctionCall2 use in the type-specific routines where calling
context wasn't needed (i.e. everything except enums). But it looks more
than ugly and highly invasive for btree_gist, and sadly that's my main
use case, exclusion constraints with enum fields.

cheers

andrew


-- 
Andrew Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

diff --git a/contrib/btree_gin/Makefile b/contrib/btree_gin/Makefile
index 0492091..c6aae26 100644
--- a/contrib/btree_gin/Makefile
+++ b/contrib/btree_gin/Makefile
@@ -4,13 +4,13 @@ MODULE_big = btree_gin
 OBJS = btree_gin.o $(WIN32RES)
 
 EXTENSION = btree_gin
-DATA = btree_gin--1.0.sql btree_gin--unpackaged--1.0.sql
+DATA = btree_gin--1.0.sql btree_gin--unpackaged--1.0.sql btree_gin--1.0--1.1.sql
 PGFILEDESC = "btree_gin - B-tree equivalent GIN operator classes"
 
 REGRESS = install_btree_gin int2 int4 int8 float4 float8 money oid \
 	timestamp timestamptz time timetz date interval \
 	macaddr inet cidr text varchar char bytea bit varbit \
-	numeric
+	numeric enum
 
 ifdef USE_PGXS
 PG_CONFIG = pg_config
diff --git a/contrib/btree_gin/btree_gin--1.0--1.1.sql b/contrib/btree_gin/btree_gin--1.0--1.1.sql
new file mode 100644
index 000..3c40ccd
--- /dev/null
+++ b/contrib/btree_gin/btree_gin--1.0--1.1.sql
@@ -0,0 +1,47 @@
+/* contrib/btree_gin/btree_gin--1.0--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "ALTER EXTENSION btree_gin UPDATE TO '1.1'" to load this file. \quit
+
+--
+--
+--
+-- enum ops
+--
+--
+
+
+CREATE FUNCTION gin_extract_value_anyenum(anyenum, internal)
+RETURNS internal
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT IMMUTABLE;
+
+CREATE FUNCTION gin_compare_prefix_anyenum(anyenum, anyenum, int2, internal)
+RETURNS int4
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT IMMUTABLE;
+
+CREATE FUNCTION gin_extract_query_anyenum(anyenum, internal, int2, internal, internal)
+RETURNS internal
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT IMMUTABLE;
+
+CREATE FUNCTION gin_enum_cmp(anyenum, anyenum)
+RETURNS int4
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT IMMUTABLE;
+
+CREATE OPERATOR CLASS enum_ops
+DEFAULT FOR TYPE anyenum USING gin
+AS
+OPERATOR1   <,
+OPERATOR2   <=,
+OPERATOR3   =,
+OPERATOR4   >=,
+OPERATOR5   >,
+FUNCTION1   gin_enum_cmp(anyenum,anyenum),
+FUNCTION2   gin_extract_value_anyenum(anyenum, internal),
+FUNCTION3   gin_extract_query_anyenum(anyenum, internal, int2, internal, internal),
+FUNCTION4   gin_btree_consistent(internal, int2, anyelement, int4, internal, internal),
+FUNCTION5   gin_compare_prefix_anyenum(anyenum,anyenum,int2, internal),
+STORAGE anyenum;
diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c
index 030b610..4694275 100644
--- a/contrib/btree_gin/btree_gin.c
+++ b/contrib/btree_gin/btree_gin.c
@@ -25,6 +25,35 @@ typedef struct QueryInfo
 	Datum		(*typecmp) (FunctionCallInfo);
 } QueryInfo;
 
+/*
+ * Routine to provide context to a data type comparison call.
+ * Needed for enum support.
+ */
+
+static Datum
+CallerContextFunctionCall2(PGFunction func,
+		   FmgrInfo *flinfo, Oid collation,
+		   Datum arg1, Datum arg2)
+{
+	FunctionCallInfoData fcinfo;
+	Datumresult;
+
+	InitFunctionCallInfoData(fcinfo, flinfo, 2, collation, NULL, NULL);
+
+	fcinfo.arg[0] = arg1;
+	fcinfo.arg[1] = arg2;
+	fcinfo.argnull[0] = false;
+	fcinfo.argnull[1] = false;
+
+	result = (*func) ();
+
+	/* Check for null result, since caller is clearly not expecting one */
+	if (fcinfo.isnull)
+		elog(ERROR, "function %p returned NULL", (void *) func);
+
+	return result;
+}
+
 
 /*** GIN support functions shared by all datatypes ***/
 
@@ -112,13 +141,14 @@ gin_btree_compare_prefix(FunctionCallInfo fcinfo)
 	int32		res,
 cmp;
 
-	cmp = DatumGetInt32(DirectFunctionCall2Coll(
-data->typecmp,
-PG_GET_COLLATION(),
-   (data->strategy == BTLessStrategyNumber ||
- data->strategy == BTLessEqualStrategyNumber)
-? data->datum : a,
-b));
+	cmp = DatumGetInt32(CallerContextFunctionCall2(
+			

Re: [HACKERS] bytea_output output of base64

[Andrew Dunstan]

On 02/23/2017 06:52 PM, David Fetter wrote:
> On Thu, Feb 23, 2017 at 05:55:37PM -0500, Bruce Momjian wrote:
>> On Thu, Feb 23, 2017 at 04:08:58PM -0500, Tom Lane wrote:
>>> Bruce Momjian  writes:
 Is there a reason we don't support base64 as a bytea_output output
 option, except that no one has implemented it?
>>> How about "we already have one too many bytea output formats"?
>>> I don't think forcing code to try to support still another one
>>> is a great thing ... especially not if it couldn't be reliably
>>> distinguished from the hex format.
>> Is there a reason we chose hex over base64?
> Whether there was or not, there's not a compelling reason now to break
> people's software.  When people want compression, methods a LOT more
> effective than base64 are common.  Gzip, for example.
>


What's the use case anyway? It's already supported by the encode() and
decode() functions if you need that format.

cheers

andrew

-- 
Andrew Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



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

Re: [HACKERS] bytea_output output of base64

[Tom Lane]

David Fetter  writes:
> On Thu, Feb 23, 2017 at 05:55:37PM -0500, Bruce Momjian wrote:
>> Is there a reason we chose hex over base64?

> Whether there was or not, there's not a compelling reason now to break
> people's software.  When people want compression, methods a LOT more
> effective than base64 are common.  Gzip, for example.

Yeah.  I think the argument for hex was about readability.  It's certainly
not more compact than the traditional "escape" format --- and depending on
what your data is like, base64 probably wouldn't be either.

If you want small, you'd go for binary transmission first, and then maybe
gzip it.

I really don't see any compelling argument for base64 except possibly
exchangability with other systems.  But if we stuck a header on it, which
I think we'd have to do to make it reliably distinguishable from the other
bytea formats, that argument goes down the drain.

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] \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless)

[Corey Huinker]

>
> I'm not sure that '@' is the best choice, but this is just one char.


> I noticed that it takes precedence over '!'. Why not. ISTM that orthogonal
> features are not shown independently, but this is a preexisting state, and
> it allows to have a shorter prompt, so why not.
>

My reasoning was this: if you're in a false block, and you're not connected
to a db, the \c isn't going to work for you until you get out of the false
block, so right now being in a false block is a bigger problem than not
being connected to a db. I have no strong opinion about what should happen
here, so I welcome suggestions for what tops what.


>
> Anyway, the '%R' documentation needs to be updated.


Done.


> It could be nice to keep test cases that show what may happen?
>

Restored. It looks weird now, but it fixes the unterminated quoted string.


> The various simplifications required result in the feature being more
> error prone for the user. Maybe the documentation could add some kind of
> warning about that?


I changed the paragraph to
Lines within false branches are parsed normally, however, any
completed
queries are not sent to the server, and any completed commands other
than conditionals (\if, \elif,
\else, \endif) are ignored.

There's no mention that psql variables AREN'T expanded, so the user has
every expectation that they are.


>
> Add space after comma when calling send_query.
>

Done.


>
> I'm not sure why you removed the comments before \if in the doc example.
> Maybe keep a one liner?
>

Didn't mean to, restored.


> Why not reuse the pop loop trick to "destroy" the stack?


Forgot about that, restored.
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index ae58708..625c9a8 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2035,6 +2035,81 @@ hello 10
 
   
 
+  
+\if expr
+\elif expr
+\else
+\endif
+
+
+This group of commands implements nestable conditional blocks, like
+this:
+
+
+-- set ON_ERROR_STOP in case the variables are not valid boolean expressions
+\set ON_ERROR_STOP on
+-- check for the existence of two separate records in the database and store
+-- the results in separate psql variables
+SELECT
+EXISTS(SELECT 1 FROM customer WHERE customer_id = 123) as is_customer,
+EXISTS(SELECT 1 FROM employee WHERE employee_id = 456) as is_employee
+\gset
+\if :is_customer
+SELECT * FROM customer WHERE customer_id = 123;
+\elif :is_employee
+\echo 'is not a customer but is an employee'
+SELECT * FROM employee WHERE employee_id = 456;
+\else
+\if yes
+\echo 'not a customer or employee'
+\else
+\echo 'this should never print'
+\endif
+\endif
+
+
+Conditional blocks must begin with a \if and end
+with an \endif, and the pairs must be found in
+the same source file. If an EOF is reached on the main file or an
+\include-ed file before all local
+\if-\endif are matched, then
+psql will raise an error.
+
+
+A conditional block can have any number of 
+\elif clauses, which may optionally be followed by a
+single \else clause.
+
+
+The \if and \elif commands
+read the rest of the line and evaluate it as a boolean expression.
+Currently, expressions are limited to a single unquoted string
+which are evaluated like other on/off options, so the valid values
+are any unambiguous case insensitive matches for one of:
+true, false, 
1,
+0, on, off,
+yes, no.  So 
+t, T, and tR
+will all match true.
+
+Expressions that do not properly evaluate to true or false will
+generate an error and cause the \if or
+\elif command to fail.  Because that behavior may
+change branching context in undesirable ways (executing code which
+was intended to be skipped, causing \elif,
+\else, and \endif commands to
+pair with the wrong \if, etc), it is
+recommended that scripts which use conditionals also set
+ON_ERROR_STOP.
+
+
+Lines within false branches are parsed normally, however, any completed
+queries are not sent to the server, and any completed commands other
+than conditionals (\if, \elif,
+\else, \endif) are ignored.
+
+
+  
 
   
 \ir or \include_relative 
filename
@@ -3703,7 +3778,8 @@ testdb= INSERT INTO my_table VALUES 
(:'content');
 
 
 In prompt 1 normally =,
-but ^ if in single-line mode,
+but ^ if in single-line mode, or
+@ if the session is in a false conditional block,
 or ! if the session is disconnected from the
 database (which can happen if \connect fails).
 In prompt 2 

Re: [HACKERS] bytea_output output of base64

[David Fetter]

On Thu, Feb 23, 2017 at 05:55:37PM -0500, Bruce Momjian wrote:
> On Thu, Feb 23, 2017 at 04:08:58PM -0500, Tom Lane wrote:
> > Bruce Momjian  writes:
> > > Is there a reason we don't support base64 as a bytea_output output
> > > option, except that no one has implemented it?
> > 
> > How about "we already have one too many bytea output formats"?
> > I don't think forcing code to try to support still another one
> > is a great thing ... especially not if it couldn't be reliably
> > distinguished from the hex format.
> 
> Is there a reason we chose hex over base64?

Whether there was or not, there's not a compelling reason now to break
people's software.  When people want compression, methods a LOT more
effective than base64 are common.  Gzip, for example.

Best,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


-- 
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] Other formats in pset like markdown, rst, mediawiki

[Michael Paquier]

On Fri, Feb 24, 2017 at 3:09 AM, Jan Michálek  wrote:
> I can try it, doesn`t look dificult, but I`m worry, that I`m not able to
> write clean, pretty code.

If you want to have something available in Postgres 10, you had better
be quick. The last commit fest of the development cycle of Postgres 10
begins on the 1st of March, you need to to register your patch here:
https://commitfest.postgresql.org/13/
Here are also some rough guidelines about submitting a patch:
https://wiki.postgresql.org/wiki/Submitting_a_Patch
-- 
Michael


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

Re: [HACKERS] Range Partitioning behaviour - query

[Venkata B Nagothi]

On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote  wrote:

> Hi,
>
> On 2017/02/23 11:55, Venkata B Nagothi wrote:
> > Hi Hackers,
> >
> > I have noticed the following behaviour in range partitioning which i felt
> > is not quite correct (i missed reporting this) -
> >
> > I have tested by creating a date ranged partition.
> >
> > I created the following table.
> >
> > db03=# CREATE TABLE orders (
> > o_orderkey INTEGER,
> > o_custkey INTEGER,
> > o_orderstatus CHAR(1),
> > o_totalprice REAL,
> > o_orderdate DATE,
> > o_orderpriority CHAR(15),
> > o_clerk CHAR(15),
> > o_shippriority INTEGER,
> > o_comment VARCHAR(79)) partition by range (o_orderdate);
> > CREATE TABLE
> >
> > Created the following partitioned tables :
> >
> >
> > db03=# CREATE TABLE orders_y1992
> > PARTITION OF orders FOR VALUES FROM ('1992-01-01') TO ('1992-12-31');
> >   CREATE TABLE
> >
> > db03=# CREATE TABLE orders_y1993
> > PARTITION OF orders FOR VALUES FROM ('1993-01-01') TO ('*1993-12-31
> '*);
> > CREATE TABLE
> >
> > db03=# CREATE TABLE orders_y1994
> >PARTITION OF orders FOR VALUES FROM ('1994-01-01') TO ('1994-12-31');
> > CREATE TABLE
> >
> >
> > The rows with the date "1993-12-31" gets rejected as shown below -
> >
> > db03=# copy orders from '/data/orders.csv' delimiter '|';
> > ERROR:  no partition of relation "orders" found for row
> > DETAIL:  Failing row contains (353, 8878, F, 273342, *1993-12-31*, 5-LOW
> >, Clerk#02241, 0,  quiet ideas sleep. even instructions cajole
> > slyly. silently spe).
> > CONTEXT:  COPY orders, line 89: "353|8878|F|273342|*1993-12-31*|5-LOW
> >|Clerk#02241|0| quiet ideas sleep. even instructions..."
> >
> > I would want the partition "orders_y1993" to accept all the rows with the
> > date 1993-12-31.
>
> [ ... ]
>
> > Am i missing anything here ?
>
> Upper bound of a range partition is an exclusive bound.  A note was added
> recently to the CREATE TABLE page to make this clear.
>
> https://www.postgresql.org/docs/devel/static/sql-createtable.html


Thanks. Actually, my confusion was that the upper bound value would be
included when "TO" clause is used in the syntax.
Also, there are no options like "<" or "LESS THAN" clauses available. So,
"TO" translates to "<". That is what i wanted to confirm.

Regards,

Venkata B N
Database Consultant

Re: [HACKERS] bytea_output output of base64

[Bruce Momjian]

On Thu, Feb 23, 2017 at 04:08:58PM -0500, Tom Lane wrote:
> Bruce Momjian  writes:
> > Is there a reason we don't support base64 as a bytea_output output
> > option, except that no one has implemented it?
> 
> How about "we already have one too many bytea output formats"?
> I don't think forcing code to try to support still another one
> is a great thing ... especially not if it couldn't be reliably
> distinguished from the hex format.

Is there a reason we chose hex over base64?

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

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


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

Re: [HACKERS] Poor memory context performance in large hash joins

[Tom Lane]

Jeff Janes  writes:
> The number of new chunks can be almost as as large as the number of old
> chunks, especially if there is a very popular value.  The problem is that
> every time an old chunk is freed, the code in aset.c around line 968 has to
> walk over all the newly allocated chunks in the linked list before it can
> find the old one being freed.  This is an N^2 operation, and I think it has
> horrible CPU cache hit rates as well.

Maybe it's time to convert that to a doubly-linked list.  Although if the
hash code is producing a whole lot of requests that are only a bit bigger
than the separate-block threshold, I'd say It's Doing It Wrong.  It should
learn to aggregate them into larger requests.

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] Poor memory context performance in large hash joins

[Peter Geoghegan]

On Thu, Feb 23, 2017 at 2:13 PM, Jeff Janes  wrote:
> Is there a good solution to this?  Could the new chunks be put in a
> different memory context, and then destroy the old context and install the
> new one at the end of ExecHashIncreaseNumBatches? I couldn't find a destroy
> method for memory contexts, it looks like you just reset the parent instead.
> But I don't think that would work here.

Are you aware of the fact that tuplesort.c got a second memory context
for 9.6, entirely on performance grounds?


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

[HACKERS] Poor memory context performance in large hash joins

[Jeff Janes]

When doing a hash join with large work_mem, you can have a large number of
chunks.  Then if ExecHashIncreaseNumBatches gets called, those chunks are
walked through, moving the tuples to new chunks (or to disk, if they no
longer match the batch's bitmask), and freeing the old chunks.

The number of new chunks can be almost as as large as the number of old
chunks, especially if there is a very popular value.  The problem is that
every time an old chunk is freed, the code in aset.c around line 968 has to
walk over all the newly allocated chunks in the linked list before it can
find the old one being freed.  This is an N^2 operation, and I think it has
horrible CPU cache hit rates as well.

Is there a good solution to this?  Could the new chunks be put in a
different memory context, and then destroy the old context and install the
new one at the end of ExecHashIncreaseNumBatches? I couldn't find a destroy
method for memory contexts, it looks like you just reset the parent
instead. But I don't think that would work here.

Thanks,

Jeff

Re: [HACKERS] Faster methods for getting SPI results (460% improvement)

[Jim Nasby]

On 1/23/17 9:23 PM, Jim Nasby wrote:

I think the last step here is to figure out how to support switching
between the current behavior and the "columnar" behavior of a dict of lists.


I've thought more about this... instead of trying to switch from the 
current situation of 1 choice of how results are return to 2 choices, I 
think it'd be better to just expose the API that the new Destination 
type provides to SPI. Specifically, execute a python function during 
Portal startup, and a different function for receiving tuples. There'd 
be an optional 3rd function for Portal shutdown.


The startup function would be handed details of the resultset it was 
about to receive, as a list that contained python tuples with the 
results of SPI_fname, _gettype, _gettypeid. This function would return a 
callback version number and a python object that would be kept in the 
DestReceiver.


The receiver function would get the object created by the startup 
function, as well as a python tuple of the TupleTableSlot that had gone 
through type conversion. It would need to add the value to the object 
from the startup function. It would return true or false, just like a 
Portal receiver function does.


The shutdown function would receive the object that's been passed 
around. It would be able to do any post-processing. Whatever it returned 
is what would be handed back to python as the results of the query.


The version number returned by the startup function allows for future 
improvements to this facility. One idea there is allowing the startup 
function to control how Datums get mapped into python objects.


In order to support all of this without breaking backwards compatibility 
or forking a new API, plpy.execute would accept a kwdict, to avoid 
conflicting with the arbitrary number of arguments that can currently be 
accepted. We'd look in the kwdict for a key called "portal_functions" 
pointing at a 2 or 3 element tuple of the startup, receive and shutdown 
functions. plpy would pre-define a tuple that provides the current 
behavior, and that's what would be used by default. In the future, we 
might add a way to control the default.


Comments?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] Idea on how to simplify comparing two sets

[Nico Williams]

On Tue, Feb 07, 2017 at 01:03:14PM -0500, Tom Lane wrote:
> "David G. Johnston"  writes:
> 
> Actually ... now that you mention full join, I believe this works:
> 
> select * from (select ...) s1 full join (select ...) s2
>   on ((s1.*)=(s2.*)) where s1.* is distinct from s2.*;

You can drop the .*s:

  select * from (select ...) s1 full join (select ...) s2
on s1 = s2 where s1 is distinct from s2;

And even:

  select s1, s2 from (select ...) s1 natural full outer join (select ...) s2;

This makes it possible to write very generic (schema-wise) code for
comparing table sources.

As I've mentioned elsewhere, there is an issue with NULLs in columns...

I really, really would like either a full equijoin where equality treats
NULL = NULL -> true for this purpose, or a natural join where only
primary key or not-nullable columns are used, or a USING clause form
where I can specify such behavior without having to list all the columns
that should be used.

I use NATURAL FULL OUTER JOIN for computing materialized view diffs in
my alternate view materialization system.  NULLs are poison for this
purpose, yielding false positive differences.  But my code also uses the
table row value form above in order to avoid having to generate column
lists for a USING clause or expressions for ON.

These requests are not for syntactic sugar, not really.  But I realize
they may be non-trivial -- I may be looking for unobtanium.

> > That said I'm not sure how much we want to go down this road on our own.
> > It'd be nice to have when its needed but its not something that gets much
> > visibility on these lists to suggest a large pent-up demand.
> 
> Yeah, if this isn't in the standard and not in other databases either,
> that would seem to suggest that it's not a big requirement.

SQLite3 famously lacks FULL joins.  It kills me because the alternative
constructions become O(N log M) instead of O(N) for a properly
implemented FULL join (assuming suitable indices anyways).

I wouldn't suggest that that's a reason not to support FULL joins in any
other RDBMS, rather, I'd suggest that SQLite3 is missing an important
feature.

Pardon the tangent.  It may not really be applicable here, as here I
think OP is looking for syntactic sugar rather than an important
optimization.  But the point is that sometimes you have to lead the
standards-setting and/or the competition.

Nico
-- 


-- 
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] \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless)

[Fabien COELHO]

Hello Corey,


v16 is everything v15 promised to be.


My 0.02€:

Patch applies, make check ok, psql make check ok as well.


Welcome to v15, highlights:
- all conditional data structure management moved to conditional.h and
  conditional.c


Indeed.

I cannot say that I find it better, but (1) Tom did required it and (2) it 
still works:-)


If the stack stuff had stayed in "fe_utils", it would have been easy to 
reuse them in pgbench where they might be useful... But who cares?



- conditional state lives in mainloop.c and is passed to
  HandleSlashCommands, exec_command and get_prompt as needed


Same.


- no more pset.active_branch, uses conditional_active(conditional_stack)
  instead


Same.


- PsqlScanState no longer has branching state


Indeed.


- Implements the %R '@' prompt on false branches.


I'm not sure that '@' is the best choice, but this is just one char.

I noticed that it takes precedence over '!'. Why not. ISTM that orthogonal 
features are not shown independently, but this is a preexisting state, and 
it allows to have a shorter prompt, so why not.


Anyway, the '%R' documentation needs to be updated.


- Variable expansion is never suppressed even in false blocks,
  regression test edited to reflect this.


It could be nice to keep test cases that show what may happen?

The various simplifications required result in the feature being more 
error prone for the user. Maybe the documentation could add some kind of 
warning about that?



- ConditionalStack could morph into PsqlFileState without too much
  work.


Probably.

Code details:

Add space after comma when calling send_query.

I'm not sure why you removed the comments before \if in the doc example. 
Maybe keep a one liner?


Why not reuse the pop loop trick to "destroy" the stack?

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

Re: [HACKERS] btree_gin and btree_gist for enums

[Andrew Dunstan]

On 02/23/2017 04:41 PM, Tom Lane wrote:
> BTW, while I'm looking at this ... isn't gin_enum_cmp broken on its face?
> It's using DirectFunctionCall2 to call enum_cmp, and that's wrong because
> DirectFunctionCall2 does not pass through a flinfo but enum_cmp needs to
> have one.  I've not tested, but I'm certain that this would dump core if
> asked to compare odd-numbered enum OIDs.
>
>   



Yes, that's what I'm trying to fix.

cheers

andrew

-- 
Andrew Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



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

Re: [HACKERS] btree_gin and btree_gist for enums

[Tom Lane]

Andrew Dunstan  writes:
> I'm not entirely sure how I should replace those DirectFunctionCall2 calls.

Basically what we want is for the called function to be able to use the
fcinfo->flinfo->fn_extra and fcinfo->flinfo->fn_mcxt fields of the
FmgrInfo struct that GIN has set up for the btree_gin support function.

The fast but somewhat scary way to do it would just be to pass through
the flinfo pointer as-is.  Imagine that fmgr.c grows a set of functions
like

Datum
DontKnowWhatToCallThisFunctionCall2(PGFunction func,
FmgrInfo *flinfo, Oid collation,
Datum arg1, Datum arg2)
{
FunctionCallInfoData fcinfo;
Datumresult;

InitFunctionCallInfoData(fcinfo, flinfo, 2, collation, NULL, NULL);

fcinfo.arg[0] = arg1;
fcinfo.arg[1] = arg2;
fcinfo.argnull[0] = false;
fcinfo.argnull[1] = false;

result = (*func) ();

/* Check for null result, since caller is clearly not expecting one */
if (fcinfo.isnull)
elog(ERROR, "function %p returned NULL", (void *) func);

return result;
}

and then you'd just pass through the fcinfo->flinfo you got.

The reason this is kind of scary is that it's just blithely assuming
that the function won't look at the *other* fields of the FmgrInfo.
If it did, it would likely get very confused, since those fields
would be describing the GIN support function, not the function we're
calling.

We could alternatively have this trampoline function set up a fresh, local
FmgrInfo struct that it zeroes except for copying fn_extra and fn_mcxt
from the caller's struct, and then it copies fn_extra back again on the
way out.  That's a few more cycles but it would be safer, I think; if the
function tried to look at the other fields such as fn_oid it would see
obviously bogus data.

BTW, while I'm looking at this ... isn't gin_enum_cmp broken on its face?
It's using DirectFunctionCall2 to call enum_cmp, and that's wrong because
DirectFunctionCall2 does not pass through a flinfo but enum_cmp needs to
have one.  I've not tested, but I'm certain that this would dump core if
asked to compare odd-numbered enum OIDs.

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] Checksums by default?

[Bruce Momjian]

On Sat, Jan 21, 2017 at 12:46:05PM -0500, Stephen Frost wrote:
> * Petr Jelinek (petr.jeli...@2ndquadrant.com) wrote:
> > As we don't know the performance impact is (there was no benchmark done
> > on reasonably current code base) I really don't understand how you can
> > judge if it's worth it or not.
> 
> Because I see having checksums as, frankly, something we always should
> have had (as most other databases do, for good reason...) and because
> they will hopefully prevent data loss.  I'm willing to give us a fair
> bit to minimize the risk of losing data.

Do these other databases do checksums because they don't do
full_page_writes?  They just detect torn pages rather than repair them
like we do?

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

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


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

Re: [HACKERS] Idea on how to simplify comparing two sets

[Corey Huinker]

>
> FWIW I've found myself needing the precursor to this this (give me the
> full diff) at least a couple times, and it's quite a PITA on anything but a
> trivial relation.
>
> It's also not possible to make this easier via an SRF because you don't
> know in advance what the result set looks like. So the best I've ever come
> up with is a file that can be included in psql that depends on having set
> two psql variables to the names of relations that can be queried (and if
> you need more than a relation you need to create a temp view).
>
> I've wondered about the possibility of allowing PLs the ability to
> dynamically define their return type based on their arguments. That would
> allow for an SRF to handle this case, and would be significantly more
> flexible than trying to do that using pseudotypes.


My experiences are similar. At the moment, I'm resigned to using relying on
pgtap:

-- set environment/path to point to "control"
create temporary table test_1_wants as ;

-- set environment/path to point to "experiment"
create temporary table test_1_has as ;

select results_eq( 'table test_1_has', 'table test_1_wants', 'test 1');

I've had to do it with temp tables any time the environment is different
between control/experiment, which is the case when you're developing a
drop-in replacement for an SRF or view or whatever.

Re: [HACKERS] Idea on how to simplify comparing two sets

[Nico Williams]

On Tue, Feb 07, 2017 at 10:58:41AM -0500, Tom Lane wrote:
> Joel Jacobson  writes:
> > Currently there is no simple way to check if two sets are equal.
> 
> Uh ... maybe check whether SELECT set1 EXCEPT SELECT set2
> and SELECT set2 EXCEPT SELECT set1 are both empty?

Even better, NATURAL(*) FULL OUTER JOIN the two table sources and check
that the result is empty.  If the two sources have useful indices (or if
PG constructs suitable automatic indices for them) for this then the
query should be O(N).

(*) However, if you do this then there'd better not be any NULLs in
columns, otherwise you'll get false positives for differences.  Of
course, if the two table sources have common primary key prefixes and
you only care about equality in those columns, then just FULL OUTER JOIN
USING ().

Nico
-- 


-- 
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] PinBuffer() no longer makes use of strategy

[Jim Nasby]

On 2/4/17 1:47 PM, Alexander Korotkov wrote:

I'm tempted to put the old logic back, but more because this likely was
unintentional, not because I think it's clearly better.


+1
Yes, it was unintentional change.  So we should put old logic back
unless we have proof that this change make it better.
Patch is attached.  I tried to make some comments, but probably they are
not enough.


Added to CF: https://commitfest.postgresql.org/13/1029/
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] Idea on how to simplify comparing two sets

[Jim Nasby]

On 2/7/17 12:03 PM, Tom Lane wrote:

That said I'm not sure how much we want to go down this road on our own.
It'd be nice to have when its needed but its not something that gets much
visibility on these lists to suggest a large pent-up demand.

Yeah, if this isn't in the standard and not in other databases either,
that would seem to suggest that it's not a big requirement.


FWIW I've found myself needing the precursor to this this (give me the 
full diff) at least a couple times, and it's quite a PITA on anything 
but a trivial relation.


It's also not possible to make this easier via an SRF because you don't 
know in advance what the result set looks like. So the best I've ever 
come up with is a file that can be included in psql that depends on 
having set two psql variables to the names of relations that can be 
queried (and if you need more than a relation you need to create a temp 
view).


I've wondered about the possibility of allowing PLs the ability to 
dynamically define their return type based on their arguments. That 
would allow for an SRF to handle this case, and would be significantly 
more flexible than trying to do that using pseudotypes.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] bytea_output output of base64

[Tom Lane]

Bruce Momjian  writes:
> Is there a reason we don't support base64 as a bytea_output output
> option, except that no one has implemented it?

How about "we already have one too many bytea output formats"?
I don't think forcing code to try to support still another one
is a great thing ... especially not if it couldn't be reliably
distinguished from the hex format.

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] Add checklist item for psql completion to commitfest review

[Jim Nasby]

On 2/15/17 9:49 AM, Robert Haas wrote:

On Tue, Feb 14, 2017 at 1:13 PM, Jim Nasby  wrote:

After seeing Yet Another Missing Psql Tab Completion it occurred to me...
why not add a checklist item to the commitfest review page? I realize most
regular contributors don't use the form, but a fair number of people do. I
don't see how it could hurt.

Another possible idea is a git hook that checks to see if the psql
completion code has been touched if any of the grammar has been. That could
certainly trigger false positives so it'd need to be easy to over-ride, but
AFAIK that could be done via a special phrase in the commit message.


In the past, our usual policy has been that tab completion isn't a
hard requirement for a patch implementing a new feature.  It often
gets added after the fact.  I think that policy has worked fine, but
it's not a bad thing for people to include tab completion in the
original patch either, if they have the brain space for it.


I've never messed with completion so I don't know how hard it is, but my 
impression is that it gets added after the fact not because of any 
intentional decisions but because people simply forget about it. ISTM it 
would be more efficient of community resources to deal with completion 
in the original patch, unless there's some reason not to.


IOW, no, don't make it a hard requirement, but don't omit it simply 
through forgetfulness.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] Index corruption with CREATE INDEX CONCURRENTLY

[Jim Nasby]

On 2/19/17 5:27 AM, Robert Haas wrote:

(1) a multi-batch hash join, (2) a nested loop,
and (3) a merge join.  (2) is easy to implement but will generate a
ton of random I/O if the table is not resident in RAM.  (3) is most
suitable for very large tables but takes more work to code, and is
also likely to be a lot slower for small tables than a hash or
nestloop-based approach.


As I understand it, #3 is already in place for validate_index(). I think 
you'd just need a different callback that checks the heap key.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] Automatic cleanup of oldest WAL segments with pg_receivexlog

[Jim Nasby]

On 2/23/17 10:10 AM, Magnus Hagander wrote:

Wouldn't this one, along with some other scenarios, be better provided
by the "run command at end of segment" function that we've talked about
before? And then that external command could implement whatever aging
logic would be appropriate for the environment?


That kind of API lead to difficulties with archiving direct from the 
database, so I'm not sure it's the best way to go.


ISTM what's really needed is a good way for users to handle retention 
for both WAL as well as base backups. A tool that did that would need to 
understand what WAL is required to safely restore a base backup. It 
should be possible for users to have a separate retention policy for 
just base backups as well as backups that support full PITR. You'd also 
need an easy way to deal with date ranges (so you can do things like 
"delete all backups more than 1 year old").


Perhaps a good starting point would be a tool that lets you list what 
base backups you have, what WAL those backups need, when the backups 
were taken, etc.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] A typo in mcxt.c

[Jim Nasby]

On 2/23/17 6:38 AM, Thomas Munro wrote:

I'm not so confident, but the "'tis" seems to me to be a typo of
"it's".

That is an archaic way of contracting the same words differently:


Given the number of non-native English speakers we have, it's probably 
worth changing it...

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


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

Re: [HACKERS] Measuring replay lag

[Simon Riggs]

On 21 February 2017 at 21:38, Thomas Munro
 wrote:
> On Tue, Feb 21, 2017 at 6:21 PM, Simon Riggs  wrote:
>> And happier again, leading me to move to the next stage of review,
>> focusing on the behaviour emerging from the design.
>>
>> So my current understanding is that this doesn't rely upon LSN
>> arithmetic to measure lag, which is good. That means logical
>> replication should "just work" and future mechanisms to filter
>> physical WAL will also just work. This is important, so please comment
>> if you see that isn't the case.
>
> Yes, my understanding (based on
> https://www.postgresql.org/message-id/f453caad-0396-1bdd-c5c1-5094371f4...@2ndquadrant.com
> ) is that this should in principal work for logical replication, it
> just might show the same number in 2 or 3 of the lag columns because
> of the way it reports LSNs.
>
> However, I think a call like LagTrackerWrite(SendRqstPtr,
> GetCurrentTimestamp()) needs to go into XLogSendLogical, to mirror
> what happens in XLogSendPhysical.  I'm not sure about that.

Me neither, but I think we need this for both physical and logical.

Same use cases graphs for both, I think. There might be issues with
the way LSNs work for logical.

>> I think what we need to show some test results with the graph of lag
>> over time for these cases:
>> 1. steady state - pgbench on master, so we can see how that responds
>> 2. blocked apply on standby - so we can see how the lag increases but
>> also how the accuracy goes down as the lag increases and whether the
>> reported value changes (depending upon algo)
>> 3. burst mode - where we go from not moving to moving at high speed
>> and then stop again quickly
>> +other use cases you or others add
>
> Good idea.  Here are some graphs.  This is from a primary/standby pair
> running on my local development machine, so the times are low in the
> good cases.  For 1 and 2 I used pgbench TPCB-sort-of.  For 3 I used a
> loop that repeatedly dropped and created a huge table, sleeping in
> between.

Thanks, very nice

>> Does the proposed algo work for these cases? What goes wrong with it?
>> It's the examination of these downsides, if any, are the things we
>> need to investigate now to allow this to get committed.
>
> The main problem I discovered was with 2.  If replay is paused, then
> the reported LSN completely stops advancing, so replay_lag plateaus.
> When you resume replay, it starts reporting LSNs advancing again and
> suddenly discovers and reports a huge lag because it advances past the
> next sample in the buffer.
>
> I realised that you had suggested the solution to this problem
> already: interpolation.  I have added simple linear interpolation that
> checks if there is a future LSN in the buffer, and if so it
> interpolates linearly to synthesise the local flush time of the
> reported LSN, which is somewhere between the last and next sample's
> recorded local flush time.  This seems to work well for the
> apply-totally-stopped case.

Good

> I added a fourth case 'overwhelm.png' which you might find
> interesting.  It's essentially like one 'burst' followed by a 100% ide
> primary.  The primary stops sending new WAL around 50 seconds in and
> then there is no autovacuum, nothing happening at all.  The standby
> start is still replaying its backlog of WAL, but is sending back
> replies only every 10 seconds (because no WAL arriving so no other
> reason to send replies except status message timeout, which could be
> lowered).  So we see some big steps, and then we finally see it
> flat-line around 60 seconds because there is still now new WAL so we
> keep showing the last measured lag.  If new WAL is flushed it will pop
> back to 0ish, but until then its last known measurement is ~14
> seconds, which I don't think is technically wrong.

If I understand what you're saying, "14 secs" would not be seen as the
correct answer by our users when the delay is now zero.

Solving that is where the keepalives need to come into play. If no new
WAL, send a keepalive and track the lag on that.

>> Some minor points on code...
>> Why are things defined in walsender.c and not in .h?
>
> Because they are module-private.

;-) It wasn't a C question.


So looks like we're almost there.

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[HACKERS] bytea_output output of base64

[Bruce Momjian]

Currently bytea_output supports values of 'hex' (the default) and
'escape' (octal).  'hex' uses two characters per byte, while escape uses
three (ignoring the prefix overhead of \x or \[0-9].)

It is my understanding that base64 uses 1.37 characters per byte:

https://en.wikipedia.org/wiki/Base64

Is there a reason we don't support base64 as a bytea_output output
option, except that no one has implemented it?

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

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


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

Re: [HACKERS] \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless)

[Pavel Stehule]

2017-02-23 18:52 GMT+01:00 Fabien COELHO :

>
> Hello Daniel,
>
> Ah, I see why *that* wants to know about it ... I think.  I suppose you're
>>> arguing that variable expansion shouldn't be able to insert, say, an
>>> \else
>>> in a non-active branch?  Maybe, but if it can insert an \else in an
>>> active
>>> branch, then why not non-active too?  Seems a bit inconsistent.
>>>
>>
>> Are we sold on the idea that conditionals should be implemented
>> by meta-commands, rather than for example terminal symbols of
>> a new grammar on top of the existing?
>>
>
> I would say that this already exists server-side, and it is named
> PL/pgSQL:-)
>
> I think that once psql has started with \xxx commands, then client-side
> extensions must stick with it till the end of time.


+1

we don't need strong client side scripting language - it should be just
simple.

Pavel

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

Re: [HACKERS] Other formats in pset like markdown, rst, mediawiki

[Pavel Stehule]

2017-02-23 19:09 GMT+01:00 Jan Michálek :

> I can try it, doesn`t look dificult, but I`m worry, that I`m not able to
> write clean, pretty code.
>

It is not a problem, there is lot of iteration for code cleaning

Regards

Pavel

>
> 2017-02-23 18:03 GMT+01:00 Alvaro Herrera :
>
>> Leknín Řepánek wrote:
>> > I was mainly using tables in rst, i think, that this format is very
>> > similar to "grid tables" in markdown. And it is really similar to
>> default borders used
>> > in psql with border 2.
>>
>> Great.  So are you writing the code now?  :-)
>>
>> --
>> Álvaro Herrerahttps://www.2ndQuadrant.com/
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>
>
>
> --
> Jelen
> Starší čeledín datovýho chlíva
>

Re: [HACKERS] Documentation improvements for partitioning

[Peter Geoghegan]

On Thu, Feb 23, 2017 at 11:13 AM, Simon Riggs  wrote:
> My argument was that CREATE INDEX is expected to just work on tables
> at present, so should also just work on partitioned tables. Without
> that, the reality is people will need to write scripts.

Really? What about postgres_fdw?

Even if that counter-example didn't exist, I'd still disagree. People
may expect that CREATE INDEX should just work, but that expectation is
not as general as you suggest. Otherwise, I doubt we'd be talking
about it at all.

> I don't see how that relates to the desire for multiple index options,
> since one of them would need to be the default and we could provide
> one in this release, one in the next etc..

You didn't say any of that until now. And besides, I think that global
indexes make a lot more sense as a default.

You seem to be saying that a simple CREATE INDEX could be interpreted
as meaning one or the other of those two behaviors just as easily
(global index vs. create an index on each partition). I don't think
it's a good idea to try to meet any general "just works" expectation
if what you actually get does not fit the intuition of almost all
users. "Just don't show me an error" seems like a bad design goal,
especially for a utility statement.

> The current design has assumed many things, leading me to question
> what else has been assumed.
>
> Challenging those assumptions is important and has been upheld.

I agree. The irony is that in so doing, you yourself make your own
assumptions, confusing everyone, and making it harder to act on your
feedback. You did make some reasonable points, IMV.

> I've seen many patches rejected because they do not contain the
> desired feature set, yet.

Obviously that general principle is not under discussion. My point, of
course, was that it seems pretty clear to me that this is on the right
side of that fence.

-- 
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] Documentation improvements for partitioning

[Simon Riggs]

On 23 February 2017 at 17:27, Peter Geoghegan  wrote:
> On Thu, Feb 23, 2017 at 8:08 AM, Simon Riggs  wrote:
>> What claims are you talking about? Which things have been exaggerated,
>> and by whom?
>
> * The specious argument that we should "just" have CREATE INDEX create
> equivalent indexes across partitions, to save all those people from
> writing all those scripts.
>
> The reality is that there are several ways that that would not comport
> with the existing design of things. Most obviously: where does that
> leave the design of global indexes that we eventually come up with?
> Now, maybe there is a good way to make this easier for users, worth
> doing sooner rather than later, but that will be subtle, and you
> should at least acknowledge that.

My argument was that CREATE INDEX is expected to just work on tables
at present, so should also just work on partitioned tables. Without
that, the reality is people will need to write scripts.

I don't see how that relates to the desire for multiple index options,
since one of them would need to be the default and we could provide
one in this release, one in the next etc..

> * "It leaves me asking what else is missing"... "If we wanted them to
> act identically we wouldn't have any need for a new feature at all, so
> clearly that doesn't make sense as an argument."
>
> These remarks sound imperious to me. I think that this could be quite
> demoralizing to someone in Amit's position, and you ought to give some
> consideration to that. I think that several of your remarks on the
> patch are facile and/or needlessly ambiguous, which is what makes this
> lack of tact seem unfair to me.

The current design has assumed many things, leading me to question
what else has been assumed.

Challenging those assumptions is important and has been upheld.

I agree my review comments could well be demoralizing, which is why I
said "Good work so far". It takes a while to realise that review
comments are given to patch authors with the intent to help improve
the product, not as personal attacks. I thought you would know that by
now.

Imperious? No, definitely a Jedi.

> * "Good work so far, but there is still a very significant amount of
> work to do."
>
> There is always more work to do, so why say so? I think that the
> implication is that this isn't complete as a feature that goes into
> the next release, which I disagree with.

I've seen many patches rejected because they do not contain the
desired feature set, yet.

ISTM my opinion on when that is reached is as valid as yours or anyone
else's, so I'm unclear as to your issue.

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)

[Bruce Momjian]

On Thu, Feb 23, 2017 at 03:58:59PM -0300, Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > On Thu, Feb 23, 2017 at 03:45:24PM -0300, Alvaro Herrera wrote:
> 
> > > > and potentially trim the first HOT chain as those tuples become
> > > > invisible.
> > > 
> > > That can already happen even without WARM, no?
> > 
> > Uh, the point is that with WARM those four early tuples can be removed
> > via a prune, rather than requiring a VACUUM. Without WARM, the fourth
> > tuple can't be removed until the index is cleared by VACUUM.
> 
> I *think* that the WARM-updated one cannot be pruned either, because
> it's pointed to by at least one index (otherwise it'd have been a HOT
> update).  The ones prior to that can be removed either way.

Well, if you can't prune across index-column changes, how is a WARM
update different than just two HOT chains with no WARM linkage?

> I think the part you want (be able to prune the WARM updated tuple) is
> part of what Pavan calls "turning the WARM chain into a HOT chain", so
> not part of the initial patch.  Pavan can explain this part better, and
> also set me straight in case I'm wrong in the above :-)

VACUUM can already remove entire HOT chains that have expired.  What
his VACUUM patch does, I think, is to remove the index entries that no
longer point to values in the HOT/WARM chain, turning the chain into
fully HOT, so another WARM addition to the chain can happen.

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

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


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

Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)

[Alvaro Herrera]

Bruce Momjian wrote:
> On Thu, Feb 23, 2017 at 03:45:24PM -0300, Alvaro Herrera wrote:

> > > and potentially trim the first HOT chain as those tuples become
> > > invisible.
> > 
> > That can already happen even without WARM, no?
> 
> Uh, the point is that with WARM those four early tuples can be removed
> via a prune, rather than requiring a VACUUM. Without WARM, the fourth
> tuple can't be removed until the index is cleared by VACUUM.

I *think* that the WARM-updated one cannot be pruned either, because
it's pointed to by at least one index (otherwise it'd have been a HOT
update).  The ones prior to that can be removed either way.

I think the part you want (be able to prune the WARM updated tuple) is
part of what Pavan calls "turning the WARM chain into a HOT chain", so
not part of the initial patch.  Pavan can explain this part better, and
also set me straight in case I'm wrong in the above :-)

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)

[Bruce Momjian]

On Thu, Feb 23, 2017 at 03:45:24PM -0300, Alvaro Herrera wrote:
> Bruce Momjian wrote:
> 
> > Well, let's walk through this.  Let's suppose you have three updates
> > that stay on the same page and don't update any indexed columns --- that
> > would produce a HOT chain of four tuples.  If you then do an update that
> > changes an indexed column, prior to this patch, you get a normal update,
> > and more HOT updates can be added to this.  With WARM, we can join those
> > chains
> 
> With WARM, what happens is that the first three updates are HOT updates
> just like currently, and the fourth one is a WARM update.

Right.

> > and potentially trim the first HOT chain as those tuples become
> > invisible.
> 
> That can already happen even without WARM, no?

Uh, the point is that with WARM those four early tuples can be removed
via a prune, rather than requiring a VACUUM. Without WARM, the fourth
tuple can't be removed until the index is cleared by VACUUM.

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

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


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

Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)

[Alvaro Herrera]

Bruce Momjian wrote:

> Well, let's walk through this.  Let's suppose you have three updates
> that stay on the same page and don't update any indexed columns --- that
> would produce a HOT chain of four tuples.  If you then do an update that
> changes an indexed column, prior to this patch, you get a normal update,
> and more HOT updates can be added to this.  With WARM, we can join those
> chains

With WARM, what happens is that the first three updates are HOT updates
just like currently, and the fourth one is a WARM update.

> and potentially trim the first HOT chain as those tuples become
> invisible.

That can already happen even without WARM, no?

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)

[Bruce Momjian]

On Thu, Feb 23, 2017 at 03:26:09PM -0300, Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > On Thu, Feb 23, 2017 at 03:03:39PM -0300, Alvaro Herrera wrote:
> > > Bruce Momjian wrote:
> > > 
> > > > As I remember, WARM only allows
> > > > a single index-column change in the chain.  Why are you seeing such a
> > > > large performance improvement?  I would have thought it would be that
> > > > high if we allowed an unlimited number of index changes in the chain.
> > > 
> > > The second update in a chain creates another non-warm-updated tuple, so
> > > the third update can be a warm update again, and so on.
> > 
> > Right, before this patch they would be two independent HOT chains.
> 
> No, they would be a regular update chain, not HOT updates.

Well, let's walk through this.  Let's suppose you have three updates
that stay on the same page and don't update any indexed columns --- that
would produce a HOT chain of four tuples.  If you then do an update that
changes an indexed column, prior to this patch, you get a normal update,
and more HOT updates can be added to this.  With WARM, we can join those
chains and potentially trim the first HOT chain as those tuples become
invisible.

Am I missing something?

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

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


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

Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)

[Alvaro Herrera]

Bruce Momjian wrote:
> On Thu, Feb 23, 2017 at 03:03:39PM -0300, Alvaro Herrera wrote:
> > Bruce Momjian wrote:
> > 
> > > As I remember, WARM only allows
> > > a single index-column change in the chain.  Why are you seeing such a
> > > large performance improvement?  I would have thought it would be that
> > > high if we allowed an unlimited number of index changes in the chain.
> > 
> > The second update in a chain creates another non-warm-updated tuple, so
> > the third update can be a warm update again, and so on.
> 
> Right, before this patch they would be two independent HOT chains.

No, they would be a regular update chain, not HOT updates.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)

[Bruce Momjian]

On Thu, Feb 23, 2017 at 03:03:39PM -0300, Alvaro Herrera wrote:
> Bruce Momjian wrote:
> 
> > As I remember, WARM only allows
> > a single index-column change in the chain.  Why are you seeing such a
> > large performance improvement?  I would have thought it would be that
> > high if we allowed an unlimited number of index changes in the chain.
> 
> The second update in a chain creates another non-warm-updated tuple, so
> the third update can be a warm update again, and so on.

Right, before this patch they would be two independent HOT chains.  It
still seems like an unexpectedly-high performance win.  Are two
independent HOT chains that much more expensive than joining them via
WARM?

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

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


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

Re: [HACKERS] btree_gin and btree_gist for enums

[Andrew Dunstan]

On 11/05/2016 03:11 PM, Andrew Dunstan wrote:
>
>
> On 11/05/2016 01:13 PM, Tom Lane wrote:
>> Andrew Dunstan  writes:
>>> On 11/05/2016 11:46 AM, Tom Lane wrote:
 That may be a good fix for robustness purposes, but it seems pretty
 horrid
 from an efficiency standpoint.  Where is this call, and should we be
 modifying it to provide a flinfo?
>>> I thought of providing an flinfo, but I couldn't see a simple way to do
>>> it that would provide something much longer lived than the function
>>> call, in which case it seemed a bit pointless. That's why I asked for
>>> assistance :-)
>> Hmm.  The problem is that the intermediate layer in btree_gist (and
>> probably btree_gin too, didn't look) does not pass through the
>> FunctionCallInfo data structure that's provided by the GIST AM.
>> That wasn't needed up to now, because none of the supported data types
>> are complex enough that any cached state would be useful, but trying
>> to extend it to enums exposes the shortcoming.
>>
>> We could fix this, but it would be pretty invasive since it would
>> require
>> touching just about every function in btree_gist/gin.  Not entirely sure
>> that it's worth it.  On the other hand, the problem might well come up
>> again in future, perhaps for a datatype where the penalty for lack of
>> a cache is greater --- eg, it would be pretty painful to support
>> record_cmp without caching.  And the changes ought to be relatively
>> mechanical, even if they are extensive.
>
>
>
> Yeah. I think it's probably worth doing. btree_gin is probably a
> better place to start because it's largely macro-ized.



So looking at this we have:

static Datum
gin_btree_compare_prefix(FunctionCallInfo fcinfo)
{
Datum   a = PG_GETARG_DATUM(0);
Datum   b = PG_GETARG_DATUM(1);
QueryInfo  *data = (QueryInfo *) PG_GETARG_POINTER(3);
int32   res,
cmp;

cmp = DatumGetInt32(DirectFunctionCall2Coll(
data->typecmp,
PG_GET_COLLATION(),
   (data->strategy ==
BTLessStrategyNumber ||
 data->strategy ==
BTLessEqualStrategyNumber)
? data->datum : a,
b));

and then the referred to routine in the enum case looks like this:

Datum
gin_enum_cmp(PG_FUNCTION_ARGS)
{
   Oid a = PG_GETARG_OID(0);
   Oid b = PG_GETARG_OID(1);
   int res = 0;

   if (ENUM_IS_LEFTMOST(a))
   {
   res = (ENUM_IS_LEFTMOST(b)) ? 0 : -1;
   }
   else if (ENUM_IS_LEFTMOST(b))
   {
   res = 1;
   }
   else
   {
   res = DatumGetInt32(DirectFunctionCall2(enum_cmp,
   ObjectIdGetDatum(a),
   ObjectIdGetDatum(b)));
   }

   PG_RETURN_INT32(res);
}


I'm not entirely sure how I should replace those DirectFunctionCall2 calls.

cheers

andrew

-- 
Andrew Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services




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

Re: [HACKERS] FYI: git worktrees as replacement for "rsync the CVSROOT"

[Dagfinn Ilmari Mannsåker]

Bruce Momjian  writes:

> On Sun, Jan 15, 2017 at 03:01:47PM -0600, Jim Nasby wrote:
>> Not sure how many people still use [1], as referenced by our git wiki[2],
>> but it appears git worktrees are a viable replacement for that technique. In
>> short, if you're already in your checkout:
>> 
>> git worktree add ../9.6 REL9_6_STABLE
>> 
>> would give you a checkout of 9.6 in the ../9.6 directory.
>> 
>> BTW, I learned about this from this "git year in review" article[3].
>> 
>> 1: 
>> https://www.postgresql.org/message-id/20090602162347.gf23...@yugib.highrise.ca
>> 2: 
>> https://wiki.postgresql.org/wiki/Working_with_Git#Continuing_the_.22rsync_the_CVSROOT.22_workflow
>> 3: 
>> https://hackernoon.com/git-in-2016-fad96ae22a15?imm_mid=0ec3e0=em-prog-na-na-newsltr_20170114#.shgj609ad
>
> Uh, I don't see this in git 2.1.4:
>
>   $ git worktree
>   git: 'worktree' is not a git command. See 'git --help'.
>
> which is in Debian Jessie.  This reports worktree was added in 2.5,
> released in July 2015:

Backports has git 2.11.0. Just add the "jessie-backports" suite to your
sources list, e.g.:

deb http://ftp..debian.org/debian/ jessie-backports main

And install git from there:

sudo apt install git/jessie-backports

Apt won't upgrade other packages to backports versions, but any packages
you've manually installed from there will be kept up-to-date.

See https://backports.debian.org/ for more details.

-- 
"A disappointingly low fraction of the human race is,
 at any given time, on fire." - Stig Sandbeck Mathisen


-- 
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] Other formats in pset like markdown, rst, mediawiki

[Jan Michálek]

I can try it, doesn`t look dificult, but I`m worry, that I`m not able to
write clean, pretty code.

2017-02-23 18:03 GMT+01:00 Alvaro Herrera :

> Leknín Řepánek wrote:
> > I was mainly using tables in rst, i think, that this format is very
> > similar to "grid tables" in markdown. And it is really similar to
> default borders used
> > in psql with border 2.
>
> Great.  So are you writing the code now?  :-)
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>



-- 
Jelen
Starší čeledín datovýho chlíva

Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)

[Alvaro Herrera]

Bruce Momjian wrote:

> As I remember, WARM only allows
> a single index-column change in the chain.  Why are you seeing such a
> large performance improvement?  I would have thought it would be that
> high if we allowed an unlimited number of index changes in the chain.

The second update in a chain creates another non-warm-updated tuple, so
the third update can be a warm update again, and so on.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)

[Bruce Momjian]

On Wed, Feb  1, 2017 at 10:46:45AM +0530, Pavan Deolasee wrote:
> > contains a WARM tuple. Alternate ideas/suggestions and review of the
> design
> > are welcome!
> 
> t_infomask2 contains one last unused bit,
> 
> 
> Umm, WARM is using 2 unused bits from t_infomask2. You mean there is another
> free bit after that too?

We are obviously going to use several heap or item pointer bits for
WARM, and once we do that it is going to be hard to undo that.  Pavan,
are you saying you could do more with WARM if you had more bits?  Are we
sure we have given you all the bits we can?  Do we want to commit to a
lesser feature because the bits are not available?

> and we could reuse vacuum
> full's bits (HEAP_MOVED_OUT, HEAP_MOVED_IN), but that will need some
> thinking ahead.  Maybe now's the time to start versioning relations so
> that we can ensure clusters upgraded to pg10 do not contain any of those
> bits in any tuple headers.
> 
> 
> Yeah, IIRC old VACUUM FULL was removed in 9.0, which is good 6 year old.
> Obviously, there still a chance that a pre-9.0 binary upgraded cluster exists
> and upgrades to 10. So we still need to do something about them if we reuse
> these bits. I'm surprised to see that we don't have any mechanism in place to
> clear those bits. So may be we add something to do that.

Yeah, good question.  :-(  We have talked about adding some page,
table, or cluster-level version number so we could identify if a given
tuple _could_ be using those bits, but never did it.

> I'd some other ideas (and a patch too) to reuse bits from t_ctid.ip_pos given
> that offset numbers can be represented in just 13 bits, even with the maximum
> block size. Can look at that if it comes to finding more bits.

OK, so it seems more bits is not a blocker to enhancements, yet.

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

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


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

Re: [HACKERS] \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless)

[Fabien COELHO]

Hello Daniel,


Ah, I see why *that* wants to know about it ... I think.  I suppose you're
arguing that variable expansion shouldn't be able to insert, say, an \else
in a non-active branch?  Maybe, but if it can insert an \else in an active
branch, then why not non-active too?  Seems a bit inconsistent.


Are we sold on the idea that conditionals should be implemented
by meta-commands, rather than for example terminal symbols of
a new grammar on top of the existing?


I would say that this already exists server-side, and it is named 
PL/pgSQL:-)


I think that once psql has started with \xxx commands, then client-side 
extensions must stick with it till the end of time.


--
Fabien.


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

Re: [HACKERS] Documentation improvements for partitioning

[Joshua D. Drake]

On 02/23/2017 09:27 AM, Peter Geoghegan wrote:

On Thu, Feb 23, 2017 at 8:08 AM, Simon Riggs  wrote:



* "Good work so far, but there is still a very significant amount of
work to do."

There is always more work to do, so why say so? I think that the
implication is that this isn't complete as a feature that goes into
the next release, which I disagree with. There is nothing
disappointing to me about this feature, and, as I said, I am
unsurprised that it doesn't support certain things.




I don't think we need to start going down the avenue of "you could be 
nicer". We can all be nicer and we all have our good and bad days.


If we start worrying about egos to this degree, we will never get 
anything done.


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


--
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] Documentation improvements for partitioning

[Peter Geoghegan]

On Thu, Feb 23, 2017 at 8:08 AM, Simon Riggs  wrote:
> What claims are you talking about? Which things have been exaggerated,
> and by whom?

* The specious argument that we should "just" have CREATE INDEX create
equivalent indexes across partitions, to save all those people from
writing all those scripts.

The reality is that there are several ways that that would not comport
with the existing design of things. Most obviously: where does that
leave the design of global indexes that we eventually come up with?
Now, maybe there is a good way to make this easier for users, worth
doing sooner rather than later, but that will be subtle, and you
should at least acknowledge that.

* "It leaves me asking what else is missing"... "If we wanted them to
act identically we wouldn't have any need for a new feature at all, so
clearly that doesn't make sense as an argument."

These remarks sound imperious to me. I think that this could be quite
demoralizing to someone in Amit's position, and you ought to give some
consideration to that. I think that several of your remarks on the
patch are facile and/or needlessly ambiguous, which is what makes this
lack of tact seem unfair to me.

* "Good work so far, but there is still a very significant amount of
work to do."

There is always more work to do, so why say so? I think that the
implication is that this isn't complete as a feature that goes into
the next release, which I disagree with. There is nothing
disappointing to me about this feature, and, as I said, I am
unsurprised that it doesn't support certain things.

-- 
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] Other formats in pset like markdown, rst, mediawiki

[Alvaro Herrera]

Leknín Řepánek wrote:
> I was mainly using tables in rst, i think, that this format is very
> similar to "grid tables" in markdown. And it is really similar to default 
> borders used
> in psql with border 2.

Great.  So are you writing the code now?  :-)

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] dropping partitioned tables without CASCADE

[Simon Riggs]

On 23 February 2017 at 06:40, Ashutosh Bapat
 wrote:

> I think this is ready for committer.

Thanks for writing and reviewing this. I'll be happy to review and
commit. Please add to CF.

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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