Re: [HACKERS] Performance improvement for joins where outer side is unique
On 20 March 2015 at 16:11, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: I think this satisfies your wish and implemented in non exhaustive-seearch-in-jointree manner. It still don't have regressions for itself but I don't see siginificance in adding it so much... This seems quite a bit better. Having the inner_unique variable as part of the JoinPath struct seems much better than what I had. This seems to remove the requirement of my patch that all joins to that RelOptInfo be unique. I also quite like the more recent change to make_hashjoin and co. just pass the JoinPath as a parameter. I don't really like the (inner unique) being tagged onto the end of the join node, but there's not much point in spending too much time talking about that right now. There's much better things to talk about. I'm sure we can all bikeshed around that one later. In joinpath.c you have a restriction to only perform the unique check for inner joins.. This should work ok for left joins too, but it would probably be more efficient to have the left join removal code analyse the SpecialJoinInfos during checks for left join removals. I think it would just be a matter of breaking down the join removal code similar to how I did in my patch, but this time add a bool inner_unique to the SpecialJoinInfo struct. The join_is_legal() function seems to select the correct SpecialJoinInfo if one exists, so add_paths_to_joinrel() shouldn't need to call relation_has_unique_index_for() if it's a LEFT JOIN, as we'll already know if it's unique by just looking at the property. You've also lost the ability to detect that subqueries are unique: create table j1(id int primary key); create table j2(value int not null); explain select * from j1 inner join (select distinct value from j2) j2 on j1.id=j2.value; The left join removal code properly detects this, so I think unique joins should too. I can continue working on your patch if you like? Or are you planning to go further with it? Regards David Rowley
Re: [HACKERS] Using 128-bit integers for sum, avg and statistics aggregates
On Fri, Mar 20, 2015 at 2:39 AM, Andreas Karlsson andr...@proxel.se wrote: On 03/20/2015 10:32 AM, Andres Freund wrote: Pushed with that additional change. Let's see if the buildfarm thinks. Thanks for the feature. Thanks to you and all the reviewers for helping me out with it. Indeed. Thanks for your efforts, Andreas. -- Peter Geoghegan -- 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] Using 128-bit integers for sum, avg and statistics aggregates
On 2015-03-20 00:49:07 +0100, Andreas Karlsson wrote: On 03/19/2015 07:08 PM, Andres Freund wrote: Working on committing this: Nice fixes. Sorry about forgetting numericvar_to_int*. As for the reviewers those lists look pretty much correct. David Rowley should probably be added to the second patch for his early review and benchmarking. Pushed with that additional change. Let's see if the buildfarm thinks. Thanks for the feature. Greetings, Andres Freund -- Andres Freund 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] Add min and max execute statement time in pg_stat_statement
On 2015-02-21 16:09:02 -0500, Andrew Dunstan wrote: I think all the outstanding issues are fixed in this patch. Do you plan to push this? I don't see a benefit in delaying things any further... Greetings, Andres Freund -- Andres Freund 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] Using 128-bit integers for sum, avg and statistics aggregates
On 03/20/2015 10:32 AM, Andres Freund wrote: Pushed with that additional change. Let's see if the buildfarm thinks. Thanks for the feature. Thanks to you and all the reviewers for helping me out with it. Andreas -- 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] assessing parallel-safety
On Thu, Mar 19, 2015 at 8:53 PM, Amit Kapila amit.kapil...@gmail.com
wrote:
On Wed, Mar 18, 2015 at 9:31 PM, Robert Haas robertmh...@gmail.com
wrote:
On Tue, Mar 17, 2015 at 9:48 AM, Robert Haas robertmh...@gmail.com
wrote:
On Tue, Mar 17, 2015 at 2:26 AM, Noah Misch n...@leadboat.com wrote:
Neither that rule, nor its variant downthread, would hurt operator
authors too
much. To make the planner categorically parallel-safe, though,
means limiting
evaluate_function() to parallel-safe functions. That would
dramatically slow
selected queries. It's enough for the PL scenario if planning a
parallel-safe
query is itself parallel-safe. If the planner is parallel-unsafe
when
planning a parallel-unsafe query, what would suffer?
Good point. So I guess the rule can be that planning a parallel-safe
query should be parallel-safe. From there, it follows that estimators
for a parallel-safe operator must also be parallel-safe. Which seems
fine.
More work is needed here, but for now, here is a rebased patch, per
Amit's request.
Apart from this, I have one observation:
static int
exec_stmt_execsql(PLpgSQL_execstate *estate,
PLpgSQL_stmt_execsql
*stmt)
{
ParamListInfo paramLI;
long tcount;
int rc;
PLpgSQL_expr *expr =
stmt-sqlstmt;
/*
* On the first call for this statement generate the plan, and detect
* whether
the statement is INSERT/UPDATE/DELETE
*/
if (expr-plan == NULL)
{
ListCell *l;
exec_prepare_plan(estate, expr, 0);
Shouldn't we need parallelOk in function exec_stmt_execsql()
to pass cursoroption in above function as we have done in
exec_run_select()?
Today while integrating parallel_seqscan patch with this patch, I had
another observation which is that even if the function is parallel-unsafe,
it still can treat statements inside that function as parallel-safe and
allow
parallelism on such statements.
I think the code in question is as below:
@@ -496,7 +496,9 @@ init_execution_state(List *queryTree_list,
if (queryTree-commandType == CMD_UTILITY)
stmt = queryTree-utilityStmt;
else
- stmt = (Node *) pg_plan_query(queryTree, 0, NULL);
+ stmt = (Node *) pg_plan_query(queryTree,
+ fcache-readonly_func ? CURSOR_OPT_PARALLEL_OK : 0,
+ NULL);
Basically this is executing a statement inside a function and if the
function is parallel-unsafe, and statement it is trying to execute is
parallel-safe (contains no other parallel-unsafe expressions), then
it will choose a parallel plan for such a statement. Shouldn't we
try to avoid such cases?
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] configure can't detect proper pthread flags
On Fri, Mar 20, 2015 at 5:20 AM, Andrew Gierth and...@tao11.riddles.org.uk wrote: Max == Max Filippov jcmvb...@gmail.com writes: Max Sorry, I must be not clear enough: why checking compiler/linker Max output instead of checking their exit code or presence of produced Max object/ executable files? Going by the comment some lines above, my guess would be because some compilers accept some option like -pthreads and issue a warning message saying that it is ignored, and pg wants to not treat such options as valid I've somehow missed that comment, thank you Andrew. -- Max -- 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] GSoC - Idea Discussion
Hello devs, Thank you so much for the feedback, to answer to your questions: Tomas:So you've created an array of 1M integers, and it's 7x faster on GPU compared to pg_qsort(), correct? No, I meant general sorting, not on pg_qsort() Well, it might surprise you, but PostgreSQL almost never sorts numbers like this. PostgreSQL sorts tuples, which is way more complicated and, considering the variable length of tuples (causing issues with memory access), rather unsuitable for GPU devices. I might be missing something, of course.Also, it often needs additional information, like collations when sorting by a text field, for example. I totally agree with you on this point, my current target area is very confined as this is the beginning, I'm only considering integer values in one row. Why don't you show us the source code? Would be simpler than explaining what it does. You can have a look at the code here: https://github.com/hiteshramani/Postgres-CUDAThis is a compiled code, you can see the call to CUDA function in src/port/qsort.c and .h files - qsort_normal.h and qsort_cuda.h. The hello world program is in src/port/qsort_cuda.cu. Compilation happens in 2 phases - compile and link, I compiled the cuda file with nvcc and for linked I edited the makefile of src/timezone/ because zic build needed the linking of the cuda file. Suggestions are welcome. I'd recommend discussing the code here. It's certainly quite complex, especially if this is your first encounter with it. Yes, I felt it's a little complex but couldn't find a lot of help resources online. I'm looking for help. PostgreSQL uses adaptive sort - in-memory when it fits into work_mem, on-disk when it does not. This is decided at runtime.You'll have to do the same thing, because the amount of memory available on GPUs is limited to a few GBs, and it needs to work for datasets exceeding that limit (the amount of data is uncertain at planning time). Yes, I thought of that too. A call could be made with the integer array as an input to the GPU. The GPU then returns the result with a sorted array. I want to proceed step by step, as there are methods to sort amount which exceed the GPU memory. Álvaro Herrera:I downloaded the zip of the latest custom_join repo I saw 2 days ago. I'll check once again. Thank you. :) KaiGai Kohei: Let me say CUDA is better than OpenCL :-)Because of software quality of OpenCL runtime drivers provided by each vendor,I've often faced mysterious problems. Only nvidia's runtime are enough reliablefrom my point of view. In addition, when we implement using OpenCL is a featurefully depends on hardware characteristics, so we cannot ignore physical hardwareunderlying the abstraction layer.So, I'm now reworking the code to move CUDA from OpenCL. That's great, I'd love to help you with that and contribute in it. It seems to me you are a little bit optimistic.Unlike CPU code, GPU-Sorting logic has to reference device memory space,so all the data to be compared needs to be transferred to GPU devices.Any pointer on host address space is not valid on GPU calculation.Amount of device memory is usually smaller than host memory, so your codeneeds a capability to combined multiple chunks that is partially sorted...Probably, it is not all here. Aren't there algorithms which help you if the device memory is limited and the data is massive? I have a rough memory because I did a course online, where I saw algorithms to deal with such problems I suppose. Thanks and Regards,Hitesh Ramani
Re: [HACKERS] GSoC 2015: Extra Jsonb functionality
On 20 March 2015 at 11:21, Dmitry Dolgov 9erthali...@gmail.com wrote:
Perhaph it's my misunderstanding, but this would seem to be more of an
intersection operation on keys rather than a delete.
Hm...why? We remove all elements, which are contains in the first and second
jsonb (f: [4, 5] in this case) from the first one.
On further thought, yes, I agree.
Could there be a corresponding jsonb_except function which does the
opposite (i.e. returns everything on the left side except where it matches
with the right)?
and if I understand your question correctly, this is exactly what the
jsonb_delete_jsonb will do, isn't it?.
Ah, yes, that's true.
Is there a use-case for the example you've given above, where you take
JSON containing objects and arrays, and flatten them out into a
one-dimensional array?
Hm...actually I don't know about such use-cases. This function is analog of
the hstore_to_array (and the similar function hstore_to_matrix), which is
used sometimes, judging by github. So I thought this function should be
implemented (after this question I'm not so sure).
Yeah, hstore was just key=value, so flattening it out resulted in a
simple {key,value,key,value} array. I don't think that's useful with
json.
What should happen if g or {g} were used instead?
Did you mean {g: key}? Hmm...but in any case, I suppose this new object
should be appended to the array as a regular element.
=# jsonb_add_to_path('{b: {c: [d, f]}}'::jsonb, {b, c}::text[],
'g'::jsonb);
jsonb_add_to_path
---
{b: {c: [d, f, g]}}
Would this also be the case for this function?...
# jsonb_add_to_path('{b: {c: [d, f]}}'::jsonb, {b, c}::text[],
'{g:4}'::jsonb);
jsonb_add_to_path
{b: {c: [d, f, {g: 4}]}}
--
Thom
--
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] btree_gin and ranges
Teodor Sigaev wrote: Teodor's patch could use some more comments. The STOP_SCAN/MATCH_SCAN/CONT_SCAN macros are a good idea, but they probably should go into src/include/access/gin.h so that they can be used in all compare_partial implementations. STOP_SCAN/MATCH_SCAN/CONT_SCAN macros are moved to gin's header, and comments are improved. Split patch to two: gin and module Here you forgot to git add the two .sql files for the extension. They are present in the patch Heikki posted upthread but not here. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] configure can't detect proper pthread flags
On Fri, Mar 20, 2015 at 6:08 AM, Tom Lane t...@sss.pgh.pa.us wrote: We don't want every link step producing a useless warning. Ideally, make -s would print nothing whatsoever; to the extent that tools produce unsuppressable routine chatter, that's evil because it makes it harder to notice actually-useful warnings. Then maybe stderr tests should grep output for a specific option, the one we're currently testing, not just any noise? -- Thanks. -- Max -- 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] GSoC 2015: Extra Jsonb functionality
Perhaph it's my misunderstanding, but this would seem to be more of an
intersection operation on keys rather than a delete.
Hm...why? We remove all elements, which are contains in the first and
second jsonb (f: [4, 5] in this case) from the first one.
Could there be a corresponding jsonb_except function which does the
opposite (i.e. returns everything on the left side except where it matches
with the right)?
and if I understand your question correctly, this is exactly what the
jsonb_delete_jsonb will do, isn't it?.
Is there a use-case for the example you've given above, where you take
JSON containing objects and arrays, and flatten them out into a
one-dimensional array?
Hm...actually I don't know about such use-cases. This function is analog of
the hstore_to_array (and the similar function hstore_to_matrix), which is
used sometimes, judging by github. So I thought this function should be
implemented (after this question I'm not so sure).
What should happen if g or {g} were used instead?
Did you mean {g: key}? Hmm...but in any case, I suppose this new object
should be appended to the array as a regular element.
=# jsonb_add_to_path('{b: {c: [d, f]}}'::jsonb, {b, c}::text[],
'g'::jsonb);
jsonb_add_to_path
---
{b: {c: [d, f, g]}}
This is a bit strange. Why did f get flattened out of d?
The main purpose if this function is to get values for required keys from
all nesting levels (actually, I thougth it will be not so convenient
otherwise and I didn't consider the implementation with path usage). If
this so confusing, I can remove this function from the list =)
On 20 March 2015 at 00:08, Alvaro Herrera alvhe...@2ndquadrant.com wrote:
Thom Brown wrote:
On 19 March 2015 at 14:35, Alvaro Herrera alvhe...@2ndquadrant.com
wrote:
Thom Brown wrote:
On 19 March 2015 at 14:12, Alvaro Herrera alvhe...@2ndquadrant.com
wrote:
Dmitry Dolgov wrote:
* jsonb_slice - extract a subset of an jsonb
Example of usage:
Okay, so it pulls it all parents? So I guess you'd get this too:
SELECT jsonb_slice('{a: 1, b: {c: 2}, d: {f: 3}, f:
4}'::jsonb, ARRAY['b', 'f', 'x']);
jsonb_slice
{a: 1, b: {c: 2}, d: {f: 3}, f: 4}
Yeah, except a wouldn't be output, of course. (The example gets more
interesting if d contains more members than just f. Those would not
get output.)
Although I'm still a bit confused about f being produced.
I guess you could say that the second argument is an array of element
paths, not key names. So to get the result I suggest, you would have
to
use ARRAY['{b}', '{d,f}', '{x}']. (Hm, this is a non-rectangular
array actually... I guess I'd go for ARRAY['b', 'd//f', 'x'] instead,
or
whatever the convention is to specify a json path).
I think that's where jsquery would come in handy.
If that's what we think, then perhaps we shouldn't accept jsonb_slice at
all because of ambiguous mode of operation.
--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Fri, Mar 20, 2015 at 05:50:03PM -0700, David G. Johnston wrote:
I'm not sure that this particular feature of the standard is something we
should encourage.
Its actually quite useful in this situation, and so maybe the novelty is just
making me nervous, but the only reason I know of this behavior is because
I've
seen a number of posts in just the past couple of years when people
accidentally used this feature and then were surprised when they didn't get an
error. If this stays I would suggest that we take the opportunity to
cross-reference back to where the syntax is defined so people aren't left
scratching their heads as to why it works - or why if they remove the newline
in their own attempt the code suddenly breaks.
Yeah, I am kind on the fence about it, but it is a nice feature,
particulary for PL/pgSQL programs. I added a mention of the string
concatentation feature --- patch attached, and URL updated.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index aa19e10..3195655
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 2998,3011
para
literalI/literal treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null.
/para
/listitem
listitem
para
literalL/literal quotes the argument value as an SQL literal.
A null value is displayed as the string literalNULL/, without
! quotes.
/para
/listitem
/itemizedlist
--- 2998,3012
para
literalI/literal treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null (equivalent to
! functionquote_ident/).
/para
/listitem
listitem
para
literalL/literal quotes the argument value as an SQL literal.
A null value is displayed as the string literalNULL/, without
! quotes (equivalent to functionquote_nullable/function).
/para
/listitem
/itemizedlist
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 158d9d2..9fc2a2f
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*** EXECUTE 'SELECT count(*) FROM mytable WH
*** 1217,1227
dynamically selected table, you could do this:
programlisting
EXECUTE 'SELECT count(*) FROM '
! || tabname::regclass
|| ' WHERE inserted_by = $1 AND inserted lt;= $2'
INTO c
USING checked_user, checked_date;
/programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
--- 1217,1236
dynamically selected table, you could do this:
programlisting
EXECUTE 'SELECT count(*) FROM '
! || quote_ident(tabname)
|| ' WHERE inserted_by = $1 AND inserted lt;= $2'
INTO c
USING checked_user, checked_date;
/programlisting
+ A cleaner approach is to use functionformat()/'s literal%I/
+ specification for table or column names (strings separated by a
+ newline are concatenated):
+ programlisting
+ EXECUTE format('SELECT count(*) FROM %I '
+'WHERE inserted_by = $1 AND inserted lt;= $2', tabname)
+INTO c
+USING checked_user, checked_date;
+ /programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
*** EXECUTE 'SELECT count(*) FROM '
*** 1297,1307
/para
para
! Dynamic values that are to be inserted into the constructed
! query require careful handling since they might themselves contain
quote characters.
! An example (this assumes that you are using dollar quoting for the
! function as a whole, so the quote marks need not be doubled):
programlisting
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
--- 1306,1320
/para
para
! Dynamic values require careful handling since they might contain
quote characters.
! An example using functionformat()/ (this assumes that you are
! dollar quoting the function body so quote marks need not be doubled):
! programlisting
! EXECUTE format('UPDATE tbl SET %I = $1 '
!'WHERE key = $2', colname) USING newvalue, keyvalue;
! /programlisting
! It is also possible to call the quoting functions directly:
programlisting
EXECUTE 'UPDATE tbl SET '
Re: [HACKERS] Abbreviated keys for Numeric
Peter == Peter Geoghegan p...@heroku.com writes: Peter Attached is a revision of this patch, that I'm calling v2. What Peter do you think, Andrew? No. is I think the best summary of my response. I strongly suggest whichever committer ends up looking at this consider my original version unchanged in preference to this. The cost/benefit decision of supporting abbreviation on 32bit platforms is a point that can be debated (I strongly support retaining the 32bit code, obviously), but the substantive changes here are actively wrong. Peter Other than that, I've tried to keep things closer to the text Peter opclass. For example, the cost model now has a few debugging Peter traces (disabled by default). I have altered the ad-hoc cost Peter model so that it no longer concerns itself with NULL inputs, Peter which seemed questionable (not least since the abbreviation Peter conversion function isn't actually called for NULL inputs. Any Peter attempt to track the full count within numeric code therefore Peter cannot work.). This is simply wrong. The reason why the cost model (in my version) tracks non-null values by having its own counter is precisely BECAUSE the passed-in memtupcount includes nulls, and therefore the code will UNDERESTIMATE the fraction of successfully abbreviated values if the comparison is based on memtupcount. In your version, if there are null values at the start of the input, then on the first non-null value after that, memtupcount will be 1 and there will be only 1 distinct abbreviated value, causing abbreviation to spuriously abort. The test to clamp the estimate to 1.0 is just nonsensical and serves no purpose whatever, and the comment for it is wrong. You should fix the text abbreviation code, not propagate your mistakes further. (BTW, there's an outright typo in your code, ';;' for ';' at the end of a line. Sloppy.) Peter I also now allocate a buffer of scratch memory separately from Peter the main sortsupport object - doing one allocation for all Peter sortsupport state, bunched together as a buffer seemed like a Peter questionable micro-optimization. It's yet another cache line... I admit I did not benchmark that choice, but then neither did you. Peter It seemed unwise to silently disable abbreviation when someone Peter happened to build with DEC_DIGITS != 4. A static assertion now Peter gives these unusual cases the opportunity to make an informed Peter decision about either disabling abbreviation or not changing Peter DEC_DIGITS in light of the performance penalty, in a Peter self-documenting way. A) Nobody in their right minds is ever going to do that anyway B) Anybody who does that is either not concerned about performance or is concerned only about performance of the low-level numeric ops, and abbreviation is the last thing they're going to be worried about in either case. -- Andrew (irc:RhodiumToad) -- 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: Add launchd Support
On Mar 19, 2015, at 8:12 PM, Bruce Momjian br...@momjian.us wrote: Where are we on this? I suggested this plist: dict keyDisabled/key false/ keyLabel/key stringorg.postgresql.postgresql/string keyUserName/key stringpostgres/string keyGroupName/key stringpostgres/string keyProgramArguments/key array string/usr/local/pgsql/bin/postgres/string string-D/string string/usr/local/pgsql/data/string /array keyStandardOutPath/key string/usr/local/pgsql/data/launchd.log/string keyStandardErrorPath/key string/usr/local/pgsql/data/launchd.log/string keyOnDemand/key!-- OS X 10.4 -- false/ keyKeepAlive/key!-- OS X 10.5+ -- true/ /dict /plist No one replied. Want a new patch with that? David smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] Superuser connect during smart shutdown
On 3/20/15 9:44 AM, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: On Thu, Mar 19, 2015 at 10:42 PM, Bruce Momjian br...@momjian.us wrote: On Mon, Oct 20, 2014 at 03:10:50PM -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Oct 19, 2014 at 12:27 PM, Tom Lane t...@sss.pgh.pa.us wrote: I've certainly objected to it in the past, but I don't believe I was the only one objecting. What's your feeling now? I'm prepared to yield on the point. OK, are we up for changing the default pg_ctl shutdown method for 9.5, (smart to fast), or should we wait for 9.6? I'm up for it. I think it's long overdue. +1 +1, but I also like the idea of allowing SU to connect during a smart shutdown. Even if you've intentionally chosen smart instead of fast it still sucks that you can't find out what's actually holding things up (and ps isn't that great a solution). -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] GSoC - Idea Discussion
KaiGai Kohei: It seems to me you are a little bit optimistic. Unlike CPU code, GPU-Sorting logic has to reference device memory space, so all the data to be compared needs to be transferred to GPU devices. Any pointer on host address space is not valid on GPU calculation. Amount of device memory is usually smaller than host memory, so your code needs a capability to combined multiple chunks that is partially sorted... Probably, it is not all here. Aren't there algorithms which help you if the device memory is limited and the data is massive? I have a rough memory because I did a course online, where I saw algorithms to deal with such problems I suppose. What I took is a hybrid approach to process data set overs device memory limitation. First, it split input data stream into multiple (= more than or equal to 1) chunks. Second, it kicks kernel of bitonic-sorting with key-comparison function generated on the fly. Third, it kicks dynamic background worker to run merge-sorting logic by CPU. It does not try to handle all the sorting stuff in GPU. The point we should not forget is, CPU/GPU is a way to sorting but not a purpose. Thanks, -- NEC OSS Promotion Center / PG-Strom Project KaiGai Kohei kai...@ak.jp.nec.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] GSSAPI, SSPI - include_realm default
On Tue, Dec 9, 2014 at 05:38:25PM -0500, Stephen Frost wrote: My comment that include_realm is supported back to 8.4 was because there is an expectation that a pg_hba.conf file can be used unchanged across several major releases. So when 9.5 comes out and people update their pg_hba.conf files for 9.5, those files will still work in old releases. But the time to do those updates is then, not now. The back-branches are being patched to discourage using the default because it's not a secure approach. New users start using PG all the time and so changing the existing documentation is worthwhile to ensure those new users understand. A note in the release notes for whichever minor release the change to the documentation shows up in would be a good way to make existing users aware of the change and hopefully encourage them to review their configuration. If we don't agree that the change should be made then we can discuss that, but everyone commenting so far has agreed on the change. Where are we on this? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Remove fsync ON/OFF as a visible option?
On 3/20/15 2:49 PM, Stephen Frost wrote: How about a big warning around fsync and make it more indepenent from the options around it? +1, and the same for full_page_writes and wal_sync_method. I think that's the best we can do at this point. As for why; Postgres already has a big reputation for being hard to use and hard to setup. Leaving footguns laying around that could easily be warned about is part of the reason for that reputation. Reality is that there are a lot of people using Postgres that are nowhere close to being DBAs and making it easy for them to munch their data on accident doesn't help anyone. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: Add launchd Support
On 3/20/15 6:11 PM, David E. Wheeler wrote: keyProgramArguments/key array string/usr/local/pgsql/bin/postgres/string string-D/string string/usr/local/pgsql/data/string /array Hrm, would /var/db/postgres be better? I'm not sure if the stuff Apple does with /private/ would cause problems though. (In any case, I think postgres is better than pgsql.) keyStandardOutPath/key string/usr/local/pgsql/data/launchd.log/string keyStandardErrorPath/key string/usr/local/pgsql/data/launchd.log/string Wouldn't /var/log be better? -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] proposal: doc: simplify examples of dynamic SQL
On Friday, March 20, 2015, Bruce Momjian br...@momjian.us wrote: On Fri, Mar 20, 2015 at 05:50:03PM -0700, David G. Johnston wrote: I'm not sure that this particular feature of the standard is something we should encourage. Its actually quite useful in this situation, and so maybe the novelty is just making me nervous, but the only reason I know of this behavior is because I've seen a number of posts in just the past couple of years when people accidentally used this feature and then were surprised when they didn't get an error. If this stays I would suggest that we take the opportunity to cross-reference back to where the syntax is defined so people aren't left scratching their heads as to why it works - or why if they remove the newline in their own attempt the code suddenly breaks. Yeah, I am kind on the fence about it, but it is a nice feature, particulary for PL/pgSQL programs. I added a mention of the string concatentation feature --- patch attached, and URL updated. The third option is to just embed a new line in the string itself. Execute Format(’... ...', tbl) USING val David J.
Re: [HACKERS] Change of pg_ctl's default shutdown method
On Fri, Mar 20, 2015 at 6:19 PM, Bruce Momjian br...@momjian.us wrote: I have not re-ordered the shutdown method options because I am trying to keep the list logical, from least to most severe, so smart is still listed first. It is odd that the default is the middle option, but I don't see any other idea on improving that. I don't really think it's a problem. -- 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] Remove fsync ON/OFF as a visible option?
On Fri, Mar 20, 2015 at 3:26 PM, Joshua D. Drake j...@commandprompt.com wrote: Fair enough. I am not going to name names but over the years (and just today) I ran into another user that corrupted their database by turning off fsync. My experience is different than yours: I haven't found this to be a particularly common mistake. I think I've had more people screw themselves by setting autovacuum_naptime=something_excessively_large or enable_seqscan=off. I'm very skeptical that removing stuff from postgresql.conf is going to help anything. If you go through your postgresql.conf and change settings at random, bad things will happen. But anyone who is doing that has a problem we can't fix. Thus far, the rule for postgresql.conf has been that pretty much everything goes in there, and that's a defensible position. Other reasonable options would be to ship the file with a small handful of settings in it and leave everything else, or to ship it completely empty of comments with only those settings that initdb sets and nothing else. I'd be OK a coherent policy change in this area, but just removing one or two setting seems like it will be confusing rather than helpful. -- 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] proposal: searching in array function - array_position
On 3/20/15 2:48 PM, Pavel Stehule wrote: 2015-03-20 18:47 GMT+01:00 Tom Lane t...@sss.pgh.pa.us mailto:t...@sss.pgh.pa.us: Alvaro Herrera alvhe...@2ndquadrant.com mailto:alvhe...@2ndquadrant.com writes: Pavel Stehule wrote: I am thinking, so it is ok - it returns a offset, not position. So you can't use it as a subscript? That sounds unfriendly. Almost every function using this will be subtly broken. I concur; perhaps offset was the design intention, but it's wrong. The result should be a subscript. do you have any idea about name for this function? array_position is ok? +1 on array_position. It's possible at some point we'll actually want array_offset that does what it claims. On another note, you mentioned elsewhere that it's not possible to return anything other than an integer. Why can't there be a variation of this function that returns an array of ndims-1 that is the slice where a value was found? -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] proposal: doc: simplify examples of dynamic SQL
On Fri, Mar 20, 2015 at 1:47 PM, Alvaro Herrera alvhe...@2ndquadrant.com
wrote:
Bruce Momjian wrote:
On Fri, Mar 20, 2015 at 08:54:24AM -0700, David G. Johnston wrote:
1. The layout of the format version is different, with respect to
newlines,
than the quote version; but while using newlines for the mandatory
concatenation is good having an excessively long format string isn't
desirable
and so maybe we should show something like:
EXECUTE format('SELECT count(*) FROM %I '
|| 'WHERE inserted_by = $1 AND insert = $2', tabname)
INTO c
USING checked_user, checked_date
I think that is very confusing --- the idea is that we don't need to use
|| with format, but you are then using || to span multiple lines.
That || seems fine, since it's only used for a line continuation; having
|| scattered all over the query string to interpolate each variable is
much more unreadable.
That said, the || there is unnecessary because per standard two literals
'lit1'
'lit2'
are concatenated if they are separated by a newline. So this
EXECUTE format('SELECT count(*) FROM %I '
'WHERE inserted_by = $1 AND insert = $2', tabname)
INTO c
USING checked_user, checked_date
should suffice.
I'm not sure that this particular feature of the standard is something we
should encourage.
Its actually quite useful in this situation, and so maybe the novelty is
just making me nervous, but the only reason I know of this behavior is
because I've seen a number of posts in just the past couple of years when
people accidentally used this feature and then were surprised when they
didn't get an error. If this stays I would suggest that we take the
opportunity to cross-reference back to where the syntax is defined so
people aren't left scratching their heads as to why it works - or why if
they remove the newline in their own attempt the code suddenly breaks.
David J.
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Fri, Mar 20, 2015 at 06:53:29PM -0700, David G. Johnston wrote: On Friday, March 20, 2015, Bruce Momjian br...@momjian.us wrote: On Fri, Mar 20, 2015 at 05:50:03PM -0700, David G. Johnston wrote: I'm not sure that this particular feature of the standard is something we should encourage. Its actually quite useful in this situation, and so maybe the novelty is just making me nervous, but the only reason I know of this behavior is because I've seen a number of posts in just the past couple of years when people accidentally used this feature and then were surprised when they didn't get an error. If this stays I would suggest that we take the opportunity to cross-reference back to where the syntax is defined so people aren't left scratching their heads as to why it works - or why if they remove the newline in their own attempt the code suddenly breaks. Yeah, I am kind on the fence about it, but it is a nice feature, particulary for PL/pgSQL programs. I added a mention of the string concatentation feature --- patch attached, and URL updated. The third option is to just embed a new line in the string itself. Execute Format(’... ...', tbl) USING val True, but that just looks odd. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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_recvlogical description
On Mon, Dec 8, 2014 at 11:53:48AM -0300, Euler Taveira wrote: Hi, The pg_recvlogical docs was rewritten but someone forgot to tweak the help description. It is a bit late in the 9.4 cycle but let be consistent. Patch applied --- thank you. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] configure can't detect proper pthread flags
On Fri, Mar 20, 2015 at 3:43 PM, Max Filippov jcmvb...@gmail.com wrote: Ok, one more attempt: maybe instead of checking that stderr is empty we could check that stderr has changed in the presence of the option that we test? The patch: http://www.postgresql.org/message-id/1426860321-13586-1-git-send-email-jcmvb...@gmail.com -- Thanks. -- Max -- 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: Add launchd Support
On Mar 20, 2015, at 4:21 PM, Jim Nasby jim.na...@bluetreble.com wrote: On 3/20/15 6:11 PM, David E. Wheeler wrote: keyProgramArguments/key array string/usr/local/pgsql/bin/postgres/string string-D/string string/usr/local/pgsql/data/string /array Hrm, would /var/db/postgres be better? I'm not sure if the stuff Apple does with /private/ would cause problems though. (In any case, I think postgres is better than pgsql.) keyStandardOutPath/key string/usr/local/pgsql/data/launchd.log/string keyStandardErrorPath/key string/usr/local/pgsql/data/launchd.log/string Wouldn't /var/log be better? /usr/local/pgsql has been the standard install location for the PostgreSQL core distribution for as long as I can remember, including on OS X. Our original OS X startup script refers to it. I figure it’s best to keep it consistent. Best, David smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] Abbreviated keys for Numeric
Attached is a revision of this patch, that I'm calling v2. What do you
think, Andrew?
I've cut the int32 representation/alternative !USE_FLOAT8_BYVAL
encoding scheme entirely, which basically means that 32-bit systems
don't get to have this optimization. 64-bit systems have been
commonplace now for about a decade. This year, new phones came out
with 64-bit architectures, so increasingly even people that work with
embedded systems don't care about 32-bit. I'm not suggesting that
legacy doesn't matter - far from it - but I care much less about
having the latest performance improvements on what are largely legacy
systems. Experience suggests that this is a good time of the cycle to
cut scope. The last commitfest has a way of clarifying what is
actually important.
It seems unwise to include what is actually a fairly distinct encoding
scheme, which the int32/ !USE_FLOAT8_BYVAL variant really was (the
same can't really be said for text abbreviation, since that can
basically work the same way on 32-bit systems, with very little extra
code). This isn't necessarily the right decision in general, but I
feel it's the right decision in the present climate of everyone
frantically closing things out, and feeling burnt out. I'm sorry that
I threw some of your work away, but since we both have other pressing
concerns, perhaps this is understandable. It may be revisited, or I
may lose the argument on this point, but going this way cuts the code
by about 30%, and makes me feel a lot better about the risk of
regressing marginal cases, since I know we always have 8 bytes to work
with. There might otherwise be a danger of regressing under tested
32-bit platforms, or indeed missing other bugs, and frankly I don't
have time to think about that right now.
Other than that, I've tried to keep things closer to the text opclass.
For example, the cost model now has a few debugging traces (disabled
by default). I have altered the ad-hoc cost model so that it no longer
concerns itself with NULL inputs, which seemed questionable (not least
since the abbreviation conversion function isn't actually called for
NULL inputs. Any attempt to track the full count within numeric code
therefore cannot work.). I also now allocate a buffer of scratch
memory separately from the main sortsupport object - doing one
allocation for all sortsupport state, bunched together as a buffer
seemed like a questionable micro-optimization. For similar reasons, I
avoid playing tricks in the VARATT_IS_SHORT() case -- my preferred
approach to avoiding palloc()/pfree() cycles is to simply re-use the
same buffer across calls to numeric_abbrev_convert(), and maybe risk
having to enlarge the relatively tiny buffer once or twice. In other
words, it works more or less the same way as it does with text
abbreviation.
It seemed unwise to silently disable abbreviation when someone
happened to build with DEC_DIGITS != 4. A static assertion now gives
these unusual cases the opportunity to make an informed decision about
either disabling abbreviation or not changing DEC_DIGITS in light of
the performance penalty, in a self-documenting way.
The encoding scheme is unchanged. I think that your conclusions on
those details were sound. Numeric abbreviation has a more compelling
cost/benefit ratio than even that of text. I easily managed to get the
same 6x - 7x improvement that you reported when sorting 10 million
random numeric rows.
Thanks
--
Peter Geoghegan
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index ff9bfcc..57532a9 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -29,13 +29,28 @@
#include access/hash.h
#include catalog/pg_type.h
#include funcapi.h
+#include lib/hyperloglog.h
#include libpq/pqformat.h
#include miscadmin.h
#include nodes/nodeFuncs.h
#include utils/array.h
#include utils/builtins.h
#include utils/int8.h
+#include utils/memutils.h
#include utils/numeric.h
+#include utils/sortsupport.h
+
+#ifndef INT64_MIN
+#define INT64_MIN (-INT64CONST(0x7FFF) - 1)
+#endif
+#ifndef INT64_MAX
+#define INT64_MAX INT64CONST(0x7FFF)
+#endif
+
+/* Abbreviation sortsupport encoding scheme supported? */
+#ifndef USE_FLOAT8_BYVAL
+#define DISABLE_NUMERIC_ABBREV
+#endif
/* --
* Uncomment the following to enable compilation of dump_numeric()
@@ -275,6 +290,19 @@ typedef struct
/* --
+ * sortsupport data
+ * --
+ */
+typedef struct
+{
+ boolestimating; /* Still estimating cardinality? */
+ void *buf; /* Scratch, for handling unaligned packed values */
+ Sizebuflen; /* current size of buf */
+ hyperLogLogState abbr_card; /* Abbreviated key cardinality state */
+} NumericSortSupport;
+
+
+/* --
* Some preinitialized constants
* --
*/
@@ -410,6 +438,14 @@ static double numeric_to_double_no_overflow(Numeric num);
static double numericvar_to_double_no_overflow(NumericVar *var);
static int cmp_numerics(Numeric
Re: [HACKERS] PATCH: pgbench - merging transaction logs
Hi, On 20.3.2015 13:43, Fabien COELHO wrote: Hello Robert, The fprintf we are talking about occurs at most once per pgbench transaction, possibly much less when aggregation is activated, and this transaction involves networks exchanges and possibly disk writes on the server. random() was occurring four times per transaction rather than once, but OTOH I think fprintf() is probably a much heavier-weight operation. Yes, sure. My point is that if there are many threads and tremendous TPS, the *detailed* per-transaction log (aka simple log) is probably a bad choice anyway, and the aggregated version is the way to go. I disagree with this reasoning. Can you provide numbers supporting it? I do agree that fprintf is not cheap, actually when profiling pgbench it's often the #1 item, but the impact on the measurements is actually quite small. For example with a small database (scale 10) and read-only 30-second runs (single client), I get this: no logging: 18672 18792 18667 18518 18613 18547 with logging: 18170 18093 18162 18273 18307 18234 So on average, that's 18634 vs. 18206, i.e. less than 2.5% difference. And with more expensive transactions (larger scale, writes, ...) the difference will be much smaller. It's true that this might produce large logs, especially when the runs are long, but that has nothing to do with fprintf. And can be easily fixed by either using a dedicated client machine, or only sample the transaction log. Introducing actual synchronization between the threads (by locking inside fprintf) is however a completely different thing. Note that even without mutex fprintf may be considered a heavy function which is going to slow down the transaction rate significantly. That could be tested as well. It is possible to reduce the lock time by preparing the string (which would mean introducing buffers) and just do a fputs under mutex. That would not reduce the print time anyway, and that may add malloc/free operations, though. I seriously doubt fprintf does the string formatting while holding lock on the file. So by doing this you only simulate what fprintf() does (assuming it's thread-safe on your platform) and gain nothing. The way to know if there's a real problem here is to test it, but I'd be pretty surprised if there isn't. Indeed, I think I can contrive a simple example where it is, basically a more or less empty or read only transaction (eg SELECT 1). That would be nice, because my quick testing suggests it's not the case. My opinion is that there is a tradeoff between code simplicity and later maintenance vs feature benefit. If threads are assumed and fprintf is used, the feature is much simpler to implement, and the maintenance is lighter. I think the if threads are assumed part makes this dead in water unless someone wants to spend time on getting rid of the thread emulation. Removing the code is quite simple, researching whether we can do that will be difficult IMHO - I have no idea which of the supported platorms require the emulation etc. And I envision endless discussions about this. The alternative implementation means reparsing the generated files over and over for merging their contents. I agree that the current implementation is not particularly pretty, and I plan to get rid of the copypaste parts etc. Also, I do not think that the detailed log provides much benefit with very fast transactions, where probably the aggregate is a much better choice anyway. If the user persists, she may generate a per-thread log and merge it later, in which case a merge script is needed, but I do not think that would be a bad thing. I disagree with this - I use transaction logs (either complete or sampled) quite often. I also explained why I think a separate merge script is awkward to use. -- Tomas Vondrahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Remove fsync ON/OFF as a visible option?
On 3/20/15 6:09 PM, Robert Haas wrote: On Fri, Mar 20, 2015 at 3:26 PM, Joshua D. Drake j...@commandprompt.com wrote: Fair enough. I am not going to name names but over the years (and just today) I ran into another user that corrupted their database by turning off fsync. My experience is different than yours: I haven't found this to be a particularly common mistake. I think I've had more people screw themselves by setting autovacuum_naptime=something_excessively_large or enable_seqscan=off. FWIW, I suspect a lot of that is due to CMD and EDB targeting different markets. I'm very skeptical that removing stuff from postgresql.conf is going to help anything. If you go through your postgresql.conf and change settings at random, bad things will happen. But anyone who is doing that has a problem we can't fix. I don't think people are making random changes; they're misunderstanding what the setting actually does. For dangerous settings (fsync, wal_sync_method and full_page_writes come to mind), a big WARNING in postgresql.conf would go a long way towards improving that. I do agree that simply removing the option isn't a great solution. Thus far, the rule for postgresql.conf has been that pretty much everything goes in there, and that's a defensible position. Other reasonable options would be to ship the file with a small handful of settings in it and leave everything else, or to ship it completely empty of comments with only those settings that initdb sets and nothing else. I'd be OK a coherent policy change in this area, but just removing one or two setting seems like it will be confusing rather than helpful. I agree with not being ad-hoc (and I think a documented postgresql.conf is much better than the other options). -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] GSoC 2015: Extra Jsonb functionality
On 3/19/15 9:07 AM, Thom Brown wrote:
jsonb_to_array
--
{a, 1, b, c, 2, d, 3, 4}
Is there a use-case for the example you've given above, where you take
JSON containing objects and arrays, and flatten them out into a
one-dimensional array?
There are a lot of things proposed here that are completely ignoring the
idea of nested elements, which I think is a big mistake.
Frankly, I think the whole proposal needs to be rethought with an eye
towards supporting and preserving nested elements instead of trying to
just flatten everything out. If a user wanted things flat they would
have just started with that in the first place.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.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] Incorrect comment in tablecmds.c
On Thu, Oct 23, 2014 at 06:29:07PM +0900, Etsuro Fujita wrote: I don't think that the lock level mentioned in the following comment in MergeAttributes() in tablecmds.c is right, since that that function has opened the relation with ShareUpdateExclusiveLock, not with AccessShareLock. Patch attached. 1749 /* 1750 * Close the parent rel, but keep our AccessShareLock on it until xact 1751 * commit. That will prevent someone else from deleting or ALTERing 1752 * the parent before the child is committed. 1753 */ 1754 heap_close(relation, NoLock); Agreed, patch applied. Thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Superuser connect during smart shutdown
Robert Haas robertmh...@gmail.com wrote: On Thu, Mar 19, 2015 at 10:42 PM, Bruce Momjian br...@momjian.us wrote: On Mon, Oct 20, 2014 at 03:10:50PM -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Oct 19, 2014 at 12:27 PM, Tom Lane t...@sss.pgh.pa.us wrote: I've certainly objected to it in the past, but I don't believe I was the only one objecting. What's your feeling now? I'm prepared to yield on the point. OK, are we up for changing the default pg_ctl shutdown method for 9.5, (smart to fast), or should we wait for 9.6? I'm up for it. I think it's long overdue. +1 -- Kevin Grittner EDB: 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] configure can't detect proper pthread flags
Hi, On 2015-03-20 03:14:48 +0300, Max Filippov wrote: and the toolchain emits the following warning at linking step: libcrypto.so: warning: gethostbyname is obsolescent, use getnameinfo() instead. FWIW, I think emitting such errors at link time is utterly pointless and rather annoying. I can see a point of emitting them them when compiling code that uses deprecated functions. But we quite obviously can't do much about openssl using gethostbyname. Greetings, Andres Freund -- Andres Freund 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] assessing parallel-safety
Thom Brown wrote: On 18 March 2015 at 16:01, Robert Haas robertmh...@gmail.com wrote: On Tue, Mar 17, 2015 at 9:48 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Mar 17, 2015 at 2:26 AM, Noah Misch n...@leadboat.com wrote: Neither that rule, nor its variant downthread, would hurt operator authors too much. To make the planner categorically parallel-safe, though, means limiting evaluate_function() to parallel-safe functions. That would dramatically slow selected queries. It's enough for the PL scenario if planning a parallel-safe query is itself parallel-safe. If the planner is parallel-unsafe when planning a parallel-unsafe query, what would suffer? Good point. So I guess the rule can be that planning a parallel-safe query should be parallel-safe. From there, it follows that estimators for a parallel-safe operator must also be parallel-safe. Which seems fine. More work is needed here, but for now, here is a rebased patch, per Amit's request. This no longer applies due to changes in commit 13dbc7a824b3f905904cab51840d37f31a07a9ef. You should be able to drop the pg_proc.h changes and run the supplied perl program. (I'm not sure that sending the patched pg_proc.h together with this patch is all that useful, really.) -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] configure can't detect proper pthread flags
By the way, acx-pthread.m4 has an outdated link to upstream acx_pthread.m4. The correct link is http://git.savannah.gnu.org/gitweb/?p=autoconf-archive.git;a=history;f=m4/ax_pthread.m4 -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] GSoC 2015: Extra Jsonb functionality
Would this also be the case for this function?...
# jsonb_add_to_path('{b: {c: [d, f]}}'::jsonb, {b, c}::text[],
'{g:4}'::jsonb);
jsonb_add_to_path
{b: {c: [d, f, {g: 4}]}}
Yes, sure (the similar logic already implemented for the jsonb_concat).
On 20 March 2015 at 18:39, Thom Brown t...@linux.com wrote:
On 20 March 2015 at 11:21, Dmitry Dolgov 9erthali...@gmail.com wrote:
Perhaph it's my misunderstanding, but this would seem to be more of an
intersection operation on keys rather than a delete.
Hm...why? We remove all elements, which are contains in the first and
second
jsonb (f: [4, 5] in this case) from the first one.
On further thought, yes, I agree.
Could there be a corresponding jsonb_except function which does the
opposite (i.e. returns everything on the left side except where it
matches
with the right)?
and if I understand your question correctly, this is exactly what the
jsonb_delete_jsonb will do, isn't it?.
Ah, yes, that's true.
Is there a use-case for the example you've given above, where you take
JSON containing objects and arrays, and flatten them out into a
one-dimensional array?
Hm...actually I don't know about such use-cases. This function is analog
of
the hstore_to_array (and the similar function hstore_to_matrix), which is
used sometimes, judging by github. So I thought this function should be
implemented (after this question I'm not so sure).
Yeah, hstore was just key=value, so flattening it out resulted in a
simple {key,value,key,value} array. I don't think that's useful with
json.
What should happen if g or {g} were used instead?
Did you mean {g: key}? Hmm...but in any case, I suppose this new
object
should be appended to the array as a regular element.
=# jsonb_add_to_path('{b: {c: [d, f]}}'::jsonb, {b,
c}::text[],
'g'::jsonb);
jsonb_add_to_path
---
{b: {c: [d, f, g]}}
Would this also be the case for this function?...
# jsonb_add_to_path('{b: {c: [d, f]}}'::jsonb, {b, c}::text[],
'{g:4}'::jsonb);
jsonb_add_to_path
{b: {c: [d, f, {g: 4}]}}
--
Thom
Re: [HACKERS] configure can't detect proper pthread flags
On 2015-03-20 10:23:51 -0300, Alvaro Herrera wrote: Andres Freund wrote: FWIW, I think emitting such errors at link time is utterly pointless and rather annoying. I can see a point of emitting them them when compiling code that uses deprecated functions. But we quite obviously can't do much about openssl using gethostbyname. We don't seem have much leverage with the guys producing the linker. If we do, then surely our best bet is to get them to be quiet, or at least provide an --yes-i-know-your-crap-is-noisy-please-shut-it-up option. It's not the linker, it's uclibc that adds the warning. http://git.uclibc.org/uClibc/commit/?id=fdc6f045fa8b71a91a0c55b6390f8d0741e9f374 Greetings, Andres Freund -- Andres Freund 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] configure can't detect proper pthread flags
Andres Freund wrote: On 2015-03-20 10:23:51 -0300, Alvaro Herrera wrote: Andres Freund wrote: FWIW, I think emitting such errors at link time is utterly pointless and rather annoying. I can see a point of emitting them them when compiling code that uses deprecated functions. But we quite obviously can't do much about openssl using gethostbyname. We don't seem have much leverage with the guys producing the linker. If we do, then surely our best bet is to get them to be quiet, or at least provide an --yes-i-know-your-crap-is-noisy-please-shut-it-up option. It's not the linker, it's uclibc that adds the warning. http://git.uclibc.org/uClibc/commit/?id=fdc6f045fa8b71a91a0c55b6390f8d0741e9f374 Wow, that stuff has been there since 2009. So there's no way to shut it up at all, is there. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] snapshot too large error when initializing logical replication (9.4)
On Mon, Nov 17, 2014 at 03:52:38PM +0100, Andres Freund wrote: On 2014-11-17 11:51:54 -0300, Alvaro Herrera wrote: Andres Freund wrote: Hi, On 2014-10-25 18:09:36 -0400, Steve Singer wrote: I sometimes get the error snapshot too large from my logical replication walsender process when in response to a CREATE_REPLICATION_SLOT. Yes. That's possible if 'too much' was going on until a consistent point was reached. I think we can just use a much larger size for the array if necessary. I've attached patch for this. Could you try whether that helps? I don't have a testcase handy that reproduces the problem. You haven't pushed this, have you? No, but it's on my todo list. Uh, where are we on this? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] assessing parallel-safety
On 20 March 2015 at 13:16, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Thom Brown wrote: On 18 March 2015 at 16:01, Robert Haas robertmh...@gmail.com wrote: On Tue, Mar 17, 2015 at 9:48 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Mar 17, 2015 at 2:26 AM, Noah Misch n...@leadboat.com wrote: Neither that rule, nor its variant downthread, would hurt operator authors too much. To make the planner categorically parallel-safe, though, means limiting evaluate_function() to parallel-safe functions. That would dramatically slow selected queries. It's enough for the PL scenario if planning a parallel-safe query is itself parallel-safe. If the planner is parallel-unsafe when planning a parallel-unsafe query, what would suffer? Good point. So I guess the rule can be that planning a parallel-safe query should be parallel-safe. From there, it follows that estimators for a parallel-safe operator must also be parallel-safe. Which seems fine. More work is needed here, but for now, here is a rebased patch, per Amit's request. This no longer applies due to changes in commit 13dbc7a824b3f905904cab51840d37f31a07a9ef. You should be able to drop the pg_proc.h changes and run the supplied perl program. (I'm not sure that sending the patched pg_proc.h together with this patch is all that useful, really.) Thanks. All patches applied and building okay. -- 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] [PATCH] Compare linker/compiler output with their default output
linker and compiler may have noisy output by default, making
acx_pthread.m4 believe that pthread options that it tries are ignored
as invalid.
Record default compiler and linker output and see if adding pthread
option changes that, instead of assuming that linker and compiler are
silent by default.
Signed-off-by: Max Filippov jcmvb...@gmail.com
---
config/acx_pthread.m4 | 18 +-
1 file changed, 17 insertions(+), 1 deletion(-)
diff --git a/config/acx_pthread.m4 b/config/acx_pthread.m4
index 581164b..028fdb1 100644
--- a/config/acx_pthread.m4
+++ b/config/acx_pthread.m4
@@ -79,6 +79,22 @@ case ${host_cpu}-${host_os} in
esac
if test x$acx_pthread_ok = xno; then
+
+cat conftest.$ac_ext _ACEOF
+int
+main (int argc, char **argv)
+{
+ (void) argc;
+ (void) argv;
+ return 0;
+}
+_ACEOF
+rm -f conftest.$ac_objext conftest$ac_exeext
+
+# Record the default linker and compiler output
+ld_default_output=`(eval $ac_link 21 15)`
+cc_default_output=`(eval $ac_compile 21 15)`
+
for flag in $acx_pthread_flags; do
tryPTHREAD_CFLAGS=
@@ -142,7 +158,7 @@ main (int argc, char **argv)
_ACEOF
rm -f conftest.$ac_objext conftest$ac_exeext
# Check both linking and compiling, because they might tolerate
different options.
-if test `(eval $ac_link 21 15)` = test `(eval
$ac_compile 21 15)` = ; then
+if test `(eval $ac_link 21 15)` = $ld_default_output
test `(eval $ac_compile 21 15)` = $cc_default_output; then
# The original macro breaks out of the loop at this point,
# but we continue trying flags because Linux needs -lpthread
# too to build libpq successfully. The test above only
--
1.8.1.4
--
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] printing table in asciidoc with psql
On Wed, Dec 3, 2014 at 03:52:30PM +0900, Michael Paquier wrote:
I see a trailing spaces, but I don't see a described effect. Please, can you
send some more specific test case?
This formatting problem is trivial to reproduce:
=# create table foo ();
CREATE TABLE
Time: 9.826 ms
=# \d
.List of relations
[options=header,cols=l,l,
l,l,frame=none]
|
^l| Schema ^l| Name ^l| Type ^l| Owner
| public | foo | table | ioltas
|
(1 row)
I just tested this patch, and yes I agree with Alvaro that it would be
good to minimize the extra spaces around the table separators '|'. Now
we need to be careful as well, and I think that we should just remove
the separators on the right of the separators as cells values
controlling for example spans would result in incorrect output, stuff
like that:
5 2.2+^.^
9 2+
Also, something a bit surprising is that this format produces always
one newline for each command, for example in the case of a DDL:
=# create table foo ();
CREATE TABLE
I think that this extra space should be removed as well, no?
This patch has been marked as Waiting on Author for a couple of
weeks, and the problems mentioned before have not been completely
addressed, hence marking this patch as returned with feedback. It
would be nice to see progress for the next CF.
I was able to fix all the reported problems with the attached patch.
I used this for testing the output:
https://asciidoclive.com/
Is it OK now?
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
new file mode 100644
index a637001..82a91ec
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*** lo_import 152801
*** 2092,2099
literalaligned/literal, literalwrapped/literal,
literalhtml/literal,
literallatex/literal (uses literaltabular/literal),
! literallatex-longtable/literal, or
! literaltroff-ms/literal.
Unique abbreviations are allowed. (That would mean one letter
is enough.)
/para
--- 2092,2099
literalaligned/literal, literalwrapped/literal,
literalhtml/literal,
literallatex/literal (uses literaltabular/literal),
! literallatex-longtable/literal,
! literaltroff-ms/literal, or literalasciidoc/literal.
Unique abbreviations are allowed. (That would mean one letter
is enough.)
/para
*** lo_import 152801
*** 2120,2126
para
The literalhtml/, literallatex/,
! literallatex-longtable/literal, and literaltroff-ms/
formats put out tables that are intended to
be included in documents using the respective mark-up
language. They are not complete documents! This might not be
--- 2120,2127
para
The literalhtml/, literallatex/,
! literallatex-longtable/literal, literaltroff-ms/,
! and literalasciidoc/
formats put out tables that are intended to
be included in documents using the respective mark-up
language. They are not complete documents! This might not be
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
new file mode 100644
index 7c9f28d..a96f0ef
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*** _align2string(enum printFormat in)
*** 2257,2262
--- 2257,2265
case PRINT_TROFF_MS:
return troff-ms;
break;
+ case PRINT_ASCIIDOC:
+ return asciidoc;
+ break;
}
return unknown;
}
*** do_pset(const char *param, const char *v
*** 2330,2338
popt-topt.format = PRINT_LATEX_LONGTABLE;
else if (pg_strncasecmp(troff-ms, value, vallen) == 0)
popt-topt.format = PRINT_TROFF_MS;
else
{
! psql_error(\\pset: allowed formats are unaligned, aligned, wrapped, html, latex, troff-ms\n);
return false;
}
--- 2333,2343
popt-topt.format = PRINT_LATEX_LONGTABLE;
else if (pg_strncasecmp(troff-ms, value, vallen) == 0)
popt-topt.format = PRINT_TROFF_MS;
+ else if (pg_strncasecmp(asciidoc, value, vallen) == 0)
+ popt-topt.format = PRINT_ASCIIDOC;
else
{
! psql_error(\\pset: allowed formats are unaligned, aligned, wrapped, html, latex, troff-ms, asciidoc\n);
return false;
}
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
new file mode 100644
index ac0dc27..93a517e
*** a/src/bin/psql/help.c
--- b/src/bin/psql/help.c
*** helpVariables(unsigned short int pager)
*** 351,357
fprintf(output, _( expanded (or x)toggle expanded output\n));
fprintf(output, _( fieldsep field separator for unaligned output (default '|')\n));
Re: [HACKERS] configure can't detect proper pthread flags
Andres Freund wrote: Hi, On 2015-03-20 03:14:48 +0300, Max Filippov wrote: and the toolchain emits the following warning at linking step: libcrypto.so: warning: gethostbyname is obsolescent, use getnameinfo() instead. FWIW, I think emitting such errors at link time is utterly pointless and rather annoying. I can see a point of emitting them them when compiling code that uses deprecated functions. But we quite obviously can't do much about openssl using gethostbyname. We don't seem have much leverage with the guys producing the linker. If we do, then surely our best bet is to get them to be quiet, or at least provide an --yes-i-know-your-crap-is-noisy-please-shut-it-up option. If we don't have leverage, and we really care enough about that platform to want to work around this problem, it seems that the latest suggestion of comparing the output of the linker with and without the option we're testing (rather than just assuming that the output without the option must surely be empty) is the safest bet ... It seems bad (fragile hack), but let's see a patch and then we can judge. Another option is to say uclibc is broken beyond belief and consider it unsupported. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] POLA violation with \c service=
On Fri, Feb 27, 2015 at 08:42:29AM -0800, David Fetter wrote: On Mon, Feb 23, 2015 at 05:56:12PM -0300, Alvaro Herrera wrote: David Fetter wrote: My thinking behind this was that the patch is a bug fix and intended to be back-patched, so I wanted to mess with as little infrastructure as possible. A new version of libpq seems like a very big ask for such a case. You'll recall that the original problem was that \c service=foo only worked accidentally for some pretty narrow use cases and broke without much of a clue for the rest. It turned out that the general problem was that options given to psql on the command line were not even remotely equivalent to \c, even though they were documented to be. So, in view of these arguments and those put forward by Pavel downthread, I think the attached is an acceptable patch for the master branch. It doesn't apply to back branches though; 9.4 and 9.3 have a conflict in tab-complete.c, 9.2 has additional conflicts in command.c, and 9.1 and 9.0 are problematic all over because they don't have src/common. Could you please submit patches adapted for each group of branches? Please find patches attached for each live branch. Is this getting into the upcoming bug fix releases? Does it need rework to do so? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: pgbench - merging transaction logs
Hello Robert, The fprintf we are talking about occurs at most once per pgbench transaction, possibly much less when aggregation is activated, and this transaction involves networks exchanges and possibly disk writes on the server. random() was occurring four times per transaction rather than once, but OTOH I think fprintf() is probably a much heavier-weight operation. Yes, sure. My point is that if there are many threads and tremendous TPS, the *detailed* per-transaction log (aka simple log) is probably a bad choice anyway, and the aggregated version is the way to go. Note that even without mutex fprintf may be considered a heavy function which is going to slow down the transaction rate significantly. That could be tested as well. It is possible to reduce the lock time by preparing the string (which would mean introducing buffers) and just do a fputs under mutex. That would not reduce the print time anyway, and that may add malloc/free operations, though. The way to know if there's a real problem here is to test it, but I'd be pretty surprised if there isn't. Indeed, I think I can contrive a simple example where it is, basically a more or less empty or read only transaction (eg SELECT 1). My opinion is that there is a tradeoff between code simplicity and later maintenance vs feature benefit. If threads are assumed and fprintf is used, the feature is much simpler to implement, and the maintenance is lighter. The alternative implementation means reparsing the generated files over and over for merging their contents. Also, I do not think that the detailed log provides much benefit with very fast transactions, where probably the aggregate is a much better choice anyway. If the user persists, she may generate a per-thread log and merge it later, in which case a merge script is needed, but I do not think that would be a bad thing. Obviously, all that is only my opinion and is quite debatable. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] configure can't detect proper pthread flags
On Fri, Mar 20, 2015 at 3:05 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 20, 2015 at 7:01 AM, Max Filippov jcmvb...@gmail.com wrote: On Fri, Mar 20, 2015 at 6:08 AM, Tom Lane t...@sss.pgh.pa.us wrote: We don't want every link step producing a useless warning. Ideally, make -s would print nothing whatsoever; to the extent that tools produce unsuppressable routine chatter, that's evil because it makes it harder to notice actually-useful warnings. Then maybe stderr tests should grep output for a specific option, the one we're currently testing, not just any noise? That sounds awfully fragile to me. It can't really be safe to assume we know precisely what the warning messages will look like. Yes, I agree, not very good. Ok, one more attempt: maybe instead of checking that stderr is empty we could check that stderr has changed in the presence of the option that we test? -- Thanks. -- Max -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [BUGS] BUG #11805: Missing SetServiceStatus call during service shutdown in pg_ctl (Windows only)
On Tue, Oct 28, 2014 at 07:02:41AM +, krystian.bi...@gmail.com wrote: The following bug has been logged on the website: Bug reference: 11805 Logged by: Krystian Bigaj Email address: krystian.bi...@gmail.com PostgreSQL version: 9.3.5 Operating system: Windows 7 Pro x64 Description: pg_ctl on Windows during service start/shutdown should notify service manager about it's status by increment dwCheckPoint and call to SetServiceStatus/pgwin32_SetServiceStatus. However during shutdown there is a missing call to SetServiceStatus. See src\bin\pg_ctl\pg_ctl.c: [ thread moved to hackers ] Can a Windows person look into this issue? http://www.postgresql.org/message-id/20141028070241.2593.58...@wrigleys.postgresql.org The thread includes a patch. I need a second person to verify its validity. Thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] assessing parallel-safety
On 20 March 2015 at 13:55, Thom Brown t...@linux.com wrote:
On 20 March 2015 at 13:16, Alvaro Herrera alvhe...@2ndquadrant.com wrote:
Thom Brown wrote:
On 18 March 2015 at 16:01, Robert Haas robertmh...@gmail.com wrote:
On Tue, Mar 17, 2015 at 9:48 AM, Robert Haas robertmh...@gmail.com
wrote:
On Tue, Mar 17, 2015 at 2:26 AM, Noah Misch n...@leadboat.com wrote:
Neither that rule, nor its variant downthread, would hurt operator
authors too
much. To make the planner categorically parallel-safe, though, means
limiting
evaluate_function() to parallel-safe functions. That would
dramatically slow
selected queries. It's enough for the PL scenario if planning a
parallel-safe
query is itself parallel-safe. If the planner is parallel-unsafe when
planning a parallel-unsafe query, what would suffer?
Good point. So I guess the rule can be that planning a parallel-safe
query should be parallel-safe. From there, it follows that estimators
for a parallel-safe operator must also be parallel-safe. Which seems
fine.
More work is needed here, but for now, here is a rebased patch, per
Amit's request.
This no longer applies due to changes in commit
13dbc7a824b3f905904cab51840d37f31a07a9ef.
You should be able to drop the pg_proc.h changes and run the supplied
perl program. (I'm not sure that sending the patched pg_proc.h together
with this patch is all that useful, really.)
Thanks. All patches applied and building okay.
Okay, breakage experienced, but not sure which thread this belongs on.
createdb pgbench
pgbench -i -s 200 pgbench
CREATE TABLE pgbench_accounts_1 (CHECK (bid = 1)) INHERITS (pgbench_accounts);
...
CREATE TABLE pgbench_accounts_200 (CHECK (bid = 200)) INHERITS
(pgbench_accounts);
WITH del AS (DELETE FROM pgbench_accounts WHERE bid = 1 RETURNING *)
INSERT INTO pgbench_accounts_1 SELECT * FROM del;
...
WITH del AS (DELETE FROM pgbench_accounts WHERE bid = 200 RETURNING *)
INSERT INTO pgbench_accounts_200 SELECT * FROM del;
VACUUM ANALYSE;
# SELECT name, setting FROM pg_settings WHERE name IN
('parallel_seqscan_degree','max_worker_processes','seq_page_cost');
name | setting
-+-
max_worker_processes| 20
parallel_seqscan_degree | 8
seq_page_cost | 1000
(3 rows)
# EXPLAIN SELECT DISTINCT bid FROM pgbench_accounts;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Log file:
2015-03-20 14:19:30 GMT [4285]: [10-1]
user=thom,db=pgbench,client=[local] DEBUG: StartTransactionCommand
2015-03-20 14:19:30 GMT [4285]: [11-1]
user=thom,db=pgbench,client=[local] DEBUG: StartTransaction
2015-03-20 14:19:30 GMT [4285]: [12-1]
user=thom,db=pgbench,client=[local] DEBUG: name: unnamed; blockState:
DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestl
vl: 1, children:
2015-03-20 14:19:30 GMT [4285]: [13-1]
user=thom,db=pgbench,client=[local] DEBUG: ProcessUtility
2015-03-20 14:19:30 GMT [4285]: [14-1]
user=thom,db=pgbench,client=[local] DEBUG: rehashing catalog cache id
45 for pg_class; 257 tups, 128 buckets
2015-03-20 14:19:30 GMT [4285]: [15-1]
user=thom,db=pgbench,client=[local] DEBUG: rehashing catalog cache id
47 for pg_statistic; 257 tups, 128 buckets
2015-03-20 14:19:30 GMT [4285]: [16-1]
user=thom,db=pgbench,client=[local] DEBUG: rehashing catalog cache id
47 for pg_statistic; 513 tups, 256 buckets
2015-03-20 14:19:30 GMT [4285]: [17-1]
user=thom,db=pgbench,client=[local] DEBUG: rehashing catalog cache id
47 for pg_statistic; 1025 tups, 512 buckets
2015-03-20 14:19:31 GMT [4273]: [76-1] user=,db=,client= DEBUG:
forked new backend, pid=4286 socket=10
2015-03-20 14:19:31 GMT [4286]: [1-1]
user=thom,db=pgbench,client=[local] DEBUG: postgres child[4286]:
starting with (
2015-03-20 14:19:31 GMT [4273]: [77-1] user=,db=,client= DEBUG:
reaping dead processes
2015-03-20 14:19:31 GMT [4273]: [78-1] user=,db=,client= DEBUG:
server process (PID 4285) was terminated by signal 11: Segmentation
fault
2015-03-20 14:19:31 GMT [4273]: [79-1] user=,db=,client= DETAIL:
Failed process was running: EXPLAIN SELECT DISTINCT bid FROM
pgbench_accounts;
2015-03-20 14:19:31 GMT [4273]: [80-1] user=,db=,client= LOG: server
process (PID 4285) was terminated by signal 11: Segmentation fault
2015-03-20 14:19:31 GMT [4273]: [81-1] user=,db=,client= DETAIL:
Failed process was running: EXPLAIN SELECT DISTINCT bid FROM
pgbench_accounts;
2015-03-20 14:19:31 GMT [4273]: [82-1] user=,db=,client= LOG:
terminating any other active server processes
2015-03-20 14:19:31 GMT [4273]: [83-1] user=,db=,client= DEBUG:
sending SIGQUIT to process 4286
2015-03-20 14:19:31 GMT [4273]: [84-1] user=,db=,client= DEBUG:
sending SIGQUIT to process 4279
2015-03-20 14:19:31 GMT [4286]: [2-1]
user=thom,db=pgbench,client=[local] DEBUG:postgres
2015-03-20 14:19:31 GMT
Re: [HACKERS] [PATCH] two-arg current_setting() with fallback
On Mar 19, 2015, at 6:27 PM, Tom Lane t...@sss.pgh.pa.us wrote:
David Christensen da...@endpoint.com writes:
The two-arg form of the current_setting() function will allow a
fallback value to be returned instead of throwing an error when an
unknown GUC is provided. This would come in most useful when using
custom GUCs; e.g.:
-- errors out if the 'foo.bar' setting is unset
SELECT current_setting('foo.bar');
-- returns current setting of foo.bar, or 'default' if not set
SELECT current_setting('foo.bar', 'default')
This would save you having to wrap the use of the function in an
exception block just to catch and utilize a default setting value
within a function.
That seems kind of ugly, not least because it assumes that you know
a value that couldn't be mistaken for a valid value of the GUC.
(I realize that you are thinking of cases where you want to pretend
that the GUC has some valid value, but that's not the only use case.)
ISTM, since we don't allow GUCs to have null values, it'd be better to
define the variant function as returning NULL for no-such-GUC. Then the
behavior you want could be achieved by wrapping that in a COALESCE, but
the behavior of probing whether the GUC is set at all would be achieved
with an IS NULL test.
regards, tom lane
In that case, the other thought I had here is that we change the function
signature of current_setting() to be a two-arg form where the second argument
is a boolean throw_error, with a default argument of true to preserve
existing semantics, and returning NULL if that argument is false. However, I'm
not sure if there are some issues with changing the signature of an existing
function (e.g., with pg_upgrade, etc.).
My *impression* is that since pg_upgrade rebuilds the system tables for a new
install it shouldn't be an issue, but not sure if having the same pg_proc OID
with different values or an alternate pg_proc OID would cause issues down the
line; anyone know if this is a dead-end?
Regards,
David
--
David Christensen
End Point Corporation
da...@endpoint.com
785-727-1171
--
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] assessing parallel-safety
On Fri, Mar 20, 2015 at 10:24 AM, Thom Brown t...@linux.com wrote: 2015-03-20 14:19:31 GMT [4273]: [78-1] user=,db=,client= DEBUG: server process (PID 4285) was terminated by signal 11: Segmentation fault Any chance you can get us a stack backtrace of this crash? -- 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] assessing parallel-safety
On Fri, Mar 20, 2015 at 11:08 AM, Thom Brown t...@linux.com wrote: On 20 March 2015 at 15:02, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 20, 2015 at 10:24 AM, Thom Brown t...@linux.com wrote: 2015-03-20 14:19:31 GMT [4273]: [78-1] user=,db=,client= DEBUG: server process (PID 4285) was terminated by signal 11: Segmentation fault Any chance you can get us a stack backtrace of this crash? (gdb) cont Continuing. Program received signal SIGSEGV, Segmentation fault. 0x00770843 in pfree () (gdb) bt #0 0x00770843 in pfree () #1 0x005a382f in ExecEndFunnel () #2 0x0059fe75 in ExecEndAppend () #3 0x005920bd in standard_ExecutorEnd () #4 0x0055004b in ExplainOnePlan () #5 0x0055025d in ExplainOneQuery () #6 0x0055064d in ExplainQuery () #7 0x00680db1 in standard_ProcessUtility () #8 0x0067e1c1 in PortalRunUtility () #9 0x0067ef1d in FillPortalStore () #10 0x0067f8eb in PortalRun () #11 0x0067d628 in PostgresMain () #12 0x00462c5e in ServerLoop () #13 0x0062e363 in PostmasterMain () #14 0x004636ad in main () OK, thanks. That looks like it's probably the fault of parallel seq scan patch rather than this one. It would help if you could build with debug symbols so that we can see line numbers and arguments. -- 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] assessing parallel-safety
On 20 March 2015 at 15:02, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 20, 2015 at 10:24 AM, Thom Brown t...@linux.com wrote: 2015-03-20 14:19:31 GMT [4273]: [78-1] user=,db=,client= DEBUG: server process (PID 4285) was terminated by signal 11: Segmentation fault Any chance you can get us a stack backtrace of this crash? (gdb) cont Continuing. Program received signal SIGSEGV, Segmentation fault. 0x00770843 in pfree () (gdb) bt #0 0x00770843 in pfree () #1 0x005a382f in ExecEndFunnel () #2 0x0059fe75 in ExecEndAppend () #3 0x005920bd in standard_ExecutorEnd () #4 0x0055004b in ExplainOnePlan () #5 0x0055025d in ExplainOneQuery () #6 0x0055064d in ExplainQuery () #7 0x00680db1 in standard_ProcessUtility () #8 0x0067e1c1 in PortalRunUtility () #9 0x0067ef1d in FillPortalStore () #10 0x0067f8eb in PortalRun () #11 0x0067d628 in PostgresMain () #12 0x00462c5e in ServerLoop () #13 0x0062e363 in PostmasterMain () #14 0x004636ad in main () -- Thom -- 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] proposal: doc: simplify examples of dynamic SQL
On Thu, Mar 19, 2015 at 6:49 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Mar 19, 2015 at 06:05:52PM -0700, David G. Johnston wrote: On Thu, Mar 19, 2015 at 5:18 PM, Bruce Momjian br...@momjian.us wrote: There are other places later in the docs where we explain all the quote* functions and show examples of query construction using string concatenation, but I am not sure how we can remove those. Can you be more specific? Yes. You can see the output of the attached patch here: http://momjian.us/tmp/pgsql/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Notice: EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_nullable(newvalue) || ' WHERE key = ' || quote_nullable(keyvalue); and EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = $$' || newvalue || '$$ WHERE key = ' || quote_literal(keyvalue); It is making a point about nulls and stuff. There are later queries that use format(). I thought maybe you meant those but your specific mention of There are other places later in the docs confused me since you made changes before and after that specific section. Those examples need to be somewhere and it doesn't seem like a undesireable enough setup that major reconstructive surgery is warranted to try and move them elsewhere. On a related note: If you are dealing with values that might be null, you should usually use quote_nullable in place of quote_literal. Its unclear why, aside from semantic uncleanliness, someone would use quote_literal given its identical behavior for non-null values and inferior behavior which passed NULL. The function table for the two could maybe be more clear since quote_nullable(NULL) returns a string representation of NULL without any quotes while quote_literal(NULL) returns an actual NULL that ultimately poisons the string concatenation that these functions are used with. reads some more The differences between the actual null and the string NULL are strictly in capitalization - which is not consistent even within the table. concat_ws states NULL arguments are ignored and so represents actual null with all-caps which is string NULL in the quote_* descriptions. Having read 40.5.4 and example 40-1 the difference is clear and obvious so maybe what is in the table is sufficient for this topic. I would suggest adding a comment to quote_ident and quote_nullable that corresponding format codes are %I and %L. Obviously there is no quote_ function to correspond with %S. There is likewise nor corresponding format code for quote_literal since quote_nullable is superior in every way (that I can tell at least). OK, I have added that tip --- good suggestion. Patch attached. I was actually referring to chapter 9 http://www.postgresql.org/docs/9.4/interactive/functions-string.html The table definitions of the quote_* function should have a comment about their equivalency to format %I and %L Also, in 9.4.1 (format - type) would be the most obvious place for the equivalency of the format %I and %L to quote_* IMO too much is trying to be done within example 40-1 (for instance, the quote_literal/nullable explanation should be moved elsewhere); and while these are mainly useful with dynamic SQL it still behooves us to put the definition stuff in the structural area and then use the example for comprehension and clarification regarding best practices (i.e., format for %I but USING for literals - though I know some would say we should necessarily express those kinds of opinions in the docs...). That said, it is not as bad as I may seem to be making it out to be and aside from wanting to put and obvious reference to format directly next to the quote_* functions is more style that content. The desire for the linkage is strong though because we want someone who naturally would use string concatenation and the quote_* functions to be made aware of, and convinced to use (they will thank us for this), the format() function instead. David J.
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
Looking at
http://momjian.us/tmp/pgsql/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
The paired example at the top of the patch has two things worth considering.
1. The layout of the format version is different, with respect to newlines,
than the quote version; but while using newlines for the mandatory
concatenation is good having an excessively long format string isn't
desirable and so maybe we should show something like:
EXECUTE format('SELECT count(*) FROM %I '
|| 'WHERE inserted_by = $1 AND insert = $2', tabname)
INTO c
USING checked_user, checked_date
2. There is a recent posting pointing out the fact that the first query did
not use quote_ident(tabname) but instead did tabname::regclass, which calls
quote_ident internally. While there is a choice is that situation with
format you must pass in an unquoted label and so must not use
tabname::regclass. I think the first example should be written to use
quote_ident(tabname).
As regards the ::regclass behavior I would need see it current treatment
and recommended usage in the docs in order to form an opinion on how it
interacts with quote_literal and %I.
David J.
Re: [HACKERS] Remove fsync ON/OFF as a visible option?
On Sat, Mar 21, 2015 at 2:47 AM, Peter Geoghegan p...@heroku.com wrote: On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake j...@commandprompt.com wrote: There are just as many people that are running with scissors that are now running (or attempting to run) our elephant in production. Does it make sense to remove fsync (and possibly full_page_writes) from such a visible place as postgresql.conf? -1 Anyone turning off fsync without even for a moment considering the consequences has only themselves to blame. I can't imagine why you'd want to remove full_page_writes or make it less visible either, since in principle it ought to be perfectly fine to turn it off in production once its verified as safe. -1 for its removal as well. It is still useful for developers to emulate CPU-bounded loads... -- Michael -- 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] double vacuum in initdb
On Thu, Dec 11, 2014 at 08:35:43PM -0500, Peter Eisentraut wrote: On 12/11/14 11:44 AM, Kevin Grittner wrote: We want to finish with VACUUM FREEZE without the FULL, unless we don't care about missing visibility maps and free space maps. I have create the attached initdb patch to update this. Why would we care, and if we do, why does VACUUM FULL remove them? You can also run plain VACUUM after FULL to put the maps back. But the documentation is apparently missing details about this. It is a long-standing TODO item I tried to fix, but couldn't: Allow VACUUM FULL and CLUSTER to update the visibility map index-only scans : abnormal heap fetches after VACUUM FULL http://www.postgresql.org/message-id/20130112191404.255...@gmx.com -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c new file mode 100644 index 18614e7..6a87467 *** a/src/bin/initdb/initdb.c --- b/src/bin/initdb/initdb.c *** vacuum_db(void) *** 2334,2340 PG_CMD_OPEN; ! PG_CMD_PUTS(ANALYZE;\nVACUUM FULL;\nVACUUM FREEZE;\n); PG_CMD_CLOSE; --- 2334,2341 PG_CMD_OPEN; ! /* Run analyze before VACUUM so the statistics are frozen. */ ! PG_CMD_PUTS(ANALYZE;\nVACUUM FREEZE;\n); PG_CMD_CLOSE; -- 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] Abbreviated keys for Numeric
On Fri, Mar 20, 2015 at 7:10 PM, Andrew Gierth and...@tao11.riddles.org.uk wrote: Peter Other than that, I've tried to keep things closer to the text Peter opclass. For example, the cost model now has a few debugging Peter traces (disabled by default). I have altered the ad-hoc cost Peter model so that it no longer concerns itself with NULL inputs, Peter which seemed questionable (not least since the abbreviation Peter conversion function isn't actually called for NULL inputs. Any Peter attempt to track the full count within numeric code therefore Peter cannot work.). This is simply wrong. The reason why the cost model (in my version) tracks non-null values by having its own counter is precisely BECAUSE the passed-in memtupcount includes nulls, and therefore the code will UNDERESTIMATE the fraction of successfully abbreviated values if the comparison is based on memtupcount. Oh, right. It's the other way around in your original. I don't really buy it, either way. In what sense is a NULL value ever abbreviated? It isn't. Whatever about the cost model, that's the truth of the matter. There is always going to be a sort of tension in any cost model, between whether or not it's worth making it more sophisticated, and the extent to which tweaking the model is chasing diminishing returns. In your version, if there are null values at the start of the input, then on the first non-null value after that, memtupcount will be 1 and there will be only 1 distinct abbreviated value, causing abbreviation to spuriously abort. By what objective standard is that spurious? As things stand, I hesitate to get these ad-hoc cost models into the business of worrying about sorts with many NULL values, because of the additional complexity. Sorts with many NULL values, such as your example, have always been very fast, but also very rare in the real world. Sure, things might change in the event of many NULLs, and so you might consider that it's worth hanging on at no extra cost. But these cost models are really about preventing the very worst case. It seems worthwhile to not over-complicate them. They're based on the assumption that a sample of the first n values are representative of the whole, which, in general, could certainly be false. We do what we can. Your example has one abbreviated key in it, which is exactly worthless among NULL values. If it is representative of the next 10K rows, or the next 100K, then we probably should abort. Maybe that isn't exactly the right thing here, but if so that's only because numeric abbreviation is relatively cheap. in general, amortizing the cost of comparisons through encoding is a lousy strategy when there will be so few real comparisons. I'd like to hear what other people think. We could certainly consider adding that back, since it isn't especially complicated. Perhaps I was hasty there. The test to clamp the estimate to 1.0 is just nonsensical and serves no purpose whatever, and the comment for it is wrong. You should fix the text abbreviation code, not propagate your mistakes further. (BTW, there's an outright typo in your code, ';;' for ';' at the end of a line. Sloppy.) We're really going to call out minor typos like that as sloppy? If so, let me name a few of yours: * Wrong ordering of header includes * Trailing whitespace * ...but this time is it he original weight in digit... (not it is?) I also think that your explanation of the encoding schemes was perfunctory. And, the VARATT_IS_SHORT() hack that you added seemed wholly unnecessary. You better remind the committer that's going to consider my [Andrew's] original version unchanged in preference to this to go over these points again. Or you could try and work it out with me, the reviewer, rather than behaving so petulantly. Peter I also now allocate a buffer of scratch memory separately from Peter the main sortsupport object - doing one allocation for all Peter sortsupport state, bunched together as a buffer seemed like a Peter questionable micro-optimization. It's yet another cache line... I admit I did not benchmark that choice, but then neither did you. You're right, I didn't. There are two reasons why: 1) It doesn't work that way. I am not required to make sure that a patch I'm reviewing doesn't take advantage of every possible micro-optimization. Clarity is a more pressing concern. If I changed existing code in the master branch, that would be another story. You're the patch author here, remember? If it's such a loss, then prove it. 2) This patch is extremely effective in general. Well done! It seemed silly to worry about a micro-optimization like that, especially given the current time pressures for *both* of us. It can always be revisited. Peter It seemed unwise to silently disable abbreviation when someone Peter happened to build with DEC_DIGITS != 4. A static assertion now Peter gives these unusual cases the opportunity to make an informed
Re: [HACKERS] Superuser connect during smart shutdown
On Thu, Mar 19, 2015 at 10:42 PM, Bruce Momjian br...@momjian.us wrote: On Mon, Oct 20, 2014 at 03:10:50PM -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Oct 19, 2014 at 12:27 PM, Tom Lane t...@sss.pgh.pa.us wrote: I've certainly objected to it in the past, but I don't believe I was the only one objecting. What's your feeling now? I'm prepared to yield on the point. OK, are we up for changing the default pg_ctl shutdown method for 9.5, (smart to fast), or should we wait for 9.6? I'm up for it. I think it's long overdue. -- 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] configure can't detect proper pthread flags
On Fri, Mar 20, 2015 at 7:01 AM, Max Filippov jcmvb...@gmail.com wrote: On Fri, Mar 20, 2015 at 6:08 AM, Tom Lane t...@sss.pgh.pa.us wrote: We don't want every link step producing a useless warning. Ideally, make -s would print nothing whatsoever; to the extent that tools produce unsuppressable routine chatter, that's evil because it makes it harder to notice actually-useful warnings. Then maybe stderr tests should grep output for a specific option, the one we're currently testing, not just any noise? That sounds awfully fragile to me. It can't really be safe to assume we know precisely what the warning messages will look like. But it seems to me that compiling every test program with every library we might need is not a great plan. (I don't know enough about autoconf to know whether changing that is realistic.) -- 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] assessing parallel-safety
On 18 March 2015 at 16:01, Robert Haas robertmh...@gmail.com wrote: On Tue, Mar 17, 2015 at 9:48 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Mar 17, 2015 at 2:26 AM, Noah Misch n...@leadboat.com wrote: Neither that rule, nor its variant downthread, would hurt operator authors too much. To make the planner categorically parallel-safe, though, means limiting evaluate_function() to parallel-safe functions. That would dramatically slow selected queries. It's enough for the PL scenario if planning a parallel-safe query is itself parallel-safe. If the planner is parallel-unsafe when planning a parallel-unsafe query, what would suffer? Good point. So I guess the rule can be that planning a parallel-safe query should be parallel-safe. From there, it follows that estimators for a parallel-safe operator must also be parallel-safe. Which seems fine. More work is needed here, but for now, here is a rebased patch, per Amit's request. This no longer applies due to changes in commit 13dbc7a824b3f905904cab51840d37f31a07a9ef. -- Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] configure can't detect proper pthread flags
On Fri, Mar 20, 2015 at 08:05:48AM -0400, Robert Haas wrote: On Fri, Mar 20, 2015 at 7:01 AM, Max Filippov jcmvb...@gmail.com wrote: On Fri, Mar 20, 2015 at 6:08 AM, Tom Lane t...@sss.pgh.pa.us wrote: We don't want every link step producing a useless warning. Ideally, make -s would print nothing whatsoever; to the extent that tools produce unsuppressable routine chatter, that's evil because it makes it harder to notice actually-useful warnings. Then maybe stderr tests should grep output for a specific option, the one we're currently testing, not just any noise? That sounds awfully fragile to me. It can't really be safe to assume we know precisely what the warning messages will look like. But it seems to me that compiling every test program with every library we might need is not a great plan. (I don't know enough about autoconf to know whether changing that is realistic.) It was our only plan, and it has worked fine in the past. Someone is going to have to do a lot of portability research to improve it. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] proposal: doc: simplify examples of dynamic SQL
On Fri, Mar 20, 2015 at 05:47:49PM -0300, Alvaro Herrera wrote:
Bruce Momjian wrote:
On Fri, Mar 20, 2015 at 08:54:24AM -0700, David G. Johnston wrote:
1. The layout of the format version is different, with respect to
newlines,
than the quote version; but while using newlines for the mandatory
concatenation is good having an excessively long format string isn't
desirable
and so maybe we should show something like:
EXECUTE format('SELECT count(*) FROM %I '
|| 'WHERE inserted_by = $1 AND insert = $2', tabname)
INTO c
USING checked_user, checked_date
I think that is very confusing --- the idea is that we don't need to use
|| with format, but you are then using || to span multiple lines.
That || seems fine, since it's only used for a line continuation; having
|| scattered all over the query string to interpolate each variable is
much more unreadable.
That said, the || there is unnecessary because per standard two literals
'lit1'
'lit2'
are concatenated if they are separated by a newline. So this
EXECUTE format('SELECT count(*) FROM %I '
'WHERE inserted_by = $1 AND insert = $2', tabname)
INTO c
USING checked_user, checked_date
should suffice.
OK, I used your idea, patch attached.
BTW very long lines are undesirable because they are truncated in the
PDF output.
True, but the length was only 95 characters --- is that too long for our
PDFs?
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index aa19e10..3195655
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 2998,3011
para
literalI/literal treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null.
/para
/listitem
listitem
para
literalL/literal quotes the argument value as an SQL literal.
A null value is displayed as the string literalNULL/, without
! quotes.
/para
/listitem
/itemizedlist
--- 2998,3012
para
literalI/literal treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null (equivalent to
! functionquote_ident/).
/para
/listitem
listitem
para
literalL/literal quotes the argument value as an SQL literal.
A null value is displayed as the string literalNULL/, without
! quotes (equivalent to functionquote_nullable/function).
/para
/listitem
/itemizedlist
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 158d9d2..bb09479
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*** EXECUTE 'SELECT count(*) FROM mytable WH
*** 1217,1227
dynamically selected table, you could do this:
programlisting
EXECUTE 'SELECT count(*) FROM '
! || tabname::regclass
|| ' WHERE inserted_by = $1 AND inserted lt;= $2'
INTO c
USING checked_user, checked_date;
/programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
--- 1217,1235
dynamically selected table, you could do this:
programlisting
EXECUTE 'SELECT count(*) FROM '
! || quote_ident(tabname)
|| ' WHERE inserted_by = $1 AND inserted lt;= $2'
INTO c
USING checked_user, checked_date;
/programlisting
+ A cleaner approach is to use functionformat()/'s literal%I/
+ specification for table or column names:
+ programlisting
+ EXECUTE format('SELECT count(*) FROM %I '
+'WHERE inserted_by = $1 AND inserted lt;= $2', tabname)
+INTO c
+USING checked_user, checked_date;
+ /programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
*** EXECUTE 'SELECT count(*) FROM '
*** 1297,1307
/para
para
! Dynamic values that are to be inserted into the constructed
! query require careful handling since they might themselves contain
quote characters.
! An example (this assumes that you are using dollar quoting for the
! function as a whole, so the quote marks need not be doubled):
programlisting
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
--- 1305,1319
/para
para
! Dynamic values require careful handling since
Re: [HACKERS] Typos in CREATE TABLE doc
On Thu, Nov 13, 2014 at 08:30:49PM +0900, Etsuro Fujita wrote: (2014/11/13 20:07), Heikki Linnakangas wrote: On 11/13/2014 12:45 PM, Etsuro Fujita wrote: It seems to me there are typos in the reference page for CREATE TABLE. The structure of the sentence is a bit funky, but it seems correct to me. If you google for should any, you'll get a bunch of pages discussing similar sentences. I would add a comma there, though: Should any row of an insert or update operation produce a FALSE result, an exception is raised and ... I understand. So, Here is the comma patch. Patch applied. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] proposal: doc: simplify examples of dynamic SQL
Bruce Momjian wrote: On Fri, Mar 20, 2015 at 05:47:49PM -0300, Alvaro Herrera wrote: BTW very long lines are undesirable because they are truncated in the PDF output. True, but the length was only 95 characters --- is that too long for our PDFs? I built some PDFs when I did the ALTER USER CURRENT_USER patch, and it seemed to me that synposes ought to limit to about 85 chars. Maybe some more still fit in the page, but I think 95 would be a bit too much. I'm not sure how the code you're writing here is indented, though; you'd have to build it and give it a look. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] proposal: doc: simplify examples of dynamic SQL
On Fri, Mar 20, 2015 at 06:05:35PM -0300, Alvaro Herrera wrote: Bruce Momjian wrote: On Fri, Mar 20, 2015 at 05:47:49PM -0300, Alvaro Herrera wrote: BTW very long lines are undesirable because they are truncated in the PDF output. True, but the length was only 95 characters --- is that too long for our PDFs? I built some PDFs when I did the ALTER USER CURRENT_USER patch, and it seemed to me that synposes ought to limit to about 85 chars. Maybe some more still fit in the page, but I think 95 would be a bit too much. I'm not sure how the code you're writing here is indented, though; you'd have to build it and give it a look. OK, thanks, good to know. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] configure can't detect proper pthread flags
Robert == Robert Haas robertmh...@gmail.com writes: Then maybe stderr tests should grep output for a specific option, the one we're currently testing, not just any noise? Robert That sounds awfully fragile to me. It can't really be safe to Robert assume we know precisely what the warning messages will look Robert like. But how safe is it to assume that a warning message about option '-foo' will contain the string '-foo' in it somewhere? (though the trace output from -x still should be dealt with separately) -- Andrew (irc:RhodiumToad) -- 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] inherit support for foreign tables
Etsuro Fujita fujita.ets...@lab.ntt.co.jp writes: I noticed that the latter disallows TRUNCATE on inheritance trees that contain at least one child foreign table. But I think it would be better to allow it, with the semantics that we quietly ignore the child foreign tables and apply the operation to the child plain tables, which is the same semantics as ALTER COLUMN SET STORAGE on such inheritance trees. Comments welcome. I've been working through the foreign table inheritance patch, and found the code that makes the above happen. I don't think this is a good idea at all. In the first place, successful TRUNCATE should leave the table empty, not well, we'll make it empty if we feel up to that. In the second place, someday we might want to make TRUNCATE actually work for foreign tables (at least for FDWs that want to support it). If we did, we would have a backwards-compatibility hazard, because suddenly a TRUNCATE on an inheritance tree that includes a foreign table would have different non-error effects than before. I think we should just throw error in this case. BTW, the SET STORAGE comparison is bogus as well. I see no reason that we shouldn't just allow SET STORAGE on foreign tables. It's probably not going to have any effect, but so what? And again, if we did ever find a use for that, we'd have a compatibility problem if inherited SET STORAGE has a pre-existing behavior that it skips foreign children. In the same vein, I'm planning to take out the existing prohibition on marking CHECK constraints on foreign tables NOT VALID. That likewise creates a corner case for inheritance trees for no obviously good reason. It was reasonable to be conservative about whether to allow that so long as there were no side-effects; but putting warts into the behavior of inheritance trees to preserve the prohibition is not a good outcome. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Order of enforcement of CHECK constraints?
My Salesforce colleagues noticed some tests flapping as a result of table CHECK constraints not always being enforced in the same order; ie, if a tuple insertion/update violates more than one CHECK constraint, it's not deterministic which one is reported. This is evidently because relcache.c's CheckConstraintFetch() just happily loads up the constraints in whatever order it happens to find them in pg_constraint. There's at least one regression test case where this can happen, so we've been lucky so far that this hasn't caused buildfarm noise. We could fix it by, say, having CheckConstraintFetch() sort the constraints by name after loading them. In principle the same problem could occur for domain CHECK constraints, though the odds of multiple CHECKs failing are probably a lot lower. Do people think this is worth fixing? 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] Order of enforcement of CHECK constraints?
On Fri, Mar 20, 2015 at 12:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: We could fix it by, say, having CheckConstraintFetch() sort the constraints by name after loading them. What not by OID, as with indexes? Are you suggesting that this would become documented behavior? -- Peter Geoghegan -- 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] Remove fsync ON/OFF as a visible option?
Joshua D. Drake j...@commandprompt.com writes: There are just as many people that are running with scissors that are now running (or attempting to run) our elephant in production. Evidence please. Does it make sense to remove fsync (and possibly full_page_writes) from such a visible place as postgresql.conf? I don't think we should remove the features themselves but perhaps only have them settable via the new alter system command? I would object to that, because it would make it vastly more difficult to use fsync=off easily for development. 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] proposal: searching in array function - array_position
Alvaro Herrera alvhe...@2ndquadrant.com writes: Pavel Stehule wrote: I am thinking, so it is ok - it returns a offset, not position. So you can't use it as a subscript? That sounds unfriendly. Almost every function using this will be subtly broken. I concur; perhaps offset was the design intention, but it's wrong. The result should be a subscript. 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] Remove fsync ON/OFF as a visible option?
On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake j...@commandprompt.com wrote: There are just as many people that are running with scissors that are now running (or attempting to run) our elephant in production. Does it make sense to remove fsync (and possibly full_page_writes) from such a visible place as postgresql.conf? -1 Anyone turning off fsync without even for a moment considering the consequences has only themselves to blame. I can't imagine why you'd want to remove full_page_writes or make it less visible either, since in principle it ought to be perfectly fine to turn it off in production once its verified as safe. -- Peter Geoghegan -- 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] assessing parallel-safety
On Fri, Mar 20, 2015 at 1:24 PM, Thom Brown t...@linux.com wrote: OK, thanks. That looks like it's probably the fault of parallel seq scan patch rather than this one. It would help if you could build with debug symbols so that we can see line numbers and arguments. Sure. Program received signal SIGABRT, Aborted. 0x7f5a49fce1d5 in raise () from /lib/x86_64-linux-gnu/libc.so.6 (gdb) bt #0 0x7f5a49fce1d5 in raise () from /lib/x86_64-linux-gnu/libc.so.6 #1 0x7f5a49fd1388 in abort () from /lib/x86_64-linux-gnu/libc.so.6 #2 0x007a053a in ExceptionalCondition (conditionName=conditionName@entry=0x813a4b !(IsInParallelMode()), errorType=errorType@entry=0x7da1d6 FailedAssertion, fileName=fileName@entry=0x81397d parallel.c, lineNumber=lineNumber@entry=123) at assert.c:54 #3 0x004cd5ba in CreateParallelContext (entrypoint=entrypoint@entry=0x659d2c ParallelQueryMain, nworkers=nworkers@entry=8) at parallel.c:123 #4 0x0065a1c0 in InitializeParallelWorkers (plan=0x281e6a0, estate=estate@entry=0x28b99a8, rel=rel@entry=0x7f594eab2370, inst_options_space=inst_options_space@entry=0x28bbfa8, buffer_usage_space=buffer_usage_space@entry=0x28bbfb0, responseqp=responseqp@entry=0x28bbf98, pcxtp=pcxtp@entry=0x28bbf90, nWorkers=8) at backendworker.c:279 #5 0x005d0e75 in InitFunnel (node=node@entry=0x28bbf00, estate=estate@entry=0x28b99a8, eflags=eflags@entry=17) at nodeFunnel.c:61 #6 0x005d1026 in ExecInitFunnel (node=0x281e738, estate=0x28b99a8, eflags=17) at nodeFunnel.c:121 #7 0x005c0f95 in ExecInitNode (node=0x281e738, estate=estate@entry=0x28b99a8, eflags=eflags@entry=17) at execProcnode.c:201 #8 0x005cd316 in ExecInitAppend (node=optimized out, estate=0x28b99a8, eflags=17) at nodeAppend.c:168 #9 0x005c0f25 in ExecInitNode (node=0x288b990, estate=estate@entry=0x28b99a8, eflags=eflags@entry=17) at execProcnode.c:163 #10 0x005ce849 in ExecInitAgg (node=0x288ba28, estate=0x28b99a8, eflags=17) at nodeAgg.c:1580 #11 0x005c10bf in ExecInitNode (node=node@entry=0x288ba28, estate=estate@entry=0x28b99a8, eflags=eflags@entry=17) at execProcnode.c:302 #12 0x005bfb35 in InitPlan (queryDesc=queryDesc@entry=0x28b5868, eflags=eflags@entry=17) at execMain.c:939 #13 0x005bfd49 in standard_ExecutorStart (queryDesc=0x28b5868, eflags=17) at execMain.c:234 #14 0x005bfd95 in ExecutorStart (queryDesc=queryDesc@entry=0x28b5868, eflags=eflags@entry=1) at execMain.c:134 #15 0x00573f21 in ExplainOnePlan (plannedstmt=plannedstmt@entry=0x28b7878, into=into@entry=0x0, es=es@entry=0x24cde68, queryString=queryString@entry=0x248a398 EXPLAIN SELECT DISTINCT bid FROM pgbench_accounts;, params=params@entry=0x0, planduration=planduration@entry=0x7fffb64f4bf0) at explain.c:478 #16 0x00574160 in ExplainOneQuery (query=optimized out, into=into@entry=0x0, es=es@entry=0x24cde68, queryString=queryString@entry=0x248a398 EXPLAIN SELECT DISTINCT bid FROM pgbench_accounts;, params=params@entry=0x0) at explain.c:346 #17 0x0057478a in ExplainQuery (stmt=stmt@entry=0x248b1b0, queryString=queryString@entry=0x248a398 EXPLAIN SELECT DISTINCT bid FROM pgbench_accounts;, params=params@entry=0x0, dest=dest@entry=0x24cddd0) at explain.c:234 #18 0x006c6419 in standard_ProcessUtility (parsetree=0x248b1b0, queryString=0x248a398 EXPLAIN SELECT DISTINCT bid FROM pgbench_accounts;, context=PROCESS_UTILITY_TOPLEVEL, params=0x0, dest=0x24cddd0, completionTag=0x7fffb64f4d90 ) at utility.c:657 #19 0x006c6808 in ProcessUtility (parsetree=parsetree@entry=0x248b1b0, queryString=optimized out, context=context@entry=PROCESS_UTILITY_TOPLEVEL, params=optimized out, dest=dest@entry=0x24cddd0, completionTag=completionTag@entry=0x7fffb64f4d90 ) at utility.c:333 #20 0x006c3272 in PortalRunUtility (portal=portal@entry=0x24f2e28, utilityStmt=0x248b1b0, isTopLevel=optimized out, dest=dest@entry=0x24cddd0, completionTag=completionTag@entry=0x7fffb64f4d90 ) at pquery.c:1188 #21 0x006c4039 in FillPortalStore (portal=portal@entry=0x24f2e28, isTopLevel=isTopLevel@entry=1 '\001') at pquery.c:1062 #22 0x006c4a12 in PortalRun (portal=portal@entry=0x24f2e28, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=1 '\001', dest=dest@entry=0x248b5e8, altdest=altdest@entry=0x248b5e8, completionTag=completionTag@entry=0x7fffb64f4fa0 ) at pquery.c:786 #23 0x006c12c3 in exec_simple_query (query_string=query_string@entry=0x248a398 EXPLAIN SELECT DISTINCT bid FROM pgbench_accounts;) at postgres.c:1107 #24 0x006c2de4 in PostgresMain (argc=optimized out, argv=argv@entry=0x2421c28, dbname=0x2421a90 pgbench, username=optimized out) at postgres.c:4118 #25 0x00665c55 in BackendRun (port=port@entry=0x2447540) at postmaster.c:4148 #26 0x006675a8 in BackendStartup (port=port@entry=0x2447540) at postmaster.c:3833
Re: [HACKERS] [PATCH] two-arg current_setting() with fallback
On Mar 20, 2015, at 11:10 AM, David G. Johnston david.g.johns...@gmail.com wrote: On Fri, Mar 20, 2015 at 9:04 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 20, 2015 at 10:54 AM, David Christensen da...@endpoint.com wrote: In that case, the other thought I had here is that we change the function signature of current_setting() to be a two-arg form where the second argument is a boolean throw_error, with a default argument of true to preserve existing semantics, and returning NULL if that argument is false. However, I'm not sure if there are some issues with changing the signature of an existing function (e.g., with pg_upgrade, etc.). My *impression* is that since pg_upgrade rebuilds the system tables for a new install it shouldn't be an issue, but not sure if having the same pg_proc OID with different values or an alternate pg_proc OID would cause issues down the line; anyone know if this is a dead-end? I think if the second argument is defaulted it would be OK. However it might make sense to instead add a new two-argument function and leave the existing one-argument function alone, because setting default arguments for functions defined in pg_proc.h is kind of a chore. Isn't there some other update along this whole error-vs-null choice going around where a separate name was chosen for the new null-returning function instead of adding a boolean switch argument? Well, speaking of the two-arg form vs alternate name, here's a version of the patch which includes the new behavior. (I couldn't think of a good name to expose for an alternate function, but I'm open to suggestions.) Regards, David -- David Christensen End Point Corporation da...@endpoint.com 785-727-1171 0001-Add-two-arg-form-of-current_setting-to-optionally-su.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] assessing parallel-safety
On 20 March 2015 at 15:25, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 20, 2015 at 11:08 AM, Thom Brown t...@linux.com wrote: On 20 March 2015 at 15:02, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 20, 2015 at 10:24 AM, Thom Brown t...@linux.com wrote: 2015-03-20 14:19:31 GMT [4273]: [78-1] user=,db=,client= DEBUG: server process (PID 4285) was terminated by signal 11: Segmentation fault Any chance you can get us a stack backtrace of this crash? (gdb) cont Continuing. Program received signal SIGSEGV, Segmentation fault. 0x00770843 in pfree () (gdb) bt #0 0x00770843 in pfree () #1 0x005a382f in ExecEndFunnel () #2 0x0059fe75 in ExecEndAppend () #3 0x005920bd in standard_ExecutorEnd () #4 0x0055004b in ExplainOnePlan () #5 0x0055025d in ExplainOneQuery () #6 0x0055064d in ExplainQuery () #7 0x00680db1 in standard_ProcessUtility () #8 0x0067e1c1 in PortalRunUtility () #9 0x0067ef1d in FillPortalStore () #10 0x0067f8eb in PortalRun () #11 0x0067d628 in PostgresMain () #12 0x00462c5e in ServerLoop () #13 0x0062e363 in PostmasterMain () #14 0x004636ad in main () OK, thanks. That looks like it's probably the fault of parallel seq scan patch rather than this one. It would help if you could build with debug symbols so that we can see line numbers and arguments. Sure. Program received signal SIGABRT, Aborted. 0x7f5a49fce1d5 in raise () from /lib/x86_64-linux-gnu/libc.so.6 (gdb) bt #0 0x7f5a49fce1d5 in raise () from /lib/x86_64-linux-gnu/libc.so.6 #1 0x7f5a49fd1388 in abort () from /lib/x86_64-linux-gnu/libc.so.6 #2 0x007a053a in ExceptionalCondition (conditionName=conditionName@entry=0x813a4b !(IsInParallelMode()), errorType=errorType@entry=0x7da1d6 FailedAssertion, fileName=fileName@entry=0x81397d parallel.c, lineNumber=lineNumber@entry=123) at assert.c:54 #3 0x004cd5ba in CreateParallelContext (entrypoint=entrypoint@entry=0x659d2c ParallelQueryMain, nworkers=nworkers@entry=8) at parallel.c:123 #4 0x0065a1c0 in InitializeParallelWorkers (plan=0x281e6a0, estate=estate@entry=0x28b99a8, rel=rel@entry=0x7f594eab2370, inst_options_space=inst_options_space@entry=0x28bbfa8, buffer_usage_space=buffer_usage_space@entry=0x28bbfb0, responseqp=responseqp@entry=0x28bbf98, pcxtp=pcxtp@entry=0x28bbf90, nWorkers=8) at backendworker.c:279 #5 0x005d0e75 in InitFunnel (node=node@entry=0x28bbf00, estate=estate@entry=0x28b99a8, eflags=eflags@entry=17) at nodeFunnel.c:61 #6 0x005d1026 in ExecInitFunnel (node=0x281e738, estate=0x28b99a8, eflags=17) at nodeFunnel.c:121 #7 0x005c0f95 in ExecInitNode (node=0x281e738, estate=estate@entry=0x28b99a8, eflags=eflags@entry=17) at execProcnode.c:201 #8 0x005cd316 in ExecInitAppend (node=optimized out, estate=0x28b99a8, eflags=17) at nodeAppend.c:168 #9 0x005c0f25 in ExecInitNode (node=0x288b990, estate=estate@entry=0x28b99a8, eflags=eflags@entry=17) at execProcnode.c:163 #10 0x005ce849 in ExecInitAgg (node=0x288ba28, estate=0x28b99a8, eflags=17) at nodeAgg.c:1580 #11 0x005c10bf in ExecInitNode (node=node@entry=0x288ba28, estate=estate@entry=0x28b99a8, eflags=eflags@entry=17) at execProcnode.c:302 #12 0x005bfb35 in InitPlan (queryDesc=queryDesc@entry=0x28b5868, eflags=eflags@entry=17) at execMain.c:939 #13 0x005bfd49 in standard_ExecutorStart (queryDesc=0x28b5868, eflags=17) at execMain.c:234 #14 0x005bfd95 in ExecutorStart (queryDesc=queryDesc@entry=0x28b5868, eflags=eflags@entry=1) at execMain.c:134 #15 0x00573f21 in ExplainOnePlan (plannedstmt=plannedstmt@entry=0x28b7878, into=into@entry=0x0, es=es@entry=0x24cde68, queryString=queryString@entry=0x248a398 EXPLAIN SELECT DISTINCT bid FROM pgbench_accounts;, params=params@entry=0x0, planduration=planduration@entry=0x7fffb64f4bf0) at explain.c:478 #16 0x00574160 in ExplainOneQuery (query=optimized out, into=into@entry=0x0, es=es@entry=0x24cde68, queryString=queryString@entry=0x248a398 EXPLAIN SELECT DISTINCT bid FROM pgbench_accounts;, params=params@entry=0x0) at explain.c:346 #17 0x0057478a in ExplainQuery (stmt=stmt@entry=0x248b1b0, queryString=queryString@entry=0x248a398 EXPLAIN SELECT DISTINCT bid FROM pgbench_accounts;, params=params@entry=0x0, dest=dest@entry=0x24cddd0) at explain.c:234 #18 0x006c6419 in standard_ProcessUtility (parsetree=0x248b1b0, queryString=0x248a398 EXPLAIN SELECT DISTINCT bid FROM pgbench_accounts;, context=PROCESS_UTILITY_TOPLEVEL, params=0x0, dest=0x24cddd0, completionTag=0x7fffb64f4d90 ) at utility.c:657 #19 0x006c6808 in ProcessUtility (parsetree=parsetree@entry=0x248b1b0, queryString=optimized out, context=context@entry=PROCESS_UTILITY_TOPLEVEL, params=optimized out, dest=dest@entry=0x24cddd0, completionTag=completionTag@entry=0x7fffb64f4d90 ) at utility.c:333
Re: [HACKERS] proposal: searching in array function - array_position
Pavel Stehule wrote:
2015-03-20 17:49 GMT+01:00 Dean Rasheed dean.a.rash...@gmail.com:
There's an issue when the array's lower bound isn't 1:
select array_offset('[2:4]={1,2,3}'::int[], 1);
array_offset
--
1
(1 row)
whereas I would expect this to return 2. Similarly for
array_offsets(), so the offsets can be used as indexes into the
original array.
I am thinking, so it is ok - it returns a offset, not position.
So you can't use it as a subscript? That sounds unfriendly. Almost
every function using this will be subtly broken.
--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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] proposal: searching in array function - array_position
2015-03-20 18:29 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:
Pavel Stehule wrote:
2015-03-20 17:49 GMT+01:00 Dean Rasheed dean.a.rash...@gmail.com:
There's an issue when the array's lower bound isn't 1:
select array_offset('[2:4]={1,2,3}'::int[], 1);
array_offset
--
1
(1 row)
whereas I would expect this to return 2. Similarly for
array_offsets(), so the offsets can be used as indexes into the
original array.
I am thinking, so it is ok - it returns a offset, not position.
So you can't use it as a subscript? That sounds unfriendly. Almost
every function using this will be subtly broken.
depends what you want. It means - it is on Nth position from start. So it
is useful when iterate over array, because it is safe against different
array start dimensions. it works, if you use it as offset. It is named
array_offset
It can be changed and renamed to array_position - it is simple fix. But I
am not sure, if it is better.
Regards
Pavel
--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
[HACKERS] Remove fsync ON/OFF as a visible option?
Hello, There are just as many people that are running with scissors that are now running (or attempting to run) our elephant in production. Does it make sense to remove fsync (and possibly full_page_writes) from such a visible place as postgresql.conf? I don't think we should remove the features themselves but perhaps only have them settable via the new alter system command? JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc Now I get it: your service is designed for a customer base that grew up with Facebook, watches Japanese seizure robot anime, and has the attention span of a gnat. I'm not that user., Tyler Riddle -- 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] two-arg current_setting() with fallback
On Fri, Mar 20, 2015 at 9:04 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 20, 2015 at 10:54 AM, David Christensen da...@endpoint.com wrote: In that case, the other thought I had here is that we change the function signature of current_setting() to be a two-arg form where the second argument is a boolean throw_error, with a default argument of true to preserve existing semantics, and returning NULL if that argument is false. However, I'm not sure if there are some issues with changing the signature of an existing function (e.g., with pg_upgrade, etc.). My *impression* is that since pg_upgrade rebuilds the system tables for a new install it shouldn't be an issue, but not sure if having the same pg_proc OID with different values or an alternate pg_proc OID would cause issues down the line; anyone know if this is a dead-end? I think if the second argument is defaulted it would be OK. However it might make sense to instead add a new two-argument function and leave the existing one-argument function alone, because setting default arguments for functions defined in pg_proc.h is kind of a chore. Isn't there some other update along this whole error-vs-null choice going around where a separate name was chosen for the new null-returning function instead of adding a boolean switch argument? David J.
Re: [HACKERS] [PATCH] two-arg current_setting() with fallback
On Fri, Mar 20, 2015 at 10:54 AM, David Christensen da...@endpoint.com wrote: In that case, the other thought I had here is that we change the function signature of current_setting() to be a two-arg form where the second argument is a boolean throw_error, with a default argument of true to preserve existing semantics, and returning NULL if that argument is false. However, I'm not sure if there are some issues with changing the signature of an existing function (e.g., with pg_upgrade, etc.). My *impression* is that since pg_upgrade rebuilds the system tables for a new install it shouldn't be an issue, but not sure if having the same pg_proc OID with different values or an alternate pg_proc OID would cause issues down the line; anyone know if this is a dead-end? I think if the second argument is defaulted it would be OK. However it might make sense to instead add a new two-argument function and leave the existing one-argument function alone, because setting default arguments for functions defined in pg_proc.h is kind of a chore. -- 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] proposal: searching in array function - array_position
On 18 March 2015 at 19:03, Alvaro Herrera alvhe...@2ndquadrant.com wrote:
Pushed after adding error checks there and fixing the docs to match.
Please verify.
There's an issue when the array's lower bound isn't 1:
select array_offset('[2:4]={1,2,3}'::int[], 1);
array_offset
--
1
(1 row)
whereas I would expect this to return 2. Similarly for
array_offsets(), so the offsets can be used as indexes into the
original array.
Regards,
Dean
--
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] proposal: searching in array function - array_position
2015-03-20 17:49 GMT+01:00 Dean Rasheed dean.a.rash...@gmail.com:
On 18 March 2015 at 19:03, Alvaro Herrera alvhe...@2ndquadrant.com
wrote:
Pushed after adding error checks there and fixing the docs to match.
Please verify.
There's an issue when the array's lower bound isn't 1:
select array_offset('[2:4]={1,2,3}'::int[], 1);
array_offset
--
1
(1 row)
whereas I would expect this to return 2. Similarly for
array_offsets(), so the offsets can be used as indexes into the
original array.
I am thinking, so it is ok - it returns a offset, not position.
Regards
Pavel
Regards,
Dean
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Fri, Mar 20, 2015 at 08:43:21AM -0700, David G. Johnston wrote:
On Thu, Mar 19, 2015 at 6:49 PM, Bruce Momjian br...@momjian.us wrote:
It is making a point about nulls and stuff. There are later queries
that use format().
I thought maybe you meant those but your specific mention of
There are other places later in the docs confused me since you made changes
before and after that specific section.
Those examples need to be somewhere and it doesn't seem like a undesireable
enough setup that major reconstructive surgery is warranted to try and move
them elsewhere.
Yes, agreed.
I was actually referring to chapter 9
http://www.postgresql.org/docs/9.4/interactive/functions-string.html
The table definitions of the quote_* function should have a comment about
their equivalency to format %I and %L
I think it is going to be awkward to mention a much more complex
function, format(), when covering a simle quote function.
Also, in 9.4.1 (format - type) would be the most obvious place for the
equivalency of the format %I and %L to quote_*
Yes, added.
Update patch attached and URL udpated with current patch too.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index aa19e10..3195655
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 2998,3011
para
literalI/literal treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null.
/para
/listitem
listitem
para
literalL/literal quotes the argument value as an SQL literal.
A null value is displayed as the string literalNULL/, without
! quotes.
/para
/listitem
/itemizedlist
--- 2998,3012
para
literalI/literal treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null (equivalent to
! functionquote_ident/).
/para
/listitem
listitem
para
literalL/literal quotes the argument value as an SQL literal.
A null value is displayed as the string literalNULL/, without
! quotes (equivalent to functionquote_nullable/function).
/para
/listitem
/itemizedlist
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 158d9d2..aee8264
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*** EXECUTE 'SELECT count(*) FROM '
*** 1222,1227
--- 1222,1234
INTO c
USING checked_user, checked_date;
/programlisting
+ A cleaner approach is to use functionformat()/'s literal%I/
+ specification for table or column names:
+ programlisting
+ EXECUTE format('SELECT count(*) FROM %I WHERE inserted_by = $1 AND inserted lt;= $2', tabname)
+INTO c
+USING checked_user, checked_date;
+ /programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
*** EXECUTE 'SELECT count(*) FROM '
*** 1297,1307
/para
para
! Dynamic values that are to be inserted into the constructed
! query require careful handling since they might themselves contain
quote characters.
! An example (this assumes that you are using dollar quoting for the
! function as a whole, so the quote marks need not be doubled):
programlisting
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
--- 1304,1317
/para
para
! Dynamic values require careful handling since they might contain
quote characters.
! An example using functionformat()/ (this assumes that you are
! dollar quoting the function body so quote marks need not be doubled):
! programlisting
! EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname) USING newvalue, keyvalue;
! /programlisting
! It is also possible to call the quoting functions directly:
programlisting
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
*** EXECUTE 'UPDATE tbl SET '
*** 1393,1407
programlisting
EXECUTE format('UPDATE tbl SET %I = %L WHERE key = %L', colname, newvalue, keyvalue);
/programlisting
The functionformat/function function can be used in conjunction with
the literalUSING/literal clause:
programlisting
EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
USING newvalue, keyvalue;
/programlisting
! This form is more
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Fri, Mar 20, 2015 at 08:54:24AM -0700, David G. Johnston wrote:
Looking at http://momjian.us/tmp/pgsql/plpgsql-statements.html#
PLPGSQL-STATEMENTS-EXECUTING-DYN
The paired example at the top of the patch has two things worth considering.
1. The layout of the format version is different, with respect to newlines,
than the quote version; but while using newlines for the mandatory
concatenation is good having an excessively long format string isn't desirable
and so maybe we should show something like:
EXECUTE format('SELECT count(*) FROM %I '
|| 'WHERE inserted_by = $1 AND insert = $2', tabname)
INTO c
USING checked_user, checked_date
I think that is very confusing --- the idea is that we don't need to use
|| with format, but you are then using || to span multiple lines.
2. There is a recent posting pointing out the fact that the first query did
not
use quote_ident(tabname) but instead did tabname::regclass, which calls
quote_ident internally. While there is a choice is that situation with format
you must pass in an unquoted label and so must not use tabname::regclass. I
think the first example should be written to use quote_ident(tabname).
Ah, good point. Updated patch attached, and URL updated.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index aa19e10..3195655
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 2998,3011
para
literalI/literal treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null.
/para
/listitem
listitem
para
literalL/literal quotes the argument value as an SQL literal.
A null value is displayed as the string literalNULL/, without
! quotes.
/para
/listitem
/itemizedlist
--- 2998,3012
para
literalI/literal treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null (equivalent to
! functionquote_ident/).
/para
/listitem
listitem
para
literalL/literal quotes the argument value as an SQL literal.
A null value is displayed as the string literalNULL/, without
! quotes (equivalent to functionquote_nullable/function).
/para
/listitem
/itemizedlist
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 158d9d2..451cbb4
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*** EXECUTE 'SELECT count(*) FROM mytable WH
*** 1217,1227
dynamically selected table, you could do this:
programlisting
EXECUTE 'SELECT count(*) FROM '
! || tabname::regclass
|| ' WHERE inserted_by = $1 AND inserted lt;= $2'
INTO c
USING checked_user, checked_date;
/programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
--- 1217,1234
dynamically selected table, you could do this:
programlisting
EXECUTE 'SELECT count(*) FROM '
! || quote_ident(tabname)
|| ' WHERE inserted_by = $1 AND inserted lt;= $2'
INTO c
USING checked_user, checked_date;
/programlisting
+ A cleaner approach is to use functionformat()/'s literal%I/
+ specification for table or column names:
+ programlisting
+ EXECUTE format('SELECT count(*) FROM %I WHERE inserted_by = $1 AND inserted lt;= $2', tabname)
+INTO c
+USING checked_user, checked_date;
+ /programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
*** EXECUTE 'SELECT count(*) FROM '
*** 1297,1307
/para
para
! Dynamic values that are to be inserted into the constructed
! query require careful handling since they might themselves contain
quote characters.
! An example (this assumes that you are using dollar quoting for the
! function as a whole, so the quote marks need not be doubled):
programlisting
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
--- 1304,1317
/para
para
! Dynamic values require careful handling since they might contain
quote characters.
! An example using functionformat()/ (this assumes that you are
! dollar quoting the function body so quote marks need not be doubled):
!
Re: [HACKERS] Repeatable read and serializable transactions see data committed after tx start
On Sat, Nov 8, 2014 at 09:53:18PM +0100, Álvaro Hernández Tortosa wrote: On 07/11/14 22:02, Greg Sabino Mullane wrote: Kevin Grittner wrote: I think most people have always assumed that BEGIN starts the transaction and that is the point at which the snapshot is obtained. But there is so much evidence to the contrary. Not only does the *name* of the command (BEGIN or START) imply a start, but pg_stat_activity shows the connection idle in transaction after the command (and before a snapshot is acquired) Er...I think we are arguing the same thing here. So no contrary needed? :) So do we agree to fix the docs? ^_^ Doc patch attached. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml new file mode 100644 index a0d6867..e43a3be *** a/doc/src/sgml/mvcc.sgml --- b/doc/src/sgml/mvcc.sgml *** COMMIT; *** 422,429 para This level is different from Read Committed in that a query in a repeatable read transaction sees a snapshot as of the start of the emphasistransaction/, not as of the start ! of the current query within the transaction. Thus, successive commandSELECT/command commands within a emphasissingle/ transaction see the same data, i.e., they do not see changes made by other transactions that committed after their own transaction started. --- 422,430 para This level is different from Read Committed in that a query in a repeatable read transaction sees a snapshot as of the start of the + first non-transaction-control statement in the emphasistransaction/, not as of the start ! of the current statement within the transaction. Thus, successive commandSELECT/command commands within a emphasissingle/ transaction see the same data, i.e., they do not see changes made by other transactions that committed after their own transaction started. -- 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] proposal: doc: simplify examples of dynamic SQL
Bruce Momjian wrote:
On Fri, Mar 20, 2015 at 08:54:24AM -0700, David G. Johnston wrote:
1. The layout of the format version is different, with respect to newlines,
than the quote version; but while using newlines for the mandatory
concatenation is good having an excessively long format string isn't
desirable
and so maybe we should show something like:
EXECUTE format('SELECT count(*) FROM %I '
|| 'WHERE inserted_by = $1 AND insert = $2', tabname)
INTO c
USING checked_user, checked_date
I think that is very confusing --- the idea is that we don't need to use
|| with format, but you are then using || to span multiple lines.
That || seems fine, since it's only used for a line continuation; having
|| scattered all over the query string to interpolate each variable is
much more unreadable.
That said, the || there is unnecessary because per standard two literals 'lit1'
'lit2'
are concatenated if they are separated by a newline. So this
EXECUTE format('SELECT count(*) FROM %I '
'WHERE inserted_by = $1 AND insert = $2', tabname)
INTO c
USING checked_user, checked_date
should suffice.
BTW very long lines are undesirable because they are truncated in the
PDF output.
--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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] Order of enforcement of CHECK constraints?
* Tom Lane (t...@sss.pgh.pa.us) wrote: My Salesforce colleagues noticed some tests flapping as a result of table CHECK constraints not always being enforced in the same order; ie, if a tuple insertion/update violates more than one CHECK constraint, it's not deterministic which one is reported. This is evidently because relcache.c's CheckConstraintFetch() just happily loads up the constraints in whatever order it happens to find them in pg_constraint. There's at least one regression test case where this can happen, so we've been lucky so far that this hasn't caused buildfarm noise. We could fix it by, say, having CheckConstraintFetch() sort the constraints by name after loading them. In principle the same problem could occur for domain CHECK constraints, though the odds of multiple CHECKs failing are probably a lot lower. Do people think this is worth fixing? Yes... I had thought they were sorted and enforced in alphabetical order similar to how triggers are fired. Having non-deterministic check constraint firing seems bad to me. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Remove fsync ON/OFF as a visible option?
On 03/20/2015 10:45 AM, Tom Lane wrote: Joshua D. Drake j...@commandprompt.com writes: There are just as many people that are running with scissors that are now running (or attempting to run) our elephant in production. Evidence please. Fair enough. I am not going to name names but over the years (and just today) I ran into another user that corrupted their database by turning off fsync. Does it make sense to remove fsync (and possibly full_page_writes) from such a visible place as postgresql.conf? I don't think we should remove the features themselves but perhaps only have them settable via the new alter system command? I would object to that, because it would make it vastly more difficult to use fsync=off easily for development. How so? alter system fsync on/off (meta) restart That seems easier than editing the file and restarting? JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc Now I get it: your service is designed for a customer base that grew up with Facebook, watches Japanese seizure robot anime, and has the attention span of a gnat. I'm not that user., Tyler Riddle -- 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] Order of enforcement of CHECK constraints?
=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com writes: On Fri, Mar 20, 2015 at 4:19 PM, Peter Geoghegan p...@heroku.com wrote: On Fri, Mar 20, 2015 at 12:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: We could fix it by, say, having CheckConstraintFetch() sort the constraints by name after loading them. What not by OID, as with indexes? Are you suggesting that this would become documented behavior? I think they should be executed in alphabetical order like triggers. Yeah. We already have a comparable, and documented, behavior for triggers, so if we're going to do anything about this I'd vote for sorting by name (or more specifically, by strcmp()). 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] Order of enforcement of CHECK constraints?
On Fri, Mar 20, 2015 at 4:19 PM, Peter Geoghegan p...@heroku.com wrote: On Fri, Mar 20, 2015 at 12:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: We could fix it by, say, having CheckConstraintFetch() sort the constraints by name after loading them. What not by OID, as with indexes? Are you suggesting that this would become documented behavior? I think they should be executed in alphabetical order like triggers. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog: http://fabriziomello.github.io Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello Github: http://github.com/fabriziomello
Re: [HACKERS] Remove fsync ON/OFF as a visible option?
On 03/20/2015 10:47 AM, Peter Geoghegan wrote: On Fri, Mar 20, 2015 at 9:52 AM, Joshua D. Drake j...@commandprompt.com wrote: There are just as many people that are running with scissors that are now running (or attempting to run) our elephant in production. Does it make sense to remove fsync (and possibly full_page_writes) from such a visible place as postgresql.conf? -1 Anyone turning off fsync without even for a moment considering the consequences has only themselves to blame. This is an extremely valid point. Shall we hand them a loaded gun with the safety off too? We can't protect everyone but this is a rather simple change that hurts nobody. I am not going to raise a huge stink or anything but it seems rather simple. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc Now I get it: your service is designed for a customer base that grew up with Facebook, watches Japanese seizure robot anime, and has the attention span of a gnat. I'm not that user., Tyler Riddle -- 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] Order of enforcement of CHECK constraints?
On 21/03/15 08:15, Tom Lane wrote: My Salesforce colleagues noticed some tests flapping as a result of table CHECK constraints not always being enforced in the same order; ie, if a tuple insertion/update violates more than one CHECK constraint, it's not deterministic which one is reported. This is evidently because relcache.c's CheckConstraintFetch() just happily loads up the constraints in whatever order it happens to find them in pg_constraint. There's at least one regression test case where this can happen, so we've been lucky so far that this hasn't caused buildfarm noise. We could fix it by, say, having CheckConstraintFetch() sort the constraints by name after loading them. In principle the same problem could occur for domain CHECK constraints, though the odds of multiple CHECKs failing are probably a lot lower. Do people think this is worth fixing? regards, tom lane I think that this is a good idea, I would have implicitly assumed that it was deterministic. Additionally, people could then name CHECK constraints in a way to get whatever order they wanted. The documentation of CREATE TRIGGER says (reading Fabrizio's post inspired me to look it up): If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name. So I would have implicitly assumed the same for CHECK constraints, had I recently read that. So I think the current situation is a violation of POLA. Cheers, Gavin -- 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] Remove fsync ON/OFF as a visible option?
On Fri, Mar 20, 2015 at 7:29 PM, Joshua D. Drake j...@commandprompt.com wrote: I am not going to raise a huge stink or anything but it seems rather simple. ALTER SYSTEM is, if anything, more accessible and easier to do without reading comments and warnings than config files. If it were a green field then naming it data_integrity=off would do more to scare wave people away than hacking the config system. But making the change now after years seems kind of silly. I do wonder if it could be made a synonym for setting a large value of wal_writer_delay. That wouldn't really help Tom's concern about development but it might provide the same performance for the traditional use case of initial loading of large databases. That would require a lot of empirical testing though. -- greg
Re: [HACKERS] proposal: searching in array function - array_position
2015-03-20 18:47 GMT+01:00 Tom Lane t...@sss.pgh.pa.us: Alvaro Herrera alvhe...@2ndquadrant.com writes: Pavel Stehule wrote: I am thinking, so it is ok - it returns a offset, not position. So you can't use it as a subscript? That sounds unfriendly. Almost every function using this will be subtly broken. I concur; perhaps offset was the design intention, but it's wrong. The result should be a subscript. do you have any idea about name for this function? array_position is ok? Regards Pavel regards, tom lane
Re: [HACKERS] Remove fsync ON/OFF as a visible option?
* Joshua D. Drake (j...@commandprompt.com) wrote: On 03/20/2015 10:45 AM, Tom Lane wrote: I would object to that, because it would make it vastly more difficult to use fsync=off easily for development. How so? alter system fsync on/off (meta) restart That seems easier than editing the file and restarting? If it's that easy then I'm not sure why you're thinking it'd stop users from using it.. Now, if you wanted to remove it from the default/shipped postgresql.conf (but do nothing else), then that might reduce the number of people who change it without reading the docs, but we might actually be better off by improving the docs in postgresql.conf to carry a clear warning about the option. At the moment, one could look at our default postgresql.conf and the turns forced synchronization on or off and think it's something akin or somehow related to synchronous_commit (which is completely different, but the options are right next to each other..). How about a big warning around fsync and make it more indepenent from the options around it? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] vacuumdb: Help text for --analyze-only.
On Tue, Nov 18, 2014 at 12:40:51AM +0100, Mats Erik Andersson wrote: Hello there, I observe that the help text of vacuumdb for --analyze, --analyze-only, and --analyze-in-stages could do with a little clarification in order to be self-documenting and thus improve the user experience of vacuumdb. The problem is that the sole addition of the word only to an otherwise identical text for --analyze and --analyze-only seems rather obscure. My suggestion follows. Patch applied. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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: multivariate statistics / proof of concept
Hello, On 20.3.2015 09:33, Kyotaro HORIGUCHI wrote: Hello, Patch 0001 needs changes for OIDs since my patch was committed. The attached is compatible with current master. Thanks. I plan to submit a new version of the patch in a few days, with significant progress in various directions. I'll have to rebase to current master before submitting the new version anyway (which includes fixing duplicate OIDs). And I tried this like this, and got the following error on analyze. But unfortunately I don't have enough time to investigate it now. postgres=# create table t1 (a int, b int, c int); insert into t1 (select a/ 1, a / 1, a / 1 from generate_series(0, 9) a); postgres=# analyze t1; ERROR: invalid memory alloc request size 1485176862 Interesting - particularly because this does not involve any multivariate stats. I can't reproduce it with the current version of the patch, so either it's unrelated, or I've fixed it since posting the last version. regards -- Tomas Vondrahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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
