Re: [HACKERS] disposition of remaining patches
On Fri, Feb 25, 2011 at 9:14 AM, Daniel Farina dan...@heroku.com wrote: Right now, as it stands, the syncrep patch will be happy as soon as the data has been fsynced to either B or A-prime; I don't think we can guarantee at any point that A-prime can become the leader, and feed B. - start A` up, replicating from A - shutdown B (now A nad A` are synchronous) now real quick: - shut down A - shut down A` -change configuration -start up A` -start up B Doesn`t this work? Greetings Marcin -- 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] why two dashes in extension load files
On Tue, Feb 15, 2011 at 9:16 PM, Peter Eisentraut pete...@gmx.net wrote: On mån, 2011-02-14 at 12:14 -0500, Tom Lane wrote: I guess the real question is what's Peter's concrete objection to the double-dash method? It just looks a bit silly and error prone. And other packaging systems have been doing without it for decades. how about : we use a single dash as the separator, and if the extension author insists on having a dash in the name, as a punishment he must duplicate the dash, i.e.: uuid--ossp-1.0--5.5.sql Greetings Marcin Mańk -- 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] ALTER EXTENSION UPGRADE, v3
On Fri, Feb 11, 2011 at 8:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: =?iso-8859-1?Q?K=E4=E4ri=E4inen_Anssi?= anssi.kaariai...@thl.fi writes: This has the side effect that you can also have downgrade scripts. I don't know if this is designed or just coincidental, so thought it would be worth mentioning. The worst case is that if you are upgrading from 1.2 to 2.0 the path is 1.2 - 1.1 - 2.0, even if there exists a path 1.2 - 1.8 - 1.9 - 2.0. This could potentially result in data loss, if the downgrade drops some columns or something like that. Hmm. That seems like it would require a rather pathological collection of upgrade scripts. In particular why would you have a one-step upgrade from 1.1 to 2.0 but no short path from 1.2? Say we have 20 versions, with up- and downgrade scripts between consecutive versions, and a fast path from 5 to 20. if we are at version 6, it would go 6-5-20. if 6-5 drops a table, we`re in trouble. Greetings Marcin Mańk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] a regression
Hello. I did: git clone git://git.postgresql.org/git/postgresql.git cd postgresql ./configure --prefix=/home/marcin/pg91 --enable-cassert --enable-debug make check which gave me the attached regression.diffs marcin@skpsms:~/postgresql$ gcc -v Using built-in specs. Target: x86_64-linux-gnu Configured with: ../src/configure -v --with-pkgversion='Debian 4.3.2-1.1' --with-bugurl=file:///usr/share/doc/gcc-4.3/README.Bugs --enable-languages=c,c++,fortran,objc,obj-c++ --prefix=/usr --enable-shared --with-system-zlib --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --enable-nls --with-gxx-include-dir=/usr/include/c++/4.3 --program-suffix=-4.3 --enable-clocale=gnu --enable-libstdcxx-debug --enable-objc-gc --enable-mpfr --enable-cld --enable-checking=release --build=x86_64-linux-gnu --host=x86_64-linux-gnu --target=x86_64-linux-gnu Thread model: posix gcc version 4.3.2 (Debian 4.3.2-1.1) marcin@skpsms:~/postgresql$ uname -a Linux skpsms 2.6.24-23-xen #1 SMP Mon Jan 26 03:09:12 UTC 2009 x86_64 GNU/Linux Greetings Marcin Mańk regression.diffs 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] a regression
On Tue, Jan 25, 2011 at 5:46 PM, marcin mank marcin.m...@gmail.com wrote: Hello. I did: git clone git://git.postgresql.org/git/postgresql.git cd postgresql ./configure --prefix=/home/marcin/pg91 --enable-cassert --enable-debug make check which gave me the attached regression.diffs uh, this may have something to do with : marcin@skpsms:~/postgresql$ locale LANG=pl_PL.UTF-8 LC_CTYPE=pl_PL.UTF-8 LC_NUMERIC=pl_PL.UTF-8 LC_TIME=pl_PL.UTF-8 LC_COLLATE=pl_PL.UTF-8 LC_MONETARY=pl_PL.UTF-8 LC_MESSAGES=pl_PL.UTF-8 LC_PAPER=pl_PL.UTF-8 LC_NAME=pl_PL.UTF-8 LC_ADDRESS=pl_PL.UTF-8 LC_TELEPHONE=pl_PL.UTF-8 LC_MEASUREMENT=pl_PL.UTF-8 LC_IDENTIFICATION=pl_PL.UTF-8 Because LC_COLLATE=C make check passes. If this is expected, sorry for the noise. Greetings Marcin Mańk -- 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] Allowing multiple concurrent base backups
On Tue, Jan 11, 2011 at 8:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: On Tue, Jan 11, 2011 at 19:51, Tom Lane t...@sss.pgh.pa.us wrote: Seems like either one of these is fairly problematic in that you have to have some monstrous kluge to get the backup_label file to appear with the right name in the tarfile. How badly do we actually need this? I don't think the use-case for concurrent base backups is all that large in practice given the I/O hit it's going to involve. I think it can be done cleaner in the tar file injection - I've been chatting with Heikki offlist about that. Not sure, but I have a feeling it does. One point that I'm particularly interested to see how you'll kluge it is ensuring that the tarball contains only the desired temp data and not also the real $PGDATA/backup_label, should there be a normal base backup being done concurrently with the streamed one. The whole thing just seems too fragile and dangerous to be worth dealing with given that actual usage will be a corner case. *I* sure wouldn't trust it to work when the chips were down. Maybe if pg_start_backup() notices that there is another backup running should block waiting for another session to run pg_stop_backup() ? Or have a new function like pg_start_backup_wait() ? Considering that parallell base backups would be io-bound (or network-bound), there is little need to actually run them in parallell . Greetings Marcin -- 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 patch for parallel pg_dump
On Sun, Dec 5, 2010 at 7:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: IIRC, in old discussions of this problem we first considered allowing clients to pull down an explicit representation of their snapshot (which actually is an existing feature now, txid_current_snapshot()) and then upload that again to become the active snapshot in another connection. Could a hot standby use such a snapshot representation? I.e. same snapshot on the master and the standby? Greetings Marcin Mańk -- 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] Per-column collation
I can only look at the locales that the operating system provides. We could conceivably make some simplifications like stripping off the .utf8, but then how far do we go and where do we stop? Locale names on Windows look different too. But in general, how do you suppose we should map an operating system locale name to an acceptable SQL identifier? You might hope, for example, that we could look through the It would be nice if we could have some mapping of locale names bult in, so one doesn`t have to write alternative sql depending on DB server OS: select * from tab order by foo collate Polish, Poland select * from tab order by foo collate pl_PL.UTF-8 (that`s how it works now, correct?) Greetings Marcin Mańk -- 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] unlogged tables
Can (should ?) unlogged tables' contents survive graceful (non-crash) shutdown? Greetings Marcin Mańk -- 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] Hash support for arrays
On Sat, Oct 30, 2010 at 6:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: 3. To hash, apply the element type's hash function to each array element. Combine these values by rotating the accumulator left one bit at each step and xor'ing in the next element's hash value. Thoughts? In particular, is anyone aware of a better method for combining the element hash values? This would make the hash the same for arrays with elements 32 apart swapped. This is what boost does: http://www.systomath.com/include/Boost-1_34/doc/html/boost/hash_combine.html Greetings -- 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] Synchronization levels in SR
On Tue, Sep 7, 2010 at 5:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: We can *not* allow the slave to replay WAL ahead of what is known committed to disk on the master. The only way to make that safe is the compare-notes-and-ship-WAL-back approach that Robert mentioned. If you feel that decoupling WAL application is absolutely essential to have a credible feature, then you'd better bite the bullet and start working on the ship-WAL-back code. In the mode where it is not required that the WAL is applied (only sent to the slave / synced to slave disk) one alternative is to have a separate pointer to the last WAL record that can be safely applied on the slave. Then You can send the un-synced WAL to the slave (while concurrently syncing it on the master). When both the slave an the master sync complete, one can give the client a commit notification, increase the pointer, and send it to the slave (it would be a separate WAL record type I guess). In case of master failure, the slave can discard the un-applied WAL after the pointer. Greetings marcin -- 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] cross column correlation revisted
On Wed, Jul 14, 2010 at 5:13 PM, Robert Haas robertmh...@gmail.com wrote: 2010/7/14 Tom Lane t...@sss.pgh.pa.us: If the combination of columns is actually interesting, there might well be an index in place, or the DBA might be willing to create it. Indexes aren't free, though, nor even close to it. Still, I think we should figure out the underlying mechanism first and then design the interface afterwards. One idea I had was a way to say compute the MCVs and histogram buckets for this table WHERE predicate. If you can prove predicate for a particular query, you can use the more refined statistics in place of the full-table statistics. This is fine for the breast cancer case, but not so useful for the zip code/street name case (which seems to be the really tough one). One way of dealing with the zipcode problem is estimating NDST = count(distinct row(zipcode, street)) - i.e. multi-column ndistinct. Then the planner doesn`t have to assume that the selectivity of a equality condition involving both zipcode and city is a multiple of the respective selectivities. As a first cut it can assume that it will get count(*) / NDST rows, but there are ways to improve it. Greetings Marcin Mańk -- 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 for 9.1: WAL streaming from WAL buffers
Having said that, I do think we urgently need some high-level design discussion on how sync rep is actually going to handle this issue (perhaps on a new thread). If we can't resolve this issue, sync rep is going to be really slow; but there are no easy solutions to this problem in sight, so if we want to have sync rep for 9.1 we'd better agree on one of the difficult solutions soon so that work can begin. When standbys reconnect after a crash, they could send the ahead-of-the-master WAL to the master. This is an alternative to choosing the most-ahead standby as the new master, as suggested elsewhere. Greetings Marcin Mańk -- 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] Idea for getting rid of VACUUM FREEZE on cold pages
On Wed, Jun 9, 2010 at 12:35 AM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Tue, 2010-06-08 at 18:03 -0400, Robert Haas wrote: OK, yes, I see what you're getting at now. There are two possible ways to do freeze the tuples and keep the xmin: we can either rely on the PD_ALL_VISIBLE page-level bit (as I previously proposed) or we can additionally have a HEAP_XMIN_FROZEN bit as you propose here. I am not sure which way is better. Doing it at tuple level is more flexible and allows more aggressive freezing. It also works better with existing tuple visibility code. I agree, relying on a page-level bit (or field) is unpleasant in a number of ways. But none of this accomplishes a damn thing towards the original goal, which was to avoid an extra disk write associated with freezing (not to mention an extra write for setting the transaction-committed hint bit). Setting a bit is no cheaper from that standpoint than changing the xmin field. Could a tuple wih the bit set be considered frozen already? Would we actually ever need to rewrite the xmin, even for anti-wraparound reasons? Greetings Marcin -- 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] testing HS/SR - 1 vs 2 performance
On Wed, Apr 21, 2010 at 4:12 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2010-04-21 at 09:51 -0400, Robert Haas wrote: Adding an assertion isn't going to do much because it's unlikely anybody is going to be running for 2^31 transactions with asserts enabled. I think the assert is a good idea. If there's no real problem here, the assert won't trip. It's just a safety precaution. If you believe that, then I think you should add this to all the other places in the current server where that assumption is made without assertion being added. As a safety precaution. Is that not a good idea that (at least for dev-builds, like with enable-cassert) the xid counter start at like 2^31 - 1000 ? It could help catch some bugs. Greetings Marcin Mańk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Streaming rep - why log shipping is necessary?
Hello, I was reading the SR docs, and have the following question: Is there a fundamental reason why archive_command etc. is required in streaming replication mode? Can`t setting up the standby be more like: pg_start_streaming_backup() on the master (this will be queuing up files in pg_xlog) copy the data dir set up the slave to connect to the master via streaming protocol set up the master to allow connections from the slave start slave (slave pulls the necessary WAL records from the master via streaming, and signals the master that it`s done backing up) When standby starts accepting connections, we know that the standby is OK. archive_command, restore_command, etc. would be configured empty in this mode. The failure mode for this is the pg_xlog directory filling up on the master before the backup is done. But then, we can tell people to use the more combersome, current setup. Greetings Marcin Mańk -- 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] Streaming rep - why log shipping is necessary?
On Thu, Feb 25, 2010 at 10:08 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: the standby needs to fall back to the archive if it falls behind so that the WAL files it needs have already been recycled in the master. Oh, so the master does not have to keep track of the state of the standbys. That`s a nice design. If you're adventurous enough, it's actually possible to set an archive_command that checks the status of the standby and returns failure as long as the standby still needs the given WAL segment. That way the primary doesn't recycle segments that are still needed by the standby, and you can get away without restore_command in the standby. That would be a nice addition to pg_standby, like pg_standby --check-streaming-standby postgres:qwe...@10.0.0.1 --check-streaming-standby postgres:qwe...@10.0.0.2:5433 Greetings Marcin Mańk -- 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] [COMMITTERS] pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
On Mon, Feb 15, 2010 at 9:36 AM, Andres Freund and...@anarazel.de wrote: On Monday 15 February 2010 08:13:32 Tom Lane wrote: Actually, looking closer, some of the Windows machines started failing after the *earlier* patch to add directory fsyncs. And not only the windows machines. Seems sensible to add a configure check whether directory-fsyncing works. It looks like a thing that can be filesystem-dependent. Maybe a kind of runtime check? Greetings Marcin Mańk -- 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] [COMMITTERS] pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
On Mon, Feb 15, 2010 at 11:02 AM, Andres Freund and...@anarazel.de wrote: Hi Marcin, Sounds rather unlikely to me. Its likely handled at an upper layer (vfs in linux' case) and only overloaded when an optimized implementation is available. Which os do you see implementing that only on a part of the filesystems? I have a Windows XP dev machine, which runs virtualbox, which runs ubuntu, which mounts a windows directory through vboxfs fsync does error out on directories inside that mount. btw: 8.4.2 initdb won`t work there too, So this is not a regression. The error is: DEBUG: creating and filling new WAL file LOG: could not link file pg_xlog/xlogtemp.2367 to pg_xlog/0001 (initialization of log file 0, segment 0): Operation not permitted FATAL: could not open file pg_xlog/0001 (log file 0, segment 0): No such file or directory But I would not be that sure that eg. NFS or something like that won`t complain. Ignoring the return code seems the right choice. Greetings Marcin Mańk -- 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] [COMMITTERS] pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
Yes, the issue with initdb failing is unrelated (and I have no problem about the fs being unsupported). But fsync still DOES fail on directories from the mount. But I would not be that sure that eg. NFS or something like that won`t complain. It does not. What if someone mounts a NFS share from a system that does not support directory fsync (per buildfarm: unixware, AIX) on Linux? I agree that this is asking for trouble, but... Greetings Marcin Mańk -- 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 9.0 and standard_conforming_strings
A certain prominent web framework has a nasty SQL injection bug when PG is configured with SCS. This bug is not present without SCS (details per email for interested PG hackers). I say, hold it off. Greetings Marcin Mańk -- 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] bug: fuzzystrmatch levenshtein is wrong
On Thu, Dec 10, 2009 at 3:00 AM, Robert Haas robertmh...@gmail.com wrote: Done. Yeah, my first commit! Great! Also, thanks for getting this in 8.4.2. Otherwise I would have to compile this on Windows myself, which is no fun. About the tabs vs spaces problem - I`ve decided that copying the patch from a remote machine is best done by selecting it in the terminal and pasting into a text file. Don`t do that :) Greetings Marcin Mańk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] bug: fuzzystrmatch levenshtein is wrong
The current behavior of levenshtein(text,text,int,int,int) is wrong. Consider:
leki_dev=# select levenshtein('','a',2,4,5);
levenshtein
-
1
(1 row)
leki_dev=# select levenshtein('a','',2,4,5);
levenshtein
-
1
(1 row)
leki_dev=# select levenshtein('aa','a',2,4,5);
levenshtein
-
1
(1 row)
leki_dev=# select levenshtein('a','aa',2,4,5);
levenshtein
-
1
(1 row)
versus (after patch)
postgres=# select levenshtein('','a',2,4,5);
levenshtein
-
2
(1 row)
postgres=# select levenshtein('a','',2,4,5);
levenshtein
-
4
(1 row)
postgres=# select levenshtein('aa','a',2,4,5);
levenshtein
-
4
(1 row)
postgres=# select levenshtein('a','aa',2,4,5);
levenshtein
-
2
(1 row)
patch attached.
Greetings
Marcin Mańk
levenshtein.diff
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] bug: fuzzystrmatch levenshtein is wrong
also there is integer overflow:
postgres=# select levenshtein('','',1,10,1);
levenshtein
-
-1179869184
(1 row)
should we reject arguments greater than,say, 1 ?
maximum input length is 255 currently, so the maximum numbers involved
would be about 1*255*2
This would leave some breathing room if we wanted to increase the
maximum input string length.
Greetings
Marcin
--
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] bug: fuzzystrmatch levenshtein is wrong
On Tue, Dec 8, 2009 at 4:10 AM, Robert Haas robertmh...@gmail.com wrote: The current behavior of levenshtein(text,text,int,int,int) is wrong. Consider: Is this the same problem as bug #5098? Yes. Exact same change, plus the shortcut evaluation (for when one of the inputs is empty) was also wrong. I fixed that too. Greetings Maricn Mańk -- 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] Block-level CRC checks
On Mon, Nov 30, 2009 at 9:27 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Simon Riggs wrote: Proposal * We reserve enough space on a disk block for a CRC check. When a dirty block is written to disk we calculate and annotate the CRC value, though this is *not* WAL logged. Imagine this: 1. A hint bit is set. It is not WAL-logged, but the page is dirtied. 2. The buffer is flushed out of the buffer cache to the OS. A new CRC is calculated and stored on the page. 3. Half of the page is flushed to disk (aka torn page problem). The CRC made it to disk but the flipped hint bit didn't. You now have a page with incorrect CRC on disk. What if we treated the hint bits as all-zeros for the purpose of CRC calculation? This would exclude them from the checksum. Greetings Marcin Mańk -- 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: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a
On Sat, Nov 28, 2009 at 12:04 AM, Tom Lane t...@sss.pgh.pa.us wrote: It's not so much so-many-paths as so-many-join-relations that's killing it. I put some instrumentation into join_search_one_level to count the number of joinrels it was creating, and got this before getting bored: This is pretty off-topic, but if we had some upper bound on the cost of the complete plan, we could discard pieces of the plan that already cost more. One way to get the upper bound is to generate the plan in depth-first fashion, instead of the current breadth-first. Instead of bottom-up dynamic programming, employ memoization. The doubt I have is that this could show to not be a win because to discard a sub-plan we would have to consider the startup cost, not the total cost, and therefore we would be discarding not enough to make it worthwile. But I thought I`d mention it anyway, in case someone has a better idea :) Greetings Marcin Mańk -- 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] per table random-page-cost?
On Tue, Oct 20, 2009 at 1:21 AM, Tom Lane t...@sss.pgh.pa.us wrote: If the parameter is defined as the chance that a page is in cache there is very real physical meaning to it. We have no such parameter... What a simple person like me would think would work is: - call the parameter cached_probability. - Invent a way to store it (I`d actually try to do it the exact same way recent alter table set statistics distinct does it) a) less radical idea: replace all usage of random_page_cost with seq_page_cost * cached_probability + random_page_cost * (1-cached_probability) b) more radical idea: b1) invent a new GUC: cached_page_cost b2) replace all usage of seq_page_cost with cached_page_cost * cached_probability + seq_page_cost * (1-cached_probability) b3) replace all usage of random_page_cost with cached_page_cost * cached_probability + random_page_cost * (1-cached_probability) How would you come up with sensible figures for this hypothetical parameter? select schemaname,relname,heap_blks_hit / cast(heap_blks_read+heap_blks_hit+1 as float) from pg_statio_all_tables Would be a nice starting point. Greetings Marcin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] per table random-page-cost?
Currently random_page_cost is a GUC. I propose that this could be set per-table. I think this is a good idea for widely-wanted planner hints. This way You can say I do NOT want this table to be index-scanned, because I know it is not cached by setting it`s random_page_cost to a large value (an obviously You can do the other way around, when setting the random_page_cost to 1 You say I don`t care how You fetch the pages, they are all in cache) The value for the per-table setting could be inferred from pg_stat(io)?.*tables . We could have a tool to suggest appropriate values. We could call it something like cached_percentage (and have the cost of a random tuple fetch be inferred from the global random_page_cost, seq_tuple_cost and the per-table cached_percentage). Then we could set the global random_page_cost to a sane value like 200. Now one can wonder why the planner works while having such blantantly unrealistic values for random_page_cost :) What do You think? Greetings Marcin Mank -- 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] per table random-page-cost?
I've been thinking about this a bit, too. I've been wondering if it might make sense to have a random_page_cost and seq_page_cost setting for each TABLESPACE, to compensate for the fact that different media might be faster or slower, and a percent-cached setting for each table over top of that. I thought about making it per-table, but realistically I think most people don`t use tablespaces now. I would not want to be telling people to be able to hint the planner to (not) index-scan the table, You must move it to a separate tablespace. A global default, a per-tablespace default overriding it, and a per-table value overriding them both seems like over-engineering to me. Greetings Marcin -- 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] per table random-page-cost?
I thought about making it per-table***space***, but realistically I -- 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] per table random-page-cost?
This proposal is just hints by the back door, ISTM. As Tom says, there is a justification for having it on tablespaces but not on individual tables. If the parameter is defined as the chance that a page is in cache there is very real physical meaning to it. And this is per-table, not per-tablespace. A users table will likely be fetched from cache all the time, while a billing_records table will be fetched mostly from disk. Greetings Marcin -- 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] Rejecting weak passwords
The case that ENCRYPTED protects against is database superusers finding out other users' original passwords, which is a security issue to the extent that those users have used the same/similar passwords for other systems. I just want to note that md5 is not much of a protection against this case these days. Take a look at this: http://www.golubev.com/hashgpu.htm It takes about 32 hours to brute force all passwords from [a-zA-Z0-9] of up to 8 chars in length. Maybe it is time to look at something like bcrypt. http://chargen.matasano.com/chargen/2007/9/7/enough-with-the-rainbow-tables-what-you-need-to-know-about-s.html Greetings Marcin -- 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] Feedback on getting rid of VACUUM FULL
Exactly. The application is typically going to throw a concurrent update type of error when this happens, and we don't want magic background operations to cause that. I`d give up the possibility of using CTIDs in the way You explained for an auto-debloater without blinking an eye. Maybe we should have a GUC to enable/disable the auto-debloater? Make it a reloption? -- 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] Fixing geometic calculation
On Sat, Aug 8, 2009 at 3:07 AM, Paul Matthewsp...@netspace.net.au wrote:
IEEE754 does not allow two number X and Y, such that X!=Y and (X-Y)==0.
And since IEEE754 has been around since the 70's or 80's I think we can
start relying on its existence and behavior by now.
You are correct, I think, though this does not solve the division problem:
$ cat t.c
#include stdio.h
int main(){
double a=1.01e-307, b=1e-307, c=a-b;
printf(a=%le, b=%le, c=%le, c==0:%d, a==b:%d
1/c=%le\n,a,b,c,c==0,a==b,1.0/c);
return 0;
}
$ gcc -Wall -O2 t.c
$ ./a.out
a=1.01e-307, b=1.00e-307, c=1.00e-313, c==0:0, a==b:0 1/c=inf
Greetings
Marcin Mańk
--
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] Alpha Releases: Docs?
As far as the alpha releases, I am still worried about the use of the word alpha. I am worried someone is going to look at 8.4alpha1 and think that represents most of the features that will be in 8.5final, and will think the Postgres project is losing momentum. I would much rather they be called Commit Feast 1 (CF1), or something like that. milestone ? regards Marcin Mańk -- 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] ALTER TABLE ... ALTER COLUMN ... SET DISTINCT
ALTER COLUMN SET DISTINCT feels like adding a unique constraint. ALTER COLUMN SET STATISTICS DISTINCT ? ALTER COLUMN SET STATISTICS NDISTINCT ? Greetings Marcin -- 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] *_collapse_limit, geqo_threshold
On Thu, Jul 9, 2009 at 5:38 AM, Noah Mischn...@leadboat.com wrote: z Describing in those terms illuminates much. While the concepts do suggest 2^N worst-case planning cost, my artificial test case showed a rigid 4^N pattern; what could explain that? Isn`t that just so that the planner has to examine O(2^N) subsets of relations, and do O(2^N) work for each of them? To create level N join the planner chooses pairs of level k and level N-k joins. the count of level k joins is O(2^k), the count of level N-k ones is O(2^(N-k)). Together it is O(N) * O(2^N) * O(2^k) * O(2^(N-k)) which is O(N* 4^N) . This is for the worst case. If we could make a better estimate of the required planning time (I believe that the input data for a good heuristic is a matrix which says which relation is constrained to which relation), we could make better decisions about when to flatten subqueries, collapse joins, launch geqo... Greetings Marcin -- 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] Regclass and quoted table names
Use plain oids or regclass values, not a text column, if you are trying to store table identities. wouldn`t oids change on dump/reload? Greetings Marcin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PROPOSAL: geqo improvement
Hello, List. There are cases when GEQO returns a very bad plan in some rare executions of a query. To decrease likehood of this happening, I propose: When GEQO detects that what it found is in fact a miserable plan it restarts the search. Simple math shows that if the probability of a bad plan found in one 'go' is p, the overall probability of a bad plan is p^N . GEQO would decide that the plan is bad when the calculated cost of the plan would exceed the time spent planning so far a fixed number of times (100 ? a configurable parameter ?) . I think a function infering cost from time spent could be calculated from cpu_operator_cost - or is there a better way? As a safety mean I wish to limit the number of replannings to a fixed value (10? 20? a configurable parameter?) If I introduce some configuration variables, I plan to infer the defaults from geqo_effort (no concrete plan for this now). An alternative to restarting the search might be just extending it - running the main loop of geqo() function longer. I plan restarting because I`m afraid the real reason for getting bad plans could be that the algorithm is getting into some local minimum and can`t get out. I will explore that more. If there is agreement to do this, it looks simple enough that I volunteer to implement it. Please tell me what is the deadline for this to make into 8.4 . What I lack is good test cases to verify the solution. Greetings Marcin Mańk -- 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: geqo improvement
It sounds like you're proposing to compare the time spent planning to the estimated execution time. AFAICS, those things are unrelated, so I'm not sure what you hope to figure out by comparing them. The idea is: If we are to spend a LOT of resources executing the query, we might as well burn some cycles in hope of finding a better plan. It sounds like you may have some concrete queries that suffer from this problem. It might be helpful to post the queries and the good and bad plans. It may be that the problem can be fixed with some tuning of the existing parameters. Actually, no. This is my random thought based on observing some threads where people get bad plans due to GEQO. The deadline for the final CommitFest was November 1st, so I think it is too late for 8.4. ugh.. too bad. I`m still interested anyway :) Marcin -- 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] Hot standby and b-tree killed items
Perhaps we should listen to the people that have said they don't want queries cancelled, even if the alternative is inconsistent answers. I think an alternative to that would be if the wal backlog is too big, let current queries finish and let incoming queries wait till the backlog gets smaller. fell free to ignore me, as a non-hacker I`m not even supposed to be reading this list :-] Greetings Marcin -- 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] plPHP and plRuby
Ultimately, I really think we need something akin to CPAN so that we don't have to bundle all kinds of stuff in the core package. In the meantime, adding PLs that we can is better than not, but we do need to be mindful of the impression it might leave on users. A page that lists the status of all PLs (specifically why they're not included if they're not) would be a good thing to have. I as a user think that there should be a clear distinction of what is a supported extension, and what is an unsupported extension . With 100 projects on pgfoundry, 150 or so on gborg, it is hard to tell which ones one can trust, and not everybody wants to beta-test on their production data (especially for things that touch the core engine directly). Maybe there should be a set of requirements fulfilling of which could get a project a special 'blessing' from the Postgresql community? Greetings Marcin Mank ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
