of
cross-overs with the materialized view sub-project.
Could you say a little more about why you wanted to achieve this?
Best Regards
Simon Riggs
2nd Quadrant
+44-7900-255520
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Mark Kirkwood
Sent: Monday
Jan Wieck wrote:
have you read src/backend/storage/buffer/README of current CVS tip?
Tom Lane wrote:
Um, did you read the discussion of the ARC buffer management algorithm
that's already been implemented for 7.5?
Tom, Jan,
No, I hadn't read this. Thank you both for your time and
performance
tuning on DBT-3 with 7.5. Thanks again for adding the new algorithm.
Best Regards, Simon
...
Simon Riggs wrote:
...
My suggestion would be to:
- split the buffer cache into two, just as Oracle does: KEEP
RECYCLE.
This could default to KEEP=66% of total memory available
OK, I will attempt to draw together this information as currently
stands. If this makes any sense, we can discuss what the
requirement/process is for regular maintenance (daily/weekly/monthly
etc).
Understood to mean changes in next release (current progress) - items
that have been
Jan,
Happy to continue the discussion...though without changing my suggestion
that we defer any further more specialised improvements for now.
Jan Wieck replied to...
Simon Riggs wrote:
If we know ahead of time that a large scan is going to have this
effect,
why wait for the ARC to play
that exhibits the described properties and quantifying
the effects and their frequency. If it IS worth it, and I accept that it
may not be, I'll have a hack at the very specialised improvement I was
suggesting, for very specific workload types.
Best Regards
Simon Riggs
Tom Lane writes
In the second place, what the code is doing is dependent on an
understanding
of the semantics of IN; I'm not sure it's applicable to, say,
WHERE outervar ANY (SELECT innervar FROM ...)
and it's definitely not applicable to
WHERE outervar ALL (SELECT innervar
Tom Lane writes
In the second place, what the code is doing is dependent on an
understanding
of the semantics of IN; I'm not sure it's applicable to, say,
WHERE outervar ANY (SELECT innervar FROM ...)
and it's definitely not applicable to
WHERE outervar ALL (SELECT innervar
PROTECTED]
Subject: Re: [HACKERS] Recursive
optimization of IN subqueries
Simon Riggs wrote:
Tom Lane writesIn the second place, what the code is doing is dependent on anunderstandingof the semantics of IN; I'm not sure it's applicable to, say, WHERE outervar ANY (SELECT innervar FROM ...)and it's
Bruce Momjian wrote
Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
If the TODO-list-with-dash isn't the correct place to have looked,
is
there another list of committed changes for the next release?
We tend to rely on the CVS commit logs as the definitive source.
You
can
POSTGRESQL: Summary of Changes since last release (7.4.1)
--
26 Jan 2004
This is a summary of most changes since code versions marked 7_4_1,
rather than a weekly news bulletin, a summary of desired future items,
or the definitive list of
noise, poorly wired cabling and intermittently used shared SCSI...
Best of luck, Simon Riggs
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
without
having persistent bitmap indices. IMHO, the longer term goal of a good
star join plan is an important one - that may influence the design
selection for this discussion.
Hope some of that helps,
Best regards, Simon Riggs
---(end of broadcast
an awful hidden secret feature
either.
Best regards, Simon Riggs
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
raise the limit without changing everything else.
Best regards, Simon Riggs
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
be a
press-release: PostgreSQL community focuses new efforts towards
Robustness features for its next major release.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
handling it blind - without regard to its contents, as ARC
does now - that sounds expensive.
Hope some of that helps..
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send
behaviour regarding this point. Anybody?
Best regards, Simon Riggs
---(end of broadcast)---
TIP 8: explain analyze is your friend
is that they haven't been cast in stone yet...
-- Simon Riggs, [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can
my error, Simon Riggs
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Bruce Momjian
Simon Riggs wrote:
Tom Lane
Simon Riggs [EMAIL PROTECTED] writes:
Most importantly, other references I have state that: the ANSI
SQL-99
specification does require that if a statement errors then only
that
statement's changes are rolled back.
...if anybody
lock (and perhaps it
shouldn't be).
May not be the case...but the answer should be interesting.
Hope it helps, Simon Riggs
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs
, don't use the SELECT..FOR UPDATE metaphor, try another
design that doesn't require this style of locking. Application level
locking can get you round many problems - the database can't do
everything.
Best regards, Simon Riggs
---(end of broadcast
two best
angles of attack on the DBT-3 workload, IMHO.
I very much look forward to further news.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs
Tom Lane
Simon Riggs [EMAIL PROTECTED] writes:
Could I suggest that your next step is to sync up with the work
being
done on tuning the DBT-3 query workload? As I'm sure you're aware,
that
is very similar to TPC-H workload, where most of the commercial
RDBMS
vendors utilise Materialized
of a database without thinking...
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
and the time
spent would double other requirements. MySQL has multiple storage
managers and that must slow them down enormously trying to test or even
fine tune things.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 3: if posting/reading through
are not recoverable from backup?
- it might be desirable to allow recovery with less than all of the
original tablespces
- it might also be desirable to allow recovery when the tablespaces txn
Ids don't match (though that is forbidden on many other dbms)
Best Regards, Simon Riggs
Tom Lane [mailto:[EMAIL PROTECTED]
Simon Riggs [EMAIL PROTECTED] writes:
You're absolutely right about the not-knowing when you're out of
space
issue. However, if the xlog has been written then it is not
desirable,
but at least acceptable that the checkpoint/bgwriter cannot complete
.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
!
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Neil Conway
Simon Riggs wrote:
Josh Berkus wrote
Simon Riggs wrote
Please set WAL_DEBUG to 1 so we can see a bit more info: thanks.
I'm pretty sure that WAL_DEBUG requires a compile-time option.
I'm surprised, but you are right, the manual does SAY this requires
a
compile time
comments, Best Regards, Simon Riggs, 2nd Quadrant
Review of current Crash Recovery
Crash recovery is catered for by the use of WAL logging, or xlogs. xlogs
are
written to disk immediately before a transaction is acknowledged as
committed. xlogs contain REDO information sufficient to rollforward any
Bruce Momjian
Simon Riggs wrote:
User-selectable behaviour? OK. That's how we deal with fsync; I can
relate to that. That hadn't been part of my thinking because of the
importance I'd attached to the log files themselves, but I can go
with
that, if that's what was meant.
So, if we had
Joe Conway [mailto:[EMAIL PROTECTED]
Simon Riggs wrote:
Tom Lane [mailto:[EMAIL PROTECTED] That should be user-scriptable
policy, in my worldview.
O... and other dbms will freeze when this situation is hit, rather
than continue and drop archive logs.]
Been there, done that, don't see
Tom Lane
Simon Riggs [EMAIL PROTECTED] writes:
The behaviour I wish to add is:
Keep wal_debug as a value between 0 and 16.
If =0 then no debug output (default).
Use following bitmasks against the value
Mask 1 = XLOG Checkpoints get logged
Mask 2 = Archive API calls get logged
Mask 4
Andreas Pflug
Josh Berkus wrote:
Related to the above, what I don't see in your paper or the proposed
API
is a
way to coordinate full back-ups and WAL archiving. Obviously, the
PITR
Archive is only useful in reference to an existing full backup, so it
is
important to be able to associate a
...but there will be a list of credits somewhere down the line
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 8: explain analyze is your friend
Richard Huxton
On Monday 08 March 2004 23:28, Simon Riggs wrote:
PITR Functional Design v2 for 7.5
Review of current Crash Recovery
Is there any value in putting this section on techdocs or similar? We
do
get a
small but steady trickle of people asking for details on internals,
and I
Josh Berkus [mailto:[EMAIL PROTECTED]
First off, let me compliment you on such a thorough proposal. I'm
feeling very enthusiastic about 7.5 PITR
Thank you, though please realise that I am in many ways summarising a
wide range of suggestions and earlier work into a coherent whole.
Me too! I'm
for
full xlogs and then executes XLogArchiveNotify() for them?
Thoughts anyone?
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
From: Josh Berkus [mailto:[EMAIL PROTECTED]
SIGHUP - seems to allow different parameter settings in each backend
Nope. SIGHUP means that you need to send a HUP to the postmaster,
such
as
you would with changes to pg_hba.conf.
SUSET - maybe what you're looking for???
Yes.
to set it down as well as up, remember).
That way, the default behaviour improves even when the index stats
parameter is not actually set, yet is still controllable when you do.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 1: subscribe
being able to ask that question, if
that is a business requirement.
Best regards, Simon Riggs
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
. Not
uncommon, I grant you, but in the circumstances I didn't really need the
extra recovery practice...
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
security in just the
same way other systems already do.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Bruce Momjian wrote
Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
[ expecting to finish PITR by early June ]
Is this all still OK for 7.5? (My attempts at cataloguing
changes has
fallen by the wayside in concentrating on the more
important task of
PITR.) Do we have
.
This mode requires more thought, but is certainly possible, in time.
Best Regards
Simon Riggs
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
that in a script if they choose.
Internally, we could then implement it however we chose.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Bruce Momjian
Simon Riggs wrote:
The function to kill backend seems no longer in doubt, but the
*reason* is still blurred, other than we don't want to
bring down the
postmaster to do this.
So far, reasons given have been:
1. to kill idlers
2. to kill runaway queries/statements
concepts
from other industry areas are probably more important to the success of
pg than conformance to internet/LINUX norms.]
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
.
Sounds great. That would save me looking further into it, so I will be
happy to read drafts and ask more detailed questions based upon what you
find, or be willing to look at specific parts if you want to chunk out
the work. I'm sure you'll get lets of support.
Best regards, Simon Riggs
in the
WAL file and choose the xid for recovery.
Bruce, As I started this e-mail (1st time), I completely agreed with
you. I've now had to switch my thinking.
(Doesn't effect archiving architecture)
I'm a little dazedcomments anyone?
Best regards, Simon Riggs
have answers, but I strive for the best answer.
Thanks very much, Best regards, Simon Riggs
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
On Tue, 2004-05-11 at 21:29, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
Currently, recovery loops until end of xlogs. There is no exit condition
from the loop. There is not currently a timestamp on the xlogs -
anywhere apart from the file date on each xlog.
Sure
On Tue, 2004-05-11 at 22:15, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
I need to send a signal from a backend to the archiver process.
1. What signal should I use?
SIGUSR1 or SIGUSR2 would be the safest choices.
2. How do I give the processid of the archiver
On Tue, 2004-05-11 at 22:01, Bruce Momjian wrote:
Anyway, I though we agreed to just get total recovery working for 7.5
and we can deal with recovery to a particular point later.
Back on target now! Regards, Simon
---(end of broadcast)---
TIP 1:
Thanks to all of you for such swift advice and correction!
Have a good evening...
On Tue, 2004-05-11 at 22:26, Alvaro Herrera wrote:
On Tue, May 11, 2004 at 09:25:37PM +0100, Simon Riggs wrote:
On Tue, 2004-05-11 at 16:33, Bruce Momjian wrote:
Tom Lane wrote:
Alvaro Herrera [EMAIL
clear for me.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
X, but do not include this
one. Does that make sense? Does everything we have support that?
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
and make any
comments or questions now...time for rework is now slipping fast.
Best Regards, Simon Riggs, 2nd Quadrant
...detail chatter follows
On Thu, 2004-05-06 at 05:38, Bruce Momjian wrote:
Simon Riggs wrote:
Bruce, was this OK with you...shall I post?
Some items occurred to me during
, what is the benefit of using NFS?
PostgreSQL offers client/server access - so why not use that instead?
Best Regards,
Simon Riggs
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
On Fri, 2004-04-30 at 04:02, Bruce Momjian wrote:
Simon Riggs wrote:
Agreed we want to allow the superuser control over writing of the
archive logs. The question is how do they get access to that. Is it by
running a client program continuously or calling an interface script
from
to an arbitrary point but have a strained
implementation that we have to revisit for 7.6.
Interesting thought, I see now your priorities.
Will read and digest over next few days.
Thanks for your help and attention,
Best regards, Simon Riggs
---(end of broadcast
sequence.
i.e. my thinking was about a way to keep logical looking more like
physical, in certain situations.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister
.
Best Regards, Simon Riggs, 2ndQuadrant
http://www.2ndquadrant.com
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
, Simon Riggs, 2ndQuadrant
http://www.2ndquadrant.com
---(end of broadcast)---
TIP 8: explain analyze is your friend
sets too solid.
Regards, Simon
On Mon, 2004-04-26 at 17:48, Bruce Momjian wrote:
I want to come hug you --- where do you live? !!!
:-)
---
Simon Riggs wrote:
I've now completed the coding of Phase 1 of PITR
On Mon, 2004-04-26 at 16:37, Simon Riggs wrote:
I've now completed the coding of Phase 1 of PITR.
This allows a backup to be recovered and then rolled forward (all the
way) on transaction logs. This proves the code and the design works, but
also validates a lot of the earlier assumptions
On Mon, 2004-04-26 at 22:05, Bruce Momjian wrote:
Simon Riggs wrote:
Transaction log files currently have timestamps, so that is
straightforward, but probably not the best we can do. We would
rollforward until the xlog file time desired point in time.
To make (2) work we would have
On Mon, 2004-04-26 at 23:01, Alvaro Herrera wrote:
On Mon, Apr 26, 2004 at 05:05:41PM -0400, Bruce Momjian wrote:
Simon Riggs wrote:
Transaction log files currently have timestamps, so that is
straightforward, but probably not the best we can do. We would
rollforward until the xlog
On Mon, 2004-04-26 at 18:08, Bruce Momjian wrote:
Simon Riggs wrote:
Well, I guess I was fairly happy too :-)
YES!
I'd be more comfortable if I'd found more bugs though, but I'm sure the
kind folk on this list will see that wish of mine comes true!
The code is in a needs more
On Tue, 2004-04-27 at 10:38, Richard Huxton wrote:
On Tuesday 27 April 2004 00:32, Bruce Momjian wrote:
Simon Riggs wrote:
On Mon, 2004-04-26 at 23:01, Alvaro Herrera wrote:
On Mon, Apr 26, 2004 at 05:05:41PM -0400, Bruce Momjian wrote:
I was thinking --- how would someone know
On Tue, 2004-04-27 at 08:56, Hans-Jürgen Schönig wrote:
Simon Riggs wrote:
Since Phase1 is functioning and should hopefully soon complete, we can
now start thinking about Phase 2: full recovery to a point-in-time.
Previous thinking was that a command line switch would be used
irrelevant to me.
For long running transactions where you want to recover as much as possible,
one might also want to recover up until just before a specific transaction
committed (as opposed to started).
Sounds like the difference between and =, so should be possible...
Best Regards, Simon
On Tue, 2004-04-27 at 18:10, Peter Eisentraut wrote:
Simon Riggs wrote:
New utility aimed at being located in src/bin/pg_arch
Why isn't the archiver process integrated into the server?
Number of reasons
Overall, I initially favoured the archiver as another special backend,
like
On Mon, 2004-04-26 at 17:24, Manfred Koizar wrote:
On Mon, 26 Apr 2004 14:29:58 +0100, Simon Riggs [EMAIL PROTECTED]
wrote:
Now that FSM
covers free btree index pages this access pattern might be highly
nonsequential.
I had considered implementing a mode where the index doesn't keep
to a PIT directly
3. Recovery to a named checkpoint
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
On Tue, 2004-04-27 at 21:56, scott.marlowe wrote:
On Mon, 26 Apr 2004, Andrew Payne wrote:
Bruce asked an excellent question:
My question is, What can we learn from MySQL? I don't know there is
anything, but I think it makes sense to ask the question.
Ignore the opposition
.
PostgreSQL already has crash recovery...this is for restore from backup
scenarios.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do
On Tue, 2004-04-27 at 23:11, Rod Taylor wrote:
On Tue, 2004-04-27 at 17:36, Simon Riggs wrote:
On Tue, 2004-04-27 at 21:56, Rod Taylor wrote:
Overall, I'd refer back to the points Bruce raised - you certainly do
need a way of finding out the time to recover to, and as others have
On Tue, 2004-04-27 at 23:47, Jonathan Gardner wrote:
I've just discovered OLAP and it looks like a competing technology with
materialized views.
Yes. Read up some more, but don't get sucked in by the marketing.
In a nutshell, OLAP seems to be pre-storing the results of potential
queries.
.
I'll be around daily to answer queries and fix reported bugs.
Thanks,
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 8: explain analyze is your friend
On Wed, 2004-04-28 at 16:14, Peter Eisentraut wrote:
Am Tuesday 27 April 2004 19:59 schrieb Bruce Momjian:
Peter Eisentraut wrote:
Simon Riggs wrote:
New utility aimed at being located in src/bin/pg_arch
Why isn't the archiver process integrated into the server?
I think
On Wed, 2004-04-28 at 05:00, Bruce Momjian wrote:
Simon Riggs wrote:
On Tue, 2004-04-27 at 21:56, Rod Taylor wrote:
Overall, I'd refer back to the points Bruce raised - you certainly do
need a way of finding out the time to recover to, and as others have
said also, time isn't
On Wed, 2004-04-28 at 18:35, Andrew Dunstan wrote:
Simon Riggs wrote:
On Wed, 2004-04-28 at 05:00, Bruce Momjian wrote:
What if we added transaction id to log_line_prefix? The user could then
log all queries and find the xid where they want to stop, but of course
that assumes
On Thu, 2004-04-29 at 16:09, Peter Eisentraut wrote:
Simon Riggs wrote:
I'd suggest extending the CHECKPOINT command so you can say:
CHECKPOINT text message
e.g. CHECKPOINT 'starting payroll Feb04';
(I'm sure some other DBMS does this...head spinning can;t recall...)
the text could just
- and in any
case any practically focused API has a reference port as a way of
showing how to use it and exposing any bugs in the server side
implementation.
The point is...everybody is now empowered to write tape drive code,
whatever you fancy go do.
Best regards, Simon Riggs
On Thu, 2004-04-29 at 20:24, Bruce Momjian wrote:
Simon Riggs wrote:
The archiver should be able to do a whole range of things. Basically,
that point was discussed and the agreed approach was to provide an API
that would allow anybody and everybody to write whatever they wanted
documentation
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
tripped over on various issues, nor
even 1 press-worthy user was unable to recover correctly.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do
, then /bin/sync:
what percentage of the device's erase blocks get hit?
Thanks,
Simon Riggs
2nd Quadrant
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
that this should be a session settable parameter, to allow
session transaction semantics to mimic particular DBMS.
I want the behaviour but not the effort...
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
, how long has PITR been
brewing? And, who thinks that we'll get increased adoption without the
big ticket items? (Even if your opinion is that PITR isn't one of them).
I can't complete by 1 June. Think worse of me if you choose.
Best Regards, Simon Riggs
---(end
freeze.
...as-early-as-possible is understood...
Best Regards, Simon Riggs, 2nd Quadrant
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
data in the event of a crash...
i.e. default=NOLOGGING on all statements
Best regards, Simon Riggs
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
might have helped all of us to understand the
impact of an extra weeks discussion etc..
Personally, I feel I had good notice, but that doesn't mean it was
possible for me to finish by that time...
Best Regards, Simon Riggs
---(end of broadcast
recover-to-a point scenario. It
*MIGHT* make it into this release, if we get the other stuff done first.
I think we need to explore the procedures we are going to use for PITR.
Much of that has already been discussed.
Best Regards, Simon Riggs
---(end of broadcast
records, but please lets wait awhile...
Not that I like neither of those ideas really ... issuing normal WAL
index creation traffic if PITR is active is certainly the easiest way.
I agree, certainly for now.
Best Regards, Simon Riggs
---(end of broadcast
to be obsolete in 7.5, but its still much
needed advice for anybody below that level.
That would also remind people to upgrade??
Just and idea...
Best regards, Simon Riggs
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ
1 - 100 of 8408 matches
Mail list logo
