Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Tuesday, June 18, 2013 11:25 PM Josh Berkus wrote: Amit, I think, the decision of name, we can leave to committer with below possibilities, as it is very difficult to build consensus on any particular name. Auto.conf System.auto.conf Postgresql.auto.conf Persistent.auto.conf Reasons for auto.conf as a choice above all of the previous: 1) short 2) significantly different from postgresql.conf What is the advantage to keep name significantly different from postgresql.conf, rather will it be not helpful for use if it is similar to postgresql.conf? 3) near the beginning of the alphabet, so it should sort near the top if there are other files in conf/ directory How would user correlate auto.conf contains configuration parameters set by Alter System command? I had kept persistent.auto.conf based on suggestion in this thread that user can easily correlate it with SET PERSISTENT command. So based on that theory I think it would be better to name it as System.auto.conf or Postgresql.auto.conf With Regards, Amit Kapila. -- 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] Git-master regression failure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 On 18-06-2013 22:18, Jeff Janes wrote: In Danish, apparently 'AA' 'WA', so two more rows show up. Yes of course We have three extra vowels following Z (namely Æ, Ø and Å) and for keyboard missing those essential keys we have an official alternate way to write them as AE , OE and AA. Which of course means that AA is larger than any other letter ;) Nice find :) Svenne -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.20 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQI5BAEBCAAjBQJRwUjzHBpodHRwOi8vc3Zlbm5lLmRrL3BncC9wb2xpY3kACgkQ /zLSj+olL/LfqxAAqZ6Qc9uC7lZ/gE/ZT5RjMqEudQvHvRxGiUQLcLdif/n2novf yf3Phe7gJOMPgt7V47dT5Wu/l3/PunV6Yo2Q1Ifg+XsrvUXC4CDMLo/RDP97EUwZ B96o1UH2Tx1+GnaJrZkcjq5V8y+4X5QJ6+vP11SEUyuMl7Ist2EegMet55N3ndTe W/mjZr5uTpmEyBr1aMJo6RJ3T1gxPLelc2b4aA5jnQlO7RbXlCgtWUQzQ/Q/dBWb kuE0MEd9CSR4kuo9u6yImVKUMUdX+NGgOeD6D36UtS4pdvnWOI+gX95Mze4AfiY/ ZPt6mCv0afBfQW/uioctHDLScY9v6cT58dkzrLjWyuNd6CfvqBiWfXBygwUclDZK cWpuszwIjVaOnHiDlHK8sg7yXU+FkhV5V8PXz6KBulCPxmR+Z8oI0AKiU2MhRIe7 mQhgDHgHT6nBpqWGlmX3VAjiTL6+/DZT34TnxNqdLDtWKrHUK+2KzZ/kYJ97zwo4 C78UX6F+1/6JxOkMVjm77M1ZQx306pJC8YGS3+7/fRh1EZd3LQXPaOXCU2EtM6UL sBaafDB5yMEZsdNw3DKOLnbg/bTq9MPa2uBZdEJ1w8LsvNrSeOohTXZFn2PL2zoJ L8JnJcAndM3SJG+UO1K1ALJhZlD0oRovYnzolAlKCXavNTn+zBd2uyNYSIQ= =qC25 -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] XLogInsert scaling, revisited
On 18.06.2013 21:17, Jeff Janes wrote: Hi Heikki, I am getting conflicts applying version 22 of this patch to 9.4dev. Could you rebase? Here you go. Does anyone know of an easy way to apply an external patch through git, so I can get git-style merge conflict markers, rather than getting patch's reject file? I've been wishing for that too. You can check out an older version of the branch, one that the patch applies cleanly to, and then merge forward. But that's cumbersome. - Heikki xloginsert-scale-23.patch.gz Description: GNU Zip compressed 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] How do we track backpatches?
On Wed, Jun 19, 2013 at 4:44 AM, Peter Eisentraut pete...@gmx.net wrote: On Tue, 2013-06-18 at 12:32 +0200, Magnus Hagander wrote: The CF app was and is specifically for dealing with CFs. Having it deal with backpatches makes it, well, a bugtracker. It's not meant to be that. If we want a bugtracker, then it has very different requirements. It's not in evidence that the requirements are different. The CF app is basically a list of lists of patches with date information and associated person's names. Tracking backpatch candidates doesn't sound that much different. (That said, I'm not convinced backpatches need any tracking at all, but if they did, I think the CF app would be just fine.) Having an always-open CF would defeat the workflow. I'd imagine having a CF entry per release, so after a set of minor releases, the CF is closed. Oh, I think I misunderstood what you meant. That way does make a lot more sense than what I thought you were saying :) I shall withdraw my objection. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] extensible external toast tuple support snappy prototype
On Wed, Jun 5, 2013 at 8:01 AM, Andres Freund and...@2ndquadrant.comwrote: Two patches attached: 1) add snappy to src/common. The integration needs some more work. 2) Combined patch that adds indirect tuple and snappy compression. Those coul be separated, but this is an experiment so far... I took a look at them a little. This proposal is a super set of patch #1127. https://commitfest.postgresql.org/action/patch_view?id=1127 - endian.h is not found in my mac. Commented it out, it builds clean. - I don't see what the added is_inline flag means in toast_compress_datum(). Obviously not used, but I wonder what was the intention. - By this, * compression method. We could just use the two bytes to store 3 other * compression methods but maybe we better don't paint ourselves in a * corner again. you mean two bits, not two bytes? And patch adds snappy-c in src/common. I definitely like the idea to have pluggability for different compression algorithm for datum, but I am not sure if this location is a good place to add it. Maybe we want a modern algorithm other than pglz for different components across the system in core, and it's better to let users choose which to add more. The mapping between the index number and compression algorithm should be consistent for the entire life of database, so it should be defined at initdb time. From core maintainability perspective and binary size of postgres, I don't think we want to put dozenes of different algorithms into core in the future. And maybe someone will want to try BSD-incompatible algorithm privately. I guess it's ok to use one more byte to indicate which compression is used for the value. It is a compressed datum and we don't expect something short anyway. I don't see big problems in this patch other than how to manage the pluggability, but it is a WIP patch anyway, so I'm going to mark it as Returned with Feedback. Thanks, -- Hitoshi Harada
Re: [HACKERS] extensible external toast tuple support snappy prototype
On 2013-06-19 00:15:56 -0700, Hitoshi Harada wrote: On Wed, Jun 5, 2013 at 8:01 AM, Andres Freund and...@2ndquadrant.comwrote: Two patches attached: 1) add snappy to src/common. The integration needs some more work. 2) Combined patch that adds indirect tuple and snappy compression. Those coul be separated, but this is an experiment so far... I took a look at them a little. This proposal is a super set of patch #1127. https://commitfest.postgresql.org/action/patch_view?id=1127 - endian.h is not found in my mac. Commented it out, it builds clean. - I don't see what the added is_inline flag means in toast_compress_datum(). Obviously not used, but I wonder what was the intention. Hm. I don't think you've looked at the latest version of the patch, check http://archives.postgresql.org/message-id/20130614230625.gd19...@awork2.anarazel.de - that should be linked from the commitfest. The is_inline part should be gone there. - By this, * compression method. We could just use the two bytes to store 3 other * compression methods but maybe we better don't paint ourselves in a * corner again. you mean two bits, not two bytes? Yes, typo... The plan is to use those two bits in the following way - 00 pglz - 01 snappy/lz4/whatever - 10 another - 11 one extra byte And patch adds snappy-c in src/common. I definitely like the idea to have pluggability for different compression algorithm for datum, but I am not sure if this location is a good place to add it. Maybe we want a modern algorithm other than pglz for different components across the system in core, and it's better to let users choose which to add more. The mapping between the index number and compression algorithm should be consistent for the entire life of database, so it should be defined at initdb time. From core maintainability perspective and binary size of postgres, I don't think we want to put dozenes of different algorithms into core in the future. And maybe someone will want to try BSD-incompatible algorithm privately. We've argued about this in the linked thread and I still think we should add one algorithm now, and when that one is outdated - which probably will be some time - replace it. Building enough infrastructure to make this really pluggable is not likely enough to be beneficial to many. There will be a newer version of the patch coming today or tomorrow, so there's probably no point in looking at the one linked above 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
[HACKERS] Re: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist
2013/6/17 Josh Kupershmidt schmi...@gmail.com: On Fri, Mar 8, 2013 at 11:58 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I'll see - please, stay tuned to 9.4 first commitfest Hi Pavel, Just a reminder, I didn't see this patch in the current commitfest. I would be happy to spend some more time reviewing if you wish to pursue the patch. Hello yes, I hadn't free time for finalization of last patch. I hope so I can do final version next month to next commitfest. Regards and thank you Pavel Josh -- 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] SET work_mem = '1TB';
On 18 June 2013 22:57, Fujii Masao masao.fu...@gmail.com wrote: On Wed, Jun 19, 2013 at 2:40 AM, Simon Riggs si...@2ndquadrant.com wrote: On 18 June 2013 17:10, Fujii Masao masao.fu...@gmail.com wrote: On Tue, Jun 18, 2013 at 1:06 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Tuesday, May 21, 2013, Simon Riggs wrote: I worked up a small patch to support Terabyte setting for memory. Which is OK, but it only works for 1TB, not for 2TB or above. I've incorporated my review into a new version, attached. Added TB to the docs, added the macro KB_PER_TB, and made show to print 1TB rather than 1024GB. Looks good to me. But I found you forgot to change postgresql.conf.sample, so I changed it and attached the updated version of the patch. Barring any objection to this patch and if no one picks up this, I will commit this. In truth, I hadn't realised somebody had added this to the CF. It was meant to be an exploration and demonstration that further work was/is required rather than a production quality submission. AFAICS it is still limited to '1 TB' only... Yes. Thank you both for adding to this patch. Since you've done that, it seems churlish of me to interrupt that commit. I was thinking that this is the infrastructure patch for your future proposal, i.e., support higher values of TBs. But if it interferes with your future proposal, of course I'm okay to drop this patch. Thought? Yes, please commit. -- Simon Riggs 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] GIN improvements part2: fast scan
On 18.06.2013 23:59, Alexander Korotkov wrote: I would like to illustrate that on example. Imagine you have fulltext query rare_term frequent_term. Frequent term has large posting tree while rare term has only small posting list containing iptr1, iptr2 and iptr3. At first we get iptr1 from posting list of rare term, then we would like to check whether we have to scan part of frequent term posting tree where iptr iptr1. So we call pre_consistent([false, true]), because we know that rare term is not present for iptr iptr2. pre_consistent returns false. So we can start scanning frequent term posting tree from iptr1. Similarly we can skip lags between iptr1 and iptr2, iptr2 and iptr3, from iptr3 to maximum possible pointer. Thanks, now I understand the rare-term frequent-term problem. Couldn't you do that with the existing consistent function? I don't see why you need the new pre-consistent function for this. - 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] Add visibility map information to pg_freespace.
Thank you.
This makes sense to me. I only lament the fact that this makes the
module a misnomer. Do we want to 1) rename the module (how
inconvenient), 2) create a separate module for this (surely not
warranted), or 3) accept it and move on?
Although I also feel uneasy with the module name, I suppose this
is not so major change as changing the module name.
I'm not sure why this is suggested as being part of pg_freespace and
not part of pageinspect? (Which is where all the other inspection
tools live).
I'm afraid I wasn't aware of that. I think the following
operation shows the info of fsm.
| =# select fsm_page_contents(get_raw_page('t', 'fsm', 0));
| fsm_page_contents
| ---
| 0: 147 +
| 1: 147 +
...
| 2047: 147+
| 4095: 147+
| fp_next_slot: 0 +
If this is the only way to inspect fsm info with this module, I
can't say it is consise enough just to know the fsm info
corresponds to certain heap block. pg_freespace seems preferable
for such a purpose.
Following the manner shown above, I'll provide vm_page_contents
then command and it'll show result as following.
| =# select vm_page_contents(get_raw_page('t', 'vm', 0));
| v_page_contents
| ---
| 0: t +
| 1: f +
...
| 65343: t +
# Too long...
It should useful in other aspects but it seems a bit complicated
just to know about visibility bits for certain blocks.
If I wanted to see the vismap (and I do...) then I'd like to see the
whole vismap, not just the part that relates to blocks currently in
cache.
If you do want that, you can just join the two things together
(function to see vismap joined to pg_freespace).
From the aspect of interface, thay look to be separate
functions.
On the other hand there's no problem to add vm_page_contents to
pageinspect, although in another output format. It'll look like,
| v_page_contents
| ---
| : 0 0 1 1 1 0 0 0 0 0 1 1 0 0 1 0 +
| 0001: 0 0 0 0 0 0 0 0 0 0 1 0 0 1 0 0 +
| : 0 0 0 0 0 0 0 0 0 0 1 0 0 1 0 0 +
...
| ff30: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
# 't' and 'f' are too confusing beeing shown in a line...
Any suggestions for the output format?
(Having said that, I don't have a major objection to it being in
pg_freespace as well).
I prefer to leave that issue this time for anyone - including me :-p
--
Kyotaro Horiguchi
NTT Open Source Software Center
--
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] GIN improvements part2: fast scan
On Wed, Jun 19, 2013 at 11:48 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 18.06.2013 23:59, Alexander Korotkov wrote: I would like to illustrate that on example. Imagine you have fulltext query rare_term frequent_term. Frequent term has large posting tree while rare term has only small posting list containing iptr1, iptr2 and iptr3. At first we get iptr1 from posting list of rare term, then we would like to check whether we have to scan part of frequent term posting tree where iptr iptr1. So we call pre_consistent([false, true]), because we know that rare term is not present for iptr iptr2. pre_consistent returns false. So we can start scanning frequent term posting tree from iptr1. Similarly we can skip lags between iptr1 and iptr2, iptr2 and iptr3, from iptr3 to maximum possible pointer. Thanks, now I understand the rare-term frequent-term problem. Couldn't you do that with the existing consistent function? I don't see why you need the new pre-consistent function for this. In the case of two entries I can. But in the case of n entries things becomes more complicated. Imagine you have term_1 term_2 ... term_n query. When you get some item pointer from term_1 you can skip all the lesser item pointers from term_2, term_3 ... term_n. But if all you have for it is consistent function you have to call it with following check arguments: 1) [false, false, false, ... , false] 2) [false, true, false, ... , false] 3) [false, false, true, ... , false] 4) [false, true, true, ..., false] .. i.e. you have to call it 2^(n-1) times. But if you know the query specific (i.e. in opclass) it's typically easy to calculate exactly what we need in single pass. That's why I introduced pre_consistent. -- With best regards, Alexander Korotkov.
Re: [HACKERS] GIN improvements part2: fast scan
On Wed, Jun 19, 2013 at 12:30 PM, Alexander Korotkov aekorot...@gmail.comwrote: On Wed, Jun 19, 2013 at 11:48 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 18.06.2013 23:59, Alexander Korotkov wrote: I would like to illustrate that on example. Imagine you have fulltext query rare_term frequent_term. Frequent term has large posting tree while rare term has only small posting list containing iptr1, iptr2 and iptr3. At first we get iptr1 from posting list of rare term, then we would like to check whether we have to scan part of frequent term posting tree where iptr iptr1. So we call pre_consistent([false, true]), because we know that rare term is not present for iptr iptr2. pre_consistent returns false. So we can start scanning frequent term posting tree from iptr1. Similarly we can skip lags between iptr1 and iptr2, iptr2 and iptr3, from iptr3 to maximum possible pointer. Thanks, now I understand the rare-term frequent-term problem. Couldn't you do that with the existing consistent function? I don't see why you need the new pre-consistent function for this. In the case of two entries I can. But in the case of n entries things becomes more complicated. Imagine you have term_1 term_2 ... term_n query. When you get some item pointer from term_1 you can skip all the lesser item pointers from term_2, term_3 ... term_n. But if all you have for it is consistent function you have to call it with following check arguments: 1) [false, false, false, ... , false] 2) [false, true, false, ... , false] 3) [false, false, true, ... , false] 4) [false, true, true, ..., false] .. i.e. you have to call it 2^(n-1) times. To be precise you don't need the first check argument I listed. So, it's 2^(n-1)-1 calls. -- With best regards, Alexander Korotkov.
Re: [HACKERS] GIN improvements part2: fast scan
On 19.06.2013 11:30, Alexander Korotkov wrote: On Wed, Jun 19, 2013 at 11:48 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 18.06.2013 23:59, Alexander Korotkov wrote: I would like to illustrate that on example. Imagine you have fulltext query rare_term frequent_term. Frequent term has large posting tree while rare term has only small posting list containing iptr1, iptr2 and iptr3. At first we get iptr1 from posting list of rare term, then we would like to check whether we have to scan part of frequent term posting tree where iptr iptr1. So we call pre_consistent([false, true]), because we know that rare term is not present for iptr iptr2. pre_consistent returns false. So we can start scanning frequent term posting tree from iptr1. Similarly we can skip lags between iptr1 and iptr2, iptr2 and iptr3, from iptr3 to maximum possible pointer. Thanks, now I understand the rare-term frequent-term problem. Couldn't you do that with the existing consistent function? I don't see why you need the new pre-consistent function for this. In the case of two entries I can. But in the case of n entries things becomes more complicated. Imagine you have term_1 term_2 ... term_n query. When you get some item pointer from term_1 you can skip all the lesser item pointers from term_2, term_3 ... term_n. But if all you have for it is consistent function you have to call it with following check arguments: 1) [false, false, false, ... , false] 2) [false, true, false, ... , false] 3) [false, false, true, ... , false] 4) [false, true, true, ..., false] .. i.e. you have to call it 2^(n-1) times. But if you know the query specific (i.e. in opclass) it's typically easy to calculate exactly what we need in single pass. That's why I introduced pre_consistent. Hmm. So how does that work with the pre-consistent function? Don't you need to call that 2^(n-1)-1 times as well? - 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] GIN improvements part2: fast scan
On Wed, Jun 19, 2013 at 12:49 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 19.06.2013 11:30, Alexander Korotkov wrote: On Wed, Jun 19, 2013 at 11:48 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 18.06.2013 23:59, Alexander Korotkov wrote: I would like to illustrate that on example. Imagine you have fulltext query rare_term frequent_term. Frequent term has large posting tree while rare term has only small posting list containing iptr1, iptr2 and iptr3. At first we get iptr1 from posting list of rare term, then we would like to check whether we have to scan part of frequent term posting tree where iptr iptr1. So we call pre_consistent([false, true]), because we know that rare term is not present for iptr iptr2. pre_consistent returns false. So we can start scanning frequent term posting tree from iptr1. Similarly we can skip lags between iptr1 and iptr2, iptr2 and iptr3, from iptr3 to maximum possible pointer. Thanks, now I understand the rare-term frequent-term problem. Couldn't you do that with the existing consistent function? I don't see why you need the new pre-consistent function for this. In the case of two entries I can. But in the case of n entries things becomes more complicated. Imagine you have term_1 term_2 ... term_n query. When you get some item pointer from term_1 you can skip all the lesser item pointers from term_2, term_3 ... term_n. But if all you have for it is consistent function you have to call it with following check arguments: 1) [false, false, false, ... , false] 2) [false, true, false, ... , false] 3) [false, false, true, ... , false] 4) [false, true, true, ..., false] .. i.e. you have to call it 2^(n-1) times. But if you know the query specific (i.e. in opclass) it's typically easy to calculate exactly what we need in single pass. That's why I introduced pre_consistent. Hmm. So how does that work with the pre-consistent function? Don't you need to call that 2^(n-1)-1 times as well? I call pre-consistent once with [false, true, true, ..., true]. Pre-consistent knows that each true passed to it could be false positive. So, if it returns false it guarantees that consistent will be false for all possible combinations. -- With best regards, Alexander Korotkov.
Re: [HACKERS] Add visibility map information to pg_freespace.
On 19 June 2013 09:19, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: It should useful in other aspects but it seems a bit complicated just to know about visibility bits for certain blocks. With your current patch you can only see the visibility info for blocks in cache, not for all blocks. So while you may think it is useful, it is also unnecessarily limited in scope. Let's just have something that is easy to use that lets us see the visibility state for a block, not just blocks in freespace. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add visibility map information to pg_freespace.
On 2013-06-19 10:03:40 +0100, Simon Riggs wrote: On 19 June 2013 09:19, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: It should useful in other aspects but it seems a bit complicated just to know about visibility bits for certain blocks. With your current patch you can only see the visibility info for blocks in cache, not for all blocks. So while you may think it is useful, it is also unnecessarily limited in scope. pg_freespace should do more than that? Are you thinking of pg_buffercache? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add visibility map information to pg_freespace.
On 19 June 2013 10:15, Andres Freund and...@2ndquadrant.com wrote: On 2013-06-19 10:03:40 +0100, Simon Riggs wrote: On 19 June 2013 09:19, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: It should useful in other aspects but it seems a bit complicated just to know about visibility bits for certain blocks. With your current patch you can only see the visibility info for blocks in cache, not for all blocks. So while you may think it is useful, it is also unnecessarily limited in scope. pg_freespace should do more than that? Are you thinking of pg_buffercache? I was... my mistake. Please continue Kyotaro. -- Simon Riggs 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] Bugfix and new feature for PGXS
Le mercredi 19 juin 2013 04:48:11, Peter Eisentraut a écrit : On Tue, 2013-06-18 at 15:52 +0200, Cédric Villemain wrote: This allows for example to install hstore header and be able to include them in another extension like that: # include contrib/hstore/hstore.h That's not going to work. hstore's header file is included as #include hstore.h (cf. hstore source code). Having it included under different paths in different contexts will be a mess. OK. At the beginning I though of putting headers in include/contrib but feared that some header may have the same name. If you think that it is suitable, I can do that ? (and include the clean: recipe that I missed on the first shot) Also, do we want to keep the word 'contrib' ? include/extension looks fine too... -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] [PATCH] Remove useless USE_PGXS support in contrib
Le mercredi 19 juin 2013 04:58:15, Peter Eisentraut a écrit : On Mon, 2013-06-17 at 19:00 +0200, Cédric Villemain wrote: My only grief is to loose the perfect regression tests for PGXS those contribs are. I think they are neither perfect nor regression tests. If we want tests, let's write tests. You are right. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
[HACKERS] Implementing incremental backup
Hi, I'm thinking of implementing an incremental backup tool for PostgreSQL. The use case for the tool would be taking a backup of huge database. For that size of database, pg_dump is too slow, even WAL archive is too slow/ineffective as well. However even in a TB database, sometimes actual modified blocks are not that big, may be even several GB. So if we can backup those modified blocks only, that would be an effective incremental backup method. For now, my idea is pretty vague. - Record info about modified blocks. We don't need to remember the whole history of a block if the block was modified multiple times. We just remember that the block was modified since the last incremental backup was taken. - The info could be obtained by trapping calls to mdwrite() etc. We need to be careful to avoid such blocks used in xlogs and temporary tables to not waste resource. - If many blocks were modified in a file, we may be able to condense the info as the whole file was modified to reduce the amount of info. - How to take a consistent incremental backup is an issue. I can't think of a clean way other than locking whole cluster, which is obviously unacceptable. Maybe we should give up hot backup? Comments, thoughts are welcome. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[Review] Re: [HACKERS] [PATCH] Remove useless USE_PGXS support in contrib
Le jeudi 13 juin 2013 05:16:48, Peter Eisentraut a écrit : This has served no purpose except to 1. take up space 2. confuse users 3. produce broken external extension modules that take contrib as an example 4. break builds of PostgreSQL when users try to fix 3. by exporting USE_PGXS There is adequate material in the documentation and elsewhere (PGXN) on how to write extensions and their makefiles, so this is not needed. --- pursuant to discussion here: http://www.postgresql.org/message-id/512ceab8.9010...@gmx.net * Submission review: patch apply on HEAD, no doc or test required. * Usability review ** Does the patch actually implement that? yes ** Do we want that? Consensus is not complete: some use case raised. 1/ regression test: not a good excuse, see [1] 2/ being able to build contrib out of tree, it is unsure it is really needed on its own but was suggested. See [2] and [3] Arguments against removal are new features (extension layout, more work on PGXS shoulders, extension headers exported, clean regression test for PGXS) ** Does it follow the community-agreed behavior? Some people voiced against the idea. More answers might be better to confirm that this is wanted. Amul, Joe, Craig ? ** Are there dangers? The only I can see is packagers building contribs with PGXS, but as it is currently buggy I'm sure they can't do that. * Feature test: it deprecates a not-fully-implemented-feature (even fully implemented this may not be considered a feature at all) * Performance review: not relevant (contribs may build some µs faster...) * Coding review: OK * Architecture review: looks good too. The patch needs to reach consensus before commit. There is no status for that in CF, for me current status is: 'Ready, Waiting more feedback from community'. [1] http://www.postgresql.org/message- id/1371610695.13762.25.ca...@vanquo.pezone.net [2] http://www.postgresql.org/message- id/1371172850.79798.yahoomail...@web193505.mail.sg3.yahoo.com [3] http://www.postgresql.org/message-id/51bbe3a5.40...@2ndquadrant.com -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Optimizing pglz compressor
On Tuesday, March 05, 2013 7:03 PM Heikki Linnakangas wrote: I spent some more time on this, and came up with the attached patch. It includes the changes I posted earlier, to use indexes instead of pointers in the hash table. In addition, it makes the hash table size variable, depending on the length of the input. This further reduces the startup cost on small inputs. I changed the hash method slightly, because the old method would not use any bits from the 3rd byte with a small hash table size, but fortunately that didn't seem to negative impact with larger hash table sizes either. I wrote a little C extension to test this. It contains a function, which runs pglz_compress() on a bytea input, N times. I ran that with different kinds of inputs, and got the following results: The purpose of this patch is to improve LZ compression speed by reducing the startup cost of initialization of hash_start array. To achieve the same it uses variable hash and reduced the size of each history entry by replacing pointers with int16 indexes. It achieves it's purpose for small data, but for large data in some cases performance is degaraded, refer second set of performance data. 1. Patch compiles cleanly and all regression tests passed. 2. Change in pglz_hist_idx macro is not very clear to me, neither it is mentioned in comments 3. Why first entry is kept as INVALID_ENTRY? It appears to me, it is for cleaner checks in code. Performance Data -- I have used pglz-variable-size-hash-table.patch to collect all performance data: Results of compress-tests.sql -- inserting large data into tmp table -- testname |unpatched | patched ---+--+ 5k text | 4.8932 | 4.9014 512b text | 22.6209 | 18.6849 256b text | 13.9784 | 8.9342 1K text | 20.4969 | 20.5988 2k random | 10.5826 | 10.0758 100k random | 3.9056 | 3.8200 500k random | 22.4078 | 22.1971 512b random | 15.7788 | 12.9575 256b random | 18.9213 | 12.5209 1K random | 11.3933 | 9.8853 100k of same byte | 5.5877 | 5.5960 500k of same byte | 2.6853 | 2.6500 Observation - 1. This clearly shows that the patch improves performance for small data without any impact for large data. Performance data for directly calling lz_compress function (tests.sql) --- select testname, (compresstest(data, nrows, 8192)::numeric / 1000)::numeric(10,3) as auto from tests; Head testname | auto ---+--- 5k text | 3511.879 512b text | 1430.990 256b text | 1768.796 1K text | 1390.134 3K text | 4099.304 2k random | -402.916 100k random | -10.311 500k random |-2.019 512b random | -753.317 256b random | -1096.999 1K random | -559.931 10k of same byte | 3548.651 100k of same byte | 36037.280 500k of same byte | 25565.195 (14 rows) Patch(pglz-variable-size-hash-table.patch) testname | auto ---+--- 5k text | 3840.207 512b text | 1088.897 256b text | 982.172 1K text | 1402.488 3K text | 4334.802 2k random | -333.100 100k random |-8.390 500k random |-1.672 512b random | -499.251 256b random | -524.889 1K random | -453.177 10k of same byte | 4754.367 100k of same byte | 50427.735 500k of same byte | 36163.265 (14 rows) Observations -- 1. For small data perforamce is always good with patch. 2. For random small/large data performace is good. 3. For medium and large text and same byte data(3K,5K text, 10K,100K,500K same byte), performance is degraded. I have used attached compress-tests-init.sql to generate data. I am really not sure why the data you reported and what I taken differ in few cases. I had tried multiple times but the result is same. Kindly let me know if you think I am doing something wrong. Note - To generate data in randomhex, I used Copy from file. I used same command you provided to generate a file. With Regards, Amit Kapila. compress-tests-init.sql 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] Patch for removng unused targets
Hi Harada-san, Thank you for the review. I think that the parse tree has enough information to do this optimization and that the easiest way to do it is to use the information, though I might not have understand your comments correctly. So, I would like to fix the bug by simply modifying the removability check in adjust_targetlist() so that the resjunk column is not used in GROUP BY, DISTINCT ON and *window PARTITION/ORDER BY*, besides ORDER BY. No? I am open to any comments. Thanks, Best regards, Etsuro Fujita From: Hitoshi Harada [mailto:umi.tan...@gmail.com] Sent: Wednesday, June 19, 2013 2:57 PM To: Etsuro Fujita Cc: Tom Lane; Alexander Korotkov; pgsql-hackers Subject: Re: [HACKERS] Patch for removng unused targets On Tue, Jun 18, 2013 at 5:15 AM, Etsuro Fujita fujita.ets...@lab.ntt.co.jp wrote: Hi Alexander, I wrote: From: Tom Lane [mailto:t...@sss.pgh.pa.us] resjunk means that the target is not supposed to be output by the query. Since it's there at all, it's presumably referenced by ORDER BY or GROUP BY or DISTINCT ON, but the meaning of the flag doesn't depend on that. What you would need to do is verify that the target is resjunk and not used in any clause besides ORDER BY. I have not read your patch, but I rather imagine that what you've got now is that the parser checks this and sets the new flag for consumption far downstream. Why not just make the same check in the planner? I've created a patch using this approach. I've rebased the above patch against the latest head. Could you review the patch? If you have no objection, I'd like to mark the patch ready for committer. Sorry, I've had a cleanup of the patch. Please find attached the patch. Don't forget about window functions! test=# EXPLAIN (ANALYZE, VERBOSE) SELECT *, count(*) over (partition by slow_func(x,y)) FROM test ORDER BY slow_func(x,y) LIMIT 10; QUERY PLAN --- Limit (cost=0.28..3.52 rows=10 width=16) (actual time=20.860..113.764 rows=10 loops=1) Output: x, y, (count(*) OVER (?)) - WindowAgg (cost=0.28..324.27 rows=1000 width=16) (actual time=20.858..113.747 rows=10 loops=1) Output: x, y, count(*) OVER (?) - Index Scan using test_idx on public.test (cost=0.28..59.27 rows=1000 width=16) (actual time=10.563..113.530 rows=11 loops=1) Output: slow_func(x, y), x, y Total runtime: 117.889 ms (7 rows) And I don't think it's a good idea to rely on the parse tree to see if we can remove those unused columns from the target list, because there should be a lot of optimization that has been done through grouping_planner, and the parse tree is not necessarily representing the corresponding elements at this point. I think it'd be better to see path keys to find out the list of elements that may be removed, rather than SortClause, which would be a more generalized approach. Thanks, -- Hitoshi Harada
Re: [HACKERS] Review: UNNEST (and other functions) WITH ORDINALITY
On 19 June 2013 04:11, David Fetter da...@fetter.org wrote:
On Tue, Jun 18, 2013 at 11:36:08AM +0100, Dean Rasheed wrote:
On 17 June 2013 06:33, David Fetter da...@fetter.org wrote:
Next revision of the patch, now with more stability. Thanks, Andrew!
Rebased vs. git master.
Here's my review of the WITH ORDINALITY patch.
Overall I think that the patch is in good shape, and I think that this
will be a very useful new feature, so I'm keen to see this committed.
All the basic stuff is OK --- the patch applies cleanly, compiles with
no warnings, and has appropriate docs and new regression tests which
pass. I also tested it fairly thoroughly myself, and I couldn't break
it. Everything worked as I expected, and it works nicely with LATERAL.
I have a few minor comments that should be considered before passing
it on to a committer:
1). In func.sgml, the new doc example unnest WITH ORDINALITY is
mislablled, since it it's not actually an example of unnest().
Fixed in patch attached.
Also it doesn't really belong in that code example block, which is
about generate_subscripts(). I think that there should probably be a
new sub-section starting at that point for WITH ORDINALITY. Perhaps
it should start with a brief paragraph explaining how WITH
ORDINALITY can be applied to functions in the FROM clause of a
query.
How's the attached?
Looks good.
[Actually it appears that WITH ORDINALITY works with non-SRF's too,
but that's less useful, so I think that the SRF section is probably
still the right place to document this]
As of this patch, that's now both in the SELECT docs and the SRF
section.
It might also be worth mentioning here that currently WITH ORDINALITY
is not supported for functions that return records.
Added.
In the code example itself, the prompt should be trimmed down to match
the previous examples.
Done.
Oh, on closer inspection, the previous examples mostly don't show the
prompt at all, except for the last one. So perhaps it should be
removed from both those places.
2). In the SELECT docs, where function_name is documented, I would be
tempted to start a new paragraph for the sentence starting If the
function has been defined as returning the record data type..., since
that's really a separate syntax. I think that should also make mention
of the fact that WITH ORDINALITY is not currently supported in that
case.
Done-ish. What do you think?
Hmm, I fear that might have made it worse, because now it reads as if
functions that return records can't be used in the FROM clause at all
(at least if you don't read all the way to the end, which many people
don't). I think if you just take out this change:
Function calls can appear in the literalFROM/literal
clause. (This is especially useful for functions that return
-result sets, but any function can be used.) This acts as
+result sets, but any function except those that return
+literal[SETOF] RECORD/literal can be used.) This acts as
then what's left is OK.
3). I think it would be good to have a more meaningful default name
for the new ordinality column, rather than ?column?. In many cases
the user might then choose to not bother giving it an alias. It could
simply be called ordinality by default, since that's non-reserved.
I don't think this needs doing, per spec. The column name needs to be
unique, and if someone happens to name an output column of a function,
?column?, that's really not our problem.
I don't think the spec says anything about how the new column should
be named, so it's up to us, but I do think a sensible default would be
useful to save the user from having to give it an alias in many common
cases.
For example SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS file
would then produce a column that could be referred to in the rest of
the query as file.ordinality or simply ordinality. As it stands,
they'd have to write file.?column?, which is really ugly, so we're
effectively forcing them to supply an alias for this column every
time. I think it would be better if they only had to supply a name to
resolve name conflicts, or if they wanted a different name.
4). In gram.y, WITH_ORDINALITY should not be listed as an ordinary
keyword, but instead should be listed as a token below that (just
before WITH_TIME).
Done.
5). In plannodes.h, FunctionScan's new field should probably have a comment.
Done.
6). In parsenodes.h, the field added to RangeTblEntry is only valid
for function RTEs, so it should be moved to that group of fields and
renamed appropriately (unless you're expecting to extend it to other
RTE kinds in the future?).
Nope, and done.
Logically then, the new check for ordinality in
inline_set_returning_function() should be moved so that it is after
the check that the RTE actually a function RTE, and in
addRangeTableEntryForFunction() the RTE's ordinality field should be
set at the start along with
Re: [HACKERS] Memory leak in PL/pgSQL function which CREATE/SELECT/DROP a temporary table
From: Jeff Janes jeff.ja...@gmail.com On Tue, Jun 18, 2013 at 3:40 PM, MauMau maumau...@gmail.com wrote: Really? Would the catcache be polluted with entries for nonexistent tables? I'm surprised at this. I don't think it is necessary to speed up the query that fails with nonexistent tables, because such queries should be eliminated during application development. I was thinking the same thing, optimizing for failure is nice if there are no tradeoffs, but not so nice if it leaks memory. But apparently the negative cache was added for real reasons, not just theory. See discussion from when it was added: http://www.postgresql.org/message-id/19585.1012350...@sss.pgh.pa.us Thanks for the info. I (probably) understood why negative catcache entries are necessary. Hmm. I could repeat this, and it seems that the catcache for pg_statistic accumulates negative cache entries. Those slowly take up the memory. Seems that we should somehow flush those, when the table is dropped. Not sure how, but I'll take a look. As Heikki san said as above, there should be something wrong somewhere, shouldn't there? In my testing, just repeating CREATE (TEMPORARY) TABLE, SELECT against it, and DROP TABLE on it led to more than 400MB of CacheMemoryContext, after which I stopped the test. It seems that the catcache grows without bounds simply by repeating simple transactions. I wish to know the conditions where this happens and take all workarounds in my application to avoid the problem. Cooperation would be much appreciated. Regards MauMau -- 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] Implementing incremental backup
Tatsuo, * Tatsuo Ishii (is...@postgresql.org) wrote: I'm thinking of implementing an incremental backup tool for PostgreSQL. The use case for the tool would be taking a backup of huge database. For that size of database, pg_dump is too slow, even WAL archive is too slow/ineffective as well. However even in a TB database, sometimes actual modified blocks are not that big, may be even several GB. So if we can backup those modified blocks only, that would be an effective incremental backup method. I'm trying to figure out how that's actually different from WAL..? It sounds like you'd get what you're suggesting with simply increasing the checkpoint timeout until the WAL stream is something which you can keep up with. Of course, the downside there is that you'd have to replay more WAL when recovering. What about a tool which receives WALs but then compresses them across a longer period of time than the normal checkpointing by simply keeping in memory the current set of blocks modified and applying each WAL record against that block in memory as it reads the WAL? It would then purge that block out using a full-page WAL write at some pre-defined point, perhaps at the end of the overall backup? Consider this: connect the WAL-compressor to a PG backend, issue a 'start backup', which the WAL-compressor detects and then starts keeping track of every block changed in memory, applying the WAL stream of full page and non-full-page changes to the in memory set, until the 'stop backup' is called, at which point the WAL-compressor simply dumps all the records as full page writes into this new WAL stream. Or perhaps some combination of an 'always running' WAL compressor which simply reduces the overall size of the WAL stream with coordination around full backups. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Implementing incremental backup
On Wed, Jun 19, 2013 at 1:13 PM, Tatsuo Ishii is...@postgresql.org wrote: I'm thinking of implementing an incremental backup tool for PostgreSQL. The use case for the tool would be taking a backup of huge database. For that size of database, pg_dump is too slow, even WAL archive is too slow/ineffective as well. However even in a TB database, sometimes actual modified blocks are not that big, may be even several GB. So if we can backup those modified blocks only, that would be an effective incremental backup method. PostgreSQL definitely needs better tools to cope with TB scale databases. Especially when the ideas that get rid of anti-wraparound vacuums materialize and make huge databases more practical. For now, my idea is pretty vague. - Record info about modified blocks. We don't need to remember the whole history of a block if the block was modified multiple times. We just remember that the block was modified since the last incremental backup was taken. - The info could be obtained by trapping calls to mdwrite() etc. We need to be careful to avoid such blocks used in xlogs and temporary tables to not waste resource. Unless I'm missing something, the information about modified blocks can also be obtained by reading WAL, not requiring any modifications to core. - If many blocks were modified in a file, we may be able to condense the info as the whole file was modified to reduce the amount of info. You could keep a list of block ranges modified and when the list gets too large, merge ranges that are close together. - How to take a consistent incremental backup is an issue. I can't think of a clean way other than locking whole cluster, which is obviously unacceptable. Maybe we should give up hot backup? I don't see why regular pg_start_backup(), copy out modified blocks, pg_stop_backup(), copy WAL needed to recover approach wouldn't work here. A good feature of the tool would be to apply the incremental backup to the previous backup while copying out old blocks so you could have the latest full backup available and incremental changes to rewind it to the previous version. Regards, Ants Aasma -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- 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] Git-master regression failure
Svenne Krap svenne.li...@krap.dk wrote: On 18-06-2013 22:18, Jeff Janes wrote: In Danish, apparently 'AA' 'WA', so two more rows show up. Yes of course We have three extra vowels following Z (namely Æ, Ø and Å) and for keyboard missing those essential keys we have an official alternate way to write them as AE , OE and AA. Which of course means that AA is larger than any other letter ;) Does anyone object to the attached change, so that regression tests pass when run in a Danish locale? I think it should be back-patched to 9.2, where the test was introduced. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company*** a/src/test/regress/expected/create_index.out --- b/src/test/regress/expected/create_index.out *** *** 2652,2657 SELECT count(*) FROM tenk1 --- 2652,2659 -- -- Check behavior with duplicate index column contents + -- The upper limit on f1 is to protect against problems in Danish collations + -- due to 'AA' sorting above 'Z'. -- CREATE TABLE dupindexcols AS SELECT unique1 as id, stringu2::text as f1 FROM tenk1; *** *** 2659,2676 CREATE INDEX dupindexcols_i ON dupindexcols (f1, id, f1 text_pattern_ops); ANALYZE dupindexcols; EXPLAIN (COSTS OFF) SELECT count(*) FROM dupindexcols ! WHERE f1 'WA' and id 1000 and f1 ~~ 'YX'; ! QUERY PLAN ! --- Aggregate - Bitmap Heap Scan on dupindexcols ! Recheck Cond: ((f1 'WA'::text) AND (id 1000) AND (f1 ~~ 'YX'::text)) - Bitmap Index Scan on dupindexcols_i !Index Cond: ((f1 'WA'::text) AND (id 1000) AND (f1 ~~ 'YX'::text)) (5 rows) SELECT count(*) FROM dupindexcols ! WHERE f1 'WA' and id 1000 and f1 ~~ 'YX'; count --- 97 --- 2661,2678 ANALYZE dupindexcols; EXPLAIN (COSTS OFF) SELECT count(*) FROM dupindexcols ! WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id 1000 and f1 ~~ 'YX'; !QUERY PLAN ! Aggregate - Bitmap Heap Scan on dupindexcols ! Recheck Cond: ((f1 = 'WA'::text) AND (f1 = 'ZZZ'::text) AND (id 1000) AND (f1 ~~ 'YX'::text)) - Bitmap Index Scan on dupindexcols_i !Index Cond: ((f1 = 'WA'::text) AND (f1 = 'ZZZ'::text) AND (id 1000) AND (f1 ~~ 'YX'::text)) (5 rows) SELECT count(*) FROM dupindexcols ! WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id 1000 and f1 ~~ 'YX'; count --- 97 *** a/src/test/regress/sql/create_index.sql --- b/src/test/regress/sql/create_index.sql *** *** 876,881 SELECT count(*) FROM tenk1 --- 876,883 -- -- Check behavior with duplicate index column contents + -- The upper limit on f1 is to protect against problems in Danish collations + -- due to 'AA' sorting above 'Z'. -- CREATE TABLE dupindexcols AS *** *** 885,893 ANALYZE dupindexcols; EXPLAIN (COSTS OFF) SELECT count(*) FROM dupindexcols ! WHERE f1 'WA' and id 1000 and f1 ~~ 'YX'; SELECT count(*) FROM dupindexcols ! WHERE f1 'WA' and id 1000 and f1 ~~ 'YX'; -- -- Check ordering of =ANY indexqual results (bug in 9.2.0) --- 887,895 EXPLAIN (COSTS OFF) SELECT count(*) FROM dupindexcols ! WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id 1000 and f1 ~~ 'YX'; SELECT count(*) FROM dupindexcols ! WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id 1000 and f1 ~~ 'YX'; -- -- Check ordering of =ANY indexqual results (bug in 9.2.0) -- 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] Git-master regression failure
On 2013-06-19 06:18:20 -0700, Kevin Grittner wrote: Svenne Krap svenne.li...@krap.dk wrote: On 18-06-2013 22:18, Jeff Janes wrote: In Danish, apparently 'AA' 'WA', so two more rows show up. Yes of course We have three extra vowels following Z (namely Æ, Ø and Å) and for keyboard missing those essential keys we have an official alternate way to write them as AE , OE and AA. Which of course means that AA is larger than any other letter ;) Does anyone object to the attached change, so that regression tests pass when run in a Danish locale? I think it should be back-patched to 9.2, where the test was introduced. Don't we actually run make check/standard pg_regress with an enforced C locale? In which case this would imply some bigger problem we probably don't want to hide. 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] Git-master regression failure
On 2013-06-19 15:23:16 +0200, Andres Freund wrote: On 2013-06-19 06:18:20 -0700, Kevin Grittner wrote: Svenne Krap svenne.li...@krap.dk wrote: On 18-06-2013 22:18, Jeff Janes wrote: In Danish, apparently 'AA' 'WA', so two more rows show up. Yes of course We have three extra vowels following Z (namely Æ, Ø and Å) and for keyboard missing those essential keys we have an official alternate way to write them as AE , OE and AA. Which of course means that AA is larger than any other letter ;) Does anyone object to the attached change, so that regression tests pass when run in a Danish locale? I think it should be back-patched to 9.2, where the test was introduced. Don't we actually run make check/standard pg_regress with an enforced C locale? In which case this would imply some bigger problem we probably don't want to hide. Misremembered, we only do that optionally. So yes, seems sensible. 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] [PATCH] add --throttle to pgbench (submission 3)
On 05/01/13 04:57, Fabien COELHO wrote: Add --throttle to pgbench Each client is throttled to the specified rate, which can be expressed in tps or in time (s, ms, us). Throttling is achieved by scheduling transactions along a Poisson-distribution. This is an update of the previous proposal which fix a typo in the sgml documentation. The use case of the option is to be able to generate a continuous gentle load for functional tests, eg in a practice session with students or for testing features on a laptop. Why does this need two option formats (-H and --throttle)? Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] pgbench --throttle (submission 7 - with lag measurement)
I'm still getting the same sort of pauses waiting for input with your v11. Alas. This is a pretty frustrating problem; I've spent about two days so far trying to narrow down how it happens. I've attached the test program I'm using. It seems related to my picking a throttled rate that's close to (but below) the maximum possible on your system. I'm using 10,000 on a system that can do about 16,000 TPS when running pgbench in debug mode. This problem is 100% reproducible here; happens every time. This is a laptop running Mac OS X. It's possible the problem is specific to that platform. I'm doing all my tests with the database itself setup for development, with debug and assertions on. The lag spikes seem smaller without assertions on, but they are still there. Here's a sample: transaction type: SELECT only What is this test script? I'm doing pgbench for tests. scaling factor: 10 query mode: simple number of clients: 25 number of threads: 1 duration: 30 s number of transactions actually processed: 301921 average transaction lag: 1.133 ms (max 137.683 ms) tps = 10011.527543 (including connections establishing) tps = 10027.834189 (excluding connections establishing) And those slow ones are all at the end of the latency log file, as shown in column 3 here: 22 11953 3369 0 1371578126 954881 23 11926 3370 0 1371578126 954918 3 12238 30310 0 1371578126 984634 7 12205 30350 0 1371578126 984742 8 12207 30359 0 1371578126 984792 11 12176 30325 0 1371578126 984837 13 12074 30292 0 1371578126 984882 0 12288 175452 0 1371578127 126340 9 12194 171948 0 1371578127 126421 12 12139 171915 0 1371578127 126466 24 11876 175657 0 1371578127 126507 Indeed, there are two spikes, but not all clients are concerned. As I have not seen that, debuging is hard. I'll give it a try on tomorrow. When no one is sleeping, the timeout becomes infinite, so only returning data will break it. This is intended behavior though. This is not coherent. Under --throttle there should basically always be someone asleep, unless the server cannot cope with the load and *all* transactions are late. A no time out state looks pretty unrealistic, because it means that there is no throttling. I don't think the st-listen related code has anything to do with this either. That flag is only used to track when clients have completed sending their first query over to the server. Once reaching that point once, afterward they should be listening for results each time they exit the doCustom() code. This assumption seems false if you can have a sleep at the beginning of the sequence, which is what throttle is doing, but can be done by any custom script, so that the client is expected to wait before sending any command, thus there can be no select underway in that case. So listen should be set to 1 when a select as been sent, and set back to 0 when the result data have all been received. doCustom makes implicit assumptions about what is going on, whereas it should focus on looking at the incoming state, performing operations, and leaving with a state which correspond to the actual status, without assumptions about what is going to happen next. st-listen goes to 1 very soon after startup and then it stays there, and that logic seems fine too. -- 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] Git-master regression failure
On 6/19/13 9:18 AM, Kevin Grittner wrote: Svenne Krap svenne.li...@krap.dk wrote: On 18-06-2013 22:18, Jeff Janes wrote: In Danish, apparently 'AA' 'WA', so two more rows show up. Yes of course We have three extra vowels following Z (namely Æ, Ø and Å) and for keyboard missing those essential keys we have an official alternate way to write them as AE , OE and AA. Which of course means that AA is larger than any other letter ;) Does anyone object to the attached change, so that regression tests pass when run in a Danish locale? I think it should be back-patched to 9.2, where the test was introduced. Yes, that should be fixed. I wouldn't put in the comment, though. A few releases ago, I fixed a number of other Danish issues, so adding this comment would give the impression that this the only place. -- 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] Bugfix and new feature for PGXS
On 6/19/13 5:55 AM, Cédric Villemain wrote: Le mercredi 19 juin 2013 04:48:11, Peter Eisentraut a écrit : On Tue, 2013-06-18 at 15:52 +0200, Cédric Villemain wrote: This allows for example to install hstore header and be able to include them in another extension like that: # include contrib/hstore/hstore.h That's not going to work. hstore's header file is included as #include hstore.h (cf. hstore source code). Having it included under different paths in different contexts will be a mess. OK. At the beginning I though of putting headers in include/contrib but feared that some header may have the same name. If you think that it is suitable, I can do that ? (and include the clean: recipe that I missed on the first shot) I don't think there is any value in moving the contrib header files around. -- 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] Bugfix and new feature for PGXS
On 06/19/2013 10:06 AM, Peter Eisentraut wrote: On 6/19/13 5:55 AM, Cédric Villemain wrote: Le mercredi 19 juin 2013 04:48:11, Peter Eisentraut a écrit : On Tue, 2013-06-18 at 15:52 +0200, Cédric Villemain wrote: This allows for example to install hstore header and be able to include them in another extension like that: # include contrib/hstore/hstore.h That's not going to work. hstore's header file is included as #include hstore.h (cf. hstore source code). Having it included under different paths in different contexts will be a mess. OK. At the beginning I though of putting headers in include/contrib but feared that some header may have the same name. If you think that it is suitable, I can do that ? (and include the clean: recipe that I missed on the first shot) I don't think there is any value in moving the contrib header files around. What are they going to be used for anyway? I rubbed up against this not too long ago. Things will blow up if you use stuff from the module and the module isn't already loaded. 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] how to find out whether a view is updatable
On 6/13/13 1:37 AM, Dean Rasheed wrote: On 12 June 2013 23:01, Tom Lane t...@sss.pgh.pa.us wrote: Dean Rasheed dean.a.rash...@gmail.com writes: [ pg_relation_is_updatable.patch ] I've committed this with some modifications as mentioned. There is still room to debate exactly what information_schema.columns.is_updatable means --- we can now change that without an initdb. Thanks. Those modifications all look pretty neat. We still don't have any support for this in psql, do we? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Push down
Hi all, I'm writing a foreign data wrapper in which i'm taking control of various aspects of SELECT queries (such as join, order by, count, sum etc.). Is it possible? for example, when trying to count(*), i see that pg supplies an empty list of columns to select from, and probably does the counting by itself. The same goes for sum and order by and so on. Help will be much appreciated :-) Thanks
Re: [HACKERS] Bad error message on valuntil
On 6/7/13 2:57 PM, Tom Lane wrote: Joshua D. Drake j...@commandprompt.com writes: I had a customer pulling their hair out today because they couldn't login to their system. The error was consistently: 2013-06-07 08:42:44 MST postgres 10.1.11.67 27440 FATAL: password authentication failed for user user However the problem had nothing to do with password authentication. It was because the valuntil on the user had been set till a date in the past. Now technically if we just removed the word password from the error it would be accurate but it seems it would be better to say, FATAL: the user user has expired. I think it's intentional that we don't tell the *client* that level of detail. I could see emitting a log message about it, but it's not clear whether that will help an unsophisticated user. Usually, when I log in somewhere and the password is expired, it tells me that the password is expired. I don't think we gain anything by hiding that from the user. -- 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] Bugfix and new feature for PGXS
On 6/19/13 10:19 AM, Andrew Dunstan wrote: What are they going to be used for anyway? I rubbed up against this not too long ago. Things will blow up if you use stuff from the module and the module isn't already loaded. See transforms. -- 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] Git-master regression failure
Peter Eisentraut pete...@gmx.net wrote: On 6/19/13 9:18 AM, Kevin Grittner wrote: Does anyone object to the attached change, so that regression tests pass when run in a Danish locale? I think it should be back-patched to 9.2, where the test was introduced. Yes, that should be fixed. I wouldn't put in the comment, though. A few releases ago, I fixed a number of other Danish issues, so adding this comment would give the impression that this the only place. OK, pushed without the comment. -- Kevin Grittner 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] how to find out whether a view is updatable
On 19 June 2013 15:22, Peter Eisentraut pete...@gmx.net wrote: We still don't have any support for this in psql, do we? No, but at least we now have an API that psql can use. There are still a number of questions about the best way to display it in psql. Should it be another column in \d+'s list of relations? Should it appear in \d+ for a single relation? Should it distinguish updatable from insertable and deletable? Should tab-completion also be modified? Currently I'm thinking yes, yes, no, yes. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Implementing incremental backup
On Wed, Jun 19, 2013 at 7:13 AM, Tatsuo Ishii is...@postgresql.org wrote: For now, my idea is pretty vague. - Record info about modified blocks. We don't need to remember the whole history of a block if the block was modified multiple times. We just remember that the block was modified since the last incremental backup was taken. - The info could be obtained by trapping calls to mdwrite() etc. We need to be careful to avoid such blocks used in xlogs and temporary tables to not waste resource. - If many blocks were modified in a file, we may be able to condense the info as the whole file was modified to reduce the amount of info. - How to take a consistent incremental backup is an issue. I can't think of a clean way other than locking whole cluster, which is obviously unacceptable. Maybe we should give up hot backup? I don't see how this is better than snapshotting at the filesystem level. I have no experience with TB scale databases (I've been limited to only hundreds of GB), but from my limited mid-size db experience, filesystem snapshotting is pretty much the same thing you propose there (xfs_freeze), and it works pretty well. There's even automated tools to do that, like bacula, and they can handle incremental snapshots. -- 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 for removng unused targets
On Wed, Jun 19, 2013 at 4:49 AM, Etsuro Fujita fujita.ets...@lab.ntt.co.jpwrote: Hi Harada-san, ** ** Thank you for the review. ** ** I think that the parse tree has enough information to do this optimization and that the easiest way to do it is to use the information, though I might not have understand your comments correctly. So, I would like to fix the bug by simply modifying the removability check in adjust_targetlist() so that the resjunk column is not used in GROUP BY, DISTINCT ON and *window PARTITION/ORDER BY*, besides ORDER BY. No? I am open to any comments.*** * I guess the patch works fine, but what I'm saying is it might be limited to small use cases. Another instance of this that I can think of is ORDER BY clause of window specifications, which you may want to remove from the target list as well, in addition to ORDER BY of query. It will just not be removed by this approach, simply because it is looking at only parse-sortClause. Certainly you can add more rules to the new function to look at the window specification, but then I'm not sure what we are missing. So, as it stands it doesn't have critical issue, but more generalized approach would be desirable. That said, I don't have strong objection to the current patch, and just posting one thought to see if others may have the same opinion. Thanks, -- Hitoshi Harada
Re: [HACKERS] Bugfix and new feature for PGXS
Le mercredi 19 juin 2013 18:20:11, Andrew Dunstan a écrit : On 06/19/2013 11:26 AM, Peter Eisentraut wrote: On 6/19/13 10:19 AM, Andrew Dunstan wrote: What are they going to be used for anyway? I rubbed up against this not too long ago. Things will blow up if you use stuff from the module and the module isn't already loaded. See transforms. So you're saying to install extension headers, but into the main directory where we put server headers? At the same level than server headers, but I'm open for suggestion. $ tree -d include include ├── libpq └── postgresql ├── contrib │ └── hstore ├── informix │ └── esql ├── internal │ └── libpq └── server And all subidrs of server/ -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Bugfix and new feature for PGXS
On 06/19/2013 11:26 AM, Peter Eisentraut wrote: On 6/19/13 10:19 AM, Andrew Dunstan wrote: What are they going to be used for anyway? I rubbed up against this not too long ago. Things will blow up if you use stuff from the module and the module isn't already loaded. See transforms. So you're saying to install extension headers, but into the main directory where we put server headers? 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] Bugfix and new feature for PGXS
On 06/19/2013 12:32 PM, Cédric Villemain wrote: Le mercredi 19 juin 2013 18:20:11, Andrew Dunstan a écrit : On 06/19/2013 11:26 AM, Peter Eisentraut wrote: On 6/19/13 10:19 AM, Andrew Dunstan wrote: What are they going to be used for anyway? I rubbed up against this not too long ago. Things will blow up if you use stuff from the module and the module isn't already loaded. See transforms. So you're saying to install extension headers, but into the main directory where we put server headers? At the same level than server headers, but I'm open for suggestion. $ tree -d include include ├── libpq └── postgresql ├── contrib │ └── hstore ├── informix │ └── esql ├── internal │ └── libpq └── server And all subidrs of server/ This is what Peter was arguing against, isn't it? 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] event trigger API documentation?
Peter Eisentraut pete...@gmx.net writes: Looks pretty good, but the description of the parsetree field was obviously copied from somewhere else. I would fix it myself, but I don't know what kind of assurances we want to offer about what's in that field. Oh, oops. I think we should direct the reader to the source code for more information (and propose both where the Node structure is defined and some places where it's used, ProcessUtility comes to mind), and warn that the parse tree exact content for any given command will change in between major releases, and could change in between minor releases. That said, now that it's exposed in the FDW code and the Event Trigger code, we might want to have some ABI compat' in place for minor versions. I'm only raising the question, my knowledge on how to do that and the impact on the code maintaince is so sore that I have no opinion about what the good answer is. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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 cosmetic problem while dumping/restoring rules
Gražvydas Valeika gvale...@gmail.com writes: - create new database; - CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public; - backup it; - create new database and restore it from this new backup. It produces 3 errors while executing these 3 statements: This has been fixed by Joe Conway meanwhile. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] how to find out whether a view is updatable
On 6/19/13 11:50 AM, Dean Rasheed wrote: On 19 June 2013 15:22, Peter Eisentraut pete...@gmx.net wrote: We still don't have any support for this in psql, do we? No, but at least we now have an API that psql can use. There are still a number of questions about the best way to display it in psql. Should it be another column in \d+'s list of relations? Should it appear in \d+ for a single relation? Should it distinguish updatable from insertable and deletable? Should tab-completion also be modified? Currently I'm thinking yes, yes, no, yes. I would be satisfied with no, yes, no, no. Although I don't know what tab completion changes you have in mind. -- 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] How do we track backpatches?
I'd imagine having a CF entry per release, so after a set of minor releases, the CF is closed. How would we name these? Also, what about patches for beta? Should we have a beta CF? -- 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] Bugfix and new feature for PGXS
On 6/19/13 12:20 PM, Andrew Dunstan wrote: So you're saying to install extension headers, but into the main directory where we put server headers? Yes, if we choose to install some extension headers, that is where we should put them. -- 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 for fail-back without fresh backup
On Tuesday, June 18, 2013, Amit Kapila wrote: On Tuesday, June 18, 2013 12:18 AM Sawada Masahiko wrote: On Sun, Jun 16, 2013 at 2:00 PM, Amit kapila amit.kap...@huawei.comjavascript:; wrote: On Saturday, June 15, 2013 8:29 PM Sawada Masahiko wrote: On Sat, Jun 15, 2013 at 10:34 PM, Amit kapila amit.kap...@huawei.com javascript:; wrote: On Saturday, June 15, 2013 1:19 PM Sawada Masahiko wrote: On Fri, Jun 14, 2013 at 10:15 PM, Amit Kapila amit.kap...@huawei.com javascript:; wrote: On Friday, June 14, 2013 2:42 PM Samrat Revagade wrote: Hello, We have already started a discussion on pgsql-hackers for the problem of taking fresh backup during the failback operation here is the link for that: http://www.postgresql.org/message-id/CAF8Q-Gxg3PQTf71NVECe- 6OzRaew5pWhk7yQtb jgwrfu513...@mail.gmail.com javascript:; Let me again summarize the problem we are trying to address. How will you take care of extra WAL on old master during recovery. If it plays the WAL which has not reached new-master, it can be a problem. you means that there is possible that old master's data ahead of new master's data. I mean to say is that WAL of old master can be ahead of new master. I understood that data files of old master can't be ahead, but I think WAL can be ahead. so there is inconsistent data between those server when fail back. right? if so , there is not possible inconsistent. because if you use GUC option as his propose (i.g., failback_safe_standby_mode = remote_flush), when old master is working fine, all file system level changes aren't done before WAL replicated. Would the propose patch will take care that old master's WAL is also not ahead in some way? If yes, I think i am missing some point. yes it will happen that old master's WAL ahead of new master's WAL as you said. but I think that we can solve them by delete all WAL file when old master starts as new standby. I think ideally, it should reset WAL location at the point where new master has forrked off. In such a scenario it would be difficult for user who wants to get a dump of some data in old master which hasn't gone to new master. I am not sure if such a need is there for real users, but if it is there, then providing this solution will have some drawbacks. I think that we can dumping data before all WAL files deleting. All WAL files deleting is done when old master starts as new standby. Can we dump data without starting server? Sorry I made a mistake. We can't it. this proposing patch need to be able to also handle such scenario in future. Regards, --- Sawada Masahiko -- Regards, --- Sawada Masahiko
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
On 6/13/13 5:47 PM, Josh Berkus wrote: 2. File name to store settings set by ALTER SYSTEM command is still persistent.auto.conf Why? Shouldn't it just be auto.conf? Or system.auto.conf? I prefer auto.conf, personally. Well, not much about it is automatic, really. It's just set elsewhere. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
On 06/19/2013 10:48 AM, Peter Eisentraut wrote: On 6/13/13 5:47 PM, Josh Berkus wrote: 2. File name to store settings set by ALTER SYSTEM command is still persistent.auto.conf Why? Shouldn't it just be auto.conf? Or system.auto.conf? I prefer auto.conf, personally. Well, not much about it is automatic, really. It's just set elsewhere. True, but can you think of a better word to mean don't edit this by hand? -- 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] how to find out whether a view is updatable
On 19 June 2013 18:12, Peter Eisentraut pete...@gmx.net wrote: On 6/19/13 11:50 AM, Dean Rasheed wrote: On 19 June 2013 15:22, Peter Eisentraut pete...@gmx.net wrote: We still don't have any support for this in psql, do we? No, but at least we now have an API that psql can use. There are still a number of questions about the best way to display it in psql. Should it be another column in \d+'s list of relations? Should it appear in \d+ for a single relation? Should it distinguish updatable from insertable and deletable? Should tab-completion also be modified? Currently I'm thinking yes, yes, no, yes. I would be satisfied with no, yes, no, no. Although I don't know what tab completion changes you have in mind. Yes, on reflection having an extra column in the list of relations is probably not a good idea. In many cases that's just going to be a list of tables, all of which will be updatable. So it would only be for \d+ on a single view or foreign table - simply: Updatable: yes|no Tab-completion was discussed on the original thread, but then I forgot about it: http://www.postgresql.org/message-id/caa-alv4_atxij7paqgvh73n5a0f-patvh5em-lmqu+ofuze...@mail.gmail.com Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
On 6/19/13 1:49 PM, Josh Berkus wrote: On 06/19/2013 10:48 AM, Peter Eisentraut wrote: On 6/13/13 5:47 PM, Josh Berkus wrote: 2. File name to store settings set by ALTER SYSTEM command is still persistent.auto.conf Why? Shouldn't it just be auto.conf? Or system.auto.conf? I prefer auto.conf, personally. Well, not much about it is automatic, really. It's just set elsewhere. True, but can you think of a better word to mean don't edit this by hand? generated_by_server.conf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Jun 19, 2013 7:55 PM, Peter Eisentraut pete...@gmx.net wrote: On 6/19/13 1:49 PM, Josh Berkus wrote: On 06/19/2013 10:48 AM, Peter Eisentraut wrote: On 6/13/13 5:47 PM, Josh Berkus wrote: 2. File name to store settings set by ALTER SYSTEM command is still persistent.auto.conf Why? Shouldn't it just be auto.conf? Or system.auto.conf? I prefer auto.conf, personally. Well, not much about it is automatic, really. It's just set elsewhere. True, but can you think of a better word to mean don't edit this by hand? generated_by_server.conf System.conf? /Magnus
Re: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review]
On 6/7/13 12:14 AM, Amit Kapila wrote:
I will change the patch as per below syntax if there are no objections:
ALTER SYSTEM SET configuration_parameter {TO | =} {value, | 'value'};
I do like using ALTER SYSTEM in general, but now that I think about it,
the 9.3 feature to create global settings in pg_db_role_setting would
also have been a candidate for exactly that same syntax if it had been
available. In fact, if we do add ALTER SYSTEM, it might make sense to
recast that feature into that syntax.
It might be clearer to do something like ALTER SYSTEM SET EXTERNAL FILE
or something like that. It's only a small syntax change, so don't worry
about it too much, but let's keep thinking about it.
--
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)
The use case of the option is to be able to generate a continuous gentle load for functional tests, eg in a practice session with students or for testing features on a laptop. Why does this need two option formats (-H and --throttle)? On the latest version it is --rate and -R. Because you may want to put something very readable and understandable in a script and like long options, or have to type it interactively every day in a terminal and like short ones. Most UNIX commands include both kind. -- 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] [PATCH] pgbench --throttle (submission 7 - with lag measurement)
number of transactions actually processed: 301921 Just a thought before spending too much time on this subtle issue. The patch worked reasonnably for 301900 transactions in your above run, and the few last ones, less than the number of clients, show strange latency figures which suggest that something is amiss in some corner case when pgbench is stopping. However, the point of pgbench is to test a steady state, not to achieve the cleanest stop at the end of a run. So my question is: should this issue be a blocker wrt to the feature? -- 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] [PATCH] add --throttle to pgbench (submission 3)
On 06/19/13 14:34, Fabien COELHO wrote: The use case of the option is to be able to generate a continuous gentle load for functional tests, eg in a practice session with students or for testing features on a laptop. Why does this need two option formats (-H and --throttle)? On the latest version it is --rate and -R. Because you may want to put something very readable and understandable in a script and like long options, or have to type it interactively every day in a terminal and like short ones. Most UNIX commands include both kind. Would it make sense then to add long versions for all the other standard options too? Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- 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] Implementing incremental backup
On 6/19/13 11:02 AM, Claudio Freire wrote: On Wed, Jun 19, 2013 at 7:13 AM, Tatsuo Ishii is...@postgresql.org wrote: For now, my idea is pretty vague. - Record info about modified blocks. We don't need to remember the whole history of a block if the block was modified multiple times. We just remember that the block was modified since the last incremental backup was taken. - The info could be obtained by trapping calls to mdwrite() etc. We need to be careful to avoid such blocks used in xlogs and temporary tables to not waste resource. - If many blocks were modified in a file, we may be able to condense the info as the whole file was modified to reduce the amount of info. - How to take a consistent incremental backup is an issue. I can't think of a clean way other than locking whole cluster, which is obviously unacceptable. Maybe we should give up hot backup? I don't see how this is better than snapshotting at the filesystem level. I have no experience with TB scale databases (I've been limited to only hundreds of GB), but from my limited mid-size db experience, filesystem snapshotting is pretty much the same thing you propose there (xfs_freeze), and it works pretty well. There's even automated tools to do that, like bacula, and they can handle incremental snapshots. A snapshot is not the same as an incremental backup; it presents itself as a full copy of the filesystem. Actually, since it's on the same underlying storage a snapshot isn't really a good backup at all. The proposal (at least as I read it) is to provide a means to easily deal with *only* the data that has actually *changed* since the last backup was taken. -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How do we track backpatches?
On Wed, Jun 19, 2013 at 8:54 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Josh Berkus wrote: I'd imagine having a CF entry per release, so after a set of minor releases, the CF is closed. How would we name these? Also, what about patches for beta? Should we have a beta CF? Don't we have the Open Items wiki page for those? Seems to work well enough. Yes. The CF app only tracks things that already have patches. For the beta, we really need to track things that may not have been fixed - or that may have been done, just only partially so far. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Bugfix and new feature for PGXS
Le mercredi 19 juin 2013 18:48:21, Andrew Dunstan a écrit : On 06/19/2013 12:32 PM, Cédric Villemain wrote: Le mercredi 19 juin 2013 18:20:11, Andrew Dunstan a écrit : On 06/19/2013 11:26 AM, Peter Eisentraut wrote: On 6/19/13 10:19 AM, Andrew Dunstan wrote: What are they going to be used for anyway? I rubbed up against this not too long ago. Things will blow up if you use stuff from the module and the module isn't already loaded. See transforms. So you're saying to install extension headers, but into the main directory where we put server headers? At the same level than server headers, but I'm open for suggestion. $ tree -d include include ├── libpq └── postgresql ├── contrib │ └── hstore ├── informix │ └── esql ├── internal │ └── libpq └── server And all subidrs of server/ This is what Peter was arguing against, isn't it? Now I have a doubt :) I believe he answered the proposal to put all headers on the same flat directory, instead of a tree. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Bad error message on valuntil
On 06/19/2013 08:24 AM, Peter Eisentraut wrote: I think it's intentional that we don't tell the *client* that level of detail. I could see emitting a log message about it, but it's not clear whether that will help an unsophisticated user. Usually, when I log in somewhere and the password is expired, it tells me that the password is expired. I don't think we gain anything by hiding that from the user. FTR: there is an actual patch for this sitting over at the, Change authentication error message thread. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
Magnus Hagander wrote: On Jun 19, 2013 7:55 PM, Peter Eisentraut pete...@gmx.net wrote: generated_by_server.conf System.conf? alter_system.conf ? -- Á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] [PATCH] Add transforms feature
On 6/17/13 5:31 PM, Alvaro Herrera wrote: This is a large patch. Do you intend to push the whole thing as a single commit, or split it? I thought about splitting it up, but I didn't find a reasonable way to do it. -- 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] Change authentication error message (patch)
On 06/18/2013 02:25 AM, Markus Wanner wrote: On 06/16/2013 06:02 PM, Joshua D. Drake wrote: Instead of pushing extra info to the logs I decided that we could without giving away extra details per policy. I wrote the error message in a way that tells the most obvious problems, without admitting to any of them. Please see attached: +1 for solving this with a bit of word-smithing. However, the proposed wording doesn't sound like a full sentence to my ears, because a password or username cannot fail per-se. I believe it actually can. The error message that is returned for a bad password, bad user or expired password is all the same. Which is why I put the username in there. How about: password authentication failed or account expired for user \%s\ It's a bit longer, but sounds more like a full sentence, no? Yes but I don't think it is accurate, what about: Authentication failed or password has expired for user \%s\ Authentication failed covers any combination of a username/password being wrong and obviously password expired covers the other. Sincerely, Joshua D. Drake Regards Markus Wanner -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- 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] How do we track backpatches?
Josh Berkus wrote: I'd imagine having a CF entry per release, so after a set of minor releases, the CF is closed. How would we name these? Also, what about patches for beta? Should we have a beta CF? Don't we have the Open Items wiki page for those? Seems to work well enough. -- Á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] Bugfix and new feature for PGXS
Peter Eisentraut wrote: On 6/19/13 12:20 PM, Andrew Dunstan wrote: So you're saying to install extension headers, but into the main directory where we put server headers? Yes, if we choose to install some extension headers, that is where we should put them. The question of the name of the directory still stands. contrib would be the easiest answer, but it's slightly wrong because externally-supplied modules could also want to install headers. extension might be it, but there are things that aren't extensions (right? if not, that would be my choice). -- Á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] pg_dump cosmetic problem while dumping/restoring rules
This has been fixed by Joe Conway meanwhile. Nice, thaks!
Re: [HACKERS] Implementing incremental backup
On Wed, Jun 19, 2013 at 3:54 PM, Jim Nasby j...@nasby.net wrote: On 6/19/13 11:02 AM, Claudio Freire wrote: On Wed, Jun 19, 2013 at 7:13 AM, Tatsuo Ishii is...@postgresql.org wrote: For now, my idea is pretty vague. - Record info about modified blocks. We don't need to remember the whole history of a block if the block was modified multiple times. We just remember that the block was modified since the last incremental backup was taken. - The info could be obtained by trapping calls to mdwrite() etc. We need to be careful to avoid such blocks used in xlogs and temporary tables to not waste resource. - If many blocks were modified in a file, we may be able to condense the info as the whole file was modified to reduce the amount of info. - How to take a consistent incremental backup is an issue. I can't think of a clean way other than locking whole cluster, which is obviously unacceptable. Maybe we should give up hot backup? I don't see how this is better than snapshotting at the filesystem level. I have no experience with TB scale databases (I've been limited to only hundreds of GB), but from my limited mid-size db experience, filesystem snapshotting is pretty much the same thing you propose there (xfs_freeze), and it works pretty well. There's even automated tools to do that, like bacula, and they can handle incremental snapshots. A snapshot is not the same as an incremental backup; it presents itself as a full copy of the filesystem. Actually, since it's on the same underlying storage a snapshot isn't really a good backup at all. Read on bacula[0], which is huge and thus this info may be hard to find, you can take that snapshot, which will be on the same filesystem of course, and *then* back it up. So you get a consistent snapshot on your backup, which means a correct backup, and the backup certainly doesn't have to be on the same filesystem. It even works for ext3 if you install the right kernel modules. Yes, it's a snapshot of the entire filesystem. So it's not the same as a database-only backup. But it does have a huge overlap don't you think? When WAL archiving can get you PITR, and bacula-like tools can get you incremental and consistent full-FS-snapshot backups, what does the proposed feature add? I don't think you can get PITR with the proposed feature, as it takes a snapshot only when told to, and it can't take multiple snapshots. The only way to get PITR AFAIK is with WAL archiving, so whether it's viable or not for TB-sized databases is moot, if it's the only option. And it will add an overhead. A considerable overhead. Even if you only have to flip a bit on some page map, it amplifies writes twofold (unless writes can be coalesced, of which there is no guarantee). In the end, it may be preferrable to just alter PG's behavior slightly to make bacula, rsync or whichever tool's job easier. Like trying hard not to write to cold segments, so entire segments can be skipped by quick mtime checks. [0] http://www.bacula.org/en/ -- 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] Remove useless USE_PGXS support in contrib
Peter Eisentraut pete...@gmx.net writes: We could do something like PG_CONFIG = fake_intree_pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) There's something to that idea. Of course we would need to offer a comment about the PG_CONFIG game and propose something else for real world extensions (PG_CONFIG ?= pg_config). where fake_intree_pg_config is a purpose-built shell script that points to the right places inside the source tree. If that works, that gets my preference over removing PGXS support in contrib modules. Setting an example is important, in-tree build is not a useful example for anyone but contributors to core. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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)
Because you may want to put something very readable and understandable in a script and like long options, or have to type it interactively every day in a terminal and like short ones. Most UNIX commands include both kind. Would it make sense then to add long versions for all the other standard options too? Yep. It is really a stylistic (pedantic?) matter. See for pgbench: https://commitfest.postgresql.org/action/patch_view?id=1106 -- 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] Change authentication error message (patch)
This probably is nit-picking, but it interests me in terms of how the language is used and understood. On 06/19/2013 08:55 PM, Joshua D. Drake wrote: I believe it actually can. The error message that is returned for a bad password, bad user or expired password is all the same. Which is why I put the username in there. Sure, the authentication can fail for all these reasons. What I stumbled over was the formulation of a failed username. If an engine fails, it might literally fall apart. The username itself - even if it doesn't pass authentication - is not falling apart in the same sense. But does the username (or the password) fail if authentication with it (in combination with password and account expiration time) is not possible? After all, it might still a valid and complete username for another cluster or another service. You can probably say: that username failed when you actually mean it failed to authenticate together with the provided password. Or how do English native speakers perceive this? Authentication failed or password has expired for user \%s\ Authentication failed covers any combination of a username/password being wrong and obviously password expired covers the other. Works for me. Considering the password to be the thing that expires (rather than the account) is probably more accurate as well. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] FP precision
test=# select '100'::real + '5'::real; ?column? -- 1e+06 (1 row) test=# select '100'::real + '6'::real; ?column? - 1.1e+06 (1 row) test=# select '0.1'::real + 0.0; ?column? --- 0.10001490116 (1 row) -- 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] Remove useless USE_PGXS support in contrib
On 06/19/2013 03:52 PM, Dimitri Fontaine wrote: Peter Eisentraut pete...@gmx.net writes: We could do something like PG_CONFIG = fake_intree_pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) There's something to that idea. Of course we would need to offer a comment about the PG_CONFIG game and propose something else for real world extensions (PG_CONFIG ?= pg_config). where fake_intree_pg_config is a purpose-built shell script that points to the right places inside the source tree. If that works, that gets my preference over removing PGXS support in contrib modules. Setting an example is important, in-tree build is not a useful example for anyone but contributors to core. Not true - you're forgetting there is no pgxs for MSVC builds. If we're going to enable building of contrib modules using pgxs but without an install we will make targets for that, and buildfarm support. 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] Change authentication error message (patch)
On 06/19/2013 01:18 PM, Markus Wanner wrote: Authentication failed or password has expired for user \%s\ Authentication failed covers any combination of a username/password being wrong and obviously password expired covers the other. Works for me. Considering the password to be the thing that expires (rather than the account) is probably more accurate as well. It is also how it is worded in the docs (which is why I used it). Patch below. JD diff --git a/src/backend/libpq/auth.c b/src/backend/libpq/auth.c index 415b614..f129fe1 100644 --- a/src/backend/libpq/auth.c +++ b/src/backend/libpq/auth.c @@ -270,7 +270,7 @@ auth_failed(Port *port, int status) break; case uaPassword: case uaMD5: - errstr = gettext_noop(password authentication failed for user \%s\); + errstr = gettext_noop(Authentication failed or password has expired for user \%s\); /* We use it to indicate if a .pgpass password failed. */ errcode_return = ERRCODE_INVALID_PASSWORD; break; -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- 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] Remove useless USE_PGXS support in contrib
Andrew Dunstan and...@dunslane.net writes: Not true - you're forgetting there is no pgxs for MSVC builds. Oh, indeed, totally forgot about that. If we're going to enable building of contrib modules using pgxs but without an install we will make targets for that, and buildfarm support. So we would need some kind of windows only pgxs.pl script? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] Bugfix and new feature for PGXS
Le mercredi 19 juin 2013 21:06:23, Alvaro Herrera a écrit : Peter Eisentraut wrote: On 6/19/13 12:20 PM, Andrew Dunstan wrote: So you're saying to install extension headers, but into the main directory where we put server headers? Yes, if we choose to install some extension headers, that is where we should put them. The question of the name of the directory still stands. contrib would be the easiest answer, but it's slightly wrong because externally-supplied modules could also want to install headers. extension might be it, but there are things that aren't extensions (right? if not, that would be my choice). yes, I think the same. auth_delay for example is not an extension as in CREATE EXTENSION. So...it is probably better to postpone this decision and keep on the idea to just install headers where there should be will traditional name (contrib). -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] FP precision
Sorry folks. That email was misdirected by accident. -- 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] Git-master regression failure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 On 19-06-2013 17:41, Kevin Grittner wrote: OK, pushed without the comment. Works like a charm :) Svenne -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.20 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQI5BAEBCAAjBQJRwhpmHBpodHRwOi8vc3Zlbm5lLmRrL3BncC9wb2xpY3kACgkQ /zLSj+olL/LBzRAAje+BsZ6eGbNEXwPWPob0sJwAD+51UNT24DMHCLAg4T38Bheo HaT+xSQm3hlIIjL/JF5BLiRFG4Fz1+GGOGbykKRW1Fm+VWzw6934lajbh/Z7wqzO T+n0XXwhbMnA4O++FvIJ67C7iazsNbAobOmqwjPe+6D6ID8VR9rNB/NEwelueR3L dVmVrSg1Mc8DVHS2h7Aq4bJaeCJHOQstB89+AEa14cwsJ25PiQFcdbUWsNNqbnoE TN3jQlXZLAl/qCzdKQ7FRAsi7Yk1r9uU7g1HNSY7ggh+W5KmRNqX99LS4v5mO6rf XquzXNf6nzl3ngfucB+60C2NloqieYKGVbzDh7LjCThbv284/fdlW+Wu80vYGcto TWIb5Ku4fbKP0Hmcuby1VlcYyZhyiBa76zV7JF5AtMe9yfCpOdmyAUdHDQUq6T6c /BVV/bCVsDAhJN/AJQC2itNnt4G+Bw62YEQCwuw1cNT5BUzjf0mbcer5kRchkNr3 lBdTEp4HBzElBDnCY7oSre+aSKomgNZwyJsps2aLuyz8nTj4WxpDQTDo1m7Kyi7K dFGRZLJRy5FS0xNrVtBW1cfoYy8B2GM9cmcZru5bFo2XAJfJaSSl4EZLdlIk5k66 k+VkwS5XoIl7oR/qT1fSwERM8TfDS2An2rr+jvVAATm2QQsKL42AN7aSROk= =2COi -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] [PATCH] Remove useless USE_PGXS support in contrib
On 06/19/2013 04:47 PM, Dimitri Fontaine wrote: Andrew Dunstan and...@dunslane.net writes: Not true - you're forgetting there is no pgxs for MSVC builds. Oh, indeed, totally forgot about that. If we're going to enable building of contrib modules using pgxs but without an install we will make targets for that, and buildfarm support. So we would need some kind of windows only pgxs.pl script? Well, MSVC support and buildfarm support are two separate issues. For out of tree MSVC builds we would probably need to install a substantial part of src/tools/msvc, and have a script that could generate a Project and so on. I'm happy to help but it probably needs someone with a deeper knowledge than I have of the internal workings of MSVC project files to build it. It's worth doing - I know some people who find this a major barrier. 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] [PATCH] Remove useless USE_PGXS support in contrib
Le mercredi 19 juin 2013 22:22:22, Andrew Dunstan a écrit : On 06/19/2013 03:52 PM, Dimitri Fontaine wrote: Peter Eisentraut pete...@gmx.net writes: We could do something like PG_CONFIG = fake_intree_pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) There's something to that idea. Of course we would need to offer a comment about the PG_CONFIG game and propose something else for real world extensions (PG_CONFIG ?= pg_config). where fake_intree_pg_config is a purpose-built shell script that points to the right places inside the source tree. If that works, that gets my preference over removing PGXS support in contrib modules. Setting an example is important, in-tree build is not a useful example for anyone but contributors to core. Not true - you're forgetting there is no pgxs for MSVC builds. PGXS + MSVC is still in the TODO list I won't be able to work on that. If we're going to enable building of contrib modules using pgxs but without an install we will make targets for that, and buildfarm support. With the set of patches I sent, contrib can be built with PGXS, there is no issue hereExcept maybe pg_xlogdump, and this one might be improved not to have to rebuild shared object from postgresql (IIRC it is a static build or something like that)... -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Implementing incremental backup
* Claudio Freire (klaussfre...@gmail.com) wrote: I don't see how this is better than snapshotting at the filesystem level. I have no experience with TB scale databases (I've been limited to only hundreds of GB), but from my limited mid-size db experience, filesystem snapshotting is pretty much the same thing you propose there (xfs_freeze), and it works pretty well. There's even automated tools to do that, like bacula, and they can handle incremental snapshots. Large databases tend to have multiple filesystems and getting a single, consistent, snapshot across all of them while under load is.. 'challenging'. It's fine if you use pg_start/stop_backup() and you're saving the XLOGs off, but if you can't do that.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Git-master regression failure
On Wed, Jun 19, 2013 at 8:41 AM, Kevin Grittner kgri...@ymail.com wrote: Peter Eisentraut pete...@gmx.net wrote: On 6/19/13 9:18 AM, Kevin Grittner wrote: Does anyone object to the attached change, so that regression tests pass when run in a Danish locale? I think it should be back-patched to 9.2, where the test was introduced. Yes, that should be fixed. I wouldn't put in the comment, though. A few releases ago, I fixed a number of other Danish issues, so adding this comment would give the impression that this the only place. OK, pushed without the comment. I had started this and let it run overnight: for LANG in `locale -a`; do make check /dev/null ; echo $? $LANG; done Of the 735 language/locales/encodings, I got 93 failures. After your commit I re-tested just the failures, and it fixed 25 of them. Of the ones I looked at, most of the problems are in create_index, some in matview as well. Lithuanian has Y coming between I and J. Estonian has Z between S and T. Norwegian seems to treat V and W as being equal except to break suffix-ties. Is there an infrastructure to use a different expected file depending on the LANG used? Cheers, Jeff
Re: [HACKERS] Vacuum/visibility is busted
On Thu, Feb 7, 2013 at 12:01 PM, Andres Freund and...@2ndquadrant.comwrote: On 2013-02-07 11:15:46 -0800, Jeff Janes wrote: Does anyone have suggestions on how to hack the system to make it fast-forward the current transaction id? It would certainly make testing this kind of thing faster if I could make transaction id increment by 100 each time a new one is generated. Then wrap-around could be approached in minutes rather than hours. I had various plpgsql functions to do that, but those still took quite some time. As I needed it before I just spent some minutes hacking up a contrib module to do the job. Hi Andres, Your patch needs the file xidfuncs--1.0.sql, but does not include it. I could probably guess what needs to be in that file, but do you still have a copy of it? I doubt it really think it makes sense as a contrib module on its own though? Maybe PGXN? Cheers, Jeff
Re: [HACKERS] Implementing incremental backup
On Wed, Jun 19, 2013 at 6:20 PM, Stephen Frost sfr...@snowman.net wrote: * Claudio Freire (klaussfre...@gmail.com) wrote: I don't see how this is better than snapshotting at the filesystem level. I have no experience with TB scale databases (I've been limited to only hundreds of GB), but from my limited mid-size db experience, filesystem snapshotting is pretty much the same thing you propose there (xfs_freeze), and it works pretty well. There's even automated tools to do that, like bacula, and they can handle incremental snapshots. Large databases tend to have multiple filesystems and getting a single, consistent, snapshot across all of them while under load is.. 'challenging'. It's fine if you use pg_start/stop_backup() and you're saving the XLOGs off, but if you can't do that.. Good point there. I still don't like the idea of having to mark each modified page. The WAL compressor idea sounds a lot more workable. As in scalable. -- 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] Git-master regression failure
Jeff Janes jeff.ja...@gmail.com wrote: Kevin Grittner kgri...@ymail.com wrote: Peter Eisentraut pete...@gmx.net wrote: On 6/19/13 9:18 AM, Kevin Grittner wrote: Does anyone object to the attached change, so that regression tests pass when run in a Danish locale? I think it should be back-patched to 9.2, where the test was introduced. Yes, that should be fixed. I wouldn't put in the comment, though. A few releases ago, I fixed a number of other Danish issues, so adding this comment would give the impression that this the only place. OK, pushed without the comment. I had started this and let it run overnight: for LANG in `locale -a`; do make check /dev/null ; echo $? $LANG; done Of the 735 language/locales/encodings, I got 93 failures. Ouch! After your commit I re-tested just the failures, and it fixed 25 of them. That's more than I would have guessed. Cool. Of the ones I looked at, most of the problems are in create_index, some in matview as well. So of the 68 remaining locales which fail, most are due to a couple scripts. Lithuanian has Y coming between I and J. Estonian has Z between S and T. Norwegian seems to treat V and W as being equal except to break suffix-ties. Is there an infrastructure to use a different expected file depending on the LANG used? Well, any one test can have alternative expected scripts; but in previous discussions we decided that that facility should not be used for locale issues. It would tend to get into multiplicative permutations with other reasons to have alternatives. What we have done is to try to create tests that don't hit those edge conditions when we know of them. Could you share your detailed information on the remaining failures? -- Kevin Grittner 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] Implementing incremental backup
Claudio Freire escribió: On Wed, Jun 19, 2013 at 6:20 PM, Stephen Frost sfr...@snowman.net wrote: * Claudio Freire (klaussfre...@gmail.com) wrote: I don't see how this is better than snapshotting at the filesystem level. I have no experience with TB scale databases (I've been limited to only hundreds of GB), but from my limited mid-size db experience, filesystem snapshotting is pretty much the same thing you propose there (xfs_freeze), and it works pretty well. There's even automated tools to do that, like bacula, and they can handle incremental snapshots. Large databases tend to have multiple filesystems and getting a single, consistent, snapshot across all of them while under load is.. 'challenging'. It's fine if you use pg_start/stop_backup() and you're saving the XLOGs off, but if you can't do that.. Good point there. I still don't like the idea of having to mark each modified page. The WAL compressor idea sounds a lot more workable. As in scalable. There was a project that removed useless WAL records from the stream, to make it smaller and useful for long-term archiving. It only removed FPIs as far as I recall. It's dead now, and didn't compile on recent (9.1?) Postgres because of changes in the WAL structs, IIRC. This doesn't help if you have a large lot of UPDATEs that touch the same set of rows over and over, though. Tatsuo-san's proposal would allow this use-case to work nicely because you only keep one copy of such data, not one for each modification. If you have the two technologies, you could teach them to work in conjunction: you set up WAL replication, and tell the WAL compressor to prune updates for high-update tables (avoid useless traffic), then use incremental backup to back these up. This seems like it would have a lot of moving parts and be rather bug-prone, though. -- Á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: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
Josh Berkus j...@agliodbs.com writes: True, but can you think of a better word to mean don't edit this by hand? The file name is not nearly as important for that as putting in a header comment # Don't edit this file by hand. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Implementing incremental backup
On Wed, Jun 19, 2013 at 7:18 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: If you have the two technologies, you could teach them to work in conjunction: you set up WAL replication, and tell the WAL compressor to prune updates for high-update tables (avoid useless traffic), then use incremental backup to back these up. This seems like it would have a lot of moving parts and be rather bug-prone, though. I don't think it would be worse than storage-manager-level stuff. And though more complex, don't underestimate the pros: lower footprint, better scalability, and you get consistent online backups. That mechanism can also be used to distill a list of modified pages, mind you, instead of hooking into storage-manager stuff. The pro there, is that it wouldn't amplify writes. The con there is that you don't get consistent online backups. -- 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] Implementing incremental backup
I'm thinking of implementing an incremental backup tool for PostgreSQL. The use case for the tool would be taking a backup of huge database. For that size of database, pg_dump is too slow, even WAL archive is too slow/ineffective as well. However even in a TB database, sometimes actual modified blocks are not that big, may be even several GB. So if we can backup those modified blocks only, that would be an effective incremental backup method. I'm trying to figure out how that's actually different from WAL..? It sounds like you'd get what you're suggesting with simply increasing the checkpoint timeout until the WAL stream is something which you can keep up with. Of course, the downside there is that you'd have to replay more WAL when recovering. Yeah, at first I thought using WAL was a good idea. However I realized that the problem using WAL is we cannot backup unlogged tables because they are not written to WAL. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Implementing incremental backup
On Wed, Jun 19, 2013 at 7:39 PM, Tatsuo Ishii is...@postgresql.org wrote: I'm thinking of implementing an incremental backup tool for PostgreSQL. The use case for the tool would be taking a backup of huge database. For that size of database, pg_dump is too slow, even WAL archive is too slow/ineffective as well. However even in a TB database, sometimes actual modified blocks are not that big, may be even several GB. So if we can backup those modified blocks only, that would be an effective incremental backup method. I'm trying to figure out how that's actually different from WAL..? It sounds like you'd get what you're suggesting with simply increasing the checkpoint timeout until the WAL stream is something which you can keep up with. Of course, the downside there is that you'd have to replay more WAL when recovering. Yeah, at first I thought using WAL was a good idea. However I realized that the problem using WAL is we cannot backup unlogged tables because they are not written to WAL. How does replication handle that? Because I doubt that's an issue only with backups. -- 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] Change authentication error message (patch)
On Wed, Jun 19, 2013 at 11:55 AM, Joshua D. Drake j...@commandprompt.comwrote: On 06/18/2013 02:25 AM, Markus Wanner wrote: On 06/16/2013 06:02 PM, Joshua D. Drake wrote: How about: password authentication failed or account expired for user \%s\ It's a bit longer, but sounds more like a full sentence, no? Yes but I don't think it is accurate, what about: Authentication failed or password has expired for user \%s\ I think we need to keep the first password. Password authentication is a single thing, it is the authentication method attempted. It is the password method (which includes MD5) which failed, as opposed to the LDAP method or the Peer method or one of the other methods. Without this level of explicitness, it might be hard to figure out which row in pg_hba.conf was the one that PostgreSQL glommed onto to use for authentication. (Although by this argument, I don't know why MD5 doesn't get its own message specific to it, rather than sharing plain password) Cheers, Jeff
Re: [HACKERS] Implementing incremental backup
I'm trying to figure out how that's actually different from WAL..? It sounds like you'd get what you're suggesting with simply increasing the checkpoint timeout until the WAL stream is something which you can keep up with. Of course, the downside there is that you'd have to replay more WAL when recovering. Yeah, at first I thought using WAL was a good idea. However I realized that the problem using WAL is we cannot backup unlogged tables because they are not written to WAL. How does replication handle that? Because I doubt that's an issue only with backups. Unlogged tables are not replicated to streaming replication standbys. It is clearly stated in the doc. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Implementing incremental backup
* Tatsuo Ishii (is...@postgresql.org) wrote: Yeah, at first I thought using WAL was a good idea. However I realized that the problem using WAL is we cannot backup unlogged tables because they are not written to WAL. Unlogged tables are also nuked on recovery, so I'm not sure why you think an incremental backup would help.. If you're recovering (even from a simple crash), unlogged tables are going to go away. Put simply, unlogged tables should not be used for any data you care about, period. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Implementing incremental backup
* Tatsuo Ishii (is...@postgresql.org) wrote: Yeah, at first I thought using WAL was a good idea. However I realized that the problem using WAL is we cannot backup unlogged tables because they are not written to WAL. Unlogged tables are also nuked on recovery, so I'm not sure why you think an incremental backup would help.. If you're recovering (even from a simple crash), unlogged tables are going to go away. If my memory serves, unlogged tables are not nuked when PostgeSQL is stopped by a planned shutdown (not by crash or by pg_ctl -m i stop). If PostgreSQL works so, incremental backup should be able to recover unlogged tables as well, at least people would expect so IMO. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] SET work_mem = '1TB';
On Wed, Jun 19, 2013 at 4:47 PM, Simon Riggs si...@2ndquadrant.com wrote: On 18 June 2013 22:57, Fujii Masao masao.fu...@gmail.com wrote: On Wed, Jun 19, 2013 at 2:40 AM, Simon Riggs si...@2ndquadrant.com wrote: On 18 June 2013 17:10, Fujii Masao masao.fu...@gmail.com wrote: On Tue, Jun 18, 2013 at 1:06 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Tuesday, May 21, 2013, Simon Riggs wrote: I worked up a small patch to support Terabyte setting for memory. Which is OK, but it only works for 1TB, not for 2TB or above. I've incorporated my review into a new version, attached. Added TB to the docs, added the macro KB_PER_TB, and made show to print 1TB rather than 1024GB. Looks good to me. But I found you forgot to change postgresql.conf.sample, so I changed it and attached the updated version of the patch. Barring any objection to this patch and if no one picks up this, I will commit this. In truth, I hadn't realised somebody had added this to the CF. It was meant to be an exploration and demonstration that further work was/is required rather than a production quality submission. AFAICS it is still limited to '1 TB' only... Yes. Thank you both for adding to this patch. Since you've done that, it seems churlish of me to interrupt that commit. I was thinking that this is the infrastructure patch for your future proposal, i.e., support higher values of TBs. But if it interferes with your future proposal, of course I'm okay to drop this patch. Thought? Yes, please commit. Committed. Regards, -- Fujii Masao -- 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] Implementing incremental backup
* Tatsuo Ishii (is...@postgresql.org) wrote: * Tatsuo Ishii (is...@postgresql.org) wrote: Yeah, at first I thought using WAL was a good idea. However I realized that the problem using WAL is we cannot backup unlogged tables because they are not written to WAL. Unlogged tables are also nuked on recovery, so I'm not sure why you think an incremental backup would help.. If you're recovering (even from a simple crash), unlogged tables are going to go away. If my memory serves, unlogged tables are not nuked when PostgeSQL is stopped by a planned shutdown (not by crash or by pg_ctl -m i stop). If PostgreSQL works so, incremental backup should be able to recover unlogged tables as well, at least people would expect so IMO. Sure, if you shut down PG, rsync the entire thing and then bring it back up then unlogged tables should work when backed up. They're not WAL'd, so expecting them to work when restoring a backup of a PG that had been running at the time of the backup is folly. Thanks, Stephen signature.asc Description: Digital signature
