[HACKERS] varchar_transform
when did use varchar_transform function? src/backend/uitls/adt/varchar.c.
Re: [HACKERS] jsonb and nested hstore
All,
Here's a draft cleanup on the JSON section of the Datatype docs. Since
there's been a bunch of incremental patches on this, I just did a diff
against HEAD.
I looked over json-functions a bit, but am not clear on what needs to
change there; the docs are pretty similar to other sections of
Functions, and if they're complex it's because of the sheer number of
JSON-related functions.
Anyway, this version of datatypes introduces a comparison table, which I
think should make things a bit clearer for users.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 00ccbe1..4baefb6 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -13,7 +13,7 @@
- PostgreSQL has a rich set of native data
+ PostgreSQL has a rich set of native data<
types available to users. Users can add new types to
PostgreSQL using the command.
@@ -139,7 +139,13 @@
json
- JSON data
+ JSON data, varlena format
+
+
+
+ jsonb
+
+ JSON data, binary structured format
@@ -3156,7 +3162,7 @@ SELECT person.name, holidays.num_weeks FROM person, holidays
coordinates, as floating-point numbers.
-
+f
Points are output using the first syntax.
@@ -4233,27 +4239,101 @@ SET xmloption TO { DOCUMENT | CONTENT };
- JSON Type
+ JSON Types
JSON
+
+JSONB
+
+
-The json data type can be used to store JSON (JavaScript
-Object Notation) data, as specified in http://www.ietf.org/rfc/rfc4627.txt";>RFC 4627. Such
-data can also be stored as text, but the
-json data type has the advantage of checking that each
-stored value is a valid JSON value. There are also related support
+JSON data types are for storing JSON (JavaScript Object Notation)
+data, as specified in http://www.ietf.org/rfc/rfc4627.txt";
+>RFC 4627. Such data can also be stored as text,
+but the JSON data types have the advantage of checking that each
+stored value is a valid JSON value. There are also related support
functions available; see .
+There are two JSON data types: json and jsonb.
+Both accept identical sets of values as input. The difference is primarily
+a matter of storage. The json data type stores an exact
+copy of the input text, while the jsonb is stored in a decomposed
+binary format which limits reparsing and supports future index and operator features.
+
+
+
+ JSON and JSONB Comparison
+
+
+
+ Feature
+ JSON
+ JSONB
+
+
+
+
+
+
+ Storage Format
+ Varlena (text)
+ Binary structured, decomposed
+
+
+
+ Parsed On
+ Every use
+ Input only
+
+
+
+ Whitespace
+ Preserved
+ Normalized
+
+
+
+ Duplicate keys
+ Preserved
+ Removed (keeps last key)
+
+
+
+ Key ordering
+ Preserved
+ Normalized
+
+
+
+ Indexing
+ Function indexes only
+ Function and GIN indexes (with Hstore2 Extension)
+
+
+
+
+
+
+
+In general, most applications will find it advantageous to store JSON data
+as jsonb, as jsonb is more efficient for most purposes and will
+support future advanced json index, operator and search features. The
+json will primarily be useful for applications which need to
+preserve exact formatting of the input JSON, or users with existing
+json columns which they do not want to convert to
+jsonb.
+
+
+
PostgreSQL allows only one server encoding
-per database. It is therefore not possible for JSON to conform rigidly
-to the specification unless the server encoding is UTF-8. Attempts to
-directly include characters which cannot be represented in the server
+per database. It is therefore not possible for the JSON types to conform
+rigidly to the specification unless the server encoding is UTF-8. Attempts
+to directly include characters which cannot be represented in the server
encoding will fail; conversely, characters which can be represented in
the server encoding but not in UTF-8 will be allowed.
\u escapes are allowed regardless of the server
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [review] PostgreSQL Service on Windows does not start if data directory given is relative path
On 22 February 2014 06:16, MauMau Wrote:
Thanks for reviewing again.
> Please make small cosmetic changes so that make_absolute_path() follows
> the
> style of other parts. Then I'll make this ready for committer.
>
> (1)
> Add the function name in the comment as in:
>
> /*
> * make_absolute_path
> *
> * ...existing function descripton
> */
Added.
> (2)
> Add errno description as in:
>
> fprintf(stderr, _("could not get current working directory: %s\n",
> strerror(errno)));
Modified.
Please find the attached modified patch.
Thanks and Regards,
Kumar Rajeev Rastogi
pgctl_win32service_rel_dbpath_v6.patch
Description: pgctl_win32service_rel_dbpath_v6.patch
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: contrib/cache_scan (Re: [HACKERS] What's needed for cache-only table scan?)
On Fri, Feb 21, 2014 at 2:19 AM, Kohei KaiGai wrote: > Hello, > > The attached patch is a revised one for cache-only scan module > on top of custom-scan interface. Please check it. > Thanks for the revised patch. Please find some minor comments. 1. memcpy(dest, tuple, HEAPTUPLESIZE); + memcpy((char *)dest + HEAPTUPLESIZE, + tuple->t_data, tuple->t_len); For a normal tuple these two addresses are different but in case of ccache, it is a continuous memory. Better write a comment as even if it continuous memory, it is treated as different only. 2. + uint32 required = HEAPTUPLESIZE + MAXALIGN(tuple->t_len); t_len is already maxaligned. No problem of using it again, The required length calculation is differing function to function. For example, in below part of the same function, the same t_len is used directly. It didn't generate any problem, but it may give some confusion. 4. + cchunk = ccache_vacuum_tuple(ccache, ccache->root_chunk, &ctid); + if (pchunk != NULL && pchunk != cchunk) + ccache_merge_chunk(ccache, pchunk); + pchunk = cchunk; The merge_chunk is called only when the heap tuples are spread across two cache chunks. Actually one cache chunk can accommodate one or more than heap pages. it needs some other way of handling. 4. for (i=0; i < 20; i++) Better to replace this magic number with a meaningful macro. 5. "columner" is present in sgml file. correct it. 6. "max_cached_attnum" value in the document saying as 128 by default but in the code it set as 256. I will start regress and performance tests. I will inform you the same once i finish. Regards, Hari Babu Fujitsu Australia
Re: [HACKERS] jsonb and nested hstore
Teodor, Oleg: Some bitrot on the nested-hstore patch on current HEAD, possibly due to the recent update release? josh@radegast:~/git/pg94$ patch -p1 -i nested-hstore-10.patch patching file contrib/hstore/.gitignore patching file contrib/hstore/Makefile patching file contrib/hstore/crc32.c patching file contrib/hstore/crc32.h patching file contrib/hstore/expected/hstore.out patching file contrib/hstore/expected/nested.out patching file contrib/hstore/expected/types.out patching file contrib/hstore/hstore--1.2--1.3.sql patching file contrib/hstore/hstore--1.2.sql patching file contrib/hstore/hstore--1.3.sql patching file contrib/hstore/hstore.control patching file contrib/hstore/hstore.h Hunk #2 FAILED at 13. Hunk #3 succeeded at 201 (offset 9 lines). 1 out of 3 hunks FAILED -- saving rejects to file contrib/hstore/hstore.h.rej patching file contrib/hstore/hstore_compat.c patching file contrib/hstore/hstore_gin.c patching file contrib/hstore/hstore_gist.c patching file contrib/hstore/hstore_gram.y patching file contrib/hstore/hstore_io.c Hunk #1 FAILED at 2. Hunk #2 succeeded at 23 (offset 1 line). Hunk #3 succeeded at 53 (offset 1 line). Hunk #4 FAILED at 63. Hunk #5 succeeded at 297 (offset 13 lines). Hunk #6 succeeded at 309 (offset 13 lines). Hunk #7 succeeded at 348 (offset 13 lines). Hunk #8 succeeded at 359 (offset 13 lines). Hunk #9 succeeded at 394 with fuzz 2 (offset 20 lines). Hunk #10 succeeded at 406 (offset 20 lines). Hunk #11 succeeded at 462 (offset 20 lines). Hunk #12 FAILED at 508. Hunk #13 succeeded at 551 (offset 21 lines). Hunk #14 succeeded at 561 (offset 21 lines). Hunk #15 succeeded at 651 (offset 21 lines). Hunk #16 succeeded at 696 (offset 21 lines). Hunk #17 succeeded at 703 (offset 21 lines). Hunk #18 succeeded at 767 (offset 21 lines). Hunk #19 succeeded at 776 (offset 21 lines). Hunk #20 succeeded at 791 (offset 21 lines). Hunk #21 succeeded at 807 (offset 21 lines). Hunk #22 succeeded at 820 (offset 21 lines). Hunk #23 succeeded at 856 (offset 21 lines). Hunk #24 FAILED at 1307. Hunk #25 FAILED at 1433. 5 out of 25 hunks FAILED -- saving rejects to file contrib/hstore/hstore_io.c.rej patching file contrib/hstore/hstore_op.c Hunk #1 FAILED at 25. Hunk #2 succeeded at 202 (offset 14 lines). Hunk #3 succeeded at 247 (offset 14 lines). Hunk #4 FAILED at 253. Hunk #5 succeeded at 756 (offset 15 lines). Hunk #6 succeeded at 799 (offset 15 lines). Hunk #7 succeeded at 885 (offset 15 lines). Hunk #8 succeeded at 1416 (offset 15 lines). Hunk #9 succeeded at 1605 (offset 15 lines). Hunk #10 succeeded at 1720 (offset 15 lines). 2 out of 10 hunks FAILED -- saving rejects to file contrib/hstore/hstore_op.c.rej -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GiST support for inet datatypes
On 02/06/2014 06:14 PM, Emre Hasegeli wrote: Third versions of the patches attached. They are rebased to the HEAD. In this versions, the bitncommon function is changed. included to network_gist.c to be able to compile it on FreeBSD. Geometric mean calculation for partial bucket match on the function inet_hist_inclusion_selectivity reverted back. It was something I changed without enough testing on the second revision of the patch. This version uses the maximum divider calculated from the boundaries of the bucket, like the first version. It is simpler and more reliable. Thanks for the updated patch. About the discussions about upgrading PostgreSQL, extensions and defaults I do not have any strong opinion. I think that this patch is useful even if it does not end up the default, but it would be a pity since the BTree GiST index is broken. Note: The patches do not apply anymore due to changes to src/backend/utils/adt/Makefile. I am not convinced of your approach to calculating the selectivity from the histogram. The thing I have the problem with is the clever trickery involved with how you handle different operator types. I prefer the clearer code of the range types with how calc_hist_selectivity_scalar is used. Is there any reason for why that approach would not work here or result in worse code? Currently we do not have histogram of the lower and upper bounds as the range types. Current histogram can be used nicely as the lower bound, but not the upper bound because the comparison is first on the common bits of the network part, then on the length of the network part. For example, 10.0/16 is defined as greater than 10/8. Using the histogram as the lower bounds of the networks is not enough to calculate selectivity for any of these operators. Using it also as the upper bounds is still not enough for the inclusion operators. The lengths of the network parts should taken into consideration in a way and it is what this patch does. Using separate histograms for the lower bounds, the upper bounds and the lengths of the network parts can solve all of these problems, but it is a lot of work. I see, thanks for the explanation. But I am still not very fond of how that code is written since I find it hard to verify the correctness of it, but have no better suggestions. I see from the tests that you still are missing selectivity functions for operators, what is your plan for this? This was because the join selectivity estimation functions. I set the geo_selfuncs for the missing ones. All tests pass with them. I want to develop the join selectivity function too, but not for this commit fest. All tests pass now. Excellent! Do you think the new index is useful even if you use the basic geo_selfuncs? Or should we wait with committing the patches until all selfuncs are implemented? -- Andreas Karlsson -- 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] SSL: better default ciphersuite
On Sat, Feb 22, 2014 at 08:31:14PM -0500, Peter Eisentraut wrote: > On 2/2/14, 7:16 AM, Marko Kreen wrote: > > On Thu, Dec 12, 2013 at 04:32:07PM +0200, Marko Kreen wrote: > >> Attached patch changes default ciphersuite to HIGH:MEDIUM:+3DES:!aNULL > >> and also adds documentation about reasoning for it. > > > > This is the last pending SSL cleanup related patch: > > > > https://commitfest.postgresql.org/action/patch_view?id=1310 > > > > Peter, you have claimed it as committer, do you see any remaining > > issues with it? > > I'm OK with this change on the principle of clarifying and refining the > existing default. But after inspecting the expanded cipher list with > the "openssl cipher" tool, I noticed that the new default re-enabled MD5 > ciphers. Was that intentional? Yes, kind of. First note that only RC4-MD5 is SSLv3+, rest are SSLv2-only suites. There are 2 points relevant about RC4-MD5: * Main reason MEDIUM was added is to get RC4, for compatibility. * ALthough MD5 is broken, TLS protocol uses HMAC-MD5 which is not. So RC4-MD5 is weak suite not because of MD5 but because of RC4. My conclusion is it's unnecessary to add '!MD5' to MEDIUM as that would not actually make things more secure. Instead 'MEDIUM' alone is enough to show that user will not get state-of-the-art-only suites. -- marko -- 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] often PREPARE can generate high load (and sometimes minutes long unavailability)
Hi, On 2014-02-23 20:04:39 +0100, Pavel Stehule wrote: > There is relative few very long ProcArrayLocks lwlocks > > This issue is very pathologic on fast computers with more than 8 CPU. This > issue was detected after migration from 8.4 to 9.2. (but tested with same > result on 9.0) I see it on devel 9.4 today actualized. > > When I moved PREPARE from cycle, then described issues is gone. But when I > use a EXECUTE IMMEDIATELY, then the issue is back. So it looks it is > related to planner, ... In addition to the issue Jeff mentioned, I'd suggest trying the same workload with repeatable read. That can do *wonders* because of the reduced number of snapshots. 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] Should PostgresMain() do a LWLockReleaseAll()?
On 2014-02-23 14:48:12 -0500, Tom Lane wrote: > Andres Freund writes: > > Currently the error handling of normal backends only does a > > LWLockReleaseAll() once CurrentTransactionState->state != TRANS_DEFAULT > > because it's called in AbortTransaction(). There's pretty damn few > > places that fiddle with lwlocks outside of a transaction command, but I > > still do wonder whether it'd wouldn't be a tad more robust to > > unconditionally do a LWLockReleaseAll(), just like other error handlers > > are doing? > > Why do that thing in particular, and not all the other things that > AbortTransaction() does? Because the other things in AbortTransaction() should really only be relevant inside a transaction, but there's valid reasons to use lwlocks outside one. E.g. I think that before Robert and I added a LWLockReleaseAll() to WalSndErrorCleanup() the whole walsender code wasn't protected. I am not entirely sure there's a real problem there in the backbranches, but it's a fair amount of code, espcially around base backups... 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: [BUGS] Re: [HACKERS] Re: BUG #9210: PostgreSQL string store bug? not enforce check with correct characterSET/encoding
Noah Misch writes: > On Fri, Feb 21, 2014 at 05:20:06PM -0500, Tom Lane wrote: >> ... However, I think there's a case to be >> made for adding the additional pg_verify_mbstr() calls in the back >> branches. We've been promising since around 8.3 that invalidly encoded >> data can't get into a database, and it's disturbing to find that there >> are leaks in that. > I had a dark corner of an app break from the 8.4-vintage change to make > E'abc\000def'::text raise an error rather than truncate the string. The old > behavior was clearly wrong, but I was still glad the change arrived in a major > release; the truncation happened to be harmless for that app. Adding > pg_verify_mbstr() calls creates a similar situation. Since I'm not hearing anybody else argue for a back-patch, I've committed this in HEAD only. 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] often PREPARE can generate high load (and sometimes minutes long unavailability)
2014-02-23 20:35 GMT+01:00 Jeff Janes : > On Sun, Feb 23, 2014 at 11:04 AM, Pavel Stehule > wrote: > >> Hello >> >> I got a example of code, that generate relatively high load with minimal >> connections. >> >> This code is +/- bad - it repeatedly generate prepare statement, but >> somewhere uses prepared statements as protections against SQL injections >> and they can use same use case. >> >> Pseudocode (I can send a test case privately): >> >> Script a: >> >> -- A,B are in RAM >> for i in 1 .. N loop >> insert into A values(); >> for j in 1 .. M loop >> insert into B values(); >> end loop; >> end loop; >> >> Script b: >> >> -- query is extremely fast - returns 0 or 1 rows usually >> 40 threads execute >> while true loop >> pr = PREPARE SELECT * FROM A LEFT JOIN B ON .. >> EXECUTE pr(...) >> sleep(10 ms) >> end loop >> > > > Digging through uncommitted tuples at the top or bottom of an index (which > happenings during planning, especially the planner of merge joins) is very > contentious. Tom proposed changing the snapshot used for planning to > Dirty, but the proposal didn't go anywhere because no one did the testing > to confirm that it solved the problem in the field. Perhaps you can help > do that. > > See: > > "[PERFORM] Performance bug in prepared statement binding in 9.2?" and > several related threads. > yes, it is very similar. Only it is little bit worse - on 16CPU it can produce a 20-60 minutes unavailability regards Pavel > > Cheers, > > Jeff >
Re: [HACKERS] often PREPARE can generate high load (and sometimes minutes long unavailability)
2014-02-23 20:35 GMT+01:00 Jeff Janes : > On Sun, Feb 23, 2014 at 11:04 AM, Pavel Stehule > wrote: > >> Hello >> >> I got a example of code, that generate relatively high load with minimal >> connections. >> >> This code is +/- bad - it repeatedly generate prepare statement, but >> somewhere uses prepared statements as protections against SQL injections >> and they can use same use case. >> >> Pseudocode (I can send a test case privately): >> >> Script a: >> >> -- A,B are in RAM >> for i in 1 .. N loop >> insert into A values(); >> for j in 1 .. M loop >> insert into B values(); >> end loop; >> end loop; >> >> Script b: >> >> -- query is extremely fast - returns 0 or 1 rows usually >> 40 threads execute >> while true loop >> pr = PREPARE SELECT * FROM A LEFT JOIN B ON .. >> EXECUTE pr(...) >> sleep(10 ms) >> end loop >> > > > Digging through uncommitted tuples at the top or bottom of an index (which > happenings during planning, especially the planner of merge joins) is very > contentious. Tom proposed changing the snapshot used for planning to > Dirty, but the proposal didn't go anywhere because no one did the testing > to confirm that it solved the problem in the field. Perhaps you can help > do that. > I am able to test some patches. Thank you for info Regards Pavel > > See: > > "[PERFORM] Performance bug in prepared statement binding in 9.2?" and > several related threads. > > Cheers, > > Jeff >
Re: [HACKERS] Should PostgresMain() do a LWLockReleaseAll()?
Andres Freund writes: > Currently the error handling of normal backends only does a > LWLockReleaseAll() once CurrentTransactionState->state != TRANS_DEFAULT > because it's called in AbortTransaction(). There's pretty damn few > places that fiddle with lwlocks outside of a transaction command, but I > still do wonder whether it'd wouldn't be a tad more robust to > unconditionally do a LWLockReleaseAll(), just like other error handlers > are doing? Why do that thing in particular, and not all the other things that AbortTransaction() does? The reason that other process main loops don't use AbortTransaction is that they don't run transactions. I don't think arguing from what they do is particularly relevant to PostgresMain. 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] often PREPARE can generate high load (and sometimes minutes long unavailability)
On Sun, Feb 23, 2014 at 11:04 AM, Pavel Stehule wrote: > Hello > > I got a example of code, that generate relatively high load with minimal > connections. > > This code is +/- bad - it repeatedly generate prepare statement, but > somewhere uses prepared statements as protections against SQL injections > and they can use same use case. > > Pseudocode (I can send a test case privately): > > Script a: > > -- A,B are in RAM > for i in 1 .. N loop > insert into A values(); > for j in 1 .. M loop > insert into B values(); > end loop; > end loop; > > Script b: > > -- query is extremely fast - returns 0 or 1 rows usually > 40 threads execute > while true loop > pr = PREPARE SELECT * FROM A LEFT JOIN B ON .. > EXECUTE pr(...) > sleep(10 ms) > end loop > Digging through uncommitted tuples at the top or bottom of an index (which happenings during planning, especially the planner of merge joins) is very contentious. Tom proposed changing the snapshot used for planning to Dirty, but the proposal didn't go anywhere because no one did the testing to confirm that it solved the problem in the field. Perhaps you can help do that. See: "[PERFORM] Performance bug in prepared statement binding in 9.2?" and several related threads. Cheers, Jeff
[HACKERS] often PREPARE can generate high load (and sometimes minutes long unavailability)
Hello I got a example of code, that generate relatively high load with minimal connections. This code is +/- bad - it repeatedly generate prepare statement, but somewhere uses prepared statements as protections against SQL injections and they can use same use case. Pseudocode (I can send a test case privately): Script a: -- A,B are in RAM for i in 1 .. N loop insert into A values(); for j in 1 .. M loop insert into B values(); end loop; end loop; Script b: -- query is extremely fast - returns 0 or 1 rows usually 40 threads execute while true loop pr = PREPARE SELECT * FROM A LEFT JOIN B ON .. EXECUTE pr(...) sleep(10 ms) end loop running both script together can produce high load with minimal number of executed queries. 354246.00 93.0% s_lock /usr/lib/postgresql/9.2/bin/postgres 10503.00 2.8% LWLockRelease /usr/lib/postgresql/9.2/bin/postgres 8802.00 2.3% LWLockAcquire /usr/lib/postgresql/9.2/bin/postgres 828.00 0.2% _raw_spin_lock [kernel.kallsyms] 559.00 0.1% _raw_spin_lock_irqsave [kernel.kallsyms] 340.00 0.1% switch_mm [kernel.kallsyms] 305.00 0.1% poll_schedule_timeout [kernel.kallsyms] 274.00 0.1% native_write_msr_safe [kernel.kallsyms] 257.00 0.1% _raw_spin_lock_irq [kernel.kallsyms] 238.00 0.1% apic_timer_interrupt [kernel.kallsyms] 236.00 0.1% __schedule [kernel.kallsyms] 213.00 0.1% HeapTupleSatisfiesMVCC With systemtap I got list of spin locks light weight locks lockname mode countavg (time) DynamicLocks Exclusive 2804 1025 DynamicLocks Shared106130 ProcArrayLock Exclusive 63 963551 ProcArrayLock Shared 50 4160 LockMgrLocks Exclusive 18159 IndividualLock Exclusive 2 7 There is relative few very long ProcArrayLocks lwlocks This issue is very pathologic on fast computers with more than 8 CPU. This issue was detected after migration from 8.4 to 9.2. (but tested with same result on 9.0) I see it on devel 9.4 today actualized. When I moved PREPARE from cycle, then described issues is gone. But when I use a EXECUTE IMMEDIATELY, then the issue is back. So it looks it is related to planner, ... Regards Pavel
[HACKERS] Should PostgresMain() do a LWLockReleaseAll()?
Hi, Currently the error handling of normal backends only does a LWLockReleaseAll() once CurrentTransactionState->state != TRANS_DEFAULT because it's called in AbortTransaction(). There's pretty damn few places that fiddle with lwlocks outside of a transaction command, but I still do wonder whether it'd wouldn't be a tad more robust to unconditionally do a LWLockReleaseAll(), just like other error handlers are doing? In comparison to the cost of a longjmp and the rest of error handling that ought to be nearly free. 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] gaussian distribution pgbench
Gaussian Pgbench v8 patch by Mitsumasa KONDO review & patch v9.
* The purpose of the patch is to allow a pgbench script to draw from normally
distributed or exponentially distributed integer values instead of uniformly
distributed.
This is a valuable contribution to enable pgbench to generate more realistic
loads, which is seldom uniform in practice.
* Very minor change
I have updated the patch (v9) based on Mitsumasa latest v8:
- remove one spurious space in the help message.
* Compilation
The patch applies cleanly and compiles against current head.
* Check
I have checked that the aid values are skewed depending on the
parameters by looking at the "aid" distribution in the "pgbench_history"
table after a run.
* Mathematical soundness
I've checked the mathematical soundness of the methods involved.
I'm fine with casting doubles to integers for having the expected
distribution on integers.
Although there is a retry loop for finding a suitable, the looping
probability is low thanks to the minimum threshold parameter required.
* Conclusion
I suggest to apply this patch which provide a useful and more realistic
testing capability to pgbench.
--
Fabien.diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index a836acf..35edd27 100644
--- a/contrib/pgbench/pgbench.c
+++ b/contrib/pgbench/pgbench.c
@@ -98,6 +98,9 @@ static int pthread_join(pthread_t th, void **thread_return);
#define LOG_STEP_SECONDS 5 /* seconds between log messages */
#define DEFAULT_NXACTS 10 /* default nxacts */
+#define MIN_GAUSSIAN_THRESHOLD 2.0 /* minimum threshold for gauss */
+#define MIN_EXPONENTIAL_THRESHOLD 2.0 /* minimum threshold for exp */
+
int nxacts = 0; /* number of transactions per client */
int duration = 0; /* duration in seconds */
@@ -169,6 +172,14 @@ bool is_connect; /* establish connection for each transaction */
bool is_latencies; /* report per-command latencies */
int main_pid; /* main process id used in log filename */
+/* gaussian distribution tests: */
+double stdev_threshold; /* standard deviation threshold */
+booluse_gaussian = false;
+
+/* exponential distribution tests: */
+double exp_threshold; /* threshold for exponential */
+bool use_exponential = false;
+
char *pghost = "";
char *pgport = "";
char *login = NULL;
@@ -330,6 +341,88 @@ static char *select_only = {
"SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
};
+/* --exponential case */
+static char *exponential_tpc_b = {
+ "\\set nbranches " CppAsString2(nbranches) " * :scale\n"
+ "\\set ntellers " CppAsString2(ntellers) " * :scale\n"
+ "\\set naccounts " CppAsString2(naccounts) " * :scale\n"
+ "\\setexponential aid 1 :naccounts :exp_threshold\n"
+ "\\setrandom bid 1 :nbranches\n"
+ "\\setrandom tid 1 :ntellers\n"
+ "\\setrandom delta -5000 5000\n"
+ "BEGIN;\n"
+ "UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
+ "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
+ "UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;\n"
+ "UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;\n"
+ "INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
+ "END;\n"
+};
+
+/* --exponential with -N case */
+static char *exponential_simple_update = {
+ "\\set nbranches " CppAsString2(nbranches) " * :scale\n"
+ "\\set ntellers " CppAsString2(ntellers) " * :scale\n"
+ "\\set naccounts " CppAsString2(naccounts) " * :scale\n"
+ "\\setexponential aid 1 :naccounts :exp_threshold\n"
+ "\\setrandom bid 1 :nbranches\n"
+ "\\setrandom tid 1 :ntellers\n"
+ "\\setrandom delta -5000 5000\n"
+ "BEGIN;\n"
+ "UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
+ "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
+ "INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
+ "END;\n"
+};
+
+/* --exponential with -S case */
+static char *exponential_select_only = {
+ "\\set naccounts " CppAsString2(naccounts) " * :scale\n"
+ "\\setexponential aid 1 :naccounts :exp_threshold\n"
+ "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
+};
+
+/* --gaussian case */
+static char *gaussian_tpc_b = {
+ "\\set nbranches " CppAsString2(nbranches) " * :scale\n"
+ "\\set ntellers " CppAsString2(ntellers) " * :scale\n"
+ "\\set naccounts " CppAsString2(naccounts) " * :scale\n"
+ "\\setgaussian aid 1 :naccounts :stdev_threshold\n"
+ "\\setrandom bid 1 :nbranches\n"
+ "\\setrandom tid 1 :ntellers\n"
+ "\\setrandom delta -5000 5000\n"
+ "BEGIN;\n"
+ "UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
+ "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
+ "UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;\n"
+ "UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :b
