[pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
20 messages Options
Reply | Threaded
Open this post in threaded view
|

[pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Chris Travers-2
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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Christopher Kings-Lynne
> 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 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Josh berkus
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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Michael Glaesemann
In reply to this post by Christopher Kings-Lynne

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



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Gregory Youngblood
In reply to this post by Christopher Kings-Lynne
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
>


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Christopher Kings-Lynne
> 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...


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Joshua D. Drake
In reply to this post by Josh berkus
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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Chris Travers-2
In reply to this post by Christopher Kings-Lynne
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
>
>


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Tom Lane-2
In reply to this post by Gregory Youngblood
Gregory Youngblood <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Gregory Youngblood

On Jul 27, 2005, at 9:53 PM, Tom Lane wrote:

Gregory Youngblood <[hidden email]> 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 ...

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
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Tom Lane-2
Gregory Youngblood <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

David Fetter
In reply to this post by Joshua D. Drake
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 [hidden email] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Kenneth Marshall
In reply to this post by Tom Lane-2
On Thu, Jul 28, 2005 at 12:53:07AM -0400, Tom Lane wrote:

> Gregory Youngblood <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Josh berkus
In reply to this post by David Fetter
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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Joshua D. Drake
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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Decibel!
In reply to this post by Tom Lane-2
On Thu, Jul 28, 2005 at 12:53:07AM -0400, Tom Lane wrote:
> Gregory Youngblood <[hidden email]> 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               [hidden email]
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?"

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Decibel!
In reply to this post by Chris Travers-2
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               [hidden email]
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?"

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Álvaro Herrera
In reply to this post by Decibel!
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 <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Tom Lane-2
In reply to this post by Decibel!
"Jim C. Nasby" <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type

Decibel!
On Fri, Jul 29, 2005 at 03:57:48PM -0400, Tom Lane wrote:

> "Jim C. Nasby" <[hidden email]> 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               [hidden email]
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?"

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org