Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On Tue, Nov 04, 2014 at 08:30:21AM +, Laurenz Albe wrote: > David Fetter wrote: > > On Tue, Nov 04, 2014 at 07:51:06AM +0900, Tatsuo Ishii wrote: > >> Just out of curiosity, why is Oracle's NUMBER (I assume you are > >> talking about this) so fast? > > > > I suspect that what happens is that N

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 11/04/2014 07:33 AM, Tom Lane wrote: > More generally, it seems like a grab bag of not terribly well designed > features, and the features that do seem well designed seem like they > ought to be more generic than just for int4 arrays. So to me it feels > like proof-of-concept experimentation ra

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Craig Ringer writes: > On 11/03/2014 03:41 AM, Tom Lane wrote: >> Nothing that I recall at the moment, but there is certainly plenty of >> stuff of dubious quality in there. I'd argue that chkpass, intagg, >> intarray, isn, spi, and xml2 are all in worse shape than the money type. > What's wrong

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 11/03/2014 10:11 PM, Tom Lane wrote: Josh Berkus writes: On 11/02/2014 11:41 AM, Tom Lane wrote: Nothing that I recall at the moment, but there is certainly plenty of stuff of dubious quality in there. I'd argue that chkpass, intagg, intarray, isn, spi, and xml2 are all in worse shape tha

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On Tue, Nov 4, 2014 at 8:16 AM, Kevin Grittner wrote: > In any event, I'm against removing or re-deprecating the money > type. There are some problems with money; there are other problems > with numeric. If the docs are failing to make the trade-offs > clear, we should fix the docs. And we can

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 11/02/2014 06:41 PM, Tom Lane wrote: I wrote: Either way, requiring a dump/reload for upgrade is surely a better answer for users of the type than just summarily screwing them. BTW, after reflecting a bit more I'm less than convinced that this datatype is completely useless. Even if you pr

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Feng Tian wrote: > Performance different between Money and Numeric is *HUGE*. For > TPCH Q1, the performance difference is 5x for stock postgres, and > ~20x for vitesse. > > Stock postgres, for my laptop, TPCH 1G, Q1, use money type ~ 9s, > use Numeric (15, 2) is ~53s. > >> test=# do $$ begin pe

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On Tue, Nov 04, 2014 at 11:44:22AM +0900, Michael Paquier wrote: > On Sun, Nov 2, 2014 at 2:30 AM, Tom Lane wrote: > > > In the case of hash indexes, because we still have to have the hash > > opclasses in core, there's no way that it could be pushed out as an > > extension module even if we othe

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

David Fetter wrote: > On Tue, Nov 04, 2014 at 07:51:06AM +0900, Tatsuo Ishii wrote: >> Just out of curiosity, why is Oracle's NUMBER (I assume you are >> talking about this) so fast? > > I suspect that what happens is that NUMBER is stored as a native type > (int2, int4, int8, int16) that depends

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Josh, Do you have a list of what needs to be done to keep the MONEY type? What is wrong with it? Thanks, -cktan On Mon, Nov 3, 2014 at 10:30 PM, Feng Tian wrote: > Hi, > > This is Feng from Vitesse. Performance different between Money and Numeric > is *HUGE*. For TPCH Q1, the performance di

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 11/03/2014 03:41 AM, Tom Lane wrote: > Nothing that I recall at the moment, but there is certainly plenty of > stuff of dubious quality in there. I'd argue that chkpass, intagg, > intarray, isn, spi, and xml2 are all in worse shape than the money type. What's wrong with intarray? I'm aware of

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Hi, This is Feng from Vitesse. Performance different between Money and Numeric is *HUGE*. For TPCH Q1, the performance difference is 5x for stock postgres, and ~20x for vitesse. Stock postgres, for my laptop, TPCH 1G, Q1, use money type ~ 9s, use Numeric (15, 2) is ~53s. Kevin, test=# do $$

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Michael Paquier writes: > There is as well another way: finally support WAL-logging for hash indexes. Sure, but that's a bit easier said than done. I think Robert looked into that a year or two back and found some stumbling blocks that it wasn't obvious how to surmount. I do hope it will happen

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Josh Berkus writes: > On 11/02/2014 11:41 AM, Tom Lane wrote: >> Nothing that I recall at the moment, but there is certainly plenty of >> stuff of dubious quality in there. I'd argue that chkpass, intagg, >> intarray, isn, spi, and xml2 are all in worse shape than the money type. > Why are we ho

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On Sun, Nov 2, 2014 at 2:30 AM, Tom Lane wrote: > In the case of hash indexes, because we still have to have the hash > opclasses in core, there's no way that it could be pushed out as an > extension module even if we otherwise had full support for AMs as > extensions. So what I hear you proposi

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 11/02/2014 11:41 AM, Tom Lane wrote: > Nothing that I recall at the moment, but there is certainly plenty of > stuff of dubious quality in there. I'd argue that chkpass, intagg, > intarray, isn, spi, and xml2 are all in worse shape than the money type. Why are we holding on to xml2 again? FWI

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On Tue, Nov 04, 2014 at 07:51:06AM +0900, Tatsuo Ishii wrote: > > The performance of our numeric vs Oracle's was a common complaint when > > I was at EnterpriseDB (in 2007). > > > > Perhaps numeric's performance could be greatly improved in cases where > > the precision is low enough to map to an

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Alvaro Herrera wrote: > There is a real advantage of money over numeric in the performance > front. I haven't measured it, but suffice to say that money uses > integer operations which map almost directly to CPU instructions, > whereas numeric needs to decode from our varlena base-1 digit >

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

> The performance of our numeric vs Oracle's was a common complaint when > I was at EnterpriseDB (in 2007). > > Perhaps numeric's performance could be greatly improved in cases where > the precision is low enough to map to an int/bigint. That would get us > closer to eliminating money as well as g

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 11/3/14, 1:34 PM, Alvaro Herrera wrote: David Fetter wrote: On Mon, Nov 03, 2014 at 01:54:09PM +0100, Michael Banck wrote: As an additional datapoint, Vitesse Data changed the DB schema from NUMERIC to MONEY for their TPCH benchmark for performance reasons: "The modification to data types is

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On Sat, Nov 1, 2014 at 10:26 AM, Andres Freund wrote: > On 2014-11-01 10:18:03 -0700, Josh Berkus wrote: > > On 10/31/2014 03:07 PM, Tom Lane wrote: > > > I don't care one way or the other about the money type, but I will > defend > > > hash indexes, especially seeing that we've already added a p

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

David Fetter wrote: > On Mon, Nov 03, 2014 at 01:54:09PM +0100, Michael Banck wrote: > > As an additional datapoint, Vitesse Data changed the DB schema from > > NUMERIC to MONEY for their TPCH benchmark for performance reasons: "The > > modification to data types is easy to understand -- money and

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On Mon, Nov 03, 2014 at 01:54:09PM +0100, Michael Banck wrote: > Am Sonntag, den 02.11.2014, 12:41 -0500 schrieb Tom Lane: > > BTW, after reflecting a bit more I'm less than convinced that this > > datatype is completely useless. Even if you prefer to store currency > > values in numeric columns,

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 11/1/14, 1:45 PM, Andrew Dunstan wrote: On 11/01/2014 02:34 PM, Andres Freund wrote: Yeah, if we were trying to duplicate the behavior of indisvalid, there'd need to be a way to detect the invalid index at plan time and not use it. But I'm not sure that that's actually an improvement from the

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Am Sonntag, den 02.11.2014, 12:41 -0500 schrieb Tom Lane: > BTW, after reflecting a bit more I'm less than convinced that this > datatype is completely useless. Even if you prefer to store currency > values in numeric columns, casting to or from money provides a way to > accept or emit values in w

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 11/02/2014 02:41 PM, Tom Lane wrote: Andrew Dunstan writes: On 11/02/2014 11:53 AM, Tom Lane wrote: Well, the OID compatibility issue could be dodged by saying that we can't do a pg_upgrade (in-place upgrade) of a database containing MONEY columns. In fact, we might be able to just reject

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Andrew Dunstan writes: > On 11/02/2014 11:53 AM, Tom Lane wrote: >> Well, the OID compatibility issue could be dodged by saying that we can't >> do a pg_upgrade (in-place upgrade) of a database containing MONEY >> columns. In fact, we might be able to just reject databases containing >> MONEY[] (

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 11/02/2014 11:53 AM, Tom Lane wrote: Andrew Dunstan writes: On 11/02/2014 10:01 AM, Jaime Casanova wrote: Not knowing how difficult it could be maybe a fair compromise is to move MONEY datatype to a contrib. And documenting its limitations. That's pretty much dead in the water, I think. B

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

I wrote: > Either way, requiring a dump/reload for upgrade is surely a better answer > for users of the type than just summarily screwing them. BTW, after reflecting a bit more I'm less than convinced that this datatype is completely useless. Even if you prefer to store currency values in numeric

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Andrew Dunstan writes: > On 11/02/2014 10:01 AM, Jaime Casanova wrote: >> Not knowing how difficult it could be maybe a fair compromise is to >> move MONEY datatype to a contrib. And documenting its limitations. > That's pretty much dead in the water, I think. Builtin types and > functions have

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 11/02/2014 10:01 AM, Jaime Casanova wrote: El nov 2, 2014 7:54 AM, "Andres Freund" > escribió: > > On 2014-11-01 16:59:35 -0700, Josh Berkus wrote: > > All, > > > > While there's argument about hash indexes, it looks like nobody minds if > > the MONEY type

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

El nov 2, 2014 7:54 AM, "Andres Freund" escribió: > > On 2014-11-01 16:59:35 -0700, Josh Berkus wrote: > > All, > > > > While there's argument about hash indexes, it looks like nobody minds if > > the MONEY type goes bye-bye. So, time for a patch ... > > FWIW there have been somewhat recent patch

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 2014-11-01 16:59:35 -0700, Josh Berkus wrote: > All, > > While there's argument about hash indexes, it looks like nobody minds if > the MONEY type goes bye-bye. So, time for a patch ... FWIW there have been somewhat recent patches for money and it was undeprecated not too long ago. So apparen

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On Sun, Nov 2, 2014 at 12:59 PM, Josh Berkus wrote: > All, > > While there's argument about hash indexes, it looks like nobody minds if > the MONEY type goes bye-bye. So, time for a patch ... > > Will the patch move the feature to a contrib module? Or will our release notes state that all MONEY

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

All, While there's argument about hash indexes, it looks like nobody minds if the MONEY type goes bye-bye. So, time for a patch ... -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subsc

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On Sat, Nov 1, 2014 at 12:40 PM, Andres Freund wrote: > I'm not all that excited about unlogged hash indexes. Yes, that'll > remove a annoying hazard, but I probably won't use them anyway. I am > somewhat excited about the more general unlogged indexes feature. I strongly agree. -- Peter Geogh

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Andres Freund writes: > On 2014-11-01 15:33:27 -0400, Tom Lane wrote: >> With current usage of hash indexes, nobody would ever construct such an >> arrangement > Do you think this should only be implemented for hash indexes? I'd think > we'd do it for all existing index AMs? I think we should ev

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 2014-11-01 15:33:27 -0400, Tom Lane wrote: > With current usage of hash indexes, nobody would ever construct such an > arrangement Do you think this should only be implemented for hash indexes? I'd think we'd do it for all existing index AMs? I'm not all that excited about unlogged hash indexe

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Andres Freund writes: > On 2014-11-01 15:11:40 -0400, Tom Lane wrote: >> I don't see how HS has anything to do with this discussion. > Consider: > SELECT * FROM tbl WHERE active AND value = $1; > that can be satisfied by two indexes. One on (value), and an unlogged > index on (value) WHERE active

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 2014-11-01 15:11:40 -0400, Tom Lane wrote: > Andres Freund writes: > > One argument in that direction imo is HS. We certainly would just > > generally ignore unlogged indexes for querying while InRecovery, right? > > Because otherwise HS would become pretty useless. And I think it'd be > > pret

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Andres Freund writes: > One argument in that direction imo is HS. We certainly would just > generally ignore unlogged indexes for querying while InRecovery, right? > Because otherwise HS would become pretty useless. And I think it'd be > pretty wierd if things worked on HS and not on the primary (

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Andrew Dunstan writes: > The real question here is whether the table should continue to be usable > in a degraded state until it's reindexed. It certainly will be, as long as your notion of "usable in a degraded state" doesn't include issuing queries that would prefer to use the broken index. T

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Andres Freund writes: > On 2014-11-01 14:48:20 -0400, Tom Lane wrote: >> Sure. And as long as you aren't issuing queries that would want to scan >> the crashed index, it won't matter either way. The question is whether >> you'd rather that your "inessential reporting queries" fail without the >>

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 2014-11-01 14:56:35 -0400, Andrew Dunstan wrote: > > On 11/01/2014 02:39 PM, Tom Lane wrote: > >Andres Freund writes: > >>A REINDEX is imo unlikely to be acceptable. It takes long (why would you > >>bother on a small table?) and locks the relation/indexes. > >I think the goalposts just took a

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 11/01/2014 02:39 PM, Tom Lane wrote: Andres Freund writes: A REINDEX is imo unlikely to be acceptable. It takes long (why would you bother on a small table?) and locks the relation/indexes. I think the goalposts just took a vacation to Acapulco. What exactly do you think is going to make

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 2014-11-01 14:48:20 -0400, Tom Lane wrote: > Andres Freund writes: > > On 2014-11-01 14:39:21 -0400, Tom Lane wrote: > >> What exactly do you think is going to make a crashed unlogged index valid > >> again without a REINDEX? Certainly the people who are currently using > >> hash indexes in th

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Andrew Dunstan writes: > It's a bit of a pity we don't have REINDEX CONCURRENTLY. Well, that's an entirely different thread. Let's not get distracted by that topic. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 2014-11-01 14:45:47 -0400, Andrew Dunstan wrote: > > On 11/01/2014 02:34 PM, Andres Freund wrote: > >>Yeah, if we were trying to duplicate the behavior of indisvalid, there'd > >>need to be a way to detect the invalid index at plan time and not use it. > >>But I'm not sure that that's actually

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Andres Freund writes: > On 2014-11-01 14:39:21 -0400, Tom Lane wrote: >> What exactly do you think is going to make a crashed unlogged index valid >> again without a REINDEX? Certainly the people who are currently using >> hash indexes in the way Andrew describes are expecting to have to REINDEX

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 11/01/2014 02:34 PM, Andres Freund wrote: Yeah, if we were trying to duplicate the behavior of indisvalid, there'd need to be a way to detect the invalid index at plan time and not use it. But I'm not sure that that's actually an improvement from the user's standpoint: what they'd see is quer

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 2014-11-01 14:39:21 -0400, Tom Lane wrote: > Andres Freund writes: > > A REINDEX is imo unlikely to be acceptable. It takes long (why would you > > bother on a small table?) and locks the relation/indexes. > > I think the goalposts just took a vacation to Acapulco. I think that might be cause

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Andres Freund writes: > A REINDEX is imo unlikely to be acceptable. It takes long (why would you > bother on a small table?) and locks the relation/indexes. I think the goalposts just took a vacation to Acapulco. What exactly do you think is going to make a crashed unlogged index valid again wit

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 2014-11-01 14:23:07 -0400, Tom Lane wrote: > Andres Freund writes: > > On 2014-11-01 13:58:02 -0400, Tom Lane wrote: > >> maybe we don't have to. What about having the init-fork mechanism restore > >> a hash index into a state with the metapage marked as invalid? hashinsert > >> etc could sim

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Andres Freund writes: > On 2014-11-01 14:19:22 -0400, Andrew Dunstan wrote: >> Isn't the planner still going to try to use the index in that case? If it's >> not then I'd be OK with it, but if it's going to make the table largely >> unusable until it's reindexed that would be rather sad. > Both t

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 2014-11-01 14:19:22 -0400, Andrew Dunstan wrote: > Isn't the planner still going to try to use the index in that case? If it's > not then I'd be OK with it, but if it's going to make the table largely > unusable until it's reindexed that would be rather sad. Both the planner (for querying) and

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Andres Freund writes: > On 2014-11-01 13:58:02 -0400, Tom Lane wrote: >> maybe we don't have to. What about having the init-fork mechanism restore >> a hash index into a state with the metapage marked as invalid? hashinsert >> etc could simply do nothing when they see this metapage state. >> has

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 11/01/2014 01:58 PM, Tom Lane wrote: Andres Freund writes: On 2014-11-01 10:18:03 -0700, Josh Berkus wrote: Yes, and I'm arguing that is the wrong decision. If hash indexes are "discouraged", then they shouldn't be in core in the first place. Last time we discussed it there were people (

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 2014-11-01 14:13:02 -0400, Andrew Dunstan wrote: > Yes, although there might not be much reason to use them either. I think > Tom's first step of making hash indexes automatically unlogged makes sense. > Longer term I'd like to see unlogged as an option for all AMs - especially > btree. If we i

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 11/01/2014 01:26 PM, Andres Freund wrote: On 2014-11-01 10:18:03 -0700, Josh Berkus wrote: On 10/31/2014 03:07 PM, Tom Lane wrote: I don't care one way or the other about the money type, but I will defend hash indexes, especially seeing that we've already added a pretty in-your-face warning

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 2014-11-01 13:58:02 -0400, Tom Lane wrote: > Yeah. When we last discussed this, the difficulty was around how to make > that combination work. An unlogged index on an unlogged table is no > problem: the init-fork mechanism is able to make them both go to empty > after a crash. But for an unlo

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Andres Freund writes: > On 2014-11-01 10:18:03 -0700, Josh Berkus wrote: >> Yes, and I'm arguing that is the wrong decision. If hash indexes are >> "discouraged", then they shouldn't be in core in the first place. > Last time we discussed it there were people (IIRC Andrew was one of > them) comm

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Josh Berkus writes: > On 10/31/2014 03:07 PM, Tom Lane wrote: >> I don't care one way or the other about the money type, but I will defend >> hash indexes, especially seeing that we've already added a pretty >> in-your-face warning as of 9.5: >> >> regression=# create table foo(f1 int); >> CREATE

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 2014-11-01 10:18:03 -0700, Josh Berkus wrote: > On 10/31/2014 03:07 PM, Tom Lane wrote: > > I don't care one way or the other about the money type, but I will defend > > hash indexes, especially seeing that we've already added a pretty > > in-your-face warning as of 9.5: > > > > regression=# cr

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On 10/31/2014 03:07 PM, Tom Lane wrote: > I don't care one way or the other about the money type, but I will defend > hash indexes, especially seeing that we've already added a pretty > in-your-face warning as of 9.5: > > regression=# create table foo(f1 int); > CREATE TABLE > regression=# create

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Hi, On Sat, Nov 1, 2014 at 1:21 PM, Eric Ridge wrote: > On Fri, Oct 31, 2014 at 6:07 PM, Tom Lane wrote: >> >> I don't know if/when that will happen as such, but Simon was making noises >> about writing code to treat hash indexes as unlogged automatically, which >> would more or less fix the wor

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

On Fri, Oct 31, 2014 at 6:07 PM, Tom Lane wrote: > I don't know if/when that will happen as such, but Simon was making noises > about writing code to treat hash indexes as unlogged automatically, which > would more or less fix the worst risks. That's not just a special case > for hash indexes, b

Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices

Josh Berkus writes: > Of course, I'm talking about the MONEY type and hash indexes (not the > hash ops class, which is useful, just the index type). It's time to put > both of these features out to pasture. Certainly neither of theise > features would be accepted into PostgreSQL today given the