Thread: MySQL to PostgreSQL, was ENUM type
So, it seems to me that there is a fair bit of work to be done on helping people migrate MySQL to PostgreSQL. So far, the checklist I can see includes: * Maintaining conversion scripts * Reviewing pain points and looking at ways of mitigating them. * Building solid migration documentation * Providing porting frameworks This last item could have some potentials. For example, a source-code compatible library to map MySQL client lib system calls to libpq operations for the C API, PHP wrapper scripts, etc. My company will be providing a PHP wrapper script, and will be working on some documentation. Most of the documentation will be BSD-style licensed with the exception of the references to my company's services (which must be stripped out of non-verbatim reproductions). If anyone else sees any other interesting areas that need work, it might be worthwhile to discuss them as well. Best Wishes, Chris Travers Metatron Technology Consulting
> So far, the checklist I can see includes: > * Maintaining conversion scripts What I think we need is a C program that dumps directly from MySQL into PostgreSQL sql. ie. Take the mysqldump source code and just modify its output. Will inherit the MySQL license though :( Chris
KL- > What I think we need is a C program that dumps directly from MySQL into > PostgreSQL sql. Why C? PerlDBI or JDBC should be able to do this readily enough. -- Josh Berkus Aglio Database Solutions San Francisco
On Jul 28, 2005, at 11:20 AM, Christopher Kings-Lynne wrote: > What I think we need is a C program that dumps directly from MySQL > into PostgreSQL sql. > > ie. Take the mysqldump source code and just modify its output. > > Will inherit the MySQL license though :( Just the conversion program would, correct? If so, of course it couldn't be bundled with the distribution, but that doesn't mean the program wouldn't be useful. Seems like a natural for a pgfoundry project. Michael Glaesemann grzm myrealbox com
If linking it in directly via C would bring in the MySQL license, and you want to avoid that, what about one of the scripting languages such as perl or python, or possibly even ruby? Or, what about using UnixODBC to talk to MySQL. I've written a few perl scripts when I need to convert MySQL into Postgres. Nothing formalized, I usually just recreate it each time I need to do something. My needs are typically pretty simple though, and I know what I'm converting, so it makes it easier. I think one of the more difficult areas will be to convert unsigned fields from mysql into postgres. For smaller sizes it is possible to convert to postgres by moving one size up and using constraints to restrict numbers to be positive, and possibly within the mysql range too. But, the problem is unsigned bigint in mysql to postgresql. There's not another larger integer size that can be used that would allow the 18446744073709551615 (is that the max value?) max value available in mysql. Or am I missing something? I think running into these would be rare, but it is something to be considered. Greg On Jul 27, 2005, at 7:20 PM, Christopher Kings-Lynne wrote: >> So far, the checklist I can see includes: >> * Maintaining conversion scripts >> > > What I think we need is a C program that dumps directly from MySQL > into PostgreSQL sql. > > ie. Take the mysqldump source code and just modify its output. > > Will inherit the MySQL license though :( > > Chris > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
> I think one of the more difficult areas will be to convert unsigned > fields from mysql into postgres. For smaller sizes it is possible to > convert to postgres by moving one size up and using constraints to > restrict numbers to be positive, and possibly within the mysql range > too. But, the problem is unsigned bigint in mysql to postgresql. > There's not another larger integer size that can be used that would > allow the 18446744073709551615 (is that the max value?) max value > available in mysql. Or am I missing something? You'd just issue a warning...
Josh Berkus wrote: > KL- > > >>What I think we need is a C program that dumps directly from MySQL into >>PostgreSQL sql. > > > Why C? PerlDBI or JDBC should be able to do this readily enough. Uhmmm isn't that what fetters DBI-Link thing does? -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit http://www.commandprompt.com / http://www.postgresql.org
Christopher Kings-Lynne wrote: >> So far, the checklist I can see includes: >> * Maintaining conversion scripts > > > What I think we need is a C program that dumps directly from MySQL > into PostgreSQL sql. > > ie. Take the mysqldump source code and just modify its output. > > Will inherit the MySQL license though :( > It then occurred to me that a better answer would be something like a DBI perl script that has modules for storing important queries for various SQL database servers, and outputting the format in PostgreSQL syntax. That way one can have a general conversion toolkit that can be easily expanded and could also be used in porting Oracle, MS SQL, SQL-Lite, and other databases to PostgreSQL. In this way, you could also avoid the MySQL license even by MySQL AB's weird interpretation of derivative works. This way one could also have fairly smart logic in the conversion as well. Gee, your max unsigned bigint is over our bigint limit, lets use numeric instead or hmm... you have an ENUM type here. Lets use a VARCHAR() with a constraint for now. This logic could be easily tweeked by a decent programmer. I think that such a program should be somewhat trivial to write for at least tables and data definitions. Triggers, functions, etc. would be more difficult. But I think it is reasonable to expect that functions might have to be manually ported in most circumstances. Best Wishes, Chris Travers > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > >
Gregory Youngblood <pgcluster@netio.org> writes:
> ... the problem is unsigned bigint in mysql to postgresql.
> There's not another larger integer size that can be used that would
> allow the 18446744073709551615 (is that the max value?) max value
> available in mysql. Or am I missing something?
You'd have to translate that to NUMERIC, which would work but would
take a bit of a performance hit ...
regards, tom lane
On Jul 27, 2005, at 9:53 PM, Tom Lane wrote:
Gregory Youngblood <pgcluster@netio.org> writes:... the problem is unsigned bigint in mysql to postgresql.There's not another larger integer size that can be used that wouldallow the 18446744073709551615 (is that the max value?) max valueavailable in mysql. Or am I missing something?You'd have to translate that to NUMERIC, which would work but wouldtake a bit of a performance hit ...
The most common places I've seen unsigned bigint used have been primary keys for tables where the counter is expected to basically grow forever. I've also seen it used to store unique user id numbers instead of varchar fields. In both of those cases, the number is used as a key, either alone or with another field, in an index. Would there be much of a performance hit for that type of use? I can image the biggest performance hit being mathematical calculations, such as sum(), etc. Or would the performance hit be across the board?
Greg
Gregory Youngblood <pgcluster@netio.org> writes:
> On Jul 27, 2005, at 9:53 PM, Tom Lane wrote:
>> You'd have to translate that to NUMERIC, which would work but would
>> take a bit of a performance hit ...
> The most common places I've seen unsigned bigint used have been
> primary keys for tables where the counter is expected to basically
> grow forever. I've also seen it used to store unique user id numbers
> instead of varchar fields.
[ shrug... ] So store it as plain bigint. There is not any real
difference between 2^63 and 2^64 available values --- either way,
we'll all be safely dead before overflow occurs.
regards, tom lane
On Wed, Jul 27, 2005 at 08:34:56PM -0700, Joshua D. Drake wrote: > Josh Berkus wrote: > >KL- > > > > > >>What I think we need is a C program that dumps directly from MySQL into > >>PostgreSQL sql. > > > > > >Why C? PerlDBI or JDBC should be able to do this readily enough. > > Uhmmm isn't that what fetters DBI-Link thing does? Yikes! Another unintended use. :P Anybody interested in contributing to this? Cheers, D (who is ashamed about how long it's been since his last commit) -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
D- > Anybody interested in contributing to this? Interested, yes. Free time's a little harder to come by, as you know ... -- --Josh Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus wrote: > D- > > >>Anybody interested in contributing to this? > > > Interested, yes. Free time's a little harder to come by, as you know ... What is this thing "free time"? I have yet to see or hear of it. > -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit http://www.commandprompt.com / http://www.postgresql.org
On Thu, Jul 28, 2005 at 12:53:07AM -0400, Tom Lane wrote: > Gregory Youngblood <pgcluster@netio.org> writes: > > ... the problem is unsigned bigint in mysql to postgresql. > > There's not another larger integer size that can be used that would > > allow the 18446744073709551615 (is that the max value?) max value > > available in mysql. Or am I missing something? > > You'd have to translate that to NUMERIC, which would work but would > take a bit of a performance hit ... > > regards, tom lane > Since the numeric range of the PostgreSQL bigint and the MySQL unsigned bigint is the same, just shifted. How difficult would it be to put a bigint overlay in PostgreSQL that would do that for you. We had to do this on the application side to make use of the PostgreSQL bigint and not the slower NUMERIC. Ken
On Thu, Jul 28, 2005 at 12:53:07AM -0400, Tom Lane wrote: > Gregory Youngblood <pgcluster@netio.org> writes: > > ... the problem is unsigned bigint in mysql to postgresql. > > There's not another larger integer size that can be used that would > > allow the 18446744073709551615 (is that the max value?) max value > > available in mysql. Or am I missing something? > > You'd have to translate that to NUMERIC, which would work but would > take a bit of a performance hit ... Is there any serious impediment to adding unsigned types to PostgreSQL? They should be as readily supported as signed, right? I don't think these would even have to be in core, if that's a concern. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Wed, Jul 27, 2005 at 09:16:04PM -0700, Chris Travers wrote: > Christopher Kings-Lynne wrote: > > >>So far, the checklist I can see includes: > >>* Maintaining conversion scripts > > > > > >What I think we need is a C program that dumps directly from MySQL > >into PostgreSQL sql. > > > >ie. Take the mysqldump source code and just modify its output. > > > >Will inherit the MySQL license though :( > > > > It then occurred to me that a better answer would be something like a > DBI perl script that has modules for storing important queries for > various SQL database servers, and outputting the format in PostgreSQL > syntax. That way one can have a general conversion toolkit that can be > easily expanded and could also be used in porting Oracle, MS SQL, > SQL-Lite, and other databases to PostgreSQL. In this way, you could > also avoid the MySQL license even by MySQL AB's weird interpretation of > derivative works. > > This way one could also have fairly smart logic in the conversion as > well. Gee, your max unsigned bigint is over our bigint limit, lets use > numeric instead or hmm... you have an ENUM type here. Lets use a > VARCHAR() with a constraint for now. This logic could be easily tweeked > by a decent programmer. I think that such a program should be somewhat > trivial to write for at least tables and data definitions. Triggers, > functions, etc. would be more difficult. But I think it is reasonable > to expect that functions might have to be manually ported in most > circumstances. The only downside I can think of to such an approach is that it will be much slower than directly reading a dump file of some kind. But, on the up-side, it's also possible that it could allow for minimal downtime migraitons to take place in the future (of course adding that support would take a lot of work, but theoretically it's possible). -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Fri, Jul 29, 2005 at 02:49:54PM -0500, Jim C. Nasby wrote: > On Thu, Jul 28, 2005 at 12:53:07AM -0400, Tom Lane wrote: > > Gregory Youngblood <pgcluster@netio.org> writes: > > > ... the problem is unsigned bigint in mysql to postgresql. > > > There's not another larger integer size that can be used that would > > > allow the 18446744073709551615 (is that the max value?) max value > > > available in mysql. Or am I missing something? > > > > You'd have to translate that to NUMERIC, which would work but would > > take a bit of a performance hit ... > > Is there any serious impediment to adding unsigned types to PostgreSQL? > They should be as readily supported as signed, right? I don't think > these would even have to be in core, if that's a concern. Tom mentioned a couple of days ago that the worst problems that would be created by adding more numeric types are solved as of 8.1. -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "Before you were born your parents weren't as boring as they are now. They got that way paying your bills, cleaning up your room and listening to you tell them how idealistic you are." -- Charles J. Sykes' advice to teenagers
"Jim C. Nasby" <decibel@decibel.org> writes:
> Is there any serious impediment to adding unsigned types to PostgreSQL?
Quick, what's the datatype of 12345? And why?
My guess is that transparent support for such a thing would require some
tricky and fragile compromises in the numeric hierarchy's promotion
rules. The handling of smallint is already pretty unsatisfactory
because "12345" is considered int not smallint; adding three or four
unsigned types would make that problem many times worse.
> I don't think these would even have to be in core, if that's a
> concern.
Sure, if you don't need it to work reasonably nicely --- although the
promotion rules are now embedded in pg_cast and hence modifiable, the
initial determination of a datatype for a numeric literal is still
hard-wired in the grammar.
regards, tom lane
On Fri, Jul 29, 2005 at 03:57:48PM -0400, Tom Lane wrote: > "Jim C. Nasby" <decibel@decibel.org> writes: > > Is there any serious impediment to adding unsigned types to PostgreSQL? > > Quick, what's the datatype of 12345? And why? > > My guess is that transparent support for such a thing would require some > tricky and fragile compromises in the numeric hierarchy's promotion > rules. The handling of smallint is already pretty unsatisfactory > because "12345" is considered int not smallint; adding three or four > unsigned types would make that problem many times worse. ISTM that so long as you don't overflow or lose precision it shouldn't really matter what datatype is used to represent a number, until you use it someplace that specifies a datatype to use (such as a function or a table). I realize that function overloading gums this up a bit, but I don't think it's unreasonable to require the user to explicitly cast if they want an explicit function out of the overloaded set to be used (though throwing a warning if they don't cast might be a good idea). > > I don't think these would even have to be in core, if that's a > > concern. > > Sure, if you don't need it to work reasonably nicely --- although the > promotion rules are now embedded in pg_cast and hence modifiable, the > initial determination of a datatype for a numeric literal is still > hard-wired in the grammar. So what would those rules currently do when they find a value that exceedes a signed int? Or a signed bigint? -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"