Re: [HACKERS] FSM versus GIN pending list bloat

On Tue, Aug 4, 2015 at 1:39 AM, Simon Riggs wrote: > On 4 August 2015 at 06:03, Jeff Janes wrote: > > >> The attached proof of concept patch greatly improves the bloat for both >> the insert and the update cases. You need to turn on both features: adding >> the page

Re: [HACKERS] FSM versus GIN pending list bloat

On Tue, Aug 4, 2015 at 6:35 AM, Simon Riggs wrote: > On 4 August 2015 at 09:39, Simon Riggs wrote: > >> On 4 August 2015 at 06:03, Jeff Janes wrote: >> >> >>> The attached proof of concept patch greatly improves the bloat for both >>> the insert and t

Re: [HACKERS] LWLock deadlock and gdb advice

On Sun, Aug 2, 2015 at 8:05 AM, Andres Freund wrote: > On 2015-08-02 17:04:07 +0200, Andres Freund wrote: > > I've attached a version of the patch that should address Heikki's > > concern. It imo also improves the API and increases debuggability by not > > having stale variable values in the vari

Fwd: [HACKERS] 9.5 release notes

On Wed, Jun 10, 2015 at 9:15 PM, Bruce Momjian wrote: > I have committed the first draft of the 9.5 release notes. You can view > the output here: > > http://momjian.us/pgsql_docs/release-9-5.html > > and it will eventually appear here: > > http://www.postgresql.org/docs/devel/st

Re: [HACKERS] GIN pageinspect functions

On Fri, Nov 21, 2014 at 2:04 AM, Heikki Linnakangas wrote: > On 11/20/2014 05:52 AM, Michael Paquier wrote: > >> On Wed, Nov 19, 2014 at 7:01 AM, Peter Geoghegan wrote: >> >>> On Tue, Nov 4, 2014 at 7:26 AM, Amit Kapila >>> wrote: >>> 1. Documentation seems to be missing, other API's exposed >>

Re: PENDING_LIST_CLEANUP_SIZE - maximum size of GIN pending list Re: [HACKERS] HEAD seems to generate larger WAL regarding GIN index

On Thu, Oct 30, 2014 at 5:30 AM, Fujii Masao wrote: > On Thu, Oct 30, 2014 at 7:30 PM, Etsuro Fujita > wrote: > > > + { > > + {"pending_list_cleanup_size", PGC_USERSET, > > CLIENT_CONN_STATEMENT, > > + gettext_noop("Sets the maximum size of the > pending

Re: [HACKERS] FSM versus GIN pending list bloat

On Tue, Aug 4, 2015 at 12:38 PM, Jeff Janes wrote: > On Tue, Aug 4, 2015 at 1:39 AM, Simon Riggs wrote: > >> On 4 August 2015 at 06:03, Jeff Janes wrote: >> >> >>> The attached proof of concept patch greatly improves the bloat for both >>> the insert

[HACKERS] statistics for array types

When reviewing some recent patches, I decided the statistics gathered for arrays had some pre-existing shortcomings. The main one is that when the arrays contain rare elements there is no histogram to fall back upon when the MCE array is empty, the way there is for scalar stats. So it has to punt

[HACKERS] GIN pending clean up is not interruptable

When a user backend (as opposed to vacuum or autoanalyze) gets burdened with cleaning up the GIN pending list, it does not call CHECK_FOR_INTERRUPTS(). Since cleaning does a lot of random IO, it can take a long time and it is not nice to be uninterruptable. The attached patch adds an else branch

Re: [HACKERS] GIN pending clean up is not interruptable

On Tue, Aug 11, 2015 at 5:27 PM, Tom Lane wrote: > Andres Freund writes: > > On 2015-08-11 15:07:15 -0700, Jeff Janes wrote: > >> The attached patch adds an else branch to call CHECK_FOR_INTERRUPTS(). > >> > >> But I think we could instead just call vacuum_de

[HACKERS] GIN pending list clean up exposure to SQL

I've written a function which allows users to clean up the pending list. It takes the index name and returns the number of pending list pages deleted. # select * from gin_clean_pending_list('foo_text_array_idx'); gin_clean_pending_list 278 (1 row) Tim

[HACKERS] Potential GIN vacuum bug

When ginbulkdelete gets called for the first time in a VACUUM(i.e. stats == NULL), one of the first things it does is call ginInsertCleanup to get rid of the pending list. It does this in lieu of vacuuming the pending list. This is important because if there are any dead tids still in the Pendin

Re: [HACKERS] Potential GIN vacuum bug

On Mon, Aug 17, 2015 at 6:23 AM, Jeff Janes wrote: > > On Aug 16, 2015 11:49 PM, "Heikki Linnakangas" wrote: > > > > On 08/16/2015 12:58 AM, Jeff Janes wrote: > >> > >> When ginbulkdelete gets called for the first time in a VACUUM(i.e. > stats

Re: [HACKERS] Potential GIN vacuum bug

On Mon, Aug 17, 2015 at 3:02 PM, Alvaro Herrera wrote: > Jeff Janes wrote: > > > The attached patch takes a ShareUpdateExclusiveLock lock on the index in > > order to clean the pending list. > > Does it take a lock on the table also? Because if not ... > There must b

[HACKERS] Make HeapTupleSatisfiesMVCC more concurrent

When we check a tuple for MVCC, it has to pass checks that the inserting transaction has committed, and that it committed before our snapshot began. And similarly that the deleting transaction hasn't committed, or did so after our snapshot. XidInMVCCSnapshot is (or can be) very much cheaper than

Re: [HACKERS] Make HeapTupleSatisfiesMVCC more concurrent

On Tue, Aug 18, 2015 at 5:36 PM, Tom Lane wrote: > I wrote: > > Just thinking about this ... I wonder why we need to call > > TransactionIdIsInProgress() at all rather than believing the answer from > > the snapshot? Under what circumstances could TransactionIdIsInProgress() > > return true wher

Re: [HACKERS] Mention column name in error messages

On Sun, Aug 9, 2015 at 8:44 AM, Franck Verrot wrote: > On Wed, Jul 1, 2015 at 12:30 AM, Tom Lane wrote: >> >> What seems more likely to lead to a usable patch is to arrange for the >> extra information you want to be emitted as error "context", via an error >> context callback that gets installe

Re: [HACKERS] Patch for ginCombineData

On Wed, Aug 5, 2015 at 3:17 AM, Robert Abraham < robert.abraha...@googlemail.com> wrote: > Hello, > > we are using gin indexes on big tables. these tables happen to have > several billion rows. > the index creation fails in ginCombineData in src/backend/access/ginbulk.c > because repalloc is limit

Re: [HACKERS] Potential GIN vacuum bug

On Tue, Aug 18, 2015 at 8:59 AM, Robert Haas wrote: > On Mon, Aug 17, 2015 at 5:41 PM, Jeff Janes wrote: > > User backends attempt to take the lock conditionally, because otherwise > they > > would cause an autovacuum already holding the lock to cancel itself, > whic

Re: [HACKERS] Test code is worth the space

On Tue, Aug 18, 2015 at 3:32 PM, David Fetter wrote: > On Tue, Aug 18, 2015 at 04:54:07PM +0100, Greg Stark wrote: > > On Tue, Aug 18, 2015 at 2:16 PM, David Fetter wrote: > > > I'm given to understand that this tight coupling is necessary for > > > performance. Are you saying that it could be

Re: [HACKERS] statistics for array types

On Thu, Aug 20, 2015 at 6:00 PM, Tomas Vondra wrote: > Hi, > > On 08/11/2015 04:38 PM, Jeff Janes wrote: > >> When reviewing some recent patches, I decided the statistics gathered >> for arrays had some pre-existing shortcomings. >> >> The main one is that w

Re: [HACKERS] Resource Owner reassign Locks

On Tue, Aug 25, 2015 at 5:48 AM, Michael Paquier wrote: > On Fri, Jul 10, 2015 at 4:22 AM, Andres Freund wrote: > > On July 9, 2015 9:13:20 PM GMT+02:00, Jeff Janes > wrote: > > > >>Unfortunately I don't know what that means about the API. Does it mean > &g

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

On Fri, Jul 10, 2015 at 11:29 AM, Tomas Vondra 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 this > comment: > > * XXX this is overly conservative for partial indexes, since we will

[HACKERS] Spurious standby query cancellations

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 work. But what it does is something different than that. At l

Re: [HACKERS] patch: version_stamp.pl: Add Git commit info to version if 'git' is specified

On Fri, Aug 28, 2015 at 1:44 AM, Andres Freund wrote: > On 2015-08-28 07:48:28 +0200, Fabien COELHO wrote: > > >Salesforce did something similar in their internal build, and TBH I do > not > > >find it a good idea. The basic problem is it's completely misleading to > > >equate the last commit wi

[HACKERS] buffer README is out of date

The buffer/README section on buffer clean up locks never got updated for the creation of Heap Only Tuples and their associated compaction logic. I've attached a patch to change the explanation. I'm sure someone can word it better than I have. Cheers, Jeff

Re: [HACKERS] buffer README is out of date

On Sat, Aug 29, 2015 at 11:45 AM, Jeff Janes wrote: > The buffer/README section on buffer clean up locks never got updated for > the creation of Heap Only Tuples and their associated compaction logic. > > I've attached a patch to change the explanation. I'm sure someon

Re: [HACKERS] buffer README is out of date

On Sat, Aug 29, 2015 at 1:27 PM, Jim Nasby wrote: > On 8/29/15 2:21 PM, Jeff Janes wrote: > >> The buffer/README section on buffer clean up locks never got updated >> for the creation of Heap Only Tuples and their associated compaction >> logic. >> &

Re: [HACKERS] Potential GIN vacuum bug

On Sat, Aug 22, 2015 at 11:25 AM, Jeff Janes wrote: > On Tue, Aug 18, 2015 at 8:59 AM, Robert Haas > wrote: > >> On Mon, Aug 17, 2015 at 5:41 PM, Jeff Janes wrote: >> > User backends attempt to take the lock conditionally, because otherwise >> they >>

Re: [HACKERS] Potential GIN vacuum bug

On Sun, Aug 30, 2015 at 11:11 AM, Tom Lane wrote: > Jeff Janes writes: > > Your earlier point about how the current design throttles insertions to > keep the pending list from growing without bound seems like a bigger deal > to worry about. I think we'd like to have some

Re: [HACKERS] Potential GIN vacuum bug

On Sun, Aug 30, 2015 at 3:57 PM, Tom Lane wrote: > Jeff Janes writes: > > On Sun, Aug 30, 2015 at 11:11 AM, Tom Lane wrote: > >> Your earlier point about how the current design throttles insertions to > >> keep the pending list from growing without bound seems l

Re: [HACKERS] Buildfarm failure from overly noisy warning message

On Tue, Jul 28, 2015 at 2:38 PM, Tom Lane wrote: > Kevin Grittner writes: > > Tom Lane wrote: > >> Kevin Grittner writes: > >>> I think a LOG entry when an autovacuum process is actually canceled > >>> has value just in case it is happening on a particular table so > >>> frequently that the ta

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

On Wed, Sep 2, 2015 at 2:31 PM, Josh Berkus wrote: > On 09/02/2015 02:25 PM, Tomas Vondra wrote: > > > > As I explained, spindles have very little to do with it - you need > > multiple I/O requests per device, to get the benefit. Sure, the DBAs > > should know how many spindles they have and shou

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

On Thu, Sep 3, 2015 at 5:14 AM, Atsushi Yoshida wrote: > Hi. > > I cought a strange result. > I execute such query. > > > SELECT "attend"."lid", "attend"."status" FROM "attend" WHERE > "attend"."sid" = 325 AND "attend"."lid" IN ('ABF0010', 'ABF0010', > 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',

Re: [HACKERS] Potential GIN vacuum bug

On Mon, Aug 31, 2015 at 12:10 AM, Jeff Janes wrote: > On Sun, Aug 30, 2015 at 3:57 PM, Tom Lane wrote: > >> Jeff Janes writes: >> > On Sun, Aug 30, 2015 at 11:11 AM, Tom Lane wrote: >> > > But we would still have to deal with the >> > fact that unc

Re: [HACKERS] [PROPOSAL] Inputs on forcing VACUUM VERBOSE to write timestamp

On Thu, Sep 3, 2015 at 9:18 AM, Andres Freund wrote: > Hi, > > On 2015-09-03 21:45:52 +0530, dinesh kumar wrote: > > Forcing VACUUM VERBOSE to write timestamp, for each "INFO" entry. This > was > > raised already in this > > < > http://www.postgresql.org/message-id/20031110162349.65542.qm...@web2

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

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

On Thu, Sep 3, 2015 at 10:55 PM, Atsushi Yoshida wrote: > >> 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. > > > > > > Index Scan using idx_attend_00 on attend (cost=0.29..627.20 rows=172

Re: [HACKERS] pgbench progress with timestamp

On Sun, Aug 23, 2015 at 4:25 AM, Fabien COELHO wrote: > > It is not easy to compare events on a pgbench runs (oops, the tps is down) > with for instance events in postgres log, so as to figure out what may have > cause a given glitch. > > This patches adds an option to replace the "time since pgb

Re: [HACKERS] pgbench progress with timestamp

On Mon, Sep 7, 2015 at 11:25 PM, Fabien COELHO wrote: > > Use milliseconds for consistency with the '%n' log_prefix patch currently >>> submitted by Tomas Vondra in the CF. >>> >>> sh> ./pgbench -P 1 -N -T 100 -c 2 >>> starting vacuum...end. >>> progress: 1.0 s, 546.0 tps, lat 3.619 ms stdd

[HACKERS] ALTER INDEX...SET tab completion

I can never remember the syntax for setting index storage parameters. Is it =, TO, or just a space between the parameter name and the setting? This makes the tab completion more helpful, by providing the (mandatory) equals sign. Cheers, Jeff alter_index_tabcomplete_v1.patch Description: Binar

Re: [HACKERS] checkpointer continuous flushing

On Wed, Sep 9, 2015 at 12:12 PM, Andres Freund wrote: > On 2015-09-09 20:56:15 +0200, Fabien COELHO wrote: > > As I wrote before, FreeBSD would be a good candidate because the > > posix_fadvise seems much more reasonable than on Linux, and should be > > profitable, so it would be a pity to remove

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

On Thu, Sep 10, 2015 at 8:14 AM, Alvaro Herrera wrote: > dinesh kumar wrote: > > > Also, I was under impression that, all our TODO > > items are filtered for the real > use > > cases. Is my impression wrong. If I wanted to work on another TODO item, > > whe

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On Mon, Jan 13, 2014 at 2:36 PM, Mel Gorman wrote: > On Mon, Jan 13, 2014 at 06:27:03PM -0200, Claudio Freire wrote: > > On Mon, Jan 13, 2014 at 5:23 PM, Jim Nasby wrote: > > > On 1/13/14, 2:19 PM, Claudio Freire wrote: > > >> > > >> On Mon, Jan 13, 2014 at 5:15 PM, Robert Haas > > >> wrote: >

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Mon, Jan 13, 2014 at 6:44 PM, Dave Chinner wrote: > On Tue, Jan 14, 2014 at 02:26:25AM +0100, Andres Freund wrote: > > On 2014-01-13 17:13:51 -0800, James Bottomley wrote: > > > a file into a user provided buffer, thus obtaining a page cache entry > > > and a copy in their userspace buffer, th

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Wed, Jan 15, 2014 at 7:12 AM, Tom Lane wrote: > Heikki Linnakangas writes: > > On 01/15/2014 07:50 AM, Dave Chinner wrote: > >> FWIW [and I know you're probably sick of hearing this by now], but > >> the blk-io throttling works almost perfectly with applications that > >> use direct IO. >

Re: [HACKERS] Patch for fail-back without fresh backup

On Thu, Nov 21, 2013 at 2:43 PM, Andres Freund wrote: > On 2013-11-21 14:40:36 -0800, Jeff Janes wrote: > > But if the transaction would not have otherwise generated WAL (i.e. a > > select that did not have to do any HOT pruning, or an update with zero > rows > > matc

Re: [HACKERS] WAL Rate Limiting

On Thu, Jan 16, 2014 at 8:19 AM, Tom Lane wrote: > > > I think the usecases that would want this for DML probably also wan this > > to work for unlogged, temp tables. > > Huh? Unlogged tables generate *zero* WAL, by definition. > Transactions that only change unlogged tables still generate comm

Re: [HACKERS] Patch for fail-back without fresh backup

On Thu, Jan 16, 2014 at 9:37 AM, Andres Freund wrote: > On 2014-01-16 09:25:51 -0800, Jeff Janes wrote: > > On Thu, Nov 21, 2013 at 2:43 PM, Andres Freund >wrote: > > > > > On 2013-11-21 14:40:36 -0800, Jeff Janes wrote: > > > > But if the transaction woul

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Thu, Jan 16, 2014 at 3:23 PM, Dave Chinner wrote: > On Wed, Jan 15, 2014 at 06:14:18PM -0600, Jim Nasby wrote: > > On 1/15/14, 12:00 AM, Claudio Freire wrote: > > >My completely unproven theory is that swapping is overwhelmed by > > >near-misses. Ie: a process touches a page, and before it's >

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On Wed, Jan 15, 2014 at 2:08 AM, Mel Gorman wrote: > On Tue, Jan 14, 2014 at 09:30:19AM -0800, Jeff Janes wrote: > > > > > > That could be something we look at. There are cases buried deep in the > > > VM where pages get shuffled to the end of the LRU and get tagge

Re: [HACKERS] [Lsf-pc] Linux kernel impact on PostgreSQL performance

On Thursday, January 16, 2014, Dave Chinner > wrote: > On Thu, Jan 16, 2014 at 03:58:56PM -0800, Jeff Janes wrote: > > On Thu, Jan 16, 2014 at 3:23 PM, Dave Chinner > wrote: > > > > > On Wed, Jan 15, 2014 at 06:14:18PM -0600, Jim Nasby wrote: > > > >

Re: [HACKERS] Hard limit on WAL space used (because PANIC sucks)

On Tue, Jan 21, 2014 at 9:35 AM, Tom Lane wrote: > Simon Riggs writes: > > On 6 June 2013 16:00, Heikki Linnakangas > wrote: > >> The current situation is that if you run out of disk space while writing > >> WAL, you get a PANIC, and the server shuts down. That's awful. > > > I don't see we nee

Re: [HACKERS] Why do we let autovacuum give up?

On Thu, Jan 23, 2014 at 1:41 PM, Mark Kirkwood < mark.kirkw...@catalyst.net.nz> wrote: > On 24/01/14 10:16, Mark Kirkwood wrote: > >> On 24/01/14 10:09, Robert Haas wrote: >> >>> On Thu, Jan 23, 2014 at 4:03 PM, Mark Kirkwood >>> wrote: >>> On 24/01/14 09:49, Tom Lane wrote: > 2. Wh

Re: [HACKERS] A better way than tweaking NTUP_PER_BUCKET

On Mon, Jan 27, 2014 at 10:00 AM, Simon Riggs wrote: > On 27 January 2014 17:44, Pavel Stehule wrote: > > > This topic is interesting - we found very bad performance with hashing > large > > tables with high work_mem. MergeJoin with quicksort was significantly > > faster. > > I've seen this also

[HACKERS] Weird error messages from Windows upon client death

On windows, if the client gets terminated while sending data to the server, in a COPY for example, it results in some rather head-scratcher messages in the server log, for example: LOG: could not receive data from client: No connection could be made because the target machine actively refused it.

Re: [HACKERS] Fwd: Request for error explaination || Adding a new integer in indextupleData Structure

On Tue, Jan 28, 2014 at 10:57 AM, Rohit Goyal wrote: Hello, > > I started all the process again and configured my eclipse with raw > postgresql code. First change i made in the code is > > I added *int i; *in indextupleData structure in itup.h. > You should show us *exactly* where you added it.

Re: [HACKERS] drop duplicate buffers in OS

On Wed, Jan 15, 2014 at 10:34 AM, Robert Haas wrote: > On Wed, Jan 15, 2014 at 1:53 AM, KONDO Mitsumasa > wrote: > > I create patch that can drop duplicate buffers in OS using usage_count > > alogorithm. I have developed this patch since last summer. This feature > seems to > > be discussed in h

Re: [HACKERS] [PATCH] Use MAP_HUGETLB where supported (v3)

On Wed, Jan 29, 2014 at 4:12 AM, Heikki Linnakangas wrote: > On 01/28/2014 06:11 PM, Christian Kruse wrote: > >> Hi, >> >> attached you will find a new version of the patch, ported to HEAD, >> fixed the mentioned bug and - hopefully - dealing the the remaining >> issues. >> > > Thanks, I have com

Re: [HACKERS] Wait free LW_SHARED acquisition - v0.2

On Sun, Feb 2, 2014 at 6:00 AM, Andres Freund wrote: > > Some background: > The setups that triggered me into working on the patchset didn't really > have a pgbench like workload, the individual queries were/are more > complicated even though it's still an high throughput OLTP workload. And > the

Re: [HACKERS] narwhal and PGDLLIMPORT

On Tue, Feb 4, 2014 at 8:06 AM, Andrew Dunstan wrote: > > On 02/04/2014 10:43 AM, Tom Lane wrote: > >> >> Ugh. This problem was bad enough when I thought that it would only lead >> to link-time errors detectable in the buildfarm. If it can lead to errors >> only observable at runtime --- and ma

Re: [HACKERS] narwhal and PGDLLIMPORT

On Tue, Feb 4, 2014 at 9:26 AM, Andrew Dunstan wrote: > > On 02/04/2014 11:30 AM, Andres Freund wrote: > > > >>> >>> We have details on how to build with Mingw/Msys on Windows on an Amazon >>> >>> VM which is >>> either >>> free or very cheap.

Re: [HACKERS] integrate pg_upgrade analyze_new_cluster.sh into vacuumdb

On Tue, Jan 21, 2014 at 9:06 AM, Oskari Saarenmaa wrote: > 09.01.2014 05:15, Peter Eisentraut kirjoitti: > > pg_upgrade creates a script analyze_new_cluster.{sh|bat} that runs >> vacuumdb --analyze-only in three stages with different statistics target >> settings to get a fresh cluster analyzed

Re: [HACKERS] [PERFORM] encouraging index-only scans

On Mon, Feb 3, 2014 at 8:55 AM, Robert Haas wrote: > I've also had some further thoughts about the right way to drive > vacuum scheduling. I think what we need to do is tightly couple the > rate at which we're willing to do vacuuming to the rate at which we're > incurring "vacuum debt". That

Re: [HACKERS] Minor performance improvement in transition to external sort

On Tue, Feb 4, 2014 at 2:22 PM, Jeremy Harris wrote: > The attached patch replaces the existing siftup method for heapify with > a siftdown method. Tested with random integers it does 18% fewer > compares and takes 10% less time for the heapify, over the work_mem > range 1024 to 1048576. > Thank

Re: [HACKERS] WIP patch for Todo Item : Provide fallback_application_name in contrib/pgbench, oid2name, and dblink

On Wed, Jul 4, 2012 at 12:41 PM, Robert Haas wrote: > On Tue, Jul 3, 2012 at 11:36 PM, Amit Kapila > wrote: > > Hi Shigeru/Robert, > > > >> The way fixing oid2name and pgbench seems reasonable, so applying it to > >> vacuumlo (as Peter mentioned) would be enough for this issue. > > > > Shall I c

Re: [HACKERS] WIP patch for Todo Item : Provide fallback_application_name in contrib/pgbench, oid2name, and dblink

On Sun, Feb 9, 2014 at 4:56 PM, Robert Haas wrote: > On Sun, Feb 9, 2014 at 6:33 PM, Jeff Janes wrote: > > > > Since this commit (17676c785a95b2598c573), pgbench no longer uses > .pgpass to > > obtain passwords, but instead prompts for a password > > > &g

Re: [HACKERS] [PERFORM] encouraging index-only scans

On Tue, Feb 11, 2014 at 9:12 AM, Bruce Momjian wrote: > On Tue, Feb 11, 2014 at 11:28:36AM -0500, Robert Haas wrote: > > A sequential scan will set hint bits and will prune the page, but > > pruning the page doesn't ever mark it all-visible; that logic is > > entirely in vacuum. If that could be

Re: [HACKERS] HBA files w/include support?

On Fri, Feb 14, 2014 at 6:33 AM, Bruce Momjian wrote: > On Fri, Feb 14, 2014 at 03:28:23AM -0500, Stephen Frost wrote: > > Bruce, > > > Having @include and directory.d-style capabilities for pg_hba.conf *and* > > pg_ident.conf would make managing larger environments much better. > > There has bee

Re: [HACKERS] HBA files w/include support?

On Sunday, February 16, 2014, Jim Nasby wrote: > On 2/14/14, 1:06 PM, Jeff Janes wrote: > >> On Fri, Feb 14, 2014 at 6:33 AM, Bruce Momjian > br...@momjian.us>> wrote: >> >> On Fri, Feb 14, 2014 at 03:28:23AM -0500, Stephen Frost wrote: >> &g

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

On Sun, Feb 16, 2014 at 6:26 PM, Robert Haas wrote: > The current bgwriter_lru_maxpages value limits the background writer > to a maximum of 4MB/s. If one imagines shared_buffers = 8GB, that > starts to seem rather low, but I don't have a good feeling for what a > better value would be. > I

Re: [HACKERS] Do you know the reason for increased max latency due to xlog scaling?

On Tue, Feb 18, 2014 at 3:49 AM, MauMau wrote: > From: "Andres Freund" > >> On 2014-02-18 01:35:52 +0900, MauMau wrote: >> >>> For example, please see the max latencies of test set 2 (PG 9.3) and test >>> set 4 (xlog scaling with padding). They are 207.359 and 1219.422 >>> respectively. The th

Re: [HACKERS] Do you know the reason for increased max latency due to xlog scaling?

On Tue, Feb 18, 2014 at 9:12 AM, Heikki Linnakangas wrote: > On 02/18/2014 06:27 PM, Jeff Janes wrote: > >> On Tue, Feb 18, 2014 at 3:49 AM, MauMau wrote: >> >> --- or in other words, greater variance in response times. With my >>> simple >>> und

Re: [HACKERS] Storing the password in .pgpass file in an encrypted format

On Fri, Feb 21, 2014 at 7:04 AM, Alvaro Herrera wrote: > Euler Taveira wrote: > > On 21-02-2014 09:49, firoz e v wrote: > > > Even though, there are ways to set the permissions on .pgpass, to > disallow any access to world or group, the security rules of many > organizations disallow to hold any k

Re: [HACKERS] Storing the password in .pgpass file in an encrypted format

On Fri, Feb 21, 2014 at 8:42 AM, Craig Ringer wrote: > On 02/22/2014 12:20 AM, Alvaro Herrera wrote: > > Jeff Janes escribió: > >> On Fri, Feb 21, 2014 at 7:04 AM, Alvaro Herrera < > alvhe...@2ndquadrant.com>wrote: > > > >>> If you were to have a me

Re: [HACKERS] often PREPARE can generate high load (and sometimes minutes long unavailability)

On Sun, Feb 23, 2014 at 11:04 AM, Pavel Stehule wrote: > Hello > > I got a example of code, that generate relatively high load with minimal > connections. > > This code is +/- bad - it repeatedly generate prepare statement, but > somewhere uses prepared statements as protections against SQL inject

Re: [HACKERS] often PREPARE can generate high load (and sometimes minutes long unavailability)

On Mon, Feb 24, 2014 at 7:02 AM, Pavel Stehule wrote: > > > > 2014-02-23 21:32 GMT+01:00 Andres Freund : > > Hi, >> >> On 2014-02-23 20:04:39 +0100, Pavel Stehule wrote: >> > There is relative few very long ProcArrayLocks lwlocks >> > >> > This issue is very pathologic on fast computers with more

Re: [HACKERS] GSoC on WAL-logging hash indexes

On Mon, Mar 3, 2014 at 8:12 AM, Robert Haas wrote: > > Unfortunately, I don't believe that it's possible to do this easily > today because of the way bucket splits are handled. I wrote about > this previously here, with an idea for solving the problem: > > > http://www.postgresql.org/message-id/

Re: [HACKERS] Disable hot-update functionality

On Wed, Mar 5, 2014 at 12:32 PM, Rohit Goyal wrote: > Hi All, > > Is there any ways by which i can disable the hot-update functionality? > Build an index on a volatile column. For example, to force pgbench to bypass HOT updates for testing purposes I build an index on pgbench_accounts (abalance

Re: [HACKERS] GSoC on WAL-logging hash indexes

On Thu, Mar 6, 2014 at 11:34 AM, Robert Haas wrote: > > Putting the split-in-progress flag in the new bucket's primary page > makes a lot of sense. I don't have any problem with dumping the rest > of it for a first cut if we have a different long-term plan for how to > improve concurrency, but I

Re: [HACKERS] Selection of join algorithm.

On Sat, Mar 8, 2014 at 6:18 AM, Ishaya Bhatt wrote: > Hi, > > I am trying to analyze join performance. But I see that even for a table > having 100,000 rows and join attribute as primary key, postgres always > performs hash join. > > Can anyone please tell me under which conditions merge join or

Re: [HACKERS] pg_upgrade on high number tables database issues

On Mon, Mar 10, 2014 at 6:58 AM, Pavel Stehule wrote: > Hello > > I had to migrate our databases from 9.1 to 9.2. We have high number of > databases per cluster (more than 1000) and high number of tables (indexes) > per database (sometimes more than 10K, exceptionally more than 100K). > > I seen t

Re: [HACKERS] Autovacuum different in 9.2.4?

On Mon, Aug 5, 2013 at 11:16 AM, Joshua D. Drake wrote: > > Hello, > > I seem to recall autovacuum changes landing for 9.2.4. Can someone please > describe what those changes were and how they could affect usage? Those landed in 9.2.3, see release notes for that version: Fix performance problems

Re: [HACKERS] Moving 'hot' pages from buffer pool to heap

On Mon, Aug 5, 2013 at 12:36 PM, Atri Sharma wrote: > Hi all, > > I was experimenting with the idea of moving hot buffer pages from the > buffer pool to heap, Which heap do you mean here? Alas, half the data structures used in CS are called "heap". I can't think of any of them that are good cand

Re: [HACKERS] [9.3 bug] disk space in pg_xlog increases during archive recovery

On Wed, Aug 7, 2013 at 7:03 AM, Fujii Masao wrote: > On Fri, Aug 2, 2013 at 12:24 AM, MauMau wrote: >> From: "Fujii Masao" >> However, isn't StandbyRequested true (= standby_mode set to on) to enable warm standby? >>> >>> >>> We can set up warm-standby by using pg_standby even if stand

Re: [HACKERS] killing pg_dump leaves backend process

On Sat, Aug 10, 2013 at 4:26 AM, Greg Stark wrote: > > The problem is that I don't know of any way to detect eof on a socket > other than trying to read from it (or calling poll or select). So the > server would have to periodically poll the client even when it's not > expecting any data. The inef

Re: [HACKERS] Modyfication Sort Merge Join Alghoritm

On Mon, Aug 12, 2013 at 1:31 PM, tubadzin wrote: > Hi users. > I want to sure, that is no this implementation in Postgresql: > For Sorte Merge Join Alghoritm: > If the large input arrives sorted, > g-join joins its pages with the buffer pool contents by strictly > increasing join key values and t

Re: [HACKERS] Foreground vacuum and buffer access strategy

Reviving a very old thread, because I've run into the issue again. On Tue, May 29, 2012 at 11:58 AM, Robert Haas wrote: > On Fri, May 25, 2012 at 4:06 PM, Jeff Janes wrote: >> If I invoke vacuum manually and do so with VacuumCostDelay == 0, I >> have basically declared my in

Re: [HACKERS] danger of stats_temp_directory = /dev/shm

On Thu, Apr 25, 2013 at 8:24 AM, Peter Eisentraut wrote: > On 4/25/13 12:09 AM, Tom Lane wrote: >> I think we need it fixed to reject any stats_temp_directory that is not >> postgres-owned with restrictive permissions. The problem here is not >> with what it deletes, it's with the insanely insecu

Re: [HACKERS] danger of stats_temp_directory = /dev/shm

On Tuesday, August 13, 2013, Josh Berkus wrote: > On 08/13/2013 09:57 AM, Jeff Janes wrote: > > Is this a blocker for 9.3? > > Why would it be? This issue doesn't originate with 9.3. > Before 9.3, it would delete one specific file from a potentially shared directory.

[HACKERS] pgstat_reset_remove_files ignores its argument

in 9.3 and 9.4, pgstat_reset_remove_files uses the global variable pgstat_stat_directory rather than the argument it is passed, "directory". On crash recovery, this means the tmp directory gets cleared twice and the permanent pg_stat doesn't get cleared at all. It seems like the obvious one line

Re: [HACKERS] pgstat_reset_remove_files ignores its argument

On Fri, Aug 16, 2013 at 12:38 PM, Robert Haas wrote: > On Wed, Aug 14, 2013 at 12:13 AM, Jeff Janes wrote: >> in 9.3 and 9.4, pgstat_reset_remove_files uses the global variable >> pgstat_stat_directory rather than the argument it is passed, "directory". >> On cra

Re: [HACKERS] StrategyGetBuffer optimization, take 2

On Mon, Aug 5, 2013 at 8:49 AM, Merlin Moncure wrote: > > *) What I think is happening: > I think we are again getting burned by getting de-scheduled while > holding the free list lock. I've been chasing this problem for a long > time now (for example, see: > http://postgresql.1045698.n5.nabble.co

Re: [HACKERS] StrategyGetBuffer optimization, take 2

On Wed, Aug 7, 2013 at 7:40 AM, Merlin Moncure wrote: > I agree; at least then it's not unambiguously better. if you (in > effect) swap all contention on allocation from a lwlock to a spinlock > it's not clear if you're improving things; it would have to be proven > and I'm trying to keep things

Re: [HACKERS] danger of stats_temp_directory = /dev/shm

On Monday, August 19, 2013, Alvaro Herrera wrote: > Tom Lane wrote: > > Alvaro Herrera > writes: > > > > Here's the second attachment. > > > > This looks good except that it can't tell "db_123.statfoo" isn't a match. > > The scan limit/buffer size needs to be greater than the longest string > > yo

Re: [HACKERS] Back-patch change in hashed DISTINCT estimation?

On Wed, Aug 21, 2013 at 4:05 AM, Andres Freund wrote: > On 2013-08-20 17:24:18 -0400, Tom Lane wrote: >> In a thread over in pgsql-performance, Tomas Vondra pointed out that >> choose_hashed_distinct was sometimes making different choices than >> choose_hashed_grouping, so that queries like these:

[HACKERS] Allow child table to be missing nullable column from parent.

Currently a child table has to have all the columns the parent table has: create table foo1 (x integer, y text, z bool); create table foo2 (x integer, y text, q text); alter table foo2 inherit foo1 ; ERROR: child table is missing column "z" In theory it seems like this could be allowed as long a

[HACKERS] Effectiveness of enable_material = off

I recently could not shift a plan off of using a materialize, to see what other options were out there, by setting enable_material to off. >From src/backend/optimizer/path/costsize.c: * We don't test the value of enable_material here, because * materialization is required for correctne

[HACKERS] Re: [HACKERS] Is it necessary to rewrite table while increasing the scale of datatype numeric？

On Tue, Sep 3, 2013 at 9:08 PM, wrote: > Hi, Hackers! > > I find that it takes a long time when I increase the scale of a numeric > datatype. > By checking the code, I found that's because it needs to rewrite that > table's file. > After checking that table's data file, I found only parameter n_h

[HACKERS] Re: [HACKERS] Is it necessary to rewrite table while increasing the scale of datatype numeric？

On Wed, Sep 4, 2013 at 10:06 PM, wrote: > 于 2013-09-04 23:41, Jeff Janes 回复: > >> On Tue, Sep 3, 2013 at 9:08 PM, wrote: >>> >>> Hi, Hackers! >>> >>> I find that it takes a long time when I increase the scale of a numeric >>> datatype.

<    1   2   3   4   5   6   7   8   9   10   >