Re: [HACKERS] silent data loss with ext4 / all current versions

On Mon, Mar 28, 2016 at 8:25 AM, Andres Freund wrote: > I've also noticed that Coming back to this issue because... > a) pg_basebackup doesn't do anything about durability (it probably needs >a very similar patch to the one pg_rewind just received). I think that one of

Re: [HACKERS] Academic help for Postgres

On 12 May 2016 at 11:16, Rajeev rastogi wrote: > >Any others? > GPU offload. Some work on that already got done as part of the AXLE project, but there's still a lot more to do to get anything that can be usefully integrated into Pg. This likely ties in with

Re: [HACKERS] Use %u to print user mapping's umid and userid

On 2016/05/12 13:02, Tom Lane wrote: Etsuro Fujita writes: On 2016/05/11 18:03, Ashutosh Bapat wrote: A call to GetForeignTable would incur a catalog lookup which means a catalog table/index scan if corresponding entry is not in the cache. This is followed by

Re: [HACKERS] Use %u to print user mapping's umid and userid

Etsuro Fujita writes: > On 2016/05/11 18:03, Ashutosh Bapat wrote: >> A call to GetForeignTable would incur a catalog lookup which means a >> catalog table/index scan if corresponding entry is not in the cache. >> This is followed by GetUserMapping() which is another

Re: [HACKERS] Perf Benchmarking and regression.

On Wed, May 11, 2016 at 12:51 AM, Ashutosh Sharma wrote: > I am extremely sorry for the delayed response. As suggested by you, I have > taken the performance readings at 128 client counts after making the > following two changes: > > 1). Removed

Re: [HACKERS] Vacuum Full by Scheme

On Thu, May 12, 2016 at 7:31 AM, Filho Arrais wrote: > There is some development to use the vacuum full by scheme? > > Ex: vacuumdb -d postgres -parameter public > > In databases with thousands of tables and multiple scheme, it would be > interesting to have this feature.

Re: [HACKERS] Use %u to print user mapping's umid and userid

On 2016/05/11 18:03, Ashutosh Bapat wrote: On Wed, May 11, 2016 at 1:34 PM, Etsuro Fujita > wrote: On 2016/05/11 16:49, Ashutosh Bapat wrote: The patch is calculating user mapping when it's readily available

Re: [HACKERS] Academic help for Postgres

On 11 May 2016 19:50, Bruce Momjian Wrote: >I am giving a keynote at an IEEE database conference in Helsinki next >week (http://icde2016.fi/). (Yes, I am not attending PGCon Ottawa >because I accepted the Helsinki conference invitation before the PGCon >Ottawa date was changed from June to

Re: [HACKERS] Academic help for Postgres

On 11 May 2016 at 22:20, Bruce Momjian wrote: > I am giving a keynote at an IEEE database conference in Helsinki next > week (http://icde2016.fi/). (Yes, I am not attending PGCon Ottawa > because I accepted the Helsinki conference invitation before the PGCon > Ottawa date was

Re: [HACKERS] Does Type Have = Operator?

On May 11, 2016, at 11:01 AM, Fabrízio de Royes Mello wrote: > I know... but you can do that just in case the current behaviour fail by > cathing it with "begin...exception...", so you'll minimize the looking for > process on catalog. Yeah, I guess. Honestly 90% of

Re: [HACKERS] Does Type Have = Operator?

On May 11, 2016, at 10:34 AM, Kevin Grittner wrote: > I'm not clear enough on your intended usage to know whether these > operators are a good fit, but they are sitting there waiting to be > used if they do fit. Huh. I haven’t had any problems with IS DISTINCT FROM for rows,

Re: [HACKERS] parallel.c is not marked as test covered

On 12 May 2016 at 07:04, Robert Haas wrote: > On Wed, May 11, 2016 at 1:57 PM, Robert Haas wrote: >> I don't immediately understand what's going wrong here. It looks to >> me like make_group_input_target() already called, and that worked OK, >> but

[HACKERS] Vacuum Full by Scheme

Hello, There is some development to use the vacuum full by scheme? Ex: *vacuumdb -d postgres **-parameter public* In databases with thousands of tables and multiple scheme, it would be interesting to have this feature. *Filho **Arrais * Analista de Tecnologia da Informação MBA em Gestão de

Re: [HACKERS] Academic help for Postgres

On 12/05/16 02:20, Bruce Momjian wrote: I am giving a keynote at an IEEE database conference in Helsinki next week (http://icde2016.fi/). (Yes, I am not attending PGCon Ottawa because I accepted the Helsinki conference invitation before the PGCon Ottawa date was changed from June to May). As

Re: [HACKERS] parallel.c is not marked as test covered

On Wed, May 11, 2016 at 1:57 PM, Robert Haas wrote: > I don't immediately understand what's going wrong here. It looks to > me like make_group_input_target() already called, and that worked OK, > but now make_partialgroup_input_target() is failing using more-or-less > the

Re: [HACKERS] Does Type Have = Operator?

On Wed, May 11, 2016 at 2:07 AM, David E. Wheeler wrote: > > On May 10, 2016, at 5:56 PM, Fabrízio de Royes Mello < fabriziome...@gmail.com> wrote: > > > Searching for the operator in pg_operator catalog isn't enought? > > Seems like overkill, but will do if there’s nothing

Re: [HACKERS] parallel.c is not marked as test covered

On Wed, May 11, 2016 at 1:48 PM, David G. Johnston wrote: > What happens when there are no workers available due to max_worker_processes > already being assigned? Then the leader runs the plan after all. > Related question, if max_parallel_degree is >1 and "the

Re: [HACKERS] parallel.c is not marked as test covered

On Wed, May 11, 2016 at 1:38 PM, Robert Haas wrote: >> I would just go fix this, along the lines of >> >> *** create_plain_partial_paths(PlannerInfo * >> *** 702,708 >> * with all of its inheritance siblings it may well pay off. >> */ >>

Re: [HACKERS] parallel.c is not marked as test covered

On Wed, May 11, 2016 at 10:38 AM, Robert Haas wrote: > On Wed, May 11, 2016 at 12:34 AM, Tom Lane wrote: > >> Hmm, that is strange. I would have expected that to stuff a Gather on > >> top of the Aggregate. I wonder why it's not doing that. > > > >

Re: [HACKERS] Re: Need help debugging why autovacuum seems "stuck" -- until I use superuser to vacuum freeze pg_database

Shawn wrote: > The problem is though, when I look at age(datfrozenxid) for > "my_database_name" it is the oldest at 654,189,546 and should trigger the > autovacuum_freeze_max_age of 200,000,000 but it doesn't. The relation with > this age is "pg_database". If I issue a manual "vacuum freeze

Re: [HACKERS] Academic help for Postgres

On 11 May 2016 at 12:58, Josh berkus wrote: > Together with that, automated substitution of materialized views for > query clauses. > > Also: optimizing for new hardware, like persistent memory. I recently saw some material in ACM SIGOPS on tuning filesystems to play better

Re: [HACKERS] parallel.c is not marked as test covered

On Wed, May 11, 2016 at 12:34 AM, Tom Lane wrote: >> Hmm, that is strange. I would have expected that to stuff a Gather on >> top of the Aggregate. I wonder why it's not doing that. > > The reason is that create_plain_partial_paths() contains a hard-wired > decision not to

Re: [HACKERS] Does Type Have = Operator?

On Wed, May 11, 2016 at 12:23 PM, David E. Wheeler wrote: > Oh, well crap. Maybe I’d be better off just comparing the plain > text of the expressions as Tom suggested. At the other extreme are the row comparison operators that only consider values equal if they have the

Re: [HACKERS] Does Type Have = Operator?

On May 11, 2016, at 10:19 AM, Kevin Grittner wrote: > As long as you don't assume too much about *what* is equal. > > test=# select '(1,1)(2,2)'::box = '(-4.5,1000)(-2.5,1000.5)'::box; > ?column? > -- > t > (1 row) Oh, well crap. Maybe I’d be better off just

Re: [HACKERS] Does Type Have = Operator?

On Wed, May 11, 2016 at 12:01 PM, David G. Johnston wrote: > Its hard to imagine defining "=" to mean something different in logic, > though, without intentionally trying to be cryptic. As long as you don't assume too much about *what* is equal. test=# select

Re: [HACKERS] Does Type Have = Operator?

On Wed, May 11, 2016 at 9:54 AM, Robert Haas wrote: > On Tue, May 10, 2016 at 9:16 PM, David G. Johnston > wrote: > > Brute force: you'd have to query pg_amop and note the absence of a row > with > > a btree (maybe hash too...) family strategy

Re: [HACKERS] Academic help for Postgres

On 05/11/2016 07:54 AM, Bruce Momjian wrote: > On Wed, May 11, 2016 at 05:41:21PM +0300, Heikki Linnakangas wrote: >> On 11/05/16 17:32, Bruce Momjian wrote: >>> On Wed, May 11, 2016 at 05:31:10PM +0300, Konstantin Knizhnik wrote: On 11.05.2016 17:20, Bruce Momjian wrote: > I am giving a

Re: [HACKERS] Does Type Have = Operator?

On Tue, May 10, 2016 at 9:16 PM, David G. Johnston wrote: > Brute force: you'd have to query pg_amop and note the absence of a row with > a btree (maybe hash too...) family strategy 3 (1 for hash) [equality] where > the left and right types are the same and match the

Re: [HACKERS] asynchronous and vectorized execution

On Wed, May 11, 2016 at 12:30 PM, Andres Freund wrote: > On 2016-05-11 12:27:55 -0400, Robert Haas wrote: >> On Wed, May 11, 2016 at 11:49 AM, Andres Freund wrote: >> > On 2016-05-11 10:12:26 -0400, Robert Haas wrote: >> >> > Hm. Do we really have to keep

Re: [HACKERS] asynchronous and vectorized execution

On 2016-05-11 12:27:55 -0400, Robert Haas wrote: > On Wed, May 11, 2016 at 11:49 AM, Andres Freund wrote: > > On 2016-05-11 10:12:26 -0400, Robert Haas wrote: > >> > Hm. Do we really have to keep the page locked in the page-at-a-time > >> > mode? Shouldn't the pin suffice? >

Re: [HACKERS] asynchronous and vectorized execution

On Wed, May 11, 2016 at 11:49 AM, Andres Freund wrote: > On 2016-05-11 10:12:26 -0400, Robert Haas wrote: >> > I've to admit I'm not that convinced about the speedups in the !fdw >> > case. There seems to be a lot easier avenues for performance >> > improvements. >> >> What

Re: [HACKERS] asynchronous and vectorized execution

On 2016-05-11 10:32:20 -0400, Robert Haas wrote: > On Tue, May 10, 2016 at 8:50 PM, Andres Freund wrote: > > That seems to suggest that we need to restructure how we get to calling > > fmgr functions, before worrying about the actual fmgr call. > > Any ideas on how to do

Re: [HACKERS] ALTER TABLE lock downgrades have broken pg_upgrade

Peter Eisentraut wrote: > On 5/3/16 1:25 PM, Alvaro Herrera wrote: > >If we can put together a script that runs test.sh for various versions > >and then verifies the runs, we could use it in both buildfarm and > >coverage. > > Not that that would be useless, but note that the value in this case

Re: [HACKERS] asynchronous and vectorized execution

On 2016-05-11 10:12:26 -0400, Robert Haas wrote: > > I've to admit I'm not that convinced about the speedups in the !fdw > > case. There seems to be a lot easier avenues for performance > > improvements. > > What I'm talking about is a query like this: > > SELECT * FROM

Re: [HACKERS] Academic help for Postgres

2016-05-11 23:20 GMT+09:00 Bruce Momjian : > I am giving a keynote at an IEEE database conference in Helsinki next > week (http://icde2016.fi/). (Yes, I am not attending PGCon Ottawa > because I accepted the Helsinki conference invitation before the PGCon > Ottawa date was

Re: [HACKERS] HeapTupleSatisfiesToast() busted? (was atomic pin/unpin causing errors)

On May 11, 2016 7:06:05 AM PDT, Teodor Sigaev wrote: >>> Allow Pin/UnpinBuffer to operate in a lockfree manner. >>> I get the errors: >>> >>> ERROR: attempted to delete invisible tuple >>> ERROR: unexpected chunk number 1 (expected 2) for toast value > >Just reminder,

Re: [HACKERS] Academic help for Postgres

On Wed, May 11, 2016 at 9:32 AM, Bruce Momjian wrote: > On Wed, May 11, 2016 at 05:31:10PM +0300, Konstantin Knizhnik wrote: >> Incremental materialized views? > > I don't know. Is that something academics would research? One paper I have found particularly good is this:

Re: [HACKERS] Academic help for Postgres

On 11/05/16 17:32, Bruce Momjian wrote: On Wed, May 11, 2016 at 05:31:10PM +0300, Konstantin Knizhnik wrote: On 11.05.2016 17:20, Bruce Momjian wrote: I am giving a keynote at an IEEE database conference in Helsinki next week (http://icde2016.fi/). (Yes, I am not attending PGCon Ottawa

Re: [HACKERS] Academic help for Postgres

On Wed, May 11, 2016 at 05:41:21PM +0300, Heikki Linnakangas wrote: > On 11/05/16 17:32, Bruce Momjian wrote: > >On Wed, May 11, 2016 at 05:31:10PM +0300, Konstantin Knizhnik wrote: > >>On 11.05.2016 17:20, Bruce Momjian wrote: > >>>I am giving a keynote at an IEEE database conference in Helsinki

Re: [HACKERS] Academic help for Postgres

On 11.05.2016 17:32, Bruce Momjian wrote: On Wed, May 11, 2016 at 05:31:10PM +0300, Konstantin Knizhnik wrote: On 11.05.2016 17:20, Bruce Momjian wrote: I am giving a keynote at an IEEE database conference in Helsinki next week (http://icde2016.fi/). (Yes, I am not attending PGCon Ottawa

Re: [HACKERS] Academic help for Postgres

On Wed, May 11, 2016 at 5:20 PM, Bruce Momjian wrote: > I am giving a keynote at an IEEE database conference in Helsinki next > week (http://icde2016.fi/). (Yes, I am not attending PGCon Ottawa > because I accepted the Helsinki conference invitation before the PGCon > Ottawa

Re: [HACKERS] asynchronous and vectorized execution

On Tue, May 10, 2016 at 8:50 PM, Andres Freund wrote: > That seems to suggest that we need to restructure how we get to calling > fmgr functions, before worrying about the actual fmgr call. Any ideas on how to do that? ExecMakeFunctionResultNoSets() isn't really doing a heck

Re: [HACKERS] Academic help for Postgres

On Wed, May 11, 2016 at 05:31:10PM +0300, Konstantin Knizhnik wrote: > > > On 11.05.2016 17:20, Bruce Momjian wrote: > >I am giving a keynote at an IEEE database conference in Helsinki next > >week (http://icde2016.fi/). (Yes, I am not attending PGCon Ottawa > >because I accepted the Helsinki

Re: [HACKERS] Academic help for Postgres

On Wed, May 11, 2016 at 05:24:44PM +0300, Oleg Bartunov wrote: > On Wed, May 11, 2016 at 5:20 PM, Bruce Momjian wrote: > > As part of the keynote, I would like to mention areas where academia can > > help us. The topics I can think of are: > > > > Query optimization > >

Re: [HACKERS] Academic help for Postgres

On 11.05.2016 17:20, Bruce Momjian wrote: I am giving a keynote at an IEEE database conference in Helsinki next week (http://icde2016.fi/). (Yes, I am not attending PGCon Ottawa because I accepted the Helsinki conference invitation before the PGCon Ottawa date was changed from June to May).

Re: [HACKERS] ALTER TABLE lock downgrades have broken pg_upgrade

On Wed, May 11, 2016 at 09:40:09AM -0400, Peter Eisentraut wrote: > Not that that would be useless, but note that the value in this case (and > most others) comes from having a candidate object in the database before > upgrade that exercises the particular problem, mostly independent of what >

Re: [HACKERS] asynchronous and vectorized execution

On 10.05.2016 20:26, Robert Haas wrote: At this moment (February) them have implemented translation of only few PostgreSQL operators used by ExecQuals and do not support aggregates. Them get about 2 times increase of speed at synthetic queries and 25% increase at TPC-H Q1 (for Q1 most

Re: [HACKERS] asynchronous and vectorized execution

On Wed, May 11, 2016 at 10:17 AM, Konstantin Knizhnik wrote: > Yes, I agree with you that complete rewriting of optimizer is huge project > with unpredictable influence on performance of some queries. > Changing things incrementally is good approach, but only if we are

Re: [HACKERS] asynchronous and vectorized execution

On Tue, May 10, 2016 at 8:23 PM, Andres Freund wrote: >> c. Modify some nodes (perhaps start with nodeAgg.c) to allow them to >> process a batch TupleTableSlot. This will require some tight loop to >> aggregate the entire TupleTableSlot at once before returning. >> d. Add

[HACKERS] Academic help for Postgres

I am giving a keynote at an IEEE database conference in Helsinki next week (http://icde2016.fi/). (Yes, I am not attending PGCon Ottawa because I accepted the Helsinki conference invitation before the PGCon Ottawa date was changed from June to May). As part of the keynote, I would like to

Re: [HACKERS] asynchronous and vectorized execution

On 11.05.2016 17:00, Robert Haas wrote: On Tue, May 10, 2016 at 3:42 PM, Konstantin Knizhnik wrote: Doesn't this actually mean that we need to have normal job scheduler which is given queue of jobs and having some pool of threads will be able to orginize efficient

Re: [HACKERS] HeapTupleSatisfiesToast() busted? (was atomic pin/unpin causing errors)

Allow Pin/UnpinBuffer to operate in a lockfree manner. I get the errors: ERROR: attempted to delete invisible tuple ERROR: unexpected chunk number 1 (expected 2) for toast value Just reminder, you investigate "unexpected chunk number" problem, but, seems, we have another bug (first

Re: [HACKERS] asynchronous and vectorized execution

On Tue, May 10, 2016 at 7:57 PM, Andres Freund wrote: >> 1. asynchronous execution, by which I mean the ability of a node to >> somehow say that it will generate a tuple eventually, but is not yet >> ready, so that the executor can go run some other part of the plan >> tree

Re: [HACKERS] asynchronous and vectorized execution

On Tue, May 10, 2016 at 3:42 PM, Konstantin Knizhnik wrote: > Doesn't this actually mean that we need to have normal job scheduler which > is given queue of jobs and having some pool of threads will be able to > orginize efficient execution of queries? Optimizer can

Re: [HACKERS] asynchronous and vectorized execution

On Tue, May 10, 2016 at 4:57 PM, Jim Nasby wrote: > Even so, I would think that the simplification in the executor would be > worth it. If you need to add a new node there's dozens of places where you > might have to mess with these giant case statements. Dozens? I

Re: [HACKERS] Add jsonb_compact(...) for whitespace-free jsonb to text

On 4/29/16 8:56 AM, Shulgin, Oleksandr wrote: It would probably make sense model this function after Python's "dump-to-JSON-string" function: https://docs.python.org/2/library/json.html#json.dumps With the optional parameters for sorting the keys, indentation size and punctuation. This way all

Re: [HACKERS] ALTER TABLE lock downgrades have broken pg_upgrade

On 5/3/16 1:25 PM, Alvaro Herrera wrote: If we can put together a script that runs test.sh for various versions and then verifies the runs, we could use it in both buildfarm and coverage. Not that that would be useless, but note that the value in this case (and most others) comes from having

[HACKERS] Minor documentation patch

Hi, Yesterday I was going over some consultancy and went to check some syntax for CREATE FUNCTION, particularly related to SECURITY DEFINER part. Reading there I saw a paragraph which had a sentence that wasn't very clear at first. The patch's description gives a better idea on the change, and

Re: [HACKERS] Use %u to print user mapping's umid and userid

On Wed, May 11, 2016 at 1:34 PM, Etsuro Fujita wrote: > On 2016/05/11 16:49, Ashutosh Bapat wrote: > >> The patch is calculating user mapping when it's readily available >> through RelOptInfo::fdw_private. That incurs a catalog lookup >> unnecessarily. Instead, can

Re: [HACKERS] Use %u to print user mapping's umid and userid

On 2016/05/11 16:49, Ashutosh Bapat wrote: The patch is calculating user mapping when it's readily available through RelOptInfo::fdw_private. That incurs a catalog lookup unnecessarily. Instead, can we add new function makeOid, oidVal on the lines of makeInteger and intVal to store and retrieve

Re: [HACKERS] Use %u to print user mapping's umid and userid

On Wed, May 11, 2016 at 1:10 PM, Etsuro Fujita wrote: > On 2016/05/10 16:56, Etsuro Fujita wrote: > >> Here is a patch to fix this. >> > > I found that the previous patch handles the ForeignScan's fs_relids > Bitmapset destructively. Also, I noticed that I removed

Re: [HACKERS] Odd oid-system-column handling in postgres_fdw

On 2016/04/05 17:15, Etsuro Fujita wrote: On 2016/03/16 16:25, Etsuro Fujita wrote: PG9.5 allows us to add an oid system column to foreign tables, using ALTER FOREIGN TABLE SET WITH OIDS, but currently, that column reads as zeroes in postgres_fdw. That seems to me like a bug. So, I'd like to

Re: [HACKERS] Use %u to print user mapping's umid and userid

On 2016/05/10 16:56, Etsuro Fujita wrote: Here is a patch to fix this. I found that the previous patch handles the ForeignScan's fs_relids Bitmapset destructively. Also, I noticed that I removed some existing comments inadvertently. So, I'm attaching the updated patch to fix those things.

Re: [HACKERS] Optimization for updating foreign tables in Postgres FDW

On 2016/04/28 13:45, Michael Paquier wrote: On Wed, Apr 27, 2016 at 12:16 PM, Etsuro Fujita wrote: On 2016/04/26 21:45, Etsuro Fujita wrote: While re-reviewing the fix, I noticed that since PQcancel we added to pgfdw_xact_callback to cancel a DML pushdown query

Re: [HACKERS] asynchronous and vectorized execution

On Wed, May 11, 2016 at 3:52 AM, Andres Freund wrote: > On 2016-05-11 03:20:12 +0300, Ants Aasma wrote: >> On Tue, May 10, 2016 at 7:56 PM, Robert Haas wrote: >> > On Mon, May 9, 2016 at 8:34 PM, David Rowley >> > wrote: