Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Valentine Gogichashvili
On Sun, Oct 30, 2011 at 22:12, Eric Ridge eeb...@gmail.com wrote:


 Yes.  It's basically a modifier to the star that immediately precedes
 it.  In order to support excluding multiple columns, it needs parens:
 SELECT foo.* EXCLUDING (foo.x, foo.y), bar.* EXCLUDING (bar.y),
 baz.z, (a+b) AS c

 but yes, that's what I'm thinking.  I think doing this will require
 more changes to the grammar than I had first thought because there'd
 be no point in supporting:
 SELECT foo.* EXCLUDING (foo.* EXCLUDING foo.y) FROM ...
 It looks like the above would be implicitly allowed without a bit of extra
 work.

 But, if you've got a complex query consisting of a few joins, it'd be
 nice to say:
 SELECT * EXCLUDING (table1.*, table2.x) FROM table1 INNER JOIN table2
 ...


Putting aside arguments like it is not a good idea to use * because it
generates not sustainable code especially in case when you extend table
structure,  I think this construct would be really nice for building ROWs,
for example in plpgsql triggers or in conditions for big update statements:

IF (NEW.* EXCLUDING ( last_modified ) ) IS DISTINCT FROM (OLD.* EXCLUDING (
last_modified ) ) THEN NEW.last_modified = clock_timestamp() ; END IF

by now, I do not know any really nice syntax to do that efficiently, and
for some wide tables, building this kind of structures listing all the
fields, that you have there is completely stupid and makes code unreadable.

So I would definitely like to have a syntax, that you are suggesting in
case it would give a possibility to construct ROWs (RECORDs).

Regards,

-- Valentine Gogichashvili


Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-11-02 Thread Simon Riggs
On Wed, Nov 2, 2011 at 2:40 AM, Chris Redekop ch...@replicon.com wrote:

 looks like the v3 patch re-introduces the pg_subtrans issue...

No, I just separated the patches to be clearer about the individual changes.

-- 
 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] IDLE in transaction introspection

2011-11-02 Thread Albe Laurenz
Andrew Dunstan wrote:
 On 11/01/2011 09:52 AM, Tom Lane wrote:
 I'm for just redefining the query field as current or last
 query.

 +1
 
 I could go either way on whether to rename it.
 
 Rename it please. current_query will just be wrong. I'd be inclined
 just to call it query or query_string and leave it to the docs to
 define the exact semantics.

+1 for renaming, +1 for a state column.
I think it is overkill to keep a query history beyond that -- if you
want that,
you can resort to the log files.

Yours,
Laurenz Albe

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


[HACKERS] Refactor xlog.c #1 - startup.c

2011-11-02 Thread Simon Riggs
Patch removes stuff related to startup process and creates files
under src/$DIR/postmaster for this code.

This makes startup process look more like bgwriter, walwriter etc..

Other refactoring patches to follow, chipping away at xlog.c.

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


startup_refactor.v1.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] Hot Standby startup with overflowed snapshots

2011-11-02 Thread Simon Riggs
On Fri, Oct 28, 2011 at 3:42 AM, Chris Redekop ch...@replicon.com wrote:

 On a side note I am sporadically seeing another error on hotstandby startup.
  I'm not terribly concerned about it as it is pretty rare and it will work
 on a retry so it's not a big deal.  The error is FATAL:  out-of-order XID
 insertion in KnownAssignedXids.  If you think it might be a bug and are
 interested in hunting it down let me know and I'll help any way I can...but
 if you're not too worried about it then neither am I :)

I'd be interested to see further details of this if you see it again,
or have access to previous logs.

-- 
 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] unite recovery.conf and postgresql.conf

2011-11-02 Thread Simon Riggs
On Wed, Nov 2, 2011 at 1:45 AM, Robert Haas robertmh...@gmail.com wrote:

 I think that might have some possibilities.  But how does that work in
 detail?

My thoughts also. I want to see the detail on an alternate proposal so
we can decide things sensibly.

-- 
 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] unite recovery.conf and postgresql.conf

2011-11-02 Thread Simon Riggs
On Wed, Nov 2, 2011 at 1:11 AM, Josh Berkus j...@agliodbs.com wrote:

 There is no way we're getting distro packagers to switch from pg_ctl
 start.  Also, a lot of distros use the postgres command rather than
 pg_ctl anything.

So backwards compatibility is important for downstream software.

-- 
 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] unite recovery.conf and postgresql.conf

2011-11-02 Thread Simon Riggs
On Wed, Nov 2, 2011 at 1:11 AM, Josh Berkus j...@agliodbs.com wrote:

 There is no way we're getting distro packagers to switch from pg_ctl
 start.  Also, a lot of distros use the postgres command rather than
 pg_ctl anything.

So backwards compatibility is important for downstream software.

-- 
 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-11-02 Thread Simon Riggs
On Wed, Nov 2, 2011 at 7:34 AM, Simon Riggs si...@2ndquadrant.com wrote:
 On Wed, Nov 2, 2011 at 2:40 AM, Chris Redekop ch...@replicon.com wrote:

 looks like the v3 patch re-introduces the pg_subtrans issue...

 No, I just separated the patches to be clearer about the individual changes.

3 bug fixes committed and back patched.

-- 
 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] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Stephen Frost
* Pavel Stehule (pavel.steh...@gmail.com) wrote:
 I understand it, it is really bad idea use a star in export queries

It's not how I'd want to automate things, but I hardly see it being a
really bad idea for ad-hoc COPY usage..

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Refactor xlog.c #1 - startup.c

2011-11-02 Thread Robert Haas
On Wed, Nov 2, 2011 at 4:44 AM, Simon Riggs si...@2ndquadrant.com wrote:
 Patch removes stuff related to startup process and creates files
 under src/$DIR/postmaster for this code.

 This makes startup process look more like bgwriter, walwriter etc..

In general, +1.

But I don't think we want duplicate function prototypes for the same
functions, so -1 for this hunk:

+ /* in xlog.c */
+ extern void WakeupRecovery(void);
+ extern void StartupXLOG(void);

The existing prototypes in xlog.h seem like they should be just fine.

IsPromoteTriggered could be simplified to just return
promote_triggered;; you don't need to branch.

I think one thing that would be worth considering in terms of future
refactoring is to split up the gigantic StartupXLOG() function in some
fashion.

-- 
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] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Andrew Dunstan



On 11/02/2011 03:16 AM, Valentine Gogichashvili wrote:


Putting aside arguments like it is not a good idea to use * because 
it generates not sustainable code especially in case when you extend 
table structure,  I think this construct would be really nice for 
building ROWs, for example in plpgsql triggers or in conditions for 
big update statements:


IF (NEW.* EXCLUDING ( last_modified ) ) IS DISTINCT FROM (OLD.* 
EXCLUDING ( last_modified ) ) THEN NEW.last_modified = 
clock_timestamp() ; END IF





That's a very good use case. I could certainly have used this in the past.

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] Refactor xlog.c #1 - startup.c

2011-11-02 Thread Simon Riggs
On Wed, Nov 2, 2011 at 1:14 PM, Robert Haas robertmh...@gmail.com wrote:
 On Wed, Nov 2, 2011 at 4:44 AM, Simon Riggs si...@2ndquadrant.com wrote:
 Patch removes stuff related to startup process and creates files
 under src/$DIR/postmaster for this code.

 This makes startup process look more like bgwriter, walwriter etc..

 In general, +1.

Thanks for the review.

 But I don't think we want duplicate function prototypes for the same
 functions, so -1 for this hunk:

 + /* in xlog.c */
 + extern void WakeupRecovery(void);
 + extern void StartupXLOG(void);

 The existing prototypes in xlog.h seem like they should be just fine.

That was to avoid having startup.c include xlog.h, which it doesn't
really need to see.

Not worried either way, so I'll do as you suggest.

 IsPromoteTriggered could be simplified to just return
 promote_triggered;; you don't need to branch.

Happy to do that.

 I think one thing that would be worth considering in terms of future
 refactoring is to split up the gigantic StartupXLOG() function in some
 fashion.

There are a great many other long functions in Postgres, and few of
them as critical as that one.

I don't see a reason to touch it, and would do so last of all of any
code in xlog.c

-- 
 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] Refactor xlog.c #1 - startup.c

2011-11-02 Thread Magnus Hagander
On Wed, Nov 2, 2011 at 14:33, Simon Riggs si...@2ndquadrant.com wrote:
 On Wed, Nov 2, 2011 at 1:14 PM, Robert Haas robertmh...@gmail.com wrote:
 On Wed, Nov 2, 2011 at 4:44 AM, Simon Riggs si...@2ndquadrant.com wrote:
 Patch removes stuff related to startup process and creates files
 under src/$DIR/postmaster for this code.

 This makes startup process look more like bgwriter, walwriter etc..

 In general, +1.

 Thanks for the review.

 But I don't think we want duplicate function prototypes for the same
 functions, so -1 for this hunk:

 + /* in xlog.c */
 + extern void WakeupRecovery(void);
 + extern void StartupXLOG(void);

 The existing prototypes in xlog.h seem like they should be just fine.

 That was to avoid having startup.c include xlog.h, which it doesn't
 really need to see.

Double prototypes seems to me like it's a cure worse than the disease to me...

 Not worried either way, so I'll do as you suggest.

+1.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes:
 On 11/02/2011 03:16 AM, Valentine Gogichashvili wrote:
 Putting aside arguments like it is not a good idea to use * because 
 it generates not sustainable code especially in case when you extend 
 table structure,  I think this construct would be really nice for 
 building ROWs, for example in plpgsql triggers or in conditions for 
 big update statements:
 
 IF (NEW.* EXCLUDING ( last_modified ) ) IS DISTINCT FROM (OLD.* 
 EXCLUDING ( last_modified ) ) THEN NEW.last_modified = 
 clock_timestamp() ; END IF

 That's a very good use case. I could certainly have used this in the past.

Well ... this is inventing use cases that have nothing to do with the
proposed feature and are entirely incapable of being supported by the
proposed implementation.  And I'm not sure why we should put aside the
argument that this is only a good idea in ad-hoc queries, 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] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Andrew Dunstan



On 11/02/2011 09:56 AM, Tom Lane wrote:

Andrew Dunstanand...@dunslane.net  writes:

On 11/02/2011 03:16 AM, Valentine Gogichashvili wrote:

Putting aside arguments like it is not a good idea to use * because
it generates not sustainable code especially in case when you extend
table structure,  I think this construct would be really nice for
building ROWs, for example in plpgsql triggers or in conditions for
big update statements:

IF (NEW.* EXCLUDING ( last_modified ) ) IS DISTINCT FROM (OLD.*
EXCLUDING ( last_modified ) ) THEN NEW.last_modified =
clock_timestamp() ; END IF

That's a very good use case. I could certainly have used this in the past.

Well ... this is inventing use cases that have nothing to do with the
proposed feature and are entirely incapable of being supported by the
proposed implementation.  And I'm not sure why we should put aside the
argument that this is only a good idea in ad-hoc queries, either.




Well, yes, you're right that it's not covered by the original feature. I 
guess I got interested because a couple of years ago I had to write some 
triggers in PLPerl and with much lower efficiency to achieve the same 
effect as this.


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] Refactor xlog.c #1 - startup.c

2011-11-02 Thread Simon Riggs
On Wed, Nov 2, 2011 at 1:44 PM, Magnus Hagander mag...@hagander.net wrote:

 Double prototypes seems to me like it's a cure worse than the disease to me...

Doubling them was just a mistake. Anyway, thats gone now.

-- 
 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] pg_upgrade if 'postgres' database is dropped

2011-11-02 Thread Robert Haas
On Sat, Oct 29, 2011 at 4:07 PM, Robert Haas robertmh...@gmail.com wrote:
 On Fri, Oct 28, 2011 at 9:22 PM, Bruce Momjian br...@momjian.us wrote:
 OK, the attached, applied patch removes the pg_upgrade dependency on the
 'postgres' database existing in the new cluster.  However, vacuumdb,
 used by pg_upgrade, still has this dependency:

            conn = connectDatabase(postgres, host, port, username,
                prompt_password, progname);

 In fact, all the /scripts binaries use the postgres database, except for
 createdb/dropdb, which has this heuristic:

    /*
     * Connect to the 'postgres' database by default, except have the
     * 'postgres' user use 'template1' so he can create the 'postgres'
     * database.
     */
    conn = connectDatabase(strcmp(dbname, postgres) == 0 ? template1 : 
 postgres,
                           host, port, username, prompt_password, progname);

 This makes sense because you might be creating or dropping the postgres
 database.  Do we want these to have smarter database selection code?

 Well, I suppose as long as we're cleaning this up, we might as well be
 thorough, so, sure, why not?  I think the algorithm pg_dumpall uses is
 pretty sensible: let the user specify the database to use if they so
 desire; if not, try postgres first and then template1.  I think we
 could stick some logic for that in common.c which could be shared by
 clusterdb, createdb, dropdb, dropuser, reindexdb, and vacuumdb.

 However, we need to rethink the flag to be used for this: pg_dumpall
 uses -l, but many of the other utilities already use that for some
 other purpose, and it's not exactly mnemonic anyway.  -d for
 database could work, but that's also in use in some places, and
 furthermore somewhat confusing since many if not all of these
 utilities have an option to operate on a single database only, and you
 might think that -d would specify the database to operate on, rather
 than the one to be used to get the list of databases.  pgAdmin uses
 the term maintenance database to refer to a database to be used when
 none is explicitly specified, and I think that's fairly clear
 terminology.  So I propose that we add a --maintenance-db option (with
 no short form, since this is a relatively obscure need) to the tools
 listed above.  The tools will pass the associated value (or NULL if
 the option is not specified) to the above-mentioned routine in
 common.c, which will do the rest.

 If nobody objects, I'll go do that.  Hopefully that should be enough
 to put this problem to bed more or less permanently.

All right, I've worked up a (rather boring and tedious) patch to do
this, which is attached.

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


maintenance-db.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] Hot Standby startup with overflowed snapshots

2011-11-02 Thread Chris Redekop
oopsreply-to-all

-- Forwarded message --
From: Chris Redekop ch...@replicon.com
Date: Wed, Nov 2, 2011 at 8:41 AM
Subject: Re: [HACKERS] Hot Standby startup with overflowed snapshots
To: Simon Riggs si...@2ndquadrant.com


Sure, I've got quite a few logs lying around - I've attached 3 of 'em...let
me know if there are any specific things you'd like me to do or look for
next time it happens


On Wed, Nov 2, 2011 at 2:59 AM, Simon Riggs si...@2ndquadrant.com wrote:

 On Fri, Oct 28, 2011 at 3:42 AM, Chris Redekop ch...@replicon.com wrote:

  On a side note I am sporadically seeing another error on hotstandby
 startup.
   I'm not terribly concerned about it as it is pretty rare and it will
 work
  on a retry so it's not a big deal.  The error is FATAL:  out-of-order
 XID
  insertion in KnownAssignedXids.  If you think it might be a bug and are
  interested in hunting it down let me know and I'll help any way I
 can...but
  if you're not too worried about it then neither am I :)

 I'd be interested to see further details of this if you see it again,
 or have access to previous logs.

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



postgresql-2011-10-27_202007.log
Description: Binary data


postgresql-2011-10-31_152925.log
Description: Binary data


postgresql-2011-11-01_094501.log
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


[HACKERS] Compile file_fdw with Mingw Developer Studio

2011-11-02 Thread pasman pasmański
Hi.

Is possible to use Mingw Developer Studio to
compile parts of postgres (file_fdw) ?

Anybody tried it ?



pasman

-- 
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] removing =(text, text) in 9.2

2011-11-02 Thread Robert Haas
On Wed, Nov 2, 2011 at 12:36 AM, David E. Wheeler da...@kineticode.com wrote:
 On Nov 1, 2011, at 11:19 AM, Robert Haas wrote:
 Fair enough.

 So, I tried to work up a patch for this, but I'm actually a bit
 confused about what needs to be done here.  I'll attach what I've got
 so far as a starting point for discussion.

 Looks reasonable, if verbose. (Yes, the extension design currently requires 
 that). What about doc updates?

Hmm, I was kind of expecting that to be wrong at least in some minor way.

Here's a new version with documentation updates.

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


hstore-drop-arrow-v2.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] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Eric Ridge
On Tue, Nov 1, 2011 at 11:47 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Doesn't mean anything, I think --- the SQL standard seems to exclude it.
 It's fairly hard to prevent it at the grammar level, since we regard
 foo.* as a type of primitive expression, but I suspect it might be a
 good idea for transformTargetList to throw an error instead of silently
 ignoring the column label.

Let me take a stab at fixing it in the grammar... if it's even do-able.

If it can be fixed at the grammar, it'll get me a lot closer to doing
what I want to do with the actual feature.

eric

-- 
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-11-02 Thread Marti Raudsepp
On Sun, Oct 23, 2011 at 21:39, Tom Lane t...@sss.pgh.pa.us wrote:
 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.

Cool.

I noticed now that you didn't add a regression test for this fix.
Perhaps you could reuse the test from my patch, which also tests for
plan invalidation.

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] Core Extensions relocation

2011-11-02 Thread Greg Smith

On 10/14/2011 01:48 PM, Bruce Momjian wrote:

Is this going to be done for 9.2?
   


Refreshing this patch is on my list of things to finish before the next 
CommitFest starts later this month.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us


--
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-11-02 Thread Chris Redekop
okay, sorry I'm a little confused then.  Should I be able to apply both the
v2 patch as well as the v3 patch?  or is it expected that I'd have to
manually do the merge?


On Wed, Nov 2, 2011 at 1:34 AM, Simon Riggs si...@2ndquadrant.com wrote:

 On Wed, Nov 2, 2011 at 2:40 AM, Chris Redekop ch...@replicon.com wrote:

  looks like the v3 patch re-introduces the pg_subtrans issue...

 No, I just separated the patches to be clearer about the individual
 changes.

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



[HACKERS] DeArchiver process

2011-11-02 Thread Simon Riggs
Currently, the Startup process is responsible for running
restore_command. So when the Startup process is busy or waiting, then
no new WAL files arrive.

That has these effects
* Recovery must wait while the Startup process requests next WAL file.
This reduces performance of archive recovery.
* If replication is file-based then no new files can be downloaded
while we are waiting. If the Startup process waits, it then is much
slower to catch up than it could be if it had already downloaded the
files from the archive.
* We cannot run an archive_cleanup_command, so the archive keep growing.
* Cascading from a standby that uses file based replication is not
easily possible

My solution is to create a new process called the DeArchiver. This
will run restore_command in a tight loop until the number of files
would exceed wal_keep_files, then sleep. Each time the DeArchiver
executes restore_command it will set the return code and if rc=0 the
new XLogRecPtr reached. If standby_mode = on it will continue to retry
indefinitely.

The Startup process will just read files from pg_xlog rather than from
the archive, just as it does for streaming, so this will remove the
special case code in xlog.c. (WALReciver and this process will still
need to coordinate so they are not both simultaneously active at any
point, as now).

This proposal gives a performance gain because the DeArchiver can be
restoring the next file while the Startup process is processing the
current file, so they work together using pipeline parallelism.

The DeArchiver would start when we are not in crash recovery and exit
at the end of recovery. This would then allow restore_command to be
set via reload rather than restart.

Previously, we have given greater weight to files from the archive to
files already in pg_xlog. To ensure that behaviour continues, if
restore_command is set at the Startup process will read the files in
the pg_xlog directory and remember which ones were there at startup.
That way it will be able to tell the difference between files newly
downloaded and those already in the directory. If a file is absent
from the archive we will use the file from pg_xlog.

This makes file-based and stream-based replication work in a similar
way, which is neater, and it also means all required files are
available in case of a crash, which means we can more easily get rid
of shutdown checkpoints in case of failoiver (discussed on separate
thread).

Since more files are available, it allows cascading replication to
have a sender which receives WAL data in files.

Which do we prefer DeArchiver, Restore process, or WALFileReceiver.

Thoughts?

-- 
 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] [v9.2] Fix Leaky View Problem

2011-11-02 Thread Robert Haas
On Wed, Nov 2, 2011 at 7:34 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote:
 [ new patch, with example query plans ]

I like the look of those query plans.

Redefining the RangeTblEntry's relid field to be valid for either a
table or a subquery that originated from a view seems problematic to
me, though.  For one thing, it's hard to say how much other code
assumes that field to be valid only for a table.  For example, you
didn't update _readRangeTblEntry(), and I wouldn't bet on that being
the only place that needs fixing.  For another thing, instead of
changing the meaning of the relid field, you could just leave that
alone and instead add a bool security_barrier field that caches the
answer; ApplyRetrieveRule() has the Relation object and could set that
field appropriately, and then subquery_was_security_barrier() wouldn't
need a syscache lookup.

Now, the obvious objection is that the security-barrier attribute
might change between the time the RTE is created and the time that it
gets used.  But if that's a danger, then presumably the whole view
could also change, in which case the Query object would be pointing to
the wrong data anyway.  I'm not sure I fully understand the details
here, but it seems like it ought to be safe to cache the
security_barrier attribute any place it's safe to cache the Query
itself.  It certainly doesn't seem right to think that we might end up
using a new value of the security_barrier attribute with an old query,
or the other way around.  So something seems funky here.

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

2011-11-02 Thread Robert Haas
On Wed, Nov 2, 2011 at 11:56 AM, Simon Riggs si...@2ndquadrant.com wrote:
 My solution is to create a new process called the DeArchiver. This
 will run restore_command in a tight loop until the number of files
 would exceed wal_keep_files, then sleep. Each time the DeArchiver
 executes restore_command it will set the return code and if rc=0 the
 new XLogRecPtr reached. If standby_mode = on it will continue to retry
 indefinitely.

Are you thinking of reusing the existing GUC wal_keep_segments (not
wal_keep_files), or creating a new one?  I'd suggest creating a new
one, so as to avoid having a GUC that does one thing on the master and
something quite different on the slave.

 Which do we prefer DeArchiver, Restore process, or WALFileReceiver.

My personal preference would be restore process, since we already use
the name restore_command.

 Thoughts?

+1.  Great 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] DeArchiver process

2011-11-02 Thread Dimitri Fontaine
Simon Riggs si...@2ndquadrant.com writes:
 My solution is to create a new process called the DeArchiver. This
 will run restore_command in a tight loop until the number of files
 would exceed wal_keep_files, then sleep. Each time the DeArchiver
 executes restore_command it will set the return code and if rc=0 the
 new XLogRecPtr reached. If standby_mode = on it will continue to retry
 indefinitely.

+1

I think you mean wal_keep_segments, a GUC that we already have.

 Which do we prefer DeArchiver, Restore process, or WALFileReceiver.

The only part of your proposal that I don't like is the process name,
that deArchiver thing.  wal restore process or something like that
would be better.  We already have wal writer process and wal sender
process and wal receiver process.

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


[HACKERS] heap_page_prune comments

2011-11-02 Thread Robert Haas
The following comment - or at least the last sentence thereof -
appears to be out of date.

/*
 * XXX Should we update the FSM information of this page ?
 *
 * There are two schools of thought here. We may not want to update FSM
 * information so that the page is not used for unrelated
UPDATEs/INSERTs
 * and any free space in this page will remain available for further
 * UPDATEs in *this* page, thus improving chances for doing HOT updates.
 *
 * But for a large table and where a page does not receive
further UPDATEs
 * for a long time, we might waste this space by not updating the FSM
 * information. The relation may get extended and fragmented further.
 *
 * One possibility is to leave fillfactor worth of space in this page
 * and update FSM with the remaining space.
 *
 * In any case, the current FSM implementation doesn't accept
 * one-page-at-a-time updates, so this is all academic for now.
 */

The simple fix here is just to delete that last sentence, but does
anyone think we ought to do change the behavior, now that we have the
option to do so?

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

2011-11-02 Thread Simon Riggs
On Wed, Nov 2, 2011 at 4:14 PM, Robert Haas robertmh...@gmail.com wrote:
 On Wed, Nov 2, 2011 at 11:56 AM, Simon Riggs si...@2ndquadrant.com wrote:
 My solution is to create a new process called the DeArchiver. This
 will run restore_command in a tight loop until the number of files
 would exceed wal_keep_files, then sleep. Each time the DeArchiver
 executes restore_command it will set the return code and if rc=0 the
 new XLogRecPtr reached. If standby_mode = on it will continue to retry
 indefinitely.

 Are you thinking of reusing the existing GUC wal_keep_segments (not
 wal_keep_files), or creating a new one?  I'd suggest creating a new
 one, so as to avoid having a GUC that does one thing on the master and
 something quite different on the slave.

Any standby can now become a sender node, so the meaning in that case
would be the same. That takes a little time to get your head around,
and I'm not used to it myself yet.

I guess you might say that you know for certain that a node will never
be a relay node, and so a different meaning is possible, but it seems
easier to avoid adding a new parameter just for that case.


 Which do we prefer DeArchiver, Restore process, or WALFileReceiver.

 My personal preference would be restore process, since we already use
 the name restore_command.

Restore process, with file called restore.c in src/backend/postmaster
(or src/backend/replication?)

We can change that again later, but its enough to be getting on with.


 Thoughts?

 +1.  Great idea.

Thanks.


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

2011-11-02 Thread Robert Haas
On Wed, Nov 2, 2011 at 12:42 PM, Simon Riggs si...@2ndquadrant.com wrote:
 Any standby can now become a sender node, so the meaning in that case
 would be the same. That takes a little time to get your head around,
 and I'm not used to it myself yet.

I think a new parameter will be more clear, even if in practice the
difference is fairly thin.

 Which do we prefer DeArchiver, Restore process, or WALFileReceiver.

 My personal preference would be restore process, since we already use
 the name restore_command.

 Restore process, with file called restore.c in src/backend/postmaster
 (or src/backend/replication?)

Yeah, that works.  I'd go for postmaster over replication, for parallelism.

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

2011-11-02 Thread Tom Lane
Dimitri Fontaine dimi...@2ndquadrant.fr writes:
 The only part of your proposal that I don't like is the process name,
 that deArchiver thing.  wal restore process or something like that
 would be better.  We already have wal writer process and wal sender
 process and wal receiver process.

+1, restore seems pretty vague in this context.

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

2011-11-02 Thread Simon Riggs
On Wed, Nov 2, 2011 at 5:20 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Dimitri Fontaine dimi...@2ndquadrant.fr writes:
 The only part of your proposal that I don't like is the process name,
 that deArchiver thing.  wal restore process or something like that
 would be better.  We already have wal writer process and wal sender
 process and wal receiver process.

 +1, restore seems pretty vague in this context.

Yeh, walrestore seems more natural than just restore.

-- 
 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] Core Extensions relocation

2011-11-02 Thread Josh Berkus
On 11/2/11 8:25 AM, Greg Smith wrote:
 On 10/14/2011 01:48 PM, Bruce Momjian wrote:
 Is this going to be done for 9.2?

 
 Refreshing this patch is on my list of things to finish before the next
 CommitFest starts later this month.

Put me down as reviewer.


-- 
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] [v9.2] Fix Leaky View Problem

2011-11-02 Thread Kohei KaiGai
2011/11/2 Robert Haas robertmh...@gmail.com:
 On Wed, Nov 2, 2011 at 7:34 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote:
 [ new patch, with example query plans ]

 I like the look of those query plans.

 Redefining the RangeTblEntry's relid field to be valid for either a
 table or a subquery that originated from a view seems problematic to
 me, though.  For one thing, it's hard to say how much other code
 assumes that field to be valid only for a table.  For example, you
 didn't update _readRangeTblEntry(), and I wouldn't bet on that being
 the only place that needs fixing.  For another thing, instead of
 changing the meaning of the relid field, you could just leave that
 alone and instead add a bool security_barrier field that caches the
 answer; ApplyRetrieveRule() has the Relation object and could set that
 field appropriately, and then subquery_was_security_barrier() wouldn't
 need a syscache lookup.

 Now, the obvious objection is that the security-barrier attribute
 might change between the time the RTE is created and the time that it
 gets used.  But if that's a danger, then presumably the whole view
 could also change, in which case the Query object would be pointing to
 the wrong data anyway.  I'm not sure I fully understand the details
 here, but it seems like it ought to be safe to cache the
 security_barrier attribute any place it's safe to cache the Query
 itself.  It certainly doesn't seem right to think that we might end up
 using a new value of the security_barrier attribute with an old query,
 or the other way around.  So something seems funky here.

The reason why I redefined the relid of RangeTblEntry is to avoid
the problem when security_barrier attribute get changed by concurrent
transactions between rewriter and planenr stage.

Of course, I'm not 100% sure whether we have a routine that assumes
valid relid of RangeTblEntry is regular table, or not, although we could
run the regression test correctly.

As I examined before, updates of the issued pg_class shall invalidate
prepared statements that assumed a particular security_barrier
(maybe, PlanCacheRelCallback does this work?), so it is unavailable
to use old plans based on old view definition.
If we want to avoid syscache lookup on subquery_was_security_barrier(),
I think it is a feasible idea to hold the value of security_barrier within RTE.

Thanks,
-- 
KaiGai Kohei kai...@kaigai.gr.jp

-- 
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_upgrade if 'postgres' database is dropped

2011-11-02 Thread Bruce Momjian
Robert Haas wrote:
  However, we need to rethink the flag to be used for this: pg_dumpall
  uses -l, but many of the other utilities already use that for some
  other purpose, and it's not exactly mnemonic anyway. ?-d for
  database could work, but that's also in use in some places, and
  furthermore somewhat confusing since many if not all of these
  utilities have an option to operate on a single database only, and you
  might think that -d would specify the database to operate on, rather
  than the one to be used to get the list of databases. ?pgAdmin uses
  the term maintenance database to refer to a database to be used when
  none is explicitly specified, and I think that's fairly clear
  terminology. ?So I propose that we add a --maintenance-db option (with
  no short form, since this is a relatively obscure need) to the tools
  listed above. ?The tools will pass the associated value (or NULL if
  the option is not specified) to the above-mentioned routine in
  common.c, which will do the rest.
 
  If nobody objects, I'll go do that. ?Hopefully that should be enough
  to put this problem to bed more or less permanently.
 
 All right, I've worked up a (rather boring and tedious) patch to do
 this, which is attached.

I wonder if we should bother using a flag for this.  No one has asked
for one, and the new code to conditionally connect to databases should
function fine for most use cases.

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

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

-- 
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] Single pass vacuum - take 2

2011-11-02 Thread Robert Haas
On Fri, Sep 23, 2011 at 12:37 PM, Robert Haas robertmh...@gmail.com wrote:
 I'm still not really comfortable with the handling of vacuum
 generation numbers.

Pavan and I spent a bit of time today talking about how many vacuum
generation numbers we need to have in order for this scheme to work.
Before my memory fades, here are some notes:

- In an ideal world, we'd only need two vacuum generation numbers.
Call them 1 and 2.  We store the vacuum generation number of the last
successful vacuum somewhere.  When the next vacuum starts, any
dead-vacuumed line pointers stamped with the generation number of the
last successful vacuum get marked unused.  The new vacuum uses the
other generation number, stamping any new dead line pointers with that
generation and eventually, after the index vacuum is successfully
completed, storing that value as the last successful vacuum generation
number.  The next vacuum will repeat the whole cycle with the roles of
the two available vacuum generation numbers reversed.  If a vacuum
fails midway through, the last successful vacuum generation number
doesn't get updated; the next vacuum will reuse the same vacuum
generation number, which should be fine.

- However, making this work with HOT pruning is a bit stickier.  If
the last successful vacuum generation number is stored in pg_class and
heap_page_prune() looks at the relcache entry to get it, the value
there might be out of date.  If a HOT pruning operation sees that the
last successful vacuum generation was X, but in the meantime a new
vacuum has started that also uses generation number X, then the HOT
prune might mark a dead line pointer as unused while there are still
index entries pointing to it, which would be bad.  So here, there's
value to having a large number of vacuum generations rather than just
two.

- In particular, assuming we store the vacuum generation number in
pg_class, we'd like to have enough vacuum generation numbers that the
counter can't wrap around while there's still an old relcache entry
lying around.  2^31 seems like enough, because each vacuum consumes an
XID (but will that necessarily always be the case?) and if you've
eaten through 2^31 XIDs then any still-running transaction would be
suffering from wraparound problems anyway (but what if it's read-only
and keeps taking new snapshots without ever rebuilding the relcache
entry?  can that happen?).  However, if we store the vacuum generation
in the line pointer, we only have 30 bits available.

- There's also a problem with having just two vacuum generation
numbers if someone does a transactional update to pg_class, even if
they don't touch the hypothetical field that stores the generation
number:

rhaas=# begin;
BEGIN
rhaas=# update pg_class set relname=relname where oid='test'::regclass;
UPDATE 1

Then, in another session:
rhaas=# vacuum test;
VACUUM

VACUUM is perfectly happy to do a non-transactional update on the
then-current version of the pg_class tuple even while an open
transaction has a pending update to that tuple that might get
committed just afterward.  We can't risk getting confused about the
current vacuum generation number.  Well, OK, technically we can: if
there are an infinite number of vacuum generation numbers available,
then the worst thing that happens is we forget that a bunch of dead
line pointers are reclaimable, and do a bunch of extra work that isn't
really necessary.  But if there are just two, we're now going to get
confused about which line pointers can be safely reclaimed.

...

So, what do we do?  Possible solutions appear to include:

- Find some more bit space, so that we can make the vacuum generation
number wider.
- Store the vacuum generation number someplace other than a system
catalog, where the effects that can make us see a stale value or lose
an update don't exist.
- Don't let HOT pruning reclaim dead-vacuumed line pointers.

-- 
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] IDLE in transaction introspection

2011-11-02 Thread Scott Mead
On Wed, Nov 2, 2011 at 4:12 AM, Albe Laurenz laurenz.a...@wien.gv.atwrote:

 Andrew Dunstan wrote:
  On 11/01/2011 09:52 AM, Tom Lane wrote:
  I'm for just redefining the query field as current or last
  query.
 
  +1
 
  I could go either way on whether to rename it.
 
  Rename it please. current_query will just be wrong. I'd be inclined
  just to call it query or query_string and leave it to the docs to
  define the exact semantics.

 +1 for renaming, +1 for a state column.
 I think it is overkill to keep a query history beyond that -- if you
 want that,
 you can resort to the log files.


ISTM that we're all for:

   creating a new column: state
   renaming current_query = query

   State will display RUNNING, IDLE, IDLE in transaction, etc...
   query will display the last query that was executed.

I've written this up in the attached patch, looking for feedback. (NB:
Originally I was using 9.1.1 release, I just did a git clone today to
generate this).

--
  Scott Mead
   OpenSCG http://www.openscg.com




 Yours,
 Laurenz Albe



pg_stat_activity_state_query.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] pg_upgrade if 'postgres' database is dropped

2011-11-02 Thread Robert Haas
On Wed, Nov 2, 2011 at 2:05 PM, Bruce Momjian br...@momjian.us wrote:
 Robert Haas wrote:
  However, we need to rethink the flag to be used for this: pg_dumpall
  uses -l, but many of the other utilities already use that for some
  other purpose, and it's not exactly mnemonic anyway. ?-d for
  database could work, but that's also in use in some places, and
  furthermore somewhat confusing since many if not all of these
  utilities have an option to operate on a single database only, and you
  might think that -d would specify the database to operate on, rather
  than the one to be used to get the list of databases. ?pgAdmin uses
  the term maintenance database to refer to a database to be used when
  none is explicitly specified, and I think that's fairly clear
  terminology. ?So I propose that we add a --maintenance-db option (with
  no short form, since this is a relatively obscure need) to the tools
  listed above. ?The tools will pass the associated value (or NULL if
  the option is not specified) to the above-mentioned routine in
  common.c, which will do the rest.
 
  If nobody objects, I'll go do that. ?Hopefully that should be enough
  to put this problem to bed more or less permanently.

 All right, I've worked up a (rather boring and tedious) patch to do
 this, which is attached.

 I wonder if we should bother using a flag for this.  No one has asked
 for one, and the new code to conditionally connect to databases should
 function fine for most use cases.

True, but OTOH we have such a flag for pg_dumpall, and I've already
done the 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


[HACKERS] Re: [COMMITTERS] pgsql: Reduce checkpoints and WAL traffic on low activity database serv

2011-11-02 Thread Robert Haas
On Wed, Nov 2, 2011 at 12:34 PM, Simon Riggs si...@2ndquadrant.com wrote:
 On Wed, Nov 2, 2011 at 4:15 PM, Robert Haas robertmh...@gmail.com wrote:
 On Wed, Nov 2, 2011 at 11:39 AM, Simon Riggs si...@2ndquadrant.com wrote:
 Reduce checkpoints and WAL traffic on low activity database server
 Previously, we skipped a checkpoint if no WAL had been written since
 last checkpoint, though this does not appear in user documentation.
 As of now, we skip a checkpoint until we have written at least one
 enough WAL to switch the next WAL file. This greatly reduces the
 level of activity and number of WAL messages generated by a very
 low activity server. This is safe because the purpose of a checkpoint
 is to act as a starting place for a recovery, in case of crash.
 This patch maintains minimal WAL volume for replay in case of crash,
 thus maintaining very low crash recovery time.

 I think you need to update the docs, for checkpoint_timeout if nothing else.

 Checkpoints have always been skipped, if no activity. So the docs
 don't need changing.

Eh, well, I don't agree.  The section on WAL configuration says:

 A checkpoint is created every checkpoint_segments log segments, or every 
 checkpoint_timeout seconds, whichever comes first.

Now, admittedly, that doesn't mention that a checkpoint won't be
created if there's absolutely no activity, but the fact that we might
now not do it even if there has been some minimal activity seems worth
adding.

Similarly, the discussion of checkpoint_segments itself says:

Maximum time between automatic WAL checkpoints, in seconds. The
default is five minutes (5min). Increasing this parameter can increase
the amount of time needed for crash recovery. This parameter can only
be set in the postgresql.conf file or on the server command line.

Again, it's no longer the maximum time between automatic checkpoints.

-- 
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] unite recovery.conf and postgresql.conf

2011-11-02 Thread Josh Berkus
RH, Simon,

 I think that might have some possibilities.  But how does that work in
 detail?  If you set it to empty, then the recovery_* parameters are
 just GUCs, I suppose: which seems fine.  But if you set it to a
 non-empty value then what happens, exactly?  The recovery.conf
 settings clobber anything in postgresql.conf, and when we exit
 recovery we reload the config, discarding any settings we got from
 recovery.conf?  That might not be too bad.

Yeah, that's what I was picturing.  By tying backwards-compatibility to
a setting in pg.conf, you eliminate a lot of changes for a DBA
accidentally enabling it.  This also then supports re-locating the
recovery.conf file, which has been an issue for a long time.

 I think we need to back up and figure out what problem we're trying to
 solve here.  IMV, the answer is setting up a standby is too
 complicated and requiring yet another configuration file to do it
 makes it even more complicated.   If the mechanism we introduce to
 solve that problem is more complicated than what we have now, it
 might end up being a net regression in terms of usability.

Well, as someone who sets up and admins replication for a bunch of
clients, here's what I'd like to see:

1. no more using a configuration file as a trigger
2. ability to put replication configuration in postgresql.conf or in a
manually designated include file
3. having replication configuration show up in pg_settings

The three settings above would make my life as a contract DBA much
easier ... and I presume help a lot of our users like me.  Among other
things, fixing the 3 things above would make replication integrate a lot
better with configuration management systems and monitoring.

 I feel like changing everything that's currently in recovery.conf to
 GUCs and implementing SET PERSISTENT would give everyone what they
 need, admittedly without perfect backward compatibility, but perhaps
 close enough for government work, and a step forward overall.

Is anyone working on SET PERSISTENT?  I thought that got bike-shedded to
death.

 So backwards compatibility is important for downstream software.


If it wasn't, we wouldn't be having this discussion.  However, backwards
compatibility is not necessarily the *most* important consideration.

-- 
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] unite recovery.conf and postgresql.conf

2011-11-02 Thread Robert Haas
On Wed, Nov 2, 2011 at 2:48 PM, Josh Berkus j...@agliodbs.com wrote:
 Is anyone working on SET PERSISTENT?  I thought that got bike-shedded to
 death.

I think we had a fairly good sketch of how it could work mapped out,
mostly based around adding a postgresql.auto file.  I could dig up the
old discussions, if need be.

Basically, I'd be willing to get that implemented and bull it through
to completion in time for 9.2 if having that would make it easier for
everyone to accept the idea of turning the recovery.conf parameters
into GUCs.  Otherwise I probably will not get to it this cycle.

--
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] [v9.2] Fix Leaky View Problem

2011-11-02 Thread Tom Lane
Kohei KaiGai kai...@kaigai.gr.jp writes:
 The reason why I redefined the relid of RangeTblEntry is to avoid
 the problem when security_barrier attribute get changed by concurrent
 transactions between rewriter and planenr stage.

This is complete nonsense.  If the information is being injected into
the querytree by the rewriter, it's sufficient to assume that it's up to
date.  Were it not so, we'd have problems with CREATE OR REPLACE RULE,
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] superusers are members of all roles?

2011-11-02 Thread Andrew Dunstan



On 09/11/2011 09:40 PM, Andrew Dunstan wrote:



On 09/09/2011 11:34 PM, Bruce Momjian wrote:

Robert Haas wrote:
On Sat, May 7, 2011 at 11:42 PM, Bruce Momjianbr...@momjian.us  
wrote:

Is this a TODO?

I think so.

Added to TODO:

Address problem where superusers are assumed to be members of all 
groups



http://archives.postgresql.org/pgsql-hackers/2011-04/msg00337.php


This turns out to be a one-liner.




Patch with a small docs addition also. Adding to Nov commitfest.

cheers

andrew


diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml
index 5d543cb..baed090 100644
--- a/doc/src/sgml/client-auth.sgml
+++ b/doc/src/sgml/client-auth.sgml
@@ -210,7 +210,10 @@ hostnossl  replaceabledatabase/replaceable  replaceableuser/replaceable
in productnamePostgreSQL/; a literal+/ mark really means
quotematch any of the roles that are directly or indirectly members
of this role/, while a name without a literal+/ mark matches
-   only that specific role.)
+   only that specific role.) For this purpose, a superuser is only
+   considered to be a member of a role if they are explicitly a member
+   of the role, directly or indirectly, and not just by virtue of
+   being a superuser.
Multiple user names can be supplied by separating them with commas.
A separate file containing user names can be specified by preceding the
file name with literal@/.
diff --git a/src/backend/libpq/hba.c b/src/backend/libpq/hba.c
index 1ee030f..1c84a60 100644
--- a/src/backend/libpq/hba.c
+++ b/src/backend/libpq/hba.c
@@ -442,8 +442,13 @@ is_member(Oid userid, const char *role)
 	if (!OidIsValid(roleid))
 		return false;			/* if target role not exist, say no */
 
-	/* See if user is directly or indirectly a member of role */
-	return is_member_of_role(userid, roleid);
+	/* 
+	 * See if user is directly or indirectly a member of role.
+	 * For this purpose, a superuser is not considered to be automatically
+	 * a member of the role, so group auth only applies to explicit
+	 * membership.
+	 */
+	return is_member_of_role_nosuper(userid, roleid);
 }
 
 /*

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


Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)

2011-11-02 Thread Heikki Linnakangas

On 01.11.2011 06:33, Jeff Davis wrote:

On Mon, 2011-10-24 at 15:05 +0400, Alexander Korotkov wrote:


I think implementing subtype_diff for each datatype is ok.  We could
implement some universal function based on minus operator and casting
to double precision. But such solution might be unacceptable in
both predictability (operator and casting function might do not the
things we expect) and performance.


Done.


Thanks, I'm looking into this now.


+   else if (lower1.infinite || upper1.infinite)
+   length1 = 1.0/0.0;


That seems wrong. I take it that the point is to set length1 to infinity?

PS. I note the docs still refer to subtype_float. I'll fix that before 
committing.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.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: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)

2011-11-02 Thread Tom Lane
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
 On 01.11.2011 06:33, Jeff Davis wrote:
 +   else if (lower1.infinite || upper1.infinite)
 +   length1 = 1.0/0.0;

 That seems wrong. I take it that the point is to set length1 to infinity?

Please use get_float[48]_infinity() or get_float[48]_nan(), as
appropriate (I think the latter may be intended here), rather than
making up your own way of getting those values.

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] Re: [COMMITTERS] pgsql: Reduce checkpoints and WAL traffic on low activity database serv

2011-11-02 Thread Simon Riggs
On Wed, Nov 2, 2011 at 6:27 PM, Robert Haas robertmh...@gmail.com wrote:

 Again, it's no longer the maximum time between automatic checkpoints.

You're missing the point that it never was like that. I've not altered
the situation.

And it doesn't matter either, so I will ignore.

If you see a need to correct those docs, feel free.

-- 
 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] unite recovery.conf and postgresql.conf

2011-11-02 Thread Simon Riggs
On Wed, Nov 2, 2011 at 6:48 PM, Josh Berkus j...@agliodbs.com wrote:

 Well, as someone who sets up and admins replication for a bunch of
 clients, here's what I'd like to see:

Everyone has their own set of requirements. I've tried hard to fuse
those together into a useful proposal, listening to all. Please bear
in mind that I make my living in exactly the same way you do, so you
must surely be aware I do this solely in the common interest.

I don't force you to accept that proposal, but challenging it does
require somebody to step up to the plate and work out a better
detailed proposal rather than just restate what they personally want.
I don't rule out out that a better proposal exists and would be
incredibly happy if someone worked out the design, wrote it up and
desnaggled it.

We can always do nothing, which is a safe and viable option.

-- 
 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: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)

2011-11-02 Thread Heikki Linnakangas

On 01.11.2011 06:33, Jeff Davis wrote:

On Mon, 2011-10-24 at 15:05 +0400, Alexander Korotkov wrote:


I think implementing subtype_diff for each datatype is ok.  We could
implement some universal function based on minus operator and casting
to double precision. But such solution might be unacceptable in
both predictability (operator and casting function might do not the
things we expect) and performance.


Done.

Everything is complete in this patch with the exception of two optional
things, which I still intend to do but might best be done in a separate
commit:

   * support typmod for ranges
   * support casts between different range types

Both of these things, I believe, require the introduction of an
RangeCoerceExpr, similar to ArrayCoerceExpr. That's fine, but it creates
a rather large diff, so it might be best left for a later commit.


Using the test table from the rangetypes test case:

postgres=#  select * from test_range_gist where 10 @ ir;
ERROR:  unsupported type: 3904

This seems to be coming from the selectivity estimation function. The 
selectivity function for @ is scalargtsel, which is usually used for 
scalar  and =. That doesn't seem right. But what do we store in the 
statistics for range types in the first place, and what would be the 
right thing to do for selectivity estimation?


I'll dig deeper into this tomorrow...

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.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: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)

2011-11-02 Thread Heikki Linnakangas

On 02.11.2011 22:59, Heikki Linnakangas wrote:

I'll dig deeper into this tomorrow...


Forgot to mention: I have pushed what I have done this far to my git 
repository at git://git.postgresql.org/git/users/heikki/postgres.git, if 
you want to take a look. Nothing major, just garden-variety cleanup.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.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] psql setenv command

2011-11-02 Thread Andrew Dunstan



On 09/26/2011 05:16 PM, Andrew Dunstan wrote:



On 09/26/2011 05:07 PM, Jeff Janes wrote:


But in any case, considering that we are both wondering if it works on
Windows, I think that argues that an automatic regression test would
be very handy.




I think an automated test should be possible. Something like:

   \setenv PGFOO blurfl
   \! echo $PGFOO %PGFOO%


and then have a couple of alternative results. When I get time to get 
back to this I'll experiment.





I can confirm it does work on Windows:

   C:\prog\bf\root\HEAD\testinsttype ..\..\..\setenv.sql
   \setenv PGFOO foo
   \! echo $PGFOO %PGFOO%
   \setenv PGFOO
   \! echo $PGFOO %PGFOO%
   C:\prog\bf\root\HEAD\testinstbin\psql -f ..\..\..\setenv.sql postgres
   $PGFOO foo
   $PGFOO %PGFOO%


I think I agree on reflection with Josh Kupershmidt that we don't need a 
regression test for this.


Updated patch is attached - adding to Nov commitfest.

cheers

andrew


diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index d6941e0..d8ed5c8 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2237,6 +2237,24 @@ lo_import 152801
 
 
   varlistentry
+termliteral\setenv [ replaceable class=parametername/replaceable [ replaceable class=parametervalue/replaceable ] ]/literal/term
+
+listitem
+para
+Sets the environment variable replaceable
+class=parametername/replaceable to replaceable
+class=parametervalue/replaceable, or if the 
+		replaceable class=parametervalue/replaceable is
+		not supplied, unsets the environment variable. Example:
+programlisting
+foo=gt; userinput\setenv PAGER less/userinput
+foo=gt; userinput\setenv LESS -imx4F/userinput
+/programlisting
+/para
+/listitem
+  /varlistentry
+
+  varlistentry
 termliteral\sf[+] replaceable class=parameterfunction_description/ /literal/term
 
 listitem
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 2c38902..7a91905 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1104,6 +1104,50 @@ exec_command(const char *cmd,
 		free(opt0);
 	}
 
+
+	/* \setenv -- set environment command */
+	else if (strcmp(cmd, setenv) == 0)
+	{
+		char	   *envvar = psql_scan_slash_option(scan_state,
+  OT_NORMAL, NULL, false);
+		char	   *envval = psql_scan_slash_option(scan_state,
+  OT_NORMAL, NULL, false);
+
+		if (!envvar)
+		{
+			psql_error(\\%s: missing required argument\n, cmd);
+			success = false;
+		}
+		else if (!envval)
+		{
+			/* No argument - unset the environment variable */
+			unsetenv(envvar);
+			success = true;
+		}
+		else
+		{
+			/* Set variable to the value of the next argument */
+			int len = strlen(envvar) + strlen(envval) + 1;
+			char	   *newval = malloc(len + 1);
+
+			if (!newval)
+			{
+psql_error(out of memory\n);
+exit(EXIT_FAILURE);
+			}
+			snprintf(newval, len+1, %s=%s, envvar, envval);
+			putenv(newval);
+			success = true;
+			/*
+			 * Do not free newval here, it will screw up the environment
+			 * if you do. See putenv man page for details. That means we
+			 * leak a bit of memory here, but not enough to worry about.
+			 */
+		}
+		free(envvar);
+		free(envval);
+	}
+
 	/* \sf -- show a function's source code */
 	else if (strcmp(cmd, sf) == 0 || strcmp(cmd, sf+) == 0)
 	{
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 53e4cd0..5c2172e 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -158,7 +158,7 @@ slashUsage(unsigned short int pager)
 {
 	FILE	   *output;
 
-	output = PageOutput(93, pager);
+	output = PageOutput(94, pager);
 
 	/* if you add/remove a line here, change the row count above */
 
@@ -257,6 +257,7 @@ slashUsage(unsigned short int pager)
 
 	fprintf(output, _(Operating System\n));
 	fprintf(output, _(  \\cd [DIR]  change the current working directory\n));
+	fprintf(output, _(  \\setenv NAME [VALUE]   set environment variable, or unset it if no value provided\n));
 	fprintf(output, _(  \\timing [on|off]   toggle timing of commands (currently %s)\n),
 			ON(pset.timing));
 	fprintf(output, _(  \\! [COMMAND]   execute command in shell or start interactive shell\n));

-- 
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-11-02 Thread Andrew Dunstan



On 09/02/2011 03:15 PM, Josh Berkus wrote:

OK, this seems to have some pluses and no negative comments, so it seems
worth going forward. Do we want an equivalent pg_restore option?

I'm not sure it's *as* important for pg_restore, since I can easily use
  a manifest to avoid restoring data for a single table.  So I guess it's
a question of how hard is it to add it?



The short answer is more work than I want to put in to this. 
pg_restore doesn't have any of pg_dump's infrastructure for handling 
table name patterns, nor for excluding tables. So I think all that would 
remain a TODO. (A good beginner project, maybe).


A slightly updated patch is attached, the main change being that I 
removed use of a short option and only support the long name option. 
-D didn't seem sufficiently mnemonic to me. I'll add this to the 
November commitfest, but I'd like to get it committed ASAP as it will 
simplify setting up the -pre and -post data patches.


cheers

andrew


diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index f6f33de..a5443b7 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -612,6 +612,21 @@ PostgreSQL documentation
  /varlistentry
 
  varlistentry
+  termoption--exclude-table-data=replaceable class=parametertable/replaceable/option/term
+  listitem
+   para
+Do not dump data for any tables matching the replaceable
+class=parametertable/replaceable pattern.  The pattern is
+interpreted according to the same rules as for option-t/.
+option--exclude-table-data/ can be given more than once to 
+exclude tables matching any of several patterns. This option is
+useful when you need the definition of a particular table even
+though you do not need the data in it.
+   /para
+  /listitem
+ /varlistentry
+
+ varlistentry
   termoption--inserts/option/term
   listitem
para
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index c17b52c..6048e2c 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -114,6 +114,8 @@ static SimpleStringList table_include_patterns = {NULL, NULL};
 static SimpleOidList table_include_oids = {NULL, NULL};
 static SimpleStringList table_exclude_patterns = {NULL, NULL};
 static SimpleOidList table_exclude_oids = {NULL, NULL};
+static SimpleStringList tabledata_exclude_patterns = {NULL, NULL};
+static SimpleOidList tabledata_exclude_oids = {NULL, NULL};
 
 /* default, if no inclusion switches appear, is to dump everything */
 static bool include_everything = true;
@@ -322,6 +324,7 @@ main(int argc, char **argv)
 		{column-inserts, no_argument, column_inserts, 1},
 		{disable-dollar-quoting, no_argument, disable_dollar_quoting, 1},
 		{disable-triggers, no_argument, disable_triggers, 1},
+		{exclude-table-data, required_argument, NULL, 4},
 		{inserts, no_argument, dump_inserts, 1},
 		{lock-wait-timeout, required_argument, NULL, 2},
 		{no-tablespaces, no_argument, outputNoTablespaces, 1},
@@ -485,6 +488,10 @@ main(int argc, char **argv)
 use_role = optarg;
 break;
 
+			case 4:			/* exclude table(s) data */
+simple_string_list_append(tabledata_exclude_patterns, optarg);
+break;
+
 			default:
 fprintf(stderr, _(Try \%s --help\ for more information.\n), progname);
 exit(1);
@@ -712,6 +719,10 @@ main(int argc, char **argv)
 	}
 	expand_table_name_patterns(table_exclude_patterns,
 			   table_exclude_oids);
+
+	expand_table_name_patterns(tabledata_exclude_patterns,
+			   tabledata_exclude_oids);
+
 	/* non-matching exclusion patterns aren't an error */
 
 	/*
@@ -851,6 +862,7 @@ help(const char *progname)
 	printf(_(  --column-insertsdump data as INSERT commands with column names\n));
 	printf(_(  --disable-dollar-quotingdisable dollar quoting, use SQL standard quoting\n));
 	printf(_(  --disable-triggers  disable triggers during data-only restore\n));
+	printf(_(  --exclude-table-data=TABLE  do NOT dump data for the named table(s)\n));
 	printf(_(  --inserts   dump data as INSERT commands, rather than COPY\n));
 	printf(_(  --no-security-labelsdo not dump security label assignments\n));
 	printf(_(  --no-tablespacesdo not dump tablespace assignments\n));
@@ -1084,6 +1096,15 @@ selectDumpableTable(TableInfo *tbinfo)
 		simple_oid_list_member(table_exclude_oids,
 			   tbinfo-dobj.catId.oid))
 		tbinfo-dobj.dump = false;
+
+	/* If table is to be dumped, check that the data is not excluded */
+	if (tbinfo-dobj.dump  !
+		simple_oid_list_member(tabledata_exclude_oids,
+			   tbinfo-dobj.catId.oid))
+		tbinfo-dobj.dumpdata = true;
+	else
+		tbinfo-dobj.dumpdata = false;
+		
 }
 
 /*
@@ -1515,6 +1536,10 @@ dumpTableData(Archive *fout, TableDataInfo *tdinfo)
 	DataDumperPtr dumpFn;
 	char	   *copyStmt;
 
+	/* don't do anything if the data isn't wanted */
+	if (!tbinfo-dobj.dumpdata)
+		

Re: [HACKERS] heap vacuum cleanup locks

2011-11-02 Thread Robert Haas
On Tue, Jun 7, 2011 at 3:24 PM, Greg Stark gsst...@mit.edu wrote:
 Well it's super-exclusive-vacuum-lock avoidance techniques. Why
 shouldn't it make more sense to try to reduce the frequency and impact
 of the single-purpose outlier in a non-critical-path instead of
 burdening every other data reader with extra overhead?

 I think Robert's plan is exactly right though I would phrase it
 differently. We should get the exclusive lock, freeze/kill any xids
 and line pointers, then if the pin-count is 1 do the compaction.

I wrote a really neat patch to do this today...  and then, as I
thought about it some more, I started to think that it's probably
unsafe.  Here's the trouble: with this approach, we assume that it's
OK to change the contents of the line pointer while holding only an
exclusive lock on the buffer.  But there is a good deal of code out
there that thinks it's OK to examine a line pointer with only a pin on
the buffer (no lock).  You need a content lock to scan the item
pointer array, but once you've identified an item of interest, you're
entitled to assume that it won't be modified while you hold a buffer
pin.  Now, if you've identified a particular tuple as being visible to
your scan, then you might think that VACUUM shouldn't be removing it
anyway.  But I think that's only true for MVCC scans - for example,
what happens under SnapshotNow semantics?  But then then on third
thought, if you've also got an MVCC snapshot taken before the start of
the SnapshotNow scan, you are probably OK, because your advertised
xmin should prevent anyone from removing anything anyway, so how do
you actually provoke a failure?

Anyway, I'm attaching the patch, in case anyone has any ideas on where
to go with this.

 I'm really wishing we had more bits in the vm. It looks like we could use:
  - contains not-all-visible tuples
  - contains not-frozen xids
  - in need of compaction

 I'm sure we could find a use for one more page-level vm bit too.

We've got plenty of room for more page-level bits, if we need them.

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


vacuum-unstick-v1.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] Re: [COMMITTERS] pgsql: Force strings passed to and from plperl to be in UTF8 encoding.

2011-11-02 Thread Andrew Dunstan



On 10/07/2011 12:51 PM, Alex Hunsaker wrote:

On Wed, Oct 5, 2011 at 20:36, Robert Haasrobertmh...@gmail.com  wrote:

On Wed, Oct 5, 2011 at 5:03 PM, Alex Hunsakerbada...@gmail.com  wrote:

On Wed, Oct 5, 2011 at 08:18, Robert Haasrobertmh...@gmail.com  wrote:

On Wed, Oct 5, 2011 at 3:58 AM, Amit Khandekar
amit.khande...@enterprisedb.com  wrote:

I have no more issues with the patch.
Thanks!

I think this patch needs to be added to the open CommitFest, with
links to the reviews, and marked Ready for Committer.

The open commitfest? Even if its an important bug fix that should be
backpatched?

Considering that the issue appears to have been ignored from
mid-February until early October, I don't see why it should now get to
jump to the head of the queue.  Other people may have different
opinions, of course.

Added. :-)



I'm just starting to look at this, by way of a break in staring at 
pg_dump code ;-). This just needs to be backpatched to 9.1, is that right?


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] Re: [COMMITTERS] pgsql: Force strings passed to and from plperl to be in UTF8 encoding.

2011-11-02 Thread Alex Hunsaker
On Wed, Nov 2, 2011 at 17:12, Andrew Dunstan and...@dunslane.net wrote:
 Considering that the issue appears to have been ignored from
 mid-February until early October, I don't see why it should now get to
 jump to the head of the queue.  Other people may have different
 opinions, of course.

 Added. :-)


 I'm just starting to look at this, by way of a break in staring at pg_dump
 code ;-). This just needs to be backpatched to 9.1, is that right?

Yes please.

9.0 did not have this problem (or at least if it did it was a separate issue).

-- 
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] Re: [COMMITTERS] pgsql: Reduce checkpoints and WAL traffic on low activity database serv

2011-11-02 Thread Greg Smith

On 11/02/2011 05:48 PM, Simon Riggs wrote:

On Wed, Nov 2, 2011 at 6:27 PM, Robert Haasrobertmh...@gmail.com  wrote:

   

Again, it's no longer the maximum time between automatic checkpoints.
 

You're missing the point that it never was like that. I've not altered
the situation.
   


Robert's point is more that the existing docs are already broken; this 
new patch can just increase how bad the drift between reality and 
documentation can be.  Before, the only people who ran into this had 
zero activity on the server, which meant there wasn't any data to be 
lost, either.  Now it's potentially broader than that.


With some trivial checkpoints containing a small amount of data skipped 
now, aren't there some cases where less WAL data will be written than 
before?  In that case, the user visible behavior here would be 
different.  I'd be most concerned about file-based log shipping case.


I don't think there's any change needed to the Write Ahead Log section 
of the Server Configuration chapter.  In the Reliability and the 
Write-Ahead Log chapter, this text in WAL Configuration was already 
highlighted as the problem here:


The server's background writer process automatically performs a 
checkpoint every so often. A checkpoint is created every 
checkpoint_segments log segments, or every checkpoint_timeout seconds, 
whichever comes first. The default settings are 3 segments and 300 
seconds (5 minutes), respectively. It is also possible to force a 
checkpoint by using the SQL command CHECKPOINT.


I think this needs a change like this, to address the hole that was 
already in the docs and cover the new behavior too; this goes just 
before  It is also possible to force...


In cases where there are little or no writes to the WAL, checkpoints 
will be skipped even if checkpoint_timeout has passed.  At least one new 
WAL segment must have been created before an automatic checkpoint 
occurs.  The time between checkpoints and when new WAL segments are 
created are not related in any other way.  If file-based WAL shipping is 
being used and you want to bound how often files are sent to standby 
server, to reduce potential data loss you should adjust archive_timeout 
parameter rather than the checkpoint ones.


This area is a confusing one, so some clarification of the relation 
between checkpoints and replication is a useful docs improvement.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us


--
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_upgrade if 'postgres' database is dropped

2011-11-02 Thread Bruce Momjian
Robert Haas wrote:
   If nobody objects, I'll go do that. ?Hopefully that should be enough
   to put this problem to bed more or less permanently.
 
  All right, I've worked up a (rather boring and tedious) patch to do
  this, which is attached.
 
  I wonder if we should bother using a flag for this. ?No one has asked
  for one, and the new code to conditionally connect to databases should
  function fine for most use cases.
 
 True, but OTOH we have such a flag for pg_dumpall, and I've already
 done the work.

Well, every user-visible API option has a cost, and I am not sure there
is enough usefulness to overcome the cost of this.

As far as pg_dumpall, you could argue that the -l flag isn't needed; 
the docs say:

   -l dbname, --database=dbname
   Specifies the name of the database to connect to to
   dump global objects and discover what other databases
   should be dumped. If not specified, the postgres
   database will be used, and if that does not exist,
   template1 will be used.

What is the value of this flag?  The only value I can see would be if
the 'postgres' database does not exist and you are concerned that you
might block create database operations during pg_dumpall's dump of
global objects, or you don't have permissions for template1 for some
reason.

Also, if we are going to add this flag, we should have pg_dumpall use it
too and just depricate the old options.

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

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

-- 
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] unite recovery.conf and postgresql.conf

2011-11-02 Thread Josh Berkus
Simon,

 Everyone has their own set of requirements. I've tried hard to fuse
 those together into a useful proposal, listening to all. Please bear
 in mind that I make my living in exactly the same way you do, so you
 must surely be aware I do this solely in the common interest.

Thank you for giving us a place to start. I have seen and commented on
your compromise.  Both Robert Treat and I poked holes in it.  It was a
good first attempt, but not a final attempt -- your compromise proposal
was heavily skewed towards maintaining the status quo at the expense of
improving functionality.  As a compromise, it was 70% Simon, 30%
everyone else.

 I don't force you to accept that proposal, but challenging it does
 require somebody to step up to the plate and work out a better
 detailed proposal rather than just restate what they personally want.

I made a proposal, which was based on modifying (and I believe,
improving) your proposal.

 We can always do nothing, which is a safe and viable option.

Not really, no.  The whole recovery.conf thing is very broken and
inhibits adoption of PostgreSQL because our users can't figure it out.

You've made it pretty clear that you're personally comfortable with how
replication configuration works now, and aren't really interested in any
changes.  That's certainly a valid viewpoint, but the users and
contributors who find the API horribly unusable also have a valid
viewpoint.  You don't automatically win arguments because you're on the
side of backwards compatibility.

When we released binary replication in 9.0, I thought everyone knew that
it was a first cut and that we'd be making some dramatic changes --
including ones which broke things -- over the next few versions.  There
was simply no way for us to know real user requirements until the
feature was in the field and being deployed in production.  We would
discover some things which really didn't work and that we had to break
and remake.  And we have.

Now you are arguing for premature senescence, where our first API
becomes our only API now and forever.  That's a road to project death.

-- 
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] pg_upgrade if 'postgres' database is dropped

2011-11-02 Thread Robert Haas
On Wed, Nov 2, 2011 at 8:31 PM, Bruce Momjian br...@momjian.us wrote:
 Robert Haas wrote:
   If nobody objects, I'll go do that. ?Hopefully that should be enough
   to put this problem to bed more or less permanently.
 
  All right, I've worked up a (rather boring and tedious) patch to do
  this, which is attached.
 
  I wonder if we should bother using a flag for this. ?No one has asked
  for one, and the new code to conditionally connect to databases should
  function fine for most use cases.

 True, but OTOH we have such a flag for pg_dumpall, and I've already
 done the work.

 Well, every user-visible API option has a cost, and I am not sure there
 is enough usefulness to overcome the cost of this.

I am not sure why you think this is worth the time it takes to argue
about it, but if you want to whack the patch around or just forget the
whole thing, go ahead.  The difference between what you're proposing
and what I'm proposing is about 25 lines of code, so it hardly needs
an acre of justification.  To me, making the tools consistent with
each other and not dependent on the user's choice of database names is
worth the tiny amount of code it takes to make that happen.

 Also, if we are going to add this flag, we should have pg_dumpall use it
 too and just depricate the old options.

I thought about that, but couldn't think of a compelling reason to
break backward compatibility.

-- 
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] superusers are members of all roles?

2011-11-02 Thread Robert Haas
On Wed, Nov 2, 2011 at 3:27 PM, Andrew Dunstan and...@dunslane.net wrote:
 Patch with a small docs addition also. Adding to Nov commitfest.

I have reviewed this and it looks good to me.  Marking Ready for Committer.

-- 
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] Re: [COMMITTERS] pgsql: Reduce checkpoints and WAL traffic on low activity database serv

2011-11-02 Thread Tom Lane
Greg Smith g...@2ndquadrant.com writes:
 On 11/02/2011 05:48 PM, Simon Riggs wrote:
 You're missing the point that it never was like that. I've not altered
 the situation.

 Robert's point is more that the existing docs are already broken; this 
 new patch can just increase how bad the drift between reality and 
 documentation can be.

Yeah.  I agree that we need to adjust the docs.  Anybody object
to Greg's proposed text?

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