Re: [HACKERS] some review comments on logical rep code

On Fri, Apr 28, 2017 at 02:13:48PM -0400, Peter Eisentraut wrote: > On 4/28/17 01:01, Noah Misch wrote: > > On Fri, Apr 28, 2017 at 01:55:48PM +0900, Masahiko Sawada wrote: > >> On Fri, Apr 28, 2017 at 1:42 PM, Noah Misch wrote: > >>> On Fri, Apr 28, 2017 at 06:37:09AM +0900,

Re: [HACKERS] Transition tables for triggers on foreign tables and views

Kevin Grittner writes: > Well, I was sort of hoping that the triggers that can now be defined > but can never fire *did* fire at some point. They will fire if you have an INSTEAD OF row-level trigger; the existence of that trigger is what determines whether we implement DML on

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

I wrote: > David Rowley writes: >> (On 29 April 2017 at 02:26, Tom Lane wrote: >> Seems related to the unconditional setting of extra.inner_unique to >> true for JOIN_UNIQUE_INNER jointypes in add_paths_to_joinrel() >> Setting this based on the

Re: [HACKERS] [PROPOSAL] Use SnapshotAny in get_actual_variable_range

On Fri, Apr 28, 2017 at 3:00 PM, Tom Lane wrote: > You are confusing number of tuples in the index, which we estimate from > independent measurements such as the file size, with endpoint value, > which is used for purposes like guessing whether a mergejoin will be > able to

[HACKERS] A design for amcheck heapam verification

It seems like a good next step for amcheck would be to add functionality that verifies that heap tuples have matching index tuples, and that heap pages are generally sane. I've been thinking about a design for this for a while now, and would like to present some tentative ideas before I start

Re: [HACKERS] Declarative partitioning - another take

On Fri, Apr 28, 2017 at 06:29:48PM +0900, Amit Langote wrote: > On 2017/04/28 7:36, David Fetter wrote: > > On Thu, Apr 27, 2017 at 10:30:54AM +0900, Amit Langote wrote: > >> On 2017/04/27 1:52, Robert Haas wrote: > >>> On Tue, Apr 25, 2017 at 10:34 PM, Amit Langote > >>>

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

David Rowley writes: > (On 29 April 2017 at 02:26, Tom Lane wrote: >> It looks like in the case that's giving wrong answers, the mergejoin >> is wrongly getting marked as "Inner Unique". Something's a bit too >> cheesy about that planner logic

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

On Fri, Apr 28, 2017 at 6:59 PM, Tom Lane wrote: > David Rowley writes: > > Did you mean to attach this? > > See the link in Teodor's original message (it's actually a .bz2 file > not a .gz) > Yes, I didn't mean Teodor has renamed it. --

Re: [HACKERS] [PATCH] Push limit to sort through a subquery

> > If you add this to the commitfest app, more people might look at it when > the next commitfest opens. I have added it. https://commitfest.postgresql.org/14/1119/ Also, it might help if you can provide a query/ies with numbers where this > optimization shows improvement. > I can't provide

Re: [HACKERS] Transition tables for triggers on foreign tables and views

On Fri, Apr 28, 2017 at 3:54 PM, Kevin Grittner wrote: > Not firing a table's DML because it was fired off a view would be crazy, should read: Not firing a table's trigger because the trigger is on DML run from a view's INSTEAD OF trigger would be crazy, -- Kevin Grittner

Re: [HACKERS] Transition tables for triggers on foreign tables and views

On Fri, Apr 28, 2017 at 2:42 PM, Tom Lane wrote: > Kevin Grittner writes: >> On Tue, Apr 25, 2017 at 6:17 PM, Thomas Munro >> wrote: >>> For views, aside from the question of transition tables, I noticed >>> that statement

Re: [HACKERS] pgbench tap tests & minor fixes

Here is a v3, with less files. I cannot say I find it better, but it still works. The "command_likes" function has been renamed "command_checks". -- Fabien.diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c index ae36247..749b16d 100644 --- a/src/bin/pgbench/pgbench.c +++

Re: [HACKERS] Transition tables for triggers on foreign tables and views

Kevin Grittner writes: > On Tue, Apr 25, 2017 at 6:17 PM, Thomas Munro > wrote: >> For views, aside from the question of transition tables, I noticed >> that statement triggers don't seem to fire at all with updatable >> views. Surely they

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

On Fri, Apr 28, 2017 at 1:18 AM, Ashutosh Bapat wrote: > For two-way join this works and is fairly straight-forward. I am > assuming that A an B are base relations and not joins. But making it > work for N-way join is the challenge. I don't think it's much

Re: [HACKERS] [PROPOSAL] Use SnapshotAny in get_actual_variable_range

Robert Haas writes: > On Fri, Apr 28, 2017 at 12:12 PM, Tom Lane wrote: >> Maybe we need another type of snapshot that would accept any >> non-vacuumable tuple. I really don't want SnapshotAny semantics here, > I don't, in general, share your

Re: [HACKERS] identity columns

On Fri, Apr 28, 2017 at 2:49 PM, Peter Eisentraut wrote: > On 4/27/17 16:10, Robert Haas wrote: >> I still think you should consider improving the psql output, though. >> Vitaly's examples upthread indicate that for a serial sequence, >> there's psql output

Re: [HACKERS] Transition tables for triggers on foreign tables and views

On Tue, Apr 25, 2017 at 6:17 PM, Thomas Munro wrote: > My colleague Prabhat Sahu reported off list that transition tables > don't work for views. I probably should have thought about that when > I fixed something similar for partitioned tables, and after some >

Re: [HACKERS] identity columns

On 4/27/17 16:10, Robert Haas wrote: > I still think you should consider improving the psql output, though. > Vitaly's examples upthread indicate that for a serial sequence, > there's psql output showing the linkage between the table and sequence > in both directions, but not when GENERATED is

Re: [HACKERS] some review comments on logical rep code

On 4/28/17 01:01, Noah Misch wrote: > On Fri, Apr 28, 2017 at 01:55:48PM +0900, Masahiko Sawada wrote: >> On Fri, Apr 28, 2017 at 1:42 PM, Noah Misch wrote: >>> On Fri, Apr 28, 2017 at 06:37:09AM +0900, Fujii Masao wrote: Pushed. Thanks! >>> >>> Does this close the open

Re: [HACKERS] Dropping a partitioned table takes too long

On Fri, Apr 28, 2017 at 6:12 AM, 高增琦 wrote: > It seems that in 'load_relcache_init_file()', we forget to initialize > 'rd_pdcxt' of relcache. Fixed. Thanks. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via

Re: [HACKERS] Dropping a partitioned table takes too long

On Wed, Apr 26, 2017 at 12:21 PM, Peter Eisentraut wrote: > On 4/26/17 12:15, Robert Haas wrote: >> On Tue, Apr 25, 2017 at 10:05 PM, Amit Langote >> wrote: The attached patch try to replace 'heap_open' with 'LockRelationOid'

Re: [HACKERS] Crash when partition column specified twice

On Fri, Apr 28, 2017 at 7:23 AM, Beena Emerson wrote: > Hello Amit, > > The extra n->is_from_type = false; seems to be added by mistake? > > @@ -11888,6 +11891,8 @@ TableFuncElement: ColId Typename > opt_collate_clause > n->is_local = true; >

Re: [HACKERS] Interval for launching the table sync worker

On 4/27/17 21:20, Masahiko Sawada wrote: > Isn't it better to use != NIL instead as follows? > >else if (table_state != NIL && last_start_times) I'm not a fan of that in general, and it doesn't really add any clarity here. -- Peter Eisentraut http://www.2ndQuadrant.com/

Re: [HACKERS] Interval for launching the table sync worker

On 4/27/17 15:33, Petr Jelinek wrote: > On 27/04/17 21:00, Peter Eisentraut wrote: >> On 4/27/17 06:47, Petr Jelinek wrote: >>> One thing I am missing in your patch however is cleanup of entries for >>> relations that finished sync. I wonder if it would be enough to just >>> destroy the hash when

Re: [HACKERS] [PROPOSAL] Use SnapshotAny in get_actual_variable_range

On Fri, Apr 28, 2017 at 12:12 PM, Tom Lane wrote: > Robert Haas writes: >> On Thu, Apr 27, 2017 at 5:22 PM, Tom Lane wrote: >>> How so? Shouldn't the indexscan go back and mark such tuples dead in >>> the index, such that they'd be

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

(On 29 April 2017 at 02:26, Tom Lane wrote: > Alexander Korotkov writes: >> I've reproduced this bug on d981074c. >> On default config, after loading example.sql.bz2 and VACUUM ANALYZE, query >> result is OK. >> But with seqscan and hashjoin

Re: [HACKERS] Logical replication in the same cluster

On Thu, Apr 27, 2017 at 4:08 AM, Petr Jelinek wrote: > Back when writing the original patch set, I was also playing with the > idea of having CREATE SUBSCRIPTION do multiple committed steps in > similar fashion to CREATE INDEX CONCURRENTLY but that leaves mess behind

Re: [HACKERS] Declarative partitioning - another take

On Fri, Apr 28, 2017 at 2:13 AM, Amit Langote wrote: > It seems to me that there is no difference in behavior between > inheritance-based and declarative partitioning as far as statement-level > triggers are concerned (at least currently). In both the cases, we

Re: [HACKERS] [PROPOSAL] Use SnapshotAny in get_actual_variable_range

> What I'm thinking of is the regular indexscan that's done internally > by get_actual_variable_range, not whatever ends up getting chosen as > the plan for the user query. I had supposed that that would kill > dead index entries as it went, but maybe that's not happening for > some reason.

Re: [HACKERS] [PROPOSAL] Use SnapshotAny in get_actual_variable_range

Robert Haas writes: > On Thu, Apr 27, 2017 at 5:22 PM, Tom Lane wrote: >> How so? Shouldn't the indexscan go back and mark such tuples dead in >> the index, such that they'd be visited this way only once? If that's >> not happening, maybe we should

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

David Rowley writes: > Did you mean to attach this? See the link in Teodor's original message (it's actually a .bz2 file not a .gz) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

On 29 April 2017 at 00:45, Alexander Korotkov wrote: > On default config, after loading example.sql.bz2 and VACUUM ANALYZE, query > result is OK. Hi, Did you mean to attach this? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development,

Re: [HACKERS] [PROPOSAL] Use SnapshotAny in get_actual_variable_range

On Thu, Apr 27, 2017 at 5:22 PM, Tom Lane wrote: >>> But if we delete many rows from beginning or end of index, it would be >>> very expensive too because we will fetch each dead row and reject it. > >> Yep, and I've seen that turn into a serious problem in production. > > How

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

Ah, thanks for the clue about enable_hashjoin, because it wasn't reproducing for me as stated. I missed tweaked config, sorry -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- Sent via pgsql-hackers mailing

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

Alexander Korotkov writes: > I've reproduced this bug on d981074c. > On default config, after loading example.sql.bz2 and VACUUM ANALYZE, query > result is OK. > But with seqscan and hashjoin disabled, query returns 0 rows. Ah, thanks for the clue about

Re: [HACKERS] vcregress support for single TAP tests

On 04/26/2017 10:32 PM, Peter Eisentraut wrote: > On 4/23/17 17:09, Andrew Dunstan wrote: >> Here's a patch that will allow calling vcregress.pl to run a single TAP >> test set. It would work like this: >> >> >> vcregress.pl src/test/recover true >> >> >> The second argument if true (in the

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

On Fri, Apr 28, 2017 at 12:48 PM, Teodor Sigaev wrote: > Both 9.6 and 10devel are affected to addiction of query result on seqscan >> variable. >> > Oops, I was too nervious, 9.6 is not affected to enable_seqscan setting. > But it doesn't push down condition too. I've

Re: [HACKERS] scram and \password

On 04/28/2017 07:49 AM, Noah Misch wrote: On Fri, Apr 21, 2017 at 11:04:14PM +0300, Heikki Linnakangas wrote: I'll continue reviewing the rest of the patch on Monday, but [...] This PostgreSQL 10 open item is past due for your status update. Kindly send a status update within 24 hours, and

Re: [HACKERS] Assertion failure in REL9_5_STABLE

On Wed, Apr 19, 2017 at 11:19 AM, Andrew Gierth wrote: > > "Pavan" == Pavan Deolasee writes: > > Pavan> I am attaching a patch that throws a similar ERROR during > Pavan> planning even for 9.5. AFAICS in presence of grouping sets, we

Re: [HACKERS] On How To Shorten the Steep Learning Curve Towards PG Hacking...

On 28 Apr. 2017 17:04, "Kang Yuzhe" wrote: Hello Simon, The journey that caused and is causing me a lot of pain is finding my way in PG development. Complex Code Reading like PG. Fully understanding the science of DBMS Engines: Query Processing, Storage stuff, Transaction

Re: [HACKERS] Crash when partition column specified twice

Hello Amit, The extra n->is_from_type = false; seems to be added by mistake? @@ -11888,6 +11891,8 @@ TableFuncElement: ColId Typename opt_collate_clause n->is_local = true; n->is_not_null = false; n->is_from_type = false; +

Re: [HACKERS] Dropping a partitioned table takes too long

It seems that in 'load_relcache_init_file()', we forget to initialize 'rd_pdcxt' of relcache. 2017-04-27 0:33 GMT+08:00 Robert Haas : > On Wed, Apr 26, 2017 at 12:22 PM, Tom Lane wrote: > > Robert Haas writes: > >> On Tue, Apr

Re: [HACKERS] Cached plans and statement generalization

On 26.04.2017 13:46, Pavel Stehule wrote: I attach new patch which allows to limit the number of autoprepared statements (autoprepare_limit GUC variable). Also I did more measurements, now with several concurrent connections and read-only statements. Results of pgbench

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

Both 9.6 and 10devel are affected to addiction of query result on seqscan variable. Oops, I was too nervious, 9.6 is not affected to enable_seqscan setting. But it doesn't push down condition too. -- Teodor Sigaev E-mail: teo...@sigaev.ru

Re: [HACKERS] Interval for launching the table sync worker

On Fri, Apr 28, 2017 at 5:26 PM, Kyotaro HORIGUCHI wrote: > At Fri, 28 Apr 2017 10:20:48 +0900, Masahiko Sawada > wrote in

Re: [HACKERS] Declarative partitioning - another take

On 2017/04/28 7:36, David Fetter wrote: > On Thu, Apr 27, 2017 at 10:30:54AM +0900, Amit Langote wrote: >> On 2017/04/27 1:52, Robert Haas wrote: >>> On Tue, Apr 25, 2017 at 10:34 PM, Amit Langote >>> wrote: FWIW, I too prefer the latter, that is, fire only the

[HACKERS] convert EXSITS to inner join gotcha and bug

Hi! Seems, there two issues: 1) Sometime conditions which for a first glance could be pushed down to scan are leaved as join quals. And it could be a ~30 times performance loss. 2) Number of query result depend on enabe_seqscan variable. The query explain analyze SELECT * FROM

Re: [HACKERS] On How To Shorten the Steep Learning Curve Towards PG Hacking...

Hello Simon, The journey that caused and is causing me a lot of pain is finding my way in PG development. Complex Code Reading like PG. Fully understanding the science of DBMS Engines: Query Processing, Storage stuff, Transaction Management and so on... Anyway as you said, the rough estimation

Re: [HACKERS] Declarative partitioning - another take

Hi Rajkumar, On 2017/04/28 17:11, Rajkumar Raghuwanshi wrote: > On Fri, Apr 28, 2017 at 11:43 AM, Amit Langote < >> Updated patch attached. > > I have applied given patch, could see below behaviour with statement > trigger. > > When trying to delete value within partition range, triggers got

Re: [HACKERS] Interval for launching the table sync worker

At Fri, 28 Apr 2017 10:20:48 +0900, Masahiko Sawada wrote in

Re: [HACKERS] Declarative partitioning - another take

On Fri, Apr 28, 2017 at 11:43 AM, Amit Langote < langote_amit...@lab.ntt.co.jp> wrote: > Updated patch attached. > Hi Amit, I have applied given patch, could see below behaviour with statement trigger. When trying to delete value within partition range, triggers got fired (with zero row

Re: [HACKERS] subscription worker doesn't start immediately on eabled

At Fri, 28 Apr 2017 06:43:19 +0900, Fujii Masao wrote in

Re: [HACKERS] .pgpass's behavior has changed

On Fri, Apr 28, 2017 at 4:54 PM, Kyotaro HORIGUCHI wrote: > I noticed that the precedence between host and hostaddr in a > connection string is reversed in regard to .pgpass lookup in > devel. > > For example the following connection string uses a .pgpass entry >

[HACKERS] .pgpass's behavior has changed

Hello. I noticed that the precedence between host and hostaddr in a connection string is reversed in regard to .pgpass lookup in devel. For example the following connection string uses a .pgpass entry with "127.0.0.1", not "hoge". "host=hoge hostaddr=127.0.0.1 port=5432 dbname=postgres" This

[HACKERS] PQhost may return socket dir for network connection

Hello. As the subject, PQhost() seems to be forgeting about the case where only hostaddr is specified in a connection string. PQhost() returns /var/run/postgresql or such for a connection made from the connection string "hostaddr=127.0.0.1 port=5432 dbname=postgres". I don't believe this

Re: [HACKERS] Partition-wise aggregation/grouping

Jeevan Chalke wrote: > On Thu, Apr 27, 2017 at 4:53 PM, Antonin Houska wrote: > > > Robert Haas wrote: > > > Well, I'm confused. I see that there's a relationship between what > > > Antonin is trying to do and what Jeevan

[HACKERS] Bug in prepared statement cache invalidation?

Hi hackers, I find out that now Postgres correctly invalidates prepared plans which directly depend on altered relation, but doesn't invalidate plans having transitive (indirect) dependencies. Is it a bug or feature? postgres=# create table foo(x integer); CREATE TABLE postgres=# select *

Re: [HACKERS] Merge join for GiST

Hi, hackers! I've adapted crossmatch join from pgSphere to cube for performance tests. I've placed spatial join code here https://github.com/Octonica/postgres/blob/spatialjoin/contrib/cube/spatialjoin.c and node code here

Re: [HACKERS] Declarative partitioning - another take

Thanks for taking a look. On 2017/04/28 5:24, Robert Haas wrote: > On Wed, Apr 26, 2017 at 9:30 PM, Amit Langote > wrote: >>> Do we need to update the documentation? >> >> Yes, I think we should. How about as in the attached? > > Looks reasonable, but I was