Re: [HACKERS] [TRAP: FailedAssertion] causing server to crash

Hi Craig, I had done a fresh initdb,the default parameter configuration was used. I was setting few set of parameters while startup by the below command. ./postgres -d postgres -c shared_buffers=$shared_bufs -N 200 -c min_wal_size=15GB -c max_wal_size=20GB -c checkpoint_timeout=900 -c maintenance

Re: [HACKERS] Dealing with logical replication

> On 19 July 2017 at 16:34, Tatsuo Ishii wrote: > >> Now that we are going to have logical replication in PostgreSQL 10, I >> have started thinking how Pgpool-II can deal with it. For example, the >> logical replication does not replicate DDLs. Isn't it convenient for >> users to do it automatica

Re: [HACKERS] [TRAP: FailedAssertion] causing server to crash

On 20 July 2017 at 15:00, Neha Sharma wrote: > Hi Craig, > > I had done a fresh initdb,the default parameter configuration was used. I > was setting few set of parameters while startup by the below command. > > ./postgres -d postgres -c shared_buffers=$shared_bufs -N 200 -c > min_wal_size=15GB -c

[HACKERS] xlogfilename

I study source code about wal, and have a question about xlog file name . what does 00010001 mean? Someone says that it means tli logid segno. I don't understand.

Re: [HACKERS] xlogfilename

I think you may reference to function: pg_xlogfile_name in src/backend/access/transam/xlogfuncs.c, it use XLogFileName defined in src/include/access/xlog_internal.h #define XLogFileName(fname, tli, logSegNo) \ snprintf(fname, MAXFNAMELEN, "%08X%08X%08X", tli, \ (uint32

Re: [HACKERS] xlogfilename

On Thu, Jul 20, 2017 at 10:58 AM, DEV_OPS wrote: > I think you may reference to function: pg_xlogfile_name in > src/backend/access/transam/xlogfuncs.c, it use XLogFileName defined in > src/include/access/xlog_internal.h > > #define XLogFileName(fname, tli, logSegNo) \ > snprintf(fname, MA

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

On Thu, Jul 20, 2017 at 11:46 AM, Amit Langote wrote: > On 2017/07/20 15:05, Ashutosh Bapat wrote: >> On Wed, Jul 19, 2017 at 9:54 AM, Rafia Sabih >> wrote: >>> >>> Partition information: >>> Type of partitioning - single column range partition >>> Tables partitioned - Lineitem and orders >>> >>>

Re: [HACKERS] [TRAP: FailedAssertion] causing server to crash

Regards, Neha Sharma On Thu, Jul 20, 2017 at 1:28 PM, Craig Ringer wrote: > On 20 July 2017 at 15:00, Neha Sharma > wrote: > >> Hi Craig, >> >> I had done a fresh initdb,the default parameter configuration was used. I >> was setting few set of parameters while startup by the below command. >> >

Re: [HACKERS] Adding -E switch to pg_dumpall

Hello Michaël-san, Attached is a patch to add support for this switch. I am parking that in the next CF. I'm in favor of this feature for consistency with pg_dump, and as the environment variable workaround is not specially elegant and can induce issues of its own. Patch applies and compi

[HACKERS] Proposal about a "deep" versions for some jsonb functions

Hi As far as I know, since 9.5 we're missing some convenient features, namely a deepversion of `jsonb_concat` and `jsonb_minus`. There are already few feature requests about `jsonb_minus` (see [1], [2]) and a lot of confusion and requests about a deep version of `jsonb_concat`. From my point of vi

Re: [HACKERS] Causal reads take II

On Sun, Jun 25, 2017 at 2:36 AM, Simon Riggs wrote: > On 3 January 2017 at 01:43, Thomas Munro > wrote: > >> Here is a new version of my "causal reads" patch (see the earlier >> thread from the 9.6 development cycle[1]), which provides a way to >> avoid stale reads when load balancing with strea

Re: [HACKERS] [PATCH] pageinspect function to decode infomasks

I had a quick look into this patch and also tested it and following are my observations. 1) I am seeing a server crash when passing any non meaningful value for t_infomask2 to heap_infomask_flags(). postgres=# SELECT heap_infomask_flags(2816, 3); server closed the connection unexpectedly This

[HACKERS] Mishandling of WCO constraints in direct foreign table modification

Here is an example for $subject using postgres_fdw: postgres=# create foreign table foreign_tbl (a int, b int) server loopback options (table_name 'base_tbl'); CREATE FOREIGN TABLE postgres=# create view rw_view as select * from foreign_tbl where a < b with check option; CREATE VIEW postgres=

Re: [HACKERS] <> join selectivity estimate question

On Thu, Jul 20, 2017 at 11:04 AM, Thomas Munro wrote: > On Fri, Jun 2, 2017 at 4:16 AM, Tom Lane wrote: >> I don't think it does really. The thing about a <> semijoin is that it >> will succeed unless *every* join key value from the inner query is equal >> to the outer key value (or is null). T

[HACKERS] [PATCH] A hook for session start

Hi, Currently, PostgreSQL doen't have a hook triggered at session start. Although we already have ClientAuthentication_hook, this is triggered during authentication, so we can not access the database. If we have a hook triggerd only once at session start, we may do something useful on the sessio

Re: [HACKERS] [PATCH] pageinspect function to decode infomasks

On 20 Jul. 2017 19:09, "Ashutosh Sharma" wrote: I had a quick look into this patch and also tested it and following are my observations. Thanks very much. I'll expand the tests to cover various normal and nonsensical masks and combinations and fix the identified issues. This was a quick morni

Re: [HACKERS] <> join selectivity estimate question

On Thu, Jul 20, 2017 at 11:47 PM, Ashutosh Bapat wrote: > On Thu, Jul 20, 2017 at 11:04 AM, Thomas Munro > wrote: >> On Fri, Jun 2, 2017 at 4:16 AM, Tom Lane wrote: >>> I don't think it does really. The thing about a <> semijoin is that it >>> will succeed unless *every* join key value from the

[HACKERS] pg_upgrade failed if view contain natural left join condition

Steps to reproduce - v9.6 postgres=# create table t(n int); CREATE TABLE postgres=# create table t1(a int); CREATE TABLE postgres=# create view ttt1 as SELECT e.n FROM t e NATURAL LEFT JOIN t1 d; CREATE VIEW v10 - run pg_upgrade - going to fail ,with this error - " pg_restore: creating TABLE

Re: [HACKERS] <> join selectivity estimate question

On Thu, Jul 20, 2017 at 5:30 PM, Thomas Munro wrote: > On Thu, Jul 20, 2017 at 11:47 PM, Ashutosh Bapat > wrote: >> On Thu, Jul 20, 2017 at 11:04 AM, Thomas Munro >> wrote: >>> On Fri, Jun 2, 2017 at 4:16 AM, Tom Lane wrote: I don't think it does really. The thing about a <> semijoin is t

[HACKERS] pg_upgrade failed if view is based on sequence

Steps to reproduce - v9.6 postgres=# create sequence seq_9166 start 1 increment 1; CREATE SEQUENCE postgres=# create or replace view v3_9166 as select * from seq_9166; CREATE VIEW v10 run pg_upgrade , going to fail with this error command: "./pg_restore" --host /home/centos/pg10_14july/post

Re: [HACKERS] [PATCH] A hook for session start

On Thu, Jul 20, 2017 at 8:47 AM, Yugo Nagata wrote: > > Hi, > > Currently, PostgreSQL doen't have a hook triggered at session > start. Although we already have ClientAuthentication_hook, > this is triggered during authentication, so we can not > access the database. > > If we have a hook triggerd

Re: [HACKERS] [GENERAL] huge RAM use in multi-command ALTER of table heirarchy

On 19 July 2017 at 00:26, Tom Lane wrote: > It's probably a bit late in the v10 cycle to be taking any risks in > this area, but I'd vote for ripping out RememberToFreeTupleDescAtEOX > as soon as the v11 cycle opens, unless someone can show an example > of non-broken coding that requires it. (An

Re: [HACKERS] pg_upgrade failed if view contain natural left join condition

On 20 July 2017 at 13:09, tushar wrote: > Steps to reproduce - > > v9.6 > > postgres=# create table t(n int); > CREATE TABLE > postgres=# create table t1(a int); > CREATE TABLE > postgres=# create view ttt1 as SELECT e.n FROM t e NATURAL LEFT JOIN t1 d; > CREATE VIEW > > v10 - > > run pg_upgrade -

Re: [HACKERS] pg_upgrade failed if view is based on sequence

On 20 July 2017 at 13:23, tushar wrote: > Steps to reproduce - > > v9.6 > > postgres=# create sequence seq_9166 start 1 increment 1; > CREATE SEQUENCE > postgres=# create or replace view v3_9166 as select * from seq_9166; > CREATE VIEW > > v10 > > run pg_upgrade , going to fail with this error > >

Re: [HACKERS] [GENERAL] huge RAM use in multi-command ALTER of table heirarchy

Greg Stark writes: > On 19 July 2017 at 00:26, Tom Lane wrote: >> It's probably a bit late in the v10 cycle to be taking any risks in >> this area, but I'd vote for ripping out RememberToFreeTupleDescAtEOX >> as soon as the v11 cycle opens, unless someone can show an example >> of non-broken codi

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

Greetings, * Sokolov Yura (y.soko...@postgrespro.ru) wrote: > I wrote two days ago about vacuum ring buffer: > https://www.postgresql.org/message-id/8737e9bddb82501da1134f021bf4929a%40postgrespro.ru > > Increasing Vacuum's ring buffer to size of Bulk Writer's one reduces > autovacuum time in 3-10

Re: [HACKERS] xlogfilename

On Thu, 20 Jul 2017 11:02:25 +0200 Michael Paquier wrote: > On Thu, Jul 20, 2017 at 10:58 AM, DEV_OPS wrote: > > I think you may reference to function: pg_xlogfile_name in > > src/backend/access/transam/xlogfuncs.c, it use XLogFileName defined in > > src/include/access/xlog_internal.h > > >

[HACKERS] Incorrect comment of XLByteToSeg() and XLByteToPrevSeg()

Hi, I found a type in the comment for XLByteToSeg() and XLByteToPrevSeg(). This says "Compute ID and segment from an XLogRecPtr", but these macros compute only segment numbers. I think "Compute a segment number from an XLogRecPtr" is correct. The definition of these macros were modified by the fo

Re: [HACKERS] pg_upgrade failed if view is based on sequence

On 20 July 2017 at 14:04, Thom Brown wrote: > On 20 July 2017 at 13:23, tushar wrote: >> Steps to reproduce - >> >> v9.6 >> >> postgres=# create sequence seq_9166 start 1 increment 1; >> CREATE SEQUENCE >> postgres=# create or replace view v3_9166 as select * from seq_9166; >> CREATE VIEW >> >> v

Re: [HACKERS] pg_upgrade failed if view is based on sequence

Thom Brown writes: > On 20 July 2017 at 13:23, tushar wrote: >> postgres=# create sequence seq_9166 start 1 increment 1; >> CREATE SEQUENCE >> postgres=# create or replace view v3_9166 as select * from seq_9166; >> CREATE VIEW > This is because sequence_name, start_value, increment_by, max_value

Re: [HACKERS] pg_upgrade failed if view contain natural left join condition

tushar writes: > postgres=# create table t(n int); > CREATE TABLE > postgres=# create table t1(a int); > CREATE TABLE > postgres=# create view ttt1 as SELECT e.n FROM t e NATURAL LEFT JOIN t1 d; > CREATE VIEW You realize of course that that's a pretty useless join definition. Still, yes, we do ne

Re: [HACKERS] Cache lookup errors with functions manipulation object addresses

On Wed, Jul 19, 2017 at 7:29 PM, Robert Haas wrote: > On Wed, Jul 19, 2017 at 2:25 AM, Michael Paquier > wrote: >> Would we want to improve the error handling of such objects? > > +1 for such an improvement. Attached is a patch for all that. Here are some notes: - format_type_be and friends use

Re: [HACKERS] Cache lookup errors with functions manipulation object addresses

Michael Paquier wrote: > - getObjectDescription and getObjectIdentity are called in quite a > couple of places. We could have those have a kind of missing_ok, but > as the status is just for adding cache lookup errors I have kept the > interface simple as this keeps the code in objectaddress.c mor

Re: [HACKERS] Cache lookup errors with functions manipulation object addresses

On Thu, Jul 20, 2017 at 4:04 PM, Alvaro Herrera wrote: > I think the addition of checks everywhere for NULL return is worse. > Let's add a missing_ok flag instead, so that most callers can just trust > that they get a non null value if they don't want to deal with that > case. If you want to mini

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

On 07/20/2017 06:28 AM, Stephen Frost wrote: It's not clear off-hand how much that would improve this case, as the database size appears to pretty quickly get beyond the OS memory size (and only in the first test is the DB starting size less than system memory to begin with). FYI, I will be p

Re: [HACKERS] JSONB - JSONB operator feature request

On Wed, Jul 19, 2017 at 06:17:35PM -0500, Merlin Moncure wrote: > On Tue, Jul 18, 2017 at 12:49 PM, David Fetter wrote: > > On Tue, Jul 18, 2017 at 01:36:32PM +0200, david.tu...@linuxbox.cz wrote: > >> Hi, > >> > >> some users and me used hstore - hstore for example storing only changed > >> rows

[HACKERS] Definitional questions for pg_sequences view

What exactly is the point of the new pg_sequences view? It seems like it's intended to ease conversion of applications that formerly did "select * from sequencename", but if so, there are some fairly annoying discrepancies. The old way got you these columns: regression=# \d s1 Seque

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

On Thu, Jul 20, 2017 at 12:08 AM, Peter Geoghegan wrote: >> The traditional >> wisdom about btrees, for instance, is that no matter how full you pack >> them to start with, the steady state is going to involve something like >> 1/3rd free space. You can call that bloat if you want, but it's not >

Re: [HACKERS] Increase Vacuum ring buffer.

On Tue, Jul 18, 2017 at 6:09 AM, Sokolov Yura wrote: > I investigated autovacuum performance, and found that it suffers a lot > from small ring buffer. It suffers in a same way bulk writer suffered > before Tom Lane's commit 6382448cf96: > >> Tom Lane 2009-06-23 00:04:28 >> For bulk write operat

Re: [HACKERS] Increase Vacuum ring buffer.

On Thu, Jul 20, 2017 at 11:59 AM, Robert Haas wrote: > On Tue, Jul 18, 2017 at 6:09 AM, Sokolov Yura > wrote: >> I investigated autovacuum performance, and found that it suffers a lot >> from small ring buffer. It suffers in a same way bulk writer suffered >> before Tom Lane's commit 6382448cf96:

Re: [HACKERS] JSONB - JSONB operator feature request

> On 20 July 2017 at 16:24, David Fetter wrote: > > If we can agree to a definition, we can make this go. My vague > memories from graph theory indicate that that "agree to a definition" > part is the real problem to be solved. I tried to embody some relevant thoughts in this thread [1], I think

Re: [HACKERS] GSoC 2017: weekly progress reports (week 7)

On Tue, Jul 18, 2017 at 10:36 AM, Shubham Barai wrote: > During this week, I read documentation and source code of BRIN index to > understand its implementation. > But later I found out that it is unlikely to implement page level or tuple > level predicate locking in BRIN index. > In this week, I

Re: [HACKERS] Increase Vacuum ring buffer.

On Thu, Jul 20, 2017 at 11:09 AM, Claudio Freire wrote: >> It's no secret that making the ring buffer larger will improve >> performance -- in fact, not having a ring buffer at all would improve >> performance even more. But it would also increase the likelihood that >> the background work of vac

Re: [HACKERS] pg_upgrade failed if view contain natural left join condition

On Thu, Jul 20, 2017 at 6:53 AM, Tom Lane wrote: > tushar writes: > > postgres=# create table t(n int); > > CREATE TABLE > > postgres=# create table t1(a int); > > CREATE TABLE > > postgres=# create view ttt1 as SELECT e.n FROM t e NATURAL LEFT JOIN t1 > d; > > CREATE VIEW > > You realize of cou

Re: [HACKERS] Increase Vacuum ring buffer.

Claudio Freire writes: > On Thu, Jul 20, 2017 at 11:59 AM, Robert Haas wrote: >> I think the question for this patch is "so, why didn't we do it this >> way originally?". >> >> It's no secret that making the ring buffer larger will improve >> performance -- in fact, not having a ring buffer at a

Re: [HACKERS] Increase Vacuum ring buffer.

On Thu, Jul 20, 2017 at 12:51 PM, Robert Haas wrote: > On Thu, Jul 20, 2017 at 11:09 AM, Claudio Freire > wrote: >>> It's no secret that making the ring buffer larger will improve >>> performance -- in fact, not having a ring buffer at all would improve >>> performance even more. But it would a

Re: [HACKERS] pg_upgrade failed if view contain natural left join condition

"David G. Johnston" writes: > Per the docs: > "If there are no common column names, NATURAL behaves like CROSS JOIN." > I'm being a bit pedantic here but since NATURAL is a replacement for > "ON/USING" it would seem more consistent to describe it, when no matching > columns are found, as "behaves

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

On Thu, Jul 20, 2017 at 6:28 AM, Stephen Frost wrote: > Greetings, > > * Sokolov Yura (y.soko...@postgrespro.ru) wrote: > > I wrote two days ago about vacuum ring buffer: > > https://www.postgresql.org/message-id/8737e9bddb82501da1134f021bf492 > 9a%40postgrespro.ru > > > > Increasing Vacuum's rin

Re: [HACKERS] Increase Vacuum ring buffer.

On 2017-07-20 17:59, Robert Haas wrote: On Tue, Jul 18, 2017 at 6:09 AM, Sokolov Yura wrote: I investigated autovacuum performance, and found that it suffers a lot from small ring buffer. It suffers in a same way bulk writer suffered before Tom Lane's commit 6382448cf96: Tom Lane 2009-06-23

Re: [HACKERS] Increase Vacuum ring buffer.

On 2017-07-20 19:04, Tom Lane wrote: Claudio Freire writes: On Thu, Jul 20, 2017 at 11:59 AM, Robert Haas wrote: I think the question for this patch is "so, why didn't we do it this way originally?". It's no secret that making the ring buffer larger will improve performance -- in fact, not h

Re: [HACKERS] Increase Vacuum ring buffer.

On Thu, Jul 20, 2017 at 1:08 PM, Claudio Freire wrote: > So, the 64x increase may be justifiable in absolute terms: it's not > unlikely that a 16MB buffer will be evicted from the OS cache before > vacuum is done with it, even in heavily throttled vacuums. Sorry, that should read "It's not *likel

Re: [HACKERS] Cache lookup errors with functions manipulation object addresses

Michael Paquier wrote: > On Thu, Jul 20, 2017 at 4:04 PM, Alvaro Herrera > wrote: > > I think the addition of checks everywhere for NULL return is worse. > > Let's add a missing_ok flag instead, so that most callers can just trust > > that they get a non null value if they don't want to deal with

Re: [HACKERS] [WIP] Zipfian distribution in pgbench

Hello Fabien,I am attaching patch v4. On 19 Jul 2017, at 17:21, Fabien COELHO wrote:About the maths: As already said, I'm not at ease with a random_zipfian function which does not display a (good) zipfian distribution. At the minimum the documentation should be clear about the

Re: [HACKERS] Increase Vacuum ring buffer.

On Thu, Jul 20, 2017 at 7:59 AM, Robert Haas wrote: > > > > Initially I wanted to make BAS_BULKWRITE and BAS_VACUUM ring sizes > > configurable, but after testing I don't see much gain from increasing > > ring buffer above 16MB. So I propose just 1 line change. > > I think the question for this p

Re: [HACKERS] [WIP] Zipfian distribution in pgbench

Hello Alik, About the maths: As already said, I'm not at ease with a random_zipfian function which does not display a (good) zipfian distribution. At the minimum the documentation should be clear about the approximations implied depending on the parameter value. I add one more sentence to d

Re: [HACKERS] GSoC 2017: weekly progress reports (week 7)

Hi Robert, I had detailed discussion about this with my mentor. Sorry, I didn't share details on hackers list. B-tree, gist, spgist, and gin are all tree based indexes where we scan and acquire predicate lock on only some and not all internal pages or leaf pages. So, here we have scope to reduce

Re: [HACKERS] [WIP] Zipfian distribution in pgbench

> I think that developping a test would be much simpler with the improved tap > test infrastructure, so I would suggest to wait to know the result of the > corresponding patch. Ok, I will wait then. > Also, could you recod the patch to CF 2017-09? > https://commitfest.postgresql.org/14/

Re: [HACKERS] WIP Patch: Precalculate stable functions, infrastructure v1

On Tue, Jul 18, 2017 at 9:16 AM, Marina Polyakova wrote: > Here I have made the 5th version of the patches. I have added the > precalculation of all primitive nodes that don't return set, are not > volatile themselves and their arguments are constant or precalculated > expressions too. There're re

Re: [HACKERS] GSoC 2017: weekly progress reports (week 7)

On Thu, Jul 20, 2017 at 1:22 PM, Shubham Barai wrote: > I had detailed discussion about this with my mentor. Sorry, I didn't share > details on hackers list. > > B-tree, gist, spgist, and gin are all tree based indexes where we scan and > acquire predicate lock > on only some and not all internal

Re: [HACKERS] Increase Vacuum ring buffer.

On Thu, Jul 20, 2017 at 12:16 PM, Sokolov Yura wrote: > So, from my point of view, no one just evaluate performance of increased > ring buffer for vacuum. I think that argument is clearly incorrect. In commit 6382448cf96a9b88d418cbaf86027b63f465b5d8, which you cited, Tom even added a note in the

Re: [HACKERS] Mishandling of WCO constraints in direct foreign table modification

On Thu, Jul 20, 2017 at 7:40 AM, Etsuro Fujita wrote: > Here is an example for $subject using postgres_fdw: > > postgres=# create foreign table foreign_tbl (a int, b int) server loopback > options (table_name 'base_tbl'); > CREATE FOREIGN TABLE > postgres=# create view rw_view as select * from for

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

On Thu, Jul 20, 2017 at 7:45 AM, Claudio Freire wrote: >> For the purposes of this discussion, I'm mostly talking about >> duplicates within a page on a unique index. If the keyspace owned by >> an int4 unique index page only covers 20 distinct values, it will only >> ever cover 20 distinct values

Re: [HACKERS] Increase Vacuum ring buffer.

Robert, * Robert Haas (robertmh...@gmail.com) wrote: > On Thu, Jul 20, 2017 at 12:16 PM, Sokolov Yura > wrote: > > But in fact, vacuum process performs FSYNC! It happens, cause vacuum > > evicts dirty pages from its ring buffer. And to evict dirty page, it > > has to be sure WAL record about its

Re: [HACKERS] Error while copying a large file in pg_rewind

On Thu, Jul 20, 2017 at 2:17 AM, Michael Paquier wrote: > Heikki, this bug is rather bad for anybody using pg_rewind with > relation file sizes larger than 2GB as this corrupts data of > instances. I think that you would be the best fit as a committer to > look at this patch as you implemented the

Re: [HACKERS] Increase Vacuum ring buffer.

On Thu, Jul 20, 2017 at 3:04 PM, Stephen Frost wrote: > I agree that it's a common problem for VACUUM to go too fast, or for > VACUUM to go too slow, but that's really what the vacuum_cost_limit > mechanism is for. I think that's a valid point. There are also other concerns here - e.g. whether i

Re: [HACKERS] Increase Vacuum ring buffer.

* Robert Haas (robertmh...@gmail.com) wrote: > On Thu, Jul 20, 2017 at 3:04 PM, Stephen Frost wrote: > > I agree that it's a common problem for VACUUM to go too fast, or for > > VACUUM to go too slow, but that's really what the vacuum_cost_limit > > mechanism is for. > > I think that's a valid po

Re: [HACKERS] Increase Vacuum ring buffer.

Robert Haas writes: > I think that's a valid point. There are also other concerns here - > e.g. whether instead of adopting the patch as proposed we ought to (a) > use some smaller size, or (b) keep the size as-is but reduce the > maximum fraction of shared_buffers that can be consumed, or (c) di

Re: [HACKERS] <> join selectivity estimate question

Ashutosh Bapat writes: > On Thu, Jul 20, 2017 at 5:30 PM, Thomas Munro > wrote: >> Does anyone know how to test a situation where the join is reversed >> according to >> get_join_variables, or "complicated cases where we can't tell for sure"? > explain select * from pg_class c right join pg_typ

Re: [HACKERS] PgFDW connection invalidation by ALTER SERVER/ALTER USER MAPPING

Kyotaro HORIGUCHI writes: > Here it is. First I tried this using ordinary regression > framework but the effect of this patch is shown only in log and > it contains variable parts so I gave up it before trying more > complex way. > Next I tried existing TAP test but this test needs continuous > s

Re: [HACKERS] PgFDW connection invalidation by ALTER SERVER/ALTER USER MAPPING

Kyotaro HORIGUCHI wrote: > Finally, I added a new TAP test library PsqlSession. It offers > interactive psql sessions. Then added a simple test to > postgres_fdw using it. Hmm, I think this can be very useful for other things. Let's keep this in mind to use in the future, even if we find another

Re: [HACKERS] <> join selectivity estimate question

On Fri, Jul 21, 2017 at 8:21 AM, Tom Lane wrote: > Ashutosh Bapat writes: >> On Thu, Jul 20, 2017 at 5:30 PM, Thomas Munro >> wrote: >>> Does anyone know how to test a situation where the join is reversed >>> according to >>> get_join_variables, or "complicated cases where we can't tell for sur

[HACKERS] Better error message for trying to drop a DB with open subscriptions?

All: The problem: postgres=# drop database bookdata; ERROR: database "bookdata" is being accessed by other users DETAIL: There is 1 other session using the database. postgres=# \c bookdata You are now connected to database "bookdata" as user "postgres". bookdata=# drop subscription wholedb; NOT

[HACKERS] More optimization effort?

Currently following query does not use an index: t-ishii@localhost: psql -p 5433 test Pager usage is off. psql (9.6.3) Type "help" for help. test=# explain select * from pgbench_accounts where aid*100 < 1; QUERY PLAN --

Re: [HACKERS] [PATCH] A hook for session start

On Thu, Jul 20, 2017 at 8:27 AM, Fabrízio de Royes Mello wrote: > I'm not sure your real needs but doesn't it material for improve Event > Triggers??? I've thought about that, too. One problem is what to do if the user hits ^C while the event trigger procedure is running. If you respond to that

Re: [HACKERS] [PATCH] A hook for session start

On 21 July 2017 at 08:42, Robert Haas wrote: > On Thu, Jul 20, 2017 at 8:27 AM, Fabrízio de Royes Mello > wrote: > > I'm not sure your real needs but doesn't it material for improve Event > > Triggers??? > > I've thought about that, too. One problem is what to do if the user > hits ^C while the

Re: [HACKERS] Better error message for trying to drop a DB with open subscriptions?

On 21 July 2017 at 07:09, Josh Berkus wrote: > All: > > The problem: > > postgres=# drop database bookdata; > ERROR: database "bookdata" is being accessed by other users > DETAIL: There is 1 other session using the database. > postgres=# \c bookdata > You are now connected to database "bookdata

Re: [HACKERS] More optimization effort?

On 21 July 2017 at 07:11, Tatsuo Ishii wrote: > Currently following query does not use an index: > > t-ishii@localhost: psql -p 5433 test > Pager usage is off. > psql (9.6.3) > Type "help" for help. > > test=# explain select * from pgbench_accounts where aid*100 < 1; >

Re: [HACKERS] Definitional questions for pg_sequences view

On 20 July 2017 at 22:36, Tom Lane wrote: > > This could be fixed if it were possible to translate to > select * from pg_sequences where seqoid = 'my_seq'::regclass; > but the view isn't exposing the sequence OID. Should it? > It probably should. It's not part of information_schema, it'

Re: [HACKERS] xlogfilename

On 20 July 2017 at 21:33, Yugo Nagata wrote: > On Thu, 20 Jul 2017 11:02:25 +0200 > Michael Paquier wrote: > > > On Thu, Jul 20, 2017 at 10:58 AM, DEV_OPS wrote: > > > I think you may reference to function: pg_xlogfile_name in > > > src/backend/access/transam/xlogfuncs.c, it use XLogFileName

Re: [HACKERS] [GENERAL] huge RAM use in multi-command ALTER of table heirarchy

On 2017/07/20 22:19, Tom Lane wrote: > Greg Stark writes: >> On 19 July 2017 at 00:26, Tom Lane wrote: >>> It's probably a bit late in the v10 cycle to be taking any risks in >>> this area, but I'd vote for ripping out RememberToFreeTupleDescAtEOX >>> as soon as the v11 cycle opens, unless someon

Re: [HACKERS] Mishandling of WCO constraints in direct foreign table modification

On 2017/07/21 3:24, Robert Haas wrote: I think that's reasonable. This should be committed and back-patched to 9.6, right? Yeah, because direct modify was introduced in 9.6. Attached is the second version which updated docs in postgres-fdw.sgml as well. Best regards, Etsuro Fujita *** a/co

Re: [HACKERS] [PATCH] pageinspect function to decode infomasks

On 20 July 2017 at 19:09, Ashutosh Sharma wrote: > I had a quick look into this patch and also tested it and following > are my observations. > > 1) I am seeing a server crash when passing any non meaningful value > for t_infomask2 to heap_infomask_flags(). > > postgres=# SELECT heap_infomask_fla

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

On 07/19/2017 07:57 PM, Tom Lane wrote: Peter Geoghegan writes: My argument for the importance of index bloat to the more general bloat problem is simple: any bloat that accumulates, that cannot be cleaned up, will probably accumulate until it impacts performance quite noticeably. But that ju

Re: [HACKERS] [TRAP: FailedAssertion] causing server to crash

Hi, Attached is the core dump file received on PG 10beta2 version. ​ myfile.tgz ​ Regards, Neha Sharma On Thu, Jul 20, 2017 at 2:45 PM, Neha Sharma wrote: > > > Regards, > Neha Sharma > > On T

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

On 21/07/17 15:58, Joshua D. Drake wrote: On 07/19/2017 07:57 PM, Tom Lane wrote: Peter Geoghegan writes: My argument for the importance of index bloat to the more general bloat problem is simple: any bloat that accumulates, that cannot be cleaned up, will probably accumulate until it impacts

Re: [HACKERS] Better error message for trying to drop a DB with open subscriptions?

On Thu, Jul 20, 2017 at 4:09 PM, Josh Berkus wrote: > All: > > The problem: > > postgres=# drop database bookdata; > ERROR: database "bookdata" is being accessed by other users > DETAIL: There is 1 other session using the database. > postgres=# \c bookdata > You are now connected to database "b

Re: [HACKERS] PgFDW connection invalidation by ALTER SERVER/ALTER USER MAPPING

At Thu, 20 Jul 2017 18:15:42 -0400, Tom Lane wrote in <18927.1500588...@sss.pgh.pa.us> > This seems like overkill. We can test it reasonably easily within the > existing framework, as shown in the attached patch. I'm also fairly It checks for a disconnection caused in a single session. I thoug

Re: [HACKERS] [TRAP: FailedAssertion] causing server to crash

On Fri, Jul 21, 2017 at 4:16 PM, Neha Sharma wrote: > > Attached is the core dump file received on PG 10beta2 version. Thanks Neha. It's be best to post the back trace and if possible print oldestXact and ShmemVariableCache->oldestXid from the stack frame for TruncateCLOG. The failing assertion

Re: [HACKERS] PgFDW connection invalidation by ALTER SERVER/ALTER USER MAPPING

At Thu, 20 Jul 2017 18:23:05 -0400, Alvaro Herrera wrote in <20170720222305.ij3pk7qw5im3wozr@alvherre.pgsql> > Kyotaro HORIGUCHI wrote: > > > Finally, I added a new TAP test library PsqlSession. It offers > > interactive psql sessions. Then added a simple test to > > postgres_fdw using it. > >

Re: [HACKERS] PgFDW connection invalidation by ALTER SERVER/ALTER USER MAPPING

On Fri, Jul 21, 2017 at 12:23 AM, Alvaro Herrera wrote: > Kyotaro HORIGUCHI wrote: >> Finally, I added a new TAP test library PsqlSession. It offers >> interactive psql sessions. Then added a simple test to >> postgres_fdw using it. > > Hmm, I think this can be very useful for other things. Let's

Re: [HACKERS] [TRAP: FailedAssertion] causing server to crash

Here is the back trace from the core dump attached. (gdb) bt #0 0x7f4a71424495 in raise () from /lib64/libc.so.6 #1 0x7f4a71425c75 in abort () from /lib64/libc.so.6 #2 0x009dc18a in ExceptionalCondition (conditionName=0xa905d0 "!(TransactionIdPrecedesOrEquals(oldestXact, ShmemVa

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

On Thu, Jul 20, 2017 at 8:53 AM, Thomas Munro wrote: > On Thu, Jul 20, 2017 at 2:02 PM, Robert Haas > wrote: > > On Wed, Jul 19, 2017 at 7:45 PM, Thomas Munro > > wrote: > >> Isn't this the same as the issue reported here? > >> > >> https://www.postgresql.org/message-id/flat/CAEepm%3D270ze2hVxW

Re: [HACKERS] Error while copying a large file in pg_rewind

On Thu, Jul 20, 2017 at 9:31 PM, Robert Haas wrote: > I was initially surprised that your testing managed to pass, but then > I noticed that this sanity test is using && where it should really be > using ||; it will only fail if ALL of the data types are wrong. Oops. Oh, oh. If this was right fr

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

On Fri, Jul 21, 2017 at 11:42 AM, Rafia Sabih wrote: > > Following the discussion at [1], with the patch Thomas posted there, now Q21 > completes in some 160 seconds. Your earlier reports mentioned unpartitioned case taking 300 seconds, partitioned case without partition-wise join taking 600 seco

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

On Thu, Jul 20, 2017 at 2:44 PM, Ashutosh Bapat wrote: > > On Thu, Jul 20, 2017 at 11:46 AM, Amit Langote > wrote: > > On 2017/07/20 15:05, Ashutosh Bapat wrote: > >> On Wed, Jul 19, 2017 at 9:54 AM, Rafia Sabih > >> wrote: > >>> > >>> Partition information: > >>> Type of partitioning - single c

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

On Fri, Jul 21, 2017 at 11:54 AM, Rafia Sabih wrote: > So, does this > also mean that a partitioned table will not join with an unpartitioned > table without append of partitions? > Yes. When you join an unpartitioned table with a partitioned table, the planner will choose to append all the parti

Re: [HACKERS] Cache lookup errors with functions manipulation object addresses

On Thu, Jul 20, 2017 at 6:26 PM, Alvaro Herrera wrote: > Michael Paquier wrote: >> On Thu, Jul 20, 2017 at 4:04 PM, Alvaro Herrera >> wrote: >> > I think the addition of checks everywhere for NULL return is worse. >> > Let's add a missing_ok flag instead, so that most callers can just trust >> >

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

On 2017-07-21 06:58, Joshua D. Drake wrote: On 07/19/2017 07:57 PM, Tom Lane wrote: Peter Geoghegan writes: My argument for the importance of index bloat to the more general bloat problem is simple: any bloat that accumulates, that cannot be cleaned up, will probably accumulate until it impact