Re: [HACKERS] WIP: Enhanced ALTER OPERATOR

2015-07-14 Thread Jeff Janes
On Tue, Jul 14, 2015 at 8:22 AM, Heikki Linnakangas wrote: > On 07/13/2015 03:43 PM, Uriy Zhuravlev wrote: > >> Hello hackers. >> >> Attached is a new version of patch: >> * port syntax from NULL to truth NONE >> * fix documentation (thanks Heikki) >> * rebase to master >> > > Thanks, committed

[HACKERS] intarray planning/exeuction problem with multiple operators

2015-07-12 Thread Jeff Janes
I've found an interesting performance problem in the intarray extension module. It doesn't seem to be version dependent, I've verified it in 9.4.4 and 9.6devel. If I do a query that involves both an = op and a @@ op ANDed together, it gives a high cost estimate, which is justified by the slow run

[HACKERS] ToDo list update for BRIN indexes

2015-07-09 Thread Jeff Janes
Is there anything in the below section which has been been implemented or rendered irrelevant by BRIN indexes? https://wiki.postgresql.org/wiki/Todo#Indexes "Consider smaller indexes that record a range of values per heap page, rather than having one index entry for every heap row" Cheers, Jeff

Re: [HACKERS] Resource Owner reassign Locks

2015-07-09 Thread Jeff Janes
On Fri, Jul 3, 2015 at 12:59 AM, Andres Freund wrote: > On 2015-06-07 13:44:08 -0700, Jeff Janes wrote: > > I'd like to advocate for back-patching this to 9.0, 9.1, and 9.2. It has > > run without problems for a while now, and it can be considered a bug that > > system

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

2015-07-09 Thread Jeff Janes
On Wed, Jul 8, 2015 at 10:10 PM, Sawada Masahiko wrote: > On Thu, Jul 9, 2015 at 4:31 AM, Jeff Janes wrote: > > On Fri, Jul 3, 2015 at 1:25 AM, Sawada Masahiko > > wrote: > >> > >> It's impossible to have VM bits set to frozen but not visible. > >&

Re: [HACKERS] optimizing vacuum truncation scans

2015-07-08 Thread Jeff Janes
On Wed, Jul 8, 2015 at 9:46 PM, Haribabu Kommi wrote: > On Mon, Jun 29, 2015 at 3:54 PM, Jeff Janes wrote: > > > > Attached is a patch that implements the vm scan for truncation. It > > introduces a variable to hold the last blkno which was skipped during the > > f

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

2015-07-08 Thread Jeff Janes
On Fri, Jul 3, 2015 at 1:25 AM, Sawada Masahiko wrote: > On Fri, Jul 3, 2015 at 1:23 AM, Simon Riggs wrote: > > On 2 July 2015 at 16:30, Sawada Masahiko wrote: > > > >> > >> Also, the flags of each heap page header might be set PD_ALL_FROZEN, > >> as well as all-visible > > > > > > Is it possib

Re: [HACKERS] 9.5 alpha: some small comments on BRIN and btree_gin

2015-07-06 Thread Jeff Janes
On Mon, Jul 6, 2015 at 12:20 AM, Marc Mamin wrote: > Hello, > > First: KUDO !!! > The release notes are extremely promising in regard to performance > improvements :-) > > > I've made some (dirty) tests with BRIN and btree_gin. > (on a smalll Windows laptop ...) > > just a few remarks: > > > - bt

Re: [HACKERS] pg_trgm version 1.2

2015-06-30 Thread Jeff Janes
On Tue, Jun 30, 2015 at 2:46 AM, Alexander Korotkov < a.korot...@postgrespro.ru> wrote: > On Sun, Jun 28, 2015 at 1:17 AM, Jeff Janes wrote: > >> This patch implements version 1.2 of contrib module pg_trgm. >> >> This supports the triconsistent function, intro

Re: [HACKERS] LWLock deadlock and gdb advice

2015-06-30 Thread Jeff Janes
On Mon, Jun 29, 2015 at 11:28 PM, Jeff Janes wrote: > On Mon, Jun 29, 2015 at 5:55 PM, Peter Geoghegan wrote: > >> On Mon, Jun 29, 2015 at 5:37 PM, Jeff Janes wrote: >> > Is there a way to use gdb to figure out who holds the lock they are >> waiting >> > for

Re: [HACKERS] LWLock deadlock and gdb advice

2015-06-29 Thread Jeff Janes
On Mon, Jun 29, 2015 at 5:55 PM, Peter Geoghegan wrote: > On Mon, Jun 29, 2015 at 5:37 PM, Jeff Janes wrote: > > Is there a way to use gdb to figure out who holds the lock they are > waiting > > for? > > Have you considered building with LWLOCK_STATS defined, and LO

[HACKERS] LWLock deadlock and gdb advice

2015-06-29 Thread Jeff Janes
I have a 9.5alpha1 cluster which is locked up. All the user back ends seem to be waiting on semop, eventually on WALInsertLockAcquire. Is there a way to use gdb to figure out who holds the lock they are waiting for? It is compiled with both debug and cassert. I am hoping someone can give me rec

Re: [HACKERS] PANIC in GIN code

2015-06-29 Thread Jeff Janes
On Mon, Jun 29, 2015 at 2:08 PM, Heikki Linnakangas wrote: > > Now kill -9 postmaster, and restart. Voila, the page headers are all zeros: > > postgres=# select * from page_header(get_raw_page('i_foo', 0)); > lsn| checksum | flags | lower | upper | special | pagesize | > version | > prun

Re: [HACKERS] optimizing vacuum truncation scans

2015-06-28 Thread Jeff Janes
On Tue, May 26, 2015 at 12:37 PM, Jeff Janes wrote: > On Mon, Apr 20, 2015 at 10:18 AM, Jim Nasby > wrote: > >> On 4/20/15 1:50 AM, Jeff Janes wrote: >> >>> >>> For that matter, why do we scan backwards anyway? The comments don't >>>

Re: [HACKERS] Refactoring pgbench.c

2015-06-28 Thread Jeff Janes
On Sat, Jun 27, 2015 at 5:10 PM, Tatsuo Ishii wrote: > Main pgbench logic consists of single file pgbench.c which is 4036 > lines of code as of today. This is not a small number and I think it > would be nice if it is divided into smaller files because it will make > it easier to maintain, add or

Re: [HACKERS] PANIC in GIN code

2015-06-28 Thread Jeff Janes
On Sun, Jun 28, 2015 at 12:31 PM, Heikki Linnakangas wrote: > On 06/26/2015 10:53 PM, Jeff Janes wrote: > >> On Fri, Jun 26, 2015 at 11:40 AM, Heikki Linnakangas >> wrote: >> >> The page is being split (that's evident from "info=48" above). >&

[HACKERS] pg_trgm version 1.2

2015-06-27 Thread Jeff Janes
This patch implements version 1.2 of contrib module pg_trgm. This supports the triconsistent function, introduced in version 9.4 of the server, to make it faster to implement indexed queries where some keys are common and some are rare. I've included the paths to both upgrade and downgrade betwee

Re: [HACKERS] PANIC in GIN code

2015-06-26 Thread Jeff Janes
On Fri, Jun 26, 2015 at 11:40 AM, Heikki Linnakangas wrote: > On 06/26/2015 08:02 PM, Jeff Janes wrote: > >> Under high load against f7bb7f0625771bc71869cda, I occasionally get: >> >> PANIC: XLogBeginInsert was not called >> >> It seems to only come from vacu

Re: [HACKERS] BRIN index bug due to WAL refactoring

2015-06-26 Thread Jeff Janes
On Fri, Jun 26, 2015 at 11:35 AM, Alvaro Herrera wrote: > Jeff Janes wrote: > > > On replica: > > > > set enable_seqscan TO off; > > explain (analyze) select count(*) from foobar ; > > ERROR: corrupted BRIN index: inconsistent range map > > Nice. A

[HACKERS] PANIC in GIN code

2015-06-26 Thread Jeff Janes
Under high load against f7bb7f0625771bc71869cda, I occasionally get: PANIC: XLogBeginInsert was not called It seems to only come from vacuuming. Here is an example back-trace: #0 0x003dcb632625 in raise () from /lib64/libc.so.6 #1 0x003dcb633e05 in abort () from /lib64/libc.so.6 #2

Re: [HACKERS] GIN: Implementing triConsistent and strategy number

2015-06-26 Thread Jeff Janes
On Fri, Jun 26, 2015 at 7:13 AM, Heikki Linnakangas wrote: > On 06/24/2015 11:11 PM, Jeff Janes wrote: > >> Is there a way to implement triConsistent for only some of the strategy >> numbers? >> > > No. > > It looks like I would have to in

[HACKERS] BRIN index bug due to WAL refactoring

2015-06-25 Thread Jeff Janes
BRIN index WAL is broken in HEAD. Commit 2c03216d831160bedd72d4, the Revamp the WAL record format, is the culprit. The easiest way to see this is via streaming replication. On master: create table foobar as select * from generate_series(1,1); create index on foobar using brin (generate_seri

Re: [HACKERS] pgbench - allow backslash-continuations in custom scripts

2015-06-25 Thread Jeff Janes
On Wed, Jun 24, 2015 at 1:22 PM, Josh Berkus wrote: > On 06/21/2015 01:37 PM, Fabien COELHO wrote: > > > >> The worst case with pgbench is that we break two people's test scripts, > >> they read the release notes, and fix them. > > > > Sure, I agree that breaking pgbench custom scripts compatibil

[HACKERS] GIN: Implementing triConsistent and strategy number

2015-06-24 Thread Jeff Janes
Is there a way to implement triConsistent for only some of the strategy numbers? It looks like I would have to internally re-implement something like shimTriConsistentFn for each strategy number for which I don't want to implement the ternary system in full. Am I missing a trick? Thanks, Jeff

Re: [HACKERS] pg_stat_*_columns?

2015-06-24 Thread Jeff Janes
On Sat, Jun 20, 2015 at 7:20 AM, Robert Haas wrote: > On Sat, Jun 20, 2015 at 10:12 AM, Joel Jacobson wrote: > > Is there any chance the project would accept a patch which adds the > > pg_stat_*_columns-feature without first optimizing the collector? > > I doubt it. It's such a pain point alrea

Re: [HACKERS] pg_stat_*_columns?

2015-06-24 Thread Jeff Janes
On Mon, Jun 15, 2015 at 7:47 PM, Jim Nasby wrote: > On 6/8/15 3:26 PM, Joel Jacobson wrote: > >> So I've heard from Magnus Hagander today IRL at our Stockholm PostgreSQL >> User Group meeting where we discussed this idea. He told me the overhead >> in the statistics collector is mainly when readi

[HACKERS] btree_gin and BETWEEN

2015-06-23 Thread Jeff Janes
If I use the btree_gin extension to build a gin index on a scalar value, it doesn't work well with BETWEEN queries. It looks like it scans the whole index, with the part of the index between the endpoints getting scanned twice. It is basically executed as if "col1 between x and y" were "col1 betw

Re: [HACKERS] pretty bad n_distinct estimate, causing HashAgg OOM on TPC-H

2015-06-21 Thread Jeff Janes
On Sat, Jun 20, 2015 at 8:28 AM, Tomas Vondra wrote: Hi Tomas, I've lobotomized the sampling a bit to really produce a random set of > blocks first, and that produces way better estimates: > >statistics target estimate random >--

Re: [HACKERS] pretty bad n_distinct estimate, causing HashAgg OOM on TPC-H

2015-06-21 Thread Jeff Janes
On Sat, Jun 20, 2015 at 9:55 AM, Tomas Vondra wrote: > Hi, > > On 06/20/2015 03:01 AM, Jeff Janes wrote: > >> >> >> I don't think we need to really assume the density to be constant, >> maybe we can verify that while collecting the sample? I mea

Re: [HACKERS] pretty bad n_distinct estimate, causing HashAgg OOM on TPC-H

2015-06-19 Thread Jeff Janes
On Fri, Jun 19, 2015 at 1:39 PM, Tomas Vondra wrote: > On 06/19/2015 09:48 PM, Jeff Janes wrote: > >> On Fri, Jun 19, 2015 at 12:27 PM, Tomas Vondra >> mailto:tomas.von...@2ndquadrant.com>> >> wrote: >> >> But I think you might be on to something, be

Re: [HACKERS] pretty bad n_distinct estimate, causing HashAgg OOM on TPC-H

2015-06-19 Thread Jeff Janes
On Fri, Jun 19, 2015 at 12:27 PM, Tomas Vondra wrote: > But I think you might be on to something, because I manually collected a > random sample with 30k rows (by explicitly generating 30k random TIDs), and > I get this: > > tpch=# select cnt, count(*) from (select l_orderkey, count(*) AS cnt fr

Re: [HACKERS] pretty bad n_distinct estimate, causing HashAgg OOM on TPC-H

2015-06-19 Thread Jeff Janes
On Wed, Jun 17, 2015 at 10:52 AM, Tomas Vondra wrote: > Hi, > > I'm currently running some tests on a 3TB TPC-H data set, and I tripped > over a pretty bad n_distinct underestimate, causing OOM in HashAgg (which > somehow illustrates the importance of the memory-bounded hashagg patch Jeff > Davis

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

2015-06-11 Thread Jeff Janes
On Wed, Jun 10, 2015 at 7:16 PM, Noah Misch wrote: > On Mon, Jun 08, 2015 at 03:15:04PM +0200, Andres Freund wrote: > > One more thing: > > Our testing infrastructure sucks. Without writing C code it's basically > > impossible to test wraparounds and such. Even if not particularly useful > > for

Re: [HACKERS] pg_xlog -> pg_xjournal?

2015-06-10 Thread Jeff Janes
On Tue, Jun 2, 2015 at 2:45 AM, Mark Kirkwood wrote: > On 01/06/15 05:29, Joel Jacobson wrote: > >> While anyone who is familiar with postgres would never do something as >> stupid as to delete pg_xlog, >> according to Google, there appears to be a fair amount of end-users out >> there having mad

Re: [HACKERS] skipping pg_log in basebackup (was Re: pg_basebackup and pg_stat_tmp directory)

2015-06-10 Thread Jeff Janes
On Wed, Jun 10, 2015 at 8:29 AM, Robert Haas wrote: > On Mon, Jun 8, 2015 at 12:09 AM, Michael Paquier > wrote: > >> Recently, one of our customers has had a basebackup fail because pg_log > >> contained files that were >8GB: > >> FATAL: archive member "pg_log/postgresql-20150119.log" too large

Re: [HACKERS] Checkpoints vs restartpoints

2015-06-09 Thread Jeff Janes
On Tue, Jun 9, 2015 at 4:20 PM, Thomas Munro wrote: > Hi > > Why do standby servers not simply treat every checkpoint as a > restartpoint? As I understand it, setting checkpoint_timeout and > checkpoint_segments higher on a standby server effectively instruct > standby servers to skip some check

Re: [HACKERS] Resource Owner reassign Locks

2015-06-07 Thread Jeff Janes
On Thu, Jun 21, 2012 at 5:32 AM, Heikki Linnakangas < heikki.linnakan...@enterprisedb.com> wrote: > On 18.06.2012 13:59, Heikki Linnakangas wrote: > >> On 10.06.2012 23:39, Jeff Janes wrote: >> I found the interface between resowner.c and lock.c a bit confusing. >> r

Re: [HACKERS] [CORE] Restore-reliability mode

2015-06-07 Thread Jeff Janes
On Sat, Jun 6, 2015 at 7:35 AM, Geoff Winkless wrote: > On 6 June 2015 at 13:41, Sehrope Sarkuni wrote: > >> >> > It's much easier to work into dev/test setups if there are system >> packages as it's just a config change to an existing script. Building >> from source would require a whole new wo

Re: [HACKERS] RFC: Remove contrib entirely

2015-06-05 Thread Jeff Janes
On Fri, Jun 5, 2015 at 7:42 AM, Joshua D. Drake wrote: > > On 06/05/2015 04:56 AM, Robert Haas wrote: > somewhere else. At least not that I can see. > >> >> 4. Eliminate the EGO of saying "I have a contrib module in core" >>> >> >> I've got multiple major features in core. Any ego I may have a

Re: [HACKERS] xid wrap / optimize frozen tables?

2015-06-04 Thread Jeff Janes
On Wed, Jun 3, 2015 at 2:49 PM, Jeff Janes wrote: > I would not be surprised if it were the reading, not the writing, which > caused the performance problem. > Of course I screwed up that last sentence. I meant the opposite, it would not surprise me if it were the writing that c

Re: [HACKERS] xid wrap / optimize frozen tables?

2015-06-03 Thread Jeff Janes
On May 24, 2015 6:42 AM, "Nils Goroll" wrote: > > Hi Jeff and all, > > On 23/05/15 22:13, Jeff Janes wrote: > > Are you sure it is the read IO that causes the problem? > > Yes. Trouble is here that we are talking about a 361 GB table > >

Re: [HACKERS] RFC: Remove contrib entirely

2015-05-29 Thread Jeff Janes
On Thu, May 28, 2015 at 11:26 PM, Guillaume Lelarge wrote: > Le 29 mai 2015 8:01 AM, "Fabien COELHO" a écrit : > > > > > >> FWIW, I don't mind which one we put in core and which one we put out of > >> core. But I like Joshua's idea of getting rid of contribs and pushing > them > >> out as any ot

Re: [HACKERS] RFC: Remove contrib entirely

2015-05-29 Thread Jeff Janes
On Thu, May 28, 2015 at 11:01 PM, Fabien COELHO wrote: > > FWIW, I don't mind which one we put in core and which one we put out of >> core. But I like Joshua's idea of getting rid of contribs and pushing them >> out as any other extensions. >> > > Hmmm. > > I like the contrib directory as a livi

Re: [HACKERS] [Proposal] More Vacuum Statistics

2015-05-29 Thread Jeff Janes
On Thu, May 28, 2015 at 4:08 AM, Naoya Anzai wrote: > > 2. Page visibility rate of each table > There is no way to know how many page-bits are them of each tables stored > in their visibility maps. If we can show this information, then we will be > able to guess vacuum overhead for the table. For

Re: [HACKERS] optimizing vacuum truncation scans

2015-05-26 Thread Jeff Janes
On Tue, May 26, 2015 at 12:37 PM, Jeff Janes wrote: > On Mon, Apr 20, 2015 at 10:18 AM, Jim Nasby > wrote: > >> On 4/20/15 1:50 AM, Jeff Janes wrote: >> >>> >>> For that matter, why do we scan backwards anyway? The comments don't >>>

Re: [HACKERS] Redesigning checkpoint_segments

2015-05-26 Thread Jeff Janes
On Thu, May 21, 2015 at 8:40 AM, Fujii Masao wrote: > On Thu, May 21, 2015 at 3:53 PM, Jeff Janes wrote: > > On Mon, Mar 16, 2015 at 11:05 PM, Jeff Janes > wrote: > >> > >> On Mon, Feb 23, 2015 at 8:56 AM, Heikki Linnakangas > >> wrote: > >>&

Re: [HACKERS] optimizing vacuum truncation scans

2015-05-26 Thread Jeff Janes
On Mon, Apr 20, 2015 at 10:18 AM, Jim Nasby wrote: > On 4/20/15 1:50 AM, Jeff Janes wrote: > >> >> For that matter, why do we scan backwards anyway? The comments don't >> explain it, and we have nonempty_pages as a starting point, so why >> don

Re: [HACKERS] xid wrap / optimize frozen tables?

2015-05-23 Thread Jeff Janes
On Sat, May 23, 2015 at 6:46 AM, Nils Goroll wrote: > Hi, > > as many before, I ran into the issue of a postgresql database (8.4.1) > - committing many transactions > - to huge volume tables (3-figure GB in size) > - running the xid wrap vacuum (to freeze tuples) > > where the additional read IO

Re: [HACKERS] Redesigning checkpoint_segments

2015-05-20 Thread Jeff Janes
On Mon, Mar 16, 2015 at 11:05 PM, Jeff Janes wrote: > On Mon, Feb 23, 2015 at 8:56 AM, Heikki Linnakangas < > hlinnakan...@vmware.com> wrote: > >> >> Everyone seems to be happy with the names and behaviour of the GUCs, so >> committed. > > > > The doc

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

2015-05-20 Thread Jeff Janes
On Wed, May 20, 2015 at 11:13 AM, Tom Lane wrote: > Jeff Janes writes: > > What if something like this was made to work? > > select '{"3":5}'::jsonb operator("pg_catalog"."?") '3'; > > (Where the double quotes around the ?

Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)

2015-05-20 Thread Jeff Janes
On Fri, May 15, 2015 at 1:23 PM, Dave Cramer wrote: > > > > On 15 May 2015 at 16:21, Robert Haas wrote: > >> On Fri, May 15, 2015 at 4:13 PM, Dave Cramer wrote: >> > Not sure what the point of this is: as you indicated the ship has >> sailed so >> > to speak >> >> Well, if we were to agree this

Re: [HACKERS] WALWriteLock contention

2015-05-18 Thread Jeff Janes
> > > > > My goal there was to further improve group commit. When running pgbench > > -j10 -c10, it was common to see fsyncs that alternated between flushing 1 > > transaction, and 9 transactions. Because the first one to the gate would > go > > through it and slam it on all the others, and it wou

Re: [HACKERS] WALWriteLock contention

2015-05-15 Thread Jeff Janes
On Fri, May 15, 2015 at 9:06 AM, Robert Haas wrote: > WALWriteLock contention is measurable on some workloads. In studying > the problem briefly, a couple of questions emerged: > > ... > > 2. I don't really understand why WALWriteLock is set up to prohibit > two backends from flushing WAL at t

Re: [HACKERS] PATCH: adaptive ndistinct estimator v4

2015-05-13 Thread Jeff Janes
On Thu, Apr 30, 2015 at 6:20 PM, Tomas Vondra wrote: > Hi, > > On 04/30/15 22:57, Robert Haas wrote: > >> On Tue, Mar 31, 2015 at 3:02 PM, Tomas Vondra >> wrote: >> >>> attached is v4 of the patch implementing adaptive ndistinct estimator. >>> >> >> So, I took a look at this today. It's interest

Re: [HACKERS] is possible to upgrade from 9.2 to 9.4 with pg_upgrade

2015-05-06 Thread Jeff Janes
On Wed, May 6, 2015 at 10:26 AM, Jeff Janes wrote: > On Wed, May 6, 2015 at 6:16 AM, Pavel Stehule > wrote: > >> >> >> 2015-05-06 15:15 GMT+02:00 Alvaro Herrera : >> >>> Pavel Stehule wrote: >>> > Hi >>> > >>> > I

Re: [HACKERS] is possible to upgrade from 9.2 to 9.4 with pg_upgrade

2015-05-06 Thread Jeff Janes
On Wed, May 6, 2015 at 6:16 AM, Pavel Stehule wrote: > > > 2015-05-06 15:15 GMT+02:00 Alvaro Herrera : > >> Pavel Stehule wrote: >> > Hi >> > >> > I am working on preparation the migration from 9.2 to 9.4 >> > >> > pg_upgrade fails >> > >> > pg_upgrade -b /usr/lib64/pgsql/postgresql-9.2/bin -B /

Re: [HACKERS] [PATCH] Add transforms feature

2015-04-29 Thread Jeff Janes
On Wed, Apr 29, 2015 at 11:27 AM, Robert Haas wrote: > On Tue, Apr 28, 2015 at 12:47 PM, Jeff Janes wrote: > > This commit is causing a compiler warning for me in non-cassert builds: > > > > funcapi.c: In function 'get_func_trftypes': > > funcapi.c:890:

Re: [HACKERS] Feedback on getting rid of VACUUM FULL

2015-04-29 Thread Jeff Janes
On Tue, Apr 28, 2015 at 11:32 AM, Robert Haas wrote: > On Fri, Apr 24, 2015 at 3:04 PM, Alvaro Herrera > wrote: > > > I think what we need here is something that does heap_update to tuples > > at the end of the table, moving them to earlier pages; then wait for old > > snapshots to die (the infr

Re: [HACKERS] WIP: multivariate statistics / proof of concept

2015-04-28 Thread Jeff Janes
On Tue, Apr 28, 2015 at 9:13 AM, Stephen Frost wrote: > * Jeff Janes (jeff.ja...@gmail.com) wrote: > > On Mon, Mar 30, 2015 at 5:26 PM, Tomas Vondra < > tomas.von...@2ndquadrant.com> > > wrote: > > > attached is a new version of the patch series. Aside from fixin

Re: [HACKERS] [PATCH] Add transforms feature

2015-04-28 Thread Jeff Janes
On Tue, Apr 7, 2015 at 7:55 PM, Peter Eisentraut wrote: > On 3/22/15 5:46 AM, Pavel Stehule wrote: > > Isn't better doesn't support "TRANSFORM ALL" clause? If somebody would > > to use transformations - then he have to explicitly enable it by > > "TRANSFORM FOR TYPE" ? It is safe and without poss

Re: [HACKERS] WIP: multivariate statistics / proof of concept

2015-04-28 Thread Jeff Janes
On Mon, Mar 30, 2015 at 5:26 PM, Tomas Vondra wrote: > Hello, > > attached is a new version of the patch series. Aside from fixing various > issues (crashes, memory leaks). The patches are rebased to current > master, and I also attach a few SQL scripts I used for testing (nothing > fancy, just s

Re: [HACKERS] improving speed of make check-world

2015-04-27 Thread Jeff Janes
On Sat, Apr 25, 2015 at 7:23 AM, Michael Paquier wrote: > On Sat, Apr 25, 2015 at 7:59 AM, Peter Eisentraut wrote: > > On 4/23/15 1:22 PM, Jeff Janes wrote: > >> Something about this commit (dcae5faccab64776376d354d) broke "make > >> check" in parallel

Re: [HACKERS] adding more information about process(es) cpu and memory usage

2015-04-23 Thread Jeff Janes
On Thu, Apr 23, 2015 at 10:17 AM, Heikki Linnakangas wrote: > On 04/23/2015 08:00 PM, Radovan Jablonovsky wrote: > >> During current encounters with amazon web services - RDS, the DBA does not >> have access to OS/linux shell of underlying instance. That render some >> postgresql monitoring techn

Re: [HACKERS] improving speed of make check-world

2015-04-23 Thread Jeff Janes
On Thu, Aug 14, 2014 at 10:45 PM, Peter Eisentraut wrote: > make check-world creates a temporary installation in every subdirectory > it runs a test in, which is stupid: it's very slow and uses a lot of > disk space. It's enough to do this once per run. That is the essence > of what I have impl

Re: [HACKERS] Turning off HOT/Cleanup sometimes

2015-04-20 Thread Jeff Janes
On Mon, Apr 20, 2015 at 10:33 AM, Bruce Momjian wrote: > On Thu, Apr 16, 2015 at 03:41:54PM +0100, Simon Riggs wrote: > > That is how we arrive at the idea of a cleanup limit, further enhanced > by a > > limit that applies only to dirtying clean blocks, which we have 4? > recent votes > > in favo

Re: [HACKERS] Turning off HOT/Cleanup sometimes

2015-04-20 Thread Jeff Janes
On Mon, Sep 29, 2014 at 2:13 AM, Andres Freund wrote: > > On 2014-09-28 19:51:36 +0100, Simon Riggs wrote: > > On 27 September 2014 09:29, Andres Freund wrote: > > > On 2014-09-27 10:23:33 +0300, Heikki Linnakangas wrote: > > >> This patch has gotten a fair amount of review, and has been rewritte

Re: [HACKERS] optimizing vacuum truncation scans

2015-04-19 Thread Jeff Janes
On Sun, Apr 19, 2015 at 10:38 PM, Jim Nasby wrote: > On 4/19/15 9:09 PM, Jeff Janes wrote: > >> I did literally the simplest thing I could think of as a proof of >> concept patch, to see if it would actually fix things. I just jumped >> back a certain number of

Re: [HACKERS] PATCH: adaptive ndistinct estimator v4

2015-04-19 Thread Jeff Janes
On Tue, Apr 14, 2015 at 11:45 PM, Jeff Janes wrote: > On Tue, Mar 31, 2015 at 12:02 PM, Tomas Vondra < > tomas.von...@2ndquadrant.com> wrote: > >> Hi all, >> >> attached is v4 of the patch implementing adaptive ndistinct estimator. >> > > Hi Tom

[HACKERS] optimizing vacuum truncation scans

2015-04-19 Thread Jeff Janes
After a large bulk load aborted near the end, I decided to vacuum the main table so as to not leave a huge chunk of free space in the middle of it, before re-running the bulk load. This vacuum took a frustratingly long time, as the backwards scan over the table to truncate the space did not trigge

Re: [HACKERS] pg_upgrade in 9.5 broken for adminpack

2015-04-16 Thread Jeff Janes
On Thu, Apr 16, 2015 at 11:04 PM, Jeff Janes wrote: > > On Thu, Apr 16, 2015 at 7:37 PM, Bruce Momjian wrote: > >> On Thu, Apr 16, 2015 at 07:33:50PM -0700, Jeff Janes wrote: >> > pg_upgrade was recently broken for use upgrading from a system with >

Re: [HACKERS] pg_upgrade in 9.5 broken for adminpack

2015-04-16 Thread Jeff Janes
On Thu, Apr 16, 2015 at 7:37 PM, Bruce Momjian wrote: > On Thu, Apr 16, 2015 at 07:33:50PM -0700, Jeff Janes wrote: > > pg_upgrade was recently broken for use upgrading from a system with > adminpack > > installed. > > > > Brea

[HACKERS] pg_upgrade in 9.5 broken for adminpack

2015-04-16 Thread Jeff Janes
pg_upgrade was recently broken for use upgrading from a system with adminpack installed. Breaking commit is: commit 30982be4e5019684e1772dd9170aaa53f5a8e894 Author: Peter Eisentraut Integrate pg_upgrade_support module into backend from pg_upgrade_dump_12870.log pg_restore: creating EXTEN

Re: [HACKERS] PATCH: adaptive ndistinct estimator v4

2015-04-14 Thread Jeff Janes
On Tue, Mar 31, 2015 at 12:02 PM, Tomas Vondra wrote: > Hi all, > > attached is v4 of the patch implementing adaptive ndistinct estimator. > Hi Tomas, I have a case here where the adaptive algorithm underestimates ndistinct by a factor of 7 while the default estimator is pretty close. 5MB file

Re: [HACKERS] TABLESAMPLE patch

2015-04-08 Thread Jeff Janes
On Mon, Apr 6, 2015 at 11:02 AM, Petr Jelinek wrote: > On 06/04/15 14:30, Petr Jelinek wrote: > >> On 06/04/15 11:02, Simon Riggs wrote: >> >> Are we ready for a final detailed review and commit? >>> >>> >> I plan to send v12 in the evening with some additional changes that came >> up from Amit'

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

2015-04-04 Thread Jeff Janes
On Sat, Apr 4, 2015 at 3:10 PM, Jim Nasby wrote: > On 4/3/15 12:59 AM, Sawada Masahiko wrote: > >> + case HEAPTUPLE_LIVE: >> + case HEAPTUPLE_RECENTLY_DEAD: >> + case HEAPTUPLE_INSERT_IN_PROGRESS: >> +

Re: [HACKERS] vac truncation scan problems

2015-03-31 Thread Jeff Janes
On Tue, Mar 31, 2015 at 1:29 AM, Michael Paquier wrote: > > > On Tue, Mar 31, 2015 at 3:42 PM, Jeff Janes wrote: > >> On Mon, Mar 30, 2015 at 8:54 PM, Jeff Janes wrote: >> >>> After freeing up the rows at the end of the table so it is eligible for >>>

Re: [HACKERS] vac truncation scan problems

2015-03-31 Thread Jeff Janes
On Tue, Mar 31, 2015 at 1:28 AM, Kyotaro HORIGUCHI < horiguchi.kyot...@lab.ntt.co.jp> wrote: > Hi, this is a bug in the commit 0d831389749a3baaced7b984205b9894a82444b9 . > > It allows vucuum freeze to be skipped and inversely lets regular > vacuum wait for lock. The attched patch fixes it. > > > I

Re: [HACKERS] vac truncation scan problems

2015-03-30 Thread Jeff Janes
On Mon, Mar 30, 2015 at 8:54 PM, Jeff Janes wrote: > After freeing up the rows at the end of the table so it is eligible for > truncation, then running a manual VACUUM to actually release the space, I > kept running into the problem that the truncation scan was consistently > suspen

[HACKERS] vac truncation scan problems

2015-03-30 Thread Jeff Janes
After freeing up the rows at the end of the table so it is eligible for truncation, then running a manual VACUUM to actually release the space, I kept running into the problem that the truncation scan was consistently suspended and then aborted due to a conflicting lock requested/held. But the per

[HACKERS] WAL format changes break the suppression of do-nothing checkpoints.

2015-03-30 Thread Jeff Janes
commit 2c03216d831160bedd72d45f7 has invalidated the part of the docs saying "If no WAL has been written since the previous checkpoint, new checkpoints will be skipped even if checkpoint_timeout has passed", presumably by accident. It seems that this part is no longer true when it should be true:

Re: [HACKERS] compute_index_stats is missing a CHECK_FOR_INTERRUPTS

2015-03-28 Thread Jeff Janes
On Sat, Mar 28, 2015 at 3:37 PM, Tom Lane wrote: > Jeff Janes writes: > > Analyze on functional indexes cannot be interrupted very easily. > > ... > > The attached patch fixes it, but don't vouch for its safety. > > Hm. The other per-sample-row loops in

Re: [HACKERS] proposal: row_to_array function

2015-03-28 Thread Jeff Janes
On Tue, Jan 27, 2015 at 10:58 AM, Pavel Stehule wrote: > Hi > > 2015-01-27 11:41 GMT+01:00 Pavel Stehule : > >> >> >> 2015-01-26 21:44 GMT+01:00 Jim Nasby : >> >>> On 1/25/15 4:23 AM, Pavel Stehule wrote: >>> I tested a concept iteration over array in format [key1, value1, key2, va

[HACKERS] compute_index_stats is missing a CHECK_FOR_INTERRUPTS

2015-03-28 Thread Jeff Janes
Analyze on functional indexes cannot be interrupted very easily. Example: create language plperl; create table foo1 as select x::text from generate_series(1,1000) foo (x); create table foo2 as select reverse(x) from foo1; --use a fast version to set up the demo, as we are impatient CREATE or repl

[HACKERS] compiler warnings in lwlock

2015-03-25 Thread Jeff Janes
When building with LOCK_DEBUG but without casserts, I was getting unused variable warnings. I believe this is the correct way to silence them. Cheers, Jeff silence_lwlock_lock_debug.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make c

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-25 Thread Jeff Janes
On Wed, Mar 25, 2015 at 12:45 PM, Jim Nasby wrote: > > I see 3 settings that allow people to accidentally shoot themselves in the > foot; fsync, wal_sync_method and full_page_writes. > > How about just grouping those 3 together with a bulk disclaimer along the > lines of "The following 3 settings

Re: [HACKERS] logical column ordering

2015-03-23 Thread Jeff Janes
On Mon, Mar 23, 2015 at 10:01 AM, Robert Haas wrote: > On Thu, Mar 12, 2015 at 9:57 AM, Alvaro Herrera > wrote: > > However, there's a difference between making a query silently given > > different results, and breaking it completely forcing the user to > > re-study how to write it. I think the

Re: [HACKERS] Table-level log_autovacuum_min_duration

2015-03-23 Thread Jeff Janes
On Mon, Mar 23, 2015 at 7:07 AM, Tom Lane wrote: > Alvaro Herrera writes: > > Michael Paquier wrote: > >> So a worker does not see changes in postgresql.conf once it is run and > >> processes a database, no? The launcher does run ProcessConfigFile() > >> when SIGHUP shows up though. > > > Maybe

Re: [HACKERS] PATCH: numeric timestamp in log_line_prefix

2015-03-22 Thread Jeff Janes
On Sat, Mar 21, 2015 at 4:47 PM, Tomas Vondra wrote: > Hi, > > from time to time I need to correlate PostgreSQL logs to other logs, > containing numeric timestamps - a prime example of that is pgbench. With > %t and %m that's not quite trivial, because of timezones etc. > > I propose adding two n

Re: [HACKERS] Remove fsync ON/OFF as a visible option?

2015-03-21 Thread Jeff Janes
On Sat, Mar 21, 2015 at 8:54 AM, Tom Lane wrote: > Stephen Frost writes: > > At the moment, one could look at our default postgresql.conf and the > > "turns forced synchronization on or off" and think it's something akin > > or somehow related to synchronous_commit (which is completely different

Re: [HACKERS] Redesigning checkpoint_segments

2015-03-16 Thread Jeff Janes
On Mon, Feb 23, 2015 at 8:56 AM, Heikki Linnakangas wrote: > > Everyone seems to be happy with the names and behaviour of the GUCs, so > committed. The docs suggest that max_wal_size will be respected during archive recovery (causing restartpoints and recycling), but I'm not seeing that happen

Re: [HACKERS] Patch to add functionality to specify ORDER BY in CREATE FUNCTION for SRFs

2015-03-13 Thread Jeff Janes
On Mon, Jan 5, 2015 at 7:12 AM, Atri Sharma wrote: > > Hi All, > > Please forgive if this is a repost. > > Please find attached patch for supporting ORDER BY clause in CREATE > FUNCTION for SRFs. > Hi Atri, >From the discussion, I don't know if this patch is still being proposed. If so, it need

Re: [HACKERS] logical column ordering

2015-03-13 Thread Jeff Janes
On Mon, Feb 23, 2015 at 3:09 PM, Tomas Vondra wrote: > Hi, > > attached is the result of my first attempt to make the logical column > ordering patch work. This touches a lot of code in the executor that is > mostly new to me, so if you see something that looks like an obvious > bug, it probably

Re: [HACKERS] New CF app deployment

2015-03-13 Thread Jeff Janes
On Sun, Feb 22, 2015 at 12:13 PM, Magnus Hagander wrote: > On Sun, Feb 15, 2015 at 9:46 PM, Peter Geoghegan wrote: > >> On Sun, Feb 15, 2015 at 4:59 PM, Peter Eisentraut >> wrote: >> > I think the old system where the patch submitter declared, this message >> > contains my patch, is the only on

Re: [HACKERS] procost for to_tsvector

2015-03-11 Thread Jeff Janes
On Wed, Mar 11, 2015 at 2:54 PM, Andrew Gierth wrote: > Seq Scan on comments (cost=0.00..2406.18 rows=4140 width=792) (actual > time=0.601..3946.589 rows=4056 loops=1) > > Bitmap Heap Scan on comments (cost=204.09..2404.30 rows=4140 width=792) > (actual time=2.401..11.564 rows=4056 loops=1

Re: [HACKERS] Documentation of bt_page_items()'s ctid field

2015-03-11 Thread Jeff Janes
On Mon, Mar 9, 2015 at 5:34 PM, Peter Geoghegan wrote: > On Mon, Mar 9, 2015 at 5:18 PM, Jeff Janes wrote: > >> It has a right-link (that's the easiest way to tell). > > > > > > Meaning that btpo_next is not zero? Should we say that in the patch in > so &g

Re: [HACKERS] Documentation of bt_page_items()'s ctid field

2015-03-09 Thread Jeff Janes
On Mon, Mar 9, 2015 at 4:06 PM, Peter Geoghegan wrote: > On Mon, Mar 9, 2015 at 3:51 PM, Jeff Janes wrote: > > How do I know if I am looking at a non-rightmost page? > > It has a right-link (that's the easiest way to tell). Meaning that btpo_next is not zero? Should we s

Re: [HACKERS] Documentation of bt_page_items()'s ctid field

2015-03-09 Thread Jeff Janes
On Tue, Dec 30, 2014 at 8:59 AM, Heikki Linnakangas wrote: > On 12/30/2014 04:08 AM, Peter Geoghegan wrote: > >> Attached documentation patch describes the purpose of >> bt_page_items()'s ctid field. This has come up enough times in >> disaster recovery or testing scenarios that I feel it's worth

Re: [HACKERS] Turning off HOT/Cleanup sometimes

2015-03-09 Thread Jeff Janes
On Wed, Dec 17, 2014 at 12:39 AM, Simon Riggs wrote: > On 15 December 2014 at 20:26, Jeff Janes wrote: > > > I still get the compiler error in contrib: > > > > pgstattuple.c: In function 'pgstat_heap': > > pgstattuple.c:279: error: too few argumen

Re: [HACKERS] Comparing primary/HS standby in tests

2015-03-04 Thread Jeff Janes
On Tue, Mar 3, 2015 at 7:49 AM, Andres Freund wrote: > Hi, > > I've regularly wished we had automated tests that setup HS and then > compare primary/standby at the end to verify replay worked > correctly. > > Heikki's page comparison tools deals with some of that verification, but > it's really q

Re: [HACKERS] pgbench -f and vacuum

2015-02-11 Thread Jeff Janes
On Tue, Dec 23, 2014 at 7:42 AM, Alvaro Herrera wrote: > Robert Haas wrote: > > On Mon, Dec 22, 2014 at 6:55 PM, Alvaro Herrera > > wrote: > > > Here's a completely different idea. How about we add an option that > > > means "vacuum this table before running the test" (can be given several > >

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