7 ms·

It's not a matter of drop-in portability, it's a matter of reducing the complexity of migration as well as developer confusion. Often the case for using custom

• jeffdavis 14y ago
  "Often the case for using custom data types, for example, is quite weak..." Lots of people come to the postgres community because of PostGIS, which is (among other things) a custom type distributed separately from postgres. Using custom types is not bad, the mistake is thinking that making a new type is easy. For a non-trivial custom data type, you need to tie it into the indexing system (GiST+KNN, GIN, SP-GiST, BTree, Hash) as well as the optimizer (have some good stats functions) -- not to mention the basics like a good representation and a well-thought out set of functions and operators. Custom data types are really one aspect of an extension, albeit a crucial one. So don't think about it as "hey, I'll make a new phone number type because it sounds cool", think about it like "we need better support for genomic data in postgres, let's sit down and plan a serious extension". If you want a special type for a phone number, use a domain over text with a check constraint. And that's SQL standard, too.
  • einhverfr 14y ago
    The reason to use a phone number type if you want to use one is that you can, in theory, create functions against it. A good example of what might be possible with such a type can be gained by looking at the standard networking type. Now, it's rare that you are likely to get that into phone numbers, but there might be cases where you could decompose the data and do relevant searches on components might come in really handy. The way I would look at doing that if I didn't want to go into C would be a complex type and a bunch of casts, functions, and operators. BTW, I use custom types a lot for defining output tuples for stored procedures, It is relatively hard to get away from that.
    • jeffdavis 14y ago
      "Now, it's rare that you are likely to get that into phone numbers..." That was part of my point -- if your entire business revolves around phone numbers, because you are a phone company or something, then maybe a custom data type makes sense. Then do it, and do it right. But if you think of a phone number as "something to call" then all you need to do is display it to the right person at the right time, and ensure that it reasonably matches something you can type on a phone. And that sounds more like a domain over a text field with a constraint, to me. "I use custom types a lot for defining output tuples for stored procedures" I don't really consider composite types "custom" types any more than an enum is a custom type. Maybe I misunderstood gfodor's statement.
      • einhverfr 14y ago
        With the ability to define columns which store composite types, the line between a custom data type in C and a composite type plus casts is blurring. Back in the 7.x line at one point I discovered that you could do something like: create table foo (...); create table bar (myfoo foo); And this table would be write-only, and indeed the backend would crash when reading from it. That's the only really interesting bug I ever found in PostgreSQL. However, I made the case that it would be nice to be able to treat tuples as full-fledged data types and now by at least 8.1, that was supported. So now I can: create type foo as (...); -- works with create table too create function foo_to_text .....; create function text_to_foo ....; create cast...... create table bar (my_foo foo); insert into bar (my_foo) values ('my.foo.as.text'::text); So the point is that composite types can now be used to blur the lines quite a bit between traditional custom types and the roles that composite types are traditionally used for. You can go a long way prototyping what is useful with composite types, and then later do that right in C.
    • robe2 14y ago
      You don't need to create a custom type to return tuples anymore. (that kind of went out with 8.1). You can use OUT parameters or RETURNS TABLE syntax. RETURNS TABLE is ANSI compliant and supported since 8.3. SQL Server supports RETURNS TABLE as well.
• jeffdavis 14y ago
  "it's a matter of reducing the complexity of migration" You put the code in your application so you can switch out the database, but why do you want to switch databases? You can't switch out the database for one with more features, because then you're not using the lowest common denominator any more, and you can't switch back. It can't be licensing costs, because postgresql licenses are free. The only other reason I can think of is performance. But trying to avoid database features just because you might want to migrate to a less-featureful database later seems more likely to result in performance problems than prevent them. I agree you don't want to go out on a limb with crazy features just because you can. But those features are there for a reason, and might drastically simplify portions of your application if you use them. And a lot of them are SQL standard, just not supported properly in all database systems. Also, I'd like to point out that database migrations themselves are quite rare once they become established in an organization. You might be able to migrate one fairly simple application if you bend over backwards trying to use only the simplest features; but once a few applications are depending on it, it's just too expensive.
  • sandGorgon 14y ago
    One reason if you are building a shippable product that needs to work with a variety of client databases rather than a choice of your own. 99% of the time, this is why you build DB agnostic apps.
    • jeffdavis 14y ago
      The main reason to do that is licensing. You either need to get a redistribution license, or the client needs to work out their own licensing deal -- and either way, you don't want to be beholden when negotiation time comes. That also applies to MySQL, because it's GPL. That does not apply to Postgres, because you can just ship it with the app. I exaggerated a little, there certainly are times when you just don't care what database system you use and would rather have simple migrations at the expense of owning more code. But I think these reasons are weighted much too highly, particularly in the context of postgres.
    • yread 14y ago