Re: [HACKERS] Reducing tuple overhead

On Sat, Apr 25, 2015 at 1:58 AM, Jim Nasby wrote: > > On 4/23/15 10:40 PM, Amit Kapila wrote: >> >> I agree with you and what I think one of the major reasons of bloat is that >> Index segment doesn't have visibility information due to which clearing of >> Index needs to be tied along with heap.

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

On 4/24/15 7:11 PM, Álvaro Hernández Tortosa wrote: On 24/04/15 05:24, Tom Lane wrote: ... TBH, I've got very little enthusiasm for fixing this given the number of reports of trouble from the field, which so far as I recall is zero. Álvaro's case came up through intentionally trying to create a

Re: [HACKERS] INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

On Thu, Apr 23, 2015 at 6:07 PM, Peter Geoghegan wrote: > Curious about what you think about my proposal to go back to putting > the inference specification WHERE clause within the parenthesis, since > this solves several problems, including clarifying to users that the > predicate is part of the

Re: [HACKERS] INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

* Peter Geoghegan (p...@heroku.com) wrote: > On Fri, Apr 24, 2015 at 5:50 PM, Stephen Frost wrote: > > * Peter Geoghegan (p...@heroku.com) wrote: > >> I came up with something that is along the lines of what we discussed. > >> I'll wait for you to push Dean's code, and rebase on top of that > >> b

Re: [HACKERS] INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

On Fri, Apr 24, 2015 at 5:50 PM, Stephen Frost wrote: > * Peter Geoghegan (p...@heroku.com) wrote: >> I came up with something that is along the lines of what we discussed. >> I'll wait for you to push Dean's code, and rebase on top of that >> before submitting what I came up with. Maybe this can

Re: [HACKERS] Row security violation error is misleading

Dean, * Dean Rasheed (dean.a.rash...@gmail.com) wrote: > The second patch [2] is the one that is actually relevant to this > thread. This patch is primarily to apply the RLS checks earlier, > before an update is attempted, more like a regular permissions check. > This adds a new enum to classify t

Re: [HACKERS] INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

Peter, * Peter Geoghegan (p...@heroku.com) wrote: > I came up with something that is along the lines of what we discussed. > I'll wait for you to push Dean's code, and rebase on top of that > before submitting what I came up with. Maybe this can be rolled into > my next revision if I work through

Re: [HACKERS] Row security violation error is misleading

Dean, * Dean Rasheed (dean.a.rash...@gmail.com) wrote: > On 21 April 2015 at 20:50, Stephen Frost wrote: > > Thanks a lot for this. Please take a look at the attached. > > I've given this a quick read-through, and it looks good to me. The > interaction of permissive and restrictive policies fro

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

On 24/04/15 05:24, Tom Lane wrote: Stephen Frost writes: * Bruce Momjian (br...@momjian.us) wrote: On Sun, Feb 1, 2015 at 03:54:03PM +0100, Álvaro Hernández Tortosa wrote: The problem here is that performance degrades exponentially, or worse. Speaking here from experience, we already tested

Re: [HACKERS] pg_dump: largeobject behavior issues (possible bug)

On 04/24/2015 03:41 PM, Tom Lane wrote: Given that large objects don't have any individual dependencies, one could envision fixing this by replacing the individual large-object DumpableObjects by a single placeholder to participate in the sort phase, and then when it's time to dump that, scan th

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

On 4/24/15 5:30 PM, Alvaro Herrera wrote: Jim Nasby wrote: It looks like the biggest complaint (aside from allowing a limited number of tuples to be moved) is in [1] and [2], where Tom is saying that you can't simply call heap_update() like this without holding an exclusive lock on the table. I

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

On 4/23/15 1:22 PM, Jeff Janes wrote: > Something about this commit (dcae5faccab64776376d354d) broke "make > check" in parallel conditions when started from a clean directory. It > fails with a different error each time, one example: > > make -j4 check > /dev/null > > In file included from gram.

Re: [HACKERS] pg_dump: largeobject behavior issues (possible bug)

Andrew Dunstan writes: > On 04/23/2015 04:04 PM, Andrew Gierth wrote: >> The relevant code is getBlobs in pg_dump.c, which queries the whole of >> pg_largeobject_metadata without using a cursor (so the PGresult is >> already huge thanks to having >100 million rows), and then mallocs a >> BlobInfo

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

Jim Nasby wrote: > It looks like the biggest complaint (aside from allowing a limited number of > tuples to be moved) is in [1] and [2], where Tom is saying that you can't > simply call heap_update() like this without holding an exclusive lock on the > table. Is that because we're not actually cha

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

On 4/24/15 3:34 PM, Alvaro Herrera wrote: Jim Nasby wrote: Honestly, I'd prefer we exposed some way to influence where a new tuple gets put, and perhaps better ways of accessing tuples on a specific page. That would make it a lot easier to handle this in userspace, but it would also make it eas

Re: [HACKERS] Ignoring some binaries generated in src/test

Alvaro Herrera writes: > Tom Lane wrote: >> The current logic in src/test/Makefile, particularly the way that >> the modules subdirectory is handled, seems pretty ugly/convoluted >> anyway. I wonder why it was done that way rather than just ensuring >> that modules/ doesn't do anything for "make

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

Jim Nasby wrote: > Honestly, I'd prefer we exposed some way to influence where a new tuple gets > put, and perhaps better ways of accessing tuples on a specific page. That > would make it a lot easier to handle this in userspace, but it would also > make it easier to do things like concurrent clus

Re: [HACKERS] Replication identifiers, take 4

On April 24, 2015 10:26:23 PM GMT+02:00, Peter Eisentraut wrote: >On 4/24/15 8:32 AM, Andres Freund wrote: >> On 2015-04-20 11:26:29 +0300, Heikki Linnakangas wrote: >>> On 04/17/2015 11:54 AM, Andres Freund wrote: I've attached a rebased patch, that adds decision about origin >logging

Re: [HACKERS] Reducing tuple overhead

On 4/23/15 10:40 PM, Amit Kapila wrote: I agree with you and what I think one of the major reasons of bloat is that Index segment doesn't have visibility information due to which clearing of Index needs to be tied along with heap. Now if we can move transaction information at page level, then we

Re: [HACKERS] Replication identifiers, take 4

On 4/24/15 8:32 AM, Andres Freund wrote: > On 2015-04-20 11:26:29 +0300, Heikki Linnakangas wrote: >> On 04/17/2015 11:54 AM, Andres Freund wrote: >>> I've attached a rebased patch, that adds decision about origin logging >>> to the relevant XLogInsert() callsites for "external" 2 byte identifiers

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

On 4/24/15 2:04 PM, Alvaro Herrera wrote: Heikki Linnakangas wrote: Hmm, AFAICT pg_reorg is much more complex, writing stuff to a temp table and swapping relfilenodes afterwards. More like the VACUUM REWRITE that's been discussed. For the kicks, I looked at what it would take to write a utilit

Re: [HACKERS] Improving vacuum/VM/etc

On 4/24/15 6:50 AM, Robert Haas wrote: Thanks for looking at this. On Thu, Apr 23, 2015 at 3:09 PM, Jim Nasby wrote: Unfortunately, the states I came up with using existing semantics don't look hugely useful[4], but if we take Robert's idea and make all-visible mean all-frozen, we can do much

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

On 4/24/15 6:52 AM, Robert Haas wrote: On Thu, Apr 23, 2015 at 9:03 PM, Michael Paquier wrote: On Thu, Apr 23, 2015 at 10:42 PM, Robert Haas wrote: On Thu, Apr 23, 2015 at 4:19 AM, Simon Riggs wrote: We only need a freeze/backup map for larger relations. So if we map 1000 blocks per map page

[HACKERS] collate.linux.utf8 test coverage

The optional buildfarm module that runs this test was broken by commit dcae5faccab64776376d354decda0017c648bb53 Since nobody has responded to my complaint about this, I have disabled it on crake, the only buildfarm machine that has actually been running the test, so we now have no buildfarm co

Re: [HACKERS] Ignoring some binaries generated in src/test

Tom Lane wrote: > The current logic in src/test/Makefile, particularly the way that > the modules subdirectory is handled, seems pretty ugly/convoluted > anyway. I wonder why it was done that way rather than just ensuring > that modules/ doesn't do anything for "make install"? Because we do want

Re: [HACKERS] Ignoring some binaries generated in src/test

Michael Paquier writes: > A couple of binaries in src/test, that are not part of the main make > flow, can be built but they are actually not ignored in the tree: > examples/testlibpq > examples/testlibpq2 > examples/testlibpq3 > examples/testlibpq4 > examples/testlo > exam

Re: [HACKERS] Typo in a comment in set_rel_size()

Amit Langote writes: > Attached fixes what I suppose is a typo: > * so set up a single dummy path for it. Here we only check this for > * regular baserels; if it's an otherrel, CE was already checked in > -* set_append_rel_pathlist(). > +* set_append_rel_size().

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

On 4/24/15 6:29 AM, Robert Haas wrote: On Thu, Apr 23, 2015 at 9:28 PM, Tom Lane wrote: The reason nobody's gotten around to that in the last fifteen years is that per-process rusage isn't actually all that interesting; there's too much that happens in background daemons, for instance. There'

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

Heikki Linnakangas wrote: > Hmm, AFAICT pg_reorg is much more complex, writing stuff to a temp table > and swapping relfilenodes afterwards. More like the VACUUM REWRITE > that's been discussed. > > For the kicks, I looked at what it would take to write a utility like > that. It turns out to be q

Re: [HACKERS] Proposal: knowing detail of config files via SQL

On 4/4/15 9:21 AM, Sawada Masahiko wrote: > I added documentation changes to patch is attached. > Also I tried to use memory context for allocation of guc_file_variable > in TopMemoryContext, > but it was failed access after received SIGHUP. Below is my review of the v5 patch: diff --git a/doc/src

Re: [HACKERS] Moving ExecInsertIndexTuples and friends to new file

On Fri, Apr 24, 2015 at 6:38 AM, Heikki Linnakangas wrote: > I wrote a little overview text on how unique and exclusion constraints are > enforced. Most of the information can be gleaned from comments elsewhere, > but I think it's helpful to have it in one place. Makes it easier to compare > how u

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

2015-04-24 19:16 GMT+02:00 Joel Jacobson : > On Fri, Apr 24, 2015 at 6:07 PM, Pavel Stehule > wrote: > >> Example: > >> > >> context_messages = -warning, -error, +notice > > > > > > I prefer your first proposal - and there is a precedent for plpgsql - > > plpgsql_extra_checks > > > > It is clean

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

On Fri, Apr 24, 2015 at 6:07 PM, Pavel Stehule wrote: >> Example: >> >> context_messages = -warning, -error, +notice > > > I prefer your first proposal - and there is a precedent for plpgsql - > plpgsql_extra_checks > > It is clean for anybody. +-identifiers looks like horrible httpd config. :) I

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

On Wed, Apr 22, 2015 at 10:41:02PM -0400, Bruce Momjian wrote: > > josh=# create tablespace tbl2 location '/home/josh/pg94/data/pg_xlog/'; > > CREATE TABLESPACE > > > > It really seems like we ought to block *THAT*. Of course, if we block > > tablespace creation in PGDATA generally, then that's c

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

2015-04-24 16:02 GMT+02:00 Joel Jacobson : > On Fri, Apr 24, 2015 at 1:16 PM, Robert Haas > wrote: > >> This would allow doing something crazy as: > >> > >> suppress_context_messages = warning,error > >> display_context_messages = notice > > > > This is a very flexible proposal, but it's a tremen

Re: [HACKERS] a fast bloat measurement tool (was Re: Measuring relation free space)

On 04/24/15 14:58, Amit Kapila wrote: On Fri, Apr 24, 2015 at 8:46 AM, Abhijit Menon-Sen mailto:a...@2ndquadrant.com>> wrote: > > At 2015-04-24 08:35:40 +0530, amit.kapil...@gmail.com wrote: > > > > > Just stick a PG_RETURN_NULL() at the end? > > > > That

Re: Custom/Foreign-Join-APIs (Re: [HACKERS] [v9.5] Custom Plan API)

> On Wed, Apr 22, 2015 at 10:48 PM, Kouhei Kaigai wrote: > >> + else if (scan->scanrelid == 0 && > >> +(IsA(scan, ForeignScan) || IsA(scan, CustomScan))) > >> + varno = INDEX_VAR; > >> > >> Suppose scan->scanrelid == 0 but the scan type is something else

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

On Fri, Apr 24, 2015 at 1:16 PM, Robert Haas wrote: >> This would allow doing something crazy as: >> >> suppress_context_messages = warning,error >> display_context_messages = notice > > This is a very flexible proposal, but it's a tremendous amount of > machinery for what's really a very minor is

Re: [HACKERS] Moving ExecInsertIndexTuples and friends to new file

On 04/24/2015 09:36 AM, Heikki Linnakangas wrote: On 04/24/2015 06:30 AM, Stephen Frost wrote: * Peter Geoghegan (p...@heroku.com) wrote: On Thu, Apr 23, 2015 at 12:05 PM, Heikki Linnakangas wrote: While looking at Peter's INSERT ... ON CONFLICT patch, I started to feel that ExecInsertIndexTu

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

* Robert Haas (robertmh...@gmail.com) wrote: > On Thu, Apr 23, 2015 at 11:24 PM, Tom Lane wrote: > > Having said all that, if we did try to fix it today, I'd imagine changing > > TOAST value identifiers to int64 and inventing a new TOAST pointer format > > for use when 32 bits isn't wide enough fo

Re: [HACKERS] Replication identifiers, take 4

On 24/04/15 14:32, Andres Freund wrote: On 2015-04-20 11:26:29 +0300, Heikki Linnakangas wrote: On 04/17/2015 11:54 AM, Andres Freund wrote: I've attached a rebased patch, that adds decision about origin logging to the relevant XLogInsert() callsites for "external" 2 byte identifiers and remove

Re: [HACKERS] a fast bloat measurement tool (was Re: Measuring relation free space)

On Fri, Apr 24, 2015 at 8:46 AM, Abhijit Menon-Sen wrote: > > At 2015-04-24 08:35:40 +0530, amit.kapil...@gmail.com wrote: > > > > > Just stick a PG_RETURN_NULL() at the end? > > > > That should also work. > > OK, updated patch attached with just that one change. > Patch looks good to me, will ma

Re: [HACKERS] anole - test case sha2 fails on all branches

Hi Noah The build at commit 36e5247 also failed. With new buildfarm client 4.15, we used additional configure options like --with-gssapi -with-libxml --with-libxml --with-ldap --with-libxslt . The build at commit 36e5247 and last commit is successful without these options. I'll check which of thes

Re: [HACKERS] Replication identifiers, take 4

On 2015-04-20 11:26:29 +0300, Heikki Linnakangas wrote: > On 04/17/2015 11:54 AM, Andres Freund wrote: > >I've attached a rebased patch, that adds decision about origin logging > >to the relevant XLogInsert() callsites for "external" 2 byte identifiers > >and removes the pad-reusing version in the

Re: [HACKERS] Parallel Seq Scan

On Thu, Apr 23, 2015 at 2:26 AM, Robert Haas wrote: > > On Wed, Apr 22, 2015 at 8:48 AM, Amit Kapila wrote: > > I have implemented this idea (note that I have to expose a new API > > shm_mq_from_handle as TupleQueueFunnel stores shm_mq_handle* and > > we sum_mq* to call shm_mq_detach) and apart t

Re: Custom/Foreign-Join-APIs (Re: [HACKERS] [v9.5] Custom Plan API)

On Wed, Apr 22, 2015 at 10:48 PM, Kouhei Kaigai wrote: >> + else if (scan->scanrelid == 0 && >> +(IsA(scan, ForeignScan) || IsA(scan, CustomScan))) >> + varno = INDEX_VAR; >> >> Suppose scan->scanrelid == 0 but the scan type is something else? Is >> tha

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

On Thu, Apr 23, 2015 at 11:24 PM, Tom Lane wrote: > Having said all that, if we did try to fix it today, I'd imagine changing > TOAST value identifiers to int64 and inventing a new TOAST pointer format > for use when 32 bits isn't wide enough for the ID. But I think we're best > advised to hold o

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

On Thu, Apr 23, 2015 at 9:03 PM, Michael Paquier wrote: > On Thu, Apr 23, 2015 at 10:42 PM, Robert Haas wrote: >> On Thu, Apr 23, 2015 at 4:19 AM, Simon Riggs wrote: >>> We only need a freeze/backup map for larger relations. So if we map 1000 >>> blocks per map page, we skip having a map at all w

Re: [HACKERS] Improving vacuum/VM/etc

On Thu, Apr 23, 2015 at 3:09 PM, Jim Nasby wrote: > Unfortunately, the states I came up with using existing semantics don't look > hugely useful[4], but if we take Robert's idea and make all-visible mean > all-frozen, we can do much better: > > 0: Newly inserted tuples > Tracking this state allows

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

On Thu, Apr 23, 2015 at 9:28 PM, Tom Lane wrote: > The reason nobody's gotten around to that in the last fifteen years is > that per-process rusage isn't actually all that interesting; there's > too much that happens in background daemons, for instance. There's *some* stuff that happens in backgr

Re: [HACKERS] [committers] pgsql: RLS fixes, new hooks, and new test module

Dean, * Dean Rasheed (dean.a.rash...@gmail.com) wrote: > On 24 April 2015 at 04:31, Stephen Frost wrote: > > * Christian Ullrich (ch...@chrullrich.net) wrote: > >> * Stephen Frost wrote: > >> >RLS fixes, new hooks, and new test module > >> > >> The buildfarm says that with -DCLOBBER_CACHE_ALWAYS,

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

2015-04-24 13:16 GMT+02:00 Robert Haas : > On Fri, Apr 24, 2015 at 6:11 AM, Joel Jacobson wrote: > > Entering the discussion because this is a huge pain for me in my daily > > work as well. > > > > This is not a reply to any specific post in this thread, but my first > > message in the thread. >

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

On Thu, Apr 23, 2015 at 4:30 PM, Andres Freund wrote: > On 2015-04-23 16:26:09 -0400, Robert Haas wrote: >> But pg_upgrade automates all that, so you can't use pg_upgrade in that >> case. If we add a GUC as I suggested, you can still use pg_upgrade. > > But we also have to live with data director

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

On Fri, Apr 24, 2015 at 6:11 AM, Joel Jacobson wrote: > Entering the discussion because this is a huge pain for me in my daily > work as well. > > This is not a reply to any specific post in this thread, but my first > message in the thread. > > I see a great value in providing both a GUC and a ne

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

2015-04-24 12:11 GMT+02:00 Joel Jacobson : > Entering the discussion because this is a huge pain for me in my daily > work as well. > > This is not a reply to any specific post in this thread, but my first > message in the thread. > > I see a great value in providing both a GUC and a new RAISE syn

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

Entering the discussion because this is a huge pain for me in my daily work as well. This is not a reply to any specific post in this thread, but my first message in the thread. I see a great value in providing both a GUC and a new RAISE syntax. The different benefits of the two are maybe obvious

[HACKERS] Bug in planner

Hi! I faced with planner error: ERROR: could not find RelOptInfo for given relids A test case is in attachment, all supported versions of pgsql and HEAD are affected. Suppose, there is something wrong in pull_up_sublinks_qual_recurse() near converting NOT EXISTS into join, because preventin

Re: Custom/Foreign-Join-APIs (Re: [HACKERS] [v9.5] Custom Plan API)

Hi Ashutosh, Thanks for the review. 2015/04/22 19:28、Ashutosh Bapat のメール： > Tests > --- > 1.The postgres_fdw test is re/setting enable_mergejoin at various places. The > goal of these tests seems to be to test the sanity of foreign plans > generated. So, it might be better to reset enable_

Re: [HACKERS] [committers] pgsql: RLS fixes, new hooks, and new test module

On 24 April 2015 at 04:31, Stephen Frost wrote: > Christian, > > * Christian Ullrich (ch...@chrullrich.net) wrote: >> * Stephen Frost wrote: >> >> >RLS fixes, new hooks, and new test module >> >> The buildfarm says that with -DCLOBBER_CACHE_ALWAYS, the RLS >> violations get blamed on the wrong tab

Re: [HACKERS] INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

On Fri, Apr 24, 2015 at 12:31 AM, Heikki Linnakangas wrote: > Ok, I see now that I totally screwed up the conditions on "waitMode" in that > commit. I just pushed a fix to your github repository. I can confirm that the commit you just pushed prevents the implementation from attempting to lock an

Re: [HACKERS] INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

On 04/24/2015 02:52 AM, Peter Geoghegan wrote: I found a bug that seems to be down to commit e3144183562d08e347f832f0b29daefe8bac617b on Github: """ commit e3144183562d08e347f832f0b29daefe8bac617b Author: Heikki Linnakangas Date: Thu Apr 23 18:38:11 2015 +0300 Minor cleanup of check_exc