Please see HOT WIP patch, version 4.1 posted on -patches.
here are not any significant changes since the version 4.0 patch that
I posted a week back.
This patch includes some optimizations for efficiently looking
up LP_DELETEd tuples. I have used the recent changes made by
Tom/Heikki which
Hi,
Maybe, the dummy entry could be extended to contain the bounds (max/min) for
each of the other involved partitions and they could be updated each time a
DML happens across the partitions. That ways, an update to a particular
partition needs to lock out the others, examine the dummy entries
Hi,
There are 2 other reasons to favor triggers though:
1) People (Josh Drake comes to mind) have found that if you get over
a tiny number of partitions, the performance of rules is abysmal.
2) I believe it should be possible to construct an update trigger
that allows you to perform updates
Tom Lane wrote:
The point of adding is_top_level is to provide a simpler, more reliable
means for PreventTransactionChain and related functions to detect
whether a function is trying to invoke a non-transaction-block-safe
command. Currently we rely on an ugly test involving seeing if the
On Wed, 2007-03-07 at 22:32 -0500, Luke Lonergan wrote:
Andreas,
On 3/7/07 11:45 AM, Zeugswetter Andreas ADI SD [EMAIL PROTECTED]
wrote:
Whoa, do you have anything to back that up ?
Sure - when we start to consider designs that implement advanced data
management features, we run into
On Wed, 2007-03-07 at 21:27 -0700, Jim Nasby wrote:
On Mar 7, 2007, at 3:26 AM, Simon Riggs wrote:
If you know that the constraints on each of the tables is distinct,
then
building a UNIQUE index on each of the partitions is sufficient to
prove
that all rows in the combined
It has been brought to my attention by Tokuharu Yuzawa that our calling
of WSAStartup() in DllMain() (libpqdll.c) is incorrect. Basically we're
calling WSAStartup() so that the client application does not have to.
However, due to the fact that WSAStartup() can itself load libraries,
there is a
Jim Nasby [EMAIL PROTECTED] writes:
One possibility would be to require 3 functions for a partitioned table: one
accepts the partitioning key and tells you what partition it's in, one that
tells you what the minimum partitioning key for a partition would be, and one
that tells you what the
The hard part there is checking that the partition constraints are
distinct. If the partition constraints are added one at a time, you
can use the predicate testing logic to compare the to-be-added
partition's constraint against each of the already added
constraints.
That becomes an
Magnus Hagander wrote:
The easy fix for this is to remove the calls. Which obviously will break
some client apps. A fairly easy fix for the WSAStartup() call is to have
a check in the connection functions against a global variable that will
then make sure to call WSAStartup() the first time
On Thu, Mar 08, 2007 at 12:47:42PM +0100, Andreas Pflug wrote:
Magnus Hagander wrote:
The easy fix for this is to remove the calls. Which obviously will break
some client apps. A fairly easy fix for the WSAStartup() call is to have
a check in the connection functions against a global
Pavan Deolasee wrote:
Thanks a lot, Tom. It seems to work fine for me. I will do some
more tests and report if I see any issue.
The problem mentioned before is hard to reproduce with the
suggested change, but its not completely gone away. I have
seen that again on CVS HEAD with the patch
Magnus Hagander [EMAIL PROTECTED] writes:
So I suggest the following simple patch.. Any objections?
One wonders if we need DllMain() at all any more. We certainly don't
need that switch statement ...
Also, isn't the WSACleanup() in freePGconn in the wrong place? Seems
like it shouldn't be
On Thu, Mar 08, 2007 at 10:10:28AM -0500, Tom Lane wrote:
Magnus Hagander [EMAIL PROTECTED] writes:
So I suggest the following simple patch.. Any objections?
One wonders if we need DllMain() at all any more. We certainly don't
need that switch statement ...
Indeed. Looking even more into
Hi Simon,
and what you haven't said
- all of this is orthogonal to the issue of buffer cache spoiling in
PostgreSQL itself. That issue does still exist as a non-OS issue, but
we've been discussing in detail the specific case of L2 cache effects
with specific kernel calls. All of the test
I am from pgsql-jdbc, so I may not be in the thread, so please ignore
places where my misunderstanding goes out.
The main two questions, IMHO, is:
1) What is the key to plan cache. Current option is some statement key
(id). Another option would be statement text (you still need to store it
have an SQL file with a set of about 3000 insert statements. This
file is executed twice from a remote client machine running Windows
XP. The first time it is executed against a Windows 2003 Server
running PostgreSQL 8.0. The second time it is executed against a
Windows 2003 Server running
Magnus Hagander [EMAIL PROTECTED] writes:
On Thu, Mar 08, 2007 at 10:10:28AM -0500, Tom Lane wrote:
Also, isn't the WSACleanup() in freePGconn in the wrong place? Seems
like it shouldn't be done until after we've closed the socket. I'd
be inclined to put it at the bottom of the routine.
Hi,
How can we accrately estimate the seq_page_fetch and random_page_fetch
costs from outside the postgres using for example a C routine. Using a very
simple program in C, I have two sets of files. I want to access one set
randomly i.e. pulling data from random locations within the files. The
I need to find out if anybody can give me some advice on incremental backups
and restores
Reason: backup size
abrie
On Thu, Mar 08, 2007 at 10:37:11AM -0500, Tom Lane wrote:
Magnus Hagander [EMAIL PROTECTED] writes:
On Thu, Mar 08, 2007 at 10:10:28AM -0500, Tom Lane wrote:
Also, isn't the WSACleanup() in freePGconn in the wrong place? Seems
like it shouldn't be done until after we've closed the socket.
Magnus Hagander [EMAIL PROTECTED] writes:
On Thu, Mar 08, 2007 at 10:37:11AM -0500, Tom Lane wrote:
Because the calls in DllMain covered us (ie, the WSA usage count never
got to be less than one). If we remove them, we'd better get this pair
right.
But those calls weren't even compiled in
Nik wrote:
have an SQL file with a set of about 3000 insert statements. This
file is executed twice from a remote client machine running Windows
XP. The first time it is executed against a Windows 2003 Server
running PostgreSQL 8.0. The second time it is executed against a
Windows 2003 Server
Abrie wrote:
I need to find out if anybody can give me some advice on incremental backups
and restores
Reason: backup size
Abrie - this is the pgsql-hackers mailing list. It's for questions about
the development of the PostgreSQL database system. You'll want the
pgsql-general or
Umar Farooq Minhas [EMAIL PROTECTED] writes:
How can we accrately estimate the seq_page_fetch and =
random_page_fetch costs from outside the postgres using for example a =
C routine.
Use a test case larger than memory. Repeat many times to average out
noise. IIRC, when I did the experiments
Tom Lane [EMAIL PROTECTED] writes:
Umar Farooq Minhas [EMAIL PROTECTED] writes:
How can we accrately estimate the seq_page_fetch and =
random_page_fetch costs from outside the postgres using for example a =
C routine.
Use a test case larger than memory. Repeat many times to average out
Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
Is everybody OK with not putting a per-tablespace worker limit?
Is everybody OK with putting per-database worker limits on a pg_database
column?
I don't think we need a new pg_database column. If it's a GUC you can
do ALTER
Hi Simon,
I would preserve the existing trigger function as little t -t, and
maybe implement a catchup trigger function as big t -T? Set it up so
that if the first attempt to find the WAL file postgres is currently
requesting succeeds, skip over the trigger check. If the first attempt
fails, then
Tom Lane wrote:
Gaetano Mendola [EMAIL PROTECTED] writes:
[ 8.2 evaluates volatile functions in the targetlist of a view ]
If I mark the function as STABLE or IMMUTABLE then even with version
8.2 the function is not evaluated. Is this the intended behavior?
Yes; people complained that we
On Thu, 2007-03-08 at 13:29 -0500, Doug Knight wrote:
I would preserve the existing trigger function as little t -t, and
maybe implement a catchup trigger function as big t -T? Set it up so
that if the first attempt to find the WAL file postgres is currently
requesting succeeds, skip over the
Galy Lee wrote:
Hi,
Alvaro Herrera wrote:
I still haven't received the magic bullet to solve the hot table
problem, but these at least means we continue doing *something*.
Can I know about what is your plan or idea for autovacuum improvement
for 8.3 now? And also what is the roadmap of
On Thu, Mar 08, 2007 at 01:52:27PM +0530, NikhilS wrote:
I think it'd be great to make adding and removing partitions as
simple as ALTER TABLE. I don't think that DELETE should be the
mechanism to drop a partition, though. Again, DML statements
shouldn't be performing DDL.
Since partition
On Thu, Mar 08, 2007 at 10:12:30AM +, Gregory Stark wrote:
Jim Nasby [EMAIL PROTECTED] writes:
One possibility would be to require 3 functions for a partitioned table: one
accepts the partitioning key and tells you what partition it's in, one that
tells you what the minimum
One other thought... a lot of this discussion seems to parallel the
debate from a while ago about whether SERIAL should act like a macro
(ie: it just sets everything up and users are free to monkey under the
hood afterwards), or whether it should be it's own 'closed-box'
construct.
Currently, we
Few things from our side:
1. 'Skyline Of' is a new operator proposed in ICDE 2003, one of the topmost
conferences of Data Engineering. Skyline operation is a hot area of research
in query processing. Many of the database community people do know about
this operator, and it is fast catching the
Hi Gavin,
Any progress?
Gavin Sherry wrote:
Heikki,
On Mon, 5 Mar 2007, Heikki Linnakangas wrote:
Hi all,
I'd like to see the indexam API changes needed by the bitmap indexam to
be committed soon. Has anyone looked at the proposed API in the latest
patch? Any thoughts?
Thanks for looking
Adding to this:
Ayush recently wrote a C program that emulates PG IO to do this analysis, and
we came out with (predictably) a ratio of sequential/random of 20-50 (for a
single user). This is predictable because the random component is fixed at the
access time of a single hard drive no matter
Excellent. Once you're ready, fire it over and I'll test it on our
config.
Doug
On Thu, 2007-03-08 at 18:34 +, Simon Riggs wrote:
On Thu, 2007-03-08 at 13:29 -0500, Doug Knight wrote:
I would preserve the existing trigger function as little t -t, and
maybe implement a catchup trigger
Note to Nikhil: Make sure the new syntax doesn't prevent partitions from
being placed upon multiple tablespaces in some manner, at CREATE TABLE
time.
What if the syntax was something like -
CREATE TABLE tabname (
...
...
) PARTITION BY
HASH(expr)
| RANGE(expr)
| LIST(expr)
On Thu, Mar 08, 2007 at 05:35:03PM +, Gregory Stark wrote:
Tom Lane [EMAIL PROTECTED] writes:
Umar Farooq Minhas [EMAIL PROTECTED] writes:
How can we accrately estimate the seq_page_fetch and =
random_page_fetch costs from outside the postgres using for example a =
C routine.
Simon,
Not checkpointing at all is not a good plan, since this will lead to an
enormous build up of WAL files and a very long recovery time if the
system does fail.
I appreciate your detailed comments.
Following your comments, I revised the problem.
Sigres-0.1.3 does checkpointings.
In
Jim C. Nasby wrote:
On Thu, Mar 08, 2007 at 01:52:27PM +0530, NikhilS wrote:
I think it'd be great to make adding and removing partitions as
simple as ALTER TABLE. I don't think that DELETE should be the
mechanism to drop a partition, though. Again, DML statements
shouldn't be performing DDL.
On Thu, 2007-03-08 at 17:35 +, Gregory Stark wrote:
When I was running tests I did it on a filesystem where nothing else was
running. Between tests I unmounted and remounted it. As I understand it Linux
associates the cache with the filesystem and not the block device and discards
all
On Thu, 8 Mar 2007, Heikki Linnakangas wrote:
Hi Gavin,
Any progress?
Really busy at the moment, but it's on my TODO list for today.
Thanks,
Gavin
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
Hi all,
I'm a grad student at UC Davis studying the postgres community and I
wanted to know if some on this list could help me out. I'm studying
the factors that affect people graduating from being mailing list
participant to developers with write access to the repository. Is it
possible to
Hi all,
I'm a grad student at UC Davis studying the apache server community
and I wanted to know if some on this list could help me out. I'm
studying the factors that affect people graduating from being
mailing list participant to developers with write access to the
repository. Is it possible
Re: [HACKERS] Estimating seq_page_fetch and random_page_fetchThanks a lot for
your replies. The suggestions have proved much useful.
Ayush, I'm curious to see your C program, thanks.
Here is a related but different issue. I started looking at the postgres
optimizer/planner code a month back to
Alvaro Herrera wrote:
I don't have anything else as detailed as a plan. If you have
suggestions, I'm all ears.
Cool, thanks for the update. :) We also have some new ideas on the
improvement of autovacuum now. I will raise it up later.
Now regarding your restartable vacuum work.
Does this
Jim Nasby [EMAIL PROTECTED] wrote:
Also, my recommended bgwriter_lru_maxpages is average number of
recycled buffers per cycle, that is hardly able to tune manually.
What do you mean by 'number of recycled buffers per cycle?
There is the following description in the documentation:
| *
Greg Smith [EMAIL PROTECTED] wrote:
Also, my recommended bgwriter_lru_maxpages is average number of
recycled buffers per cycle, that is hardly able to tune manually.
This is completely dependent on what percentage of your buffer cache is
pinned.
Don't you mean usage_count? In my
Alvaro Herrera [EMAIL PROTECTED] writes:
Now regarding your restartable vacuum work. I think that stopping a
vacuum at some point and being able to restart it later is very cool and
may get you some hot chicks, but I'm not sure it's really useful.
Too true :-(
I think it makes more sense to
On Fri, 9 Mar 2007, ITAGAKI Takahiro wrote:
In my understanding, each backend pins two or so buffers at once. So
percentage of pinned buffers should be low.
With the pgbench workload, a substantial percentage of the buffer cache
ends up pinned. From staring at the buffer cache using
Christian,
More specifically, could those
who worked on apache as some aspect of their job prior to getting repo
access let me know? Or if there are devs who know this information
about others, I'd be really appreciative to get it.
Hmmm. Wrong project. And I think you're making the
Hi,
On 3/9/07, Shane Ambler [EMAIL PROTECTED] wrote:
Note to Nikhil: Make sure the new syntax doesn't prevent partitions from
being placed upon multiple tablespaces in some manner, at CREATE TABLE
time.
What if the syntax was something like -
CREATE TABLE tabname (
...
...
)
Hi,
This follows on from the suggestion I made - taken along the lines of
the subject auto creation of partitions where I suggested the syntax
of partition check(month of mydatecol) and have a new partition created
as data was entered. With this scenario dropping the partition when it
was
Josh Berkus josh@agliodbs.com writes:
Christian,
More specifically, could those
who worked on apache as some aspect of their job prior to getting repo
access let me know? Or if there are devs who know this information
about others, I'd be really appreciative to get it.
Hmmm. Wrong
Tom Lane wrote:
Er, why not just finish out the scan at the reduced I/O rate? Any sort
Sometimes, you may need to vacuum large table in maintenance window and
hot table in the service time. If vacuum for hot table does not eat two
much foreground resource, then you can vacuum large table with
Greg Smith [EMAIL PROTECTED] writes:
With the pgbench workload, a substantial percentage of the buffer cache
ends up pinned.
[ raised eyebrow... ] Prove that. AFAIK it's impossible for the
pgbench queries to pin more than about three or four buffers per backend
concurrently.
Greg Smith [EMAIL PROTECTED] wrote:
In my understanding, each backend pins two or so buffers at once. So
percentage of pinned buffers should be low.
With the pgbench workload, a substantial percentage of the buffer cache
ends up pinned.
Joshua D. Drake wrote:
From an deployable application perspective, this could be a big deal. We
are already starting to see very large traction in the Win32 desktop app
arena.
There seem to be a few overlapping proposals in terms of reducing
various guarantees in the name of performance.
As
60 matches
Mail list logo
