Re: Postgres with pthread

> But it is a theory. The main idea of this prototype was to prove or disprove > this expectation at practice. > But please notice that it is very raw prototype. A lot of stuff is not > working yet. > And supporting all of exited Postgres functionality requires > much more efforts (and even more

Re: [HACKERS] Runtime Partition Pruning

On Wed, Dec 6, 2017 at 1:21 PM, David Rowley wrote: > On 2 December 2017 at 08:04, Robert Haas wrote: >> On Fri, Dec 1, 2017 at 6:20 AM, Beena Emerson >> wrote: >>> David Q1: >>> postgres=# explain analyse execute ab_q1 (3,3); --const >>>QUERY PL

Re: Speeding up pg_upgrade

On Tue, Dec 5, 2017 at 09:30:53AM -0500, Stephen Frost wrote: > > > The other concern is if there's changes made to the catalogs by non-DDL > > > activity that needs to be addressed too (logical replication?); nothing > > > definite springs to mind off-hand for me, but perhaps others will think >

Re: [HACKERS] postgres_fdw super user checks

On Wed, Dec 6, 2017 at 1:35 AM, Robert Haas wrote: >> >> "Only superusers may connect to foreign servers without password >> authentication, so always specify the password >> option for user mappings that may be used by non-superusers." But >> which user mappings may be used by non-superusers can

Re: Add PGDLLIMPORT lines to some variables

On 5 December 2017 at 22:49, Robert Haas wrote: > > Committed with these additions. Please check that I haven't messed > anything up. > > Looks good to me. For the record the commit is commit c572599c65bfe0387563233faabecd2845073538 Author: Robert Haas Date: Tue Dec 5 09:23:57 2017 -0500

Re: [HACKERS] INSERT ON CONFLICT and partitioned tables

On 2017/12/02 2:57, Robert Haas wrote: > On Fri, Dec 1, 2017 at 2:44 AM, Amit Langote > wrote: >> I forgot to consider the fact that mtstate could be NULL in >> ExecSetupPartitionTupleRouting(), so would result in dereferencing NULL >> pointer when called from CopyFrom(), which fixed in the attach

Re: Postgres with pthread

On 7 December 2017 at 11:44, Tsunakawa, Takayuki < tsunakawa.ta...@jp.fujitsu.com> wrote: > From: Craig Ringer [mailto:cr...@2ndquadrant.com] > > I'd personally expect that an immediate conversion would result > > in very > > little speedup, a bunch of code deleted, a bunch of complexi

RE: Postgres with pthread

From: Craig Ringer [mailto:cr...@2ndquadrant.com] > I'd personally expect that an immediate conversion would result > in very > little speedup, a bunch of code deleted, a bunch of complexity > added. And it'd still be massively worthwhile, to keep medium to > long > term com

Re: Postgres with pthread

On 7 December 2017 at 05:58, Thomas Munro wrote: > > Using a ton of thread local variables may be a useful stepping stone, > but if we want to be able to separate threads/processes from sessions > eventually then I guess we'll want to model sessions as first class > objects and pass them around e

Re: Postgres with pthread

On 7 December 2017 at 01:17, Andres Freund wrote: > > > I think you've done us a very substantial service by pursuing > > this far enough to get some quantifiable performance results. > > But now that we have some results in hand, I think we're best > > off sticking with the architecture we've g

Re: [HACKERS] [COMMITTERS] pgsql: Fix freezing of a dead HOT-updated tuple

On Thu, Dec 7, 2017 at 1:21 AM, Alvaro Herrera wrote: > Put together, I propose the attached delta for 0001. I have been looking at Andres' 0001 and your tweaks here for some time since yesterday... I have also performed sanity checks using all the scripts that have accumulated on my archives fo

Re: pgsql: When VACUUM or ANALYZE skips a concurrently dropped table, log i

On Wed, Dec 6, 2017 at 4:31 PM, Tom Lane wrote: > Well, I think it's a minute per query not per whole test script. But in > any case, if it's taking a longer time than any other isolation test on > the CLOBBER_CACHE_ALWAYS critters, then it's also taking a proportionately > longer time than any o

Add %r substitution for psql prompts to show recovery status

Hi Attached patch adds an "%r" substitution for psql prompts to show recovery status. Specifically it displays an "&" (ampersand) if the server is in recovery, otherwise nothing, e.g.: postgres=&# SELECT foo; Why is this useful? Because I find myself messing about with replication clusters

Re: User defined data types in Logical Replication

On Thu, Dec 7, 2017 at 12:23 AM, Dang Minh Huong wrote: > Hi Sawada-san, > > Sorry for my late response. > > On 2017/12/05 0:11, Masahiko Sawada wrote: > > There is one more case that user-defined data type is not supported in > Logical Replication. > That is when remote data type's name does not

Re: compress method for spgist - 2

On 06.12.2017 21:49, Alexander Korotkov wrote: On Wed, Dec 6, 2017 at 6:08 PM, Nikita Glukhov mailto:n.glu...@postgrespro.ru>> wrote: On 05.12.2017 23:59, Alexander Korotkov wrote: On Tue, Dec 5, 2017 at 1:14 PM, Darafei Praliaskouski mailto:m...@komzpa.net>> wrote: The

Logical replication without a Primary Key

-Hackers, In the docs it says: " If the table does not have any suitable key, then it can be set to replica identity“full”, which means the entire row becomes the key. " How does that work? Is it using one of the hidden columns on a row? Thanks, JD -- Command Prompt, Inc. || http://the

Re: Signals in a BGW

On 12/04/2017 08:03 PM, Craig Ringer wrote: > pglogical sets up its own handler 'handle_sigterm'. However, it now does > much the same as src/backend/tcop/postgres.c's 'die' function. ... > We used to have our own signal> handling but it gets seriously messy when > you're calling into arbitrary >

Re: es_query_dsa is broken

On Wed, Dec 6, 2017 at 10:16 PM, Amit Kapila wrote: > On Wed, Dec 6, 2017 at 1:14 AM, Robert Haas wrote: >> On Tue, Dec 5, 2017 at 7:24 AM, Amit Kapila wrote: >>> + EState *estate = gatherstate->ps.state; >>> + >>> + /* Install our DSA area while executing the plan. */ >>> + estate->es_query_dsa

Re: [HACKERS] Transaction control in procedures

On Wed, Dec 6, 2017 at 8:41 AM, Peter Eisentraut wrote: > On 12/5/17 13:33, Robert Haas wrote: >> On Tue, Dec 5, 2017 at 1:25 PM, Peter Eisentraut >> wrote: >>> I think ROLLBACK in a cursor loop might not make sense, because the >>> cursor query itself could have side effects, so a rollback would

Re: Postgres with pthread

On Thu, Dec 7, 2017 at 6:08 AM, Andres Freund wrote: > On 2017-12-06 19:40:00 +0300, Konstantin Knizhnik wrote: >> As far as I remember, several years ago when implementation of intra-query >> parallelism was just started there was discussion whether to use threads or >> leave traditional Postgres

Re: pgsql: When VACUUM or ANALYZE skips a concurrently dropped table, log i

Robert Haas writes: > On Wed, Dec 6, 2017 at 12:57 PM, Tom Lane wrote: >> What appears to be happening is that a database-wide ANALYZE takes more >> than a minute under CLOBBER_CACHE_ALWAYS, causing isolationtester.c's >> hardwired one-minute timeout to trigger. > Is it really our policy that no

Re: Accessing base table relational names via RelOptInfo

On 7 December 2017 at 09:26, Walter Cai wrote: > I want to be able to programmatically access the relation names inside from > inside the calc_joinrel_size_estimate method (in costsize.c) using the > RelOptInfo *outer_rel, RelOptInfo *inner_rel arguments. I'm pretty sure I > need to use the Relids

Re: pgsql: When VACUUM or ANALYZE skips a concurrently dropped table, log i

On Wed, Dec 6, 2017 at 12:57 PM, Tom Lane wrote: > What appears to be happening is that a database-wide ANALYZE takes more > than a minute under CLOBBER_CACHE_ALWAYS, causing isolationtester.c's > hardwired one-minute timeout to trigger. > > While you could imagine doing something to get around th

Accessing base table relational names via RelOptInfo

Hi, I hope this is the appropriate list to send this to. *Context:* I (grad student) am trying to insert my own cardinality estimates into the optimizer *What I need to do to get there:* I want to be able to programmatically access the relation names inside from inside the calc_joinrel_size_es

Re: [HACKERS] [COMMITTERS] pgsql: Fix freezing of a dead HOT-updated tuple

Andres Freund wrote: > I've played around quite some with the attached patch. So far, after > applying the second patch, neither VACUUM nor VACUUM FULL / CLUSTER make > the situation worse for already existing corruption. HOT pruning can > change the exact appearance of existing corruption a bit,

Re: compress method for spgist - 2

On Wed, Dec 6, 2017 at 6:08 PM, Nikita Glukhov wrote: > On 05.12.2017 23:59, Alexander Korotkov wrote: > > On Tue, Dec 5, 2017 at 1:14 PM, Darafei Praliaskouski > wrote: > >> The following review has been posted through the commitfest application: >> make installcheck-world: not tested >> Imple

Re: pow support for pgbench

Raúl Marín Rodríguez wrote: > I don't want to go too deep into it, but you get stuff like this: > > Select pow(2.0, -3)::text = pow(2, -3)::text; > ?column? > -- > f Indeed, to me, that has turned out to be the most intriguing part of the whole thread. Needs to be in some SQL subtletie

Re: Usage of epoch in txid_current

Hi, On 2017-12-06 17:39:09 +0530, Amit Kapila wrote: > We are using ShmemVariableCache->nextXid at many places so always > converting/truncating it to 32-bit number before using seems slightly > awkward, so we can think of using a separate nextBigXid 64bit number > as well. -many It's not actual

Re: Postgres with pthread

Hi, On 2017-12-06 12:28:29 -0500, Robert Haas wrote: > > Possibly we even want to continue having various > > processes around besides that, the most interesting cases involving > > threads are around intra-query parallelism, and pooling, and for both a > > hybrid model could be beneficial. > > I

Re: Postgres with pthread

On Wed, Dec 6, 2017 at 12:08 PM, Andres Freund wrote: >> 4. Rewrite file descriptor cache to be global (shared by all threads). > > That one I'm very unconvinced of, that's going to add a ton of new > contention. It might be OK on systems where we can use pread()/pwrite(). Otherwise it's going to

Re: Postgres with pthread

On 12/06/2017 06:08 PM, Andres Freund wrote: I think the biggest problem with doing this for real is that it's a huge project, and that it'll take a long time. An additional issue is that this could break a lot of extensions and in a way that it is not apparent at compile time. This means we m

Re: Postgres with pthread

> "barely a 50% speedup" - Hah. I don't believe the numbers, but that'd be > huge. They are numbers derived from a benchmark that any sane person would be using a connection pool for in a production environment, but impressive if true none the less.

Re: [HACKERS] Walsender timeouts and large transactions

On 05/12/17 21:07, Robert Haas wrote: > On Mon, Dec 4, 2017 at 10:59 PM, Craig Ringer wrote: >> To me it looks like it's time to get this committed, marking as such. > > This version has noticeably more code rearrangement than before, and > I'm not sure that is actually buying us anything. Why n

Re: Postgres with pthread

Hi, On 2017-12-06 11:53:21 -0500, Tom Lane wrote: > Konstantin Knizhnik writes: > However, if I guess at which numbers are supposed to be what, > it looks like even the best case is barely a 50% speedup. "barely a 50% speedup" - Hah. I don't believe the numbers, but that'd be huge. > That woul

Re: Postgres with pthread

On Wed, Dec 6, 2017 at 11:53 AM, Tom Lane wrote: > barely a 50% speedup. I think that's an awfully strange choice of adverb. This is, by its authors own admission, a rough cut at this, probably with very little of the optimization that could ultimately done, and it's already buying 50% on some t

Re: Postgres with pthread

Hi! On 2017-12-06 19:40:00 +0300, Konstantin Knizhnik wrote: > As far as I remember, several years ago when implementation of intra-query > parallelism was just started there was discussion whether to use threads or > leave traditional Postgres process architecture. The decision was made to > leav

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

On Wed, Dec 6, 2017 at 9:54 AM, Tom Lane wrote: > Maybe track "worker is known to have launched" in the leader's state > someplace? That's probably one piece of it, yes. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: views

Andrzej Barszcz writes: > I would be happy when checkRuleResultList in rewriteDefine.c lost so strict > conditions for returning clause. Are there any reasons to have such > restriction for views ? Uh, so that the results of a query that invokes the rule are well-defined? If you think that for y

Re: Postgres with pthread

Here it is formatted a little better. ​ So a little over 50% performance improvement for a couple of the test cases. On Wed, Dec 6, 2017 at 11:53 AM, Tom Lane wrote: > Konstantin Knizhnik writes: > > Below are some results (1000xTPS) of select-only (-S) pgbench with scale > > 100 at my des

views

Dear hackers, I would be happy when checkRuleResultList in rewriteDefine.c lost so strict conditions for returning clause. Are there any reasons to have such restriction for views ? What I mean in short: create view ... as select MAIN_TABLE ... joins ... 1. Updating view means updating MAIN_TAB

Re: Postgres with pthread

Konstantin Knizhnik writes: > Below are some results (1000xTPS) of select-only (-S) pgbench with scale > 100 at my desktop with quad-core i7-4770 3.40GHz and 16Gb of RAM: > Connections    Vanilla/default   Vanilla/prepared > pthreads/defaultpthreads/prepared > 10                    100 191 

Postgres with pthread

Hi hackers, As far as I remember, several years ago when implementation of intra-query parallelism was just started there was discussion whether to use threads or leave traditional Postgres process architecture. The decision was made to leave processes. So now we have bgworkers, shared messag

Re: ALTER TABLE ADD COLUMN fast default

On 12/06/2017 11:05 AM, Tom Lane wrote: > Andrew Dunstan writes: >> On 12/06/2017 10:16 AM, Tom Lane wrote: >>> I'm quite disturbed both by the sheer invasiveness of this patch >>> (eg, changing the API of heap_attisnull()) and by the amount of logic >>> it adds to fundamental tuple-deconstructi

Re: [HACKERS] [COMMITTERS] pgsql: Fix freezing of a dead HOT-updated tuple

I think you've done a stellar job of identifying what the actual problem was. I like the new (simpler) coding of that portion of HeapTupleSatisfiesVacuum. freeze-the-dead is not listed in isolation_schedule; an easy fix. I confirm that the test crashes with an assertion failure without the code f

Re: ALTER TABLE ADD COLUMN fast default

Andrew Dunstan writes: > On 12/06/2017 10:16 AM, Tom Lane wrote: >> I'm quite disturbed both by the sheer invasiveness of this patch >> (eg, changing the API of heap_attisnull()) and by the amount of logic >> it adds to fundamental tuple-deconstruction code paths. I think >> we'll need to ask som

Re: ALTER TABLE ADD COLUMN fast default

On 12/06/2017 10:16 AM, Tom Lane wrote: > Andrew Dunstan writes: >> Attached is a patch for $subject. It's based on Serge Reilau's patch of >> about a year ago, taking into account comments made at the time. with >> bitrot removed and other enhancements such as documentation. >> Essentially it s

Re: User defined data types in Logical Replication

Hi Sawada-san, Sorry for my late response. On 2017/12/05 0:11, Masahiko Sawada wrote: There is one more case that user-defined data type is not supported in Logical Replication. That is when remote data type's name does not exist in SUBSCRIBE. In relation.c:logicalrep_typmap_gettypname We sea

Re: ALTER TABLE ADD COLUMN fast default

Andrew Dunstan writes: > Attached is a patch for $subject. It's based on Serge Reilau's patch of > about a year ago, taking into account comments made at the time. with > bitrot removed and other enhancements such as documentation. > Essentially it stores a value in pg_attribute that is used when

Re: compress method for spgist - 2

On 05.12.2017 23:59, Alexander Korotkov wrote: On Tue, Dec 5, 2017 at 1:14 PM, Darafei Praliaskouski > wrote: The following review has been posted through the commitfest application: make installcheck-world:  not tested Implements feature:       not teste

Re: [HACKERS] Custom compression methods

On Fri, 1 Dec 2017 21:47:43 +0100 Tomas Vondra wrote: > > +1 to do the rewrite, just like for other similar ALTER TABLE commands Ok. What about the following syntax: ALTER COLUMN DROP COMPRESSION - removes compression from the column with the rewrite and removes related compression options, s

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

Robert Haas writes: > I'm not in love with that part of the fix; the other parts of that if > statement are just testing variables, and a function call that takes > and releases an LWLock is a lot more expensive. Furthermore, that > test will often be hit in practice, because we'll often arrive a

Re: [HACKERS] Transaction control in procedures

On 12/5/17 13:33, Robert Haas wrote: > On Tue, Dec 5, 2017 at 1:25 PM, Peter Eisentraut > wrote: >> I think ROLLBACK in a cursor loop might not make sense, because the >> cursor query itself could have side effects, so a rollback would have to >> roll back the entire loop. That might need more re

ALTER TABLE ADD COLUMN fast default

Attached is a patch for $subject. It's based on Serge Reilau's patch of about a year ago, taking into account comments made at the time. with bitrot removed and other enhancements such as documentation. Essentially it stores a value in pg_attribute that is used when the stored tuple is missing th

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

On Wed, Dec 6, 2017 at 1:43 AM, Amit Kapila wrote: > Looks good to me. Committed and back-patched to 9.4 where dynamic background workers were introduced. >> Barring objections, I'll commit and >> back-patch this; then we can deal with the other part of this >> afterward. > > Sure, I will rebase

Re: [HACKERS] [COMMITTERS] pgsql: Fix freezing of a dead HOT-updated tuple

On Wed, Dec 6, 2017 at 5:03 PM, Andres Freund wrote: > Ping. I'm a bit surprised that a bug fixing a significant data > corruption issue has gotten no reviews at all. Note that I was planning to look at this problem today and tomorrow my time, getting stuck for CF handling last week and conferen

Re: Usage of epoch in txid_current

On Tue, Dec 5, 2017 at 11:15 PM, Andres Freund wrote: > On 2017-12-05 16:21:27 +0530, Amit Kapila wrote: > >> Okay, it is quite strange that we haven't discovered this problem till >> now. I think we should do something to fix it. One idea is that we >> track epoch change in shared memory (proba

Re: Jsonb transform for pl/python

Hello, fixed the issues: 1. Rising errors when invalid object being transformed. 2. We don't rise the exception when transforming range(3) only in python 2. In third one it is an error. Please, find the 4-th version of the patch in attachments to this message. -- Anthony Bykov Postgres Professiona

Re: [HACKERS] Parallel Append implementation

On 6 December 2017 at 04:01, Robert Haas wrote: > On Tue, Nov 28, 2017 at 6:02 AM, amul sul wrote: >> Here are the changes I did on v21 patch to handle crash reported by >> Rajkumar[1]: >> >> diff --git a/src/backend/executor/nodeAppend.c >> b/src/backend/executor/nodeAppend.c >> index e3b17cf0e

fixed tuple descs (was JIT compiling expressions/deform)

Hi, One part of the work to make JITing worth it's while is JITing tuple deforming. That's currently often the biggest consumer of time, and if not most often in the top entries. My experimentation shows that tuple deforming is primarily beneficial when it happens as *part* of jit compiling expre

Re: es_query_dsa is broken

On Wed, Dec 6, 2017 at 1:14 AM, Robert Haas wrote: > On Tue, Dec 5, 2017 at 7:24 AM, Amit Kapila wrote: >> + EState *estate = gatherstate->ps.state; >> + >> + /* Install our DSA area while executing the plan. */ >> + estate->es_query_dsa = gatherstate->pei->area; >> outerTupleSlot = ExecProcNod

Re: [HACKERS] [COMMITTERS] pgsql: Fix freezing of a dead HOT-updated tuple

On 2017-11-20 11:18:45 -0800, Andres Freund wrote: > Hi, > > On 2017-11-13 19:03:41 -0800, Andres Freund wrote: > > Hi, > > > > On 2017-11-03 07:53:30 -0700, Andres Freund wrote: > > > Here's that patch. I've stared at this some, and Robert did too. Robert > > > mentioned that the commit message