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

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] 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 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 buffer eviction that

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

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

2011-08-12 Thread Bruce Momjian
Johann 'Myrkraverk' Oskarsson wrote: > Bruce Momjian writes: > > > Robert Haas wrote: > >> On Fri, Jul 29, 2011 at 11:18 AM, Johann 'Myrkraverk' Oskarsson > >> wrote: > >> > > >> > I just noticed that the USECS_* constants are not defined when > >> > the server is compiled without integer dates

Re: [HACKERS] PL/Perl Returned Array

2011-08-12 Thread Alex Hunsaker
On Fri, Aug 12, 2011 at 18:00, David E. Wheeler 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 that? I

[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

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

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

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 V

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

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 most

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

2011-08-12 Thread Johann 'Myrkraverk' Oskarsson
Bruce Momjian writes: > Robert Haas wrote: >> On Fri, Jul 29, 2011 at 11:18 AM, Johann 'Myrkraverk' Oskarsson >> wrote: >> > >> > I just noticed that the USECS_* constants are not defined when >> > the server is compiled without integer dates and timestamps. [snip] >> I don't see any particular

Re: [HACKERS] index-only scans

2011-08-12 Thread Kevin Grittner
Robert Haas 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 be needed, I would

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

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 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 and locking the buffer > for ever

Re: [HACKERS] index-only scans

2011-08-12 Thread Robert Haas
2011/8/12 PostgreSQL - Hans-Jürgen Schönig : > 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: http://www.enterprisedb.c

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 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 and locking the buffer > for eve

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

2011-08-12 Thread Dimitri Fontaine
Magnus Hagander 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 incomp

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 trigge

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 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 and locking the buffer > for ever

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 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 need to perform a

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

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

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

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

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

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 if

Re: [HACKERS] index-only scans

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 4: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; Ugh. I think ther

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

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 postgr

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 i

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 bac

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

2011-08-12 Thread Tom Lane
Heikki Linnakangas 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 in the same >> catalog; and

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

2011-08-12 Thread Peter Geoghegan
On 12 August 2011 20:10, David E. Wheeler 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 an array, plus a si

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 Lane 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 trustworthy, but it has th

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

2011-08-12 Thread Kevin Grittner
Tom Lane 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 this, I don'

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

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

2011-08-12 Thread Kevin Grittner
Robert Haas 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 would be. Tom said:

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

2011-08-12 Thread Marko Kreen
On Thu, Aug 11, 2011 at 5:46 PM, Tom Lane wrote: > Marko Kreen writes: >> On Wed, Aug 10, 2011 at 9:19 PM, Tom Lane wrote: >>> ... which this approach would create, because digest() isn't restricted >>> to just those algorithms.  I think it'd be better to just invent two >>> new functions, which

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

2011-08-12 Thread Tom Lane
Robert Haas writes: > On Fri, Aug 12, 2011 at 2:09 PM, Tom Lane 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 wonder if anyone >> sees it diffe

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

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 : > (2011/08/12 1:05), Robert Haas wrote: >> On Thu, Aug 11, 2011 at 12:04 PM, Alvaro Herrera >>  wrote: >>> Excerpts from Robert Haas's message of jue ago 11 11:50:40 -0400 2011: 2011/8/9 Shigeru Hanada: > I'd like to pick #3, and also change per-column options f

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 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 versions mentioni

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 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 that message >

Re: [HACKERS] Reworking the writing of WAL

2011-08-12 Thread Simon Riggs
On Fri, Aug 12, 2011 at 7:02 PM, Robert Haas wrote: > On Fri, Aug 12, 2011 at 11:34 AM, Simon Riggs 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

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

Re: [HACKERS] Reworking the writing of WAL

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 11:34 AM, Simon Riggs 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 a SHMQUEUE exactl

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

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 ex

[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] 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 Byrne 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 to tell when

Re: [HACKERS] index-only scans

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 9:31 AM, Cédric Villemain 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 by >> optimizing away the he

Re: [HACKERS] WIP: Fast GiST index build

2011-08-12 Thread Robert Haas
On Thu, Aug 11, 2011 at 6:21 AM, Alexander Korotkov 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 seems like you

Re: [HACKERS] bgwriter and checkpoints

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 9:33 AM, Simon Riggs 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 contention. Yeah, some ki

Re: [HACKERS] bgwriter and checkpoints

2011-08-12 Thread Simon Riggs
On Fri, Aug 12, 2011 at 2:19 PM, Robert Haas wrote: > On Fri, Aug 12, 2011 at 7:09 AM, Simon Riggs 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

Re: [HACKERS] index-only scans

2011-08-12 Thread Cédric Villemain
2011/8/12 Robert Haas : > On Fri, Aug 12, 2011 at 6:20 AM, Cédric Villemain > 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

Re: [HACKERS] bgwriter and checkpoints

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 7:09 AM, Simon Riggs 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 cleaning and checkpointi

Re: [HACKERS] index-only scans

2011-08-12 Thread Robert Haas
On Fri, Aug 12, 2011 at 6:20 AM, Cédric Villemain 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 >> a, b FROM foo WHERE a

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 w

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 wrote: > On Fri, Aug 12, 2011 at 1:26 PM, Robert Haas 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 que

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 wrote: > On Fri, Aug 12, 2011 at 1:14 PM, Robert Haas wrote: >> On Fri, Aug 12, 2011 at 4:33 AM, Simon Riggs wrote: >>> You're missing an important point. The SeqScan is measurably faster >>> when using the ring buffer because of the effects of L2 cac

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 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 optimisation stronger

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 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 buffers. There are proble

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 wrote: > On Fri, Aug 12, 2011 at 4:33 AM, Simon Riggs 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

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 wrote: > On Fri, Aug 12, 2011 at 5:05 AM, Robert Haas 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 manag

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 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 won't fit in shared

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

2011-08-12 Thread Marko Kreen
On Thu, Aug 11, 2011 at 5:46 PM, Tom Lane wrote: > Marko Kreen writes: >> On Wed, Aug 10, 2011 at 9:19 PM, Tom Lane wrote: >>> ... which this approach would create, because digest() isn't restricted >>> to just those algorithms.  I think it'd be better to just invent two >>> new functions, which

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 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 problem as other s

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: http://www.post

Re: [HACKERS] bgwriter and checkpoints

2011-08-12 Thread Cédric Villemain
2011/8/12 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

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

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 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, and it will be st

Re: [HACKERS] index-only scans

2011-08-12 Thread Cédric Villemain
2011/8/12 Robert Haas : > On Thu, Aug 11, 2011 at 5:39 PM, Cédric Villemain > wrote: >> 2011/8/11 Robert Haas : >>> Please find attached a patch implementing a basic version of >>> index-only scans.  This patch is the work of my colleague Ibrar Ahmed >>> and myself, and also incorporates some code

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 plpyth

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

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 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 (by running many c

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 a