Re: [HACKERS] Suggestion: Issue warning when calling SET TRANSACTION outside transaction block
On Tue, Oct 1, 2013 at 7:49 AM, Bruce Momjian br...@momjian.us wrote:
On Sun, Sep 29, 2013 at 11:40:51AM +0530, Amit Kapila wrote:
Shouldn't we do it for Set Constraints as well?
Oh, very good point. I missed that one. Updated patch attached.
I am glad you are seeing things I am not. :-)
1. The function set_config also needs similar functionality, else
there will be inconsistency, the SQL statement will give error but
equivalent function set_config() will succeed.
SQL Command
postgres=# set local search_path='public';
ERROR: SET LOCAL can only be used in transaction blocks
Function
postgres=# select set_config('search_path', 'public', true);
set_config
public
(1 row)
I looked at this but could not see how to easily pass the value of
'isTopLevel' down to the SELECT. All the other checks have isTopLevel
passed down from the utility case statement.
Yes, we cannot pass isTopLevel, but as isTopLevel is used to decide
whether we are in function (user defined) call, so if we can find
during statement execution (current case set_config execution) that
current statement is inside user function execution, then it can be
handled.
For example, one of the ways could be to use a mechanism similar to
setting of user id and sec context used by fmgr_security_definer() (by
calling function SetUserIdAndSecContext()), once userid and sec
context are set by fmgr_security_definer(), later we can use
InSecurityRestrictedOperation() anywhere to give error.
For current case, what we can do is after analyze
(pg_analyze_and_rewrite), check if its not a builtin function (as we
can have functionid after analyze, so it can be checked
fmgr_isbuiltin(functionId)) and set variable to indicate that we are
in function call.
Any better or simpler idea can also be used to identify isTopLevel
during function execution.
Doing it only for detection of transaction chain in set_config path
might seem to be more work, but I think it can be used at other places
for detection of transaction chain as well.
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] insert throw error when year field len 4 for timestamptz datatype
Thanks Bruce. Yes for me main problem was to make assumption that a 5-digit number is a year, as was bit worried about side effect of that assumption in the date/time module. I did tested patch shared by you with various test and so far it looks good to me. I would like reviewer to review/test the patch and share his comments. Attaching the git patch again with this mail. Assigning to Reviewer. Regards, Rushabh On Wed, Oct 2, 2013 at 9:34 PM, Bruce Momjian br...@momjian.us wrote: On Wed, Oct 2, 2013 at 11:00:30AM -0400, Robert Haas wrote: On Tue, Oct 1, 2013 at 7:52 PM, Bruce Momjian br...@momjian.us wrote: On Fri, Sep 27, 2013 at 10:42:17AM +, Haribabu kommi wrote: If the changes are very high to deal all scenarios, I feel it is better do it only in scenarios where the use cases needs it, until it is not confusing users. The rest can be documented. Any other opinions/suggestions welcome. I have reviewed this patch and it is good. The problem is guessing if a number with 5+ digits is YMD, HMS, or a year. I have created a modified patch, attached, assumes a 5-digit number is a year, because YMD and HMS require at least six digits, and used your date/time test to control the other cases. I also added a few more regression tests. In an ideal world the interpretation of the tokens wouldn't depend on the order in which they appear. But we don't live in an ideal world, so maybe this is fine. Yes, earlier in the thread the original patch poster questioned whether he was going in the right direction, given the unusual hacks needed, but such hacks are standard operating procedure for date/time stuff. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Rushabh Lathia timestamptz_fix_with_testcase_v3.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ToDo: fast update of arrays with fixed length fields for PL/pgSQL
Pavel Stehule pavel.steh...@gmail.com writes: If you can do a update of some array in plpgsql now, then you have to work with local copy only. It is a necessary precondition, and I am think it is valid. If the proposal only relates to assignments to elements of plpgsql local variables, it's probably safe, but it's also probably not of much value. plpgsql has enough overhead that I'm doubting you'd get much real-world speedup. I'm also not very excited about putting even more low-level knowledge about array representation into plpgsql. 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] ToDo: fast update of arrays with fixed length fields for PL/pgSQL
2013/10/3 Tom Lane t...@sss.pgh.pa.us Pavel Stehule pavel.steh...@gmail.com writes: If you can do a update of some array in plpgsql now, then you have to work with local copy only. It is a necessary precondition, and I am think it is valid. If the proposal only relates to assignments to elements of plpgsql local variables, it's probably safe, but it's also probably not of much value. plpgsql has enough overhead that I'm doubting you'd get much real-world speedup. I'm also not very excited about putting even more low-level knowledge about array representation into plpgsql. I looked to code, and I am thinking so this can be done inside array related routines. We just have to signalize request for inplace update (if we have a local copy). I have not idea, how significant speedup can be (if any), but current behave is not friendly (and for multidimensional arrays there are no workaround), so it is interesting way - and long time I though about some similar optimization. Regards Pavel regards, tom lane
Re: [HACKERS] SSI freezing bug
On 03.10.2013 01:05, Kevin Grittner wrote: Andres Freundand...@2ndquadrant.com wrote: On 2013-10-01 07:41:46 -0700, Kevin Grittner wrote: Andres Freundand...@2ndquadrant.com wrote: A better solution probably is to promote tuple-level locks if they exist to a relation level one upon freezing I guess? It would be sufficient to promote the tuple lock to a page lock. It would be pretty easy to add a function to predicate.c which would accept a Relation and HeapTuple, check for a predicate lock for the tuple, and add a page lock if found (which will automatically clear the tuple lock). This new function would be called when a tuple was chosen for freezing. Since freezing always causes WAL-logging and disk I/O, the cost of a couple hash table operations should not be noticeable. Yea, not sure why I was thinking of table level locks. This seems like a bug fix which should be back-patched to 9.1, yes? Yes. Patch attached, including new isolation test based on Heikki's example. This patch does change the signature of heap_freeze_tuple(). If anyone thinks there is risk that external code may be calling this, I could keep the old function with its old behavior (including the bug) and add a new function name with the new parameters added -- the old function could call the new one with NULL for the last two parameters. I'm not sure whether that is better than breaking a compile of code which uses the old signature, which would force a choice about what to do. Will give it a couple days for feedback before pushing. IMHO it would be better to remove xmin from the lock key, and vacuum away the old predicate locks when the corresponding tuple is vacuumed. The xmin field is only required to handle the case that a tuple is vacuumed, and a new unrelated tuple is inserted to the same slot. Removing the lock when the tuple is removed fixes that. In fact, I cannot even come up with a situation where you would have a problem if we just removed xmin from the key, even if we didn't vacuum away old locks. I don't think the old lock can conflict with anything that would see the new tuple that gets inserted in the same slot. I have a feeling that you could probably prove that if you stare long enough at the diagram of a dangerous structure and the properties required for a conflict. - 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] pg_stat_statements: calls under-estimation propagation
On Wed, Oct 2, 2013 at 6:40 PM, Sameer Thakur samthaku...@gmail.com wrote: Looks pretty good. Do you want to package up the patch with your change and do the honors and re-submit it? Thanks for helping out so much! Sure, will do. Need to add a bit of documentation explaining statistics session as well. I did some more basic testing around pg_stat_statements.max, now that we have clarity from Peter about its value being legitimate below 100. Seems to work fine, with pg_stat_statements =4 the max unique queries in the view are 4. On the 5th query the view holds just the latest unique query discarding the previous 4. Fujii had reported a segmentation fault in this scenario. Thank you for the patch Please find the patch attached regards Sameer pg_stat_statements-identification-v7.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] [PATCH] Add use of asprintf()
You are right, wget worked. Latest patch looks good to me. make check run fine. Thank you Peter. On Wed, Oct 2, 2013 at 5:02 PM, Peter Eisentraut pete...@gmx.net wrote: On 10/2/13 5:12 AM, Asif Naeem wrote: Neither git nor patch command apply the patch successfully. Can you please guide ?. Thanks. Works for me. Check that your email client isn't mangling line endings.
Re: [HACKERS] setting separate values of replication parameters to each standby to provide more granularity
The idea is to allow configuration of standby servers such that they have there own set of replication parameters as per requirements. How does this interplay with the synchronous_standby_names parameter ? Or do you think that becomes irrelevant if we do like what you are suggesting above ? AFAIK synchronous_standby_names was added because we wanted to give master control on which standbys can become SYNC standbys and in what order of priority. But if we do what you are suggesting above, I wonder if we can just have an explicit knob to make a standby a SYNC standby. Say something like: standby1.sync_mode = SYNC|ASYNC. I have same thought in my mind. We can handover these controls to user and he will decide the nature and priority of standby (SYNC or ASYNC) The user will be then more sure about his settings (currently is is depend on time at which standby connects to master) The other purpose of synchronous_standby_names is to define order of preference for sync standbys. Even that can then be explicitly specified using this mechanism. Yes. In that case the setup would be standby1.sync_mode = SYNC|ASYNC. standby1.priority = 1 standby1.wal_Sender_timeout = 40s . . standby1.guc = value . I am not sure but may be we can even allow to set synchronous_commit at each standby level. So depending on the synchronous_commit setting of each standby and their priorities also explicitly defined by this mechanism, master will decide which standbys to wait for at the commit time. That's a really good suggestion. If we become successful in combining behavior of synchronous_commit with each standby standby then that would be great change and each standby has its own synchronization level.
Re: [HACKERS] docbook-xsl version for release builds
On Tue, Oct 1, 2013 at 11:37 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Magnus Hagander wrote: On Thu, Aug 22, 2013 at 8:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: Peter Eisentraut pete...@gmx.net writes: On Fri, 2013-07-12 at 12:30 +0200, Magnus Hagander wrote: Given that, I'm fine with just bumping the version on borka to that version. Any objections? This was not done for 9.3rc1, AFAICT. Let's please do it for the next release builds. Um ... touching borka's toolchain post-rc1 sure sounds like a recipe for making ourselves look like idiots in a high-profile release. Wouldn't it be better to wait till after 9.3.0? I agree that doing it after the RC is a bad idea. We should probably try to do it more or less directly after the release though, so we (I..) don't forget it again... Did we get around to doing this? Nope. Given my schedule between now and the release wrap, I won't have a chance to do it if I want any reasonable ability to roll it back if it fails. But if you want to ahead and get it done, go ahead :) -- 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] [PATCH] Revive line type
On Wed, Oct 2, 2013 at 6:12 AM, Peter Eisentraut pete...@gmx.net wrote:
On Wed, 2013-09-25 at 14:26 +0530, Jeevan Chalke wrote:
So no issues from my side.
However, do we still need this in close_pl() ?
#ifdef NOT_USED
if (FPeq(line-A, -1.0) FPzero(line-B))
{/* vertical */
}
#endif
No, that can be removed.
Will you please attach new patch with above block removed ? Then I will
quickly check that new patch and mark as Ready For Committer.
Also close_sl, close_lb and dist_lb are NOT yet implemented. It will
be good if we have those. But I don't think we should wait for those
functions to be implemented.
Right, those are separate projects.
Agree.
Thanks
--
Jeevan B Chalke
Re: [HACKERS] 9.4 HEAD: select() failed in postmaster
From: Alvaro Herrera alvhe...@2ndquadrant.com Can you please send a fixup patch to what's already committed? OK, I'll send a patch against HEAD, which will be a few lines. Am I understanding the meaning of fixup patch? 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] SSI freezing bug
Heikki Linnakangas hlinnakan...@vmware.com wrote: IMHO it would be better to remove xmin from the lock key, and vacuum away the old predicate locks when the corresponding tuple is vacuumed. The xmin field is only required to handle the case that a tuple is vacuumed, and a new unrelated tuple is inserted to the same slot. Removing the lock when the tuple is removed fixes that. In fact, I cannot even come up with a situation where you would have a problem if we just removed xmin from the key, even if we didn't vacuum away old locks. I don't think the old lock can conflict with anything that would see the new tuple that gets inserted in the same slot. I have a feeling that you could probably prove that if you stare long enough at the diagram of a dangerous structure and the properties required for a conflict. You are the one who suggested adding xmin to the key: http://www.postgresql.org/message-id/4d5a36fc.6010...@enterprisedb.com I will review that thread in light of your recent comments, but the fact is that xmin was not originally in the lock key, testing uncovered bugs, and adding xmin fixed those bugs. I know I tried some other approach first, which turned out to be complex and quite messy -- it may have been similar to what you are proposing now. It seems to me that a change such as you are now suggesting is likely to be too invasive to back-patch. Do you agree that it would make sense to apply the patch I have proposed, back to 9.1, and then consider any alternative as 9.4 material? -- Kevin Grittner EDB: 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] 9.4 HEAD: select() failed in postmaster
MauMau escribió: From: Alvaro Herrera alvhe...@2ndquadrant.com Can you please send a fixup patch to what's already committed? OK, I'll send a patch against HEAD, which will be a few lines. Am I understanding the meaning of fixup patch? Yep, thanks. -- Á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] Wait free LW_SHARED acquisition
On Mon, Sep 30, 2013 at 5:28 PM, Bernd Helmle maili...@oopsware.de wrote: --On 30. September 2013 19:00:06 +0200 Andres Freund and...@2ndquadrant.com wrote: HEAD (default): tps = 181738.607247 (including connections establishing) tps = 182665.993063 (excluding connections establishing) HEAD (padding + 16 partitions + your lwlocks patch applied): tps = 269328.259833 (including connections establishing) tps = 270685.666091 (excluding connections establishing) So, still an improvement but far away from what you got. Do you have some other tweaks in your setup? The only relevant setting changed was -c shared_buffers=1GB, no other patches applied. At which scale did you pgbench -i? I've used a scale factor of 10 (i recall you've mentioned using the same upthread...). Okay, i've used 2GB shared buffers, repeating with your setting i get a far more noticable speedup: If Andres's patch passes muster it may end up causing us to re-evaluate practices for the shared buffer setting. I was trying to optimize buffer locking in the clock sweep using a different approach and gave up after not being able to find useful test cases to demonstrate an improvement. The main reason for this is that clock sweep issues are masked by contention in the buffer mapping lwlocks (as you guys noted). I *do* think clock sweep contention comes out in some production workloads but so far have been elusive to produce in synthetic testing. Ditto buffer pin contention (this has been documented). So I'm very excited about this patch. Right now in servers I configure (even some very large ones) I set shared buffers to max 2gb for various reasons. Something tells me that's about to change. merlin -- 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] Wait free LW_SHARED acquisition
On 2013-10-01 00:28:55 +0200, Bernd Helmle wrote: --On 30. September 2013 19:00:06 +0200 Andres Freund and...@2ndquadrant.com wrote: HEAD (default): tps = 181738.607247 (including connections establishing) tps = 182665.993063 (excluding connections establishing) HEAD (padding + 16 partitions + your lwlocks patch applied): tps = 269328.259833 (including connections establishing) tps = 270685.666091 (excluding connections establishing) So, still an improvement but far away from what you got. Do you have some other tweaks in your setup? The only relevant setting changed was -c shared_buffers=1GB, no other patches applied. At which scale did you pgbench -i? I've used a scale factor of 10 (i recall you've mentioned using the same upthread...). Okay, i've used 2GB shared buffers, repeating with your setting i get a far more noticable speedup: tps = 346292.008580 (including connections establishing) tps = 347997.073595 (excluding connections establishing) Could you send hierarchical profiles of both 1 and 2GB? It's curious that the difference is that big... Even though they will be a bit big, it'd be helpful if you pasted the output of perf report --stdio, to include the callers... 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] record identical operator - Review
On 09/30/2013 09:08 AM, Kevin Grittner wrote: Steve Singer st...@ssinger.info wrote: How about To support matching of rows which include elements without a default B-tree operator class, the following operators are defined for composite type comparison: literal*=/, literal*lt;gt;/, literal*lt;/, literal*lt;=/, literal*gt;/, and literal*gt;=/. These operators compare the internal binary representation of the two rows. Two rows might have a different binary representation even though comparisons of the two rows with the equality operator is true. The ordering of rows under these comparision operators is deterministic but not otherwise meaningful. These operators are used internally for materialized views and might be useful for other specialized purposes such as replication but are not intended to be generally useful for writing queries. I agree that's an improvement. Thanks! Are there any outstanding issues on this patch preventing it from being committed? I think we have discussed this patch enough such that we now have consensus on proceeding with adding a record identical operator to SQL. No one has objected to the latest names of the operators. You haven't adjusted the patch to reduce the duplication between the equality and comparison functions, if you disagree with me and feel that doing so would increase the code complexity and be inconsistent with how we do things elsewhere that is fine. Steve -- Kevin Grittner EDB: 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] insert throw error when year field len 4 for timestamptz datatype
On Thu, Oct 3, 2013 at 11:54:14AM +0530, Rushabh Lathia wrote: Thanks Bruce. Yes for me main problem was to make assumption that a 5-digit number is a year, as was bit worried about side effect of that assumption in the date/time module. I did tested patch shared by you with various test and so far it looks good to me. I would like reviewer to review/test the patch and share his comments. Attaching the git patch again with this mail. Assigning to Reviewer. Oh, great. If everyone likes it I can apply it. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Suggestion: Issue warning when calling SET TRANSACTION outside transaction block
On Thu, Oct 3, 2013 at 11:50:09AM +0530, Amit Kapila wrote: I looked at this but could not see how to easily pass the value of 'isTopLevel' down to the SELECT. All the other checks have isTopLevel passed down from the utility case statement. Yes, we cannot pass isTopLevel, but as isTopLevel is used to decide whether we are in function (user defined) call, so if we can find during statement execution (current case set_config execution) that current statement is inside user function execution, then it can be handled. For example, one of the ways could be to use a mechanism similar to setting of user id and sec context used by fmgr_security_definer() (by calling function SetUserIdAndSecContext()), once userid and sec context are set by fmgr_security_definer(), later we can use InSecurityRestrictedOperation() anywhere to give error. For current case, what we can do is after analyze (pg_analyze_and_rewrite), check if its not a builtin function (as we can have functionid after analyze, so it can be checked fmgr_isbuiltin(functionId)) and set variable to indicate that we are in function call. Any better or simpler idea can also be used to identify isTopLevel during function execution. Doing it only for detection of transaction chain in set_config path might seem to be more work, but I think it can be used at other places for detection of transaction chain as well. I am also worried about over-engineering this. I will wait to see if anyone else would find top-level detection useful, and if not, I will just apply my version of that patch that does not handle set_config. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] record identical operator - Review
On Thu, Oct 3, 2013 at 09:59:03AM -0400, Steve Singer wrote: Are there any outstanding issues on this patch preventing it from being committed? I have not received answers to my email of October 1: http://www.postgresql.org/message-id/20131001024620.gb13...@momjian.us -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Suggestion: Issue warning when calling SET TRANSACTION outside transaction block
On 2013-09-30 22:19:31 -0400, Bruce Momjian wrote:
On Sun, Sep 29, 2013 at 11:40:51AM +0530, Amit Kapila wrote:
Shouldn't we do it for Set Constraints as well?
Oh, very good point. I missed that one. Updated patch attached.
I am glad you are seeing things I am not. :-)
1. The function set_config also needs similar functionality, else
there will be inconsistency, the SQL statement will give error but
equivalent function set_config() will succeed.
SQL Command
postgres=# set local search_path='public';
ERROR: SET LOCAL can only be used in transaction blocks
Function
postgres=# select set_config('search_path', 'public', true);
set_config
public
(1 row)
I looked at this but could not see how to easily pass the value of
'isTopLevel' down to the SELECT. All the other checks have isTopLevel
passed down from the utility case statement.
Doesn't sound like a good idea to prohibit that anyway, it might
intentionally be used as part of a more complex statement where it only
should take effect during that single statement.
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] record identical operator
* Robert Haas (robertmh...@gmail.com) wrote: I'm wary of inventing a completely new way of doing this. I don't think that there's any guarantee that the send/recv functions won't expose exactly the same implementation details as a direct check for binary equality. I don't follow this thought. Changing the binary representation which is returned when users use the binary-mode protocol would be a pretty massive user-impacting change, while adding a new way of storing NUMERIC internally wouldn't even be visible to end users. For example, array_send() seems to directly reveal the presence or absence of a NULL bitmap. That's part of the definition of what the binary protocol for array *is* though, so that's simply a fact of life for our users. That doesn't mean we can't, say, change the array header to remove the internal type OID and use a mapping from the type OID that's on the tuple to the type OID inside the array- as long as array_send() still produces the same binary structure for the end user. Even if there were no such anomalies today, it feels fragile to rely on a fairly-unrelated concept to have exactly the semantics we want here, and it will surely be much slower. I agree that it would be slower but performance should be a consideration once correctness is accomplished and this distinction feels a great deal more correct, imv. Binary equality has existing precedent and is used in numerous places in the code for good reason. Users might be confused about the use of those semantics in those places also, but AFAICT nobody is. You've stated that a few times and I've simply not had time to run down the validity of it- so, where does internal-to-PG binary equality end up being visible to our users? Independent of that, are there places in the backend which could actually be refactored to use these new operators where it would reduce code complexity? On the other hand, if you are *replicating* those data types, then you don't want that tolerance. If you're checking whether two boxes are equal, you may indeed want the small amount of fuzziness that our comparison operators allow. But if you're copying a box or a float from one table to another, or from one database to another, you want the values copied exactly, including all of those low-order bits that tend to foul up your comparisons. That's why float8out() normally doesn't display any extra_float_digits - because you as the user shouldn't be relying on them - but pg_dump does back them up because not doing so would allow errors to propagate. Similarly here. I agree that we should be copying the values exactly- and I think we're already good there when it comes to doing a *copy*. I further agree that updating the matview should be a copy, but the manner in which we're doing that is using an equality check to see if the value needs to be updated or not which is where things get a bit fuzzy. If we were consistently copying and updating the value based on some external knowledge that the value has changed (similar to how slony works w/ triggers that dump change sets into a table- it doesn't consider has any value on this row changed?; the user did an update, presumably for some purpose, therefore the change gets recorded and propagated), I'd be perfectly happy. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] record identical operator - Review
Steve, Thanks for following-up on this; I had meant to reply much sooner but other things got in the way. Thanks again, Stephen * Steve Singer (st...@ssinger.info) wrote: Are there any outstanding issues on this patch preventing it from being committed? I think we have discussed this patch enough such that we now have consensus on proceeding with adding a record identical operator to SQL. No one has objected to the latest names of the operators. You haven't adjusted the patch to reduce the duplication between the equality and comparison functions, if you disagree with me and feel that doing so would increase the code complexity and be inconsistent with how we do things elsewhere that is fine. signature.asc Description: Digital signature
Re: [HACKERS] logical changeset generation v6.2
On 2013-10-01 16:11:47 -0400, Steve Singer wrote: On 09/30/2013 06:44 PM, Andres Freund wrote: Hi, The series from friday was a bit too buggy - obviously I was too tired. So here's a new one: With this series I've also noticed #2 0x007741a7 in ExceptionalCondition ( conditionName=conditionName@entry=0x7c2908 !(!(tuple-t_infomask 0x1000)), errorType=errorType@entry=0x7acc70 FailedAssertion, fileName=fileName@entry=0x91767e tqual.c, lineNumber=lineNumber@entry=1608) at assert.c:54 54abort(); 0x007a4432 in HeapTupleSatisfiesMVCCDuringDecoding ( htup=0x10bfe48, snapshot=0x108b3d8, buffer=310) at tqual.c:1608 #4 0x0049d6b7 in heap_hot_search_buffer (tid=tid@entry=0x10bfe4c, relation=0x7fbebbcd89c0, buffer=310, snapshot=0x10bfda0, heapTuple=heapTuple@entry=0x10bfe48, all_dead=all_dead@entry=0x7fff4aa3866f \001\370\375\v\001, first_call=1 '\001') at heapam.c:1756 #5 0x004a8174 in index_fetch_heap (scan=scan@entry=0x10bfdf8) at indexam.c:539 #6 0x004a82a8 in index_getnext (scan=0x10bfdf8, direction=direction@entry=ForwardScanDirection) at indexam.c:622 #7 0x004a6fa9 in systable_getnext (sysscan=sysscan@entry=0x10bfd48) at genam.c:343 #8 0x0076df40 in RelidByRelfilenode (reltablespace=0, relfilenode=529775) at relfilenodemap.c:214 ---Type return to continue, or q return to quit--- #9 0x00664ad7 in ReorderBufferCommit (rb=0x1082d98, xid=optimized out, commit_lsn=4638756800, end_lsn=optimized out, commit_time=commit_time@entry=433970378426176) at reorderbuffer.c:1320 Does your code use SELECT FOR UPDATE/SHARE on system or treat_as_catalog tables? 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] plpgsql.print_strict_params
On Sat, Sep 28, 2013 at 8:42 AM, Marko Tiikkaja ma...@joh.to wrote: On 2013-09-28 12:31, Ian Lawrence Barwick wrote: The patch looks good to me now; does the status need to be changed to Ready for Committer? Yes. Thanks for reviewing! This looks like a nice clean patch. My only concern is that it makes on and off unreserved plpgsql keywords. It looks like that will make them unusable as unquoted identifiers in a few contexts in which they can now be used. Has there been any discussion about whether that's OK? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] logical changeset generation v6.2
On 10/03/2013 12:38 PM, Andres Freund wrote: Does your code use SELECT FOR UPDATE/SHARE on system or treat_as_catalog tables? Greetings, Andres Freund Yes. It declares sl_table and sl_sequence and sl_set as catalog. It does a SELECT .. from @NAMESPACE@.sl_table T, @NAMESPACE@.sl_set S, pg_catalog.pg_class PGC, pg_catalog.pg_namespace PGN, pg_catalog.pg_index PGX, pg_catalog.pg_class PGXC where ... for update in the code being executed by the 'set add table'. (We also do select for update commands in many other places during cluster configuration commands) -- 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] No Index-Only Scan on Partial Index
On Oct 2, 2013, at 5:07 AM, Merlin Moncure mmonc...@gmail.com wrote: Hrm. I get a seq scan for that query: create index on try(upper_inf(irange)); explain select * from try where upper_inf(irange); QUERY PLAN --- Seq Scan on try (cost=0.00..1887.00 rows=3 width=68) Filter: upper_inf(irange) True also if I just select the irange. Is the filter the issue, here? Turn off seq scan... That rewards me with a bitmap heap scan: EXPLAIN select * from try where upper_inf(irange); QUERY PLAN -- Bitmap Heap Scan on try (cost=935.63..2197.63 rows=3 width=68) Filter: upper_inf(irange) - Bitmap Index Scan on try_upper_inf_idx (cost=0.00..927.30 rows=5 width=0) Index Cond: (upper_inf(irange) = true) But anyway, I still don’t understand why, if the function used to store the value is immutable (as upper_inf() is), why Postgres doesn't do an index scan. Is this something that could be improved in the planner? Thanks, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] record identical operator - Review
Bruce Momjian br...@momjian.us wrote: On Fri, Sep 27, 2013 at 03:34:20PM -0700, Kevin Grittner wrote: We first need to document the existing record comparison operators. If they read the docs for comparing row_constructors and expect that to be the behavior they get when they compare records, they will be surprised. Well, if they appear in \do, I am thinking they should be documented. This patch now covers the ones which are record comparison operators, old and new. Feel free to document the others if you feel strongly on that point; but I don't feel that becomes the business of this patch. Because comparing primary keys doesn't tell us whether the old and new values in the row all match. OK, but my question was about why we need a full set of operators rather than just equal, and maybe not equal. I thought you said we needed others, e.g. , so we could do merge joins, but I thought we would just be doing comparisons after primary keys are joined, and if that is true, we could just use a function. http://www.postgresql.org/docs/current/interactive/xoper-optimization.html#AEN54334 Actually, I am now realizing you have to use the non-binary-level equals comparison on keys, then the binary-level equals on rows for this to work --- that's pretty confusing. Is that true? It's a matter of replacing the = operator for record comparisons in these two places with the new *= operator. http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/matview.c;h=238ccc72f5205ae00a15e6e17f384addfa445552;hb=master#l553 http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/matview.c;h=238ccc72f5205ae00a15e6e17f384addfa445552;hb=master#l684 (With or without the new operator, the second of these needs to be schema-qualified to avoid trouble if the user adds a different implementation of = ahead of the pg_catalog implementation on search_path, as users can do.) The difference between = and *= would not generally be visible to end users -- just to those working on matview.c. A quick query (lacking schema information and schema qualification) shows what is there by default: OK, the unique list is: opcname - varchar_ops kd_point_ops cidr_ops text_pattern_ops varchar_pattern_ops bpchar_pattern_ops (6 rows) Do these all have operators defined too? Every operator class is associated with operators. For example, while text_pattern_ops uses the same = and operators as the default text opclass (because that already uses memcmp), it adds ~~, ~=~, ~~, and ~=~ operators which also use memcmp (ignoring character encoding and collation). -- Kevin Grittner EDB: 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] hstore extension version screwup
On Wed, Oct 2, 2013 at 7:17 PM, Jim Nasby j...@nasby.net wrote: On 9/29/13 9:41 PM, Andrew Dunstan wrote: On 09/29/2013 10:38 PM, Peter Eisentraut wrote: On Sun, 2013-09-29 at 22:33 -0400, Andrew Dunstan wrote: Well if these are not meant to be changed then not being able to write them in your git repo might be a clue to that. Git doesn't support setting file permissions other than the executable bit, so this is a nonstarter. Oh, didn't know that, I've certainly know other SCM systems that do. We could potentially do it with git commit hooks, but the problem is that there's no way to force use of those on clients (a huge deficiency in git, imho). We could also use git receive hooks, but those would be very hard to override when you *do* need to modify the files (which you might within a release). What we could also do is just have the make all target, or the configure script, (or something else a developer runs often) chmod the file. It's not bulletproof in any way, but it would give a decent hint in most cases. -- 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] No Index-Only Scan on Partial Index
David, But anyway, I still don’t understand why, if the function used to store the value is immutable (as upper_inf() is), why Postgres doesn't do an index scan. Is this something that could be improved in the planner? Yes. This is clearly a TODO. -- 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] record identical operator - Review
Steve Singer st...@ssinger.info wrote: You haven't adjusted the patch to reduce the duplication between the equality and comparison functions, if you disagree with me and feel that doing so would increase the code complexity and be inconsistent with how we do things elsewhere that is fine. I think the main reason to keep them separate is that it makes it easier to compare record_cmp to record_image_cmp and record_eq to record_image_eq to see what the differences and similarities are. Other reasons are that I think all those conditionals inside a combined function would get messy and make the logic harder to understand. The number of places that would need conditionals, plus new wrappers that would be needed, would mean that the net reduction in lines of code would be minimal. -- Kevin Grittner EDB: 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] record identical operator
On Thu, Oct 3, 2013 at 11:12 AM, Stephen Frost sfr...@snowman.net wrote: Binary equality has existing precedent and is used in numerous places in the code for good reason. Users might be confused about the use of those semantics in those places also, but AFAICT nobody is. You've stated that a few times and I've simply not had time to run down the validity of it- so, where does internal-to-PG binary equality end up being visible to our users? Independent of that, are there places in the backend which could actually be refactored to use these new operators where it would reduce code complexity? Well, the most obvious example is HOT. README.HOT sayeth: % The requirement for doing a HOT update is that none of the indexed % columns are changed. This is checked at execution time by comparing the % binary representation of the old and new values. We insist on bitwise % equality rather than using datatype-specific equality routines. The % main reason to avoid the latter is that there might be multiple notions % of equality for a datatype, and we don't know exactly which one is % relevant for the indexes at hand. We assume that bitwise equality % guarantees equality for all purposes. That bit about multiple notions of equality applies here too - because we don't know what the user will do with the data in the materialized view, Kevin's looking for an operator which guarantees equality for all purposes. Note that HOT could feed everything through the send and recv functions, as you are proposing here, and that would allow HOT to apply in cases where it does not currently apply. We could, for example, perform a HOT update when a value in a numeric column is changed from the long format to the short format without changing the user-perceived value. You could argue that HOT isn't user-visible, but we certainly advise people to think about structuring their indexing in a fashion that does not defeat HOT, so I think to some extent it is user-visible. Also, in xfunc.sgml, we have this note: The planner also sometimes relies on comparing constants via bitwise equality, so you can get undesirable planning results if logically-equivalent values aren't bitwise equal. There are other places as well. If two node trees are compared using equal(), any Const nodes in that tree will be compared for binary equality. So for example MergeWithExistingConstraint() will error out if the constraints are equal under btree equality operators but not binary equal. equal() is also used in various places in the planner, which may be the reason for the above warning. The point I want to make here is that we have an existing precedent to use bitwise equality when we want to make sure that values are equivalent for all purposes, regardless of what opclass or whatever is in use. There are not a ton of those places but there are some. On the other hand, if you are *replicating* those data types, then you don't want that tolerance. If you're checking whether two boxes are equal, you may indeed want the small amount of fuzziness that our comparison operators allow. But if you're copying a box or a float from one table to another, or from one database to another, you want the values copied exactly, including all of those low-order bits that tend to foul up your comparisons. That's why float8out() normally doesn't display any extra_float_digits - because you as the user shouldn't be relying on them - but pg_dump does back them up because not doing so would allow errors to propagate. Similarly here. I agree that we should be copying the values exactly- and I think we're already good there when it comes to doing a *copy*. I further agree that updating the matview should be a copy, but the manner in which we're doing that is using an equality check to see if the value needs to be updated or not which is where things get a bit fuzzy. If we were consistently copying and updating the value based on some external knowledge that the value has changed (similar to how slony works w/ triggers that dump change sets into a table- it doesn't consider has any value on this row changed?; the user did an update, presumably for some purpose, therefore the change gets recorded and propagated), I'd be perfectly happy. Sure, that'd work, but it doesn't explain what's wrong with Kevin's proposal. You're basically saying that memcpy(a, b, len) is OK with you but if (memcmp(a, b, len) != 0) memcpy(a, b, len) is not OK with you. I don't understand how you can endorse copying the value exactly, but not be OK with the optimization that says, well if it already matches exactly, then we don't need to copy it. We can certainly rip out the current implementation of REFRESH MATERIALIZED VIEW CONCURRENTLY and replace it with something that deletes every row in the view and reinserts them all, but it will be far less efficient than what we have now. All that is anybody is asking for here is the
Re: [HACKERS] hstore extension version screwup
On Wed, Oct 2, 2013 at 1:17 PM, Jim Nasby j...@nasby.net wrote: On 9/29/13 9:41 PM, Andrew Dunstan wrote: On 09/29/2013 10:38 PM, Peter Eisentraut wrote: On Sun, 2013-09-29 at 22:33 -0400, Andrew Dunstan wrote: Well if these are not meant to be changed then not being able to write them in your git repo might be a clue to that. Git doesn't support setting file permissions other than the executable bit, so this is a nonstarter. Oh, didn't know that, I've certainly know other SCM systems that do. We could potentially do it with git commit hooks, but the problem is that there's no way to force use of those on clients (a huge deficiency in git, imho). The best alternative I've been able to come up with is having hooks in a standard location in the repo and then there's one file that people would need to put into their home directory (under ~/.git I think) that would pull all of that stuff in. ISTM that what we need here is less a git-hook and more of a regression test, so that if you do the wrong thing, the buildfarm turns exciting colors. I'm not sure exactly how to write a regression test for this, but I bet we can dream up something... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] record identical operator
Stephen Frost sfr...@snowman.net wrote: If we were consistently copying and updating the value based on some external knowledge that the value has changed (similar to how slony works w/ triggers that dump change sets into a table- it doesn't consider has any value on this row changed?; the user did an update, presumably for some purpose, therefore the change gets recorded and propagated), I'd be perfectly happy. That is the equivalent of the incremental maintenance feature which won't be coming until after REFRESH has settled down. REFRESH is more like the slony initial population of a table. REFRESH CONCURRENTLY is a way of doing that which allows users to continue to read from the matview without blocking or interruption while the REFRESH is running; I think it would be a bug if that could generate different results from a non-CONCURRENT REFRESH. Wanting incremental maintenance (which we all want) is no reason to block an earlier implementation of REFRESH CONCURRENTLY which is not, and does not use, incremental maintenance. -- Kevin Grittner EDB: 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
[HACKERS] PostgreSQL Developers wanted at SFSCon in Bolzano, Italy
The Technology Innovation Center in Bolzano, Italy has invited PostgreSQL developers to attend their upcoming SFSCon on November 15th. Their main interest is to have a discussion session with students about contributing to PostgreSQL. One issue they particularly want to address is the possibility of contributing Temporal Database code to PostgreSQL, based on a joint project done by TIS and the University of Zurich. If a PostgreSQL committer or major contributor is interested in attending this and collaborating with the TIS folks, please let me know and I will inquire about travel funding. I went to SFSCon a couple years ago, and it was a really good time. So I do recommend it if anyone has the time free. Contact me off-list if you're interested. Links: * TIS: http://www.tis.bz.it/ * SFSCon: https://sfscon.it/ -- 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] Custom Plan node
On Thu, Oct 3, 2013 at 3:05 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: Sorry for my late response. I've tried to investigate the planner code to find out the way to integrate this custom api, and it is still in progress. One special handling I found was that create_join_plan() adjust root-curOuterRels prior to recursion of inner tree if NestLoop. Probably, we need some flags to control these special handling in the core. It is a hard job to list up all the stuff, so it seems to me we need to check-up them during code construction... I'm pretty sure that is only a very small tip of a very large iceberg. This above framework was exactly what I considered. Probably, we have to put a hook on functions invoked by set_base_rel_pathlist() to add another possible way to scan the provided baserel, then set_cheapest() will choose the most reasonable one. The attached patch, it's just a works-in-progress, shows which hook I try to put around the code. Please grep it with add_custom_scan_paths. That seems fairly reasonable to me, but I think we'd want a working demonstration Regarding to the guest module of this framework, another idea that I have is, built-in query cache module that returns previous scan result being cached if table contents was not updated from the previous run. Probably, it makes sense in case when most of rows are filtered out in this scan. Anyway, I'd like to consider something useful to demonstrate this API. I doubt that has any chance of working well. Supposing that query caching is a feature we want, I don't think that plan trees are the right place to try to install it. That sounds like something that ought to be done before we plan and execute the query in the first place. I am a little less sanguine about the chances of a CustomJoin node working out well. I agree that we need something to handle join pushdown, but it seems to me that might be done by providing a Foreign Scan path into the joinrel rather than by adding a concept of foreign joins per se. Indeed, if we have a hook on add_paths_to_joinrel(), it also makes sense for foreign tables; probably, planner will choose foreign-path instead of existing join node including foreign-scans. Yes, I think it's reasonable to think about injecting a scan path into a join node. And I think that lumping everything else together under not a scan or join has the least promise of all. Is replacing Append really the same as replacing Sort? I think we'll need to think harder here about what we're trying to accomplish and how to get there. As long as extension modifies PlannedStmt on the planner_hook, I don't think it is not difficult so much, as I demonstrate on the previous patch. Unlike scan or join, existing code is not designed to compare multiple possible paths, so it seems to me a feature to adjust a plan-tree already construct is sufficient for most usage because extension can decide which one can offer more cheap path than built-in ones. Well, there were a lot of problems with your demonstration, which have already been pointed out upthread. I'm skeptical about the idea of simply replacing planner nodes wholesale, and Tom is outright opposed. I think you'll do better to focus on a narrower case - I'd suggest custom scan nodes - and leave the rest as a project for another time. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] record identical operator
On Thu, Oct 3, 2013 at 10:53 AM, Robert Haas robertmh...@gmail.com wrote: The point I want to make here is that we have an existing precedent to use bitwise equality when we want to make sure that values are equivalent for all purposes, regardless of what opclass or whatever is in use. There are not a ton of those places but there are some. Btree opclasses (which are of course special, per 35.14.6. System Dependencies on Operator Classes) are restricted by the reflexive law, which implies that bitwise equality is a stronger condition than regularly equality. I'm inclined to agree that it isn't a big deal to expose a bitwise equality operator. We're talking about the possibility of being potentially overly eager according to someone's definition about refreshing, not the opposite. With reference to an actual case where this is possible, I don't think this is astonishing, though I grant Stephen that that's aesthetic. I also agree that an intermediate notion of equality isn't worth it. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [RFC] Extend namespace of valid guc names
On Mon, Sep 23, 2013 at 9:36 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: I think the idea that we should consider a different way of handling tabular configuration data has merit. In fact, how much sense does it make to have these options (the ones for which this patch is being written) be GUCs in the first place? ALTER USER/DATABASE don't work for them, they can't be usefully changed in the commandline, there's no working SET. If we had some way to plug these into pg_hba.conf parsing machinery (which is tabular data), I would suggest that. But that doesn't sound really sensible. I think the idea of putting this configuratio data in a separate file, and perhaps a more convenient format than three-level GUC options, should be considered. All very good points, IMHO. In a lot of cases, what you want is sneazle.list='foo,bar' sneazle.foo.prop1='zatz' sneazle.bar.prop1='frotz' etc. But that means that the set of GUCs that exist to be SET needs to change every time sneazle.list changes, and we haven't got any good mechanism for that. I really think we're trying to squeeze a square peg into a round hole here, and I accordingly propose to mark this patch rejected. It seems to me that if an extension wants to read and parse a configuration file in $PGDATA, it doesn't need any special core support for that. If there's enough consistency in terms of what those configuration files look like across various extensions, we might choose to provide a set of common tools in core to help parse them. But I'm not too convinced any useful pattern will emerge. Another option is to store the data in an actual table. One could have sneazle.configtable='dbname.schemaname.tablename', for example. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GIN improvements part 1: additional information
On 23.09.2013 18:35, Bruce Momjian wrote: On Sun, Sep 15, 2013 at 01:14:45PM +0400, Alexander Korotkov wrote: On Sat, Jun 29, 2013 at 12:56 PM, Heikki Linnakangashlinnakan...@vmware.com wrote: There's a few open questions: 1. How are we going to handle pg_upgrade? It would be nice to be able to read the old page format, or convert on-the-fly. OTOH, if it gets too complicated, might not be worth it. The indexes are much smaller with the patch, so anyone using GIN probably wants to rebuild them anyway, sooner or later. Still, I'd like to give it a shot. We have broken pg_upgrade index compatibility in the past. Specifically, hash and GIN index binary format changed from PG 8.3 to 8.4. I handled it by invalidating the indexes and providing a post-upgrade script to REINDEX all the changed indexes. The user message is: Your installation contains hash and/or GIN indexes. These indexes have different internal formats between your old and new clusters, so they must be reindexed with the REINDEX command. The file: ... when executed by psql by the database superuser will recreate all invalid indexes; until then, none of these indexes will be used. It would be very easy to do this from a pg_upgrade perspective. However, I know there has been complaints from others about making pg_upgrade more restrictive. In this specific case, even if you write code to read the old file format, we might want to create the REINDEX script to allow _optional_ reindexing to shrink the index files. If we do require the REINDEX, --check will clearly warn the user that this will be required. It seems we've all but decided that we'll require reindexing GIN indexes in 9.4. Let's take the opportunity to change some other annoyances with the current GIN on-disk format: 1. There's no explicit page id field in the opaque struct, like there is in other index types. This is for the benefit of debugging tools like pg_filedump. We've managed to tell GIN pages apart from other index types by the fact that the special size of GIN pages is 8 and it's not using all the high-order bits in the last byte on the page. But an explicit page id field would be nice, so let's add that. 2. I'd like to change the way incomplete splits are handled. Currently, WAL recovery keeps track of incomplete splits, and fixes any that remain at the end of recovery. That concept is slightly broken; it's not guaranteed that after you've split a leaf page, for example, you will succeed in inserting the downlink to its parent. You might e.g run out of disk space. To fix that, I'd like to add a flag to the page header to indicate if the split has been completed, ie. if the page's downlink has been inserted to the parent, and fix them lazily on the next insert. I did a similar change to GiST back in 9.1. (Strictly speaking this doesn't require changing the on-disk format, though.) 3. I noticed that the GIN b-trees, the main key entry tree and the posting trees, use a slightly different arrangement of the downlink than our regular nbtree code does. In nbtree, the downlink for a page is the *low* key of that page, ie. if the downlink is 10, all the items on that child page must be = 10. But in GIN, we store the *high* key in the downlink, ie. all the items on the child page must be = 10. That makes inserting new downlinks at a page split slightly more complicated. For example, when splitting a page containing keys between 1-10 into 1-5 and 5-10, you need to insert a new downlink with key 10 for the new right page, and also update the existing downlink to 5. The nbtree code doesn't require updating existing entries. Anything else? - 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 part 1: additional information
On Thu, Oct 3, 2013 at 2:43 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: It seems we've all but decided that we'll require reindexing GIN indexes in 9.4. I thought the consensus in Ottawa was strongly against that. I'm not aware that anyone has subsequently changed their position on the topic. Bruce is right to point out that we've done such things before and can therefore do it again, but just because we have the technical means to do it doesn't make it good policy. That having been said, if we do decide to break it... Let's take the opportunity to change some other annoyances with the current GIN on-disk format: ...then fixing as much as possible in one go-round is clearly a good plan. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] review: psql and pset without any arguments
On Wed, Oct 2, 2013 at 5:18 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello all there are no comments, so I'll close this topic This feature is ready for commit The patch looks nice and clean, and I like the feature, too. Committed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] logical changeset generation v6.2
On 2013-10-03 13:03:07 -0400, Steve Singer wrote: On 10/03/2013 12:38 PM, Andres Freund wrote: Does your code use SELECT FOR UPDATE/SHARE on system or treat_as_catalog tables? Greetings, Andres Freund Yes. It declares sl_table and sl_sequence and sl_set as catalog. It does a SELECT .. from @NAMESPACE@.sl_table T, @NAMESPACE@.sl_set S, pg_catalog.pg_class PGC, pg_catalog.pg_namespace PGN, pg_catalog.pg_index PGX, pg_catalog.pg_class PGXC where ... for update in the code being executed by the 'set add table'. (We also do select for update commands in many other places during cluster configuration commands) Ok, there were a couple of bugs because I thought mxacts wouldn't need to be supported. So far your testcase doesn't crash the database anymore - it spews some internal errors though, so I am not sure if it's entirely fixed for you. Thanks for testing and helping! I've pushed the changes to the git tree, they aren't squashed yet and there's some further outstanding stuff, so I won't repost the series yet. 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] ToDo: fast update of arrays with fixed length fields for PL/pgSQL
Hello a very ugly test shows a possibility about 100% speedup on reported example (on small arrays, a patch is buggy and doesn't work for larger arrays). I updated a code to be read only CREATE OR REPLACE FUNCTION public.fill_2d_array(rows integer, cols integer) RETURNS integer LANGUAGE plpgsql AS $function$ DECLARE img double precision[][]; i integer; j integer; cont integer; r double precision; BEGIN img := ARRAY( SELECT 0 FROM generate_series(1, rows * cols) ) ; cont:= 0; For i IN 1..rows LOOP For j IN 1..cols LOOP r := img[i * cols + j]; r := (i * cols + j)::double precision; cont := cont + 1; --raise notice '%', img; END LOOP; END LOOP; return cont; END; $function$ It exec all expressions -- original postgres=# select fill_2d_array(200,200); fill_2d_array --- 4 (1 row) Time: 12726.117 ms -- read only version postgres=# select fill_2d_array(200,200); fill_2d_array --- 4 (1 row) Time: 245.894 ms so there is about 50x slowdown 2013/10/3 Pavel Stehule pavel.steh...@gmail.com 2013/10/3 Tom Lane t...@sss.pgh.pa.us Pavel Stehule pavel.steh...@gmail.com writes: If you can do a update of some array in plpgsql now, then you have to work with local copy only. It is a necessary precondition, and I am think it is valid. If the proposal only relates to assignments to elements of plpgsql local variables, it's probably safe, but it's also probably not of much value. plpgsql has enough overhead that I'm doubting you'd get much real-world speedup. I'm also not very excited about putting even more low-level knowledge about array representation into plpgsql. I looked to code, and I am thinking so this can be done inside array related routines. We just have to signalize request for inplace update (if we have a local copy). I have not idea, how significant speedup can be (if any), but current behave is not friendly (and for multidimensional arrays there are no workaround), so it is interesting way - and long time I though about some similar optimization. Regards Pavel regards, tom lane fast_array_update.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hstore extension version screwup
On 10/3/13 12:49 PM, Magnus Hagander wrote: We could also use git receive hooks, but those would be very hard to override when you*do* need to modify the files (which you might within a release). You can have the receive hook ignore the condition on existence of a file. It's kinda kludgey, but effective. Of course you need to remember to remove the override file when you're done overriding... -- 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] No Index-Only Scan on Partial Index
On Oct 3, 2013, at 10:50 AM, Josh Berkus j...@agliodbs.com wrote: But anyway, I still don’t understand why, if the function used to store the value is immutable (as upper_inf() is), why Postgres doesn't do an index scan. Is this something that could be improved in the planner? Yes. This is clearly a TODO. Added it here: https://wiki.postgresql.org/wiki/Todo#Optimizer_.2F_Executor Teach the planner how to better use partial indexes for index-only scans • http://www.postgresql.org/message-id/25141.1345072...@sss.pgh.pa.us • http://www.postgresql.org/message-id/79c7d74d-59b0-4d97-a5e5-3ef29...@justatheory.com Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] currval and DISCARD ALL
On Mon, Sep 2, 2013 at 4:35 PM, Fabrízio de Royes Mello fabri...@timbira.com.br wrote: The attached patch fix the items reviewed by you. Committed with assorted revisions. In particular, I renamed the function that discards cached sequence data, revised the wording of the documentation, added a regression test, and tweaked the list-free code to pop items off one after the other instead of walking the list and then NULLing it out at the end. Although no ERROR is possible here currently, this coding style is generally preferable because it's robust against being interrupted in the middle. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] currval and DISCARD ALL
On Thu, Oct 3, 2013 at 5:26 PM, Robert Haas robertmh...@gmail.com wrote: Committed with assorted revisions. In particular, I renamed the function that discards cached sequence data, revised the wording of the documentation, added a regression test, and tweaked the list-free code to pop items off one after the other instead of walking the list and then NULLing it out at the end. Although no ERROR is possible here currently, this coding style is generally preferable because it's robust against being interrupted in the middle. Thanks! -- Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Re: [HACKERS] GIN improvements part 1: additional information
On Thu, Oct 3, 2013 at 10:48 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Oct 3, 2013 at 2:43 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: It seems we've all but decided that we'll require reindexing GIN indexes in 9.4. I thought the consensus in Ottawa was strongly against that. I'm not aware that anyone has subsequently changed their position on the topic. Bruce is right to point out that we've done such things before and can therefore do it again, but just because we have the technical means to do it doesn't make it good policy. That having been said, if we do decide to break it... Let's take the opportunity to change some other annoyances with the current GIN on-disk format: ...then fixing as much as possible in one go-round is clearly a good plan. Let's see what options we have at all. I see following: 1) Drop support old GIN on-disk format. But users will have to reindex after pg_upgrade. 2) Insert kluges into GIN to support both old and new formats. So, kluges are kluges :) I don't see elegant way to do it for now, because formats are very different. 3) Upgrade GIN on-disk format in pg_upgrade. However, it would be rewriting almost whole index. Is it much better than just reindex? 4) Fork GIN2, leave GIN as is. It would lead to much of duplicated code. Any other options? -- With best regards, Alexander Korotkov.
Re: [HACKERS] GIN improvements part 1: additional information
On 03.10.2013 23:37, Alexander Korotkov wrote: 2) Insert kluges into GIN to support both old and new formats. So, kluges are kluges :) I don't see elegant way to do it for now, because formats are very different. Hmm. All you need is some code to read the old format, and a function to convert a page to new format before updating. It doesn't seem *that* kludgey. It's a fair amount of work, for sure, but not insurmountable. - 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 part 1: additional information
On Fri, Oct 4, 2013 at 12:41 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 03.10.2013 23:37, Alexander Korotkov wrote: 2) Insert kluges into GIN to support both old and new formats. So, kluges are kluges :) I don't see elegant way to do it for now, because formats are very different. Hmm. All you need is some code to read the old format, and a function to convert a page to new format before updating. It doesn't seem *that* kludgey. It's a fair amount of work, for sure, but not insurmountable. My notice was not as much about amount of work as about result. ItemPointers compression reduce occupied space in all normal cases. It's not very realistic, but it could increase space in worst case. That would lead to page split after conversion. Are we going to support such case? -- With best regards, Alexander Korotkov.
Re: [HACKERS] GIN improvements part 1: additional information
On Thu, Oct 3, 2013 at 02:48:20PM -0400, Robert Haas wrote: On Thu, Oct 3, 2013 at 2:43 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: It seems we've all but decided that we'll require reindexing GIN indexes in 9.4. I thought the consensus in Ottawa was strongly against that. I'm not aware that anyone has subsequently changed their position on the topic. Bruce is right to point out that we've done such things before and can therefore do it again, but just because we have the technical means to do it doesn't make it good policy. That having been said, if we do decide to break it... Agreed. I was stating only that this is easy for pg_upgrade. One cool thing is that the upgrades completes, and the indexes are there, but just marked as invalid until the REINDEX. One other point Alexander made is that the new GIN indexes will be smaller so most people would want the new format in the new cluster anyway. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GIN improvements part 1: additional information
Bruce Momjian escribió: Agreed. I was stating only that this is easy for pg_upgrade. One cool thing is that the upgrades completes, and the indexes are there, but just marked as invalid until the REINDEX. One other point Alexander made is that the new GIN indexes will be smaller so most people would want the new format in the new cluster anyway. But they're nonfunctional until after the reindex, which is bad for people who want a quick upgrade and return to operational mode immediately. If you could just keep the old indexes around, in working state, until they are REINDEX CONCURRENTLY'ed, that would be more practical than just marking them invalid. -- Á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] GIN improvements part 1: additional information
On 03.10.2013 23:54, Alexander Korotkov wrote: ItemPointers compression reduce occupied space in all normal cases. It's not very realistic, but it could increase space in worst case. That would lead to page split after conversion. Are we going to support such case? Hmm, that's probably rare enough that the number of such indexes in the real world where that could happen is exactly 0. A compressed item requires 7 bytes in the worst case; that is an offset 127, and distance to previous item 2^(4*7) = 268435456 blocks. With the default block size, that requires an index larger than 2TB. And that's just for one such item to appear - to actually cause a page to overflow, a page would need to be full of other items widely apart each other to take up 6 bytes each. So I think if you can make the conversion work with the assumption that the compressed format always fits in the old space, and throw an error if it doesn't, that's good enough. (That's for the posting trees - the posting lists attached to entry tuples is a different story.) Besides, if you convert the page when you insert to it, you might need to split it anyway. So it might not be very difficult to split if required. IMHO the main argument for not bothering with pg_upgrade is that the gain from the patch is so great that you'll want to REINDEX after the upgrade anyway, to shrink the index. I really don't have an opinion on whether we should attempt reading the old format. On one hand, it would be really nice to not have that caveat when you pg_upgrade (oh, you have GIN indexes, you have to reindex..). On the other hand, supporting the old format is a fair amount of extra code to maintain. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Any reasons to not move pgstattuple to core?
Hi, In my practice I quite often face the problem of bloated tables. I usually use pgstattuple to perform investigations. I also create a tool that uses UPDATEs based way to smoothly remove bloat (https://github.com/grayhemp/pgtoolkit), and it partially depends on pgstatuple too. To be more precise it gets much more effective with pgstattuple. Sometimes its installation leads to a headache, because it requires an approve from security and admins, it also a problem when I have a read-only access or no access to the database at all (eg. when consulting somebody by IM or phone). I think I am not the only person who faced these nuances. According to this I would like to know if it is possible to move pgstattuple to core? And if it is I would like to request this feature. Thank you. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.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] record identical operator
* Robert Haas (robertmh...@gmail.com) wrote: You could argue that HOT isn't user-visible, but we certainly advise people to think about structuring their indexing in a fashion that does not defeat HOT, so I think to some extent it is user-visible. I do think saying HOT is user-visible is really stretching things and do we really say somewhere please be careful to make sure your updates to key fields are *BINARY IDENTICAL* to what's stored or HOT won't be used? If anything, that should be listed on a PG 'gotchas' page. Also, in xfunc.sgml, we have this note: The planner also sometimes relies on comparing constants via bitwise equality, so you can get undesirable planning results if logically-equivalent values aren't bitwise equal. And that would be under C-Language Functions, which also includes things like take care to zero out any alignment padding bytes that might be present in structs. There are other places as well. If two node trees are compared using equal(), any Const nodes in that tree will be compared for binary equality. These would primairly be cases where we've created a Const out of a string, or similar, which the *user provided*, no? It strikes me as at least unlikely that we'd end up storing a given string from the user in different ways in memory and so this consideration, again, makes sense for people writing C code but not for your general SQL user. So for example MergeWithExistingConstraint() will error out if the constraints are equal under btree equality operators but not binary equal. equal() is also used in various places in the planner, which may be the reason for the above warning. I wonder if this would need to be changed because you could actually define constraints that operate at a binary level and therefore don't overlap even though they look like they overlap based on btree equality. The point I want to make here is that we have an existing precedent to use bitwise equality when we want to make sure that values are equivalent for all purposes, regardless of what opclass or whatever is in use. There are not a ton of those places but there are some. I agree that there are some cases and further that these operators provide a way of saying are these definitely the same? but they fall down on are these definitely different? That makes these operators useful for these kinds of optimizations, but that's it. Providing SQL level optimization-only operators like this is akin to the SQL standard defining indexes. Sure, that'd work, but it doesn't explain what's wrong with Kevin's proposal. You're basically saying that memcpy(a, b, len) is OK with you but if (memcmp(a, b, len) != 0) memcpy(a, b, len) is not OK with you. I don't understand how you can endorse copying the value exactly, but not be OK with the optimization that says, well if it already matches exactly, then we don't need to copy it. Adding new operators isn't all about what happens at the C-code level. That said, I agree that PG, in general, is more 'open' to exposing implementation details than is perhaps ideal, but it can also be quite useful in some instances. I don't really like doing that in top-level operators like this, but it doesn't seem like there's a whole lot of help for it. I'm not convinced that using the send/recv approach would be all that big of a performance hit but I've not tested it. We can certainly rip out the current implementation of REFRESH MATERIALIZED VIEW CONCURRENTLY and replace it with something that deletes every row in the view and reinserts them all, but it will be far less efficient than what we have now. All that is anybody is asking for here is the ability to skip deleting and reinserting rows that are absolutely identical in the old and new versions of the view. If this was an entirely internal thing, it'd be different, but it's not. Your send/recv proposal would let us also skip deleting and reinserting rows that are ALMOST identical except for not-normally-user-visible binary format differences... but since we haven't worried about allowing such cases for e.g. HOT updates, I don't think we need to worry about them here, either. In practice, such changes are rare as hen's teeth anyway. I'm not entirely convinced that what was done for HOT in this regard is a precedent we should be building on top of. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] GIN improvements part 1: additional information
On Fri, Oct 4, 2013 at 12:37 AM, Alexander Korotkov aekorot...@gmail.comwrote: On Thu, Oct 3, 2013 at 10:48 PM, Robert Haas robertmh...@gmail.comwrote: On Thu, Oct 3, 2013 at 2:43 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: It seems we've all but decided that we'll require reindexing GIN indexes in 9.4. I thought the consensus in Ottawa was strongly against that. I'm not aware that anyone has subsequently changed their position on the topic. Bruce is right to point out that we've done such things before and can therefore do it again, but just because we have the technical means to do it doesn't make it good policy. That having been said, if we do decide to break it... Let's take the opportunity to change some other annoyances with the current GIN on-disk format: ...then fixing as much as possible in one go-round is clearly a good plan. Let's see what options we have at all. I see following: 1) Drop support old GIN on-disk format. But users will have to reindex after pg_upgrade. 2) Insert kluges into GIN to support both old and new formats. So, kluges are kluges :) I don't see elegant way to do it for now, because formats are very different. 3) Upgrade GIN on-disk format in pg_upgrade. However, it would be rewriting almost whole index. Is it much better than just reindex? 4) Fork GIN2, leave GIN as is. It would lead to much of duplicated code. Any other options? I came to idea that I like option #4 more than option #2. If we try to make new GIN work with old page formats we have to maintain 3 use cases: 1) old GIN with old page format (because of old releases) 2) new GIN with old page format 3) new GIN with new page format If we create GIN2 we maintain only 2 use cases: 1) old GIN with old page format 2) new GIN with new page format The code of old GIN would be additional code in 9.4, but not additional code we maintain. Because we anyway maintain exactly same in old releases. The problem I see is how to migrate users to GIN2. We can't expect they read release notes, create GIN2 indexes and drop GIN1 indexes. A lot of users will still use GIN1, because of they don't care :) Ideally any new GIN index should be GIN2 and reindex turns GIN1 into GIN2. -- With best regards, Alexander Korotkov.
Re: [HACKERS] GSOC13 proposal - extend RETURNING syntax
I took a look at this patch today, and I'm pretty skeptical about whether it's on the right track. It adds a new kind of RTE called RTE_ALIAS, which doesn't seem particularly descriptive and alias is used elsewhere to mean something fairly different. More generally, I'm not convinced that adding a new type of RTE is the right way to handle this. The changes in pull_up_subqueries_recurse, pullup_replace_vars_callback, preprocess_targetlist, and build_joinrel_tlist seem like weird hacks. Those functions aren't directly related to this feature; why do they need to know about it? I wonder if we shouldn't be trying to handle resolution of these names at an earlier processing stage, closer to the processor. I notice that set_returning_clause_references() seems to have already solved the hard part of this problem, which is frobbing target list entries to return values from the new row rather than, as they naturally would, the old row. In fact, we can already get approximately the desired effect already: rhaas=# update foo as after set a = before.a + 1 from foo as before where before.a = after.a returning before.a, after.a; a | a ---+--- 1 | 2 (1 row) Now this is a hack, because we don't really want to add an extra scan/join just to get the behavior we want. But it seems to me significant that this processing makes Vars that refer to the target table refer to the new values, and if we got rid of it, they'd refer to the old values. Can't we contrive to make AFTER.x parse into the same Var node that x currently does? Then we don't need an RTE for it. And maybe BEFORE.x ought to parse to the same node that just plain x does but with some marking, or some other node wrapped around it (like a TargetEntry with some flag set?) that suppresses this processing. I'm just shooting from the hip here; that might be wrong in detail, or even in broad strokes, but it just looks to me like the additional RTE kind is going to bleed into a lot of places. This patch also has significant style issues. Conforming to PostgreSQL coding style is essential; if neither the author nor the reviewer fixes problems in this area, then that is essentially making it the committer's job, and the committer may not feel like taking time to do that. Here's a selection of issues that I noticed while reading this through: we use spaces around operators; the patch adds two blank lines that shouldn't be there to the middle of the variable declarations section; variables should be declared in the innermost possible scope; single-statement blocks shouldn't have curly braces; there shouldn't be whitespace before a closing parenthesis; there should be a space after if and before the subsequent parenthesis; braces should be uncuddled; code that does non-obvious things isn't commented. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Any reasons to not move pgstattuple to core?
On Thu, Oct 3, 2013 at 5:37 PM, Sergey Konoplev gray...@gmail.com wrote: In my practice I quite often face the problem of bloated tables. I usually use pgstattuple to perform investigations. I also create a tool that uses UPDATEs based way to smoothly remove bloat (https://github.com/grayhemp/pgtoolkit), and it partially depends on pgstatuple too. To be more precise it gets much more effective with pgstattuple. Sometimes its installation leads to a headache, because it requires an approve from security and admins, it also a problem when I have a read-only access or no access to the database at all (eg. when consulting somebody by IM or phone). I think I am not the only person who faced these nuances. Well, this is a general problem with any extension - somebody might want it on a system on which the admin is unable or unwilling to install it. But we can't put every possible extension in core. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Add DISCARD SEQUENCES command.
My C is very rusty but the traversal of SeqTableData doesn't seem correct.
It saves the seqtab-next pointer into next, frees seqtab and then
dereferences it.
Shouldn't that last line be: seqtab = next?
Kevin.
+/*
+ * Flush cached sequence information.
+ */
+void
+ResetSequenceCaches(void)
+{
+ SeqTableData *next;
+
+ while (seqtab != NULL)
+ {
+ next = seqtab-next;
+ free(seqtab);
+ seqtab = seqtab-next;
+ }
On 3 October 2013 14:23, Robert Haas rh...@postgresql.org wrote:
Add DISCARD SEQUENCES command.
DISCARD ALL will now discard cached sequence information, as well.
Fabrízio de Royes Mello, reviewed by Zoltán Böszörményi, with some
further tweaks by me.
Branch
--
master
Details
---
http://git.postgresql.org/pg/commitdiff/d90ced8bb22194cbb45f58beb0961251103aeff5
Modified Files
--
doc/src/sgml/ref/discard.sgml | 12 +++-
src/backend/commands/discard.c |8 +++-
src/backend/commands/sequence.c| 16
src/backend/parser/gram.y |9 -
src/backend/tcop/utility.c |3 +++
src/bin/psql/tab-complete.c|2 +-
src/include/commands/sequence.h|1 +
src/include/nodes/parsenodes.h |1 +
src/test/regress/expected/sequence.out |3 +++
src/test/regress/sql/sequence.sql |2 ++
10 files changed, 53 insertions(+), 4 deletions(-)
--
Sent via pgsql-committers mailing list (pgsql-committ...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers
Re: [HACKERS] Any reasons to not move pgstattuple to core?
On Thu, Oct 3, 2013 at 3:34 PM, Robert Haas robertmh...@gmail.com wrote: Well, this is a general problem with any extension - somebody might want it on a system on which the admin is unable or unwilling to install it. But we can't put every possible extension in core. The flip-side is that we could have made an awful lot of built-in things extensions, but for whatever reason chose not to. I'm not necessarily in favor of putting pgstattuple in core, but the question should be asked: Why should we do this here? In what way is pgstattuple like or not like the other things that are in core? -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Any reasons to not move pgstattuple to core?
On Thu, Oct 3, 2013 at 3:55 PM, Peter Geoghegan p...@heroku.com wrote: On Thu, Oct 3, 2013 at 3:34 PM, Robert Haas robertmh...@gmail.com wrote: Well, this is a general problem with any extension - somebody might want it on a system on which the admin is unable or unwilling to install it. But we can't put every possible extension in core. The flip-side is that we could have made an awful lot of built-in things extensions, but for whatever reason chose not to. I'm not necessarily in favor of putting pgstattuple in core, but the question should be asked: Why should we do this here? In what way is pgstattuple like or not like the other things that are in core? I would highlight it as it became a kind of routine one. Also, sometimes it is required to solve problems, not to make new features, so it often can not wait. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.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] GSOC13 proposal - extend RETURNING syntax
W dniu 04.10.2013 00:28, Robert Haas pisze: I wonder if we shouldn't be trying to handle resolution of these names at an earlier processing stage, closer to the processor. Maybe it can be done in parser (in flex?) but at now it seems to be more isolated feature. In fact, we can already get approximately the desired effect already: rhaas=# update foo as after set a = before.a + 1 from foo as before where before.a = after.a returning before.a, after.a; a | a ---+--- 1 | 2 (1 row) Compare EXPLAIN ANALYZE VERBOSE on your statement and on patched workflow. I can see significant difference. And your after returns the value after whole the work (after trigger fired) as I know (I don't know if it is needed or not, I only point at the difference). Now this is a hack, because we don't really want to add an extra scan/join just to get the behavior we want. But it seems to me significant that this processing makes Vars that refer to the target table refer to the new values, and if we got rid of it, they'd refer to the old values. Can't we contrive to make AFTER.x parse into the same Var node that x currently does? Then we don't need an RTE for it. And maybe BEFORE.x ought to parse to the same node that just plain x does but with some marking, or some other node wrapped around it (like a TargetEntry with some flag set?) that suppresses this processing. I'm just shooting from the hip here; that might be wrong in detail, or even in broad strokes, but it just looks to me like the additional RTE kind is going to bleed into a lot of places. While planning/analyzing the problem there were many ideas about hot to solve it. I was trying to avoid adding new RTE and referencing to core table. However it makes more and more issues. You can see some PoC on the https://github.com/davidfetter/postgresql_projects/compare/returning_before_after (other ideas I revert w/o commit because I couldn't get expected result). The other major reason was that we can avoid touching executor and/or parser's core (flex) this way. One observation: why shouldn't we use the values computed at the moment (it would be computed again if we want to do it later, in executor)? I think we can do it by modify the Var structure (add some kind of flag while generating the vars in parser?) but I'm not sure if it is good idea. The major issue is to know if the Var/TargetEntry references to the real alias BEFORE (named with AS syntax or even the real table-name - I can see there is no difference in code) or the virtual (from feature patch) BEFORE. Doing it in parser (more low-level) would be very awful - we'd need to check in which part of statement BEFORE/AFTER is placed (it is not allowed to use it in the other places than in RETURNING). We don't want to make BEFORE and AFTER restricted keywords. Now most of the code means don't touch these because they are not real :) If anyone has the fresh idea to it better, please write it by mail, I don't have more ideas how to solve it. This patch also has significant style issues. I'll try to fix it soon. Regards, Karol Trzcionka -- 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] record identical operator
On 10/04/2013 12:22 AM, Stephen Frost wrote: That said, I agree that PG, in general, is more 'open' to exposing implementation details than is perhaps ideal, Every *real* system is more open to exposing implementation details than is *ideal*. One very popular implementation detail which surprises users over and over is performance under different use cases. There is no way you can hide this. That said, I see nothing bad in having an operator for binary equal or alternately called guaranteed equal. Its negator is not guaranteed unequal but not guaranteed to be equal The main exposed implementation detail of this operator is that it is very fast and can be recommended to be used at user level for speeding up equal query like this SELECT * FROM t WHERE guaranteed equal or equal where the plain equal will only be called when fast guaranteed equal fails. a bit similar to how you can cut down on index size on long text fields by indexing their hashes and then querying SELECT * FROM t WHERE hashtext(verylongtext) = hashtext(sample) AND verylongtext = sample It is absolutely possible that the fact that hash clashes exist also confuses some users the same way the possibility of having binary inequality and be still NOT DISTINCT FROM, but I argue that having a fast guaranteed equal operation available to users is useful. some users may also initially get confused by SELECT 3/2; returning 1 and not the right answer of 1.5. They may even bitch and moan that PostgreSQL is completely broken. But then they look it up or ask on the net and are confused no more. Greetings Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Add DISCARD SEQUENCES command.
On Thu, Oct 3, 2013 at 6:38 PM, Kevin Hale Boyes kcbo...@gmail.com wrote:
My C is very rusty but the traversal of SeqTableData doesn't seem correct.
It saves the seqtab-next pointer into next, frees seqtab and then
dereferences it.
Shouldn't that last line be: seqtab = next?
Kevin.
+/*
+ * Flush cached sequence information.
+ */
+void
+ResetSequenceCaches(void)
+{
+ SeqTableData *next;
+
+ while (seqtab != NULL)
+ {
+ next = seqtab-next;
+ free(seqtab);
+ seqtab = seqtab-next;
+ }
Oops, good catch. Will fix, thanks.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSOC13 proposal - extend RETURNING syntax
On Thu, Oct 3, 2013 at 7:54 PM, Karol Trzcionka karl...@gmail.com wrote: Compare EXPLAIN ANALYZE VERBOSE on your statement and on patched workflow. I can see significant difference. And your after returns the value after whole the work (after trigger fired) as I know (I don't know if it is needed or not, I only point at the difference). Sure, I'm not saying we should implement it that way. I'm just pointing out that the ability already exists, at the executor level, to return either tuple. So I think the executor itself shouldn't need to be changed; it's just a matter of getting the correct plan tree to pop out. While planning/analyzing the problem there were many ideas about hot to solve it. Do you have a link to previous discussion on the mailing list? I think we can do it by modify the Var structure (add some kind of flag while generating the vars in parser?) but I'm not sure if it is good idea. The major issue is to know if the Var/TargetEntry references to the real alias BEFORE (named with AS syntax or even the real table-name - I can see there is no difference in code) or the virtual (from feature patch) BEFORE. Doing it in parser (more low-level) would be very awful - we'd need to check in which part of statement BEFORE/AFTER is placed (it is not allowed to use it in the other places than in RETURNING). We don't want to make BEFORE and AFTER restricted keywords. You're right, it can't happen actually in the parser. But maybe it can happen during parse analysis. I'd spend some time looking at transformColumnRef(), because that's where we translate things x.y into Var nodes. I'm not positive there's enough information available at that stage, but if p_target_rangetblentry is populated at that point, you should be able to make AFTER.x translate to a Var referencing that range table entry. It's a bit less clear how we know that we're inside the returning-list at that point; I'm not sure how much work it would be to pass that information down. But I think it's worth looking at. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] record identical operator
* Hannu Krosing (ha...@krosing.net) wrote: The main exposed implementation detail of this operator is that it is very fast and can be recommended to be used at user level for speeding up equal query like this SELECT * FROM t WHERE guaranteed equal or equal where the plain equal will only be called when fast guaranteed equal fails. Yeah, this would be exactly the kind of misuse that we will need to be prepared to support with these new operators. If this is actually faster/better/whatever, then we should be implementing it in our conditional handling, not encouraging users to create hacks like this. a bit similar to how you can cut down on index size on long text fields by indexing their hashes and then querying SELECT * FROM t WHERE hashtext(verylongtext) = hashtext(sample) AND verylongtext = sample This case clearly requires a great deal more thought and consideration on the DBA's side and is also a lot more obvious what it's doing than having 'where x *= 123 or x = 123'. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Any reasons to not move pgstattuple to core?
Sergey Konoplev escribió: On Thu, Oct 3, 2013 at 3:55 PM, Peter Geoghegan p...@heroku.com wrote: On Thu, Oct 3, 2013 at 3:34 PM, Robert Haas robertmh...@gmail.com wrote: Well, this is a general problem with any extension - somebody might want it on a system on which the admin is unable or unwilling to install it. But we can't put every possible extension in core. The flip-side is that we could have made an awful lot of built-in things extensions, but for whatever reason chose not to. I'm not necessarily in favor of putting pgstattuple in core, but the question should be asked: Why should we do this here? In what way is pgstattuple like or not like the other things that are in core? I would highlight it as it became a kind of routine one. Also, sometimes it is required to solve problems, not to make new features, so it often can not wait. Greg Smith made a list some months ago of contrib modules that were essential for forensics analysis and such. Weren't we going to do something special about those? -- Á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] Any reasons to not move pgstattuple to core?
On Thu, Oct 3, 2013 at 6:36 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Greg Smith made a list some months ago of contrib modules that were essential for forensics analysis and such. Weren't we going to do something special about those? It was more like two years ago. I do still think that that kind of effort makes a lot of sense. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GIN improvements part 1: additional information
On Fri, Oct 4, 2013 at 02:23:33AM +0400, Alexander Korotkov wrote: I came to idea that I like option #4 more than option #2. If we try to make new GIN work with old page formats we have to maintain 3 use cases: 1) old GIN with old page format (because of old releases) 2) new GIN with old page format 3) new GIN with new page format If we create GIN2 we maintain only 2 use cases: 1) old GIN with old page format 2) new GIN with new page format The code of old GIN would be additional code in 9.4, but not additional code we maintain. Because we anyway maintain exactly same in old releases. The problem I see is how to migrate users to GIN2. We can't expect they read release notes, create GIN2 indexes and drop GIN1 indexes. A lot of users will still use GIN1, because of they don't care :) Ideally any new GIN index should be GIN2 and reindex turns GIN1 into GIN2. I am not sure I like the complexity of a GIN2, but we should give this problem some serious thought as it will affect how we deal with other on-disk index changes in the future. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Suggestion: Issue warning when calling SET TRANSACTION outside transaction block
On Thu, Oct 3, 2013 at 8:35 PM, Andres Freund and...@2ndquadrant.com wrote:
On 2013-09-30 22:19:31 -0400, Bruce Momjian wrote:
On Sun, Sep 29, 2013 at 11:40:51AM +0530, Amit Kapila wrote:
Shouldn't we do it for Set Constraints as well?
Oh, very good point. I missed that one. Updated patch attached.
I am glad you are seeing things I am not. :-)
1. The function set_config also needs similar functionality, else
there will be inconsistency, the SQL statement will give error but
equivalent function set_config() will succeed.
SQL Command
postgres=# set local search_path='public';
ERROR: SET LOCAL can only be used in transaction blocks
Function
postgres=# select set_config('search_path', 'public', true);
set_config
public
(1 row)
I looked at this but could not see how to easily pass the value of
'isTopLevel' down to the SELECT. All the other checks have isTopLevel
passed down from the utility case statement.
Doesn't sound like a good idea to prohibit that anyway, it might
intentionally be used as part of a more complex statement where it only
should take effect during that single statement.
Agreed and I think it is good reason for not changing behaviour of
set_config().
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSI freezing bug
On Thu, Oct 03, 2013 at 06:19:49AM -0700, Kevin Grittner wrote: Heikki Linnakangas hlinnakan...@vmware.com wrote: IMHO it would be better to remove xmin from the lock key, and vacuum away the old predicate locks when the corresponding tuple is vacuumed. The xmin field is only required to handle the case that a tuple is vacuumed, and a new unrelated tuple is inserted to the same slot. Removing the lock when the tuple is removed fixes that. This seems definitely safe: we need the predicate locks to determine if someone is modifying a tuple we read, and certainly if it's eligible for vacuum nobody's going to be modifying that tuple anymore. In fact, I cannot even come up with a situation where you would have a problem if we just removed xmin from the key, even if we didn't vacuum away old locks. I don't think the old lock can conflict with anything that would see the new tuple that gets inserted in the same slot. I have a feeling that you could probably prove that if you stare long enough at the diagram of a dangerous structure and the properties required for a conflict. This would also be safe, in the sense that it's OK to flag a conflict even if one doesn't exist. I'm not convinced that it isn't possible to have false positives this way. I think it's possible for a tuple to be vacuumed away and the ctid reused before the predicate locks on it are eligible for cleanup. (In fact, isn't this what was happening in the thread Kevin linked?) You are the one who suggested adding xmin to the key: http://www.postgresql.org/message-id/4d5a36fc.6010...@enterprisedb.com I will review that thread in light of your recent comments, but the fact is that xmin was not originally in the lock key, testing uncovered bugs, and adding xmin fixed those bugs. I know I tried some other approach first, which turned out to be complex and quite messy -- it may have been similar to what you are proposing now. At the time, we thought it was necessary for a predicate lock to lock *all future versions* of a tuple, and so we had a bunch of code to maintain a version chain. That was fraught with bugs, and turned out not to be necessary (IIRC, we worked that out at the pub at PGcon). That made it critical to distinguish different tuples that had the same ctid because they could wind up in the wrong chain or cause a cycle. With that code ripped out, that's no longer an issue. But all this is an exceptionally subtle part of what was an exceptionally complex patch, so a lot of careful thought is needed here... It seems to me that a change such as you are now suggesting is likely to be too invasive to back-patch. Do you agree that it would make sense to apply the patch I have proposed, back to 9.1, and then consider any alternative as 9.4 material? I agree with this. Dan -- Dan R. K. PortsUW CSEhttp://drkp.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] Suggestion: Issue warning when calling SET TRANSACTION outside transaction block
On Thu, Oct 3, 2013 at 8:32 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Oct 3, 2013 at 11:50:09AM +0530, Amit Kapila wrote: I looked at this but could not see how to easily pass the value of 'isTopLevel' down to the SELECT. All the other checks have isTopLevel passed down from the utility case statement. Yes, we cannot pass isTopLevel, but as isTopLevel is used to decide whether we are in function (user defined) call, so if we can find during statement execution (current case set_config execution) that current statement is inside user function execution, then it can be handled. For example, one of the ways could be to use a mechanism similar to setting of user id and sec context used by fmgr_security_definer() (by calling function SetUserIdAndSecContext()), once userid and sec context are set by fmgr_security_definer(), later we can use InSecurityRestrictedOperation() anywhere to give error. For current case, what we can do is after analyze (pg_analyze_and_rewrite), check if its not a builtin function (as we can have functionid after analyze, so it can be checked fmgr_isbuiltin(functionId)) and set variable to indicate that we are in function call. Any better or simpler idea can also be used to identify isTopLevel during function execution. Doing it only for detection of transaction chain in set_config path might seem to be more work, but I think it can be used at other places for detection of transaction chain as well. I am also worried about over-engineering this. I had tried to think hard but could not come up with a simpler change which could have handled all cases. We can leave the handling for set_config() and proceed with patch as Andres already given a reason where set_config() can be useful within a statement as well. I will wait to see if anyone else would find top-level detection useful, and if not, I will just apply my version of that patch that does not handle set_config. I had verified the patch once again and ran regression, everything looks fine. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for fail-back without fresh backup
On Fri, Sep 27, 2013 at 6:44 PM, Sawada Masahiko sawada.m...@gmail.com wrote: On Fri, Sep 27, 2013 at 5:18 PM, Pavan Deolasee pavan.deola...@gmail.com wrote: On Fri, Sep 27, 2013 at 1:28 PM, Sawada Masahiko sawada.m...@gmail.com wrote: Thank you for comment. I think it is good simple idea. In your opinion, if synchronous_transfer is set 'all' and synchronous_commit is set 'on', the master wait for data flush eve if user sets synchronous_commit to 'local' or 'off'. For example, when user want to do transaction early, user can't do this. we leave the such situation as constraint? No, user can still override the transaction commit point wait. So if synchronous_transfer is set to all: - If synchronous_commit is ON - wait at all points - If synchronous_commit is OFF - wait only at buffer flush (and other related to failback safety) points synchronous_transfer is set to data_flush: - If synchronous_commit is either ON o OFF - do not wait at commit points, but wait at all other points synchronous_transfer is set to commit: - If synchronous_commit is ON - wait at commit point - If synchronous_commit is OFF - do not wait at any point Thank you for explain. Understood. if synchronous_transfer is set 'all' and user changes synchronous_commit to 'off'( or 'local') at a transaction, the master server wait at buffer flush, but doesn't wait at commit points. Right? In currently patch, synchronous_transfer works in cooperation with synchronous_commit. But if user changes synchronous_commit at a transaction, they are not in cooperation. So, your idea might be better than currently behaviour of synchronous_transfer. I attached the v11 patch which have fixed following contents. - synchronous_transfer controls to wait at only data flush level, synchronous_commit controls to wait at commit level. ( Based on Pavan suggestion) - If there are no sync replication standby name, both synchronous_commit and synchronous_transfer don't work. - Fixed that we didn't support failback-safe standby. Previous patch can not support failback-safe standby. Because the patch doesn't wait at FlushBuffer which is called by autovacuum. So, if user want to do transaction early temporarily, user need to change the synchronous_transfer value and reload postgresql.conf. Regards, --- Sawada Masahiko synchronous_transfer_v11.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Compression of full-page-writes
On Mon, Sep 30, 2013 at 1:55 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Mon, Sep 30, 2013 at 10:04 AM, Fujii Masao masao.fu...@gmail.com wrote: On Mon, Sep 30, 2013 at 1:27 PM, KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp wrote: Hi Fujii-san, (2013/09/30 12:49), Fujii Masao wrote: On second thought, the patch could compress WAL very much because I used pgbench. I will do the same measurement by using another benchmark. If you hope, I can test this patch in DBT-2 benchmark in end of this week. I will use under following test server. * Test server Server: HP Proliant DL360 G7 CPU:Xeon E5640 2.66GHz (1P/4C) Memory: 18GB(PC3-10600R-9) Disk: 146GB(15k)*4 RAID1+0 RAID controller: P410i/256MB Yep, please! It's really helpful! I think it will be useful if you can get the data for 1 and 2 threads (may be with pgbench itself) as well, because the WAL reduction is almost sure, but the only thing is that it should not dip tps in some of the scenarios. Here is the measurement result of pgbench with 1 thread. scaling factor: 100 query mode: prepared number of clients: 1 number of threads: 1 duration: 900 s WAL Volume - 1344 MB (full_page_writes = on) - 349 MB (compress) - 78 MB (off) TPS 117.369221 (on) 143.908024 (compress) 163.722063 (off) 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
