Re: [HACKERS] checkpointer continuous flushing

I would be curious whether flushing helps, though. Yes, me too. I think we should try to reach on consensus for exact scenarios and configuration where this patch('es) can give benefit or we want to verify if there is any regression as I have access to this m/c for a very-very limited time.

[HACKERS] jsonb_concat: make sure we always return a non-scalar value

There was a long thread about concatenating jsonb objects to each other, but that discussion didn't touch concatenating other types. Currently jsonb_concat always just returns the other argument if one of arguments is considered empty. This causes surprising behavior when concatenating scalar

Re: [HACKERS] Fwd: Core dump with nested CREATE TEMP TABLE

On 9/4/15 12:41 PM, Tom Lane wrote: I wrote: After review of all the callers of RelationClearRelation, it seems like most of them already have sufficient guards to prevent triggering of the Assert. The ones that lack such tests are AtEOXact_cleanup and AtEOSubXact_cleanup. So what I'm now thin

Re: [HACKERS] [PATCH] SQL function to report log message

On Fri, Sep 4, 2015 at 2:03 PM, Pavel Stehule wrote: > > > 2015-09-02 21:49 GMT+02:00 dinesh kumar : > >> On Mon, Aug 31, 2015 at 10:08 PM, Pavel Stehule >> wrote: >> >>> >>> >>> 2015-09-01 6:59 GMT+02:00 Pavel Stehule : >>> 2015-08-31 20:43 GMT+02:00 dinesh kumar : > Hi,

Re: [HACKERS] checkpointer continuous flushing

Hello Amit, Woops 14 GB/s and 1.2 GB/s?! Is this a *hard* disk?? Yes, there is no SSD in system. I have confirmed the same. There are RAID spinning drives. Ok... I guess that there is some kind of cache to explain these great tps figures, probably on the RAID controller. What does "l

Re: [HACKERS] [PATCH] SQL function to report log message

On Fri, Sep 4, 2015 at 1:08 PM, Pavel Stehule wrote: > > > 2015-09-02 21:49 GMT+02:00 dinesh kumar : > >> On Mon, Aug 31, 2015 at 10:08 PM, Pavel Stehule >> wrote: >> >>> >>> >>> 2015-09-01 6:59 GMT+02:00 Pavel Stehule : >>> 2015-08-31 20:43 GMT+02:00 dinesh kumar : > Hi,

Re: [HACKERS] checkpointer continuous flushing

On Tue, Sep 1, 2015 at 5:30 PM, Fabien COELHO wrote: > > Hello Amit, > > About the disks: what kind of HDD (RAID? speed?)? HDD write cache? >>> >> >> Speed of Reads - >> Timing cached reads: 27790 MB in 1.98 seconds = 14001.86 MB/sec >> Timing buffered disk reads: 3830 MB in 3.00 seconds = 12

Re: [HACKERS] pg_ctl/pg_rewind tests vs. slow AIX buildfarm members

Noah Misch writes: > On Thu, Sep 03, 2015 at 03:31:06PM -0400, Tom Lane wrote: >>> This is the first time I've seen an indication that the >>> start_postmaster change mentioned in the comment is actually important >>> for production use, rather than just being cleanup. > I scratched my head awhil

Re: [HACKERS] pg_ctl/pg_rewind tests vs. slow AIX buildfarm members

On Thu, Sep 03, 2015 at 03:31:06PM -0400, Tom Lane wrote: > I wrote: > > This is the first time I've seen an indication that the > > start_postmaster change mentioned in the comment is actually important > > for production use, rather than just being cleanup. I scratched my head awhile without thi

Re: [HACKERS] BRIN INDEX value

On Saturday, September 5, 2015, Tatsuo Ishii wrote: > > Tatsuo Ishii wrote: > >> > On 9/4/2015 2:04 PM, Tatsuo Ishii wrote: > >> >>> One thing I imagine we could do is to change the signature of > >> >>> summrize_range() to also include heapNumBlks which its (only) caller > >> >>> brinsummarize()

Re: [HACKERS] BRIN INDEX value

> Tatsuo Ishii wrote: >> > On 9/4/2015 2:04 PM, Tatsuo Ishii wrote: >> >>> One thing I imagine we could do is to change the signature of >> >>> summrize_range() to also include heapNumBlks which its (only) caller >> >>> brinsummarize() already computes. It will look like: >> >>> >> >>> static void

Re: [HACKERS] WIP: SCRAM authentication

On Fri, Sep 4, 2015 at 04:51:33PM -0400, Stephen Frost wrote: > > Coming in late, but can you explain how multiple passwords allow for > > easier automated credential rotation? If you have five applications > > with stored passwords, I imagine you can't change them all at once, so > > with multip

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On Thu, Sep 3, 2015 at 10:56:42AM -0400, Robert Haas wrote: > The amount of opposition to this feature is remarkable considering > that it's available in Oracle, SQL Server, MongoDB, Cassandra, and > MySQL. See for example: > > http://docs.mongodb.org/manual/reference/connection-string/ > https:

Re: [HACKERS] Horizontal scalability/sharding

Hey Robert, Now the question is, where should the code that does all of this live? > postgres_fdw? Some new, sharding-specific FDW? In core? I don't > know for sure, but what I do know is that we could make a lot of > progress over where we are today by just improving postgres_fdw, and I > don

Re: [HACKERS] Waits monitoring

On 9 July 2015 at 21:10, Alexander Korotkov wrote: > I see that patch of Robert and Amit adds current lock type > into PgBackendStatus and, in turn, adds it into pg_stat_activity. This is > small part of what you implement in waits monitoring patch. Dividing big > features into small pieces make

Re: [HACKERS] Too many duplicated condition query return wrong value

>> Can you give an "explain (analyze, buffers)" for each query? Maybe you >> have a corrupted index, and one query uses the index and the other does not. > explain (analyze, buffers) SELECT "attend"."lid", "attend"."status" FROM > "attend" WHERE "attend"."sid" = 325 AND "attend"."lid" IN ('AB

Re: [HACKERS] Freeze avoidance of very large table.

On 3 September 2015 at 18:23, Masahiko Sawada wrote: > The previous patch lacks some files for regression test. > Attached fixed v12 patch. > This looks OK. You saw that I was proposing to solve this problem a different way ("Summary of plans to avoid the annoyance of Freezing"), suggesting tha

Re: [HACKERS] Summary of plans to avoid the annoyance of Freezing

On 10 August 2015 at 19:21, Josh Berkus wrote: > Question: does regular vacuum update the visibility map in the same way > vacuum freeze does? Yes -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Trainin

Re: [HACKERS] Spurious standby query cancellations

On 27 August 2015 at 22:55, Jeff Janes wrote: > In ResolveRecoveryConflictWithLock, there is the comment: > > /* > * If blowing away everybody with conflicting locks doesn't work, after > * the first two attempts then we just start blowing everybody away > until > * it does wor

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, On 09/04/2015 06:10 PM, Jeff Janes wrote: How can we evaluate Tom's performance concerns? I tried turning log_planner_stats on and using the regression test as a load generator, but I don't think that that is very demanding of a test. I've done a bit of benchmarking today, trying to meas

Re: [HACKERS] WIP: SCRAM authentication

Bruce, * Bruce Momjian (br...@momjian.us) wrote: > On Tue, Aug 18, 2015 at 09:30:39PM +0100, Greg Stark wrote: > > > OK, that's an interesting argument. If SCRAM supports multiple > > > password verifiers, and we support SCRAM, then I guess we should > > > probably do that, too. I still don't li

Re: [HACKERS] What is the length of tuple with a TOAST pointer?

Vignesh Raghunathan writes: > If a tuple has a pointer to a TOAST table entry, would the tuple's length > stored in its corresponding Item include the length of the entry in the > TOAST table? No. It would include the length of the TOAST-pointer datum. regards, tom lane

[HACKERS] What is the length of tuple with a TOAST pointer?

Hello, If a tuple has a pointer to a TOAST table entry, would the tuple's length stored in its corresponding Item include the length of the entry in the TOAST table? Thanks, Vignesh

Re: [HACKERS] PL/pgSQL, RAISE and error context

Pavel Stehule writes: > 2015-08-12 11:07 GMT+02:00 Marko Tiikkaja : >> I'm somewhat worried that this is hiding important context from some >> NOTICE or WARNING messages intended for novice users, but probably not >> worried enough to go through all of them. +3/8 from me, I guess. > I fixed ment

Re: [HACKERS] WIP: SCRAM authentication

On Tue, Aug 18, 2015 at 09:30:39PM +0100, Greg Stark wrote: > > OK, that's an interesting argument. If SCRAM supports multiple > > password verifiers, and we support SCRAM, then I guess we should > > probably do that, too. I still don't like it all that much, though. > > I think it's absolutely i

Re: [HACKERS] Fwd: Core dump with nested CREATE TEMP TABLE

I wrote: >> After review of all the callers of RelationClearRelation, it seems like >> most of them already have sufficient guards to prevent triggering of the >> Assert. The ones that lack such tests are AtEOXact_cleanup and >> AtEOSubXact_cleanup. So what I'm now thinking is that those should d

Re: [HACKERS] BRIN INDEX value

Tatsuo Ishii wrote: > > On 9/4/2015 2:04 PM, Tatsuo Ishii wrote: > >>> One thing I imagine we could do is to change the signature of > >>> summrize_range() to also include heapNumBlks which its (only) caller > >>> brinsummarize() already computes. It will look like: > >>> > >>> static void summariz

Re: [HACKERS] Allow a per-tablespace effective_io_concurrency setting

On 2015-09-04 17:21:38 +0100, Greg Stark wrote: > Wouldn't SSDs need much *less* aggressive prefetching? There's still > latency and there are multiple I/O channels so they will still need > some. But spinning media gives latencies measured in milliseconds. You > can process a lot of tuples in mill

Re: [HACKERS] Allow a per-tablespace effective_io_concurrency setting

On Thu, Sep 3, 2015 at 2:13 PM, Merlin Moncure wrote: > I find this talk of platters and spindles to be somewhat > baroque; for a 200$ part I have to work pretty hard to max out the > drive when reading and I'm still not completely sure if it's the drive > itself, postgres, cpu, or sata interfac

Re: [HACKERS] Freeze avoidance of very large table.

On Thu, Sep 3, 2015 at 11:56:52PM -0300, Alvaro Herrera wrote: > Bruce Momjian wrote: > > On Thu, Sep 3, 2015 at 11:37:09PM +0200, Petr Jelinek wrote: > > > >I don't understand. I'm just proposing that the source code for the > > > >extension to live in src/extensions/, and have the shared libra

Re: [HACKERS] Allow a per-tablespace effective_io_concurrency setting

On Fri, Sep 4, 2015 at 05:21:38PM +0100, Greg Stark wrote: > Wouldn't SSDs need much *less* aggressive prefetching? There's still > latency and there are multiple I/O channels so they will still need > some. But spinning media gives latencies measured in milliseconds. You > can process a lot of tu

Re: [HACKERS] PATCH: index-only scans with partial indexes

On Fri, Sep 4, 2015 at 4:28 AM, Anastasia Lubennikova < a.lubennik...@postgrespro.ru> wrote: > > > 25.08.2015 20:19, Jeff Janes пишет: > > On Fri, Jul 10, 2015 at 11:29 AM, Tomas Vondra < > tomas.von...@2ndquadrant.com> wrote: > >> Hi, >> >> currently partial indexes end up not using index only sc

Re: [HACKERS] [patch] Proposal for \rotate in psql

Pavel Stehule wrote: > the name "rotate" is not correct - maybe "\cross" ? I'm not dead set on \rotate and suggested other names previously in [1], but none of them seems decisively superior. The rationale behind rotate is that, it's a synonym of pivot as a verb, and it's not already us

Re: [HACKERS] pageinspect patch, for showing tuple data

В письме от 4 сентября 2015 14:58:29 пользователь Michael Paquier написал: > Yeah, I think that's acceptable to have a switch, defaulting to ERROR if > caller specifies nothing. > > Here are some observations after looking at the code, no functional testing. > > + int error_mod

Re: [HACKERS] Fwd: Core dump with nested CREATE TEMP TABLE

I wrote: > After review of all the callers of RelationClearRelation, it seems like > most of them already have sufficient guards to prevent triggering of the > Assert. The ones that lack such tests are AtEOXact_cleanup and > AtEOSubXact_cleanup. So what I'm now thinking is that those should do >

Re: [HACKERS] Fwd: Core dump with nested CREATE TEMP TABLE

Michael Paquier writes: > On Fri, Sep 4, 2015 at 10:52 PM, Tom Lane wrote: >> Also, I am very strongly tempted to convert the original problem-causing >> Assert in relcache.c into a regular runtime test-and-elog. If we're wrong >> about having sealed off this hole completely, I'd much rather see

Re: [HACKERS] Fwd: Core dump with nested CREATE TEMP TABLE

On Fri, Sep 4, 2015 at 10:52 PM, Tom Lane wrote: > Also, I am very strongly tempted to convert the original problem-causing > Assert in relcache.c into a regular runtime test-and-elog. If we're wrong > about having sealed off this hole completely, I'd much rather see the > result be an elog than

Re: [HACKERS] Non-volatile variables used for spinlock manipulation

On 2015-09-04 09:42:20 -0400, Tom Lane wrote: > Antonin Houska writes: > > Since SpinLockAcquire() / SpinLockRelease() macros usually reference > > variables > > declared as volatile, I wonder if the following changes should be applied. > > We've been making changes to remove that requirement, s

Re: [HACKERS] Fwd: Core dump with nested CREATE TEMP TABLE

Michael Paquier writes: > On Fri, Sep 4, 2015 at 12:04 PM, Tom Lane wrote: >> Attached is an updated patch with comments to this effect and some >> minor other code cleanup (mainly, not assuming that CurrentResourceOwner >> is the right thing to use in AtSubAbort_Portals). > Thanks! This looks g

Re: [HACKERS] Non-volatile variables used for spinlock manipulation

Antonin Houska writes: > Since SpinLockAcquire() / SpinLockRelease() macros usually reference variables > declared as volatile, I wonder if the following changes should be applied. We've been making changes to remove that requirement, so I think that at least in 9.5/HEAD this isn't an issue anymo

Re: [HACKERS] Making tab-complete.c easier to maintain

Thomas Munro writes: > See attached a proof-of-concept patch. It reduces the size of > tab-complete.c by a bit over a thousand lines. I realise that changing so > many lines just to refactor code may may be a difficult sell! But I think > this would make it easier to improve the tab completion

Re: [HACKERS] Testing WAL replay by comparing before and after images again

On 4 September 2015 at 13:45, Heikki Linnakangas wrote: > Another issue was with the new speculative insertions. Replaying a > speculative insertion record sets the tuple's CTID to point to itself, like > in a regular insertion. But in the original system, the CTID is set to a > special speculat

[HACKERS] Testing WAL replay by comparing before and after images again

I rerun my little testing tool that compares buffer page contents after every modification, in master and in WAL replay. Previously discussed here: http://www.postgresql.org/message-id/5357b582.7060...@vmware.com. Here's an updated version of my original hack, for current git master. (Michael p

Re: [HACKERS] [PATCH] Microvacuum for gist.

Some notices 1 gistvacuumpage(): OffsetNumber deletable[MaxOffsetNumber]; Seems, MaxOffsetNumber is too much, MaxIndexTuplesPerPage is enough 2 Loop in gistkillitems() for searching heap pointer. It seems to me that it could be a performance problem. To fix it, it's needed to remember inde

[HACKERS] Truncating/vacuuming relations on full tablespaces

Hi, Currently, when attempting to vacuum a table on a tablespace with no space left, we get an error: postgres=# vacuum test; ERROR: could not extend file "pg_tblspc/19605/PG_9.6_201508111/12382/19616_vm": No space left on device HINT: Check free disk space. This is because it first tries to g

Re: [HACKERS] PATCH: index-only scans with partial indexes

25.08.2015 20:19, Jeff Janes пишет: On Fri, Jul 10, 2015 at 11:29 AM, Tomas Vondra mailto:tomas.von...@2ndquadrant.com>> wrote: Hi, currently partial indexes end up not using index only scans in most cases, because check_index_only() is overly conservative, as explained in t

Re: [HACKERS] Foreign join pushdown vs EvalPlanQual

On 2015/09/03 19:25, Etsuro Fujita wrote: On 2015/09/03 14:22, Etsuro Fujita wrote: On 2015/09/03 9:41, Robert Haas wrote: That having been said, I don't entirely like Fujita-san's patch either. Much of the new code is called immediately adjacent to an FDW callback which could pretty trivially

[HACKERS] Non-volatile variables used for spinlock manipulation

Since SpinLockAcquire() / SpinLockRelease() macros usually reference variables declared as volatile, I wonder if the following changes should be applied. -- Antonin Houska Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de, http://www.cy

Re: [HACKERS] Declarative partitioning

On 4 September 2015 at 06:51, Amit Langote wrote: > > Sorry about the long delay in replying, to this message or the others > posted in the last few days. I should have notified in advance of my > vacation with rather limited Internet access. No problem, I'm on leave too. > >> The patch does

Re: [HACKERS] BRIN INDEX value

> On Fri, Sep 4, 2015 at 10:03 AM, Tatsuo Ishii wrote: >> >> I have looked into your patch and am a little bit worried because it >> calls RelationGetNumberOfBlocks() which is an expensive function. >> I think there are two ways to avoid it. >> >> 1) fall back to your former patch. However it may

[HACKERS] gin_fuzzy_search_limit and postgresql.conf.sample

Hi, My client found that gin_fuzzy_search_limit is not included in postgresql.conf.sample. Is this intentional? Or oversight? Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [HACKERS] BRIN INDEX value

On Fri, Sep 4, 2015 at 10:03 AM, Tatsuo Ishii wrote: > > I have looked into your patch and am a little bit worried because it > calls RelationGetNumberOfBlocks() which is an expensive function. > I think there are two ways to avoid it. > > 1) fall back to your former patch. However it may break ex

Re: [HACKERS] Freeze avoidance of very large table.

On 2015-09-04 02:11, Bruce Momjian wrote: On Thu, Sep 3, 2015 at 11:37:09PM +0200, Petr Jelinek wrote: I don't understand. I'm just proposing that the source code for the extension to live in src/extensions/, and have the shared library installed by toplevel make install; I'm not suggesting th

Re: [HACKERS] [PATCH] SQL function to report log message

2015-09-02 21:49 GMT+02:00 dinesh kumar : > On Mon, Aug 31, 2015 at 10:08 PM, Pavel Stehule > wrote: > >> >> >> 2015-09-01 6:59 GMT+02:00 Pavel Stehule : >> >>> >>> >>> 2015-08-31 20:43 GMT+02:00 dinesh kumar : >>> Hi, On Sat, Aug 29, 2015 at 4:22 PM, Pavel Stehule >>> > wrote: >>>

Re: [HACKERS] Re: [COMMITTERS] pgsql: Map basebackup tablespaces using a tablespace_map file

On Thu, Sep 3, 2015 at 6:07 PM, Fujii Masao wrote: > > On Tue, Aug 4, 2015 at 12:15 PM, Amit Kapila wrote: > > On Mon, Aug 3, 2015 at 7:44 PM, Fujii Masao wrote: > >> ISTM that we can > >> see that the online backup mode has already been canceled if backup_label > >> file > >> is successfully re

Re: [HACKERS] [PATCH] SQL function to report log message

2015-09-02 21:49 GMT+02:00 dinesh kumar : > On Mon, Aug 31, 2015 at 10:08 PM, Pavel Stehule > wrote: > >> >> >> 2015-09-01 6:59 GMT+02:00 Pavel Stehule : >> >>> >>> >>> 2015-08-31 20:43 GMT+02:00 dinesh kumar : >>> Hi, On Sat, Aug 29, 2015 at 4:22 PM, Pavel Stehule >>> > wrote: >>>

Re: [HACKERS] Allow replication roles to use file access functions

On 09/04/2015 08:14 AM, Michael Paquier wrote: On Thu, Sep 3, 2015 at 9:53 PM, Stephen Frost wrote: * Michael Paquier (michael.paqu...@gmail.com) wrote: On Thu, Sep 3, 2015 at 11:20 AM, Stephen Frost wrote: Not only, +clog, configuration files, etc. Configuration files? Perhaps you could e