Re: [HACKERS] ECPG FETCH readahead
On Sun, Mar 04, 2012 at 05:34:50PM +0100, Boszormenyi Zoltan wrote: The program logic shouldn't change at all. He meant that extra coding effort is needed if you want manual caching. It requires 2 loops instead of 1 if you use FETCH N (N1). Ah, thanks for the explanation. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org Jabber: michael.meskes at googlemail dot com VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Scaling XLog insertion (was Re: [HACKERS] Moving more work outside WALInsertLock)
On 20.02.2012 08:00, Amit Kapila wrote: I was trying to understand this patch and had few doubts: 1. In PerformXLogInsert(), why there is need to check freespace when already during ReserveXLogInsertLocation(), the space is reserved. Is it possible that the record size is more than actually calculted in ReserveXLogInsertLocation(), if so in that case what I understand is it is moving to next page to write, however isn't it possible that some other backend had already reserved that space. The calculations between PerformXLogInsert (called CopyXLogRecordToWAL() in the latest patch version) and ReserveXLogInsertLocation() must always match, otherwise we have reserved incorrect amount of WAL and you get corrupt WAL. They both need to do the same calculations of how the WAL record is split across pages, which depends on how much free space there is on the first page. There is an assertion in CopyXLogRecordToWAL() to check that once it's finished writing the WAL record, the last byte landed on the position that ReserveXLogInsertLocation() calculated it would. Another way to do that would be to remember the calculations done in ReserveXLogInsertLocation(), in an extra array or something. But we want to keep ReserveXLogInsertLocation() as simple as possible, as that runs while holding the spinlock. Any extra CPU cycles there will hurt scalability. 2. In function WaitForXLogInsertionSlotToBecomeFree(), chances are there such that when nextslot equals lastslot, all new backends try to reserve a slot will start waiting on same last slot which can lead to serialization for those backends and can impact latency. True. That warrants some performance testing to see if that effect is significant. (it's surely better than the current situation, anyway, where all WAL insertions block on the single lock) 3. GetXlogBuffer - This will get called twice, once for normal buffer, second time for when there is not enough space in current page, and both times it can lead to I/O whereas in earlier algorithm, the chances of I/O is only once. I don't see any difference to the previous situation. In both cases, if you need a new page to copy the WAL record to, you need to first flush out some old pages from the WAL buffers if they're all dirty. The patch doesn't change the number of WAL buffers consumed. Note that GetXLogBuffer() is very cheap when it doesn't need to do I/O, extra calls to it don't matter if the page is already initialized. -- 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] pgsql_fdw, FDW for PostgreSQL server
(2012/02/21 20:25), Etsuro Fujita wrote: Please find attached an updated version of the patch. This v2 patch can be applied on HEAD cleanly. Compile completed with only one expected warning of scan.c, and all regression tests for both core and contrib modules passed. This patch allows FDWs to return multiple ForeignPath nodes per a PlanForeignScan call. It also get rid of FdwPlan, FDW-private information container, by replacing with simple List. I've reviewed the patch closely, and have some comments about its design. Basically a create_foo_path is responsible for creating a node object with a particular Path-derived type, but this patch changes create_foreignscan_path to just call PlanForeignScan and return void. This change seems breaking module design. IMO create_foreignscan_path should return just one ForeignPath node per a call, so calling add_path multiple times should be done in somewhere else. I think set_foreign_pathlist suites for it, because set_foo_pathlist functions are responsible for building possible paths for a RangeTblEntry, as comment of set_foreign_pathlist says. /* * set_foreign_pathlist * Build one or more access paths for a foreign table RTE */ In this design, FDW authors can implement PlanForeignScan by repeating steps below for each possible scan path for a foreign table: (1) create a template ForeignPath node with create_foreignscan_path (2) customize the path as FDW wants, e.g. push down WHERE clause (3) store FDW-private info (4) estimate costs of the path (5) call add_path to add the path to RelOptInfo Current design doesn't allow FDWs to provide multiple paths which have different local filtering from each other, because all paths share a RelOptInfo and baserestrictinfo in it. I think this restriction wouldn't be a serious problem. Please find attached a patch implementing the design above. -- Shigeru Hanada diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c index 46394a8..bb541e3 100644 *** a/contrib/file_fdw/file_fdw.c --- b/contrib/file_fdw/file_fdw.c *** *** 25,30 --- 25,31 #include miscadmin.h #include nodes/makefuncs.h #include optimizer/cost.h + #include optimizer/pathnode.h #include utils/rel.h #include utils/syscache.h *** PG_FUNCTION_INFO_V1(file_fdw_validator); *** 93,99 /* * FDW callback routines */ ! static FdwPlan *filePlanForeignScan(Oid foreigntableid, PlannerInfo *root, RelOptInfo *baserel); static void fileExplainForeignScan(ForeignScanState *node, ExplainState *es); --- 94,100 /* * FDW callback routines */ ! static void filePlanForeignScan(Oid foreigntableid, PlannerInfo *root, RelOptInfo *baserel); static void fileExplainForeignScan(ForeignScanState *node, ExplainState *es); *** get_file_fdw_attribute_options(Oid relid *** 406,432 /* * filePlanForeignScan ! *Create a FdwPlan for a scan on the foreign table */ ! static FdwPlan * filePlanForeignScan(Oid foreigntableid, PlannerInfo *root, RelOptInfo *baserel) { ! FdwPlan*fdwplan; char *filename; List *options; /* Fetch options --- we only need filename at this point */ fileGetOptions(foreigntableid, filename, options); ! /* Construct FdwPlan with cost estimates */ ! fdwplan = makeNode(FdwPlan); estimate_costs(root, baserel, filename, ! fdwplan-startup_cost, fdwplan-total_cost); ! fdwplan-fdw_private = NIL; /* not used */ ! return fdwplan; } /* --- 407,443 /* * filePlanForeignScan ! *Create possible access paths for a scan on the foreign table ! * ! *Currently we don't support any push-down feature, so there is only one ! *possible access path, which simply returns all records in the order in ! *the data file. */ ! static void filePlanForeignScan(Oid foreigntableid, PlannerInfo *root, RelOptInfo *baserel) { ! ForeignPath *pathnode = makeNode(ForeignPath); char *filename; List *options; /* Fetch options --- we only need filename at this point */ fileGetOptions(foreigntableid, filename, options); ! /* Create a ForeignPath node and add it as only one possible path. */ ! pathnode = create_foreignscan_path(root, baserel); ! pathnode-fdw_private = NIL; estimate_costs(root, baserel, filename, ! pathnode-path.startup_cost, pathnode-path.total_cost); ! pathnode-path.rows = baserel-rows; !
Re: [HACKERS] Our regex vs. POSIX on longest match
On 5 March 2012 17:23, Robert Haas robertmh...@gmail.com wrote: This is different from what Perl does, but I think Perl's behavior here is batty: given a+|a+b+ and the string aaabbb, it picks the first branch and matches only aaa. Yeah, this is sometimes referred to as ordered alternation, basically that the branches of the alternation are prioritised in the same order in which they are described. It is fairly commonplace among regex implementations. apparently, it selects the syntactically first branch that can match, regardless of the length of the match, which strikes me as nearly pure evil. As long as it's documented that alternation prioritises in this way, I don't feel upset about it. At least it still provides you with a sensible way to get whatever you want from your RE; if you want a shorter alternative to be preferred, put it up the front. Ordered alternation also gives you a way to specify which of several same-length alternatives you would prefer to be matched, which can come in handy. It also means you can specify less-complex alternatives before more-complex ones, which can have performance advantages. I do agree with you that if you *don't* do ordered alternation, then it is right to treat alternation as greedy by default. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autovacuum locks
Tom, agreed - it looks like we dug the hole and got ourselves into it. But I still want to understand why. It looks like we have rather small table on the host where I see the slowness. And all other tables have triggers that will update one row in that small table. The small table contains single entry per table. The thing is, when I scan pg_locks - I can pretty much see everything waiting for lock to access that table. To grab pg_lock output, I'm using this view: SELECT waiting.locktype AS waiting_locktype, waiting.relation::regclass AS waiting_table, waiting_stm.current_query AS waiting_query, waiting.mode AS waiting_mode, waiting.pidAS waiting_pid, other.locktype AS other_locktype, other.relation::regclass AS other_table, other_stm.current_queryAS other_query, other.mode AS other_mode, other.pid AS other_pid, other.granted AS other_granted FROM pg_catalog.pg_locks AS waiting JOIN pg_catalog.pg_stat_activity AS waiting_stm ON ( waiting_stm.procpid = waiting.pid ) JOIN pg_catalog.pg_locks AS other ON ( ( waiting.database = other.database AND waiting.relation = other.relation ) OR waiting.transactionid = other.transactionid ) JOIN pg_catalog.pg_stat_activity AS other_stm ON ( other_stm.procpid = other.pid ) WHERE NOT waiting.granted AND waiting.pid other.pid AND other_stm.query_start now() - interval '14 hours' AND other_stm.current_query NOT LIKE 'IDLE'; And yes, some updates are there for longer then 14 hours. Now, there's two of those queries in particular - both updating just a single row. Stuck for over 14 hours (2 days now actually). I simply cannot believe that single table in the middle of things will lock stuff up so much. Also, on the subject of prepared transactions (2PC), the select * from pg_prepared_xacts ; query simply does not reveal anything, despite the fact that I know that there should be at least two of those open. Unless it only list saved transactions, not a transaction in the middle of operation. I need these 2PC transactions, in order to achieve something close to multi-master replication. But what I think I'll target first, is the triggers updating that single table on my 'main master'. Unless you guys can suggest something better. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)
On Sat, Mar 3, 2012 at 12:01 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 2, 2012 at 4:56 PM, Simon Riggs si...@2ndquadrant.com wrote: Checksums patch isn't sucking much attention at all but admittedly there are some people opposed to the patch that want to draw out the conversation until the patch is rejected, Wow. Sounds like a really shitty thing for those people to do - torpedoing a perfectly good patch for no reason. You've explained to me how you think I do that elsewhere and how that annoyed you, so I think that topic deserves discussion at the developers meeting to help us understand one another rather than perpetuate this. I have an alternative theory, though: they have sincere objections and don't accept your reasons for discounting those objections. That's exactly the problem though and the discussion on it is relevant here. Nobody thinks objections on this patch, checksums or others are made insincerely. It's what happens next that matters. The question should be about acceptance criteria. What do we need to do to get something useful committed? Without a clear set of criteria for resolution we cannot move forward swiftly enough to do useful things. My thoughts are always about salvaging what we can, trying to find a way through the maze of objections and constraints not just black/white decisions based upon the existence of an objection, as if that single point trumps any other consideration and blocks all possibilities. So there is a clear difference between an objection to any progress on a topic (I sincerely object to the checksum patch), and a technical objection to taking a particular course of action (We shouldn't use bits x1..x3 because). The first is not viable, however sincerely it is made, because it leaves the author with no way of resolving things and it also presumes that the patch only exists in one version and that the author is somehow refusing to make agreed changes. Discussion started *here* because it was said Person X is trying to force patch Y thru, which is true - but that doesn't necessarily mean the version of the patch that current objections apply to, only that the author has an equally sincere wish to do something useful. The way forwards here and elsewhere is to list out the things we can't do and list out the things that must change - a clear list of acceptance criteria. If we do that as early as possible we give the author a good shot at being able to make those changes in time to commit something useful. Again, only *something* useful: the full original vision is not always possible. In summary: What can be done in this release, given the constraints discussed? So for Peter's patch - what do we need to do to allow some/all of this to be committed? And for the checksum patch please go back to the checksum thread and list out all the things you consider unresolved. In some cases, resolutions have been suggested but not yet implemented so it would help if those are either discounted now before they are written, or accepted in principle to allow work to proceed. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server
(2012/03/05 18:21), Shigeru Hanada wrote: (2012/02/21 20:25), Etsuro Fujita wrote: Please find attached an updated version of the patch. This v2 patch can be applied on HEAD cleanly. Compile completed with only one expected warning of scan.c, and all regression tests for both core and contrib modules passed. This patch allows FDWs to return multiple ForeignPath nodes per a PlanForeignScan call. It also get rid of FdwPlan, FDW-private information container, by replacing with simple List. I've reviewed the patch closely, and have some comments about its design. Thank you for your review. Basically a create_foo_path is responsible for creating a node object with a particular Path-derived type, but this patch changes create_foreignscan_path to just call PlanForeignScan and return void. This change seems breaking module design. create_index_path builds multiple index paths for a plain relation. How about renaming the function to create_foreign_paths? Best regards, Etsuro Fujita -- Sent 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/03/05 21:00), Etsuro Fujita wrote: (2012/03/05 18:21), Shigeru Hanada wrote: (2012/02/21 20:25), Etsuro Fujita wrote: Please find attached an updated version of the patch. This v2 patch can be applied on HEAD cleanly. Compile completed with only one expected warning of scan.c, and all regression tests for both core and contrib modules passed. This patch allows FDWs to return multiple ForeignPath nodes per a PlanForeignScan call. It also get rid of FdwPlan, FDW-private information container, by replacing with simple List. I've reviewed the patch closely, and have some comments about its design. Thank you for your review. Basically a create_foo_path is responsible for creating a node object with a particular Path-derived type, but this patch changes create_foreignscan_path to just call PlanForeignScan and return void. This change seems breaking module design. create_index_path builds multiple index paths for a plain relation. How about renaming the function to create_foreign_paths? I meant create_foreignscan_paths. I'm sorry about that. Best regards, Etsuro Fujita -- Sent 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/03/05 21:05), Etsuro Fujita wrote: (2012/03/05 21:00), Etsuro Fujita wrote: create_index_path builds multiple index paths for a plain relation. How about renaming the function to create_foreign_paths? I meant create_foreignscan_paths. I'm sorry about that. Perhaps you are confusing create_index_path with create_index_paths. Former creates a IndexScan path node (so it's similar to create_foreignscan_path), and latter builds multiple IndexScan paths for a plain relation. So, just renaming create_foreignscan_path to plural form seems missing the point. -- 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] Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)
On 03/05/2012 05:12 AM, Simon Riggs wrote: On Sat, Mar 3, 2012 at 12:01 AM, Robert Haasrobertmh...@gmail.com wrote: On Fri, Mar 2, 2012 at 4:56 PM, Simon Riggssi...@2ndquadrant.com wrote: Checksums patch isn't sucking much attention at all but admittedly there are some people opposed to the patch that want to draw out the conversation until the patch is rejected, Wow. Sounds like a really shitty thing for those people to do - torpedoing a perfectly good patch for no reason. You've explained to me how you think I do that elsewhere and how that annoyed you, so I think that topic deserves discussion at the developers meeting to help us understand one another rather than perpetuate this. No matter how much we occasionally annoy each other, I think we all need to accept that we're all dealing in good faith. Suggestions to the contrary are ugly, have no foundation in fact that I'm aware of, and reflect badly on our community. Postgres has a well deserved reputation for not having the sort of public bickering that has caused people to avoid certain other projects. Please keep it that way. 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] review: CHECK FUNCTION statement
Hello 2012/3/5 Alvaro Herrera alvhe...@commandprompt.com: Excerpts from Pavel Stehule's message of dom mar 04 16:33:08 -0300 2012: Hello 2012/3/4 Alvaro Herrera alvhe...@commandprompt.com: CHECK FUNCTION - In function: 'f()' error:42P01:2:sentencia SQL:no existe la relación «foo» query:select + var + from + foo ^ (4 filas) this should be fixed. I checked expressions, that works (I expect) correctly. Caret helps - (really). Sometimes man is blind :). Agreed. I don't have your last version, so I am sending just part of CheckFunctionById function - this fragment ensures a output or please, send me your last patch and I'll do merge now result is better postgres= create function f() returns int language plpgsql as $$ postgres$ begin select postgres$ var postgres$ from postgres$ foo; end; $$; CREATE FUNCTION postgres= check function f(); CHECK FUNCTION --- In function: f() error:42P01:2:SQL statement:relation foo does not exist query:select var from foo ^ (7 rows) and some utf8 fce postgres= check function fx(int); CHECK FUNCTION -- In function: fx(integer) error:42703:3:RETURN:column ýšý does not exist query:SELECT (select žlutý from jj /* ýšý */ where /*ýšýšý8*/ ýšý = 10) ^ (7 rows) postgres= check function fx(int); CHECK FUNCTION - In function: fx(integer) error:42703:3:RETURN:column xx does not exist query:SELECT (select t.a from t /* ýšý */ where /*ýšýšý8*/ xx = 10) ^ (7 rows) caret is ok regards Pavel -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support else { resetStringInfo(sinfo); appendStringInfo(sinfo, In function: %s, funcname); do_text_output_oneline(tstate, sinfo.data); for (i = 0; i SPI_processed; i++) { char *query; resetStringInfo(sinfo); appendStringInfo(sinfo, %s:%s:%s:%s:%s, SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 8), SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 4), SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 2), SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 3), SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 5)); do_text_output_oneline(tstate, sinfo.data); resetStringInfo(sinfo); query = SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 10); if (query != NULL) { bool isnull; char *query_line; /* pointer to begin of processed line */ int query_line_caret; int caret; bool is_first_line = true; /* * put any query line to separate output line. And append * a curet, when is defined and related to processed rows. */ caret = SPI_getbinval(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 9, isnull); if (isnull) caret = -1; query_line = query; query_line_caret = caret; while (*query != '\0') { int len; if (*query == '\n') { /* now we found end of line */ *query = '\0'; if (is_first_line) { appendStringInfo(sinfo, query:%s, query_line); is_first_line = false; } else appendStringInfo(sinfo, %s, query_line); do_text_output_oneline(tstate, sinfo.data); resetStringInfo(sinfo); if (query_line_caret 0 caret == 0) { appendStringInfo(sinfo, %*s, query_line_caret, ^); do_text_output_oneline(tstate, sinfo.data); resetStringInfo(sinfo); query_line_caret = 0; } /* store caret offset for next line */ if (caret 0) query_line_caret = caret - 1; /* go to next line */ query_line = query + 1; } len = pg_mblen(query); query += len; if (caret 0) caret--; } /* last line output */ if (query_line != NULL) { if (is_first_line) { appendStringInfo(sinfo, query:%s, query_line); } else appendStringInfo(sinfo, %s, query_line); do_text_output_oneline(tstate, sinfo.data); resetStringInfo(sinfo); if (query_line_caret 0 caret == 0) { appendStringInfo(sinfo, %*s, query_line_caret, ^); do_text_output_oneline(tstate, sinfo.data); resetStringInfo(sinfo); } } } } } --
Re: [HACKERS] Collect frequency statistics for arrays
On Mon, Mar 5, 2012 at 1:11 AM, Tom Lane t...@sss.pgh.pa.us wrote: BTW, one other thing about the count histogram: seems like we are frequently generating uselessly large ones. For instance, do ANALYZE in the regression database and then run select tablename,attname,elem_count_histogram from pg_stats where elem_count_histogram is not null; You get lots of entries that look like this: pg_proc | proallargtypes | {1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,5,5,5,6,6,6,2.80556} pg_proc | proargmodes| {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,1.6} pg_proc | proargnames| {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,5,5,5,5,5,5,5,5,6,6,6,7,7,7,7,8,8,8,14,14,15,16,3.8806} pg_proc | proconfig | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} pg_class| reloptions | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} which seems to me to be a rather useless expenditure of space. Couldn't we reduce the histogram size when there aren't many different counts? It seems fairly obvious to me that we could bound the histogram size with (max count - min count + 1), but maybe something even tighter would work; or maybe I'm missing something and this would sacrifice accuracy. True. If (max count - min count + 1) is small, enumerating of frequencies is both more compact and more precise representation. Simultaneously, if (max count - min count + 1) is large, we can run out of statistics_target with such representation. We can use same representation of count distribution as for scalar column value: MCV and HISTOGRAM, but it would require additional statkind and statistics slot. Probably, you've better ideas? -- With best regards, Alexander Korotkov.
Re: [HACKERS] autovacuum locks
Gregg Jaskiewicz wrote: Also, on the subject of prepared transactions (2PC), the select * from pg_prepared_xacts ; query simply does not reveal anything, despite the fact that I know that there should be at least two of those open. Unless it only list saved transactions, not a transaction in the middle of operation. Transactions only appear there between the PREPARE statement and the COMMIT. Hopefully that is a small window of time for each transaction. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Checksums, state of play
To avoid any confusion as to where this proposed feature is now, I'd like to summarise my understanding, make proposals and also request clear feedback on them. Checksums have a number of objections to them outstanding. 1. We don't need them because there will be something better in a later release. I don't think anybody disagrees that a better solution is possible in the future; doubts have been expressed as to what will be required and when that is likely to happen. Opinions differ. We can and should do something now unless there is reason not to. 2. Turning checksums on/off/on/off in rapid succession can cause false positive reports of checksum failure if crashes occur and are ignored. That may lead to the feature and PostgreSQL being held in disrepute. This can be resolved, if desired, by having having a two-stage enabling process where we must issue a command that scans every block in the database before checksum checking begins. VACUUM is easily modified to the task, we just need to agree that is suitable and agree syntax. A suggestion is VACUUM ENABLE CHECKSUMS; others are possible. 3. Pages with checksums set need to have a version marking to show that they are a later version of the page layout. That version number needs to be extensible to many later versions. Pages of multiple versions need to exist within the server to allow simple upgrades and migration. 4. Checksums that are dependent upon a bit setting on the block are somewhat fragile. Requests have been made to add bits in certain positions and also to remove them again. No set of bits seems to please everyone. (3) and (4) are in conflict with each other, but there is a solution. We mark the block with a version number, but we don't make the checking dependant upon the version number. We simply avoid making any checks until the command to scan all blocks is complete, per point (2). That way we need to use 1 flag bit to mark the new version and zero flag bits to indicate checks should happen. (Various other permutations of solutions for (2), (3), (4) have been discussed and may also be still open) 5. The part of the page header that can be used as a checksum has been disputed. Using the 16 bits dedicated to a version number seems like the least useful consecutive 2 bytes of data in the page header. It can't be 16 bits because that wouldn't be an effective checksum for database blocks. We might prefer 32 bits, but that would require use of some other parts of the page header and possibly split that into two parts. Splitting the checksum into 2 parts will cause the code to be more complex and fragile. 6. Performance impacts. Measured to be a small regression. 7. Hint bit setting requires WAL logging. The worst case for that would be setting hints on newly loaded tables. Work has been done on other patches to remove that case. If those don't fly, this would be a cost paid by those that wish to take advantage of this feature. If there are other points I've missed for whatever reason, please add them here again for clarity. My own assessment of the above is that the checksum feature can be added to 9.2, as long as we agree the changes above and then proceed to implement them and also that no further serious problems emerge. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] xlog min recovery request ... is past current point ...
On Sun, Mar 4, 2012 at 2:41 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Hmm, I think I see how that can happen: 0. A heap page has its bit set in visibility map to begin with 1. A heap tuple is inserted/updated/deleted. This clears the VM bit. 2. time passes, and more WAL is generated 3. The page is vacuumed, and the visibility map bit is set again. Note that on 9.0.X, which Christophe is using, the setting of the visibility map bit in step #3 is not WAL-logged. In the standby, this can happen while replaying the WAL, if you restart the standby so that some WAL is re-replayed: 1. The update of the heap tuple is replayed. This clears the VM bit. 2. The VACUUM is replayed, setting the VM bit again, and updating the VM page's LSN. Therefore I think this won't happen either, on that version. Do we somehow emit an FPI for the VM page? 3. Shutdown and restart standby 4. The heap update is replayed again. This again clears the VM bit, but does not set the LSN If the VM page is now evicted from the buffer cache, you get the WARNING you saw, because the page is dirty, yet its LSN is beyond the current point in recovery. AFAICS that's totally harmless, but the warning is quite alarming, so we'll have to figure out a way to fix that. Not sure how; perhaps we need to set the LSN on the VM page when the VM bit is cleared, but I don't remember off the top of my head if there was some important reason why we don't do that currently. I suspect that it was never done just because there was no clear benefit, since heap_{insert,update,delete} all clear the bit regardless of the page LSN. But this might be a reason to do it. I can't swear it's safe, though, although I also can't see why it wouldn't be. Note also that 9.2devel behaves quite differently than previous releases: every visibilitymap_set is WAL-logged and bumps the vm page's LSN; whereas in prior releases no WAL record is emitted and the vm page's LSN is advanced to the heap page's LSN if it lags it. So we'd better think pretty carefully before assuming that any logic about what is safe here is true for all branches. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch review for logging hooks (CF 2012-01)
On Sun, Mar 4, 2012 at 10:45 AM, Andrew Dunstan and...@dunslane.net wrote: I'm just looking at this patch, and I agree, it should be testable. I'm wondering if it wouldn't be a good idea to have a module or set of modules for demonstrating and testing bits of the API that we expose. src/test/api or something similar? I'm not sure how we'd automate a test for this case, though. I guess we could use something like pg_logforward and have a UDP receiver catch the messages and write them to a file. Something like that should be possible to rig up in Perl. But all that seems a lot of work at this stage of the game. So the question is do we want to commit this patch without it? The latest version of this patch looks sound to me. We haven't insisted on having even a sample application for every hook before, let alone a regression test, so I don't think this patch needs one either. Now, it might be fairly said that we ought to have regression tests for a lot more things than we do right now, but that's basically a limitation of our regression-testing environment which the author of this patch shouldn't be obliged to fix. So my vote is to go ahead and commit it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] review: CHECK FUNCTION statement
small fix of CheckFunctionById function Regards p.s. Alvaro, please, send your patch and I'll merge it /* * Connect to SPI manager */ if (SPI_connect() != SPI_OK_CONNECT) elog(ERROR, SPI_connect failed); values[0] = ObjectIdGetDatum(funcOid); values[1] = ObjectIdGetDatum(relid); values[2] = PointerGetDatum(options); values[3] = BoolGetDatum(fatal_errors); SPI_execute_with_args(sinfo.data, 4, argtypes, values, nulls, true, 0); result = SPI_processed == 0; if (result) { resetStringInfo(sinfo); appendStringInfo(sinfo, Function is valid: '%s', funcname); do_text_output_oneline(tstate, sinfo.data); } else { resetStringInfo(sinfo); appendStringInfo(sinfo, In function: %s, funcname); do_text_output_oneline(tstate, sinfo.data); for (i = 0; i SPI_processed; i++) { char *query; resetStringInfo(sinfo); appendStringInfo(sinfo, %s:%s:%s:%s:%s, SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 8), SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 4), SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 2), SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 3), SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 5)); do_text_output_oneline(tstate, sinfo.data); resetStringInfo(sinfo); query = SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 10); if (query != NULL) { bool isnull; char *query_line; /* pointer to begin of processed line */ int query_line_caret; int caret; bool is_first_line = true; /* * put any query line to separate output line. And append * a curet, when is defined and related to processed rows. */ caret = SPI_getbinval(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 9, isnull); if (isnull) caret = -1; else caret; query_line = query; query_line_caret = caret; while (*query != '\0') { int len; if (*query == '\n') { /* now we found end of line */ *query = '\0'; if (is_first_line) { appendStringInfo(sinfo, query:%s, query_line); is_first_line = false; } else appendStringInfo(sinfo, %s, query_line); do_text_output_oneline(tstate, sinfo.data); resetStringInfo(sinfo); if (query_line_caret 0 caret == 0) { appendStringInfo(sinfo, --%*s, query_line_caret, ^); do_text_output_oneline(tstate, sinfo.data); resetStringInfo(sinfo); query_line_caret = 0; } /* store caret offset for next line */ if (caret 1) query_line_caret = caret - 1; /* go to next line */ query_line = query + 1; } len = pg_mblen(query); query += len; if (caret 0) caret--; } /* last line output */ if (query_line != NULL) { if (is_first_line) { appendStringInfo(sinfo, query:%s, query_line); } else appendStringInfo(sinfo, %s, query_line); do_text_output_oneline(tstate, sinfo.data); resetStringInfo(sinfo); if (query_line_caret 0 caret == 0) { appendStringInfo(sinfo, --%*s, query_line_caret, ^); do_text_output_oneline(tstate, sinfo.data); resetStringInfo(sinfo); } } } } } -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] poll: CHECK TRIGGER?
On Sat, Mar 3, 2012 at 9:23 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Uh! Now that I read this I realize that what you're supposed to give to CHECK TRIGGER is the trigger name, not the function name! In that light, using CHECK FUNCTION for this doesn't make a lot of sense. Okay, CHECK TRIGGER it is. I confess to some bafflement about why we need dedicated syntax for this, or even any kind of core support at all. What would be wrong with defining a function that takes regprocedure as an argument and does whatever? Sure, it's nicer syntax, but we've repeatedly rejected patches that only provided nicer syntax on the grounds that syntax is not free, and therefore syntax alone is not a reason to change the core grammar. What makes this case different? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] poll: CHECK TRIGGER?
2012/3/5 Robert Haas robertmh...@gmail.com: On Sat, Mar 3, 2012 at 9:23 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Uh! Now that I read this I realize that what you're supposed to give to CHECK TRIGGER is the trigger name, not the function name! In that light, using CHECK FUNCTION for this doesn't make a lot of sense. Okay, CHECK TRIGGER it is. I confess to some bafflement about why we need dedicated syntax for this, or even any kind of core support at all. What would be wrong with defining a function that takes regprocedure as an argument and does whatever? Sure, it's nicer syntax, but we've repeatedly rejected patches that only provided nicer syntax on the grounds that syntax is not free, and therefore syntax alone is not a reason to change the core grammar. What makes this case different? Fo checking trigger handler (trigger function) you have to know trigger definition (only joined relation now), but it can be enhanced for other tests based on trigger data. Regards Pavel -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Our regex vs. POSIX on longest match
Robert Haas robertmh...@gmail.com writes: I think the right way to imagine this is as though the regular expression were being matched to the source text in left-to-right fashion. No, it isn't. You are headed down the garden path that leads to a Perl-style definition-by-implementation, and in particular you are going to end up with an implementation that fails to satisfy the POSIX standard. POSIX requires an *overall longest* match (at least for cases where all quantifiers are greedy), and that sometimes means that the quantifiers can't be processed strictly left-to-right greedy. An example of this is regression=# select substring('aabab' from '(a*(ab)*)'); substring --- aabab (1 row) If the a* is allowed to match as much as it wants, the (ab)* will not be able to match at all, and then you fail to find the longest possible overall match. I suspect that it is possible to construct similar cases where, for an all-non-greedy pattern, finding the overall shortest match sometimes requires that individual quantifiers eat more than the local minimum. I've not absorbed enough caffeine yet this morning to produce an example though. I probably shouldn't guess too much at Henry Spencer's thought processes, but I think that he was looking for an extension of this POSIX concept to mixed-greediness cases, ie you first define what the overall RE matches and then let the individual quantifiers fight it out as to which one gets how much of that. The particular way he did that is obviously leaving a lot of people unsatisfied, but I think we need to keep looking for rules of that sort, and not revert to defining the behavior by a search algorithm. I think it's right to view every RE construct as greedy unless it's got an explicit not-greedy flag attached to it; after all, that's the traditional behavior of REs from time immemorial. Someone could invent a non-greedy form of alternation if they were so inclined. I think you can do that already: (foo|bar){1,1}? (if this doesn't result in a non-greedy alternation then it's a bug). The notation is a bit ugly admittedly. 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] RFC: Making TRUNCATE more MVCC-safe
On Sun, Mar 4, 2012 at 11:39 AM, Simon Riggs si...@2ndquadrant.com wrote: Marti, please review this latest version which has new isolation tests added. This does both TRUNCATE and CREATE TABLE. I don't see any need for a GUC to control this behavior. The current behavior is wrong, so if we're going to choose this path to fix it, then we should just fix it, period. The narrow set of circumstances in which it might be beneficial to disable this behavior doesn't seem to me to be sufficient to justify a behavior-changing GUC. It does not seem right that the logic for detecting the serialization error is in heap_beginscan_internal(). Surely this is just as much of a problem for an index-scan or index-only-scan. We don't want to patch all those places individually, either: I think the check should happen right around the time we initially lock the relation and build its relcache entry. The actual text of the error message could use some work. Maybe something like could not serialize access due to concurrent DDL, although I think we try to avoid using acronyms like DDL in translatable strings. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Scaling XLog insertion (was Re: [HACKERS] Moving more work outside WALInsertLock)
On 21.02.2012 13:19, Fujii Masao wrote: On Sat, Feb 18, 2012 at 12:36 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Attached is a new version, fixing that, and off-by-one bug you pointed out in the slot wraparound handling. I also moved code around a bit, I think this new division of labor between the XLogInsert subroutines is more readable. This patch includes not only xlog scaling improvement but also other ones. I think it's better to extract them as separate patches and commit them first. If we do so, the main patch would become more readable. Good point. For example, I think that the followings can be extracted as a separate patch. (1) Make walwriter try to initialize as many of the no-longer-needed WAL buffers for future use as we can. This is pretty hard to extract from the larger patch. The current code in master assumes that there's only one page that is currently inserted to, and relies on WALInsertLock being held in AdvanceXLInsertBuffer(). The logic with the scaling patch is quite different. (2) Refactor the update full_page_writes code. (3) Get rid of XLogCtl-Write.LogwrtResult and XLogCtl-Insert.LogwrtResult. Attached are patches for these two items. Barring objections, I'll commit these. (4) Call TRACE_POSTGRESQL_XLOG_SWITCH() even if the xlog switch has no work to do. Actually, I think I'll just move it in the patch to keep the existing behavior. I'm not sure if (3) makes sense. In current master, those two shared variables are used to reduce the contention of XLogCtl-info_lck and WALWriteLock. You think they have no effect on reducing the lock contention? XLogCtl-Write.LogwrtResult certainly seems redundant with XLogCtl-LogwrtResult. There might be some value in XLogCtl-Insert.LogwrtResult, it's used in AdvanceXLInsertBuffer() to before acquiring info_lck. But I doubt that makes any difference in practice either. At best it's saving one spinlock acquisition per WAL buffer, which isn't all much compared to all the other work involved. (once the scaling patch is committed, this point is moot anyway) -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 266c0de..eb7932e 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -731,8 +731,7 @@ XLogInsert(RmgrId rmid, uint8 info, XLogRecData *rdata) unsigned i; bool updrqst; bool doPageWrites; - bool isLogSwitch = false; - bool fpwChange = false; + bool isLogSwitch = (rmid == RM_XLOG_ID info == XLOG_SWITCH); uint8 info_orig = info; /* cross-check on whether we should be here or not */ @@ -746,30 +745,11 @@ XLogInsert(RmgrId rmid, uint8 info, XLogRecData *rdata) TRACE_POSTGRESQL_XLOG_INSERT(rmid, info); /* - * Handle special cases/records. + * In bootstrap mode, we don't actually log anything but XLOG resources; + * return a phony record pointer. */ - if (rmid == RM_XLOG_ID) + if (IsBootstrapProcessingMode() rmid != RM_XLOG_ID) { - switch (info) - { - case XLOG_SWITCH: -isLogSwitch = true; -break; - - case XLOG_FPW_CHANGE: -fpwChange = true; -break; - - default: -break; - } - } - else if (IsBootstrapProcessingMode()) - { - /* - * In bootstrap mode, we don't actually log anything but XLOG resources; - * return a phony record pointer. - */ RecPtr.xlogid = 0; RecPtr.xrecoff = SizeOfXLogLongPHD; /* start of 1st chkpt record */ return RecPtr; @@ -1232,15 +1212,6 @@ begin:; WriteRqst = XLogCtl-xlblocks[curridx]; } - /* - * If the record is an XLOG_FPW_CHANGE, we update full_page_writes - * in shared memory before releasing WALInsertLock. This ensures that - * an XLOG_FPW_CHANGE record precedes any WAL record affected - * by this change of full_page_writes. - */ - if (fpwChange) - Insert-fullPageWrites = fullPageWrites; - LWLockRelease(WALInsertLock); if (updrqst) @@ -8517,6 +8488,22 @@ UpdateFullPageWrites(void) if (fullPageWrites == Insert-fullPageWrites) return; + START_CRIT_SECTION(); + + /* + * It's always safe to take full page images, even when not strictly + * required, but not the other round. So if we're setting full_page_writes + * to true, first set it true and then write the WAL record. If we're + * setting it to false, first write the WAL record and then set the + * global flag. + */ + if (fullPageWrites) + { + LWLockAcquire(WALInsertLock, LW_EXCLUSIVE); + Insert-fullPageWrites = true; + LWLockRelease(WALInsertLock); + } + /* * Write an XLOG_FPW_CHANGE record. This allows us to keep * track of full_page_writes during archive recovery, if required. @@ -8532,12 +8519,15 @@ UpdateFullPageWrites(void) XLogInsert(RM_XLOG_ID, XLOG_FPW_CHANGE, rdata); } - else + + + if (!fullPageWrites) { LWLockAcquire(WALInsertLock, LW_EXCLUSIVE); - Insert-fullPageWrites = fullPageWrites; +
Re: [HACKERS] poll: CHECK TRIGGER?
Robert Haas robertmh...@gmail.com writes: I confess to some bafflement about why we need dedicated syntax for this, or even any kind of core support at all. What would be wrong with defining a function that takes regprocedure as an argument and does whatever? Sure, it's nicer syntax, but we've repeatedly rejected patches that only provided nicer syntax on the grounds that syntax is not free, and therefore syntax alone is not a reason to change the core grammar. What makes this case different? There's definitely something to be said for that, since it entirely eliminates the problem of providing wildcards and control over which function(s) to check --- the user could write a SELECT from pg_proc that slices things however he wants. The trigger case would presumably take arguments matching pg_trigger's primary key, viz check_trigger(trig_rel regclass, trigger_name name). But as for needing core support, we do need to extend the API for PL validators, so it's not like this could be done as an external project. 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] RFC: Making TRUNCATE more MVCC-safe
On Mon, Mar 5, 2012 at 4:32 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Mar 4, 2012 at 11:39 AM, Simon Riggs si...@2ndquadrant.com wrote: Marti, please review this latest version which has new isolation tests added. This does both TRUNCATE and CREATE TABLE. I don't see any need for a GUC to control this behavior. The current behavior is wrong, so if we're going to choose this path to fix it, then we should just fix it, period. The narrow set of circumstances in which it might be beneficial to disable this behavior doesn't seem to me to be sufficient to justify a behavior-changing GUC. I agree behaviour is wrong, the only question is whether our users rely in some way on that behaviour. Given the long discussion on that point earlier I thought it best to add a GUC. Easy to remove, now or later. It does not seem right that the logic for detecting the serialization error is in heap_beginscan_internal(). Surely this is just as much of a problem for an index-scan or index-only-scan. err, very good point. Doh. We don't want to patch all those places individually, either: I think the check should happen right around the time we initially lock the relation and build its relcache entry. OK, that makes sense and works if we need to rebuild relcache. The actual text of the error message could use some work. Maybe something like could not serialize access due to concurrent DDL, although I think we try to avoid using acronyms like DDL in translatable strings. Yeh that was designed-to-be-replaced text. We do use DDL already elsewhere without really explaining it; its also one of those acronyms that doesn't actually explain what it really means very well. So I like the phrase you suggest. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch review for logging hooks (CF 2012-01)
Robert Haas robertmh...@gmail.com writes: On Sun, Mar 4, 2012 at 10:45 AM, Andrew Dunstan and...@dunslane.net wrote: I'm just looking at this patch, and I agree, it should be testable. I'm wondering if it wouldn't be a good idea to have a module or set of modules for demonstrating and testing bits of the API that we expose. src/test/api or something similar? I'm not sure how we'd automate a test for this case, though. I guess we could use something like pg_logforward and have a UDP receiver catch the messages and write them to a file. Something like that should be possible to rig up in Perl. But all that seems a lot of work at this stage of the game. So the question is do we want to commit this patch without it? The latest version of this patch looks sound to me. We haven't insisted on having even a sample application for every hook before, let alone a regression test, so I don't think this patch needs one either. What we've generally asked for with hooks is a working sample usage of the hook, just as a cross-check that something useful can be done with it and you didn't overlook any obvious usability problems. I agree that a regression test is often not practical, especially not if you're not prepared to create a whole contrib module to provide a sample usage. In the case at hand, ISTM there are some usability questions around where/when the hook is called: in particular, if I'm reading it right, the hook could not override a log_min_messages-based decision that a given message is not to be emitted. Do we care? Also, if the hook is meant to be able to change the data that gets logged, as seems to be the case, do we care that it would also affect what gets sent to the client? I'd like to see a spec for exactly which fields of ErrorData the hook is allowed to change, and some rationale. 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: Scaling XLog insertion (was Re: [HACKERS] Moving more work outside WALInsertLock)
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 21.02.2012 13:19, Fujii Masao wrote: In some places, the spinlock insertpos_lck is taken while another spinlock info_lck is being held. Is this OK? What if unfortunately inner spinlock takes long to be taken? Hmm, that's only done at a checkpoint (and a restartpoint), so I doubt that's a big issue in practice. We had the same pattern before the patch, just with WALInsertLock instead of insertpos_lck. Holding a spinlock longer is much worse than holding a lwlock longer, but nevertheless I don't think that's a problem. No, that's NOT okay. A spinlock is only supposed to be held across a short straight-line sequence of instructions. Something that could involve a spin loop, or worse a sleep() kernel call, is right out. Please change this. 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] archive_keepalive_command
On Sun, Mar 4, 2012 at 1:20 AM, Jeff Janes jeff.ja...@gmail.com wrote: Does this patch have any user-visible effect? I thought it would make pg_last_xact_replay_timestamp() advance, but it does not seem to. I looked through the source a bit, and as best I can tell this only sets some internal state which is never used, except under DEBUG2 Thanks for the review. I'll look into that. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Our regex vs. POSIX on longest match
On Mon, Mar 5, 2012 at 11:28 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I think the right way to imagine this is as though the regular expression were being matched to the source text in left-to-right fashion. No, it isn't. You are headed down the garden path that leads to a Perl-style definition-by-implementation, and in particular you are going to end up with an implementation that fails to satisfy the POSIX standard. POSIX requires an *overall longest* match (at least for cases where all quantifiers are greedy), and that sometimes means that the quantifiers can't be processed strictly left-to-right greedy. An example of this is regression=# select substring('aabab' from '(a*(ab)*)'); substring --- aabab (1 row) If the a* is allowed to match as much as it wants, the (ab)* will not be able to match at all, and then you fail to find the longest possible overall match. Oh. Right. I suspect that it is possible to construct similar cases where, for an all-non-greedy pattern, finding the overall shortest match sometimes requires that individual quantifiers eat more than the local minimum. I've not absorbed enough caffeine yet this morning to produce an example though. Probably true. I guess, then, that the issue here is that there isn't really any principled way to decide whether the RE overall should be greedy or non-greedy. And similarly with every sub-RE. The problem with the non-greedy quantifiers is that they apply only to the quantified bit specifically, which leaves us guessing as to the user's intent with regards to everything else. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe
On Mon, Mar 5, 2012 at 4:46 PM, Simon Riggs si...@2ndquadrant.com wrote: It does not seem right that the logic for detecting the serialization error is in heap_beginscan_internal(). Surely this is just as much of a problem for an index-scan or index-only-scan. err, very good point. Doh. We don't want to patch all those places individually, either: I think the check should happen right around the time we initially lock the relation and build its relcache entry. OK, that makes sense and works if we need to rebuild relcache. Except the reason to do it at the start of the scan is that is the first time a specific snapshot has been associated with a relation and also the last point we can apply the check before the errant behaviour occurs. If we reject locks against tables that might be used against an illegal snapshot then we could easily prevent valid snapshot use cases when a transaction has multiple snapshots, one illegal, one not. We can certainly have a looser test when we first get the lock and then another test later, but I don't think we can avoid making all scans apply this test. And while I'm there, we have to add tests for things like index build scans. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe
On Mon, Mar 5, 2012 at 11:46 AM, Simon Riggs si...@2ndquadrant.com wrote: I agree behaviour is wrong, the only question is whether our users rely in some way on that behaviour. Given the long discussion on that point earlier I thought it best to add a GUC. Easy to remove, now or later. AFAICT, all the discussion upthread was about whether we ought to be trying to implement this in some entirely-different way that doesn't rely on storing XIDs in the catalog. I have a feeling that there are a whole lot more cases like this and that we're in for a lot of unpleasant nastiness if we go very far down this route; pg_constraint is another one, as SnapshotNow can see constraints that may not be valid under the query's MVCC snapshot. On the other hand, if someone comes up with a better way, I suppose we can always rip this out. In any case, I don't remember anyone saying that this needed to be configurable. Speaking of that, though, I have one further thought on this: we need to be absolutely certain that autovacuum is going to prevent this XID value from wrapping around. I suppose this is safe since, even if autovacuum is turned off, we'll forcibly kick it off every so often to advance relfrozenxid, and that will reset relvalidxid while it's there. But then again on second thought, what if relvalidxid lags relfrozenxid? Then the emergency autovacuum might not kick in until relvalidxid has already wrapped around. I think that could happen after a TRUNCATE, perhaps, since I think that would leave relfrozenxid alone while advancing relvalidxid. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe
On Mon, Mar 5, 2012 at 12:42 PM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, Mar 5, 2012 at 4:46 PM, Simon Riggs si...@2ndquadrant.com wrote: It does not seem right that the logic for detecting the serialization error is in heap_beginscan_internal(). Surely this is just as much of a problem for an index-scan or index-only-scan. err, very good point. Doh. We don't want to patch all those places individually, either: I think the check should happen right around the time we initially lock the relation and build its relcache entry. OK, that makes sense and works if we need to rebuild relcache. Except the reason to do it at the start of the scan is that is the first time a specific snapshot has been associated with a relation and also the last point we can apply the check before the errant behaviour occurs. If we reject locks against tables that might be used against an illegal snapshot then we could easily prevent valid snapshot use cases when a transaction has multiple snapshots, one illegal, one not. We can certainly have a looser test when we first get the lock and then another test later, but I don't think we can avoid making all scans apply this test. And while I'm there, we have to add tests for things like index build scans. Well, there's no point that I can see in having two checks. I just dislike the idea that we have to remember to add this check for every method of accessing the relation - doesn't seem terribly future-proof. It gets even worse if you start adding checks to DDL code paths - if we're going to do that, we really need to cover them all, and that doesn't seem very practical if they're going to spread out all over the place. I don't understand your comment that a snapshot doesn't get associated with a relation until scan time. I believe we associated a snapshot with each query before we even know what relations are involved; that query then gets passed down to all the individual scans. The query also opens and locks those relations. We ought to be able to arrange for the query snapshot to be cross-checked at that point, rather than waiting until scan-start time. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch review for logging hooks (CF 2012-01)
On 03/05/2012 12:08 PM, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: On Sun, Mar 4, 2012 at 10:45 AM, Andrew Dunstanand...@dunslane.net wrote: I'm just looking at this patch, and I agree, it should be testable. I'm wondering if it wouldn't be a good idea to have a module or set of modules for demonstrating and testing bits of the API that we expose. src/test/api or something similar? I'm not sure how we'd automate a test for this case, though. I guess we could use something like pg_logforward and have a UDP receiver catch the messages and write them to a file. Something like that should be possible to rig up in Perl. But all that seems a lot of work at this stage of the game. So the question is do we want to commit this patch without it? The latest version of this patch looks sound to me. We haven't insisted on having even a sample application for every hook before, let alone a regression test, so I don't think this patch needs one either. What we've generally asked for with hooks is a working sample usage of the hook, just as a cross-check that something useful can be done with it and you didn't overlook any obvious usability problems. I agree that a regression test is often not practical, especially not if you're not prepared to create a whole contrib module to provide a sample usage. In the case at hand, ISTM there are some usability questions around where/when the hook is called: in particular, if I'm reading it right, the hook could not override a log_min_messages-based decision that a given message is not to be emitted. Do we care? That's what I understand too. We could relax that at some stage in the future if we had a requirement, I guess. Also, if the hook is meant to be able to change the data that gets logged, as seems to be the case, do we care that it would also affect what gets sent to the client? I'd like to see a spec for exactly which fields of ErrorData the hook is allowed to change, and some rationale. Good question. I'd somewhat be inclined to say that it should only be able to change output_to_server and output_to_client, and possibly only to change them from true to false (i.e. I'm not sure the hook should be able to induce more verbose logging.) But maybe that's too restrictive. I doubt we can enforce good behaviour, though, only state that if you break things you get to keep all the pieces. 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] poll: CHECK TRIGGER?
On Mon, Mar 5, 2012 at 11:41 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I confess to some bafflement about why we need dedicated syntax for this, or even any kind of core support at all. What would be wrong with defining a function that takes regprocedure as an argument and does whatever? Sure, it's nicer syntax, but we've repeatedly rejected patches that only provided nicer syntax on the grounds that syntax is not free, and therefore syntax alone is not a reason to change the core grammar. What makes this case different? There's definitely something to be said for that, since it entirely eliminates the problem of providing wildcards and control over which function(s) to check --- the user could write a SELECT from pg_proc that slices things however he wants. The trigger case would presumably take arguments matching pg_trigger's primary key, viz check_trigger(trig_rel regclass, trigger_name name). Yes... But as for needing core support, we do need to extend the API for PL validators, so it's not like this could be done as an external project. Well, the plpgsql extension could install a function pg_check_plpgsql_function() that only works on PL/pgsql functions, and other procedural languages could do the same at their option. I think we only need to extend the API if we want to provide a dispatch function so that you can say check this function, whatever language it's written in and have the right checker get called. But since we've already talked about the possibility of having more than one checker per language doing different kinds of checks, I'm not even sure that the checker for a language is a concept that we want to invent. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server
Shigeru Hanada shigeru.han...@gmail.com writes: So, just renaming create_foreignscan_path to plural form seems missing the point. I agree that that wouldn't be an improvement. What bothers me about the patch's version of this function is that it just creates a content-free Path node and leaves it to the caller to fill in everything. That doesn't accomplish much, and it leaves the caller very exposed to errors of omission. It's also unlike the other create_xxx_path functions, which generally hand back a completed Path ready to pass to add_path. I'm inclined to think that if we provide this function in core at all, it should take a parameter list long enough to let it fill in the Path completely. That would imply that any future changes in Path structs would result in a change in the parameter list, which would break callers --- but it would break them in an obvious way that the C compiler would complain about. If we leave it as-is, those same callers would be broken silently, because they'd just be failing to fill in the new Path fields. 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 review for logging hooks (CF 2012-01)
On Mon, Mar 5, 2012 at 12:50 PM, Andrew Dunstan and...@dunslane.net wrote: The latest version of this patch looks sound to me. We haven't insisted on having even a sample application for every hook before, let alone a regression test, so I don't think this patch needs one either. What we've generally asked for with hooks is a working sample usage of the hook, just as a cross-check that something useful can be done with it and you didn't overlook any obvious usability problems. I agree that a regression test is often not practical, especially not if you're not prepared to create a whole contrib module to provide a sample usage. In the case at hand, ISTM there are some usability questions around where/when the hook is called: in particular, if I'm reading it right, the hook could not override a log_min_messages-based decision that a given message is not to be emitted. Do we care? That's what I understand too. We could relax that at some stage in the future if we had a requirement, I guess. Also, if the hook is meant to be able to change the data that gets logged, as seems to be the case, do we care that it would also affect what gets sent to the client? I'd like to see a spec for exactly which fields of ErrorData the hook is allowed to change, and some rationale. Good question. I'd somewhat be inclined to say that it should only be able to change output_to_server and output_to_client, and possibly only to change them from true to false (i.e. I'm not sure the hook should be able to induce more verbose logging.) But maybe that's too restrictive. I doubt we can enforce good behaviour, though, only state that if you break things you get to keep all the pieces. It sort of looks like it's intended to apply only to server output, so I'd find it odd to say that it should be able to mess with output_to_client. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parameterized-path cost comparisons need some work
Robert Haas robertmh...@gmail.com writes: On Sun, Mar 4, 2012 at 12:20 AM, Tom Lane t...@sss.pgh.pa.us wrote: One annoying thing about that is that it will reduce the usefulness of add_path_precheck, because that's called before we compute the rowcount estimates (and indeed not having to make the rowcount estimates is one of the major savings from the precheck). I think what we'll have to do is assume that a difference in parameterization could result in a difference in rowcount, and hence only a dominant path with exactly the same parameterization can result in failing the precheck. I wish we had some way of figuring out how much this - and maybe some of the other new planning possibilities like index-only scans - were going to cost us on typical medium-to-large join problems. In the absence of real-world data it's hard to know how worried we should be. I have been doing testing against a couple of complex queries supplied by Kevin and Andres. It'd be nice to have a larger sample though ... I'm a bit concerned that this change will end up removing most of the usefulness of add_path_precheck. I would not actually cry if that went away again, because hacking things like that greatly complicated the API of the join cost functions. But it's nervous-making to be making decisions like that on the basis of rather small sets of queries. 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] poll: CHECK TRIGGER?
2012/3/5 Robert Haas robertmh...@gmail.com: On Mon, Mar 5, 2012 at 11:41 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I confess to some bafflement about why we need dedicated syntax for this, or even any kind of core support at all. What would be wrong with defining a function that takes regprocedure as an argument and does whatever? Sure, it's nicer syntax, but we've repeatedly rejected patches that only provided nicer syntax on the grounds that syntax is not free, and therefore syntax alone is not a reason to change the core grammar. What makes this case different? There's definitely something to be said for that, since it entirely eliminates the problem of providing wildcards and control over which function(s) to check --- the user could write a SELECT from pg_proc that slices things however he wants. The trigger case would presumably take arguments matching pg_trigger's primary key, viz check_trigger(trig_rel regclass, trigger_name name). Yes... But as for needing core support, we do need to extend the API for PL validators, so it's not like this could be done as an external project. Well, the plpgsql extension could install a function pg_check_plpgsql_function() that only works on PL/pgsql functions, and other procedural languages could do the same at their option. I think we only need to extend the API if we want to provide a dispatch function so that you can say check this function, whatever language it's written in and have the right checker get called. But since we've already talked about the possibility of having more than one checker per language doing different kinds of checks, I'm not even sure that the checker for a language is a concept that we want to invent. There is not multiple PL checker function - or I don't know about it. Pavel -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] review: CHECK FUNCTION statement
Excerpts from Pavel Stehule's message of lun mar 05 13:02:50 -0300 2012: small fix of CheckFunctionById function Regards p.s. Alvaro, please, send your patch and I'll merge it Here it is, with your changes already merged. I also added back the new reference doc files which were dropped after the 2012-01-01 version. Note I haven't touched or read the plpgsql checker code at all (only some automatic indentation changes IIRC). I haven't verified the regression tests either. FWIW I'm not going to participate in the other thread; neither I am going to work any more on this patch until the other thread sees some reasonable conclusion. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support check_function-2012-03-05-1.patch.gz Description: GNU Zip compressed 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] Parameterized-path cost comparisons need some work
On Mon, Mar 5, 2012 at 1:02 PM, Tom Lane t...@sss.pgh.pa.us wrote: But it's nervous-making to be making decisions like that on the basis of rather small sets of queries. I heartily agree. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch review for logging hooks (CF 2012-01)
Robert Haas robertmh...@gmail.com writes: On Mon, Mar 5, 2012 at 12:50 PM, Andrew Dunstan and...@dunslane.net wrote: I'd like to see a spec for exactly which fields of ErrorData the hook is allowed to change, and some rationale. Good question. I'd somewhat be inclined to say that it should only be able to change output_to_server and output_to_client, and possibly only to change them from true to false (i.e. I'm not sure the hook should be able to induce more verbose logging.) But maybe that's too restrictive. I doubt we can enforce good behaviour, though, only state that if you break things you get to keep all the pieces. The reason it can't sensibly expect to change them from off to on is that control will never get here in the first place if they're both off. That is a feature not a bug: the ereport mechanisms are designed to skip most of the message-construction work for messages that are so low priority that they're not going to get printed anywhere. If we wanted the hook to be able to override that, it would have to be called from errstart and it would then have much less information to work with. It sort of looks like it's intended to apply only to server output, so I'd find it odd to say that it should be able to mess with output_to_client. OK, so let's document that the only supported change in ErrorData is to turn output_to_server from on to off. I can see how that constitutes a potential feature: the hook might be intended as a filter that allows logging or not logging according to some rule not supported by the core system. You'd have to ensure that log_min_messages is set low enough for all desired messages to get generated in the first place, but then the hook could control things beyond that. I can also imagine a hook that's meant as some sort of aggregator, so that it would accumulate a summary form of messages that it then told the core not to print. 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] performance results on IBM POWER7
On Thu, Mar 1, 2012 at 11:38 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Mar 1, 2012 at 11:23 AM, Ants Aasma ants.aa...@eesti.ee wrote: On Thu, Mar 1, 2012 at 4:54 PM, Robert Haas robertmh...@gmail.com wrote: ... After that I think maybe some testing of the remaining CommitFest patches might be in order (though personally I'd like to wrap this CommitFest up fairly soon) to see if any of those improve things. Besides performance testing, could you check how clocksources behave on this kind of machine? You can find pg_test_timing tool attached here: http://archives.postgresql.org/pgsql-hackers/2012-01/msg00937.php To see which clocksources are available, you can do: # cat /sys/devices/system/clocksource/clocksource0/available_clocksource To switch the clocksource, just write the desired clocksource like this: # echo hpet /sys/devices/system/clocksource/clocksource0/current_clocksource Sure, I'll check that as soon as it's back up. It seems that timebase is the only available clock source. pg_test_timing says: Testing timing overhead for 3 seconds. Per timing duration including loop overhead: 38.47 ns Histogram of timing durations: usec: count percent 32: 6 0.1% 16: 4 0.1% 8: 8 0.1% 4:282 0.00036% 2:2999189 3.84628% 1: 74976816 96.15333% -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] foreign key locks, 2nd attempt
On Mon, Feb 27, 2012 at 2:47 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Feb 23, 2012 at 11:01 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: This seems like a horrid mess that's going to be unsustainable both from a complexity and a performance standpoint. The only reason multixacts were tolerable at all was that they had only one semantics. Changing it so that maybe a multixact represents an actual updater and maybe it doesn't is not sane. As far as complexity, yeah, it's a lot more complex now -- no question about that. Regarding performance, the good thing about this patch is that if you have an operation that used to block, it might now not block. So maybe multixact-related operation is a bit slower than before, but if it allows you to continue operating rather than sit waiting until some other transaction releases you, it's much better. That's probably true, although there is some deferred cost that is hard to account for. You might not block immediately, but then later somebody might block either because the mxact SLRU now needs fsyncs or because they've got to decode an mxid long after the relevant segment has been evicted from the SLRU buffers. In general, it's hard to bound that latter cost, because you only avoid blocking once (when the initial update happens) but you might pay the extra cost of decoding the mxid as many times as the row is read, which could be arbitrarily many. How much of a problem that is in practice, I'm not completely sure, but it has worried me before and it still does. In the worst case scenario, a handful of frequently-accessed rows with MXIDs all of whose members are dead except for the UPDATE they contain could result in continual SLRU cache-thrashing. Cases I regularly see involve wait times of many seconds. When this patch helps, it will help performance by algorithmic gains, so perhaps x10-100. That can and should be demonstrated though, I agree. From a performance standpoint, we really need to think not only about the cases where the patch wins, but also, and maybe more importantly, the cases where it loses. There are some cases where the current mechanism, use SHARE locks for foreign keys, is adequate. In particular, it's adequate whenever the parent table is not updated at all, or only very lightly. I believe that those people will pay somewhat more with this patch, and especially in any case where backends end up waiting for fsyncs in order to create new mxids, but also just because I think this patch will have the effect of increasing the space consumed by each individual mxid, which imposes a distributed cost of its own. That is a concern also. It's taken me a while reviewing the patch to realise that space usage is actually 4 times worse than before. I think we should avoid having a theoretical argument about how serious these problems are; instead, you should try to construct somewhat-realistic worst case scenarios and benchmark them. Tom's complaint about code complexity is basically a question of opinion, so I don't know how to evaluate that objectively, but performance is something we can measure. We might still disagree on the interpretation of the results, but I still think having some real numbers to talk about based on carefully-thought-out test cases would advance the debate. It's a shame that the isolation tester can't be used directly by pgbench - I think we need something similar for performance regression testing. So yes, performance testing is required. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] foreign key locks, 2nd attempt
Excerpts from Simon Riggs's message of lun mar 05 15:28:59 -0300 2012: On Mon, Feb 27, 2012 at 2:47 AM, Robert Haas robertmh...@gmail.com wrote: From a performance standpoint, we really need to think not only about the cases where the patch wins, but also, and maybe more importantly, the cases where it loses. There are some cases where the current mechanism, use SHARE locks for foreign keys, is adequate. In particular, it's adequate whenever the parent table is not updated at all, or only very lightly. I believe that those people will pay somewhat more with this patch, and especially in any case where backends end up waiting for fsyncs in order to create new mxids, but also just because I think this patch will have the effect of increasing the space consumed by each individual mxid, which imposes a distributed cost of its own. That is a concern also. It's taken me a while reviewing the patch to realise that space usage is actually 4 times worse than before. Eh. You're probably misreading something. Previously each member of a multixact used 4 bytes (the size of an Xid). With the current patch a member uses 5 bytes (same plus a flags byte). An earlier version used 4.25 bytes per multi, which I increased to leave space for future expansion. So it's 1.25x worse, not 4x worse. -- Á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] ECPG FETCH readahead
On Sun, Mar 04, 2012 at 05:16:06PM +0100, Michael Meskes wrote: On Fri, Mar 02, 2012 at 11:41:05AM -0500, Noah Misch wrote: I suggest enabling the feature by default but drastically reducing the default readahead chunk size from 256 to, say, 5. That still reduces the FETCH round trip overhead by 80%, but it's small enough not to attract pathological behavior on a workload where each row is a 10 MiB document. I would not offer an ecpg-time option to disable the feature per se. Instead, let the user set the default chunk size at ecpg time. A setting of 1 effectively disables the feature, though one could later re-enable it with ECPGFETCHSZ. Using 1 to effectively disable the feature is fine with me, but I strongly object any default enabling this feature. It's farily easy to create cases with pathological behaviour and this features is not standard by any means. I figure a normal programmer would expect only one row being transfered when fetching one. On further reflection, I agree with you here. The prospect for queries that call volatile functions changed my mind; they would exhibit different functional behavior under readahead. We mustn't silently give affected programs different semantics. Thanks, nm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ECPG FETCH readahead
On Sun, Mar 04, 2012 at 04:33:32PM +0100, Boszormenyi Zoltan wrote: 2012-03-02 17:41 keltez?ssel, Noah Misch ?rta: On Thu, Dec 29, 2011 at 10:46:23AM +0100, Boszormenyi Zoltan wrote: I suggest enabling the feature by default but drastically reducing the default readahead chunk size from 256 to, say, 5. That still reduces the FETCH round trip overhead by 80%, but it's small enough not to attract pathological behavior on a workload where each row is a 10 MiB document. I see. How about 8? Nice round power of 2 value, still small and avoids 87.5% of overhead. Having pondered the matter further, I now agree with Michael that the feature should stay disabled by default. See my response to him for rationale. Assuming that conclusion holds, we can recommended a higher value to users who enable the feature at all. Your former proposal of 256 seems fine. BTW, the default disabled behaviour was to avoid make check breakage, see below. I would not offer an ecpg-time option to disable the feature per se. Instead, let the user set the default chunk size at ecpg time. A setting of 1 effectively disables the feature, though one could later re-enable it with ECPGFETCHSZ. This means all code previously going through ECPGdo() would go through ECPGopen()/ECPGfetch()/ECPGclose(). This is more intrusive and all regression tests that were only testing certain features would also test the readahead feature, too. It's a good sort of intrusiveness, reducing the likelihood of introducing bugs basically unrelated to readahead that happen to afflict only ECPGdo() or only the cursor.c interfaces. Let's indeed not have any preexisting test cases use readahead per se, but having them use the cursor.c interfaces anyway will build confidence in the new code. The churn in expected debug output isn't ideal, but I don't prefer the alternative of segmenting the implementation for the sake of the test cases. Also, the test for WHERE CURRENT OF at ecpg time would have to be done at runtime, possibly making previously working code fail if ECPGFETCHSZ is enabled. Good point. How about still allowing NO READAHEAD cursors that compile into plain ECPGdo()? This way, ECPGFETCHSZ don't interfere with WHERE CURRENT OF. But this would mean code changes everywhere where WHERE CURRENT OF is used. ECPGFETCHSZ should only affect cursors that make no explicit mention of READAHEAD. I'm not sure whether that should mean actually routing READHEAD 1 cursors through ECPGdo() or simply making sure that cursor.c achieves the same outcome; see later for a possible reason to still do the latter. Or how about a new feature in the backend, so ECPG can do UPDATE/DELETE ... WHERE OFFSET N OF cursor and the offset of computed from the actual cursor position and the position known by the application? This way an app can do readahead and do work on rows collected by the cursor with WHERE CURRENT OF which gets converted to WHERE OFFSET OF behind the scenes. That's a neat idea, but I would expect obstacles threatening our ability to use it automatically for readahead. You would have to make the cursor a SCROLL cursor. We'll often pass a negative offset, making the operation fail if the cursor query used FOR UPDATE. Volatile functions in the query will get more calls. That's assuming the operation will map internally to something like MOVE N; UPDATE ... WHERE CURRENT OF; MOVE -N. You might come up with innovations to mitigate those obstacles, but those innovations would probably also apply to MOVE/FETCH. In any event, this would constitute a substantive patch in its own right. One way out of trouble here is to make WHERE CURRENT OF imply READHEAD 1/READHEAD 0 (incidentally, perhaps those two should be synonyms) on the affected cursor. If the cursor has some other readahead quantity declared explicitly, throw an error during preprocessing. Failing a reasonable resolution, I'm prepared to withdraw my suggestion of making ECPGFETCHSZ always-usable. It's nice to have, not critical. +bool +ECPGopen(const int lineno, const int compat, const int force_indicator, + const char *connection_name, const bool questionmarks, + const char *curname, const int st, const char *query, ...) +{ + va_list args; + boolret, scrollable; + char *new_query, *ptr, *whold, *noscroll, *scroll, *dollar0; + struct sqlca_t *sqlca = ECPGget_sqlca(); + + if (!query) + { + ecpg_raise(lineno, ECPG_EMPTY, ECPG_SQLSTATE_ECPG_INTERNAL_ERROR, NULL); + return false; + } + ptr = strstr(query, for ); + if (!ptr) + { + ecpg_raise(lineno, ECPG_INVALID_STMT, ECPG_SQLSTATE_ECPG_INTERNAL_ERROR, NULL); + return false; + } + whold = strstr(query, with hold ); + dollar0 = strstr(query, $0); + + noscroll = strstr(query, no scroll ); + scroll = strstr(query, scroll ); A
[HACKERS] elegant and effective way for running jobs inside a database
Dear Developers, I am looking for elegant and effective way for running jobs inside a database or cluster - for now I can not find that solution. OK if You say use cron or pgAgent I say I know that solutions, but the are not effective and elegant. Compilation of pgAgent is a pain (especially wxWidgets usage on system with no X) - it can run jobs with minimal 60s periods but what when someone needs run it faster for eg. with 5s period ? Of course using cron I can do that but it is not effective and elegant solution. Why PostgreSQL can not have so elegant solution like Oracle database ? I am working with Oracle databases for many years, but I like much more PostgreSQL project but this one thing... I can not understand - the lack of jobs inside the database... Best regards, Artur 0xAF4A859D.asc Description: application/pgp-keys -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe
On Mon, Feb 13, 2012 at 09:29:56AM -0500, Robert Haas wrote: On Fri, Feb 10, 2012 at 11:46 PM, Noah Misch n...@leadboat.com wrote: I've yet to see an MVCC anomaly that one can reproduce at REPEATABLE READ and not at READ COMMITTED. ?They tend to be narrow race conditions at READ COMMITTED, yet easy to demonstrate at REPEATABLE READ. ?Related: http://archives.postgresql.org/pgsql-performance/2011-02/msg00451.php Yeah. Well, that's actually an interesting example, because it illustrates how general this problem is. We could potentially get ourselves into a situation where just about every system catalog table needs an xmin field to store the point at which the object came into existence - or for that matter, was updated. I can see this strategy applying to many relation-pertinent system catalogs. Do you foresee applications to non-relation catalogs? In any event, I think a pg_class.relvalidxmin is the right starting point. One might imagine a family of relvalidxmin, convalidxmin, indcheckxmin (already exists), inhvalidxmin, and attvalidxmin. relvalidxmin is like the AccessExclusiveLock of that family; it necessarily blocks everything that might impugn the others. The value in extending this to more catalogs is the ability to narrow the impact of failing the check. A failed indcheckxmin comparison merely excludes plans involving the index. A failed inhvalidxmin check might just skip recursion to the table in question. Those are further refinements, much like using weaker heavyweight lock types. But it's not quite the same as the xmin of the row itself, because some updates might be judged not to matter. There could also be intermediate cases where updates are invalidating for some purposes but not others. I think we'd better get our hands around more of the problem space before we start trying to engineer solutions. I'm not seeing that problem. Any operation that would update some xmin horizon should set it to the greater of its current value and the value the operation needs for its own correctness. If you have something in mind that needs more, could you elaborate? Incidentally, people use READ COMMITTED because they don't question the default, not because they know hazards of REPEATABLE READ. ?I don't know the bustedness you speak of; could we improve the documentation to inform folks? The example that I remember was related to SELECT FOR UPDATE/SELECT FOR SHARE. The idea of those statements is that you want to prevent the row from being updated or deleted until some other concurrent action is complete; for example, in the case of a foreign key, we'd like to prevent the referenced row from being deleted or updated in the relevant columns until the inserting transaction is committed. But it doesn't work, because when the updating or deleting process gets done with the lock wait, they are still using the same snapshot as before, and merrily do exactly the the thing that the lock-wait was supposed to prevent. If an actual UPDATE is used, it's safe (I think): anyone who was going to UPDATE or DELETE the row will fail with some kind of serialization error. But a SELECT FOR UPDATE that commits is treated more like an UPDATE that rolls back: it's as if the lock never existed. Someone (Florian?) proposed a patch to change this, but it seemed problematic for reasons I no longer exactly remember. Thanks. I vaguely remember that thread. nm -- Sent 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 Mon, Mar 5, 2012 at 6:37 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Simon Riggs's message of lun mar 05 15:28:59 -0300 2012: On Mon, Feb 27, 2012 at 2:47 AM, Robert Haas robertmh...@gmail.com wrote: From a performance standpoint, we really need to think not only about the cases where the patch wins, but also, and maybe more importantly, the cases where it loses. There are some cases where the current mechanism, use SHARE locks for foreign keys, is adequate. In particular, it's adequate whenever the parent table is not updated at all, or only very lightly. I believe that those people will pay somewhat more with this patch, and especially in any case where backends end up waiting for fsyncs in order to create new mxids, but also just because I think this patch will have the effect of increasing the space consumed by each individual mxid, which imposes a distributed cost of its own. That is a concern also. It's taken me a while reviewing the patch to realise that space usage is actually 4 times worse than before. Eh. You're probably misreading something. Previously each member of a multixact used 4 bytes (the size of an Xid). With the current patch a member uses 5 bytes (same plus a flags byte). An earlier version used 4.25 bytes per multi, which I increased to leave space for future expansion. So it's 1.25x worse, not 4x worse. Thanks for correcting me. That sounds better. It does however, illustrate my next review comment which is that the comments and README items are sorely lacking here. It's quite hard to see how it works, let along comment on major design decisions. It would help myself and others immensely if we could improve that. Is there a working copy on a git repo? Easier than waiting for next versions of a patch. My other comments so far are * some permutations commented out - no comments as to why Something of a fault with the isolation tester that it just shows output, there's no way to record expected output in the spec Comments required for these points * Why do we need multixact to be persistent? Do we need every page of multixact to be persistent, or just particular pages in certain circumstances? * Why do we need to expand multixact with flags? Can we avoid that in some cases? * Why do we need to store just single xids in multixact members? Didn't understand comments, no explanation -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] elegant and effective way for running jobs inside a database
2012/3/5 Artur Litwinowicz ad...@ybka.com Dear Developers, I am looking for elegant and effective way for running jobs inside a database or cluster - for now I can not find that solution. OK if You say use cron or pgAgent I say I know that solutions, but the are not effective and elegant. Compilation of pgAgent is a pain (especially wxWidgets usage on system with no X) - it can run jobs with minimal 60s periods but what when someone needs run it faster for eg. with 5s period ? Of course using cron I can do that but it is not effective and elegant solution. Why PostgreSQL can not have so elegant solution like Oracle database ? I am working with Oracle databases for many years, but I like much more PostgreSQL project but this one thing... I can not understand - the lack of jobs inside the database... IMHO it is not necessary add this feature to the PostgreSQL core, because the OS already has the capability to schedule and maintain the tasks. Best regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] foreign key locks, 2nd attempt
Excerpts from Simon Riggs's message of lun mar 05 16:34:10 -0300 2012: On Mon, Mar 5, 2012 at 6:37 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: It does however, illustrate my next review comment which is that the comments and README items are sorely lacking here. It's quite hard to see how it works, let along comment on major design decisions. It would help myself and others immensely if we could improve that. Hm. Okay. Is there a working copy on a git repo? Easier than waiting for next versions of a patch. No, I don't have an external mirror of my local repo. My other comments so far are * some permutations commented out - no comments as to why Something of a fault with the isolation tester that it just shows output, there's no way to record expected output in the spec The reason they are commented out is that they are invalid, that is, it requires running a command on a session that's blocked in the previous command. Obviously, that cannot happen in real life. isolationtester now has support for detecting such conditions; if the spec specifies running a command in a locked session, the permutation is killed with an error message invalid permutation and just continues with the next permutation. It used to simply die, aborting the test. Maybe we could just modify the specs so that all permutations are there (this can be done by simply removing the permutation lines), and the invalid permutation messages are part of the expected file. Would that be better? Comments required for these points * Why do we need multixact to be persistent? Do we need every page of multixact to be persistent, or just particular pages in certain circumstances? Any page that contains at least one multi with an update as a member must persist. It's possible that some pages contain no update (and this is even likely in some workloads, if updates are rare), but I'm not sure it's worth complicating the code to cater for early removal of some pages. * Why do we need to expand multixact with flags? Can we avoid that in some cases? Did you read my blog post? http://www.commandprompt.com/blogs/alvaro_herrera/2011/08/fixing_foreign_key_deadlocks_part_three/ This explains the reason -- the point is that we need to distinguish the lock strength acquired by each locker. * Why do we need to store just single xids in multixact members? Didn't understand comments, no explanation This is just for SELECT FOR SHARE. We don't have a hint bit to indicate this tuple has a for-share lock, so we need to create a multi for it. Since FOR SHARE is probably going to be very uncommon, this isn't likely to be a problem. We're mainly catering for users of SELECT FOR SHARE so that it continues to work, i.e. maintain backwards compatibility. (Maybe I misunderstood your question -- what I think you're asking is, why are there some multixacts that have a single member?) I'll try to come up with a good place to add some paragraphs about all this. Please let me know if answers here are unclear and/or you have further questions. -- Á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] elegant and effective way for running jobs inside a database
Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012: Dear Developers, I am looking for elegant and effective way for running jobs inside a database or cluster - for now I can not find that solution. Yeah, it'd be good to have something. Many people say it's not necessary, and probably some hackers would oppose it; but mainly I think we just haven't agreed (or even discussed) what the design of such a scheduler would look like. For example, do we want it to be able to just connect and run queries and stuff, or do we want something more elaborate able to start programs such as running pg_dump? What if the program crashes -- should it cause the server to restart? And so on. It's not a trivial problem. -- Á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] poll: CHECK TRIGGER?
2012/3/5 Robert Haas robertmh...@gmail.com: On Sat, Mar 3, 2012 at 9:23 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Uh! Now that I read this I realize that what you're supposed to give to CHECK TRIGGER is the trigger name, not the function name! In that light, using CHECK FUNCTION for this doesn't make a lot of sense. Okay, CHECK TRIGGER it is. I confess to some bafflement about why we need dedicated syntax for this, or even any kind of core support at all. What would be wrong with defining a function that takes regprocedure as an argument and does whatever? Sure, it's nicer syntax, but we've repeatedly rejected patches that only provided nicer syntax on the grounds that syntax is not free, and therefore syntax alone is not a reason to change the core grammar. What makes this case different? before argumentation for CHECK TRIGGER I show a proposed PL checker function: FUNCTION checker_function(regprocedure, regclass, options text[], fatal_errors boolen) RETURNS TABLE (functionoid oid, lineno int, statement text, sqlstate text, message text, detail text, hint text, position int, query) this function is worker for CHECK FUNCTION and CHECK TRIGGER statements. The possibility to call this function directly can enable thousands combinations - all functions, all functions from schema, all functions that has name starts with, ... for user friendly there are interface: CHECK FUNCTION and CHECK TRIGGER * provides more practical reports with caret positioning than SRF function * support often used combinations of requests - all functions from one language, all functions from schema, all functions by one user CHECK FUNCTION is clear - and there are no disagreement There are two possibilities for checking triggers a) some like CHECK FUNCTION trgfunc() ON table_name b) existing CHECK TRIGGER t1_f1 ON table_name; these forms are almost equal, although CREATE TRIGGER can provide more unique information for checking. And joining table_name to TRIGGER has bigger sense then to FUNCTION (in one statement). When I try to look on some multicheck form: a) CHECK FUNCTION ALL ON table_name b) CHECK TRIGGER ALL ON table_name then more natural form is @b (for me). Personally, I can live with one, both or second form, although I prefer CHECK TRIGGER. notes? Regards Pavel -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Our regex vs. POSIX on longest match
On Mon, Mar 05, 2012 at 11:28:24AM -0500, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: I think the right way to imagine this is as though the regular expression were being matched to the source text in left-to-right fashion. No, it isn't. You are headed down the garden path that leads to a Perl-style definition-by-implementation, and in particular you are going to end up with an implementation that fails to satisfy the POSIX standard. POSIX requires an *overall longest* match (at least for cases where all quantifiers are greedy), and that sometimes means that the quantifiers can't be processed strictly left-to-right greedy. An example of this is On the otherhand, I think requiring an overall longest match makes your implementation non-polynomial complexity. The simplest example I can think of is the knapsack problem, where given weights x_n and a total W, can be converted to a regex problem as matching a string with W a's against the regex: a{x_1}?a{x_2}?a{x_3}? etc... Yes, Perl (and others) don't guarentee an overall longest match. I think they want you to consider regular expressions as a specialised parsing language where you can configure a state machine to process your strings. Not ideal, but predicatable. The question is, what are users expecting of the PostgreSQL regex implementation? Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe
On Mon, Mar 5, 2012 at 5:49 PM, Robert Haas robertmh...@gmail.com wrote: Well, there's no point that I can see in having two checks. I just dislike the idea that we have to remember to add this check for every method of accessing the relation - doesn't seem terribly future-proof. It gets even worse if you start adding checks to DDL code paths - if we're going to do that, we really need to cover them all, and that doesn't seem very practical if they're going to spread out all over the place. Understood. Will look. I don't understand your comment that a snapshot doesn't get associated with a relation until scan time. I believe we associated a snapshot with each query before we even know what relations are involved; that query then gets passed down to all the individual scans. The query also opens and locks those relations. We ought to be able to arrange for the query snapshot to be cross-checked at that point, rather than waiting until scan-start time. What's to stop other code using an older snapshot explicitly? That fear may be bogus. Any suggestions? ISTM we don't know whether we're already locked until we get to LockAcquire() and there's no easy way to pass down snapshot information through that, let alone handle RI snapshots. Ideas please. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] elegant and effective way for running jobs inside a database
Hello 2012/3/5 Alvaro Herrera alvhe...@commandprompt.com: Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012: Dear Developers, I am looking for elegant and effective way for running jobs inside a database or cluster - for now I can not find that solution. Yeah, it'd be good to have something. Many people say it's not necessary, and probably some hackers would oppose it; but mainly I think we just haven't agreed (or even discussed) what the design of such a scheduler would look like. For example, do we want it to be able to just connect and run queries and stuff, or do we want something more elaborate able to start programs such as running pg_dump? What if the program crashes -- should it cause the server to restart? And so on. It's not a trivial problem. I agree - it is not simple * workflow support * dependency support a general ACID scheduler can be nice (in pg) but it is not really simple. There was some proposal about using autovacuum demon like scheduler. Pavel -- Á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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Our regex vs. POSIX on longest match
Martijn van Oosterhout klep...@svana.org writes: On the otherhand, I think requiring an overall longest match makes your implementation non-polynomial complexity. Only if you don't know how to implement it -- a DFA-based implementation doesn't have much trouble with this. [ equivalence of knapsack problem to regexes with bounded repetition ] Interesting, but note that neither the POSIX spec nor our implementation permit arbitrarily large repetition counts, so the theoretical NP-completeness is only theoretical. The question is, what are users expecting of the PostgreSQL regex implementation? I think a minimum expectation is that we adhere to the POSIX specification. 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] foreign key locks, 2nd attempt
On Mon, Mar 5, 2012 at 7:53 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: My other comments so far are * some permutations commented out - no comments as to why Something of a fault with the isolation tester that it just shows output, there's no way to record expected output in the spec The reason they are commented out is that they are invalid, that is, it requires running a command on a session that's blocked in the previous command. Obviously, that cannot happen in real life. isolationtester now has support for detecting such conditions; if the spec specifies running a command in a locked session, the permutation is killed with an error message invalid permutation and just continues with the next permutation. It used to simply die, aborting the test. Maybe we could just modify the specs so that all permutations are there (this can be done by simply removing the permutation lines), and the invalid permutation messages are part of the expected file. Would that be better? It would be better to have an isolation tester mode that checks to see it was invalid and if not, report that. At the moment we can't say why you commented something out. There's no comment or explanation, and we need something, otherwise 3 years from now we'll be completely in the dark. Comments required for these points * Why do we need multixact to be persistent? Do we need every page of multixact to be persistent, or just particular pages in certain circumstances? Any page that contains at least one multi with an update as a member must persist. It's possible that some pages contain no update (and this is even likely in some workloads, if updates are rare), but I'm not sure it's worth complicating the code to cater for early removal of some pages. If the multixact contains an xid and that is being persisted then you need to set an LSN to ensure that a page writes causes an XLogFlush() before the multixact write. And you need to set do_fsync, no? Or explain why not in comments... I was really thinking we could skip the fsync of a page if we've not persisted anything important on that page, since that was one of Robert's performance points. * Why do we need to expand multixact with flags? Can we avoid that in some cases? Did you read my blog post? http://www.commandprompt.com/blogs/alvaro_herrera/2011/08/fixing_foreign_key_deadlocks_part_three/ This explains the reason -- the point is that we need to distinguish the lock strength acquired by each locker. Thanks, I will, but it all belongs in a README please. * Why do we need to store just single xids in multixact members? Didn't understand comments, no explanation This is just for SELECT FOR SHARE. We don't have a hint bit to indicate this tuple has a for-share lock, so we need to create a multi for it. Since FOR SHARE is probably going to be very uncommon, this isn't likely to be a problem. We're mainly catering for users of SELECT FOR SHARE so that it continues to work, i.e. maintain backwards compatibility. Good, thanks. Are we actively recommending people use FOR KEY SHARE rather than FOR SHARE, in explicit use? (Maybe I misunderstood your question -- what I think you're asking is, why are there some multixacts that have a single member?) I'll try to come up with a good place to add some paragraphs about all this. Please let me know if answers here are unclear and/or you have further questions. Thanks I think we need to define some test workloads to measure the performance impact of this patch. We need to be certain that it has a good impact in target cases, plus a known impact in other cases. Suggest * basic pgbench - no RI * inserts into large table, RI checks to small table, no activity on small table * large table parent, large table: child 20 child rows per parent, fk from child to parent updates of multiple children at same time low/medium/heavy locking * large table parent, large table: child 20 child rows per parent,fk from child to parent updates of parent and child at same time low/medium/heavy locking -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Command Triggers, patch v11
Hi, Thanks for the extensive testing. I'm adding your tests to the regression suite, and keep wondering if you saw that lots of them were already covered? Did you try make installcheck? Thom Brown t...@linux.com writes: Creating a command trigger using ANY COMMAND results in oid, schemaname, objectname (function parameters 4 5) not being set for either BEFORE or AFTER. Yes, that's documented. It could be better documented though, it seems. There is no support for ALTER CONVERSION. It was missing in the grammar and docs only, added. WARNING: CREATE INDEX CONCURRENTLY is not supported DETAIL: The command trigger will not get fired. This should probably say that it’s not supported on AFTER command triggers yet rather than the general DDL itself. Edited. Command triggers for AFTER creating rules don’t return OIDs. Fixed. Command triggers for creating sequences don’t show the schema: Documented already, it's uneasy to get at it in the code and I figured I might as well drop the ball on that in the current patch's form. Command triggers for AFTER creating extensions with IF NOT EXISTS don’t fire, but do in the ANY COMMAND instance: Fixed. Command triggers on CREATE TEXT SEARCH DICTIONARY show the name as garbage: Fixed, test case added. Command triggers for BEFORE CREATE TYPE (exluding ANY COMMAND) don’t fire if the type isn’t created due to an error: Per design, although we might want to talk about it. I made it so that specific command triggers are only fired after errors checks have been made. That's not the case with ANY command triggers so that you can actually block DDLs on your instances, as has been asked on list. The ANY COMMAND trigger fires on creating roles, but there’s no corresponding allowance to create the trigger explicitly for creating roles. Roles are global objects, you don't want the behavior of role commands to depend on which database you happen to have been logged in when issuing the command. That would call for removing the ANY command support for them too, but I can't seem to decide about that. Any input? Command triggers for AFTER CREATE VIEW don’t show the schema: Couldn't reproduce, added test cases. Command triggers for BEFORE and AFTER ALTER DOMAIN show a garbage name and no schema when dropping a constraint: Fixed, added test cases. Continuing with this same trigger, we do get a schema but a garbage name for OWNER TO: Fixed, added test cases. When an ALTER EXTENSION fails to upgrade, the AFTER ANY COMMAND trigger fires, but not command triggers specifically for ALTER EXTENSION: Same on ALTER EXTENSION, when failing to add a member, the BEFORE ANY COMMAND trigger fires, but not the one specifically for ALTER EXTENSION: Again, per design. Let's talk about it, it will probably need at least documentation. Specific command triggers against ALTER FOREIGN TABLE (i.e. not ANY COMMAND) for BEFORE and AFTER aren’t working when renaming columns: Specific command triggers agains ALTER FUNCTION (i.e. not ANY COMMAND) don’t fire for any changes except renaming, changing owner or changing schema. Everything else fails to trigger (cost, rows, setting configuration parameters, setting strict, security invoker etc.).: I kept some TODO items as I feared I would get bored tomorrow otherwise… There doesn’t appear to be command trigger support for ALTER LARGE OBJECT. Do we want to have some? Those are in between data and command. Specific command triggers on ALTER SEQUENCE don’t fire: Specific command triggers on ALTER TABLE don’t fire for renaming columns: Also renaming attributes doesn’t fire specific triggers: Specific command triggers on ALTER VIEW don’t fire for any type of change: Kept on the TODO. Command triggers on ALTER TYPE when changing owner produce a garbage name: Fixed along with the DOMAIN test case (same code). Specific command triggers on DROP AGGREGATE don’t fire in the IF EXISTS scenario if the target object doesn’t exist: So, do we want to run the command triggers here? Is the IF EXISTS check to be considered like the other error conditions? When adding objects to an extension, then dropping the extension with a cascade, the objects are dropped with it, but triggers aren’t fired to the removal of those dependant objects: Yes, that's expected and needs documenting. Using DROP OWNED BY allows objects to be dropped without their respective specific triggers firing. Expected too. Using DROP SCHEMA … CASACDE also allows objects to be dropped without their respective specific triggers firing: Again, same expectation here. Command triggers on all DROP commands for TEXT SEARCH CONFIGURATION/DICTIONARY/PARSER/TEMPLATE show the schema name as the relation name: Now that's strange and will keep me awake longer tomorrow. Still no command triggers firing for CREATE TABLE AS: Yes, Andres made CTAS a utility command, he didn't add the code that make them fire command triggers. I
Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe
On Mon, Mar 5, 2012 at 2:22 PM, Noah Misch n...@leadboat.com wrote: On Mon, Feb 13, 2012 at 09:29:56AM -0500, Robert Haas wrote: On Fri, Feb 10, 2012 at 11:46 PM, Noah Misch n...@leadboat.com wrote: I've yet to see an MVCC anomaly that one can reproduce at REPEATABLE READ and not at READ COMMITTED. ?They tend to be narrow race conditions at READ COMMITTED, yet easy to demonstrate at REPEATABLE READ. ?Related: http://archives.postgresql.org/pgsql-performance/2011-02/msg00451.php Yeah. Well, that's actually an interesting example, because it illustrates how general this problem is. We could potentially get ourselves into a situation where just about every system catalog table needs an xmin field to store the point at which the object came into existence - or for that matter, was updated. I can see this strategy applying to many relation-pertinent system catalogs. Do you foresee applications to non-relation catalogs? Well, in theory, we have similar issues if, say, a query uses a function that didn't exist at the time the snapshot as taken; the actual results the user sees may not be consistent with any serial execution schedule. And the same could be true for any other SQL object. It's unclear that those cases are as compelling as this one, but then again it's unclear that no one will ever want to fix them, either. For example, suppose we have a view v over a table t that calls a function f. Somebody alters f to give different results and, in the same transaction, modifies the contents of t (but no DDL). This doesn't strike me as a terribly unlikely scenario; the change to t could well be envisioned as a compensating transaction. But now if somebody uses the new definition of f against the old contents of t, the user may fail to get what they were hoping for out of bundling those changes together in one transaction. Now, maybe we're never going to fix those kinds of anomalies anyway, but if we go with this architecture, then I think the chances of it ever being palatable to try are pretty low. In any event, I think a pg_class.relvalidxmin is the right starting point. One might imagine a family of relvalidxmin, convalidxmin, indcheckxmin (already exists), inhvalidxmin, and attvalidxmin. relvalidxmin is like the AccessExclusiveLock of that family; it necessarily blocks everything that might impugn the others. The value in extending this to more catalogs is the ability to narrow the impact of failing the check. A failed indcheckxmin comparison merely excludes plans involving the index. A failed inhvalidxmin check might just skip recursion to the table in question. Those are further refinements, much like using weaker heavyweight lock types. Yes, good parallel. But it's not quite the same as the xmin of the row itself, because some updates might be judged not to matter. There could also be intermediate cases where updates are invalidating for some purposes but not others. I think we'd better get our hands around more of the problem space before we start trying to engineer solutions. I'm not seeing that problem. Any operation that would update some xmin horizon should set it to the greater of its current value and the value the operation needs for its own correctness. If you have something in mind that needs more, could you elaborate? Well, consider something like CLUSTER. It's perfectly OK for CLUSTER to operate on a table that has been truncated since CLUSTER's snapshot was taken, and no serialization anomaly is created that would not have already existed as a result of the non-MVCC-safe TRUNCATE. On the other hand, if CLUSTER operates on a table that was created since CLUSTER's snapshot was taken, then you have a bona fide serialization anomaly. Maybe not a very important one, but does that prove that there's no significant problem of this type in general, or just nobody's thought through all the cases yet? After all, the issues with CREATE TABLE/TRUNCATE vs. a concurrent SELECT have been around for a very long time, and we're only just getting around to looking at them, so I don't have much confidence that there aren't other cases floating around out there. I guess another way to put this is that you could need locks of a great number of different strengths to really handle all the cases. It's going to be unappealing to, say, set the relation xmin when setting the constraint xmin would do, or to fail for a concurrent TRUNCATE as well as a concurrent CREATE TABLE when only the latter logically requires a failure. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe
On Mon, Mar 5, 2012 at 8:46 PM, Robert Haas robertmh...@gmail.com wrote: In any event, I think a pg_class.relvalidxmin is the right starting point. One might imagine a family of relvalidxmin, convalidxmin, indcheckxmin (already exists), inhvalidxmin, and attvalidxmin. relvalidxmin is like the AccessExclusiveLock of that family; it necessarily blocks everything that might impugn the others. The value in extending this to more catalogs is the ability to narrow the impact of failing the check. A failed indcheckxmin comparison merely excludes plans involving the index. A failed inhvalidxmin check might just skip recursion to the table in question. Those are further refinements, much like using weaker heavyweight lock types. Yes, good parallel. Did you guys get my comment about not being able to use an xmin value, we have to use an xid value and to a an XidInMVCCSnapshot() test? Just checking whether you agree/disagree. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] elegant and effective way for running jobs inside a database
W dniu 2012-03-05 20:56, Alvaro Herrera pisze: Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012: Dear Developers, I am looking for elegant and effective way for running jobs inside a database or cluster - for now I can not find that solution. Yeah, it'd be good to have something. Many people say it's not necessary, and probably some hackers would oppose it; but mainly I think we just haven't agreed (or even discussed) what the design of such a scheduler would look like. For example, do we want it to be able to just connect and run queries and stuff, or do we want something more elaborate able to start programs such as running pg_dump? What if the program crashes -- should it cause the server to restart? And so on. It's not a trivial problem. Yes, yes it is not a trivial problem... - tools like pgAgent are good when someone starts play with PostgreSQL - but this great environment (only one serious against something like Oracle or DB2) needs something professional, production ready. It can not happen when we are upgrading database or OS and can not compile pgAgent because of strange dependences... and for example whole sofisticated solution like web application with complicated data flow has a problem... For example I am using stored functions developed in Lua language, which are writing and reading data to and from Redis server with a periods less then one minute. Without heart beat like precise job manager it can not works as professional as it can. Every one can use CRON or something like that - yes it works but PostgreSQL has so many features and something like job manager is inalienable in mine mind. Best regards, Artur 0xAF4A859D.asc Description: application/pgp-keys -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] elegant and effective way for running jobs inside a database
2012/3/5 Artur Litwinowicz ad...@ybka.com: W dniu 2012-03-05 20:56, Alvaro Herrera pisze: Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012: Dear Developers, I am looking for elegant and effective way for running jobs inside a database or cluster - for now I can not find that solution. Yeah, it'd be good to have something. Many people say it's not necessary, and probably some hackers would oppose it; but mainly I think we just haven't agreed (or even discussed) what the design of such a scheduler would look like. For example, do we want it to be able to just connect and run queries and stuff, or do we want something more elaborate able to start programs such as running pg_dump? What if the program crashes -- should it cause the server to restart? And so on. It's not a trivial problem. Yes, yes it is not a trivial problem... - tools like pgAgent are good when someone starts play with PostgreSQL - but this great environment (only one serious against something like Oracle or DB2) needs something professional, production ready. It can not happen when we are upgrading database or OS and can not compile pgAgent because of strange dependences... and for example whole sofisticated solution like web application with complicated data flow has a problem... For example I am using stored functions developed in Lua language, which are writing and reading data to and from Redis server with a periods less then one minute. Without heart beat like precise job manager it can not works as professional as it can. Every one can use CRON or something like that - yes it works but PostgreSQL has so many features and something like job manager is inalienable in mine mind. Long time a strategy for PostgreSQL was a minimal core and extensible modules without duplication some system services. This strategy is valid still but some services are in core - example should be replication. Some proposals about custom scheduler exists http://archives.postgresql.org/pgsql-hackers/2010-02/msg01701.php and it is part of ToDo - so this feature should be in core (in next 2-4 years). Why this is not in core? Nobody wrote it :). Regards Pavel Stehule Best regards, Artur -- 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
Re: [HACKERS] Command Triggers, patch v11
On Monday, March 05, 2012 09:42:00 PM Dimitri Fontaine wrote: Still no command triggers firing for CREATE TABLE AS: Yes, Andres made CTAS a utility command, he didn't add the code that make them fire command triggers. I would expect his patch to get in first, so I don't expect him to be adding that support, I think I will have to add it when rebasing once his patch has landed. That was my assumption as well. Any opinions about adding the patch to the commitfest other than from dim? I feel a bit bad adding it to the in-progress one even if its belongs there because is a part of the command trigger stuff... Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Command Triggers, patch v11
On Sat, Mar 3, 2012 at 2:25 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Right. What I thought I was agreeing with was the notion that you should need to specify more than the trigger name to drop the trigger. Rather like how you can create a trigger AFTER INSERT OR UPDATE OR DELETE, but you don't need to specify all those events to drop the trigger -- just the name will do. The parallel between INSERT/UPDATE/DELETE and the trigger's command is not working well enough, because in the data trigger case we're managing a single catalog entry with a single command, and in the command trigger case, in my model at least, we would be managing several catalog entries per command. To take an example: CREATE COMMAND TRIGGER foo AFTER create table, create view; DROP COMMAND TRIGGER foo; The first command would create two catalog entries, and the second one would delete the same two entries. It used to work this way in the patch, then when merging with the new remove object infrastructure I lost that ability. From the beginning Robert has been saying he didn't want that behavior, and Tom is now saying the same, IIUC. So we're back to one command, one catalog entry. I hadn't made the connection here until you read this, but I agree there's a problem there. One command, one catalog entry is, I think, pretty important. So that means that if want to support a trigger on CREATE TABLE OR CREATE VIEW OR DROP EXTENSION, then the command names (or integers that serve as proxies for them) need to go into an array somewhere, and we had to look for arrays that contain the command we're looking for, rather than just the command name. That might seem prohibitively slow, but I bet if you put a proper cache in place it isn't, because pg_cmdtrigger should be pretty small and not updated very often. You can probably afford to seq-scan it and rebuild your entire cache across all command types every time it changes in any way. But just supporting one command type per trigger seems fine for a first version, too. There's nothing to prevent us from adding that later. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server
I wrote: I'm inclined to think that if we provide this function in core at all, it should take a parameter list long enough to let it fill in the Path completely. That would imply that any future changes in Path structs would result in a change in the parameter list, which would break callers --- but it would break them in an obvious way that the C compiler would complain about. If we leave it as-is, those same callers would be broken silently, because they'd just be failing to fill in the new Path fields. I've committed the PlanForeignScan API change, with that change and some other minor editorialization. The pgsql_fdw patch now needs an update, so I set it back to Waiting On Author state. 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] elegant and effective way for running jobs inside a database
W dniu 2012-03-05 22:09, Pavel Stehule pisze: 2012/3/5 Artur Litwinowicz ad...@ybka.com: W dniu 2012-03-05 20:56, Alvaro Herrera pisze: Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012: Dear Developers, I am looking for elegant and effective way for running jobs inside a database or cluster - for now I can not find that solution. Yeah, it'd be good to have something. Many people say it's not necessary, and probably some hackers would oppose it; but mainly I think we just haven't agreed (or even discussed) what the design of such a scheduler would look like. For example, do we want it to be able to just connect and run queries and stuff, or do we want something more elaborate able to start programs such as running pg_dump? What if the program crashes -- should it cause the server to restart? And so on. It's not a trivial problem. Yes, yes it is not a trivial problem... - tools like pgAgent are good when someone starts play with PostgreSQL - but this great environment (only one serious against something like Oracle or DB2) needs something professional, production ready. It can not happen when we are upgrading database or OS and can not compile pgAgent because of strange dependences... and for example whole sofisticated solution like web application with complicated data flow has a problem... For example I am using stored functions developed in Lua language, which are writing and reading data to and from Redis server with a periods less then one minute. Without heart beat like precise job manager it can not works as professional as it can. Every one can use CRON or something like that - yes it works but PostgreSQL has so many features and something like job manager is inalienable in mine mind. Long time a strategy for PostgreSQL was a minimal core and extensible modules without duplication some system services. This strategy is valid still but some services are in core - example should be replication. Some proposals about custom scheduler exists http://archives.postgresql.org/pgsql-hackers/2010-02/msg01701.php and it is part of ToDo - so this feature should be in core (in next 2-4 years). Why this is not in core? Nobody wrote it :). Regards Pavel Stehule Best regards, Artur -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers Ouch... in next 2-4 years - it broke my heart like a bullet - You should not write it... ;) I feel that I need to set aside SQL, Python, PHP and so on and take to my hands old book about C programming language from university ;) I hope my words are like drops of water for this idea and in the future some people will be happy to use professional job manager :) Best regards, Artur 0xAF4A859D.asc Description: application/pgp-keys -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Command Triggers, patch v11
On 5 March 2012 20:42, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Hi, Thanks for the extensive testing. I'm adding your tests to the regression suite, and keep wondering if you saw that lots of them were already covered? Did you try make installcheck? Yes, but I felt it better that I come up with my own separate tests. Thom Brown t...@linux.com writes: Creating a command trigger using ANY COMMAND results in oid, schemaname, objectname (function parameters 4 5) not being set for either BEFORE or AFTER. Yes, that's documented. It could be better documented though, it seems. Is there a reason why we can't provide the OID for ANY COMMAND... if it's available? I'm guessing it would end up involving having to special case for every command. :/ Command triggers for creating sequences don’t show the schema: Documented already, it's uneasy to get at it in the code and I figured I might as well drop the ball on that in the current patch's form. Fair enough. Command triggers for BEFORE CREATE TYPE (exluding ANY COMMAND) don’t fire if the type isn’t created due to an error: Per design, although we might want to talk about it. I made it so that specific command triggers are only fired after errors checks have been made. That's not the case with ANY command triggers so that you can actually block DDLs on your instances, as has been asked on list. I don't have any strong feelings about it, so I'll bear it in mind for future tests. The ANY COMMAND trigger fires on creating roles, but there’s no corresponding allowance to create the trigger explicitly for creating roles. Roles are global objects, you don't want the behavior of role commands to depend on which database you happen to have been logged in when issuing the command. That would call for removing the ANY command support for them too, but I can't seem to decide about that. Any input? If that's your reasoning, then it would make sense to remove ANY command support for it too. There doesn’t appear to be command trigger support for ALTER LARGE OBJECT. Do we want to have some? Those are in between data and command. *shrug* But ANY COMMAND triggers fire for it. So I'd say either remove support for that, or add a specific trigger. Specific command triggers on DROP AGGREGATE don’t fire in the IF EXISTS scenario if the target object doesn’t exist: So, do we want to run the command triggers here? Is the IF EXISTS check to be considered like the other error conditions? Maybe. If that's expected behaviour, I'll start expecting it then. When adding objects to an extension, then dropping the extension with a cascade, the objects are dropped with it, but triggers aren’t fired to the removal of those dependant objects: Yes, that's expected and needs documenting. Using DROP OWNED BY allows objects to be dropped without their respective specific triggers firing. Expected too. Using DROP SCHEMA … CASACDE also allows objects to be dropped without their respective specific triggers firing: Again, same expectation here. If these are all expected, does it in any way compromise the effectiveness of DDL triggers in major use-cases? I'm not sending a revised patch, please use the github branch if you want to do some more tests already, or ask me for either a new patch version or a patch-on-patch, as you see fit. Hmm... how does that work with regards to the commitfest process? But I'll re-test when you let me know when you've committed your remaining fixes to Github. -- Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] CLUSTER VERBOSE (9.1.3)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Is there any way to get more info out of CLUSTER VERBOSE so it says what index it's working on AFTER the table re-write? INFO: clustering public.values using sequential scan and sort INFO: values: found 0 removable, 260953511 nonremovable row versions in 4224437 pages DETAIL: 0 dead row versions cannot be removed yet. CPU 168.02s/4324.68u sec elapsed 8379.12 sec. And at this point it's doing something(tm), I assume re-doing the indexes. It would be nice(tm) to get more info. Ideas? - -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: l...@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.17 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iQEcBAEBAgAGBQJPVTLJAAoJENC8dtAvA1zmolAIAIgfqXTe5cWZ2ZGVXXVgzv3A pBhi1bVOEB8Xjcie82gMTyqBZKuTtIqNFHXWaB4xVxG6U93YGlru7DnUa8ArzbvW 31b0GHIeXpemUFz0OnuKv6h0Bt+H755YNuDXykN7a7VEdzwIrv/iSSGlBsbEywhG SdC1VvHrmUaRCfCV/XBF4tynC3rocRIyf29SJNPZJl9cJtkK2BDigUeHANN3mydQ 1H1WZ8CMfnTvi8vROGFuk5HCZDv0e9K9dYthfMEqIgKzBRu5jLagijADyEhVCJfO /JYP+t1eGPP1zYqf+R/OfMGTM0RYcP/XVRK8qS+8FPBTUPTphStjmOBPuHRYWDU= =GWPN -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] elegant and effective way for running jobs inside a database
Excerpts from Artur Litwinowicz's message of lun mar 05 18:32:44 -0300 2012: Ouch... in next 2-4 years - it broke my heart like a bullet - You should not write it... ;) I feel that I need to set aside SQL, Python, PHP and so on and take to my hands old book about C programming language from university ;) I hope my words are like drops of water for this idea and in the future some people will be happy to use professional job manager :) Keep in mind that it's not about coding in C but mostly about figuring out what a sane design out to look like. -- Á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] elegant and effective way for running jobs inside a database
W dniu 2012-03-05 22:44, Alvaro Herrera pisze: Excerpts from Artur Litwinowicz's message of lun mar 05 18:32:44 -0300 2012: Ouch... in next 2-4 years - it broke my heart like a bullet - You should not write it... ;) I feel that I need to set aside SQL, Python, PHP and so on and take to my hands old book about C programming language from university ;) I hope my words are like drops of water for this idea and in the future some people will be happy to use professional job manager :) Keep in mind that it's not about coding in C but mostly about figuring out what a sane design out to look like. I understand it... (I meant if you wanna something... do it for your self - it is the fastest way). Regarding a functional area I can help... but I can not understand why this idea is so unappreciated? It will be so powerfull feature - I am working with systems made for goverment (Orcale) - jobs are the core gears for data flow between many systems and other goverment bureaus. Best regards, Artur 0xAF4A859D.asc Description: application/pgp-keys -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] elegant and effective way for running jobs inside a database
On Mon, Mar 5, 2012 at 5:03 PM, Artur Litwinowicz ad...@ybka.com wrote: I understand it... (I meant if you wanna something... do it for your self - it is the fastest way). other way is to fund the work so someone can use his/her time to do it Regarding a functional area I can help... but I can not understand why this idea is so unappreciated? is not unappreciated, is just a problem that already *has* a solution if it were something that currently you can't do it then there would be more people after it It will be so powerfull feature - I am working with systems made for goverment (Orcale) - jobs are the core gears for data flow between many systems and other goverment bureaus. me too, and we solve it with cron -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] performance-test farm
On 12.5.2011 08:54, Greg Smith wrote: Tomas Vondra wrote: The idea is that buildfarm systems that are known to have a) reasonable hardware and b) no other concurrent work going on could also do performance tests. The main benefit of this approach is it avoids duplicating all of the system management and source code building work needed for any sort of thing like this; just leverage the buildfarm parts when they solve similar enough problems. Someone has actually done all that already; source code was last sync'd to the build farm master at the end of March: https://github.com/greg2ndQuadrant/client-code By far the #1 thing needed to move this forward from where it's stuck at now is someone willing to dig into the web application side of this. We're collecting useful data. It needs to now be uploaded to the server, saved, and then reports of what happened generated. Eventually graphs of performance results over time will be straighforward to generate. But the whole idea requires someone else (not Andrew, who has enough to do) sits down and figures out how to extend the web UI with these new elements. Hi, I'd like to revive this thread. A few days ago we have finally got our buildfarm member working (it's called magpie) - it's spending ~2h a day chewing on the buildfarm tasks, so we can use the other 22h to do some useful work. I suppose most of you are busy with 9.2 features, but I'm not so I'd like to spend my time on this. Now that I had to set up the buildfarm member I'm somehow aware of how the buildfarm works. I've checked the PGBuildFarm/server-code and greg2ndQuadrant/client-code repositories and while I certainly am not a perl whiz, I believe I can tweak it to handle the performance-related result too. What is the current state of this effort? Is there someone else working on that? If not, I propose this (for starters): * add a new page Performance results to the menu, with a list of members that uploaded the perfomance-results * for each member, there will be a list of tests along with a running average for each test, last test and indicator if it improved, got worse or is the same * for each member/test, a history of runs will be displayed, along with a simple graph I'm not quite sure how to define which members will run the performance tests - I see two options: * for each member, add a flag run performance tests so that we can choose which members are supposed to be safe OR * run the tests on all members (if enabled in build-farm.conf) and then decide which results are relevant based on data describing the environment (collected when running the tests) I'm also wondering if * using the buildfarm infrastructure the right thing to do, if it can provide some 'advanced features' (see below) * we should use the current buildfarm members (although maybe not all of them) * it can handle one member running the tests with different settings (various shared_buffer/work_mem sizes, num of clients etc.) and various hw configurations (for example magpie contains a regular SATA drive as well as an SSD - would be nice to run two sets of tests, one for the spinner, one for the SSD) * this can handle 'pushing' a list of commits to test (instead of just testing the HEAD) so that we can ask the members to run the tests for particular commits in the past (I consider this to be very handy feature) regards Tomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] elegant and effective way for running jobs inside a database
W dniu 2012-03-05 23:09, Jaime Casanova pisze: On Mon, Mar 5, 2012 at 5:03 PM, Artur Litwinowicz ad...@ybka.com wrote: I understand it... (I meant if you wanna something... do it for your self - it is the fastest way). other way is to fund the work so someone can use his/her time to do it Regarding a functional area I can help... but I can not understand why this idea is so unappreciated? is not unappreciated, is just a problem that already *has* a solution if it were something that currently you can't do it then there would be more people after it It will be so powerfull feature - I am working with systems made for goverment (Orcale) - jobs are the core gears for data flow between many systems and other goverment bureaus. me too, and we solve it with cron And You can modulate the jobs frequency, stop them and start from inside the database automatically using only algorithms and interenal events without administrator hand work... with cron... I can not belive... I do not meant just simple: run stored procedure... I am using cron as well, but in my work I like elegant, complex solutions - many lego blocks is not always the best and simplest solution... 0xAF4A859D.asc Description: application/pgp-keys -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] elegant and effective way for running jobs inside a database
On Mon, Mar 5, 2012 at 4:44 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Artur Litwinowicz's message of lun mar 05 18:32:44 -0300 2012: Ouch... in next 2-4 years - it broke my heart like a bullet - You should not write it... ;) I feel that I need to set aside SQL, Python, PHP and so on and take to my hands old book about C programming language from university ;) I hope my words are like drops of water for this idea and in the future some people will be happy to use professional job manager :) Keep in mind that it's not about coding in C but mostly about figuring out what a sane design out to look like. Just so. And it seems to me that the Right Thing here is to go down the road to having the fabled Stored Procedure Language, which is *not* pl/pgsql, in that iIt needs to run *outside* transactional context. It needs to be able to start transactions, not to run inside them. Given a language which can do some setup of transactions and then run them, this could be readily used for a number of useful purposes, of which a job scheduler would be just a single example. It would enable turning some backend processes from hand-coded C into possibly more dynamically-flexible scripted structures. I'd expect this to be useful for having more customizable/dynamic policies for the autovacuum process, for instance. -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] elegant and effective way for running jobs inside a database
On Mon, Mar 5, 2012 at 12:17 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello 2012/3/5 Alvaro Herrera alvhe...@commandprompt.com: Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012: Dear Developers, I am looking for elegant and effective way for running jobs inside a database or cluster - for now I can not find that solution. Yeah, it'd be good to have something. Many people say it's not necessary, and probably some hackers would oppose it; but mainly I think we just haven't agreed (or even discussed) what the design of such a scheduler would look like. For example, do we want it to be able to just connect and run queries and stuff, or do we want something more elaborate able to start programs such as running pg_dump? What if the program crashes -- should it cause the server to restart? And so on. It's not a trivial problem. I agree - it is not simple * workflow support * dependency support a general ACID scheduler can be nice (in pg) but it is not really simple. There was some proposal about using autovacuum demon like scheduler. I've been thinking about making autovacuum a special case of a general *non*-transactional job-running system because dealing with large physical changes to a database (where one wants to rewrite 300GB of data, or whatever) that are prohibitive in a transaction are -- to understate things -- incredibly painful. Painful enough that people will risk taking their site down with a large UPDATE or ALTER TABLE, hoping that they can survive the duration (and then when they cancel it and are left with huge volumes of dead tuples, things get a lot more ugly). The closest approximation a client program can make is well, I guess I'll paginate through the database and rewrite small chunks. Instead, it may make more sense to have the database spoon-feed work to do the transformations little-at-a-time ala autovacuum. -- fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: URI connection string support for libpq
On ons, 2012-02-22 at 12:26 -0500, Greg Smith wrote: I started collecting up all the variants that do work as an initial shell script regression test, so that changes don't break something that already works. Here are all the variations that already work, setup so that a series of 1 outputs is passing: Let's please add something like this to the patch. Otherwise, I foresee a lot of potential to break corner cases in the future. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] elegant and effective way for running jobs inside a database
Keep in mind that it's not about coding in C but mostly about figuring out what a sane design out to look like. While I can straddle the fence pretty my first reaction is that we are talking about application functionality that falls outside what belongs in core PostgreSQL. I'd rather see pgAgent be improved and act as a basic implementation while, for more complex use-cases, letting the community/marketplace provide solutions. Even with simple use-cases you end up having a separate process continually running anyway. The main benefit to linking with core would be the ability to startup that process after the server starts and shutdown the process before the server shutdown. That communication channel is something to consider outside this specific application and, if done, could be used to talk with whatever designated pgAgent-like application the user chooses. Other applications could also be communicated with in this way. Basically some form of API where in the postgres.conf file you specify which IP addresses and ports you wish to synchronize and which executable to launch just prior to communicating on said port. If the startup routine succeeds that Postgres will, within reason, attempt to communicate and wait for these external process to finish before shutting down. If the external application closes it should proactively notify Postgres that it is doing so AND if you startup a program manually it can look for and talk with a running Postgres instance. David J. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Checksums, state of play
3. Pages with checksums set need to have a version marking to show that they are a later version of the page layout. That version number needs to be extensible to many later versions. Pages of multiple versions need to exist within the server to allow simple upgrades and migration. This is a statement of a problem; do you have a proposed solution for it? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Dropping PL language retains support functions
I have a report related to pg_upgrade where the user states that dropping a PL language retains the PL support functions, and retains the dependency on the PL library, which causes pg_upgrade to complain. The exact case is that the user was using plpython2u in PG 9.0, but the PG 9.1 one-click installer only supplies plpython3u. Pg_upgrade rightly complains that the $libdir/plpython2 is missing. The user removed their plpython2 functions, and then tried pg_upgrade again, and they still got the report of the missing $libdir/plpython2 library. I tested this myself on PG HEAD, and got the same results: CREATE LANGUAGE plpython2u; CREATE LANGUAGE CREATE OR REPLACE FUNCTION pymax (a integer, b integer) RETURNS integer AS $$ if a b: return a return b $$ LANGUAGE plpython2u; CREATE FUNCTION DROP LANGUAGE plpython2u CASCADE; NOTICE: drop cascades to function pymax(integer,integer) DROP LANGUAGE SELECT proname,probin FROM pg_proc WHERE probin LIKE '%python%'; proname | probin --+--- plpython2_call_handler | $libdir/plpython2 plpython2_inline_handler | $libdir/plpython2 plpython2_validator | $libdir/plpython2 (3 rows) I looked at our C code, and we basically set up this dependency: user plpython2 function depends on plpython2 language depends on plpython2_* support functions By doing a DROP CASCADE on plpython2, you drop the user functions, but not the support functions. This certainly looks like a bug. Should I work on a patch? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Speed dblink using alternate libpq tuple storage
Hello, I'm sorry for the abesnce. But it's broken in V3 protocol - getAnotherTuple() will be called only if the packet is fully read. If the packet contents do not agree with packet header, it's protocol error. Only valid EOF return in V3 getAnotherTuple() is when row processor asks for early exit. Original code of getAnotherTuple returns EOF when the bytes to be read is not fully loaded. I understand that this was inappropriately (redundant checks?) written at least for the pqGetInt() for the field length in getAnotherTuple. But I don't understand how to secure the rows (or table data) fully loaded at the point of getAnotherTuple called... Nevertheles the first pgGetInt() can return EOF when the previsous row is fully loaded but the next row is not loaded so the EOF-rerurn seems necessary even if the each row will passed after fully loaded. * Convert old EOFs to protocol errors in V3 getAnotherTuple() Ok. I will do that. * V2 getAnotherTuple() can leak PGresult when handling custom error from row processor. mmm. I will confirm it. * remove pqIsnonblocking(conn) check when row processor returned 2. I missed that it's valid to call PQisBusy/PQconsumeInput/PQgetResult on sync connection. mmm. EOF from getAnotherTuple makes PQgetResult try furthur reading until asyncStatus != PGASYNC_BUSY as far as I saw. And It seemed to do so when I tried to remove 'return 2'. I think that it is needed at least one additional state for asyncStatus to work EOF as desied here. * It seems the return codes from callback should be remapped, (0, 1, 2) is unusual pattern. Better would be: -1 - error 0 - stop parsing / early exit (I'm not done yet) 1 - OK (I'm done with the row) I almost agree with it. I will consider the suggestion related to pqAddRow together. * Please drop PQsetRowProcessorErrMsg() / PQresultSetErrMsg(). Main problem is that it needs to be synced with error handling in rest of libpq, which is unlike the rest of row processor patch, which consists only of local changes. All solutions here are either ugly hacks or too complex to be part of this patch. Ok, I will take your advice. Also considering that we have working exceptions and PQgetRow, I don't see much need for custom error messages. If really needed, it should be introduced as separate patch, as the area of code it affects is completely different. I agree with it. Currently the custom error messaging seems to be the blocker for this patch, because of raised complexity when implementing it and when reviewing it. Considering how unimportant the provided functionality is, compared to rest of the patch, I think we should simply drop it. Ok. My suggestion - check in getAnotherTuple whether resultStatus is already error and do nothing then. This allows internal pqAddRow to set regular out of memory error. Otherwise give generic row processor error. regards, -- Kyotaro Horiguchi 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] pg_upgrade --logfile option documentation
On Tue, Feb 28, 2012 at 09:45:41PM -0500, Bruce Momjian wrote: On Tue, Feb 28, 2012 at 02:15:30PM -0500, Bruce Momjian wrote: On Tue, Feb 28, 2012 at 01:24:45PM -0500, Robert Haas wrote: Running this script will delete the old cluster's data files: /usr/local/pgdev/pg_upgrade/delete_old_cluster.sh I think you should rename the old control file just before the step that says linking user relation files. That's the point after which it becomes unsafe to start the old cluster, right? Yes, it is true that that is the danger point, and also it is much less likely to fail at that point --- it usually happens during the schema creation. I would have to add some more conditional wording without clearly stating if the old suffix is present. OK, I have implemented both Roberts and Àlvaro's ideas in my patch. I only add the .old suffix to pg_controldata when link mode is used, and I now do it after the schema has been created (the most common failure case for pg_upgrade), and just before we actually link files --- both very good ideas. Patch attached; new pg_upgrade output with link mode below. Patch applied. I will now work on the change to keep the schema restore and server logs around in case of a failure. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dropping PL language retains support functions
Bruce Momjian br...@momjian.us writes: By doing a DROP CASCADE on plpython2, you drop the user functions, but not the support functions. Well, yeah. The language depends on the support functions, not the other way around. This certainly looks like a bug. Should I work on a patch? It's not a bug, and it's unlikely you can fix it in pg_upgrade without making things worse. The long-run plan is that the procedural language and its support functions are all part of an extension and what you do is drop the extension. We're not quite there yet. As of 9.1, if you do create extension plpython2 to start with, dropping the extension does drop the support functions too ... but if you use the legacy create language syntax, that doesn't happen, because an extension object isn't created. 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] ECPG FETCH readahead
2012-03-05 19:56 keltezéssel, Noah Misch írta: Having pondered the matter further, I now agree with Michael that the feature should stay disabled by default. See my response to him for rationale. Assuming that conclusion holds, we can recommended a higher value to users who enable the feature at all. Your former proposal of 256 seems fine. OK. BTW, the default disabled behaviour was to avoid make check breakage, see below. I would not offer an ecpg-time option to disable the feature per se. Instead, let the user set the default chunk size at ecpg time. A setting of 1 effectively disables the feature, though one could later re-enable it with ECPGFETCHSZ. This means all code previously going through ECPGdo() would go through ECPGopen()/ECPGfetch()/ECPGclose(). This is more intrusive and all regression tests that were only testing certain features would also test the readahead feature, too. It's a good sort of intrusiveness, reducing the likelihood of introducing bugs basically unrelated to readahead that happen to afflict only ECPGdo() or only the cursor.c interfaces. Let's indeed not have any preexisting test cases use readahead per se, but having them use the cursor.c interfaces anyway will build confidence in the new code. The churn in expected debug output isn't ideal, but I don't prefer the alternative of segmenting the implementation for the sake of the test cases. I see. Also, the test for WHERE CURRENT OF at ecpg time would have to be done at runtime, possibly making previously working code fail if ECPGFETCHSZ is enabled. Good point. How about still allowing NO READAHEAD cursors that compile into plain ECPGdo()? This way, ECPGFETCHSZ don't interfere with WHERE CURRENT OF. But this would mean code changes everywhere where WHERE CURRENT OF is used. ECPGFETCHSZ should only affect cursors that make no explicit mention of READAHEAD. I'm not sure whether that should mean actually routing READHEAD 1 cursors through ECPGdo() or simply making sure that cursor.c achieves the same outcome; see later for a possible reason to still do the latter. Or how about a new feature in the backend, so ECPG can do UPDATE/DELETE ... WHERE OFFSET N OF cursor and the offset of computed from the actual cursor position and the position known by the application? This way an app can do readahead and do work on rows collected by the cursor with WHERE CURRENT OF which gets converted to WHERE OFFSET OF behind the scenes. That's a neat idea, but I would expect obstacles threatening our ability to use it automatically for readahead. You would have to make the cursor a SCROLL cursor. We'll often pass a negative offset, making the operation fail if the cursor query used FOR UPDATE. Volatile functions in the query will get more calls. That's assuming the operation will map internally to something like MOVE N; UPDATE ... WHERE CURRENT OF; MOVE -N. You might come up with innovations to mitigate those obstacles, but those innovations would probably also apply to MOVE/FETCH. In any event, this would constitute a substantive patch in its own right. I was thinking along the lines of a Portal keeping the ItemPointerData for each tuple in the last FETCH statement. The WHERE OFFSET N OF cursor would treat the offset value relative to the tuple order returned by FETCH. So, OFFSET 0 OF == CURRENT OF and other values of N are negative. This way, it doesn't matter if the cursor is SCROLL, NO SCROLL or have the default behaviour with SCROLL in some cases. Then ECPGopen() doesn't have to play games with the DECLARE statement. Only ECPGfetch() needs to play with MOVE statements, passing different offsets to the backend, not what the application passed. One way out of trouble here is to make WHERE CURRENT OF imply READHEAD 1/READHEAD 0 (incidentally, perhaps those two should be synonyms) on the affected cursor. If the cursor has some other readahead quantity declared explicitly, throw an error during preprocessing. I played with this idea a while ago, from a different point of view. If the ECPG code had the DECLARE mycur, DML ... WHERE CURRENT OF mycur and OPEN mycur in exactly this order, i.e. WHERE CURRENT OF appears in a standalone function between DECLARE and the first OPEN for the cursor, then ECPG disabled readahead automatically for that cursor and for that cursor only. But this requires effort on the user of ECPG and can be very fragile. Code cleanup with reordering functions can break previously working code. Failing a reasonable resolution, I'm prepared to withdraw my suggestion of making ECPGFETCHSZ always-usable. It's nice to have, not critical. +bool +ECPGopen(const int lineno, const int compat, const int force_indicator, + const char *connection_name, const bool questionmarks, + const char *curname, const int st, const char *query, ...) +{ + va_list args; + boolret, scrollable; + char
Re: [HACKERS] review: CHECK FUNCTION statement
Hello * I refreshed regress tests and appended tests for multi lines query * There are enhanced checking of SELECT INTO statement * I fixed showing details and hints Regards Pavel Stehule 2012/3/5 Alvaro Herrera alvhe...@commandprompt.com: Excerpts from Pavel Stehule's message of lun mar 05 13:02:50 -0300 2012: small fix of CheckFunctionById function Regards p.s. Alvaro, please, send your patch and I'll merge it Here it is, with your changes already merged. I also added back the new reference doc files which were dropped after the 2012-01-01 version. Note I haven't touched or read the plpgsql checker code at all (only some automatic indentation changes IIRC). I haven't verified the regression tests either. FWIW I'm not going to participate in the other thread; neither I am going to work any more on this patch until the other thread sees some reasonable conclusion. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support check_function-2012-03-06-1.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers