Re: [HACKERS] WIP: Fast GiST index build

2011-08-12 Thread Heikki Linnakangas
On 11.08.2011 23:30, Alexander Korotkov wrote: On Thu, Aug 11, 2011 at 2:28 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 10.08.2011 22:44, Alexander Korotkov wrote: Manual and readme updates. Thanks, I'm reviewing these now. Do we want to expose the level-step

Re: [HACKERS] our buffer replacement strategy is kind of lame

2011-08-12 Thread Simon Riggs
On Fri, Aug 12, 2011 at 5:05 AM, Robert Haas robertmh...@gmail.com wrote: On the other hand, the buffer manager has *no problem at all* trashing the buffer arena if we're faulting in pages for an index scan rather than a sequential scan.  If you manage to get all of sample_data into memory

Re: [HACKERS] our buffer replacement strategy is kind of lame

2011-08-12 Thread Simon Riggs
On Fri, Aug 12, 2011 at 5:05 AM, Robert Haas robertmh...@gmail.com wrote: rhaas=# select usagecount, sum(1) from pg_buffercache group by 1 order by 1; usagecount |  sum +---          1 |   136          2 |    12          3 |    72          4 |     7          5 | 13755    

Re: [HACKERS] plpython crash

2011-08-12 Thread Jan Urbański
On 11/08/11 18:01, Jean-Baptiste Quenot wrote: Hi there, plpython crashes on me on various 64-bit Ubuntu hosts, see the gdb backtrace at: https://gist.github.com/1140005 Do you believe there was recent bugfixes regarding PLyMapping_ToTuple() ? This is PG 9.0.4 with HEAD of plpython

Re: [HACKERS] index-only scans

2011-08-12 Thread Cédric Villemain
2011/8/12 Robert Haas robertmh...@gmail.com: On Thu, Aug 11, 2011 at 5:39 PM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: 2011/8/11 Robert Haas robertmh...@gmail.com: Please find attached a patch implementing a basic version of index-only scans.  This patch is the work of my

Re: [HACKERS] our buffer replacement strategy is kind of lame

2011-08-12 Thread Simon Riggs
On Fri, Aug 12, 2011 at 5:05 AM, Robert Haas robertmh...@gmail.com wrote: The general problem here is that we are not very smart about handling workloads with weak locality - i.e. the working set is larger than shared buffers.  If the working set fits in shared_buffers, we will keep it there,

Re: [HACKERS] WIP: Fast GiST index build

2011-08-12 Thread Alexander Korotkov
On Fri, Aug 12, 2011 at 12:23 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I think it would also be fairly simple to decrease levelstep and/or adjust buffersize on-the-fly. The trick would be in figuring out the heuristics on when to do that. I would be simple to

[HACKERS] bgwriter and checkpoints

2011-08-12 Thread Simon Riggs
The bgwriter has been responsible for two main activities: incremental page cleaning and checkpointing. We've worked out the code to smooth checkpointing, but that now means we have periods where we do both page cleaning and checkpointing, and other times when we do just page cleaning. That means

Re: [HACKERS] bgwriter and checkpoints

2011-08-12 Thread Cédric Villemain
2011/8/12 Simon Riggs si...@2ndquadrant.com: The bgwriter has been responsible for two main activities: incremental page cleaning and checkpointing. We've worked out the code to smooth checkpointing, but that now means we have periods where we do both page cleaning and checkpointing, and

Re: [HACKERS] plpython crash

2011-08-12 Thread Jean-Baptiste Quenot
Here is the same with -O0: https://gist.github.com/1140005 sys.version reports this: INFO: 2.6.6 (r266:84292, Sep 15 2010, 16:41:53) [GCC 4.4.5] -- Jean-Baptiste Quenot -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] our buffer replacement strategy is kind of lame

2011-08-12 Thread Simon Riggs
On Fri, Aug 12, 2011 at 11:53 AM, Simon Riggs si...@2ndquadrant.com wrote: I've not been reading the literature, given the problems we had in 2004/5 regarding patents in this area. I also think that since we rely on the underlying filesystem for cacheing that we don't have exactly the same

Re: [HACKERS] sha1, sha2 functions into core?

2011-08-12 Thread Marko Kreen
On Thu, Aug 11, 2011 at 5:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: Marko Kreen mark...@gmail.com writes: On Wed, Aug 10, 2011 at 9:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: ... which this approach would create, because digest() isn't restricted to just those algorithms.  I think it'd be

Re: [HACKERS] our buffer replacement strategy is kind of lame

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 4:33 AM, Simon Riggs si...@2ndquadrant.com wrote: You're missing an important point. The SeqScan is measurably faster when using the ring buffer because of the effects of L2 cacheing on the buffers. I hadn't thought of that, but I think that's only true if the relation

Re: [HACKERS] our buffer replacement strategy is kind of lame

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 4:36 AM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, Aug 12, 2011 at 5:05 AM, Robert Haas robertmh...@gmail.com wrote: On the other hand, the buffer manager has *no problem at all* trashing the buffer arena if we're faulting in pages for an index scan rather than

Re: [HACKERS] our buffer replacement strategy is kind of lame

2011-08-12 Thread Simon Riggs
On Fri, Aug 12, 2011 at 1:14 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Aug 12, 2011 at 4:33 AM, Simon Riggs si...@2ndquadrant.com wrote: You're missing an important point. The SeqScan is measurably faster when using the ring buffer because of the effects of L2 cacheing on the

Re: [HACKERS] our buffer replacement strategy is kind of lame

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 6:53 AM, Simon Riggs si...@2ndquadrant.com wrote: The worst case behaviour of the current freelist code is that it can take up to 5 * shared_buffers checks before identifying a victim buffer. That occurs when we have a working set exactly matching size of shared

Re: [HACKERS] our buffer replacement strategy is kind of lame

2011-08-12 Thread Simon Riggs
On Fri, Aug 12, 2011 at 1:26 PM, Robert Haas robertmh...@gmail.com wrote:  But it will be a loser to apply the optimization to data sets that would otherwise have fit in shared_buffers. Spoiling the cache is a bad plan, even if it makes the current query faster. I think we should make the

Re: [HACKERS] our buffer replacement strategy is kind of lame

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 8:28 AM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, Aug 12, 2011 at 1:14 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Aug 12, 2011 at 4:33 AM, Simon Riggs si...@2ndquadrant.com wrote: You're missing an important point. The SeqScan is measurably faster when

Re: [HACKERS] our buffer replacement strategy is kind of lame

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 8:35 AM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, Aug 12, 2011 at 1:26 PM, Robert Haas robertmh...@gmail.com wrote:  But it will be a loser to apply the optimization to data sets that would otherwise have fit in shared_buffers. Spoiling the cache is a bad

Re: [HACKERS] index-only scans

2011-08-12 Thread Oleg Bartunov
Robert, I imagine we store positional information in gin index and return tuples in relevant order - instant full-text search ! Great work, guys ! Oleg On Thu, 11 Aug 2011, Robert Haas wrote: Please find attached a patch implementing a basic version of index-only scans. This patch is the

Re: [HACKERS] index-only scans

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 6:20 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: Can this faux heap tuple be appended by the data from another index once it has been created ? ( if the query involves those 2 index) I don't see how to make that work.  In general, a query like SELECT

Re: [HACKERS] bgwriter and checkpoints

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 7:09 AM, Simon Riggs si...@2ndquadrant.com wrote: The bgwriter has been responsible for two main activities: incremental page cleaning and checkpointing. We've worked out the code to smooth checkpointing, but that now means we have periods where we do both page

Re: [HACKERS] index-only scans

2011-08-12 Thread Cédric Villemain
2011/8/12 Robert Haas robertmh...@gmail.com: On Fri, Aug 12, 2011 at 6:20 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: Can this faux heap tuple be appended by the data from another index once it has been created ? ( if the query involves those 2 index) I don't see how to

Re: [HACKERS] bgwriter and checkpoints

2011-08-12 Thread Simon Riggs
On Fri, Aug 12, 2011 at 2:19 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Aug 12, 2011 at 7:09 AM, Simon Riggs si...@2ndquadrant.com wrote: The bgwriter has been responsible for two main activities: incremental page cleaning and checkpointing. We've worked out the code to smooth

Re: [HACKERS] bgwriter and checkpoints

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 9:33 AM, Simon Riggs si...@2ndquadrant.com wrote: Yes, they would still need to talk. But the good news is that they only actually need to talk once per checkpoint cycle so we can buffer them to a certain extent in shared memory to remove the worst part of such

Re: [HACKERS] WIP: Fast GiST index build

2011-08-12 Thread Robert Haas
On Thu, Aug 11, 2011 at 6:21 AM, Alexander Korotkov aekorot...@gmail.com wrote: [ new patch ] Some random comments: - It appears that the noFollowFight flag is really supposed to be called noFollowRight. - In gist_private.h you've written halt-filled where you really mean half-filled. - It

Re: [HACKERS] index-only scans

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 9:31 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: Well... PostgreSQL can only use the index on a or the index on b, not both.  This patch doesn't change that.  I'm not trying to use indexes in some completely new way; I'm just trying to make them faster

Re: [HACKERS] Possible Bug in pg_upgrade

2011-08-12 Thread Dave Byrne
On 08/11/2011 12:02 AM, Peter Eisentraut wrote: On ons, 2011-08-10 at 18:53 -0400, Tom Lane wrote: Dave Byrnedby...@mdb.com writes: Attached is a patch that skips orphaned temporary relations in pg_upgrade if they are lingering around. It works for 9.0 - 9.1 upgrades, however I wasn't able

[HACKERS] Reworking the writing of WAL

2011-08-12 Thread Simon Riggs
I present a number of connected proposals 1. Earlier, I suggested that the sync rep code would allow us to redesign the way we write WAL, using ideas from group commit. My proposal is that when when a backend needs to flush WAL to local disk it will be added to a SHMQUEUE exactly the same as when

Re: [HACKERS] plpython crash

2011-08-12 Thread Jan Urbański
On 12/08/11 13:55, Jean-Baptiste Quenot wrote: Here is the same with -O0: https://gist.github.com/1140005 sys.version reports this: INFO: 2.6.6 (r266:84292, Sep 15 2010, 16:41:53) [GCC 4.4.5] I'm still at a loss. Did you reproduce it with git HEAD? I see that the query being execute

Re: [HACKERS] sha1, sha2 functions into core?

2011-08-12 Thread David E. Wheeler
On Aug 12, 2011, at 5:02 AM, Marko Kreen wrote: My point was that giving such open-ended list of algorithms was bad idea, but there is no problem keeping old behaviour. I don't see anything much wrong with sha1(bytea/text) - bytea. There's no law that says it has to work exactly like md5()

Re: [HACKERS] Reworking the writing of WAL

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 11:34 AM, Simon Riggs si...@2ndquadrant.com wrote: 1. Earlier, I suggested that the sync rep code would allow us to redesign the way we write WAL, using ideas from group commit. My proposal is that when when a backend needs to flush WAL to local disk it will be added to

[HACKERS] VACUUM FULL versus system catalog cache invalidation

2011-08-12 Thread Tom Lane
I've been testing the problem reported by Dave Gould by running make installcheck-parallel together with a tight loop of vacuum full pg_class: while true; do psql -c vacuum full pg_class regression; usleep 10; done Even after fixing the cache-reset-recovery-order problem I described

Re: [HACKERS] Reworking the writing of WAL

2011-08-12 Thread Simon Riggs
On Fri, Aug 12, 2011 at 7:02 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Aug 12, 2011 at 11:34 AM, Simon Riggs si...@2ndquadrant.com wrote: 1. Earlier, I suggested that the sync rep code would allow us to redesign the way we write WAL, using ideas from group commit. My proposal is

Re: [HACKERS] VACUUM FULL versus system catalog cache invalidation

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 2:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: But in 9.0 and up, we have a problem.  So far I've thought of two possible avenues to fix it: 1. When a SHAREDINVALCATALOG_ID inval message comes in (telling us a VAC FULL or CLUSTER just finished on a system catalog), enter

Re: [HACKERS] Extra check in 9.0 exclusion constraint unintended consequences

2011-08-12 Thread Robert Haas
On Thu, Aug 11, 2011 at 2:25 PM, Jeff Davis pg...@j-davis.com wrote: On Thu, 2011-08-11 at 11:58 -0400, Robert Haas wrote: I'm OK with adding a note either to the 9.0 docs only (which means it might be missed by a 9.0 user who only looks at the current docs) or with adding a note to all

Re: Change format of FDW options used in \d* commands (was: Re: [HACKERS] per-column FDW options, v5)

2011-08-12 Thread Robert Haas
2011/8/12 Shigeru Hanada shigeru.han...@gmail.com: (2011/08/12 1:05), Robert Haas wrote: On Thu, Aug 11, 2011 at 12:04 PM, Alvaro Herrera alvhe...@commandprompt.com  wrote: Excerpts from Robert Haas's message of jue ago 11 11:50:40 -0400 2011: 2011/8/9 Shigeru Hanadashigeru.han...@gmail.com:

Re: [HACKERS] Further news on Clang - spurious warnings

2011-08-12 Thread Peter Eisentraut
On fre, 2011-08-12 at 11:46 -0700, David E. Wheeler wrote: I figure there might be warnings you haven't seen if you haven't been building with bonjour, perl, openssl, pam, libxml, or ossp-uuid, so I've attached the output. We have a build farm member (smew) that covers all of that except

Re: [HACKERS] Further news on Clang - spurious warnings

2011-08-12 Thread David E. Wheeler
On Aug 12, 2011, at 12:09 PM, Peter Eisentraut wrote: I figure there might be warnings you haven't seen if you haven't been building with bonjour, perl, openssl, pam, libxml, or ossp-uuid, so I've attached the output. We have a build farm member (smew) that covers all of that except

Re: [HACKERS] VACUUM FULL versus system catalog cache invalidation

2011-08-12 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Fri, Aug 12, 2011 at 2:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: 2. Forget about targeting catcache invals by TID, and instead just use the key hash value to determine which cache entries to drop. Right at the moment I'm leaning to approach #2.  I

Re: [HACKERS] sha1, sha2 functions into core?

2011-08-12 Thread Marko Kreen
On Thu, Aug 11, 2011 at 5:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: Marko Kreen mark...@gmail.com writes: On Wed, Aug 10, 2011 at 9:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: ... which this approach would create, because digest() isn't restricted to just those algorithms.  I think it'd be

Re: [HACKERS] VACUUM FULL versus system catalog cache invalidation

2011-08-12 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: Perhaps we should just fix this one in master and consider back-patching it if and when we get some plausibly related bug reports. I'm not completely clear on what one would do to be vulnerable to hitting the bug, or what the impact of hitting it

[HACKERS] Inserting heap tuples in bulk in COPY

2011-08-12 Thread Heikki Linnakangas
COPY is slow. Let's make it faster. One obvious optimization is to insert heap tuples in bigger chunks, instead of calling heap_insert() separately for every tuple. That saves the overhead of pinning and locking the buffer for every tuple, and you only need to write one WAL record for all the

Re: [HACKERS] VACUUM FULL versus system catalog cache invalidation

2011-08-12 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: In any case, it is now clear to me that this bug is capable of eating peoples' databases, as in what just happened to our most critical table? Uh, it's not there anymore, boss, but we seem to have duplicate pg_class entries for this other table. Based on

Re: [HACKERS] VACUUM FULL versus system catalog cache invalidation

2011-08-12 Thread Heikki Linnakangas
On 12.08.2011 21:49, Robert Haas wrote: On Fri, Aug 12, 2011 at 2:09 PM, Tom Lanet...@sss.pgh.pa.us wrote: 2. Forget about targeting catcache invals by TID, and instead just use the key hash value to determine which cache entries to drop. Approach #2 seems a lot less invasive and more

Re: [HACKERS] Further news on Clang - spurious warnings

2011-08-12 Thread Peter Geoghegan
On 12 August 2011 20:10, David E. Wheeler da...@kineticode.com wrote: Ah, great, thanks! Unfortunately, you'll need to build your own Clang to be up to speed on the work I've done here, because the Clang developers both fixed the spurious warning from assigning past what appears to be the end of

Re: [HACKERS] VACUUM FULL versus system catalog cache invalidation

2011-08-12 Thread Tom Lane
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 12.08.2011 21:49, Robert Haas wrote: I don't think it really matters whether we occasionally blow away an entry unnecessarily due to a hash-value collision. IIUC, we'd only need to worry about hash-value collisions between rows

Re: [HACKERS] Inserting heap tuples in bulk in COPY

2011-08-12 Thread Gurjeet Singh
On Fri, Aug 12, 2011 at 3:16 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: COPY is slow. No kidding! So at least for now, the patch simply falls back to inserting one row at a time if there are any triggers on the table. Maybe we want to change that to fall back to

Re: [HACKERS] Inserting heap tuples in bulk in COPY

2011-08-12 Thread Florian Pflug
On Aug12, 2011, at 21:16 , Heikki Linnakangas wrote: Triggers complicate this. I believe it is only safe to group tuples together like this if the table has no triggers. A BEFORE ROW trigger might run a SELECT on the table being copied to, and check if some of the tuples we're about to

Re: [HACKERS] index-only scans

2011-08-12 Thread Heikki Linnakangas
On 11.08.2011 23:06, Robert Haas wrote: Comments, testing, review appreciated... I would've expected this to use an index-only scan: postgres=# CREATE TABLE foo AS SELECT generate_series(1,10) AS id; SELECT 10 postgres=# CREATE INDEX i_foo ON foo (id) WHERE id = 10; CREATE INDEX

Re: [HACKERS] VACUUM FULL versus system catalog cache invalidation

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 3:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: In any case, it is now clear to me that this bug is capable of eating peoples' databases, as in what just happened to our most critical table?  Uh, it's not there anymore, boss, but we seem to have duplicate pg_class entries

Re: [HACKERS] index-only scans

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 4:03 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 11.08.2011 23:06, Robert Haas wrote: Comments, testing, review appreciated... I would've expected this to use an index-only scan: postgres=# CREATE TABLE foo AS SELECT generate_series(1,10)

Re: [HACKERS] Inserting heap tuples in bulk in COPY

2011-08-12 Thread Heikki Linnakangas
On 12.08.2011 22:57, Florian Pflug wrote: On Aug12, 2011, at 21:16 , Heikki Linnakangas wrote: Triggers complicate this. I believe it is only safe to group tuples together like this if the table has no triggers. A BEFORE ROW trigger might run a SELECT on the table being copied to, and check

Re: [HACKERS] psql: bogus descriptions displayed by \d+

2011-08-12 Thread Peter Eisentraut
On tor, 2011-08-04 at 14:59 -0400, Robert Haas wrote: Well, the facts are: According to the SQL standard, table includes views and foreign tables. According to scientific-ish database literature, a table is a relation and vice versa. So what are you supposed to call it if you mean,

Re: [HACKERS] psql: bogus descriptions displayed by \d+

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 4:11 PM, Peter Eisentraut pete...@gmx.net wrote: On tor, 2011-08-04 at 14:59 -0400, Robert Haas wrote: Well, the facts are:  According to the SQL standard, table includes views and foreign tables.  According to scientific-ish database literature, a table is a

Re: [HACKERS] index-only scans

2011-08-12 Thread PostgreSQL - Hans-Jürgen Schönig
On Aug 12, 2011, at 10:03 PM, Heikki Linnakangas wrote: On 11.08.2011 23:06, Robert Haas wrote: Comments, testing, review appreciated... I would've expected this to use an index-only scan: postgres=# CREATE TABLE foo AS SELECT generate_series(1,10) AS id; SELECT 10 postgres=#

Re: [HACKERS] Extra check in 9.0 exclusion constraint unintended consequences

2011-08-12 Thread Jeff Davis
On Fri, 2011-08-12 at 14:58 -0400, Robert Haas wrote: Having thought about this a bit further, I'm coming around to the view that if it isn't worth adding this in master, it's not worth adding at all. I just don't think it's going to get any visibility as a back-branch only doc patch. Fine

[HACKERS] New copyright program

2011-08-12 Thread David Fetter
Folks, I noticed that src/tools/copyright looks like it can only be run on Bruce's machine, so this translation to Perl is intended: 1. To make the script idempotent, which allows its safe use in automated tools that might run it many times. 2. To get the script to run on any machine a

Re: [HACKERS] VACUUM FULL versus system catalog cache invalidation

2011-08-12 Thread Simon Riggs
On Fri, Aug 12, 2011 at 7:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: Right at the moment I'm leaning to approach #2.  I wonder if anyone sees it differently, or has an idea for a third approach? You are trying to solve the problem directly, which seems overkill. With HOT, there is very little

Re: [HACKERS] Inserting heap tuples in bulk in COPY

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 3:16 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: COPY is slow. Let's make it faster. One obvious optimization is to insert heap tuples in bigger chunks, instead of calling heap_insert() separately for every tuple. That saves the overhead of pinning

Re: [HACKERS] Inserting heap tuples in bulk in COPY

2011-08-12 Thread Andrew Dunstan
On 08/12/2011 04:57 PM, Robert Haas wrote: I thought about trying to do this at one point in the past, but I couldn't figure out exactly how to make it work. I think the approach you've taken here is good. Aside from the point already raised about needing to worry only about BEFORE ROW

Re: [HACKERS] Enforcing that all WAL has been replayed after restoring from backup

2011-08-12 Thread Dimitri Fontaine
Magnus Hagander mag...@hagander.net writes: Or add a signal handler in the pg_basebackup client emitting a warning about it? We don't have such a signal handler pg_dump either. I don't think we should add it. Hmm. I guess an aborted pg_dump will also look ok but actually be corrupt (or

Re: [HACKERS] Inserting heap tuples in bulk in COPY

2011-08-12 Thread Simon Riggs
On Fri, Aug 12, 2011 at 8:16 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: COPY is slow. Let's make it faster. One obvious optimization is to insert heap tuples in bigger chunks, instead of calling heap_insert() separately for every tuple. That saves the overhead of pinning

Re: [HACKERS] index-only scans

2011-08-12 Thread Robert Haas
2011/8/12 PostgreSQL - Hans-Jürgen Schönig postg...@cybertec.at: is there any plan to revise the cost for index only scans compared to what it is now? That's one of the points I asked for feedback on in my original email. How should the costing be done? -- Robert Haas EnterpriseDB:

Re: [HACKERS] Inserting heap tuples in bulk in COPY

2011-08-12 Thread Merlin Moncure
On Fri, Aug 12, 2011 at 2:16 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: COPY is slow. Let's make it faster. One obvious optimization is to insert heap tuples in bigger chunks, instead of calling heap_insert() separately for every tuple. That saves the overhead of pinning

Re: [HACKERS] USECS_* constants undefined with float8 timestamps?

2011-08-12 Thread Bruce Momjian
Robert Haas wrote: On Fri, Jul 29, 2011 at 11:18 AM, Johann 'Myrkraverk' Oskarsson joh...@2ndquadrant.com wrote: Hi all, I just noticed that the USECS_* constants are not defined when the server is compiled without integer dates and timestamps. Explicitly, timestamp.h is #ifdef

Re: [HACKERS] index-only scans

2011-08-12 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: That's one of the points I asked for feedback on in my original email. How should the costing be done? It seems pretty clear that there should be some cost adjustment. If you can't get good numbers somehow on what fraction of the heap accesses will

Re: [HACKERS] USECS_* constants undefined with float8 timestamps?

2011-08-12 Thread Johann 'Myrkraverk' Oskarsson
Bruce Momjian br...@momjian.us writes: Robert Haas wrote: On Fri, Jul 29, 2011 at 11:18 AM, Johann 'Myrkraverk' Oskarsson joh...@2ndquadrant.com wrote: I just noticed that the USECS_* constants are not defined when the server is compiled without integer dates and timestamps. [snip] I

Re: [HACKERS] Inserting heap tuples in bulk in COPY

2011-08-12 Thread Heikki Linnakangas
On 13.08.2011 00:17, Simon Riggs wrote: Also, we discussed that you would work on buffering the index inserts, which is where the main problem lies. The main heap is only a small part of the overhead if we have multiple indexes already built on a table - which is the use case that causes the

Re: [HACKERS] Inserting heap tuples in bulk in COPY

2011-08-12 Thread Heikki Linnakangas
On 13.08.2011 00:26, Merlin Moncure wrote: On Fri, Aug 12, 2011 at 2:16 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Triggers complicate this. I believe it is only safe to group tuples together like this if the table has no triggers. A BEFORE ROW trigger might run a SELECT

Re: [HACKERS] VACUUM FULL versus system catalog cache invalidation

2011-08-12 Thread daveg
On Fri, Aug 12, 2011 at 09:26:02PM +0100, Simon Riggs wrote: With HOT, there is very little need to perform a VACUUM FULL on any shared catalog table. Look at the indexes... I would a suggest that VACUUM FULL perform only a normal VACUUM on shared catalog tables, then perform an actual

Re: [HACKERS] our buffer replacement strategy is kind of lame

2011-08-12 Thread daveg
On Fri, Aug 12, 2011 at 01:28:49PM +0100, Simon Riggs wrote: I think there are reasonable arguments to make * prefer_cache = off (default) | on a table level storage parameter, =on will disable the use of BufferAccessStrategy * make cache_spoil_threshold a parameter, with default 0.25

[HACKERS] OperationalError: FATAL: lock AccessShareLock on object 0/1260/0 is already

2011-08-12 Thread daveg
This seems to be bug month for my client. Now there are seeing periods where all new connections fail immediately with the error: FATAL: lock AccessShareLock on object 0/1260/0 is already held This happens on postgresql 8.4.7 on a large (512GB, 32 core) system that has been up for months.

[HACKERS] PL/Perl Returned Array

2011-08-12 Thread David E. Wheeler
Hackers, Given this script on 9.1beta3: BEGIN; CREATE EXTENSION plperl; CREATE OR REPLACE FUNCTION wtf( ) RETURNS TEXT[] LANGUAGE plperl AS $$ return []; $$; SELECT wtf() = '{}'::TEXT[]; ROLLBACK; The output is: BEGIN CREATE EXTENSION CREATE FUNCTION

Re: [HACKERS] PL/Perl Returned Array

2011-08-12 Thread Alex Hunsaker
On Fri, Aug 12, 2011 at 18:00, David E. Wheeler da...@kineticode.com wrote: Hackers, Given this script on 9.1beta3:    BEGIN;    CREATE EXTENSION plperl;    CREATE OR REPLACE FUNCTION wtf(    ) RETURNS TEXT[] LANGUAGE plperl AS $$ return []; $$;    SELECT wtf() = '{}'::TEXT[]; Why is

Re: [HACKERS] USECS_* constants undefined with float8 timestamps?

2011-08-12 Thread Bruce Momjian
Johann 'Myrkraverk' Oskarsson wrote: Bruce Momjian br...@momjian.us writes: Robert Haas wrote: On Fri, Jul 29, 2011 at 11:18 AM, Johann 'Myrkraverk' Oskarsson joh...@2ndquadrant.com wrote: I just noticed that the USECS_* constants are not defined when the server is compiled

Re: [HACKERS] pgbench internal contention

2011-08-12 Thread Bruce Momjian
Robert Haas wrote: Works for me. Just to confirm, that means we'd also change GEQO to use pg_erand48(). BTW, as far as the original plan of using random_r is concerned, how did you manage to not run into this? http://sourceware.org/bugzilla/show_bug.cgi?id=3662 I just wasted half an

Re: [HACKERS] PL/Perl Returned Array

2011-08-12 Thread David E. Wheeler
On Aug 12, 2011, at 6:17 PM, Alex Hunsaker wrote: Anyway, the attached patch fixes it for me. That is when we don't have an array state, just return an empty array. (Also adds some additional comments) Fix confirmed, thank you! +1 to getting this committed before the next beta/RC. David

Re: [HACKERS] our buffer replacement strategy is kind of lame

2011-08-12 Thread Greg Stark
On Fri, Aug 12, 2011 at 5:05 AM, Robert Haas robertmh...@gmail.com wrote: Only 96 of the 14286 buffers in sample_data are in shared_buffers, despite the fact that we have 37,218 *completely unused* buffers lying around.  That sucks, because it means that the sample query did a whole lot of

Re: [HACKERS] PL/Perl Returned Array

2011-08-12 Thread Darren Duncan
David E. Wheeler wrote: On Aug 12, 2011, at 6:17 PM, Alex Hunsaker wrote: Anyway, the attached patch fixes it for me. That is when we don't have an array state, just return an empty array. (Also adds some additional comments) Fix confirmed, thank you! +1 to getting this committed before the

Re: [HACKERS] Transient plans versus the SPI API

2011-08-12 Thread Bruce Momjian
Tom Lane wrote: Note that the SPI functions are more or less directly exposed in PL/Perl and PL/Python, and there are a number of existing idioms there that make use of prepared plans. Changing the semantics of those functions might upset a lot of code. Right, but by the same token, if