Re: Fwd: [HACKERS] How does the partitioned lock manager works?
Ah... It seems that a item is calculated its hash value, get the bucket number from it and insert into that bucket chain. The insertion has nothing to do with partition number(but Alvaro says which hash is used depends on the partition number. I haven't really understood this: how can we get a hash value without deciding which hash to use? ). However, when we travel along a chain to get a item, we can infer its partition number from its hash value. My problem is, I'm not so sure about the process stated above, because in that way, items in ONE chain may belong to different partitions,and it is obviously conflicted with so that different partitions use different hash chains as README mentioned. 2007/4/28, Tom Lane [EMAIL PROTECTED]: It's not that hard: the bucket number is some number of low-order bits of the hash value, and the partition number is some smaller (or at most equal) number of low-order bits of the hash value. regards, tom lane
Re: Fwd: [HACKERS] How does the partitioned lock manager works?
2007/4/28, Heikki Linnakangas [EMAIL PROTECTED]: 3. Lock that partition 6. Unlock partition I suddenly realize that LW locks are used to manage the lock hash table.So when a item is to be inserted into hash table, we must gain that partition lock first to change that table. As the insertion algorithm described, a specific partition lock manage some items, but these items can be stored in anywhere of the hash table,not necessarily in a bucket chain. So there are some problems with different partitions use different hash chains, a partition can use different hash chains,too. Am I right?
Re: Fwd: [HACKERS] How does the partitioned lock manager works?
Cui Shijun wrote: As the insertion algorithm described, a specific partition lock manage some items, but these items can be stored in anywhere of the hash table,not necessarily in a bucket chain. So there are some problems with different partitions use different hash chains, a partition can use different hash chains,too. Am I right? No, you're still confused. Each bucket in the hash table is a chain. Each chain can have 0, 1, or more items. I'd suggest that you study how the normal non-partitioned hash tables work first. The partitioning is a straightforward extension of that. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Avoiding unnecessary reads in recovery
On Fri, 2007-04-27 at 12:22 +0100, Heikki Linnakangas wrote:
Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
As regards the zero_damaged_pages question, I raised that some time ago
but we didn't arrive at an explicit answer. All I would say is we can't
allow invalid pages in the buffer manager at any time, whatever options
we have requested, otherwise other code will fail almost immediately.
Yeah --- the proposed new bufmgr routine should probably explicitly zero
the content of the buffer. It doesn't really matter in the context of
WAL recovery, since there can't be any concurrent access to the buffer,
but it'd make it safe to use in non-WAL contexts (I think there are
other places where we know we are going to init the page and so a
physical read is a waste of time).
To implement that correctly, I think we'd need to take the content lock
to clear the buffer if it's already found in the cache. It doesn't seem
right to me for the buffer manager to do that, in the worst case it
could lead to deadlocks if that function was ever used while holding
another buffer locked.
What we could have is the semantics of Return a buffer, with either
correct contents or completely zeroed out. It would act just like
ReadBuffer if the buffer was already in memory, and zero out the page
otherwise. That's a bit strange semantics to have, but is simple to
implement and works for the use-cases we've been talking about.
Sounds good.
Patch implementing that attached. I named the function ReadOrZeroBuffer.
We already have an API quirk similar to this: relation extension. It
seems strange to have two different kinds of special case API that are
used alongside each other in XLogReadBuffer()
Currently if we extend by a block we say
buffer = ReadBuffer(reln, P_NEW);
Why not just add another option, so where you use ReadOrZeroBuffer we
just say
buffer = ReadBuffer(reln, P_INIT);
which we then check for on entry by saying
isInit = (blockNum == P_INIT);
just as we already do for P_NEW
That way you can do the code like this
if (isExtend || isInit)
{
/* new or initialised buffers are zero-filled */
MemSet((char *) bufBlock, 0, BLCKSZ);
if (isExtend)
smgrextend(reln-rd_smgr, blockNum,
(char *) bufBlock,
reln-rd_istemp);
}
That way we don't have to have ReadBuffer_common etc..
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Avoiding unnecessary reads in recovery
Simon Riggs wrote: On Fri, 2007-04-27 at 12:22 +0100, Heikki Linnakangas wrote: Patch implementing that attached. I named the function ReadOrZeroBuffer. We already have an API quirk similar to this: relation extension. It seems strange to have two different kinds of special case API that are used alongside each other in XLogReadBuffer() Currently if we extend by a block we say buffer = ReadBuffer(reln, P_NEW); Why not just add another option, so where you use ReadOrZeroBuffer we just say buffer = ReadBuffer(reln, P_INIT); Because ReadOrZeroBuffer needs the block number as an argument. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Avoiding unnecessary reads in recovery
I was actually thinking that we could slip this in 8.3. It's a simple, well-understood patch, which fixes a little data integrity quirk as well as gives a nice recovery speed up. Bruce Momjian wrote: I assume this is 8.4 material. --- Heikki Linnakangas wrote: Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: As regards the zero_damaged_pages question, I raised that some time ago but we didn't arrive at an explicit answer. All I would say is we can't allow invalid pages in the buffer manager at any time, whatever options we have requested, otherwise other code will fail almost immediately. Yeah --- the proposed new bufmgr routine should probably explicitly zero the content of the buffer. It doesn't really matter in the context of WAL recovery, since there can't be any concurrent access to the buffer, but it'd make it safe to use in non-WAL contexts (I think there are other places where we know we are going to init the page and so a physical read is a waste of time). To implement that correctly, I think we'd need to take the content lock to clear the buffer if it's already found in the cache. It doesn't seem right to me for the buffer manager to do that, in the worst case it could lead to deadlocks if that function was ever used while holding another buffer locked. What we could have is the semantics of Return a buffer, with either correct contents or completely zeroed out. It would act just like ReadBuffer if the buffer was already in memory, and zero out the page otherwise. That's a bit strange semantics to have, but is simple to implement and works for the use-cases we've been talking about. Patch implementing that attached. I named the function ReadOrZeroBuffer. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] strange buildfarm failures
Alvaro Herrera wrote:
Tom Lane wrote:
Stefan Kaltenbrunner [EMAIL PROTECTED] writes:
Stefan Kaltenbrunner wrote:
two of my buildfarm members had different but pretty weird looking
failures lately:
http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=quaggadt=2007-04-25%2002:03:03
and
http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=emudt=2007-04-24%2014:35:02
any ideas on what might causing those ?
Just for the record, quagga and emu failures don't seem related to the
report below. They don't crash; the regression.diffs contains data that
suggests that there may be data corruption of some sort.
INSERT INTO INET_TBL (c, i) VALUES ('192.168.1.2/30', '192.168.1.226');
ERROR: invalid cidr value: %{
This doesn't seem to make much sense.
no idea - but quagga and emu seem to have similiar failure (in the sense
that they don't make any sense) and i have no reson to believe that the
hardware is a fault.
lionfish just failed too:
http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=lionfishdt=2007-04-25%2005:30:09
And had a similar failure a few days ago. The curious thing is that
what we get in the postmaster log is
LOG: server process (PID 23405) was terminated by signal 6: Aborted
LOG: terminating any other active server processes
You would think SIGABRT would come from an assertion failure, but
there's no preceding assertion message in the log. The other
characteristic of these crashes is that *all* of the failing regression
instances report terminating connection because of crash of another
server process, which suggests strongly that the crash was in an
autovacuum process (if it were bgwriter or stats collector the
postmaster would've said so). So I think the recent autovac patches
are at fault. I spent a bit of time trolling for a spot where the code
might abort() without having printed anything, but didn't find one.
Hmm. I kept an eye on the buildfarm for a few days, but saw nothing
that could be connected to autovacuum so I neglected it.
This is the other failure:
http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=lionfishdt=2007-04-20%2005:30:14
It shows the same pattern. I am baffled -- I don't understand how it
can die without reporting the error.
Apparently it crashes rather frequently, so it shouldn't be too
difficult to reproduce on manual runs. If we could get it to run with a
higher debug level, it might prove helpful to further pinpoint the
problem.
The core file would be much better obviously (first and foremost to
confirm that it's autovacuum that's crashing ... )
well - i now have a core file but it does not seem to be much worth
except to prove that autovacuum seems to be the culprit:
Core was generated by `postgres: autovacuum worker process
'.
Program terminated with signal 6, Aborted.
[...]
#0 0x0ed9 in ?? ()
warning: GDB can't find the start of the function at 0xed9.
GDB is unable to find the start of the function at 0xed9
and thus can't determine the size of that function's stack frame.
This means that GDB may be unable to access that stack frame, or
the frames below it.
This problem is most likely caused by an invalid program counter or
stack pointer.
However, if you think GDB should simply search farther back
from 0xed9 for code which looks like the beginning of a
function, you can increase the range of the search using the `set
heuristic-fence-post' command.
Stefan
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] question for serial types with CHECK conditions
Thanks for your reply Michael. My point was to step on the asumption that the implicit serial call for a type represents the fact that the sequence will start allways in the same place, unless inmediatelly after your create table you plan to modify that, which makes no sense when we go back to what the CREATE SEQUENCE represents for the case. For what I saw, straight foward what I did is wrong, but the server allowed me to proceed. So yes, my fault, but with a bit of help, right? After all I am just being boggus on a silly point. The range of potential DBA's which may come to this situation is pretty small for further discussions :) Best wishes, g.- On 4/28/07, Michael Glaesemann [EMAIL PROTECTED] wrote: On Apr 28, 2007, at 10:30 , Guido Barosio wrote: Now, my question is: Shouldn't postgresql avoid the creation of the table while a serial type contains a check condition? My question to you is why should it? a SERIAL is a shorthand for creating an INTEGER column a, a sequence (a_seq) with a dependency, and DEFAULT nextval(a_seq). There may be a valid reason someone wants to put additional constraints on the column, and I'm not sure why the server should second guess the DBA in this case. If the CHECK constraint isn't what you want, then don't include it: and in this case the server helpfully gave you an error which let you know that the CHECK constraint was not doing what you expected. Also, the server doesn't have the smarts to look into the CHECK constraint and decide if it makes sense in your case. For example, perhaps you want to have CHECK (a 0), which won't really do anything for a default sequence. However, if the sequence is changed, it may return negative integers, which you may not want, so in some cases, CHECK (a 0) may be a valid constraint *in your case*. The crux of the issue is that there may be valid reasons to have a CHECK constraint on a INTEGER (SERIAL) column, and the server is not (and will probably never be) smart enough to know your particular business rules without you telling it specifically. Does this help clarify the situation? Michael Glaesemann grzm seespotcode net -- Guido Barosio --- http://www.globant.com [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] question for serial types with CHECK conditions
On Apr 28, 2007, at 10:30 , Guido Barosio wrote: Now, my question is: Shouldn't postgresql avoid the creation of the table while a serial type contains a check condition? My question to you is why should it? a SERIAL is a shorthand for creating an INTEGER column a, a sequence (a_seq) with a dependency, and DEFAULT nextval(a_seq). There may be a valid reason someone wants to put additional constraints on the column, and I'm not sure why the server should second guess the DBA in this case. If the CHECK constraint isn't what you want, then don't include it: and in this case the server helpfully gave you an error which let you know that the CHECK constraint was not doing what you expected. Also, the server doesn't have the smarts to look into the CHECK constraint and decide if it makes sense in your case. For example, perhaps you want to have CHECK (a 0), which won't really do anything for a default sequence. However, if the sequence is changed, it may return negative integers, which you may not want, so in some cases, CHECK (a 0) may be a valid constraint *in your case*. The crux of the issue is that there may be valid reasons to have a CHECK constraint on a INTEGER (SERIAL) column, and the server is not (and will probably never be) smart enough to know your particular business rules without you telling it specifically. Does this help clarify the situation? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] I have made the first step on postgresql, but got some problems
few days before, I said I wanto joinin the postgresql develope group. and recently, I make some progress, such as get throught the base steps. but when i add some functions to the spatial data, such as box_add1() to box data, but when I compile it, and install it to my computer, it turns out ,the box_add1() didnot work, but the original one box_add(),which has existed in the current one, works. so what happened? and I check it, that, the declaration of the function, I add, can be found in the installed include files, it's local\pgsql\include\server\utils\geo_decls.h. anyone can give me some suggestions? thankyou!
[HACKERS] SOS, help me please, one problem towards the postgresql develope on windows
my postgresql source code is at c:/mingw/postgresql and instal to
C:/msys/1.0/local/pgsql/
I add a function to src\backend\utils\adt\geo_ops.c as the following:
*Datum
box_add2(PG_FUNCTION_ARGS)
{
BOX *box = PG_GETARG_BOX_P(0);
Point*p = PG_GETARG_POINT_P(1);*
* PG_RETURN_BOX_P(box_construct((box-high.x + 2* p-x),
(box-low.x + 2* p-x),
(box-high.y +2* p-y),
(box-low.y + 2* p-y)));
}
*there is another similar one(this is the original one):
*Datum
box_add(PG_FUNCTION_ARGS)
{
BOX *box = PG_GETARG_BOX_P(0);
Point*p = PG_GETARG_POINT_P(1);*
* PG_RETURN_BOX_P(box_construct((box-high.x + p-x),
(box-low.x + p-x),
(box-high.y + p-y),
(box-low.y + p-y)));
}*
And i also add the declaration to the src\include\utils\geo_decls.h like
this:
extern Datum box_add2(PG_FUNCTION_ARGS);
and then I did the following like step by step:
$ cd /c/mingw/postgresql
$ ./configure
///as i download the alib, but don't kown where it should be put. so i
ignore this, does it
matter
configure: error: zlib library not found
If you have zlib already installed, see config.log for details on the
failure. It is possible the compiler isn't looking in the proper directory.
Use --without-zlib to disable zlib support.
$ make
...
All of PostgreSQL successfully made. Ready to install.
$ make install
PostgreSQL installation complete.
$ initdb -D /usr/local/pgsql/data //before this i add the
environments
variableslike this：
PGDATA=C:/msys/1.0/local/pgsql/data
PGHOME=C:/msys/1.0/local/pgsql
PGHOST=localhost
PGPORT=5434
PATH= C:/msys/1.0/local/pgsql/bin
.
Success. You can now start the database server using:
C:\msys\1.0\local\pgsql\bin\postgres -D C:/msys/1.0/local/pgsql/data
or
C:\msys\1.0\local\pgsql\bin\pg_ctl -D C:/msys/1.0/local/pgsql/data
-l logfile start
$ pg_ctl start -l logfile
server starting
$ createdb testdb
CREATE DATABASE
then I use pgadminIII to open the database:
just run the scripts:
*select box_add(box '((0,0),(1,1))',point'(2,2)')*
got:
(3,3),(2,2)
*select box_add2(box '((0,0),(1,1))',point'(2,2)')*
got:
*ERROR: function box_add2(box, point) does not exist
SQL state: 42883
advice:No function matches the given name and argument types. You may need
to add explicit **type casts.
chars:8*
anyone know this??? why this happened? what should I do?
thankyou very much!!!
Re: [HACKERS] Avoiding unnecessary reads in recovery
Heikki Linnakangas [EMAIL PROTECTED] writes: I was actually thinking that we could slip this in 8.3. It's a simple, well-understood patch, which fixes a little data integrity quirk as well as gives a nice recovery speed up. Yeah. It's arguably a bug fix, in fact, since it eliminates the issue that the recovery behavior is wrong if full-page-writes had been off when the WAL log was made. regards, tom lane ---(end of broadcast)--- TIP 1: 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
Re: [HACKERS] temporal variants of generate_series()
On Thu, Apr 12, 2007 at 02:56:24PM -0700, Andrew Hammond wrote:
I've written the following function definitions to extend
generate_series to support some temporal types (timestamptz, date and
time). Please include them if there's sufficient perceived need or
value.
-- timestamptz version
CREATE OR REPLACE FUNCTION generate_series
( start_ts timestamptz
, end_ts timestamptz
, step interval
) RETURNS SETOF timestamptz
AS $$
DECLARE
current_ts timestamptz := start_ts;
BEGIN
IF start_ts end_ts AND step INTERVAL '0 seconds' THEN
LOOP
IF current_ts end_ts THEN
RETURN;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
ELSIF end_ts start_ts AND step INTERVAL '0 seconds' THEN
LOOP
IF current_ts end_ts THEN
RETURN;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
Here's an SQL version without much in the way of bounds checking :)
CREATE OR REPLACE FUNCTION generate_series (
start_ts timestamptz,
end_ts timestamptz,
step interval
) RETURNS SETOF timestamptz
LANGUAGE sql
AS $$
SELECT
CASE
WHEN $1 $2 THEN
$1
WHEN $1 $2 THEN
$2
END + s.i * $3 AS generate_series
FROM generate_series(
0,
floor(
CASE
WHEN $1 $2 AND $3 INTERVAL '0 seconds' THEN
extract('epoch' FROM $2) -
extract('epoch' FROM $1)
WHEN $1 $2 AND $3 INTERVAL '0 seconds' THEN
extract('epoch' FROM $1) -
extract('epoch' FROM $2)
END/extract('epoch' FROM $3)
)::int8
) AS s(i);
$$;
It should be straight-forward to make similar ones to those below.
CREATE OR REPLACE FUNCTION generate_series
( start_ts date
, end_ts date
, step interval
) RETURNS SETOF date
-- time version
CREATE OR REPLACE FUNCTION generate_series
( start_ts time
, end_ts time
, step interval
) RETURNS SETOF time
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] I have made the first step on postgresql, but got some problems
Umm, please define does not work. You're going to need to give details about your setup, what you're actually doing and what's going wrong if you expect any answers... On Sat, Apr 28, 2007 at 08:56:46PM +0800, shieldy wrote: few days before, I said I wanto joinin the postgresql develope group. and recently, I make some progress, such as get throught the base steps. but when i add some functions to the spatial data, such as box_add1() to box data, but when I compile it, and install it to my computer, it turns out ,the box_add1() didnot work, but the original one box_add(),which has existed in the current one, works. so what happened? and I check it, that, the declaration of the function, I add, can be found in the installed include files, it's local\pgsql\include\server\utils\geo_decls.h. anyone can give me some suggestions? thankyou! -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] pgsql crollable cursor doesn't support one form ofpostgresql's cu
On Fri, 2007-04-27 at 07:36 +0200, Pavel Stehule wrote: it's true. There is bug. I'll send actualised version tomorrow. No need: I fixed the bug and applied the patch. Thanks for the patch. -Neil ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Reducing stats collection overhead
Arjen van der Meijden told me that according to the tweakers.net benchmark, HEAD is noticeably slower than 8.2.4, and I soon confirmed here that for small SELECT queries issued as separate transactions, there's a significant difference. I think much of the difference stems from the fact that we now have stats_row_level ON by default, and so every transaction sends a stats message that wasn't there by default in 8.2. When you're doing a few thousand transactions per second (not hard for small read-only queries) that adds up. It seems to me that this could be fixed fairly easily by allowing the stats to accumulate across multiple small transactions before sending a message. There's surely not much point in kicking stats out quickly when the stats collector only reports them to the world every half second anyway. The first design that comes to mind is that at transaction end (pgstat_report_tabstat() time) we send a stats message only if at least X milliseconds have elapsed since we last sent one, where X is PGSTAT_STAT_INTERVAL or closely related to it. We also make sure to flush stats out before process exit. This approach ensures that in a lots-of-short-transactions scenario, we only need to send one stats message every X msec, not one per query. The cost is possible delay of stats reports. I claim that any transaction that makes a really sizable change in the stats will run longer than X msec and therefore will send its stats immediately. Cases where a client does a small transaction after sleeping for awhile (more than X msec) will also send immediately. You might get a delay in reporting the last few transactions of a burst of short transactions, but how much does it matter? So I think that complicating the design with, say, a timeout counter to force out the stats after a sleep interval is not necessary. Doing so would add a couple of kernel calls to every client interaction so I'd really rather avoid that. Any thoughts, better ideas? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
