Re: [HACKERS] Documentation improvements for partitioning

2017-02-23 Thread 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

Re: [HACKERS] bytea_output output of base64

2017-02-23 Thread 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,

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

2017-02-23 Thread 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

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

2017-02-23 Thread 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

Re: [HACKERS] btree_gin and btree_gist for enums

2017-02-23 Thread 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

Re: [HACKERS] GUC for cleanup indexes threshold.

2017-02-23 Thread 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

Re: [HACKERS] Range Partitioning behaviour - query

2017-02-23 Thread 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. >> >>

Re: [HACKERS] btree_gin and btree_gist for enums

2017-02-23 Thread 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

Re: [HACKERS] Documentation improvements for partitioning

2017-02-23 Thread 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

Re: [HACKERS] Documentation improvements for partitioning

2017-02-23 Thread 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

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

2017-02-23 Thread 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

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

2017-02-23 Thread 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

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

2017-02-23 Thread 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

Re: [HACKERS] bytea_output output of base64

2017-02-23 Thread 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 >

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

2017-02-23 Thread 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

Re: [HACKERS] Range Partitioning behaviour - query

2017-02-23 Thread 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

Re: [HACKERS] bytea_output output of base64

2017-02-23 Thread 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: >

Re: [HACKERS] dropping partitioned tables without CASCADE

2017-02-23 Thread 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

Re: [HACKERS] Logical Replication and Character encoding

2017-02-23 Thread 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 ;

Re: [HACKERS] A typo in mcxt.c

2017-02-23 Thread 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

Re: [HACKERS] Add checklist item for psql completion to commitfest review

2017-02-23 Thread 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

Re: [HACKERS] Range Partitioning behaviour - query

2017-02-23 Thread 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

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

2017-02-23 Thread 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

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

2017-02-23 Thread 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

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

2017-02-23 Thread 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

Re: [HACKERS] Measuring replay lag

2017-02-23 Thread 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

Re: [HACKERS] btree_gin and btree_gist for enums

2017-02-23 Thread 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,

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

2017-02-23 Thread 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

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

2017-02-23 Thread 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

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

2017-02-23 Thread 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.

Re: [HACKERS] btree_gin and btree_gist for enums

2017-02-23 Thread 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

[HACKERS] Poor memory context performance in large hash joins

2017-02-23 Thread 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

Re: [HACKERS] Documentation improvements for partitioning

2017-02-23 Thread 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

[HACKERS] bytea_output output of base64

2017-02-23 Thread 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:

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

2017-02-23 Thread 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)

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

2017-02-23 Thread 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

Re: [HACKERS] bytea_output output of base64

2017-02-23 Thread 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

Re: [HACKERS] Checksums by default?

2017-02-23 Thread 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

Re: [HACKERS] Index corruption with CREATE INDEX CONCURRENTLY

2017-02-23 Thread 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

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

2017-02-23 Thread 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

Re: [HACKERS] PinBuffer() no longer makes use of strategy

2017-02-23 Thread 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

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

2017-02-23 Thread 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

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

2017-02-23 Thread 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

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

2017-02-23 Thread 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

Re: [HACKERS] A typo in mcxt.c

2017-02-23 Thread 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

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

2017-02-23 Thread 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

[HACKERS] utility commands benefiting from parallel plan

2017-02-23 Thread 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

Re: [HACKERS] case_preservation_and_insensitivity = on

2017-02-23 Thread 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

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

2017-02-23 Thread 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

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

2017-02-23 Thread 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.

Re: [HACKERS] Should we cacheline align PGXACT?

2017-02-23 Thread 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

[HACKERS] timeouts in PostgresNode::psql

2017-02-23 Thread 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

Re: [HACKERS] Documentation improvements for partitioning

2017-02-23 Thread 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

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

2017-02-23 Thread 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

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

2017-02-23 Thread 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

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

2017-02-23 Thread 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

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

2017-02-23 Thread 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,

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

2017-02-23 Thread 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 >

Re: [HACKERS] increasing the default WAL segment size

2017-02-23 Thread 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

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

2017-02-23 Thread 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.

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

2017-02-23 Thread 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

Re: [HACKERS] Should we cacheline align PGXACT?

2017-02-23 Thread 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

Re: [HACKERS] Range Partitioning behaviour - query

2017-02-23 Thread 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,

Re: [HACKERS] Should we cacheline align PGXACT?

2017-02-23 Thread 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

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

2017-02-23 Thread 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

Re: [HACKERS] Range Partitioning behaviour - query

2017-02-23 Thread 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: >> >>

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

2017-02-23 Thread 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

Re: [HACKERS] UPDATE of partition key

2017-02-23 Thread 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 >

Re: [HACKERS] A typo in mcxt.c

2017-02-23 Thread 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

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

2017-02-23 Thread 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

Re: [HACKERS] Documentation improvements for partitioning

2017-02-23 Thread 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. > >

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

2017-02-23 Thread 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

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

2017-02-23 Thread 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

Re: [HACKERS] UPDATE of partition key

2017-02-23 Thread 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

Re: [HACKERS] btree_gin and btree_gist for enums

2017-02-23 Thread 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

Re: [HACKERS] bytea_output output of base64

2017-02-23 Thread 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? > >

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

2017-02-23 Thread 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

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

2017-02-23 Thread 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

Re: [HACKERS] bytea_output output of base64

2017-02-23 Thread 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

Re: [HACKERS] Range Partitioning behaviour - query

2017-02-23 Thread 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) - > >

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

2017-02-23 Thread 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

Re: [HACKERS] bytea_output output of base64

2017-02-23 Thread 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

Re: [HACKERS] asynchronous execution

2017-02-23 Thread Kyotaro HORIGUCHI
Hello, I totally reorganized the patch set to four pathces on the current master (9e43e87). At Wed, 22 Feb 2017 17:39:45 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20170222.173945.262776579.horiguchi.kyot...@lab.ntt.co.jp> > Finally, I couldn't

Re: [HACKERS] Declarative partitioning - another take

2017-02-23 Thread Yugo Nagata
Hi Amit, On Thu, 23 Feb 2017 16:29:32 +0900 Amit Langote wrote: > Thanks for taking care of that. > > + * PartitionRoot relation descriptor for parent > relation > > Maybe: relation descriptor for root parent relation This seems

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

2017-02-23 Thread Michael Paquier
On Thu, Feb 23, 2017 at 7:29 PM, Jan Michálek wrote: > Is there possibility, that support for some others formats will be added in > the future? Or is it possible to create some of this formats by manipulating > with pset? Addition of new formats is definitely possible,

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

2017-02-23 Thread Tom Lane
Greg Stark writes: > On 22 February 2017 at 15:08, Tom Lane wrote: >> Indeed. When I wrote the comment you're referring to, quite a few years >> ago now, I thought that popular demand might force us to allow omitted >> aliases. But the demand never

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

2017-02-23 Thread Tom Lane
Bernd Helmle writes: > Right, i'm going to give it a try then. Currently i see these options: > * Validate any generated alias against a list of explicit alias names. > This means we have to collect explicit alias names in, say a hashtable, > and validate a generated name

Re: [HACKERS] case_preservation_and_insensitivity = on

2017-02-23 Thread Tom Lane
Robert Haas writes: > On Mon, Feb 20, 2017 at 3:06 PM, Joel Jacobson wrote: >> On Mon, Feb 20, 2017 at 1:45 AM, Tom Lane wrote: >>> The versions of autocommit that have actually stood the test of time were >>> implemented on the

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

2017-02-23 Thread Greg Stark
On 22 February 2017 at 15:08, Tom Lane wrote: > Indeed. When I wrote the comment you're referring to, quite a few years > ago now, I thought that popular demand might force us to allow omitted > aliases. But the demand never materialized. At this point it seems > clear to

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

2017-02-23 Thread Stephen Frost
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

[HACKERS] user mapping messages

2017-02-23 Thread Andrew Dunstan
While reviewing the IF NOT EXISTS patch for CREATE USER MAPPING I noticed that in several places we treat the user name as the name of the user mapping. Strictly ISTM that user mappings are really anonymous objects, so instead of something like user "mapping \"%s\" does not exist for the server"

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

2017-02-23 Thread Greg Stark
On 23 February 2017 at 13:27, Greg Stark wrote: > => SELECT "?column"? FROM (select 1+1 as "?column?", 1+1) AS x; Oops, I missed the typo there: => SELECT "?column?" FROM (select 1+1 AS "?column?", 1+1) AS x; ERROR: 42702: column reference "?column?" is ambiguous LINE 2: SELECT

Re: [HACKERS] UPDATE of partition key

2017-02-23 Thread Amit Langote
Hi Amit, Thanks for working on this. On 2017/02/13 21:01, Amit Khandekar wrote: > Currently, an update of a partition key of a partition is not allowed, > since it requires to move the row(s) into the applicable partition. > > Attached is a WIP patch (update-partition-key.patch) that removes

[HACKERS] proposal - psql: possibility to specify sort for describe commands, when size is printed

2017-02-23 Thread Pavel Stehule
Hi Currently is not possible to control sort columns for \d* commands. Usually schema and table name is used. Really often task is collect the most big objects in database. "\dt+, \di+" shows necessary information, but not in practical order. Instead introduction some additional flags to

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

2017-02-23 Thread Jan Michálek
Have a nice day I`m using \pset format html for generating tables for some documentation, training, reports, blogs... Mainly sample of data for SQL queries, or explaining structure etc. But in some software like sphinx, wiki on redmine, or markdown on github issues I`m not able to insert table in

Re: [HACKERS] GUC for cleanup indexes threshold.

2017-02-23 Thread Masahiko Sawada
On Wed, Feb 22, 2017 at 12:01 AM, Amit Kapila wrote: > On Tue, Feb 21, 2017 at 1:09 AM, Simon Riggs wrote: >> On 20 February 2017 at 10:27, Amit Kapila wrote: >>> On Mon, Feb 20, 2017 at 3:01 PM, Simon Riggs

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

2017-02-23 Thread Bernd Helmle
Am Mittwoch, den 22.02.2017, 22:17 -0500 schrieb Tom Lane: > [ shrug... ]  Well, I won't resist this hard as long as it's done > competently, which to me means "the subquery name doesn't conflict > with > anything else".  Not "it doesn't conflict unless you're unlucky > enough > to have used the

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

2017-02-23 Thread Pantelis Theodosiou
On Thu, Feb 23, 2017 at 3:17 AM, Tom Lane wrote: > Robert Haas writes: > > On Wed, Feb 22, 2017 at 10:33 PM, Nico Williams > wrote: > >> I suspect most users, like me, just roll their eyes, grumble, and put up > >> with it

[HACKERS] A typo in mcxt.c

2017-02-23 Thread Kyotaro HORIGUCHI
Hello, while I investigated a bug of async-exec patch. I found a (maybe) typo in mcxt.c. | * It's not entirely clear whether 'tis better to do this before or after | * delinking the context; but an error in a callback will likely result in I'm not so confident, but the "'tis" seems to me to be

Re: [HACKERS] Documentation improvements for partitioning

2017-02-23 Thread Simon Riggs
On 22 February 2017 at 19:57, Peter Geoghegan wrote: > FWIW, I agree that some of what has been claimed about what > contributors failed to do with this patch is exaggerated, and not in a > way that I'd understand as hyperbole that drives home a deeper point. What claims are you

  1   2   >