Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?
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
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
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
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
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
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
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
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
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 ...?
* 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
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 ...?
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
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
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 ...?
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 ...?
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
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
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
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
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
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 ...?
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?
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
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
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
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
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
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
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
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
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
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
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
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
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/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
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
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
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
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
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
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
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
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?
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)
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)
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
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
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)
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)
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
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
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
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.
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.
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
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
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
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
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?
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
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
