Re: [HACKERS] Covering Indexes
On Thu, Jun 28, 2012 at 8:16 AM, David E. Wheeler da...@justatheory.com wrote: Hackers, Very interesting design document for SQLite 4: http://www.sqlite.org/src4/doc/trunk/www/design.wiki I'm particularly intrigued by covering indexes. For example: CREATE INDEX cover1 ON table1(a,b) COVERING(c,d); This allows the following query to do an index-only scan: SELECT c, d FROM table1 WHERE a=? AND b=?; Now that we have index-only scans in 9.2, I'm wondering if it would make sense to add covering index support, too, where additional, unindexed columns are stored alongside indexed columns. And I wonder if it would work well with expressions, too? David IRC MS SQL also allow unindexed columns in the index. -- Rob Wultsch wult...@gmail.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] Streaming-only Remastering
On Sun, Jun 10, 2012 at 11:47 AM, Joshua Berkus j...@agliodbs.com wrote: So currently we have a major limitation in binary replication, where it is not possible to remaster your system (that is, designate the most caught-up standby as the new master) based on streaming replication only. This is a major limitation because the requirement to copy physical logs over scp (or similar methods), manage and expire them more than doubles the administrative overhead of managing replication. This becomes even more of a problem if you're doing cascading replication. Therefore I think this is a high priority for 9.3. As far as I can tell, the change required for remastering over streaming is relatively small; we just need to add a new record type to the streaming protocol, and then start writing the timeline change to that. Are there other steps required which I'm not seeing? Problem that may exist and is likely out of scope: It is possible for a master with multiple slave servers to have slaves which have not read all of the logs off of the master. It is annoying to have to rebuild a replica because it was 1kb behind in reading logs from the master. If the new master could deliver the last bit of the old masters logs that would be very nice. -- Rob Wultsch wult...@gmail.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] swapcache-style cache?
On Mon, Feb 27, 2012 at 11:54 PM, Andrea Suisani sick...@opinioni.net wrote: On 02/28/2012 04:52 AM, Rob Wultsch wrote: On Wed, Feb 22, 2012 at 2:31 PM, jamesja...@mansionfamily.plus.com wrote: Has anyone considered managing a system like the DragonFLY swapcache for a DBMS like PostgreSQL? https://www.facebook.com/note.php?note_id=388112370932 in the same vein: http://bcache.evilpiepirate.org/ from the main page: Bcache is a patch for the Linux kernel to use SSDs to cache other block devices. It's analogous to L2Arc for ZFS, but Bcache also does writeback caching, and it's filesystem agnostic. It's designed to be switched on with a minimum of effort, and to work well without configuration on any setup. By default it won't cache sequential IO, just the random reads and writes that SSDs excel at. It's meant to be suitable for desktops, servers, high end storage arrays, and perhaps even embedded. it was submitted to linux kernel mailing list a bunch of time, the last one: https://lkml.org/lkml/2011/9/10/13 Andrea I am pretty sure I won't get fired (or screw up the IPO) by saying that I have a high opinion of Flashcache (at least within the fb environment). Is anyone using bcache at scale? -- Rob Wultsch wult...@gmail.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] swapcache-style cache?
On Wed, Feb 22, 2012 at 2:31 PM, james ja...@mansionfamily.plus.com wrote: Has anyone considered managing a system like the DragonFLY swapcache for a DBMS like PostgreSQL? https://www.facebook.com/note.php?note_id=388112370932 -- Rob Wultsch wult...@gmail.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] MySQL search query is not executing in Postgres DB
On Fri, Feb 17, 2012 at 4:12 PM, Josh Berkus j...@agliodbs.com wrote: On 2/17/12 12:04 PM, Robert Haas wrote: The argument isn't about whether the user made the right design choices; it's about whether he should be forced to insert an explicit type cast to get the query to do what it is unambiguously intended to do. I don't find INTEGER LIKE '1%' to be unambiguous. Prior to this discussion, if I had run across such a piece of code, I couldn't have told you what it would do in MySQL without testing. What *does* it do in MySQL? IIRC it casts each INTEGER (without any left padding) to text and then does the comparison as per normal. Comparison of dissimilar types are a recipe for full table scans and unexpected results. A really good example is select * from employees where first_name=5; vs select * from employees where first_name='5'; Where first_name is string the queries above have very different behaviour in MySQL. The first does a full table scan and coerces first_name to an integer (so '5adfs' - 5) while the second can use an index as it is normal string comparison. I have seen this sort of things cause significant production issues several times.* I have seen several companies use comparisons of dissimilar data types as part of their stump the prospective DBA test and they stump lots of folks. -- Rob Wultsch wult...@gmail.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] Page Checksums + Double Writes
On Wed, Dec 21, 2011 at 1:59 PM, David Fetter da...@fetter.org wrote: One of the things VMware is working on is double writes, per previous discussions of how, for example, InnoDB does things. The world is moving to flash, and the lifetime of flash is measured writes. Potentially doubling the number of writes is potentially halving the life of the flash. Something to think about... -- Rob Wultsch wult...@gmail.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_dump.c
On Sun, Sep 11, 2011 at 9:18 AM, Andrew Dunstan and...@dunslane.net wrote: On 09/11/2011 10:25 AM, David Fetter wrote: On Thu, Sep 08, 2011 at 03:20:14PM -0400, Andrew Dunstan wrote: In the refactoring Large C files discussion one of the biggest files Bruce mentioned is pg_dump.c. There has been discussion in the past of turning lots of the knowledge currently embedded in this file into a library, which would make it available to other clients (e.g. psql). I'm not sure what a reasonable API for that would look like, though. Does anyone have any ideas? Here's a sketch. In essence, libpgdump should have the following areas of functionality: - Discover the user-defined objects in the database. - Tag each as pre-data, data, and post-data. - Make available the dependency graph of the user-defined objects in the database. - Enable the mechanical selection of subgraphs which may or may not be connected. - Discover parallelization capability, if available. - Dump requested objects of an arbitrary subset of the database, optionally using such capability. Then there's questions of scope, which I'm straddling the fence about. Should there be separate libraries to transform and restore? A thing I'd really like to have in a libdump would be to have the RDBMS-specific parts as loadable modules, but that, too, could be way out of scope for this. In the first place, this isn't an API, it's a description of functionality. A C library's API is expressed in its header files. Also, I think you have seriously misunderstood the intended scope of the library. Dumping and restoring, parallelization, and so on are not in the scope I was thinking of. I think those are very properly the property of pg_dump.c and friends. The only part I was thinking of moving to a library was the discovery part, which is in fact a very large part of pg_dump.c. One example of what I'd like to provide is something this: char * pg_get_create_sql(PGconn *conn, object oid, catalog_class oid, pretty boolean); Which would give you the sql to create an object, optionally pretty printing it. Another is: char * pg_get_select(PGconn *conn, table_or_view oid, pretty boolean, alias *char ); which would generate a select statement for all the fields in a given table, with an optional alias prefix. For the purposes of pg_dump, perhaps we'd want to move all the getFoo() functions in pg_dump.c into the library, along with a couple of bits from common.c like getSchemaData(). (Kinda thinking out loud here.) cheers andrew For whatever it is worth, the SHOW CREATE TABLE command in MySQL is well loved. Having the functionality to generate SQL in the server can be very nice. -- Rob Wultsch wult...@gmail.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] crash-safe visibility map, take five
On Fri, May 6, 2011 at 2:47 PM, Robert Haas robertmh...@gmail.com wrote: Comments? At my day job there is saying: Silence is consent. I am surprised there has not been more discussion of this change, considering the magnitude of the possibilities it unlocks. -- Rob Wultsch wult...@gmail.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] making an unlogged table logged
On Wed, Jan 5, 2011 at 7:48 AM, David Fetter da...@fetter.org wrote: On Wed, Jan 05, 2011 at 09:04:08AM -0500, Robert Haas wrote: On Tue, Jan 4, 2011 at 10:56 PM, Rob Wultsch wult...@gmail.com wrote: 1. Could the making a table logged be a non-exclusive lock if the ALTER is allowed to take a full checkpoint? No, that doesn't solve either of the two problems I described, unfortunately. That is too bad. 2. Unlogged to logged has giant use case. Agree. 3. In MySQL I have had to ALTER tables to engine BLACKHOLE because they held data that was not vital, but the server was out of IO. Going logged - unlogged has a significant placed, I think. Interesting. So you'd change a logged table into an unlogged table to cut down on I/O, and take the risk of losing the data if the server went down? BLACKHOLE is a storage engine that's equivalent to /dev/null, so it wasn't a risk /per se/. Exactly. It was data I could live without and by having schema attached to /dev/null the application did not error out and die. It is a very bad option and being able to turn off logging for a table is a much better one. -- Rob Wultsch wult...@gmail.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] making an unlogged table logged
On Tue, Jan 4, 2011 at 7:41 PM, Robert Haas robertmh...@gmail.com wrote: Somebody asked about this on Depesz's blog today, and I think it's come up here before too, so I thought it might be worth my writing up a few comments on this. I don't think I'm going to have time to work on this any time soon, but if someone else wants to work up a patch, I'm game to review. I think it'd clearly be a good feature. Generally, to do this, it would be necessary to do the following things (plus anything I'm forgetting): 1. Take an AccessExclusiveLock on the target table. You might think that concurrent selects could be allowed, but I believe that's not the case. Read on. 2. Verify that there are no foreign keys referencing other unlogged tables, because if that were the case then after the change we'd have a permanent table referencing an unlogged table, which would violate referential integrity. (Note that unlogged referencing permanent is OK, but permanent referencing unlogged is a no-no, so what matters when upgrading is outbound foreign keys.) 3. Write out all shared buffers for the target table, and drop them. This ensures that there are no buffers floating around for the target relation that are marked BM_UNLOGGED, which would be a bad thing. Or maybe it's possible to just clear the BM_UNLOGGED flag, instead of dropping them. This is the step that makes me think we need an access exclusive lock - otherwise, somebody else might read in a buffer and, seeing that the relation is unlogged (which is true, since we haven't committed yet), mark it BM_UNLOGGED. 4. fsync() any segments of the target relation - of any fork except that init fork - that might have dirty pages not on disk. 5. Arrange for the appropriate file deletions at commit or abort, by updating pendingDeletes. On commit, we want to delete the init fork for the table and all its indexes. On abort, we want to delete everything else, but only for pretend; that is, the abort record should reflect the deletions since they'll need to happen on any standbys, but we shouldn't actually perform them on the master since we don't want to obliterate the contents of the table for no reason. There's a subtle problem here I'm not quite sure how to deal with: what happens if we *crash* without writing an abort record? It seems like that could leave a stray file around on a standby, because the current code only cleans things up on the standby at the start of recovery; to make this bullet-proof, I suppose it'd need to repeat that every time a crash happens on the master, but I don't know how to do that. Note also that if wal_level is minimal, then we need only worry about the commit case; the abort case can be a no-op. 6. If wal_level != minimal, XLOG every page of every fork except the init fork, for both the table and the associated indexes. (Note that this step also requires an AccessExclusiveLock rather than some weaker lock, because of the arbitrary rule that only AccessExclusiveLocks are sent to standbys. If we held only ShareRowExclusiveLock on the master, for example, a Hot Standby backend might read the table while it's only been half-copied.) 7. Update pg_class.relpersistence from 'u' to 'p', for both the table and the associated indexes. Going the other direction ought to be possible too, although it seems somewhat less useful. For that, you'd need to flip around the check in step #2 (i.e. check for a reference FROM a permanent table), perform step #3, skip step #4, do step #5 backwards (create and log init forks, arranging for them to be removed on abort - this too has an issue with crashes that don't leave abort records behind); and arrange for the rest of the forks to be removed on commit on any standby without doing it on the master), skip step #6, and do step #7 backwards. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company A couple thoughts: 1. Could the making a table logged be a non-exclusive lock if the ALTER is allowed to take a full checkpoint? 2. Unlogged to logged has giant use case. 3. In MySQL I have had to ALTER tables to engine BLACKHOLE because they held data that was not vital, but the server was out of IO. Going logged - unlogged has a significant placed, I think. -- Rob Wultsch wult...@gmail.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] Can postgres create a file with physically continuous blocks.
On Tue, Dec 21, 2010 at 4:49 AM, Robert Haas robertmh...@gmail.com wrote: On Sun, Dec 19, 2010 at 1:10 PM, Jim Nasby j...@nasby.net wrote: On Dec 19, 2010, at 1:10 AM, flyusa2010 fly wrote: Does postgres make an effort to create a file with physically continuous blocks? AFAIK all files are expanded as needed. I don't think there's any flags you can pass to the filesystem to tell it this file will eventually be 1GB in size. So, we're basically at the mercy of the FS to try and keep things contiguous. There have been some reports that we would do better on some filesystems if we extended the file more than a block at a time, as we do today. However, AFAIK, no one is pursuing this ATM. The has been found to be the case in the MySQL world, particularly when ext3 is in use: http://forge.mysql.com/worklog/task.php?id=4925 http://www.facebook.com/note.php?note_id=194501560932 Also, InnoDB has an option for how much data should be allocated at the end of a tablespace when it needs to grow: http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_data_file_path -- Rob Wultsch wult...@gmail.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] Can postgres create a file with physically continuous blocks.
On Wed, Dec 22, 2010 at 12:15 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 22.12.2010 03:45, Rob Wultsch wrote: On Tue, Dec 21, 2010 at 4:49 AM, Robert Haasrobertmh...@gmail.com wrote: On Sun, Dec 19, 2010 at 1:10 PM, Jim Nasbyj...@nasby.net wrote: On Dec 19, 2010, at 1:10 AM, flyusa2010 fly wrote: Does postgres make an effort to create a file with physically continuous blocks? AFAIK all files are expanded as needed. I don't think there's any flags you can pass to the filesystem to tell it this file will eventually be 1GB in size. So, we're basically at the mercy of the FS to try and keep things contiguous. There have been some reports that we would do better on some filesystems if we extended the file more than a block at a time, as we do today. However, AFAIK, no one is pursuing this ATM. The has been found to be the case in the MySQL world, particularly when ext3 is in use: http://forge.mysql.com/worklog/task.php?id=4925 http://www.facebook.com/note.php?note_id=194501560932 These seem to be about extending the transaction log, and we already pre-allocate the WAL. The WAL is repeatedly fsync'd, so I can understand that extending that in small chunks would hurt performance a lot, as the filesystem needs to flush the metadata changes to disk at every commit. However, that's not an issue with extending data files, they are only fsync'd at checkpoints. It might well be advantageous to extend data files in larger chunks too, but it's probably nowhere near as important as with the WAL. Agree. Also, InnoDB has an option for how much data should be allocated at the end of a tablespace when it needs to grow: http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_data_file_path Hmm, innodb_autoextend_increment seems more like what we're discussing here (http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_autoextend_increment). If I'm reading that correctly, InnoDB defaults to extending files in 8MB chunks. This is not pure apples to apples as InnoDB does direct io, however doesn't the checkpoint completion target code call fsync repeatedly in order to achieve the check point completion target? And for that matter, haven't there been recent discussion on hackers about calling fsync more often? Sorry for the loopy email. I have not been getting anywhere near enough sleep recently :( -- Rob Wultsch wult...@gmail.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] ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
On Sun, Dec 12, 2010 at 4:49 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Dec 12, 2010 at 6:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Sun, 2010-12-12 at 17:57 -0500, Tom Lane wrote: Huh? It allows you to postpone the check until commit. That's far from not enforcing it. This clearly implies that un-enforced constraints are not checked at commit. [ shrug... ] I can't argue with you about what may or may not be in an unpublished draft of an unratified version of the standard, since I don't have a copy. But allow me to harbor doubts that they really intend to allow someone to force a constraint to be considered valid without any verification. This proposal strikes me as something mysql would do, not the standards committee. (In particular, can a constraint go from not-enforced to enforced state without getting checked at that time?) Even if you're reading the draft correctly, and the wording makes it into a released standard, the implementation you propose would break our code. The incremental FK checks are designed on the assumption that the constraint condition held before; they aren't likely to behave very sanely if the data is bad. I'd want to see a whole lot more analysis of the resulting behavior before even considering an idea like this. Wow, you've managed to bash Simon, MySQL, and the SQL standards committee all in one email. I'm not going to argue that careful analysis isn't needed before doing something like this - and, in particular, if we ever get inner-join removal, which I'm still hoping to do at some point, a foreign key that isn't actually guaranteed to be valid might result in queries returning different answers depending on whether or not a join is removed. I guess we'd have to define that as the user's problem for alleging a foreign-key relationship that doesn't truly exist. On the other hand, there's clearly also a use case for this behavior. If a bulk load of prevalidated data forces an expensive revalidation of constraints that are already known to hold, there's a real chance the DBA will be backed into a corner where he simply has no choice but to not use foreign keys, even though he might really want to validate the foreign-key relationships on a going-forward basis. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company MySQL does in fact have this feature and it is used by mysqldump. This feature is very useful. -- Rob Wultsch wult...@gmail.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] unlogged tables
On Fri, Dec 10, 2010 at 5:34 PM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: 2010/12/8 Kineticode Billing da...@kineticode.com: On Dec 8, 2010, at 10:37 AM, Chris Browne wrote: Other possibilities include TRANSIENT, EPHEMERAL, TRANSIENT, TENUOUS. EVANESCENT. UNSAFE ? troll MyISAM /troll -- Rob Wultsch wult...@gmail.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] ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
On Sun, Dec 12, 2010 at 7:24 PM, Andrew Dunstan and...@dunslane.net wrote: In fact it's possible now to disable FK enforcement, by disabling the triggers. It's definitely a footgun though. Just the other day I was asked how data violating the constraint could have got into the table, and caused some surprise by demonstrating how easy this was to produce. Ugh. I have read the entire pg manual and I did not recall that footgun. At least in MySQL disabling fk's is explicit. There is something to be said for being able to tell the database: Hey, hold my beer and watch this, it might be stupid but it is what we are going to do. The database telling it's user that is a much larger issue (and yes, MySQL is generally worse). The user at least gets to talk to db through sql, the database only really gets to talk to the user through errors and the manual. The fact that fk checks are implemented by the trigger system somehow seems surprising. -- Rob Wultsch wult...@gmail.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] unlogged tables
On Sun, Dec 12, 2010 at 7:33 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Dec 12, 2010 at 9:31 PM, Rob Wultsch wult...@gmail.com wrote: On Fri, Dec 10, 2010 at 5:34 PM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: 2010/12/8 Kineticode Billing da...@kineticode.com: On Dec 8, 2010, at 10:37 AM, Chris Browne wrote: Other possibilities include TRANSIENT, EPHEMERAL, TRANSIENT, TENUOUS. EVANESCENT. UNSAFE ? troll MyISAM /troll Heh. But that would be corrupt-on-crash, not truncate-on-crash, no? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company troll Yep. Truncate-on-shutdown MySQL options are the MEMORY and PBXT (using the memory resident option). /troll I like TRANSIENT but wonder if MEMORY might be more easily understood by users. -- Rob Wultsch wult...@gmail.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] profiling connection overhead
On Sun, Dec 5, 2010 at 11:59 AM, Josh Berkus j...@agliodbs.com wrote: * no coordination of restarts/configuration changes between the cluster and the pooler * you have two pieces of config files to configure your pooling settings (having all that available say in a catalog in pg would be awesome) * you lose all of the advanced authentication features of pg (because all connections need to go through the pooler) and also ip-based stuff * no SSL support(in the case of pgbouncer) * complexity in reseting backend state (we added some support for that through explicit SQL level commands in recent releases but it still is a hazard) More: * pooler logs to separate file, for which there are (currently) no anaysis tools * pooling is incompatible with the use of ROLES for data security The last is a major issue, and not one I think we can easily resolve. MySQL has a pooling-friendly user system, because when you connect to MySQL you basically always connect as the superuser and on connection it switches you to your chosen login role. This, per Rob Wulsch, is one of the things at the heart of allowing MySQL to support 100,000 low frequency users per cheap hosting system. As you might imagine, this behavior is also the source of a lot of MySQL's security bugs. I don't see how we could imitate it without getting the bugs as well. I think you have read a bit more into what I have said than is correct. MySQL can deal with thousands of users and separate schemas on commodity hardware. There are many design decisions (some questionable) that have made MySQL much better in a shared hosting environment than pg and I don't know where the grants system falls into that. MySQL does not have that many security problems because of how grants are stored. Most MySQL security issues are DOS sort of stuff based on a authenticated user being able to cause a crash. The decoupled backend storage and a less than awesome parser shared most of the blame for these issues. One thing I would suggest that the PG community keeps in mind while talking about built in connection process caching, is that it is very nice feature for memory leaks caused by a connection to not exist for and continue growing forever. NOTE: 100k is not a number that I would put much stock in. I don't recall ever mentioning that number and it is not a number that would be truthful for me to throw out. -- Rob Wultsch wult...@gmail.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] profiling connection overhead
On Sun, Dec 5, 2010 at 12:45 PM, Rob Wultsch wult...@gmail.com wrote: One thing I would suggest that the PG community keeps in mind while talking about built in connection process caching, is that it is very nice feature for memory leaks caused by a connection to not exist for and continue growing forever. s/not exist for/not exist/ I have had issues with very slow leaks in MySQL building up over months. It really sucks to have to go to management to ask for downtime because of a slow memory leak. -- Rob Wultsch wult...@gmail.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] Spread checkpoint sync
On Sun, Dec 5, 2010 at 2:53 PM, Greg Smith g...@2ndquadrant.com wrote: Heikki Linnakangas wrote: If you fsync() a file with one dirty page in it, it's going to return very quickly, but a 1GB file will take a while. That could be problematic if you have a thousand small files and a couple of big ones, as you would want to reserve more time for the big ones. I'm not sure what to do about it, maybe it's not a problem in practice. It's a problem in practice allright, with the bulk-loading situation being the main one you'll hit it. If somebody is running a giant COPY to populate a table at the time the checkpoint starts, there's probably a 1GB file of dirty data that's unsynced around there somewhere. I think doing anything about that situation requires an additional leap in thinking about buffer cache evicition and fsync absorption though. Ultimately I think we'll end up doing sync calls for relations that have gone cold for a while all the time as part of BGW activity, not just at checkpoint time, to try and avoid this whole area better. That's a lot more than I'm trying to do in my first pass of improvements though. In the interest of cutting the number of messy items left in the official CommitFest, I'm going to mark my patch here Returned with Feedback and continue working in the general direction I was already going. Concept shared, underlying patches continue to advance, good discussion around it; those were my goals for this CF and I think we're there. I have a good idea how to autotune the sync spread that's hardcoded in the current patch. I'll work on finishing that up and organizing some more extensive performance tests. Right now I'm more concerned about finishing the tests around the wal_sync_method issues, which are related to this and need to get sorted out a bit more urgently. -- Greg Smith 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Forgive me, but is all of this a step on the slippery slope to direction io? And is this a bad thing? -- Rob Wultsch wult...@gmail.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] profiling connection overhead
On Sun, Dec 5, 2010 at 6:59 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Dec 5, 2010 at 2:45 PM, Rob Wultsch wult...@gmail.com wrote: I think you have read a bit more into what I have said than is correct. MySQL can deal with thousands of users and separate schemas on commodity hardware. There are many design decisions (some questionable) that have made MySQL much better in a shared hosting environment than pg and I don't know where the grants system falls into that. Objection: Vague. I retract the remark, your honor. At some point Hackers should look at pg vs MySQL multi tenantry but it is way tangential today. -- Rob Wultsch wult...@gmail.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] DELETE with LIMIT (or my first hack)
On Wed, Dec 1, 2010 at 4:01 AM, Daniel Loureiro loureir...@gmail.com wrote: A) an feature MySQL-like which will DELETE/UPDATE just K tuples B) an feature to protect the database in case the DBA forget the WHERE statement MySQL has B as well. To quote the manual: For beginners, a useful startup option is --safe-updates (or --i-am-a-dummy, which has the same effect). This option was introduced in MySQL 3.23.11. It is helpful for cases when you might have issued a DELETE FROM tbl_name statement but forgotten the WHERE clause. Normally, such a statement deletes all rows from the table. With --safe-updates, you can delete rows only by specifying the key values that identify them. This helps prevent accidents. ... * You are not permitted to execute an UPDATE or DELETE statement unless you specify a key constraint in the WHERE clause or provide a LIMIT clause (or both). For example: UPDATE tbl_name SET not_key_column=val WHERE key_column=val; UPDATE tbl_name SET not_key_column=val LIMIT 1; * The server limits all large SELECT results to 1,000 rows unless the statement includes a LIMIT clause. * The server aborts multiple-table SELECT statements that probably need to examine more than 1,000,000 row combinations. I have actually suggested that a certain subset of my users only connect to the database if they are willing to use the --i-am-a-dummy flag. -- Rob Wultsch wult...@gmail.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] crash-safe visibility map, take three
On Mon, Nov 29, 2010 at 9:57 PM, Robert Haas robertmh...@gmail.com wrote: 1. Pin each visibility map page. If any VM_BECOMING_ALL_VISIBLE bits are set, take the exclusive content lock for long enough to clear them. I wonder what the performance hit will be to workloads with contention and if this feature should be optional. -- Rob Wultsch wult...@gmail.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] DELETE with LIMIT (or my first hack)
On Mon, Nov 29, 2010 at 10:50 PM, Marti Raudsepp ma...@juffo.org wrote: On Tue, Nov 30, 2010 at 05:09, Jaime Casanova ja...@2ndquadrant.com wrote: at least IMHO the only sensible way that LIMIT is usefull is with an ORDER BY clause with make the results very well defined... DELETE with LIMIT is also useful for deleting things in batches, so you can do large deletes on a live system without starving other users from I/O. In this case deletion order doesn't matter (it's more efficient to delete rows in physical table order) -- ORDER BY isn't necessary. Regards, Marti ++ I have a lot of DELETE with LIMIT in my (mysql) environment for this reason. -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can we tell how far behind the standby is?
On Fri, Nov 5, 2010 at 5:39 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Nov 5, 2010 at 2:46 PM, Josh Berkus j...@agliodbs.com wrote: I'm continuing in my efforts now to document how to deploy and manage replication on our wiki. One of the things a DBA needs to do is to use pg_current_xlog_location() (and related functions) to check how far behind the master the standby is. However, there's some serious problems with that: (1) comparing these numbers is quite mathematically complex -- and, for that matter, undocumented. (2) pg_rotate_xlog and/or archive_timeout will create a gap in the xlog positions, quite a large one if it happens near the beginning of a file. There is no way for any monitoring on the standby to tell the difference between a gap created by forced rotation as opposed to being most of a file behind, until the next record shows up. Hello, nagios false alerts! (3) There is no easy way to relate a difference in log positions to an amount of time. I'll work on some tools to make this a bit more palatable, but I disagree with earlier assertions that we have the replication monitoring done. There's still a *lot* of work to do. I've heard the same complaint, and I agree with your concerns. All this has happened before, and all of it will happen again. At this point pg has the equivalent of MySQL's show slave status in 4.0. The output of that change significantly over time: http://dev.mysql.com/doc/refman/4.1/en/show-slave-status.html http://dev.mysql.com/doc/refman/5.5/en/show-slave-status.html Also of interest http://dev.mysql.com/doc/refman/4.1/en/show-binary-logs.html -- Rob Wultsch wult...@gmail.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] max_wal_senders must die
On Tue, Oct 19, 2010 at 12:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Oct 19, 2010 at 12:18 PM, Josh Berkus j...@agliodbs.com wrote: On 10/19/2010 09:06 AM, Greg Smith wrote: I think Magnus's idea to bump the default to 5 triages the worst of the annoyance here, without dropping the feature (which has uses) or waiting for new development to complete. Setting max_wal_senders to a non-zero value causes additional work to be done every time a transaction commits, aborts, or is prepared. Yes. This isn't just a numeric parameter; it's also a boolean indicating do I want to pay the overhead to be prepared to be a replication master?. Josh has completely failed to make a case that that should be the default. In fact, the system would fail to start at all if we just changed the default for max_wal_senders and not the default for wal_level. regards, tom lane If the variable is altered such that it is dynamic, could it not be updated by the postmaster when a connection attempts to begin replicating? -- Rob Wultsch wult...@gmail.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 with Quorum Commit
* On 10/8/10, Fujii Masao masao.fu...@gmail.com wrote: On Fri, Oct 8, 2010 at 5:10 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Do we really need that? Yes. But if there is no unsent WAL when the master goes down, we can start new standby without new backup by copying the timeline history file from new master to new standby and setting recovery_target_timeline to 'latest'. In this case, new standby advances the recovery to the latest timeline ID which new master uses before connecting to the master. This seems to have been successful in my test environment. Though I'm missing something. I don't think that's acceptable, we'll need to fix that if that's the case. Agreed. You can cross timelines with the archive, though. But IIRC there was some issue with that too, you needed to restart the standbys because the standby scans what timelines exist at the beginning of recovery, and won't notice new timelines that appear after that? Yes. We need to address that, apart from any of the other things discussed wrt. synchronous replication. It will benefit asynchronous replication too. IMHO *that* is the next thing we should do, the next patch we commit. You mean to commit that capability before synchronous replication? If so, I disagree with you. I think that it's not easy to address that problem. So I'm worried about that implementing that capability first means the miss of sync rep in 9.1. 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 -- Rob Wultsch wult...@gmail.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] configure gaps
On Sat, Oct 2, 2010 at 10:01 AM, Andrew Dunstan and...@dunslane.net wrote: I'm, not sure if this is worth fixing, but it seemed worth mentioning. ISTM that if we pass configure, we should be able to assume that the requisite build tools, libraries, include files etc. are present. trolling One of the things I really like about drizzle is if there a missing dependency it will explicitly tell you what you are missing and where to go find it for popular platforms. Not being able to easily build is a barrier to entry. Does pg want those? /trolling -- Rob Wultsch wult...@gmail.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] update on global temporary and unlogged tables
On Mon, Sep 6, 2010 at 7:55 PM, Robert Haas robertmh...@gmail.com wrote: 3. With respect to unlogged tables, the major obstacle seems to be figuring out a way for these to get automatically truncated at startup time. (please forgive what is probably a stupid question) By truncate do mean reduce the table to a very small number (or zero) number of pages? Is there a case to be made for instead somehow marking all pages as available for reuse? Deallocating and reallocating space can be expensive. -- Rob Wultsch wult...@gmail.com
Re: [HACKERS] Return of the Solaris vacuum polling problem -- anyone remember this?
For a documentation patch should this not be back ported to all relevant versions? On 8/21/10, Bruce Momjian br...@momjian.us wrote: Josh Berkus wrote: On further reflection, though: since we put in the BufferAccessStrategy code, which was in 8.3, the background writer isn't *supposed* to be very much involved in writing pages that are dirtied by VACUUM. VACUUM runs in a small ring of buffers and is supposed to have to clean its own dirt most of the time. So it's wrong to blame this on the bgwriter not holding up its end. Rather, what you need to be thinking about is how come vacuum seems to be making lots of pages dirty on only one of these machines. This is an anti-wraparound vacuum, so it could have something to do with the hint bits. Maybe it's setting the freeze bit on every page, and writing them one page at a time? Still don't understand the call to pollsys, even so, though. We often mention that we do vacuum freeze for anti-wraparound vacuum, but not for pg_clog file removal, which is the primary trigger for autovacuum vacuum freezing. I have added the attached documentation patch for autovacuum_freeze_max_age; back-patched to 9.0. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Rob Wultsch wult...@gmail.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] SHOW TABLES
On Sun, Jul 18, 2010 at 11:58 AM, Andres Freund and...@anarazel.de wrote: On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote: SHOW ANY TABLE GROUP BY tablename HAVING array_agg(attributes) @ array['date'::regtype, 'time'::regtype]; Why is that in *any* way better than SELECT * FROM meta.tables ... Oh. The second looks like something I know. Oh. My editor maybe as well? Oh. And some other tools also? Your syntax also forgets that maybe I only need a subset of the information. I am quite a bit surprised about all this discussion. I have a very hard time we will find anything people agree about and can remember well enough to be usefull for both manual and automatic processing. I agree that the internal pg_* tables are not exactly easy to query. And that the information_schema. ones arent complete enough and have enough concept mismatch to be confusing. But why all this? Andres Do you have an alternative suggestion for emulating SHOW SCHEMAS SHOW TABLES DESC object? Make a user friendly interface is not easy, but it sure as heck is important. -- Rob Wultsch wult...@gmail.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] SHOW TABLES
On Fri, Jul 16, 2010 at 9:56 AM, Robert Haas robertmh...@gmail.com wrote: For committers. Perhaps this discussions should be moved to the General list in order to poll the userbase. My .02 is that SHOW commands (even if they are not compatible) would make it much easier for me to make an argument to my boss to at least consider moving off another open source database. The show commands are in *very* widespread use by the MySQL community even after ~5 years of having the i_s. The Drizzle team (a radical fork of MySQL) very briefly considered removing the SHOW commands and the unanimous objections that followed caused that idea to scrapped. -- Rob Wultsch wult...@gmail.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] SHOW TABLES
On Fri, Jul 16, 2010 at 10:52 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 16/07/10 20:11, Rob Wultsch wrote: On Fri, Jul 16, 2010 at 9:56 AM, Robert Haasrobertmh...@gmail.com wrote: For committers. Perhaps this discussions should be moved to the General list in order to poll the userbase. My .02 is that SHOW commands (even if they are not compatible) would make it much easier for me to make an argument to my boss to at least consider moving off another open source database. The show commands are in *very* widespread use by the MySQL community even after ~5 years of having the i_s. The Drizzle team (a radical fork of MySQL) very briefly considered removing the SHOW commands and the unanimous objections that followed caused that idea to scrapped. That's for MySQL. I come from a DB2 background, and when I started using psql years ago, I often typed LIST TABLES without thinking much about it. Not SHOW TABLES, but LIST TABLES. I bet Oracle users coming to PostgreSQL will try DESC. Not SHOW TABLES. As Simon listed, every DBMS out there has a different syntax for this. I have nothing against SHOW TABLES (it might cause conflicts in grammar though), but if we're going to cater to people migrating from MySQL, I feel we should cater to people migrating from other products too. But surely we're not going to implement 10 different syntaxes for the same thing! We could, however, give a hint in the syntax error in all those cases. That way we're not on the hook to maintain them forever, and we will be doing people a favor by introducing them to the backslash commands or information schema, which are more powerful. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com desc[ribe] also works in MySQL. Perhaps describe would be a good option: describe tables describe table table name (or perhaps descrive object?) describe schemas etc -- Rob Wultsch wult...@gmail.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] Functional dependencies and GROUP BY
On Mon, Jun 7, 2010 at 6:41 PM, Stephen Frost sfr...@snowman.net wrote: * Peter Eisentraut (pete...@gmx.net) wrote: This is frequently requested by MySQL converts (and possibly others). I'd certainly love to see it- but let's not confuse people by implying that it would actually act the way MySQL does. It wouldn't, because what MySQL does is alot closer to 'distinct on' and is patently insane to boot. Again, I'd *love* to see this be done in PG, but when we document it and tell people about it, *please* don't say it's similar in any way to MySQL's oh, we'll just pick a random value from the columns that aren't group'd on implementation. Preface: I work as a MySQL DBA (yeah, yeah, laugh it up...). It has been my experience that the vast majority of the time when a MySQL users make use of the fine feature which allows them to use unaggregated columns which is not present in the GROUP BY clause in an aggregating query they have made an error because they do not understand GROUP BY. I have found this lack of understanding to be very wide spread across the MySQL developer and *DBA* communities. I also would really hesitate to compare this useful feature to the *fine feature* present in MySQL. Due to a long standing bug (http://bugs.mysql.com/bug.php?id=8510) it really is not possible to get MySQL to behave sanely. It is my impression that many programs of significant size that interact with MySQL have errors because of this issue and it would be good to not claim to have made Postgres compatible. That said, I imagine if this feature could make it into the Postgres tree it would be very useful. Would I be correct in assuming that while this feature would make query planning more expensive, it would also often decrease the cost of execution? Best, Rob Wultsch wult...@gmail.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] List traffic
Linux has *as many if not more* ... MySQL, if memory servers, has a half dozen or more ... etc ... MySQL has a bunch of lists, none of which get much traffic. Honestly, they should probably be combined. -- Rob Wultsch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Row-level Locks SERIALIZABLE transactions, postgres vs. Oracle
On Fri, May 14, 2010 at 7:32 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Oracle, and all other MVCC databases I've read about outside of PostgreSQL, use an update in place with a rollback log technique. Have you looked at PBXT (which is explicitly NOT SERIALIZABLE)? -- Rob Wultsch wult...@gmail.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] Row-level Locks SERIALIZABLE transactions, postgres vs. Oracle
On Sat, May 15, 2010 at 4:09 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Anything in particular you wanted me to notice about it besides that? Nope. It was just a counter point to your previous comment. -- Rob Wultsch wult...@gmail.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] List traffic
On Fri, May 14, 2010 at 11:50 PM, Rob Wultsch wult...@gmail.com wrote: Linux has *as many if not more* ... MySQL, if memory servers, has a half dozen or more ... etc ... MySQL has a bunch of lists, none of which get much traffic. Honestly, they should probably be combined. -- Rob Wultsch They was referring to the various low traffic MySQL lists which in my opinion does not work. As far as Linux, when I briefly subscribed to the kernel mailing list there was such a volume of traffic that it was difficult to manage as a noob. I do not have an opinion about PG. I think that those two examples could be seen as how not to run email lists effectively. -- Rob Wultsch wult...@gmail.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] max_standby_delay considered harmful
On Wed, May 5, 2010 at 9:32 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, May 5, 2010 at 11:50 PM, Bruce Momjian br...@momjian.us wrote: If someone wants to suggest that HS is useless if max_standby_delay supports only boolean values, I am ready to suggest we remove HS as well and head to 9.0 because that would suggest that HS itself is going to be useless. I think HS is going to be a lot less useful than many people think, at least in 9.0. But I think ripping out max_standby_delay will make it worse. The code will not be thrown away; we will bring it back for 9.1. If that's the case, then taking it out makes no sense. mysql dba troll I manage a bunch of different environments and I am pretty sure that in any of them if the db started seemingly randomly killing queries I would have application teams followed quickly by executives coming after me with torches and pitchforks. I can not imagine setting this value to anything other than a bool and most of the time that bool would be -1. I would only be unleashing a kill storm in utter desperation and I would probably need to explain myself in detail after. Utter desperation means I am sure I am going to have to do a impactful failover at any moment and need a slave completely up to date NOW. It is good to have the option to automatically cancel queries, but I think it is a mistake to assume many people will use it. What I would really need for instrumentation is the ability to determine *easily* how much a slave is lagging in clock time. /mysql dba troll -- Rob Wultsch wult...@gmail.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 rev 2: MySQL-ism help patch for psql
On Tue, Jan 19, 2010 at 5:01 PM, David Christensen da...@endpoint.com wrote: On Jan 19, 2010, at 4:23 PM, Robert Haas wrote: On Tue, Jan 19, 2010 at 5:14 PM, David E. Wheeler da...@kineticode.com wrote: Why would they want more? It's not MySQL, and they know that. If we give them some very minor helpful hints for the most common things they try to do, it would be a huge benefit to them. I know I've badly wanted the opposite when I've had to use MySQL, but I don't expect MySQL to implement \c for me. +1. I think this is a well-thought out proposal. I like Tom's suggestion upthread for how to handle \c. I've attached a second revision of this patch incorporating the various feedback I've received. Although the deadline for patches for 8.5 has supposedly already passed Yeah, I realized this after I scratched my itch, and had just thought I would send to the list any way for after the CF; you can commit or bump as needed. Patch enclosed as a context-diff attachment this time. Regards, David -- David Christensen End Point Corporation da...@endpoint.com Although I have a snowballs chance in hell to convert my coworkers to using pg I think that this patch would make such an outcome more likely. Please consider what a MySQL dba does when he gets a call at 3AM that a server (p3.any43.db69.I_have_no_clue_what_this_stupid_f'ing_server_is.wtf.pg ) is at max-connections. I think that some helpful hints for non-pg dba's that are using pg in some capacity are a very good idea. -- Rob Wultsch wult...@gmail.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] MySQL-ism help patch for psql
On Tue, Jan 19, 2010 at 3:14 PM, David E. Wheeler da...@kineticode.com wrote:
On Jan 19, 2010, at 1:39 PM, Tom Lane wrote:
I thought Magnus had a really good point: covering these four cases will
probably be enough to teach newbies to look at psql's backslash
commands. And once they absorb that, we're over a big hump.
+1
On Jan 19, 2010, at 1:57 PM, Devrim GÜNDÜZ wrote:
I disagree. If they want to use PostgreSQL, they should learn our
syntax. How can you make sure that this will be enough for them? What if
they want more?
Why would they want more? It's not MySQL, and they know that. If we give them
some very minor helpful hints for the most common things they try to do, it
would be a huge benefit to them. I know I've badly wanted the opposite when
I've had to use MySQL, but I don't expect MySQL to implement \c for me.
What if some other people will come up with the idea of adding similar
functionality for their favorite database? The only exception will be
Informix IMHO, because of historical reasons.
I think it'd be helpful for other databases, too. Oracle comes to mind: What
commands are finger-trained in Oracle DBAs?
Best,
David
As a MySQL DBA the commands I think are most useful are:
show databases (please punt this, most MySQL dba's that I have worked
with will need to consider the difference between a db and a schema)
use database (please punt)
LOAD DATA INFILE(please punt, they should look at the manual as COPY
is... well, more limited)
show tables
desc(ribe) table
show processlist
I suggest adding:
+ else if (MYSQL_HELP_CHECK(show processlist))
+ {
+ MYSQL_HELP_OUTPUT(SELECT * from
pg_stat_activity);
+ }
+ else if (MYSQL_HELP_CHECK(desc))
+ {
+ MYSQL_HELP_OUTPUT(\\d tablename);
+ }
--
Rob Wultsch
wult...@gmail.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] Disable and enable of table and column constraints
On Tue, Sep 8, 2009 at 1:07 PM, Alvaro Herreraalvhe...@commandprompt.com wrote: Tom Lane wrote: Michael Gould mgo...@intermodalsoftwaresolutions.net writes: It would be nice if we could enable and disable column and table constraints. I believe that you can do this in Oracle but this is very handy for testing stored procedures and other external processes. Drop the constraint and re-add it later... That's not very useful when adding it later means grabbing an exclusive lock on the table for the whole duration of the full table scan required to check the table. Actually something in this area is on my plate too -- a customer of ours wants to be able to define constraints but not have it checked immediately. I envision it similar to how concurrent index creation works: the constraint is created as not checked and the transaction is committed; new insertions are checked against the constraint. Then the table is scanned to verify that extant tuples pass the constraint, _without_ the exclusive lock on the table. Both DB2 and Oracle have an ENFORCE setting for constraints, and a MySQL blog hinted some time ago that it might be in SQL 201x. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. The mysql'ism foreign_key_checks would seem to do similar things...? (http://dev.mysql.com/doc/refman/5.1/en/server-session-variables.html#sysvar_foreign_key_checks ) -- Rob Wultsch wult...@gmail.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] MySQL Compatibility WAS: 8.5 release timetable, again
2009/8/27 Jaime Casanova jcasa...@systemguards.com.ec: 2009/8/27 Jean-Michel Pouré j...@poure.com: Otherwise, replicating some MySQL SQL syntax will not work. As you know, people willing to use PostgreSQL replication are possibly already MySQL replication users. So if they test and PostgreSQL fails, this is too bad. yeah! but some times the reason MySQL is not failing on those cases is because it's broken... Consider this one: http://archives.postgresql.org/pgsql-general/2005-12/msg00487.php we don't want to copy the bugs nor the bad designs decisions from MySQL, no that everything from MySQL is bad but i would be scary if we start supporting every single piece of code MySQL accepts And that behavior has changed to be sane in 5.0+, iirc. -- Rob Wultsch wult...@gmail.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] SET syntax in INSERT
Given the recent discussion of DELETE syntax on JOINS I thought it
might be interesting to bring a bit MySQL syntax that is in somewhat
widespread use, generally create somewhat cleaner code and I imagine
would not break much if implemented.
MySQL allows INSERTs of the form:
INSERT INTO t SET
col1='val1',
col2='va21',
col3='val3',
col4='val4',
col5='val5',
col6='val6',
col7='val7',
col8='val8',
col9='val9',
col10='val10',
col11='val11',
col12='val12',
col13='val13',
col14='val14',
col15='val15';
Which I think sometimes compares very favorably
INSERT INTO t
(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15)
VALUES
('val1','val2','val3','val4','val5','val6','val7','val8','val9','val10','val11','val12','val13','val14','val15')
Probably a pipe dream...
--
Rob Wultsch
wult...@gmail.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] SET syntax in INSERT
On Tue, Aug 25, 2009 at 10:36 AM, Pavel Stehulepavel.steh...@gmail.com wrote:
2009/8/25 Rob Wultsch wult...@gmail.com:
Given the recent discussion of DELETE syntax on JOINS I thought it
might be interesting to bring a bit MySQL syntax that is in somewhat
widespread use, generally create somewhat cleaner code and I imagine
would not break much if implemented.
MySQL allows INSERTs of the form:
INSERT INTO t SET
col1='val1',
col2='va21',
col3='val3',
col4='val4',
col5='val5',
col6='val6',
col7='val7',
col8='val8',
col9='val9',
col10='val10',
col11='val11',
col12='val12',
col13='val13',
col14='val14',
col15='val15';
Which I think sometimes compares very favorably
INSERT INTO t
(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15)
VALUES
('val1','val2','val3','val4','val5','val6','val7','val8','val9','val10','val11','val12','val13','val14','val15')
Probably a pipe dream...
-1 PostgreSQL isn't MySQL!
Regards
Pavel Stehule
For an insert with many columns or with large value this syntax can
significantly improve readability. So it wasn't invented here, so
what? I don't see a downside to allowing this syntax other than MySQL
used it first, and there are multiple upsides (readability, easier
transitions).
--
Rob Wultsch
wult...@gmail.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
