Re: [DOCS] Mysql -> Postgresql pitfalls
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) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [DOCS] Mysql -> Postgresql pitfalls
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) > > 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. 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [DOCS] Mysql -> Postgresql pitfalls
On Sat, 2 Aug 2003, Chad N. Tindel wrote: > > > 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] truth table missing values
On Mon, 4 Aug 2003, Dennis Björklund wrote: > The reason stated in > http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#Differences_from_ANSI > > is > > "NULL AND FALSE will evaluate to NULL and not to FALSE. This is because we > don't think it's good to have to evaluate a lot of extra conditions in > this case." wow, with a development philosophy like that it's amazing mysql isn't running in banks everywhere. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] Mysql -> Postgresql pitfalls
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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [DOCS] Mysql -> Postgresql pitfalls
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.
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [DOCS] Mysql -> Postgresql pitfalls
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.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [DOCS] buffring in pgsql
On Thu, 18 Sep 2003, Josh Berkus wrote: > Monu, > > > I have developed a new page buffring algo for DBMS packages I want to > > implement it in postgresql but I haven't any knowlegde of postgresql code > > can any1 tell me from where I should start & which docs I will have to > > study for that. > > Start by reading all of: > http://www.postgresql.org/docs/7.3/interactive/developer.html > > *after* you've done that, join the PGSQL-HACKERS list. Be sure and look through the archives too at http://archives.postgresql.org/pgsql-hackers/ for anything people have done in the past relating to this. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
