Re: [HACKERS] 9.3: Empty arrays returned by array_remove()
On 31 May 2013 02:52, Dean Rasheed dean.a.rash...@gmail.com wrote: Testing 9.3beta, it seems that array_remove() may return an empty 1-d array whose upper bound is lower than its lower bound. I know that we discussed allowing this kind of array, but I don't think that discussion reached any conclusion, other than to agree that the current empty 0-d array behaviour would be kept in 9.3. That's right, zero-D is still the only supported representation of an empty array, so when array_remove() yields an empty array it ought to be zero-D. Good catch. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] add --throttle to pgbench (submission 3)
However I'm not sure that pg_stat_replication currently has the necessary information on either side to measure the lag (in time transactions, but how do I know when a transaction was committed? or number of transactions?). The BDR codebase now has a handy function to report when a transaction was committed, pg_get_transaction_committime(xid) . This looks handy for monitoring a replication setup. It should really be in core... Any plans? Or is there other ways to get this kind of information in core? -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.3: Empty arrays returned by array_remove()
On 31 May 2013 08:34, Brendan Jurd dire...@gmail.com wrote: On 31 May 2013 02:52, Dean Rasheed dean.a.rash...@gmail.com wrote: Testing 9.3beta, it seems that array_remove() may return an empty 1-d array whose upper bound is lower than its lower bound. I know that we discussed allowing this kind of array, but I don't think that discussion reached any conclusion, other than to agree that the current empty 0-d array behaviour would be kept in 9.3. That's right, zero-D is still the only supported representation of an empty array, so when array_remove() yields an empty array it ought to be zero-D. Good catch. Yeah, that's what I thought. Here's a patch to fix it, plus a new regression test to confirm that the result is a zero-D array. Regards, Dean array_remove.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Freezing without write I/O
On 31.05.2013 00:06, Bruce Momjian wrote: On Thu, May 30, 2013 at 04:33:50PM +0300, Heikki Linnakangas wrote: This would also be the first step in allowing the clog to grow larger than 2 billion transactions, eliminating the need for anti-wraparound freezing altogether. You'd still want to truncate the clog eventually, but it would be nice to not be pressed against the wall with run vacuum freeze now, or the system will shut down. Keep in mind that autovacuum_freeze_max_age is 200M to allow faster clog truncation. Does this help that? No. If you want to keep autovacuum_freeze_max_age set at less than 2 billion, you don't need support for more than 2 billion transactions. But for those who would like to set autovacuum_freeze_max_age higher than 2B, it would be nice to allow it. Actually, even with autovacuum_freeze_max_age = 200 M, it would be nice to not have the hard stop at 2 billion, in case autovacuum falls behind really badly. With autovacuum_freeze_max_age = 200M, there's a lot of safety margin, but with 1000M or so, not so much. - Heikki -- 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] Behavior of a pg_trgm index for 2 (or 3) character LIKE queries
On Fri, May 31, 2013 at 11:16 AM, Amit Langote amitlangot...@gmail.com wrote: On Fri, May 31, 2013 at 4:25 AM, Alexander Korotkov aekorot...@gmail.com wrote: On Thu, May 30, 2013 at 12:49 PM, Sawada Masahiko sawada.m...@gmail.com wrote: following emails are discussed about partial match of pg_trgm. I hope will this help. http://www.postgresql.org/message-id/cahgqgwfjshvv2ngme19wdtw9tefw_w7h2ns4e+yysjkb9wd...@mail.gmail.com as you may know, if search string contains multibyte characters trigram key is converted to CRC of 4 byte and it is used as key. (but only use upper 3 byte from CRC) so we can do partial matching if KEEPONLYALNUM is enabled. Please, read the further discussion on that thread. We can't do partial matching because of CRC independently of KEEPONLYALNUM. Also, a few more questions: 1) When building a trgm index, are there any differences for multi-byte character strings. For example, would a 2 character Japanese string (multi-byte offcourse) produce exactly 3 trigrams to be stored in the index which would later be used while look-up? in above case a 2 character multibyte string produce 3 trigrams of CRC. (because these was larger than 3 byte) and these are used while look-up. 2) And if that is so, is there problem in gin_extract_query_trgm(), that is while generating trigrams from a query search term that causes trigrams (stored in the index if answer to (1) is yes) NOT to be used in such a partial matching case? it means that we can't use trigrams in case of partial matching because trigrams (stored in index) are converted to different value(CRC). right? Regards, -- --- Sawada Masahiko -- 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] removing PD_ALL_VISIBLE
On Thu, May 30, 2013 at 09:47:22AM -0400, Robert Haas wrote: Well, as Heikki points out, I think that's unacceptably dangerous. Loss or corruption of a single visibility map page means possible loss of half a gigabyte of data. Also, if we go that route, looking at the visibility map is no longer an optimization; it's essential for correctness. We can't decide to skip it when it seems expensive, for example, as Jeff was proposing. Isn't the visibility map already required for proper return results as we use it for index-only scans. I think the optimization-only ship has sailed. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Unsigned integer types
On May 29, 2013, at 10:48 AM, Fabien COELHO coe...@cri.ensmp.fr wrote: If you do it, having uint1 (1 byte) would be nice as well. There is a signed 1byte int on PGXN, FWIW: http://pgxn.org/extension/tinyint Best, David -- 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] removing PD_ALL_VISIBLE
On 2013-05-31 13:14:13 -0400, Bruce Momjian wrote: On Thu, May 30, 2013 at 09:47:22AM -0400, Robert Haas wrote: Well, as Heikki points out, I think that's unacceptably dangerous. Loss or corruption of a single visibility map page means possible loss of half a gigabyte of data. Also, if we go that route, looking at the visibility map is no longer an optimization; it's essential for correctness. We can't decide to skip it when it seems expensive, for example, as Jeff was proposing. Isn't the visibility map already required for proper return results as we use it for index-only scans. I think the optimization-only ship has sailed. At the moment we can remove it without causing corruption. If we were to use it for freezing we couldn't anymore. So there's a difference - how big it is I am not sure. 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] Freezing without write I/O
On Thu, May 30, 2013 at 10:04:23PM -0400, Robert Haas wrote: Hm. Why? If freezing gets notably cheaper I don't really see much need for keeping that much clog around? If we still run into anti-wraparound areas, there has to be some major operational problem. That is true, but we have a decent number of customers who do in fact have such problems. I think that's only going to get more common. As hardware gets faster and PostgreSQL improves, people are going to process more and more transactions in shorter and shorter periods of time. Heikki's benchmark results for the XLOG scaling patch show rates of 80,000 tps. Even at a more modest 10,000 tps, with default settings, you'll do anti-wraparound vacuums of the entire cluster about every 8 hours. That's not fun. Are you assuming these are all write transactions, hence consuming xids? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] removing PD_ALL_VISIBLE
Isn't the visibility map already required for proper return results as we use it for index-only scans. I think the optimization-only ship has sailed. At the moment we can remove it without causing corruption. If we were to use it for freezing we couldn't anymore. So there's a difference - how big it is I am not sure. Depends on your definition of corruption, really. But yes, right now, the vismap can lose bits without causing any corruption, and making all-frozen depend on it would eliminate that. -- 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
[HACKERS] detecting binary backup in progress
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I can check for the presence of $PGDATA/backup_label in order to detect a backup in progress (i.e. pg_start_backup() has been run and pg_stop_backup() has not yet been run). However there is a period of time after pg_start_backup() is first executed to when it completes, during which backup_label file does not exist yet, but the backup has essentially been started. Is there any way to detect this state? Thanks, Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBAgAGBQJRqOAwAAoJEDfy90M199hlMdMP/A0ebvKs3v1H4bfejtzNhGy+ qCDc+G8zll6r8Kh6jcUhm7s3pGQ6Ku8vW6OjyfOHW4D5risuGaUiYO29+JLujJVU ksP+bkZCx66l/hwlq3dnRtyuyqjj8BrzfuwfJYDGyygj5eCXjTeSH7P9KkrYQp2C UmIVGzivrShlA7mT/4oj7keeC+pYynA9yJ1ZyB1OnxeRz01myLTlen/h4u6Ybc4u /aRlXnjhyy8eiDVlErOGwT8n54/1pF5pMmorQHffs2vRbgTRpg4G4ulutjWqEhLB IhMZX1YCKynHDTsqkvO6gZXQW2iT0sx8nXnpORbjHQABnGkj2d9qsmtnKTOoQS/6 mVc6x4FMF2yXUkRT0j5blqQD5v2/teDGwB/8Z1DEkaYF+vMvPNUVU9dtNCtiXxuf 2MjYy3k+uMqMcP5/Dxsajwdmt09nxt6eWYN6BNhxZ3lXIrkGWEc9fE7I8w1kOy+Y 28AvdvHL91eEQ3ZN7bI2DSXf6nRe7GWzGvcsQIgdTg7KONeDT1+pvqVwTmzYoCqL ji0q9DbGOjFDF7dUICMO4rOs3GybAkx76Xne1yz2KLaxmOc625UiP0Glo4s+6ar3 ajYH9HmcXND+v6OnRu3HpJUWhk9P7eiwhYFma3PWhjHfJdWpdkvmMT2PUoZy8QEw y/FygFJZIYU2KZkuRSBz =4xnd -END PGP SIGNATURE- -- 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] Behavior of a pg_trgm index for 2 (or 3) character LIKE queries
On Sat, Jun 1, 2013 at 1:48 AM, Sawada Masahiko sawada.m...@gmail.com wrote: On Fri, May 31, 2013 at 11:16 AM, Amit Langote amitlangot...@gmail.com wrote: 2) And if that is so, is there problem in gin_extract_query_trgm(), that is while generating trigrams from a query search term that causes trigrams (stored in the index if answer to (1) is yes) NOT to be used in such a partial matching case? it means that we can't use trigrams in case of partial matching because trigrams (stored in index) are converted to different value(CRC). right? When I debugged a partial match case such as column like '%st%' , it appears that get_wildcard_trigrams return no trigrams for wildcard part 'st' since charlen 3. Hence, GIN_SEARCH_MODE_ALL mode is used and results in full index scan instead of trigrams being used. This happens for multibyte case too. The problem is that for wildcard part consisting of less than 3 characters, get_wildcard_trigrams return nothing. -- Amit Langote -- 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] detecting binary backup in progress
On 2013-05-31 10:38:56 -0700, Joe Conway wrote: I can check for the presence of $PGDATA/backup_label in order to detect a backup in progress (i.e. pg_start_backup() has been run and pg_stop_backup() has not yet been run). However there is a period of time after pg_start_backup() is first executed to when it completes, during which backup_label file does not exist yet, but the backup has essentially been started. Is there any way to detect this state? 9.3 has pg_is_in_backup() for that. I don't think there's a way to detect it safely without C code before that. 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] detecting binary backup in progress
On 31 May 2013 18:38, Joe Conway m...@joeconway.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I can check for the presence of $PGDATA/backup_label in order to detect a backup in progress (i.e. pg_start_backup() has been run and pg_stop_backup() has not yet been run). However there is a period of time after pg_start_backup() is first executed to when it completes, during which backup_label file does not exist yet, but the backup has essentially been started. Is there any way to detect this state? pg_is_in_backup() -- Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] removing PD_ALL_VISIBLE
On Fri, May 31, 2013 at 10:28:12AM -0700, Josh Berkus wrote: Isn't the visibility map already required for proper return results as we use it for index-only scans. I think the optimization-only ship has sailed. At the moment we can remove it without causing corruption. If we were to use it for freezing we couldn't anymore. So there's a difference - how big it is I am not sure. Depends on your definition of corruption, really. But yes, right now, the vismap can lose bits without causing any corruption, and making all-frozen depend on it would eliminate that. Roberts statement was: Loss or corruption of a single visibility map page means possible loss of half a gigabyte of data. Certainly unidentified corruption of a visibility map page could easily cause incorrect results. So, technically, _adding_ bits would cause corruption. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] detecting binary backup in progress
On 31.05.2013 20:38, Joe Conway wrote: I can check for the presence of $PGDATA/backup_label in order to detect a backup in progress (i.e. pg_start_backup() has been run and pg_stop_backup() has not yet been run). However there is a period of time after pg_start_backup() is first executed to when it completes, during which backup_label file does not exist yet, but the backup has essentially been started. Is there any way to detect this state? What are you trying to accomplish? Even if you eliminate that window, it's always possible that a backup starts just after you've checked that there is no backup in progress. - Heikki -- 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] Behavior of a pg_trgm index for 2 (or 3) character LIKE queries
On Fri, May 31, 2013 at 9:41 PM, Amit Langote amitlangot...@gmail.comwrote: On Sat, Jun 1, 2013 at 1:48 AM, Sawada Masahiko sawada.m...@gmail.com wrote: On Fri, May 31, 2013 at 11:16 AM, Amit Langote amitlangot...@gmail.com wrote: 2) And if that is so, is there problem in gin_extract_query_trgm(), that is while generating trigrams from a query search term that causes trigrams (stored in the index if answer to (1) is yes) NOT to be used in such a partial matching case? it means that we can't use trigrams in case of partial matching because trigrams (stored in index) are converted to different value(CRC). right? When I debugged a partial match case such as column like '%st%' , it appears that get_wildcard_trigrams return no trigrams for wildcard part 'st' since charlen 3. Hence, GIN_SEARCH_MODE_ALL mode is used and results in full index scan instead of trigrams being used. This happens for multibyte case too. The problem is that for wildcard part consisting of less than 3 characters, get_wildcard_trigrams return nothing. Partial match for LIKE queries is not implemented at all. With current index structure it could be possible to implement it with guarantee that all indexed strings don't contain multibyte characters (i.e. single-byte encoding). Also, at it was mentioned, it's possible to implement operator class with text storage type which would support partial match in all the cases. However, I doubt it's reasonable to implement it based on pg_trgm, because of many hard-wired assumptions that trigram is fixed length and compatibility. -- With best regards, Alexander Korotkov.
Re: [HACKERS] removing PD_ALL_VISIBLE
Bruce, Roberts statement was: Loss or corruption of a single visibility map page means possible loss of half a gigabyte of data. I fail to be alarmed at this; currently losing a single page of the clog causes just as widespread corruption (worse, actually, since it's not confined to one table). It does point to the eventual need to checksum these things, though. Certainly unidentified corruption of a visibility map page could easily cause incorrect results. So, technically, _adding_ bits would cause corruption. Yes, that's already true. I'm pointing out that if we depend on the vismap for all-frozen, then losing bits *also* causes corruption, so that's something we need to test for. Right now, there is no possible corruption from losing bits; we simply end up scannning more pages than we have to. -- 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] removing PD_ALL_VISIBLE
On Fri, May 31, 2013 at 11:00:19AM -0700, Josh Berkus wrote: Bruce, Roberts statement was: Loss or corruption of a single visibility map page means possible loss of half a gigabyte of data. I fail to be alarmed at this; currently losing a single page of the clog causes just as widespread corruption (worse, actually, since it's not confined to one table). It does point to the eventual need to checksum these things, though. Certainly unidentified corruption of a visibility map page could easily cause incorrect results. So, technically, _adding_ bits would cause corruption. Yes, that's already true. I'm pointing out that if we depend on the vismap for all-frozen, then losing bits *also* causes corruption, so that's something we need to test for. Right now, there is no possible corruption from losing bits; we simply end up scannning more pages than we have to. Right, and it is hard to see that losing and adding are somehow more/less likely, so it seems we already realy on the visibility map being correct. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] detecting binary backup in progress
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/31/2013 10:42 AM, Andres Freund wrote: On 2013-05-31 10:38:56 -0700, Joe Conway wrote: I can check for the presence of $PGDATA/backup_label in order to detect a backup in progress (i.e. pg_start_backup() has been run and pg_stop_backup() has not yet been run). However there is a period of time after pg_start_backup() is first executed to when it completes, during which backup_label file does not exist yet, but the backup has essentially been started. Is there any way to detect this state? 9.3 has pg_is_in_backup() for that. I don't think there's a way to detect it safely without C code before that. Good to know, although it won't help me much in my current scenario ;-) Thanks, Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBAgAGBQJRqO12AAoJEDfy90M199hlkxgP/jrrnjRKhC+bIHQc/gCmaGXe CoL490f3ClST2V8e2GffybrqHrJ5xTnhVs04X+kIZOF3vSh67mWmHtSANElxjbYB t95kkLbkxPhPFnr0ubnalbMRplxlNhLERmi8BWPuknJCJGeEEZ7qlIZROV9GXH51 ZArMewbBlNGUvZQU+DHVYuA3WYVcDSCR0F9AL/O1lLsZ6RVbh/M4WH+q+GzDWsJT 80PDusnSSzHZzq1xMj96kRH3n7s8rpx0kDa5xuM/TBne0Fif1AZuh079cAUtUmnO w50pgOuTc3lhbH5HmTTuefR94820YBHnol1lsGyLQaKPdXN/6JU+Ssz+q4xx/pWb Fy1VscYRGb2yka7DpmcGlR4k8kqLhjRwZMKM+GLNGVeERHlHVV30Q++DwVZXQSSF hfgvpphAv1klCh1R0vwounD/DFb48ZZnVp541GULXSZr1X6cQl+vecrzisDlHjVq SAJHEvwsiCmR3TzaXgno3GNCmReLfQ9Wg6BJHszSuOieeT2ha+ffJQi7tZZEzsKs quEYdrgkyXheRwrO+bzi3h59wJf5MdJU3i1U9VPL0JWo9F4QcoGAwrQVYdM3RpUb DZf2T45V9nNEYYe10FUe3GfpiaoriflOU0RUSlpWoiq2IsCZA+S2jU65mqtdAtOk JLCDZUJGkoRjXtwWxMqj =l+5T -END PGP SIGNATURE- -- 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] detecting binary backup in progress
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/31/2013 10:46 AM, Heikki Linnakangas wrote: On 31.05.2013 20:38, Joe Conway wrote: I can check for the presence of $PGDATA/backup_label in order to detect a backup in progress (i.e. pg_start_backup() has been run and pg_stop_backup() has not yet been run). However there is a period of time after pg_start_backup() is first executed to when it completes, during which backup_label file does not exist yet, but the backup has essentially been started. Is there any way to detect this state? What are you trying to accomplish? Even if you eliminate that window, it's always possible that a backup starts just after you've checked that there is no backup in progress. We can lay down a lockfile before checking for backup in progress, and the script that starts the backup can check for the lockfile before doing anything. There is still a race-window, but I want to minimize it. pg_is_in_backup() mentioned nearby on this thread will help close the gap. Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBAgAGBQJRqO7WAAoJEDfy90M199hlSPkP/AtbQjRImH0TYp0nGV+XG8zq zyeaQXtU1kB+eon7egUjObN/khnM2ePlqmKBG5TuZW8HQWK+AnISG68rB/FU10Vo rgiN2BSw/GZ4oCsJ1tXnbvyE70vRhq5pMbEbkfCCqjt/LZOGg50/Z/ROmr7RwmSx 2dSVsvhUryYS3eo/OxgkyBlCtnlV3fkkdKLc6S5VCaNWfHvu731Q9JPgN6/S7NhB kcpjdD8/OA6m7MH7VkZkYcz1clVJKDCTRP39MteM4RT4IHZL6+rztnqrgICEccSh b+gP8GUipY4o/V3QBTH09OXkSjyomgobtRFUqtB5DslY3JCdATyZdDKePUyHqZdo /jVeUddde22BkRIoC98QwB1MiNUNXXrHBZqUV9ITlj1BrFC0HpWXJ4d/TP6Sah7W 4+n6eW+2mbRd3w+TzY95Q4i3gT3U4DS6qJKL36DBlKJQhwlmvJjZS1AMEXoaAiMj BBl78u9Bo6meKxKacrwXq9vfgkKwup/kc8vHwEHvJz+nM/j2/Gve0iwjhIuuXoHp XETld9x2LWiEN8xYzI5halycP1eR7eD/IBDm8X6FzUCL+gV3/ORvYxwAR587aprf eS143/GS1JPH9nt+3ILhCGrwaBz8a/HPWE3eYnJqYlH8hVmoxjbZToDjU3FHdCNg zsdcwv1Lg7XtX6wCMiEm =eG9/ -END PGP SIGNATURE- -- 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] removing PD_ALL_VISIBLE
* Bruce Momjian (br...@momjian.us) wrote: Right, and it is hard to see that losing and adding are somehow more/less likely, so it seems we already realy on the visibility map being correct. Yes, but there's also a way to get *back* to a valid state if things go south with the visibility map. It's similar to an index today- sure, you might get corruption, and that might give you wrong results, but if you detect it, it's easy to correct- rebuild the index, drop the visibility map, etc. With CRCs, it'll be even easier to detect and discover when you need to do such a thing. Corruption in the heap is certainly bad too- you may lose some records, but you could zero those pages out and move on with only losing 8k or what-have-you. Clog corruption is certainly a bad thing, but if nearly everything in your DB is already frozen, it's not as bad as it *could* be. I wonder if you could rebuild a portion of clog from the WAL.. There are a lot of different tradeoffs here, certainly. It's certainly nice that single bit or single page corruption can often be recovered from by rebuilding an index or rebuilding the visbility map or just nuking a few records from a table. CRCs help with identifying when corruption has happened, but they do nothing for what to *do* when it happens, and restore completely from backup isn't a great answer when you've got terrabytes of data. Where I'm going with this whole thing is simply that I do worry a bit about using a bitmap for freeze, or similar, information and not being able to reconstruct that bitmap from the heap. Perhaps that's overly paranoid, but, well, we also write the same data out to disk in multiple places multiple times- some might call that paranoid too. ;) Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] detecting binary backup in progress
On 31.05.2013 21:41, Joe Conway wrote: On 05/31/2013 10:46 AM, Heikki Linnakangas wrote: On 31.05.2013 20:38, Joe Conway wrote: I can check for the presence of $PGDATA/backup_label in order to detect a backup in progress (i.e. pg_start_backup() has been run and pg_stop_backup() has not yet been run). However there is a period of time after pg_start_backup() is first executed to when it completes, during which backup_label file does not exist yet, but the backup has essentially been started. Is there any way to detect this state? What are you trying to accomplish? Even if you eliminate that window, it's always possible that a backup starts just after you've checked that there is no backup in progress. We can lay down a lockfile before checking for backup in progress, and the script that starts the backup can check for the lockfile before doing anything. There is still a race-window, but I want to minimize it. pg_is_in_backup() mentioned nearby on this thread will help close the gap. Note that pg_is_in_backup() just checks for presence of $PGDATA/backup_label. Also note that pg_basebackup doesn't create backup_label in the server. It's included in the backup that's sent to the client, but it's never written to disk in the server. So checking for backup_label manually or with pg_is_in_backup() will return false even if pg_basebackup is running. - Heikki -- 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] detecting binary backup in progress
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/31/2013 12:29 PM, Heikki Linnakangas wrote: Note that pg_is_in_backup() just checks for presence of $PGDATA/backup_label. Also note that pg_basebackup doesn't create backup_label in the server. It's included in the backup that's sent to the client, but it's never written to disk in the server. So checking for backup_label manually or with pg_is_in_backup() will return false even if pg_basebackup is running. Oh, that's really good to know. So essentially there is no reliable way to know that pg_start_backup() has been executed and pg_stop_backup() has not? Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBAgAGBQJRqPuGAAoJEDfy90M199hlAbIP/RkeOMMEc2aw0RjYbQKcGLRX GJFU2MxymtnsbR6kqeijxgeug48NVu1Yx0yyw0rf2aS8pAN6v6Xd5aRIS9li+j/A K6A16+2LxwvtTlyKFkdU/8NP4Uo/xAtG3qOBbfpYNT0YRwuKr8nA9UhWFOEG4+hQ Kz27O5mwjTCzW6WSrre2HZxM4B3J1eWSKQrZ+WDUAmh6GEXAIkIXdDJJnjZqiZcJ rNldh95lEEAPmqZXTq2tANYpIE7UHmRl7Vm0foNHbqsyy+fWGZtqWA4tK8mEVSrQ QdPEa8CjzOAL9gL/bLN3Ti2UimLexKcG6jn9BaUiCNLj5G50tRSiUgH+ur1vSgyv vQYmvi2E3W+L0FABzAuNcsEY1lb2V+JEBQua64m82tOyDvXPpULKw3Bi7PKcxkci M8Si1knYUnWdaeUV3c1ZVbVmtTGtanSF1TiLJH/S7tLb49eoTN3pF3EpIGSXNqcc 4H2Ixemdtu/PZ0FGT9+sKGcIWo+Bp37pdnXXWiGc/kZMML2I+5BK+/Pd6Tm3nJFH MDVI7NrPUqCtSHbrDZ/Uv9fKLulaq0kHznyNg9tW0XBwHNpM9RJcbw9Vq88UgDDW mlaTCvR6G7GM72R1+czM/ek3Oy7v/sPZQmeJrVk0OdPqCe31myyBYP2XRWMW2cW2 2S7SSN2LnQD1YF9VrrYt =c9gP -END PGP SIGNATURE- -- 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] detecting binary backup in progress
On 2013-05-31 22:29:45 +0300, Heikki Linnakangas wrote: On 31.05.2013 21:41, Joe Conway wrote: On 05/31/2013 10:46 AM, Heikki Linnakangas wrote: On 31.05.2013 20:38, Joe Conway wrote: I can check for the presence of $PGDATA/backup_label in order to detect a backup in progress (i.e. pg_start_backup() has been run and pg_stop_backup() has not yet been run). However there is a period of time after pg_start_backup() is first executed to when it completes, during which backup_label file does not exist yet, but the backup has essentially been started. Is there any way to detect this state? What are you trying to accomplish? Even if you eliminate that window, it's always possible that a backup starts just after you've checked that there is no backup in progress. We can lay down a lockfile before checking for backup in progress, and the script that starts the backup can check for the lockfile before doing anything. There is still a race-window, but I want to minimize it. pg_is_in_backup() mentioned nearby on this thread will help close the gap. Note that pg_is_in_backup() just checks for presence of $PGDATA/backup_label. Also note that pg_basebackup doesn't create backup_label in the server. It's included in the backup that's sent to the client, but it's never written to disk in the server. So checking for backup_label manually or with pg_is_in_backup() will return false even if pg_basebackup is running. Whoa. You are right, but I'd call that a bug. I don't understand why we aren't just checking XLogCtl-Insert.(nonExclusiveBackups||exlusiveBackup)? I vote for changing this before we release pg_is_in_backup(). 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] detecting binary backup in progress
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/31/2013 12:36 PM, Andres Freund wrote: On 2013-05-31 22:29:45 +0300, Heikki Linnakangas wrote: Note that pg_is_in_backup() just checks for presence of $PGDATA/backup_label. Also note that pg_basebackup doesn't create backup_label in the server. It's included in the backup that's sent to the client, but it's never written to disk in the server. So checking for backup_label manually or with pg_is_in_backup() will return false even if pg_basebackup is running. Whoa. You are right, but I'd call that a bug. I don't understand why we aren't just checking XLogCtl-Insert.(nonExclusiveBackups||exlusiveBackup)? I vote for changing this before we release pg_is_in_backup(). +1 Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBAgAGBQJRqP4QAAoJEDfy90M199hl7tEQAJd4afwW3AM5CpZSjG0gcrrD P795WAE7UH+lyTCjM1XUunMD8q9cqgnDkl2RXxjow/QD7POEOB85+6T5pHQwsqkN gSjLKUha3Q58tQHQqFOkclCWkiIRCAltvwmALYYl2W9Enlbef0vQo0Za1Rfoe4FB H5xGPAogsbaHQQGjorNcWHylxyGd/epDsTMbTfzS5LZwpK3H+KYqegs+ULHImvTv 1LS7thRe4LFC8xQ4lImZCI3ZdxhcLSL37f3b/nxv5bjM20ZRJynT4ZTMwFfALEK+ ol76zOcLLKUy7xBq+S/MFHXG5Oq+nUA3ZTokwigJPLmLI+nDC/9gFU0NfkUNoeao 5D5hCqulaqBdtbp8I+pQtY1pv/6ZdKtLbIdE7LKpvAAkh+0kCABZxyWv1f2w1DPQ VMQmmb56kKCAdk2/300pa2q0b/KuC6rjYjHgtvyz5bmhCs0wQ7FUuQeO4tlhzrGI +rW1eoi5rI79lbKp8y626ZG6XhvqKrZ4sUEjcE52lePhEcaE0F7uS/yFhzS5uv6Y wlrKWxglaFcdf4mPFM3ysxHP1i50kxZ+YcXY1F2Cn5WIWiRb0a9qjeYMP2NU6gCB 4/2+TrDWpO8JWuMrxAMlHB9QrbcW5iPyenwklVo0dh5pP0XuYt2oqUNqFrRJYHBQ SgQ7sGyuuxta/2qapWiS =e17o -END PGP SIGNATURE- -- 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] removing PD_ALL_VISIBLE
On 05/31/2013 12:22 PM, Stephen Frost wrote: Where I'm going with this whole thing is simply that I do worry a bit about using a bitmap for freeze, or similar, information and not being able to reconstruct that bitmap from the heap. Perhaps that's overly paranoid, but, well, we also write the same data out to disk in multiple places multiple times- some might call that paranoid too. ;) On the other hand, we could combine Heikki's proposal (epoch numbers in the page header) together with using the visibility map for pages we don't need to vacuum freeze, and get vastly better behavior without increasing corruption risk that I can see. -- 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] Running pgindent
On 05/29/2013 11:41 PM, Bruce Momjian wrote: On Wed, May 29, 2013 at 10:08:10PM -0400, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: Wow, uh, yeah, I guess we could do that. I will await more feedback. Please don't. I'm already rather concerned by this one. It looks like there's a rule to pull a line in to meet the max-column requirement even when that makes things line up 'funny', eg: I did a comparison of the parameters passed to BSD indent, and Andrew did accurately transfer all the flags, so I am a little confused why there is such a difference, as BSD indent has not changed. I spent quite a lot of time trying to make the tool behave the same as the old script. cheers andrew -- 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] detecting binary backup in progress
On 31.05.2013 22:36, Andres Freund wrote: On 2013-05-31 22:29:45 +0300, Heikki Linnakangas wrote: Note that pg_is_in_backup() just checks for presence of $PGDATA/backup_label. Also note that pg_basebackup doesn't create backup_label in the server. It's included in the backup that's sent to the client, but it's never written to disk in the server. So checking for backup_label manually or with pg_is_in_backup() will return false even if pg_basebackup is running. Whoa. You are right, but I'd call that a bug. I don't understand why we aren't just checking XLogCtl-Insert.(nonExclusiveBackups||exlusiveBackup)? Well, depends on what you imagine the function is used for. If you think of it as will pg_start_backup() throw an error if I call it now, or do I need to call pg_stop_backup(), then the current behavior is correct. The manual says: pg_is_in_backup() boolTrue if an on-line exclusive backup is still in progress. So clearly that is intentional. That could use some rephrasing, though; a layman won't know what an exclusive backup is. - Heikki -- 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] detecting binary backup in progress
Heikki Linnakangas wrote: The manual says: pg_is_in_backup()boolTrue if an on-line exclusive backup is still in progress. So clearly that is intentional. That could use some rephrasing, though; a layman won't know what an exclusive backup is. Heck, I don't understand what it is either. -- Álvaro Herrerahttp://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] detecting binary backup in progress
On 2013-05-31 22:53:14 +0300, Heikki Linnakangas wrote: On 31.05.2013 22:36, Andres Freund wrote: On 2013-05-31 22:29:45 +0300, Heikki Linnakangas wrote: Note that pg_is_in_backup() just checks for presence of $PGDATA/backup_label. Also note that pg_basebackup doesn't create backup_label in the server. It's included in the backup that's sent to the client, but it's never written to disk in the server. So checking for backup_label manually or with pg_is_in_backup() will return false even if pg_basebackup is running. Whoa. You are right, but I'd call that a bug. I don't understand why we aren't just checking XLogCtl-Insert.(nonExclusiveBackups||exlusiveBackup)? Well, depends on what you imagine the function is used for. If you think of it as will pg_start_backup() throw an error if I call it now, or do I need to call pg_stop_backup(), then the current behavior is correct. The manual says: pg_is_in_backup()boolTrue if an on-line exclusive backup is still in progress. So clearly that is intentional. Well, just because it's intentional, doesn't mean its a good idea ;). There very well are reasons to check for in progress non-exclusive backups as well. You e.g. wouldn't want to restart the database while the weekly base backup of your 1TB database is in progress, just because it's done via the replication protocol. If we weren't in beta 1 already I'd vote for making it into: pg_backup_in_progress(OUT bool exclusive, OUT int non_exclusive) or similar. Perhaps we should do that anyway? That could use some rephrasing, though; a layman won't know what an exclusive backup is. True. Although I have to admit I can't come up with a succinct name for it it right now. 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] Running pgindent
On Fri, May 31, 2013 at 03:51:30PM -0400, Andrew Dunstan wrote: On 05/29/2013 11:41 PM, Bruce Momjian wrote: On Wed, May 29, 2013 at 10:08:10PM -0400, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: Wow, uh, yeah, I guess we could do that. I will await more feedback. Please don't. I'm already rather concerned by this one. It looks like there's a rule to pull a line in to meet the max-column requirement even when that makes things line up 'funny', eg: I did a comparison of the parameters passed to BSD indent, and Andrew did accurately transfer all the flags, so I am a little confused why there is such a difference, as BSD indent has not changed. I spent quite a lot of time trying to make the tool behave the same as the old script. Yes, and I believe we tested running the Perl version to make sure it was the same, so the changes we are seeing are just normal (unfortunate) adjustments by pgindent. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Unsigned integer types
If you do it, having uint1 (1 byte) would be nice as well. There is a signed 1byte int on PGXN, FWIW: http://pgxn.org/extension/tinyint That's good, thanks for the pointer! However, it is a signed tinyint (-128..127 range), not an unsigned one. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] removing PD_ALL_VISIBLE
* Josh Berkus (j...@agliodbs.com) wrote: On 05/31/2013 12:22 PM, Stephen Frost wrote: Where I'm going with this whole thing is simply that I do worry a bit about using a bitmap for freeze, or similar, information and not being able to reconstruct that bitmap from the heap. Perhaps that's overly paranoid, but, well, we also write the same data out to disk in multiple places multiple times- some might call that paranoid too. ;) On the other hand, we could combine Heikki's proposal (epoch numbers in the page header) together with using the visibility map for pages we don't need to vacuum freeze, and get vastly better behavior without increasing corruption risk that I can see. That was actually my thinking as well.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Running pgindent
* Bruce Momjian (br...@momjian.us) wrote: On Fri, May 31, 2013 at 03:51:30PM -0400, Andrew Dunstan wrote: I spent quite a lot of time trying to make the tool behave the same as the old script. Yes, and I believe we tested running the Perl version to make sure it was the same, so the changes we are seeing are just normal (unfortunate) adjustments by pgindent. Fair enough, thanks for the efforts and for looking into it, apologies for my complaining. If no one objects, I'm going to review the conditional statements and similar lines which could be split out on to multiple lines instead of being squeezed closer to the left edge and not lined up properly. Then I'll try and get pgindent running locally. If I can get that happening and it doesn't want to further change the modifications that I make, then I'll commit those cleanups. This will only be for changes made during this latest pgindent run and only against master- I don't want to create any more code churn beyond what's already being impacted. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Running pgindent
On Fri, May 31, 2013 at 04:57:20PM -0400, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: On Fri, May 31, 2013 at 03:51:30PM -0400, Andrew Dunstan wrote: I spent quite a lot of time trying to make the tool behave the same as the old script. Yes, and I believe we tested running the Perl version to make sure it was the same, so the changes we are seeing are just normal (unfortunate) adjustments by pgindent. Fair enough, thanks for the efforts and for looking into it, apologies for my complaining. If no one objects, I'm going to review the conditional statements and similar lines which could be split out on to multiple lines instead of being squeezed closer to the left edge and not lined up properly. Then I'll try and get pgindent running locally. If I can get that happening and it doesn't want to further change the modifications that I make, then I'll commit those cleanups. This will only be for changes made during this latest pgindent run and only against master- I don't want to create any more code churn beyond what's already being impacted. OK. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] 9.3: Empty arrays returned by array_remove()
On Fri, May 31, 2013 at 08:55:49AM +0100, Dean Rasheed wrote: On 31 May 2013 08:34, Brendan Jurd dire...@gmail.com wrote: On 31 May 2013 02:52, Dean Rasheed dean.a.rash...@gmail.com wrote: Testing 9.3beta, it seems that array_remove() may return an empty 1-d array whose upper bound is lower than its lower bound. I know that we discussed allowing this kind of array, but I don't think that discussion reached any conclusion, other than to agree that the current empty 0-d array behaviour would be kept in 9.3. That's right, zero-D is still the only supported representation of an empty array, so when array_remove() yields an empty array it ought to be zero-D. Good catch. Yeah, that's what I thought. Here's a patch to fix it, plus a new regression test to confirm that the result is a zero-D array. Committed. Thanks. -- Noah Misch 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] fallocate / posix_fallocate for new WAL file creation (etc...)
On Thu, May 30, 2013 at 02:58:26PM +0200, Andres Freund wrote: * Andres Freund (and...@2ndquadrant.com) wrote: But really, I am not at all concerned about some obscure values being returned, but about a read() not being successful.. After a bit of standard perusing writing a single byte to the end of the file after the fallocate ought to make at least the reading guaranteed to be defined. If we did seek(last_byte); write(); posix_fallocate() we should even always have defined content. Yuck. This portion of the posix_fallocate() specification requires the hoped-for effect on subsequent read() calls: If the offset+ len is beyond the current file size, then posix_fallocate() shall adjust the file size to offset+ len. Otherwise, the file size shall not be changed. -- http://pubs.opengroup.org/onlinepubs/9699919799/functions/posix_fallocate.html When the file size increases, read()'s defined behavior switches from returning short to retrieving zeros. There's no need for an additional write() to ensure that. -- Noah Misch 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] removing PD_ALL_VISIBLE
On Fri, May 31, 2013 at 1:44 PM, Bruce Momjian br...@momjian.us wrote: On Fri, May 31, 2013 at 10:28:12AM -0700, Josh Berkus wrote: Isn't the visibility map already required for proper return results as we use it for index-only scans. I think the optimization-only ship has sailed. At the moment we can remove it without causing corruption. If we were to use it for freezing we couldn't anymore. So there's a difference - how big it is I am not sure. Depends on your definition of corruption, really. But yes, right now, the vismap can lose bits without causing any corruption, and making all-frozen depend on it would eliminate that. Roberts statement was: Loss or corruption of a single visibility map page means possible loss of half a gigabyte of data. Certainly unidentified corruption of a visibility map page could easily cause incorrect results. So, technically, _adding_ bits would cause corruption. Adding bits could cause tuples that ought to be invisible to be treated as visible. Currently, removing bits is harmless (except to performance), but if we used the VM bit to indicate whether the page was frozen in lieu of actually freezing it, a cleared bit would potentially cause vacuum to nuke everything on that page. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] removing PD_ALL_VISIBLE
On Fri, May 31, 2013 at 3:45 PM, Josh Berkus j...@agliodbs.com wrote: On 05/31/2013 12:22 PM, Stephen Frost wrote: Where I'm going with this whole thing is simply that I do worry a bit about using a bitmap for freeze, or similar, information and not being able to reconstruct that bitmap from the heap. Perhaps that's overly paranoid, but, well, we also write the same data out to disk in multiple places multiple times- some might call that paranoid too. ;) On the other hand, we could combine Heikki's proposal (epoch numbers in the page header) together with using the visibility map for pages we don't need to vacuum freeze, and get vastly better behavior without increasing corruption risk that I can see. Yeah, I was thinking about that as well. In fact, under either Heikki's proposal or Andres's proposal or my variants of either one of them, anti-wraparound vacuums no longer need to scan all-visible pages. Under Andres's proposal (and variants), all-visible pages are ipso facto frozen and therefore need not be scanned for freezing. But under Heikki's proposal (and variants), anti-wraparound vacuums only need to remove dead tuples; freezing live ones is a no-op. And all-visible pages don't contain any dead tuples, so we're right back in the same place.[1] Where things diverge a little is what you when an anti-wraparound scan encounters a page that isn't all-visible and can't be marked all-visible. Under the XID epoch family of proposals, we need to truncate any dead tuples on the page to line pointers, and that's it. Under the treat all-visible as frozen family of proposals, we *also* need to do old-style freezing on any aged but live tuples on the page. So the XID epoch saves write I/O in this case, because we don't dirty pages or write WAL just because we see a tuple with a high XID age. Only pages that contain actual dead tuples get dirtied. So on further reflection, I'm not seeing any advantage to combining the two proposals. The XID epoch approach has the complications of requiring page format changes and consuming space on every page, although I think I may have worked out a variant approach that will avoid that (which no one has commented on, so maybe I'm hallucinating). But in other respects it seems better all around. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company [1] I said upthread that Heikki's idea would require a separate freeze map, but now I think I was wrong. -- 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] Freezing without write I/O
On Fri, May 31, 2013 at 1:26 PM, Bruce Momjian br...@momjian.us wrote: On Thu, May 30, 2013 at 10:04:23PM -0400, Robert Haas wrote: Hm. Why? If freezing gets notably cheaper I don't really see much need for keeping that much clog around? If we still run into anti-wraparound areas, there has to be some major operational problem. That is true, but we have a decent number of customers who do in fact have such problems. I think that's only going to get more common. As hardware gets faster and PostgreSQL improves, people are going to process more and more transactions in shorter and shorter periods of time. Heikki's benchmark results for the XLOG scaling patch show rates of 80,000 tps. Even at a more modest 10,000 tps, with default settings, you'll do anti-wraparound vacuums of the entire cluster about every 8 hours. That's not fun. Are you assuming these are all write transactions, hence consuming xids? Well, there might be read-only transactions as well, but the point is about how many write transactions there can be. 10,000 tps or more is not out of the question even today, and progressively higher numbers are only going to become more and more common. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] extensible external toast tuple support
On Thu, May 30, 2013 at 7:42 AM, Andres Freund and...@2ndquadrant.com wrote: In http://archives.postgresql.org/message-id/20130216164231.GA15069%40awork2.anarazel.de I presented the need for 'indirect' toast tuples which point into memory instead of a toast table. In the comments to that proposal, off-list and in-person talks the wish to make that a more general concept has been voiced. The previous patch used varattrib_1b_e.va_len_1be to discern between different types of external tuples. That obviously only works if the data sizes of all possibly stored datum types are distinct which isn't nice. So what the newer patch now does is to rename that field into 'va_tag' and decide based on that what kind of Datum we have. To get the actual length of that datum there now is a VARTAG_SIZE() macro which maps the tags back to size. To keep on-disk compatibility the size of an external toast tuple containing a varatt_external is used as its tag value. This should allow for fairly easy development of a new compression scheme for out-of-line toast tuples. It will *not* work for compressed inline tuples (i.e. VARATT_4B_C). I am not convinced that that is a problem or that if it is, that it cannot be solved separately. FWIW, in some quick microbenchmarks I couldn't find any performance difference due to the slightly more complex size computation which I do *not* find surprising. Opinions? Seems pretty sensible to me. The patch is obviously WIP but the direction seems fine to me. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump with postgis extension dumps rules separately
On Wed, May 29, 2013 at 6:55 PM, Joe Conway m...@joeconway.com wrote: OK, simple enough. New patch attached. I still need to do some testing to verify this does not break anything, but other than that, any complaints (including the notion of backpatching this back to 9.1)? Here's a cleaned up version, which also includes documentation. I'll commit back to 9.1 in a day or two unless there are any objections. Changing SQL syntax in the back-branches isn't normally something we do, but I confess I don't see any real reason not to do it in this case. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
