Re: [HACKERS] [BUGS] BUG #14244: wrong suffix for pg_size_pretty()
On 07/30/2016 11:16 AM, Bruce Momjian wrote: On Sat, Jul 30, 2016 at 10:35:58AM -0400, Tom Lane wrote: Greg Starkwrites: I agree that a GUC and new functions are overkill --- we should just decide on the format we want to output and what to support for input. As logical as the IEC format appears, I just don't think the Ki/Mi/Gi prefixes are used widely enough for us to use it --- I think it will cause too many problem reports: https://en.wikipedia.org/wiki/Binary_prefix I have developed two possible patches for PG 10 --- the first one merely allows "KB" to be used in addition to the existing "kB", and documents this as an option. The second patch does what Tom suggests above by outputting only "KB", and it supports "kB" for backward compatibility. What it doesn't do is to allow arbitrary case, which I think would be a step backward. The second patch actually does match the JEDEC standard, except for allowing "kB". I also just applied a doc patch that increases case and spacing consistency in the use of kB/MB/GB/TB. +1 -- Command Prompt, Inc. http://the.postgres.company/ +1-503-667-4564 PostgreSQL Centered full stack support, consulting and development. Everyone appreciates your honesty, until you are honest with them. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Combining hash values
Hi hackers, Andres Freund asked me off list what I thought about this part of execGrouping.c, which builds a hash from the hashes of several datums in a loop: /* rotate hashkey left 1 bit at each step */ hashkey = (hashkey << 1) | ((hashkey & 0x8000) ? 1 : 0); ... hashkey ^= hkey; I think we should consider improving it and putting it somewhere central. 1. The same code also appears in nodeHash.c, and we also need to do the same thing in a couple of new projects that my EDB colleagues and I are working on for proposal as 10.0 features, based on DSM-backed hash tables. So I think it would make sense to define a hash_combine function or macro to be reused by all of such places rather than repeating it everywhere. 2. I suspect that this algorithm for combining hashes is weak, and could amplify weaknesses in the hash functions feeding it. Compare Boost's hash_combine, which does some more work before XORing: seed ^= hash_value(v) + 0x9e3779b9 + (seed << 6) + (seed >> 2); That constant approximates the golden ratio (as a fraction of the 32 bit hash space), and it also appears in our hash_any and hash_uint32 functions. I don't claim to understand the mathematics but I think this may have to do with TACP section 6, theorem S and exercises 8 and 9, though I'm not sure if it's being used for the same purpose in algorithms that add it (is it just some random bits? [1][2]) and algorithms that multiply by it [3][4]. Perhaps we could write a reusable hash_combine function/macro using existing code from hashfunc.c, or use the formula from Boost, or something else, to improve the quality of our hash combinations. It would be very interesting to hear from someone with expertise in this area. Hash indexes don't currently support multiple column keys. If they ever do in future, they would also benefit from high quality combination. Assuming we'd use the same algorithm there too, changing the combination algorithm after we start persisting the results to disk in hash indexes would obviously be difficult. There doesn't seem to be any reason why we can't change the algorithm before then. [1] http://stackoverflow.com/questions/4948780/magic-number-in-boosthash-combine [2] https://xkcd.com/221/ [3] http://cstheory.stackexchange.com/questions/9639/how-did-knuth-derive-a [4] http://community.haskell.org/~simonmar/base/src/Data-HashTable.html -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hash indexes and effective_cache_size in CREATE INDEX documentation
The CREATE INDEX documentation states: "For hash indexes, the value of effective_cache_size is also relevant to index creation time: PostgreSQL will use one of two different hash index creation methods depending on whether the estimated index size is more or less than effective_cache_size. For best results, make sure that this parameter is also set to something reflective of available memory, and be careful that the sum of maintenance_work_mem and effective_cache_size is less than the machine's RAM less whatever space is needed by other programs." The hash index build process does not actually care about effective_cache_size at all -- this extract may have been written with the intent of describing the threshold at which a hash tuplesort is used to build a hash index, something that is based on shared_buffers (or, in 9.6, maintenance_work_mem). OTOH, GiST index builds do consider effective_cache_size, as noted elsewhere on the CREATE INDEX page. I was sure that the hash index behavior with regard to using tuplesort was not described in the documentation (this came up recently during discussion of what became commit 9563d5b5), and have said so on list. I'm now not so sure that that's actually the case. -- 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] sslmode=require fallback
On Fri, Jul 29, 2016 at 11:27:06AM -0400, Peter Eisentraut wrote: > On 7/29/16 11:13 AM, Bruce Momjian wrote: > > Yes, I am thinking of a case where Postgres is down but a malevolent > > user starts a Postgres server on 5432 to gather passwords. Verifying > > against an SSL certificate would avoid this problem, so there is some > > value in using SSL on localhost. (There is no such security available > > for Unix-domain socket connections.) > > Sure, there is the requirepeer connection option for that. Oh, nice, I had not seen that. -- Bruce Momjianhttp://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] [BUGS] BUG #14244: wrong suffix for pg_size_pretty()
On Sat, Jul 30, 2016 at 10:35:58AM -0400, Tom Lane wrote: > Greg Starkwrites: > > I think Bruce's summary is a bit revisionist. > > I would say it's a tempest in a teapot. > > What I think we should do is accept "kb" and the rest case-insensitively, > print them all in all-upper-case always, and tell standards pedants > to get lost. The idea of introducing either a GUC or new function names > is just silly; it will cause far more confusion and user code breakage > than will result from just leaving well enough alone. I agree that a GUC and new functions are overkill --- we should just decide on the format we want to output and what to support for input. As logical as the IEC format appears, I just don't think the Ki/Mi/Gi prefixes are used widely enough for us to use it --- I think it will cause too many problem reports: https://en.wikipedia.org/wiki/Binary_prefix I have developed two possible patches for PG 10 --- the first one merely allows "KB" to be used in addition to the existing "kB", and documents this as an option. The second patch does what Tom suggests above by outputting only "KB", and it supports "kB" for backward compatibility. What it doesn't do is to allow arbitrary case, which I think would be a step backward. The second patch actually does match the JEDEC standard, except for allowing "kB". I also just applied a doc patch that increases case and spacing consistency in the use of kB/MB/GB/TB. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c new file mode 100644 index 6ac5184..40038ac *** a/src/backend/utils/misc/guc.c --- b/src/backend/utils/misc/guc.c *** typedef struct *** 694,700 #error XLOG_SEG_SIZE must be between 1MB and 1GB #endif ! static const char *memory_units_hint = gettext_noop("Valid units for this parameter are \"kB\", \"MB\", \"GB\", and \"TB\"."); static const unit_conversion memory_unit_conversion_table[] = { --- 694,700 #error XLOG_SEG_SIZE must be between 1MB and 1GB #endif ! static const char *memory_units_hint = gettext_noop("Valid units for this parameter are \"kB\"/\"KB\", \"MB\", \"GB\", and \"TB\"."); static const unit_conversion memory_unit_conversion_table[] = { *** convert_to_base_unit(int64 value, const *** 5322,5328 for (i = 0; *table[i].unit; i++) { if (base_unit == table[i].base_unit && ! strcmp(unit, table[i].unit) == 0) { if (table[i].multiplier < 0) *base_value = value / (-table[i].multiplier); --- 5322,5331 for (i = 0; *table[i].unit; i++) { if (base_unit == table[i].base_unit && ! (strcmp(unit, table[i].unit) == 0 || ! /* support the JEDEC standard which uses "KB" for 1024 */ ! (strcmp(unit, "KB") == 0 && ! strcmp(table[i].unit, "kB") == 0))) { if (table[i].multiplier < 0) *base_value = value / (-table[i].multiplier); diff --git a/configure b/configure new file mode 100755 index b49cc11..8466e5a *** a/configure --- b/configure *** Optional Packages: *** 1502,1511 --with-libs=DIRSalternative spelling of --with-libraries --with-pgport=PORTNUM set default port number [5432] --with-blocksize=BLOCKSIZE ! set table block size in kB [8] --with-segsize=SEGSIZE set table segment size in GB [1] --with-wal-blocksize=BLOCKSIZE ! set WAL block size in kB [8] --with-wal-segsize=SEGSIZE set WAL segment size in MB [16] --with-CC=CMD set compiler (deprecated) --- 1502,1511 --with-libs=DIRSalternative spelling of --with-libraries --with-pgport=PORTNUM set default port number [5432] --with-blocksize=BLOCKSIZE ! set table block size in KB [8] --with-segsize=SEGSIZE set table segment size in GB [1] --with-wal-blocksize=BLOCKSIZE ! set WAL block size in KB [8] --with-wal-segsize=SEGSIZE set WAL segment size in MB [16] --with-CC=CMD set compiler (deprecated) *** case ${blocksize} in *** 3550,3557 32) BLCKSZ=32768;; *) as_fn_error $? "Invalid block size. Allowed values are 1,2,4,8,16,32." "$LINENO" 5 esac ! { $as_echo "$as_me:${as_lineno-$LINENO}: result: ${blocksize}kB" >&5 ! $as_echo "${blocksize}kB" >&6; } cat >>confdefs.h <<_ACEOF --- 3550,3557 32) BLCKSZ=32768;; *) as_fn_error $? "Invalid block size. Allowed values are 1,2,4,8,16,32." "$LINENO" 5 esac ! { $as_echo "$as_me:${as_lineno-$LINENO}: result: ${blocksize}KB" >&5 ! $as_echo "${blocksize}KB" >&6; } cat >>confdefs.h <<_ACEOF *** case
Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)
On 7/30/16 10:47 AM, Tom Lane wrote: > Pavel Stehulewrites: >> But there are some patterns used with work with temp tables,that should not >> working, and we would to decide if we prepare workaround or not. > >> -- problematic pattern (old code) >> IF NOT EXISTS(SELECT * FROM pg_class WHERE ) THEN >> CREATE TEMP TABLE xxx() >> ELSE >> TRUNCATE TABLE xxx; >> END IF; > >> -- modern patter (new code) >> BEGIN >> TRUNCATE TABLE xxx; >> EXCEPTION WHEN . THEN >> CREATE TEMP TABLE(...) >> END; > > If the former stops working, that's a sufficient reason to reject the > patch: it hasn't been thought through carefully enough. The key reason > why I don't think that's negotiable is that if there aren't (apparently) > catalog entries corresponding to the temp tables, that will almost > certainly break many things in the backend and third-party extensions, > not only user code patterns like this one. We'd constantly be fielding > bug reports that "feature X doesn't work with temp tables anymore". > > In short, I think that the way to make something like this work is to > figure out how to have "virtual" catalog rows describing a temp table. > Or maybe to partition the catalogs so that vacuuming away temp-table > rows is easier/cheaper than today. In addition the latter pattern burns an xid which can be a problem for high-volume databases. How about CREATE TEMP TABLE IF NOT EXISTS...? -- -David da...@pgmasters.net -- 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] [BUGS] BUG #14244: wrong suffix for pg_size_pretty()
On Sat, Jul 30, 2016 at 10:35 AM, Tom Lanewrote: > Greg Stark writes: > > I think Bruce's summary is a bit revisionist. > > I would say it's a tempest in a teapot. > > What I think we should do is accept "kb" and the rest case-insensitively, > print them all in all-upper-case always, and tell standards pedants > to get lost. The idea of introducing either a GUC or new function names > is just silly; it will cause far more confusion and user code breakage > than will result from just leaving well enough alone. > I wouldn't mind fixing case sensitivity in the process...but I don't think we need to touch the GUC infrastructure at all. For a product that has a reasonably high regard for the SQL standard I'd like to at least keep an open mind about other relevant standards - and if accommodation is as simple as writing a new function I'd see no reason to reject such a patch. pg_size_pretty never did seem like a good name for a function with its behavior...lets be open to accepting an improved version without a pg_ prefix. We could even avoid a whole new function and add an "iB" template pattern to the to_char function - although I'm not sure that wouldn't be more confusing than helpful in practice. David J.
Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)
Pavel Stehulewrites: > But there are some patterns used with work with temp tables,that should not > working, and we would to decide if we prepare workaround or not. > -- problematic pattern (old code) > IF NOT EXISTS(SELECT * FROM pg_class WHERE ) THEN > CREATE TEMP TABLE xxx() > ELSE > TRUNCATE TABLE xxx; > END IF; > -- modern patter (new code) > BEGIN > TRUNCATE TABLE xxx; > EXCEPTION WHEN . THEN > CREATE TEMP TABLE(...) > END; If the former stops working, that's a sufficient reason to reject the patch: it hasn't been thought through carefully enough. The key reason why I don't think that's negotiable is that if there aren't (apparently) catalog entries corresponding to the temp tables, that will almost certainly break many things in the backend and third-party extensions, not only user code patterns like this one. We'd constantly be fielding bug reports that "feature X doesn't work with temp tables anymore". In short, I think that the way to make something like this work is to figure out how to have "virtual" catalog rows describing a temp table. Or maybe to partition the catalogs so that vacuuming away temp-table rows is easier/cheaper than today. 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] [BUGS] BUG #14244: wrong suffix for pg_size_pretty()
Greg Starkwrites: > I think Bruce's summary is a bit revisionist. I would say it's a tempest in a teapot. What I think we should do is accept "kb" and the rest case-insensitively, print them all in all-upper-case always, and tell standards pedants to get lost. The idea of introducing either a GUC or new function names is just silly; it will cause far more confusion and user code breakage than will result from just leaving well enough alone. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)
On 07/30/2016 06:49 AM, Pavel Stehule wrote: 1) I wonder whether the FAST makes sense - does this really change the performance significantly? IMHO you only move the catalog rows to memory, so why should the tables be any faster? I also believe this conflicts with SQL standard specification of CREATE TABLE. Probably has zero value to have slow and fast temp tables (from catalogue cost perspective). So the FAST implementation should be used everywhere. But there are some patterns used with work with temp tables,that should not working, and we would to decide if we prepare workaround or not. -- problematic pattern (old code) IF NOT EXISTS(SELECT * FROM pg_class WHERE ) THEN CREATE TEMP TABLE xxx() ELSE TRUNCATE TABLE xxx; END IF; I'd argue that if you mess with catalogs directly, you're on your own. Not only it's fragile, but this pattern is also prone to race conditions (although a concurrent session can't create a conflicting temporary table). -- modern patter (new code) BEGIN TRUNCATE TABLE xxx; EXCEPTION WHEN . THEN CREATE TEMP TABLE(...) END; In this case we can use GUC, because visible behave should be same. What GUC? The benefit of zero catalogue cost temp tables is significant - and for some larger applications the temp tables did hard performance issues. Yeah, catalog bloat is a serious issue in such cases, and it's amplified by indexes created on the temporary tables. Some other random notes: 1. With this code should not be hard to implement global temp tables - shared persistent structure, temp local data - significant help for any who have to migrate from Oracle. The patch moves in pretty much the opposite direction - if anything, it'll make it more difficult to implement global temporary tables, because it removes the definitions from the catalog, thus impossible to share by catalogs. To get global temporary tables, I think the best approach would be to share the catalog definition and only override the filename. Or something like that. 2. This should to work on slaves - it is one of ToDo No, it does not work on slaves, because it still does a read-write transaction. test=# begin read only; BEGIN test=# create fast temporary table x (id int); ERROR: cannot execute CREATE TABLE in a read-only transaction No idea how difficult it'd be to make it work. 3. I didn't see support for memory store for column's statistics. Some separate questions is about production statistics - pg_stat_user_table, .. That seems to work (both analyze and pg_stat_user_tables). Not sure where it's in the code, and I'm not willing to reverse engineer it. regards -- Tomas Vondra http://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] [BUGS] BUG #14244: wrong suffix for pg_size_pretty()
On Sat, Jul 30, 2016 at 2:47 AM, David G. Johnstonwrote: > After bouncing on this for a bit I'm inclined to mark the bug itself "won't > fix" but introduce a "to_binary_iso" function (I'm hopeful a better name > will emerge...) that will output a number using ISO binary suffixes. I > would document this under 9.8 "data type formatting functions" instead of > within system functions. I think Bruce's summary is a bit revisionist. All these standards are attempts to reconcile two different conflicting traditions that have been conflicting for decades. There's a conflict for a reason though and the tradition of using powers of 2 is well-ingrained in plenty of practices and software, not just Postgres. Personally I'm pretty satisfied with the current mode of operation because I think powers of 2 are vastly more useful and more likely to be what the user actually wants. You would be hard pressed to find any users actually typing KiB or MiB in config files and being surprised they don't work or any users typing work_mem=100MB and being surprised that they're not getting 95.367 MiB. If you really want to support a strict interpretation of the SI standards then I don't see anything wrong with having a GUC. It doesn't change the semantics of SQL parsing so the worst-case downside is that if you change the setting and then reload a config file or if you move a setting from one place in a config file to another the interpretation of the config file would change. The best practice would probably be to set this config at the top of the config file and nowhere else. I would suggest having a GUC like "strict_si_units" with false as the default. If it's true then units like KiB and KB are both accepted and mean different things. If it's false then still accept both but treat them as synonyms meaning powers of 2. This means users who don't care can continue using convenient powers of 2 everywhere without thinking about it and users who do can start using the new-fangled SI units (and have the pitfall of accidentally specifying in units of powers of 10). For outputs like pg_size_pretty, SHOW, and pg_settings you could either say to always use KiB so that the outputs are always correct to use regardless of the setting of strict_si_units or you could have it print KB et al when strict_si_units is false -- the latter have the advantage that outputs could be copied to older versions safely but the disadvantage that if you change the setting then the interpretation of existing config files change. I think it would be better to print KiB/MiB etc always. I suppose there's the alternative of trying to guess which unit results in the most concise display but that seems unnecessarily baroque and likely to just hide mistakes rather than help. -- greg -- 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] old_snapshot_threshold allows heap:toast disagreement
On Fri, Jul 29, 2016 at 1:10 AM, Robert Haaswrote: > On Wed, Jul 27, 2016 at 7:26 PM, Andres Freund wrote: > > New version attached. > +static inline void +InitToastSnapshot(Snapshot snapshot, XLogRecPtr lsn) +{ + snapshot->satisfies = HeapTupleSatisfiesToast; + snapshot->lsn = lsn; +} Here, don't you need to initialize whenTaken as that is also used in TestForOldSnapshot_impl() to report error "snapshot too old". -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dumping extensions having sequences with 9.6beta3
On Sat, Jul 30, 2016 at 5:47 AM, Stephen Frostwrote: > What we need to be doing here is combining the set of components that > the sequence has been marked with and the set of components that the > table has been marked with, not trying to figure out if the sequence is > a member of an extension or not and changing what to do in those cases, > that's checkExtensionMembership()'s job, really. OK, thanks for the confirmation. I have been playing a bit with your patch and it is correctly dumping ACLs and policies that are modified after creating an extension. So that looks good to me. > Attached is a patch implementing this and which passes the regression > tests you added and a couple that I added for the non-extension case. > I'm going to look at adding a few more regression tests and if I don't > come across any issues then I'll likely push the fix sometime tomorrow. +* table will be equivilantly marked. s/equivilantly/equivalently/. By the way, I noticed 3 places in dumpProcLang and 1 place in dumpSequence where dobj.dump is used in a test but it is not directly compared with DUMP_COMPONENT_NONE. Perhaps you'd want to change that as well.. -- 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] Re: GiST optimizing memmoves in gistplacetopage for fixed-size updates [PoC]
Here is BRIN-compatible version of patch. Now function PageIndexTupleOverwrite consumes tuple size as a parameter, this behavior is coherent with PageAddItem. Also, i had to remove asserstion that item has a storage in the loop that adjusts line pointers. It would be great if someone could check that place (commented Assert(ItemIdHasStorage(ii)); in PageIndexTupleOverwrite). I suspect that there might be a case when linp's should not be adjusted. Best regards, Andrey Borodin, Octonica & Ural Federal University. PageIndexTupleOverwrite v6.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