Re: [HACKERS] single bit integer (TINYINT) revisited for 8.5
On Fri, 2009-07-03 at 13:38 -0400, Robert Treat wrote: On Thursday 02 July 2009 12:40:49 Simon Riggs wrote: On Wed, 2009-07-01 at 11:19 -0400, Caleb Cushing wrote: A couple of times I've been told you don't need tinyint, use boolean which is not true, several projects I've worked on I've needed and integer field that supports number within a small range 0-5 1-10 1-100 or something similar. I end up using smallint but it's range is huge for the actual requirements. Completely agree. Blech. More often than not, I find people using all these granular types to be nothing more than premature optimization. And if you really do need a single byte type, you can use char (though again I'm not a big fan of that) I agree that many optimizations are used inappropriately. Another reason for making it an add-on module. I'm aware of char and it doesn't do all I would wish. I'm most or the way through working on this as an add-on module, rather than a new datatype in core. I don't see much reason to include it in core: its not an SQL standard datatype, it complicates catalog entries and most people don't need or want it. That's too bad. I'd much rather see someone implement something closer to Oracle's number type. Please explain what you mean? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] single bit integer (TINYINT) revisited for 8.5
On Thursday 02 July 2009 12:40:49 Simon Riggs wrote: On Wed, 2009-07-01 at 11:19 -0400, Caleb Cushing wrote: A couple of times I've been told you don't need tinyint, use boolean which is not true, several projects I've worked on I've needed and integer field that supports number within a small range 0-5 1-10 1-100 or something similar. I end up using smallint but it's range is huge for the actual requirements. Completely agree. Blech. More often than not, I find people using all these granular types to be nothing more than premature optimization. And if you really do need a single byte type, you can use char (though again I'm not a big fan of that) I'm most or the way through working on this as an add-on module, rather than a new datatype in core. I don't see much reason to include it in core: its not an SQL standard datatype, it complicates catalog entries and most people don't need or want it. That's too bad. I'd much rather see someone implement something closer to Oracle's number type. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] single bit integer (TINYINT) revisited for 8.5
On Wed, 2009-07-01 at 11:19 -0400, Caleb Cushing wrote: I'd like to see this topic revisited since as far as I can see it hasn't been seriously discussed in years. I believe the main arguments against are why do we need more more numeric datatypes and increased maintenance. It would seem to me that a tinyint datatype maintenance wise would get all the same updates as the other int types, making it only a slight increase in maintenance. I think there was 1 more reason but I can't find the original thread now. most (if not all?) of posgresql's major competitor's (mysql, sql server, db2, etc) support a single bit integer datatype. it would bring increased compatibility with existing mysql apps esp, making them easier to port. It (in theory?) should also bring a speed enhancement where usable since it would take less disk space. A couple of times I've been told you don't need tinyint, use boolean which is not true, several projects I've worked on I've needed and integer field that supports number within a small range 0-5 1-10 1-100 or something similar. I end up using smallint but it's range is huge for the actual requirements. Completely agree. I'm most or the way through working on this as an add-on module, rather than a new datatype in core. I don't see much reason to include it in core: its not an SQL standard datatype, it complicates catalog entries and most people don't need or want it. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] single bit integer (TINYINT) revisited for 8.5
I'd like to see this topic revisited since as far as I can see it hasn't been seriously discussed in years. I believe the main arguments against are why do we need more more numeric datatypes and increased maintenance. It would seem to me that a tinyint datatype maintenance wise would get all the same updates as the other int types, making it only a slight increase in maintenance. I think there was 1 more reason but I can't find the original thread now. most (if not all?) of posgresql's major competitor's (mysql, sql server, db2, etc) support a single bit integer datatype. it would bring increased compatibility with existing mysql apps esp, making them easier to port. It (in theory?) should also bring a speed enhancement where usable since it would take less disk space. A couple of times I've been told you don't need tinyint, use boolean which is not true, several projects I've worked on I've needed and integer field that supports number within a small range 0-5 1-10 1-100 or something similar. I end up using smallint but it's range is huge for the actual requirements. -- Caleb Cushing http://xenoterracide.blogspot.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] single bit integer (TINYINT) revisited for 8.5
Caleb Cushing xenoterrac...@gmail.com wrote: most (if not all?) of posgresql's major competitor's (mysql, sql server, db2, etc) support a single bit integer datatype. A couple of times I've been told you don't need tinyint, use boolean which is not true, several projects I've worked on I've needed and integer field that supports number within a small range 0-5 1-10 1-100 or something similar. I think you mean byte where you've said bit. Boolean would be adequate for a single bit, and I haven't (so far) seen any database which supports both a single-bit type and a boolean. Many databases support a TINYINT type as a single-byte value, although I'm not sure there's consistency on whether that's a signed or unsigned value. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] single bit integer (TINYINT) revisited for 8.5
Caleb. I'd like to see this topic revisited since as far as I can see it hasn't been seriously discussed in years. I believe the main arguments against are why do we need more more numeric datatypes and increased maintenance. It would seem to me that a tinyint datatype maintenance wise would get all the same updates as the other int types, making it only a slight increase in maintenance. I think there was 1 more reason but I can't find the original thread now. The main reason not to have one is that given byte-alignment, 95% of the time using a tinyint would save no actual disk space or memory over just using INT2 (or indeed INT4). I'll point out that the MySQLers are enamored of the 3-byte integer, which even on MySQL saves zero space or memory over using a 4-byte. Just because people like it doesn't mean it works. So the only real point in having an INT1 would be if you had a table with a lot of them in a row; an unusual but not unheard-of design. Still, that makes this feature less-than-critical for most users. But ... the nice thing about PostgreSQL is that data types can be loaded at runtime. Which means that you don't need INT1 in core for it to be useful to you and others; just write the data type and put it on pgFoundry. Then submit it for /contrib for 8.5, and we'll see how popular the idea is. Overall, I'm not keen on it. For the handful of times when peformance-optimization-by-datatype makes sense, there's a large number where it's develpers who have no idea what they're doing. We should be moving in the direction of having the database engine take care of space optimizations, not having the user do it. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] single bit integer (TINYINT) revisited for 8.5
Josh Berkus j...@agliodbs.com writes: But ... the nice thing about PostgreSQL is that data types can be loaded at runtime. Which means that you don't need INT1 in core for it to be useful to you and others; just write the data type and put it on pgFoundry. Yeah. The argument against that used to be that you couldn't integrate it properly into the numeric type hierarchy, but I think that's no longer the case now that add-on types can define which type category they belong to. At the very least it would be useful to try to do it as an add-on and see if there are still any obstacles to that. Which of course brings up the other argument against doing it, which was that it'd possibly mess up the rather delicate arrangement of implicit promotions among the numeric types. That was based on very old bad experiences, though, so I'm not certain if there's still a problem. Again, if there is a reason why an add-on type couldn't solve the problem while a built-in type could, I'd be more interested in finding and fixing that problem than in the value of INT1 per se. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] single bit integer (TINYINT) revisited for 8.5
On Wed, Jul 1, 2009 at 11:41 AM, Kevin Grittnerkevin.gritt...@wicourts.gov wrote: I think you mean byte where you've said bit. you're correct. I'm being a nerf. Boolean would be adequate for a single bit, and I haven't (so far) seen any database which supports both a single-bit type and a boolean. wasn't aware of that. I'm admittedly most familiar with sqlite, postgres, and mysql Many databases support a TINYINT type as a single-byte value, although I'm not sure there's consistency on whether that's a signed or unsigned value. wouldn't any implementation in pg support both? -- Caleb Cushing http://xenoterracide.blogspot.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] single bit integer (TINYINT) revisited for 8.5
Caleb Cushing xenoterrac...@gmail.com writes: On Wed, Jul 1, 2009 at 11:41 AM, Kevin Grittnerkevin.gritt...@wicourts.gov wrote: Many databases support a TINYINT type as a single-byte value, although I'm not sure there's consistency on whether that's a signed or unsigned value. wouldn't any implementation in pg support both? Introducing unsigned types into PG is a whole different discussion. The problem there is designing reasonable automatic promotion rules. Considering that C's rules still confuse people after nigh 40 years, I'm not enthusiastic about it ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] single bit integer (TINYINT) revisited for 8.5
On Wed, Jul 1, 2009 at 12:09 PM, Josh Berkusj...@agliodbs.com wrote: The main reason not to have one is that given byte-alignment, 95% of the time using a tinyint would save no actual disk space or memory over just using INT2 (or indeed INT4). I'll point out that the MySQLers are enamored of the 3-byte integer, which even on MySQL saves zero space or memory over using a 4-byte. Just because people like it doesn't mean it works. So the only real point in having an INT1 would be if you had a table with a lot of them in a row; an unusual but not unheard-of design. Still, that makes this feature less-than-critical for most users. The primary type of field that I see it used as involves some sort of statistics collection. like ratings, or maybe strongly agree - strongly disagree (this could be in hundreds of colums), even collecting gender's is appropriate if I recall.. as the iso for genders is an integer with like 4 possible value's. At least this is my thought, and a lot of these things end up in the same table. It's not critical, but I'd suggest the benefits outweigh the costs. But ... the nice thing about PostgreSQL is that data types can be loaded at runtime. Which means that you don't need INT1 in core for it to be useful to you and others; just write the data type and put it on pgFoundry. Then submit it for /contrib for 8.5, and we'll see how popular the idea is. I suppose I can see what I can do to this end (since it seems to be the consensus) although it'll take me a while given I've no idea what I'm doing... I can read C better than I write it, which doesn't say much. On the other hand I'd think this would be on the list of 'easiest things to do' which means I should be able to get it done. Overall, I'm not keen on it. For the handful of times when peformance-optimization-by-datatype makes sense, there's a large number where it's develpers who have no idea what they're doing. We should be moving in the direction of having the database engine take care of space optimizations, not having the user do it. eh... not sure I agree on that 100%... but to some degree that's what sql is for... at the same time that kinda sounds like sqlite's more dynamic like typing. but it is another discussion -- Caleb Cushing http://xenoterracide.blogspot.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] single bit integer (TINYINT) revisited for 8.5
Incidentally there *is* a single-byte integer data type in Postgres, it's called char (the quote marks are necessary in SQL due to the char(n) data type). It's a bit weird though, mainly because its output format is to output ascii characters -- kind of like how C's single-byte integer data type is mainly used to hold ascii characters... -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
