Re: [HACKERS] Issues for named/mixed function notation patch
2010/2/23 Bruce Momjian br...@momjian.us: Can someone work on a patch to implement the document changes suggested below? This patch is useless now. There are no this issue now, because we have integrated true SQL parser. Regards Pavel --- Jeff Davis wrote: On Tue, 2009-09-15 at 10:51 +0200, Pavel Stehule wrote: My renonc, please, try new patch. I forgot mark regproc.c file. I think the documentation around calling functions is disorganized: Variadic functions, functions with defaults, SRFs, out parameters, and polymorphism are all explained in 34.4, which is about SQL functions specifically. Overloading is in chapter 34 also, but not specifically in the SQL function section like the rest. Function calls themselves are only given 5 lines of explanation in 4.2.6, with no mention of things like the VARIADIC keyword. These complaints aren't about the patch, but we might want to consider some reorganization of those sections (probably a separate doc patch). The interaction with variadic functions appears to be misdocumented. From the code and tests, the VARIADIC keyword appears to be optional when using named notation, but required when using positional notation. But the documentation says: However, a named variadic argument can only be called the way shown in the example above. The VARIADIC keyword must not be specified and a variadic notation of all arguments is not supported. To use variadic argument lists you must use positional notation instead. What is the intended behavior? I think we should always require VARIADIC to be specified regardless of using named notation. I'm still reviewing the code. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian br...@momjian.us http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] Recent vendor SSL renegotiation patches break PostgreSQL
Tom Lane wrote: One way to deal with it would be to expose the whole renegotiation setting as a user configuratble option. So they can set *when* we renegotiate, which would also let them turn it off completely. Well, that might be a reasonable thing to do, because it's not just a temporary kluge (that we won't know when to remove) but is adding an arguably-useful-in-other-ways knob. You'd still have to turn it off on the server side if you have a *single* client that has the broken patch, but that's still a lot better than nothing. Well, if it's a GUC it can be set per-user or per-database, so there's at least some hope of not having to turn it off for everyone. Think it's worth taking a stab at? If you want to do it, I'd be fine with it. +1 That would help me with a different problem: SSL renegotiation is broken with Npgsql, the cause is Bug 321325 in the Mono security library https://bugzilla.novell.com/show_bug.cgi?id=321325 which does not look like it is ever going to be fixed. Up to now I have crippled SSL renegotiation in our servers with a patch, because I figured that bad SSL is better than no SSL. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SR/libpq - outbound interface/ipaddress binding
While playing with SR/HS in a more complex datacenter environment I immediatly hit the need to being able to specify the ipaddress(or interface) that the backend(or libpq) uses to connect to the master. There are a few reasons for being able to do so like: * we are now suddenly in a situation where the backend can create outbound connections on it's own so people will have to add firewall rules and being able to guarantee the source IP will help maintainance (otherwise stuff might break if you say add an alias IP on an interface) * prioritising - if you know that replication traffic is on a given IP you can actually do fancy stuff like routing it over a different gigE line or giving it prority on a WAN connection * some of those also apply to other libpq clients but those are usually not in that complex network/system environments as servers are comment? Stefan -- 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] [COMMITTERS] Re: pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
On Tue, Feb 23, 2010 at 5:37 AM, Tom Lane t...@sss.pgh.pa.us wrote: So the problem is that fsync_fname is trying to fsync a file it's opened O_RDONLY. I don't know whether Windows is similarly picky, but we'll soon find out. Argh, now I feel silly. I had actually found that in my searches after the first batch of problems. But somehow i didn't connect that to the current problems. Sorry. There are other similarly confused OSes that don't allow fsync on read-only file descriptors: http://svn.haxx.se/dev/archive-2006-02/0488.shtml (I wonder if some of them are doing fsync wrong and only syncing changes written to this file descriptor and not any file descriptor for this file?) The plan I was thinking of was to pass a flag indicating if it's a directory to fsync_fname() and open it RD_ONLY if it's a directory and RDRW if it's a file. Then ignore any error returns (or at least EBADF and EINVAL) iff the flag indicating it was a directory was true. I don't like using configure tests for this because I fear someone could compile Postgres on a system with one set of behaviour and then switch to a different kernel version with a different set of behaviour. In the worst case it could be filesystem dependent whether you can open directories or whether they accept fsyncs. -- greg -- 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] synchronous commit in dump
2010/2/23 Jaime Casanova jcasa...@systemguards.com.ec: Hi, it's safe to set synchrounous_commit to off in a pg_dump generated script? if yes, would this help to the performance of restore a database? It might help if you're dumping as individual inserts and not COPY, but if you're doing that you're not asking for performance in the first place. I don't really see how it would help in any other cases - sync_commit=off helps when you have many small transactions, which really is the opposite of pg_dump/pg_restore. -- 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] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
Tom Lane t...@sss.pgh.pa.us writes: Alvaro Herrera alvhe...@commandprompt.com writes: Regarding hooks or events, I think postmaster should be kept simple: launch at start, reset at crash recovery, kill at stop. This is exactly why I think the whole proposal is a nonstarter. It is necessarily pushing more complexity into the postmaster, which means an overall reduction in system reliability. I was under the illusion that having a separate supervisor process child of postmaster to care about the user daemons would protect postmaster itself. At least the only thing it'd have to do is start a new child. Then let it care. How much that would give us as far as postmaster reliability is concerned? -- dim -- 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] SR/libpq - outbound interface/ipaddress binding
2010/2/23 Stefan Kaltenbrunner ste...@kaltenbrunner.cc: While playing with SR/HS in a more complex datacenter environment I immediatly hit the need to being able to specify the ipaddress(or interface) that the backend(or libpq) uses to connect to the master. There are a few reasons for being able to do so like: * we are now suddenly in a situation where the backend can create outbound connections on it's own so people will have to add firewall rules and being able to guarantee the source IP will help maintainance (otherwise stuff might break if you say add an alias IP on an interface) * prioritising - if you know that replication traffic is on a given IP you can actually do fancy stuff like routing it over a different gigE line or giving it prority on a WAN connection * some of those also apply to other libpq clients but those are usually not in that complex network/system environments as servers are comment? Seems like this could be very useful functionality, provided it can be done in a reasonably portable way. As long as it's a libpq connection parameter, it'll benefit everybody else as well as the replication stuff at no extra cost. It's not a very broad use-case, but in that use-case I can see how it would be very useful. Now, are you up for actually writing it? ;) -- 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] Recent vendor SSL renegotiation patches break PostgreSQL
2010/2/22 Tom Lane t...@sss.pgh.pa.us: Magnus Hagander mag...@hagander.net writes: 2010/2/22 Tom Lane t...@sss.pgh.pa.us: You'd still have to turn it off on the server side if you have a *single* client that has the broken patch, but that's still a lot better than nothing. Well, if it's a GUC it can be set per-user or per-database, so there's at least some hope of not having to turn it off for everyone. Think it's worth taking a stab at? If you want to do it, I'd be fine with it. Seems easy enough, see attached. Comments? This version is set to superuser only. It's a security related feature, so just letting a random user turn it off may be seen as wrong. On the other hand, this is just about the connection security, and if we have a malicious user on the other end, he can do a lot worse things than disable renegotiation (such as resending the plaintext after it's been decrypted). I'd therefore suggest we make it USERSET. Anything wrong in that discussion? (That would also for example allow npgsql to always set it to 0, if it's known to be broken) Also, do we want to add a specific note to the documentation saying this is the way around broken SSL libraries? Or leave that to release notes? Or just leave it to the mailinglist archives? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ ssl_renegotiate.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Recent vendor SSL renegotiation patches break PostgreSQL
2010/2/23 Albe Laurenz laurenz.a...@wien.gv.at: Tom Lane wrote: One way to deal with it would be to expose the whole renegotiation setting as a user configuratble option. So they can set *when* we renegotiate, which would also let them turn it off completely. Well, that might be a reasonable thing to do, because it's not just a temporary kluge (that we won't know when to remove) but is adding an arguably-useful-in-other-ways knob. You'd still have to turn it off on the server side if you have a *single* client that has the broken patch, but that's still a lot better than nothing. Well, if it's a GUC it can be set per-user or per-database, so there's at least some hope of not having to turn it off for everyone. Think it's worth taking a stab at? If you want to do it, I'd be fine with it. +1 That would help me with a different problem: SSL renegotiation is broken with Npgsql, the cause is Bug 321325 in the Mono security library https://bugzilla.novell.com/show_bug.cgi?id=321325 which does not look like it is ever going to be fixed. *ouch* Up to now I have crippled SSL renegotiation in our servers with a patch, because I figured that bad SSL is better than no SSL. Given the major security hole in the whole project, SSL without renegotiation was a *lot* more secure than SSL *with* renegotiation, until very recently :-) But patching the server is always annoying... -- 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] A thought on Index Organized Tables
On Tue, Feb 23, 2010 at 10:42 AM, Tom Lane t...@sss.pgh.pa.us wrote: Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp writes: Instead, how about excluding columns in primary keys from table data? How will you implement select * from mytable ? Or even select * from mytable where non_primary_key = something ? If you can't do either of those without great expense, I think a savings on primary-key lookups is not going to be adequate recompense. Tom, I am talking things more from the perspective of how things have got implemented in Oracle/SQL Server. Both Oracle and SQL Server store the snapshot info with indexes and hence can do index-only scans with their indexes. But still they have the concept of Index Organized Tables / Clustered Indexes. Apart from the disk footprint, it will have an impact on the cache efficiency also. In Oracle IOT and SQL Server Clustered Indexes, you have an option to store some of the columns in the leaf pages( but not in the non-leaf pages) and hence the tuples won't get sorted based on them, but you don't require an extra i/o to access them. This optimization is again to reduce the size of IOT. Oracle IOT has a concept called overflow regions, which is more like a heap and will store a few columns. There will be a pointer from main b-tree structure to this secondary structure. Accessing these columns are costly, but the idea is that the database designer has taken this into account while deciding on the columns to be put in the overflow regions. We can design secondary indexes to make the access faster for non-primary key based searches. But since the Secondary indexes store primary key in the place of HeapTuple Pointer, the access will usually take 2-3 more i/os. But the idea is that the IOT is for those kind of data. which will be 99% queried based on primary keys. The database provides that extra performance for that kind of access patterns. So to answer your question, full table scans(if overflow regions are involved) and search based on non-primary keys will be slow in an IOT. I looked at the postgres nbtree code. From my analysis(which might be wrong!), we can implement IOTs, provided we make a decision on broken data types issue. Thanks, Gokul.
Re: [HACKERS] [COMMITTERS] Re: pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
I wrote: Any theories about what is happening? Hah --- the AIX failures, at least, are explained at http://publib.boulder.ibm.com/infocenter/aix/v6r1/index.jsp?topic=/com.ibm.aix.basetechref/doc/basetrf1/fsync.htm which says Error Codes The fsync or fsync_range subroutine is unsuccessful if one or more of the following are true: EIO An I/O error occurred while reading from or writing to the file system. EBADFThe FileDescriptor parameter is not a valid file descriptor open for writing. EINVAL The file is not a regular file. EINTRThe subroutine was interrupted by a signal. So the problem is that fsync_fname is trying to fsync a file it's opened O_RDONLY. I don't know whether Windows is similarly picky, but we'll soon find out. Now, this doesn't mean that all is fine and dandy. I believe that a majority of Unixen will reject attempts to open directories for writing, so this solution puts us even further away from being able to fsync the directories. I would bet however that the platforms that reject this are ones that don't need fsync on directories. Maybe we just have to have two different code paths depending on platform :-( 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] A thought on Index Organized Tables
Hi all, On Mon, 2010-02-22 at 10:29 +, Greg Stark wrote: On Mon, Feb 22, 2010 at 8:18 AM, Gokulakannan Somasundaram gokul...@gmail.com wrote: a) IOT has both table and index in one structure. So no duplication of data b) With visibility maps, we have three structures a) Table b) Index c) Visibility map. So the disk footprint of the same data will be higher in postgres ( 2x + size of the visibility map). These sound like the same point to me. I don't think we're concerned with footprint -- only with how much of that footprint actually needs to be scanned. For some data the disk foot-print would be actually important: on our data bases we have one table which has exactly 2 fields, which are both part of it's primary key, and there's no other index. The table is write-only, never updated and rarely deleted from. The disk footprint of the table is 30%-50% of the total disk space used by the DB (depending on the other data). This amounts to about 1.5-2TB if I count it on all of our DBs, and it has to be fast disk too as the table is heavily used... so disk space does matter for some. And yes, I put the older entries in some archive partition on slower disks, but I just halve the problem - the data is growing exponentially, and about half of it is always in use. I guess our developers are just ready to get this table out of postgres and up to hadoop... Cheers, Csaba. -- 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] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
Dimitri Fontaine wrote: Tom Lane t...@sss.pgh.pa.us writes: Alvaro Herrera alvhe...@commandprompt.com writes: Regarding hooks or events, I think postmaster should be kept simple: launch at start, reset at crash recovery, kill at stop. This is exactly why I think the whole proposal is a nonstarter. It is necessarily pushing more complexity into the postmaster, which means an overall reduction in system reliability. I was under the illusion that having a separate supervisor process child of postmaster to care about the user daemons would protect postmaster itself. At least the only thing it'd have to do is start a new child. Then let it care. The problem I have with this design is that those processes are then not direct children of postmaster itself, which is a problem when it wants them to stop and such. (This is why autovacuum workers are started by postmaster and not by the launcher directly. If I knew of a way to make it work reliably, I wouldn't have bothered with that signalling mechanism, which is quite fragile and gets its fair share of bug reports.) (Hmm, but then, autovacuum workers are backends and so they need to be more closely linked to postmaster. These other processes needn't be.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Issues for named/mixed function notation patch
Pavel Stehule wrote: 2010/2/23 Bruce Momjian br...@momjian.us: Can someone work on a patch to implement the document changes suggested below? This patch is useless now. There are no this issue now, because we have integrated true SQL parser. Great, thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] function side effects
Tatsuo Ishii wrote: Hi, I'm wondering if we could detect a funcion has a side effect, i.e. does a write to database. This is neccessary for pgpool to decide if a qeury should to be sent to all of databases or not. If a query includes functions which do writes to database, it should send the query to all of databases, otherwise the contents of databases go into inconsistent state. I was talking about this to someone in Cuba and one conclusion we reached was that this was a fairly difficult task -- consider that someone may choose to define an innocent-looking operator using a volatile function. If you only examine things that look like functions in the query you will miss those. The only way to figure out whether a query has a write effect is to ask the server about the whole query. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Assertion failure in walreceiver
I tried to set up a simple master/slave setup and immediately ran into this assertion failure. The slave is just a cold copy of the database immediately after initdb. The first WAL segment hasn't been archived yet. It sees that the first archive fail hasn't been archived yet, starts up walreceiver but it looks like the start point hasn't been initialized yet because it hasn't processed any checkpoint WAL records yet. $ /usr/local/pgsql/bin/postgres -D /var/tmp/pg85/s/ LOG: database system was shut down at 2010-02-23 14:30:08 GMT cp: cannot stat `/var/tmp/pg85/w/0001': No such file or directory TRAP: FailedAssertion(!(startpoint.xlogid != 0 || startpoint.xrecoff != 0), File: libpqwalreceiver.c, Line: 87) LOG: WAL receiver process (PID 3536) was terminated by signal 6: Aborted LOG: terminating any other active server processes LOG: startup process (PID 3534) exited with exit code 2 LOG: aborting startup due to startup process failure -- greg -- 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] A thought on Index Organized Tables
On Mon, 2010-02-22 at 08:51 +0200, Heikki Linnakangas wrote: Gokulakannan Somasundaram wrote: May i get a little clarification on this issue? Will we be supporting the IOT feature in postgres in future? What seems like the best path to achieve the kind of performance benefits that IOTs offer is allowing index-only-scans using the visibility map. I don't agree with that. Could you explain why you think that would be the case? It would be a shame to have everybody think you can solve a problem if it turned out not to be the case. -- Simon Riggs www.2ndQuadrant.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] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
On Tue, 2010-02-23 at 00:02 -0500, Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Regarding hooks or events, I think postmaster should be kept simple: launch at start, reset at crash recovery, kill at stop. Salt and pepper allowed but that's about it -- more complex ingredients are out of the question due to added code to postmaster, which we want to be as robust as possible and thus not able to cook much of anything else. This is exactly why I think the whole proposal is a nonstarter. It is necessarily pushing more complexity into the postmaster, which means an overall reduction in system reliability. There are some things I'm willing to accept extra postmaster complexity for, but I say again that not one single one of the arguments made in this thread are convincing reasons to take that risk. Nobody wants to weigh down and sink the postmaster. What is wanted is a means to integrate parts of a solution that are already intimately tied to Postgres. Non-integration makes the whole Postgres-based solution less reliable and harder to operate. Postgres should not assume that it is the only aspect of the server: in almost all other DBMS features are built into the database: session pools, trigger-based replication, scheduling, etc.. -- Simon Riggs www.2ndQuadrant.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] A thought on Index Organized Tables
Gokulakannan Somasundaram wrote: I looked at the postgres nbtree code. From my analysis(which might be wrong!), we can implement IOTs, provided we make a decision on broken data types issue. I am not familiar with this term broken data types, and I just looked for it in the source code and couldn't find it. What exactly are you referring to? 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] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
Simon Riggs wrote: What is wanted is a means to integrate parts of a solution that are already intimately tied to Postgres. Non-integration makes the whole Postgres-based solution less reliable and harder to operate. Postgres should not assume that it is the only aspect of the server: in almost all other DBMS features are built into the database: session pools, trigger-based replication, scheduling, etc.. Yeah, back when autovac wasn't integrated, it was a pain to work with -- the need to start and stop it separately from postmaster was a hard task to manage. The Debian init script used to have some very ugly hacks to work with it. Having it now integrated makes thing *so* much easier. Giving postmaster the ability to manage other processes (whether directly or through a supervisor) would make people lives simpler as well. I think it was Dimitri who said that even if postmaster is running but the connection pooler is down, the system is effectively down for some users, and thus you really want postmaster to be able to do something about it. I cannot agree more. (You can set up monitoring and such, but this is merely working around the fact that it doesn't work in the first place.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] function side effects
I'm wondering if we could detect a funcion has a side effect, i.e. does a write to database. This is neccessary for pgpool to decide if a qeury should to be sent to all of databases or not. If a query includes functions which do writes to database, it should send the query to all of databases, otherwise the contents of databases go into inconsistent state. I was talking about this to someone in Cuba and one conclusion we reached was that this was a fairly difficult task -- consider that someone may choose to define an innocent-looking operator using a volatile function. If you only examine things that look like functions in the query you will miss those. The only way to figure out whether a query has a write effect is to ask the server about the whole query. In general you are right. However in most database application systems, it is possible that all functions are properly designed and implemented (at least they want so). In this world, more or less PostgreSQL functions are just a part of their applications. If they trust their client side applications, why they cannot trust PostgreSQL custom functions as well? -- 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] A thought on Index Organized Tables
Simon Riggs wrote: On Mon, 2010-02-22 at 08:51 +0200, Heikki Linnakangas wrote: Gokulakannan Somasundaram wrote: May i get a little clarification on this issue? Will we be supporting the IOT feature in postgres in future? What seems like the best path to achieve the kind of performance benefits that IOTs offer is allowing index-only-scans using the visibility map. I don't agree with that. Could you explain why you think that would be the case? It would be a shame to have everybody think you can solve a problem if it turned out not to be the case. I'm thinking of a scan based on the index key. With an index-organised-table, you can skip the heap access because the heap and the index are the same structure. An index-only-scan likewise allows you to skip the heap access. I grant you that an index-organised-table can have other benefits, like reduced disk space usage (which is good cache efficiency), or less random I/O required for updates. The question was if PostgreSQL will be supporting index-organised-tables in the future. The answer is not in the foreseeable future. No-one has come up with a plausible plan for how to do it, and no-one working on it at the moment. I don't want to discourage thinking about pie-in-the-sky features. There's many tricks like column-oriented storage, compression, index-organised-tables etc. that would be nice to have. Whether any particular feature is worthwhile in the end, the devil is in the details. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A thought on Index Organized Tables
Andrew Dunstan wrote: Gokulakannan Somasundaram wrote: I looked at the postgres nbtree code. From my analysis(which might be wrong!), we can implement IOTs, provided we make a decision on broken data types issue. I am not familiar with this term broken data types, and I just looked for it in the source code and couldn't find it. What exactly are you referring to? I believe he's referring to the fact that once a key is inserted to an index, it might not be possible to re-find it, if the datatype is broken in such a way that e.g comparison operator returns a different value. For example, today 1 2 returns true, but tomorrow it returns false. The decision on that is that you need to deal with it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A thought on Index Organized Tables
Simon Riggs si...@2ndquadrant.com wrote: On Mon, 2010-02-22 at 08:51 +0200, Heikki Linnakangas wrote: Gokulakannan Somasundaram wrote: May i get a little clarification on this issue? Will we be supporting the IOT feature in postgres in future? What seems like the best path to achieve the kind of performance benefits that IOTs offer is allowing index-only-scans using the visibility map. I don't agree with that. Could you explain why you think that would be the case? It would be a shame to have everybody think you can solve a problem if it turned out not to be the case. I'd like to be clear on what feature we're discussing. There has been mention of an organization where there is no heap per se, but all columns are stored in the leaf node of one of the table's indexes (which is the structure referred to as a CLUSTERED INDEX in some other popular products). There has been some mention of storing some of the data out-of-line, which could be considered to be already covered by TOAST. I know that one of the things which makes this technique particularly effective with such things as name columns for a clustered index is that these other products store index entries after the first in a page with a length that matches the previous entry and the differing data at the tail, which we don't yet have. Clearly it's not trivial, but there are certainly cases where it can be a big performance win. Besides the obvious issues around having a relation which functions like both an index and a heap (at the leaf level), there are the details of having other indexes point to these leaf nodes, creating and dropping clustered indexes, impact on vacuums, etc. Situations where clustered indexes tended to help: (1) Most access through a particular index -- often one less random read per access. (2) Frequent sequential access through a range of values in an index -- turn random access into mostly sequential. (3) Index values comprise a large portion of each tuple -- avoid redundant storage, reducing disk footprint, thereby improving cache hits. Points 1 and 2 could be covered to some degree by index-only scans, particularly if additional columns are added to indexes to make them covering indexes. Index-only scans don't help with 3 at all; in fact, adding the additional columns to indexes to allow that optimization tends to work against it. -Kevin -- 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] A thought on Index Organized Tables
On Tue, 2010-02-23 at 17:08 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: On Mon, 2010-02-22 at 08:51 +0200, Heikki Linnakangas wrote: Gokulakannan Somasundaram wrote: May i get a little clarification on this issue? Will we be supporting the IOT feature in postgres in future? What seems like the best path to achieve the kind of performance benefits that IOTs offer is allowing index-only-scans using the visibility map. I don't agree with that. Could you explain why you think that would be the case? It would be a shame to have everybody think you can solve a problem if it turned out not to be the case. I'm thinking of a scan based on the index key. With an index-organised-table, you can skip the heap access because the heap and the index are the same structure. An index-only-scan likewise allows you to skip the heap access. I grant you that an index-organised-table can have other benefits, like reduced disk space usage (which is good cache efficiency), or less random I/O required for updates. The question was if PostgreSQL will be supporting index-organised-tables in the future. The answer is not in the foreseeable future. No-one has come up with a plausible plan for how to do it, and no-one working on it at the moment. I think Gokul was asking because he wanted to work on it, but wanted to check community approval first. I don't want to discourage thinking about pie-in-the-sky features. Planning, is what I would call that. Calling them pie in the sky is just a negative label, as much as if someone else called them obvious next steps is a positive label. There's many tricks like column-oriented storage, compression, index-organised-tables etc. that would be nice to have. Whether any particular feature is worthwhile in the end, the devil is in the details. I agree that the way to improve things is to focus on a particular architectural technique and then a design for doing that. Going straight to the design and naming it doesn't help at all. That was why I named an earlier project Frequent Update Optimisation rather than any of the names that referred to a design. The devil is in the details, I agree. The important part is analysis though, not coding. Which is why I was asking why you were working on index-only scans, though do not doubt your ability to make them work. And also why I would say to Gokul: the right approach isn't to ask will we be supporting IOTs and then go and build them. The right approach is to work out what you want to improve and give a clear justification of why, come up with a proposal to do that with analysis of how the proposal will improve the situation and then think about coding. -- Simon Riggs www.2ndQuadrant.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] [COMMITTERS] Re: pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
Greg Stark st...@mit.edu writes: I don't like using configure tests for this because I fear someone could compile Postgres on a system with one set of behaviour and then switch to a different kernel version with a different set of behaviour. In the worst case it could be filesystem dependent whether you can open directories or whether they accept fsyncs. Yeah, and there's also the problem of cross-compilation. I don't want a configure test either if we can avoid it. The plan I was thinking of was to pass a flag indicating if it's a directory to fsync_fname() and open it RD_ONLY if it's a directory and RDRW if it's a file. Then ignore any error returns (or at least EBADF and EINVAL) iff the flag indicating it was a directory was true. Works for me, but let's first try just ignoring EBADF, which is the only value we saw in the recent buildfarm failures. If we got past the fd0 test then EBADF could only indicate a rdonly failure, whereas it's less clear what EINVAL might cover. 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] function side effects
Tom Lane t...@sss.pgh.pa.us wrote: Those classifications are meant as planner directives; they are NOT meant to be bulletproof. Hanging database integrity guarantees on whether a non volatile function changes anything is entirely unsafe. To give just one illustration of the problems, a nonvolatile function is allowed to call a volatile one. Could it work to store a flag in each process to indicate when it is executing a non-volatile function, and throw an error on any attempt to call a volatile function or modify the database? -Kevin -- 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] pretty print viewdefs
Bruce Momjian wrote: What happened to this? I didn't see it applied. I got puzzled by some delphic comments, and then I got pulled into work of a higher priority, so it slipped down my list. Maybe we can pick it up again in 9.1. 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] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
On Feb 22, 2010, at 9:02 PM, Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Regarding hooks or events, I think postmaster should be kept simple: launch at start, reset at crash recovery, kill at stop. Salt and pepper allowed but that's about it -- more complex ingredients are out of the question due to added code to postmaster, which we want to be as robust as possible and thus not able to cook much of anything else. This is exactly why I think the whole proposal is a nonstarter. It is necessarily pushing more complexity into the postmaster, which means an overall reduction in system reliability. There are some things I'm willing to accept extra postmaster complexity for, but I say again that not one single one of the arguments made in this thread are convincing reasons to take that risk. Would having a higher level process manager be adequate - one that spawns the postmaster and a list of associated processes (queue manager, job scheduler, random user daemons that are used for database application maintenance). It sounds like something like that would be able to start up and shut down an entire family of daemons, of which the postmaster is the major one, gracefully. It could also be developed almost independently of core code, at most it might benefit from a way for the postmaster to tell it when it's started up successfully. Cheers, Steve -- 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] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
Steve Atkins wrote: Would having a higher level process manager be adequate - one that spawns the postmaster and a list of associated processes (queue manager, job scheduler, random user daemons that are used for database application maintenance). It sounds like something like that would be able to start up and shut down an entire family of daemons, of which the postmaster is the major one, gracefully. Sort of a super-pg_ctl, eh? Hmm, that sounds like it could work ... It could also be developed almost independently of core code, at most it might benefit from a way for the postmaster to tell it when it's started up successfully. Right -- pg_ping pops up again ... I think it'd also want to be signalled when postmaster undergoes a restart cycle, so that it can handle the other daemons appropriately. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] function side effects
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: Those classifications are meant as planner directives; they are NOT meant to be bulletproof. Hanging database integrity guarantees on whether a non volatile function changes anything is entirely unsafe. To give just one illustration of the problems, a nonvolatile function is allowed to call a volatile one. Could it work to store a flag in each process to indicate when it is executing a non-volatile function, and throw an error on any attempt to call a volatile function or modify the database? It's *not an error* for a nonvolatile function to call a volatile one. At least it's never been in the past, and I'm sure you'd break some applications if you made it so in the future. 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] [COMMITTERS] Re: pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
I wrote: BTW, I notice that after allegedly fixing things, we are now seeing fsync failures during CREATE DATABASE in the installcheck phase of buildfarm runs on (apparently) all the Windows critters, plus a couple of other platforms too. This mystifies me. I could believe that there was something still wrong with copydir.c, but then how come these machines are getting through the earlier make check phase? BTW, although things seem to be going green with the RDONLY-RDWR change, I'm still mystified why these machines didn't fail at make check. Is it possible that make check runs the postmaster with fsync disabled? I don't see that in the code anywhere... 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] to_char(HH12) with intervals
Bruce Momjian wrote: bruce wrote: bruce wrote: Dave Page wrote: This was posted as a documentation comment: to_char(interval '0d 0h 12m 44s', 'DD HH MI SS'); with HH and HH12 will return 12 instead of 0. Testing on 8.4.1, it does seem to be the case that you get 00 12 12 44. Seems bogus to me, but am I and the OP missing something? Fixed with the attached patch. I think HH and HH24 should be the same for intervals. It is hard to explain why zero hours should show as '12' for intervals. Oops, I needed a second patch to fix hours 12 for intervals. Patch attached and applied. It will now report the full hours of the interval. We currently have this in our documentation: functionto_char(interval)/function formats literalHH/ and literalHH12/ as hours in a single day, while literalHH24/ can output hours exceeding a single day, e.g., gt;24. This seems pretty confusing because HH/HH12 formats as hours in a single 1/2 day, 12 hours, and it really does wall-clock time, zero hours is 12, and for intervals it does the right thing now and prints the interval hours. We also have these range definitions: row entryliteralHH12/literal/entry entryhour of day (01-12)/entry /row row entryliteralHH24/literal/entry entryhour of day (00-23)/entry /row HH24 could always be 24 for intervals, and now HH12 can too for intervals. What should be changed here? I have rethought this and now realize the original code was fine, but the documentation was very unclear. I have reverted the patch and added a C command and documentation updates with examples; attached. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/func.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.505 diff -c -c -r1.505 func.sgml *** doc/src/sgml/func.sgml 19 Feb 2010 00:15:25 - 1.505 --- doc/src/sgml/func.sgml 23 Feb 2010 16:12:37 - *** *** 5317,5324 listitem para functionto_char(interval)/function formats literalHH/ and ! literalHH12/ as hours in a single day, while literalHH24/ ! can output hours exceeding a single day, e.g., gt;24. /para /listitem --- 5317,5325 listitem para functionto_char(interval)/function formats literalHH/ and ! literalHH12/ as shown on a 12-hour clock, i.e. zero hours ! and 36 hours output as literal12/, while literalHH24/ ! outputs the full hour value, which can exceed 23 for intervals. /para /listitem Index: src/backend/utils/adt/formatting.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v retrieving revision 1.165 diff -c -c -r1.165 formatting.c *** src/backend/utils/adt/formatting.c 23 Feb 2010 06:29:01 - 1.165 --- src/backend/utils/adt/formatting.c 23 Feb 2010 16:12:37 - *** *** 2088,2097 break; case DCH_HH: case DCH_HH12: sprintf(s, %0*d, S_FM(n-suffix) ? 0 : 2, ! is_interval ? tm-tm_hour : ! tm-tm_hour % (HOURS_PER_DAY / 2) == 0 ? ! 12 : tm-tm_hour % (HOURS_PER_DAY / 2)); if (S_THth(n-suffix)) str_numth(s, s, S_TH_TYPE(n-suffix)); s += strlen(s); --- 2088,2097 break; case DCH_HH: case DCH_HH12: + /* display time as shown on a 12-hour clock, even for intervals */ sprintf(s, %0*d, S_FM(n-suffix) ? 0 : 2, ! tm-tm_hour % (HOURS_PER_DAY / 2) == 0 ? 12 : ! tm-tm_hour % (HOURS_PER_DAY / 2)); if (S_THth(n-suffix)) str_numth(s, s, S_TH_TYPE(n-suffix)); s += strlen(s); -- 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] pretty print viewdefs
Andrew Dunstan wrote: Bruce Momjian wrote: What happened to this? I didn't see it applied. I got puzzled by some delphic comments, and then I got pulled into work of a higher priority, so it slipped down my list. Maybe we can pick it up again in 9.1. OK, should it be added to the TODO list? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] function side effects
I was talking about this to someone in Cuba and one conclusion we reached was that this was a fairly difficult task -- consider that someone may choose to define an innocent-looking operator using a volatile function. If you only examine things that look like functions in the query you will miss those. The only way to figure out whether a query has a write effect is to ask the server about the whole query. In general you are right. However in most database application systems, it is possible that all functions are properly designed and implemented (at least they want so). In this world, more or less PostgreSQL functions are just a part of their applications. If they trust their client side applications, why they cannot trust PostgreSQL custom functions as well? Still there could be none honest functions such as calling volatile functions from non volatile function in the PostgreSQL system(I have not made any investigation. But it's possible). Or vendor provided functions (for example embedded in closed source packages) might fall into this category. Probably it's enough for pgpool to have a black list of such that function. Maintaining such a list is a boring task but I cannot think of any good way at this point. -- 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] pretty print viewdefs
Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: What happened to this? I didn't see it applied. I got puzzled by some delphic comments, and then I got pulled into work of a higher priority, so it slipped down my list. Maybe we can pick it up again in 9.1. OK, should it be added to the TODO list? Sure. 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
[HACKERS] Re: [BUGS] BUG #5021: ts_parse doesn't recognize email addresses with underscores
Oleg, Teodor, can you look at this? I tried to fix it in wparser_def.c,
but couldn't figure out how. Thanks.
---
Dan O'Hara wrote:
The following bug has been logged online:
Bug reference: 5021
Logged by: Dan O'Hara
Email address: danarasoftw...@gmail.com
PostgreSQL version: 8.3.7
Operating system: win32
Description:ts_parse doesn't recognize email addresses with
underscores
Details:
In the following example,
select distinct token as email
from ts_parse('default', ' first_l...@yahoo.com ' )
where tokid = 4
ts_parse returns l...@yahoo.com rather than first_l...@yahoo.com It seems
that any text prior to the underscore is truncated. If the portion
following the underscore is only numeric, such as this example,
select distinct token as email
from ts_parse('default', ' bill_2...@yahoo.com ' )
where tokid = 4
then ts_parse returns nothing at all.
section 3.2.3 of RFC 5322 indicates that underscores are valid characters in
an email address.
http://tools.ietf.org/html/rfc5322
--
Sent via pgsql-bugs mailing list (pgsql-b...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +
--
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] function side effects
On Tue, Feb 23, 2010 at 11:08 AM, Tom Lane t...@sss.pgh.pa.us wrote: It's *not an error* for a nonvolatile function to call a volatile one. it should be considered an error i think, someone think there is a use cas for calling volatile functions inside stable ones but i can see what that reason could be... At least it's never been in the past, and I'm sure you'd break some applications if you made it so in the future. i'm sure of that too, but in this case seems reasonable to do so -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Resjunk sort columns, Heikki's index-only quals patch, and bug #5000
Is this a TODO? --- Heikki Linnakangas wrote: Robert Haas wrote: Since you previously stated that you were going to put this patch aside to work on HS and SR[1], I'm going to move this to Returned with Feedback for now. Hope that's OK, and that the feedback is sufficient and useful. Yes, on both counts. Thank you! -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] function side effects
Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: throw an error on any attempt to call a volatile function or modify the database? It's *not an error* for a nonvolatile function to call a volatile one. Right, we all know it currently doesn't throw an error, but I can't think of anywhere I'd like to have someone do that in a database for which I have any responsibility. Does anyone have a sane use case for a non-volatile function to call a volatile one or to update the database? -Kevin -- 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] Index-only quals
I added this URL to the existing TODO item. --- Heikki Linnakangas wrote: Here is an updated version of my patch to return data from b-tree indexes, and use it to satisfy quals. I added a new column 'amregurgitate' to pg_am, to mark which indexams can return index tuples. Also, the data type of the index column in pg_attribute must match the type of the heap column - this catches the hack that 'name' is stored as cstring, that I had hardcoded before. As discussed, GiST/GIN would need more infrastructure to mark which opclasses can return tuples, but as long as GiST/GIN doesn't support regurgitation at all, I'm not going to complicate the catalogs with that. There's also some planner fixes - indexes that are only useful because of index-only quals are not considered for bitmap scans, and volatile expressions mustn't be used as index-only quals. This patch comes in two parts. Indexam API changes, which just splits the indexam_getnext function into two without providing any new functionality, and the main patch that applies on top of the indexam API changes. The patches are also available at git://git.postgresql.org/git/users/heikki/postgres.git, branches 'indexam-api-changes and 'indexfilter'. Barring objections, I'm going to apply the indexam API changes part, since that simplifies the code in question regardless of the rest of the work. I'm pretty happy now with the indexfilter patch as well, but want to do some more testing on that before committing. Some more eyeballs would be appreciated as well. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_stop_backup does not complete
Simon, Fujii, All: While demoing HS/SR at SCALE, I ran into a problem which is likely to be a commonly encountered bug when people first setup HS/SR. Here's the sequence: 1) Set up a brand new master with an archive-commmand and archive=on. 2) Start the master 3) Do a pg_start_backup() 4) Realize, based on log error messages, that I've misconfigured the archive_command. 5) Attempt to shut down the master. Master tells me that pg_stop_backup must be run in order to shut down. 6) Execute pg_stop_backup. 7) pg_stop_backup waits forever without ever stopping backup. Ever 60 seconds, it give me a helpful still waiting message, but at least in the amount of time I was willing to wait (5 minutes), it never completed. 8) do an immediate shutdown, as it's the only way I can get the database unstuck. With some experimentation, the problem seems to occur when you have a failing archive_command and a master which currently has no database traffic; for example, if I did some database write activity (a createdb) then pg_stop_backup would complete after about 60 seconds (which, btw, is extremely annoying, but at least tolerable). This issue is 100% reproduceable. --Josh Berkus -- 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_stop_backup does not complete
This issue is 100% reproduceable. Oh, btw, this is on Alpha4. --Josh Berkus -- 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_stop_backup does not complete
On Tue, 2010-02-23 at 09:45 -0800, Josh Berkus wrote: Simon, Fujii, All: While demoing HS/SR at SCALE, I ran into a problem which is likely to be a commonly encountered bug when people first setup HS/SR. Here's the sequence: 1) Set up a brand new master with an archive-commmand and archive=on. 2) Start the master 3) Do a pg_start_backup() 4) Realize, based on log error messages, that I've misconfigured the archive_command. 5) Attempt to shut down the master. Master tells me that pg_stop_backup must be run in order to shut down. If I issue a shutdown, PostgreSQL should do whatever it needs to do to shutdown; including issuing a pg_stop_backup. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- 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_stop_backup does not complete
Joshua D. Drake j...@commandprompt.com wrote: If I issue a shutdown, PostgreSQL should do whatever it needs to do to shutdown; including issuing a pg_stop_backup. Should we have a pg_fail_backup function, so that it doesn't put out a file which suggests that we have a complete backup? -Kevin -- 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] 8.5 TODO: Add comments to output indicating version of pg_dump and of the database server
On Mon, Feb 22, 2010 at 8:55 PM, Bruce Momjian br...@momjian.us wrote: What happened to this patch? I don't see any objections, but it was not applied. I think that the patch author never added it to the open CommitFest and nobody else thought it was important enough to pick up. It looks innocuous to me; want to go ahead and apply? ...Robert -- 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] function side effects
On Tue, Feb 23, 2010 at 4:52 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Right, we all know it currently doesn't throw an error, but I can't think of anywhere I'd like to have someone do that in a database for which I have any responsibility. Does anyone have a sane use case for a non-volatile function to call a volatile one or to update the database? So consider for example a function which explicitly sets the timezone and then uses timestamp without timezone functions (which are volatile only because the GUC variable might change between calls). Or somebody who uses the tsearch functions because they're planning to not change their dictionaries. Or builds a hash function by calling random after setting the seed to a specific value -- this is actually a fairly popular strategy for building good hash functions. -- greg -- 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] backend: compare word-at-a-time in bcTruelen
On Mon, Feb 22, 2010 at 9:35 PM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Jeremy Kerr j...@ozlabs.org writes: Stephen, If the updated function is always faster when the overall string is at least, say, 16 characters long, But that's not the case - the cost of the function (and the speedup from the previous version) depends on the number of spaces that there are at the end. Right, but there are certainly not more spaces than there are string characters ;-) I think Dimitri's idea is eminently worth trying. In a string of less than, say, 16 bytes, the prospects of being able to win anything get much smaller compared to the prospects of wasting the extra loop overhead. There is also a DBA psychology angle to it. If you've got CHAR(n) for very small n, it's likely that the type is being used in the canonical fashion and there won't be many trailing blanks. The case where we can hope to win is where we have CHAR(255) or some other plucked-from-the-air limit. What ever happened to this patch? I think it's unclear that all of the best and worst cases have been sufficiently tested and that the results are satisfactory. We have everything from massive performance gains to no obvious benefit at all, and it's very unclear that anyone has made a serious effort to find a benchmark the worst-case scenarios. I think we should drop this for now. *If* someone wants to put some work into more thorough analysis for 9.1, we can revisit it then. ...Robert -- 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] 8.5 TODO: Add comments to output indicating version of pg_dump and of the database server
On 2/23/10 10:14 AM, Robert Haas wrote: On Mon, Feb 22, 2010 at 8:55 PM, Bruce Momjian br...@momjian.us wrote: What happened to this patch? I don't see any objections, but it was not applied. I think that the patch author never added it to the open CommitFest and nobody else thought it was important enough to pick up. It looks innocuous to me; want to go ahead and apply? I'd say yes. It's post-freeze, but this falls into the class of oops, we forgot about this patch which the CFs were designed to prevent. --Josh Berkus -- 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] More robust pg_hba.conf parsing/error logging
On Tue, Feb 23, 2010 at 12:22 AM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Andrew Dunstan wrote: It will affect any dbname or username in mixed or upper case, not just ALL, won't it? No, I am suggesting to change only the comparisons to the literals all, sameuser, samegroup and samerole. What happened to this idea? Hmm. These words are effectively keywords, so +1 for treating them case-insensitively, as we do in SQL. But I wonder whether there isn't an argument for making the comparisons of role and database names behave more like SQL, too --- that is FOO matches foo but not FOO. And this one? Nobody's implemented them? I'd suggest adding these to the TODO. ...Robert -- 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] Adding \ev view editor?
On Tue, Feb 23, 2010 at 12:43 AM, Bruce Momjian br...@momjian.us wrote: Is this a TODO? Sounds good to me. It doesn't seem like it would be that difficult to add a view editor as \ev. ...Robert -- 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] backend: compare word-at-a-time in bcTruelen
Robert Haas escribió: On Mon, Feb 22, 2010 at 9:35 PM, Bruce Momjian br...@momjian.us wrote: What ever happened to this patch? I think it's unclear that all of the best and worst cases have been sufficiently tested and that the results are satisfactory. We have everything from massive performance gains to no obvious benefit at all, and it's very unclear that anyone has made a serious effort to find a benchmark the worst-case scenarios. I think we should drop this for now. *If* someone wants to put some work into more thorough analysis for 9.1, we can revisit it then. +1 -- it's not like it hasn't been a problem all along. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] 8.5 TODO: Add comments to output indicating version of pg_dump and of the database server
Josh Berkus j...@agliodbs.com writes: On 2/23/10 10:14 AM, Robert Haas wrote: On Mon, Feb 22, 2010 at 8:55 PM, Bruce Momjian br...@momjian.us wrote: What happened to this patch? I don't see any objections, but it was not applied. I think that the patch author never added it to the open CommitFest and nobody else thought it was important enough to pick up. It looks innocuous to me; want to go ahead and apply? I'd say yes. It's post-freeze, but this falls into the class of oops, we forgot about this patch which the CFs were designed to prevent. That would be an argument for sticking this in the next CF, not for applying it now --- it was submitted after the close of the last CF no? 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] [PATCH] backend: compare word-at-a-time in bcTruelen
Alvaro Herrera wrote: Robert Haas escribió: On Mon, Feb 22, 2010 at 9:35 PM, Bruce Momjian br...@momjian.us wrote: What ever happened to this patch? I think it's unclear that all of the best and worst cases have been sufficiently tested and that the results are satisfactory. We have everything from massive performance gains to no obvious benefit at all, and it's very unclear that anyone has made a serious effort to find a benchmark the worst-case scenarios. I think we should drop this for now. *If* someone wants to put some work into more thorough analysis for 9.1, we can revisit it then. +1 -- it's not like it hasn't been a problem all along. hmm I tend to disagree, this patch was specifically done to address a hotspot I noticed under a given workload and it helped a lot for that workload(like getting 6000qps more is pretty neat imho). While people might not use fixed width chars that often(which especially for migrated database is imho not true) it is an issue that can be seen with a rather popular database related benchmarking tool so we should not really dismiss it easily. Stefan -- 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] 8.5 TODO: Add comments to output indicating version of pg_dump and of the database server
Tom Lane escribió: Josh Berkus j...@agliodbs.com writes: On 2/23/10 10:14 AM, Robert Haas wrote: On Mon, Feb 22, 2010 at 8:55 PM, Bruce Momjian br...@momjian.us wrote: What happened to this patch? I don't see any objections, but it was not applied. I think that the patch author never added it to the open CommitFest and nobody else thought it was important enough to pick up. It looks innocuous to me; want to go ahead and apply? I'd say yes. It's post-freeze, but this falls into the class of oops, we forgot about this patch which the CFs were designed to prevent. That would be an argument for sticking this in the next CF, not for applying it now --- it was submitted after the close of the last CF no? Sep. 29 2009? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Adding \ev view editor?
Robert Haas wrote: On Tue, Feb 23, 2010 at 12:43 AM, Bruce Momjian br...@momjian.us wrote: Is this a TODO? Sounds good to me. I think it would be useful if we put line breaks in the column list it gets, otherwise not so much for any view with more than a handful of columns. That's where we came in on the pretty-printing of viewdefs ... 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] function side effects
Greg Stark gsst...@mit.edu wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: Does anyone have a sane use case for a non-volatile function to call a volatile one or to update the database? So consider for example a function which explicitly sets the timezone and then uses timestamp without timezone functions (which are volatile only because the GUC variable might change between calls). OK, I can see where that would be sane, but it seems more fragile than using timestamp with time zone. But, OK, something sane and functional could break on that. Or somebody who uses the tsearch functions because they're planning to not change their dictionaries. I didn't realize tsearch functions were volatile. Should they really be so? Or builds a hash function by calling random after setting the seed to a specific value -- this is actually a fairly popular strategy for building good hash functions. I'd never seen that. I'm not sure I understand where that comes in useful, but if you've seen it enough to call it fairly popular I guess I have to accept it. Thanks for the examples. They did make me consider a real-life type of process which isn't currently implemented as a PostgreSQL function, but conceivably could be -- randomizing a pool of jurors to facilitate jury selection. My eyes are opened. :-) -Kevin -- 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] backend: compare word-at-a-time in bcTruelen
Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: hmm I tend to disagree, this patch was specifically done to address a hotspot I noticed under a given workload and it helped a lot for that workload(like getting 6000qps more is pretty neat imho). While people might not use fixed width chars that often(which especially for migrated database is imho not true) it is an issue that can be seen with a rather popular database related benchmarking tool so we should not really dismiss it easily. Nobody suggested dismissing it. The point was that it hasn't been tested adequately to justify applying it now. 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] [PATCH] backend: compare word-at-a-time in bcTruelen
Tom Lane wrote: Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: hmm I tend to disagree, this patch was specifically done to address a hotspot I noticed under a given workload and it helped a lot for that workload(like getting 6000qps more is pretty neat imho). While people might not use fixed width chars that often(which especially for migrated database is imho not true) it is an issue that can be seen with a rather popular database related benchmarking tool so we should not really dismiss it easily. Nobody suggested dismissing it. The point was that it hasn't been tested adequately to justify applying it now. not sure what testing people want to get done though (there are a fair amount of results and profiles in the thread)? Stefan -- 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] 8.5 TODO: Add comments to output indicating version of pg_dump and of the database server
Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane escribió: That would be an argument for sticking this in the next CF, not for applying it now --- it was submitted after the close of the last CF no? Sep. 29 2009? Oh, I was thinking it had just come in recently, but looking back you're right. It did slip through the cracks. However, has the patch actually been reviewed? pg_dump is a piece of code where it is notoriously easy for novices to do things wrong, and this is especially true for adding output that should only come out in particular cases. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
On Tue, Feb 23, 2010 at 6:39 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Or somebody who uses the tsearch functions because they're planning to not change their dictionaries. I didn't realize tsearch functions were volatile. Should they really be so? Uhm, my mistake. They're stable. Ok, for that one I'll substitute a function which uses pg_read_file knowing that the file in question won't be changed. Perhaps it's a per-machine key or something like that. Or builds a hash function by calling random after setting the seed to a specific value -- this is actually a fairly popular strategy for building good hash functions. I'd never seen that. I'm not sure I understand where that comes in useful, but if you've seen it enough to call it fairly popular I guess I have to accept it. http://en.wikipedia.org/wiki/Universal_hashing They have the useful property that it's hard for an attacker to contrive data which has poor collision behaviour. Thanks for the examples. They did make me consider a real-life type of process which isn't currently implemented as a PostgreSQL function, but conceivably could be -- randomizing a pool of jurors to facilitate jury selection. My eyes are opened. :-) I'm not actually sure I follow what you're picturing. -- greg -- 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] backend: compare word-at-a-time in bcTruelen
Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: Tom Lane wrote: Nobody suggested dismissing it. The point was that it hasn't been tested adequately to justify applying it now. not sure what testing people want to get done though (there are a fair amount of results and profiles in the thread)? Robert was complaining that the worst case hadn't been characterized adequately, which I agree with. We know it helps a lot on certain cases, but what's the downside? 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] pg_stop_backup does not complete
On Tue, 2010-02-23 at 09:45 -0800, Josh Berkus wrote: 1) Set up a brand new master with an archive-commmand and archive=on. 2) Start the master 3) Do a pg_start_backup() 4) Realize, based on log error messages, that I've misconfigured the archive_command. 5) Attempt to shut down the master. Master tells me that pg_stop_backup must be run in order to shut down. 6) Execute pg_stop_backup. 7) pg_stop_backup waits forever without ever stopping backup. Ever 60 seconds, it give me a helpful still waiting message, but at least in the amount of time I was willing to wait (5 minutes), it never completed. 8) do an immediate shutdown, as it's the only way I can get the database unstuck. With some experimentation, the problem seems to occur when you have a failing archive_command and a master which currently has no database traffic; for example, if I did some database write activity (a createdb) then pg_stop_backup would complete after about 60 seconds (which, btw, is extremely annoying, but at least tolerable). This issue is 100% reproduceable. IMHO there in no problem in that behaviour. If somebody requests a backup then we should wait for it to complete. Kevin's suggestion of pg_fail_backup() is the only sensible conclusion there because it gives an explicit way out of deadlock. ISTM the problem is that you didn't test. Steps 3 and 4 should have been reversed. Perhaps we should put something in the docs to say and test. The correct resolution is to put in an archive_command that works. We can put in an extra step to prevent a pg_start_backup() if there are a significant number of outstanding files to be archived. Doing that seems like closing the door after the horse has bolted, since we just introduced streaming replication that doesn't rely on archived files. In any case, I don't see many people working on a production system hitting a problem on an archive_command and then deciding to shut down. So I don't see this as something that needs fixing for 9.0. There is already too much non-essential code there, all of which needs to be tested. I don't think adding in new corner cases to help people makes any sense until we have automated testing that allows us to rerun the regression tests to check all this stuff still works. -- Simon Riggs www.2ndQuadrant.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] function side effects
Greg Stark gsst...@mit.edu writes: On Tue, Feb 23, 2010 at 6:39 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: I didn't realize tsearch functions were volatile. Should they really be so? Uhm, my mistake. They're stable. IMMUTABLE/STABLE/VOLATILE is not really about side effects, it is about how long the function value can be expected to hold still for. There are quite a lot of cases of functions that are marked conservatively as stable (or even volatile) but could be considered immutable in particular queries, because the application developer is prepared to assume that values such as GUCs won't change in his usage. The traditional way to deal with that is to wrap them in an immutable wrapper function. There's actually code in the planner to make that work --- we have to suppress inlining to avoid exposing the not-immutable guts, else the planner will not do what's wanted. There may be some value in inventing a has no side effects marker, but that should not be confused with IMMUTABLE/STABLE. 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] [PATCH] 8.5 TODO: Add comments to output indicating version of pg_dump and of the database server
On Tue, Feb 23, 2010 at 1:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane escribió: That would be an argument for sticking this in the next CF, not for applying it now --- it was submitted after the close of the last CF no? Sep. 29 2009? Oh, I was thinking it had just come in recently, but looking back you're right. It did slip through the cracks. However, has the patch actually been reviewed? pg_dump is a piece of code where it is notoriously easy for novices to do things wrong, and this is especially true for adding output that should only come out in particular cases. It's a fairly trivial patch. I took a quick look at it. It needs more than that, but I think not too much more. I think it would be less effort for someone to review it and make a decision than it would be to keep it as an open item for the next 6 months. But that's just MHO: if the consensus is to postpone it, then let's just do that and move on. ...Robert -- 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_stop_backup does not complete
On Tue, 2010-02-23 at 18:58 +, Simon Riggs wrote: On Tue, 2010-02-23 at 09:45 -0800, Josh Berkus wrote: This issue is 100% reproduceable. IMHO there in no problem in that behaviour. If somebody requests a backup then we should wait for it to complete. Kevin's suggestion of pg_fail_backup() is the only sensible conclusion there because it gives an explicit way out of deadlock. ISTM the problem is that you didn't test. Steps 3 and 4 should have been reversed. Perhaps we should put something in the docs to say and test. The correct resolution is to put in an archive_command that works. The problem isn't that it is a bad archive_command, it is that PostgreSQL has no way to deal with this gracefully. Yes people should test but are we dealing with the real world or not? So I don't see this as something that needs fixing for 9.0. There is already too much non-essential code there, all of which needs to be tested. I don't think adding in new corner cases to help people makes any sense until we have automated testing that allows us to rerun the regression tests to check all this stuff still works. This will bite us if we release like this. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- 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_stop_backup does not complete
Simon Riggs si...@2ndquadrant.com wrote:
The correct resolution is to put in an archive_command that works.
One really should ensure that WAL files (or should I now say data?
;-) are flowing before issuing running the pg_start_backup()
function. The documentation has always been pretty explicit about
that:
http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html
| 24.3.2. Making a Base Backup
|
| The procedure for making a base backup is relatively simple:
|
| 1. Ensure that WAL archiving is enabled and working.
|
| 2. Connect to the database as a superuser, and issue the command:
|
| SELECT pg_start_backup('label');
| ...
As long as the SR documentation is equally explicit on this point,
you'd have to be blatantly going against the instructions to hit
this.
Which makes me think that while pg_fail_backup() might actually be a
good idea, it's not really needed to solve this, so it's 9.1
material at best.
-Kevin
--
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_stop_backup does not complete
On Tue, Feb 23, 2010 at 06:58:22PM +, Simon Riggs wrote: On Tue, 2010-02-23 at 09:45 -0800, Josh Berkus wrote: 1) Set up a brand new master with an archive-commmand and archive=on. 2) Start the master 3) Do a pg_start_backup() 4) Realize, based on log error messages, that I've misconfigured the archive_command. 5) Attempt to shut down the master. Master tells me that pg_stop_backup must be run in order to shut down. 6) Execute pg_stop_backup. 7) pg_stop_backup waits forever without ever stopping backup. Ever 60 seconds, it give me a helpful still waiting message, but at least in the amount of time I was willing to wait (5 minutes), it never completed. 8) do an immediate shutdown, as it's the only way I can get the database unstuck. With some experimentation, the problem seems to occur when you have a failing archive_command and a master which currently has no database traffic; for example, if I did some database write activity (a createdb) then pg_stop_backup would complete after about 60 seconds (which, btw, is extremely annoying, but at least tolerable). This issue is 100% reproduceable. IMHO there in no problem in that behaviour. If somebody requests a backup then we should wait for it to complete. Kevin's suggestion of pg_fail_backup() is the only sensible conclusion there because it gives an explicit way out of deadlock. ISTM the problem is that you didn't test. Steps 3 and 4 should have been reversed. Perhaps we should put something in the docs to say and test. The correct resolution is to put in an archive_command that works. +1 for clarifying and extending the docs. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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_stop_backup does not complete
On Tue, 2010-02-23 at 11:24 -0800, Joshua D. Drake wrote: This will bite us if we release like this. No it won't. The current behaviour was put there by user request a few releases back. This isn't a 9.0 issue, and as I've said its addressing something that we now longer see as mainstream going forwards. There are plenty of things that will bite us, but not this. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_stop_backup does not complete
On Tue, Feb 23, 2010 at 12:52 PM, Joshua D. Drake j...@commandprompt.com wrote: On Tue, 2010-02-23 at 09:45 -0800, Josh Berkus wrote: Simon, Fujii, All: While demoing HS/SR at SCALE, I ran into a problem which is likely to be a commonly encountered bug when people first setup HS/SR. Here's the sequence: 1) Set up a brand new master with an archive-commmand and archive=on. 2) Start the master 3) Do a pg_start_backup() 4) Realize, based on log error messages, that I've misconfigured the archive_command. 5) Attempt to shut down the master. Master tells me that pg_stop_backup must be run in order to shut down. If I issue a shutdown, PostgreSQL should do whatever it needs to do to shutdown; including issuing a pg_stop_backup. Maybe. But for sure, if it doesn't, and instead tells the user to issue pg_stop_backup(), then pg_stop_backup() had better WORK when the user tries to execute it. I gather that the problem is that it has to finish all that outstanding archiving before shutting down, in which case Kevin's suggestion of having a command to abort the backup seems reasonable. I might call it pg_abort_backup() rather than pg_fail_backup(), but... ...Robert -- 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] function side effects
On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane t...@sss.pgh.pa.us wrote: There may be some value in inventing a has no side effects marker, but that should not be confused with IMMUTABLE/STABLE. Yeah, that's what I was thinking, too ...Robert -- 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] function side effects
Greg Stark gsst...@mit.edu wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: Thanks for the examples. They did make me consider a real-life type of process which isn't currently implemented as a PostgreSQL function, but conceivably could be -- randomizing a pool of jurors to facilitate jury selection. My eyes are opened. :-) I'm not actually sure I follow what you're picturing. Well, to facilitate people's rights to a jury of their peers, we obtain lists of people in each county based on having a drivers license or state ID, being registered to vote, etc., then (after eliminating duplicates and those who have served on juries in recent years) we randomly select a subset, who get questionnaires, from which (at a later date) we randomly pick people to summon for jury a juror panel, from which (on each day they appear) we randomly select people for particular juries. Any flaw in the randomness of selection could constitute grounds for an appeal of the outcome of a case, so we have to be careful about process. (Randomness being defined as the properties that nobody with an interest in the case can control or predict who will be selected from one group into the next, and there is no bias on anything related to demographics, like age or last name [which could correlate with ethnicity]). Sounds like fun, eh? -Kevin -- 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_stop_backup does not complete
On Tue, 2010-02-23 at 14:49 -0500, Robert Haas wrote: If I issue a shutdown, PostgreSQL should do whatever it needs to do to shutdown; including issuing a pg_stop_backup. Maybe. But for sure, if it doesn't, and instead tells the user to issue pg_stop_backup(), then pg_stop_backup() had better WORK when the user tries to execute it. Right. I gather that the problem is that it has to finish all that outstanding archiving before shutting down, in which case Kevin's suggestion of having a command to abort the backup seems reasonable. I might call it pg_abort_backup() rather than pg_fail_backup(), but... But...? Joshua D. Drake ...Robert -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- 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_stop_backup does not complete
On Tue, Feb 23, 2010 at 3:09 PM, Joshua D. Drake j...@commandprompt.com wrote: On Tue, 2010-02-23 at 14:49 -0500, Robert Haas wrote: If I issue a shutdown, PostgreSQL should do whatever it needs to do to shutdown; including issuing a pg_stop_backup. Maybe. But for sure, if it doesn't, and instead tells the user to issue pg_stop_backup(), then pg_stop_backup() had better WORK when the user tries to execute it. Right. I gather that the problem is that it has to finish all that outstanding archiving before shutting down, in which case Kevin's suggestion of having a command to abort the backup seems reasonable. I might call it pg_abort_backup() rather than pg_fail_backup(), but... But...? But it seems like a good idea other than that. ...Robert -- 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] phypot - Pygmy Hippotause ?
Bruce Momjian br...@momjian.us wrote: I assume this is not something we are supposed to apply. While it appears to improve conformance with the IEEE Std 1003.1 and expand the range of numbers which are correctly handled, it does more calculations. I wouldn't want to see it get in without performance testing and confirmation that existing apps don't rely on the non-standard behavior. I don't remember either of those happening. -Kevin -- 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] backend: compare word-at-a-time in bcTruelen
Tom Lane wrote: Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: Tom Lane wrote: Nobody suggested dismissing it. The point was that it hasn't been tested adequately to justify applying it now. not sure what testing people want to get done though (there are a fair amount of results and profiles in the thread)? Robert was complaining that the worst case hadn't been characterized adequately, which I agree with. We know it helps a lot on certain cases, but what's the downside? Added to TODO list so we don't forget about it: Considering improving performance of computing CHAR() value lengths -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] More robust pg_hba.conf parsing/error logging
Robert Haas wrote: On Tue, Feb 23, 2010 at 12:22 AM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Andrew Dunstan wrote: It will affect any dbname or username in mixed or upper case, not just ALL, won't it? No, I am suggesting to change only the comparisons to the literals all, sameuser, samegroup and samerole. What happened to this idea? Hmm. ?These words are effectively keywords, so +1 for treating them case-insensitively, as we do in SQL. ?But I wonder whether there isn't an argument for making the comparisons of role and database names behave more like SQL, too --- that is FOO matches foo but not FOO. And this one? Nobody's implemented them? I'd suggest adding these to the TODO. Added: |Process pg_hba.conf keywords as case-insensitive -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] Adding \ev view editor?
Andrew Dunstan wrote: Robert Haas wrote: On Tue, Feb 23, 2010 at 12:43 AM, Bruce Momjian br...@momjian.us wrote: Is this a TODO? Sounds good to me. I think it would be useful if we put line breaks in the column list it gets, otherwise not so much for any view with more than a handful of columns. That's where we came in on the pretty-printing of viewdefs ... Added to TODO: |Add ability to edit views with \ev -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] 8.5 TODO: Add comments to output indicating version of pg_dump and of the database server
Robert Haas wrote: On Tue, Feb 23, 2010 at 1:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane escribi?: That would be an argument for sticking this in the next CF, not for applying it now --- it was submitted after the close of the last CF no? Sep. 29 2009? Oh, I was thinking it had just come in recently, but looking back you're right. ?It did slip through the cracks. However, has the patch actually been reviewed? ?pg_dump is a piece of code where it is notoriously easy for novices to do things wrong, and this is especially true for adding output that should only come out in particular cases. It's a fairly trivial patch. I took a quick look at it. It needs more than that, but I think not too much more. I think it would be less effort for someone to review it and make a decision than it would be to keep it as an open item for the next 6 months. Agreed, applied, and TODO updated. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] function side effects
On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane t...@sss.pgh.pa.us wrote: There may be some value in inventing a has no side effects marker, but that should not be confused with IMMUTABLE/STABLE. a READONLY function? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] phypot - Pygmy Hippotause ?
On Tue, Feb 23, 2010 at 4:03 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Bruce Momjian br...@momjian.us wrote: I assume this is not something we are supposed to apply. While it appears to improve conformance with the IEEE Std 1003.1 and expand the range of numbers which are correctly handled, it does more calculations. I wouldn't want to see it get in without performance testing and confirmation that existing apps don't rely on the non-standard behavior. I don't remember either of those happening. Perhaps we should add it to the next CommitFest? ...Robert -- 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] 8.5 TODO: Add comments to output indicating version of pg_dump and of the database server
Robert Haas robertmh...@gmail.com writes: On Tue, Feb 23, 2010 at 1:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: However, has the patch actually been reviewed? pg_dump is a piece of code where it is notoriously easy for novices to do things wrong, and this is especially true for adding output that should only come out in particular cases. It's a fairly trivial patch. I took a quick look at it. It needs more than that, but I think not too much more. I think it would be less effort for someone to review it and make a decision than it would be to keep it as an open item for the next 6 months. But that's just MHO: if the consensus is to postpone it, then let's just do that and move on. Well, trivial and correct are entirely different things :-(. If we're still talking about http://archives.postgresql.org/message-id/c2ee6dbd0909270432hd7773edk144080185fb52...@mail.gmail.com then it is in fact printing the wrong thing for pg_dump's version. PG_VERSION is a compiled-in constant so what you will get when examining an archive is pg_restore's version not pg_dump's version. This is no doubt fixable but it looks like the code doesn't currently bother to set archiveDumpVersion in the plain pg_dump code path, so it's not entirely trivial. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Pika buildfarm member failure on pgcrypto/test sha2
Hi,
Pika, which has been upgraded to NetBSD/mips 5.0.2, failed twice in a row
pgcrypto/test sha2 because of the following warning (identical each time) :
**
/home/pgbuildfarm/workdir/HEAD/pgsql.12374/contrib/pgcrypto/expected/sha2.out
Tue Feb 23 13:14:03 2010
---
/home/pgbuildfarm/workdir/HEAD/pgsql.12374/contrib/pgcrypto/results/sha2.out
Tue Feb 23 19:01:19 2010
***
*** 65,100
--- 65,106
-- SHA384
SELECT encode(digest('', 'sha384'), 'hex');
+ WARNING: problem in alloc set ExprContext: detected write past chunk end in
block 0x7d4b, chunk 0x7d4b0088
encode
--
38b060a751ac96384cd9327eb1b1e36a21fdb71114be07434c0cc7bf63f6e1da274edebfe76f65fbd51ad2f14898b95b
(1 row)
SELECT encode(digest('a', 'sha384'), 'hex');
+ WARNING: problem in alloc set ExprContext: detected write past chunk end in
block 0x7d4b, chunk 0x7d4b0088
encode
--
54a59b9f22b0b80880d8427e548b7c23abd873486e1f035dce9cd697e85175033caa88e6d57bc35efae0b5afd3145f31
(1 row)
SELECT encode(digest('abc', 'sha384'), 'hex');
+ WARNING: problem in alloc set ExprContext: detected write past chunk end in
block 0x7d4b, chunk 0x7d4b0088
encode
--
cb00753f45a35e8bb5a03d699ac65007272c32ab0eded1631a8b605a43ff5bed8086072ba1e7cc2358baeca134c825a7
(1 row)
SELECT
encode(digest('abcdbcdecdefdefgefghfghighijhijkijkljklmklmnlmnomnopnopq',
'sha384'), 'hex');
+ WARNING: problem in alloc set ExprContext: detected write past chunk end in
block 0x7d4b, chunk 0x7d4b0088
encode
--
3391fdddfc8dc7393707a65b1b4709397cf8b1d162af05abfe8f450de5f36bc6b0455a8520bc4e6f5fe95b1fe3c8452b
(1 row)
SELECT
encode(digest('abcdefghbcdefghicdefghijdefghijkefghijklfghijklmghijklmnhijklmnoijklmnopjklmnopqklmnopqrlmnopqrsmnopqrstnopqrstu',
'sha384'), 'hex');
+ WARNING: problem in alloc set ExprContext: detected write past chunk end in
block 0x7d4b, chunk 0x7d4b0088
encode
--
09330c33f71147e83d192fc782cd1b4753111b173b3b05d22fa08086e3b0f712fcc7c71a557e2db966c3e9fa91746039
(1 row)
SELECT
encode(digest('abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz',
'sha384'), 'hex');
+ WARNING: problem in alloc set ExprContext: detected write past chunk end in
block 0x7d4b, chunk 0x7d4b0088
encode
--
3d208973ab3508dbbd7e2c2862ba290ad3010e4978c198dc4d8fd014e582823a89e16f9b2a7bbc1ac938e2d199e8bea4
Anything I should try ?
Regards,
Rémi Zara
--
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] Linux start script updates
Kevin Grittner wrote:
Due to a thread about the neglect of the sample start scripts I took a
look at the current Linux file. There's certainly room for several
improvements, but some of them might require discussion. Attached are
a couple small changes which seem to me to be pretty tame. Hopefully
a small, non-controversial step in the right direction.
(1) It adds an LSB INIT INFO comment block, consistent with the
chkconfig comment block above it.
http://refspecs.freestandards.org/LSB_3.1.0/LSB-Core-generic/LSB-Core-generic/initscrcomconv.html
(2) It doesn't exit with zero for a missing executable unless the
request is stop. It uses 5, which means program is not installed.
http://refspecs.freestandards.org/LSB_3.1.0/LSB-Core-generic/LSB-Core-generic/iniscrptact.html
I applied a modified version of your script, attached. I also modified
the FreeBSD one to output a message, but it still returns 0.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +
Index: contrib/start-scripts/linux
===
RCS file: /cvsroot/pgsql/contrib/start-scripts/linux,v
retrieving revision 1.10
diff -c -c -r1.10 linux
*** contrib/start-scripts/linux 11 Jan 2010 18:39:32 - 1.10
--- contrib/start-scripts/linux 23 Feb 2010 22:08:13 -
***
*** 64,70
set -e
# Only start if we can find the postmaster.
! test -x $DAEMON || exit 0
# Parse command line parameters.
case $1 in
--- 64,78
set -e
# Only start if we can find the postmaster.
! test -x $DAEMON ||
! {
! echo $DAEMON not found
! if [ $1 = stop ]
! then exit 0
! else exit 5
! fi
! }
!
# Parse command line parameters.
case $1 in
--
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] function side effects
On Mon, 2010-02-22 at 23:49 -0500, Tom Lane wrote: Tatsuo Ishii is...@postgresql.org writes: I'm wondering if we could detect a funcion has a side effect, i.e. does a write to database. Currently we have three properties of functions: IMMUTABLE, STABLE and VOLATILE. According to docs IMMUTABLE or STABLE functions do not write to database. Those classifications are meant as planner directives; they are NOT meant to be bulletproof. You make them sound like hints. (I thought we frowned on those?) That isn't true, they don't just change the optimal plan in the way the enable_* parameters do. Immutable functions are reduced in ways that would give the wrong answer if the function is actually volatile. Referring to function properties as planner directives hides their critical importance to the output of a query that calls such functions. Hanging database integrity guarantees on whether a non volatile function changes anything is entirely unsafe. To give just one illustration of the problems, a nonvolatile function is allowed to call a volatile one. So wrongly marking a function as something other than volatile *is* a data integrity issue. Why is that OK? ISTM that this should work the way Tatsuo wants it to work. Immutability should be passed down through the call stack to ensure we can't get this wrong. If people have been advising clients to set things immutable when they are not that seems fairly questionable. We shouldn't avoid fixing an integrity loophole just simply to preserve a planner backdoor, especially since other backdoors are specifically avoided. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] 8.5 TODO: Add comments to output indicating version of pg_dump and of the database server
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Feb 23, 2010 at 1:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: However, has the patch actually been reviewed? ?pg_dump is a piece of code where it is notoriously easy for novices to do things wrong, and this is especially true for adding output that should only come out in particular cases. It's a fairly trivial patch. I took a quick look at it. It needs more than that, but I think not too much more. I think it would be less effort for someone to review it and make a decision than it would be to keep it as an open item for the next 6 months. But that's just MHO: if the consensus is to postpone it, then let's just do that and move on. Well, trivial and correct are entirely different things :-(. If we're still talking about http://archives.postgresql.org/message-id/c2ee6dbd0909270432hd7773edk144080185fb52...@mail.gmail.com Yes, that is the patch. then it is in fact printing the wrong thing for pg_dump's version. Uh, right now it is printing: -- pg_dump version: 9.0devel -- -- remote database version: 9.0devel (9) That is in the SQL output file. PG_VERSION is a compiled-in constant so what you will get when examining an archive is pg_restore's version not pg_dump's version. This is no doubt fixable but it looks like the code doesn't currently bother to set archiveDumpVersion in the plain pg_dump code path, so it's not entirely trivial. So you are saying if you run pg_restore on the SQL dump file, it doesn't pick up the version? I didn't even know pg_restore could do that for text dump files. In fact, I can't get it to work: $ pg_dump -v test /rtmp/x ... $ pg_restore -l /rtmp/x -d test pg_restore: [archiver] input file does not appear to be a valid archive I obviously am missing something. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] auto_explain log_verbose causes regression failure
Uh, I don't see this patch as applied. Was it not necessary? --- Itagaki Takahiro wrote: Robert Haas robertmh...@gmail.com wrote: It looks like this is enough to reproduce the cache lookup failure: The cache loopup failure part could be fixed by the attached patch. It forbids explaining if the transaction is in error state. I cannot reproduce unexpected refassgnexpr and unexpected FieldStore errors yet. We might need another fix for them. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center [ Attachment, skipping... ] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] function side effects
On Tue, 2010-02-23 at 12:51 +0900, Tatsuo Ishii wrote: I'm wondering if we could detect a funcion has a side effect, i.e. does a write to database. This is neccessary for pgpool to decide if a qeury should to be sent to all of databases or not. If a query includes functions which do writes to database, it should send the query to all of databases, otherwise the contents of databases go into inconsistent state. Currently we have three properties of functions: IMMUTABLE, STABLE and VOLATILE. According to docs IMMUTABLE or STABLE functions do not write to database. VOLATILE functions *may* do writes to database. Maybe I could regard VOLATILE functions always do write, but priblem is, VOLATILE qfunctions such as random() and timeofday() apparently do not write and sending those queries that include such functions is overkill. Can we VOLATILE property divide into two categories, say, VOLATILE without write, and VOLATILE with write? pgpool parses the query before deciding how to route it, yes? Why not mark random() and timeofday() as stable in the pgpool catalog, yet leave them as volatile on the database servers? It will just work then. -- Simon Riggs www.2ndQuadrant.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] phypot - Pygmy Hippotause ?
Robert Haas robertmh...@gmail.com wrote: Perhaps we should add it to the next CommitFest? Sounds like the right course of action to me. If nobody objects or beats me to it, I'll do that. -Kevin -- 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] opportunistic tuple freezing
I assume no progress has been made on testing the performance of this patch. --- Jeff Davis wrote: Attached is a patch to implement the idea discussed here: http://archives.postgresql.org/pgsql-hackers/2009-08/msg01137.php If VACUUM freezes one tuple on a page, it's likely that there are others on the same page that are close to vacuum_freeze_min_age, but not quite. Because the page is already dirty from freezing one tuple, it makes sense to be more aggressive about freezing the rest, in the hope that all the tuples will be frozen, and we will not have to dirty the page again later. This patch introduces a GUC vacuum_freeze_opportunistic_ratio. If one tuple on a page is frozen by vacuum, it effectively multiplies vacuum_freeze_min_age by vacuum_freeze_opportunistic_ratio and uses that lower (more aggressive) value only for the current page. The reason we don't just freeze all the tuples we can (effectively setting the vacuum_freeze_opportunistic_ratio to zero) is to preserve transaction ID information for diagnosing problems. Regards, Jeff Davis [ Attachment, skipping... ] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] Streaming replication and pg_xlogfile_name()
On Thu, 2010-01-28 at 10:28 +0200, Heikki Linnakangas wrote: Fujii Masao wrote: In relation to the functions added recently, I found an annoying problem; pg_xlogfile_name(pg_last_xlog_receive/replay_location()) might report the wrong name because pg_xlogfile_name() always uses the current timeline, and a backend doesn't know the actual timeline related to the location which pg_last_xlog_receive/replay_location() reports. Even if a backend knows that, pg_xlogfile_name() would be unable to determine which timeline should be used. Hmm, I'm not sure what the use case for this is Agreed. What is the use case for this? -- Simon Riggs www.2ndQuadrant.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] opportunistic tuple freezing
On Tue, 2010-02-23 at 17:49 -0500, Bruce Momjian wrote: I assume no progress has been made on testing the performance of this patch. That's correct. As of right now, the potential benefits of the patch do not seem to justify the performance testing effort. Others are welcome to try, of course. Regards, Jeff Davis -- 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] function side effects
Simon Riggs si...@2ndquadrant.com writes: So wrongly marking a function as something other than volatile *is* a data integrity issue. Why is that OK? ISTM that this should work the way Tatsuo wants it to work. Please read the rest of the thread. 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] SR/libpq - outbound interface/ipaddress binding
On Tue, 2010-02-23 at 10:00 +0100, Stefan Kaltenbrunner wrote: While playing with SR/HS in a more complex datacenter environment I immediatly hit the need to being able to specify the ipaddress(or interface) that the backend(or libpq) uses to connect to the master. There are a few reasons for being able to do so like: * we are now suddenly in a situation where the backend can create outbound connections on it's own so people will have to add firewall rules and being able to guarantee the source IP will help maintainance (otherwise stuff might break if you say add an alias IP on an interface) * prioritising - if you know that replication traffic is on a given IP you can actually do fancy stuff like routing it over a different gigE line or giving it prority on a WAN connection * some of those also apply to other libpq clients but those are usually not in that complex network/system environments as servers are The whole reason for using libpq was that it gave us a stable base to work on. It also means that we are restricted to any issues libpq has, though the benefit is that any improvement there helps all clients. So any changes you make would benefit Slony, Bucardo, Londiste as well. Not for 9.0, though sounds like a welcome change. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Move documentation of all recovery.conf option to a new chapter.
On Mon, 2010-02-22 at 11:47 +, Heikki Linnakangas wrote: Log Message: --- Move documentation of all recovery.conf option to a new chapter. They used to be scattered between the backup and restore and streaming replication chapters. It's just taken me 15 minutes to locate the settings for primary_conninfo to better understand Stefan's recent post. The commit referred to here is an extremely bad change. If you intended this to be a heading within the High Availability chapter then I would agree. This is what I thought you had done. Having Streaming Rep described in HA and then describing the parameters that make it work in a separate chapter is ridiculous. Plus, wherever they are, they need cross references between them. This is the last straw for me. The Streaming Rep docs are now truly appalling. They were thin before, but tieing them in knots and splitting them into disconnected pieces is just too much. If I can't find the damn things, we've gone too far. SR is a big feature and deserves proper docs. -- Simon Riggs www.2ndQuadrant.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] Issues for named/mixed function notation patch
On Tue, 2010-02-23 at 09:34 -0500, Bruce Momjian wrote: Pavel Stehule wrote: 2010/2/23 Bruce Momjian br...@momjian.us: Can someone work on a patch to implement the document changes suggested below? This patch is useless now. There are no this issue now, because we have integrated true SQL parser. Great, thanks. I believe a documentation issue still exists here. The section on calling functions (4.3) still says nothing about VARIADIC. Also, it's not 100% clear to me where function overloading should go, but perhaps it should be mentioned in that section as well. Regards, Jeff Davis -- 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] synchronous commit in dump
On Tue, Feb 23, 2010 at 4:40 AM, Magnus Hagander mag...@hagander.net wrote: 2010/2/23 Jaime Casanova jcasa...@systemguards.com.ec: Hi, it's safe to set synchrounous_commit to off in a pg_dump generated script? if yes, would this help to the performance of restore a database? It might help if you're dumping as individual inserts and not COPY, but if you're doing that you're not asking for performance in the first place. That isn't necesarily true. Maybe you don't have shell access to the database server but just trough pgadmin or something like that... in that env the copy of the dumps won't work so you need to make the backup with inserts (by need) and still want some performance -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Pika buildfarm member failure on pgcrypto/test sha2
On 2/24/10, Rémi Zara remi_z...@mac.com wrote: Pika, which has been upgraded to NetBSD/mips 5.0.2, failed twice in a row pgcrypto/test sha2 because of the following warning (identical each time) : Anything I should try ? Please try --without-openssl. -- marko -- 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_stop_backup does not complete
On Wed, Feb 24, 2010 at 4:49 AM, Robert Haas robertmh...@gmail.com wrote: Maybe. But for sure, if it doesn't, and instead tells the user to issue pg_stop_backup(), then pg_stop_backup() had better WORK when the user tries to execute it. I gather that the problem is that it has to finish all that outstanding archiving before shutting down, in which case Kevin's suggestion of having a command to abort the backup seems reasonable. I might call it pg_abort_backup() rather than pg_fail_backup(), but... Or how about adding new boolean parameter of pg_stop_backup() that specifies whether WAL archiving needs to be waited? pg_stop_backup([wait boolean]) This parameter is optional. If true (default), it waits for archiving. In warm-standby and SR, we don't need to wait for archiving before starting the standby from the base backup. So pg_stop_backup(false) would be useful for speedup of setup of log-shipping. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] function side effects
I'm wondering if we could detect a funcion has a side effect, i.e. does a write to database. This is neccessary for pgpool to decide if a qeury should to be sent to all of databases or not. If a query includes functions which do writes to database, it should send the query to all of databases, otherwise the contents of databases go into inconsistent state. Currently we have three properties of functions: IMMUTABLE, STABLE and VOLATILE. According to docs IMMUTABLE or STABLE functions do not write to database. VOLATILE functions *may* do writes to database. Maybe I could regard VOLATILE functions always do write, but priblem is, VOLATILE qfunctions such as random() and timeofday() apparently do not write and sending those queries that include such functions is overkill. Can we VOLATILE property divide into two categories, say, VOLATILE without write, and VOLATILE with write? pgpool parses the query before deciding how to route it, yes? Right. Why not mark random() and timeofday() as stable in the pgpool catalog, yet leave them as volatile on the database servers? It will just work then. Please note that random() and timeofday() are just examples. What I'm thinking about was, a function which directly or indirectly cause write to database (thus lead writing to log). Consider a function that calls those has-side-effect functions. We need a property which is inherited to child function to parent function. -- 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
