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] psql filename completion: quoting
Excerpts from Noah Misch's message of sáb ene 14 12:20:18 -0300 2012: Occasionally, I have a SQL file destined for psql's \i command whose name contains a space. Less often, I'll have a .csv destined for \copy with the same problem. psql's filename completion does not handle these well. It completes on the literal name, but the commands will only recognize quoted names. For example, given a file foo bar, \i fTAB will complete to \i foo bar, which will not execute. If I type \i 'fTAB, completion will not help at all. The attached patch wraps rl_filename_completion_function() to dequote on input and enquote on output. Now, \i fTAB and \i 'fTAB will both complete to \i 'foo bar', which executes as expected. The new code handles embedded whitespace, quotes, and backslashes. Nice -- thanks, pushed. tab-complete.c works in terms of whitespace-separated words. As such, \i 'foo bTAB does not complete, because tab-complete.c has no notion of quotes affecting token boundaries. It thinks 'foo is one token and b is another. Yeah, it's a bit annoying if you have both a b and a c. If you have a file named a\b, attempting to complete past a\\ doesn't work either. (If you have 'a\\ it does work, however). -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. 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] Trigger execution role (was: Triggers with DO functionality)
2012/2/28 Tom Lane t...@sss.pgh.pa.us: Christopher Browne cbbro...@gmail.com writes: On Mon, Feb 27, 2012 at 6:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: So, whatever the desirability of having them run as table owner, we can't just up and change that. I'm inclined to hold to the argument that it Works Properly Now, and that we shouldn't break it by changing it. I would say the same, or at least that any argument for changing it is probably not strong enough to trump backwards compatibility. +1 However, Peter seems to think the other way is required by standard. We can get away with defining whatever behavior we want for triggers that invoke functions, since that syntax is nonstandard anyway. But, if you remember the original point of this thread, it was to add syntax that is pretty nearly equivalent to the spec's. If we're going to do that, it had better also have semantics similar to the spec's. So (assuming Peter has read the spec correctly) I'm coming around to the idea that the anonymous trigger functions created by this syntax ought to be SECURITY DEFINER table_owner. It should be strange if using two forms of one code can have two relative different behave. Actually we are in opposition to spec, because it expect SECURITY DEFINER for all stored procedures. All logic about rights are consistent now and I am not for changes in this area. Regards Pavel 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] misleading error message from connectMaintenanceDatabase()
I noticed a misleading error message recently while using createdb. Try: test=# CREATE ROLE dummy NOLOGIN; Now, attempt to use createdb as that role. Here's 9.1.1: $ createdb -Udummy testdb createdb: could not connect to database postgres: FATAL: role dummy is not permitted to log in And here is git head: $ createdb -Udummy testdb createdb: could not connect to databases postgres or template1 Please specify an alternative maintenance database. Try createdb --help for more information. Although I guess you could argue the latter message is technically correct, since could not connect is true, the first error message seems much more helpful. Plus, Please specify an alternative maintenance database is a rather misleading hint to be giving in this situation. This seems to be happening because connectMaintenanceDatabase() is passing fail_ok = true to connectDatabase(), which in turn just returns NULL and doesn't print a PQerrorMessage() for the failed conn. So connectMaintenanceDatabase() has no idea why the connection really failed. A simple fix would be just to pass fail_ok = false for the last connectDatabase() call inside connectMaintenanceDatabase(), and give up on trying to tack on a likely-misleading hint about the maintenance database. Patch attached. This leads to: $ createdb -Udummy testdb createdb: could not connect to database template1: FATAL: role dummy is not permitted to log in which is almost the same as the 9.1.1 output, with the exception that template1 is mentioned by default instead of the postgres database. Josh connectMDB_error.diff 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] Checkpointer vs pg_stat_bgwriter
On Mon, Feb 27, 2012 at 4:24 AM, Magnus Hagander mag...@hagander.net wrote: Hi! I admit to not having actually tested this since I don't have a good cluster to test it on right now, but from what I can tell the code in the new checkpointer process only sends statistics to the collector once the checkpoint is finished (checkpointer.c, line 549). The 9.1 and earlier sent this every time they entered a delay state (in BgWriterNap() called from CheckpointWriteDelay()). So in 9.1 and earlier we could see how a checkpoint wrote things as it was running, but in 9.2 we'll get it all as one big block at the end of the checkpoint - which can be a lot later in the spread case. Am I reading the code right? And if so, was this an intentional change, and if so why? To me it seems like a loss of functionality that should be fixed.. I agree that this should be fixed. 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] Initial 9.2 pgbench write results
On Feb 27, 2012 10:36 PM, Greg Smith g...@2ndquadrant.com wrote: One of the reasons I drilled right into this spot is because of fears that running the writer more often would sprout regressions in TPS. I can't explain exactly why exactly having backends write their own buffers out at the latest possible moment works significantly better in some cases here. But that fact isn't new to 9.2; it's just has a slightly higher potential to get in the way, now that the writing happens during the sync phase. My hypothesis for the TPS regression is that it is due to write combining. When the workload is mainly bound by I/O, every little bit that can be saved helps the bottomline. Larger scalefactors don't get the benefit because there is less write combining going on overall. Anyway, most people don't run their databases at 100% load. At lesser loads bgwriter should help end user latency. Is there a standard benchmark to measure that? -- Ants Aasma
Re: [HACKERS] xlog location arithmetic
On Sun, Feb 26, 2012 at 8:53 AM, Euler Taveira de Oliveira eu...@timbira.com wrote: On 25-02-2012 09:23, Magnus Hagander wrote: Do we even *need* the validate_xlog_location() function? If we just remove those calls, won't we still catch all the incorrectly formatted ones in the errors of the sscanf() calls? Or am I too deep into weekend-mode and missing something obvious? sscanf() is too fragile for input sanity check. Try pg_xlog_location_diff('12/3', '-10/0'), for example. I won't object removing that function if you protect xlog location input from silly users. After this patch will have been committed, it would be better to change pg_xlogfile_name() and pg_xlogfile_name_offset() so that they use the validate_xlog_location() function to validate the input. 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] How to know a table has been modified?
2012/2/28 Tatsuo Ishii is...@postgresql.org: Are you suggesting log_statement? I don't think it's a solution by following reasons: 1) it's slow to enable that on busy systems 2) tables affected by cascading delete/update/drop is not logged in PostgreSQL log What about reading archived WAL files? They would contain every delete/update/drop including cascading ones, though it might be too late because WAL files are not available until archived. xlogdump would help reading WAL files. https://github.com/snaga/xlogdump -- Shigeru Hanada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server
(2012/02/25 7:31), Peter Eisentraut wrote: Could we name this postgresql_fdw instead? We already have several ${productname}_fdw out there, and I don't want to get in the business of having to guess variant spellings. I worry name conflict with existing postgresql_fdw_validator, which is implemented in backend binary and used by contrib/dblink. I thought that we should use another name for PostgreSQL FDW unless we can change specification of dblink connection string. -- Shigeru Hanada -- Sent 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 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] foreign key locks, 2nd attempt
On Tue, Feb 28, 2012 at 12:28 AM, Noah Misch n...@leadboat.com wrote: On Mon, Feb 27, 2012 at 02:13:32PM +0200, Heikki Linnakangas wrote: On 23.02.2012 18:01, Alvaro Herrera wrote: As far as complexity, yeah, it's a lot more complex now -- no question about that. How about assigning a new, real, transaction id, to represent the group of transaction ids. The new transaction id would be treated as a subtransaction of the updater, and the xids of the lockers would be stored in the multixact-members slru. That way the multixact structures wouldn't need to survive a crash; you don't care about the shared lockers after a crash, and the xid of the updater would be safely stored as is in the xmax field. That way you wouldn't need to handle multixact wraparound, because we already handle xid wraparound, and you wouldn't need to make multixact slrus crash-safe. Not sure what the performance implications would be. You would use up xids more quickly, which would require more frequent anti-wraparound vacuuming. And if we just start using real xids as the key to multixact-offsets slru, we would need to extend that a lot more often. But I feel it would probably be acceptable. When a key locker arrives after the updater and creates this implicit subtransaction of the updater, how might you arrange for the xid's clog status to eventually get updated in accordance with the updater's outcome? Somewhat off-topic, but just seen another bad case of FK lock contention. Thanks for working on this everybody. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers