And then what? Make the search box on www.postgresql.org able to handle an
email address as search text without throwing a shoe?
Search for [EMAIL PROTECTED] or any other 'email' address from the postgres
home page. Barfage every time.
Easy for some isn't easy for all, apparently. Left that out
Simon,
You may know we've built something similar and have seen similar gains.
We're planning a modification that I think you should consider: when there
is a sequential scan of a table larger than the size of shared_buffers, we
are allowing the scan to write through the shared_buffers cache.
The
"Simon Riggs" <[EMAIL PROTECTED]> wrote:
> > > With the default
> > > value of scan_recycle_buffers(=0), VACUUM seems to use all of buffers in
> > > pool,
> > > just like existing sequential scans. Is this intended?
> >
> New test version enclosed, where scan_recycle_buffers = 0 doesn't change
Tom Lane <[EMAIL PROTECTED]> wrote:
> Who said anything about external schedulers? I remind you that this is
> AUTOvacuum. If you want to implement manual scheduling you can still
> use plain 'ol vacuum commands.
I think we can split autovacuum into two (or more?) functions:
task gatherers and
ITAGAKI Takahiro <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> wrote:
>> In any case, I still haven't seen a good case made why a global work
>> queue will provide better behavior than each worker keeping a local
>> queue.
> If we have some external vacuum schedulers, we need to see a
Tom Lane <[EMAIL PROTECTED]> wrote:
> In any case, I still haven't seen a good case made why a global work
> queue will provide better behavior than each worker keeping a local
> queue. The need for small "hot" tables to be visited more often than
> big tables suggests to me that a global queue w
Greg Smith <[EMAIL PROTECTED]> writes:
> Here's what I get as statistics on the buffer pool after a
> scan when the server is "happy", from a run with 20 clients:
> writes=38.3MB (8.2%) pinned+used=38.3MB (8.2%)
> dirty buffer usage count histogram:
> 0=0.1% 1=0.3% 2=26% 3=17% 4=21% 5+=36%
Inter
Galy Lee <[EMAIL PROTECTED]> writes:
> We can use the fix-size share memory to maintain such a queue. The
> maximum task size is the number of all tables. So the size of the queue
> can be the same with max_fsm_relations which is usually larger than the
> numbers of tables and indexes in the cluste
On Fri, 9 Mar 2007, Tom Lane wrote:
It strikes me that the patch would be more useful if it produced a
histogram of the observed usage_counts
Don't have something worth releasing yet, but I did code a first rev of
this today. The results are quite instructive and it's well worth looking
at.
Gregory Stark wrote:
"Richard Huxton" writes:
Well the cost depends on where/how complex the extra fields are. If you're just
talking about adding columns usercol01..NN with different types and possibly a
lookup to a single client_attributes table, it's not difficult.
And then what? dynamica
Hi, Alvaro
Alvaro Herrera wrote:
> keep such a task list in shared memory, because we aren't able to grow
> that memory after postmaster start.
We can use the fix-size share memory to maintain such a queue. The
maximum task size is the number of all tables. So the size of the queue
can be the sa
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> 1. Grab the AutovacSchedule LWLock exclusively.
>> 2. Check to see if another worker is currently processing
>> that table; if so drop LWLock and go to next list entry.
>> 3. Recompute whether table needs vacuuming; if not,
>> drop LWL
> And then what? dynamically construct all your SQL queries?
> Sure, sounds like a simple solution to me...
Not to mention DB security issues. How do you secure your database when
your web client has DDL access?
So, Edward, the really *interesting* idea would be to come up with a
secure, norm
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> In each chain, there must be at least one non-dead tuple with xmin <
> Oldestxmin.
Huh? Typically *all* the tuples but the last are dead, for varying
values of "dead". Please be more specific what you mean.
regards, tom l
Gregory Stark <[EMAIL PROTECTED]> writes:
> Huh, it occurs to me that I haven't seen any plperl regression tests fly by
> when I've been running regression tests myself. What do I have to do to test
> if plperl, plpython, etc work with the packed varlena patch?
cd to $TOP/src/pl, run "make install
Gregory Stark wrote:
> "Tom Lane" <[EMAIL PROTECTED]> writes:
>
>> [EMAIL PROTECTED] (Peter Eisentraut) writes:
>>> Make configuration parameters fall back to their default values when
>>> they
>>> are removed from the configuration file.
>>
>> It appears that this patch has broken custom GUC varia
Josh Berkus writes:
> Why is a function which returns void returning a row?
Returning a scalar result that happens to be of type VOID is an entirely
different thing from returning a set result that contains no rows.
> Why is that row
> NULL if it's a SQL function and empty if it's a PLPGSQL fun
"Tom Lane" <[EMAIL PROTECTED]> writes:
> [EMAIL PROTECTED] (Peter Eisentraut) writes:
>> Make configuration parameters fall back to their default values when they
>> are removed from the configuration file.
>
> It appears that this patch has broken custom GUC variables; at the very
> least it's br
"Richard Huxton" writes:
> Well the cost depends on where/how complex the extra fields are. If you're
> just
> talking about adding columns usercol01..NN with different types and possibly a
> lookup to a single client_attributes table, it's not difficult.
And then what? dynamically construct al
Josh Berkus writes:
> One of the Sun benchmarking guys ran across this issue, using a fairly
> recent (within the last 4 weeks) 8.3 snapshot. It appears to me that the
> 8.3 planner is unable to tell that, if the value for the first column of
> an index scan is a constant, the second column do
[EMAIL PROTECTED] (Peter Eisentraut) writes:
> Make configuration parameters fall back to their default values when they
> are removed from the configuration file.
It appears that this patch has broken custom GUC variables; at the very
least it's broken plperl.
regards, to
Andrew Hammond wrote:
On 3/12/07, Richard Huxton wrote:
Josh Berkus wrote:
> I really don't see any way you could implement UDFs other than EAV that
> wouldn't be immensely awkward, or result in executing DDL at runtime.
What's so horrible about DDL at runtime? Obviously, you're only going to
On 3/12/07, Richard Huxton wrote:
Josh Berkus wrote:
> I really don't see any way you could implement UDFs other than EAV that
> wouldn't be immensely awkward, or result in executing DDL at runtime.
What's so horrible about DDL at runtime? Obviously, you're only going to
allow specific addition
Josh Berkus wrote:
I really don't see any way you could implement UDFs other than EAV that
wouldn't be immensely awkward, or result in executing DDL at runtime.
What's so horrible about DDL at runtime? Obviously, you're only going to
allow specific additions to specific schemas/tables, but why
Josh Berkus wrote:
> Amdrew,
>
>> I have yet to encounter one, but
>> Josh has more experience, and more varied experience than I do. To me,
>> EAV is a perfect example of ignoring the YAGNI principal.
>
> I've done plenty of applications where part of the specification for the
> application was
Amdrew,
> I have yet to encounter one, but
> Josh has more experience, and more varied experience than I do. To me,
> EAV is a perfect example of ignoring the YAGNI principal.
I've done plenty of applications where part of the specification for the
application was "User Defined Fields" allowing
On Mar 11, 12:47 pm, [EMAIL PROTECTED] (Josh Berkus) wrote:
> No matter how much Heikki hates them, I think he'd agree that EAV tables are
> better than having the application execute DDL at runtime.
EAV moves the structure that is typically in the design of the tables
into the contents of the tab
"Josh Berkus" writes:
> postgres=# select * from void_func2(19);
> void_func2
>
>
> (1 row)
>
>
> postgres=# select void_func2(19) is null;
> ?column?
> --
> f
> (1 row)
>
>
> Why is a function which returns void returning a row? Why is that row
> NULL if it's a SQL fu
All,
One of the Sun benchmarking guys ran across this issue, using a fairly
recent (within the last 4 weeks) 8.3 snapshot. It appears to me that the
8.3 planner is unable to tell that, if the value for the first column of
an index scan is a constant, the second column doesn't need to be sorted
Folks,
This seems wrong to me:
postgres=# create table test1 ( testy int );
CREATE TABLE
postgres=# insert into test1 values ( 5 );
INSERT 0 1
postgres=# create function void_func ( IN theval int ) returns void as $f$
postgres$# update test1 set testy = $1;
postgres$# $f$ language sql;
CREATE FU
Heikki Linnakangas wrote:
Tom Lane wrote:
The reason it's not trivial is that you also have to preserve the t_ctid
links of update chains. If you look into VACUUM FULL, a very large part
of its complexity is that it moves update chains as a unit to make that
possible. (BTW, I believe the probl
Tom Lane wrote:
> I suggest that maybe we don't need exposed TODO lists at all. Rather
> the workers could have internal TODO lists that are priority-sorted
> in some way, and expose only their current table OID in shared memory.
> Then the algorithm for processing each table in your list is
>
>
Alvaro Herrera wrote:
> worker to-do list
> -
>
> When each worker starts, it determines which tables to process in the
> usual fashion: get pg_autovacuum and pgstat data and compute the
> equations.
>
> The worker then takes a "snapshot" of what's currently going on in the
> dat
Galy Lee wrote:
>
> Alvaro Herrera wrote:
> >worker to-do list
> >-
> >It removes from its to-do list the tables being processed. Finally, it
> >writes the list to disk.
>
> I am worrying about the worker-to-do-list in your proposal. I think
> worker isn't suitable to maintain an
On Mon, 2007-03-12 at 10:30 -0400, Tom Lane wrote:
> ITAGAKI Takahiro <[EMAIL PROTECTED]> writes:
> > I tested your patch with VACUUM FREEZE. The performance was improved when
> > I set scan_recycle_buffers > 32. I used VACUUM FREEZE to increase WAL
> > traffic,
> > but this patch should be useful
"Jonathan Scher" <[EMAIL PROTECTED]> writes:
> I attach gdb to /usr/.../bin/postgres, then I put a breakpoint. Whenever
> postgres stop on that breakpoint, it just kills/restarts the server instead of
> asking me what to do.
Not sure why it's crashing but you don't want to run postgres itself un
CAJ CAJ wrote:
Thanks for the response. This'd be wonderful if I can get my process
right. My assumptions (probably incorrect) are that pgdump has to be
excuted twice on a database with blobs. Once to get the data and once
to get the blob (using the -b flag).
Why do you assume that? T
Hello!
I'm trying to add some features to PostgreSQL, but I just can't figure
out how to make gdb work with it.
I attach gdb to /usr/.../bin/postgres, then I put a breakpoint. Whenever
postgres stop on that breakpoint, it just kills/restarts the server
instead of asking me what to do.
It's
> What is the fastest way to upgrade postgres for large databases that
> has binary objects?
Your procedure dumps and restore the databases twice. This seems less
than sound. My prediction is that you could get a 50% speed improvement
by fixing that ...
Thanks for the response. This'd be wo
On Mon, 2007-03-12 at 08:42 -0700, Luke Lonergan wrote:
> On 3/12/07 6:21 AM, "Simon Riggs" <[EMAIL PROTECTED]> wrote:
>
> > So based on those thoughts, sync_scan_offset should be fixed at 16,
> > rather than being variable. In addition, ss_report_loc() should only
> > report its position every 1
Simon,
On 3/12/07 6:21 AM, "Simon Riggs" <[EMAIL PROTECTED]> wrote:
> So based on those thoughts, sync_scan_offset should be fixed at 16,
> rather than being variable. In addition, ss_report_loc() should only
> report its position every 16 blocks, rather than do this every time,
> which will redu
CAJ CAJ wrote:
Hello,
I didn't get any response on the GENERAL list so i'm escalating this
We have several independent database servers with ~50GB+ databases
running postgres 8.0.x. We are planning to upgrade these databases to
postgres 8.2.x over the weekend
We plan to use the followi
On Saturday 10 March 2007 00:13, NikhilS wrote:
> Hi,
>
> > > Given that Simon wants to do away with having the master table APPENDed
> >
> > in
> >
> > > the planning phase, this would be better.
> >
> > ISTM you're trading appending the master table for appending the DUMP
> > partition, which afa
Galy Lee <[EMAIL PROTECTED]> writes:
> I am worrying about the worker-to-do-list in your proposal. I think
> worker isn't suitable to maintain any vacuum task list; instead
> it is better to maintain a unified vacuum task queue on autovacuum share
> memory.
Shared memory is fixed-size.
ITAGAKI Takahiro <[EMAIL PROTECTED]> writes:
> I tested your patch with VACUUM FREEZE. The performance was improved when
> I set scan_recycle_buffers > 32. I used VACUUM FREEZE to increase WAL traffic,
> but this patch should be useful for normal VACUUMs with backgrond jobs!
Proving that you can s
On Fri, 2007-03-02 at 15:03 -0800, Jeff Davis wrote:
> Is there any consensus about whether to include these two parameters as
> GUCs or constants if my patch is to be accepted?
>
> (1) sync_scan_threshold: Use synchronized scanning for tables greater
> than this many pages; smaller tables will no
Simon Riggs wrote:
Better thought: say that CLUSTER requires an "order-defining index".
That better explains the point that it is the table being clustered,
using the index to define the physical order of the rows in the heap. We
then use the word "clustered" to refer to what has happened to the
Alvaro Herrera wrote:
>worker to-do list
>-
>It removes from its to-do list the tables being processed. Finally, it
>writes the list to disk.
I am worrying about the worker-to-do-list in your proposal. I think
worker isn't suitable to maintain any vacuum task list; instead
it is
On Mon, 2007-03-12 at 09:14 +, Simon Riggs wrote:
> On Mon, 2007-03-12 at 16:21 +0900, ITAGAKI Takahiro wrote:
> > With the default
> > value of scan_recycle_buffers(=0), VACUUM seems to use all of buffers in
> > pool,
> > just like existing sequential scans. Is this intended?
>
> Yes, but i
On Mon, 2007-03-12 at 16:21 +0900, ITAGAKI Takahiro wrote:
> "Simon Riggs" <[EMAIL PROTECTED]> wrote:
>
> > I've implemented buffer recycling, as previously described, patch being
> > posted now to -patches as "scan_recycle_buffers".
> >
> > - for VACUUMs of any size, with the objective of reduci
50 matches
Mail list logo