Re: [HACKERS] Auto Partitioning
On Wed, Apr 04, 2007 at 09:34:03PM +0200, Markus Schiltknecht wrote: > Joshua D. Drake wrote: > >If we don't have multi-table indexes how do we enforce a primary key > >against a partitioned set? > > The executor would have to be clever enough to not do a single index > scan, but possibly scan through multiple indexes when asking for > uniqueness, depending on the partitioning rule set. But it's not the executor that checks uniqueness, it's built into the btre code. If someone manages to crack uniqueness for GiST indexes, we'll have our answer, since it has exactly the same problem but on a different scale. (Or vice-versa, if some gets uniqueness for multiple indexes, we can do it for GiST also). Have a nice day, -- Martijn van Oosterhout 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] Auto Partitioning
Hi, > The only problem I have with this is that the shops I know with big > partitioned tables favor triggers over rules for both performance reason and > a cleaner implementation. Even with automated rule creation this isnt going > to change afaics... not to mention we already create our rules & triggers > automatically, so really this just isn't exciting to me (though it may make > it easier for people getting in on the ground floor) I second this. The trigger route is much more maintainable than the rule route. IMO what really needs to happen is something more low level where there are no DBA visible changes. Triggers also have overhead, it would be nice to get a little more bare metal with this. I had raised this issue about rules/triggers back then and the responses seemed to be evenly split as to which ones to use. I think the broad question really is how well we want to support the current inheritance based partitioning mechanism. If we want to stick to it for a while (and to which we will stick to unless something concrete/better/"bare metal" comes up), IMHO we should try to make things simpler (by automating things if possible) to make it easier for people getting in. Regards, Nikhils -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Interaction of PITR backups andBulkoperationsavoiding WAL
On Wed, 2007-04-04 at 22:05 -0400, Bruce Momjian wrote: > I _still_ have no patch for this. Bruce, As I've mentioned, there is another thread where the discussion continued, which you should refer to. The subject of this thread is a potential bug that has existed since 8.1 and that I recently picked up on. Knowing that fixing bugs was OK after freeze, I thought it best to finish the features I was working on first. Even so, before freeze I requested some additional time to work on some related minor items. So there's nothing overdue now and in any case will be finished shortly. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Checkpoint gets stuck in mdsync
ITAGAKI Takahiro wrote: Heikki Linnakangas <[EMAIL PROTECTED]> wrote: Now that the CheckpointStartLock starvation has been taken care of, I'm seeing another problem with checkpoints in my test run: mdsync never finishes. My proposed fix is to make a copy of pendingOpsTable before entering the loop. AbsorbFsyncRequest will put new requests to a fresh new pendingOpsTable, while the mdsync loop will drain the copy. I'll write a patch along those lines if there's no better ideas. Yeah, I'm also anxious about the stuck. I wrote a fix to use a copy of pendingOpsTable as you said, when I implemented Load distributed checkpoint patch. (http://momjian.us/mhonarc/patches/msg00025.html) It would make me very happy if you review my patch and check whether my fix is proper. There was another reason to fix it in my patch. I wanted to fsync files only once for each file because bgwriter sleeps for each file in my patch. Ah, I see. I looked at the patch briefly a few days ago, and wondered why there was so many changes to mdsync. I didn't realize there was a fix to the "getting stuck" problem in there as well. I'll take a closer look, and try to write a patch to just fix the "getting stuck" problem, but in a way that anticipates the load distributed checkpoint patch so that it doesn't need to be rewritten again. -- 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] Checkpoint gets stuck in mdsync
Heikki Linnakangas <[EMAIL PROTECTED]> wrote: > Now that the CheckpointStartLock starvation has been taken care of, I'm > seeing another problem with checkpoints in my test run: mdsync never > finishes. > > My proposed fix is to make a copy of pendingOpsTable before entering the > loop. AbsorbFsyncRequest will put new requests to a fresh new > pendingOpsTable, while the mdsync loop will drain the copy. I'll write a > patch along those lines if there's no better ideas. Yeah, I'm also anxious about the stuck. I wrote a fix to use a copy of pendingOpsTable as you said, when I implemented Load distributed checkpoint patch. (http://momjian.us/mhonarc/patches/msg00025.html) It would make me very happy if you review my patch and check whether my fix is proper. There was another reason to fix it in my patch. I wanted to fsync files only once for each file because bgwriter sleeps for each file in my patch. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Full page writes improvement, code update
On Tue, 2007-04-03 at 19:45 +0900, Koichi Suzuki wrote: > Bruce Momjian wrote: > > Your patch has been added to the PostgreSQL unapplied patches list at: > > > > http://momjian.postgresql.org/cgi-bin/pgpatches > > Thank you very much for including. Attached is an update of the patch > according to Simon Riggs's comment about GUC name. The patch comes with its own "install kit", which is great to review (many thanks), but hard to determine where you think code should go when committed. My guess based on your patch - the patch gets applied to core :-) - pg_compresslog *and* pg_decompresslog go to a contrib directory called contrib/lesslog? Can you please produce a combined patch that does all of the above, plus edits the contrib Makefile to add all of those, as well as editing the README so it doesn't mention the patch, just the contrib executables? The patch looks correct to me now. I haven't tested it yet, but will be doing so in the last week of April, which is when I'll be doing docs for this and other stuff, since time is pressing now. -- Simon Riggs 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
[HACKERS] Checkpoint gets stuck in mdsync
Now that the CheckpointStartLock starvation has been taken care of, I'm seeing another problem with checkpoints in my test run: mdsync never finishes. Here's what's happening: 1. checkpoint calls mdsync 2. mdsync start processing pending fsyncs from pendingOpsTable (at this point, normal backends have to start doing writes themselves, because bgwriter is busy checkpointing and isn't keeping buffers clean) 3. after fsyncing 10 files, it calls AbsorbFsyncRequests 4. AbsorbFsyncRequests puts back entries into pendingOpsTable for those files that were already fsynced. 5. mdsync starts over, goto 2. The loop doesn't end until the test run is over, mdsync keeps fsyncing the same over and over again. My proposed fix is to make a copy of pendingOpsTable before entering the loop. AbsorbFsyncRequest will put new requests to a fresh new pendingOpsTable, while the mdsync loop will drain the copy. I'll write a patch along those lines if there's no better ideas. -- 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] Bug in UTF8-Validation Code?
Tatsuo Ishii wrote:
> I think we need to continute design discussion, probably
> targetting for 8.4, not 8.3.
But isn't a simple fix for chr() and ascii(), which does not
require a redesign, a Good Thing for 8.3 if possible? Something
that maintains as much upward and/or Oracle compatibility as
possible while doing away with ascii('EUR') returning 226 in UTF-8?
And I think - correct me if I am wrong - that conversion between
character and integer representation of the character in the current
database encoding is exactly that.
I see Tom Lane's point in rejecting chr(0), though.
Yours,
Laurenz Albe
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] Checkpoint gets stuck in mdsync
ITAGAKI Takahiro wrote: Heikki Linnakangas <[EMAIL PROTECTED]> wrote: Now that the CheckpointStartLock starvation has been taken care of, I'm seeing another problem with checkpoints in my test run: mdsync never finishes. My proposed fix is to make a copy of pendingOpsTable before entering the loop. AbsorbFsyncRequest will put new requests to a fresh new pendingOpsTable, while the mdsync loop will drain the copy. I'll write a patch along those lines if there's no better ideas. Yeah, I'm also anxious about the stuck. I wrote a fix to use a copy of pendingOpsTable as you said, when I implemented Load distributed checkpoint patch. (http://momjian.us/mhonarc/patches/msg00025.html) It would make me very happy if you review my patch and check whether my fix is proper. I just posted a patch to pgsql-patches that fixes the issue along the lines of your Load distributed checkpoint patch. Load distributed checkpoint patch now just needs to add the "calculate total file length" and the nap delay to mdsync. Thanks for the patch! -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Auto Partitioning
Hi, > I had raised this issue about rules/triggers back then and the > responses seemed to be evenly split as to which ones to use. Presumably your implementation already uses Triggers for INSERTs though, so why not use triggers for everything? No I am using rules for all the 3 cases. I am done with the UPDATE stuff too on which I was stuck with some help, so here is what the patch will do: postgres=# create table test1 (a int unique , b int check (b > 0)) partition by range(a) (partition child_1 check (a < 10)); NOTICE: CREATE TABLE / UNIQUE will create implicit index "test1_a_key" for table "test1" NOTICE: CREATE TABLE / UNIQUE will create implicit index "child_1_a_key" for table "child_1" CREATE TABLE A describe of the parent shows the rules added to it: postgres=# \d test1 Table "public.test1" Column | Type | Modifiers +-+--- a | integer | b | integer | Indexes: "test1_a_key" UNIQUE, btree (a) Check constraints: "test1_b_check" CHECK (b > 0) Rules: test1_child_1_delete AS ON DELETE TO test1 WHERE old.a < 10 DO INSTEAD DELETE FROM child_1 WHERE child_1.a = old.a test1_child_1_insert AS ON INSERT TO test1 WHERE new.a < 10 DO INSTEAD INSERT INTO child_1 (a, b) VALUES (new.a, new.b) test1_child_1_update AS ON UPDATE TO test1 WHERE old.a < 10 DO INSTEAD UPDATE child_1 SET a = new.a, b = new.b WHERE child_1.a = old.a Whereas a describe on the child shows the following: postgres=# \d child_1 Table "public.child_1" Column | Type | Modifiers +-+--- a | integer | b | integer | Indexes: "child_1_a_key" UNIQUE, btree (a) Check constraints: "child_1_a_check" CHECK (a < 10) "test1_b_check" CHECK (b > 0) Inherits: test1 Regards, Nikhils -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Auto Partitioning
On Thu, 2007-04-05 at 13:59 +0530, NikhilS wrote: > Hi, > > The only problem I have with this is that the shops I know > with big > > partitioned tables favor triggers over rules for both > performance reason and > > a cleaner implementation. Even with automated rule creation > this isnt going > > to change afaics... not to mention we already create our > rules & triggers > > automatically, so really this just isn't exciting to me > (though it may make > > it easier for people getting in on the ground floor) > > I second this. The trigger route is much more maintainable > than the rule > route. IMO what really needs to happen is something more low > level where > there are no DBA visible changes. Triggers also have overhead, > it would > be nice to get a little more bare metal with this. > > I had raised this issue about rules/triggers back then and the > responses seemed to be evenly split as to which ones to use. Presumably your implementation already uses Triggers for INSERTs though, so why not use triggers for everything? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(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] Auto Partitioning
On Thu, 2007-04-05 at 16:35 +0530, NikhilS wrote: > Hi, > > > I had raised this issue about rules/triggers back then and > the > > responses seemed to be evenly split as to which ones to > use. > > Presumably your implementation already uses Triggers for > INSERTs though, > so why not use triggers for everything? > > No I am using rules for all the 3 cases. So we are unable to load any of the tables using COPY. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Bug in UTF8-Validation Code?
On Thu, Apr 05, 2007 at 09:34:25AM +0900, Tatsuo Ishii wrote: > I'm not sure what kind of use case for unicode_char() you are thinking > about. Anyway if you want a "code point" from a character, we could > easily add such functions to all backend encodings currently we > support. Probably it would look like: I think the problem is that most encodings do not have the concept of a code point anyway, so implementing it for them is fairly useless. > An example outputs are: > > ASCII - 41 > ISO 10646 - U+0041 > ISO 10646 - U+29E3D > ISO 8859-1 - a5 > JIS X 0208 - 4141 In every case other than Unicode you're doing the same thing as encode/decode. Since we already have those functions, there's no need to get chr/ascii to duplicate it. In the case of UTF-8 however, it does something that is not done by encode/decode, hence the proposal to simply extend chr/ascii to do that. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[HACKERS] Buildfarm failures en masse
Seems the change of the XML messages now causes more or less every buildfarm member to fail the XML test. Meaning all the ones that don't have libxml, but that seems to be the vast majority... Looks like a missing update to regression tests? //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Auto Partitioning
So we are unable to load any of the tables using COPY. Aww, guess should have stuck to triggers as a first choice. Mea culpa, since I should have investigated some more before deciding on rules, or should have prodded you more earlier:) Regards, Nikhils -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Bug in UTF8-Validation Code?
On Thu, Apr 05, 2007 at 11:52:14AM +0200, Albe Laurenz wrote:
> But isn't a simple fix for chr() and ascii(), which does not
> require a redesign, a Good Thing for 8.3 if possible? Something
> that maintains as much upward and/or Oracle compatibility as
> possible while doing away with ascii('EUR') returning 226 in UTF-8?
I think the earlier expressed idea of getting chr/ascii to bail on
non-ascii character isn't a bad one.
I think your idea is bad in the sense that I actually need to know the
encoding of the character I want to be able to use it. If I knew the
encoding already, I could just use encode().
What I was thinking of was something that, irrespective of the
encoding, gave me a string properly encoded with the character I want.
Since AFAIK Unicode is the only character set that actually numbers the
characters in a way not related to the encoding, so it would seem
useful to be able to give a unicode character number and get a string
with that character...
So your implemntation is simply:
1. Take number and make UTF-8 string
2. Convert it to database encoding.
> And I think - correct me if I am wrong - that conversion between
> character and integer representation of the character in the current
> database encoding is exactly that.
AFAIK there is no "integer representation" of a character in anything
other than Unicode. Unicode is the only case that cannot be handled by
a simple encode/decode.
Have a nice day,
--
Martijn van Oosterhout 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] Buildfarm failures en masse
Magnus Hagander wrote: > Seems the change of the XML messages now causes more or less every > buildfarm member to fail the XML test. Meaning all the ones that don't have > libxml, but that seems to be the vast majority... > > Looks like a missing update to regression tests? I don't think the new wording complies with the message guidelines anyway, so my vote goes to revert the wording change and reach a consensus about it before committing anything else. I think this should be ERROR: unsupported XML feature DETAIL: The function foo requires libxml2 support. HINT: You need to re-compile PostgreSQL with --enable-whatever-xml. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Buildfarm failures en masse
Alvaro Herrera wrote: > Magnus Hagander wrote: > > Seems the change of the XML messages now causes more or less every > > buildfarm member to fail the XML test. Meaning all the ones that don't have > > libxml, but that seems to be the vast majority... > > > > Looks like a missing update to regression tests? > > I don't think the new wording complies with the message guidelines > anyway, so my vote goes to revert the wording change and reach a > consensus about it before committing anything else. > > I think this should be > > ERROR: unsupported XML feature > DETAIL: The function foo requires libxml2 support. > HINT: You need to re-compile PostgreSQL with --enable-whatever-xml. Yes, I like your wording better. I will make the updates to the code and regression tests. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] Buildfarm failures en masse
Bruce Momjian wrote: > Alvaro Herrera wrote: > > Magnus Hagander wrote: > > > Seems the change of the XML messages now causes more or less every > > > buildfarm member to fail the XML test. Meaning all the ones that don't > > > have > > > libxml, but that seems to be the vast majority... > > > > > > Looks like a missing update to regression tests? > > > > I don't think the new wording complies with the message guidelines > > anyway, so my vote goes to revert the wording change and reach a > > consensus about it before committing anything else. > > > > I think this should be > > > > ERROR: unsupported XML feature > > DETAIL: The function foo requires libxml2 support. > > HINT: You need to re-compile PostgreSQL with --enable-whatever-xml. > > Yes, I like your wording better. I will make the updates to the code > and regression tests. OK, new wording: ERROR: unsupported XML feature DETAIL: This functionality requires libxml support. HINT: You need to re-compile PostgreSQL using --with-libxml. The error is all in one macro, so it has to be generic and not mention the specific function, or xml data type. Regression tests updated. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Last minute mini-proposal (I know, I know) for PQexecf()
My apologies for the late reply... Tom Lane wrote: <[EMAIL PROTECTED]> writes: I'd like to see a new variant on PQexec(): PGresult * PQexecf(PGconn *conn, const char *fmt, ...); Way too late for 8.3 --- if we were going to do something like this, we should think first and program later. In particular, blindly adopting the sprintf format string definition doesn't seem very helpful. The sorts of escapes I'd want to have are "properly quoted SQL identifier", "properly quoted SQL literal", etc. A large fraction of what sprintf knows about is more or less irrelevant to the task of creating SQL commands. The advantage of using stock sprintf commands is that most compilers understand them these days, and can check that the arguments given match the format string. If you go with your own format specifiers, this is no longer true. Brian
Re: [HACKERS] pg_index updates and SI invalidation
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Pavan Deolasee wrote: > On 4/3/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: > > > > > > Where are we on this? > > > > > > --- > > > > Tom Lane wrote: > > > > > > [squint...] How can that fail during a reload if it worked the first > > > time? Needs a closer look at what's happening. > > > > > > > Please see the attached updated patch, based on Tom's comments. > > Attempt to reload index information for system indexes such as > pg_class_oid_index can cause infinite recursion. But I realized that > we don't need to reload system index information because we > neither allow CREATE INDEX or CIC on system relations. Only > REINDEX is allowed which does not need any reload. So we skip > index information reload for system relations. > > Thanks, > Pavan > > -- > > EnterpriseDB http://www.enterprisedb.com [ Attachment, skipping... ] -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Interaction of PITR backups andBulkoperationsavoiding WAL
Simon Riggs wrote: > On Wed, 2007-04-04 at 22:05 -0400, Bruce Momjian wrote: > > > I _still_ have no patch for this. > > Bruce, > > As I've mentioned, there is another thread where the discussion > continued, which you should refer to. > > The subject of this thread is a potential bug that has existed since 8.1 > and that I recently picked up on. Knowing that fixing bugs was OK after > freeze, I thought it best to finish the features I was working on first. > Even so, before freeze I requested some additional time to work on some > related minor items. > > So there's nothing overdue now and in any case will be finished shortly. OK, I was unaware you were still working on this item. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Checkpoint gets stuck in mdsync
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > My proposed fix is to make a copy of pendingOpsTable before entering the > loop. AbsorbFsyncRequest will put new requests to a fresh new > pendingOpsTable, while the mdsync loop will drain the copy. I'll write a > patch along those lines if there's no better ideas. That sounds pretty ugly. Perhaps better is a "cycle ID" field added to the table entries, assigned from a counter that's bumped before entering the fsync loop. Then you could distinguish entries made before starting the loop from those made after. One fine point is to not let AbsorbFsyncRequest change the cycle ID on a pre-existing entry ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Bug in UTF8-Validation Code?
Martijn van Oosterhout writes: > I think the problem is that most encodings do not have the concept of a > code point anyway, so implementing it for them is fairly useless. Yeah. I'm beginning to think that the right thing to do is (a) make chr/ascii do the same thing as Oracle (ie, as in the discussed patch) (b) make a different pair of functions that translate Unicode code points to/from the current database encoding. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Auto Partitioning
On Wednesday 04 April 2007 21:17, Gregory Stark wrote:
> "Simon Riggs" <[EMAIL PROTECTED]> writes:
> >> If we don't have multi-table indexes how do we enforce a primary key
> >> against a partitioned set? What about non primary keys that are just
> >> UNIQUE? What about check constraints that aren't apart of the exclusion?
> >
> > I can come up with arbitrary examples that require them, but I've not
> > seen one that makes sense in a real business app. Calling columns a, b
> > and c disguises the validity of the example, IMHO.
>
> Usually it comes with a situation where you want to do something like
> "partition invoices by invoice_date" while simultaneously "use invoice_num"
> as the primary key".
>
> Normally the invoices numbers will be incrementing chronologically but
> there's no constraint or any mechanism to enforce that or to enforce that
> an old invoice number from an old partition isn't reused.
>
> In practice I think this isn't really a serious problem though. The old
> partitions are going to be read-only so you can just check that the invoice
> number doesn't already exist without worrying about race conditions.
In practice many people need a PK on the table not just as a unique identifier
for the row, but to act as a parent in a FK relationship. If you start your
schema with one table and have to break it up into partitions later, this
will raise a number of red flags.
> And in
> most cases it's being sequence-generated or something equally reliable so
> the constraints are really just there as a backstop; you're not depending
> on them for correctness.
>
With that argument why have unique constraints at all?
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] Auto Partitioning
> > That lets you enforce unique constraints as long as the partition key > > is part of the unique constraint. > > Is that already sufficient? yes > That would alter the ordering of > the columns in the index, no? I mean: It produces ordered blocks of append nodes for range queries that span multiple partitions, but one unique key can still only be in exactly one of the partitions. e.g. If you range partition by b, only one partition is applicable regardless of the position of b in the index. This is sufficient for a working unique constraint with current pg versions. > CREATE INDEX x ON test(a, b, c); > > isn't the same as > > CRETAE INDEX x ON test(c, b, a); That is only a problem if you also want to avoid a sort (e.g. for an order by), it is not an issue for filtering rows. And in some cases the sort could still be avoided with some range proving logic, if you can bring the append nodes of partitions into an order that represents the order by. (an example would be a query "where c=5 and b between 0 and 20" and two partitions one for 0 <= b < 10 and a second for 10 <= b) > That's why I'd say, the first columns of an index would have > to be equal to all of the columns used in the partitioning key. No. It may change performance in some situations, but it is not needed for unique constraints. Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] "Garbled" postgres logs
On Tue, Apr 03, 2007 at 04:51:33PM -0400, Tom Lane wrote: > No, syslogger is single-threaded so it can't be at fault. The > interleaving must be happening when the data is inserted into the pipe > that leads to syslogger. We've got multiple backends concurrently > writing that pipe, remember. > > BTW, although I'm blaming stdio here, it's conceivable that it is > issuing messages in a single write() and the kernel is failing to keep > the writes atomic, as I think is required by spec if the write is for > less than PIPEBUF bytes. So Tim might want to compare exact kernel > versions as well as exact libc versions between the misbehaving machine > and the others. I've compared the libc and kernel versions between a misbehaving machine and a machine that is logging properly and they're the same: bos.rte.libc 5.3.0.52 C F libc Library bos.mp64:bos.mp64:5.3.0.52: : :A:F:Base Operating System 64-bit Multiprocessor Runtime -- Tim Goodaire416-673-4126[EMAIL PROTECTED] Database Team Lead, Afilias Canada Corp. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] "Garbled" postgres logs
[EMAIL PROTECTED] (Tim Goodaire) writes:
> I've compared the libc and kernel versions between a misbehaving machine and a
> machine that is logging properly and they're the same:
[ scratches head... ] So what's different?
Anyway, if you are interested in trying to cut libc out of the equation,
the patch I had in mind goes like this (against 8.1)
regards, tom lane
*** src/backend/utils/error/elog.c.orig Thu Feb 22 03:11:50 2007
--- src/backend/utils/error/elog.c Thu Apr 5 12:03:57 2007
***
*** 1710,1716
write_eventlog(edata->elevel, buf.data);
else
#endif
! fprintf(stderr, "%s", buf.data);
}
/* If in the syslogger process, try to write messages direct to file */
--- 1710,1716
write_eventlog(edata->elevel, buf.data);
else
#endif
! write(fileno(stderr), buf.data, buf.len);
}
/* If in the syslogger process, try to write messages direct to file */
***
*** 2043,2048
--- 2043,2049
#ifndef WIN32
/* On Unix, we just fprintf to stderr */
vfprintf(stderr, fmt, ap);
+ fflush(stderr);
#else
/*
***
*** 2058,2065
--- 2059,2069
write_eventlog(EVENTLOG_ERROR_TYPE, errbuf);
}
else
+ {
/* Not running as service, write to stderr */
vfprintf(stderr, fmt, ap);
+ fflush(stderr);
+ }
#endif
va_end(ap);
}
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Auto Partitioning
Hi, Zeugswetter Andreas ADI SD wrote: CREATE INDEX x ON test(a, b, c); isn't the same as CRETAE INDEX x ON test(c, b, a); That is only a problem if you also want to avoid a sort (e.g. for an order by), ..or if you want to use that index for 'WHERE a = 5'. The first one is probably helping you, the second isn't. (an example would be a query "where c=5 and b between 0 and 20" and two partitions one for 0 <= b < 10 and a second for 10 <= b) Hm.. in that case, an index on (a, b, c) wouldn't help. An index on (c, b, a) would be just perfect, agreed? Now, for the partitioning: you simply have to scan two partitions in that case, no matter how you arrange your indexes. And this is where we need some sort of multi-table index scan functionality. (I'm not saying a multi-table index. Such a thing would be too large on disk. That functionality should probably better be realized by using the underlying per-table indexes). That's why I'd say, the first columns of an index would have to be equal to all of the columns used in the partitioning key. I correct my own statement somewhat, here: only in that case, a single table index can satisfy your request. For other cases, you'd have to query more than one partition's indexes and mix them correctly to maintain the right order, if required. No. It may change performance in some situations, but it is not needed for unique constraints. Agreed, for unique constraints. But indexes are used for some more things than just unique constraints checking. ;-) Regards Markus ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] buildfarm minimum script versions
Following a suggestion by Tom Lane, I have written some code that will reject reports from members running very old script versions. In a week I intend to turn it on and require script version 1.45 as a minimum. That version is 18 months old. 1 month later I propose to set that to 1.68, which is now 7 months old. The following members are going to be affected: kingfisher | 'script_version' => '1.17' otter | 'script_version' => '1.17' hare | 'script_version' => '1.45' thrush | 'script_version' => '1.45' bear | 'script_version' => '1.57' grebe | 'script_version' => '1.62' kite | 'script_version' => '1.62' stoat | 'script_version' => '1.62' yak| 'script_version' => '1.62' cheers andrew ---(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] Auto Partitioning
Hi, Martijn van Oosterhout wrote: The executor would have to be clever enough to not do a single index scan, but possibly scan through multiple indexes when asking for uniqueness, depending on the partitioning rule set. But it's not the executor that checks uniqueness, it's built into the btre code. Well, it's the executor calling into the btree code. Couldn't the executor choose which (btree-) indexes to query for uniqueness? If someone manages to crack uniqueness for GiST indexes, we'll have our answer, since it has exactly the same problem but on a different scale. (Or vice-versa, if some gets uniqueness for multiple indexes, we can do it for GiST also). Uh.. can you elaborate on that? AFAICS, you would simply have to query multiple btree indexes and make sure non of them is violated. How would that help making unique GiST indexes possible? What's the problem there? Regards Markus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Auto Partitioning
On Thu, Apr 05, 2007 at 10:00:37PM +0200, Markus Schiltknecht wrote: > >If someone manages to crack uniqueness for GiST indexes, we'll have our > >answer, since it has exactly the same problem but on a different scale. > >(Or vice-versa, if some gets uniqueness for multiple indexes, we can do > >it for GiST also). > > Uh.. can you elaborate on that? AFAICS, you would simply have to query > multiple btree indexes and make sure non of them is violated. How would > that help making unique GiST indexes possible? What's the problem there? There's a race condition. What happens if someone else tries to insert the same key at the same time. If you know it's going to be in the same index you can lock the page. Across multiple indexes you run into deadlock issues. I'm not saying it's hard, just that it's not as easy as checking each index... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[HACKERS] What X86/X64 OS's do we need coverage for?
I'm in the process of building a new box that will have Dual Xeon 5120's (Dual Core), and 4G of ram and 2.4T of disk (6x400G SATA). It will have CentOS 4.4 X86_64 as the base os with VMWare Server running on it. I am willing to run any X86 or X64 OS's in VM's as buildfarm clients. What OS's do we need coverage for? LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893
Re: [HACKERS] What X86/X64 OS's do we need coverage for?
It will have CentOS 4.4 X86_64 as the base os with VMWare Server running on it. I am willing to run any X86 or X64 OS’s in VM’s as buildfarm clients. What OS’s do we need coverage for? CentOS5 hits ina couple days. J LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] What X86/X64 OS's do we need coverage for?
I might use that as the base then, since the hardware finishes getting here tomorrow. My question still stands on what OS's we need coverage for. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Joshua D. Drake Sent: Thursday, April 05, 2007 6:18 PM To: Larry Rosenman Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] What X86/X64 OS's do we need coverage for? > > It will have CentOS 4.4 X86_64 as the base os with VMWare Server running > on it. > > > > I am willing to run any X86 or X64 OS's in VM's as buildfarm clients. > > > > What OS's do we need coverage for? CentOS5 hits ina couple days. J > > > > LER > > > > > > -- > > Larry Rosenman http://www.lerctr.org/~ler > > Phone: +1 512-248-2683 E-Mail: ler@lerctr.org > > US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 > > > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Bitmap index thoughts (another segfault)
I'm seeing a segfault on a size TPC-H size 10 database. The patch and
code are:
- bitmap patch from 12 Mar
- 8.3 dev from 27 Mar
SELECT count(distinct(o_orderkey))
FROM orders orders_alias
WHERE o_orderpriority IN ('1-URGENT', '3-MEDIUM') AND o_orderstatus='P';
(gdb) bt
#0 0x in ?? ()
#1 0x08155eb5 in bitmap_stream_free (opaque=0x84e4070) at tidbitmap.c:1336
#2 0x08142914 in ExecEndBitmapHeapScan (node=0x8405548)
at nodeBitmapHeapscan.c:463
#3 0x0813789a in ExecutorEnd (queryDesc=0x83ed948) at execMain.c:992
#4 0x081134ef in PortalCleanup (portal=0x83ee018) at portalcmds.c:302
#5 0x0823e2d2 in PortalDrop (portal=0x83ee018, isTopCommit=0 '\0')
at portalmem.c:382
#6 0x081b2182 in exec_simple_query (
query_string=0x83a8018 "SELECT count(distinct(o_orderkey))\nFROM
orders orders_alias\nWHERE o_orderpriority IN ('1-URGENT', '3-MEDIUM')
AND o_orderstatus='P';") at postgres.c:964
#7 0x081b4350 in PostgresMain (argc=4, argv=0x833a638,
username=0x833a610 "postgres") at postgres.c:3488
#8 0x0818faab in ServerLoop () at postmaster.c:2985
#9 0x081911b1 in PostmasterMain (argc=1, argv=0xbfbfec30) at
postmaster.c:967
#10 0x08153592 in main (argc=1, argv=0xbfbfec30) at main.c:188
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] What X86/X64 OS's do we need coverage for?
ler@lerctr.org ("Larry Rosenman") writes:
> I might use that as the base then, since the hardware finishes getting here
> tomorrow.
>
> My question still stands on what OS's we need coverage for.
I've got Debian testing/unstable covered. I'm not sure we have
Novell/SuSE covered...
--
output = ("cbbrowne" "@" "linuxdatabases.info")
http://www3.sympatico.ca/cbbrowne/emacs.html
"Motto for a research laboratory: What we work on today, others will
first think of tomorrow." -- Alan J. Perlis
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] pgbench transaction timestamps
> > Tatsuo, would you please comment on this patch too? > > No problem. I will come up with a comment by the end of this week. Here are comments. 1) latency log file format extention looks usefull (-x option). 2) it seems the "cleanup feature" (-X option) was withdrawed by the author, but the patches still include the feature. So I'm confused. -- Tatsuo Ishii SRA OSS, Inc. Japan > > --- > > > > Greg Smith wrote: > > > This patch changes the way pgbench outputs its latency log files so that > > > every transaction gets a timestamp and notes which transaction type was > > > executed. It's a one-line change that just dumps some additional > > > information that was already sitting in that area of code. I also made a > > > couple of documentation corrections and clarifications on some of the > > > more > > > confusing features of pgbench. > > > > > > It's straightforward to parse log files in this format to analyze what > > > happened during the test at a higher level than was possible with the > > > original format. You can find some rough sample code to convert this > > > latency format into CVS files and then into graphs at > > > http://www.westnet.com/~gsmith/content/postgresql/pgbench.htm which I'll > > > be expanding on once I get all my little patches sent in here. > > > > > > If you recall the earlier version of this patch I submitted, it added a > > > cleanup feature that did a vacuum and checkpoint after the test was > > > finished and reported two TPS results. The idea was to quantify how much > > > of a hit the eventual table maintenance required to clean up after the > > > test would take. While those things do influence results and cause some > > > of the run-to-run variation in TPS (checkpoints are particularly visible > > > in the graphs), after further testing I concluded running a VACUUM > > > VERBOSE > > > and CHECKPOINT in a script afterwards and analyzing the results was more > > > useful than integrating something into pgbench itself. > > > > > > -- > > > * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD > > Content-Description: > > > > [ Attachment, skipping... ] > > > > > > > > ---(end of broadcast)--- > > > TIP 6: explain analyze is your friend > > > > -- > > Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us > > EnterpriseDB http://www.enterprisedb.com > > > > + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [PATCHES] Optimized pgbench for 8.3
> > Tatsuo, would you please comment on this patch? > > Sure. I will come up with a comment by the end of this week. The patches look good to me. BTW, is anybody working on enabling the fill factor to the tables used by pgbench? 8.3 will introduce HOT, and I think adding the feature will make it easier to test HOT. -- Tatsuo Ishii SRA OSS, Inc. Japan > > --- > > > > ITAGAKI Takahiro wrote: > > > The attached is a patch to optimize contrib/pgbench using new 8.3 > > > features. > > > > > > - Use DROP IF EXISTS to suppress errors for initial loadings. > > > - Use a combination of TRUNCATE and COPY to reduce WAL on creating > > > the accounts table. > > > > > > Also, there are some cosmetic changes. > > > > > > - Change the output of -v option from "starting full vacuum..." > > > to "starting vacuum accounts..." in reflection of the fact. > > > - Shape duplicated error checks into executeStatement(). > > > > > > > > > There is a big performance win in "COPY with no WAL" feature. > > > Thanks for the efforts! > > > > > > Regards, > > > --- > > > ITAGAKI Takahiro > > > NTT Open Source Software Center > > > > [ Attachment, skipping... ] > > > > > > > > ---(end of broadcast)--- > > > TIP 5: don't forget to increase your free space map settings > > > > -- > > Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us > > EnterpriseDB http://www.enterprisedb.com > > > > + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Full page writes improvement, code update
Hi, I agree to put the patch to core and the others (pg_compresslog and pg_decompresslog) to contrib/lesslog. I will make separate materials to go to core and contrib. As for patches, we have tested against pgbench, DBT-2 and our propriatery benchmarks and it looked to run correctly. Regards; Simon Riggs wrote: On Tue, 2007-04-03 at 19:45 +0900, Koichi Suzuki wrote: Bruce Momjian wrote: Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches Thank you very much for including. Attached is an update of the patch according to Simon Riggs's comment about GUC name. The patch comes with its own "install kit", which is great to review (many thanks), but hard to determine where you think code should go when committed. My guess based on your patch - the patch gets applied to core :-) - pg_compresslog *and* pg_decompresslog go to a contrib directory called contrib/lesslog? Can you please produce a combined patch that does all of the above, plus edits the contrib Makefile to add all of those, as well as editing the README so it doesn't mention the patch, just the contrib executables? The patch looks correct to me now. I haven't tested it yet, but will be doing so in the last week of April, which is when I'll be doing docs for this and other stuff, since time is pressing now. -- Koichi Suzuki ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Hey, there's a free infomask bit
Along the way in applying Greg Stark's varvarlena patch, I removed HEAP_HASCOMPRESSED, which was not used and AFAIR never had been used for anything. Don't all propose a use for that bit at once... regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] What X86/X64 OS's do we need coverage for?
Larry Rosenman wrote: I might use that as the base then, since the hardware finishes getting here tomorrow. The other thing to consider is that CentOS 5 has Xen built right in, so you should be able run VMs without VMWare on it. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Auto Partitioning
Markus Schiltknecht <[EMAIL PROTECTED]> writes: > Uh.. can you elaborate on that? AFAICS, you would simply have to query > multiple btree indexes and make sure non of them is violated. That only works for the partition-key indexes, ie, ones where you can be sure a-priori that there cannot be duplicate keys in two different indexes. I believe the complaint here is that people would like to be able to enforce uniqueness across the whole partitioned table on columns that are not part of the partition key. (But that sounds rather like pie in the sky, actually. Which other databases can do that, and how do they do it?) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Fix mdsync never-ending loop problem
Heikki Linnakangas <[EMAIL PROTECTED]> wrote: > Itagaki, would you like to take a stab at this? Yes, I'll try to fix the mdsync problem. I'll separate this fix from LDC patch. If we need to backport the fix to the back branches, a stand-alone patch would be better. In my understanding from the discussion, we'd better to take "cycle ID" approach instead of "making a copy of pendingOpsTable", because duplicated table is hard to debug and requires us to pay attention not to leak memories. I'll adopt the cycle ID approach and build LDC on it as a separate patch. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Optimized pgbench for 8.3
Tatsuo Ishii <[EMAIL PROTECTED]> writes: > The patches look good to me. Please commit whatever you think is reasonable. > BTW, is anybody working on enabling the fill factor to the tables used > by pgbench? 8.3 will introduce HOT, and I think adding the feature > will make it easier to test HOT. I'm not 100% sure that HOT will make it ... but I agree that it'd be useful for pgbench to support different fillfactor choices. Please add if you have time. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [PATCHES] Fix mdsync never-ending loop problem
ITAGAKI Takahiro <[EMAIL PROTECTED]> writes: > In my understanding from the discussion, we'd better to take "cycle ID" > approach instead of "making a copy of pendingOpsTable", because duplicated > table is hard to debug and requires us to pay attention not to leak memories. > I'll adopt the cycle ID approach and build LDC on it as a separate patch. Heikki made some reasonable arguments against the cycle-ID idea. I'm not intending to insist on it ... I do think there are multiple issues here and it'd be better to try to separate the fixes into different patches. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] What X86/X64 OS's do we need coverage for?
Hi, On Fri, 2007-04-06 at 01:23 -0400, Matthew T. O'Connor wrote: > > The other thing to consider is that CentOS 5 has Xen built right in, > so you should be able run VMs without VMWare on it. ... if the kernel of the OS has Xen support, there will be no performance penalty (only 2%-3%) (Para-virtualization). Otherwise, there will be full-virtualization, and we should expect a performance loss about 30% for each guest OS (like Windows). Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.CommandPrompt.com ---(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
[HACKERS] Postgres optimizer
Hi, I want to get the coding details regarding postgres optimizer. Essentially, which files correspond to which functions, how the algo is implemented, the flow etc.. Where can I find this material ? Also, how to I start changing Postgres Code using eclipse platform (with tracing, debug functionalities ) ? Thanks, Suresh - Need Mail bonding? Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.
