Re: [HACKERS] Indexes vs. cache flushes

Greg Stark <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> But you couldn't make any meaningful changes in the definition of an >> index, such as changing its column set, operator classes, partial-index >> predicate, etc, except by dropping and recreating it. > The only examp

Re: [HACKERS] Indexes vs. cache flushes

Tom Lane <[EMAIL PROTECTED]> writes: > This would still support REINDEX (which changes pg_class.relfilenode in > order to replace the physical file) and ALTER INDEX SET TABLESPACE. > But you couldn't make any meaningful changes in the definition of an > index, such as changing its column set, ope

Re: [HACKERS] Surrogate keys (Was: enums)

On Wed, Jan 18, 2006 at 03:58:50PM -0800, Josh Berkus wrote: > Martjin, > > > Interesting. However, in my experience very few things have "natural > > keys". There are no combination of attributes for people, phone calls > > or even real events that make useful natural keys. > > I certainly hope

Re: [HACKERS] No heap lookups on index

Greg Stark <[EMAIL PROTECTED]> writes: > I wonder if the bitmap can actually be one bit per page actually. Yeah, I think we'd agreed that per-page was the way to go. Per-tuple bitmaps are painful to manage because of the variable number of tuples per page. And really all you need to know is whet

Re: [HACKERS] No heap lookups on index

Greg Stark <[EMAIL PROTECTED]> writes: > You pay in Oracle when you read these records too. If there are pending > updates you have to do a second read to the rollback segment to get the old > record. This hits long-running batch queries especially hard since by the time > they finish a large numbe

Re: [HACKERS] No heap lookups on index

David Scott <[EMAIL PROTECTED]> writes: > Since I am sure everyone is tired of the intro by now, I'll get to the > questions: ... > Is there any way to modify PostgreSQL to allow index lookups without heap > validation that doesn't involve re-writing the MVCC implementation of > keeping dead rows

Re: [HACKERS] 8.0.5 Bug in unique indexes?

"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Odd problem with unique indexes: What's the database's locale? This could be the same problem fixed in 8.0.6, if the locale has weird ideas about what string equality means. regards, tom lane ---(end

Re: [HACKERS] No heap lookups on index

Tom Lane <[EMAIL PROTECTED]> writes: > Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > >> Oracle does, but you pay in other ways. Instead of keeping dead tuples > >> in the main heap, they shuffle them off to an 'undo log'. This has some > >> downsides: > >> Rollbacks take *forever*, though

Re: [HACKERS] Surrogate keys (Was: enums)

> >> If a primary key exists for a collection that is known never to change, > >> for example social security number, student registration number, or > >> employee number, then no additional system-assigned UID is required. In point of fact Social security numbers *can* change. -- greg --

[HACKERS] Indexes vs. cache flushes

I've been working on getting the system to pass regression tests cleanly when forcing a cache flush at every possible instant. The main tests pass now (in 8.1 --- HEAD remains broken pending lookup_rowtype_tupdesc fix), but contrib is still crashing. On investigation the problem turns out to be i

Re: [HACKERS] Unique constraints for non-btree indexes

Tom Lane <[EMAIL PROTECTED]> writes: > Martijn van Oosterhout writes: > > I guess what you're talking about is a constrained index, of which a > > unique index is just a particular type. I suppose the actual constraint > > would be one of the operators defined for the operator class (since > > w

[HACKERS] 8.0.5 Bug in unique indexes?

Hello, Odd problem with unique indexes: 8.0.5 64 bit (Quad Opteron) 100 tables, each table has same layout, 1 million rows per table. The problem persists within multiple tables but only within the set of 100 tables. I have a composite unique key on each table: "uniq1" UNIQUE, btree (unit_i

Re: [HACKERS] Surrogate keys (Was: enums)

Michael Glaesemann <[EMAIL PROTECTED]> writes: > As far as I can tell, the only difference between your position, > Dann, and Date and Darwen's, is that you think no natural key is > immutable. D&D's examples of "natural" keys are worth a second look though: >> If a primary key exists for a c

Re: [HACKERS] pgxs/windows

Bruce Momjian wrote: > Andrew Dunstan wrote: > > > > > > Bruce Momjian wrote: > > > > > > > >Could this be related to the fact that pre-8.2 makefiles were not > > >space-safe? I am unsure how pgxs worked on Win32 without being > > >space-safe. > > > > > > > > > > > > > I don't see how. In fa

Re: [HACKERS] suppress output for benchmarking

On Wed, Jan 18, 2006 at 10:35:48PM -0500, uwcssa wrote: > I am testing the performance of postgresql on a set of workloads. However, > the output significantly affects the performance evaluation. Is there a way > to by-pass all output of select statements so the timing reflects only the > query ev

[HACKERS] suppress output for benchmarking

 I am testing the performance of postgresql on a set of workloads. However, the output significantly affects the performance evaluation. Is there a way to by-pass all output of select statements so the timing reflects only the query evaluation process? thanks a lot

Re: FW: [HACKERS] Surrogate keys (Was: enums)

Dann, > The primary key should be immutable, meaning that its value should not be > changed during the course of normal operations of the database. Why? I don't find this statement to be self-evident. Why would we have ON UPDATE CASCADE if keys didn't change sometimes? > At any rate, the us

Re: [HACKERS] pgxs/windows

Andrew Dunstan wrote: > > > Bruce Momjian wrote: > > > > >Could this be related to the fact that pre-8.2 makefiles were not > >space-safe? I am unsure how pgxs worked on Win32 without being > >space-safe. > > > > > > > > I don't see how. In fact, pgxs seems to use short form paths anyway.

Re: [HACKERS] No heap lookups on index

On Thu, 2006-01-19 at 09:18 +0800, Christopher Kings-Lynne wrote: > > Oracle does, but you pay in other ways. Instead of keeping dead tuples > > in the main heap, they shuffle them off to an 'undo log'. This has some > > downsides: > > > > Rollbacks take *forever*, though this usually isn't much o

[HACKERS] TODO-Item: B-tree fillfactor control

Hi Hackers, I'm trying the following TODO item: [Indexes] - Add fillfactor to control reserved free space during index creation I have already made an patch and it seemed to work well. $ ./pgbench -i -s 10 # select relpages from pg_class where relname = 'accounts_pkey'; relpages | 27

Re: [HACKERS] Surrogate keys (Was: enums)

> -Original Message- > From: Michael Glaesemann [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 18, 2006 5:48 PM > To: Dann Corbit > Cc: Leandro Guimarães Faria Corcete Dutra; Jim C. Nasby; pgsql- > [EMAIL PROTECTED] > Subject: Re: [HACKERS] Surrogate keys (Was: enums) > > > On Jan 1

Re: [HACKERS] Surrogate keys (Was: enums)

On Jan 19, 2006, at 10:34 , Dann Corbit wrote: http://www.db.ucsd.edu/cse132B/Thirdmanifesto.pdf "PROPOSITION 1.4: Unique Identifiers (UIDs) for records should be assigned by the DBMS only if a user-defined primary key is not available. An immutable primary key has an extra advantage

Re: FW: [HACKERS] Surrogate keys (Was: enums)

Maybe it goes better into Advocacy or something, but I have found a quote by database big-wigs that I strongly disagree with: From: http://www.db.ucsd.edu/cse132B/Thirdmanifesto.pdf We have this. "PROPOSITION 1.4: Unique Identifiers (UIDs) for records should be assigned by the DBMS only if a us

Re: [HACKERS] No heap lookups on index

"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Would this open the door for allowing tables to be maintained in CLUSTER > order (at least at the block level if not within the blocks)? Though I > have no idea how you'd handle page splits without a lot of pain I think the way you'd attack that is by b

Re: [HACKERS] No heap lookups on index

On Wed, Jan 18, 2006 at 08:13:59PM -0500, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > We only need to index the row with the lowest value on any page so the main > > index would get 100 times smaller. The main part of the index would not > > need to be written to except when a blo

Re: [HACKERS] No heap lookups on index

Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: >> Oracle does, but you pay in other ways. Instead of keeping dead tuples >> in the main heap, they shuffle them off to an 'undo log'. This has some >> downsides: >> Rollbacks take *forever*, though this usually isn't much of an issue >> unless yo

Re: [HACKERS] No heap lookups on index

On Thu, Jan 19, 2006 at 09:18:55AM +0800, Christopher Kings-Lynne wrote: > >Oracle does, but you pay in other ways. Instead of keeping dead tuples > >in the main heap, they shuffle them off to an 'undo log'. This has some > >downsides: > > > >Rollbacks take *forever*, though this usually isn't much

Re: [HACKERS] No heap lookups on index

Oracle does, but you pay in other ways. Instead of keeping dead tuples in the main heap, they shuffle them off to an 'undo log'. This has some downsides: Rollbacks take *forever*, though this usually isn't much of an issue unless you need to abort a really big transaction. It's a good point tho

Re: [HACKERS] No heap lookups on index

Simon Riggs <[EMAIL PROTECTED]> writes: > We only need to index the row with the lowest value on any page so the main > index would get 100 times smaller. The main part of the index would not > need to be written to except when a block overflows. BTW, the above is equivalent to saying that the lea

Re: [HACKERS] Surrogate keys (Was: enums)

On Jan 19, 2006, at 9:31 , Leandro Guimarães Faria Corcete Dutra wrote: OK. Again, data independence should be the goal here. Again, implementation details... levels mixup. Lack of data implementation biting us again. Yet real user-defined data ty

Re: [HACKERS] No heap lookups on index

Simon Riggs <[EMAIL PROTECTED]> writes: > Hopefully we could avoid trying to support GIST-heaps? Well, that would be an extra index AM that someone might or might not get around to writing someday. I was thinking that both btree and hash index AMs might be interesting for this, though. Hash in p

Re: FW: [HACKERS] Surrogate keys (Was: enums)

Em Qua, 2006-01-18 às 17:22 -0600, Jim C. Nasby escreveu: > > > > Forgive me my ignorance, but are ints inherently faster to compare than > >strings, or is it just an implementation detail? Ideally, if this is so > >a fully data-independent system would create a hash behind the back of > >user

Re: [HACKERS] pgxs/windows

Bruce Momjian wrote: Could this be related to the fact that pre-8.2 makefiles were not space-safe? I am unsure how pgxs worked on Win32 without being space-safe. I don't see how. In fact, pgxs seems to use short form paths anyway. Example (from previous email): dllwrap -o rainbow.

Re: [HACKERS] No heap lookups on index

On Wed, 2006-01-18 at 18:27 -0500, Tom Lane wrote: > Imagine an index that > contains only the upper levels of a search tree --- links to what > would be the leaf level point into the associated heap. In this > design > the heap is still a heap in the sense that you can seqscan it without > any aw

Re: [HACKERS] Surrogate keys (Was: enums)

> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > [EMAIL PROTECTED] On Behalf Of Dann Corbit > Sent: Wednesday, January 18, 2006 4:04 PM > To: josh@agliodbs.com; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Surrogate keys (Was: enums) > > > -Original Mess

Re: [HACKERS] Surrogate keys (Was: enums)

> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > [EMAIL PROTECTED] On Behalf Of Josh Berkus > Sent: Wednesday, January 18, 2006 3:59 PM > To: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Surrogate keys (Was: enums) > > Martjin, > > > Interesting. However, i

Re: [HACKERS] Surrogate keys (Was: enums)

Martjin, > Interesting. However, in my experience very few things have "natural > keys". There are no combination of attributes for people, phone calls > or even real events that make useful natural keys. I certainly hope that I never have to pick up one of your projects. A table without a nat

Re: [HACKERS] No heap lookups on index

Simon Riggs <[EMAIL PROTECTED]> writes: > You might want to consider the thought of "organised heaps" as an > alternative thought to index improvements. That way there is no heap to > avoid visiting because the index is also the main data structure. > This would offer performance, but would be one

Re: [HACKERS] pgxs/windows

Andrew Dunstan wrote: > > > Tom Lane wrote: > > >Andrew Dunstan <[EMAIL PROTECTED]> writes: > > > > > >>dllwrap doesn't seem to get given LDFLAGS, and maybe doesn't honor it > >>either. > >> > >> > > > >I wouldn't expect it to handle everything that might appear in LDFLAGS, > >but maybe i

Re: FW: [HACKERS] Surrogate keys (Was: enums)

>> Comparing two ints is much, much faster than comparing two text >> fields. For a small number of comparisons, it doesn't matter. When >> you're joining tables together, it's a different story. > > That is where data independence would come handy... like a better enum, >with possreps and hi

FW: [HACKERS] Surrogate keys (Was: enums)

Ooops, fat-finger'd -hackers... -Original Message- Adding -hackers back to the list. > From: Leandro Guimarães Faria Corcete Dutra > Em Seg, 2006-01-16 às 12:52 -0600, Jim C. Nasby escreveu: > > On Sat, Jan 14, 2006 at 07:28:21PM +0900, Michael Glaesemann wrote: > > > > > > For UPDATEs a

Re: [HACKERS] No heap lookups on index

On Wed, Jan 18, 2006 at 04:02:45PM -0500, Jonah H. Harris wrote: > David, > > You can find some of this discussion in "Much Ado About COUNT(*)". Related > to that discussion, I had written a patch which added visibility information > to the indexes. > > If you're interested in the patch and/or c

Re: [HACKERS] No heap lookups on index

On Wed, Jan 18, 2006 at 12:14:12PM -0800, David Scott wrote: >Do commercial databases implement MVCC in a way that allows an > efficient implementation of index lookups that can avoid heap lookups? Oracle does, but you pay in other ways. Instead of keeping dead tuples in the main heap, they

Re: [HACKERS] Unique constraints for non-btree indexes

Martijn van Oosterhout writes: > On Wed, Jan 18, 2006 at 04:18:10PM -0500, Tom Lane wrote: >> In btree we can identify a unique page to lock for any given key value >> to ensure that no one else is concurrently inserting a conflicting >> key, thus usually allowing concurrent insertions of differen

Re: [HACKERS] No heap lookups on index

On Wed, 2006-01-18 at 12:14 -0800, David Scott wrote: > Is the additional overhead of keeping full tuple visibility > information inside of the index so odious to the Postgres community > as > to prevent a patch with this solution from being applied back to the > head? Maybe as an optional use

Re: [HACKERS] Unique constraints for non-btree indexes

On Wed, Jan 18, 2006 at 04:18:10PM -0500, Tom Lane wrote: > I think the generalization that would be appropriate for GIST is that > a "unique" index guarantees there are no two entries x, y such that > x ~ y, where ~ is some boolean operator nominated by the opclass. We'd > probably have to insist

Re: [HACKERS] Unique constraints for non-btree indexes

On Wed, Jan 18, 2006 at 04:10:16PM -0500, Tom Lane wrote: > Martijn van Oosterhout writes: > > check_unique_index( ctid of inserting tuple, ctid of possibly > > conflicting tuple) > > I agree it's pretty ugly to have the index AM directly poking into > the heap, but adding a level of subroutine d

Re: [HACKERS] Unique constraints for non-btree indexes

Martijn van Oosterhout writes: > I guess what you're talking about is a constrained index, of which a > unique index is just a particular type. I suppose the actual constraint > would be one of the operators defined for the operator class (since > whatever the test is, it needs to be indexable). A

Re: [HACKERS] Unique constraints for non-btree indexes

Martijn van Oosterhout writes: > check_unique_index( ctid of inserting tuple, ctid of possibly > conflicting tuple) I agree it's pretty ugly to have the index AM directly poking into the heap, but adding a level of subroutine doesn't really make that a whole lot nicer :-(. In any case, you've un

Re: [HACKERS] No heap lookups on index

David,You can find some of this discussion in "Much Ado About COUNT(*)".  Related to that discussion, I had written a patch which added visibility information to the indexes.If you're interested in the patch and/or consulting, contact me offline. -JonahOn 1/18/06, Tom Lane <[EMAIL PROTECTED]> wrote

Re: [HACKERS] Bad estimate on LIKE matching

On Wed, 2006-01-18 at 10:37 -0500, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > On Tue, 2006-01-17 at 13:53 +0100, Magnus Hagander wrote: > >> Any way to teach the planner about this? > > > In a recent thread on -perform, I opined that this case could best be > > solved by using d

Re: [HACKERS] No heap lookups on index

David Scott <[EMAIL PROTECTED]> writes: > Is the additional overhead of keeping full tuple visibility > information inside of the index so odious to the Postgres community as > to prevent a patch with this solution from being applied back to the > head? This has been discussed and rejected

[HACKERS] No heap lookups on index

Allow me a brief introduction. I work in a company who contracts intelligence analysis software to the government. We are currently developing a product which is using PostgreSQL at it's core. Due to the licensing of the product and the integration with perl this is our first choice in datab

[HACKERS] log_min_messages and debug levels

Hi, in my machine (win xp) i was trying to start psql (8.1.1) with log_min_messages to debug5 (just to see the messages :) but even the service start i cannot use psql nor pgadmin i receive an error of server closed the connection unexpectedly postgres=# select version();

Re: [HACKERS] Surrogate keys (Was: enums)

On Wed, Jan 18, 2006 at 01:08:53PM +, Leandro Guimar??es Faria Corcete DUTRA wrote: > > b) If each parent record will have many children, the space savings from > > using a surrogate key can be quite large > > Not such a common case. Hmmm... Many blog entries per user... Many blog comments

Re: [HACKERS] Bad estimate on LIKE matching

Simon Riggs <[EMAIL PROTECTED]> writes: > On Tue, 2006-01-17 at 13:53 +0100, Magnus Hagander wrote: >> Any way to teach the planner about this? > In a recent thread on -perform, I opined that this case could best be > solved by using dynamic random block sampling at plan time followed by a > direc

Re: [HACKERS] debug_query_string and multiple statements

Yep, I couldn't find a better way to do it when I added debug_query_string long ago. Unless we go to a lot of work to parse the string, we could end up with something worse than we have now. --- Neil Conway wrote: > While r

Re: [HACKERS] Unique constraints for non-btree indexes

I think I understand what you're saying, just that I don't think the btree index has anything to do with it.The extensibility is there for indexes to handle uniques in any way they choose.  If you wanted to add a common unique index checking function for GIST, I'd just add it to GIST.  It just seem

Re: [HACKERS] Unique constraints for non-btree indexes

On Wed, Jan 18, 2006 at 09:15:04AM -0500, Jonah H. Harris wrote: > I thought gistinsert had checkUnique, it was just ifdef'd out because there > was no code to enforce it... and as such, during bootstrap it was marked as > amcanunique = false. Would it be that hard to enable it? Well, it has the

Re: [HACKERS] Surrogate keys (Was: enums)

Leandro Guimarães Faria Corcete DUTRA <[EMAIL PROTECTED]> writes: > Greg Stark mit.edu> writes: > > > I hate knee-jerk reactions too, but just think of all the pain of people > > dealing with databases where they used Social Security numbers for primary > > keys. I would never use an attribute t

Re: [HACKERS] Unique constraints for non-btree indexes

I thought gistinsert had checkUnique, it was just ifdef'd out because there was no code to enforce it... and as such, during bootstrap it was marked as amcanunique = false.  Would it be that hard to enable it?On 1/18/06, Martijn van Oosterhout wrote:Hi,Currently due to the way

[HACKERS] Unique constraints for non-btree indexes

Hi, Currently due to the way unique constraints are tied to btree there is no way to allow GiST indexes to do the same thing. The thing I'm specifically interested in is an index where you insert ranges (start,end) and if unique, the index will complain if they overlap. As a side-effect, this may

Re: [HACKERS] Surrogate keys (Was: enums)

On Jan 18, 2006, at 22:08 , Leandro Guimarães Faria Corcete DUTRA wrote: Jim C. Nasby pervasive.com> writes: a) the optimizer does a really poor job on multi-column index statistics So it should be fixed? Of course! Patches welcome! Michael Glaesemann grzm myrealbox com ---

Re: [HACKERS] enums

Leandro Guimarães Faria Corcete DUTRA wrote: Andrew Dunstan dunslane.net> writes: If people would like to play, I have created a little kit to help in creating first class enum types in a few seconds. Isn't what we actually want possreps? You appear to be responding to mai

Re: [HACKERS] Surrogate keys (Was: enums)

On Wed, Jan 18, 2006 at 01:08:53PM +, Leandro Guimarães Faria Corcete DUTRA wrote: > Jim C. Nasby pervasive.com> writes: > > Generally, I just use surrogate keys for everything unless performance > > dictates something else. > > What I am proposing is the reverse: use natural keys for everyt

Re: [HACKERS] enums

Andrew Dunstan dunslane.net> writes: > If people would like to play, I have created a little kit to help in > creating first class enum types in a few seconds. Isn't what we actually want possreps? ---(end of broadcast)--- TIP 6: explain analyze

Re: [HACKERS] Surrogate keys (Was: enums)

Greg Stark mit.edu> writes: > I hate knee-jerk reactions too, but just think of all the pain of people > dealing with databases where they used Social Security numbers for primary > keys. I would never use an attribute that represents some real-world datum as > a primary key any more. I am not f

Re: [HACKERS] Surrogate keys (Was: enums)

Jim C. Nasby pervasive.com> writes: > a) the optimizer does a really poor job on multi-column index statistics So it should be fixed? And there are a *lot* of singular, natural keys. > b) If each parent record will have many children, the space savings from > using a surrogate key can be quit

Re: [HACKERS] Bad estimate on LIKE matching

> > I have tried upping the statistics target up to 1000, with > no changes. > > > Any way to teach the planner about this? > > In a recent thread on -perform, I opined that this case could > best be solved by using dynamic random block sampling at plan > time followed by a direct evaluation

Re: [HACKERS] equivalence class not working?

On Mon, 2006-01-16 at 19:03 -0500, uwcssa wrote: > Fine. The rest documentation says:" For now, the test only uses > restriction clauses (those in restrictinfo_list). --Nels, Dec '92", > however, I understand it as being overridden by the > followup, which is:"XXX as of 7.1, equivalence class info

Re: [HACKERS] Bad estimate on LIKE matching

On Tue, 2006-01-17 at 13:53 +0100, Magnus Hagander wrote: > On this table, I do a query like: > SELECT * FROM path WHERE path LIKE 'f:/userdirs/s/super_73/%' > > The estimate for this query is comlpetely off, which I beleive is the > cause for a very bad selection of a query plan when it's used i