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.

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

Re: [HACKERS] Typed hstore proposal

On 22/12/11 10:44, Tom Lane wrote: Johann 'Myrkraverk' Oskarsson joh...@2ndquadrant.com 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

[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

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 by making other

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 07/01/12 23:01, Peter Eisentraut wrote: On tor, 2012-01-05 at 10:04 +, 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 experiencing a big performance regression in some

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

(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 overloaded right now

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] 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 larger

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 hoping for 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 t...@sss.pgh.pa.us wrote: Peter Eisentraut pete...@gmx.net 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

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. Beyond

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 j...@agliodbs.com 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

[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

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 and...@2ndquadrant.com 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 single

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 heikki.linnakan...@enterprisedb.com 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 each

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 bgrundm...@janestreet.com 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

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 bgrundm...@janestreet.com wrote: On Fri, Jul 20, 2012 at 10:46 AM, Benedikt Grundmann bgrundm...@janestreet.com wrote: Actually I believe this must be it. I just went back and checked the library and it does not CLOSE the cursors

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

On Tue, Aug 28, 2012 at 9:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Aug 28, 2012 at 2:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Oh, I'd forgotten that worked that way. Frankly, that makes me quite a bit more concerned about this proposal

Re: [HACKERS] Finer Extension dependencies

On Thu, Mar 29, 2012 at 1:01 PM, Kevin Grittner kevin.gritt...@wicourts.gov 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

Re: [HACKERS] Schema version management

On Mon, May 21, 2012 at 5:03 AM, Joel Jacobson j...@trustly.com 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

Re: [HACKERS] json api WIP patch

On Mon, Feb 4, 2013 at 4:10 PM, Andrew Dunstan and...@dunslane.net 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

[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] streaming replication, frozen snapshot backup on it and missing relfile (postgres 9.2.3 on xfs + LVM)

error might be permanent. The disk is not full nor are there any messages in the kernel logs. Cheers, Bene On Tue, May 14, 2013 at 9:27 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 14.05.2013 14:57, Benedikt Grundmann wrote: Today we have seen this on our testing

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 16:48, Benedikt Grundmann

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

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 haven't seen this before moving

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 hlinnakan...@vmware.com 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

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

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 22:50, Benedikt Grundmann wrote: On Wed, May 15, 2013 at 2:50 PM

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

, 2013 at 11:26 AM, Robert Haas robertmh...@gmail.comwrote: On Tue, May 21, 2013 at 11:59 AM, Benedikt Grundmann bgrundm...@janestreet.com wrote: We are seeing these errors on a regular basis on the testing box now. We have even changed the backup script to shutdown the hot standby, take

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

On Fri, Jun 14, 2013 at 10:11 AM, Samrat Revagade revagade.sam...@gmail.com 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:

Re: [HACKERS] MD5 aggregate

On Fri, Jun 14, 2013 at 2:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: Marko Kreen mark...@gmail.com writes: On Thu, Jun 13, 2013 at 12:35 PM, Dean Rasheed dean.a.rash...@gmail.com wrote: Attached is a patch implementing a new aggregate function md5_agg() to compute the aggregate MD5 sum

[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

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

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

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

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 g...@2ndquadrant.com wrote: On 7/10/13 12:53 PM, Benedikt Grundmann wrote: The server will probably be most

Re: [HACKERS] Backup throttling

On Tue, Aug 27, 2013 at 12:58 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Aug 20, 2013 at 2:37 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Throttling in the client seems much better to me. TCP is designed to handle a slow client. Other people have already offered some

Re: [HACKERS] record identical operator

On Thu, Sep 12, 2013 at 11:27 PM, Kevin Grittner kgri...@ymail.com 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

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

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

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 mostly just calls to c

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

On Thu, May 15, 2014 at 11:31 AM, Greg Stark st...@mit.edu wrote: 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

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

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 backup.

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

[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

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 <t...@sss.pgh.pa.us> wrote: > >> Kevin Grittner <kgri...@gmail.com> writes: >> > On Fri, Jan 15, 2016 at 9:33 AM, Tom La

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 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] 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

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:

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

On Tue, May 10, 2016 at 7:41 AM, David Rowley <david.row...@2ndquadrant.com> wrote: > On 10 May 2016 at 16:34, David G. Johnston <david.g.johns...@gmail.com> > wrote: > > On Mon, May 9, 2016 at 8:53 AM, Benedikt Grundmann > > <bgrundm...@janestreet.com> wrot

[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

[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

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

Re: [HACKERS] pg_dump versus rules, once again

On 30 December 2016 at 11:58, Benedikt Grundmann <bgrundm...@janestreet.com> wrote: > > On 17 November 2016 at 03:45, Robert Haas <robertmh...@gmail.com> wrote: > >> On Wed, Nov 16, 2016 at 10:14 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> >