Re: [HACKERS] Insert values() per-statement overhead

On 2016-01-15 13:17:12 +0300, Vladimir Sitnikov wrote: > There is a finding that insert(x) values(y);insert(x) values(z); is > 2-4 times slower than insert(..) values(y),(z); > see [1], [2]. If you indeed just mean statements like above, without begin/commit, a large portion of the overhead will

[HACKERS] Minor documentation tweak to GetForeignPlan documentation

Attached patch makes minor modification to the GetForeignPlan documentation. This adds the description about outer_plan, the new parameter added in 9.5. Best regards, Etsuro Fujita *** a/doc/src/sgml/fdwhandler.sgml --- b/doc/src/sgml/fdwhandler.sgml *** *** 178,184

Re: [HACKERS] Minor comment update in setrefs.c

On 2015/12/11 2:21, Robert Haas wrote: On Tue, Dec 8, 2015 at 6:16 AM, Etsuro Fujita wrote: Attached is a small patch to adjust a comment in setrefs.c; in set_foreignscan_references, fdw_recheck_quals also gets adjusted to reference foreign scan tuple, in case of a

[HACKERS] Insert values() per-statement overhead

Hi, There is a finding that insert(x) values(y);insert(x) values(z); is 2-4 times slower than insert(..) values(y),(z); see [1], [2]. In other words, there is a significant per-statement overhead even though server-prepared statements are properly used. The issue is reproducible in 9.5rc1. Is

Re: [HACKERS] Stream consistent snapshot via a logical decoding plugin as a series of INSERTs

On Fri, Jan 15, 2016 at 11:08 AM, Simon Riggs wrote: > On 15 January 2016 at 08:30, Shulgin, Oleksandr < > oleksandr.shul...@zalando.de> wrote: > > >> I'd like to propose generic functions (probably in an extension, or in >> core if not possible otherwise) to facilitate

Re: [HACKERS] Insert values() per-statement overhead

>I guess you mean there's a transaction surrounding it? Sure there is a transaction. I measure the latency from the first Bind message to the ReadyForQuery response. The database is at localhost. The flow is as follows (I've use 4 queries in batch for brevity, however the test above is executed

[HACKERS] Invitation to the OSGEO Code Sprint 2016 / Feb 23-26 in Paris

Hi everyone, The Open Source Geospatial Foundation is organizing its annual Code Sprint this year in Paris from February 23 to February 26. Many PostGIS developers will attend this event, including Paul Ramsey, Olivier Courtin, and others... This a great opportunity to work with them on how

Re: [HACKERS] Limit and inherited tables

Konstantin Knizhnik writes: > I noticed that LIMIT clause is not pushed down to inherited tables. It is when appropriate. > Consider the following tables: > create table foo(x integer primary key); > create table foo1 () inherits(foo); > create table foo2 ()

Re: [HACKERS] Proposal: BSD Authentication support

On Thu, Jan 14, 2016 at 11:59 PM, Chapman Flack wrote: > Forgive my late comment ... I haven't used the PAM support in postgresql > either, or I'd know. PAM (I know for sure), and I suppose similarly BSD > Authentication, models a generalized auth interaction where a given

[HACKERS] Limit and inherited tables

Hi, I am sorry if this question was already discussed but I failed to find any information about in archive. I noticed that LIMIT clause is not pushed down to inherited tables. Consider the following tables: create table foo(x integer primary key); create table foo1 () inherits(foo); create

Re: [HACKERS] dealing with extension dependencies that aren't quite 'e'

Abhijit Menon-Sen writes: > I'm looking at an extension that creates some triggers (on user tables) > dynamically (i.e., not during CREATE EXTENSION, but afterwards). The > author has two problems with it: > * «DROP EXTENSION ext» won't work without adding CASCADE, which

Re: [HACKERS] extend pgbench expressions with functions

Hello Michaël, Here is a v19 : - avoid noisy changes - abort on double->int overflow - implement operators as functions There is still \setrandom, that I can remove easily with a green light. -- Fabien.diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml index

Re: [HACKERS] Truncating/vacuuming relations on full tablespaces

On Fri, Sep 4, 2015 at 8:04 AM, Thom Brown wrote: > 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

Re: [HACKERS] jsonb - jsonb operators

- Original Message - > From: Merlin Moncure > To: Glyn Astill > Cc: "pgsql-hackers@postgresql.org" > Sent: Friday, 15 January 2016, 14:50 > Subject: Re: [HACKERS] jsonb - jsonb operators > > On Fri, Jan 15,

Re: [HACKERS] Death by regexp_replace

On Fri, Jan 15, 2016 at 10:12 AM, Benedikt Grundmann wrote: > Today we discovered that we had a backend whose client had gone away, the > automatic query watching process had send both pg_cancel and > pg_terminate_backend but nevertheless the process was sitting there >

Re: [HACKERS] Combining Aggregates

Man, I really shouldn't go on vacation for Christmas or, like, ever. My email responses get way too slow. Sorry. On Tue, Dec 29, 2015 at 7:39 PM, David Rowley wrote: >> No, the idea I had in mind was to allow it to continue to exist in the >> expanded format until

Re: [HACKERS] jsonb - jsonb operators

On Fri, Jan 15, 2016 at 7:43 AM, Glyn Astill wrote: > Hi all, > > I was just looking through the new jsonb operators in the 9.5 release, and > was wondering if there's any future intention to add a delete operator that > removes element/pair matches? I.e. some sort of

[HACKERS] Death by regexp_replace

Today we discovered that we had a backend whose client had gone away, the automatic query watching process had send both pg_cancel and pg_terminate_backend but nevertheless the process was sitting there consuming resources and had been for over 1 day... gdb revealed that we were sitting in

[HACKERS] jsonb - jsonb operators

Hi all, I was just looking through the new jsonb operators in the 9.5 release, and was wondering if there's any future intention to add a delete operator that removes element/pair matches? I.e. some sort of top-level "jsonb - jsonb" operator, e.g. # select '{"a":1, "b":2}'::jsonb - '{"b":2,

Re: [HACKERS] Death by regexp_replace

Benedikt Grundmann wrote: > Today we discovered that we had a backend whose client had gone away, the > automatic query watching process had send both pg_cancel and > pg_terminate_backend but nevertheless the process was sitting there > consuming resources and had been

Re: [HACKERS] Death by regexp_replace

On Fri, Jan 15, 2016 at 4:39 PM, Benedikt Grundmann < bgrundm...@janestreet.com> wrote: > > On Fri, Jan 15, 2016 at 4:26 PM, Tom Lane wrote: > >> Kevin Grittner writes: >> > On Fri, Jan 15, 2016 at 9:33 AM, Tom Lane wrote: >> >> (FWIW,

Re: [HACKERS] pglogical - logical replication contrib module

On 2016-01-09 19:30, Steve Singer wrote:\ I am going to send my comments/issues out in batches as I find them instead of waiting till I look over everything. Thanks for looking at this! Yes going in batches/steps makes sense, this is huge patch. I find this part of the documentation a

Re: [HACKERS] [BUGS] about test_parser installation failure problem(PostgreSQL in 9.5.0)？

"=?utf-8?B?6Zas6Zas44Kk44G1?=" writes: > test_parser install is ok (postgresql 9.2.4) > but at (postgresql 9.5.0) failure？ Yes, we moved test_parser and some other only-useful-for-testing modules from contrib to src/test/modules, which means they won't get

Re: [HACKERS] Truncating/vacuuming relations on full tablespaces

On Fri, Jan 15, 2016 at 11:05 AM, Thom Brown wrote: > On 15 January 2016 at 15:21, Robert Haas wrote: >> On Fri, Sep 4, 2015 at 8:04 AM, Thom Brown wrote: >>> Currently, when attempting to vacuum a table on a tablespace with no space >>>

Re: [HACKERS] Death by regexp_replace

> Hmm I just wanted to get the rpm for the latest 9.2 release for centos6 but > it looks like you haven't released at least the link on this page for 9.2 > > http://yum.postgresql.org/repopackages.php > > says 7 in the filename which is certainly not 14 ;-) > >

Re: [HACKERS] Multi-tenancy with RLS

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > >> Stephen Frost writes: > >>> I don't follow how this would destroy the ability to run pg_dump. > >>> Ideally, we'd have a result where a user

Re: [HACKERS] dealing with extension dependencies that aren't quite 'e'

On Fri, Jan 15, 2016 at 7:49 AM, Tom Lane wrote: > Abhijit Menon-Sen writes: > > I'm looking at an extension that creates some triggers (on user tables) > > dynamically (i.e., not during CREATE EXTENSION, but afterwards). The > > author has two problems

Re: [HACKERS] pglogical - logical replication contrib module

On 2016-01-10 20:57, Steve Singer wrote: On 01/09/2016 01:30 PM, Steve Singer wrote: On 12/31/2015 06:34 PM, Petr Jelinek wrote: I'm not really sure what to do to 'recover' my cluster at this point so I'll send this off and rebuild my cluster and start over. I had a setup test1--->test2

Re: [HACKERS] dealing with extension dependencies that aren't quite 'e'

I'm sorry, it wasn't clear from my earlier post that the triggers are dependent on a function provided by the extension. So when you do CREATE EXTENSION foo, it creates foo_somefunc() that RETURNS TRIGGER. Later, a trigger is created (somehow; in this case it is by some other function in the

Re: [HACKERS] Death by regexp_replace

On Fri, Jan 15, 2016 at 9:33 AM, Tom Lane wrote: >> *WARNING DO NOT DO THIS ON A PRODUCTION BOX* >> select regexp_replace('VODI GR,VOD LN,VOD LN,VODN MM,VODPF US,VOD US,VZC >> LN', '([^,]+)(,*\1)+', '\1'); > This responds to cancel just fine for me. > (FWIW, I think you

Re: [HACKERS] Truncating/vacuuming relations on full tablespaces

On 15 January 2016 at 15:21, Robert Haas wrote: > On Fri, Sep 4, 2015 at 8:04 AM, Thom Brown wrote: >> Currently, when attempting to vacuum a table on a tablespace with no space >> left, we get an error: >> >> postgres=# vacuum test; >> ERROR: could not

Re: [HACKERS] Death by regexp_replace

9.2.6 On Fri, Jan 15, 2016 at 3:48 PM, Kevin Grittner wrote: > On Fri, Jan 15, 2016 at 9:33 AM, Tom Lane wrote: > > >> *WARNING DO NOT DO THIS ON A PRODUCTION BOX* > >> select regexp_replace('VODI GR,VOD LN,VOD LN,VODN MM,VODPF US,VOD US,VZC > >> LN',

Re: [HACKERS] Death by regexp_replace

On 2016-01-15 10:25 AM, Robert Haas wrote: On Fri, Jan 15, 2016 at 10:12 AM, Benedikt Grundmann wrote: Today we discovered that we had a backend whose client had gone away, the automatic query watching process had send both pg_cancel and pg_terminate_backend but

Re: [HACKERS] Death by regexp_replace

Kevin Grittner writes: > On Fri, Jan 15, 2016 at 9:33 AM, Tom Lane wrote: >> (FWIW, I think you probably wanted ,+ not ,* in the regex, else there's >> practically no constraint there, leading to having to consider O(N^2) >> or more possibilities.) > On

Re: [HACKERS] Multi-tenancy with RLS

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > >> However, by "not that much trouble" I only mean getting an implementation > >> that works and doesn't create more security problems than it fixes. > >> Usability is

Re: [HACKERS] Multi-tenancy with RLS

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Joe Conway writes: > > As Stephen mentioned, yes, I am very interested in at least some aspects > > of this patch. The ability to apply RLS to system tables could be useful > > to solve a number of problems we don't have a good story

Re: [HACKERS] Stream consistent snapshot via a logical decoding plugin as a series of INSERTs

On Fri, Jan 15, 2016 at 12:09 PM, Shulgin, Oleksandr < oleksandr.shul...@zalando.de> wrote: > On Fri, Jan 15, 2016 at 11:08 AM, Simon Riggs > wrote: > >> On 15 January 2016 at 08:30, Shulgin, Oleksandr < >> oleksandr.shul...@zalando.de> wrote: >> >> >>> I'd like to propose

Re: [HACKERS] Multi-tenancy with RLS

Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> However, by "not that much trouble" I only mean getting an implementation >> that works and doesn't create more security problems than it fixes. >> Usability is still likely to be a huge problem. In particular

Re: [HACKERS] Limit and inherited tables

This example is lacking indexes on the child tables, which is why the plan shown is about as good as you're going to get. The contents of foo1 and foo2 have to be read in entirety in any case, and sorting them separately is not a win compared to doing a single sort. It is true, but not in case

Re: [HACKERS] Limit and inherited tables

Konstantin Knizhnik writes: >> This example is lacking indexes on the child tables, which is >> why the plan shown is about as good as you're going to get. >> The contents of foo1 and foo2 have to be read in entirety in any >> case, and sorting them separately is not a

Re: [HACKERS] Death by regexp_replace

On Fri, Jan 15, 2016 at 4:26 PM, Tom Lane wrote: > Kevin Grittner writes: > > On Fri, Jan 15, 2016 at 9:33 AM, Tom Lane wrote: > >> (FWIW, I think you probably wanted ,+ not ,* in the regex, else there's > >> practically no constraint

Re: [HACKERS] Multi-tenancy with RLS

Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> Stephen Frost writes: >>> I don't follow how this would destroy the ability to run pg_dump. >>> Ideally, we'd have a result where a user could run pg_dump without >>> having to apply any

Re: [HACKERS] Death by regexp_replace

Hi, That is the version of *repo* RPM, not PostgreSQL itself.Once you install it, you can grab the latest version with yum install postgresql92-server Regards, Devrim On January 15, 2016 7:48:53 PM GMT+02:00, Robert Haas wrote: >> Hmm I just wanted to get the rpm for

Re: [HACKERS] Truncating/vacuuming relations on full tablespaces

On Fri, Jan 15, 2016 at 1:24 PM, Kevin Grittner wrote: > On Fri, Jan 15, 2016 at 11:41 AM, Robert Haas wrote: >> Anybody else want to weigh in with thoughts on any of this? > > Leaving aside VACUUM issues for a moment, what problems to you see > with an

Re: [HACKERS] Truncating/vacuuming relations on full tablespaces

Robert Haas writes: > Now, I do think it's a somewhat fair complaint that if you have a > tablespace which is totally, 100% full, recovery is difficult. You > can probably DROP the table, but TRUNCATE might fail, and so might > VACUUM. You could argue that DROP is usually

[HACKERS] Expanded Object Header and Flat Cache

I've been working through the expanded object code to try and get a demonstration of it working with PostGIS (still having some problems, but it's a learning experience). On an unrelated from, I noticed in the array expanded code, that the array code is managing its own copy of a cache of the flat

Re: [HACKERS] Truncating/vacuuming relations on full tablespaces

On Fri, Jan 15, 2016 at 11:41 AM, Robert Haas wrote: > Anybody else want to weigh in with thoughts on any of this? Leaving aside VACUUM issues for a moment, what problems to you see with an empty table that has no visibility map or free space map fork? In other words,

Re: [HACKERS] Expanded Object Header and Flat Cache

Paul Ramsey writes: > I've been working through the expanded object code to try and get a > demonstration of it working with PostGIS (still having some problems, > but it's a learning experience). On an unrelated from, I noticed in > the array expanded code, that the

Re: [HACKERS] BUG #13854: SSPI authentication failure: wrong realm name used

* Christian Ullrich wrote: * Christian Ullrich wrote: * Christian Ullrich wrote: > According to the release notes, the default for the "include_realm" > option in SSPI authentication was changed from off to on in 9.5 for > > improved security. However, the authenticated user name, with the

Re: [HACKERS] checkpointer continuous flushing

Hi Fabien, On 2016-01-11 14:45:16 +0100, Andres Freund wrote: > I measured it in a different number of cases, both on SSDs and spinning > rust. I just reproduced it with: > > postgres-ckpt14 \ > -D /srv/temp/pgdev-dev-800/ \ > -c maintenance_work_mem=2GB \ > -c fsync=on \

Re: [HACKERS] Fuzzy substring searching with the pg_trgm extension

On Fri, Dec 18, 2015 at 11:43 AM, Artur Zakirov wrote: > Hello. > > PostgreSQL has a contrib module named pg_trgm. It is used to the fuzzy text > search. It provides some functions and operators for determining the > similarity of the given texts using trigram matching.

Re: [HACKERS] GIN pending list clean up exposure to SQL

On Sun, Jan 10, 2016 at 4:24 AM, Julien Rouhaud wrote: > On 29/12/2015 00:30, Jeff Janes wrote: >> On Wed, Nov 25, 2015 at 9:29 AM, Jeff Janes wrote: >>> >>> I'll prepare a patch for core for the January commitfest, and see if >>> it flies. If

[HACKERS] Proposal: speeding up GIN build with parallel workers

Hi, Hackers. The task of building GIN can require lots of time and eats 100 % CPU, but we could easily make it use more than a 100 %, especially since we now have parallel workers in postgres. The process of building GIN looks like this: 1. Accumulate a batch of index records into an rbtree in

Re: [HACKERS] pgindent-polluted commits

On Wed, Jan 13, 2016 at 12:13:11PM -0500, Tom Lane wrote: > Simon Riggs writes: > > On 13 January 2016 at 14:48, Noah Misch wrote: > >> I've noticed commits, from a few of you, carrying pgindent changes to lines > >> the patch would not otherwise change.

Re: [HACKERS] POC: Cache data in GetSnapshotData()

On Fri, Jan 15, 2016 at 11:23 AM, Mithun Cy wrote: > On Mon, Jan 4, 2016 at 2:28 PM, Andres Freund wrote: > > > I think at the very least the cache should be protected by a separate > > lock, and that lock should be acquired with TryLock. I.e. the

Re: [HACKERS] Proposal: speeding up GIN build with parallel workers

On Fri, Jan 15, 2016 at 2:38 PM, Constantin S. Pan wrote: > I have a draft implementation which divides the whole process between > N parallel workers, see the patch attached. Instead of a full scan of > the relation, I give each worker a range of blocks to read. I am currently

Re: [HACKERS] Combining Aggregates

On 16 January 2016 at 03:03, Robert Haas wrote: > On Tue, Dec 29, 2015 at 7:39 PM, David Rowley > wrote: > >> No, the idea I had in mind was to allow it to continue to exist in the > >> expanded format until you really need it in the varlena

Re: [HACKERS] GIN pending list clean up exposure to SQL

On 15/01/2016 22:59, Jeff Janes wrote: > On Sun, Jan 10, 2016 at 4:24 AM, Julien Rouhaud > wrote: >> On 29/12/2015 00:30, Jeff Janes wrote: >>> On Wed, Nov 25, 2015 at 9:29 AM, Jeff Janes wrote: I'll prepare a patch for core for the

[HACKERS] Re: [BUGS] about test_parser installation failure problem(PostgreSQL in 9.5.0)？

On Sat, Jan 16, 2016 at 2:03 AM, Tom Lane wrote: > "=?utf-8?B?6Zas6Zas44Kk44G1?=" writes: >> test_parser install is ok (postgresql 9.2.4) >> but at (postgresql 9.5.0) failure？ > > Yes, we moved test_parser and some other only-useful-for-testing

Re: [HACKERS] checkpointer continuous flushing

Hi Fabien, Hello Tomas. On 2016-01-11 14:45:16 +0100, Andres Freund wrote: I measured it in a different number of cases, both on SSDs and spinning rust. I just reproduced it with: postgres-ckpt14 \ -D /srv/temp/pgdev-dev-800/ \ -c maintenance_work_mem=2GB \ -c

[HACKERS] Stream consistent snapshot via a logical decoding plugin as a series of INSERTs

Hello, I'd like to propose generic functions (probably in an extension, or in core if not possible otherwise) to facilitate streaming existing data from the database *in the same format* that one would get if these would be the changes decoded by a logical decoding plugin. The idea is to use a

Re: [HACKERS] Comment typo in port/atomics/generic.h

On Fri, Jan 15, 2016 at 2:35 AM, Tatsuro Yamada < yamada.tats...@lab.ntt.co.jp> wrote: > Hi, > > I found a typo in generic.h > The attached patch fixes it: s/tomic/atomic/g > Applied, thanks! -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/

Re: [HACKERS] POC: Cache data in GetSnapshotData()

On Fri, Jan 15, 2016 at 11:23 AM, Mithun Cy wrote: > On Mon, Jan 4, 2016 at 2:28 PM, Andres Freund wrote: > > > I think at the very least the cache should be protected by a separate > > lock, and that lock should be acquired with TryLock. I.e. the

Re: [HACKERS] Fuzzy substring searching with the pg_trgm extension

On 12.01.2016 02:31, Alvaro Herrera wrote: I gave a quick look through the patch and noticed a few minor things while trying to understand it. I think the test corpus isn't particularly interesting for how big it is. I'd rather have (a) a small corpus (say 100 words) with which to do detailed

Re: [HACKERS] Minor code improvements to create_foreignscan_plan/ExecInitForeignScan

On 2016/01/12 18:00, Etsuro Fujita wrote: On 2016/01/12 2:36, Alvaro Herrera wrote: I wonder, --- 2166,2213 } /* ! * If rel is a base relation, detect whether any system columns are ! * requested from the rel. (If rel is a join relation, rel->relid will be !