Thread: Re: [GENERAL] A real currency type
This looks very interesting. Should we add it to the core distribution? --------------------------------------------------------------------------- Martijn van Oosterhout wrote: -- Start of PGP signed section. > For a while I've been wondering about making a type that was really a > shell around a base type that tagged the type in some way. For example, > associating a currency with a numeric and complaining about additions > between mismatches. > > Well, I did it and it's available here: > http://svana.org/kleptog/pgsql/taggedtypes.html > > Below some examples of it in action. Yes, that's a timestamp that > remembers the timezone. Neat huh? > > Tested on 7.4 and a recent 8.1devel so it should work for most people. > Installation reports welcome. Note, this is beta software, don't run it > on your production server. Thanks. > > Have a nice day, > > > test=# select '5.6 USD'::currency + '4.5 USD'::currency;; > ?column? > ----------- > 10.10 USD > (1 row) > > test=# select '5.6 USD'::currency + '4.5 AUD'::currency;; > ERROR: Using operator +(currency,currency) with incompatable tags (USD,AUD) > test=# select c1, print_currency(c1) from c; > c1 | print_currency > ------------+---------------- > 232.44 USD | US$ 232.44 > 21.20 EUR | ? 21.20 > -13.44 AUD | AU$ -13.44 > 0.01 USD | US$ 0.01 > 14.00 AUD | AU$ 14.00 > (5 rows) > > test=# select 5.4*c1 from c where tag(c1) = 'AUD'; > ?column? > ------------ > -72.58 AUD > 75.60 AUD > (2 rows) > > test=# select t, "timestamp"(t), date_part('hour',t) from c; > t | timestamp | date_part > -----------------------------------------+---------------------+----------- > 2005-08-14 02:00:00+02 Europe/Amsterdam | 2005-08-14 02:00:00 | 2 > 2005-08-14 02:00:00+02 Australia/Sydney | 2005-08-14 10:00:00 | 10 > 2005-08-14 02:00:00+02 Asia/Hong_Kong | 2005-08-14 08:00:00 | 8 > 2005-08-14 02:00:00+02 America/New_York | 2005-08-13 20:00:00 | 20 > 2005-08-14 02:00:00+02 Asia/Kuwait | 2005-08-14 03:00:00 | 3 > (5 rows) > > -- > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > > tool for doing 5% of the work and then sitting around waiting for someone > > else to do the other 95% so you can sue them. -- End of PGP section, PGP failed! -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. +
On Tue, Mar 21, 2006 at 08:49:18AM -0500, Bruce Momjian wrote: > > This looks very interesting. Should we add it to the core distribution? Excellent question. As yet I have received very little feedback on it, though it does work as advertised. I have had some people complain that while they'd like to use it (especially the timestamp-that-remembers- the-timezone), they don't like the idea of an external module. I suppose the are a few technical issues that could be raised, like the fact that it searches user tables during the parse phase, but this is a generic problem with non-immutable type input functions. I'd like it to be considered for inclusion. If the interest is there I can make any changes people suggest. Have a nice day, > > --------------------------------------------------------------------------- > > Martijn van Oosterhout wrote: > -- Start of PGP signed section. > > For a while I've been wondering about making a type that was really a > > shell around a base type that tagged the type in some way. For example, > > associating a currency with a numeric and complaining about additions > > between mismatches. > > > > Well, I did it and it's available here: > > http://svana.org/kleptog/pgsql/taggedtypes.html > > > > Below some examples of it in action. Yes, that's a timestamp that > > remembers the timezone. Neat huh? > > > > Tested on 7.4 and a recent 8.1devel so it should work for most people. > > Installation reports welcome. Note, this is beta software, don't run it > > on your production server. Thanks. > > > > Have a nice day, > > > > > > test=# select '5.6 USD'::currency + '4.5 USD'::currency;; > > ?column? > > ----------- > > 10.10 USD > > (1 row) > > > > test=# select '5.6 USD'::currency + '4.5 AUD'::currency;; > > ERROR: Using operator +(currency,currency) with incompatable tags (USD,AUD) > > test=# select c1, print_currency(c1) from c; > > c1 | print_currency > > ------------+---------------- > > 232.44 USD | US$ 232.44 > > 21.20 EUR | ? 21.20 > > -13.44 AUD | AU$ -13.44 > > 0.01 USD | US$ 0.01 > > 14.00 AUD | AU$ 14.00 > > (5 rows) > > > > test=# select 5.4*c1 from c where tag(c1) = 'AUD'; > > ?column? > > ------------ > > -72.58 AUD > > 75.60 AUD > > (2 rows) > > > > test=# select t, "timestamp"(t), date_part('hour',t) from c; > > t | timestamp | date_part > > -----------------------------------------+---------------------+----------- > > 2005-08-14 02:00:00+02 Europe/Amsterdam | 2005-08-14 02:00:00 | 2 > > 2005-08-14 02:00:00+02 Australia/Sydney | 2005-08-14 10:00:00 | 10 > > 2005-08-14 02:00:00+02 Asia/Hong_Kong | 2005-08-14 08:00:00 | 8 > > 2005-08-14 02:00:00+02 America/New_York | 2005-08-13 20:00:00 | 20 > > 2005-08-14 02:00:00+02 Asia/Kuwait | 2005-08-14 03:00:00 | 3 > > (5 rows) > > > > -- > > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > > > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > > > tool for doing 5% of the work and then sitting around waiting for someone > > > else to do the other 95% so you can sue them. > -- End of PGP section, PGP failed! > > -- > Bruce Momjian http://candle.pha.pa.us > SRA OSS, Inc. http://www.sraoss.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 -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
>> This looks very interesting. Should we add it to the core >> distribution? > > Excellent question. As yet I have received very little feedback on it, > though it does work as advertised. I have had some people complain > that > while they'd like to use it (especially the timestamp-that-remembers- > the-timezone), they don't like the idea of an external module. > > I suppose the are a few technical issues that could be raised, like > the > fact that it searches user tables during the parse phase, but this > is a > generic problem with non-immutable type input functions. > > I'd like it to be considered for inclusion. If the interest is there I > can make any changes people suggest. There was an old thread on the list about "extended type". The general idea was to add a attribute to each column to represent the "unit" (as for physical measure). I had worked on a development system that use this kind of "extended type" and it was very good to catch errors by checking the compatibility of extended type. Cordialement, Jean-Gérard Pailloncy
Bruce, Martijin, > > This looks very interesting. Should we add it to the core distribution? > > Excellent question. As yet I have received very little feedback on it, > though it does work as advertised. I have had some people complain that > while they'd like to use it (especially the timestamp-that-remembers- > the-timezone), they don't like the idea of an external module. So, how about adding it to contrib for one version? -- Josh Berkus Aglio Database Solutions San Francisco
On Tue, Mar 21, 2006 at 06:15:29PM +0100, Pailloncy Jean-Gerard wrote: > >>This looks very interesting. Should we add it to the core > >>distribution? > > > >Excellent question. As yet I have received very little feedback on it, > >though it does work as advertised. I have had some people complain > >that > >while they'd like to use it (especially the timestamp-that-remembers- > >the-timezone), they don't like the idea of an external module. > > > >I suppose the are a few technical issues that could be raised, like > >the > >fact that it searches user tables during the parse phase, but this > >is a > >generic problem with non-immutable type input functions. > > > >I'd like it to be considered for inclusion. If the interest is there I > >can make any changes people suggest. > There was an old thread on the list about "extended type". > > The general idea was to add a attribute to each column to represent > the "unit" (as for physical measure). > > I had worked on a development system that use this kind of "extended > type" and it was very good to catch errors by checking the > compatibility of extended type. While I'm not sure it makes sense to include such an 'extended type' in the base system, it would be a neat project to have on pgFoundry. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Tue, Mar 21, 2006 at 09:39:09AM -0800, Josh Berkus wrote: > Bruce, Martijin, > > > > This looks very interesting. Should we add it to the core distribution? > > > > Excellent question. As yet I have received very little feedback on it, > > though it does work as advertised. I have had some people complain that > > while they'd like to use it (especially the timestamp-that-remembers- > > the-timezone), they don't like the idea of an external module. > > So, how about adding it to contrib for one version? ISTM that having a currency type is pretty common for most databases; I don't really see any reason not to just include it. Likewise for a type that actually stores timezone info with a timestamp. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On 3/21/06, Jim C. Nasby <jnasby@pervasive.com> wrote: > ISTM that having a currency type is pretty common for most databases; I > don't really see any reason not to just include it. Likewise for a type > that actually stores timezone info with a timestamp. This really should be generalized to work with all the base types because there are strong use cases for each. (timezones with timestamps, currencies with numeric, physical units with floats and ints)
On Tue, Mar 21, 2006 at 02:00:14PM -0500, Gregory Maxwell wrote: > On 3/21/06, Jim C. Nasby <jnasby@pervasive.com> wrote: > > ISTM that having a currency type is pretty common for most databases; I > > don't really see any reason not to just include it. Likewise for a type > > that actually stores timezone info with a timestamp. > > This really should be generalized to work with all the base types > because there are strong use cases for each. (timezones with > timestamps, currencies with numeric, physical units with floats and > ints) Have you looked at the code? It *is* generalised. All you need to do is provide a table of tags and indicate the base-type. Hence: currency type = table of currencies + numeric base type timestamp+zone type = table of timezones + timestamp base type physical unit = table of units + numeric base type These are examples already included in the code, but I'm sure people can come up with more. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Bruce Momjian wrote: > This looks very interesting. Should we add it to the core > distribution? I think such types would be better implemented as some sort of structured type, possibly with constructors and methods and all the other stuff that SQL talks about. We don't have all of that yet -- maybe we don't need all of it immediately -- but before we start endorsing many of these types I'd like to see some consideration given to this issue. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: >Bruce Momjian wrote: > > >>This looks very interesting. Should we add it to the core >>distribution? >> >> > >I think such types would be better implemented as some sort of >structured type, possibly with constructors and methods and all the >other stuff that SQL talks about. We don't have all of that yet -- >maybe we don't need all of it immediately -- but before we start >endorsing many of these types I'd like to see some consideration given >to this issue. > > > Yeah, looks interesting but let's find the wrinkles. I was wondering if it might have relevance to what I wanted to do with enumeration types, i.e. we would tag each one with its particular enumeration id. I also don't like the idea of it being done with user tables - if this is core material then the tags should go in the catalog, ISTM. cheers andrew
Martijn van Oosterhout <kleptog@svana.org> writes:
> I'd like it to be considered for inclusion.
The description page lists sufficiently many unresolved issues that
I'd have to call it "not ready for prime time" ... even assuming there
are not any issues you failed to identify. One showstopper I can cite
immediately is the lack of any pg_dump support.
I could see putting this in contrib, perhaps, but to become part of core
it'd need to be better designed and better integrated. That probably
means fixing some limitations in the current core code (for instance the
need for the auxiliary-type kluge).
Peter's point that there may exist SQL2003 features that cover the same
ground is also well taken ... we should investigate that before
inventing nonstandard stuff that we'll be stuck with supporting
forevermore.
regards, tom lane
On Tue, Mar 21, 2006 at 04:10:21PM -0500, Tom Lane wrote: > Martijn van Oosterhout <kleptog@svana.org> writes: > > I'd like it to be considered for inclusion. > > The description page lists sufficiently many unresolved issues that > I'd have to call it "not ready for prime time" ... even assuming there > are not any issues you failed to identify. One showstopper I can cite > immediately is the lack of any pg_dump support. Really? The code creates ordinary types, operators and functions, all of which are dumped fine by pg_dump. Dependancies are created which should ensure the parts get dumped in the right order. What special support in pg_dump were you envisioning? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
On Tue, Mar 21, 2006 at 03:59:31PM -0500, Andrew Dunstan wrote: > >I think such types would be better implemented as some sort of > >structured type, possibly with constructors and methods and all the > >other stuff that SQL talks about. We don't have all of that yet -- > >maybe we don't need all of it immediately -- but before we start > >endorsing many of these types I'd like to see some consideration given > >to this issue. > > Yeah, looks interesting but let's find the wrinkles. I was wondering if > it might have relevance to what I wanted to do with enumeration types, > i.e. we would tag each one with its particular enumeration id. I have considered making enumeration types with this. It would probably work but kind of overkill. The main problem being that this code is seriously aimed at creating new types that you intend to do operations on, create operators for. An enumeration is just that, an enumeration. > I also don't like the idea of it being done with user tables - if this > is core material then the tags should go in the catalog, ISTM. But the user is providing the list of tags when creating the type. Unless you are proposing copying the tag table to the catalog when the type is created? You can't store all the tags for all tagged types in one tag table either. At least, that wasn't what I was envisiging. See the currency type where the tag-table also stores the format string for output. No other tagged type is going to need that information. SQL types with constructors and methods might provide this also, I don't know. My purpose with this was allowing people to simply create new types with specific behaviour without writing functions in C. Right now type input/output functions cannot be written in anything other than C. Here are a few C functions that handle these new types in a generic way and user can write little pl/pgsql wrappers to customise the output. Have a ncie day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Martijn van Oosterhout <kleptog@svana.org> writes:
> Really? The code creates ordinary types, operators and functions, all
> of which are dumped fine by pg_dump. Dependancies are created which
> should ensure the parts get dumped in the right order. What special
> support in pg_dump were you envisioning?
The dump should look the same as the commands originally used to create
the type, which is surely not going to happen with that "SELECT
create_tagged_type()" stuff barring pg_dump modifications. Otherwise
we are nailing down not one but two representations of this feature that
we'll have to support forevermore: what the users see and what's in
pg_dump scripts. We've already learned that lesson the hard way several
times, and are still trying to cope with the fallout in some places
(serial columns for instance).
Now I happen to think that SELECT create_tagged_type() is a horrid kluge
anyway ;-) so I'm not proposing that pg_dump be changed to output that.
What we would need is to design a command syntax that we're actually
prepared to live with for the indefinite future, then implement it in
the backend and teach pg_dump about it.
What we *don't* want is a pg_dump representation that exposes
implementation details. I would classify both the backing table for a
tagged type's enum values, and the representation of its operators and
functions, as implementation details.
BTW, I share Andrew Dunstan's feeling that there's huge overlap here
with support for mysql-like enum types. We ought to try to kill that
bird with the same stone.
regards, tom lane
On Tue, Mar 21, 2006 at 05:25:15PM -0500, Tom Lane wrote: > The dump should look the same as the commands originally used to create > the type, which is surely not going to happen with that "SELECT > create_tagged_type()" stuff barring pg_dump modifications. Otherwise > we are nailing down not one but two representations of this feature that > we'll have to support forevermore: what the users see and what's in > pg_dump scripts. Well, I agree with you in principle there. However, when you install tsearch2 and do a pg_dump you don't get just a "\i tsearch.sql" out either (I did propose something like this once though). Now, tsearch2 isn't integrated either, so I guess that the difference. Let me put it this way: if this is to progress beyond just a contrib module, it needs to go all the way (special syntax, pg_dump, etc). I'm not sure if I'm that enamoured with it to want all that. > Now I happen to think that SELECT create_tagged_type() is a horrid kluge > anyway ;-) so I'm not proposing that pg_dump be changed to output that. > What we would need is to design a command syntax that we're actually > prepared to live with for the indefinite future, then implement it in > the backend and teach pg_dump about it. Ack. > What we *don't* want is a pg_dump representation that exposes > implementation details. I would classify both the backing table for a > tagged type's enum values, and the representation of its operators and > functions, as implementation details. One slight problem: the "backing table" is provided by the creator of the type. And in principle they could be altered once the type is created. So if the user creates a new tagged type, the pg_dump output is going to have to output the backing table in some form, if not COPY then something else. The only way to avoid that is if both the type and the backing table are included in the standard distribution and we forbid user changes. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Martijn van Oosterhout <kleptog@svana.org> writes:
> Let me put it this way: if this is to progress beyond just a contrib
> module, it needs to go all the way (special syntax, pg_dump, etc). I'm
> not sure if I'm that enamoured with it to want all that.
My feelings in a nutshell ;-)
> The only way to avoid that is if both the type and the backing table
> are included in the standard distribution and we forbid user changes.
I was thinking something more like a CREATE ENUM TYPE command that
specifically lists the enum values, and some extension of that to cater
for tagged types, and the values are put into a system catalog that the
user doesn't manipulate directly. I don't see why it's a good idea to
put control of the backing table in the user's hands. Yes, you can
think of advanced applications where it's useful to have random
additional stuff in the table, but that's exactly the point at which you
normally have to get down-and-dirty with some C code --- after all, what
is standardized code going to *do* with the additional stuff? Nothing,
that's what. If the argument for this is to make it simple to make
simple enum and tagged types, then I don't think that the design should
be centered on allowing extra stuff.
regards, tom lane
On Tue, Mar 21, 2006 at 05:55:09PM -0500, Tom Lane wrote: <snip> > Yes, you can > think of advanced applications where it's useful to have random > additional stuff in the table, but that's exactly the point at which you > normally have to get down-and-dirty with some C code --- after all, what > is standardized code going to *do* with the additional stuff? Nothing, > that's what. If the argument for this is to make it simple to make > simple enum and tagged types, then I don't think that the design should > be centered on allowing extra stuff. Umm, actually that extra info is very useful. For example in the currency type it can store the currency symbol and how many decimal places, etc. The code uses it by converting the datum into a composite form (base,tag) (the "auxilliary type") or by using the taginfo function return the info tuple directly. These are easily manipulated by straight SQL or pl/pgsql functions. While it may not be critical to the use of then, it's an extremely handy feature. Half the point of a currency type would be the formatting, no? The information needs to be stored somewhere... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Tue, Mar 21, 2006 at 05:55:09PM -0500, Tom Lane wrote:
>> Yes, you can
>> think of advanced applications where it's useful to have random
>> additional stuff in the table, but that's exactly the point at which you
>> normally have to get down-and-dirty with some C code --- after all, what
>> is standardized code going to *do* with the additional stuff? Nothing,
>> that's what.
> Umm, actually that extra info is very useful. For example in the
> currency type it can store the currency symbol and how many decimal
> places, etc.
And you do what with that in non-currency-specific code? This argument
isn't holding water for me. The "decimal places" info is in the wrong
place anyway, it should be attached to the underlying numeric type...
regards, tom lane
Tom Lane said: > I was thinking something more like a CREATE ENUM TYPE command that > specifically lists the enum values, and some extension of that to cater > for tagged types, and the values are put into a system catalog that the > user doesn't manipulate directly. I don't see why it's a good idea to > put control of the backing table in the user's hands. Me either. I like the sound of this. cheers andrew
Without directly addressing the merits of enumerations, enumeration interfaces, real currency and time zone types, or whether currency and time zone types should be built using enumerations, I would like to ask the powers-that-be to seriously consider radically modularizing Postgresql's type system. The core Postgresql installation would come with just those built-in types needed to bootstrap itself, perhaps just varchar and an integer type. Everything else would be a contributed module. An interface or contract would be described for creating additional types. It would include things like parameter handlers, how to dump the type, and how to load the type. (That is, standard housekeeping functions needed by the Postgresql engine.) Other that the tiny number of bootstrap types, Postgresql types would basically all be contrib modules. Types could be bundled into groups such as binary, character, numerical, 2d-spatial, networking, and so on. Then one would not debate whether a type (or meta-type, like an enumeration) should be put into the core product. Instead, the debate would be whether or not to grade the type as "mature" and whether or not to put a given type into pre-packaged type libraries with names like "legacy", "sql-2003-standard", or "recommended-default". Power user DBA's could customize the types offered on their systems. In short: 1) Types would be modular. This would be elegant, but have no practical effect on database performance. 2) The framework needed to support modular types would encourage type development. This would enhance Postgresql's adaptability which would be A Very Good Thing.
Trent Shipley said: > Without directly addressing the merits of enumerations, enumeration > interfaces, real currency and time zone types, or whether currency and > time zone types should be built using enumerations, I would like to > ask the powers-that-be to seriously consider radically modularizing > Postgresql's type system. > > The core Postgresql installation would come with just those built-in > types needed to bootstrap itself, perhaps just varchar and an integer > type. Everything else would be a contributed module. > > An interface or contract would be described for creating additional > types. It would include things like parameter handlers, how to dump > the type, and how to load the type. (That is, standard housekeeping > functions needed by the Postgresql engine.) > > Other that the tiny number of bootstrap types, Postgresql types would > basically all be contrib modules. > > Types could be bundled into groups such as binary, character, > numerical, 2d-spatial, networking, and so on. > > Then one would not debate whether a type (or meta-type, like an > enumeration) should be put into the core product. Instead, the debate > would be whether or not to grade the type as "mature" and whether or > not to put a given type into pre-packaged type libraries with names > like "legacy", "sql-2003-standard", or "recommended-default". > > Power user DBA's could customize the types offered on their systems. > > In short: > > 1) Types would be modular. This would be elegant, but have no > practical effect on database performance. > > 2) The framework needed to support modular types would encourage type > development. This would enhance Postgresql's adaptability which would > be A Very Good Thing. We already have good support of type development. It's not clear to me that this would buy us anything at all. It seems like modularisation for the sake of it. The real issue is what types and type mechanisms should be in the postgresql core distribution. We won't win any thanks from anyone if we reduce them. Getting some types right is hard. There is no case that I can see for pushing timestamps, numerics, bitstrings or geometric or network types out of the core - they need all the support they can get. I'm also not sure which of these are required by the SQL spec. cheers andrew
Trent Shipley <tshipley@deru.com> writes:
> Without directly addressing the merits of enumerations, enumeration
> interfaces, real currency and time zone types, or whether currency and time
> zone types should be built using enumerations, I would like to ask the
> powers-that-be to seriously consider radically modularizing Postgresql's type
> system.
Er ... what makes you think it's not modular now?
regards, tom lane
remove my email from the list
Regards,
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Andrew Dunstan
Sent: Wednesday, March 22, 2006 9:05 AM
To: tshipley@deru.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Modular Type Libraries: was A real currency type
Trent Shipley said:
> Without directly addressing the merits of enumerations, enumeration
> interfaces, real currency and time zone types, or whether currency and
> time zone types should be built using enumerations, I would like to
> ask the powers-that-be to seriously consider radically modularizing
> Postgresql's type system.
>
> The core Postgresql installation would come with just those built-in
> types needed to bootstrap itself, perhaps just varchar and an integer
> type. Everything else would be a contributed module.
>
> An interface or contract would be described for creating additional
> types. It would include things like parameter handlers, how to dump
> the type, and how to load the type. (That is, standard housekeeping
> functions needed by the Postgresql engine.)
>
> Other that the tiny number of bootstrap types, Postgresql types would
> basically all be contrib modules.
>
> Types could be bundled into groups such as binary, character,
> numerical, 2d-spatial, networking, and so on.
>
> Then one would not debate whether a type (or meta-type, like an
> enumeration) should be put into the core product. Instead, the debate
> would be whether or not to grade the type as "mature" and whether or
> not to put a given type into pre-packaged type libraries with names
> like "legacy", "sql-2003-standard", or "recommended-default".
>
> Power user DBA's could customize the types offered on their systems.
>
> In short:
>
> 1) Types would be modular. This would be elegant, but have no
> practical effect on database performance.
>
> 2) The framework needed to support modular types would encourage type
> development. This would enhance Postgresql's adaptability which would
> be A Very Good Thing.
We already have good support of type development. It's not clear to me that
this would buy us anything at all. It seems like modularisation for the sake
of it. The real issue is what types and type mechanisms should be in the
postgresql core distribution. We won't win any thanks from anyone if we
reduce them. Getting some types right is hard. There is no case that I can
see for pushing timestamps, numerics, bitstrings or geometric or network
types out of the core - they need all the support they can get. I'm also not
sure which of these are required by the SQL spec.
cheers
andrew
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
"Andrew Dunstan" <andrew@dunslane.net> writes:
> ... The real issue is what types and type mechanisms should be in the
> postgresql core distribution. We won't win any thanks from anyone if we
> reduce them. Getting some types right is hard. There is no case that I can
> see for pushing timestamps, numerics, bitstrings or geometric or network
> types out of the core - they need all the support they can get. I'm also not
> sure which of these are required by the SQL spec.
Timestamps and numerics are definitely in the spec, geometric and
network types are definitely not. IIRC, bitstring types are in SQL99
but for some reason are deprecated in SQL2003 (if anyone knows the
reasoning behind the SQL committee's about-face on that, please clue
us in).
AFAICS, the main part of the type system that isn't modular is the
support for type parameters (a/k/a typmod), such as the maximum length
for varchar or the precision/scale for numeric. We could certainly
invent an API for interpreting such parameters. But in the current state
of the system the types that have such parameters have to be hard-wired
into the SQL grammar, and I don't see how to get rid of that hard-wiring
without breaking a whole lot of stuff. Any bison gurus hanging about?
regards, tom lane
unsubscribe
On Tue, Mar 21, 2006 at 10:39:03PM -0500, Tom Lane wrote: > AFAICS, the main part of the type system that isn't modular is the > support for type parameters (a/k/a typmod), such as the maximum length > for varchar or the precision/scale for numeric. We could certainly > invent an API for interpreting such parameters. But in the current state > of the system the types that have such parameters have to be hard-wired > into the SQL grammar, and I don't see how to get rid of that hard-wiring > without breaking a whole lot of stuff. Any bison gurus hanging about? We've been here before: http://archives.postgresql.org/pgsql-hackers/2005-08/msg01142.php Turns out it isn't too hard, except there are a number of limitations. Unfortunatly I seem to have the deleted the patch I created then. :( Summerising the thread then: - It made some changes to what was and wasn't a reserved word. - Character sets for CHAR types still fall outside the system. - Some other changes in behaviour I'm not sure if much has changed since that discussion. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
"Tom Lane" <tgl@sss.pgh.pa.us> > Timestamps and numerics are definitely in the spec, geometric and > network types are definitely not. IIRC, bitstring types are in SQL99 > but for some reason are deprecated in SQL2003 (if anyone knows the > reasoning behind the SQL committee's about-face on that, please clue > us in). There is a standard data type called "BIT" in ODBC and JDBC, but it is sth. like SQL standard's BOOLEAN, not BIT. It seems that some DBMSs implement BIT as BOOLEAN in the backend. Maybe the standard committee think that bit string is useless and easy to cause confusion? Regards, William ZHANG
On Wednesday 2006-03-22 08:53, William ZHANG wrote: > "Tom Lane" <tgl@sss.pgh.pa.us> > > > Timestamps and numerics are definitely in the spec, geometric and > > network types are definitely not. IIRC, bitstring types are in SQL99 > > but for some reason are deprecated in SQL2003 (if anyone knows the > > reasoning behind the SQL committee's about-face on that, please clue > > us in). > > There is a standard data type called "BIT" in ODBC and JDBC, > but it is sth. like SQL standard's BOOLEAN, not BIT. It seems that > some DBMSs implement BIT as BOOLEAN in the backend. Maybe the standard > committee think that bit string is useless and easy to cause confusion? In or out of the standard, bitstring can be a nice type to have.
Martijn van Oosterhout wrote: > On Tue, Mar 21, 2006 at 03:59:31PM -0500, Andrew Dunstan wrote: >>> I think such types would be better implemented as some sort of >>> structured type, possibly with constructors and methods and all the >>> other stuff that SQL talks about. We don't have all of that yet -- >>> maybe we don't need all of it immediately -- but before we start >>> endorsing many of these types I'd like to see some consideration given >>> to this issue. >> Yeah, looks interesting but let's find the wrinkles. I was wondering if >> it might have relevance to what I wanted to do with enumeration types, >> i.e. we would tag each one with its particular enumeration id. > > I have considered making enumeration types with this. It would probably > work but kind of overkill. The main problem being that this code is > seriously aimed at creating new types that you intend to do operations > on, create operators for. An enumeration is just that, an enumeration. > >> I also don't like the idea of it being done with user tables - if this >> is core material then the tags should go in the catalog, ISTM. > > But the user is providing the list of tags when creating the type. > Unless you are proposing copying the tag table to the catalog when the > type is created? You can't store all the tags for all tagged types in > one tag table either. At least, that wasn't what I was envisiging. See > the currency type where the tag-table also stores the format string for > output. No other tagged type is going to need that information. > > SQL types with constructors and methods might provide this also, I > don't know. My purpose with this was allowing people to simply create > new types with specific behaviour without writing functions in C. Right > now type input/output functions cannot be written in anything other > than C. Here are a few C functions that handle these new types in a > generic way and user can write little pl/pgsql wrappers to customise > the output. > > Have a ncie day, > Martin, This would be extremely useful to have. For example, if I store currencies tagged properly, I could do a select and multiply the currency tag by the factor associated with that tag. This would allow easily quoting financials in a standard currency, but protect against type mismatches presumably at the database level (USD + AUD fails). Either way, it should be easy to add tags (ie, INSERT or similar) or get a list of them (SELECT * from somewhere where type = "currency"). I'm sure this later will be the case system catalog or not. - August
On Fri, Mar 24, 2006 at 08:54:54AM -0800, August Zajonc wrote: > Martin, > > This would be extremely useful to have. > > For example, if I store currencies tagged properly, I could do a select > and multiply the currency tag by the factor associated with that tag. > This would allow easily quoting financials in a standard currency, but > protect against type mismatches presumably at the database level (USD + > AUD fails). Yep, it does do that. And it's very handy :) > Either way, it should be easy to add tags (ie, INSERT or similar) or get > a list of them (SELECT * from somewhere where type = "currency"). I'm > sure this later will be the case system catalog or not. The current implementation allows that. You just insert new tags or update them. Anyway, the code works if you want ot try it. Otherwise, maybe someone will eventually build something equivalent into the backend... Have a nice day. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.