Re: [HACKERS] Another extensions bug

2011-08-24 Thread Dimitri Fontaine
Tom Lane t...@sss.pgh.pa.us writes:
 On further reflection, it seems more in keeping with the coding
 elsewhere in this module to treat this as a distinct dependency type,
 instead of confusing it with a NORMAL dependency.  There's no actual
 functional difference at the moment, but more info is better than less.

Seems better indeed.  In my first implementation, we had no EXTENSION
kind of dependency and used only INTERNAL, which IIRC reads reverse than
the other ones.  Having to finally have EXTENSION and REVERSE kinds of
dependencies here is not that surprising.

 Hence, proposed patch attached (which also improves some of the related
 comments).

+1 on the idea, although I'm not in a position to further review or play
with the patch today.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

-- 
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] cheaper snapshots redux

2011-08-24 Thread Markus Wanner
Hello Dimitri,

On 08/23/2011 06:39 PM, Dimitri Fontaine wrote:
 I'm far from familiar with the detailed concepts here, but allow me to
 comment.  I have two open questions:
 
  - is it possible to use a distributed algorithm to produce XIDs,
something like Vector Clocks?
 
Then each backend is able to create a snapshot (well, and XID) on its
own, and any backend is still able to compare its snapshot to any
other snapshot (well, XID)

Creation of snapshots and XID assignment are not as related as you imply
here.  Keep in mind that a read-only transaction have a snapshot, but no
XID.  (Not sure if it's possible for a transaction to have an XID, but
no snapshot.  If it only touches system catalogs with SnapshotNow,
maybe?  Don't think we support that, ATM).

  - is it possible to cache the production of the next snapshots so that
generating an XID only means getting the next in a pre-computed
vector?

The way I look at it, what Robert proposed can be thought of as cache
the production of the next snapshot, with a bit of a stretch of what a
cache is, perhaps.  I'd rather call it early snapshot creation, maybe
look-ahead something.

ATM backends all scan ProcArray to generate their snapshot.  Instead,
what Robert proposes would - sometimes, somewhat - move that work from
snapshot creation time to commit time.

As Tom points out, the difficulty lies in the question of when it's
worth doing that:  if you have lots of commits in a row, and no
transaction ever uses the (pre generated) snapshots of the point in time
in between, then those were wasted.  OTOH, if there are just very few
COMMITs spread across lots of writes, the read-only backends will
re-create the same snapshots, over and over again.  Seems wasteful as
well (as GetSnapshotData popping up high on profiles confirms somewhat).

Hope to have cleared up things a bit.

Regards

Markus

-- 
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] cheaper snapshots redux

2011-08-24 Thread Markus Wanner
Robert, Jim,

thanks for thinking out loud about dynamic allocation of shared memory.
 Very much appreciated.

On 08/23/2011 01:22 AM, Robert Haas wrote:
 With respect to a general-purpose shared memory allocator, I think
 that there are cases where that would be useful to have, but I don't
 think there are as many of them as many people seem to think.  I
 wouldn't choose to implement this using a general-purpose allocator
 even if we had it, both because it's undesirable to allow this or any
 subsystem to consume an arbitrary amount of memory (nor can it fail...
 especially in the abort path) and because a ring buffer is almost
 certainly faster than a general-purpose allocator.

I'm in respectful disagreement regarding the ring-buffer approach and
think that dynamic allocation can actually be more efficient if done
properly, because there doesn't need to be head and tail pointers, which
might turn into a point of contention.

As a side note: that I've been there with imessages.  Those were first
organized as a ring-bufffer.  The major problem with that approach was
the imessages were consumed with varying delay.  In case an imessage was
left there for a longer amount of time, it blocked creation of new
imessages, because the ring-buffer cycled around once and its head
arrived back at the unconsumed imessage.

IIUC (which might not be the case) the same issue applies for snapshots.

Regards

Markus Wanner

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] Windows env returns error while running select pgstatindex

2011-08-24 Thread Rushabh Lathia
Description:
===

Error Message  invalid input syntax for type double precision: -1#I is
displayed while running select pgstatindex

Issue only getting reproduce on windows environment.

Analysis:
=

Consider the following testcase to reproduce the issue on windows:

create table test (a  int primary key );

Windows Output:
==

psqlselect pgstatindex('public.test_pkey');
ERROR:  invalid input syntax for type double precision: -1.#J

Linux output:
==

psql=# select pgstatindex('public.test_pkey');
pgstatindex
---
 (2,0,0,0,0,0,0,0,NaN,NaN)
(1 row)

here when we run the select on linux its returning proper result and on
windows error coming from float8in() while trying to work for the NaN
values.

After debugging I noticed that 0/0 returning NaN on linux but it returns
-1.#JIND on windows. Now when float8in() getting call for such value
on windows it ending up with error  invalid input syntax for type double
precision: as strtod() not able to understand such values.

I added to check into pgstatindex() to avoid 0/0 situation and issue got
fixed.

PFA patch for the same.


Thanks,
Rushabh Lathia

EnterpriseDB Corporation
The Enterprise Postgres Company


Website: http://www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb


win_pgstat_fix.patch
Description: Binary data

-- 
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] the big picture for index-only scans

2011-08-24 Thread Gokulakannan Somasundaram


 There are extensive comments on this in visibilitymap.c and, in
 heapam.c, heap_xlog_visible().

 I went through the design again and again. I am convinced that this should
not have any functional bugs and should not cause much performance issues.
Nice thoughts on bypassing the WAL Logging.

Gokul.


Re: [HACKERS] WIP: Fast GiST index build

2011-08-24 Thread Alexander Korotkov
I've added some testing results to the wiki page:
http://wiki.postgresql.org/wiki/Fast_GiST_index_build_GSoC_2011
There are not all the results I planned for the first chunk because it takes
more time than I expect.
Some notes about it.

Now I see two causes which accelerate regular build of GiST indexes:
1) As it was noted before regular index build of pretty ordered dataset is
fast.
2) I found that worse index is faster to build. I mean worse index is index
with higher overlaps. Function gistchoose selects the first index tuple with
zero penalty if any. Thus, with higher overlap in root page only few index
tuples of it will be choosed for insert. And, recursively, only small part
of the tree will be used for actual inserts. And that part of tree can
easier fit to the cache. Thus, high overlaps  makes inserts cheaper as much
as searches expensiver.

In the tests on the first version of patch I found index quality of regular
build much better than it of buffering build (without neighborrelocation).
Now it's similar, though it's because index quality of regular index build
become worse. There by in current tests regular index build is faster than
in previous. I see following possible causes of it:
 1) I didn't save source random data. So, now it's a new random data.
2) Some environment parameters of my test setup may alters, though I doubt.
Despite these possible explanation it seems quite strange for me.

In order to compare index build methods on more qualitative indexes, I've
tried to build indexes with my double sorting split method (see:
http://syrcose.ispras.ru/2011/files/SYRCoSE2011_Proceedings.pdf#page=36). So
on uniform dataset search is faster in about 10 times! And, as it was
expected, regular index build becomes much slower. It runs more than 60
hours and while only 50% of index is complete (estimated by file sizes).

Also, automatic switching to buffering build shows better index quality
results in all the tests. While it's hard for me to explain that.

--
With best regards,
Alexander Korotkov.


Re: [HACKERS] Windows env returns error while running select pgstatindex

2011-08-24 Thread Tom Lane
Rushabh Lathia rushabh.lat...@gmail.com writes:
 After debugging I noticed that 0/0 returning NaN on linux but it returns
 -1.#JIND on windows.

[ rolls eyes ]

 I added to check into pgstatindex() to avoid 0/0 situation and issue got
 fixed.

Hmm.  I agree we need to avoid executing 0/0 here, but should we force
the result to 0, or to NaN?

regards, tom lane

-- 
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] Windows env returns error while running select pgstatindex

2011-08-24 Thread Euler Taveira de Oliveira

Em 24-08-2011 11:27, Tom Lane escreveu:

Hmm.  I agree we need to avoid executing 0/0 here, but should we force
the result to 0, or to NaN?


If it returns NaN on other platforms, let's be consistent.


--
  Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
  PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
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] Another extensions bug

2011-08-24 Thread Tom Lane
Dimitri Fontaine dimi...@2ndquadrant.fr writes:
 Tom Lane t...@sss.pgh.pa.us writes:
 Hence, proposed patch attached (which also improves some of the related
 comments).

 +1 on the idea, although I'm not in a position to further review or play
 with the patch today.

Further testing shows that this patch still doesn't make things really
work properly.  There's yet *another* bug with extension-dropping: given
a case such as types cube[] and cube both belonging to extension cube,
the end result is that type cube[] never gets dropped at all!  This is
because, since cube[] has both INTERNAL and EXTENSION dependencies, it's
impossible for findDependentObjects to get past its first loop when
cube[] is the target.  It will always recurse to the other owning
object.  Infinite recursion is avoided because it finds the other owning
object already on the stack, but we never get to the point of deciding
that it's okay to delete type cube[].  So:

test=# create extension cube;
CREATE EXTENSION
test=# \dT
 List of data types
 Schema | Name | Description
 
+--+
-
 public | cube | multi-dimensional cube '(FLOAT-1, FLOAT-2, ..., FLOAT-N), (FLOA
T-1, FLOAT-2, ..., FLOAT-N)'
(1 row)

test=# drop extension cube;
DROP EXTENSION
test=# \dT
  List of data types
 Schema | Name  | Description 
+---+-
 public | ???[] | 
(1 row)

test=# 

This is another bug that was arguably latent in the original coding,
but could not manifest as long as there was only one INTERNAL-like
dependency per object.

I think the right fix is to replace the simple is the referenced object
on top of the stack? check at lines 600ff with a check for is the
referenced object anywhere in the stack?.

It's also becoming brutally obvious that we need some regression tests
checking extension drop scenarios.  I'll work on that today too.

regards, tom lane

-- 
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] Windows env returns error while running select pgstatindex

2011-08-24 Thread Tom Lane
Euler Taveira de Oliveira eu...@timbira.com writes:
 Em 24-08-2011 11:27, Tom Lane escreveu:
 Hmm.  I agree we need to avoid executing 0/0 here, but should we force
 the result to 0, or to NaN?

 If it returns NaN on other platforms, let's be consistent.

I kinda suspect that the NaN behavior was not designed but accidental.
What I'm wondering is whether it's really the right, sensible,
behavior.

On reflection I suspect it isn't --- it'd bollix sum() or avg()
calculations over the function's results, for instance.  But now
I'm not sure zero is the right thing to put in, either.

regards, tom lane

-- 
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] Windows env returns error while running select pgstatindex

2011-08-24 Thread Robert Haas
On Wed, Aug 24, 2011 at 11:45 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Euler Taveira de Oliveira eu...@timbira.com writes:
 Em 24-08-2011 11:27, Tom Lane escreveu:
 Hmm.  I agree we need to avoid executing 0/0 here, but should we force
 the result to 0, or to NaN?

 If it returns NaN on other platforms, let's be consistent.

 I kinda suspect that the NaN behavior was not designed but accidental.
 What I'm wondering is whether it's really the right, sensible,
 behavior.

 On reflection I suspect it isn't --- it'd bollix sum() or avg()
 calculations over the function's results, for instance.  But now
 I'm not sure zero is the right thing to put in, either.

It's not very sensible to sum() or avg() such values from different
tables, but if you did wish to do so it would be easy enough to shove
a CASE statement in there to filter out the NaN results.

On a blank slate, I might choose to do it differently, but considering
that we have numerous releases out in the field that return NaN, I
think we should stick with that rather than using this minor bug as an
excuse to change the answer on platforms where this isn't already
broken.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] Windows env returns error while running select pgstatindex

2011-08-24 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Wed, Aug 24, 2011 at 11:45 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 I kinda suspect that the NaN behavior was not designed but accidental.
 What I'm wondering is whether it's really the right, sensible,
 behavior.

 On a blank slate, I might choose to do it differently, but considering
 that we have numerous releases out in the field that return NaN, I
 think we should stick with that rather than using this minor bug as an
 excuse to change the answer on platforms where this isn't already
 broken.

[ pokes at it... ]  Hmm, you're right, everything back to 8.2 produces
NaNs on this test case (at least on IEEE-compliant platforms).  I yield
to the let's emit NaN viewpoint.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2011-08-24 Thread Daniel Farina
Hello list,

At Heroku we use CREATE INDEX CONCURRENTLY with great success, but
recently when frobbing around some indexes I realized that there is no
equivalent for DROP INDEX, and this is a similar but lesser problem
(as CREATE INDEX takes much longer), as DROP INDEX takes an ACCESS
EXCLUSIVE lock on the parent table while doing the work to unlink
files, which nominally one would think to be trivial, but I assure you
it is not at times for even indexes that are a handful of gigabytes
(let's say ~= a dozen).  By non-trivial, I mean it can take 30+
seconds, but less than a couple of minutes.  The storage layer
(starting from the higher levels of abstraction) are XFS, a somewhat
trivial lvm setup, mdraid (8-ways), Amazon EBS (NBD?).

I was poking around at tablecmds and index.c and wonder if a similar
two-pass approach as used by CREATE INDEX CONCURRENTLY can be used to
create a DROP INDEX CONCURRENTLY, and if there would be any interest
in accepting such a patch.

Quoth index.c:

/*
 * To drop an index safely, we must grab exclusive lock on its parent
 * table.  Exclusive lock on the index alone is insufficient because
 * another backend might be about to execute a query on the parent 
table.
 * If it relies on a previously cached list of index OIDs, then it could
 * attempt to access the just-dropped index.  We must therefore take a
 * table lock strong enough to prevent all queries on the table from
 * proceeding until we commit and send out a shared-cache-inval notice
 * that will make them update their index lists.
 */

Could I make the ACCESS EXCLUSIVE section just long enough to commit
catalog updates, and then have the bulk of the work happen afterwards?

The general idea is:

1) set an index as invalid, to ensure no backend will use it in planning
2) wait for the xmin horizon to advance to ensure no open snapshots
that may not see the invalidation of the index are gone (is there a
way to tighten that up? although even this conservative version would
be 80-90% of the value for us...)
3) then use performDeletions without taking a lock on the parent
table, similar to what's in tablecmds.c already.

A DROP INDEX CONCURRENTLY may leave an invalid index if aborted
instead of waiting for statement confirmation, just like CREATE INDEX
CONCURRENTLY.

--
fdr

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

2011-08-24 Thread Peter Eisentraut
On tis, 2011-08-23 at 21:17 -0400, Tom Lane wrote:
 There are at least two ways we could fix this: change
 earthdistance/Makefile to do this:
 
 REGRESS_OPTS = --extra-install=contrib/cube --dbname=$(CONTRIB_TESTDB)
 
 or change pgxs.mk to do this:
 
 REGRESS_OPTS += --dbname=$(CONTRIB_TESTDB)
 
 I'm leaning towards the latter as being less prone to mistakes of
 omission.  If there's some reason for a pgxs-using makefile to
 override
 the target DB name, it can set CONTRIB_TESTDB instead of messing with
 REGRESS_OPTS.

I like the latter solution as well.


-- 
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] Should I implement DROP INDEX CONCURRENTLY?

2011-08-24 Thread Merlin Moncure
On Wed, Aug 24, 2011 at 1:24 PM, Daniel Farina dan...@heroku.com wrote:
 Hello list,

 At Heroku we use CREATE INDEX CONCURRENTLY with great success, but
 recently when frobbing around some indexes I realized that there is no
 equivalent for DROP INDEX, and this is a similar but lesser problem
 (as CREATE INDEX takes much longer), as DROP INDEX takes an ACCESS
 EXCLUSIVE lock on the parent table while doing the work to unlink
 files, which nominally one would think to be trivial, but I assure you
 it is not at times for even indexes that are a handful of gigabytes
 (let's say ~= a dozen).  By non-trivial, I mean it can take 30+
 seconds, but less than a couple of minutes.  The storage layer
 (starting from the higher levels of abstraction) are XFS, a somewhat
 trivial lvm setup, mdraid (8-ways), Amazon EBS (NBD?).

Are you sure that you are really waiting on the time to unlink the
file?  there's other stuff going on in there like waiting for lock,
plan invalidation, etc.  Point being, maybe the time consuming stuff
can't really be deferred which would make the proposal moot.

merlin

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

2011-08-24 Thread Peter Eisentraut
On lör, 2011-08-20 at 09:56 -0400, Bruce Momjian wrote:
 Peter Eisentraut wrote:
  On tis, 2011-08-16 at 20:35 -0400, Tom Lane wrote:
   In fact, now that I think about it, setting the transaction snapshot
   from a utility statement would be functionally useful because then you
   could take locks beforehand.
  
  Another issue is that in some client interfaces, BEGIN and COMMIT are
  hidden behind API calls, which cannot easily be changed or equipped with
  new parameters.  So in order to have this functionality available
  through those interfaces, we'd need a separately callable command.
 
 How do they set a transaction to SERIALIZABLE?  Seem the same syntax
 should be used here.

The API typically has parameters to set the isolation level, since
that's a standardized property.



-- 
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] make -j4 world falls over

2011-08-24 Thread Peter Eisentraut
On tor, 2011-08-18 at 18:56 -0400, Tom Lane wrote:
 I ran into $SUBJECT whilst doing trial RPM packaging of 9.1.  The problem
 is that make starts building contrib modules before errcodes.h has been
 made, leading to failures such as
 
 In file included from ../../src/include/postgres.h:48:0,
  from auth_delay.c:12:
 ../../src/include/utils/elog.h:69:28: fatal error: utils/errcodes.h: No such 
 file or directory
 compilation terminated.
 In file included from ../../src/include/postgres.h:48:0,
  from adminpack.c:15:
 ../../src/include/utils/elog.h:69:28: fatal error: utils/errcodes.h: No such 
 file or directory
 compilation terminated.
 
 I can work around this for the moment, but I think it's a must fix
 before release.

Fixed.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] patch to slightly improve clarity of a comment in postgresql.conf.sample

2011-08-24 Thread Dougal Sutherland
The attached change to postgresql.conf.sample makes it more clear at a
glance that the default value of listen_addresses is 'localhost', not
'localhost, *'. This would have saved a friend an hour or two of fiddling
tonight.

Thanks,
Dougal


doc-clarity.patch
Description: Binary data

-- 
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] make -j4 world falls over

2011-08-24 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes:
 On tor, 2011-08-18 at 18:56 -0400, Tom Lane wrote:
 I ran into $SUBJECT whilst doing trial RPM packaging of 9.1.

 Fixed.

Works for me --- thanks!

regards, tom lane

-- 
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] Should I implement DROP INDEX CONCURRENTLY?

2011-08-24 Thread Robert Haas
On Wed, Aug 24, 2011 at 2:24 PM, Daniel Farina dan...@heroku.com wrote:
 Could I make the ACCESS EXCLUSIVE section just long enough to commit
 catalog updates, and then have the bulk of the work happen afterwards?

 The general idea is:

 1) set an index as invalid, to ensure no backend will use it in planning
 2) wait for the xmin horizon to advance to ensure no open snapshots
 that may not see the invalidation of the index are gone (is there a
 way to tighten that up? although even this conservative version would
 be 80-90% of the value for us...)
 3) then use performDeletions without taking a lock on the parent
 table, similar to what's in tablecmds.c already.

 A DROP INDEX CONCURRENTLY may leave an invalid index if aborted
 instead of waiting for statement confirmation, just like CREATE INDEX
 CONCURRENTLY.

This might be a dumb idea, but could we rearrange CommitTransaction()
so that smgrDoPendingDeletes() happens just a bit further down, after
those ResourceOwnerRelease() calls?  It seems like that might
accomplish what you're trying to do here without needing a new
command.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] Should I implement DROP INDEX CONCURRENTLY?

2011-08-24 Thread Tom Lane
Merlin Moncure mmonc...@gmail.com writes:
 On Wed, Aug 24, 2011 at 1:24 PM, Daniel Farina dan...@heroku.com wrote:
 At Heroku we use CREATE INDEX CONCURRENTLY with great success, but
 recently when frobbing around some indexes I realized that there is no
 equivalent for DROP INDEX, and this is a similar but lesser problem
 (as CREATE INDEX takes much longer), as DROP INDEX takes an ACCESS
 EXCLUSIVE lock on the parent table while doing the work to unlink
 files, which nominally one would think to be trivial, but I assure you
 it is not at times for even indexes that are a handful of gigabytes
 (let's say ~= a dozen).

 Are you sure that you are really waiting on the time to unlink the
 file?  there's other stuff going on in there like waiting for lock,
 plan invalidation, etc.  Point being, maybe the time consuming stuff
 can't really be deferred which would make the proposal moot.

Assuming the issue really is the physical unlinks (which I agree I'd
like to see some evidence for), I wonder whether the problem could be
addressed by moving smgrDoPendingDeletes() to after locks are released,
instead of before, in CommitTransaction/AbortTransaction.  There does
not seem to be any strong reason why we have to do that before lock
release, since incoming potential users of a table should not be trying
to access the old physical storage after that anyway.

regards, tom lane

-- 
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] Should I implement DROP INDEX CONCURRENTLY?

2011-08-24 Thread Daniel Farina
On Wed, Aug 24, 2011 at 12:38 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Merlin Moncure mmonc...@gmail.com writes:
 On Wed, Aug 24, 2011 at 1:24 PM, Daniel Farina dan...@heroku.com wrote:
 At Heroku we use CREATE INDEX CONCURRENTLY with great success, but
 recently when frobbing around some indexes I realized that there is no
 equivalent for DROP INDEX, and this is a similar but lesser problem
 (as CREATE INDEX takes much longer), as DROP INDEX takes an ACCESS
 EXCLUSIVE lock on the parent table while doing the work to unlink
 files, which nominally one would think to be trivial, but I assure you
 it is not at times for even indexes that are a handful of gigabytes
 (let's say ~= a dozen).

 Are you sure that you are really waiting on the time to unlink the
 file?  there's other stuff going on in there like waiting for lock,
 plan invalidation, etc.  Point being, maybe the time consuming stuff
 can't really be deferred which would make the proposal moot.

 Assuming the issue really is the physical unlinks (which I agree I'd
 like to see some evidence for), I wonder whether the problem could be
 addressed by moving smgrDoPendingDeletes() to after locks are released,
 instead of before, in CommitTransaction/AbortTransaction.  There does
 not seem to be any strong reason why we have to do that before lock
 release, since incoming potential users of a table should not be trying
 to access the old physical storage after that anyway.

Alright, since this concern about confirming the expensive part of
index dropping has come up a few times but otherwise the waters are
warm, I'll go ahead and do some work to pin things down a bit before
we continue working on those assumptions.

-- 
fdr

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] is there somebody with access to db2?

2011-08-24 Thread Pavel Stehule
Hello,

I am interesting about a CONTINUE HANDLER behave, please can you
check, and send a result of following function?


   CREATE FUNCTION REVERSE(INSTR VARCHAR(4000))
 RETURNS INT
 DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
 BEGIN
 DECLARE RES INT;
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
   SET RES = 10;
 BEGIN
   SIGNAL '3';
   SET RES = 30;
 END;
 RETURN RES;
   END;

Thank you

Regards

Pavel Stehule

-- 
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] pg_dump --exclude-table-data

2011-08-24 Thread Josh Berkus

 For those who are (like my clients :-) ) anxious to get their hands on
 this immediately, a backport patch is also attached which applies to 9.0
 sources, and applies with offsets to 8.4 sources.

FWIW, I have immediate use for this in creating cut-down versions of
databases for testing purposes.  It'll eliminate a couple pages of shell
scripts for me.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

-- 
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] SSI 2PC coverage

2011-08-24 Thread Alvaro Herrera
Excerpts from Kevin Grittner's message of mar ago 23 15:07:33 -0300 2011:
 Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote:

  I did change the lexer slightly, to trim whitespace from the
  beginning and end of SQL blocks. This cuts the size of expected
  output a bit, and makes it look nicer anyway.
  
 OK.  You missed the alternative outputs for a couple files.  These
 are needed to get successful tests with
 default_transaction_isolation = 'serializable' or 'repeatable read'.
 Patch attached.

Thanks, applied.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
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] SSI 2PC coverage

2011-08-24 Thread Alvaro Herrera
Excerpts from Heikki Linnakangas's message of jue ago 18 09:57:34 -0400 2011:

 Committed. I removed the second expected output file, and marked the 
 prepared-transactions tests in the schedule as ignore instead. That 
 way if max_prepared_transactions=0, you get a notice that the test case 
 failed, but pg_regress still returns 0 as exit status.

After having to play with this, I didn't like it very much, because
regression.diffs gets spammed with the (rather massive and completely
useless) diff in that test.  For the xml tests, rather than ignoring it
fail on an installation without libxml, we use an alternative output.

Unless there are objections, I will commit the alternative file proposed
by Dan.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
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] pg_dump --exclude-table-data

2011-08-24 Thread bricklen
On Wed, Aug 24, 2011 at 2:01 PM, Josh Berkus j...@agliodbs.com wrote:

 FWIW, I have immediate use for this in creating cut-down versions of
 databases for testing purposes.  It'll eliminate a couple pages of shell
 scripts for me.

Speaking of cut-down versions, I have recently been using pg_sample,
and been happy with the resulting subset database. I created a db 10
GB in size from a source db ~600Gb in a few minutes.

https://github.com/mla/pg_sample

-- 
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] pg_restore --no-post-data and --post-data-only

2011-08-24 Thread Josh Berkus
On 8/23/11 1:30 PM, Andrew Dunstan wrote:
 
 Attached is an undocumented patch that allows pg_restore to omit
 post-data items or omit all but post-data items. This has been discussed
 before, and Simon sent in a patch back on 2008, which has bitrotted
 some. I'm not sure why it was dropped at the time, but I think it's time
 to do this. This patch relies on some infrastructure that was added
 since Simon's patch, so it works a bit differently  (and more simply).

If it's not clear from Andrew's description, the purpose of this patch
is to allow dividing your pgdump into 3 portions:

1. schema
2. data
3. constraints/indexes

This allows users to implement a number of custom solutions for ad-hoc
parallel dump, conditional loading, data munging and sampled databases.
 While doing so was possible before using the manifest from pg_restore
-l, the manifest approach has been complex to automate and relies on
obscure knowledge.

I have immediate production use for this patch and may be backporting it.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

-- 
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] SSI 2PC coverage

2011-08-24 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com writes:
 After having to play with this, I didn't like it very much, because
 regression.diffs gets spammed with the (rather massive and completely
 useless) diff in that test.  For the xml tests, rather than ignoring it
 fail on an installation without libxml, we use an alternative output.

 Unless there are objections, I will commit the alternative file proposed
 by Dan.

+1 ... ignore is a pretty ugly hack here.

Eventually we need some way of detecting that specific tests should be
skipped because they're irrelevant to the current system configuration.
contrib/sepgsql is already doing something of the sort, but it's rather
crude ...

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [pgsql-advocacy] [HACKERS] blog post on ancient history

2011-08-24 Thread John Wang
FYI, I was just checking out the contributors page and noticed that he's
listed under Past Contributors.

http://www.postgresql.org/community/contributors/


On Fri, Jul 8, 2011 at 1:55 PM, Bruce Momjian br...@momjian.us wrote:

 Tom Lane wrote:
  Robert Haas robertmh...@gmail.com writes:
   Anyone feels in mood for a comment?
 
   I see our mailing list archives for pgsql-hackers only go back to
   1997, so it's hard to track down what was going on in 1996.  But as
   for why no one remembers the guy, it's probably because we've had
   nearly 100% churn in the set of people who are involved.  Tom Lane
   isn't mentioned in the commit log until 1998.  We could see if Bruce
   or Marc remember him, but just to put this in perspective, the guy
   made 6 commits out of almost 900 that year.
 
  According to the logs there was a seventh patch committed for him by
  Marc, but still: seven patches, touching only libpq and psql (not by any
  means as internal as this blogger thinks), committed over a period of
  about a month.  That's not exactly a large or sustained contribution.
  Is it surprising that everyone had forgotten it a few years later?
 
   I don't think we had the
   same standards for granting commit access back then that we do now.
 
  Yeah, the only thing that's even mildly surprising is that he seems to
  have been given commit privileges after only one patch.  However,
  there's an indication in one of the commit messages that he'd previously
  contributed to the code while Berkeley had it:
 
  1996-07-25 02:46  julian
 
* src/bin/psql/psql.c: Large re-write/enhancement. In pg-101 Jolly
only included a smaller part of my (proff) patch. This is the rest
of it, with a few, mainly aesthetic changes. I've removed a lot of
redundency from the original code, added support for the new
PQprint() routines in libpq, expanded tables, and a few generally
nifty ways of massaging data in and out of the backend. Still needs
some good stress testing.
 
  so maybe that history had something to do with it.

 The spring/summer of 1996 was a time when we were trying to gather all
 the scattered work of people who had created patches to Postgres95 but
 had not been integrated by Jolly.  Seems Julian had been in that group
 so his patches were quickly applied.  If he had asked for commit, I
 would have given it to him because he had a history of contributing to
 the project (which I could not confirm).

 FYI, I do think I have an archive of much of the pg95-...@ki.net on a
 DAT tape in my basement, and I have an unpowered computer down there
 with a DAT tape drive ... hmmm.

 --
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

 --
 Sent via pgsql-advocacy mailing list (pgsql-advoc...@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-advocacy



Re: [HACKERS] SSI 2PC coverage

2011-08-24 Thread Robert Haas
On Wed, Aug 24, 2011 at 9:11 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Alvaro Herrera alvhe...@commandprompt.com writes:
 After having to play with this, I didn't like it very much, because
 regression.diffs gets spammed with the (rather massive and completely
 useless) diff in that test.  For the xml tests, rather than ignoring it
 fail on an installation without libxml, we use an alternative output.

 Unless there are objections, I will commit the alternative file proposed
 by Dan.

 +1 ... ignore is a pretty ugly hack here.

 Eventually we need some way of detecting that specific tests should be
 skipped because they're irrelevant to the current system configuration.
 contrib/sepgsql is already doing something of the sort, but it's rather
 crude ...

I'm fairly unhappy with the fact that we don't have a better way of
deciding whether we should even *build* sepgsql.  The --with-selinux
flag basically doesn't do anything except enable the compile of that
contrib module, and that's kind of a lame use of a configure flag.

Not that I have a better idea...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] pg_restore --no-post-data and --post-data-only

2011-08-24 Thread Andrew Dunstan



On 08/24/2011 08:43 PM, Josh Berkus wrote:

On 8/23/11 1:30 PM, Andrew Dunstan wrote:

Attached is an undocumented patch that allows pg_restore to omit
post-data items or omit all but post-data items. This has been discussed
before, and Simon sent in a patch back on 2008, which has bitrotted
some. I'm not sure why it was dropped at the time, but I think it's time
to do this. This patch relies on some infrastructure that was added
since Simon's patch, so it works a bit differently  (and more simply).

If it's not clear from Andrew's description, the purpose of this patch
is to allow dividing your pgdump into 3 portions:

1. schema
2. data
3. constraints/indexes

This allows users to implement a number of custom solutions for ad-hoc
parallel dump, conditional loading, data munging and sampled databases.
  While doing so was possible before using the manifest from pg_restore
-l, the manifest approach has been complex to automate and relies on
obscure knowledge.

I have immediate production use for this patch and may be backporting it.


It's already backported, at least as far as 8.4. Check your email :-)

cheers

andrew

--
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] skip WAL on COPY patch

2011-08-24 Thread Robert Haas
On Tue, Aug 23, 2011 at 4:51 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 Excerpts from Robert Haas's message of mar ago 23 17:43:13 -0300 2011:
 On Tue, Aug 23, 2011 at 4:17 PM, Tom Lane t...@sss.pgh.pa.us wrote:
  Robert Haas robertmh...@gmail.com writes:
  What I think would be really interesting is a way to make this work
  when the table *isn't* empty.  In other words, have a COPY option that
  (1) takes an exclusive lock on the table, (2) writes the data being
  inserted into new pages beyond the old EOF, and (3) arranges for crash
  recovery or transaction abort to truncate the table back to its
  previous length.  Then you could do fast bulk loads even into a table
  that's already populated, so long as you don't mind that the table
  will be excusive-locked and freespace within existing heap pages won't
  be reused.
 
  What are you going to do with the table's indexes?

 Oh, hmm.  That's awkward.

 If you see what I proposed, it's simple: you can scan the new segment(s)
 and index the tuples found there (maybe in bulk which would be even
 faster).

You can do that much even if you just append to the file - you don't
need variable-length segments to make that part work.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] cheaper snapshots redux

2011-08-24 Thread Robert Haas
On Wed, Aug 24, 2011 at 4:30 AM, Markus Wanner mar...@bluegap.ch wrote:
 I'm in respectful disagreement regarding the ring-buffer approach and
 think that dynamic allocation can actually be more efficient if done
 properly, because there doesn't need to be head and tail pointers, which
 might turn into a point of contention.

True; although there are some other complications.  With a
sufficiently sophisticated allocator you can avoid mutex contention
when allocating chunks, but then you have to store a pointer to the
chunk somewhere or other, and that then requires some kind of
synchronization.

 As a side note: that I've been there with imessages.  Those were first
 organized as a ring-bufffer.  The major problem with that approach was
 the imessages were consumed with varying delay.  In case an imessage was
 left there for a longer amount of time, it blocked creation of new
 imessages, because the ring-buffer cycled around once and its head
 arrived back at the unconsumed imessage.

 IIUC (which might not be the case) the same issue applies for snapshots.

One difference with snapshots is that only the latest snapshot is of
any interest.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] cheaper snapshots redux

2011-08-24 Thread Markus Wanner
Robert,

On 08/25/2011 04:59 AM, Robert Haas wrote:
 True; although there are some other complications.  With a
 sufficiently sophisticated allocator you can avoid mutex contention
 when allocating chunks, but then you have to store a pointer to the
 chunk somewhere or other, and that then requires some kind of
 synchronization.

Hm.. right.

 One difference with snapshots is that only the latest snapshot is of
 any interest.

Theoretically, yes.  But as far as I understood, you proposed the
backends copy that snapshot to local memory.  And copying takes some
amount of time, possibly being interrupted by other backends which add
newer snapshots...  Or do you envision the copying to restart whenever a
new snapshot arrives?

Regards

Markus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers