Re: [HACKERS] Overhauling GUCS
Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: Oh, and wal_buffers, the default for which we should just change if it weren't for SHMMAX. Uh, why? On a workload of mostly small transactions, what value is there in lots of wal_buffers? None. But there's also little to no harm in having a higher setting; at worst you waste a few megabytes of memory. Besides, most databases are initialized from some outside source in the beginning, and data loading does benefit from a higher wal_buffers setting. Ideally, of course, there would be no wal_buffers setting, and WAL buffers would be allocated from shared_buffers pool on demand... -- 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] Runtime checking of MCV (Was: ... histogram bucket numdistinct statistics)
On Tue, 2008-06-10 at 19:03 -0400, Tom Lane wrote: Given such an MCV list, the planner will always make the right choice of whether to do index or seqscan ... as long as it knows the value being searched for, that is. Parameterized plans have a hard time here, but that's not really the fault of the statistics. This is maybe the best example where multiple (sub)plans could be glued together with some kind of plan fork node, so that the actual plan to be executed would be decided based on the parameter values and checking the statistics at runtime instead of plan time for parameterized plans... so the planner creates alternative (sub)plans (e.g. seqscan vs index scan) for the cases where the parameters are MCV or not, and then place them in different branches of a runtime check of the parameter values vs the statistics. Of course the number of branches must be limited, this would be the challenge of such a feature... to cover the parameter space with the minimal number of plan branches so that disastrous plans for special parameter values are avoided. It would also be possible perhaps to gradually grow the alternative counts as a reaction to the actual parameter values used by queries, so that only the parameter space actually in use by queries is covered. In fact I would be interested in experimenting with this. Would it be possible to add new planner behavior as external code ? I would expect not, as the planner is in charge also for the correctness of the results and any external code would put that correctness at risk I guess... in any case, I'll go and check the source. BTW, there was a discussion about global prepared statements/caching of query plans, is there any advance on that ? Thorough planning would make the most sense in that context, possibly by using a special syntax for the application to signal the need for such planning for the most problematic (not necessarily the most used though) queries. Cheers, Csaba. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] B-tree finish incomplete split bug
While testing my xlogutils.c refactoring patch, I bumped into an
existing bug in the B-tree code that finishes an incomplete split:
btree_xlog_cleanup() calls _bt_insert_parent() to insert the parent
pointer. If the split page was the root page, _bt_insert_parent()
creates a new root page by calling _bt_newroot(). _bt_newroot() calls
CacheInvalidateRelcache() to notify other backends, but
CacheInvalidateRelcache causes a segfault when called during WAL replay,
because the invalidation infrastructure hasn't been initialized yet.
This bug was introduced in 8.2, when we started to cache metapage
information in relcache. It's no wonder that no-one has bumped into this
in the field, as the window for that to happen is extremely small; I had
to inject an XLogFlush(); elog(PANIC) into _bt_split to trigger it. I
wish we had regression tests for WAL recovery :-(.
The trivial fix is to not call CacheInvalidateRelcache() in recovery
(patch attached). Another option is to put the check into
CacheInvalidateRelcache() itself, but in the name of consistency we
should then put the same check into the other CacheInvalidate* variants
as well. As nbtinsert.c is the only place that calls
CacheInvalidateRelcache during WAL replay, I'm going to do the trivial fix.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Index: src/backend/access/nbtree/nbtinsert.c
===
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/access/nbtree/nbtinsert.c,v
retrieving revision 1.146.2.2
diff -c -r1.146.2.2 nbtinsert.c
*** src/backend/access/nbtree/nbtinsert.c 31 Dec 2007 04:52:20 - 1.146.2.2
--- src/backend/access/nbtree/nbtinsert.c 11 Jun 2008 08:20:25 -
***
*** 690,696
/* release buffers; send out relcache inval if metapage changed */
if (BufferIsValid(metabuf))
{
! CacheInvalidateRelcache(rel);
_bt_relbuf(rel, metabuf);
}
--- 690,697
/* release buffers; send out relcache inval if metapage changed */
if (BufferIsValid(metabuf))
{
! if (!InRecovery)
! CacheInvalidateRelcache(rel);
_bt_relbuf(rel, metabuf);
}
***
*** 1623,1629
END_CRIT_SECTION();
/* send out relcache inval for metapage change */
! CacheInvalidateRelcache(rel);
/* done with metapage */
_bt_relbuf(rel, metabuf);
--- 1627,1634
END_CRIT_SECTION();
/* send out relcache inval for metapage change */
! if (!InRecovery)
! CacheInvalidateRelcache(rel);
/* done with metapage */
_bt_relbuf(rel, metabuf);
--
Sent 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 - improve eqsel estimates by including histogram bucket numdistinct statistics
Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] writes: It's possible that the second option I described -- teaching Append when to use something other than sum() -- would only work in the cases where constraint exclusion could be fixed though. In which case having fractional row counts might actually be necessary. The above is just armwaving. IMHO, if you don't understand the structure of the table set then you're not going to be able to get the desired behavior via fractional rowcounts either. That's only a specific subset of cases. You could just as easily have quals which are only coincidentally related to the partition key or even not related at all, just very selective and produce no records from some partitions. The bottom line is that if you have a large table our statistics do a good job estimating the selectivity of a where clause with the minimum clamped to 1. If you partition it into 100 partitions then the minimum is clamped to 100. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] why copy tuple in the end of trigger when nothing changed in NEW OLD record variable
pgsql-hackers，hello:
version 8.3.0 in function plpgsql_exec_trigger.
in a trigger, if NEW is returned as the result and we do nothing to NEW.
for example, we have a table like this:
create table test (a int);
insert into test values(1);
and a trigger like:
create or replace function test_trigger() returns trigger as $$
begin
return new;
end; $$language plpgsql;
create trigger before_update_test before update
on test for each row execute procedure test_trigger();
in this trigger, we don't change the value of NEW.
than execute:
update test set a = 3;
after execution:
/* Copy tuple to upper executor memory */
rettup = SPI_copytuple((HeapTuple) (estate.retval));
we come to function ExecUpdate():
HeapTuple newtuple;
newtuple = ExecBRUpdateTriggers(estate, resultRelInfo,
tupleid, tuple);
Since the trigger's return value is copied to another memory address,
the newtuple is impossible equal to the oldtuple.
so the following condition:
if (newtuple != tuple) /* modified by Trigger(s) */
{
is FALSE for ever.
I think we can add some judgment conditions in function
plpgsql_exec_trigger() to avoid this problem.
billy
[EMAIL PROTECTED]
2008-06-11
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] math error or rounding problem Money type
On 6/8/08, Gregory Stark [EMAIL PROTECTED] wrote: I don't think as late as possible applies with money. If you were dealing with approximate measurements you want to round as late as possible because rounding is throwing away precision. But if you're dealing with money you're dealing with exact quantities. There is only going to be one correct time to round and that's whenever you're creating an actual ledger item or order line item or whatever. Once you've calculated how much interest to credit or whatever you have to make that credit an exact number of cents and the $0.004 you lost or gained in rounding never comes up again. Completely correct. In a proper accounting system you can only pull from a very limited subset of arithmetic operations. 'rounding' is not one of them except in the special case you mention above. 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] Proposal: Multiversion page api (inplace upgrade)
1) Overview
This proposal is part of inplace upgrade project. PostgreSQL should be able to
read any page in old version. This is basic for all possible upgrade method.
2) Background
We have several macros for manipulating of the page structures but this list is
not complete and many parts of code access into this structures directly and
severals part does not use existing macros. The idea is to use only specified
API for manipulation/access of data structure on page. This API will recognize
page layout version and it process data correctly.
3) API
Proposed API is extended version of current macros which does not satisfy all
Page Header manipulation. I plan to use function in first implementation,
because it offers better type control and debugging capability, but some
functions could be converted into macros (or into inline functions) in final
solution (performance improving). All changes are related to bufpage.h and page.c.
4) Implementation
The main point of implementation is to have several version of PageHeader
structure (e.g. PageHeader_04, PageHeader_03 ...) and correct structure will be
handled in special branch (see examples).
Possible improvement is to use union which combine different PageHeader version
and because most PageHeader items are same for all Page Layout version, it will
reduce number of switches. But I'm afraid if union have same data layout as
separate structure on all supported platforms.
There are examples:
void PageSetFull(Page page)
{
switch ( PageGetPageLayoutVersion(page) )
{
case 4 : ((PageHeader_04) (page))-pd_flags |= PD_PAGE_FULL;
break;
default elog(PANIC, PageSetFull is not supported on page layout
version %i,
PageGetPageLayoutVersion(page));
}
}
LocationIndex PageGetLower(Page page)
{
switch ( PageGetPageLayoutVersion(page) )
{
case 4 : return ((PageHeader_04) (page))-pd_lower);
}
elog(PANIC, Unsupported page layout in function PageGetLower.);
}
5) Issues
a) hash index has hardcoded PageHeader into meta page structure - need
rewrite hash index implementation to be multiheader version friendly
b) All *ItemSize macros (+toast chunk size) depends on sizeof(PageHeader) -
separate proposal will follow soon.
All comments are welcome.
Zdenek
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] B-tree finish incomplete split bug
Heikki Linnakangas [EMAIL PROTECTED] writes: The trivial fix is to not call CacheInvalidateRelcache() in recovery (patch attached). Another option is to put the check into CacheInvalidateRelcache() itself, but in the name of consistency we should then put the same check into the other CacheInvalidate* variants as well. As nbtinsert.c is the only place that calls CacheInvalidateRelcache during WAL replay, I'm going to do the trivial fix. This will need to be revisited if we ever hope to get read-only slaves working. But I agree with the trivial fix for now (especially in the back branches). 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] Proposal: Multiversion page api (inplace upgrade)
Zdenek Kotala [EMAIL PROTECTED] writes:
There are examples:
void PageSetFull(Page page)
{
switch ( PageGetPageLayoutVersion(page) )
{
case 4 : ((PageHeader_04) (page))-pd_flags |= PD_PAGE_FULL;
break;
default elog(PANIC, PageSetFull is not supported on page
layout version %i,
PageGetPageLayoutVersion(page));
}
}
LocationIndex PageGetLower(Page page)
{
switch ( PageGetPageLayoutVersion(page) )
{
case 4 : return ((PageHeader_04) (page))-pd_lower);
}
elog(PANIC, Unsupported page layout in function PageGetLower.);
}
I'm fairly concerned about the performance impact of turning what had
been simple field accesses into function calls. I argue also that since
none of the PageHeader fields have actually moved in any version that's
likely to be supported, the above functions are actually of exactly
zero value.
The proposed PANIC in PageSetFull seems like it requires more thought as
well: surely we don't want that ever to happen. Which means that
callers need to be careful not to invoke such an operation on an
un-updated page, but this proposed coding offers no aid in making sure
that won't happen. What is needed there, I think, is some more global
policy about what operations are permitted on old (un-converted) pages
and a high-level approach to ensuring that unsafe operations aren't
attempted.
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] Proposal: Multiversion page api (inplace upgrade)
Zdenek Kotala wrote: 4) Implementation The main point of implementation is to have several version of PageHeader structure (e.g. PageHeader_04, PageHeader_03 ...) and correct structure will be handled in special branch (see examples). (this won't come as a surprise as we talked about this in PGCon, but) I think we should rather convert the page structure to new format in ReadBuffer the first time a page is read in. That would keep the changes a lot more isolated. Note that you need to handle not only page header changes, but changes to internal representations of different data types, and changes like varvarlen and combocid. Those are things that have happened in the past; in the future, I'm foreseeing changes to the toast header, for example, as there's been a lot of ideas related to toast options compression. -- 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: Multiversion page api (inplace upgrade)
Heikki Linnakangas [EMAIL PROTECTED] writes: (this won't come as a surprise as we talked about this in PGCon, but) I think we should rather convert the page structure to new format in ReadBuffer the first time a page is read in. That would keep the changes a lot more isolated. The problem is that ReadBuffer is an extremely low-level environment, and it's not clear that it's possible (let alone practical) to do a conversion at that level in every case. In particular it hardly seems sane to expect ReadBuffer to do tuple content conversion, which is going to be practically impossible to perform without any catalog accesses. Another issue is that it might not be possible to update a page for lack of space. Are we prepared to assume that there will never be a transformation we need to apply that makes the data bigger? (Likely counterexample: adding collation info to text values.) In such a situation an in-place update might be impossible, and that certainly takes it outside the bounds of what ReadBuffer can be expected to manage. 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] Proposal: Multiversion page api (inplace upgrade)
Tom Lane napsal(a):
Zdenek Kotala [EMAIL PROTECTED] writes:
There are examples:
void PageSetFull(Page page)
{
switch ( PageGetPageLayoutVersion(page) )
{
case 4 : ((PageHeader_04) (page))-pd_flags |= PD_PAGE_FULL;
break;
default elog(PANIC, PageSetFull is not supported on page layout
version %i,
PageGetPageLayoutVersion(page));
}
}
LocationIndex PageGetLower(Page page)
{
switch ( PageGetPageLayoutVersion(page) )
{
case 4 : return ((PageHeader_04) (page))-pd_lower);
}
elog(PANIC, Unsupported page layout in function PageGetLower.);
}
I'm fairly concerned about the performance impact of turning what had
been simple field accesses into function calls.
I use functions now because it is easy to track what's going on. Finally it
should be (mostly) macros.
I argue also that since
none of the PageHeader fields have actually moved in any version that's
likely to be supported, the above functions are actually of exactly
zero value.
Yeah, it is why I'm thinking to use page header with unions inside (for example
TSL/flag field)
and use switch only in case like TSL or flags fields. What I don't know if
fields in this structure will be placed on same place on all platforms.
The proposed PANIC in PageSetFull seems like it requires more thought as
well: surely we don't want that ever to happen. Which means that
callers need to be careful not to invoke such an operation on an
un-updated page, but this proposed coding offers no aid in making sure
that won't happen. What is needed there, I think, is some more global
policy about what operations are permitted on old (un-converted) pages
and a high-level approach to ensuring that unsafe operations aren't
attempted.
ad) PANIC
PANIC shouldn't happen because page validation in BufferRead should check
supported page version.
ad) policy - it is good catch. I think all read page operation should be allowed
on old page version. Only tuple, LSN, TSL, and special modification should be
allowed for writing. Addpageitem should invokes page conversion before any
action happen (if there is free space for tuple, it is possible to convert page
in to the new format, but after conversion space could be smaller then tuple.).
Zdenek
--
Sent 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: Multiversion page api (inplace upgrade)
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: (this won't come as a surprise as we talked about this in PGCon, but) I think we should rather convert the page structure to new format in ReadBuffer the first time a page is read in. That would keep the changes a lot more isolated. The problem is that ReadBuffer is an extremely low-level environment, and it's not clear that it's possible (let alone practical) to do a conversion at that level in every case. Well, we can't predict the future, and can't guarantee that it's possible or practical to do the things we need to do in the future no matter what approach we choose. In particular it hardly seems sane to expect ReadBuffer to do tuple content conversion, which is going to be practically impossible to perform without any catalog accesses. ReadBuffer has access to Relation, which has information about what kind of a relation it's dealing with, and TupleDesc. That should get us pretty far. It would be a modularity violation, for sure, but I could live with that for the purpose of page version conversion. Another issue is that it might not be possible to update a page for lack of space. Are we prepared to assume that there will never be a transformation we need to apply that makes the data bigger? We do need some solution to that. One idea is to run a pre-upgrade script in the old version that scans the database and moves tuples that would no longer fit on their pages in the new version. This could be run before the upgrade, while the old database is still running, so it would be acceptable for that to take some time. No doubt people would prefer something better than that. Another idea would be to have some over-sized buffers that can be used as the target of conversion, until some tuples are moved off to another page. Perhaps the over-sized buffer wouldn't need to be in shared memory, if they're read-only until some tuples are moved. This is pretty hand-wavy, I know. The point is, I don't think these problems are insurmountable. (Likely counterexample: adding collation info to text values.) I doubt it, as collation is not a property of text values, but operations. But that's off-topic... -- 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] Overhauling GUCS
Greg Sabino Mullane wrote: * The word 'paramters' is still misspelled. :) Corrected for 8.4. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent 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: Multiversion page api (inplace upgrade)
Heikki Linnakangas napsal(a): Zdenek Kotala wrote: 4) Implementation The main point of implementation is to have several version of PageHeader structure (e.g. PageHeader_04, PageHeader_03 ...) and correct structure will be handled in special branch (see examples). (this won't come as a surprise as we talked about this in PGCon, but) I think we should rather convert the page structure to new format in ReadBuffer the first time a page is read in. That would keep the changes a lot more isolated. I agree with Tom's reply. And anyway this approach will be mostly isolated into page.c and you need to able read old page in both cases. Note that you need to handle not only page header changes, but changes to internal representations of different data types, and changes like varvarlen and combocid. Those are things that have happened in the past; in the future, I'm foreseeing changes to the toast header, for example, as there's been a lot of ideas related to toast options compression. I know, this is a first small step for inplace upgrade. Tupleheader will follow. Page structure is basic. I want to split development into small steps, because it is easy to review. Zdenek -- Sent 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: Multiversion page api (inplace upgrade)
Heikki Linnakangas napsal(a): Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: (this won't come as a surprise as we talked about this in PGCon, but) I think we should rather convert the page structure to new format in ReadBuffer the first time a page is read in. That would keep the changes a lot more isolated. The problem is that ReadBuffer is an extremely low-level environment, and it's not clear that it's possible (let alone practical) to do a conversion at that level in every case. Well, we can't predict the future, and can't guarantee that it's possible or practical to do the things we need to do in the future no matter what approach we choose. In particular it hardly seems sane to expect ReadBuffer to do tuple content conversion, which is going to be practically impossible to perform without any catalog accesses. ReadBuffer has access to Relation, which has information about what kind of a relation it's dealing with, and TupleDesc. That should get us pretty far. It would be a modularity violation, for sure, but I could live with that for the purpose of page version conversion. But if you look for example into hash implementation some pages are not in regular format and conversion could need more information which we do not have to have in ReadBuffer. Another issue is that it might not be possible to update a page for lack of space. Are we prepared to assume that there will never be a transformation we need to apply that makes the data bigger? We do need some solution to that. One idea is to run a pre-upgrade script in the old version that scans the database and moves tuples that would no longer fit on their pages in the new version. This could be run before the upgrade, while the old database is still running, so it would be acceptable for that to take some time. It could not work for indexes and do not forget TOAST chunks. I think in some cases you can get unused quoter of each page in TOAST table. No doubt people would prefer something better than that. Another idea would be to have some over-sized buffers that can be used as the target of conversion, until some tuples are moved off to another page. Perhaps the over-sized buffer wouldn't need to be in shared memory, if they're read-only until some tuples are moved. Anyway, you need mechanism how to mark that this page is read only which is also require a lot of modification. And some mechanism how to make a decision when this page converted. I guess this approach will require similar modification as convert on write. This is pretty hand-wavy, I know. The point is, I don't think these problems are insurmountable. (Likely counterexample: adding collation info to text values.) I doubt it, as collation is not a property of text values, but operations. But that's off-topic... Yes, it is offtopic, however I think Tom is right :-). Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
On Wed, 2008-06-11 at 11:45 -0400, Bruce Momjian wrote: Greg Sabino Mullane wrote: * The word 'paramters' is still misspelled. :) Corrected for 8.4. Technically this is a bug fix... why not backpatch it too? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Heikki, Ideally, of course, there would be no wal_buffers setting, and WAL buffers would be allocated from shared_buffers pool on demand... +1 --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: Oh, and wal_buffers, the default for which we should just change if it weren't for SHMMAX. Uh, why? On a workload of mostly small transactions, what value is there in lots of wal_buffers? Actually, it's also useful for any workload with many connections. Any time you have high throughput, really. We've seen this on DBT2, SpecJAppserver and iGen. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Josh Berkus wrote: Heikki, Ideally, of course, there would be no wal_buffers setting, and WAL buffers would be allocated from shared_buffers pool on demand... Same for pg_subtrans, pg_clog, etc (as previously discussed) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Robert Lor wrote: Robert Treat wrote: On Wednesday 04 June 2008 22:04:54 Greg Smith wrote: I was just talking to someone today about building a monitoring tool for this. Not having a clear way to recommend people monitor use of work_mem and its brother spilled to disk sorts is an issue right now, I'll whack that one myself if someone doesn't beat me to it before I get time. I remember *years* ago, someone wrote a perl script to poll pgsql_tmp and print out anytime something showed up... you could probably find that in the archives if you look around. of course to me this sounds like an excellent idea for a dtrace probe ;-) Actually, you can find out from the sort-end probe now whether or not the sort spilled to disk and number of disk blocks used. This is one of the probes from Simon. TRACE_POSTGRESQL_SORT_END(state-tapeset, (state-tapeset ? LogicalTapeSetBlocks(state-tapeset) : (state-allowedMem - state-availMem + 1023) / 1024)); 8.3 has GUC log_temp_files. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Joshua D. Drake wrote: On Wed, 2008-06-11 at 11:45 -0400, Bruce Momjian wrote: Greg Sabino Mullane wrote: * The word 'paramters' is still misspelled. :) Corrected for 8.4. Technically this is a bug fix... why not backpatch it too? That might show up as a diff for people doing upgrades where the minor version changed the spelling. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Bruce Momjian wrote: Joshua D. Drake wrote: On Wed, 2008-06-11 at 11:45 -0400, Bruce Momjian wrote: Greg Sabino Mullane wrote: * The word 'paramters' is still misspelled. :) Corrected for 8.4. Technically this is a bug fix... why not backpatch it too? That might show up as a diff for people doing upgrades where the minor version changed the spelling. People upgrading won't see it, I think. You only see it when you do a new initdb.. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Josh Berkus wrote: Heikki, Ideally, of course, there would be no wal_buffers setting, and WAL buffers would be allocated from shared_buffers pool on demand... +1 --Josh +1 -Jignesh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Dave Page wrote: On Mon, Jun 2, 2008 at 5:46 AM, Joshua D. Drake [EMAIL PROTECTED] wrote: I've seen people not doing so more often than you would think. Perhaps because they are DBAs and not sysadmins? I also meant a tool to do things like verify that the changes are valid, as someone else mentioned elsewhere in this thread. pg_ctl -D data check? I would +1 that. I would also really like to see that - though I'd also like to see an SQL interface so we can check a config before saving when editing via pgAdmin or similar. Should this be a TODO? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
On Wed, Jun 11, 2008 at 6:56 PM, Bruce Momjian [EMAIL PROTECTED] wrote: Dave Page wrote: On Mon, Jun 2, 2008 at 5:46 AM, Joshua D. Drake [EMAIL PROTECTED] wrote: pg_ctl -D data check? I would +1 that. I would also really like to see that - though I'd also like to see an SQL interface so we can check a config before saving when editing via pgAdmin or similar. Should this be a TODO? Yes please. -- Dave Page EnterpriseDB UK: 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
[HACKERS] How to Sponsor a Feature
Folks, I forgot to post the fact that I'd put up a http://wiki.postgresql.org/wiki/How_to_sponsor_a_feature, per my TODO from the developers' meeting in Ottawa. Sorry about that. Anyhow, Jignesh has come up with a proposal template http://wiki.postgresql.org/wiki/ProposalTemplate that could use a once-over, too. Comments? Questions? Brickbats? Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] 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] Overhauling GUCS
Dave Page wrote: On Wed, Jun 11, 2008 at 6:56 PM, Bruce Momjian [EMAIL PROTECTED] wrote: Dave Page wrote: On Mon, Jun 2, 2008 at 5:46 AM, Joshua D. Drake [EMAIL PROTECTED] wrote: pg_ctl -D data check? I would +1 that. I would also really like to see that - though I'd also like to see an SQL interface so we can check a config before saving when editing via pgAdmin or similar. Should this be a TODO? Yes please. Added to TODO: * Add pg_ctl option to do a syntax check of postgresql.conf -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Alvaro Herrera [EMAIL PROTECTED] writes: Josh Berkus wrote: Ideally, of course, there would be no wal_buffers setting, and WAL buffers would be allocated from shared_buffers pool on demand... Same for pg_subtrans, pg_clog, etc (as previously discussed) I agree with that for pg_clog and friends, but I'm much more leery of folding WAL into the same framework. Its access pattern is *totally* unlike standard caches, so the argument that this would be good for performance is resting on nothing but imagination. Also I'm concerned about possible deadlocks, because WAL is customarily accessed while holding one or more exclusive buffer locks. 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] How to Sponsor a Feature
David Fetter wrote: Folks, I forgot to post the fact that I'd put up a http://wiki.postgresql.org/wiki/How_to_sponsor_a_feature, per my TODO from the developers' meeting in Ottawa. Sorry about that. Anyhow, Jignesh has come up with a proposal template http://wiki.postgresql.org/wiki/ProposalTemplate that could use a once-over, too. Comments? Questions? Brickbats? Who is the intended audience for this? Many potential sponsors will surely have no idea what CVS tip or context-style diffs are. If we want to help people to sponsor features, then I think we need to deal with subjects like finding someone to undertake the development, the sponsor's relationship with the developer, methods and times of payment, etc. as well as expected interaction with the community on the part of both the sponsor and the developer. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to Sponsor a Feature
On Wed, 11 Jun 2008, Andrew Dunstan wrote: If we want to help people to sponsor features, then I think we need to deal with subjects like finding someone to undertake the development, the sponsor's relationship with the developer, methods and times of payment, etc. The bit on the wiki is helpful for developers trying to get a new feature implemented but I think that's where its scope ends. There seem to be occasional person wandering by here that it really doesn't help though. Periodically you'll see I want feature $X in PostgreSQL. I'm willing to help fund it. What do I do?. In most of those that have wandered by recently, $X is a known feature any number of other people want. Good sample cases here are recent requests to help fund or implement materialized views, supporting queries on read-only slaves, and SQL window support. I don't think these people need guidance on how to manage the project, they need some sort of way to feel comfortable saying will pledge $Y for feature $X in a way that makes sense on both sides. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: * Can we build a configuration wizard to tell newbies what settings they need to tweak? That would trump all the other suggestions conclusively. Anyone good at expert systems? How far could we get with the answers to just three questions: * How many concurrent queries do you expect to have? * How much RAM space are you willing to let Postgres use? * How much overhead disk space are you willing to let Postgres use? concurrent queries drives max_connections, obviously, and RAM space would drive shared_buffers and effective_cache_size, and both of them would be needed to size work_mem. The third one is a bit weird but I don't see any other good way to set the checkpoint parameters. If those aren't enough questions, what else must we ask? Or maybe they aren't the right questions at all --- maybe we should ask is this a dedicated machine or not and try to extrapolate everything else from what we (hopefully) can find out about the hardware. Having returned from Japan, I read through this thread. It had lots of ideas (new format for postgresql.conf, more/less comments in postgresql.conf) but I didn't see any of the ideas getting a majority. I think we do a good job of making many settings automatic (meaning no one even sees them), but we don't to a great job of making the visible settings easy to set, both in the process (no GUI) and in knowing the proper value. There are two ideas I did think had merit. First, using ## for system-supplied comments, so user comments would be easier to identify. There might be value in doing that even if it were not helpful for scripts. The second idea is the idea of having one parameter depend on another. Not only could we do that for some of our existing parameters, but we could have pseudo-parameters like concurrent_queries, memory_usage, and extra_disk_space that could be at the top of postgresql.conf and then affect the other settings. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Adding more context to tuptoaster's elog messages
Reflecting on this thread: http://archives.postgresql.org/pgsql-general/2008-06/msg00344.php it strikes me that the elog messages in tuptoaster.c would be significantly more useful if they gave the name of the toast table containing the problem, which is readily available at the sites of the elog calls. Any objections? Should I back-patch that, or just do it in HEAD? (Since these are elog's not ereport's, there's no translation impact from choosing to back-patch. I agree with their being elog's because they should be can't-happen cases, but when they do happen ...) 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] .psqlrc output for \pset commands
In my .psqlrc I have: \pset format wrapped and this outputs this on psql startup: $ psql test -- Output format is wrapped. psql (8.4devel) Type help for help. Is this desirable? \set QUIET at the top of .psqlrc fixes it, but I am wondering if we should be automatically doing quiet while .psqlrc is processed. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Bruce Momjian [EMAIL PROTECTED] writes: The second idea is the idea of having one parameter depend on another. Not only could we do that for some of our existing parameters, but we could have pseudo-parameters like concurrent_queries, memory_usage, and extra_disk_space that could be at the top of postgresql.conf and then affect the other settings. We have tried to do that in the past, and it didn't work well *at all*. The idea has a fundamental logical flaw, which is that it's not clear which parameter wins if the user changes both. 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] Overhauling GUCS
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: The second idea is the idea of having one parameter depend on another. Not only could we do that for some of our existing parameters, but we could have pseudo-parameters like concurrent_queries, memory_usage, and extra_disk_space that could be at the top of postgresql.conf and then affect the other settings. We have tried to do that in the past, and it didn't work well *at all*. We have? When? The idea has a fundamental logical flaw, which is that it's not clear which parameter wins if the user changes both. Yes, you could get into problems by having variable dependency loops, but I see no way to easily improve configuration without it. My idea was to have: memory_usage = 128MB## pseudo-parameter shared_buffers = $memory_usage * 0.75 -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent 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: Multiversion page api (inplace upgrade)
Tom Lane [EMAIL PROTECTED] writes: (Likely counterexample: adding collation info to text values.) I don't think the argument really needs an example, but I would be pretty upset if we proposed tagging every text datum with a collation. Encoding perhaps, though that seems like a bad idea to me on performance grounds, but collation is not a property of the data at all. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .psqlrc output for \pset commands
Bruce Momjian [EMAIL PROTECTED] writes: In my .psqlrc I have: \pset format wrapped and this outputs this on psql startup: $ psql test -- Output format is wrapped. psql (8.4devel) Type help for help. Is this desirable? \set QUIET at the top of .psqlrc fixes it, but I am wondering if we should be automatically doing quiet while .psqlrc is processed. I was wondering about this myself, but I'm still not used to the new banner. It seems kind of... curt. Perhaps it should just be a single line instead of two lines both around 20 characters... Anyways the thing that struck me as odd was the messages appearing *before* the header. It seems to me the header should print followed by .psqlrc output followed by normal output. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Tom Lane [EMAIL PROTECTED] writes: Alvaro Herrera [EMAIL PROTECTED] writes: Josh Berkus wrote: Ideally, of course, there would be no wal_buffers setting, and WAL buffers would be allocated from shared_buffers pool on demand... Same for pg_subtrans, pg_clog, etc (as previously discussed) I agree with that for pg_clog and friends, but I'm much more leery of folding WAL into the same framework. Its access pattern is *totally* unlike standard caches, so the argument that this would be good for performance is resting on nothing but imagination. Also I'm concerned about possible deadlocks, because WAL is customarily accessed while holding one or more exclusive buffer locks. Well it may still be worthwhile stealing buffers from shared_buffers even if we set a special flag marking them as owned by WAL and out of bounds for the normal buffer manager. At least that way we could always steal more if we want or return some, as long as we're careful about when we do it. That would open the door to having these parameters be dynamically adjustable. That alone would be worthwhile even if we bypass all bells and whistles of the buffer manager. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA 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] Overhauling GUCS
Greg, At least that way we could always steal more if we want or return some, as long as we're careful about when we do it. That would open the door to having these parameters be dynamically adjustable. That alone would be worthwhile even if we bypass all bells and whistles of the buffer manager. One hitch, though, is that asynchronous commit could consume big chunks of shared_buffers. So we might still need a limit for people who are using async. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cannot use result of (insert..returning)
Heikki Linnakangas wrote: dvs wrote: Hello, I need to use query like: select (insert into test (a) values (x) returning b),c from anytable where condition but it say ERROR: syntax error at or near into Is this a bug? No, it's a known limitation. Is there a TODO item for this? I don't see one, do you? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Magnus Hagander wrote: Bruce Momjian wrote: Joshua D. Drake wrote: On Wed, 2008-06-11 at 11:45 -0400, Bruce Momjian wrote: Greg Sabino Mullane wrote: * The word 'paramters' is still misspelled. :) Corrected for 8.4. Technically this is a bug fix... why not backpatch it too? That might show up as a diff for people doing upgrades where the minor version changed the spelling. People upgrading won't see it, I think. You only see it when you do a new initdb.. The problem is that people doing initdb with different minor versions will have different stock postgresql.conf files. That isn't a huge problem, but I don't see a need to create the problem just to fix a spelling mistake that few have observed. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: The second idea is the idea of having one parameter depend on another. We have tried to do that in the past, and it didn't work well *at all*. We have? When? Just a couple months ago we had to give up enforcing an interrelationship between NBuffers and MaxConnections, because it got too complicated and un-explainable. I seem to recall some other interactions in the distant past, but a quick look through the CVS logs didn't find any smoking guns. The idea has a fundamental logical flaw, which is that it's not clear which parameter wins if the user changes both. Yes, you could get into problems by having variable dependency loops, Who said anything about loops? What I am talking about is what happens during set memory_usage = X; // implicitly sets work_mem = X/100, say set work_mem = Y; set memory_usage = Z; What is work_mem now, and what's your excuse for saying so, and how will you document the behavior so that users can understand it? (Just to make things interesting, assume that some of the above SETs happen via changing postgresql.conf rather than directly.) If the objective is to make configuration easier to understand, I don't believe that behind-the-scenes changes of configuration values will advance that goal. but I see no way to easily improve configuration without it. The higher-level concepts should be things that a configuration wizard works with, and then tells you how to set the postmaster parameters. They should not end up in the configure file (unless maybe as comments?) 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] Overhauling GUCS
Josh Berkus [EMAIL PROTECTED] writes: Greg, At least that way we could always steal more if we want or return some, as long as we're careful about when we do it. That would open the door to having these parameters be dynamically adjustable. That alone would be worthwhile even if we bypass all bells and whistles of the buffer manager. One hitch, though, is that asynchronous commit could consume big chunks of shared_buffers. So we might still need a limit for people who are using async. Well currently we use a fixed number of fixed-sized buffers, no? I doubt we'll change that even if we take this tact of making wal_buffers resizable by stealing buffers from the buffer manager for precisely the reasons Tom was describing. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA 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] Overhauling GUCS
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: I agree with that for pg_clog and friends, but I'm much more leery of folding WAL into the same framework. Well it may still be worthwhile stealing buffers from shared_buffers even if we set a special flag marking them as owned by WAL and out of bounds for the normal buffer manager. At least that way we could always steal more if we want or return some, as long as we're careful about when we do it. ... and as long as you can acquire the WAL per-buffer management space out of nowhere ... 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] cannot use result of (insert..returning)
Bruce Momjian wrote: Heikki Linnakangas wrote: dvs wrote: Hello, I need to use query like: select (insert into test (a) values (x) returning b),c from anytable where condition but it say ERROR: syntax error at or near into Is this a bug? No, it's a known limitation. Is there a TODO item for this? I don't see one, do you? Allow INSERT/UPDATE ... RETURNING inside a SELECT 'FROM' clause http://archives.postgresql.org/pgsql-general/2006-09/msg00803.php http://archives.postgresql.org/pgsql-hackers/2006-10/msg00693.php ? 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: Multiversion page api (inplace upgrade)
Gregory Stark wrote: Tom Lane [EMAIL PROTECTED] writes: (Likely counterexample: adding collation info to text values.) I don't think the argument really needs an example, but I would be pretty upset if we proposed tagging every text datum with a collation. Encoding perhaps, though that seems like a bad idea to me on performance grounds, but collation is not a property of the data at all. Again not directly related to difficulties upgrading pages... The recent discussion ... http://archives.postgresql.org/pgsql-hackers/2008-06/msg00102.php ... mentions keeping collation information together with text data, however it is referring to keeping it together when processing it, not when storing the text. Regards, Stephen Denne. -- At the Datamail Group we value teamwork, respect, achievement, client focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by replying immediately, destroy it and do not copy, disclose or use it in any way. The Datamail Group, through our GoGreen programme, is committed to environmental sustainability. Help us in our efforts by not printing this email. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
On Wed, 11 Jun 2008, Tom Lane wrote: Who said anything about loops? What I am talking about is what happens during set memory_usage = X; // implicitly sets work_mem = X/100, say set work_mem = Y; set memory_usage = Z; What is work_mem now, and what's your excuse for saying so, and how will you document the behavior so that users can understand it? (Just to make things interesting, assume that some of the above SETs happen via changing postgresql.conf rather than directly.) People are already exposed to issues in this area via things like the include file mechanism. You can think of that two ways. You can say, there's already problems like this so who cares if there's another one. Or, you can say let's not add even more confusion like that. Having a mini programming language for setting parameters is interesting and all, and it might be enough to do a good job of handling the basic newbie setup chores. But I don't think it's a complete solution and therefore I find moving in that direction a bit of a distraction; your concerns about ambiguity just amplify that feeling. It's unlikely that will get powerful enough to enable the one true config file that just works for everybody. There's too many things that depend a bit on both data access pattern and on overall database size/structure no matter what you do. [If only there were some technology that did workload profiling and set the server parameters based on that. Some sort of dynamic tuning tool; wouldn't that be great? Oh well, that's just a dream right now I guess.] I'm not sure if I've stated this explicitly yet, but I personally have no interest in just solving the newbie problem. I want a tool to help out tuning medium to large installs, and generating a simple config file is absolutely something that should come out of that as a bonus. Anything that just targets the simple installs, though, I'm not very motivated to chase after. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .psqlrc output for \pset commands
Gregory Stark [EMAIL PROTECTED] writes: Anyways the thing that struck me as odd was the messages appearing *before* the header. It seems to me the header should print followed by .psqlrc output followed by normal output. I think the reason for the current behavior is to allow \set QUIET in .psqlrc to affect the printing of the banner. Are we prepared to give that up? 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] Overhauling GUCS
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: The idea has a fundamental logical flaw, which is that it's not clear which parameter wins if the user changes both. Yes, you could get into problems by having variable dependency loops, Who said anything about loops? What I am talking about is what happens during set memory_usage = X; // implicitly sets work_mem = X/100, say set work_mem = Y; set memory_usage = Z; My initial thought was that this would behave like a shell script variable, meaning once you set something it would affect all references to it below in postgresql.conf. The problem with that is that we comment out all settings, so there isn't a logical order like you would have in a shell script. I was not thinking of memory_usage implicity changing anything. I figured postgresql.conf would have: memory_usage = 100 work_mem = $memory_usage * 0.75 If you change memory_usage via SET, it will not change work_mem at all because you are not re-initializing the variables. I am kind of lost how this would work logically and am willing to think about it some more, but I do think we aren't going to simplify postgresql.conf without such a facility. The big problem I see is that right now everything has a constant default. If we allowed memory_usage to change some of the defaults, how would we signal that we want the variables based on it to change their values? This is your behind-the-scenes problem you mentioned. What is work_mem now, and what's your excuse for saying so, and how will you document the behavior so that users can understand it? (Just to make things interesting, assume that some of the above SETs happen via changing postgresql.conf rather than directly.) If the objective is to make configuration easier to understand, I don't believe that behind-the-scenes changes of configuration values will advance that goal. but I see no way to easily improve configuration without it. The higher-level concepts should be things that a configuration wizard works with, and then tells you how to set the postmaster parameters. They should not end up in the configure file (unless maybe as comments?) I am concerned that each wizzard is going to have to duplicate the same logic each time, and adjust to release-based changes. I thought having the configuration file itself help with setting some values would be helpful. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Greg Smith wrote: On Wed, 11 Jun 2008, Tom Lane wrote: Who said anything about loops? What I am talking about is what happens during set memory_usage = X; // implicitly sets work_mem = X/100, say set work_mem = Y; set memory_usage = Z; What is work_mem now, and what's your excuse for saying so, and how will you document the behavior so that users can understand it? (Just to make things interesting, assume that some of the above SETs happen via changing postgresql.conf rather than directly.) People are already exposed to issues in this area via things like the include file mechanism. You can think of that two ways. You can say, there's already problems like this so who cares if there's another one. Or, you can say let's not add even more confusion like that. Having a mini programming language for setting parameters is interesting and all, and it might be enough to do a good job of handling the basic newbie setup chores. But I don't think it's a complete solution and therefore I find moving in that direction a bit of a distraction; your concerns about ambiguity just amplify that feeling. It's unlikely that will get powerful enough to enable the one true config file that just works for everybody. There's too many things that depend a bit on both data access pattern and on overall database size/structure no matter what you do. You are right that the complete solution is going to have to ask users questions, and my idea of using variables is not going to get us that far. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
