Re: [HACKERS] One-shot expanded output in psql using \gx
I've tested the new \gx against 10beta and current git HEAD. Actually one of my favourite features of PostgreSQL 10! However in my environment it was behaving strangely. After some debugging I found that \gx does not work if you have \set FETCH_COUNT n before. Please find attached a patch that fixes this incl. new regression test. Best regards, Tobias psql_gx_fetch_count_v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Press Release Draft - 2016-02-09 Cumulative Update
Am 08.02.2017 um 20:17 schrieb Alvaro Herrera : > Note that this is likely to fail if the original index name is close to > the 63 chars limit. Perhaps it's enough to add substring() when > computing index_name_tmp. (You could just not use :'index_name' there > and rely on the random md5 only, actually). Watch out for UNIQUE too. thank you for your valuable input! Here is a version that should take both into account - the query also could be simplified a bit: \set index_name 'my_bad_index' \set table_schema 'public' SELECT 'tmp_'||md5(random()::text) AS index_name_tmp \gset SELECT replace(pg_get_indexdef((quote_ident(:'table_schema')||'.'||quote_ident(:'index_name'))::regclass), ' '||quote_ident(:'index_name')||' ON', ' CONCURRENTLY '||:'index_name_tmp'||' ON') \gexec DROP INDEX CONCURRENTLY :"table_schema".:"index_name"; ALTER INDEX :"table_schema".:"index_name_tmp" RENAME TO :"index_name"; > FWIW for previous problems we've documented them in wiki pages along > with suggested solutions, and added a link to that wiki page in the > announce. Perhaps one thing to do is create a wiki page for this one > too (not volunteering myself). I'm not even remotely into the details of the CIC issue, so I'm not the right one to create a page on that topic. But I could put this snippet as a "REINDEX CONCURRENTLY" workaround into the Administrative Snippets category of the wiki, if there are no further objections about the way it works. I always have a bit of mixed feelings with these kind of string manipulations on dynamic SQL. Best, Tobias -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Press Release Draft - 2016-02-09 Cumulative Update
Am 07.02.2017 um 18:44 schrieb Alvaro Herrera : > 80 CREATE INDEX CONCURRENTLY bad_index_name ON table_name (column_name); > /* replace names with your original index definition */ I was thinking if we could replace that "replace names with your original index definition" with something more fancy using pg_get_indexdef in that recipe. I ended up with quite a "REINDEX CONCURRENTLY" monster: \set index_name 'my_bad_index' \set table_schema 'public' SELECT :'index_name'||'_'||left(md5(random()::text), 5) AS index_name_tmp \gset SELECT replace(replace(pg_get_indexdef((quote_ident(:'table_schema')||'.'||quote_ident(:'index_name'))::regclass), 'INDEX '||quote_ident(:'index_name'), 'INDEX '||quote_ident(:'index_name_tmp')), 'CREATE INDEX', 'CREATE INDEX CONCURRENTLY') \gexec DROP INDEX CONCURRENTLY :"table_schema".:"index_name"; ALTER INDEX :"table_schema".:"index_name_tmp" RENAME TO :"index_name"; Probably not useable as a recipe in such an announcement but it was fun to build and to see what is actually possible with some psql magic :) Tobias -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Draft release notes for next week's releases are up for review
Am 04.02.2017 um 18:57 schrieb Tom Lane : > Right now the question is whether individual items are > correctly/adequately documented. > Allow statements prepared with PREPARE to be given parallel plans (Amit > Kapila, Tobias Bussman) another typo taken over from commit log: s/Tobias Bussman/Tobias Bussmann/ thanks Tobias -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parallel execution and prepared statements
> I think if we don't see any impact then we should backpatch this > patch. However, if we decide to go some other way, then I can provide > a separate patch for back branches. BTW, what is your opinion? I could not find anything on backporting guidelines in the wiki but my opinion would be to backpatch the patch in total. With a different behaviour between the simple and extended query protocol it would be hard to debug query performance issue in user applications that uses PQprepare. If the user tries to replicate a query with a PREPARE in psql and tries to EXPLAIN EXECUTE it, the results would be different then what happens within the application. That behaviour could be confusing, like differences between EXPLAIN SELECT and EXPLAIN EXECUTE can be to less experienced users. Best regards Tobias -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parallel execution and prepared statements
> On Thu, Dec 1, 2016 at 9:40 PM, Robert Haas wrote: >> >> OK, then my vote is to do it that way for now. Thanks for your opinion. That's fine with me. > Am 02.12.2016 um 07:22 schrieb Amit Kapila : > Done that way in attached patch. Did a quick review: The patch applies cleanly against current head. make installcheck with force_parallel_mode = regress passes all tests. My manual tests show that parallel query is working for prepared statements in SQL with PREPARE and EXECUTE. CREATE TABLE AS EXECUTE is working, EXPLAIN on that shows a parallel plan, EXPLAIN ANALZE indicates 0 launched workers for that. Looks fine so far! You should however include a sentence in the documentation on that parallel plan w/o workers corner-case behaviour. Feel free to take that from my patch or phase a better wording. And again my question regarding back patching to 9.6: - 9.6 is currently broken as Laurenz showed in [1] - 9.6 does not have documented that SQL PREPARE prepared statements cannot not use parallel query The former could be fixed by back patching the full patch which would void the latter. Or it could be fixed by disabling generation of parallel plans in extended query protocol prepare. Alternatively only the change in execMain.c could be back patched. In these cases we would need to have the a separate wording for the 9.6 docs. Best regards, Tobias [1] a737b7a37273e048b164557adef4a58b53999...@ntex2010i.host.magwien.gv.at -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fix typo in ecpg.sgml
> Right, and the "SQL" was missing, too. Thanks for spotting, fixed in > HEAD. oops. Thanks for taking care. Tobias -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Fix typo in ecpg.sgml
Hi, there is a missing "EXEC" in ecpg.sgml in the list of transaction management commands. Attached a trivial patch to fix this. Best regards Tobias ecpg-doc-typo.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parallel execution and prepared statements
Am 18.11.2016 um 14:21 schrieb Albe Laurenz :
> But then the change to use CURSOR_OPT_PARALLEL_OK in tcop/postgres.c should
> be reverted as well, because it breaks things just as bad:
>
> /* creates a parallel-enabled plan */
> PQprepare(conn, "pstmt", "SELECT id FROM l WHERE val = $1", 1, NULL);
> /* blows up with "cannot insert tuples during a parallel operation" */
> PQexec(conn, "CREATE TABLE bad(id) AS EXECUTE pstmt('abcd')");
Great example of mixing a v3 prepare with an simple query execute. I didn't
think about that while even the docs state clearly: "Named prepared statements
can also be created and accessed at the SQL command level, using PREPARE and
EXECUTE." Sticking with either protocol version does not trigger the error.
> I think we should either apply something like that patch or disable parallel
> execution for prepared statements altogether and document that.
So we likely need to backpatch something more then a doc-fix for 9.6. Given the
patch proposals around, this would either disable a feature (in extended query
protocol) or add a new one (in simple query protocol/SQL). Or would it be more
appropriate to split the patch and use CURSOR_OPT_PARALLEL_OK in prepare.c on
master only? I'm asking in case there is a necessity to prepare a proposal for
an documentation patch targeting 9.6 specifically.
Best regards
Tobias
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parallel execution and prepared statements
> True, but we also try to avoid it whenever possible, because it's > likely to lead to poor performance. This non-readonly case should be way less often hit compared to other uses of prepared statements. But sure, it depends on the individual use case and a likely performance regession in these edge cases is nothing to decide for easily. > I think it would be a good idea to come up with a way for a query to > produce both a parallel and a non-parallel plan and pick between them > at execution time. However, that's more work than I've been willing > to undertake. Wouldn't the precautionary generation of two plans always increase the planning overhead, which precisely is what one want to reduce by using prepared statements? Best regards Tobias -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parallel execution and prepared statements
> Yeah, we could do something like this, perhaps not in exactly this > way, but I'm not sure it's a good idea to just execute the parallel > plan without workers. sure, executing parallel plans w/o workers seems a bit of a hack. But: - we already do it this way in some other situations - the alternative in this special situation would be to _force_ replanning without the CURSOR_OPT_PARALLEL_OK. The decision for replanning is hidden deep within plancache.c and while we could influence it with CURSOR_OPT_CUSTOM_PLAN this wouldn't have an effect if the prepared statement doesn't have any parameters. Additionally, influencing the decision and generating a non-parallel plan would shift the avg cost calculation used to choose custom or generic plans. Maybe someone can come up with a better idea for a solution. These three approaches are all I see so far. Best regards, Tobias Bussmann -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parallel execution and prepared statements
> Can you once test by just passing CURSOR_OPT_PARALLEL_OK in > PrepareQuery() and run the tests by having forc_parallel_mode=regress? > It seems to me that the code in the planner is changed for setting a > parallelModeNeeded flag, so it might work as it is. Do you mean running a "make installcheck" with "force_parallel_mode=regress" in postgresql.conf? I did so with just CURSOR_OPT_PARALLEL_OK in PrepareQuery (like the original commit 57a6a72b) and still got 3 failed tests, all with CREATE TABLE .. AS EXECUTE .. . With my patch applied, all tests were successful. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parallel execution and prepared statements
As the patch in [1] targeting the execution of the plan in ExecutePlan depending on the destination was declined, I hacked around a bit to find another way to use parallel mode with SQL prepared statements while disabling the parallel execution in case of an non read-only execution. For this I used the already present test for an existing intoClause in ExecuteQuery to set the parallelModeNeeded flag of the prepared statement. This results in a non parallel execution of the parallel plan, as we see with a non-zero fetch count used with the extended query protocol. Despite this patch seem to work in my tests, I'm by no means confident this being a proper way of handling the situation in question. Best Tobias [1] https://www.postgresql.org/message-id/CAA4eK1KxiYm8F9Pe9xvqzoZocK43w%3DTRPUNHZpe_iOjF%3Dr%2B_Vw%40mail.gmail.com prepared_stmt_execute_parallel_query.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parallel execution and prepared statements
Thanks Laurenz, for your help with debugging on this topic. I was preparing a message to the list myself and found an earlier discussion [1] on the topic. If I understand it correctly, the issue is with CREATE TABLE ... AS EXECUTE So it seems parallel execution of prepared statements was intentionally disabled in 7bea19d. However, what is missing is at least a mention of that current limitation in the 9.6 docs at "When Can Parallel Query Be Used?" [2] Best regards, Tobias [1] https://www.postgresql.org/message-id/CA%2BTgmoaxyXVr6WPDvPQduQpFhD9VRWExXU7axhDpJ7jZBvqxfQ%40mail.gmail.com [2] https://www.postgresql.org/docs/current/static/when-can-parallel-query-be-used.html > Am 15.11.2016 um 16:41 schrieb Albe Laurenz : > > Tobias Bussmann has discovered an oddity with prepared statements. > > Parallel scan is used with prepared statements, but only if they have > been created with protocol V3 "Parse". > If a prepared statement has been prepared with the SQL statement PREPARE, > it will never use a parallel scan. > > I guess that is an oversight in commit 57a6a72b, right? > PrepareQuery in commands/prepare.c should call CompleteCachedPlan > with cursor options CURSOR_OPT_PARALLEL_OK, just like > exec_prepare_message in tcop/postgres.c does. > > The attached patch fixes the problem for me. > > Yours, > Laurenz Albe > <0001-Consider-parallel-plans-for-statements-prepared-with.patch> > -- > 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] extract text from XML
> I have found a basic use case which is supported by the xml2 module, > but is unsupported by the new XML API. > It is not possible to correctly extract text Indeed. I came accross this shortcomming some months ago myself but still manage an item on my ToDo list to report it here as the deprecation notice at https://www.postgresql.org/docs/devel/static/xml2.html#AEN180625 asks for. Done, thanks ;) I did some archive-browsing on that topic. The issue (if you want to call it that way) was introduced by an patch to ensure xpath() always returns xml, applied for 9.2 after some discussion: https://www.postgresql.org/message-id/201106291934.23089.rsmogura%40softperience.eu and is since then known: https://www.postgresql.org/message-id/1409795403248-5817667.post%40n5.nabble.com The new behaviour was later reported as a bug and discussed again: https://www.postgresql.org/message-id/CAAY5AM1L83y79rtOZAUJioREO6n4%3DXAFKcGu6qO3hCZE1yJytg%40mail.gmail.com Anyhow - (un)escaping functions to support the text<->xml conversion are often talked about but still seem only to be found in xml2 module. Seeing a xmltable implementing patch here recently, these functions would be another step to make the contrib module obsolete, finally. > Perhaps a function xpath_value(text, xml) -> text[] would close the gap? such an design, resembling the xml2 behaviour, would certainly fit the need, imho. regards Tobias -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
