Re: [HACKERS] Postgres as Historian
Thanks for all your responses and my apologies for putting the question in the wrong list. I think OLAP is the answer for my requirements. Regards, Hardik On Wed, Aug 4, 2010 at 5:40 AM, Greg Smith g...@2ndquadrant.com wrote: Hardik Belani wrote: For this i can create a table with number and time (may be time offset instead of timestamp) as columns. But still it will require me to store huge number of rows in the order of few millions. Data is read only and only inserts can happen. But I need to perform all kinds of aggregation to get various statistics. for example: daily avg, monthly avg etc.. You've unfortunately asked on the wrong list about this. pgsql-hackers is intended mainly for discussion related to the source code of PostgreSQL, so this is off-topic for it. The people who like to argue about the best way to implement aggregates and the like are on the pgsql-performance list. You'd be more likely to get detailed responses if you asked this question there. That group loves to talk about how to design things for other people. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us http://www.2ndquadrant.us/
Re: [HACKERS] tracking inherited columns (was: patch for check constraints using multiple inheritance)
Robert Haas wrote: On Tue, Aug 3, 2010 at 3:05 PM, Yeb Havinga yebhavi...@gmail.com wrote: Yeb Havinga wrote: The underlying cause is the failure of the code to recognize that if relation C inherits from both A and B, where A and B both have column x, that A.x 'is the same as' B.x, where the 'is the same as' relation is the same that holds for (A.x, C.x) and (B.x, C.x), which the code does a lot of trouble for to recognize. This means that if some definition is altered on A.x, only C.x is updated and B.x not touched. IMO this is wrong and either a multiple inheritance structure like this should be prohibited, since the user did not explicitly declare that A.x and B.x 'are the same' (by e.g. defining a relation D.x and have A and B inherit from that), or the code should update parents of relations when the childs are updated. Thinking about this a bit more, the name 'is the same as' is a bit confusing, since that relation might not be commutative. C.x 'inherits properties from' A.x, or C.x 'is defined by' A.x are perhaps better names, that reflect that the converse might not hold. OTOH, what does C.x 'inherits (all) properties from' A.x mean? If it means that for all properties P, P(C.x) iff P(A.x), then C.x = A.x commutatively and by similar reasoning A.x = B.x. ALTER TABLE top1 RENAME COLUMN a_table_column TO another_table_column; When looking for previous discussions that was referred to upthread, the first thing I found was this recent thread about the exactly the same problem http://archives.postgresql.org/pgsql-hackers/2010-01/msg03117.php Sorry for the double post, however the previous discussion postponed work to .. now, so maybe there is some value in first trying to specify exactly what 'inherits' means, and derive consequences for code behaviour from that. Yeah, I was thinking about that thread, too, on my drive home from Metuchen. I just read that thread. In the beginning there is a short discussion what the non-astonishing behaviour of the RENAME in the case of multiple origin inheritance should be, which is preventing renames or any property change in that case. I think we should explore the possibilty of allowing the RENAME more. What if on a RENAME of a column (maybe with a necessary explicit CASCADE) in the multiple origin parent case, the parents with the same columns are altered too? I don't think it is ashtonishing for users; after all they've created the tree in the first place, but mostly for programmers with some experience with inheritance in computer languages: inheritance should go down, not up. That's why I tried to make reasoning exact, to figure out why it would be ok (or not) to update another parent as well. The reasoning can be made more formal/exact, but I believe in its current form it makes a strong case to technically allow to prograpage property changes to other parents as well (if they have the same inherited column). 1. If you're changing properties of a column, you need to verify for each relation in the inheritance tree that the expected attinhcount and the actual attinhcount match. If, for any relation in the inheritance tree rooted at the named table, they don't, then they are doubly inherited there, from some other table outside the hierarchy rooted at the named table, and the operation must fail. If we want to block these RENAMES, yes. This is essentially KaiGai's patch http://archives.postgresql.org/pgsql-hackers/2010-01/msg02878.php 2. If you're adding a column, you need to propagate the new column to relations that don't have it yet, but if you find one that already has it than you adjust attinhcount and don't recurse to its chidlren. Sound ok. 3. If you're dropping a column, you essentially decrement the attinhcount of all your children; then you recurse into any that reach attincount = 0 and not attislocal and drop the column there as well. This too. regards, Yeb Havinga -- 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] Develop item from TODO list
Thanks for the advice! Yes, we are new to linux too :) We have chosen Eclipse, because we have already experience with it. However, after downloading the code from CVS, we can't build it, because of some include commands in *tutorial / complex.c *says *No such file or directory*. Does anybody know what the clue is? What should we get, if the code starts? All the best, Chris Viktor 2010/8/3 Joshua D. Drake j...@commandprompt.com On Tue, 2010-08-03 at 11:10 -0400, Robert Haas wrote: On Tue, Aug 3, 2010 at 9:01 AM, Thom Brown t...@linux.com wrote: I can't answer the main question, but you can try Anjuta as a C editor: http://www.anjuta.org/ Or vi. cough. The hint here would be: As we have few experience with open-source programs, can someone recommend a good C-editor for Ubuntu? I doubt they are unix/linux people at all. Putting them into Vi land is a bit torturous. There are quite a few editors out there but here are some that are going to be a bit more user friendly (in terms of learning curve): Bluefish Anjuta Kate If you want a full environment with projects, SCM integration etc... I would suggest Eclipse. That said, if you can get a handle on VI/VIM or (joe :P) you will probably be pleased with the efficiency. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
Re: [HACKERS] documentation for committing with git
On 21/07/10 18:22, Robert Haas wrote: At the developer meeting, I promised to do the work of documenting how committers should use git. So here's a first version. http://wiki.postgresql.org/wiki/Committing_with_Git Note that while anyone is welcome to comment, I mostly care about whether the document is adequate for our existing committers, rather than whether someone who is not a committer thinks we should manage the project differently... that might be an interesting discussion, but we're theoretically making this switch in about a month, and getting agreement on changing our current workflow will take about a decade, so there is not time now to do the latter before we do the former. So I would ask everyone to consider postponing those discussions until after we've made the switch and ironed out the kinks. On the other hand, if you have technical corrections, or if you have suggestions on how to do the same things better (rather than suggestions on what to do differently), that would be greatly appreciated. I'm a bit disappointed that the wiki page advises against git-new-workdir - that's exactly what I was planning to use. It claims there's data loss issues with that, does someone know the details? Is there really a risk of data loss if multiple workdirs are used, in our situation? I'm planning to have only one local repository, with one workdir per branch. When applying a patch to multiple branches, I could work simultaneously on all branches, finish and commit the patches on all branches, and finally do one git push to push all the changes to the PostgreSQL repository in one go. I'm working like that with the internal EDB repository right now, and seems to work fine. I keep the master branch checked out in the main workdir, within the repository, and for each backbranch there's an extra workdir created with git-new-workdir. Though I've only been doing this for a month or so - if there's issues I might not have noticed yet. PS. I highly recommend always using git push --dry-run before the real thing, to make sure you're not doing anything funny. -- Heikki Linnakangas 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] Synchronous replication
On 27/07/10 13:29, Fujii Masao wrote: On Tue, Jul 27, 2010 at 7:39 PM, Yeb Havingayebhavi...@gmail.com wrote: Fujii Masao wrote: I noted the changes in XlogSend where instead of *caughtup = true/false it now returns !MyWalSnd-sndrqst. That value is initialized to false in that procedure and it cannot be changed to true during execution of that procedure, or can it? That value is set to true in WalSndWakeup(). If WalSndWakeup() is called after initialization of that value in XLogSend(), *caughtup is set to false. There's some race conditions with the signaling. If another process finishes XLOG flush and sends the signal when a walsender has just finished one iteration of its main loop, walsender will reset xlogsend_requested and go to sleep. It should not sleep but send the pending WAL immediately. -- Heikki Linnakangas 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] merge command - GSoC progress
Dear Robert, I am just considering that there may be some logical mistakes for my rule rewriting strategy of MERGE actions. In my current design, if we find that an action type, say UPDATE, is replaced by INSTEAD rules, we will remove all the actions of this type from the MERGE command, as if they are not be specified by user from the beginning. See the test example in my pages for this situation. https://wiki.postgresql.org/wiki/MergeTestExamples#With_INSTEAD_rules Now,I am thinking that maybe we should keep the replaced actions in action list, and just mark them to be invalid. If one join tuple from the main plan fits the condition of this action, we will do nothing on it. This strategy is a little bit different with the current one. If we delete an action, the tuples that meet it condition will be caught by other actions. If we keep it, the tuples that match it will be skipped. I think the new design is more logical, and I wonder your opinion on this problem. Yours Boxuan
Re: [HACKERS] documentation for committing with git
On Sun, Aug 1, 2010 at 5:08 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I'm a bit disappointed that the wiki page advises against git-new-workdir - that's exactly what I was planning to use. It claims there's data loss issues with that, does someone know the details? Is there really a risk of data loss if multiple workdirs are used, in our situation? As I understand it, there is a risk of corruption if you ever do git gc in the respository that the get-new-workdir was spawned from. See also Daniel Farina's email, here: http://archives.postgresql.org/pgsql-hackers/2010-07/msg01489.php It's not easy for me to mentally verify that the way I work won't cause problems with this approach, but you may feel differently, and that's fine. PS. I highly recommend always using git push --dry-run before the real thing, to make sure you're not doing anything funny. Ah, that sounds like a good idea. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] tracking inherited columns (was: patch for check constraints using multiple inheritance)
On Wed, Aug 4, 2010 at 3:48 AM, Yeb Havinga yebhavi...@gmail.com wrote: I just read that thread. In the beginning there is a short discussion what the non-astonishing behaviour of the RENAME in the case of multiple origin inheritance should be, which is preventing renames or any property change in that case. I think we should explore the possibilty of allowing the RENAME more. If child inherits column A from parent1 and parent2, and it is then renamed to B in parent2, what should the name be in the child after the rename is completed? For bonus points, how should pg_dump handle this to make sure the state after a dump and reload matches the state before the dump and reload? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] tracking inherited columns (was: patch for check constraints using multiple inheritance)
Robert Haas wrote: On Wed, Aug 4, 2010 at 3:48 AM, Yeb Havinga yebhavi...@gmail.com wrote: I just read that thread. In the beginning there is a short discussion what the non-astonishing behaviour of the RENAME in the case of multiple origin inheritance should be, which is preventing renames or any property change in that case. I think we should explore the possibilty of allowing the RENAME more. If child inherits column A from parent1 and parent2, and it is then renamed to B in parent2, what should the name be in the child after the rename is completed? The column should be renamed to B in parent2, child and parent1. For bonus points, how should pg_dump handle this to make sure the state after a dump and reload matches the state before the dump and reload? If the change happens in a single transaction there should be no problems here, as opposed to e.g. have the user issue two renames. Did I get the bonus points? :-) regards, Yeb Havinga -- 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] tracking inherited columns (was: patch for check constraints using multiple inheritance)
On Wed, Aug 4, 2010 at 6:41 AM, Yeb Havinga yebhavi...@gmail.com wrote: If child inherits column A from parent1 and parent2, and it is then renamed to B in parent2, what should the name be in the child after the rename is completed? The column should be renamed to B in parent2, child and parent1. Uh, really? Wow. You want to follow the inheritance hierarchy in both directions, both down and up? That seems like it could be confusing. For bonus points, how should pg_dump handle this to make sure the state after a dump and reload matches the state before the dump and reload? If the change happens in a single transaction there should be no problems here, as opposed to e.g. have the user issue two renames. Did I get the bonus points? :-) Sure, though I'm not sure I like the basic idea. :-) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] tracking inherited columns (was: patch for check constraints using multiple inheritance)
Robert Haas wrote: On Wed, Aug 4, 2010 at 6:41 AM, Yeb Havinga yebhavi...@gmail.com wrote: If child inherits column A from parent1 and parent2, and it is then renamed to B in parent2, what should the name be in the child after the rename is completed? The column should be renamed to B in parent2, child and parent1. Uh, really? Wow. You want to follow the inheritance hierarchy in both directions, both down and up? That seems like it could be confusing. Yes, the idea is to follow the up direction in the case of column changes, only if the column is already present in another parent. To avoid confusion we could block the first attempt of a change with a message other parents exists, and the hint to add e.g. CASCADE to update the definition in the other parent as well. -- Yeb -- 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] multibyte charater set in levenshtein function
On Mon, Aug 2, 2010 at 5:20 AM, Robert Haas robertmh...@gmail.com wrote:
I reviewed this code in a fair amount of detail today and ended up
rewriting it. In general terms, it's best to avoid changing things
that are not relevant to the central purpose of the patch. This patch
randomly adds a whole bunch of whitespace and removes a whole bunch of
comments which I find useful and do not want to see removed. It took
me about an hour to reverse out all of those changes, which then let
me get down to the business of actually analyzing the code.
I'm sorry. This is my first patch, I will be more accurate next time. But, I
think there is no unified opinion about some changes like replacement !m
with m==0.
I think this line is not correct:
if (m != s_bytes n != t_bytes)
The correct negation of assumption, that both strings are single-byte, is
the assumption, that at least one string is not single-byte. In this patch
levenshtein function can calculate distance incorrectly:
test=# select levenshtein('фw', 'ww');
levenshtein
-
2
(1 row)
This line should be rewritten by this.
if (m != s_bytes || n != t_bytes)
The attached patch takes the approach of using a fast-path for just
the innermost loop when neither string contains any multi-byte
characters (regardless of whether or not the encoding allows
multi-byte characters). It's still slower than CVS HEAD, but for
strings containing no multi-byte characters it's only marginally, if
at all, slower than previous releases, because 9.0 doesn't contain
your fix to avoid the extra string copy; and it's faster than your
implementation even when multi-byte characters ARE present. It is
slightly slower on single-byte encoding databases (I tested
SQL_ASCII), but still faster than previous releases. It's also quite
a bit less code duplication.
Can I look at the test which shows that your implementation is faster than
my when multi-byte characters are present? My tests show reverse. For
example, with russian dictionary of openoffice:
With my version of patch:
test=# select sum(levenshtein(word, 'фывафыва')) from test;
sum
-
1675281
(1 row)
Time: 277,190 ms
With your version of patch:
test=# select sum(levenshtein(word, 'фывафыва')) from test;
sum
-
1675281
(1 row)
Time: 398,011 ms
I think that the cause of slow down slowdown is memcmp function. This
function is very fast for long parts of memory, but of few bytes inline
function is faster, because compiler have more freedom for optimization.
After replacing memcmp with inline function like following in your
implementation:
static inline bool char_cmp(const char *s, const char *d, int len)
{
while (len--)
{
if (*s++ != *d++)
return false;
}
return true;
}
Code becomes much faster:
test=# select sum(levenshtein(word, 'фывафыва')) from test;
sum
-
1675281
(1 row)
Time: 241,272 ms
With best regards,
Alexander Korotkov.
[HACKERS] Review of Synchronous Replication patches
Hi, After setting up a real SR cluster based on V9 beta3 and Fuji's SR patch synch_rep_0722.patch and doing some simple update_and_check tests, it seems that active and standby are not in sync. Can this be a problem of the SR or the HSB feature ? Or is fsync still not supported ? Used configuration: node1# cat postgresql.conf ... max_wal_senders = 2 wal_level = 'hot_standby' wal_keep_segments = 10 checkpoint_segments = 10 checkpoint_timeout = 3min hot_standby = on quorum = 1 wal_sender_delay = 1ms # walsender cycle time, 1-1 milliseconds node2# cat recovery.conf standby_mode = 'true' #replication_mode = 'recv' replication_mode = 'fsync' Test script is: node1# cat check.sh #set -x RC=$(./bin/psql postgres -t -h node1 -p 5432 -c drop table test ;) RC=$(./bin/psql postgres -t -h node1 -p 5432 -c create table test (i integer,d integer);) RC=$(./bin/psql postgres -t -h node1 -p 5432 -c insert into test values (1,1);) retry=1 while (true) do RC=$(./bin/psql postgres -t -h node1 -p 5432 -c update test set d=$retry where i=1;) DATA=$(./bin/psql postgres -t -h node2 -p 5432 -c select d from test wherei=1;) DIFF=$(( $retry - $DATA )) echo $retry - $DATA - $DIFF i=$(( retry++ )) done Output is: 1 - 1 - 0 2 - 1 - 1 3 - 2 - 1 4 - 3 - 1 5 - 4 - 1 6 - 5 - 1 7 - 6 - 1 8 - 8 - 0 9 - 9 - 0 ... Any ideas ? -- 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] multibyte charater set in levenshtein function
Now I think patch is as good as can be. :) I'm going to prepare less-or-equal function in same manner as this patch. With best regards, Alexander Korotkov.
Re: [HACKERS] Patch to show individual statement latencies in pgbench output
On Aug3, 2010, at 21:16 , Greg Smith wrote: That was a leftover of the trimming and comment skipping logic, which my patch moves to process_command. I think there's still a trimming error here--line 195 of the new patch is now removing the declaration of i just before it sets it to zero? Hm, I think it's just the diff thats miss-leading there. It correctly marks the int i line as removed with a -, but for some reason marks the i = 0 line (and its successors) with a !, although they're removed too, and not modified. On the coding standard side, I noticed all your for loops are missing a space between the for and the (; that should get fixed. Fixed Finally, now that the rest of the patch is looking in good shape and is something I think is worth considering to commit, it's time to work on the documentation SGML. I've added the -r option to the list of pgbench options in pgbench.sgml and also added a short section that shows how the output looks like, similar to how things are done for the -l option. Also: when generating multiple versions of a patch like this, standard practice is to add something like -vX to the naming, so those of us trying to review can keep them straight. Will do from now on. Updated patch is attached. I've also pushed this as branch pgbench_statementlatency to git://github.com/fgp/postgres.git best regards, Florian Pflug pgbench_statementlatency_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] Develop item from TODO list
Tom Lane wrote: Viktor Valy vili0...@gmail.com writes: We are 2 Students from the Technical University of Vienna. At our internship we would like to develop the item of the TODO list: Allow SET CONSTRAINTS to be qualified by schema/table name. Is anyone working on it? Uh, it was done years ago, AFAICS, unless the Todo entry means something non-obvious. regression=# create schema foo; CREATE SCHEMA regression=# create table foo.bar (f1 int unique deferrable); NOTICE: CREATE TABLE / UNIQUE will create implicit index bar_f1_key for table bar CREATE TABLE regression=# set constraints foo.bar_f1_key deferred; SET CONSTRAINTS regression=# set constraints foo.bar_f1_key immediate; SET CONSTRAINTS regression=# Bruce, do you remember what that entry was really about? Yep, that was it. I have remove that TODO item. Thanks. -- 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] Proposal / proof of concept: Triggers on VIEWs
On 8/4/10 2:39 PM +0300, Dean Rasheed wrote: Does this sound like a useful feature? Is this a sane approach to implementing it? If not, has anyone else given any thought as to how it might be implemented? I didn't look at the patch, but so far, I've identified three problems with the existing view system: 1) You can't re-evaluate the UPDATE expression like an UPDATE on a table does. Consider for example UPDATE foo SET a=a+1; If the tuples change before we get to them, we lose data because we simply can't re-evaluate a+1 in the trigger. 2) You can't set the number of affected rows. 3) You can't set the RETURNING results. You suggested that RETURNING for DELETE would return the OLD value, but that seems broken because that's not necessarily what was deleted. I didn't understand what you suggestion for UPDATE was; how does PG know that if the view doesn't have a primary key? I think these are the main three problems that prevent people from actually using views, and I think these should be focused on when adding triggers on VIEWS. I would love to see the feature though. Any thoughts? Regards, Marko Tiikkaja -- 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] merge command - GSoC progress
On 04/08/10 12:23, Boxuan Zhai wrote: I am just considering that there may be some logical mistakes for my rule rewriting strategy of MERGE actions. In my current design, if we find that an action type, say UPDATE, is replaced by INSTEAD rules, we will remove all the actions of this type from the MERGE command, as if they are not be specified by user from the beginning. See the test example in my pages for this situation. https://wiki.postgresql.org/wiki/MergeTestExamples#With_INSTEAD_rules Now,I am thinking that maybe we should keep the replaced actions in action list, and just mark them to be invalid. If one join tuple from the main plan fits the condition of this action, we will do nothing on it. This strategy is a little bit different with the current one. If we delete an action, the tuples that meet it condition will be caught by other actions. If we keep it, the tuples that match it will be skipped. I think the new design is more logical, and I wonder your opinion on this problem. So if I understood correctly, in the instead rule example you have at the wiki page, the stock table should contain one row, with the same balance it had before running the MERGE? Yeah, agreed, that's much more logical. -- Heikki Linnakangas 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] review: psql: edit function, show function commands patch
On Tue, Aug 3, 2010 at 7:20 AM, Pavel Stehule pavel.steh...@gmail.com wrote:
I hope so I found and fixed last issue - the longer functions was
showed directly - without a pager.
As a matter of style, I suggest leaving bool *edited as the last
argument to do_edit() and inserting int lineno as the second-to-last
argument.
! int lineno = -1;
[...]
! if (atoi(ln) 1)
! {
! psql_error(invalid line
number\n);
! status = PSQL_CMD_ERROR;
! }
! else
! lineno = atoi(ln);
Why call atoi(n) twice? Can't you just write:
lineno = atoi(n);
if (lineno 1) {
...error stuff...
}
I suggested writing psql_assert(datag != NULL) rather than just
psql_assert(datag).
Instead of: cannot use a editor navigation without setting
EDITOR_LINENUMBER_SWITCH variable
I suggest: cannot edit a specific line because the
EDITOR_LINENUMBER_SWITCH variable is not set
I don't find the name get_dg_tag() to be very mnemonic. How about
get_function_dollarquote_tag()?
In help.c, it looks like you've only added one line but incremented
the pager count by three.
In this bit:
! dqtag = get_dq_tag(lines);
! if (dqtag)
! {
! free(dqtag);
! break;
! }
! else
! lineno++;
The else is unnecessary. And just after that:
! if (end_of_line)
! lines = end_of_line + 1;
! else
! break;
You can write this more cleanly by saying if (!end_of_line) break;
lines = end_of_line + 1.
The following diff hunk (2213,2218) just adds a blank line and is
therefore unnecessary. There's a similar hunk in the docs portion of
the patch.
In this part:
func = psql_scan_slash_option(scan_state,
OT_WHOLE_LINE, NULL, true);
! lineno = extract_lineno_from_funcdesc(func, status);
!
! if (status != PSQL_CMD_ERROR)
{
! if (!func)
! {
! /* set up an empty command to fill in */
! printfPQExpBuffer(query_buf,
!
CREATE FUNCTION ( )\n
!
RETURNS \n
!
LANGUAGE \n
! --
common options: IMMUTABLE STABLE STRICT SECURITY
DEFINER\n
! AS
$function$\n
!
\n$function$\n);
! }
! else if (!lookup_function_oid(pset.db, func,
foid))
! {
! /* error already reported */
! status = PSQL_CMD_ERROR;
! }
! else if (!get_create_function_cmd(pset.db,
foid, query_buf))
! {
! /* error already reported */
! status = PSQL_CMD_ERROR;
! }
! if (func)
! free(func);
}
Why is it correct for if (func) free(func) to be inside the if (status
!= PSQL_CMD_ERROR) block? It looks to me like func gets initialized
first, before status is set.
It seems unnecessary for extract_lineno_from_funcdesc() to return the
line number and have a separate out parameter to return a
backslashResult. Can't you just return -1 to indicate an error? (You
might need to move the if (!func) test at the top to the caller, but
that seems OK.)
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] tracking inherited columns (was: patch for check constraints using multiple inheritance)
On 08/04/2010 06:41 AM, Robert Haas wrote: On Wed, Aug 4, 2010 at 6:41 AM, Yeb Havingayebhavi...@gmail.com wrote: If child inherits column A from parent1 and parent2, and it is then renamed to B in parent2, what should the name be in the child after the rename is completed? The column should be renamed to B in parent2, child and parent1. Uh, really? Wow. You want to follow the inheritance hierarchy in both directions, both down and up? That seems like it could be confusing. It seems more than confusing. It seems fundamentally wrong. It would certainly be a violation of POLA. Unless there's an extremely persuasive case made for it I'm inclined just to say no. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review of Synchronous Replication patches
On Tue, Aug 3, 2010 at 1:50 PM, Kolb, Harald (NSN - DE/Munich) harald.k...@nsn.com wrote: Or is fsync still not supported ? Wouldn't you need to have it set to apply to get the behavior you want here? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] merge command - GSoC progress
On Wednesday 04 August 2010 14:09:51 Heikki Linnakangas wrote: Yep. I believe Boxuan is using git in a simplistic way, doing just git diff to create patches. For adding new files, you need to do git add filename, but note that this adds the new file to staging area. To view all changes in the staging area, use git diff --cached, but that won't show any modifications to existing files that you haven't also git added. So to generate a patch you need to git add all modified and added files (git add -u will add all modified files automatically), and then use git diff --cached to generate the diff. Or use git add --intent--to-add (or -N). That adds the file but not the actual changes. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal / proof of concept: Triggers on VIEWs
On 4 August 2010 13:22, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: On 8/4/10 2:39 PM +0300, Dean Rasheed wrote: Does this sound like a useful feature? Is this a sane approach to implementing it? If not, has anyone else given any thought as to how it might be implemented? I didn't look at the patch, but so far, I've identified three problems with the existing view system: 1) You can't re-evaluate the UPDATE expression like an UPDATE on a table does. Consider for example UPDATE foo SET a=a+1; If the tuples change before we get to them, we lose data because we simply can't re-evaluate a+1 in the trigger. Is this the same problem the writeable CTE patch ran into? The way I've done this, the OLD values passed to the trigger all come from a snapshot established at the start of the query, so you're right, the trigger won't see values changed after the query started, unless it re-queries for them. I don't see an easy way round that. 2) You can't set the number of affected rows. Yeah, the assumption is that the number of affected rows is the number of rows in the view that matched the user's WHERE clause. You could return fewer affected rows by having the trigger return NULL for some of them, but you can't say that you've affected more than that. So even if the trigger updates 10 rows in the base tables for a given row in the view, that still only counts as 1 row affected in the view by the original query. 3) You can't set the RETURNING results. You suggested that RETURNING for DELETE would return the OLD value, but that seems broken because that's not necessarily what was deleted. Well that's what happens for a table. Alternatively the trigger could modify OLD, and then have RETURNING return that, but that's not what happens in a BEFORE DELETE trigger on a table. I didn't understand what you suggestion for UPDATE was; how does PG know that if the view doesn't have a primary key? For INSERT and UPDATE the trigger would compute and make the necessary changes to the base tables, and then return the new contents of the view's row in a modified copy of NEW, if necessary for RETURNING. This might include re-computed derived values for example. If the view doesn't have a PK, or any other way of uniquely identifying rows then its probably hopeless. That's not a case that this patch is targeted for. Regards, Dean I think these are the main three problems that prevent people from actually using views, and I think these should be focused on when adding triggers on VIEWS. I would love to see the feature though. Any thoughts? Regards, Marko Tiikkaja -- 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] documentation for committing with git
On Wed, Aug 4, 2010 at 9:29 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Hmm, if I understand correctly, Daniel talks about data loss when using alternates, if you e.g delete a branch and run git gc in the parent repository, because the child repository referring to the parent via the alternate reference can depend on objects in the parent repository that are no longer required by the parent repository itself. I guess that applies to multiple workdirs too, if you have staged but uncommitted changes in the staging area of a workdir. This message http://kerneltrap.org/mailarchive/git/2007/10/11/335637 agrees. Shawn O Pearce's last paragraph says: You might want to edit your new section so that it refers to some of the earlier material rather than duplicating it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] documentation for committing with git
On 04/08/10 13:32, Robert Haas wrote: On Sun, Aug 1, 2010 at 5:08 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I'm a bit disappointed that the wiki page advises against git-new-workdir - that's exactly what I was planning to use. It claims there's data loss issues with that, does someone know the details? Is there really a risk of data loss if multiple workdirs are used, in our situation? As I understand it, there is a risk of corruption if you ever do git gc in the respository that the get-new-workdir was spawned from. See also Daniel Farina's email, here: http://archives.postgresql.org/pgsql-hackers/2010-07/msg01489.php It's not easy for me to mentally verify that the way I work won't cause problems with this approach, but you may feel differently, and that's fine. Hmm, if I understand correctly, Daniel talks about data loss when using alternates, if you e.g delete a branch and run git gc in the parent repository, because the child repository referring to the parent via the alternate reference can depend on objects in the parent repository that are no longer required by the parent repository itself. I guess that applies to multiple workdirs too, if you have staged but uncommitted changes in the staging area of a workdir. This message http://kerneltrap.org/mailarchive/git/2007/10/11/335637 agrees. Shawn O Pearce's last paragraph says: Heh. As you can see it has some issues with its use. Its a very powerful tool, but it does give you more than enough room to shoot yourself in the foot. Using it is like tieing a gun to your ankle, keeping it aimed at your big toe at all times, with a string tied to your wrist and the gun's trigger. Reach too far and *bam*. Which is why its still in contrib status. All those issues can be avoided if you only run git gc when all the working directories are in a clean state, with no staged but uncommitted changes or other funny things. I can live with that gun tied to my ankle ;-). I've added a section describing git-new-workdir the way I'm going to use it. PS. I highly recommend always using git push --dry-run before the real thing, to make sure you're not doing anything funny. Ah, that sounds like a good idea. I added a note of that to the wiki too. -- Heikki Linnakangas 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] Synchronous replication
On 02/08/10 11:45, Fujii Masao wrote: On Sun, Aug 1, 2010 at 3:11 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I don't think any of this quorum stuff makes much sense without explicitly registering standbys in the master. I'm not sure if this is a good idea. This requires users to do more manual operations than ever when setting up the replication; assign unique name (or ID) to each standby, register them in the master, specify the names in each recovery.conf (or elsewhere), and remove the registration from the master when getting rid of the standby. But this is similar to the way of MySQL replication setup, so some people (excluding me) may be familiar with it. That would also solve the fuzziness with wal_keep_segments - if the master knew what standbys exist, it could keep track of how far each standby has received WAL, and keep just enough WAL for each standby to catch up. What if the registered standby stays down for a long time? Then you risk running out of disk space. Similar to having an archive command that fails for some reason. That's one reason the registration should not be too automatic - there is serious repercussions if the standby just disappears. If the standby is a synchronous one, the master will stop committing or delay acknowledging commits, depending on the configuration, and the master needs to keep extra WAL around. Of course, we can still support unregistered standbys, with the current semantics. -- Heikki Linnakangas 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] Proposal / proof of concept: Triggers on VIEWs
On 8/4/10 4:31 PM +0300, Dean Rasheed wrote: 1) You can't re-evaluate the UPDATE expression like an UPDATE on a table does. Consider for example UPDATE foo SET a=a+1; If the tuples change before we get to them, we lose data because we simply can't re-evaluate a+1 in the trigger. Is this the same problem the writeable CTE patch ran into? No, that was something different. Yeah, the assumption is that the number of affected rows is the number of rows in the view that matched the user's WHERE clause. You could return fewer affected rows by having the trigger return NULL for some of them, but you can't say that you've affected more than that. So even if the trigger updates 10 rows in the base tables for a given row in the view, that still only counts as 1 row affected in the view by the original query. I think that's fine. 3) You can't set the RETURNING results. You suggested that RETURNING for DELETE would return the OLD value, but that seems broken because that's not necessarily what was deleted. Well that's what happens for a table. Alternatively the trigger could modify OLD, and then have RETURNING return that, but that's not what happens in a BEFORE DELETE trigger on a table. I'm not sure I understand. RETURNING in DELETE on a table fetches the old value after it was DELETEd, so it really is what the tuple was before the DLETE, not what is seen by the snapshot. In a BEFORE DELETE trigger, the row is always locked so it can't change after the trigger is fired. For INSERT and UPDATE the trigger would compute and make the necessary changes to the base tables, and then return the new contents of the view's row in a modified copy of NEW, if necessary for RETURNING. This might include re-computed derived values for example. I see. Regards, Marko Tiikkaja -- 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] documentation for committing with git
On 08/04/2010 09:29 AM, Heikki Linnakangas wrote: All those issues can be avoided if you only run git gc when all the working directories are in a clean state, with no staged but uncommitted changes or other funny things. I can live with that gun tied to my ankle ;-). But to make sure of that I think you need to prevent git commands from running gc automatically: git config gc.auto 0 or possibly git config --global gc.auto 0 And you'll need to make sure you run gc yourself from time to time. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal / proof of concept: Triggers on VIEWs
On 4 August 2010 14:43, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: 3) You can't set the RETURNING results. You suggested that RETURNING for DELETE would return the OLD value, but that seems broken because that's not necessarily what was deleted. Well that's what happens for a table. Alternatively the trigger could modify OLD, and then have RETURNING return that, but that's not what happens in a BEFORE DELETE trigger on a table. I'm not sure I understand. RETURNING in DELETE on a table fetches the old value after it was DELETEd, so it really is what the tuple was before the DLETE, not what is seen by the snapshot. In a BEFORE DELETE trigger, the row is always locked so it can't change after the trigger is fired. Ah, I think I mis-understood. If I understand what you're saying correctly, you're worried that the row might have been modified in the same query, prior to being deleted, and you want RETURNING to return the updated value, as it was when it was deleted. So yes, you're right, that really is different from a table. I guess it would have to be handled by the trigger returning a modified copy of OLD for RETURNING to use. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal / proof of concept: Triggers on VIEWs
On 8/4/10 5:03 PM +0300, Dean Rasheed wrote: On 4 August 2010 14:43, Marko Tiikkajamarko.tiikk...@cs.helsinki.fi wrote: I'm not sure I understand. RETURNING in DELETE on a table fetches the old value after it was DELETEd, so it really is what the tuple was before the DLETE, not what is seen by the snapshot. In a BEFORE DELETE trigger, the row is always locked so it can't change after the trigger is fired. Ah, I think I mis-understood. If I understand what you're saying correctly, you're worried that the row might have been modified in the same query, prior to being deleted, and you want RETURNING to return the updated value, as it was when it was deleted. I'm mainly concerned about concurrently running transactions. Regards, Marko Tiikkaja -- 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: edit function, show function commands patch
On Mon, Aug 02, 2010 at 11:34:02PM -0400, Robert Haas wrote: On Mon, Aug 2, 2010 at 11:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Aug 2, 2010 at 10:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, it'd still work fine for \e foo. It'll just blow up for \e foo 3. My concern isn't really that things that which work now will break so much as that this new feature will fail to work for a large percentage of the people who try to use it, including virtually everyone who tries to use it on Win32. That concern is a show-stopper. While this is superficially a Nice Thing to Have and I would certainly support it if +linenumber were relatively universal, it's really a pretty minor convenience when you come right down to it, and I am not at all convinced it is worth the hassle of trying to divine what piece of syntax will equip the user's choice of editor with the necessary amount of clue. The other approach we could take is that this whole thing is disabled by default, and you have to set a psql variable EDITOR_LINENUMBER_SWITCH to turn it on. If you haven't read the documentation enough to find out that variable exists, well, no harm no foul. That might be reasonable. Right now the default behavior is to do +line on Linux and /line on Windows. But maybe a more sensible default would be to fail with an error message that says you have to set thus-and-so variable if you want to use this feature. That seems better than sometimes working and sometimes failing depending on what editor the user has configured. A side question is whether this should be an environment variable or a psql variable. I'd say yes. As with $EDITOR/PSQL_EDITOR, there should be something that looks for an overriding psql variable, drops through to look for an environment variable, and then to a sane default, for some reasonable value of sane. Perhaps this default could depend on OS (Windows vs. Everything Else) to start with. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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: edit function, show function commands patch
On Wed, Aug 4, 2010 at 10:10 AM, David Fetter da...@fetter.org wrote: On Mon, Aug 02, 2010 at 11:34:02PM -0400, Robert Haas wrote: On Mon, Aug 2, 2010 at 11:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Aug 2, 2010 at 10:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, it'd still work fine for \e foo. It'll just blow up for \e foo 3. My concern isn't really that things that which work now will break so much as that this new feature will fail to work for a large percentage of the people who try to use it, including virtually everyone who tries to use it on Win32. That concern is a show-stopper. See downthread; I believe we have a resolution to this issue. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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: edit function, show function commands patch
Hello
updated patch attached
2010/8/4 Robert Haas robertmh...@gmail.com:
On Tue, Aug 3, 2010 at 7:20 AM, Pavel Stehule pavel.steh...@gmail.com wrote:
I hope so I found and fixed last issue - the longer functions was
showed directly - without a pager.
As a matter of style, I suggest leaving bool *edited as the last
argument to do_edit() and inserting int lineno as the second-to-last
argument.
! int lineno = -1;
[...]
! if (atoi(ln) 1)
! {
! psql_error(invalid line
number\n);
! status = PSQL_CMD_ERROR;
! }
! else
! lineno = atoi(ln);
Why call atoi(n) twice? Can't you just write:
lineno = atoi(n);
if (lineno 1) {
...error stuff...
}
fixed
I suggested writing psql_assert(datag != NULL) rather than just
psql_assert(datag).
Instead of: cannot use a editor navigation without setting
EDITOR_LINENUMBER_SWITCH variable
I suggest: cannot edit a specific line because the
EDITOR_LINENUMBER_SWITCH variable is not set
fixed
I don't find the name get_dg_tag() to be very mnemonic. How about
get_function_dollarquote_tag()?
I used get_functiondef_dollarquote_tag
In help.c, it looks like you've only added one line but incremented
the pager count by three.
The original value for pager is probably wrong (not actual). I
rechecked real row numbers in external editor.
In this bit:
! dqtag = get_dq_tag(lines);
! if (dqtag)
! {
! free(dqtag);
! break;
! }
! else
! lineno++;
The else is unnecessary. And just after that:
! if (end_of_line)
! lines = end_of_line + 1;
! else
! break;
You can write this more cleanly by saying if (!end_of_line) break;
lines = end_of_line + 1.
fixed
The following diff hunk (2213,2218) just adds a blank line and is
therefore unnecessary. There's a similar hunk in the docs portion of
the patch.
fixed
In this part:
func = psql_scan_slash_option(scan_state,
OT_WHOLE_LINE, NULL, true);
! lineno = extract_lineno_from_funcdesc(func, status);
!
! if (status != PSQL_CMD_ERROR)
{
! if (!func)
! {
! /* set up an empty command to fill in
*/
! printfPQExpBuffer(query_buf,
!
CREATE FUNCTION ( )\n
!
RETURNS \n
!
LANGUAGE \n
!
-- common options: IMMUTABLE STABLE STRICT SECURITY
DEFINER\n
! AS
$function$\n
!
\n$function$\n);
! }
! else if (!lookup_function_oid(pset.db, func,
foid))
! {
! /* error already reported */
! status = PSQL_CMD_ERROR;
! }
! else if (!get_create_function_cmd(pset.db,
foid, query_buf))
! {
! /* error already reported */
! status = PSQL_CMD_ERROR;
! }
! if (func)
! free(func);
}
Why is it correct for if (func) free(func) to be inside the if (status
!= PSQL_CMD_ERROR) block? It looks to me like func gets initialized
first, before status is set.
fixed
It seems unnecessary for extract_lineno_from_funcdesc() to return the
line number and have a separate out parameter to return a
backslashResult. Can't you just return -1 to indicate an error? (You
might need to move the if (!func) test at the top to the caller, but
that seems OK.)
I can't to do it. There are three states
1)
Re: [HACKERS] merge command - GSoC progress
On Wed, 2010-08-04 at 17:23 +0800, Boxuan Zhai wrote: Dear Robert, I am just considering that there may be some logical mistakes for my rule rewriting strategy of MERGE actions. In my current design, if we find that an action type, say UPDATE, is replaced by INSTEAD rules, we will remove all the actions of this type from the MERGE command, as if they are not be specified by user from the beginning. See the test example in my pages for this situation. https://wiki.postgresql.org/wiki/MergeTestExamples#With_INSTEAD_rules It seems sensible to use the test files that I wrote for MERGE in 2008, published to -hackers at that time. The tests were a complete output from a MERGE test script. Developing new tests when we already have code makes little sense, plus its a good way of objectively testing that the spec has been implemented correctly in these patches. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and 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] review: psql: edit function, show function commands patch
David Fetter da...@fetter.org writes: On Mon, Aug 02, 2010 at 11:34:02PM -0400, Robert Haas wrote: A side question is whether this should be an environment variable or a psql variable. I'd say yes. As with $EDITOR/PSQL_EDITOR, there should be something that looks for an overriding psql variable, drops through to look for an environment variable, and then to a sane default, for some reasonable value of sane. Perhaps this default could depend on OS (Windows vs. Everything Else) to start with. Well, the thing about $EDITOR is that it's a very-widely-understood convention. This one won't be, so the argument for making it an environment variable seems pretty thin. It'd be saner to set it in your ~/.psqlrc file than to add another few nanoseconds to every process launch you ever do. 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] Where in the world is Itagaki Takahiro?
2010/8/3 Josh Berkus j...@agliodbs.com: On 8/2/10 3:42 PM, Itagaki Takahiro wrote: Sorry for delayed reply. I moved to a new job, and was very busy for it. Congratulations! Are you still at NTT Open Source? No. Now I'm working at Forcia, Inc. (http://www.forcia.com/), where Postgres' extensibility is used very much to develop innovative applications. I can continue to develop Postgres thanks to the company's support! -- Itagaki Takahiro -- 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] reducing NUMERIC size for 9.1
Robert Haas robertmh...@gmail.com writes: On Fri, Jul 30, 2010 at 9:55 PM, Robert Haas robertmh...@gmail.com wrote: The smallest value for precision which requires 2 numeric_digits is always 2; and the required number of numeric_digits increases by 1 each time the number of base-10 digits increases by DEC_DIGITS. And here is a patch implementing that. Looks good to me. One thought --- to make this look more like the typmod-whacking in the numeric() function, perhaps the first line of numeric_maximum_size ought to be if (typemod = (int32) (VARHDRSZ)) return -1; I think the author of numeric() was concerned that VARHDRSZ might be unsigned, in which case the cast-less comparison would do the Wrong Thing. Reference to c.h shows that it's signed, so no bug, but having the cast in the comparison seems like good conservative programming. Or, if you prefer, you can take out the cast in numeric(). I merely opine that the two bits of code should match. 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] tracking inherited columns (was: patch for check constraints using multiple inheritance)
Andrew Dunstan and...@dunslane.net writes: On 08/04/2010 06:41 AM, Robert Haas wrote: Uh, really? Wow. You want to follow the inheritance hierarchy in both directions, both down and up? That seems like it could be confusing. It seems more than confusing. It seems fundamentally wrong. It would certainly be a violation of POLA. I agree, this idea seems completely nuts. It is *not* reasonable for an action applied to a child to change the definition of the parent. 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] merge command - GSoC progress
On Wed, 2010-08-04 at 15:36 +0100, Simon Riggs wrote: On Wed, 2010-08-04 at 17:23 +0800, Boxuan Zhai wrote: Dear Robert, I am just considering that there may be some logical mistakes for my rule rewriting strategy of MERGE actions. In my current design, if we find that an action type, say UPDATE, is replaced by INSTEAD rules, we will remove all the actions of this type from the MERGE command, as if they are not be specified by user from the beginning. See the test example in my pages for this situation. https://wiki.postgresql.org/wiki/MergeTestExamples#With_INSTEAD_rules It seems sensible to use the test files that I wrote for MERGE in 2008, published to -hackers at that time. Even more sensible for me to include it as a patch, with the files in the right places and the schedules updated. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out new file mode 100644 index 000..18e3891 --- /dev/null +++ b/src/test/regress/expected/merge.out @@ -0,0 +1,279 @@ +-- +-- MERGE +-- +CREATE TABLE target (id integer, balance integer); +CREATE TABLE source (id integer, balance integer); +INSERT INTO target VALUES (1, 10); +INSERT INTO target VALUES (2, 20); +INSERT INTO target VALUES (3, 30); +SELECT * FROM target; + id | balance ++- + 1 | 10 + 2 | 20 + 3 | 30 +(3 rows) + +-- +-- initial tests +-- +-- empty source means 0 rows touched +MERGE into target t +USING (select * from source) AS s +ON t.id = s.id +WHEN MATCHED THEN + UPDATE SET balance = t.balance + s.balance +; +-- insert some source rows to work from +INSERT INTO source VALUES (2, 5); +INSERT INTO source VALUES (3, 20); +INSERT INTO source VALUES (4, 40); +SELECT * FROM source; + id | balance ++- + 2 | 5 + 3 | 20 + 4 | 40 +(3 rows) + +-- do a simple equivalent of an UPDATE join +BEGIN; +MERGE into target t +USING (select * from source) AS s +ON t.id = s.id +WHEN MATCHED THEN + UPDATE SET balance = t.balance + s.balance +; +SELECT * FROM target; + id | balance ++- + 1 | 10 + 2 | 25 + 3 | 50 +(3 rows) + +ROLLBACK; +-- do a simple equivalent of an INSERT SELECT +BEGIN; +MERGE into target t +USING (select * from source) AS s +ON t.id = s.id +WHEN NOT MATCHED THEN + INSERT VALUES (s.id, s.balance) +; +SELECT * FROM target; + id | balance ++- + 1 | 10 + 2 | 20 + 3 | 30 + 4 | 40 +(4 rows) + +ROLLBACK; +-- now the classic UPSERT +BEGIN; +MERGE into target t +USING (select * from source) AS s +ON t.id = s.id +WHEN MATCHED THEN + UPDATE SET balance = t.balance + s.balance +WHEN NOT MATCHED THEN + INSERT VALUES (s.id, s.balance) +; +SELECT * FROM target; + id | balance ++- + 1 | 10 + 2 | 25 + 3 | 50 + 4 | 40 +(4 rows) + +ROLLBACK; +-- +-- Non-standard functionality +-- +-- do a simple equivalent of a DELETE join +BEGIN; +MERGE into target t +USING (select * from source) AS s +ON t.id = s.id +WHEN MATCHED THEN + DELETE +; +SELECT * FROM target; + id | balance ++- + 1 | 10 +(1 row) + +ROLLBACK; +-- now the classic UPSERT, with a DELETE +-- the Standard doesn't allow the DELETE clause for some reason, +-- though other implementations do +BEGIN; +MERGE into target t +USING (select * from source) AS s +ON t.id = s.id +WHEN MATCHED AND s.balance 10 THEN + UPDATE SET balance = t.balance + s.balance +WHEN MATCHED THEN + DELETE +WHEN NOT MATCHED THEN + INSERT VALUES (s.id, s.balance) +; +SELECT * FROM target; + id | balance ++- + 1 | 10 + 3 | 50 + 4 | 40 +(3 rows) + +ROLLBACK; +-- Prepare the test data to generate multiple matching rows for a single target +INSERT INTO source VALUES (3, 5); +SELECT * FROM source ORDER BY id, balance; + id | balance ++- + 2 | 5 + 3 | 5 + 3 | 20 + 4 | 40 +(4 rows) + +-- we now have a duplicate key in source, so when we join to +-- target we will generate 2 matching rows, not one +-- In the following statement row id=3 will be both updated +-- and deleted by this statement and so will cause a run-time error +-- when the second change to that row is detected +-- This next SQL statement +-- fails according to standard +-- fails in PostgreSQL implementation +BEGIN; +MERGE into target t +USING (select * from source) AS s +ON t.id = s.id +WHEN MATCHED AND s.balance 10 THEN + UPDATE SET balance = t.balance + s.balance +WHEN MATCHED THEN + DELETE +WHEN NOT MATCHED THEN + INSERT VALUES (s.id, s.balance) +; +ERROR: multiple actions on single target row + +ROLLBACK; + +-- This next SQL statement +-- fails according to standard +-- suceeds in PostgreSQL implementation by simply ignoring the second +-- matching row since it activates no WHEN clause +BEGIN; +MERGE into target t +USING (select * from source) AS s +ON t.id =
Re: [HACKERS] GROUPING SETS revisited
On Wed, Aug 04, 2010 at 04:44:05AM +0200, Pavel Stehule wrote: Yeah, I seem to have done a poor job of producing the patch based on the repository I was working from. That said, it seems Pavel's working actively on a patch anyway, so perhaps my updating the old one isn't all that worthwhile. Pavel, is your code somewhere that we can get to it? not now. please wait a week. That works for me. I'm glad to try doing a better job of putting together my version of the patch, if anyone thinks it's useful, but it seems that since Pavel's code is due to appear sometime in the foreseeable future, there's not much point in my doing that. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] reducing NUMERIC size for 9.1
On Wed, Aug 4, 2010 at 11:05 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Jul 30, 2010 at 9:55 PM, Robert Haas robertmh...@gmail.com wrote: The smallest value for precision which requires 2 numeric_digits is always 2; and the required number of numeric_digits increases by 1 each time the number of base-10 digits increases by DEC_DIGITS. And here is a patch implementing that. Looks good to me. One thought --- to make this look more like the typmod-whacking in the numeric() function, perhaps the first line of numeric_maximum_size ought to be if (typemod = (int32) (VARHDRSZ)) return -1; I think the author of numeric() was concerned that VARHDRSZ might be unsigned, in which case the cast-less comparison would do the Wrong Thing. Reference to c.h shows that it's signed, so no bug, but having the cast in the comparison seems like good conservative programming. Or, if you prefer, you can take out the cast in numeric(). I merely opine that the two bits of code should match. *scratches head* One of those tests uses and the other uses = Which one is wrong? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] reducing NUMERIC size for 9.1
Robert Haas robertmh...@gmail.com writes: *scratches head* One of those tests uses and the other uses = Which one is wrong? Well, neither, since NUMERIC(0,0) is disallowed. However, it's probably not the place of these functions to assume that, so I'd suggest treating equality as valid. 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
[HACKERS] patch for contrib/isn
hi all, currently i am working on a big project for a german bookseller and publisher. one of the requirements was correct hyphenation of ISBN-13 for about 14.400.000 books in postgresql database. so added support for hyphenating isbn with the new 979-prefix and additionally added all missing ranges since year 2006 for isbn with 978-prefix. patch against HEAD is attached and validated against a lot of previously wrong and correct hyphenated isbn. regards, jan contrib_isn-1.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] reducing NUMERIC size for 9.1
On Wed, Aug 4, 2010 at 12:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: *scratches head* One of those tests uses and the other uses = Which one is wrong? Well, neither, since NUMERIC(0,0) is disallowed. However, it's probably not the place of these functions to assume that, so I'd suggest treating equality as valid. OK, done. I renamed the argument from typemod to typmod so the tests would all match byte-for-byte. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] patch for contrib/isn
On Wed, 2010-08-04 at 19:32 +0200, Jan Otto wrote: hi all, currently i am working on a big project for a german bookseller and publisher. one of the requirements was correct hyphenation of ISBN-13 for about 14.400.000 books in postgresql database. so added support for hyphenating isbn with the new 979-prefix and additionally added all missing ranges since year 2006 for isbn with 978-prefix. patch against HEAD is attached and validated against a lot of previously wrong and correct hyphenated isbn. regards, jan Great! Thanks. We will get it on the review list. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] tracking inherited columns (was: patch for check constraints using multiple inheritance)
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 08/04/2010 06:41 AM, Robert Haas wrote: Uh, really? Wow. You want to follow the inheritance hierarchy in both directions, both down and up? That seems like it could be confusing. It seems more than confusing. It seems fundamentally wrong. It would certainly be a violation of POLA. I agree, this idea seems completely nuts. It is *not* reasonable for an action applied to a child to change the definition of the parent. Also not in the case that we're talking about here? A.a_columnB.a_column | / v v C.a_column C inherits from A and B. The user wants to change a_column to better_name. ALTER TABLE A RENAME COLUMN a_column TO better_name; ERROR: could not rename column because an inherited child inherits the same column from other inheritance parents HINT: use CASCADE to rename the column in the other parents and their childs as well ALTER TABLE A RENAME COLUMN a_column TO better_name CASCADE; (succeeds) This doesn't seem nuts to me. After all, the set of columns with name 'a_column' is like a domain, in the sense that all names and types of all three columns are the same. If the user wants to rename a_column, with the current code he gets an inconsistent database. There is a patch that prevents renaming in this case, and then the user could work around it by adding an artificial relation from which A and B inherit, rename a_column there and then remove that relation again. IMHO to allow the rename if the user explicitly asks for it is more user friendly, with no compromises at all. Since the upward inheritance relation scanning is only used to gather the set of a_columns to be updated in the cascade case, I do not see why this is nuts, nor why it should violate any definition of inheritance. After all: all conditions regarding inheritance I can think of are valid *after* the DDL update. regards, Yeb Havinga -- 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] Rewrite, normal execution vs. EXPLAIN ANALYZE
Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: According to the latter commit, not updating the snapshot could be preferable for EXPLAIN ANALYZE, but I don't see why this is. Maybe we should wait until we hear from Tom? Sorry for not catching up on my email sooner. On the whole I'm in agreement with the argument that wCTEs should *not* take whole new snapshots between queries, but only advance the CID. The points that sway me that way are: 1. To do otherwise will decrease the predictability of wCTE results. 2. The argument for taking a new snapshot seems to be mostly because that's what rules do; but it's agreed that rules have a lot of surprising and undesirable behavior, and it's not clear that this isn't part of that. 3. In particular I don't agree with the argument that functions taking new snapshots between queries should be a precedent for this. In a function, the user thinks of the successive lines as separate queries, and it's reasonable for them to act similarly to what would happen if they were issued as separate top-level commands. I do not see that that argument applies to wCTEs, which by definition are parts of a single command. In any case the behavior is going to have to be documented, but I vote for advance-CID-only so far as wCTEs are concerned. The other thing that was being argued was whether rules should be changed to act that way too, and if not whether EXPLAIN ANALYZE should be fixed to make sure it emulates rule execution better. Personally I'd be in favor of changing rule execution and leaving EXPLAIN ANALYZE alone, though I'm not sure if that position can command a consensus. I seriously doubt that there are many applications out there that are actually depending on this aspect of rule execution; if anything, there are probably more that will see it as a bug. 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] tracking inherited columns (was: patch for check constraints using multiple inheritance)
Yeb Havinga yebhavi...@gmail.com writes: Tom Lane wrote: I agree, this idea seems completely nuts. It is *not* reasonable for an action applied to a child to change the definition of the parent. Also not in the case that we're talking about here? A.a_columnB.a_column | / v v C.a_column C inherits from A and B. The user wants to change a_column to better_name. Well, if A and B inherited the column from a common ancestor, he can easily do that. If not, maybe he should have thought harder before he started. I do NOT agree that issuing a rename against C is a sane way of dealing with this. This doesn't seem nuts to me. You're in the minority. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch for contrib/isn
Joshua D. Drake j...@commandprompt.com wrote: On Wed, 2010-08-04 at 19:32 +0200, Jan Otto wrote: patch against HEAD is attached and validated against a lot of previously wrong and correct hyphenated isbn. Great! Thanks. We will get it on the review list. I added it as isbn update to the 2010-09 CommitFest page. -Kevin -- 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] Where in the world is Itagaki Takahiro?
No. Now I'm working at Forcia, Inc. (http://www.forcia.com/), where Postgres' extensibility is used very much to develop innovative applications. I can continue to develop Postgres thanks to the company's support! Cool! My condolences to Koichi-san, though. I know he'll miss having you there. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Alex Hunsaker bada...@gmail.com writes: On Wed, Aug 4, 2010 at 11:04, Tom Lane t...@sss.pgh.pa.us wrote: If we were a bit earlier in the 9.0 cycle I would suggest that this confusion is a sufficient reason to drop the one-argument form of string_agg. It's too late now though. FWIW I think we can still change it. Isn't this type of issue part of what beta is for? If we were in RC that would be a different story :) Well, it'd take an initdb to get rid of it. In the past we've avoided forcing initdb post-beta1 unless it was Really Necessary. OTOH, we seem to be in the mode of encouraging beta testers to test pg_upgrade, so maybe that concern isn't worth much at the moment. I am right, am I not, in thinking that we invented string_agg out of whole cloth? I don't see it in SQL:2008. If there is a compatibility- with-other-products reason to support the one-argument form, that would be a consideration here. I don't see a whole lot of functionality gain from having the one-argument form, though. BTW, as far as I can tell from checking in the system catalogs, there are no other built-in aggregates that come in differing-numbers-of-arguments variants. So string_agg is the only one presenting this hazard. 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
[HACKERS] Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 13:11, Tom Lane t...@sss.pgh.pa.us wrote: Alex Hunsaker bada...@gmail.com writes: On Wed, Aug 4, 2010 at 11:04, Tom Lane t...@sss.pgh.pa.us wrote: If we were a bit earlier in the 9.0 cycle I would suggest that this confusion is a sufficient reason to drop the one-argument form of string_agg. It's too late now though. FWIW I think we can still change it. Isn't this type of issue part of what beta is for? If we were in RC that would be a different story :) Well, it'd take an initdb to get rid of it. I think forcing an initdb might be more trouble than this wart is worth. In the past we've avoided forcing initdb post-beta1 unless it was Really Necessary. OTOH, we seem to be in the mode of encouraging beta testers to test pg_upgrade, so maybe that concern isn't worth much at the moment. I have one or two 9.0-beta databases, a forced initdb would defiantly motivate me to try pg_upgrade :). To me, the question is are we planning on releasing a new beta anyway? Maybe its worth it then. If we were planning on going RC after this last beta (and I dont think we were?), I agree with Kevin, its not something worth pushing the release 9.0 for. By that I mean I assume if we force an initdb that we would want to do another beta regardless. Either way, I don't have strong feelings on this other than if we dont fix it now when will we? Maybe we will get lucky and someone will find an issue that we have to initdb for anyways :). -- 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] Rewrite, normal execution vs. EXPLAIN ANALYZE
On Wed, Aug 4, 2010 at 2:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: The other thing that was being argued was whether rules should be changed to act that way too, and if not whether EXPLAIN ANALYZE should be fixed to make sure it emulates rule execution better. Personally I'd be in favor of changing rule execution and leaving EXPLAIN ANALYZE alone, though I'm not sure if that position can command a consensus. I seriously doubt that there are many applications out there that are actually depending on this aspect of rule execution; if anything, there are probably more that will see it as a bug. Changing EXPLAIN ANALYZE seems a bit less likely to break things for anyone depending on current behavior; but, on the other hand, it seems there's a broad consensus that the best thing to do about rule execution is deprecate it, so maybe it doesn't really matter. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 3:25 PM, Alex Hunsaker bada...@gmail.com wrote: I think forcing an initdb might be more trouble than this wart is worth. +1. I would not make this change unless we have to force an initdb anyway. And I really hope we don't, because I'm sort of hoping the next 9.0 release will be rc1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Alex Hunsaker bada...@gmail.com writes: Either way, I don't have strong feelings on this other than if we dont fix it now when will we? Well, we won't. If 9.0 ships with both forms of string_agg, we're stuck with it IMO. It's not exactly a bug, so I won't cry if that's how things go; but it is striking that already two different people have gotten confused enough to file bug reports because of this. If we don't pull the one-argument form then I think we can look forward to many more of those in future years. 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
[HACKERS] Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On 4 August 2010 20:25, Alex Hunsaker bada...@gmail.com wrote: On Wed, Aug 4, 2010 at 13:11, Tom Lane t...@sss.pgh.pa.us wrote: Alex Hunsaker bada...@gmail.com writes: On Wed, Aug 4, 2010 at 11:04, Tom Lane t...@sss.pgh.pa.us wrote: If we were a bit earlier in the 9.0 cycle I would suggest that this confusion is a sufficient reason to drop the one-argument form of string_agg. It's too late now though. FWIW I think we can still change it. Isn't this type of issue part of what beta is for? If we were in RC that would be a different story :) Well, it'd take an initdb to get rid of it. I think forcing an initdb might be more trouble than this wart is worth. In the past we've avoided forcing initdb post-beta1 unless it was Really Necessary. OTOH, we seem to be in the mode of encouraging beta testers to test pg_upgrade, so maybe that concern isn't worth much at the moment. I have one or two 9.0-beta databases, a forced initdb would defiantly motivate me to try pg_upgrade :). To me, the question is are we planning on releasing a new beta anyway? Maybe its worth it then. If we were planning on going RC after this last beta (and I dont think we were?), I agree with Kevin, its not something worth pushing the release 9.0 for. By that I mean I assume if we force an initdb that we would want to do another beta regardless. Either way, I don't have strong feelings on this other than if we dont fix it now when will we? Maybe we will get lucky and someone will find an issue that we have to initdb for anyways :). I think it should be left exactly how it is. It only needed clarification in the documentation to explain its usage for the scenario in question, and probably a couple entries in the regression tests as they're lacking at the moment. I wish I had held back on mentioning it as I remembered later that this has already been discussed to a degree, and I'd probably have kept my mouth shut upon recalling it. -- Thom Brown Registered Linux user: #516935 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] more numeric stuff
I have a couple ideas for further work on the numeric code that I want to get feedback on. 1. Cramming it down some more. I propose that we introduce a third format with a one-byte header: 1 bit for sign, 3 bits for dynamic scale, and 4 bits for weight (the first of which is a sign bit). This might seem crazy, but it's still enough to represent values with a weight of between +7 and -8, but it's still enough to represent a number with up to 32 digits before the decimal point and up to 7 decimal places, which covers a lot of ground. And if you've got a billion rows on disk with several numeric values in each row, saving a byte per value starts to be significant. We don't need any special marker to indicate that the 1-byte format is in use, because we can deduce it from the length of the varlena (after excluding the header): even = 2b or 4b header, odd = 1b header. There can't be any odd-length numerics already on disk, so there shouldn't be any compatibility break for pg_upgrade to worry about. 2. Don't untoast/don't copy. Right now, given a numeric stored as a short varlena (the normal case if it's coming from on disk), we untoast it before doing anything, and then we copy the digits into a separate palloc'd digit buffer. Copying the data twice is clearly a waste. It seems that very few of the var-manipulation functions in numeric.c actually scribble on their input (exceptions I've found so far: round_var, trunc_var, strip_var). So, when translating a Numeric into a NumericVar (set_var_from_num), we could potentially skip allocating the digit buffer if the digit string in the Numeric is already allocated, and teach the few functions that need to scribble on their input to force the buffer to be allocated if it hasn't been yet. I'm not too sure whether this is the trouble; a quick test this morning suggested that such a patch would not be too difficult to write, but on the other hand the performance gain was pretty small. Another, not necessarily mutually exclusive option would be to try to operate directly on the packed format. That looks like it would require some fairly major surgery; I'm not sure what we would do with the many copies of this code: Numeric num = PG_GETARG_NUMERIC(0); 3. 64-bit arithmetic. Right now, mul_var() and div_var() use int for arithmetic, but haven't we given up on supporting platforms without long long? I'm not sure I'm motivated enough to write the patch myself, but it seems like 64-bit arithmetic would give us a lot more room to postpone carries. OK, time to duck. Thoughts? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] patch for contrib/isn
On Wed, Aug 4, 2010 at 2:58 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Joshua D. Drake j...@commandprompt.com wrote: On Wed, 2010-08-04 at 19:32 +0200, Jan Otto wrote: patch against HEAD is attached and validated against a lot of previously wrong and correct hyphenated isbn. Great! Thanks. We will get it on the review list. I added it as isbn update to the 2010-09 CommitFest page. Please put contrib/isn in the name somewhere so that there is some overlap between the subject line and the CF entry. I can't tell you how many times I've gone oh, crud, what was the subject line for the latest version of the \ef / \sf patch? in the last two weeks. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Robert Haas robertmh...@gmail.com writes: On Wed, Aug 4, 2010 at 3:25 PM, Alex Hunsaker bada...@gmail.com wrote: I think forcing an initdb might be more trouble than this wart is worth. +1. I would not make this change unless we have to force an initdb anyway. And I really hope we don't, because I'm sort of hoping the next 9.0 release will be rc1. Hm? I don't think that an initdb here would have any impact on whether we can call the next drop RC1 or not. We're talking about removing a single built-in entry in pg_proc --- it's one of the safest changes we could possibly make. The only argument I can see against it is not wanting to force beta testers through an initdb. But it seems like that might actually be a positive thing not a negative one, in this cycle, because we're trying to get test coverage on pg_upgrade. 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Well, it'd take an initdb to get rid of it. In the past we've avoided forcing initdb post-beta1 unless it was Really Necessary. OTOH, we seem to be in the mode of encouraging beta testers to test pg_upgrade, so maybe that concern isn't worth much at the moment. If it's causing bugs, drop it now. If we include it in 9.0, we're stuck with it for years. I'm OK with forcing an initDB for RC1. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Josh Berkus j...@agliodbs.com writes: If it's causing bugs, drop it now. If we include it in 9.0, we're stuck with it for years. Well, it's causing bug reports, which is not exactly the same thing as bugs. But yeah, I'm thinking we should get rid of it. 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
04.Ağu.2010 tarihinde 22:44 saatinde, Josh Berkus j...@agliodbs.com şunları yazdı: I'm OK with forcing an initDB for RC1. I think beta5 will be a better choice than RC 1 here. -- Devrim GÜNDÜZ PostgreSQL DBA @ Akinon/Markafoni, Red Hat Certified Engineer devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz -- 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 contrib/isn
Robert Haas robertmh...@gmail.com wrote: Please put contrib/isn in the name somewhere so that there is some overlap between the subject line and the CF entry. It is now contrib/isn isbn update. -Kevin -- 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] Rewrite, normal execution vs. EXPLAIN ANALYZE
On 8/4/2010 10:26 PM, Robert Haas wrote: On Wed, Aug 4, 2010 at 2:45 PM, Tom Lanet...@sss.pgh.pa.us wrote: The other thing that was being argued was whether rules should be changed to act that way too, and if not whether EXPLAIN ANALYZE should be fixed to make sure it emulates rule execution better. Personally I'd be in favor of changing rule execution and leaving EXPLAIN ANALYZE alone, though I'm not sure if that position can command a consensus. I seriously doubt that there are many applications out there that are actually depending on this aspect of rule execution; if anything, there are probably more that will see it as a bug. Changing EXPLAIN ANALYZE seems a bit less likely to break things for anyone depending on current behavior; but, on the other hand, it seems there's a broad consensus that the best thing to do about rule execution is deprecate it, so maybe it doesn't really matter. I'm having a hard time imagining that anyone would depend on a behaviour like this. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 13:42, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Aug 4, 2010 at 3:25 PM, Alex Hunsaker bada...@gmail.com wrote: I think forcing an initdb might be more trouble than this wart is worth. +1. I would not make this change unless we have to force an initdb anyway. And I really hope we don't, because I'm sort of hoping the next 9.0 release will be rc1. Hm? I don't think that an initdb here would have any impact on whether we can call the next drop RC1 or not. We're talking about removing a single built-in entry in pg_proc --- it's one of the safest changes we could possibly make. Great, I was afraid people would want another beta if we forced an initdb. So a hearty +1 for fixing it and not doing another beta (pending other bugs obviously). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Great, I was afraid people would want another beta if we forced an initdb. So a hearty +1 for fixing it and not doing another beta (pending other bugs obviously). And, btw, there has been a lot of testing of pg_upgrade due to the initdbs and otherwise. I think 9.0 is going to have a pretty darned solid pg_upgrade because of it. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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] Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On 4 August 2010 20:58, Josh Berkus j...@agliodbs.com wrote: Great, I was afraid people would want another beta if we forced an initdb. So a hearty +1 for fixing it and not doing another beta (pending other bugs obviously). And, btw, there has been a lot of testing of pg_upgrade due to the initdbs and otherwise. I think 9.0 is going to have a pretty darned solid pg_upgrade because of it. Leave my name off the commit comment then ;) People who have been waiting for this will burn me as a heretical witch... er.. man witch... warlock? -- Thom Brown Registered Linux user: #516935 -- 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] more numeric stuff
Robert Haas robertmh...@gmail.com writes: I have a couple ideas for further work on the numeric code that I want to get feedback on. 1. Cramming it down some more. I propose that we introduce a third format with a one-byte header: 1 bit for sign, 3 bits for dynamic scale, and 4 bits for weight (the first of which is a sign bit). This might seem crazy, Yes, it does. In the first place it isn't going to work conveniently because NumericDigit requires int16 alignment. In the second, shaving just one byte doesn't seem like enough win to be worth the trouble. I don't believe your billion rows argument because you aren't factoring in the result of row-level alignment padding --- most of the time you're not going to win anything. We don't need any special marker to indicate that the 1-byte format is in use, because we can deduce it from the length of the varlena (after excluding the header): even = 2b or 4b header, odd = 1b header. There can't be any odd-length numerics already on disk, so there shouldn't be any compatibility break for pg_upgrade to worry about. Really? Not sure this is true, because numerics can be toast-compressed. It hardly ever happens, but to do this that's not good enough. 2. Don't untoast/don't copy. This would be good, but I'm not sure how to do it. The main problem again is NumericDigit alignment. Only about half the time is the digit array going to be aligned the way you need, so that puts a real crimp in the possible win. (In fact, if we assume the previous field is more than byte aligned and the toast header is one byte, then the digit array is *never* properly aligned on disk :-() One possibility is to have an additional toasting rule that forces odd-byte-alignment of a field's one-byte header. But it's a bit hard to argue that numeric deserves the additional overhead that that would put into all the core tuple forming/deforming logic. 3. 64-bit arithmetic. Right now, mul_var() and div_var() use int for arithmetic, but haven't we given up on supporting platforms without long long? I'm not sure I'm motivated enough to write the patch myself, but it seems like 64-bit arithmetic would give us a lot more room to postpone carries. I don't think this would win unless we went to 32-bit NumericDigit, which is a problem from the on-disk-compatibility standpoint, not to mention making the alignment issues even worse. Postponing carries is good, but we have enough headroom for that already --- I really doubt that making the array elements wider would save anything noticeable unless you increase NBASE. 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] Rewrite, normal execution vs. EXPLAIN ANALYZE
Robert Haas robertmh...@gmail.com writes: On Wed, Aug 4, 2010 at 2:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: I seriously doubt that there are many applications out there that are actually depending on this aspect of rule execution; if anything, there are probably more that will see it as a bug. Changing EXPLAIN ANALYZE seems a bit less likely to break things for anyone depending on current behavior; Well, the point I was trying to make is that there may well be fewer people depending on the current behavior than there are people for whom the current behavior is wrong, only they don't know it because they've not seen a failure (or not seen one often enough to diagnose what's happening). This is of course merest speculation either way. But I don't feel that we need to necessarily treat rule behavior as graven in stone. 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] Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 04, 2010 at 09:02:43PM +0100, Thom Brown wrote: On 4 August 2010 20:58, Josh Berkus j...@agliodbs.com wrote: Great, I was afraid people would want another beta if we forced an initdb. So a hearty +1 for fixing it and not doing another beta (pending other bugs obviously). And, btw, there has been a lot of testing of pg_upgrade due to the initdbs and otherwise. I think 9.0 is going to have a pretty darned solid pg_upgrade because of it. Leave my name off the commit comment then ;) People who have been waiting for this will burn me as a heretical witch... er.. man witch... warlock? Witch. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 3:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alex Hunsaker bada...@gmail.com writes: On Wed, Aug 4, 2010 at 11:04, Tom Lane t...@sss.pgh.pa.us wrote: If we were a bit earlier in the 9.0 cycle I would suggest that this confusion is a sufficient reason to drop the one-argument form of string_agg. It's too late now though. FWIW I think we can still change it. Isn't this type of issue part of what beta is for? If we were in RC that would be a different story :) Well, it'd take an initdb to get rid of it. In the past we've avoided forcing initdb post-beta1 unless it was Really Necessary. OTOH, we seem to be in the mode of encouraging beta testers to test pg_upgrade, so maybe that concern isn't worth much at the moment. I vote fix it. This is going to be a high travel function, and should be right. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Using Small Size SSDs to improve performance?
The prices of large capacity Solid State Disks (SLCs of course) are still too high to most of us. But it´s already possible to find SSDs of small size (8 to 32 GB) today with affordable prices and good performance (0,1ms access time and at least 150MB/s r/w transfer rate). So, would it possible to use these Small Size SSDs (S5Ds) as a buffer to improve postgreSQL's write performance? For now, I detected two opportunities: 1) usage of a S5D to temporarily store the WAL log files until a deamon process copy them to the regular HD. 2) usage of a S5D to store instructions to a make a checkpoint. Instead of write the dirty pages directly to database files, postgreSQL could dump to SSD the dirty pages and the instructions of how update the data files. Later, a deamon process would update the files following these instructions and erase the instruction files after that. I guess that MVCC induces the spread of writes along the file area, requiring lots of seeks to find the correct disk block. So SSDs will produce a good performance in burst situation. I guess these ideas could improve the write performance significantly (3x to 50x) in databases systems that perform writes with SYNC and have many write bursts or handle large (20MB+) BLOBs (many WAL segments and pages to write on checkpoint). Of course, postgreSQL should be patched to handle, not only with the new behaviours, but to handle a possible SSD full. One solution to (1) could be a fast/main volume scheme. In case of the fast volume full condition, postgreSQL should use the main volume. The (2) solution is more delicate because introduce a new type of file to hold data. But, if the gain worths, it should be examinated ... Well, that´s it. -- Nilson
Re: [HACKERS] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Michael Meskes mich...@fam-meskes.de wrote: I'd consider this a bug. Could you explain why? The assertions that people consider it a bug without explanation of *why* is confusing for me. It sounds more like a feature of the ECPG interface that people would really like, and which has been technically possible since PostgreSQL 8.3, but for which nobody submitted a patch until this week. There was some hint that a 9.0 ECPG patch added new features which might make people expect this feature to have also been added. If this patch isn't necessarily correct, and would be dangerous to apply at this point, should the other patch be reverted as something which shouldn't go out without this feature? -Kevin -- 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
I wrote: Hm? I don't think that an initdb here would have any impact on whether we can call the next drop RC1 or not. We're talking about removing a single built-in entry in pg_proc --- it's one of the safest changes we could possibly make. Well, I forgot that an aggregate involves more than one catalog row ;-). So it's a bit bigger patch than that, but still pretty small and safe. See attached. What we are doing here, IMO, is not just changing string_agg() but instituting a project policy that we are not going to offer built-in aggregates with the same names and different numbers of arguments --- otherwise the problem will come right back. Therefore, the attached patch adds an opr_sanity regression test that will complain if anyone tries to add such. Of course, this isn't preventing users from creating such aggregates, but it's on their own heads to not get confused if they do. This policy also implies that we are never going to allow default arguments for aggregates, or at least never have any built-in ones that use such a feature. By my count the following people had offered an opinion on making this change: for: tgl, josh, badalex, mmoncure against: rhaas, thom Anybody else want to vote, or change their vote after seeing the patch? regards, tom lane Index: doc/src/sgml/func.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.522 diff -c -r1.522 func.sgml *** doc/src/sgml/func.sgml 29 Jul 2010 19:34:40 - 1.522 --- doc/src/sgml/func.sgml 4 Aug 2010 22:01:12 - *** *** 9731,9737 thead row entryFunction/entry ! entryArgument Type/entry entryReturn Type/entry entryDescription/entry /row --- 9731,9737 thead row entryFunction/entry ! entryArgument Type(s)/entry entryReturn Type/entry entryDescription/entry /row *** *** 9901,9917 primarystring_agg/primary /indexterm function ! string_agg(replaceable class=parameterexpression/replaceable ! [, replaceable class=parameterdelimiter/replaceable ] ) /function /entry entry !typetext/type /entry entry typetext/type /entry ! entryinput values concatenated into a string, optionally with delimiters/entry /row row --- 9901,9917 primarystring_agg/primary /indexterm function ! string_agg(replaceable class=parameterexpression/replaceable, ! replaceable class=parameterdelimiter/replaceable) /function /entry entry !typetext/type, typetext/type /entry entry typetext/type /entry ! entryinput values concatenated into a string, separated by delimiter/entry /row row Index: doc/src/sgml/syntax.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v retrieving revision 1.149 diff -c -r1.149 syntax.sgml *** doc/src/sgml/syntax.sgml 4 Aug 2010 15:27:57 - 1.149 --- doc/src/sgml/syntax.sgml 4 Aug 2010 22:01:12 - *** *** 1589,1598 /programlisting not this: programlisting ! SELECT string_agg(a ORDER BY a, ',') FROM table; -- not what you want /programlisting ! The latter syntax will be accepted, but literal','/ will be ! treated as a (useless) sort key. /para para --- 1589,1599 /programlisting not this: programlisting ! SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect /programlisting ! The latter is syntactically valid, but it represents a call of a ! single-argument aggregate function with two literalORDER BY/ keys ! (the second one being rather useless since it's a constant). /para para Index: src/backend/utils/adt/varlena.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/varlena.c,v retrieving revision 1.177 diff -c -r1.177 varlena.c *** src/backend/utils/adt/varlena.c 26 Feb 2010 02:01:10 - 1.177 --- src/backend/utils/adt/varlena.c 4 Aug 2010 22:01:12 - *** *** 3320,3326 /* * string_agg - Concatenates values and returns string. * ! * Syntax: string_agg(value text, delimiter text = '') RETURNS text * * Note: Any NULL values are ignored. The first-call delimiter isn't * actually used at all, and on subsequent calls the delimiter precedes --- 3320,3326 /* * string_agg - Concatenates values and returns string. * ! * Syntax: string_agg(value text, delimiter text) RETURNS text * * Note: Any NULL values are ignored. The first-call delimiter isn't * actually used at all, and on subsequent calls the
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On 4 August 2010 23:19, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: Hm? I don't think that an initdb here would have any impact on whether we can call the next drop RC1 or not. We're talking about removing a single built-in entry in pg_proc --- it's one of the safest changes we could possibly make. Well, I forgot that an aggregate involves more than one catalog row ;-). So it's a bit bigger patch than that, but still pretty small and safe. See attached. What we are doing here, IMO, is not just changing string_agg() but instituting a project policy that we are not going to offer built-in aggregates with the same names and different numbers of arguments --- otherwise the problem will come right back. Therefore, the attached patch adds an opr_sanity regression test that will complain if anyone tries to add such. Of course, this isn't preventing users from creating such aggregates, but it's on their own heads to not get confused if they do. Yes, I think that's sensible. This policy also implies that we are never going to allow default arguments for aggregates, or at least never have any built-in ones that use such a feature. By my count the following people had offered an opinion on making this change: for: tgl, josh, badalex, mmoncure against: rhaas, thom Anybody else want to vote, or change their vote after seeing the patch? regards, tom lane I was afraid that the function would be pulled completely, but from looking at the patch, you're only removing the function with a single-parameter signature, which is quite innocuous. So I'm for now. -- Thom Brown Registered Linux user: #516935 -- 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Thom Brown t...@linux.com writes: I was afraid that the function would be pulled completely, but from looking at the patch, you're only removing the function with a single-parameter signature, which is quite innocuous. Yes, of course, sorry if I confused anyone. It's the combination of having both one- and two-argument forms that is the problem. Since the one-argument form isn't doing anything but offering a rather useless default, we won't lose functionality if we pull it. 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
[HACKERS] Two different methods of sneaking non-immutable data into an index
While chatting with Haas off-list regarding how the new array/string functions should work (see the thread in its glory here: http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg148865.html) the debate morphed into the relative pros and cons about the proposed concat() being marked stable vs immutable. I did some checking into how things work now, and found some surprising cases. *) No dependency check on user definable casts: postgres=# create function hackdate(date) returns text as $$ select 'casted!'::text; $$ language sql; CREATE FUNCTION postgres=# create cast (date as text) with function hackdate(date); CREATE CAST postgres=# select now()::date || 'abc'::text; -- you're right! ?column? casted!abc postgres=# create table vtest(a date, b text); CREATE TABLE postgres=# create unique index vtest_idx on vtest((a || b)); CREATE INDEX postgres=# insert into vtest values (now(), 'test'); INSERT 0 1 postgres=# insert into vtest values (now(), 'test'); -- should fail ERROR: duplicate key value violates unique constraint vtest_idx DETAIL: Key ((a || b))=(casted!test) already exists. postgres=# drop cast (date as text); DROP CAST postgres=# insert into vtest values (now(), 'test'); INSERT 0 1 postgres=# select * from vtest; a | b +-- 2010-08-01 | test 2010-08-01 | test (2 rows) *) textanycat is defined immutable and shouldn't be: create table vtest(a date, b text); create unique index vtest_idx on vtest((a::text || b)); -- fails on immutable check create unique index vtest_idx on vtest((a || b)); -- works?? insert into vtest values (now(), 'test'); set datestyle to 'SQL, DMY'; insert into vtest values (now(), 'test'); postgres=# select * from vtest;date a | b +-- 31/07/2010 | test 31/07/2010 | test (2 rows) postgres=# select * from vtest where a|| b = now()::date || 'test'; a | b +-- 31/07/2010 | test (1 row) *) also, isn't it possible to change text cast influencing GUCs 'n' times per statement considering any query can call a function and any function can say, change datestyle? Shouldn't the related functions be marked 'volatile', not stable? 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] Using Small Size SSDs to improve performance?
On Wed, Aug 4, 2010 at 5:09 PM, Nilson nilson.bra...@gmail.com wrote: The prices of large capacity Solid State Disks (SLCs of course) are still too high to most of us. But it´s already possible to find SSDs of small size (8 to 32 GB) today with affordable prices and good performance (0,1ms access time and at least 150MB/s r/w transfer rate). So, would it possible to use these Small Size SSDs (S5Ds) as a buffer to improve postgreSQL's write performance? For now, I detected two opportunities: 1) usage of a S5D to temporarily store the WAL log files until a deamon process copy them to the regular HD. 2) usage of a S5D to store instructions to a make a checkpoint. Instead of write the dirty pages directly to database files, postgreSQL could dump to SSD the dirty pages and the instructions of how update the data files. Later, a deamon process would update the files following these instructions and erase the instruction files after that. I guess that MVCC induces the spread of writes along the file area, requiring lots of seeks to find the correct disk block. So SSDs will produce a good performance in burst situation. I guess these ideas could improve the write performance significantly (3x to 50x) in databases systems that perform writes with SYNC and have many write bursts or handle large (20MB+) BLOBs (many WAL segments and pages to write on checkpoint). Of course, postgreSQL should be patched to handle, not only with the new behaviours, but to handle a possible SSD full. One solution to (1) could be a fast/main volume scheme. In case of the fast volume full condition, postgreSQL should use the main volume. The (2) solution is more delicate because introduce a new type of file to hold data. But, if the gain worths, it should be examinated ... Well, that´s it. This is offtopic for -hackers...better -general or -performance. In fact, there is a long highly informative topic on SSDs going on right now which you might be interested in reading. The real short answer to your question is that $/gb is not the only metric, and the current situation with flash SSD is much more complex than it appears on the surface (for example, they basically suck without write buffering). 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 04, 2010 at 06:19:49PM -0400, Tom Lane wrote: I wrote: Hm? I don't think that an initdb here would have any impact on whether we can call the next drop RC1 or not. We're talking about removing a single built-in entry in pg_proc --- it's one of the safest changes we could possibly make. Well, I forgot that an aggregate involves more than one catalog row ;-). So it's a bit bigger patch than that, but still pretty small and safe. See attached. What we are doing here, IMO, is not just changing string_agg() but instituting a project policy that we are not going to offer built-in aggregates with the same names and different numbers of arguments --- otherwise the problem will come right back. Therefore, the attached patch adds an opr_sanity regression test that will complain if anyone tries to add such. Of course, this isn't preventing users from creating such aggregates, but it's on their own heads to not get confused if they do. This policy also implies that we are never going to allow default arguments for aggregates, or at least never have any built-in ones that use such a feature. By my count the following people had offered an opinion on making this change: for: tgl, josh, badalex, mmoncure against: rhaas, thom Anybody else want to vote, or change their vote after seeing the patch? +1 for removing the single-argument version. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 16:33, Thom Brown t...@linux.com wrote: I was afraid that the function would be pulled completely, but from looking at the patch, you're only removing the function with a single-parameter signature, which is quite innocuous. So I'm for now. Ahh, Now I see why you were worried about people calling you a witch :) On another note, I do wonder if we could avoid more confusion by just reordering the arguments. In-fact I bet the original argument ordering was done precisely so it would match the 1 argument version. I dunno about anyone else but (a, ',' order by a) just looks weird. Or in other words, any thoughts on: select string_agg(delim, expression); I don't want to stretch this out, but while we are making changes... -- 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] Two different methods of sneaking non-immutable data into an index
Merlin Moncure mmonc...@gmail.com writes: While chatting with Haas off-list regarding how the new array/string functions should work (see the thread in its glory here: http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg148865.html) the debate morphed into the relative pros and cons about the proposed concat() being marked stable vs immutable. I did some checking into how things work now, and found some surprising cases. Er ... now being defined as what? I can't replicate your results in HEAD. In particular, textanycat isn't immutable anymore. The DROP CAST case is a bit interesting. We don't record a dependency on the cast as such, but on the underlying function --- if you'd tried to drop the function you'd not have been allowed to. It is a bit peculiar that dropping the cast causes the meaning of a::text to change, but I'm not sure there's much we can do about that. In any case, it seems like that's not nearly as much of a hazard as doing CREATE OR REPLACE FUNCTION and changing the computation done by the function. We could disallow that maybe, but that cure seems worse than the disease. 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Alex Hunsaker bada...@gmail.com writes: I dunno about anyone else but (a, ',' order by a) just looks weird. I suppose, but aren't you just focusing on the argument being constant? In the more general case I don't think there's anything unnatural about this syntax. Or in other words, any thoughts on: select string_agg(delim, expression); That looks pretty weird to me anyway, with or without use of ORDER BY. Nobody would think to write the delimiter first. Usually you put the most important argument first, and no one would see the delimiter as the most important one. 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] more numeric stuff
On Wed, Aug 4, 2010 at 4:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I have a couple ideas for further work on the numeric code that I want to get feedback on. 1. Cramming it down some more. I propose that we introduce a third format with a one-byte header: 1 bit for sign, 3 bits for dynamic scale, and 4 bits for weight (the first of which is a sign bit). This might seem crazy, Yes, it does. In the first place it isn't going to work conveniently because NumericDigit requires int16 alignment. It is definitely not convenient. I'm not disputing that. In the second, shaving just one byte doesn't seem like enough win to be worth the trouble. I don't believe your billion rows argument because you aren't factoring in the result of row-level alignment padding --- most of the time you're not going to win anything. Row-level alignment padding is a problem, and on very short rows, or rows where numeric is the only varlena, you may see no benefit. But if there are multiple text or numeric columns packed up next to each other, things are more promising. We don't need any special marker to indicate that the 1-byte format is in use, because we can deduce it from the length of the varlena (after excluding the header): even = 2b or 4b header, odd = 1b header. There can't be any odd-length numerics already on disk, so there shouldn't be any compatibility break for pg_upgrade to worry about. Really? Not sure this is true, because numerics can be toast-compressed. It hardly ever happens, but to do this that's not good enough. I was thinking of it like PG_GETARG_TEXT_PP() and similar - we would detoast compressed and external datums, but leave packed ones as-is. At that point you should have an accurate length count, and can decide what to do. 2. Don't untoast/don't copy. This would be good, but I'm not sure how to do it. The main problem again is NumericDigit alignment. Only about half the time is the digit array going to be aligned the way you need, so that puts a real crimp in the possible win. (In fact, if we assume the previous field is more than byte aligned and the toast header is one byte, then the digit array is *never* properly aligned on disk :-() This is another reason why I think a 1-byte numeric header would be good to have. One possibility is to have an additional toasting rule that forces odd-byte-alignment of a field's one-byte header. But it's a bit hard to argue that numeric deserves the additional overhead that that would put into all the core tuple forming/deforming logic. Yeah, plus we'd be adding more alignment padding for an extremely tenuous performance gain. The benchmarks I did this morning seem to indicate that the extra palloc/pfree/memcpy overhead is only barely more than zero, so it only makes sense if we can get it without suffering other penalties. 3. 64-bit arithmetic. Right now, mul_var() and div_var() use int for arithmetic, but haven't we given up on supporting platforms without long long? I'm not sure I'm motivated enough to write the patch myself, but it seems like 64-bit arithmetic would give us a lot more room to postpone carries. I don't think this would win unless we went to 32-bit NumericDigit, which is a problem from the on-disk-compatibility standpoint, This would increase the average size of a Numeric value considerably, so it would be a very BAD thing IMO. not to mention making the alignment issues even worse. Postponing carries is good, but we have enough headroom for that already --- I really doubt that making the array elements wider would save anything noticeable unless you increase NBASE. I dunno, it was just a thought, based on some quick benchmarking that indicated some possible hotspots in that area. But I didn't test it carefully enough to be sure. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 7:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Or in other words, any thoughts on: select string_agg(delim, expression); That looks pretty weird to me anyway, with or without use of ORDER BY. Nobody would think to write the delimiter first. Usually you put the most important argument first, and no one would see the delimiter as the most important one. Well, it would match the syntax of things like perl's join(). But I think that's probably not enough reason to go fiddling with it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 17:07, Tom Lane t...@sss.pgh.pa.us wrote:
Alex Hunsaker bada...@gmail.com writes:
I dunno about anyone else but (a, ',' order by a) just looks weird.
I suppose, but aren't you just focusing on the argument being constant?
Yes.
Or in other words, any thoughts on:
select string_agg(delim, expression);
That looks pretty weird to me anyway, with or without use of ORDER BY.
Nobody would think to write the delimiter first. Usually you put the
most important argument first, and no one would see the delimiter
as the most important one.
No argument about the most important arg first. I think we have a
difference of opinion on what the important argument is :-)
It might just be my perl upbringing talking, for example you join(',',
...) or split(',', ) in perl.
Either way *shrug* Im happy to leave it alone.
--
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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 6:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: for: tgl, josh, badalex, mmoncure against: rhaas, thom Anybody else want to vote, or change their vote after seeing the patch? If we're not regarding this as beta-forcing, I abstain. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] more numeric stuff
Robert Haas robertmh...@gmail.com writes: On Wed, Aug 4, 2010 at 4:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: This would be good, but I'm not sure how to do it. The main problem again is NumericDigit alignment. Only about half the time is the digit array going to be aligned the way you need, so that puts a real crimp in the possible win. (In fact, if we assume the previous field is more than byte aligned and the toast header is one byte, then the digit array is *never* properly aligned on disk :-( This is another reason why I think a 1-byte numeric header would be good to have. Hmm. That's a good point --- 1-byte toast header plus 1-byte numeric header would leave you correctly aligned, anytime the previous field didn't end on an odd byte boundary. So maybe the combination of both things would have enough synergy to be worth the trouble. Still, it seems like it'd be quite messy to deal with 1-byte header followed by NumericDigits without any padding ... there'd be no way to declare that as a C struct, for sure. Have you got a plan for what this would actually look like in code? Also, maybe this idea should supersede the one with two-byte numeric header. I'm not sure it's worth having three variants, and we are not at all committed to the two-byte version yet. I don't think this would win unless we went to 32-bit NumericDigit, which is a problem from the on-disk-compatibility standpoint, This would increase the average size of a Numeric value considerably, so it would be a very BAD thing IMO. Oh, I certainly wasn't advocating for doing that ;-) 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] Two different methods of sneaking non-immutable data into an index
On Wed, Aug 4, 2010 at 7:00 PM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: While chatting with Haas off-list regarding how the new array/string functions should work (see the thread in its glory here: http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg148865.html) the debate morphed into the relative pros and cons about the proposed concat() being marked stable vs immutable. I did some checking into how things work now, and found some surprising cases. Er ... now being defined as what? I can't replicate your results in HEAD. In particular, textanycat isn't immutable anymore. ah, my mistake. I'm using a fairly old build of 9.0. The DROP CAST case is a bit interesting. We don't record a dependency on the cast as such, but on the underlying function --- if you'd tried to drop the function you'd not have been allowed to. It is a bit peculiar that dropping the cast causes the meaning of a::text to change, but I'm not sure there's much we can do about that. In any case, it seems like that's not nearly as much of a hazard as doing CREATE OR REPLACE FUNCTION and changing the computation done by the function. We could disallow that maybe, but that cure seems worse than the disease. yep (the textanycat was much more interesting to me) 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] Using Small Size SSDs to improve performance?
Nilson wrote: 1) usage of a S5D to temporarily store the WAL log files until a deamon process copy them to the regular HD. The WAL is rarely as much of a bottleneck as people think it is. Because it's all sequential writes, so long as you put it onto a dedicated disk there's minimal advantage to be had using an SSD for it. Lots of small sequential writes is really not the place where SSD shines compared with regular disk. 2) usage of a S5D to store instructions to a make a checkpoint. Instead of write the dirty pages directly to database files, postgreSQL could dump to SSD the dirty pages and the instructions of how update the data files. Later, a deamon process would update the files following these instructions and erase the instruction files after that. This is essentially what happens with the operating system cache: it buffers writes into memory as the checkpoint does them, and then later does the actual I/O to write them to disk--hopefully before the sync call that pushes it out comes in. There are plenty of problems with how that's done right now. But I don't feel there's enough benefit to optimize specifically for SSD when a more general improvement could be done instead in that area instead. I guess these ideas could improve the write performance significantly (3x to 50x) in databases systems that perform writes with SYNC and have many write bursts or handle large (20MB+) BLOBs (many WAL segments and pages to write on checkpoint). That's optimistic. Right now heavy write systems get a battery-backed cache in the RAID card that typically absorbs 256MB to 512MB worth of activity. You really need to reference SSD acceleration against that as your reference. If you do that, the SSD gains stop looking so big. Checkpoint writes right now go: shared_buffers - OS cache - RAID BBWC - disk And those two layers in the middle are already providing a significant speedup on burst workloads. Ultimately, all the burst stuff has to make it onto regular disks eventually though, if you can't fit the whole thing on SSD, and then you're back to solving the non-SSD problem again. That's the problem with these things that keeps them from being magic bullets; if you have a database large enough that you can't fit the working set in RAM nowadays, you probably can't fit whole thing on SSD either. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] Using Small Size SSDs to improve performance?
And those two layers in the middle are already providing a significant speedup on burst workloads. Ultimately, all the burst stuff has to make it onto regular disks eventually though, if you can't fit the whole thing on SSD, and then you're back to solving the non-SSD problem again. That's the problem with these things that keeps them from being magic bullets; if you have a database large enough that you can't fit the working set in RAM nowadays, you probably can't fit whole thing on SSD either. The only times we've seen real gains from using SSD's in production was when we lavished money on a lot of them for data warehousing. There the speedup in both throughput and random seeks really boosted performance. In the other use cases I've tested, the only real advantage to SSDs was keeping your form factor down. I haven't been able to test things like putting a hot table on a specific SSD. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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] Using Small Size SSDs to improve performance?
Josh Berkus wrote: I haven't been able to test things like putting a hot table on a specific SSD. Putting a hot table or even better an index on them, where that relation fits on SSD but the whole database doesn't, can work well. But it doesn't give the speedup levels people expect because hot stuff tends to already be in memory, too. I've deflated multiple SSD will fix our problems! meetings with output from pg_buffercache, showing everything they were planning to put on there was already in the hottest part of database RAM: shared_buffers. Indexes of heavily written tables are the thing I've seen the most actual improvement on like this. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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: edit function, show function commands patch
On Wed, Aug 4, 2010 at 3:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, the thing about $EDITOR is that it's a very-widely-understood convention. This one won't be, so the argument for making it an environment variable seems pretty thin. Fwiw the +linenumber convention has been part of $EDITOR since basically as long as vi has existed. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 11:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: What we are doing here, IMO, is not just changing string_agg() but instituting a project policy that we are not going to offer built-in aggregates with the same names and different numbers of arguments --- otherwise the problem will come right back. Well I think this can be a pretty soft policy. The thing is that for string_agg it's a pretty weak argument for the one-argument form anyways so there's not much loss in losing the 1-argument form. In other cases the extra arguments might be for very obscure cases or there may be lots of precedent for the variadic form and users might expect to have it. In which case we could decide the cost/benefit calculation comes down the other way. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] more numeric stuff
On Wed, Aug 4, 2010 at 7:27 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Aug 4, 2010 at 4:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: This would be good, but I'm not sure how to do it. The main problem again is NumericDigit alignment. Only about half the time is the digit array going to be aligned the way you need, so that puts a real crimp in the possible win. (In fact, if we assume the previous field is more than byte aligned and the toast header is one byte, then the digit array is *never* properly aligned on disk :-( This is another reason why I think a 1-byte numeric header would be good to have. Hmm. That's a good point --- 1-byte toast header plus 1-byte numeric header would leave you correctly aligned, anytime the previous field didn't end on an odd byte boundary. So maybe the combination of both things would have enough synergy to be worth the trouble. Still, it seems like it'd be quite messy to deal with 1-byte header followed by NumericDigits without any padding ... there'd be no way to declare that as a C struct, for sure. Have you got a plan for what this would actually look like in code? No. I was hoping you'd have a brilliant idea. Generally, I think we'd need to treat a Numeric as essentially a void * and probably lose the special cases that try to operate directly on the packed format. That would allow us to confine the knowledge of the multiple header formats to the pack/unpack functions (set_var_from_num and make_result). Also, maybe this idea should supersede the one with two-byte numeric header. I'm not sure it's worth having three variants, and we are not at all committed to the two-byte version yet. It's a thought, but let's not get ahead of ourselves. The code for the two-byte header code is done, tested, reviewed, and committed, whereas the code for the one-byte header is vaporware and full of difficulties. Furthermore, let's not kid ourselves: a broad range of useful values can be represented using a one-byte header, but to need a four-byte header instead of a two-byte header you need to be doing something fairly ridiculous. Even if the one-byte header thing gets implemented, I don't think it makes sense to give back 2 bytes on all the fine things that can be represented with a two-byte header for some tenuous code complexity benefit. I don't think this would win unless we went to 32-bit NumericDigit, which is a problem from the on-disk-compatibility standpoint, This would increase the average size of a Numeric value considerably, so it would be a very BAD thing IMO. Oh, I certainly wasn't advocating for doing that ;-) Oh, good. :-) Making this smaller is too much work to think about doing *anything* that might make it bigger. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Two different methods of sneaking non-immutable data into an index
On Wed, Aug 4, 2010 at 6:43 PM, Merlin Moncure mmonc...@gmail.com wrote: *) also, isn't it possible to change text cast influencing GUCs 'n' times per statement considering any query can call a function and any function can say, change datestyle? Shouldn't the related functions be marked 'volatile', not stable? This is just evil. It seems to me that we might want to instead prevent functions from changing things for their callers, or postponing any such changes until the end of the statement, or, uh, something. We can't afford to put ourselves in a situation of having to make everything volatile; at least, not if performance is anywhere in our top 50 goals. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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: edit function, show function commands patch
On Wed, Aug 4, 2010 at 8:50 PM, Greg Stark gsst...@mit.edu wrote: On Wed, Aug 4, 2010 at 3:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, the thing about $EDITOR is that it's a very-widely-understood convention. This one won't be, so the argument for making it an environment variable seems pretty thin. Fwiw the +linenumber convention has been part of $EDITOR since basically as long as vi has existed. What precisely do you mean by that? We've pretty much established that the convention is nothing like universally accepted by the editors that are out there. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Using Small Size SSDs to improve performance?
On Wed, Aug 4, 2010 at 7:43 PM, Greg Smith g...@2ndquadrant.com wrote: 2) usage of a S5D to store instructions to a make a checkpoint. Instead of write the dirty pages directly to database files, postgreSQL could dump to SSD the dirty pages and the instructions of how update the data files. Later, a deamon process would update the files following these instructions and erase the instruction files after that. This is essentially what happens with the operating system cache: it buffers writes into memory as the checkpoint does them, and then later does the actual I/O to write them to disk--hopefully before the sync call that pushes it out comes in. There are plenty of problems with how that's done right now. But I don't feel there's enough benefit to optimize specifically for SSD when a more general improvement could be done instead in that area instead. One of the tricky parts here is that we don't actually handle large values of shared_buffers that well. If I recall your previous posts correctly, the benefit dries up at a level no higher than about 10GB. We'd probably need to try to understand what underlies that effect before thinking too hard about ways to effectively enlarge shared buffers even more. Another thought I had was whether there would be any benefit to attempting to tune the buffer management algorithm to improve cache locality. In other words, if we don't really need all of shared_buffers, maybe there would be some benefit in trying to use only as much of it as we actually need; or maybe provide some mechanism for individual backends to have a weak preference for reusing the same pages that they've used before. But maybe this doesn't actually matter: even a L3 cache isn't big enough to hold a significant percentage of a large shared_buffers setting, I think. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] documentation for committing with git
On Wed, Aug 4, 2010 at 6:29 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: All those issues can be avoided if you only run git gc when all the working directories are in a clean state, with no staged but uncommitted changes or other funny things. I can live with that gun tied to my ankle ;-). Does even that open a possibility for data loss? Use of the alternates feature will, to my knowledge, never write the referenced repository: all new objects are held in the referencers. The only condition as I understand it is not to generate garbage in the reference repository, and that nominally does not happen in a repo that exists only to be an object pool (you probably even want to use a bare repository instead of one with checked out files). I believe this feature is popular with hosting serving many repos of the same project. The especially paranoid may want to try setting their alternate, referenced repository to be read-only with respect to the user doing all the potentially-modifying work, undoing this if and when they feel like adding more objects to the referenced repository. My guess is one can do a clean checkout and then ride this strategy for quite a long time (a year? more? it depends on how space-conscious one is), so that would not be a incredibly onerous paranoia, if one has it. fdr -- 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: edit function, show function commands patch
Robert Haas robertmh...@gmail.com writes: On Wed, Aug 4, 2010 at 8:50 PM, Greg Stark gsst...@mit.edu wrote: On Wed, Aug 4, 2010 at 3:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, the thing about $EDITOR is that it's a very-widely-understood convention. This one won't be, so the argument for making it an environment variable seems pretty thin. Fwiw the +linenumber convention has been part of $EDITOR since basically as long as vi has existed. What precisely do you mean by that? We've pretty much established that the convention is nothing like universally accepted by the editors that are out there. More to the point, what I was saying is that there is no convention out there for a second environment variable that tells programs calling $EDITOR how to specify a linenumber argument. 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: [BUGS] Re: [HACKERS] Drop one-argument string_agg? (was Re: string_agg delimiter having no effect with order by)
Greg Stark gsst...@mit.edu writes: On Wed, Aug 4, 2010 at 11:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: What we are doing here, IMO, is not just changing string_agg() but instituting a project policy that we are not going to offer built-in aggregates with the same names and different numbers of arguments --- otherwise the problem will come right back. Well I think this can be a pretty soft policy. Sure, we could break it given good cause. What I intend with the opr_sanity test is just that people won't be able to put something in without being reminded of this consideration. 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
