Re: != should give error?

On 2017-12-21 08:16, Craig Ringer wrote: postgres=# create table tbl (col_a int, col_b int); CREATE TABLE postgres=# insert into tbl values (1,2); INSERT 0 1 postgres=# insert into tbl values (2,1); INSERT 0 1 *postgres=# select * from tbl where col_a ! = 1; col_

Re: Using ProcSignal to get memory context stats from a running backend

On 21 December 2017 at 15:24, Andres Freund wrote: > Hi, > > On 2017-12-21 15:13:13 +0800, Craig Ringer wrote: > > There tons of callers to enlargeStringInfo, so a 'noerror' parameter > would > > be viable. > > Not sure what you mean with that sentence? > Mangled in editing and sent prematurely

Re: Using ProcSignal to get memory context stats from a running backend

Hi, On 2017-12-21 15:13:13 +0800, Craig Ringer wrote: > There tons of callers to enlargeStringInfo, so a 'noerror' parameter would > be viable. Not sure what you mean with that sentence? > But I'm not convinced it's worth it personally. If we OOM in response to a > ProcSignal request for memor

Re: != should give error?

On 21 December 2017 at 14:43, Rajkumar Raghuwanshi < rajkumar.raghuwan...@enterprisedb.com> wrote: > Hi, > > with below query I am getting unexpected output. here != is > behaving as = > is this expected behaviour? > > postgres=# create table tbl (col_a int, col_b int); > CREATE TABLE > postgres=#

Re: Using ProcSignal to get memory context stats from a running backend

On 21 December 2017 at 14:58, Andres Freund wrote: > Hi, > > On 2017-12-21 14:49:28 +0800, Craig Ringer wrote: > > +/* > > + * Accumulate writes into the buffer in diag_request_buf, > > + * for use with functions that expect a printf-like callback. > > + */ > > +static void > > +printwrapper_stri

Re: Using ProcSignal to get memory context stats from a running backend

Hi, On 2017-12-21 14:49:28 +0800, Craig Ringer wrote: > +/* > + * Accumulate writes into the buffer in diag_request_buf, > + * for use with functions that expect a printf-like callback. > + */ > +static void > +printwrapper_stringinfo(void *extra, const char * fmt, ...) > +{ > + StringInfo out

Re: Using ProcSignal to get memory context stats from a running backend

On 20 December 2017 at 08:46, Craig Ringer wrote: > On 20 December 2017 at 02:35, Andres Freund wrote: > > >> > Yeah. But please don't mess with MemoryContextStats per se --- >> > I dunno about you guys but "call MemoryContextStats(TopMemoryContext)" >> > is kinda wired into my gdb reflexes. I

!= should give error?

Hi, with below query I am getting unexpected output. here != is behaving as = is this expected behaviour? postgres=# create table tbl (col_a int, col_b int); CREATE TABLE postgres=# insert into tbl values (1,2); INSERT 0 1 postgres=# insert into tbl values (2,1); INSERT 0 1 *postgres=# select

Re: Bitmap table scan cost per page formula

Hi Jeff, The issue happens on our customer's production environment, I don't have access to their hardware. But I agree, the default value 100 is indeed a poor value. After I change the default value to 30 or less, the query starts generating plan with bitmap scan as expected. ~ ~ ~ Haisheng Yuan

Re: domain cast in parameterized vs. non-parameterized query

Hi 2017-12-20 23:41 GMT+01:00 Tom Lane : > I wrote: > > You might consider whether you can write 'spa-000'::uid explicitly in > your > > query; that results in immediate application of the domain coercion, so > > that the planner no longer sees that as a run-time operation it has to > > avoid. >

Re: Bitmap table scan cost per page formula

On Wed, Dec 20, 2017 at 5:03 PM, Tom Lane wrote: > > The parabola is probably wrong in detail --- its behavior as we approach > reading all of the pages ought to be more asymptotic, seems like. > I suppose that the reason it appears to go below the seqscan cost at the > right is that even the rig

Re: Bitmap table scan cost per page formula

On Tue, Dec 19, 2017 at 7:25 PM, Justin Pryzby wrote: > > I started playing with this weeks ago (probably during Vitaliy's problem > report). Is there any reason cost_bitmap_heap_scan shouldn't interpolate > based > on correlation from seq_page_cost to rand_page_cost, same as cost_index ? > I t

Re: Bitmap table scan cost per page formula

On Tue, Dec 19, 2017 at 11:55 AM, Haisheng Yuan wrote: > Hi hackers, > > This is Haisheng Yuan from Greenplum Database. > > We had some query in production showing that planner favors seqscan over > bitmapscan, and the execution of seqscan is 5x slower than using > bitmapscan, but the cost of bit

Re: GSoC 2018

Hi, Stefan! > 15 дек. 2017 г., в 15:03, Stefan Keller написал(а): > > What about adding "Learned Index" as project task [*]? > This type of index looks promising for certain properties. > > [*] "The Case for Learned Index Structures" Kraska et al. (Dec 2017) > https://arxiv.org/abs/1712.01208 L

Re: Bitmap table scan cost per page formula

On Wed, Dec 20, 2017 at 2:18 PM, Robert Haas wrote: > On Wed, Dec 20, 2017 at 4:20 PM, Jeff Janes wrote: >> >> It is not obvious to me that the parabola is wrong. I've certainly seen >> cases where reading every 2nd or 3rd block (either stochastically, or >> modulus) actually does take longer t

Re: Add hint about replication slots when nearing wraparound

On Wed, Dec 20, 2017 at 10:00 PM, Feike Steenbergen wrote: > As far as I know the issue only occurs for stale replication slots for > logical decoding but not for physical replication, is that correct? Yeah, I recall something similar. @@ -255,7 +255,9 @@ $ pg_recvlogical -d postgres --slot test

Re: Letting plpgsql in on the fun with the new expression eval stuff

On Wed, Dec 20, 2017 at 6:06 PM, Tom Lane wrote: > Anyway, I left it as-is, but I'm willing to make the change if > people feel the other way is better. I feel the other way -- let's not add more pointer indirections if it isn't really necessary. -- Robert Haas EnterpriseDB: http://www.enterpri

Re: [HACKERS] REINDEX CONCURRENTLY 2.0

On 21 December 2017 at 11:31, Michael Paquier wrote: > On Thu, Dec 21, 2017 at 11:46 AM, Alvaro Herrera > wrote: > > Michael Paquier wrote: > >> Well, the idea is really to get rid of that as there are already > >> facilities of this kind for CREATE TABLE LIKE in the parser and ALTER > >> TABLE

Re: [HACKERS] REINDEX CONCURRENTLY 2.0

On Thu, Dec 21, 2017 at 11:46 AM, Alvaro Herrera wrote: > Michael Paquier wrote: >> Well, the idea is really to get rid of that as there are already >> facilities of this kind for CREATE TABLE LIKE in the parser and ALTER >> TABLE when rewriting a relation. It is not really attractive to have a >>

Re: [HACKERS] replace GrantObjectType with ObjectType

Michael, Peter, all, * Michael Paquier (michael.paqu...@gmail.com) wrote: > On Thu, Dec 21, 2017 at 1:19 AM, Peter Eisentraut > wrote: > > On 12/20/17 10:37, Alvaro Herrera wrote: > >> I think Michael's point is that instead of a "default:" clause, this > >> switch should list all the known value

Re: Reproducible builds: genbki.pl and Gen_fmgrtab.pl

Christoph Berg writes: > Re: Tom Lane 2017-12-16 <417.1513438...@sss.pgh.pa.us> >> I think we're talking at cross-purposes. I'm not saying we should not fix >> this problem. I'm saying that the proposed fix appears incomplete ... > Grepping through the source, there are three places where $0 pr

Re: [HACKERS] REINDEX CONCURRENTLY 2.0

Michael Paquier wrote: > Well, the idea is really to get rid of that as there are already > facilities of this kind for CREATE TABLE LIKE in the parser and ALTER > TABLE when rewriting a relation. It is not really attractive to have a > 3rd method in the backend code to do the same kind of things,

Re: AS OF queries

On 21 December 2017 at 00:17, Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > On 12/20/17 10:29, Tom Lane wrote: > > Please say that's just an Oracle-ism and not SQL standard, because it's > > formally ambiguous. This is required to work by spec: > > > > regression=# select x as of

Re: [HACKERS] REINDEX CONCURRENTLY 2.0

Andreas Karlsson wrote: > Here is a rebased version of the patch. Is anybody working on rebasing this patch? -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] replace GrantObjectType with ObjectType

On Thu, Dec 21, 2017 at 1:19 AM, Peter Eisentraut wrote: > On 12/20/17 10:37, Alvaro Herrera wrote: >> I think Michael's point is that instead of a "default:" clause, this >> switch should list all the known values of the enum and throw an >> "unsupported object type" error for them. So whenever

Re: User defined data types in Logical Replication

On Wed, Dec 20, 2017 at 5:39 PM, Huong Dangminh wrote: > Hi Sawada-san, > >> Thank you for quick response. The changes look good to me. But I wonder >> if the following changes needs some comments to describe what each checks >> does for. >> >> -if (errarg->attnum < 0) >> +if (errarg->loca

Re: Bitmap table scan cost per page formula

Robert Haas writes: > On Wed, Dec 20, 2017 at 4:20 PM, Jeff Janes wrote: >> It is not obvious to me that the parabola is wrong. I've certainly seen >> cases where reading every 2nd or 3rd block (either stochastically, or >> modulus) actually does take longer than reading every block, because it

Re: Tracking of page changes for backup purposes. PTRACK [POC]

On Thu, Dec 21, 2017 at 7:35 AM, Robert Haas wrote: > On Wed, Dec 20, 2017 at 3:45 PM, Tomas Vondra > wrote: >>> Isn't more effective hold this info in Postgres than in backup sw? >>> Then any backup sw can use this implementation. >> >> I don't think it means it can't be implemented in Postgres,

Re: Basebackups reported as idle

On Wed, Dec 20, 2017 at 9:02 PM, Magnus Hagander wrote: > On Wed, Dec 20, 2017 at 12:50 PM, Michael Paquier > wrote: > Yes. Of course. I can't read. That's the same as the notice below about it > not returning false -- I managed to miss the extra if() there, and thought > it always exited with ER

Re: Bitmap table scan cost per page formula

Robert, you are right. The new formula serves Greenplum better than the original formula, because our default random page cost is much higher than Postgres. We don't want random cost always dominates in the final cost per page. ~ ~ ~ Haisheng Yuan On Wed, Dec 20, 2017 at 12:25 PM, Robert Haas wr

Re: Letting plpgsql in on the fun with the new expression eval stuff

I wrote: > * Redesign the API for the ParamListInfo paramFetch hook so that the > ParamExternData array can be entirely virtual. Typical access to > the info about a PARAM_EXTERN Param now looks like > > if (paramInfo->paramFetch != NULL) > prm = paramInfo->paramFetch(paramInf

Re: vacuum vs heap_update_tuple() and multixactids

On Wed, Dec 20, 2017 at 9:05 AM, Andres Freund wrote: > Indeed. I kinda wonder whether we hackishly solve this by simply > returning true fore all pids if it's waiting for a cleanup lock. That's > not actually that far from the truth... The big problem with that I see > is very short waits that re

Re: domain cast in parameterized vs. non-parameterized query

I wrote: > You might consider whether you can write 'spa-000'::uid explicitly in your > query; that results in immediate application of the domain coercion, so > that the planner no longer sees that as a run-time operation it has to > avoid. Hm, scratch that --- experimentation shows that the pars

Re: Tracking of page changes for backup purposes. PTRACK [POC]

On Wed, Dec 20, 2017 at 3:45 PM, Tomas Vondra wrote: >> Isn't more effective hold this info in Postgres than in backup sw? >> Then any backup sw can use this implementation. > > I don't think it means it can't be implemented in Postgres, but does it > need to be done in backend? > > For example, i

Re: Shouldn't execParallel.c null-terminate query_string in the parallel DSM?

On Wed, Dec 20, 2017 at 2:03 AM, Rafia Sabih wrote: > On Wed, Dec 20, 2017 at 7:58 AM, Thomas Munro > wrote: >> I just saw some trailing garbage in my log file emanating from a >> parallel worker when my query happened to be a BUFFERALIGNed length >> (in this case 64 characters). Did commit 4c72

Re: domain cast in parameterized vs. non-parameterized query

> > You might consider whether you can write 'spa-000'::uid explicitly in your > query; that results in immediate application of the domain coercion, so > that the planner no longer sees that as a run-time operation it has to > avoid. > I should have mentioned that I tried an explicit cast and fou

Re: domain cast in parameterized vs. non-parameterized query

Robert Haas writes: > On Wed, Dec 20, 2017 at 1:03 AM, David Kamholz wrote: >> Note that in the above plan, 'spa-000' is cast to text before it's cast to >> uid. This >> is apparently connected to why postgresql can't choose the better plan. > It's slightly hard for me to follow what's going on

Re: Bitmap table scan cost per page formula

On Wed, Dec 20, 2017 at 4:20 PM, Jeff Janes wrote: > > It is not obvious to me that the parabola is wrong. I've certainly seen > cases where reading every 2nd or 3rd block (either stochastically, or > modulus) actually does take longer than reading every block, because it > defeats read-ahead. B

Re: domain cast in parameterized vs. non-parameterized query

> > That's not too surprising. PostgreSQL can't choose a plan based on > the parameter value when it doesn't know the parameter value I thought that since 9.2, postgresql could "generate plans based on the parameter value even when using prepared statements" (paraphrase of 9.2 release notes). I'

Re: domain cast in parameterized vs. non-parameterized query

On Wed, Dec 20, 2017 at 1:03 AM, David Kamholz wrote: > I've recently come across a query that produces different plans depending on > whether it's parameterized or not. That's not too surprising. PostgreSQL can't choose a plan based on the parameter value when it doesn't know the parameter valu

Re: Bitmap table scan cost per page formula

On Wed, Dec 20, 2017 at 12:29 PM, Robert Haas wrote: > On Tue, Dec 19, 2017 at 2:55 PM, Haisheng Yuan wrote: >> >> Below is the graph (credit to Heikki) that plots the total estimated cost >> of a bitmap heap scan, where table size is 1 pages, and >> random_page_cost=10 and seq_page_cost=1.

Re: [HACKERS] parallel.c oblivion of worker-startup failures

On Tue, Dec 19, 2017 at 11:28 PM, Amit Kapila wrote: > That is not the main point I am bothered about. I am concerned that > the patch proposed by you can lead to hang if there is a crash (abrupt > failure like proc_exit(1)) after attaching to the error queue. This is > explained in my email up t

Re: AS OF queries

On 20/12/17 14:48, Konstantin Knizhnik wrote: On 20.12.2017 16:12, Laurenz Albe wrote: Konstantin Knizhnik wrote: I wonder if Postgres community is interested in supporting time travel queries in PostgreSQL (something like AS OF queries in Oracle: https://docs.oracle.com/cd/B14117_01/appdev

Re: Tracking of page changes for backup purposes. PTRACK [POC]

On 12/20/2017 09:29 PM, Pavel Stehule wrote: > > > 2017-12-20 21:18 GMT+01:00 Robert Haas >: > > On Wed, Dec 20, 2017 at 3:15 PM, Pavel Stehule > mailto:pavel.steh...@gmail.com>> wrote: > >> > So I'm somewhat hesitant to proclaim option 5 as the clear

Re: Letting plpgsql in on the fun with the new expression eval stuff

I wrote: > Will send a patch in a bit. I need to write an explanation of what all > I changed. OK then. What the attached patch does is: * Create a new step type EEOP_PARAM_CALLBACK (if anyone has a better naming idea, I'm receptive) and add the infrastructure needed for add-on modules to gener

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-20 21:18 GMT+01:00 Robert Haas : > On Wed, Dec 20, 2017 at 3:15 PM, Pavel Stehule > wrote: > >> > So I'm somewhat hesitant to proclaim option 5 as the clear winner, > here. > >> > >> I agree. I think (4) is better. > > > > Can depends on load? For smaller intensive updated databases the

Re: Bitmap table scan cost per page formula

On Tue, Dec 19, 2017 at 2:55 PM, Haisheng Yuan wrote: > > Below is the graph (credit to Heikki) that plots the total estimated cost > of a bitmap heap scan, where table size is 1 pages, and > random_page_cost=10 and seq_page_cost=1. X axis is the number of pages > fetche. I.e. on the left, no

Re: Bitmap table scan cost per page formula

On Tue, Dec 19, 2017 at 10:25 PM, Justin Pryzby wrote: > In this old thread: > https://www.postgresql.org/message-id/CAGTBQpZ%2BauG%2BKhcLghvTecm4-cGGgL8vZb5uA3%3D47K7kf9RgJw%40mail.gmail.com > ..Claudio Freire wrote: >> Correct me if I'm wrong, but this looks like the planner not >> accounting

Re: Tracking of page changes for backup purposes. PTRACK [POC]

On Wed, Dec 20, 2017 at 3:15 PM, Pavel Stehule wrote: >> > So I'm somewhat hesitant to proclaim option 5 as the clear winner, here. >> >> I agree. I think (4) is better. > > Can depends on load? For smaller intensive updated databases the 5 can be > optimal, for large less updated databases the 4

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-20 21:11 GMT+01:00 Robert Haas : > On Tue, Dec 19, 2017 at 5:37 PM, Tomas Vondra > wrote: > > On 12/18/2017 11:18 AM, Anastasia Lubennikova wrote: > >> 1. Use file modification time as a marker that the file has changed. > >> 2. Compute file checksums and compare them. > >> 3. LSN-based m

Re: Tracking of page changes for backup purposes. PTRACK [POC]

On Tue, Dec 19, 2017 at 5:37 PM, Tomas Vondra wrote: > On 12/18/2017 11:18 AM, Anastasia Lubennikova wrote: >> 1. Use file modification time as a marker that the file has changed. >> 2. Compute file checksums and compare them. >> 3. LSN-based mechanisms. Backup pages with LSN >= last backup LSN. >

Re: Missed parallelism option in plpgsql?

On Wed, Dec 20, 2017 at 2:26 PM, Tom Lane wrote: > I happened to notice that while writing this in plpgsql > will consider a parallel plan: > > select count(*) into s from tenk1 where ten = x; > > writing this will not: > > s := count(*) from tenk1 where ten = x; > > Is that intent

Re: [HACKERS] Proposal: Local indexes for partitioned table

Just to show what I'm talking about, here are a few prototype patches. Beware, these are all very lightly tested/reviewed. Input is welcome, particularly if it comes in the guise of patches to regression tests showing cases that misbehave. 0001 is a fixup for the v6 patch I posted upthread; it's

Re: Cost Model

2017-12-20 17:34 GMT-02:00 Andres Freund : > On 2017-12-20 17:13:31 -0200, neto brpr wrote: > > Just to explain it better. The idea of ​​differentiating read and write > > parameters (sequential and random) is exactly so that the access plans > can > > be better chosen by the optimizer. But for th

PGCon 2018 call for papers

PGCon 2018 will be on 29 May - 1 June 2018 at University of Ottawa. * 29-30 May (Tue-Wed) tutorials * 30 May (Wed) The Unconference * 31 May - 1 June (Thu-Fri) talks - the main part of the conference See http://www.pgcon.org/2018/ We are now accepting proposals for the main part of the conferenc

Re: Cost Model

On 2017-12-20 17:13:31 -0200, neto brpr wrote: > Just to explain it better. The idea of ​​differentiating read and write > parameters (sequential and random) is exactly so that the access plans can > be better chosen by the optimizer. But for this, the Hash join, merge join, > sorting and other alg

Re: Cost Model

On Wed, Dec 20, 2017 at 12:26 PM, neto brpr wrote: > > About what you said, that some settings can be configured by Tablespace? > I have already seen this in IBM DB2, but in Postgresql as far as I know, > for example the Random_page_cost and Seq_page_cost parameters are > configured for the Integ

Re: Cost Model

2017-12-20 16:37 GMT-02:00 David G. Johnston : > On Wed, Dec 20, 2017 at 11:26 AM, neto brpr wrote: > >> Dear David >> I have read documentation that you send, but it has only sequential page >> cost and random page cost parameters. What I need, would be a model of >> custo for Differentiate Read

Missed parallelism option in plpgsql?

I happened to notice that while writing this in plpgsql will consider a parallel plan: select count(*) into s from tenk1 where ten = x; writing this will not: s := count(*) from tenk1 where ten = x; Is that intentional? Seems to me these cases ought to be treated the same. The

Re: Cost Model

neto brpr wrote: > Anyway, It seems that there has not yet been any initiative related to this > in the postgresql community, am I right? Yes. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] Proposal: Local indexes for partitioned table

Robert Haas wrote: > Sounds great! I made the comment during my talk at PGCONF.EU that > partitioned tables in PostgreSQL are really just a bunch of tables > glued together, but that over time "we'll make the glue better", and I > think the improvements on which you are working will go a long way

Re: Cost Model

2017-12-20 16:35 GMT-02:00 Alvaro Herrera : > neto brpr wrote: > > Dear David > > I have read documentation that you send, but it has only sequential page > > cost and random page cost parameters. What I need, would be a model of > > custo for Differentiate Read/Write (sequential and random), beca

Re: [HACKERS] Proposal: Local indexes for partitioned table

On Wed, Dec 20, 2017 at 12:01 PM, Alvaro Herrera wrote: > I have two patches to rebase on top of this, which I hope to post later > today: > > 1) let these indexes be unique (i.e. allow unique and PK constraints) > 2) allow foreign keys on partitioned tables > > I have a further patch to allow FOR

Re: AS OF queries

On Wed, Dec 20, 2017 at 4:26 PM, Tom Lane wrote: > Peter Eisentraut writes: > > On 12/20/17 10:29, Tom Lane wrote: > >> Please say that's just an Oracle-ism and not SQL standard, because it's > >> formally ambiguous. > > > The SQL standard syntax appears to be something like > > > "tablename" [

Re: Basebackups reported as idle

Magnus Hagander wrote: > PFA a patch that fixes this. I think this is bugfix-for-backpatch, I don't > think it has a large risk of breaking things. Thoughts? Agreed. As long as it doesn't show up as idle-in-transaction afterwards or something odd like that, it should be okay to backpatch. (I su

Re: Cost Model

On Wed, Dec 20, 2017 at 11:26 AM, neto brpr wrote: > Dear David > I have read documentation that you send, but it has only sequential page > cost and random page cost parameters. What I need, would be a model of > custo for Differentiate Read/Write (sequential and random), because in SSDs > the r

Re: Cost Model

neto brpr wrote: > Dear David > I have read documentation that you send, but it has only sequential page > cost and random page cost parameters. What I need, would be a model of > custo for Differentiate Read/Write (sequential and random), because in SSDs > the reads and writes have different costs

Re: Cost Model

Dear David I have read documentation that you send, but it has only sequential page cost and random page cost parameters. What I need, would be a model of custo for Differentiate Read/Write (sequential and random), because in SSDs the reads and writes have different costs. If you or someone knows a

Re: Letting plpgsql in on the fun with the new expression eval stuff

Andres Freund writes: > On 2017-12-20 12:12:48 -0500, Tom Lane wrote: >> I'm using several different test cases, but one that shows up the problem >> is [...] > Which certainly seems interesting. The outer ExecInterpExpr() indeed > doesn't do that much, it's the inner call that's the most relevan

Re: Letting plpgsql in on the fun with the new expression eval stuff

Hi, On 2017-12-20 12:12:48 -0500, Tom Lane wrote: > Andres Freund writes: > > What's the workload you're testing? I'm mildly surprised to see > > ExecEvalParamExtern() show up, rather than just plpgsql_param_fetch() & > > exec_eval_datum(). Or were you just listing that to specify the > > callpat

Re: Cost Model

On Wed, Dec 20, 2017 at 10:29 AM, neto brpr wrote: > Any comment, hint about it or something, please inform me. > The docs contain this - its seem to cover what you describe: ​ https://www.postgresql.org/docs/10/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS David J.

Re: Schema-qualify function calls in information_schema

David Fetter writes: > Please find attached a patch against master to do $Subject, which > tones down the hilarity, at least in information_schema. The views do not need this sort of change, because they're parsed only once during initdb. The bodies of functions in information_schema do need qua

Cost Model

Hello All Anyone know if there is a patch that changes the PostgreSQL cost model in some way? I'm working with an tuning technique, based in hybrid storage environments (HDDs and SSDs). I need to know if exist any patches that allow postgresql to differentiate, for example, I/O costs of read and

Schema-qualify function calls in information_schema

Folks, It's possible to arrange for schemas to precede pg_catalog and information_schema in a search_path setting, and when that's done, hilarity can ensue, especially when someone has created functions with identical signatures but non-identical behavior. People who do that should probably be pr

Re: Letting plpgsql in on the fun with the new expression eval stuff

Andres Freund writes: > On 2017-12-19 13:00:41 -0500, Tom Lane wrote: >> I'm looking at ways to get plpgsql expression evaluation to go faster, >> and one thing I'm noticing is the rather large overhead of going through >> ExecEvalParamExtern and plpgsql_param_fetch to get to the useful work >> (e

Re: [HACKERS] Proposal: Local indexes for partitioned table

Great, thanks for the input. pg_dump behaves as described upthread -- thanks David and Robert for the input. I did this by injecting a fake "INDEX ATTACH" object in pg_dump's model, which depends on the index-on-parent-table; which in turn depends on the index-on-partition. Because of the depend

Re: AS OF queries

Peter Eisentraut writes: > On 12/20/17 10:29, Tom Lane wrote: >> Please say that's just an Oracle-ism and not SQL standard, because it's >> formally ambiguous. > The SQL standard syntax appears to be something like > "tablename" [ AS OF SYSTEM TIME 'something' ] [ [ AS ] "alias" ] > That's not

Re: AS OF queries

On Wed, Dec 20, 2017 at 5:17 PM, Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > On 12/20/17 10:29, Tom Lane wrote: > > Please say that's just an Oracle-ism and not SQL standard, because it's > > formally ambiguous. This is required to work by spec: > > > > regression=# select x as

Re: AS OF queries

On 12/20/17 10:29, Tom Lane wrote: > Please say that's just an Oracle-ism and not SQL standard, because it's > formally ambiguous. This is required to work by spec: > > regression=# select x as of from (values(1)) t(x); > of > > 1 > (1 row) > > so it's not possible for us ever to suppor

Re: AS OF queries

On Wed, Dec 20, 2017 at 03:03:50PM +0100, Laurenz Albe wrote: > Konstantin Knizhnik wrote: > > Please notice that it is necessary to configure postgres in proper > > way in order to be able to perform time travels. If you do not > > disable autovacuum, then old versions will be just cleaned-up. I

Re: [HACKERS] taking stdbool.h into use

On 11/15/17 15:13, Peter Eisentraut wrote: > I'm going to put this patch set as Returned With Feedback for now. The > GinNullCategory issues look like they will need quite a bit of work. > But it will be worth picking this up some time. I think the issue with GinNullCategory is practically unfixa

Re: [HACKERS] static assertions in C++

Peter Eisentraut writes: > On 12/20/17 00:57, Tom Lane wrote: >> I do not have a well-informed opinion on whether >> #if defined(__cpp_static_assert) && __cpp_static_assert >= 200410 >> is an appropriate test for static_assert() being available, but I'm >> pretty suspicious of it because none of m

Re: [HACKERS] replace GrantObjectType with ObjectType

Peter Eisentraut wrote: > On 12/19/17 19:56, Michael Paquier wrote: > > -stringify_adefprivs_objtype(GrantObjectType objtype) > > +stringify_adefprivs_objtype(ObjectType objtype) > > [...] > > +default: > > +elog(ERROR, "unrecognized grant object type: %d", (int) > > objtype);

Re: pltcl valgrind output

Andrew Dunstan writes: > The following appears to keep valgrind quiet. AFAICT the problem isn't > in our code. I'm a little worried about whether this could suppress reports that we do need to hear about. I don't know what TclNRRunCallbacks is or does exactly, but just going by the name, it seem

Re: AS OF queries

Laurenz Albe writes: > Konstantin Knizhnik wrote: >> I failed to support AS OF clause (as in Oracle) because of shift-reduce >> conflicts with aliases, >> so I have to introduce new ASOF keyword. May be yacc experts can propose >> how to solve this conflict without introducing new keyword... >

Re: [HACKERS] replace GrantObjectType with ObjectType

On 12/19/17 19:56, Michael Paquier wrote: > -stringify_adefprivs_objtype(GrantObjectType objtype) > +stringify_adefprivs_objtype(ObjectType objtype) > [...] > +default: > +elog(ERROR, "unrecognized grant object type: %d", (int) objtype); > +return "???";

Re: [HACKERS] static assertions in C++

On 12/20/17 00:57, Tom Lane wrote: > I do not have a well-informed opinion on whether > > #if defined(__cpp_static_assert) && __cpp_static_assert >= 200410 > > is an appropriate test for static_assert() being available, but I'm > pretty suspicious of it because none of my C++ compilers seem to >

Re: [HACKERS] Transaction control in procedures

Updated patch attached. I have addressed the most recent review comments I believe. The question about what happens to cursor loops in PL/Perl and PL/Python would be addressed by the separate thread "portal pinning". The test cases in this patch are currently marked by FIXMEs. I have changed th

Re: pltcl valgrind output

On 12/18/2017 10:12 AM, Andrew Dunstan wrote: > I've been adding support for valgrind to the buildfarm client (code will > hit the git repo shortly). Mostly the results have been pretty clean, > but the pltcl tests generated the attached output. Perhaps someone with > more valgrind-fu than I have

Re: Letting plpgsql in on the fun with the new expression eval stuff

Hi, Cool to see you looking at that, I think there's quite some optimization potential around. I've to reread a bunch of plpgsql code, it's not exactly an area of the code I'm intimately familiar with. On 2017-12-19 13:00:41 -0500, Tom Lane wrote: > I'm looking at ways to get plpgsql expression

Re: vacuum vs heap_update_tuple() and multixactids

Hi, On 2017-12-19 15:01:03 -0500, Robert Haas wrote: > On Tue, Dec 19, 2017 at 1:31 PM, Andres Freund wrote: > > Could I perhaps convince somebody to add that as a feature to > > isolationtester? I'm willing to work on a bugfix for the bug itself, but > > I've already spent tremendous amounts of

Re: GSoC 2018

On Mon, Dec 18, 2017 at 10:53 AM, Aleksander Alekseev wrote: > Hello hackers, > > Thanks you a lot for your feedback. I modified the project description > to make it more clear that it implies augmenting an existing HA > solution, particularly Stolon, and doesn't imply solving existing > limitatio

Re: AS OF queries

Konstantin Knizhnik wrote: > Please notice that it is necessary to configure postgres in proper way in > order to be able to perform time travels. > If you do not disable autovacuum, then old versions will be just cleaned-up. > If transaction commit timestamps are not tracked, then it is not possi

Re: AS OF queries

On 20 Dec 2017, at 13:48, Konstantin Knizhnik wrote: > > On 20.12.2017 16:12, Laurenz Albe wrote: >> Konstantin Knizhnik wrote: >>> I wonder if Postgres community is interested in supporting time travel >>> queries in PostgreSQL (something like AS OF queries in Oracle: >>> https://docs.oracle.c

Re: AS OF queries

On 20.12.2017 16:12, Laurenz Albe wrote: Konstantin Knizhnik wrote: I wonder if Postgres community is interested in supporting time travel queries in PostgreSQL (something like AS OF queries in Oracle: https://docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_fl.htm). As far as I know somet

Re: AS OF queries

Konstantin Knizhnik wrote: > I wonder if Postgres community is interested in supporting time travel > queries in PostgreSQL (something like AS OF queries in Oracle: > https://docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_fl.htm). > As far as I know something similar is now developed for Ma

Re: Add hint about replication slots when nearing wraparound

On 20 December 2017 at 06:22, Michael Paquier wrote: > prepare_transaction.sgml has a "Caution" block mentioning that it is > unwise to keep 2PC transactions unfinished for a too-long time as it > interferes with VACUUM. In doc/src/sgml/logicaldecoding.sgml, it would > be nice to add the a similar

AS OF queries

I wonder if Postgres community is interested in supporting time travel queries in PostgreSQL (something like AS OF queries in Oracle: https://docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_fl.htm). As far as I know something similar is now developed for MariaDB. It seems to me that it wil

Re: Basebackups reported as idle

On Wed, Dec 20, 2017 at 12:50 PM, Michael Paquier wrote: > On Wed, Dec 20, 2017 at 6:07 PM, Magnus Hagander > wrote: > > What about the attached? > > The new positions look good to me, still aren't you missing the case > where a SQL command is found and exec_replication_command returns > false?

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Tue, Dec 5, 2017 at 1:24 PM, Rajkumar Raghuwanshi wrote: > On Tue, Dec 5, 2017 at 11:04 AM, Rajkumar Raghuwanshi > wrote: >> On Mon, Dec 4, 2017 at 7:34 AM, Ashutosh Bapat >> wrote: >>> I agree, the patch looks longer than expected. I think, it's important >>> to have some testcases to test p

  1   2   >