Re: [HACKERS] synchronized snapshots

2011-10-23 Thread Thom Brown
On 23 October 2011 03:15, Tom Lane t...@sss.pgh.pa.us wrote:
 Thom Brown t...@linux.com writes:
 Can I ask why it doesn't return the same snapshot ID each time?
 Surely it can't change since you can only export the snapshot of a
 serializable or repeatable read transaction?

 No, that's incorrect.  You can export from a READ COMMITTED transaction;
 indeed, you'd more or less have to, if you want the control transaction
 to be able to see what the slaves do.

My bad.  I didn't read the documentation carefully enough.  I can make
sense of it now.

Thanks

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: 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] Visual Studio 2010/Windows SDK 7.1 support

2011-10-23 Thread thready
Hi Brar,
Thanks for sharing your work compiling Postgres with VS2010.  I see that
there's a patch file you created to fix the files that need fixing to do so,
but I don't know how to apply this patch.  What's the exe that runs this
patch?  Will it ask me where the source folder root is when I run it? 
Thanks for your help!

Mike

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Visual-Studio-2010-Windows-SDK-7-1-support-tp3325421p4929680.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] [PATCH] Deferrable unique constraints vs join removal -- bug?

2011-10-23 Thread Marti Raudsepp
On Sun, Oct 23, 2011 at 06:44, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 I think maybe what we should do is add
 an immediate field to IndexOptInfo, mirroring the existing unique
 flag, and have get_relation_info() populate it from indimmediate, and
 then make relation_has_unique_index() disqualify any non-immediate
 index.

 Yeah, this seems like the right fix.

Oh, that sounds pretty obvious now that you mention it. :)

I will try to come up with a new patch in a few days (haven't had too
much time lately).

Regards,
Marti

-- 
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] [PATCH] Deferrable unique constraints vs join removal -- bug?

2011-10-23 Thread Tom Lane
Marti Raudsepp ma...@juffo.org writes:
 On Sun, Oct 23, 2011 at 06:44, Tom Lane t...@sss.pgh.pa.us wrote:
 Yeah, this seems like the right fix.

 Oh, that sounds pretty obvious now that you mention it. :)

 I will try to come up with a new patch in a few days (haven't had too
 much time lately).

Oh, I did it already.

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] Visual Studio 2010/Windows SDK 7.1 support

2011-10-23 Thread Brar Piening

thready wrote:
[...]

I don't know how to apply this patch.  What's the exe that runs this
patch?  Will it ask me where the source folder root is when I run it?
As the archives seem to be unreachable at the moment I'm copying text 
from the following message which I would otherwise ask you to read:

http://archives.postgresql.org/message-id/4e2813ec.2050...@gmx.de

CITATION
In short (for the records): download and install msysgit from 
http://code.google.com/p/msysgit/downloads/list

Open git bash and cd into an empty directory like:
cd c:
mkdir pgdev
cd pgdev

Clone the postgresql repository:
git clone git://git.postgresql.org/git/postgresql.git
cd postgresql

Copy the patch (the part with the strange perl-like text  of the 
message) from 
http://archives.postgresql.org/message-id/4e14fd1a.8080...@gmx.de and 
save it to a file (like VS2010v9.patch) which you put into the directory 
which contains your newly created repository (c:\pgdev).


Apply the Patch:
patch -Ec -p 1 -i ../VS2010v9.patch
(which doesn't seem to apply cleanly anymore which will break things for 
you - but I currently have no time to fix it)


Put bison and flex into your path like:
echo \$ENV{PATH}=\$ENV{PATH} . ';C:\Program Files (x86)\Git\bin';  
src/tools/msvc/buildenv.pl

(you might have to adopt the path)

Open a Visual Studio 2010 command prompt and cd into the src/tools/msvc 
directory of your repository like:

cd C:\pgdev\postgresql\src\tools\msvc

Start the build:
build.bat
/CITATION

Please use the commitfest app 
(https://commitfest.postgresql.org/action/patch_view?id=523) to find out 
about the current state of the patch and to get the latest version.


If you have problems applying the patch when copied from the archives 
(v10 - 
http://archives.postgresql.org/message-id/4e837b20.4020...@gmx.de), 
please drop me a line and I'll send you the latest version off list.


Regards,

Brar

--
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] termination of backend waiting for sync rep generates a junk log message

2011-10-23 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Tue, Oct 18, 2011 at 11:27 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 One thing worth asking is why we're willing to violate half a dozen
 different coding rules if we see ProcDiePending, yet we're perfectly
 happy to rely on the client understanding a WARNING for the
 QueryCancelPending case.  Another is whether this whole function isn't
 complete BS in the first place, since it appears to be coded on the
 obviously-false assumption that nothing it calls can throw elog(ERROR)
 --- and of course, if any of those functions do throw ERROR, all the
 argumentation here goes out the window.

 Well, there is a general problem that anything which throws an ERROR
 too late in the commit path is Evil; and sync rep makes that worse to
 the extent that it adds more stuff late in the commit path, but it
 didn't invent the problem.  What it did do is add stuff late in the
 commit path that can block for a potentially unbounded period of time,
 and I don't see that there are any solutions to that problem that
 aren't somewhat grotty.

After further reflection, you're right that all sync rep is really doing
is extending the time duration of the interval wherein clients will have
a hard time telling whether the commit occurred or not.  It's always
been the case that if a cancel/die interrupt occurs during
CommitTransaction, that will get serviced at the RESUME_INTERRUPTS call
at the end, and the client will see an apparent failure even though the
transaction was committed.  Even without that, an interrupt occurring
just after this code sequence, but before we reach the point of sending
a command-complete response message, is going to result in client
confusion, and there's very little we can do about that.

I think what we should do in SyncRepWaitForLSN is just send a warning
and abandon waiting.  Trying to fool with the interrupt response
behavior beyond that is simply broken, and it doesn't help any that we
chose to break it in two different, but equally indefensible, ways for
cancel versus die interrupts.

It would help BTW for the warning to have its own SQLSTATE, if we're
imagining that some clients may be able to interpret it.  Also, this
code is supposing that it must be called within a HOLD_INTERRUPTS
context, but it doesn't look to me like that is being done for the
various calls from twophase.c.

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] So, is COUNT(*) fast now?

2011-10-23 Thread Jeff Janes
On Fri, Oct 21, 2011 at 12:07 PM, Robert Haas robertmh...@gmail.com wrote:
 On Fri, Oct 21, 2011 at 2:33 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 I think HeapTupleSatisfiesMVCC is probably being skipped anyway in
 this case, since all the heap pages should be PD_ALL_VISIBLE.

 Proves my point ;-) ... you're comparing a code path that's been beat on
 for *years* with one that just got written.

 I know.  I wrote a chunk of it.  :-)  My point is just that it'd be
 nice to make it better.

 Anyhow, here's the scoop.  On my desktop machine running F14, running
 SELECT sum(1) FROM pgbench_accounts in a tight loop, 60 s worth of
 oprofile data:

 176830   13.0801  postgres postgres 
 ExecProject

Hi Robert,

count(*) and sum(1) do different things internally, and in my hands
sum(1) is ~10% slower.

I don't know how to dump the output of ExecBuildProjectionInfo into a
human readable form, so I don't know the basis of the difference.  But
I wonder if using count(*) would lower the weight of the ExecProject
function.


Cheers,

Jeff

-- 
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] Hot Backup with rsync fails at pg_clog if under load

2011-10-23 Thread Daniel Farina
On Mon, Oct 17, 2011 at 11:30 PM, Chris Redekop ch...@replicon.com wrote:
 Well, on the other hand maybe there is something wrong with the data.
  Here's the test/steps I just did -
 1. I do the pg_basebackup when the master is under load, hot slave now will
 not start up but warm slave will.
 2. I start a warm slave and let it catch up to current
 3. On the slave I change 'hot_standby=on' and do a 'service postgresql
 restart'
 4. The postgres fails to restart with the same error.
 5. I turn hot_standby back off and postgres starts back up fine as a warm
 slave
 6. I then turn off the load, the slave is all caught up, master and slave
 are both sitting idle
 7. I, again, change 'hot_standby=on' and do a service restart
 8. Again it fails, with the same error, even though there is no longer any
 load.
 9. I repeat this warmstart/hotstart cycle a couple more times until to my
 surprise, instead of failing, it successfully starts up as a hot standby
 (this is after maybe 5 minutes or so of sitting idle)
 So...given that it continued to fail even after the load had been turned of,
 that makes me believe that the data which was copied over was invalid in
 some way.  And when a checkpoint/logrotation/somethingelse occurred when not
 under load it cleared itself upI'm shooting in the dark here
 Anyone have any suggestions/ideas/things to try?

Having digged at this a little -- but not too much -- the problem
seems to be that postgres is reading the commit logs way, way too
early, that is to say, before it has played enough WAL to be
'consistent' (the WAL between pg_start and pg_stop backup).  I have
not been able to reproduce this problem (I think) after the message
from postgres suggesting it has reached a consistent state; at that
time I am able to go into hot-standby mode.

The message is like: consistent recovery state reached at %X/%X.
(this is the errmsg)

It doesn't seem meaningful for StartupCLOG (or, indeed, any of the
hot-standby path functionality) to be called before that code is
executed, but it is anyway right now.  I'm not sure if this oversight
is simply an oversight, or indicative of a misplaced assumption
somewhere.  Basically, my thoughts for a fix are to suppress
hot_standby = on (in spirit) before the consistent recovery state is
reached.

-- 
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] So, is COUNT(*) fast now?

2011-10-23 Thread Tom Lane
Jeff Janes jeff.ja...@gmail.com writes:
 count(*) and sum(1) do different things internally, and in my hands
 sum(1) is ~10% slower.
 I don't know how to dump the output of ExecBuildProjectionInfo into a
 human readable form, so I don't know the basis of the difference.  But
 I wonder if using count(*) would lower the weight of the ExecProject
 function.

Probably.  count() doesn't actually have any arguments, so there's
nothing for ExecProject to do.  sum(1) invokes the generic case there
(ExecTargetList).  I suppose we could add another special-case path for
constant tlist elements, but I suspect that would mostly be optimizing
for benchmarks rather than helping real-world cases.

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] termination of backend waiting for sync rep generates a junk log message

2011-10-23 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 Well, there is a general problem that anything which throws an ERROR
 too late in the commit path is Evil; and sync rep makes that worse to
 the extent that it adds more stuff late in the commit path, but it
 didn't invent the problem.

BTW, it strikes me that if we want to do something about that, it ought
to be possible; but it has to be built into error handling, not a
localized hack for sync rep.

Consider a design along these lines: we invent a global flag that gets
set at some appropriate point in RecordTransactionCommit (probably right
where we exit the commit critical section) and is not cleared until we
send a suitable message to the client --- I think either
command-complete or an error message would qualify, but that would have
to be analyzed more carefully than I've done so far.  If elog.c is told
to send an error message while this flag is set, then it does something
special to inform the client that this was a post-commit error and the
xact is in fact committed.

My inclination for the something special would be to add a new error
message field, but that could be difficult for clients to examine
depending on what sort of driver infrastructure they're dealing with.
You could also imagine emitting a separate NOTICE or WARNING message,
which is analogous to the current hack in SyncRepWaitForLSN, but seems
pretty ugly because it requires clients to re-associate that event with
the later error message.  (But it might be worth doing anyway for human
users, even if we provide a different flag mechanism that is intended
for program consumption.)  Or maybe we could override the SQLSTATE with
some special value.  Or something else.

Given infrastructure like this, it would be reasonable for
SyncRepWaitForLSN to just throw an ERROR if it gets an interrupt,
instead of trying to kluge its own solution.

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] Hot Backup with rsync fails at pg_clog if under load

2011-10-23 Thread Simon Riggs
On Sun, Oct 23, 2011 at 9:48 PM, Daniel Farina dan...@heroku.com wrote:
 On Mon, Oct 17, 2011 at 11:30 PM, Chris Redekop ch...@replicon.com wrote:
 Well, on the other hand maybe there is something wrong with the data.
  Here's the test/steps I just did -
 1. I do the pg_basebackup when the master is under load, hot slave now will
 not start up but warm slave will.
 2. I start a warm slave and let it catch up to current
 3. On the slave I change 'hot_standby=on' and do a 'service postgresql
 restart'
 4. The postgres fails to restart with the same error.
 5. I turn hot_standby back off and postgres starts back up fine as a warm
 slave
 6. I then turn off the load, the slave is all caught up, master and slave
 are both sitting idle
 7. I, again, change 'hot_standby=on' and do a service restart
 8. Again it fails, with the same error, even though there is no longer any
 load.
 9. I repeat this warmstart/hotstart cycle a couple more times until to my
 surprise, instead of failing, it successfully starts up as a hot standby
 (this is after maybe 5 minutes or so of sitting idle)
 So...given that it continued to fail even after the load had been turned of,
 that makes me believe that the data which was copied over was invalid in
 some way.  And when a checkpoint/logrotation/somethingelse occurred when not
 under load it cleared itself upI'm shooting in the dark here
 Anyone have any suggestions/ideas/things to try?

 Having digged at this a little -- but not too much -- the problem
 seems to be that postgres is reading the commit logs way, way too
 early, that is to say, before it has played enough WAL to be
 'consistent' (the WAL between pg_start and pg_stop backup).  I have
 not been able to reproduce this problem (I think) after the message
 from postgres suggesting it has reached a consistent state; at that
 time I am able to go into hot-standby mode.

 The message is like: consistent recovery state reached at %X/%X.
 (this is the errmsg)

 It doesn't seem meaningful for StartupCLOG (or, indeed, any of the
 hot-standby path functionality) to be called before that code is
 executed, but it is anyway right now.  I'm not sure if this oversight
 is simply an oversight, or indicative of a misplaced assumption
 somewhere.  Basically, my thoughts for a fix are to suppress
 hot_standby = on (in spirit) before the consistent recovery state is
 reached.

Not sure about that, but I'll look at where this comes from.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services

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


[HACKERS] Index only scans and visibilitymap.c

2011-10-23 Thread Jeff Janes
With index only scans, the comments in
src/backend/access/heap/visibilitymap.c are probably out of date,
starting with:

Currently, the visibility map is only used as a hint


Also, is there a discussion of how and why index-only scans is safe?
i.e. what lock, if any, has to be held while nodeIndexonlyscan.c
calls  visibilitymap_test?  Do index-only scans need a README file?

Thanks,

Jeff

-- 
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] So, is COUNT(*) fast now?

2011-10-23 Thread Jeff Janes
On Fri, Oct 21, 2011 at 12:52 PM, Robert Haas robertmh...@gmail.com wrote:

 Also, this line is kind of expensive:

        if (!visibilitymap_test(scandesc-heapRelation,
                                ItemPointerGetBlockNumber(tid),
                                node-ioss_VMBuffer))

 Around 2%.  But I don't see any way to avoid that, or even make it cheaper.

Could we cache by ItemPointerGetBlockNumber(tid) the results of those
tests, for groups of tids on the same index page?

How useful this would be would depend on how well-clustered the table
and index are.


Cheers,

Jeff

-- 
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] EXECUTE tab completion

2011-10-23 Thread Tom Lane
Andreas Karlsson andr...@proxel.se writes:
 Thanks for cleaning up the code to some sanity, I should have done so 
 myself when I noticed the problem.

 A new version is attached.

Committed with minor adjustments --- I didn't see any need to make this
wait for the next commitfest.

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] Hot Backup with rsync fails at pg_clog if under load

2011-10-23 Thread Simon Riggs
On Sun, Oct 16, 2011 at 2:33 AM, Chris Redekop ch...@replicon.com wrote:

 pg_subtrans: http://pastebin.com/qAXEHAQt

I confirm this as a HS issue and will investigate from here.

FATAL:  could not access status of transaction 21110784
which, in pg_subtrans, is the first xid on a new subtrans page. So we
have missed zeroing a page.

pg_control shows ... Latest checkpoint's oldestActiveXID:  2111
which shows quite clearly that the pg_control file is later than it should be.

Chris, can you rearrange the backup so you copy the pg_control file as
the first act after the pg_start_backup?

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services

-- 
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] Hot Backup with rsync fails at pg_clog if under load

2011-10-23 Thread Simon Riggs
On Sun, Oct 23, 2011 at 9:48 PM, Daniel Farina dan...@heroku.com wrote:

 Having digged at this a little -- but not too much -- the problem
 seems to be that postgres is reading the commit logs way, way too
 early, that is to say, before it has played enough WAL to be
 'consistent' (the WAL between pg_start and pg_stop backup).  I have
 not been able to reproduce this problem (I think) after the message
 from postgres suggesting it has reached a consistent state; at that
 time I am able to go into hot-standby mode.

The WAL appears too early because the other control info is later than
it should be.

So this is approx backwards and nothing related to consistent state,
but thanks for drawing my attention to this.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services

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


[HACKERS] autovacuum and orphaned large objects

2011-10-23 Thread Euler Taveira de Oliveira

Hi,

The main point of autovacuum is maintenance tasks. Currently, it executes 
VACUUM and ANALYZE commands. I want to propose that we incorporate vacuumlo 
functionality into it. While dealing with large objects (LO), we have lo 
contrib module that helps with LO maintenance but has some limitations (does 
not handle DROP TABLE and TRUNCATE cases) and vacuumlo that does an excellent 
job but have to be executed outside DBMS. The proposal is to clean up LO when 
autovacuum triggers VACUUM; cleanup LO routine will starts after(?) VACUUM 
command.


In a near future I want to propose that orphaned LO be cleaned up by VACUUM 
but that a history for another thread...


Comments?


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