pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedMysql -> Postgresql pitfalls
26+ messages / 9 participants
[nested] [flat]
* Mysql -> Postgresql pitfalls
@ 2003-08-01 01:10 Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 26+ messages in thread
From: Chad N. Tindel @ 2003-08-01 01:10 UTC (permalink / raw)
To: pgsql-docs
Hello-
I was exchanging some email with Tom Lane about some problems I was running
into converting from mysql to postgresql. He suggested I contact this list.
Is there some sort of document describing common mysql->pgsql pitfalls?
If not, I'd be happy to start one with the things I've run into. Who
would I talk to about that?
I'm not on this list, so please CC me if you reply to this message.
Thanks,
Chad
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
@ 2003-08-01 04:46 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
0 siblings, 1 reply; 26+ messages in thread
From: Bruce Momjian @ 2003-08-01 04:46 UTC (permalink / raw)
To: Chad N. Tindel <chad@tindel.net>; +Cc: pgsql-docs
Have you looked at techdocs.postgresql.org?
---------------------------------------------------------------------------
Chad N. Tindel wrote:
> Hello-
>
> I was exchanging some email with Tom Lane about some problems I was running
> into converting from mysql to postgresql. He suggested I contact this list.
>
> Is there some sort of document describing common mysql->pgsql pitfalls?
> If not, I'd be happy to start one with the things I've run into. Who
> would I talk to about that?
>
> I'm not on this list, so please CC me if you reply to this message.
>
> Thanks,
>
> Chad
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-08-01 16:35 ` Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 26+ messages in thread
From: Chad N. Tindel @ 2003-08-01 16:35 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-docs
Excellent! That is exactly what I'm talking about.
BTW, the "my2pg.pl" link is a dead link.
Chad
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
@ 2003-08-01 20:29 ` scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
0 siblings, 1 reply; 26+ messages in thread
From: scott.marlowe @ 2003-08-01 20:29 UTC (permalink / raw)
To: Chad N. Tindel <chad@tindel.net>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-docs
On Fri, 1 Aug 2003, Chad N. Tindel wrote:
> Excellent! That is exactly what I'm talking about.
>
> BTW, the "my2pg.pl" link is a dead link.
This is probably something that could be codified a bit here in the news
group.
I'd say the pitfalls I'm aware of are:
autoincrement fields -> sequences
full text indexing -> fts/tsearch etc...
enum -> check constraint
vacuum / analyze
simple performance tuning (out of the box the postgresql.conf settings
aren't really all that great, but they let postgresql come up on almost
anything.)
pg_hba.conf default setting that doesn't allow tcp/ip connections
not being able to run postgresql as root (a good thing TM)
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
@ 2003-08-01 20:51 ` Chad N. Tindel <chad@tindel.net>
2003-08-02 02:49 ` Re: Mysql -> Postgresql pitfalls Tom Lane <tgl@sss.pgh.pa.us>
2003-08-02 06:33 ` Re: Mysql -> Postgresql pitfalls Roberto Mello <rmello@cc.usu.edu>
2003-08-04 15:13 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 3 replies; 26+ messages in thread
From: Chad N. Tindel @ 2003-08-01 20:51 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: pgsql-docs
> > Excellent! That is exactly what I'm talking about.
> >
> > BTW, the "my2pg.pl" link is a dead link.
>
> This is probably something that could be codified a bit here in the news
> group.
>
> I'd say the pitfalls I'm aware of are:
>
> autoincrement fields -> sequences
Yes. The documentation very clearly states using sequences instead of
auto-increment, but it doesn't make it clear that inserting the id's into
data by hand doesn't cause the sequence to be auto-matically incremented. It'd
be nice of postgres had a way to trigger an update of the sequence value after
every insert containing an id clumn.
> not being able to run postgresql as root (a good thing TM)
<RANT>As a programmer, I personally would never write code that kept people from
running things as root. I mean, what is the point? If an administrator
wants to run postgresql or apache as root, why shouldn't they be allowed
to make that conscious decision for themselves? As it is, you have to recompile
apache with some BIG_SECURITY_HOLE defined in order to run as root, which means
you can't just use the out of the box apache rpm. Its so stupid to write
*extra* code that keeps people from doing something that isn't even
fundamentally incorrect.</RANT>
All that being said, I don't think it causes too big of a problem for postgres
installations.
Chad
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
@ 2003-08-02 02:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-02 17:04 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2 siblings, 1 reply; 26+ messages in thread
From: Tom Lane @ 2003-08-02 02:49 UTC (permalink / raw)
To: Chad N. Tindel <chad@tindel.net>; +Cc: scott.marlowe <scott.marlowe@ihs.com>; pgsql-docs
"Chad N. Tindel" <chad@tindel.net> writes:
> <RANT>As a programmer, I personally would never write code that kept
> people from running things as root. I mean, what is the point?
If someone roots your box, it's not our fault. Simple as that.
Personally, I wish more net-accessible servers were written with that
philosophy. The correct question is NEVER "why can't I run this as
root?". The correct question is ALWAYS "how can I avoid running this as
root? And if I can't avoid it, why not?". Any other approach leads to
Outlook Express. (BTW, have you forgotten the SQL Server worm already?)
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-02 02:49 ` Re: Mysql -> Postgresql pitfalls Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-08-02 17:04 ` Chad N. Tindel <chad@tindel.net>
2003-08-03 17:03 ` Re: Mysql -> Postgresql pitfalls Tom Lane <tgl@sss.pgh.pa.us>
2003-08-04 15:21 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 17:27 ` Re: Mysql -> Postgresql pitfalls Lamar Owen <lowen@pari.edu>
0 siblings, 3 replies; 26+ messages in thread
From: Chad N. Tindel @ 2003-08-02 17:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs
> > <RANT>As a programmer, I personally would never write code that kept
> > people from running things as root. I mean, what is the point?
>
> If someone roots your box, it's not our fault. Simple as that.
I didn't say "require them to run as a non-root user". I said "Give them
the choice to decide what is correct for their environment". In the case
of apache, there are many internal webservers that are not exposed to the
threat of the public internet; for such servers, it may be appropriate to run
apache as root because it simplifies the administration and automation of
tasks. But to do so, one has to know how to re-compile apache, which will
exclude a lot of your basic garden variety administrators. Its very
un-friendly programming.
Chad
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-02 02:49 ` Re: Mysql -> Postgresql pitfalls Tom Lane <tgl@sss.pgh.pa.us>
2003-08-02 17:04 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
@ 2003-08-03 17:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 26+ messages in thread
From: Tom Lane @ 2003-08-03 17:03 UTC (permalink / raw)
To: Chad N. Tindel <chad@tindel.net>; +Cc: pgsql-docs
"Chad N. Tindel" <chad@tindel.net> writes:
>> If someone roots your box, it's not our fault. Simple as that.
> I didn't say "require them to run as a non-root user". I said "Give them
> the choice to decide what is correct for their environment". In the case
> of apache, there are many internal webservers that are not exposed to the
> threat of the public internet; for such servers, it may be appropriate to run
> apache as root because it simplifies the administration and automation of
> tasks. But to do so, one has to know how to re-compile apache, which will
> exclude a lot of your basic garden variety administrators. Its very
> un-friendly programming.
If they don't know how to recompile apache, what are the odds that they
are truly competent to decide that they can safely run it as root?
Semi-competent people administering servers are the Achilles heel of the
internet already. We are doing them a favor, not creating a problem,
by preventing them from adopting insecure practices.
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-02 02:49 ` Re: Mysql -> Postgresql pitfalls Tom Lane <tgl@sss.pgh.pa.us>
2003-08-02 17:04 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
@ 2003-08-04 15:21 ` scott.marlowe <scott.marlowe@ihs.com>
2 siblings, 0 replies; 26+ messages in thread
From: scott.marlowe @ 2003-08-04 15:21 UTC (permalink / raw)
To: Chad N. Tindel <chad@tindel.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
On Sat, 2 Aug 2003, Chad N. Tindel wrote:
> > > <RANT>As a programmer, I personally would never write code that kept
> > > people from running things as root. I mean, what is the point?
> >
> > If someone roots your box, it's not our fault. Simple as that.
>
> I didn't say "require them to run as a non-root user". I said "Give them
> the choice to decide what is correct for their environment". In the case
> of apache, there are many internal webservers that are not exposed to the
> threat of the public internet; for such servers, it may be appropriate to run
> apache as root because it simplifies the administration and automation of
> tasks. But to do so, one has to know how to re-compile apache, which will
> exclude a lot of your basic garden variety administrators. Its very
> un-friendly programming.
So tell me, what does the sysadmin gain by running postgresql as root.
Seriously, what one advantage does he have? Besides allowing him to be
lazy, I can't think of one.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-02 02:49 ` Re: Mysql -> Postgresql pitfalls Tom Lane <tgl@sss.pgh.pa.us>
2003-08-02 17:04 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
@ 2003-08-04 17:27 ` Lamar Owen <lowen@pari.edu>
2003-08-04 17:54 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2 siblings, 1 reply; 26+ messages in thread
From: Lamar Owen @ 2003-08-04 17:27 UTC (permalink / raw)
To: Chad N. Tindel <chad@tindel.net>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs
On Saturday 02 August 2003 13:04, Chad N. Tindel wrote:
> I didn't say "require them to run as a non-root user". I said "Give them
> the choice to decide what is correct for their environment". In the case
> of apache, there are many internal webservers that are not exposed to the
> threat of the public internet; for such servers, it may be appropriate to
> run apache as root because it simplifies the administration and automation
> of tasks.
It also simplifies a bug in apache crashing your box and scribbling all over
your disk. Apache does have bugs, you know. (as do MySQL and PostgreSQL, but
that's another story).
The postmaster will not run as root. That is just the way it is, and has
been, for quite some time. It is foolish to run an RDBMS server (or any
other server that doesn't need root's permissions) as root when it is not
necessary. It is lazy to run things as root to 'simplify' administration;
properly administering a box isn't that hard, and user protections and
permissions should be used to their intended effect in the course of routine
administration. That's just basic Unix sysadmin practice that is well
accepted by the vast majority of sysadmins. We just encourage the best
practice in a more direct way than other servers, that's all.
As to the subject matter of this thread, there are a great number of
educational opportunities in such a migration/pitfalls document. The MySQL
'way' and the PostgreSQL 'way' are very different, and at points don't even
have a common frame of reference. This issue is one of them; a thorough
explanation, written in a direct non-condescending style, of why postmaster
won't run as root would be a nice addition.
--
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-02 02:49 ` Re: Mysql -> Postgresql pitfalls Tom Lane <tgl@sss.pgh.pa.us>
2003-08-02 17:04 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-04 17:27 ` Re: Mysql -> Postgresql pitfalls Lamar Owen <lowen@pari.edu>
@ 2003-08-04 17:54 ` Chad N. Tindel <chad@tindel.net>
2003-08-04 21:08 ` Re: Mysql -> Postgresql pitfalls Lamar Owen <lowen@pari.edu>
0 siblings, 1 reply; 26+ messages in thread
From: Chad N. Tindel @ 2003-08-04 17:54 UTC (permalink / raw)
To: Lamar Owen <lowen@pari.edu>; +Cc: Chad N. Tindel <chad@tindel.net>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
> > I didn't say "require them to run as a non-root user". I said "Give them
> > the choice to decide what is correct for their environment". In the case
> > of apache, there are many internal webservers that are not exposed to the
> > threat of the public internet; for such servers, it may be appropriate to
> > run apache as root because it simplifies the administration and automation
> > of tasks.
>
> It also simplifies a bug in apache crashing your box and scribbling all over
> your disk. Apache does have bugs, you know. (as do MySQL and PostgreSQL, but
> that's another story).
True. There are good reasons to not run apache as root. There are also
perfectly valid reasons to run it as root, for such users who want to make
an informed decision.
> The postmaster will not run as root. That is just the way it is, and has
> been, for quite some time. It is foolish to run an RDBMS server (or any
> other server that doesn't need root's permissions) as root when it is not
> necessary. It is lazy to run things as root to 'simplify' administration;
> properly administering a box isn't that hard, and user protections and
> permissions should be used to their intended effect in the course of routine
> administration. That's just basic Unix sysadmin practice that is well
> accepted by the vast majority of sysadmins. We just encourage the best
> practice in a more direct way than other servers, that's all.
My posts to the list are not going through. I am NOT advocating running
postmaster as root. I never was.
> As to the subject matter of this thread, there are a great number of
> educational opportunities in such a migration/pitfalls document. The MySQL
> 'way' and the PostgreSQL 'way' are very different, and at points don't even
> have a common frame of reference. This issue is one of them; a thorough
> explanation, written in a direct non-condescending style, of why postmaster
> won't run as root would be a nice addition.
I don't think there is a non-condescending way to tell an administrator that
you think you know what is better for their operating environment than they do.
Everybody understands the pitfalls of running root daemons; I don't think there
is any more explanation required.
I definitely think there is benefit to starting an official "how to migrate"
document. There is already a lot of information up on the techdocs website
that someone pointed me to. I've found answers to all the questions that
I needed by looking around, but it might be nice to create a more coherent
explanation of the "mysql way" vs. the "postgresql way".
Chad
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-02 02:49 ` Re: Mysql -> Postgresql pitfalls Tom Lane <tgl@sss.pgh.pa.us>
2003-08-02 17:04 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-04 17:27 ` Re: Mysql -> Postgresql pitfalls Lamar Owen <lowen@pari.edu>
2003-08-04 17:54 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
@ 2003-08-04 21:08 ` Lamar Owen <lowen@pari.edu>
0 siblings, 0 replies; 26+ messages in thread
From: Lamar Owen @ 2003-08-04 21:08 UTC (permalink / raw)
To: Chad N. Tindel <chad@tindel.net>; +Cc: Chad N. Tindel <chad@tindel.net>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
On Monday 04 August 2003 13:54, Chad N. Tindel wrote:
> True. There are good reasons to not run apache as root. There are also
> perfectly valid reasons to run it as root, for such users who want to make
> an informed decision.
Name one.
> My posts to the list are not going through. I am NOT advocating running
> postmaster as root. I never was.
No, you're advocating giving that option. We have chosen to not be the
instrument of an admin shooting themselves in the foot.
> I don't think there is a non-condescending way to tell an administrator
> that you think you know what is better for their operating environment than
> they do.
Yes, there are. MySQL does it all the time. "You don't need
{transactions|foreign keys|subqueries|feature of the day they don't have}."
People don't seem to mind that. We have other reasons, IIRC.
--
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
@ 2003-08-02 06:33 ` Roberto Mello <rmello@cc.usu.edu>
2003-08-02 17:10 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2 siblings, 1 reply; 26+ messages in thread
From: Roberto Mello @ 2003-08-02 06:33 UTC (permalink / raw)
To: Chad N. Tindel <chad@tindel.net>; +Cc: scott.marlowe <scott.marlowe@ihs.com>; pgsql-docs
On Fri, Aug 01, 2003 at 04:51:11PM -0400, Chad N. Tindel wrote:
>
> <RANT>As a programmer, I personally would never write code that kept people from
> running things as root. I mean, what is the point? If an administrator
Hmmm?
The point is something called security.
> wants to run postgresql or apache as root, why shouldn't they be allowed
> to make that conscious decision for themselves? As it is, you have to recompile
Because administrators have too much to worry about. If an application
makes a conscious decision to allow itself to run in a knowingly insecure
manner, that application is doing the admin a disfavor, and should be
ditched.
> apache with some BIG_SECURITY_HOLE defined in order to run as root, which means
> you can't just use the out of the box apache rpm. Its so stupid to write
> *extra* code that keeps people from doing something that isn't even
> fundamentally incorrect.</RANT>
This has no logic. Security is fundamental. No security is fundamentally
incorrect.
-Roberto
--
+----| Roberto Mello - http://www.brasileiro.net/ |------+
+ Computer Science Graduate Student, Utah State University +
+ USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +
---------------/ NO INSPIRATION TODAY /-------------------
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-02 06:33 ` Re: Mysql -> Postgresql pitfalls Roberto Mello <rmello@cc.usu.edu>
@ 2003-08-02 17:10 ` Chad N. Tindel <chad@tindel.net>
2003-08-02 21:21 ` Re: Mysql -> Postgresql pitfalls Roberto Mello <rmello@cc.usu.edu>
2003-08-03 18:19 ` Re: Mysql -> Postgresql pitfalls Ian Barwick <barwick@gmx.net>
0 siblings, 2 replies; 26+ messages in thread
From: Chad N. Tindel @ 2003-08-02 17:10 UTC (permalink / raw)
To: Roberto Mello <rmello@cc.usu.edu>; +Cc: pgsql-docs
> Hmmm?
>
> The point is something called security.
There is no such thing as a "proper amount of security that is correct for
all operating environments".
> > apache with some BIG_SECURITY_HOLE defined in order to run as root, which means
> > you can't just use the out of the box apache rpm. Its so stupid to write
> > *extra* code that keeps people from doing something that isn't even
> > fundamentally incorrect.</RANT>
>
> This has no logic. Security is fundamental. No security is fundamentally
> incorrect.
Well, you could make a box very secure by unplugging all the LAN cables from
it and putting it in a giant safe deposit box. However, I would say that such
a machine would be fundamentally incorrect for most operating environments.
Do you worry about whether or not someone snuck into your house at night and
installed some sort of keyboard logging device onto your PC so that they can
get your root password? Is that a "fundamental" part of your personal
security? Probably not, because *that* would be illogical for most people to
worry about.
Many people run their machines with "+ +" in root's .rhosts file because it
eases the task of doing administration. They work in a company where the box is
behind a firewall on some public network and they need there computers to
get real work done.... they don't want things like "security" to get in the
way because nobody is trying to hack those machines.
Chad
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-02 06:33 ` Re: Mysql -> Postgresql pitfalls Roberto Mello <rmello@cc.usu.edu>
2003-08-02 17:10 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
@ 2003-08-02 21:21 ` Roberto Mello <rmello@cc.usu.edu>
1 sibling, 0 replies; 26+ messages in thread
From: Roberto Mello @ 2003-08-02 21:21 UTC (permalink / raw)
To: Chad N. Tindel <chad@tindel.net>; +Cc: pgsql-docs
On Sat, Aug 02, 2003 at 01:10:49PM -0400, Chad N. Tindel wrote:
> > Hmmm?
> >
> > The point is something called security.
>
> There is no such thing as a "proper amount of security that is correct for
> all operating environments".
Whoever said there was? I didn't.
> > This has no logic. Security is fundamental. No security is fundamentally
> > incorrect.
>
> Well, you could make a box very secure by unplugging all the LAN cables from
> it and putting it in a giant safe deposit box. However, I would say that such
> a machine would be fundamentally incorrect for most operating environments.
Nice strawmen. Too bad it's a logical fallacy.
> Many people run their machines with "+ +" in root's .rhosts file because it
> eases the task of doing administration. They work in a company where the box is
> behind a firewall on some public network and they need there computers to
How about people inside the company? Are they all nice people who live in
happy valley?
> get real work done.... they don't want things like "security" to get in the
> way because nobody is trying to hack those machines.
I digress.
-Roberto
--
+----| Roberto Mello - http://www.brasileiro.net/ |------+
+ Computer Science Graduate Student, Utah State University +
+ USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +
Backup not found: (Q)uem mandou usar o Stacker?
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-02 06:33 ` Re: Mysql -> Postgresql pitfalls Roberto Mello <rmello@cc.usu.edu>
2003-08-02 17:10 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
@ 2003-08-03 18:19 ` Ian Barwick <barwick@gmx.net>
2003-08-04 16:29 ` Re: Mysql -> Postgresql pitfalls Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 26+ messages in thread
From: Ian Barwick @ 2003-08-03 18:19 UTC (permalink / raw)
To: Chad N. Tindel <chad@tindel.net>; Roberto Mello <rmello@cc.usu.edu>; +Cc: pgsql-docs
On Sunday 03 August 2003 19:03, you wrote:
> Many people run their machines with "+ +" in root's .rhosts file because it
> eases the task of doing administration. They work in a company where the
> box is behind a firewall on some public network and they need there
> computers to get real work done.... they don't want things like "security"
> to get in the way because nobody is trying to hack those machines.
Can you send me the names and current employers of these "many
people" so I can be sure I never do business with their companies
and / or consider them for employment?
Only slightly ;-)
Ian Barwick
barwick@gmx.net
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-02 06:33 ` Re: Mysql -> Postgresql pitfalls Roberto Mello <rmello@cc.usu.edu>
2003-08-02 17:10 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-03 18:19 ` Re: Mysql -> Postgresql pitfalls Ian Barwick <barwick@gmx.net>
@ 2003-08-04 16:29 ` Josh Berkus <josh@agliodbs.com>
2003-08-04 16:37 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 17:23 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
0 siblings, 2 replies; 26+ messages in thread
From: Josh Berkus @ 2003-08-04 16:29 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; Chad N. Tindel <chad@tindel.net>; Roberto Mello <rmello@cc.usu.edu>; +Cc: pgsql-docs
Folks,
Are we actually arguing about whether or not Postmaster should be allowed to
run as root?
I thought this question was settled, like, 5 years ago.
If migrating MySQL users have trouble with it, maybe we should focus on
supplying a battery of sample startup and maintainence scripts for them
instead of monkeying with PostgreSQL's security setup?
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-02 06:33 ` Re: Mysql -> Postgresql pitfalls Roberto Mello <rmello@cc.usu.edu>
2003-08-02 17:10 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-03 18:19 ` Re: Mysql -> Postgresql pitfalls Ian Barwick <barwick@gmx.net>
2003-08-04 16:29 ` Re: Mysql -> Postgresql pitfalls Josh Berkus <josh@agliodbs.com>
@ 2003-08-04 16:37 ` scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 0 replies; 26+ messages in thread
From: scott.marlowe @ 2003-08-04 16:37 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Ian Barwick <barwick@gmx.net>; Chad N. Tindel <chad@tindel.net>; Roberto Mello <rmello@cc.usu.edu>; pgsql-docs
On Mon, 4 Aug 2003, Josh Berkus wrote:
> Folks,
>
> Are we actually arguing about whether or not Postmaster should be allowed to
> run as root?
>
> I thought this question was settled, like, 5 years ago.
>
> If migrating MySQL users have trouble with it, maybe we should focus on
> supplying a battery of sample startup and maintainence scripts for them
> instead of monkeying with PostgreSQL's security setup?
No, I would consider that to have been a "thread jacking" over the weekend
while I was away. I don't read the lists on the weekends (I don't even
check my email, I pretty much disappear two days a week from the
internet).
anyway, the real issue is that there are common issues that we see from
migrating MySQL users, and we should probably have a "oh, you're coming
from MySQL-land? read this." kind of document.
The fact that most mysql users see not running as root as a heavy handed
tactic from the postgresql people, and not as a security issue give us a
hint on how to write such a document.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-02 06:33 ` Re: Mysql -> Postgresql pitfalls Roberto Mello <rmello@cc.usu.edu>
2003-08-02 17:10 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-03 18:19 ` Re: Mysql -> Postgresql pitfalls Ian Barwick <barwick@gmx.net>
2003-08-04 16:29 ` Re: Mysql -> Postgresql pitfalls Josh Berkus <josh@agliodbs.com>
@ 2003-08-04 17:23 ` Chad N. Tindel <chad@tindel.net>
1 sibling, 0 replies; 26+ messages in thread
From: Chad N. Tindel @ 2003-08-04 17:23 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Ian Barwick <barwick@gmx.net>; Chad N. Tindel <chad@tindel.net>; Roberto Mello <rmello@cc.usu.edu>; pgsql-docs
> Are we actually arguing about whether or not Postmaster should be allowed to
> run as root?
>
> I thought this question was settled, like, 5 years ago.
I don't think my responses have been being posted to the list... they're
probably all blocked pending approval of some administrator.
No, I'm not arguing to allow postmaster to run as root.
Chad
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
@ 2003-08-04 15:13 ` scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 16:49 ` Re: Mysql -> Postgresql pitfalls Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 26+ messages in thread
From: scott.marlowe @ 2003-08-04 15:13 UTC (permalink / raw)
To: Chad N. Tindel <chad@tindel.net>; +Cc: pgsql-docs
On Fri, 1 Aug 2003, Chad N. Tindel wrote:
> > > Excellent! That is exactly what I'm talking about.
> > >
> > > BTW, the "my2pg.pl" link is a dead link.
> >
> > This is probably something that could be codified a bit here in the news
> > group.
> >
> > I'd say the pitfalls I'm aware of are:
> >
> > autoincrement fields -> sequences
>
> Yes. The documentation very clearly states using sequences instead of
> auto-increment, but it doesn't make it clear that inserting the id's into
> data by hand doesn't cause the sequence to be auto-matically incremented. It'd
> be nice of postgres had a way to trigger an update of the sequence value after
> every insert containing an id clumn.
Actually, from a data cohesion point of view, that's an EXTREMELY
dangerous thing to do, and is not likely to ever get implemented.
However, mentioning that not only does postgresql do it this way, but
here's why it's dangerous to do it the MySQL way as well, would be a good
idea.
Note that what I'm thinking of in a list of these pitfalls is simply a
list of them, with links to the paragraphs that cover the pitfalls in the
regular docs.
> > not being able to run postgresql as root (a good thing TM)
>
> <RANT>As a programmer, I personally would never write code that kept people from
> running things as root. I mean, what is the point? If an administrator
> wants to run postgresql or apache as root, why shouldn't they be allowed
> to make that conscious decision for themselves?
> As it is, you have to recompile
> apache with some BIG_SECURITY_HOLE defined in order to run as root, which means
> you can't just use the out of the box apache rpm. Its so stupid to write
> *extra* code that keeps people from doing something that isn't even
> fundamentally incorrect.</RANT>
If you don't know why running a non-system service as root is bad, you
haven't been running Unix long enough. It is wrong, period, and
dangerous, period. Not because you might do something dumb, but because
it allows attackers to own your whole box should they compromise one
non-system service. Very bad form.
> All that being said, I don't think it causes too big of a problem for postgres
> installations.
No, only with folks who don't understand why running non-system services
as root is quite possibly the biggest mistake you can make when
configuring a service.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-04 15:13 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
@ 2003-08-04 16:49 ` Josh Berkus <josh@agliodbs.com>
2003-08-04 17:26 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
0 siblings, 1 reply; 26+ messages in thread
From: Josh Berkus @ 2003-08-04 16:49 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; Chad N. Tindel <chad@tindel.net>; +Cc: pgsql-docs
Chad, Scott:
> > Yes. The documentation very clearly states using sequences instead of
> > auto-increment, but it doesn't make it clear that inserting the id's into
> > data by hand doesn't cause the sequence to be auto-matically incremented.
> > It'd be nice of postgres had a way to trigger an update of the sequence
> > value after every insert containing an id clumn.
Um, how would this be a bennefit? If you're inserting rows 101-259, how does
it benefit you to have the system automatically increment the sequence from
601-759?
FWIW, in 7.5 we're likely to implement SQL3 IDENTITY columns ... basically
just a SERIAL column where you don't have the option of inserting your own
value, you have to take what it gives you.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-04 15:13 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 16:49 ` Re: Mysql -> Postgresql pitfalls Josh Berkus <josh@agliodbs.com>
@ 2003-08-04 17:26 ` Chad N. Tindel <chad@tindel.net>
2003-08-04 19:06 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 26+ messages in thread
From: Chad N. Tindel @ 2003-08-04 17:26 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: scott.marlowe <scott.marlowe@ihs.com>; pgsql-docs
> > > Yes. The documentation very clearly states using sequences instead of
> > > auto-increment, but it doesn't make it clear that inserting the id's into
> > > data by hand doesn't cause the sequence to be auto-matically incremented.
> > > It'd be nice of postgres had a way to trigger an update of the sequence
> > > value after every insert containing an id clumn.
>
> Um, how would this be a bennefit? If you're inserting rows 101-259, how does
In mysql, when you insert into an auto_increment field and you specify an id,
all future requests to insert a row without specifying the ID will still work
properly. In postgres, if you specify the id, your next insert without and
id will fail because the sequence won't have been updated.
> FWIW, in 7.5 we're likely to implement SQL3 IDENTITY columns ... basically
> just a SERIAL column where you don't have the option of inserting your own
> value, you have to take what it gives you.
Interesting... how do you import data from a dump with such columsn?
Chad
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-04 15:13 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 16:49 ` Re: Mysql -> Postgresql pitfalls Josh Berkus <josh@agliodbs.com>
2003-08-04 17:26 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
@ 2003-08-04 19:06 ` scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 19:29 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
0 siblings, 1 reply; 26+ messages in thread
From: scott.marlowe @ 2003-08-04 19:06 UTC (permalink / raw)
To: Chad N. Tindel <chad@tindel.net>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-docs
On Mon, 4 Aug 2003, Chad N. Tindel wrote:
> > > > Yes. The documentation very clearly states using sequences instead of
> > > > auto-increment, but it doesn't make it clear that inserting the id's into
> > > > data by hand doesn't cause the sequence to be auto-matically incremented.
> > > > It'd be nice of postgres had a way to trigger an update of the sequence
> > > > value after every insert containing an id clumn.
> >
> > Um, how would this be a bennefit? If you're inserting rows 101-259, how does
> In mysql, when you insert into an auto_increment field and you specify an id,
> all future requests to insert a row without specifying the ID will still work
> properly. In postgres, if you specify the id, your next insert without and
> id will fail because the sequence won't have been updated.
Correct. And MySQL is doing it "wrong" but everyone is used to it.
In Postgresql, there are exact functions for setting the increment.
Imagine this (T1 and T2 represent two different transactions:
T1: begin;
T2: begin;
T1: select nextval('seqname'); <- returns 55
T2: select nextval('seqname'); <- returns 56
T2: insert into parent_table (id,info) values (56,'information');
T1: insert into parent_table (id,info) values (55,'somemoreinformation');
T1: insert into child_table (p_id,info) values (55,'childinfo');
T2: insert into child_table (p_id,info) values (56,'childinfo');
T1: commit;
T2: commit;
If Postgresql autoset the field to the value last inserted, then the
sequence would be reset back to 55 and be ready to reissue 56 on the next
call. Bad news.
Or, imagine I delete a parent row then reinsert it, with a lower value,
then the sequence is reset. Resetting the sequence automatically on
insert is NOT the best way to handle sequences. Setting them by hand
during imports is the preferred method because you'll assume that you're
the only one on the database making changes.
Once you go live, i.e. you've got 200 simultaneous users doing updates,
the last thing you want is some user process accidentally setting your
sequence number to some low number that's already been used just because
they inserted it by hand.
Postgresql does things differently than MySQL, and most of the time it
does, it's been better thought out in postgresql, in terms of impact on
highly paralleled database accesses.
MySQL tends to be developed thinking of convenience instead of handling
the "whoopsies" situations that can be created by programming for
convenience. A lot like Unix versus Windows. Unix isn't just hard for
the fun of it, some things in unix are hard so you'll have to understand
the underlying reasoning before jumping in with both feet. The Windows
methodology tends to be faster to learn and use, but often puts your data
at risk.
> > FWIW, in 7.5 we're likely to implement SQL3 IDENTITY columns ... basically
> > just a SERIAL column where you don't have the option of inserting your own
> > value, you have to take what it gives you.
>
> Interesting... how do you import data from a dump with such columsn?
Easy, after you import the last row, you
select setval('seqname',lastvalue);
on the sequence. Like I said above, it's mostly just a different way of
doing things in Postgresql, and often those different ways are less
obvious, and quite often, being less obvious is actually safer even if
it is a littler harder to learn up front.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-04 15:13 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 16:49 ` Re: Mysql -> Postgresql pitfalls Josh Berkus <josh@agliodbs.com>
2003-08-04 17:26 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-04 19:06 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
@ 2003-08-04 19:29 ` Chad N. Tindel <chad@tindel.net>
2003-08-04 19:26 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 26+ messages in thread
From: Chad N. Tindel @ 2003-08-04 19:29 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Chad N. Tindel <chad@tindel.net>; Josh Berkus <josh@agliodbs.com>; pgsql-docs
> > > FWIW, in 7.5 we're likely to implement SQL3 IDENTITY columns ... basically
> > > just a SERIAL column where you don't have the option of inserting your own
> > > value, you have to take what it gives you.
> >
> > Interesting... how do you import data from a dump with such columsn?
>
> Easy, after you import the last row, you
>
> select setval('seqname',lastvalue);
>
> on the sequence. Like I said above, it's mostly just a different way of
> doing things in Postgresql, and often those different ways are less
> obvious, and quite often, being less obvious is actually safer even if
> it is a littler harder to learn up front.
But you just said that I can't actually include the id column in an insert
query. So how would I import data from a dump and ensure that the id columns
are what I expect them to be?
Chad
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-04 15:13 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 16:49 ` Re: Mysql -> Postgresql pitfalls Josh Berkus <josh@agliodbs.com>
2003-08-04 17:26 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-04 19:06 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 19:29 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
@ 2003-08-04 19:26 ` scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 21:01 ` Re: Mysql -> Postgresql pitfalls Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 26+ messages in thread
From: scott.marlowe @ 2003-08-04 19:26 UTC (permalink / raw)
To: Chad N. Tindel <chad@tindel.net>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-docs
On Mon, 4 Aug 2003, Chad N. Tindel wrote:
> > > > FWIW, in 7.5 we're likely to implement SQL3 IDENTITY columns ... basically
> > > > just a SERIAL column where you don't have the option of inserting your own
> > > > value, you have to take what it gives you.
> > >
> > > Interesting... how do you import data from a dump with such columsn?
> >
> > Easy, after you import the last row, you
> >
> > select setval('seqname',lastvalue);
> >
> > on the sequence. Like I said above, it's mostly just a different way of
> > doing things in Postgresql, and often those different ways are less
> > obvious, and quite often, being less obvious is actually safer even if
> > it is a littler harder to learn up front.
>
> But you just said that I can't actually include the id column in an insert
> query. So how would I import data from a dump and ensure that the id columns
> are what I expect them to be?
I did not say that, I was talking about serial types.
What the guy ahead of ME said was that they were looking at building the
SQL3 IDENTITY columns, which are a serial you can't set the value of in an
insert. With those, the import would happen "behind it's back" so to
speak in the \copy command. I.e. you wouldn't use inserts to load your
data, you'd use a bulk copy, which bypassess all the serial / IDENTITY
stuff. Basically, with the IDENTITY type, if you try to insert a value,
it just ignores it and inserts the next sequence. So, users would have no
way of setting the id being inserted. There would still be, I'm sure, a
method for setting the sequence number, it just might be limited to
superusers / IDENTITY owners.
So, I think we were getting Postgresql's CURRENT serial implementation
confused with a possible future implementation of IDENTITY type.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Mysql -> Postgresql pitfalls
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Re: Mysql -> Postgresql pitfalls Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-04 15:13 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 16:49 ` Re: Mysql -> Postgresql pitfalls Josh Berkus <josh@agliodbs.com>
2003-08-04 17:26 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-04 19:06 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 19:29 ` Re: Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-04 19:26 ` Re: Mysql -> Postgresql pitfalls scott.marlowe <scott.marlowe@ihs.com>
@ 2003-08-04 21:01 ` Rod Taylor <rbt@rbt.ca>
0 siblings, 0 replies; 26+ messages in thread
From: Rod Taylor @ 2003-08-04 21:01 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Chad N. Tindel <chad@tindel.net>; Josh Berkus <josh@agliodbs.com>; pgsql-docs
> speak in the \copy command. I.e. you wouldn't use inserts to load your
> data, you'd use a bulk copy, which bypassess all the serial / IDENTITY
> stuff. Basically, with the IDENTITY type, if you try to insert a value,
COPY enforces everything that insert does. It's simply a little quicker
than insert due to a different string parsing method and avoiding places
that are for advanced features (subselects, functions, etc.).
A default is still applied if the column has not been provided.
Likewise, triggers (constraint triggers anyway) still run.
Bumping the start value for an IDENTITY is simple:
CREATE TABLE tab (
col integer GENERATED ALWAYS AS IDENTITY(START WITH 42)
);
I suppose we'll need a GUC so that GENERATED ALWAYS isn't actually
always -- just usually.
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2003-08-04 21:08 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-01 01:10 Mysql -> Postgresql pitfalls Chad N. Tindel <chad@tindel.net>
2003-08-01 04:46 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-01 16:35 ` Chad N. Tindel <chad@tindel.net>
2003-08-01 20:29 ` scott.marlowe <scott.marlowe@ihs.com>
2003-08-01 20:51 ` Chad N. Tindel <chad@tindel.net>
2003-08-02 02:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-02 17:04 ` Chad N. Tindel <chad@tindel.net>
2003-08-03 17:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-04 15:21 ` scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 17:27 ` Lamar Owen <lowen@pari.edu>
2003-08-04 17:54 ` Chad N. Tindel <chad@tindel.net>
2003-08-04 21:08 ` Lamar Owen <lowen@pari.edu>
2003-08-02 06:33 ` Roberto Mello <rmello@cc.usu.edu>
2003-08-02 17:10 ` Chad N. Tindel <chad@tindel.net>
2003-08-02 21:21 ` Roberto Mello <rmello@cc.usu.edu>
2003-08-03 18:19 ` Ian Barwick <barwick@gmx.net>
2003-08-04 16:29 ` Josh Berkus <josh@agliodbs.com>
2003-08-04 16:37 ` scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 17:23 ` Chad N. Tindel <chad@tindel.net>
2003-08-04 15:13 ` scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 16:49 ` Josh Berkus <josh@agliodbs.com>
2003-08-04 17:26 ` Chad N. Tindel <chad@tindel.net>
2003-08-04 19:06 ` scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 19:29 ` Chad N. Tindel <chad@tindel.net>
2003-08-04 19:26 ` scott.marlowe <scott.marlowe@ihs.com>
2003-08-04 21:01 ` Rod Taylor <rbt@rbt.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox