Re: [HACKERS] WIP: Fast GiST index build

On 26.08.2011 17:18, Alexander Korotkov wrote: On Thu, Aug 25, 2011 at 11:08 PM, Heikki Linnakangas< heikki.linnakan...@enterprisedb.com> wrote: Could you share the test scripts, patches and data sets etc. needed to reproduce the tests you've been running? I'd like to try them out on a test se

Re: [HACKERS] WIP: Fast GiST index build

On 30.08.2011 12:08, Heikki Linnakangas wrote: What's going on here? This data set was large enough to not fit in RAM, the table was about 8.5 GB in size (and I think the index is even larger than that), and the box has 4GB of RAM. Does the buffering only help with even larger indexes that exceed

Re: [HACKERS] WIP: Fast GiST index build

On Tue, Aug 30, 2011 at 1:13 PM, Heikki Linnakangas < heikki.linnakan...@enterprisedb.com> wrote: > So, over 50% of the CPU time is spent in choosing a block from the > temporary files. That should be pretty easy to improve.. > Yes, probably we can just remove free blocks sorting. -- With bes

Re: [HACKERS] WIP: Fast GiST index build

On Tue, Aug 30, 2011 at 1:08 PM, Heikki Linnakangas < heikki.linnakan...@enterprisedb.com> wrote: > > Thanks. Meanwhile, I hacked together my own set of test scripts, and let > them run over the weekend. I'm still running tests with ordered data, but > here are some preliminary results: > >

Re: [HACKERS] Single pass vacuum - take 2

On Tue, Aug 23, 2011 at 2:47 AM, Jim Nasby wrote: > On Aug 22, 2011, at 1:22 AM, Pavan Deolasee wrote: >> Hi All, >> >> Here is a revised patch based on our earlier discussion. I implemented >> Robert's idea of tracking the vacuum generation number in the line >> pointer itself. For LP_DEAD line p

Re: [HACKERS] WIP: Fast GiST index build

On 30.08.2011 13:29, Alexander Korotkov wrote: On Tue, Aug 30, 2011 at 1:13 PM, Heikki Linnakangas< heikki.linnakan...@enterprisedb.com> wrote: So, over 50% of the CPU time is spent in choosing a block from the temporary files. That should be pretty easy to improve.. Yes, probably we can jus

Re: [HACKERS] WIP: Fast GiST index build

On 30.08.2011 13:38, Alexander Korotkov wrote: On Tue, Aug 30, 2011 at 1:08 PM, Heikki Linnakangas< heikki.linnakan...@enterprisedb.com> wrote: Thanks. Meanwhile, I hacked together my own set of test scripts, and let them run over the weekend. I'm still running tests with ordered data, but he

[HACKERS] compile from git repository

Hi, hackers I try to compile from git repository and get some errors. Windows 7 x86 VC Express 2008 perl v5.8.9 flex 2.5.4a-1 bison 2.4.1 git clone git://git.postgresql.org/git/postgresql.git git checkout REL9_0_4 cd ...\src\tools\msvc build.bat Build started: Project: postgres, Configurati

Re: [HACKERS] Join push-down for foreign tables

Thanks for the comments. (2011/08/30 1:42), Tom Lane wrote: >> Costs of ForeignJoinPath are estimated by FDW via new routine >> PlanForeignJoin, and SQL based FDW would need to generate remote SQL >> here. If a FDW can't push down that join, then it can set disable_cost >> (1.0e10) to tell planne

Re: [HACKERS] compile from git repository

On 08/30/2011 07:11 AM, 权宗亮 wrote: Hi, hackers I try to compile from git repository and get some errors. Windows 7 x86 VC Express 2008 perl v5.8.9 flex 2.5.4a-1 bison 2.4.1 Your version of flex is too old. You can try the one at

[HACKERS] postgesql-9.0.4 compile on AIX 6.1 using gcc 4.4.6

Hello, I am just trying to compile postgresql-9.0.4 on AIX 6100-06-03-1048 using gcc 4.4.6. Since many years I did this successfully on other versions of AIX using xlc. However my version 9 of xlc is no longer running on AIX 6.1 and no one is willing to sponsor a new version of xlc. So I st

Re: [HACKERS] compile from git repository

Andrew Dunstan writes: > On 08/30/2011 07:11 AM, 权宗亮 wrote: >> I try to compile from git repository and get some errors. >> >> Windows 7 x86 >> VC Express 2008 >> perl v5.8.9 >> flex 2.5.4a-1 >> bison 2.4.1 > Your version of flex is too old. Hmm ... had the OP been using configure, he'd h

Re: [HACKERS] compile from git repository

On Tue, Aug 30, 2011 at 16:30, Tom Lane wrote: > Andrew Dunstan writes: >> On 08/30/2011 07:11 AM, 权宗亮 wrote: >>> I try to compile from git repository and get some errors. >>> >>> Windows 7 x86 >>> VC Express 2008 >>> perl v5.8.9 >>> flex 2.5.4a-1 >>> bison 2.4.1 > >> Your version of flex is too

Re: [HACKERS] Join push-down for foreign tables

Shigeru Hanada writes: > BTW, Is adding foreign server oid to RelOptInfo acceptable? No objection here. > This field > is set in build_simple_rel() or build_join_rel() if the RelOptInfo > itself is a foreign scan, or it is a foreign join and both inner and > outer RelOptInfo have same and valid

Re: [HACKERS] Inputting relative datetimes

On Sun, Aug 28, 2011 at 5:39 AM, Dean Rasheed wrote: > On 28 August 2011 00:39, Robert Haas wrote: >> On Sat, Aug 27, 2011 at 7:43 AM, Dean Rasheed >> wrote: >>> On 27 August 2011 12:29, Dean Rasheed wrote: ... if nothing else it has been a fun exercise figuring out how the datetime

Re: [HACKERS] Inputting relative datetimes

Robert Haas writes: > On Sun, Aug 28, 2011 at 5:39 AM, Dean Rasheed > wrote: >> The attached patch makes "today", "tomorrow" and "yesterday" only set >> the year, month and day fields. All the other fields are already >> initialised to 0 at the start, and may be set non-zero before or after >> e

Re: [HACKERS] Inputting relative datetimes

On Tue, Aug 30, 2011 at 11:52 AM, Tom Lane wrote: > Robert Haas writes: >> On Sun, Aug 28, 2011 at 5:39 AM, Dean Rasheed >> wrote: >>> The attached patch makes "today", "tomorrow" and "yesterday" only set >>> the year, month and day fields. All the other fields are already >>> initialised to 0

Re: [HACKERS] dropdb and dropuser: IF EXISTS

On Mon, Aug 29, 2011 at 7:34 PM, Josh Kupershmidt wrote: > On Fri, Aug 26, 2011 at 10:42 PM, Robert Haas wrote: >> +1 for --if-exists, but -X isn't doing a lot for me, especially since >> we've used -X for other purposes in other commands.  I'd just skip >> having a short form for this one. > > F

Re: [HACKERS] strange row number estimates in pg9.1rc1

I wrote: > "Sergey E. Koposov" writes: >> I'm seeing something weird which looks like a bug in 9.1rc1 after the >> upgrade 8.4->9.0->9.1 done using pg_upgrade. > Hm, I wonder what pg_upgrade left relpages/reltuples set to ... Sure enough, that's the problem. pg_upgrade leaves relpages/reltuple

Re: [HACKERS] compile from git repository

On 08/30/2011 10:43 AM, Magnus Hagander wrote: On Tue, Aug 30, 2011 at 16:30, Tom Lane wrote: Andrew Dunstan writes: On 08/30/2011 07:11 AM, 权宗亮 wrote: I try to compile from git repository and get some errors. Windows 7 x86 VC Express 2008 perl v5.8.9 flex 2.5.4a-1 bison 2.4.1 Your versi

Re: [HACKERS] strange row number estimates in pg9.1rc1

On Tue, 30 Aug 2011, Tom Lane wrote: Sure enough, that's the problem. pg_upgrade leaves relpages/reltuples set to zero, but it also imports the visibility map pages from the old cluster. If the old visibility map shows the table as all-visible, then this happens when you try to VACUUM ANALYZE

Re: [HACKERS] strange row number estimates in pg9.1rc1

"Sergey E. Koposov" writes: > I wonder what should be the best way to proceed for already migrated > databases -- running analyze repeatedly may not be the best way for > very large clusters with large tables... You can just manually update pg_class.reltuples to a more reasonable value.

Re: [HACKERS] dropdb and dropuser: IF EXISTS

On Tue, Aug 30, 2011 at 11:14 AM, Robert Haas wrote: > Committed with some edits.  I stole the documentation language from > the DROP DATABASE and DROP USER pages and just copied it over, instead > of saying the same thing in different words.  And I rearranged the > options handling to be more con

Re: [HACKERS] dropdb and dropuser: IF EXISTS

On Tue, Aug 30, 2011 at 12:44 PM, Josh Kupershmidt wrote: > On Tue, Aug 30, 2011 at 11:14 AM, Robert Haas wrote: >> Committed with some edits.  I stole the documentation language from >> the DROP DATABASE and DROP USER pages and just copied it over, instead >> of saying the same thing in differen

Re: [HACKERS] WIP: Fast GiST index build

On 30.08.2011 13:29, Alexander Korotkov wrote: On Tue, Aug 30, 2011 at 1:13 PM, Heikki Linnakangas< heikki.linnakan...@enterprisedb.com> wrote: So, over 50% of the CPU time is spent in choosing a block from the temporary files. That should be pretty easy to improve.. Yes, probably we can jus

Re: [HACKERS] Inputting relative datetimes

Robert Haas writes: > Well, I'm fine with not back-patching it, but think the existing > behavior is flat wrong. I'm not arguing that this way isn't better, just that it's different. There have been zero user complaints about this behavior since Tom Lockhart put it in, more than ten years ago. T

Re: [HACKERS] pg_upgrade automatic testing

Here is an updated version of the pg_upgrade test script I posted a while ago. I've cleaned it up so that it offers moderately user-friendly feedback, it supports check and installcheck mode, and should use all the things from the right directories in either case. diff --git i/contrib/pg_upgrade/

[HACKERS] postgresql.conf archive_command example

I think it would be useful to add the following explanation and sample to the postgresql.conf sample file: diff --git i/src/backend/utils/misc/postgresql.conf.sample w/src/backend/utils/misc/postgresql.conf.sample --- i/src/backend/utils/misc/postgresql.conf.sample +++ w/src/backend/utils/misc/po

Re: [HACKERS] postgresql.conf archive_command example

On 31 August 2011 04:39, Peter Eisentraut wrote: > I think it would be useful to add the following explanation and sample > to the postgresql.conf sample file: > Good idea Peter, +1. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscr

[HACKERS] symbol mismatches on minor version upgrades

A while ago, I blogged about the following problem: (http://petereisentraut.blogspot.com/2011/07/undefined-symbol.html) Between PG 8.4.4 and 8.4.5, a new symbol PinPortal was added to the backend and plpgsql was changed to call it. So in that particular case, upgrading plpgsql without also upgrad

[HACKERS] Comparing two PostgreSQL databases -- order of pg_dump output

Hi, In order to compare the schema of two presumably identical databases, I've been diffing the output of pg_dump -Osx. However, I've found that the order of the output is not very reliable. For example, after recreating the Pagila sample database, I find the following: --- pagila.dmp 201

Re: [HACKERS] Comparing two PostgreSQL databases -- order of pg_dump output

On Tue, Aug 30, 2011 at 2:07 PM, Joe Abbate wrote: > Hi, > > In order to compare the schema of two presumably identical databases, I've > been diffing the output of pg_dump -Osx.  However, I've found that the order > of the output is not very reliable. what about using pg_dump -Fc -Osx and use pg

Re: [HACKERS] symbol mismatches on minor version upgrades

Peter Eisentraut writes: > A while ago, I blogged about the following problem: > (http://petereisentraut.blogspot.com/2011/07/undefined-symbol.html) While not wishing to deny that this can be a problem, I think you're overstating this aspect: > Now if this had been, say, plpython, which is also

Re: [HACKERS] spinlocks on HP-UX

I wrote: > I am hoping to do a similar test on another machine with $bignum Xeon > processors, to see if Intel hardware reacts any differently. But that > machine is in the Westford office which is currently without power, > so it will have to wait a few days. OK, the lights are on again in Westf

Re: [HACKERS] Comparing two PostgreSQL databases -- order of pg_dump output

Hola Jaime, On 08/30/2011 03:24 PM, Jaime Casanova wrote: what about using pg_dump -Fc -Osx and use pg_restore -l to list objects. then you can sort and compare objects and then a script that compare schema of objects extracting them with -P, -T or -t That appears to be of limited use (i.e., i

Re: [HACKERS] spinlocks on HP-UX

On Tue, Aug 30, 2011 at 4:05 PM, Tom Lane wrote: > This suggests that (1) an unlocked test in TAS_SPIN might be a good idea > on x86_64 after all, and (2) this test scenario may not be pushing the > system hard enough to expose limitations of the spinlock implementation. > > I am now thinking that

Re: [HACKERS] pg_upgrade automatic testing

Peter Eisentraut writes: > +# contrib/pg_upgrade/test.sh > +# > +# Test driver for pg_upgrade. Initializes a new database cluster, > +# runs the regression tests (to put in some data), runs pg_dumpall, > +# runs pg_upgrade, runs pg_dumpall again, compares the dumps. Hm .. my experience is that t

Re: [HACKERS] pg_upgrade automatic testing

On Tue, Aug 30, 2011 at 22:25, Tom Lane wrote: > Peter Eisentraut writes: >> +# contrib/pg_upgrade/test.sh >> +# >> +# Test driver for pg_upgrade.  Initializes a new database cluster, >> +# runs the regression tests (to put in some data), runs pg_dumpall, >> +# runs pg_upgrade, runs pg_dumpall ag

Re: [HACKERS] spinlocks on HP-UX

Robert Haas writes: > I am a bit surprised by your test results, because I also tried x86_64 > with an unlocked test, also on pgbench -S, and I am pretty sure I got > a regression. Maybe I'll try rerunning that. It seems possible that > the x86_64 results depend on the particular sub-architectur

Re: [HACKERS] WIP: Fast GiST index build

On Tue, Aug 30, 2011 at 9:29 PM, Heikki Linnakangas < heikki.linnakan...@enterprisedb.com> wrote: > On 30.08.2011 13:29, Alexander Korotkov wrote: > >> On Tue, Aug 30, 2011 at 1:13 PM, Heikki Linnakangas< >> heikki.linnakangas@**enterprisedb.com> >> wrote: >> >> So, over 50% of the CPU time is s

Re: [HACKERS] pg_upgrade automatic testing

Magnus Hagander writes: > On Tue, Aug 30, 2011 at 22:25, Tom Lane wrote: >> How can we improve things here?  I've toyed with the idea of installing >> pg_regress.so so that we can refer to it relative to $libdir, but that >> might be a bit invasive, especially if we were to try to back-patch it >

Re: [HACKERS] spinlocks on HP-UX

On Tue, Aug 30, 2011 at 4:37 PM, Tom Lane wrote: >> If this is on Linux, I am surprised >> that you didn't get killed by the lseek() contention problem on a >> machine with that many cores. > > Hm ... now that you mention it, all of these tests have been using > the latest-and-greatest unreleased

Re: [HACKERS] Comparing two PostgreSQL databases -- order of pg_dump output

On Tue, Aug 30, 2011 at 3:14 PM, Joe Abbate wrote: > Hola Jaime, > > On 08/30/2011 03:24 PM, Jaime Casanova wrote: >> >> what about using pg_dump -Fc -Osx and use pg_restore -l to list >> objects. then you can sort and compare objects and then a script that >> compare schema of objects extracting

Re: [HACKERS] Comparing two PostgreSQL databases -- order of pg_dump output

On 08/30/2011 05:33 PM, Jaime Casanova wrote: On Tue, Aug 30, 2011 at 3:14 PM, Joe Abbate wrote: Hola Jaime, On 08/30/2011 03:24 PM, Jaime Casanova wrote: what about using pg_dump -Fc -Osx and use pg_restore -l to list objects. then you can sort and compare objects and then a script that com

Re: [HACKERS] Comparing two PostgreSQL databases -- order of pg_dump output

Joe Abbate writes: > In order to compare the schema of two presumably identical databases, > I've been diffing the output of pg_dump -Osx. However, I've found that > the order of the output is not very reliable. Yeah, we've been around on that before. pg_dump does actually sort the output ite

Re: [HACKERS] spinlocks on HP-UX

Robert Haas writes: > On Tue, Aug 30, 2011 at 4:37 PM, Tom Lane wrote: >>> If this is on Linux, I am surprised >>> that you didn't get killed by the lseek() contention problem on a >>> machine with that many cores. >> Hm ... now that you mention it, all of these tests have been using >> the late

Re: [HACKERS] spinlocks on HP-UX

On Tue, Aug 30, 2011 at 6:33 PM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Aug 30, 2011 at 4:37 PM, Tom Lane wrote: If this is on Linux, I am surprised that you didn't get killed by the lseek() contention problem on a machine with that many cores. > >>> Hm ... now that you

Re: [HACKERS] Comparing two PostgreSQL databases -- order of pg_dump output

On 08/30/2011 06:07 PM, Tom Lane wrote: Yeah, we've been around on that before. pg_dump does actually sort the output items (modulo dependency requirements), but it sorts by the same "tag" values that are printed by pg_restore -l, and those aren't currently designed to be unique. It's not too c

Re: [HACKERS] Comparing two PostgreSQL databases -- order of pg_dump output

* Joe Abbate (j...@freedomcircle.com) wrote: > In order to compare the schema of two presumably identical > databases, I've been diffing the output of pg_dump -Osx. I'm not sure exactly how it does it, but check_postgres.pl offers this. http://bucardo.org/wiki/Check_postgres It also offers a w

Re: [HACKERS] spinlocks on HP-UX

Robert Haas writes: > On Tue, Aug 30, 2011 at 6:33 PM, Tom Lane wrote: >> I ran it up to "pgbench -c 200 -j 200 -S -T 300 bench" and still see >> vmstat numbers around 50% user time, 12% system time, 38% idle. >> So no lseek problem here, boss. Kernel calls itself 2.6.32-192.el6.x86_64. > Eh, wa

Re: [HACKERS] spinlocks on HP-UX

On Tue, Aug 30, 2011 at 7:21 PM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Aug 30, 2011 at 6:33 PM, Tom Lane wrote: >>> I ran it up to "pgbench -c 200 -j 200 -S -T 300 bench" and still see >>> vmstat numbers around 50% user time, 12% system time, 38% idle. >>> So no lseek problem here, bo

Re: [HACKERS] Comparing two PostgreSQL databases -- order of pg_dump output

Hi Stephen, On 08/30/2011 07:11 PM, Stephen Frost wrote: * Joe Abbate (j...@freedomcircle.com) wrote: In order to compare the schema of two presumably identical databases, I've been diffing the output of pg_dump -Osx. I'm not sure exactly how it does it, but check_postgres.pl offers this. ht

Re: [HACKERS] spinlocks on HP-UX

I wrote: > No I/O anywhere. I'm thinking the reported idle time must correspond to > spinlock delays that are long enough to reach the select() calls in > s_lock. If so, 38% is depressingly high, but it's not out of line with > what we've seen in the past in tests designed to provoke spinlock > c

Re: [HACKERS] compile from git repository

Thank you everyone. Now, there is still one error: Build started: Project: postgres, Configuration: Release|Win32 Running flex on src\backend\parser\scan.l VCBUILD : cygwin warning : MS-DOS style path detected: src\backend\parser\scan.l Preferred POSIX equivalent is: src/backend/parse

Re: [HACKERS] compile from git repository

On 08/30/2011 09:50 PM, 权宗亮 wrote: > Thank you everyone. > > Now, there is still one error: > > Build started: Project: postgres, Configuration: Release|Win32 > Running flex on src\backend\parser\scan.l > VCBUILD : cygwin warning : > MS-DOS style path detected: src\backend\parser\scan.l > Preferr

Re: [HACKERS] Comparing two PostgreSQL databases -- order of pg_dump output

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Note that what I'm looking for is something to compare just about > EVERYTHING DDL under the PostgreSQL sun: tables, types, functions, > operators, etc. The description of same_schema appears to imply only a > subset of objects are compared

[HACKERS] casting between range types

At one point, the question of casting between range types came up. At first, this seemed like a fairly reasonable suggestion, but now I don't think I like the semantics. A normal cast changes between essentially equivalent values in different domains. For instance 3 as an int4 is equivalent to 3.0

Re: [HACKERS] casting between range types

On 31.08.2011 09:14, Jeff Davis wrote: At one point, the question of casting between range types came up. At first, this seemed like a fairly reasonable suggestion, but now I don't think I like the semantics. A normal cast changes between essentially equivalent values in different domains. For i