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

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 > >

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

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

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

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

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: > > >

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

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=#

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 ***

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

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

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

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] 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

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

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

[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

[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;

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 >>>

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

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

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

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

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

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

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

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

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

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

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

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

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 >

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] 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

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

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

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

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

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

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

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

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 > > > >

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 >

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

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 >>

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

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 >>

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

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

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

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

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,

[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

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

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

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 >

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

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.

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

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

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

[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

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

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

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

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 ,

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

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

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

[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

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

[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

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

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.

[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

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

[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

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

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

[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

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

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

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

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) \ >

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, \

[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] [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

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 >>

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

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

On Thu, Jul 20, 2017 at 3:13 PM, Julien Rouhaud wrote: > On Thu, Jul 20, 2017 at 5:44 AM, Peter Geoghegan wrote: >> On Wed, Jul 19, 2017 at 8:33 PM, Craig Ringer wrote: >>> That's silly, so here's a patch to teach pageinspect how to

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

Finally, I added new TAP test library PsqlSession. At Tue, 18 Jul 2017 18:12:13 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20170718.181213.206979369.horiguchi.kyot...@lab.ntt.co.jp> > > * How about some regression test cases? You couldn't really

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

On Thu, Jul 20, 2017 at 12:30 AM, Robert Haas wrote: > >> This suggests that partitioning is not a suitable strategy for this query, >> but then may be partition wise should not be picked for such a case to >> aggravate the performance issue. > > In the unpartitioned case,

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

On Fri, Jul 7, 2017 at 9:33 AM, Michael Paquier wrote: > On Fri, Jul 7, 2017 at 4:31 PM, Kuntal Ghosh > wrote: >> On Fri, Jul 7, 2017 at 7:49 AM, Michael Paquier >> wrote: >> I don't have any more inputs on this

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

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 >> >> Lineitem - >> Partition key =

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

On Thu, Jul 20, 2017 at 5:44 AM, Peter Geoghegan wrote: > On Wed, Jul 19, 2017 at 8:33 PM, Craig Ringer wrote: >> That's silly, so here's a patch to teach pageinspect how to decode infomasks >> to a human readable array of flag names. >> >> Example: >> >>

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

On 2017-07-20 05:52, Masahiko Sawada wrote: On Thu, Jul 20, 2017 at 8:24 AM, Stephen Frost wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: "Joshua D. Drake" writes: > At PGConf US Philly last week I was talking with Jim and Jan about > performance.

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

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 > > Lineitem - > Partition key = l_orderkey > No of partitions = 18 > > Orders - >