Re: [HACKERS] Change authentication error message (patch)
On 06/20/2013 12:51 AM, Jeff Janes wrote: > I think we need to keep the first "password". "Password authentication" > is a single thing, it is the authentication method attempted. It is the > password method (which includes MD5) which failed, as opposed to the > LDAP method or the Peer method or one of the other methods. That's against the rule of not revealing any more knowledge than a potential attacker already has, no? For that reason, I'd rather go with just "authentication failed". > Without this level of explicitness, it might be hard to figure out which > row in pg_hba.conf was the one that PostgreSQL glommed onto to use for > authentication. As argued before, that should go into the logs for diagnosis by the sysadmin, but should not be revealed to an attacker. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Adding IEEE 754:2008 decimal floating point and hardware support for it
On 20 June 2013 06:45, Craig Ringer wrote: > I think a good starting point would be to use the Intel and IBM > libraries to implement basic DECIMAL32/64/128 to see if they perform > better than the gcc builtins tested by Pavel by adapting his extension. > > If the performance isn't interesting it may still be worth adding for > compliance reasons, but if we can only add IEEE-compliant decimal FP by > using non-SQL-standard type names I don't think that's super useful. I think we should be adding a datatype that is IEEE compliant, even if that doesn't have space and/or performance advantages. We might hope it does, but if not then it may do in the future. It seems almost certain that the SQL standard would adopt the IEEE datatypes in the future. > If > there are significant performance/space gains to be had, we could > consider introducing DECIMAL32/64/128 types with the same names used by > DB2, so people could explicitly choose to use them where appropriate. Typenames are easily setup if compatibility is required, so thats not a problem. We'd want to use the name the SQL std people assign. -- 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] Add visibility map information to pg_freespace.
On 20 June 2013 04:26, Satoshi Nagayasu wrote: > I'm looking into this patch as a reviewer. > > > (2013/06/19 18:03), Simon Riggs wrote: >> >> On 19 June 2013 09:19, Kyotaro HORIGUCHI >> wrote: >> >>> It should useful in other aspects but it seems a bit complicated >>> just to know about visibility bits for certain blocks. >> >> >> With your current patch you can only see the visibility info for >> blocks in cache, not for all blocks. So while you may think it is >> useful, it is also unnecessarily limited in scope. >> >> Let's just have something that is easy to use that lets us see the >> visibility state for a block, not just blocks in freespace. > > > I think we can have this visibility map statistics also > in pgstattuple function (as a new column) for this purpose. > > IMHO, we have several modules for different purposes. > > - pageinspect provies several functions for debugging purpose. > - pg_freespace provies a view for monitoring purpose. > - pgstattuple provies several functions for collecting > specific table/index statistics. > > So, we can have similar feature in different modules. > > Any comments? +1 -- 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] Patch for fail-back without fresh backup
On Wednesday, June 19, 2013 10:45 PM Sawada Masahiko wrote: On Tuesday, June 18, 2013, Amit Kapila wrote: On Tuesday, June 18, 2013 12:18 AM Sawada Masahiko wrote: > On Sun, Jun 16, 2013 at 2:00 PM, Amit kapila > wrote: > > On Saturday, June 15, 2013 8:29 PM Sawada Masahiko wrote: > > On Sat, Jun 15, 2013 at 10:34 PM, Amit kapila > wrote: > >> > >> On Saturday, June 15, 2013 1:19 PM Sawada Masahiko wrote: > >> On Fri, Jun 14, 2013 at 10:15 PM, Amit Kapila > wrote: > >>> On Friday, June 14, 2013 2:42 PM Samrat Revagade wrote: > Hello, > >>> >>> I think that we can dumping data before all WAL files deleting. All >>> WAL files deleting is done when old master starts as new standby. >> Can we dump data without starting server? >Sorry I made a mistake. We can't it. > this proposing patch need to be able to also handle such scenario in future. I am not sure the purposed patch can handle it so easily, but I think if others also felt it important, then a method should be a provided to user for extracting his last committed data. With Regards, Amit Kapila. -- 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: Adding IEEE 754:2008 decimal floating point and hardware support for it
On 06/12/2013 07:51 PM, Andres Freund wrote: > On 2013-06-12 19:47:46 +0800, Craig Ringer wrote: >> On 06/12/2013 05:55 PM, Greg Stark wrote: >>> On Wed, Jun 12, 2013 at 12:56 AM, Craig Ringer >>> wrote: The main thing I'm wondering is how/if to handle backward compatibility with the existing NUMERIC and its DECIMAL alias >>> If it were 100% functionally equivalent you could just hide the >>> implementation internally. Have a bit that indicates which >>> representation was stored and call the right function depending. >> That's what I was originally wondering about, but as Tom pointed out it >> won't work. We'd still need to handle scale and precision greater than >> that offered by _Decimal128 and wouldn't know in advance how much >> scale/precision they wanted to preserve. So we'd land up upcasting >> everything to NUMERIC whenever we did anything with it anyway, only to >> then convert it back into the appropriate fixed size decimal type for >> storage. > Well, you can limit the "upcasting" to the cases where we would exceed > the precision. How do you determine that for, say, DECIMAL '4'/ DECIMAL '3'? Or sqrt(DECIMAL '2') ? ... actually, in all those cases Pg currently arbitrarily limits the precision to 17 digits. Interesting. Not true for multiplication though: regress=> select (NUMERIC '4' / NUMERIC '3') * NUMERIC '3.141592653589793238462643383279502884197169'; ?column? -- 4.1887902047863908798971027247128958968414458906832371934277 (1 row) so simple operations like: SELECT (DECIMAL '4'/ DECIMAL '3') * (DECIMAL '1.11'); would exceed the precision currently provided and be upcast. We'd quickly land up getting to full "NUMERIC" internally no matter what type we started with. I think a good starting point would be to use the Intel and IBM libraries to implement basic DECIMAL32/64/128 to see if they perform better than the gcc builtins tested by Pavel by adapting his extension. If the performance isn't interesting it may still be worth adding for compliance reasons, but if we can only add IEEE-compliant decimal FP by using non-SQL-standard type names I don't think that's super useful. If there are significant performance/space gains to be had, we could consider introducing DECIMAL32/64/128 types with the same names used by DB2, so people could explicitly choose to use them where appropriate. >> Pretty pointless, and made doubly so by the fact that if we're >> not using a nice fixed-width type and have to support VARLENA we miss >> out on a whole bunch of performance benefits. > I rather doubt that using a 1byte varlena - which it will be for > reasonably sized Datums - will be a relevant bottleneck here. Maybe if > you only have 'NOT NULL', fixed width columns, but even then... That's good to know - if I've overestimated the cost of using VARLENA for this, that's really quite good news. -- Craig Ringer 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: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
Amit Kapila wrote: > Currently header comment is: > # Do not edit this file manually! > # It will be overwritten by ALTER SYSTEM command. > > Kindly let me know your suggestion about the above comment in header? This seems perfect to me. -- Álvaro Herrerahttp://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] Bugfix and new feature for PGXS
On 06/20/2013 11:26 AM, Peter Eisentraut wrote: > 3) Install them in a different directory and require a different > #include line. But then you have to change the existing uses as well, > which would probably require moving files around. If I understand you correctly, your concern seems to be with referring to the extensions headers within that extension. For example, a pgxs build of hstore using "contrib/hstore/hstore.h" wouldn't work if "hstore.h" was in the current location, the root of the hstore contrib module. It'd be fine for a non-pgxs build, since we'd just add $(top_srcdir) to the include path when building contrib modules in-tree. So, for pgxs, we'd have to construct a temporary include dir, copying hstore.h into a temporary 'contrib/hstore' subdir for the build. Which is messy, but would work, and would confine the change mostly to pgxs. Or we'd have to move it there permanently in the source tree, which seems kind of excessive. There's a certain appeal in having extensions follow the same model as the main server code: hstore/ src/ contrib hstore crc32.c hstore_compat.c hstore_gin.c hstore_gist.c hstore_io.c hstore_op.c include contrib hstore hstore.h ... but that's pretty significant disruption for something I was hoping would be a rather small change. As you note, the other option is to just refer to extension headers by their unqualified name. I'm *really* not a fan of that idea due to the obvious clash possibilities with Pg's own headers or system headers, especially given that the whole idea of extensions is that they're user supplied. Not having any kind of namespacing is worrying. What could possibly work would be to install extension headers in contrib/[modulename]/ and automatically have pgxs and the in-tree build add appropriate -I directives to those subdirs based on dependencies declared in the Makefile. Again, though, that makes the whole thing a lot more complex than I'd like. Drat. A final option: When building the extension its self, use "hstore.h". When referring to it from elsewhere, use "contrib/hstore/hstore.h". In pgxs's case it'd be installed, in an in-tree build it'd be handled by adding ${top_srcdir} to the include path when we're building contribs. Opinions all? * Give up on being able to use one extension's header from another entirely, except in the special case of in-tree builds * Install install-enabled contrib headers into an include/contrib/ that's always on the pgxs include path, so you can still just "#include hstore.h" . For in-tree builds, explicit add other modules' contrib dirs as Peter has done in the proposed plperl_hstore and plpython_hstore. (Use unqualified names). * Install install-enabled contrib headers to include/contrib/[modulename]/ . Within the module, use the unqualified header name. When referring to it from outside the module, use #include "contrib/modulename/header.h". Add $(top_srcdir) to the include path when building extensions in-tree. Personally, I'm all for just using the local path when building the module, and the qualified path elsewhere. It requires only a relatively simple change, and I don't think that using "hstore.h" within hstore, and "contrib/hstore/hstore.h" elsewhere is of great concern. -- Craig Ringer 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] Patch to add support of "IF NOT EXISTS" to others "CREATE" statements
On Wed, Jun 19, 2013 at 12:45 PM, Fabrízio de Royes Mello wrote: > On Mon, Jun 17, 2013 at 11:33 PM, Peter Eisentraut wrote: >> >> Replace/alter the object if it already exists, but fail if it does not >> exist. >> >> The complete set of variants is: >> >> - object does not exist: >> >> - proceed (normal CREATE) >> - error (my above description) >> >> - object exists: >> >> - replace (CREATE OR REPLACE) >> - skip (CREATE IF NOT EXISTS) >> - error (normal CREATE) >> > > I understood. > > The syntax can be like that? > - CREATE [ OR REPLACE | IF NOT EXISTS ] AGGREGATE ... > - CREATE [ OR REPLACE | IF NOT EXISTS ] OPERATOR ... > - CREATE [ OR REPLACE | IF NOT EXISTS ] FUNCTION ... > > I can add this features too, but IMHO it is more prudent at this CF we just > implement the IF NOT EXISTS according the initial proposal. > > I'm planning another patch do next CF to add support to "IF NOT EXISTS" to > others "CREATE" statements. See my planning [1]. > Is it possible to: CREATE [ OR REPLACE | IF NOT EXISTS ] OPERATOR CLASS I am in a situation where I need to conditionally create an operator class (that is, create only if already does not exist). For example, currently, while trying out pg_trgm and a new external module pg_bigm, I found that, currently, only one of them can be installed in a database at a time. pg_bigm for backward compatibility also creates pg_trgm_ops operator class with its member functions being the ones implemented by pg_bigm. So, if pg_trgm already exists, then I won't be able to add pg_bigm (which has its own use cases and we can probably have the two co-exist) and vice versa. It would be nice if we had the above feature so that pg_bigm or pg_trgm can use 'IF NOT EXISTS' while creating pg_trgm_ops operator class. Thoughts? -- Amit Langote -- 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] C++ compiler
On 06/16/2013 03:19 AM, Gaetano Mendola wrote: > I have read Peter Eisentraut blog entry about "Moving to C++", I full agree > with him about what he wrote. > > Is there any interest or work in progress in making the entire Postgresql > code base compilable by a C++ compiler? Well, from Peter at least, clearly ;-) Personally I'd find it useful - I'm used to working with C-compiled-as-C++ and find the things the C compiler will pass without warning or mention to be astonishing. The C++ compiler, even when being used to build programs that are entirely 'extern "C" ', provides significantly more useful static checking than the C compiler, but nothing like a proper static checker. PostgreSQL wouldn't play well with idiomatic C++, given its heavy use of longjmp() based exception handling; that doesn't play at all well with C++'s expectation that it can unwind the stack when a C++ exception occurs. It'd have to compile with -fno-exceptions (or equivalent in MSVC) - and because of that, would have to be very careful if any 3rd party C++ libraries were used. That just reinforces the idea that using the C++ compiler as a better C compiler would be the way to go. Though, given the presence of PG_TRY etc, I'd want to see just what would be involved in using C++ exceptions. If nothing else, I'd have fun re-defining PG_TRY and PG_CATCH to use C++ exceptions, then watching the fireworks. A read of the elog/ereport functions shows that they're significantly different to C++ exception handling, particularly where it comes to things like the logic for promoting errors in certain situations (FATAL->PANIC during a critical section, etc). Anyway, what astonished me when I looked at the post was the assumption from so many that it'd be better to *re-write* in C++. Was nothing learned from the Mozilla fiasco? Even if the PostgreSQL codebase were in a truly shocking state - and it certainly isn't! - it would probably make very little sense to rewrite. Well, not if you wanted to have anything usable for the next 10-15 years. I see value in making the codebase compileable with g++... and down the track I can see being able to use basic class features as quite useful given Pg's fairly OO internal design. Inline template functions instead of macros would be nice. I've worked with C++ enough not to be overly excited by the idea of going down the rabbit hole of Boost, complex template arrangements, etc, and I don't love the STL ... but any sensible project adopting C++ carefully picks and chooses what features it uses and how. -- Craig Ringer 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: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Thursday, June 20, 2013 12:32 AM Alvaro Herrera wrote: > Magnus Hagander wrote: > > On Jun 19, 2013 7:55 PM, "Peter Eisentraut" wrote: > > > > generated_by_server.conf > > > > System.conf? > > alter_system.conf ? All the names proposed in this thread are as follows: Auto.conf- 1 Vote (Josh) System.auto.conf - 1 Vote (Josh) Postgresql.auto.conf - 2 Votes (Zoltan, Amit) Persistent.auto.conf - 0 Vote generated_by_server.conf - 1 Vote (Peter E) System.conf - 1 Vote (Magnus) alter_system.conf- 1 Vote (Alvaro) I had consolidated the list, so that it would be helpful for committer to decide among proposed names for this file. With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Thursday, June 20, 2013 3:53 AM Tom Lane wrote: > Josh Berkus writes: > > True, but can you think of a better word to mean "don't edit this by > hand"? > > The file name is not nearly as important for that as putting in a > header comment "# Don't edit this file by hand." Currently header comment is: # Do not edit this file manually! # It will be overwritten by ALTER SYSTEM command. Kindly let me know your suggestion about the above comment in header? With Regards, Amit Kapila. -- 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] Proposal for Allow postgresql.conf values to be changed via SQL [review]
On Wednesday, June 19, 2013 11:30 PM Peter Eisentraut wrote: > On 6/7/13 12:14 AM, Amit Kapila wrote: > > I will change the patch as per below syntax if there are no > objections: > > > > ALTER SYSTEM SET configuration_parameter {TO | =} {value, | > 'value'}; > > I do like using ALTER SYSTEM in general, but now that I think about it, > the 9.3 feature to create global settings in pg_db_role_setting would > also have been a candidate for exactly that same syntax if it had been > available. In fact, if we do add ALTER SYSTEM, it might make sense to > recast that feature into that syntax. > > It might be clearer to do something like ALTER SYSTEM SET EXTERNAL FILE > or something like that. It's only a small syntax change, so don't > worry > about it too much, but let's keep thinking about it. Is your intention to extend the syntax by EXTERNAL FILE is for specifying SCOPE for configuration parameter? ALTER SYSTEM SET SCOPE FILE ALTER SYSTEM SET EXTENSION FILE With Regards, Amit Kapila. -- 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] add long options to pgbench (submission 1)
> New submission which put long options in alphabetical order, which seems > more logical. > > This is for reference to the next commitfest. When I applied your patch appeared the following messages: $ patch -p1 < /tmp/pgbench-longopts.patch patching file contrib/pgbench/pgbench.c Hunk #1 succeeded at 2109 (offset 29 lines). patching file doc/src/sgml/pgbench.sgml Hunk #2 succeeded at 172 (offset 12 lines). Hunk #3 FAILED at 170. Hunk #4 succeeded at 193 (offset 11 lines). Hunk #5 FAILED at 199. Hunk #13 succeeded at 344 (offset -33 lines). Hunk #14 succeeded at 367 (offset -33 lines). Hunk #15 succeeded at 382 (offset -33 lines). Hunk #16 succeeded at 395 (offset -33 lines). Hunk #17 succeeded at 407 (offset -33 lines). Hunk #18 succeeded at 422 (offset -33 lines). Hunk #19 succeeded at 432 (offset -33 lines). Hunk #20 succeeded at 442 (offset -33 lines). Hunk #21 succeeded at 454 (offset -33 lines). 2 out of 24 hunks FAILED -- saving rejects to file doc/src/sgml/pgbench.sgml.rej Please fix that and re-send the patch. Regards, - -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Blog sobre TI: http://fabriziomello.blogspot.com >> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello -- View this message in context: http://postgresql.1045698.n5.nabble.com/PATCH-add-long-options-to-pgbench-submission-1-tp5754115p5760039.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add visibility map information to pg_freespace.
I'm looking into this patch as a reviewer. (2013/06/19 18:03), Simon Riggs wrote: On 19 June 2013 09:19, Kyotaro HORIGUCHI wrote: It should useful in other aspects but it seems a bit complicated just to know about visibility bits for certain blocks. With your current patch you can only see the visibility info for blocks in cache, not for all blocks. So while you may think it is useful, it is also unnecessarily limited in scope. Let's just have something that is easy to use that lets us see the visibility state for a block, not just blocks in freespace. I think we can have this visibility map statistics also in pgstattuple function (as a new column) for this purpose. IMHO, we have several modules for different purposes. - pageinspect provies several functions for debugging purpose. - pg_freespace provies a view for monitoring purpose. - pgstattuple provies several functions for collecting specific table/index statistics. So, we can have similar feature in different modules. Any comments? Regards, -- Satoshi Nagayasu Uptime Technologies, LLC. http://www.uptime.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] Bugfix and new feature for PGXS
On Wed, 2013-06-19 at 20:58 +0200, Cédric Villemain wrote: > I believe he answered the proposal to put all headers on the same flat > directory, instead of a tree. The headers are used as #include "hstore.h" #include "ltree.h" etc. in the existing source code. If you want to install the for use by others, you can do one of three things: 1) Install them into $(pg_config --includedir-server), so other users will just include them in the same way as shown above. 2) Install them in a different directory, but keep the same #include lines. That would require PGXS changes, perhaps a new pg_config option, or something that produces the right -I option to find them. 3) Install them in a different directory and require a different #include line. But then you have to change the existing uses as well, which would probably require moving files around. Both 2 and 3 require a lot of work, possibly compatibility breaks, for no obvious reason. -- 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] Fix pgstattuple/pgstatindex to use regclass-type as the argument
(2013/06/17 4:02), Fujii Masao wrote: On Sat, Mar 9, 2013 at 3:23 PM, Satoshi Nagayasu wrote: It is obviously easy to keep two types of function interfaces, one with regclass-type and another with text-type, in the next release for backward-compatibility like below: pgstattuple(regclass) -- safer interface. pgstattuple(text) -- will be depreciated in the future release. So you're thinking to remove pgstattuple(oid) soon? AFAIK, a regclass type argument would accept an OID value, which means regclass type has upper-compatibility against oid type. So, even if the declaration is changed, compatibility could be kept actually. This test case (in sql/pgstattuple.sql) confirms that. select * from pgstatindex('myschema.test_pkey'::regclass::oid); version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation -+++---+++-+---+--+ 2 | 0 | 8192 | 1 | 0 | 1 | 0 | 0 | 0.79 | 0 (1 row) Having both interfaces for a while would allow users to have enough time to rewrite their applications. Then, we will be able to obsolete (or just drop) old interfaces in the future release, maybe 9.4 or 9.5. I think this approach would minimize an impact of such interface change. So, I think we can clean up function arguments in the pgstattuple module, and also we can have two interfaces, both regclass and text, for the next release. Any comments? In the document, you should mark old functions as deprecated. I'm still considering changing the function name as Tom pointed out. How about "pgstatbtindex"? In fact, pgstatindex does support only BTree index. So, "pgstatbtindex" seems to be more appropriate for this function. We can keep having both (old) pgstatindex and (new) pgstatbtindex during next 2-3 major releases, and the old one will be deprecated after that. Any comments? Regards, -- Satoshi Nagayasu Uptime Technologies, LLC. http://www.uptime.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] slightly confusing JSON error context
On 06/19/2013 09:19 PM, Peter Eisentraut wrote: These are some cases from the regression tests: SELECT ''::json;-- ERROR, no value ERROR: invalid input syntax for type json LINE 1: SELECT ''::json; ^ DETAIL: The input string ended unexpectedly. CONTEXT: JSON data, line 1: SELECT ''::json;-- ERROR, no value ERROR: invalid input syntax for type json LINE 1: SELECT ''::json; ^ DETAIL: The input string ended unexpectedly. CONTEXT: JSON data, line 1: Perhaps we should either put some quotes around the whitespace at the end of the CONTEXT lines, or write something like "end of input". Otherwise this might look weird. There might be more complicated cases where it looks even weirder. It's been that way since 9.2. I at least have no problem if you want to make improvements. 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: backend hangs at immediate shutdown (Re: [HACKERS] Back-branch update releases coming in a couple weeks)
MauMau escribió: > Could you review the patch? The summary of the change is: > 1. postmaster waits for children to terminate when it gets an > immediate shutdown request, instead of exiting. > > 2. postmaster sends SIGKILL to remaining children if all of the > child processes do not terminate within 10 seconds since the start > of immediate shutdown or FatalError condition. This seems reasonable. Why 10 seconds? We could wait 5 seconds, or 15. Is there a rationale behind the 10? If we said 60, that would fit perfectly well within the already existing 60-second loop in postmaster, but that seems way too long. I have only one concern about this patch, which is visible in the documentation proposed change: This is the Immediate Shutdown mode. The master postgres process will send a - SIGQUIT to all child processes and exit - immediately, without properly shutting itself down. The child processes - likewise exit immediately upon receiving - SIGQUIT. This will lead to recovery (by + SIGQUIT to all child processes, wait for + them to terminate, and exit. The child processes + exit immediately upon receiving + SIGQUIT. If any of the child processes + does not terminate within 10 seconds for some unexpected reason, + the master postgres process will send a SIGKILL + to all remaining ones, wait for their termination + again, and exit. This will lead to recovery (by replaying the WAL log) upon next start-up. This is recommended only in emergencies. Note that the previous text said that postmaster will send SIGQUIT, then terminate without checking anything. In the new code, postmaster sends SIGQUIT, then waits, then SIGKILL, then waits again. If there's an unkillable process (say because it's stuck in a noninterruptible sleep) postmaster might never exit. I think it should send SIGQUIT, then wait, then SIGKILL, then exit without checking. I have tweaked the patch a bit and I'm about to commit as soon as we resolve the above two items. -- Álvaro Herrerahttp://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] Git-master regression failure
On Wed, 2013-06-19 at 14:50 -0700, Jeff Janes wrote: > Is there an infrastructure to use a different expected file depending > on the LANG used? Not really. A couple of years ago I did the same exercise you just did, and we just fixed most of what was reasonable to fix by adjusting the test cases. -- 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_resetxlog -m documentation not up to date
Peter Eisentraut wrote: > Ping. This ought to be fixed before 9.3 goes out. Will fix. > On Sat, 2013-04-27 at 21:22 -0400, Peter Eisentraut wrote: > > The pg_resetxlog -m option was changed from -- Álvaro Herrerahttp://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_resetxlog -m documentation not up to date
Ping. This ought to be fixed before 9.3 goes out. On Sat, 2013-04-27 at 21:22 -0400, Peter Eisentraut wrote: > The pg_resetxlog -m option was changed from > > -m XID set next multitransaction ID > > to > > -m XID,OLDESTset next multitransaction ID and oldest value > > but the man page does not reflect that change. > > It was introduced in 0ac5ad5134f2769ccbaefec73844f8504c4d6182 "Improve > concurrency of foreign key locking", but there is also no explanation > there. It is apparently needed in pg_upgrade. > > This should be documented, and I think the help line should be changed > to something like > > -m XID,XID set next and oldest multitransaction ID > > > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] warn_unused_result in pgbench
When building without thread safety, I get the following compiler warning in pgbench: pgbench.c:2612:2: error: ignoring return value of function declared with warn_unused_result attribute [-Werror,-Wunused-result] write(th->pipes[1], ret, sizeof(TResult)); That should be fixed, I think. -- 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] Implementing incremental backup
> On Wed, Jun 19, 2013 at 8:40 PM, Tatsuo Ishii wrote: >>> On Wed, Jun 19, 2013 at 6:20 PM, Stephen Frost wrote: * Claudio Freire (klaussfre...@gmail.com) wrote: > I don't see how this is better than snapshotting at the filesystem > level. I have no experience with TB scale databases (I've been limited > to only hundreds of GB), but from my limited mid-size db experience, > filesystem snapshotting is pretty much the same thing you propose > there (xfs_freeze), and it works pretty well. There's even automated > tools to do that, like bacula, and they can handle incremental > snapshots. Large databases tend to have multiple filesystems and getting a single, consistent, snapshot across all of them while under load is.. 'challenging'. It's fine if you use pg_start/stop_backup() and you're saving the XLOGs off, but if you can't do that.. >>> >>> Good point there. >>> >>> I still don't like the idea of having to mark each modified page. The >>> WAL compressor idea sounds a lot more workable. As in scalable. >> >> Why do you think WAL compressor idea is more scalable? I really want >> to know why. Besides the unlogged tables issue, I can accept the idea >> if WAL based solution is much more efficient. If there's no perfect, >> ideal solution, we need to prioritize things. My #1 priority is >> allowing to create incremental backup against TB database, and the >> backup file should be small enough and the time to create it is >> acceptable. I just don't know why scanning WAL stream is much cheaper >> than recording modified page information. > > Because it aggregates updates. > > When you work at the storage manager level, you only see block-sized > operations. This results in the need to WAL-log bit-sized updates on > some hypothetical dirty-map index. Even when done 100% efficiently, > this implies at least one write per dirtied block, which could as much > as double write I/O in the worse (and totally expectable) case. > > When you do it at WAL segment recycle time, or better yet during > checkpoints, you deal with checkpoint-scale operations. You can > aggregate dirty-map updates, if you keep a dirty-map, which could not > only reduce I/O considerably (by a much increased likelihood of write > coalescence), but also let you schedule it better (toss it in the > background, with checkpoints). This is for gathering dirty-map > updates, which still leaves you with the complex problem of then > actually snapshotting those pages consistently without interfering > with ongoing transactions. > > If you do a WAL compressor, WAL entries are write-once, so you'll have > no trouble snapshotting those pages. You have the checkpoint's initial > full page write, so you don't even have to read the page, and you can > accumulate all further partial writes into one full page write, and > dump that on an "incremental archive". So, you get all the I/O > aggregation from above, which reduces I/O to the point where it only > doubles WAL I/O. It's bound by a constant, and in contrast to > dirty-map updates, it's sequential I/O so it's a lot faster. It's thus > perfectly scalable. > > Not only that, but you're also amortizing incremental backup costs > over time, as you're making them constantly as opposed to regular > intervals. You'll have one incremental backup per checkpoint. If you > want to coalesce backups, you launch another compressor to merge the > last incremental checkpoint with the new one. And, now this is the > cherry on top, you only have to do this on the archived WALs, which > means you could very well do it on another system, freeing your main > cluster from all this I/O. It's thus perfectly scalable. > > The only bottleneck here, is WAL archiving. This assumes you can > afford WAL archiving at least to a local filesystem, and that the WAL > compressor is able to cope with WAL bandwidth. But I have no reason to > think you'd be able to cope with dirty-map updates anyway if you were > saturating the WAL compressor, as the compressor is more efficient on > amortized cost per transaction than the dirty-map approach. Thank you for detailed explanation. I will think more about this. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] slightly confusing JSON error context
These are some cases from the regression tests: SELECT ''::json;-- ERROR, no value ERROR: invalid input syntax for type json LINE 1: SELECT ''::json; ^ DETAIL: The input string ended unexpectedly. CONTEXT: JSON data, line 1: SELECT ''::json;-- ERROR, no value ERROR: invalid input syntax for type json LINE 1: SELECT ''::json; ^ DETAIL: The input string ended unexpectedly. CONTEXT: JSON data, line 1: Perhaps we should either put some quotes around the whitespace at the end of the CONTEXT lines, or write something like "end of input". Otherwise this might look weird. There might be more complicated cases where it looks even weirder. -- 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] event trigger API documentation?
Random observation in this general area: Regular triggers provide the field Trigger *tg_trigger in the trigger data, which allows you to get the trigger name, OID, and such. Event triggers don't expose anything comparable. That should perhaps be added. Also, as I'm maybe about the fourth person ever to write an actual event trigger, I have a usability report of sorts. I found it confusing that the trigger timing is encoded into the event name. So instead of ON ddl_command_start, I was expecting something more like BEFORE ddl_command. There might be a reason for this design choice, but I found it a confusing departure from known trigger concepts. -- 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] Implementing incremental backup
On Wed, Jun 19, 2013 at 8:40 PM, Tatsuo Ishii wrote: >> On Wed, Jun 19, 2013 at 6:20 PM, Stephen Frost wrote: >>> * Claudio Freire (klaussfre...@gmail.com) wrote: I don't see how this is better than snapshotting at the filesystem level. I have no experience with TB scale databases (I've been limited to only hundreds of GB), but from my limited mid-size db experience, filesystem snapshotting is pretty much the same thing you propose there (xfs_freeze), and it works pretty well. There's even automated tools to do that, like bacula, and they can handle incremental snapshots. >>> >>> Large databases tend to have multiple filesystems and getting a single, >>> consistent, snapshot across all of them while under load is.. >>> 'challenging'. It's fine if you use pg_start/stop_backup() and you're >>> saving the XLOGs off, but if you can't do that.. >> >> Good point there. >> >> I still don't like the idea of having to mark each modified page. The >> WAL compressor idea sounds a lot more workable. As in scalable. > > Why do you think WAL compressor idea is more scalable? I really want > to know why. Besides the unlogged tables issue, I can accept the idea > if WAL based solution is much more efficient. If there's no perfect, > ideal solution, we need to prioritize things. My #1 priority is > allowing to create incremental backup against TB database, and the > backup file should be small enough and the time to create it is > acceptable. I just don't know why scanning WAL stream is much cheaper > than recording modified page information. Because it aggregates updates. When you work at the storage manager level, you only see block-sized operations. This results in the need to WAL-log bit-sized updates on some hypothetical dirty-map index. Even when done 100% efficiently, this implies at least one write per dirtied block, which could as much as double write I/O in the worse (and totally expectable) case. When you do it at WAL segment recycle time, or better yet during checkpoints, you deal with checkpoint-scale operations. You can aggregate dirty-map updates, if you keep a dirty-map, which could not only reduce I/O considerably (by a much increased likelihood of write coalescence), but also let you schedule it better (toss it in the background, with checkpoints). This is for gathering dirty-map updates, which still leaves you with the complex problem of then actually snapshotting those pages consistently without interfering with ongoing transactions. If you do a WAL compressor, WAL entries are write-once, so you'll have no trouble snapshotting those pages. You have the checkpoint's initial full page write, so you don't even have to read the page, and you can accumulate all further partial writes into one full page write, and dump that on an "incremental archive". So, you get all the I/O aggregation from above, which reduces I/O to the point where it only doubles WAL I/O. It's bound by a constant, and in contrast to dirty-map updates, it's sequential I/O so it's a lot faster. It's thus perfectly scalable. Not only that, but you're also amortizing incremental backup costs over time, as you're making them constantly as opposed to regular intervals. You'll have one incremental backup per checkpoint. If you want to coalesce backups, you launch another compressor to merge the last incremental checkpoint with the new one. And, now this is the cherry on top, you only have to do this on the archived WALs, which means you could very well do it on another system, freeing your main cluster from all this I/O. It's thus perfectly scalable. The only bottleneck here, is WAL archiving. This assumes you can afford WAL archiving at least to a local filesystem, and that the WAL compressor is able to cope with WAL bandwidth. But I have no reason to think you'd be able to cope with dirty-map updates anyway if you were saturating the WAL compressor, as the compressor is more efficient on amortized cost per transaction than the dirty-map approach. -- 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] Implementing incremental backup
* Tatsuo Ishii (is...@postgresql.org) wrote: > Why do you think WAL compressor idea is more scalable? I really want > to know why. Besides the unlogged tables issue, I can accept the idea > if WAL based solution is much more efficient. If there's no perfect, > ideal solution, we need to prioritize things. My #1 priority is > allowing to create incremental backup against TB database, and the > backup file should be small enough and the time to create it is > acceptable. I just don't know why scanning WAL stream is much cheaper > than recording modified page information. Because that's what the WAL *is*..? Why would you track what's changed twice? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Implementing incremental backup
* Tatsuo Ishii (is...@postgresql.org) wrote: > I don't think using rsync (or tar or whatever general file utils) > against TB database for incremental backup is practical. If it's > practical, I would never propose my idea. You could use rsync for incremental updates if you wanted, it'd certainly be faster in some cases and it's entirely possible to use such against TB databases in some cases. > > They're not WAL'd, so expecting them to work when restoring a backup of > > a PG that had been running at the time of the backup is folly. > > Probably you forget about our nice pg_dump tool:-) I don't consider pg_dump a mechanism for backing up TB databases. You're certainly welcome to use it for dumping unlogged tables, but I can't support the notion that unlogged tables should be supported through WAL-supported file-based backups. If we're going down this road, I'd much rather see support for exporting whole files from and importing them back into PG in some way which completely avoids the need to re-parse or re-validate data and supports pulling in indexes as part of the import. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Implementing incremental backup
> On Wed, Jun 19, 2013 at 6:20 PM, Stephen Frost wrote: >> * Claudio Freire (klaussfre...@gmail.com) wrote: >>> I don't see how this is better than snapshotting at the filesystem >>> level. I have no experience with TB scale databases (I've been limited >>> to only hundreds of GB), but from my limited mid-size db experience, >>> filesystem snapshotting is pretty much the same thing you propose >>> there (xfs_freeze), and it works pretty well. There's even automated >>> tools to do that, like bacula, and they can handle incremental >>> snapshots. >> >> Large databases tend to have multiple filesystems and getting a single, >> consistent, snapshot across all of them while under load is.. >> 'challenging'. It's fine if you use pg_start/stop_backup() and you're >> saving the XLOGs off, but if you can't do that.. > > Good point there. > > I still don't like the idea of having to mark each modified page. The > WAL compressor idea sounds a lot more workable. As in scalable. Why do you think WAL compressor idea is more scalable? I really want to know why. Besides the unlogged tables issue, I can accept the idea if WAL based solution is much more efficient. If there's no perfect, ideal solution, we need to prioritize things. My #1 priority is allowing to create incremental backup against TB database, and the backup file should be small enough and the time to create it is acceptable. I just don't know why scanning WAL stream is much cheaper than recording modified page information. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.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] Implementing incremental backup
> * Tatsuo Ishii (is...@postgresql.org) wrote: >> > * Tatsuo Ishii (is...@postgresql.org) wrote: >> >> Yeah, at first I thought using WAL was a good idea. However I realized >> >> that the problem using WAL is we cannot backup unlogged tables because >> >> they are not written to WAL. >> > >> > Unlogged tables are also nuked on recovery, so I'm not sure why you >> > think an incremental backup would help.. If you're recovering (even >> > from a simple crash), unlogged tables are going to go away. >> >> If my memory serves, unlogged tables are not nuked when PostgeSQL is >> stopped by a planned shutdown (not by crash or by "pg_ctl -m i >> stop"). If PostgreSQL works so, incremental backup should be able to >> recover unlogged tables as well, at least people would expect so IMO. > > Sure, if you shut down PG, rsync the entire thing and then bring it back > up then unlogged tables should work when "backed up". I don't think using rsync (or tar or whatever general file utils) against TB database for incremental backup is practical. If it's practical, I would never propose my idea. > They're not WAL'd, so expecting them to work when restoring a backup of > a PG that had been running at the time of the backup is folly. Probably you forget about our nice pg_dump tool:-) -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.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] Implementing incremental backup
* Tatsuo Ishii (is...@postgresql.org) wrote: > > * Tatsuo Ishii (is...@postgresql.org) wrote: > >> Yeah, at first I thought using WAL was a good idea. However I realized > >> that the problem using WAL is we cannot backup unlogged tables because > >> they are not written to WAL. > > > > Unlogged tables are also nuked on recovery, so I'm not sure why you > > think an incremental backup would help.. If you're recovering (even > > from a simple crash), unlogged tables are going to go away. > > If my memory serves, unlogged tables are not nuked when PostgeSQL is > stopped by a planned shutdown (not by crash or by "pg_ctl -m i > stop"). If PostgreSQL works so, incremental backup should be able to > recover unlogged tables as well, at least people would expect so IMO. Sure, if you shut down PG, rsync the entire thing and then bring it back up then unlogged tables should work when "backed up". They're not WAL'd, so expecting them to work when restoring a backup of a PG that had been running at the time of the backup is folly. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] SET work_mem = '1TB';
On Wed, Jun 19, 2013 at 4:47 PM, Simon Riggs wrote: > On 18 June 2013 22:57, Fujii Masao wrote: >> On Wed, Jun 19, 2013 at 2:40 AM, Simon Riggs wrote: >>> On 18 June 2013 17:10, Fujii Masao wrote: On Tue, Jun 18, 2013 at 1:06 PM, Jeff Janes wrote: > On Tuesday, May 21, 2013, Simon Riggs wrote: >> >> I worked up a small patch to support Terabyte setting for memory. >> Which is OK, but it only works for 1TB, not for 2TB or above. > > > I've incorporated my review into a new version, attached. > > Added "TB" to the docs, added the macro KB_PER_TB, and made "show" to > print > "1TB" rather than "1024GB". Looks good to me. But I found you forgot to change postgresql.conf.sample, so I changed it and attached the updated version of the patch. Barring any objection to this patch and if no one picks up this, I will commit this. >>> >>> In truth, I hadn't realised somebody had added this to the CF. It was >>> meant to be an exploration and demonstration that further work was/is >>> required rather than a production quality submission. AFAICS it is >>> still limited to '1 TB' only... >> >> Yes. >> >>> Thank you both for adding to this patch. Since you've done that, it >>> seems churlish of me to interrupt that commit. >> >> I was thinking that this is the infrastructure patch for your future >> proposal, i.e., support higher values of TBs. But if it interferes with >> your future proposal, of course I'm okay to drop this patch. Thought? > > Yes, please commit. Committed. Regards, -- Fujii Masao -- 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] Implementing incremental backup
> * Tatsuo Ishii (is...@postgresql.org) wrote: >> Yeah, at first I thought using WAL was a good idea. However I realized >> that the problem using WAL is we cannot backup unlogged tables because >> they are not written to WAL. > > Unlogged tables are also nuked on recovery, so I'm not sure why you > think an incremental backup would help.. If you're recovering (even > from a simple crash), unlogged tables are going to go away. If my memory serves, unlogged tables are not nuked when PostgeSQL is stopped by a planned shutdown (not by crash or by "pg_ctl -m i stop"). If PostgreSQL works so, incremental backup should be able to recover unlogged tables as well, at least people would expect so IMO. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.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] Implementing incremental backup
* Tatsuo Ishii (is...@postgresql.org) wrote: > Yeah, at first I thought using WAL was a good idea. However I realized > that the problem using WAL is we cannot backup unlogged tables because > they are not written to WAL. Unlogged tables are also nuked on recovery, so I'm not sure why you think an incremental backup would help.. If you're recovering (even from a simple crash), unlogged tables are going to go away. Put simply, unlogged tables should not be used for any data you care about, period. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Implementing incremental backup
>>> I'm trying to figure out how that's actually different from WAL..? It >>> sounds like you'd get what you're suggesting with simply increasing the >>> checkpoint timeout until the WAL stream is something which you can keep >>> up with. Of course, the downside there is that you'd have to replay >>> more WAL when recovering. >> >> Yeah, at first I thought using WAL was a good idea. However I realized >> that the problem using WAL is we cannot backup unlogged tables because >> they are not written to WAL. > > How does replication handle that? > > Because I doubt that's an issue only with backups. Unlogged tables are not replicated to streaming replication standbys. It is clearly stated in the doc. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.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] Change authentication error message (patch)
On Wed, Jun 19, 2013 at 11:55 AM, Joshua D. Drake wrote: > > On 06/18/2013 02:25 AM, Markus Wanner wrote: > >> >> On 06/16/2013 06:02 PM, Joshua D. Drake wrote: >> >>> >>> >> How about: >> "password authentication failed or account expired for user \"%s\"" >> >> It's a bit longer, but sounds more like a full sentence, no? >> > > Yes but I don't think it is accurate, what about: > > "Authentication failed or password has expired for user \"%s\"" > I think we need to keep the first "password". "Password authentication" is a single thing, it is the authentication method attempted. It is the password method (which includes MD5) which failed, as opposed to the LDAP method or the Peer method or one of the other methods. Without this level of explicitness, it might be hard to figure out which row in pg_hba.conf was the one that PostgreSQL glommed onto to use for authentication. (Although by this argument, I don't know why MD5 doesn't get its own message specific to it, rather than sharing plain password) Cheers, Jeff
Re: [HACKERS] Implementing incremental backup
On Wed, Jun 19, 2013 at 7:39 PM, Tatsuo Ishii wrote: >>> I'm thinking of implementing an incremental backup tool for >>> PostgreSQL. The use case for the tool would be taking a backup of huge >>> database. For that size of database, pg_dump is too slow, even WAL >>> archive is too slow/ineffective as well. However even in a TB >>> database, sometimes actual modified blocks are not that big, may be >>> even several GB. So if we can backup those modified blocks only, >>> that would be an effective incremental backup method. >> >> I'm trying to figure out how that's actually different from WAL..? It >> sounds like you'd get what you're suggesting with simply increasing the >> checkpoint timeout until the WAL stream is something which you can keep >> up with. Of course, the downside there is that you'd have to replay >> more WAL when recovering. > > Yeah, at first I thought using WAL was a good idea. However I realized > that the problem using WAL is we cannot backup unlogged tables because > they are not written to WAL. How does replication handle that? Because I doubt that's an issue only with backups. -- 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] Implementing incremental backup
>> I'm thinking of implementing an incremental backup tool for >> PostgreSQL. The use case for the tool would be taking a backup of huge >> database. For that size of database, pg_dump is too slow, even WAL >> archive is too slow/ineffective as well. However even in a TB >> database, sometimes actual modified blocks are not that big, may be >> even several GB. So if we can backup those modified blocks only, >> that would be an effective incremental backup method. > > I'm trying to figure out how that's actually different from WAL..? It > sounds like you'd get what you're suggesting with simply increasing the > checkpoint timeout until the WAL stream is something which you can keep > up with. Of course, the downside there is that you'd have to replay > more WAL when recovering. Yeah, at first I thought using WAL was a good idea. However I realized that the problem using WAL is we cannot backup unlogged tables because they are not written to WAL. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.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] Implementing incremental backup
On Wed, Jun 19, 2013 at 7:18 PM, Alvaro Herrera wrote: > If you have the two technologies, you could teach them to work in > conjunction: you set up WAL replication, and tell the WAL compressor to > prune updates for high-update tables (avoid useless traffic), then use > incremental backup to back these up. This seems like it would have a > lot of moving parts and be rather bug-prone, though. I don't think it would be worse than storage-manager-level stuff. And though more complex, don't underestimate the pros: lower footprint, better scalability, and you get consistent online backups. That mechanism can also be used to distill a list of modified pages, mind you, instead of hooking into storage-manager stuff. The pro there, is that it wouldn't amplify writes. The con there is that you don't get consistent online backups. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
Josh Berkus writes: > True, but can you think of a better word to mean "don't edit this by hand"? The file name is not nearly as important for that as putting in a header comment "# Don't edit this file by hand." 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] Implementing incremental backup
Claudio Freire escribió: > On Wed, Jun 19, 2013 at 6:20 PM, Stephen Frost wrote: > > * Claudio Freire (klaussfre...@gmail.com) wrote: > >> I don't see how this is better than snapshotting at the filesystem > >> level. I have no experience with TB scale databases (I've been limited > >> to only hundreds of GB), but from my limited mid-size db experience, > >> filesystem snapshotting is pretty much the same thing you propose > >> there (xfs_freeze), and it works pretty well. There's even automated > >> tools to do that, like bacula, and they can handle incremental > >> snapshots. > > > > Large databases tend to have multiple filesystems and getting a single, > > consistent, snapshot across all of them while under load is.. > > 'challenging'. It's fine if you use pg_start/stop_backup() and you're > > saving the XLOGs off, but if you can't do that.. > > Good point there. > > I still don't like the idea of having to mark each modified page. The > WAL compressor idea sounds a lot more workable. As in scalable. There was a project that removed "useless" WAL records from the stream, to make it smaller and useful for long-term archiving. It only removed FPIs as far as I recall. It's dead now, and didn't compile on recent (9.1?) Postgres because of changes in the WAL structs, IIRC. This doesn't help if you have a large lot of UPDATEs that touch the same set of rows over and over, though. Tatsuo-san's proposal would allow this use-case to work nicely because you only keep one copy of such data, not one for each modification. If you have the two technologies, you could teach them to work in conjunction: you set up WAL replication, and tell the WAL compressor to prune updates for high-update tables (avoid useless traffic), then use incremental backup to back these up. This seems like it would have a lot of moving parts and be rather bug-prone, though. -- Álvaro Herrerahttp://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] Git-master regression failure
Jeff Janes wrote: > Kevin Grittner wrote: >> Peter Eisentraut wrote: >>> On 6/19/13 9:18 AM, Kevin Grittner wrote: >> >> Does anyone object to the attached change, so that regression tests pass when run in a Danish locale? I think it should be back-patched to 9.2, where the test was introduced. >>> >>> Yes, that should be fixed. I wouldn't put in the comment, though. A >>> few releases ago, I fixed a number of other "Danish" issues, so adding >>> this comment would give the impression that this the only place. >> >> OK, pushed without the comment. > > I had started this and let it run overnight: > > for LANG in `locale -a`; do make check >& /dev/null ; echo $? $LANG; done > > Of the 735 language/locales/encodings, I got 93 failures. Ouch! > After your commit I re-tested just the failures, and it fixed 25 > of them. That's more than I would have guessed. Cool. > Of the ones I looked at, most of the problems are in > create_index, some in matview as well. So of the 68 remaining locales which fail, most are due to a couple scripts. > Lithuanian has Y coming between I and J. > Estonian has Z between S and T. > Norwegian seems to treat V and W as being equal except to break suffix-ties. > Is there an infrastructure to use a different expected file > depending on the LANG used? Well, any one test can have alternative "expected" scripts; but in previous discussions we decided that that facility should not be used for locale issues. It would tend to get into multiplicative permutations with other reasons to have alternatives. What we have done is to try to create tests that don't hit those edge conditions when we know of them. Could you share your detailed information on the remaining failures? -- Kevin Grittner 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] Implementing incremental backup
On Wed, Jun 19, 2013 at 6:20 PM, Stephen Frost wrote: > * Claudio Freire (klaussfre...@gmail.com) wrote: >> I don't see how this is better than snapshotting at the filesystem >> level. I have no experience with TB scale databases (I've been limited >> to only hundreds of GB), but from my limited mid-size db experience, >> filesystem snapshotting is pretty much the same thing you propose >> there (xfs_freeze), and it works pretty well. There's even automated >> tools to do that, like bacula, and they can handle incremental >> snapshots. > > Large databases tend to have multiple filesystems and getting a single, > consistent, snapshot across all of them while under load is.. > 'challenging'. It's fine if you use pg_start/stop_backup() and you're > saving the XLOGs off, but if you can't do that.. Good point there. I still don't like the idea of having to mark each modified page. The WAL compressor idea sounds a lot more workable. As in scalable. -- 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] Vacuum/visibility is busted
On Thu, Feb 7, 2013 at 12:01 PM, Andres Freund wrote: > On 2013-02-07 11:15:46 -0800, Jeff Janes wrote: > > > > Does anyone have suggestions on how to hack the system to make it > > fast-forward the current transaction id? It would certainly make > > testing this kind of thing faster if I could make transaction id > > increment by 100 each time a new one is generated. Then wrap-around > > could be approached in minutes rather than hours. > > I had various plpgsql functions to do that, but those still took quite > some time. As I needed it before I just spent some minutes hacking up a > contrib module to do the job. > Hi Andres, Your patch needs the file "xidfuncs--1.0.sql", but does not include it. I could probably guess what needs to be in that file, but do you still have a copy of it? > > I doubt it really think it makes sense as a contrib module on its own > though? > Maybe PGXN? Cheers, Jeff
Re: [HACKERS] Git-master regression failure
On Wed, Jun 19, 2013 at 8:41 AM, Kevin Grittner wrote: > Peter Eisentraut wrote: > > On 6/19/13 9:18 AM, Kevin Grittner wrote: > > >> Does anyone object to the attached change, so that regression tests > >> pass when run in a Danish locale? I think it should be > >> back-patched to 9.2, where the test was introduced. > > > > Yes, that should be fixed. I wouldn't put in the comment, though. A > > few releases ago, I fixed a number of other "Danish" issues, so adding > > this comment would give the impression that this the only place. > > OK, pushed without the comment. > I had started this and let it run overnight: for LANG in `locale -a`; do make check >& /dev/null ; echo $? $LANG; done Of the 735 language/locales/encodings, I got 93 failures. After your commit I re-tested just the failures, and it fixed 25 of them. Of the ones I looked at, most of the problems are in create_index, some in matview as well. Lithuanian has Y coming between I and J. Estonian has Z between S and T. Norwegian seems to treat V and W as being equal except to break suffix-ties. Is there an infrastructure to use a different expected file depending on the LANG used? Cheers, Jeff
Re: [HACKERS] Implementing incremental backup
* Claudio Freire (klaussfre...@gmail.com) wrote: > I don't see how this is better than snapshotting at the filesystem > level. I have no experience with TB scale databases (I've been limited > to only hundreds of GB), but from my limited mid-size db experience, > filesystem snapshotting is pretty much the same thing you propose > there (xfs_freeze), and it works pretty well. There's even automated > tools to do that, like bacula, and they can handle incremental > snapshots. Large databases tend to have multiple filesystems and getting a single, consistent, snapshot across all of them while under load is.. 'challenging'. It's fine if you use pg_start/stop_backup() and you're saving the XLOGs off, but if you can't do that.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCH] Remove useless USE_PGXS support in contrib
Le mercredi 19 juin 2013 22:22:22, Andrew Dunstan a écrit : > On 06/19/2013 03:52 PM, Dimitri Fontaine wrote: > > Peter Eisentraut writes: > >> We could do something like > >> > >> PG_CONFIG = fake_intree_pg_config > >> PGXS := $(shell $(PG_CONFIG) --pgxs) > >> include $(PGXS) > > > > There's something to that idea. Of course we would need to offer a > > comment about the PG_CONFIG game and propose something else for real > > world extensions (PG_CONFIG ?= pg_config). > > > >> where fake_intree_pg_config is a purpose-built shell script that points > >> to the right places inside the source tree. > > > > If that works, that gets my preference over removing PGXS support in > > contrib modules. Setting an example is important, in-tree build is not > > a useful example for anyone but contributors to core. > > Not true - you're forgetting there is no pgxs for MSVC builds. PGXS + MSVC is still in the TODO list I won't be able to work on that. > If we're going to enable building of contrib modules using pgxs but > without an install we will make targets for that, and buildfarm support. With the set of patches I sent, contrib can be built with PGXS, there is no issue hereExcept maybe pg_xlogdump, and this one might be improved not to have to rebuild shared object from postgresql (IIRC it is a static build or something like that)... -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] [PATCH] Remove useless USE_PGXS support in contrib
On 06/19/2013 04:47 PM, Dimitri Fontaine wrote: Andrew Dunstan writes: Not true - you're forgetting there is no pgxs for MSVC builds. Oh, indeed, totally forgot about that. If we're going to enable building of contrib modules using pgxs but without an install we will make targets for that, and buildfarm support. So we would need some kind of windows only pgxs.pl script? Well, MSVC support and buildfarm support are two separate issues. For out of tree MSVC builds we would probably need to install a substantial part of src/tools/msvc, and have a script that could generate a Project and so on. I'm happy to help but it probably needs someone with a deeper knowledge than I have of the internal workings of MSVC project files to build it. It's worth doing - I know some people who find this a major barrier. 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] Git-master regression failure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 On 19-06-2013 17:41, Kevin Grittner wrote: > > OK, pushed without the comment. > Works like a charm :) Svenne -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.20 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQI5BAEBCAAjBQJRwhpmHBpodHRwOi8vc3Zlbm5lLmRrL3BncC9wb2xpY3kACgkQ /zLSj+olL/LBzRAAje+BsZ6eGbNEXwPWPob0sJwAD+51UNT24DMHCLAg4T38Bheo HaT+xSQm3hlIIjL/JF5BLiRFG4Fz1+GGOGbykKRW1Fm+VWzw6934lajbh/Z7wqzO T+n0XXwhbMnA4O++FvIJ67C7iazsNbAobOmqwjPe+6D6ID8VR9rNB/NEwelueR3L dVmVrSg1Mc8DVHS2h7Aq4bJaeCJHOQstB89+AEa14cwsJ25PiQFcdbUWsNNqbnoE TN3jQlXZLAl/qCzdKQ7FRAsi7Yk1r9uU7g1HNSY7ggh+W5KmRNqX99LS4v5mO6rf XquzXNf6nzl3ngfucB+60C2NloqieYKGVbzDh7LjCThbv284/fdlW+Wu80vYGcto TWIb5Ku4fbKP0Hmcuby1VlcYyZhyiBa76zV7JF5AtMe9yfCpOdmyAUdHDQUq6T6c /BVV/bCVsDAhJN/AJQC2itNnt4G+Bw62YEQCwuw1cNT5BUzjf0mbcer5kRchkNr3 lBdTEp4HBzElBDnCY7oSre+aSKomgNZwyJsps2aLuyz8nTj4WxpDQTDo1m7Kyi7K dFGRZLJRy5FS0xNrVtBW1cfoYy8B2GM9cmcZru5bFo2XAJfJaSSl4EZLdlIk5k66 k+VkwS5XoIl7oR/qT1fSwERM8TfDS2An2rr+jvVAATm2QQsKL42AN7aSROk= =2COi -END PGP SIGNATURE- -- 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] FP precision
Sorry folks. That email was misdirected by accident. -- 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] Bugfix and new feature for PGXS
Le mercredi 19 juin 2013 21:06:23, Alvaro Herrera a écrit : > Peter Eisentraut wrote: > > On 6/19/13 12:20 PM, Andrew Dunstan wrote: > > > So you're saying to install extension headers, but into the main > > > directory where we put server headers? > > > > Yes, if we choose to install some extension headers, that is where we > > should put them. > > The question of the name of the directory still stands. "contrib" would > be the easiest answer, but it's slightly wrong because > externally-supplied modules could also want to install headers. > "extension" might be it, but there are things that aren't extensions > (right? if not, that would be my choice). yes, I think the same. auth_delay for example is not an extension as in CREATE EXTENSION. So...it is probably better to postpone this decision and keep on the idea to just install headers where there should be will traditional name (contrib). -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] [PATCH] Remove useless USE_PGXS support in contrib
Andrew Dunstan writes: > Not true - you're forgetting there is no pgxs for MSVC builds. Oh, indeed, totally forgot about that. > If we're going to enable building of contrib modules using pgxs but without > an install we will make targets for that, and buildfarm support. So we would need some kind of windows only pgxs.pl script? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Change authentication error message (patch)
On 06/19/2013 01:18 PM, Markus Wanner wrote: "Authentication failed or password has expired for user \"%s\"" Authentication failed covers any combination of a username/password being wrong and obviously password expired covers the other. Works for me. Considering the password to be the thing that expires (rather than the account) is probably more accurate as well. It is also how it is worded in the docs (which is why I used it). Patch below. JD diff --git a/src/backend/libpq/auth.c b/src/backend/libpq/auth.c index 415b614..f129fe1 100644 --- a/src/backend/libpq/auth.c +++ b/src/backend/libpq/auth.c @@ -270,7 +270,7 @@ auth_failed(Port *port, int status) break; case uaPassword: case uaMD5: - errstr = gettext_noop("password authentication failed for user \"%s\""); + errstr = gettext_noop("Authentication failed or password has expired for user \"%s\""); /* We use it to indicate if a .pgpass password failed. */ errcode_return = ERRCODE_INVALID_PASSWORD; break; -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] FP precision
test=# select '100'::real + '5'::real; ?column? -- 1e+06 (1 row) test=# select '100'::real + '6'::real; ?column? - 1.1e+06 (1 row) test=# select '0.1'::real + 0.0; ?column? --- 0.10001490116 (1 row) -- 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] Remove useless USE_PGXS support in contrib
On 06/19/2013 03:52 PM, Dimitri Fontaine wrote: Peter Eisentraut writes: We could do something like PG_CONFIG = fake_intree_pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) There's something to that idea. Of course we would need to offer a comment about the PG_CONFIG game and propose something else for real world extensions (PG_CONFIG ?= pg_config). where fake_intree_pg_config is a purpose-built shell script that points to the right places inside the source tree. If that works, that gets my preference over removing PGXS support in contrib modules. Setting an example is important, in-tree build is not a useful example for anyone but contributors to core. Not true - you're forgetting there is no pgxs for MSVC builds. If we're going to enable building of contrib modules using pgxs but without an install we will make targets for that, and buildfarm support. 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] Change authentication error message (patch)
This probably is nit-picking, but it interests me in terms of how the language is used and understood. On 06/19/2013 08:55 PM, Joshua D. Drake wrote: > I believe it actually can. The error message that is returned for a bad > password, bad user or expired password is all the same. Which is why I > put the username in there. Sure, the authentication can fail for all these reasons. What I stumbled over was the formulation of a "failed username". If an engine fails, it might literally fall apart. The username itself - even if it doesn't pass authentication - is not falling apart in the same sense. But does the username (or the password) fail if authentication with it (in combination with password and account expiration time) is not possible? After all, it might still a valid and complete username for another cluster or another service. You can probably say: "that username failed" when you actually mean it "failed to authenticate together with the provided password". Or how do English native speakers perceive this? > "Authentication failed or password has expired for user \"%s\"" > > Authentication failed covers any combination of a username/password > being wrong and obviously password expired covers the other. Works for me. Considering the password to be the thing that expires (rather than the account) is probably more accurate as well. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] add --throttle to pgbench (submission 3)
Because you may want to put something very readable and understandable in a script and like long options, or have to type it interactively every day in a terminal and like short ones. Most UNIX commands include both kind. Would it make sense then to add long versions for all the other standard options too? Yep. It is really a stylistic (pedantic?) matter. See for pgbench: https://commitfest.postgresql.org/action/patch_view?id=1106 -- Fabien. -- 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] Remove useless USE_PGXS support in contrib
Peter Eisentraut writes: > We could do something like > > PG_CONFIG = fake_intree_pg_config > PGXS := $(shell $(PG_CONFIG) --pgxs) > include $(PGXS) There's something to that idea. Of course we would need to offer a comment about the PG_CONFIG game and propose something else for real world extensions (PG_CONFIG ?= pg_config). > where fake_intree_pg_config is a purpose-built shell script that points > to the right places inside the source tree. If that works, that gets my preference over removing PGXS support in contrib modules. Setting an example is important, in-tree build is not a useful example for anyone but contributors to core. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Implementing incremental backup
On Wed, Jun 19, 2013 at 3:54 PM, Jim Nasby wrote: > On 6/19/13 11:02 AM, Claudio Freire wrote: >> >> On Wed, Jun 19, 2013 at 7:13 AM, Tatsuo Ishii >> wrote: >>> >>> >>> For now, my idea is pretty vague. >>> >>> - Record info about modified blocks. We don't need to remember the >>>whole history of a block if the block was modified multiple times. >>>We just remember that the block was modified since the last >>>incremental backup was taken. >>> >>> - The info could be obtained by trapping calls to mdwrite() etc. We need >>>to be careful to avoid such blocks used in xlogs and temporary >>>tables to not waste resource. >>> >>> - If many blocks were modified in a file, we may be able to condense >>>the info as "the whole file was modified" to reduce the amount of >>>info. >>> >>> - How to take a consistent incremental backup is an issue. I can't >>>think of a clean way other than "locking whole cluster", which is >>>obviously unacceptable. Maybe we should give up "hot backup"? >> >> >> >> I don't see how this is better than snapshotting at the filesystem >> level. I have no experience with TB scale databases (I've been limited >> to only hundreds of GB), but from my limited mid-size db experience, >> filesystem snapshotting is pretty much the same thing you propose >> there (xfs_freeze), and it works pretty well. There's even automated >> tools to do that, like bacula, and they can handle incremental >> snapshots. > > > A snapshot is not the same as an incremental backup; it presents itself as a > full copy of the filesystem. Actually, since it's on the same underlying > storage a snapshot isn't really a good backup at all. Read on bacula[0], which is huge and thus this info may be hard to find, you can take that snapshot, which will be on the same filesystem of course, and *then* back it up. So you get a consistent snapshot on your backup, which means a correct backup, and the backup certainly doesn't have to be on the same filesystem. It even works for ext3 if you install the right kernel modules. Yes, it's a snapshot of the entire filesystem. So it's not the same as a database-only backup. But it does have a huge overlap don't you think? When WAL archiving can get you PITR, and bacula-like tools can get you incremental and consistent full-FS-snapshot backups, what does the proposed feature add? I don't think you can get PITR with the proposed feature, as it takes a snapshot only when told to, and it can't take multiple snapshots. The only way to get PITR AFAIK is with WAL archiving, so whether it's viable or not for TB-sized databases is moot, if it's the only option. And it will add an overhead. A considerable overhead. Even if you only have to flip a bit on some page map, it amplifies writes twofold (unless writes can be coalesced, of which there is no guarantee). In the end, it may be preferrable to just alter PG's behavior slightly to make bacula, rsync or whichever tool's job easier. Like trying hard not to write to cold segments, so entire segments can be skipped by quick mtime checks. [0] http://www.bacula.org/en/ -- 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 cosmetic problem while dumping/restoring rules
> This has been fixed by Joe Conway meanwhile. > Nice, thaks!
Re: [HACKERS] Bugfix and new feature for PGXS
Peter Eisentraut wrote: > On 6/19/13 12:20 PM, Andrew Dunstan wrote: > > So you're saying to install extension headers, but into the main > > directory where we put server headers? > > Yes, if we choose to install some extension headers, that is where we > should put them. The question of the name of the directory still stands. "contrib" would be the easiest answer, but it's slightly wrong because externally-supplied modules could also want to install headers. "extension" might be it, but there are things that aren't extensions (right? if not, that would be my choice). -- Álvaro Herrerahttp://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] Change authentication error message (patch)
On 06/18/2013 02:25 AM, Markus Wanner wrote: On 06/16/2013 06:02 PM, Joshua D. Drake wrote: Instead of pushing extra info to the logs I decided that we could without giving away extra details per policy. I wrote the error message in a way that tells the most obvious problems, without admitting to any of them. Please see attached: +1 for solving this with a bit of word-smithing. However, the proposed wording doesn't sound like a full sentence to my ears, because a password or username cannot fail per-se. I believe it actually can. The error message that is returned for a bad password, bad user or expired password is all the same. Which is why I put the username in there. How about: "password authentication failed or account expired for user \"%s\"" It's a bit longer, but sounds more like a full sentence, no? Yes but I don't think it is accurate, what about: "Authentication failed or password has expired for user \"%s\"" Authentication failed covers any combination of a username/password being wrong and obviously password expired covers the other. Sincerely, Joshua D. Drake Regards Markus Wanner -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- 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] How do we track backpatches?
Josh Berkus wrote: > > > I'd imagine having a "CF" entry per release, so after a set of minor > > releases, the "CF" is closed. > > How would we name these? > > Also, what about patches for beta? Should we have a "beta" CF? Don't we have the Open Items wiki page for those? Seems to work well enough. -- Álvaro Herrerahttp://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] [PATCH] Add transforms feature
On 6/17/13 5:31 PM, Alvaro Herrera wrote: > This is a large patch. Do you intend to push the whole thing as a > single commit, or split it? I thought about splitting it up, but I didn't find a reasonable way to do it. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
Magnus Hagander wrote: > On Jun 19, 2013 7:55 PM, "Peter Eisentraut" wrote: > > generated_by_server.conf > > System.conf? alter_system.conf ? -- Álvaro Herrerahttp://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] Bad error message on valuntil
On 06/19/2013 08:24 AM, Peter Eisentraut wrote: I think it's intentional that we don't tell the *client* that level of detail. I could see emitting a log message about it, but it's not clear whether that will help an unsophisticated user. Usually, when I log in somewhere and the password is expired, it tells me that the password is expired. I don't think we gain anything by hiding that from the user. FTR: there is an actual patch for this sitting over at the, "Change authentication error message" thread. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- 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] Bugfix and new feature for PGXS
Le mercredi 19 juin 2013 18:48:21, Andrew Dunstan a écrit : > On 06/19/2013 12:32 PM, Cédric Villemain wrote: > > Le mercredi 19 juin 2013 18:20:11, Andrew Dunstan a écrit : > >> On 06/19/2013 11:26 AM, Peter Eisentraut wrote: > >>> On 6/19/13 10:19 AM, Andrew Dunstan wrote: > What are they going to be used for anyway? I rubbed up against this > not too long ago. Things will blow up if you use stuff from the > module and the module isn't already loaded. > >>> > >>> See transforms. > >> > >> So you're saying to install extension headers, but into the main > >> directory where we put server headers? > > > > At the same level than server headers, but I'm open for suggestion. > > > > $ tree -d include > > include > > ├── libpq > > └── postgresql > > > > ├── contrib > > │ └── hstore > > ├── informix > > │ └── esql > > ├── internal > > │ └── libpq > > └── server > > > > And all subidrs of server/ > > This is what Peter was arguing against, isn't it? Now I have a doubt :) I believe he answered the proposal to put all headers on the same flat directory, instead of a tree. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] How do we track backpatches?
On Wed, Jun 19, 2013 at 8:54 PM, Alvaro Herrera wrote: > Josh Berkus wrote: >> >> > I'd imagine having a "CF" entry per release, so after a set of minor >> > releases, the "CF" is closed. >> >> How would we name these? >> >> Also, what about patches for beta? Should we have a "beta" CF? > > Don't we have the Open Items wiki page for those? Seems to work well > enough. Yes. The CF app only tracks things that already have patches. For the beta, we really need to track things that may not have been fixed - or that may have been done, just only partially so far. -- 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] Implementing incremental backup
On 6/19/13 11:02 AM, Claudio Freire wrote: On Wed, Jun 19, 2013 at 7:13 AM, Tatsuo Ishii wrote: For now, my idea is pretty vague. - Record info about modified blocks. We don't need to remember the whole history of a block if the block was modified multiple times. We just remember that the block was modified since the last incremental backup was taken. - The info could be obtained by trapping calls to mdwrite() etc. We need to be careful to avoid such blocks used in xlogs and temporary tables to not waste resource. - If many blocks were modified in a file, we may be able to condense the info as "the whole file was modified" to reduce the amount of info. - How to take a consistent incremental backup is an issue. I can't think of a clean way other than "locking whole cluster", which is obviously unacceptable. Maybe we should give up "hot backup"? I don't see how this is better than snapshotting at the filesystem level. I have no experience with TB scale databases (I've been limited to only hundreds of GB), but from my limited mid-size db experience, filesystem snapshotting is pretty much the same thing you propose there (xfs_freeze), and it works pretty well. There's even automated tools to do that, like bacula, and they can handle incremental snapshots. A snapshot is not the same as an incremental backup; it presents itself as a full copy of the filesystem. Actually, since it's on the same underlying storage a snapshot isn't really a good backup at all. The proposal (at least as I read it) is to provide a means to easily deal with *only* the data that has actually *changed* since the last backup was taken. -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] add --throttle to pgbench (submission 3)
On 06/19/13 14:34, Fabien COELHO wrote: > >>> The use case of the option is to be able to generate a continuous gentle >>> load for functional tests, eg in a practice session with students or for >>> testing features on a laptop. >> >> Why does this need two option formats (-H and --throttle)? > > On the latest version it is --rate and -R. > > Because you may want to put something very readable and understandable in > a script and like long options, or have to type it interactively every day > in a terminal and like short ones. Most UNIX commands include both kind. > Would it make sense then to add long versions for all the other standard options too? Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- 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] pgbench --throttle (submission 7 - with lag measurement)
number of transactions actually processed: 301921 Just a thought before spending too much time on this subtle issue. The patch worked reasonnably for 301900 transactions in your above run, and the few last ones, less than the number of clients, show strange latency figures which suggest that something is amiss in some corner case when pgbench is stopping. However, the point of pgbench is to test a steady state, not to achieve the cleanest stop at the end of a run. So my question is: should this issue be a blocker wrt to the feature? -- Fabien. -- 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] add --throttle to pgbench (submission 3)
The use case of the option is to be able to generate a continuous gentle load for functional tests, eg in a practice session with students or for testing features on a laptop. Why does this need two option formats (-H and --throttle)? On the latest version it is --rate and -R. Because you may want to put something very readable and understandable in a script and like long options, or have to type it interactively every day in a terminal and like short ones. Most UNIX commands include both kind. -- Fabien. -- 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] Proposal for Allow postgresql.conf values to be changed via SQL [review]
On 6/7/13 12:14 AM, Amit Kapila wrote: > I will change the patch as per below syntax if there are no objections: > > ALTER SYSTEM SET configuration_parameter {TO | =} {value, | 'value'}; I do like using ALTER SYSTEM in general, but now that I think about it, the 9.3 feature to create global settings in pg_db_role_setting would also have been a candidate for exactly that same syntax if it had been available. In fact, if we do add ALTER SYSTEM, it might make sense to recast that feature into that syntax. It might be clearer to do something like ALTER SYSTEM SET EXTERNAL FILE or something like that. It's only a small syntax change, so don't worry about it too much, but let's keep thinking about it. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Jun 19, 2013 7:55 PM, "Peter Eisentraut" wrote: > > On 6/19/13 1:49 PM, Josh Berkus wrote: > > On 06/19/2013 10:48 AM, Peter Eisentraut wrote: > >> On 6/13/13 5:47 PM, Josh Berkus wrote: > 2. File name to store settings set by ALTER SYSTEM command is still > > persistent.auto.conf > >>> Why? Shouldn't it just be auto.conf? Or system.auto.conf? > >>> > >>> I prefer auto.conf, personally. > >> > >> Well, not much about it is automatic, really. It's just set elsewhere. > >> > > > > True, but can you think of a better word to mean "don't edit this by hand"? > > generated_by_server.conf System.conf? /Magnus
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
On 6/19/13 1:49 PM, Josh Berkus wrote: > On 06/19/2013 10:48 AM, Peter Eisentraut wrote: >> On 6/13/13 5:47 PM, Josh Berkus wrote: 2. File name to store settings set by ALTER SYSTEM command is still > persistent.auto.conf >>> Why? Shouldn't it just be auto.conf? Or system.auto.conf? >>> >>> I prefer auto.conf, personally. >> >> Well, not much about it is automatic, really. It's just set elsewhere. >> > > True, but can you think of a better word to mean "don't edit this by hand"? generated_by_server.conf -- 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] how to find out whether a view is updatable
On 19 June 2013 18:12, Peter Eisentraut wrote: > On 6/19/13 11:50 AM, Dean Rasheed wrote: >> On 19 June 2013 15:22, Peter Eisentraut wrote: >>> We still don't have any support for this in psql, do we? >>> >> >> No, but at least we now have an API that psql can use. >> >> There are still a number of questions about the best way to display it in >> psql. >> Should it be another column in \d+'s list of relations? >> Should it appear in \d+ for a single relation? >> Should it distinguish updatable from insertable and deletable? >> Should tab-completion also be modified? >> >> Currently I'm thinking yes, yes, no, yes. > > I would be satisfied with no, yes, no, no. Although I don't know what > tab completion changes you have in mind. > Yes, on reflection having an extra column in the list of relations is probably not a good idea. In many cases that's just going to be a list of tables, all of which will be updatable. So it would only be for \d+ on a single view or foreign table - simply: Updatable: yes|no Tab-completion was discussed on the original thread, but then I forgot about it: http://www.postgresql.org/message-id/caa-alv4_atxij7paqgvh73n5a0f-patvh5em-lmqu+ofuze...@mail.gmail.com Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
On 06/19/2013 10:48 AM, Peter Eisentraut wrote: > On 6/13/13 5:47 PM, Josh Berkus wrote: >>> 2. File name to store settings set by ALTER SYSTEM command is still persistent.auto.conf >> Why? Shouldn't it just be auto.conf? Or system.auto.conf? >> >> I prefer auto.conf, personally. > > Well, not much about it is automatic, really. It's just set elsewhere. > True, but can you think of a better word to mean "don't edit this by hand"? -- 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: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
On 6/13/13 5:47 PM, Josh Berkus wrote: >> 2. File name to store settings set by ALTER SYSTEM command is still >> > persistent.auto.conf > Why? Shouldn't it just be auto.conf? Or system.auto.conf? > > I prefer auto.conf, personally. Well, not much about it is automatic, really. It's just set elsewhere. -- 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 for fail-back without fresh backup
On Tuesday, June 18, 2013, Amit Kapila wrote: > On Tuesday, June 18, 2013 12:18 AM Sawada Masahiko wrote: > > On Sun, Jun 16, 2013 at 2:00 PM, Amit kapila > > > > > > wrote: > > > On Saturday, June 15, 2013 8:29 PM Sawada Masahiko wrote: > > > On Sat, Jun 15, 2013 at 10:34 PM, Amit kapila > > > wrote: > > >> > > >> On Saturday, June 15, 2013 1:19 PM Sawada Masahiko wrote: > > >> On Fri, Jun 14, 2013 at 10:15 PM, Amit Kapila > > > wrote: > > >>> On Friday, June 14, 2013 2:42 PM Samrat Revagade wrote: > > Hello, > > >>> > > >> We have already started a discussion on pgsql-hackers for the > > problem of > > >> taking fresh backup during the failback operation here is the > > link for that: > > >> > > >> > > >> http://www.postgresql.org/message-id/CAF8Q-Gxg3PQTf71NVECe- > > 6OzRaew5pWhk7yQtb > > >> jgwrfu513...@mail.gmail.com > > >> > > >> Let me again summarize the problem we are trying to address. > > >> > > >> > > > How will you take care of extra WAL on old master during > > recovery. If it > > > plays the WAL which has not reached new-master, it can be a > > problem. > > >> > > you means that there is possible that old master's data ahead of > > new > > master's data. > > >> > > >>> I mean to say is that WAL of old master can be ahead of new > > master. I understood that > > >>> data files of old master can't be ahead, but I think WAL can be > > ahead. > > >> > > so there is inconsistent data between those server when fail back. > > right? > > if so , there is not possible inconsistent. because if you use GUC > > option > > as his propose (i.g., failback_safe_standby_mode = remote_flush), > > when old master is working fine, all file system level changes > > aren't > > done before WAL replicated. > > >> > > >>> Would the propose patch will take care that old master's WAL is > > also not ahead in some way? > > >>> If yes, I think i am missing some point. > > > > > >> yes it will happen that old master's WAL ahead of new master's WAL > > as you said. > > >> but I think that we can solve them by delete all WAL file when old > > >> master starts as new standby. > > > > > > I think ideally, it should reset WAL location at the point where new > > master has forrked off. > > > In such a scenario it would be difficult for user who wants to get a > > dump of some data in > > > old master which hasn't gone to new master. I am not sure if such a > > need is there for real users, but if it > > > is there, then providing this solution will have some drawbacks. > > > I think that we can dumping data before all WAL files deleting. All > > WAL files deleting is done when old master starts as new standby. > > Can we dump data without starting server? > > Sorry I made a mistake. We can't it. this proposing patch need to be able to also handle such scenario in future. Regards, --- Sawada Masahiko -- Regards, --- Sawada Masahiko
Re: [HACKERS] Bugfix and new feature for PGXS
On 6/19/13 12:20 PM, Andrew Dunstan wrote: > So you're saying to install extension headers, but into the main > directory where we put server headers? Yes, if we choose to install some extension headers, that is where we should put them. -- 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] How do we track backpatches?
> I'd imagine having a "CF" entry per release, so after a set of minor > releases, the "CF" is closed. How would we name these? Also, what about patches for beta? Should we have a "beta" CF? -- 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] how to find out whether a view is updatable
On 6/19/13 11:50 AM, Dean Rasheed wrote: > On 19 June 2013 15:22, Peter Eisentraut wrote: >> We still don't have any support for this in psql, do we? >> > > No, but at least we now have an API that psql can use. > > There are still a number of questions about the best way to display it in > psql. > Should it be another column in \d+'s list of relations? > Should it appear in \d+ for a single relation? > Should it distinguish updatable from insertable and deletable? > Should tab-completion also be modified? > > Currently I'm thinking yes, yes, no, yes. I would be satisfied with no, yes, no, no. Although I don't know what tab completion changes you have in mind. -- 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 cosmetic problem while dumping/restoring rules
Gražvydas Valeika writes: > - create new database; > - CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public; > - backup it; > - create new database and restore it from this new backup. > > It produces 3 errors while executing these 3 statements: This has been fixed by Joe Conway meanwhile. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] event trigger API documentation?
Peter Eisentraut writes: > Looks pretty good, but the description of the parsetree field was > obviously copied from somewhere else. I would fix it myself, but I > don't know what kind of assurances we want to offer about what's in that > field. Oh, oops. I think we should direct the reader to the source code for more information (and propose both where the Node structure is defined and some places where it's used, ProcessUtility comes to mind), and warn that the parse tree exact content for any given command will change in between major releases, and could change in between minor releases. That said, now that it's exposed in the FDW code and the Event Trigger code, we might want to have some ABI compat' in place for minor versions. I'm only raising the question, my knowledge on how to do that and the impact on the code maintaince is so sore that I have no opinion about what the good answer is. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bugfix and new feature for PGXS
On 06/19/2013 12:32 PM, Cédric Villemain wrote: Le mercredi 19 juin 2013 18:20:11, Andrew Dunstan a écrit : On 06/19/2013 11:26 AM, Peter Eisentraut wrote: On 6/19/13 10:19 AM, Andrew Dunstan wrote: What are they going to be used for anyway? I rubbed up against this not too long ago. Things will blow up if you use stuff from the module and the module isn't already loaded. See transforms. So you're saying to install extension headers, but into the main directory where we put server headers? At the same level than server headers, but I'm open for suggestion. $ tree -d include include ├── libpq └── postgresql ├── contrib │ └── hstore ├── informix │ └── esql ├── internal │ └── libpq └── server And all subidrs of server/ This is what Peter was arguing against, isn't it? 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] Bugfix and new feature for PGXS
Le mercredi 19 juin 2013 18:20:11, Andrew Dunstan a écrit : > On 06/19/2013 11:26 AM, Peter Eisentraut wrote: > > On 6/19/13 10:19 AM, Andrew Dunstan wrote: > >> What are they going to be used for anyway? I rubbed up against this not > >> too long ago. Things will blow up if you use stuff from the module and > >> the module isn't already loaded. > > > > See transforms. > > So you're saying to install extension headers, but into the main > directory where we put server headers? At the same level than server headers, but I'm open for suggestion. $ tree -d include include ├── libpq └── postgresql ├── contrib │ └── hstore ├── informix │ └── esql ├── internal │ └── libpq └── server And all subidrs of server/ -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Patch for removng unused targets
On Wed, Jun 19, 2013 at 4:49 AM, Etsuro Fujita wrote: > Hi Harada-san, > > ** ** > > Thank you for the review. > > ** ** > > I think that the parse tree has enough information to do this optimization > and that the easiest way to do it is to use the information, though I might > not have understand your comments correctly. So, I would like to fix the > bug by simply modifying the removability check in adjust_targetlist() so > that the resjunk column is not used in GROUP BY, DISTINCT ON and *window > PARTITION/ORDER BY*, besides ORDER BY. No? I am open to any comments.*** > * > > > I guess the patch works fine, but what I'm saying is it might be limited to small use cases. Another instance of this that I can think of is ORDER BY clause of window specifications, which you may want to remove from the target list as well, in addition to ORDER BY of query. It will just not be removed by this approach, simply because it is looking at only parse->sortClause. Certainly you can add more rules to the new function to look at the window specification, but then I'm not sure what we are missing. So, as it stands it doesn't have critical issue, but more generalized approach would be desirable. That said, I don't have strong objection to the current patch, and just posting one thought to see if others may have the same opinion. Thanks, -- Hitoshi Harada
Re: [HACKERS] Bugfix and new feature for PGXS
On 06/19/2013 11:26 AM, Peter Eisentraut wrote: On 6/19/13 10:19 AM, Andrew Dunstan wrote: What are they going to be used for anyway? I rubbed up against this not too long ago. Things will blow up if you use stuff from the module and the module isn't already loaded. See transforms. So you're saying to install extension headers, but into the main directory where we put server headers? 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] Implementing incremental backup
On Wed, Jun 19, 2013 at 7:13 AM, Tatsuo Ishii wrote: > > For now, my idea is pretty vague. > > - Record info about modified blocks. We don't need to remember the > whole history of a block if the block was modified multiple times. > We just remember that the block was modified since the last > incremental backup was taken. > > - The info could be obtained by trapping calls to mdwrite() etc. We need > to be careful to avoid such blocks used in xlogs and temporary > tables to not waste resource. > > - If many blocks were modified in a file, we may be able to condense > the info as "the whole file was modified" to reduce the amount of > info. > > - How to take a consistent incremental backup is an issue. I can't > think of a clean way other than "locking whole cluster", which is > obviously unacceptable. Maybe we should give up "hot backup"? I don't see how this is better than snapshotting at the filesystem level. I have no experience with TB scale databases (I've been limited to only hundreds of GB), but from my limited mid-size db experience, filesystem snapshotting is pretty much the same thing you propose there (xfs_freeze), and it works pretty well. There's even automated tools to do that, like bacula, and they can handle incremental snapshots. -- 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] how to find out whether a view is updatable
On 19 June 2013 15:22, Peter Eisentraut wrote: > We still don't have any support for this in psql, do we? > No, but at least we now have an API that psql can use. There are still a number of questions about the best way to display it in psql. Should it be another column in \d+'s list of relations? Should it appear in \d+ for a single relation? Should it distinguish updatable from insertable and deletable? Should tab-completion also be modified? Currently I'm thinking yes, yes, no, yes. Regards, Dean -- 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] Git-master regression failure
Peter Eisentraut wrote: > On 6/19/13 9:18 AM, Kevin Grittner wrote: >> Does anyone object to the attached change, so that regression tests >> pass when run in a Danish locale? I think it should be >> back-patched to 9.2, where the test was introduced. > > Yes, that should be fixed. I wouldn't put in the comment, though. A > few releases ago, I fixed a number of other "Danish" issues, so adding > this comment would give the impression that this the only place. OK, pushed without the comment. -- Kevin Grittner 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] Bugfix and new feature for PGXS
On 6/19/13 10:19 AM, Andrew Dunstan wrote: > What are they going to be used for anyway? I rubbed up against this not > too long ago. Things will blow up if you use stuff from the module and > the module isn't already loaded. See transforms. -- 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] Bad error message on valuntil
On 6/7/13 2:57 PM, Tom Lane wrote: > "Joshua D. Drake" writes: >> I had a customer pulling their hair out today because they couldn't >> login to their system. The error was consistently: > >> 2013-06-07 08:42:44 MST postgres 10.1.11.67 27440 FATAL: password >> authentication failed for user "user > >> However the problem had nothing to do with password authentication. It >> was because the valuntil on the user had been set till a date in the >> past. Now technically if we just removed the word "password" from the >> error it would be accurate but it seems it would be better to say, >> "FATAL: the user "user" has expired". > > I think it's intentional that we don't tell the *client* that level of > detail. I could see emitting a log message about it, but it's not clear > whether that will help an unsophisticated user. Usually, when I log in somewhere and the password is expired, it tells me that the password is expired. I don't think we gain anything by hiding that from the user. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Push down
Hi all, I'm writing a foreign data wrapper in which i'm taking control of various aspects of SELECT queries (such as join, order by, count, sum etc.). Is it possible? for example, when trying to count(*), i see that pg supplies an empty list of columns to select from, and probably does the counting by itself. The same goes for sum and order by and so on. Help will be much appreciated :-) Thanks
Re: [HACKERS] how to find out whether a view is updatable
On 6/13/13 1:37 AM, Dean Rasheed wrote: > On 12 June 2013 23:01, Tom Lane wrote: >> > Dean Rasheed writes: >>> >> [ pg_relation_is_updatable.patch ] >> > >> > I've committed this with some modifications as mentioned. There is >> > still room to debate exactly what >> > information_schema.columns.is_updatable means --- we can now change that >> > without an initdb. >> > > Thanks. Those modifications all look pretty neat. We still don't have any support for this in psql, do we? -- 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] Bugfix and new feature for PGXS
On 06/19/2013 10:06 AM, Peter Eisentraut wrote: On 6/19/13 5:55 AM, Cédric Villemain wrote: Le mercredi 19 juin 2013 04:48:11, Peter Eisentraut a écrit : On Tue, 2013-06-18 at 15:52 +0200, Cédric Villemain wrote: This allows for example to install hstore header and be able to include them in another extension like that: # include "contrib/hstore/hstore.h" That's not going to work. hstore's header file is included as #include "hstore.h" (cf. hstore source code). Having it included under different paths in different contexts will be a mess. OK. At the beginning I though of putting headers in include/contrib but feared that some header may have the same name. If you think that it is suitable, I can do that ? (and include the clean: recipe that I missed on the first shot) I don't think there is any value in moving the contrib header files around. What are they going to be used for anyway? I rubbed up against this not too long ago. Things will blow up if you use stuff from the module and the module isn't already loaded. 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] Bugfix and new feature for PGXS
On 6/19/13 5:55 AM, Cédric Villemain wrote: > Le mercredi 19 juin 2013 04:48:11, Peter Eisentraut a écrit : >> On Tue, 2013-06-18 at 15:52 +0200, Cédric Villemain wrote: >>> This allows for example to install hstore header and be able to >>> include them >>> >>> in another extension like that: >>> # include "contrib/hstore/hstore.h" >> >> That's not going to work. hstore's header file is included as #include >> "hstore.h" (cf. hstore source code). Having it included under different >> paths in different contexts will be a mess. > > OK. > At the beginning I though of putting headers in include/contrib but feared > that some header may have the same name. > If you think that it is suitable, I can do that ? (and include the clean: > recipe that I missed on the first shot) I don't think there is any value in moving the contrib header files around. -- 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] Git-master regression failure
On 6/19/13 9:18 AM, Kevin Grittner wrote: > Svenne Krap wrote: >> On 18-06-2013 22:18, Jeff Janes wrote: > >>>In Danish, apparently 'AA' > 'WA', so two more rows show up. >> >> Yes of course >> >> We have three extra vowels following Z (namely Æ, Ø and Å) and >> for keyboard missing those essential keys we have an official >> alternate way to write them as AE , OE and AA. >> >> Which of course means that AA is larger than any other letter ;) > > Does anyone object to the attached change, so that regression tests > pass when run in a Danish locale? I think it should be > back-patched to 9.2, where the test was introduced. Yes, that should be fixed. I wouldn't put in the comment, though. A few releases ago, I fixed a number of other "Danish" issues, so adding this comment would give the impression that this the only place. -- 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] pgbench --throttle (submission 7 - with lag measurement)
I'm still getting the same sort of pauses waiting for input with your v11. Alas. This is a pretty frustrating problem; I've spent about two days so far trying to narrow down how it happens. I've attached the test program I'm using. It seems related to my picking a throttled rate that's close to (but below) the maximum possible on your system. I'm using 10,000 on a system that can do about 16,000 TPS when running pgbench in debug mode. This problem is 100% reproducible here; happens every time. This is a laptop running Mac OS X. It's possible the problem is specific to that platform. I'm doing all my tests with the database itself setup for development, with debug and assertions on. The lag spikes seem smaller without assertions on, but they are still there. Here's a sample: transaction type: SELECT only What is this test script? I'm doing pgbench for tests. scaling factor: 10 query mode: simple number of clients: 25 number of threads: 1 duration: 30 s number of transactions actually processed: 301921 average transaction lag: 1.133 ms (max 137.683 ms) tps = 10011.527543 (including connections establishing) tps = 10027.834189 (excluding connections establishing) And those slow ones are all at the end of the latency log file, as shown in column 3 here: 22 11953 3369 0 1371578126 954881 23 11926 3370 0 1371578126 954918 3 12238 30310 0 1371578126 984634 7 12205 30350 0 1371578126 984742 8 12207 30359 0 1371578126 984792 11 12176 30325 0 1371578126 984837 13 12074 30292 0 1371578126 984882 0 12288 175452 0 1371578127 126340 9 12194 171948 0 1371578127 126421 12 12139 171915 0 1371578127 126466 24 11876 175657 0 1371578127 126507 Indeed, there are two spikes, but not all clients are concerned. As I have not seen that, debuging is hard. I'll give it a try on tomorrow. When no one is sleeping, the timeout becomes infinite, so only returning data will break it. This is intended behavior though. This is not coherent. Under --throttle there should basically always be someone asleep, unless the server cannot cope with the load and *all* transactions are late. A no time out state looks pretty unrealistic, because it means that there is no throttling. I don't think the st->listen related code has anything to do with this either. That flag is only used to track when clients have completed sending their first query over to the server. Once reaching that point once, afterward they should be "listening" for results each time they exit the doCustom() code. This assumption seems false if you can have a "sleep" at the beginning of the sequence, which is what throttle is doing, but can be done by any custom script, so that the client is expected to wait before sending any command, thus there can be no select underway in that case. So listen should be set to 1 when a select as been sent, and set back to 0 when the result data have all been received. "doCustom" makes implicit assumptions about what is going on, whereas it should focus on looking at the incoming state, performing operations, and leaving with a state which correspond to the actual status, without assumptions about what is going to happen next. st->listen goes to 1 very soon after startup and then it stays there, and that logic seems fine too. -- Fabien. -- 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] add --throttle to pgbench (submission 3)
On 05/01/13 04:57, Fabien COELHO wrote: > > Add --throttle to pgbench > > Each client is throttled to the specified rate, which can be expressed in > tps or in time (s, ms, us). Throttling is achieved by scheduling > transactions along a Poisson-distribution. > > This is an update of the previous proposal which fix a typo in the sgml > documentation. > > The use case of the option is to be able to generate a continuous gentle > load for functional tests, eg in a practice session with students or for > testing features on a laptop. Why does this need two option formats (-H and --throttle)? Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers