Re: [HACKERS] GRANT EXECUTE ON FUNCTION foo() TO bar();

2017-02-22 Thread Pavel Stehule
2017-02-22 9:20 GMT+01:00 Joel Jacobson : > On Wed, Feb 22, 2017 at 9:07 AM, Pavel Stehule > wrote: > > Usage of X functions can be locked in schema. > > I think that's also a good idea. Both are useful I think. They solve > two different use-cases. > >

Re: [HACKERS] GRANT EXECUTE ON FUNCTION foo() TO bar();

2017-02-22 Thread Pavel Stehule
2017-02-22 8:06 GMT+01:00 Joel Jacobson : > Hi Hackers, > > Currently, it's only possible to grant/revoke execute on functions to > roles. > > I think it would be useful in many situations, both for documentation > purposes, > but also for increased security, to in a precise way

Re: [HACKERS] Partitioned tables and relfilenode

2017-02-22 Thread Amit Langote
On 2017/02/21 22:21, Ashutosh Bapat wrote: > Some comments about 0003 patch. > > @@ -996,10 +996,20 @@ inheritance_planner(PlannerInfo *root) > Index rti; > + RangeTblEntry *parent_rte; > There's already another variable declared in that function within a loop > foreach(lc,

Re: [HACKERS] GRANT EXECUTE ON FUNCTION foo() TO bar();

2017-02-22 Thread Joel Jacobson
On Wed, Feb 22, 2017 at 9:07 AM, Pavel Stehule wrote: > Usage of X functions can be locked in schema. I think that's also a good idea. Both are useful I think. They solve two different use-cases. If there are multiple callers of a private function within a schema, it

Re: [HACKERS] Logical Replication WIP

2017-02-22 Thread Petr Jelinek
Hi, I updated these patches for current HEAD and removed the string initialization in walsender as Fuji Masao committed similar fix in meantime. I also found typo/thinko in the first patch which is now fixed. -- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL

Re: [HACKERS] snapbuild woes

2017-02-22 Thread Erik Rijkers
On 2017-02-22 03:05, Petr Jelinek wrote: So to summarize attached patches: 0001 - Fixes performance issue where we build tons of snapshots that we don't need which kills CPU. 0002 - Disables the use of ondisk historical snapshots for initial consistent snapshot export as it may result in

Re: [HACKERS] Push down more UPDATEs/DELETEs in postgres_fdw

2017-02-22 Thread Rushabh Lathia
On Wed, Feb 22, 2017 at 12:15 PM, Etsuro Fujita wrote: > On 2017/02/21 19:31, Rushabh Lathia wrote: > >> On Mon, Feb 20, 2017 at 1:41 PM, Etsuro Fujita >> > wrote: >> > > On 2017/02/13 18:24,

Re: [HACKERS] mat views stats

2017-02-22 Thread Jim Mlodgenski
On Wed, Feb 22, 2017 at 12:43 AM, Jim Nasby wrote: > On 2/21/17 4:22 PM, Peter Eisentraut wrote: > >> Attached is a patch to trigger autovacuum based on a matview refresh >>> along with a system view pg_stat_all_matviews to show information more >>> meaningful for

Re: [HACKERS] [patch] reorder tablespaces in basebackup tar stream for backup_label

2017-02-22 Thread Bernd Helmle
Am Dienstag, den 21.02.2017, 11:17 +0100 schrieb Michael Banck: > However, third party tools using the BASE_BACKUP command might want > to > extract the backup_label, e.g. in order to figure out the START WAL > LOCATION. If they make a big tarball for the whole cluster > potentially > including

Re: [HACKERS] dropping partitioned tables without CASCADE

2017-02-22 Thread Ashutosh Bapat
On Wed, Feb 22, 2017 at 11:11 AM, Amit Langote wrote: > On 2017/02/22 13:46, Ashutosh Bapat wrote: >> Looks good to me. In the attached patch I have added a comment >> explaining the reason to make partition tables "Auto" dependent upon >> the corresponding

Re: [HACKERS] Partitioned tables and relfilenode

2017-02-22 Thread Ashutosh Bapat
> >> I am wondering whether we should deal with inh flat reset in a >> slightly different way. Let expand_inherited_rtentry() mark inh = >> false for the partitioned tables without any partitions and deal with >> those at the time of estimating size by marking those as dummy. That >> might be

Re: [HACKERS] Logical replication existing data copy

2017-02-22 Thread Erik Rijkers
On 2017-02-22 13:03, Petr Jelinek wrote: 0001-Skip-unnecessary-snapshot-builds.patch 0002-Don-t-use-on-disk-snapshots-for-snapshot-export-in-l.patch 0003-Fix-xl_running_xacts-usage-in-snapshot-builder.patch 0001-Use-asynchronous-connect-API-in-libpqwalreceiver.patch

[HACKERS] Cleanup: avoid direct use of ip_posid/ip_blkid

2017-02-22 Thread Pavan Deolasee
Hello All, I would like to propose the attached patch which removes all direct references to ip_posid and ip_blkid members of ItemPointerData struct and instead use ItemPointerGetOffsetNumber and ItemPointerGetBlockNumber macros respectively to access these members. My motivation to do this is

Re: [HACKERS] pg_monitor role

2017-02-22 Thread Dave Page
Hi On Tue, Feb 21, 2017 at 5:40 PM, Masahiko Sawada wrote: > On Mon, Feb 20, 2017 at 8:48 PM, Dave Page wrote: >> Further to the patch I just submitted >>

Re: [HACKERS] Change in "policy" on dump ordering?

2017-02-22 Thread Peter Eisentraut
On 2/22/17 00:55, Jim Nasby wrote: > Originally, no, but reviewing the list again I'm kindof wondering about > DO_DEFAULT_ACL, especially since the acl code in pg_dump looks at > defaults as part of what removes the need to explicitly dump > permissions. I'm also wondering if DO_POLICY could

Re: [HACKERS] Hash support for grouping sets

2017-02-22 Thread Thom Brown
On 6 January 2017 at 03:48, Andrew Gierth wrote: > Herewith a patch for doing grouping sets via hashing or mixed hashing > and sorting. > > The principal objective is to pick whatever combination of grouping sets > has an estimated size that fits in work_mem, and

Re: [HACKERS] Logical Replication WIP

2017-02-22 Thread Petr Jelinek
On 22/02/17 12:24, Petr Jelinek wrote: > Hi, > > I updated these patches for current HEAD and removed the string > initialization in walsender as Fuji Masao committed similar fix in meantime. > > I also found typo/thinko in the first patch which is now fixed. > And of course I missed the

Re: [HACKERS] snapbuild woes

2017-02-22 Thread Petr Jelinek
On 22/02/17 11:29, Erik Rijkers wrote: > On 2017-02-22 03:05, Petr Jelinek wrote: >> >> So to summarize attached patches: >> 0001 - Fixes performance issue where we build tons of snapshots that we >> don't need which kills CPU. >> >> 0002 - Disables the use of ondisk historical snapshots for

Re: [HACKERS] GRANT EXECUTE ON FUNCTION foo() TO bar();

2017-02-22 Thread Tom Lane
Joel Jacobson writes: > Currently, it's only possible to grant/revoke execute on functions to roles. > I think it would be useful in many situations, both for documentation > purposes, > but also for increased security, to in a precise way control what > other function(s) are

Re: [HACKERS] GRANT EXECUTE ON FUNCTION foo() TO bar();

2017-02-22 Thread Joel Jacobson
On Wed, Feb 22, 2017 at 2:18 PM, Tom Lane wrote: > I think this is really *not* a good idea. The entire permissions model > is built around granting permissions to roles, by other roles. My bad. I shouldn't have proposed the idea on how to achieve/implement the idea. I

Re: [HACKERS] Replication vs. float timestamps is a disaster

2017-02-22 Thread Tom Lane
Andres Freund writes: > On 2017-02-22 00:10:35 -0600, Jim Nasby wrote: >> I wounder if a separate "floatstamp" data type might fit the bill there. It >> might not be completely seamless, but it would be binary compatible. > I don't really see what'd that solve. Seems to me

Re: [HACKERS] Replication vs. float timestamps is a disaster

2017-02-22 Thread Stephen Frost
Tom, all, * Tom Lane (t...@sss.pgh.pa.us) wrote: > While I'm generally not one to vote for dropping backwards-compatibility > features, I have to say that I find #4 the most attractive of these > options. It would result in getting rid of boatloads of under-tested > code, whereas #2 would really

Re: [HACKERS] mat views stats

2017-02-22 Thread Peter Eisentraut
On 2/22/17 06:31, Jim Mlodgenski wrote: > Matviews already show up in the pg_stat_*_tables and the patch does > leverage the existing pg_stat_*_tables underlying structure, but it > creates more meaningful pg_stat_*_matviews leaving out things like > insert and update counts. But fields like

Re: [HACKERS] Replication vs. float timestamps is a disaster

2017-02-22 Thread Andres Freund
On 2017-02-22 08:43:28 -0500, Tom Lane wrote: > Andres Freund writes: > > On 2017-02-22 00:10:35 -0600, Jim Nasby wrote: > >> I wounder if a separate "floatstamp" data type might fit the bill there. It > >> might not be completely seamless, but it would be binary compatible. >

Re: [HACKERS] pg_monitor role

2017-02-22 Thread Magnus Hagander
On Wed, Feb 22, 2017 at 1:47 PM, Dave Page wrote: > > On Tue, Feb 21, 2017 at 5:40 PM, Masahiko Sawada > wrote: > > On Mon, Feb 20, 2017 at 8:48 PM, Dave Page wrote: > >> Further to the patch I just submitted > >>

[HACKERS] remove deprecated COMMENT ON RULE syntax

2017-02-22 Thread Peter Eisentraut
There is support for COMMENT ON RULE without specifying a table name, for upgrading from pre-7.3 instances. I think it might be time for that workaround to go. Patch attached. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA,

Re: [HACKERS] "may be unused" warnings for gcc

2017-02-22 Thread Peter Eisentraut
On 2/21/17 22:17, Andres Freund wrote: > I've not run comparisons this year, but late last year I was seeing > 5% > < 10% benefits - that seems plenty enough to care. You mean the 5-minute benchmarks on my laptop are not representative? ;-) Here is a patch that I had lying around that clears the

[HACKERS] Make subquery alias optional in FROM clause

2017-02-22 Thread Bernd Helmle
>From time to time, especially during migration projects from Oracle to PostgreSQL, i'm faced with people questioning why the alias in the FROM clause for subqueries in PostgreSQL is mandatory. The default answer here is, the SQL standard requires it. This also is exactly the comment in our

Re: [HACKERS] Replication vs. float timestamps is a disaster

2017-02-22 Thread Andres Freund
On 2017-02-22 09:06:38 -0600, Jim Nasby wrote: > On 2/22/17 7:56 AM, Andres Freund wrote: > > It sounded more like Jim suggested a full blown SQL type, given that he > > replied to my concern about the possible need for a deprecation period > > due to pg_upgrade concerns. To be useful for that,

Re: [HACKERS] mat views stats

2017-02-22 Thread Jim Nasby
On 2/22/17 7:56 AM, Peter Eisentraut wrote: What behavior would we like by default? Refreshing a materialized view is a pretty expensive operation, so I think scheduling an analyze quite aggressively right afterwards is often what you want. I think sending a stats message with the number of

Re: [HACKERS] Replication vs. float timestamps is a disaster

2017-02-22 Thread Jim Nasby
On 2/22/17 9:12 AM, Andres Freund wrote: That would allow an in-place upgrade of a really large cluster. A user would still need to modify their code to use the new type. Put another way: add ability for pg_upgrade to change the type of a field. There might be other uses for that as well. Type

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

2017-02-22 Thread Corey Huinker
> > but if you think that it should be somewhere else, please advise Corey > about where to put it. Just a reminder that I'm standing by for advice. The issue at hand is whether the if-state should be a part of the PsqlScanState, or if it should be a separate state variable owned by MainLoop()

Re: [HACKERS] Replication vs. float timestamps is a disaster

2017-02-22 Thread Andrew Dunstan
On 02/22/2017 10:21 AM, Jim Nasby wrote: > On 2/22/17 9:12 AM, Andres Freund wrote: >>> That would allow an in-place upgrade of >>> a really large cluster. A user would still need to modify their code >>> to use >>> the new type. >>> >>> Put another way: add ability for pg_upgrade to change the

Re: [HACKERS] Replication vs. float timestamps is a disaster

2017-02-22 Thread Tom Lane
Andrew Dunstan writes: > On 02/22/2017 10:21 AM, Jim Nasby wrote: >> Only in the catalog though, not the datums, right? I would think you >> could just change the oid in the catalog the same as you would for a >> table column. > No, in the datums. Yeah, I don't

Re: [HACKERS] pg_monitor role

2017-02-22 Thread Stephen Frost
Dave, all, * Stephen Frost (sfr...@snowman.net) wrote: > * Dave Page (dp...@pgadmin.org) wrote: > > What modules should be included? > > On a quick review of all of the modules, excluding those that are just > testing or examples or which can already be used by non-superusers by > default, and

[HACKERS] Note about bug #14563

2017-02-22 Thread Tom Lane
I looked into the report in bug #14563 about pg_trgm giving wrong answers for regexp searches. There is a whole lot of complex mechanism in trgm_regexp.c for extracting trigram patterns out of regexps, and so I was afraid that it might be a very subtle bug, but actually the problem seems to be in

Re: [HACKERS] Proposal : For Auto-Prewarm.

2017-02-22 Thread Mithun Cy
Hi all thanks, I have tried to fix all of the comments given above with some more code cleanups. On Wed, Feb 22, 2017 at 6:28 AM, Robert Haas wrote: > I think it's OK to treat that as something of a corner case. There's > nothing to keep you from doing that today: just

Re: [HACKERS] [PROPOSAL] Temporal query processing with range types

2017-02-22 Thread Peter Eisentraut
On 2/16/17 07:41, Robert Haas wrote: > Also, it sounds like all of this is intended to work with ranges that > are stored in different columns rather than with PostgreSQL's built-in > range types. Yeah, that should certainly be changed. -- Peter Eisentraut

Re: [HACKERS] Documentation improvements for partitioning

2017-02-22 Thread Peter Geoghegan
On Tue, Feb 21, 2017 at 6:14 PM, Robert Haas wrote: > On Tue, Feb 21, 2017 at 2:03 AM, Bruce Momjian wrote: >> I have to admit my reaction was similar to Simon's, meaning that the >> lack of docs is a problem, and that the limitations are kind of a >>

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

2017-02-22 Thread Tom Lane
Corey Huinker writes: >> but if you think that it should be somewhere else, please advise Corey >> about where to put it. > Just a reminder that I'm standing by for advice. Sorry, I'd lost track of this thread. > The issue at hand is whether the if-state should be a

Re: [HACKERS] Should we cacheline align PGXACT?

2017-02-22 Thread Alvaro Herrera
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 -- Sent via

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

2017-02-22 Thread Corey Huinker
On Wed, Feb 22, 2017 at 4:00 PM, Tom Lane wrote: > Corey Huinker writes: > >> but if you think that it should be somewhere else, please advise Corey > >> about where to put it. > > > Just a reminder that I'm standing by for advice. > > Sorry, I'd

Re: [HACKERS] [PATCH] Rename pg_switch_xlog to pg_switch_wal

2017-02-22 Thread Bruce Momjian
On Fri, Feb 3, 2017 at 05:21:28AM -0500, Stephen Frost wrote: > > Also googling for pg_wal, I'm finding food for thought like this > > IBM technote: > > http://www-01.ibm.com/support/docview.wss?uid=isg3T1015637 > > which recommends to > > "Remove all files under /var/lib/pgsql/9.0/data/pg_wal/"

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

2017-02-22 Thread Tom Lane
Corey Huinker writes: > On Wed, Feb 22, 2017 at 4:00 PM, Tom Lane wrote: >> One thing I'm wondering is why the "active_branch" bool is in "pset" >> and not in the conditional stack. That seems, at best, pretty grotty. >> _psqlSettings is meant for

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

2017-02-22 Thread Tom Lane
Corey Huinker writes: > After some research, GetVariable is called by psql_get_variable, which is > one of the callback functions passed to psql_scan_create(). So passing a > state variable around probably isn't going to work and PsqlFileState now > looks like the best

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

2017-02-22 Thread Corey Huinker
> > > Dunno, that sounds a lot like an "if the only tool I have is a hammer, > then this must be a nail" argument. More of a "don't rock the boat more than absolutely necessary", but knowing that adding another global struct might be welcomed is good to know. > reasonable interpretation of

Re: [HACKERS] Replication vs. float timestamps is a disaster

2017-02-22 Thread Tom Lane
Andres Freund writes: > On 2017-02-22 08:43:28 -0500, Tom Lane wrote: >> (To be concrete, I'm suggesting dropping --disable-integer-datetimes >> in HEAD, and just agreeing that in the back branches, use of replication >> protocol with float-timestamp servers is not supported

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

2017-02-22 Thread Corey Huinker
On Wed, Feb 22, 2017 at 5:11 PM, Corey Huinker wrote: > Dunno, that sounds a lot like an "if the only tool I have is a hammer, >> then this must be a nail" argument. > > > More of a "don't rock the boat more than absolutely necessary", but > knowing that adding another

Re: [HACKERS] pg_monitor role

2017-02-22 Thread Stephen Frost
Dave, * Dave Page (dp...@pgadmin.org) wrote: > On Wed, Feb 22, 2017 at 4:52 PM, Stephen Frost wrote: > >> > > And what about the diagnostic tools such as pageinspect and > >> > > pgstattuple? > >> > > >> > I think external/contrib modules should not be included. To install >

Re: [HACKERS] Change in "policy" on dump ordering?

2017-02-22 Thread Peter Eisentraut
On 2/22/17 10:14, Jim Nasby wrote: > CREATE MATERIALIZED VIEW tmv AS SELECT * FROM pg_subscription; > SELECT 0 > > IOW, you can create matviews that depend on any other > table/view/matview, but right now if the matview includes certain items > it will mysteriously end up empty post-restore.

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

2017-02-22 Thread Corey Huinker
On Wed, Feb 22, 2017 at 5:59 PM, Tom Lane wrote: > 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

Re: [HACKERS] Change in "policy" on dump ordering?

2017-02-22 Thread Michael Banck
Hi, On Wed, Feb 22, 2017 at 05:24:49PM -0600, Jim Nasby wrote: > On 2/22/17 12:29 PM, Peter Eisentraut wrote: > >On 2/22/17 10:14, Jim Nasby wrote: > >>CREATE MATERIALIZED VIEW tmv AS SELECT * FROM pg_subscription; > >>SELECT 0 > >> > >>IOW, you can create matviews that depend on any other >

Re: [HACKERS] Packages: Again

2017-02-22 Thread Bruce Momjian
On Fri, Feb 3, 2017 at 07:59:26AM +0100, Pavel Stehule wrote: > It should be documented and presented (who is read a documentation? :-)) > > It is not only PostgreSQL issue, same issue has to have any other databases. > The Oracle architecture is very specific and often question is, how to map >

Re: [HACKERS] Change in "policy" on dump ordering?

2017-02-22 Thread Jim Nasby
On 2/22/17 5:38 PM, Michael Banck wrote: diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c index ea643397ba..708a47f3cb 100644 --- a/src/bin/pg_dump/pg_dump_sort.c +++ b/src/bin/pg_dump/pg_dump_sort.c @@ -26,6 +26,9 @@ static const char *modulename =

[HACKERS] objsubid vs subobjid

2017-02-22 Thread Jim Nasby
pg_get_object_address() currently returns a field called subobjid, while pg_depend calls that objsubid. I'm guessing that wasn't on purpose (especially because internally the function uses objsubid), and it'd be nice to fix it. Attached does that, as well as updating the input naming on the

Re: [HACKERS] Change in "policy" on dump ordering?

2017-02-22 Thread Stephen Frost
Jim, * Jim Nasby (jim.na...@bluetreble.com) wrote: > On 2/22/17 12:29 PM, Peter Eisentraut wrote: > >On 2/22/17 10:14, Jim Nasby wrote: > >>CREATE MATERIALIZED VIEW tmv AS SELECT * FROM pg_subscription; > >>SELECT 0 > >> > >>IOW, you can create matviews that depend on any other >

Re: [HACKERS] pg_dump does not refresh matviews from extensions

2017-02-22 Thread Jim Nasby
On 2/21/17 2:05 PM, Stephen Frost wrote: As for $SUBJECT, I feel like it really depends, doesn't it? If the extension creates the matview w/ no data in it, and doesn't mark it as a config table, should we really refresh it? On the other hand, if the extension creates the matview and either

Re: [HACKERS] WARM and indirect indexes

2017-02-22 Thread Bruce Momjian
On Wed, Jan 11, 2017 at 04:38:10PM -0500, Robert Haas wrote: > More broadly, I don't share Bruce's negativity about indirect indexes. > My estimate of what needs to be done for them to be really useful is - > I think - higher than your estimate of what needs to be done, but I > think the concept

Re: [HACKERS] Change in "policy" on dump ordering?

2017-02-22 Thread Jim Nasby
On 2/22/17 12:29 PM, Peter Eisentraut wrote: On 2/22/17 10:14, Jim Nasby wrote: CREATE MATERIALIZED VIEW tmv AS SELECT * FROM pg_subscription; SELECT 0 IOW, you can create matviews that depend on any other table/view/matview, but right now if the matview includes certain items it will

Re: [HACKERS] Replication vs. float timestamps is a disaster

2017-02-22 Thread Tom Lane
Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> While I'm generally not one to vote for dropping backwards-compatibility >> features, I have to say that I find #4 the most attractive of these >> options. It would result in getting rid of boatloads of

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

2017-02-22 Thread Tom Lane
Bernd Helmle writes: >> From time to time, especially during migration projects from Oracle to > PostgreSQL, i'm faced with people questioning why the alias in the FROM > clause for subqueries in PostgreSQL is mandatory. The default answer > here is, the SQL standard

Re: [HACKERS] Replication vs. float timestamps is a disaster

2017-02-22 Thread Jim Nasby
On 2/22/17 7:56 AM, Andres Freund wrote: On 2017-02-22 08:43:28 -0500, Tom Lane wrote: Andres Freund writes: On 2017-02-22 00:10:35 -0600, Jim Nasby wrote: I wounder if a separate "floatstamp" data type might fit the bill there. It might not be completely seamless, but it

Re: [HACKERS] Change in "policy" on dump ordering?

2017-02-22 Thread Jim Nasby
On 2/22/17 8:00 AM, Peter Eisentraut wrote: Actually, I think matviews really need to be the absolute last thing. What if you had a matview that referenced publications or subscriptions? I'm guessing that would be broken right now. I'm not sure what you have in mind here. Publications and

Re: [HACKERS] GRANT EXECUTE ON FUNCTION foo() TO bar();

2017-02-22 Thread Jim Nasby
On 2/22/17 2:51 AM, Pavel Stehule wrote: The solution based on rights is elegant, but in this moment I cannot to see all possible impacts on performance - because it means new check for any call of any function. Maybe checking call stack can be good enough - I have not idea how often use case it

Re: [HACKERS] make async slave to wait for lsn to be replayed

2017-02-22 Thread Thom Brown
On 23 January 2017 at 11:56, Ivan Kartyshov wrote: > Thank you for reviews and suggested improvements. > I rewrote patch to make it more stable. > > Changes > === > I've made a few changes: > 1) WAITLSN now doesn`t depend on snapshot > 2) Check current replayed LSN

Re: [HACKERS] [PATCH] Suppress Clang 3.9 warnings

2017-02-22 Thread Fabien COELHO
Hello Aleksander, ``` xloginsert.c:742:18: warning: implicit conversion from 'int' to 'char' changes value from 253 to -3 [-Wconstant-conversion] ``` There is a bunch of these in "xlog.c" as well, and the same code is used in "pg_resetwal.c". Patch that fixes these warnings is attached

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

2017-02-22 Thread David G. Johnston
On Wed, Feb 22, 2017 at 8:08 AM, Tom Lane wrote: > Bernd Helmle writes: > >> From time to time, especially during migration projects from Oracle to > > PostgreSQL, i'm faced with people questioning why the alias in the FROM > > clause for subqueries in

Re: [HACKERS] remove deprecated COMMENT ON RULE syntax

2017-02-22 Thread Tom Lane
Peter Eisentraut writes: > There is support for COMMENT ON RULE without specifying a table > name, for upgrading from pre-7.3 instances. I think it might be time > for that workaround to go. No objection here. regards, tom lane --

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

2017-02-22 Thread David G. Johnston
On Wed, Feb 22, 2017 at 8:08 AM, Tom Lane wrote: > Or else not generate > a name at all, in which case there simply wouldn't be a way to refer to > the subquery by name; I'm not sure what that might break though. > ​Yeah, usually when I want this I don't end up needing refer

Re: [HACKERS] PATCH: Batch/pipelining support for libpq

2017-02-22 Thread Craig Ringer
On 22 Feb. 2017 14:14, "Vaishnavi Prabakaran" wrote: Thanks for reviewing the patch. > Thanks for picking it up! I've wanted to see this process for some time, but just haven't had the bandwidth for it.

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

2017-02-22 Thread Robert Haas
On Wed, Feb 22, 2017 at 10:33 PM, Nico Williams wrote: > On Wed, Feb 22, 2017 at 10:08:38AM -0500, Tom Lane wrote: >> Bernd Helmle writes: >> >> From time to time, especially during migration projects from Oracle to >> > PostgreSQL, i'm faced with

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

2017-02-22 Thread Andres Freund
On 2017-02-23 08:21:41 +0530, Robert Haas wrote: > On Wed, Feb 22, 2017 at 10:33 PM, Nico Williams wrote: > > On Wed, Feb 22, 2017 at 10:08:38AM -0500, Tom Lane wrote: > >> Bernd Helmle writes: > >> >> From time to time, especially during migration

[HACKERS] Range Partitioning behaviour - query

2017-02-22 Thread Venkata B Nagothi
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

Re: [HACKERS] Range Partitioning behaviour - query

2017-02-22 Thread Amit Langote
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=#

Re: [HACKERS] remove deprecated COMMENT ON RULE syntax

2017-02-22 Thread Robert Haas
On Wed, Feb 22, 2017 at 8:05 PM, Tom Lane wrote: > Peter Eisentraut writes: >> There is support for COMMENT ON RULE without specifying a table >> name, for upgrading from pre-7.3 instances. I think it might be time >> for that workaround to

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

2017-02-22 Thread Tom Lane
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 rather than complain. It's a pain point, but tolerable enough >> that no one bothers

Re: [HACKERS] Partitioned tables and relfilenode

2017-02-22 Thread Amit Langote
Thanks for the review. On 2017/02/23 15:44, Ashutosh Bapat wrote: > On Thu, Feb 23, 2017 at 11:19 AM, Amit Langote wrote: >> Rewrote that comment block as: >> >> * >> * If the parent is a partitioned table, we already set the nominal >> * relation. >> */ >> > > I reworded

[HACKERS] ToDo: Schema Private Function

2017-02-22 Thread Pavel Stehule
Hi I propose a schema private functions as analogy to Oracle package functions. My target of this proposal is better isolation generally available top level callable functions from other auxiliary functions. A execution of functions can be little bit more robust due less dependency on

Re: [HACKERS] Partitioned tables and relfilenode

2017-02-22 Thread Ashutosh Bapat
On Thu, Feb 23, 2017 at 1:08 PM, Amit Langote wrote: > Thanks for the review. > > On 2017/02/23 15:44, Ashutosh Bapat wrote: >> On Thu, Feb 23, 2017 at 11:19 AM, Amit Langote wrote: >>> Rewrote that comment block as: >>> >>> * >>> * If the parent is a

[HACKERS] Index usage for elem-contained-by-const-range clauses

2017-02-22 Thread Pritam Baral
The topic has been previously discussed[0] on the -performance mailing list, about four years ago. In that thread, Tom suggested[0] the planner could be made to "expand "intcol <@ 'x,y'::int4range" into "intcol between x and y", using something similar to the index LIKE optimization (ie, the

Re: [HACKERS] Logical replication existing data copy

2017-02-22 Thread Erik Rijkers
On 2017-02-22 18:13, Erik Rijkers wrote: On 2017-02-22 14:48, Erik Rijkers wrote: On 2017-02-22 13:03, Petr Jelinek wrote: 0001-Skip-unnecessary-snapshot-builds.patch 0002-Don-t-use-on-disk-snapshots-for-snapshot-export-in-l.patch 0003-Fix-xl_running_xacts-usage-in-snapshot-builder.patch

Re: [HACKERS] Logical replication existing data copy

2017-02-22 Thread Petr Jelinek
On 23/02/17 01:02, Erik Rijkers wrote: > On 2017-02-22 18:13, Erik Rijkers wrote: >> On 2017-02-22 14:48, Erik Rijkers wrote: >>> On 2017-02-22 13:03, Petr Jelinek wrote: >>> 0001-Skip-unnecessary-snapshot-builds.patch 0002-Don-t-use-on-disk-snapshots-for-snapshot-export-in-l.patch

Re: [HACKERS] case_preservation_and_insensitivity = on

2017-02-22 Thread Jim Nasby
On 2/20/17 3:30 AM, Joel Jacobson wrote: Also, I think the --lowercase-uniqueness feature would be useful by itself even without the --case-preserving feature, since that might be a good way to enforce a good design of new databases, as a mix of "users" and "Users" is probably considered ugly by

Re: [HACKERS] case_preservation_and_insensitivity = on

2017-02-22 Thread Robert Haas
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 client side (in psql and JDBC, and I think ODBC as >>

Re: [HACKERS] Declarative partitioning - another take

2017-02-22 Thread Yugo Nagata
Hi, I found that a comment for PartitionRoot in ResultRelInfo is missing. Although this is trivial, since all other members have comments, I think it is needed. Attached is the patch to fix it. Regards, Yugo Nagata On Tue, 27 Dec 2016 17:59:05 +0900 Amit Langote

Re: [HACKERS] Speedup twophase transactions

2017-02-22 Thread Michael Paquier
On Thu, Feb 2, 2017 at 3:07 PM, Nikhil Sontakke wrote: >>> Yeah. Was thinking about this yesterday. How about adding entries in >>> TwoPhaseState itself (which become valid later)? Only if it does not >>> cause a lot of code churn. >> >> That's possible as well, yes. > >

Re: [HACKERS] DROP SUBSCRIPTION and ROLLBACK

2017-02-22 Thread Masahiko Sawada
On Wed, Feb 22, 2017 at 2:17 AM, Masahiko Sawada wrote: > On Wed, Feb 22, 2017 at 1:52 AM, Fujii Masao wrote: >> On Tue, Feb 21, 2017 at 7:52 PM, Masahiko Sawada >> wrote: >>> On Tue, Feb 21, 2017 at 3:42 AM, Fujii Masao

Re: [HACKERS] Declarative partitioning - another take

2017-02-22 Thread Amit Langote
Nagata-san, On 2017/02/23 16:17, Yugo Nagata wrote: > Hi, > > I found that a comment for PartitionRoot in ResultRelInfo is missing. > Although this is trivial, since all other members have comments, I > think it is needed. Attached is the patch to fix it. Thanks for taking care of that. + *

Re: [HACKERS] Supporting huge pages on Windows

2017-02-22 Thread Tsunakawa, Takayuki
From: Amit Kapila [mailto:amit.kapil...@gmail.com] > > Hmm, the large-page requires contiguous memory for each page, so this > error could occur on a long-running system where the memory is heavily > fragmented. For example, please see the following page and check the memory > with RAMMap program

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

2017-02-22 Thread Bernd Helmle
On Wed, 2017-02-22 at 10:08 -0500, 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 me that

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

2017-02-22 Thread Dilip Kumar
On Wed, Feb 22, 2017 at 10:25 AM, Rafia Sabih wrote: > Hello everybody, > > In the current version, queries in SQL or PL functions can not > leverage parallelism. To relax this restriction I prepared a patch, > the approach used in the patch is explained next, Some

Re: [HACKERS] pg_monitor role

2017-02-22 Thread Stephen Frost
All, * Magnus Hagander (mag...@hagander.net) wrote: > On Wed, Feb 22, 2017 at 1:47 PM, Dave Page wrote: > > On Tue, Feb 21, 2017 at 5:40 PM, Masahiko Sawada > > wrote: > > > On Mon, Feb 20, 2017 at 8:48 PM, Dave Page wrote: > > >>

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

2017-02-22 Thread Nico Williams
On Wed, Feb 22, 2017 at 10:08:38AM -0500, Tom Lane wrote: > Bernd Helmle writes: > >> From time to time, especially during migration projects from Oracle to > > PostgreSQL, i'm faced with people questioning why the alias in the FROM > > clause for subqueries in PostgreSQL is

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

2017-02-22 Thread Bernd Helmle
On Wed, 2017-02-22 at 08:13 -0700, David G. Johnston wrote: > I'll contribute to the popular demand aspect but given that the error > is > good and the fix is very simple its not exactly a strong desire. In one project i've recently seen, for some reasons, they need to maintain an application

Re: [HACKERS] pg_monitor role

2017-02-22 Thread Dave Page
Hi On Wed, Feb 22, 2017 at 4:52 PM, Stephen Frost wrote: >> > > What about granting to the role to read other statistic views such as >> > > pg_stat_replication and pg_stat_wal_receiver? Since these informations >> > > can only be seen by superuser the for example monitoring

Re: [HACKERS] Replication vs. float timestamps is a disaster

2017-02-22 Thread David Fetter
On Mon, Feb 20, 2017 at 11:58:12AM +0100, Petr Jelinek wrote: > On 20/02/17 08:03, Andres Freund wrote: > > On 2017-02-19 10:49:29 -0500, Tom Lane wrote: > >> Robert Haas writes: > >>> On Sun, Feb 19, 2017 at 3:31 AM, Tom Lane wrote: > Thoughts?

Re: [HACKERS] Measuring replay lag

2017-02-22 Thread Thomas Munro
On Tue, Feb 21, 2017 at 6:21 PM, Simon Riggs wrote: > 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

Re: [HACKERS] dropping partitioned tables without CASCADE

2017-02-22 Thread Amit Langote
On 2017/02/22 21:24, Ashutosh Bapat wrote: > On Wed, Feb 22, 2017 at 11:11 AM, Amit Langote wrote: >> + /* >> +* Unlike inheritance children, partition tables are expected to be >> dropped >> +* when the parent partitioned table gets dropped. >> +*/ >> >> Hmm.

[HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

2017-02-22 Thread Michael Paquier
Hi all, When storing WAL segments on a dedicated partition with pg_receivexlog, for some deployments, the removal of past WAL segments depends on the frequency of base backups happening on the server. In short, once a new base backup is taken, it may not be necessary to keep around those past WAL

Re: [HACKERS] dropping partitioned tables without CASCADE

2017-02-22 Thread Ashutosh Bapat
I think this is ready for committer. On Thu, Feb 23, 2017 at 12:02 PM, Amit Langote wrote: > On 2017/02/22 21:24, Ashutosh Bapat wrote: >> On Wed, Feb 22, 2017 at 11:11 AM, Amit Langote wrote: >>> + /* >>> +* Unlike inheritance children, partition

  1   2   >