Re: [HACKERS] pg_dump versus rules, once again

On 30 December 2016 at 11:58, Benedikt Grundmann wrote: > > On 17 November 2016 at 03:45, Robert Haas wrote: > >> On Wed, Nov 16, 2016 at 10:14 PM, Tom Lane wrote: >> > Robert Haas writes: >> >> On Wed, Nov 16, 2016 at 10:00 PM, Tom Lane wrote: >&g

Re: [HACKERS] pg_dump versus rules, once again

On 17 November 2016 at 03:45, Robert Haas wrote: > On Wed, Nov 16, 2016 at 10:14 PM, Tom Lane wrote: > > Robert Haas writes: > >> On Wed, Nov 16, 2016 at 10:00 PM, Tom Lane wrote: > >>> The changes in pg_backup_archiver.c would have to be back-patched > >>> into all versions supporting --if-ex

[HACKERS] Decoding proacl

I'm trying to understand how to decode proacl in pg_proc. The documentation says: PostgreSQL grants default privileges on some types of objects to PUBLIC. ... EXECUTE privilege for functions; ... Also, these initial default privilege settings can be changed using the ALTER DEFAULT PRIVILEGES comm

Re: [HACKERS] between not propated into a simple equality join

On Tue, May 10, 2016 at 7:41 AM, David Rowley wrote: > On 10 May 2016 at 16:34, David G. Johnston > wrote: > > On Mon, May 9, 2016 at 8:53 AM, Benedikt Grundmann > > wrote: > >> > >> We just run into a very simple query that the planner does much worse

[HACKERS] between not propated into a simple equality join

We just run into a very simple query that the planner does much worse on than we thought it would (in production the table in question is ~ 100 GB). It surprised us given the planner is generally quite good, so I thought I share our surprise Setup: postgres_prod@proddb_testing=# select version()

Re: [HACKERS] pg_basebackup compression TODO item

On Sun, Mar 6, 2016 at 7:36 PM, Euler Taveira wrote: > On 03-03-2016 14:44, Magnus Hagander wrote: > > On Thu, Mar 3, 2016 at 6:34 PM, Andres Freund > > wrote: > > > > On 2016-03-03 18:31:03 +0100, Magnus Hagander wrote: > > > I think we want it at protocol lev

Re: [HACKERS] Death by regexp_replace

thanks On Fri, Jan 15, 2016 at 7:22 PM, Devrim Gündüz wrote: > 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

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, I think you pr

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 there, leading to having to consider O(N^2) > >> or more pos

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', '([^,]+)(,*\1)+', '\1'); > > > This respond

[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 pg_rege

Re: [HACKERS] More thorough planning for OLAP queries (was: [PATCH] Equivalence Class Filters)

On Wed, Dec 30, 2015 at 7:16 AM, David Rowley wrote: > > A number of ideas were suggested on the other thread about how we might go > about solving this problem. In [3] Simon talked about perhaps enabling > extra optimisations when the planner sees that the plan will cost more than > some given t

Re: [HACKERS] Triggers on transaction?

On Wed, May 27, 2015 at 12:07 PM, hubert depesz lubaczewski < dep...@depesz.com> wrote: > On Wed, May 27, 2015 at 01:55:24PM +0300, Jordan Gigov wrote: > > Updating a materialized view in my case. It should only update when 2-3 > of > > our 30+ tables get new data, which for those is kind of rare.

Re: [HACKERS] Proposal: Incremental Backup

On Thu, Aug 7, 2014 at 6:29 PM, Gabriele Bartolini < gabriele.bartol...@2ndquadrant.it> wrote: > Hi Marco, > > > With the current full backup procedure they are backed up, so I think > > that having them backed up with a rsync-like algorithm is what an user > > would expect for an incremental back

[HACKERS] Is there a way to temporarily disable a index

That is it possible to tell the planner that index is off limits i.e. don't ever generate a plan using it? Rationale: Schema changes on big tables. I might have convinced myself / strong beliefs that for all queries that I need to be fast the planner does not need to use a given index (e.g. othe

Re: [HACKERS] gettimeofday is at the end of its usefulness?

On Thu, May 15, 2014 at 11:31 AM, Greg Stark wrote: > On Thu, May 15, 2014 at 8:19 AM, Benedikt Grundmann > wrote: > > I posted this on this mailing list before at Jane Street we have > developed > > very fast code to get timing information based on TSC if available. I

Re: [HACKERS] gettimeofday is at the end of its usefulness?

On Thu, May 15, 2014 at 8:19 AM, Benedikt Grundmann < bgrundm...@janestreet.com> wrote: > I posted this on this mailing list before at Jane Street we have developed > very fast code to get timing information based on TSC if available. It's > all ocaml but well documented and

Re: [HACKERS] gettimeofday is at the end of its usefulness?

I posted this on this mailing list before at Jane Street we have developed very fast code to get timing information based on TSC if available. It's all ocaml but well documented and mostly just calls to c functions so should be easy to port to C and we release it under a very liberal license so it

[HACKERS] How to do fast performance timing

At Jane Street we have recently spend a lot of time trying to get a fast gettimeofday. I saw lots of references in various postgres hacker threads related to a lack of such a facility so The culmination of those efforts can be read here: https://github.com/janestreet/core/blob/master/lib/ti

Re: [HACKERS] record identical operator

On Thu, Sep 12, 2013 at 11:27 PM, Kevin Grittner wrote: > Attached is a patch for a bit of infrastructure I believe to be > necessary for correct behavior of REFRESH MATERIALIZED VIEW > CONCURRENTLY as well as incremental maintenance of matviews. > [...] > The patch adds an "identical" operator (

Re: [HACKERS] Backup throttling

On Tue, Aug 27, 2013 at 12:58 PM, Robert Haas wrote: > On Tue, Aug 20, 2013 at 2:37 AM, Heikki Linnakangas > wrote: > > Throttling in the client seems much better to me. TCP is designed to > handle > > a slow client. > > Other people have already offered some good points in this area, but > let

Re: [HACKERS] hardware donation

The server is already turned off and in our nyc office (I'm based in the ldn one). But I'm pretty sure its a LSI MegaRAID SAS 9285. On Thu, Jul 18, 2013 at 11:58 PM, Greg Smith wrote: > On 7/10/13 12:53 PM, Benedikt Grundmann wrote: > >> The server will probably be mo

Re: [HACKERS] column "b" is of type X but expression is of type text

ce is very very local and quickly resorts to the default type. thanks everyone, Bene On Fri, Jul 12, 2013 at 3:17 PM, David Johnston wrote: > Benedikt Grundmann wrote > > A third party application we use generates SQL queries. Here is query it > > generated that broke today and for

[HACKERS] column "b" is of type X but expression is of type text

A third party application we use generates SQL queries. Here is query it generated that broke today and for which I have a hard time arguing that the postgres behavior is correct (minimally the error message is confusing): =# create temporary table foo (b double precision ); CREATE TABLE Time: 40

[HACKERS] hardware donation

Jane Street has a spare server we would like to donate to the postgres community. We originally planed to use it for one of our database clusters and it matches exactly what we use in production at the moment. Rough specs: CPU: 8x Intel(R) Xeon(R) CPU X5570 @ 2.93GHz MEM: 48GB The ser

Re: [HACKERS] MD5 aggregate

On Fri, Jun 14, 2013 at 2:14 PM, Tom Lane wrote: > Marko Kreen writes: > > On Thu, Jun 13, 2013 at 12:35 PM, Dean Rasheed > wrote: > >> Attached is a patch implementing a new aggregate function md5_agg() to > >> compute the aggregate MD5 sum across a number of rows. > > > It's more efficient to

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

On Fri, Jun 14, 2013 at 10:11 AM, Samrat Revagade wrote: > Hello, > > > We have already started a discussion on pgsql-hackers for the problem of > taking fresh backup during the failback operation here is the link for that: > > > > > http://www.postgresql.org/message-id/caf8q-gxg3pqtf71nvece-6ozr

Re: [HACKERS] streaming replication, "frozen snapshot backup on it" and missing relfile (postgres 9.2.3 on xfs + LVM)

ence against an issue in the backup procedure (though I'm > not ruling it out). We moved back to taking the backup off of the primary > and all errors for all three clusters went away. All of the hardware is > the same, OS and postgres versions are largely the same (9.2.3 vs. 9.2.4 in &

Re: [HACKERS] streaming replication, "frozen snapshot backup on it" and missing relfile (postgres 9.2.3 on xfs + LVM)

g that a shutdown, snapshot, > restart produces a backup that passes the vacuum analyze test. I'm going > to run a full vacuum today. > > -David > > > On Wed, May 15, 2013 at 3:53 PM, Heikki Linnakangas < > hlinnakan...@vmware.com> wrote: > >> On 15.05.2013

Re: [HACKERS] streaming replication, "frozen snapshot backup on it" and missing relfile (postgres 9.2.3 on xfs + LVM)

On Wed, May 15, 2013 at 2:50 PM, Heikki Linnakangas wrote: > On 15.05.2013 15:42, David Powers wrote: > >> First, thanks for the replies. This sort of thing is frustrating and hard >> to diagnose at a distance, and any help is appreciated. >> >> Here is some more background: >> >> We have 3 9.2.

Re: [HACKERS] streaming replication, "frozen snapshot backup on it" and missing relfile (postgres 9.2.3 on xfs + LVM)

f now unclear circumstances). You seem to be quite convinced that it must be LVM can you elaborate why? Thanks, Bene On Tue, May 14, 2013 at 12:09 PM, Benedikt Grundmann < bgrundm...@janestreet.com> wrote: > That's one possible explanation. It's worth noting that we ha

Re: [HACKERS] streaming replication, "frozen snapshot backup on it" and missing relfile (postgres 9.2.3 on xfs + LVM)

That's one possible explanation. It's worth noting that we haven't seen this before moving to streaming rep first and we have been using that method for a long time. On Tue, May 14, 2013 at 11:34 AM, Heikki Linnakangas < hlinnakan...@vmware.com> wrote: > On 14.05.2013 1

Re: [HACKERS] streaming replication, "frozen snapshot backup on it" and missing relfile (postgres 9.2.3 on xfs + LVM)

earlier ERROR's in the logs. 2013-05-14 09:38:03.115 EDT,,,30911,,51919d78.78bf,1868,,2013-05-13 22:12:08 EDT,,0,ERROR,XX000,"xlog flush request 1D08/9B57FCD0 is not satisfied --- flushed only to 1CEE/3C869588","writing block 0 of relation base/16416/291498116"&

[HACKERS] streaming replication, "frozen snapshot backup on it" and missing relfile (postgres 9.2.3 on xfs + LVM)

Today we have seen this on our testing database instance: ERROR: could not open file "base/16416/291498116.3" (target block 431006): No such file or directory That database get's created by rsyncing the LVM snapshot of the standby, which is a readonly backup of proddb using streaming replication

Re: [HACKERS] json api WIP patch

On Mon, Feb 4, 2013 at 4:10 PM, Andrew Dunstan wrote: > > On 02/04/2013 10:47 AM, Robert Haas wrote: > >> >> The SQL standards considerations seem worth thinking about, too. >> We've certainly gone through a lot of pain working toward eliminating >> => as an operator name, and if the SQL standard

Re: [HACKERS] proposal: Set effective_cache_size to greater of .conf value, shared_buffers

On Wed, Jan 9, 2013 at 2:01 AM, Josh Berkus wrote: > All, > > >> Well, the problem of "find out the box's physical RAM" is doubtless > >> solvable if we're willing to put enough sweat and tears into it, but > >> I'm dubious that it's worth the trouble. The harder part is how to know > >> if the

Re: [HACKERS] Improve compression speeds in pg_lzcompress.c

> Personally, my biggest gripe about the way we do compression is that > it's easy to detoast the same object lots of times. More generally, > our in-memory representation of user data values is pretty much a > mirror of our on-disk representation, even when that leads to excess > conversions. Be

Re: [HACKERS] MySQL search query is not executing in Postgres DB

On Tue, Aug 28, 2012 at 9:47 PM, Tom Lane wrote: > Robert Haas writes: > > On Tue, Aug 28, 2012 at 2:55 PM, Tom Lane wrote: > >> Oh, I'd forgotten that worked that way. Frankly, that makes me quite a > >> bit more concerned about this proposal than I was before. I do *not* > >> want to re-int

Re: [HACKERS] postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects

On Fri, Jul 20, 2012 at 10:56 AM, Benedikt Grundmann wrote: > On Fri, Jul 20, 2012 at 10:46 AM, Benedikt Grundmann > wrote: >> > Actually I believe this must be it. I just went back and checked the library > and it does not CLOSE the cursors. This is normally not

Re: [HACKERS] postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects

On Fri, Jul 20, 2012 at 10:46 AM, Benedikt Grundmann wrote: > > DECLARE sqmlcursor51587 CURSOR FOR select > entry_time,source,bad_fields,isin,sedol,cusip,bloomberg,reuters,exchange_code,currency,description,bbg_instrument_type,instrument_type,specifics,definer,primary_exchange,is_primary

Re: [HACKERS] postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects

On Fri, Jul 20, 2012 at 9:10 AM, Heikki Linnakangas wrote: > On 20.07.2012 10:19, Benedikt Grundmann wrote: >> >> We yesterday encountered a program that in a degenerate case issued in >> a single transaction a huge number of selects (in a single transaction >> but eac

Re: [HACKERS] postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects

First of all thanks to everyone who has replied so far. On Fri, Jul 20, 2012 at 10:04 AM, Andres Freund wrote: > > Hi, > > On Friday, July 20, 2012 09:19:31 AM Benedikt Grundmann wrote: > > We yesterday encountered a program that in a degenerate case > > issued in a s

[HACKERS] postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects

We yesterday encountered a program that in a degenerate case issued in a single transaction a huge number of selects (in a single transaction but each select in a separate call to PGExec) (huge = ~ 400,000). That transaction would continue to eat memory up until a point where calls to malloc (in a

Re: [HACKERS] Schema version management

On Mon, May 21, 2012 at 5:03 AM, Joel Jacobson wrote: > > http://archives.postgresql.org/pgsql-hackers/2010-12/msg02301.php > The initial feedback was on the usage of OIDs as file names. > This was indeed a bad idea and was changed, see > http://archives.postgresql.org/pgsql-hackers/2010-12/msg023

Re: [HACKERS] Finer Extension dependencies

On Thu, Mar 29, 2012 at 1:01 PM, Kevin Grittner wrote: > I gather from previous posts that the intent isn't to allow different > packages from different authors to provide a common and compatible > feature; but what happens in the current design if someone > accidentally or maliciously produces an

Re: [HACKERS] psql tab completion for SELECT

On 10/02/12 08:50, Robert Haas wrote: > On Fri, Feb 10, 2012 at 1:24 AM, Tom Lane wrote: > > Peter Eisentraut writes: > >> That seems pretty useful, and it's more or less a one-line change, as in > >> the attached patch. > > > > That seems pretty nearly entirely bogus.  What is the argument for >

Re: [HACKERS] random_page_cost vs seq_page_cost

On 07/02/12 19:58, Bruce Momjian wrote: > On Tue, Feb 07, 2012 at 05:06:18PM -0500, Greg Smith wrote: > > On 02/07/2012 03:23 PM, Bruce Momjian wrote: > > >Where did you see that there will be an improvement in the 9.2 > > >documentation? I don't see an improvement. > > > > I commented that I'm h

Re: [HACKERS] Vacuum rate limit in KBps

On 19/01/12 17:39, Greg Smith wrote: > On 1/19/12 1:10 PM, Robert Haas wrote: > >I have to say that I find that intensely counterintuitive. The > >current settings are not entirely easy to tune correctly, but at least > >they're easy to explain. > > If there's anyone out there who has run a large

Re: [HACKERS] random_page_cost vs seq_page_cost

On 11/01/12 08:26, Benedikt Grundmann wrote: > (replying just to you) Clearly I didn't. Sigh. Getting myself a coffee now. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] random_page_cost vs seq_page_cost

(replying just to you) On 10/01/12 15:22, Greg Smith wrote: > On 1/5/12 5:04 AM, Benedikt Grundmann wrote: > That sort of thing is one reason why all attempts so far to set > random_page_cost based on physical characteristics haven't gone > anywhere useful. The setting is sort of

Re: [HACKERS] Page Checksums

On 10/01/12 09:07, Simon Riggs wrote: > > You can repeat that argument ad infinitum. Even if the CRC covers all the > > pages in the OS buffer cache, it still doesn't cover the pages in the > > shared_buffers, CPU caches, in-transit from one memory bank to another etc. > > You have to draw the line

Re: [HACKERS] random_page_cost vs seq_page_cost

On 07/01/12 23:01, Peter Eisentraut wrote: > On tor, 2012-01-05 at 10:04 +0000, Benedikt Grundmann wrote: > > We have recently upgrade two of our biggest postgres databases > > to new hardware and minor version number bump (8.4.5 -> 8.4.9). > > > > We are

Re: [HACKERS] Page Checksums + Double Writes

For what's worth here are the numbers on one of our biggest databases (same system as I posted about separately wrt seq_scan_cost vs random_page_cost). 0053 1001 00BA 1009 0055 1001 00B9 1020 0054 983 00BB 1010 0056 1001 00BC 1019 0069 0 00BD 1009 006A 224 00BE 1018 006B 1009 00BF 1008 006C 1008

Re: [HACKERS] random_page_cost vs seq_page_cost

On 05/01/12 10:04, Benedikt Grundmann wrote: > > As a counter measure we are experimenting with > enable_nestloop = off > random_page_cost = 20 (instead of the previous 4). > For what it is worth we had to revert the enable_nestloop = off change. It just moved the pain around

[HACKERS] random_page_cost vs seq_page_cost

Hello list, I have a question of how to benchmark hardware to determine the appropriate ratio of seq_page_cost vs random_page_cost. Emails in this mailing lists archive seem to indicate that 1.0 vs 3.0 - 4.0 are appropriate values on modern hardware. Which surprised me a bit as I had thought th

Re: [HACKERS] Typed hstore proposal

On 22/12/11 10:44, Tom Lane wrote: > "Johann 'Myrkraverk' Oskarsson" writes: > > I mean to create a typed hstore, called tstore for now. > > Um ... what is the point of this, exactly? From what I've seen, most > applications for hstore are pretty happy with the fact that hstore is > only weakly

Re: [HACKERS] Real-life range datasets

Hello, We have a table in a postgres 8.4 database that would make use of date ranges and exclusion constraints if they were available. Sadly I cannot give you the data as it is based on data we are paying for and as part of the relevant licenses we are obliqued to not give the data to third parti

Re: [HACKERS] JSON for PG 9.2

Let me mention another lightweight data-interchange format. At http://www.janestreet.com we have developed a small c module to deal with S-expressions (sexp) as a way to store arbitrary data. As we write most of our code in OCaml sexps are a natural way for us to store data. http://hg.ocaml.info/