[DOCS] truth table missing values
The truth table in http://www.postgresql.org/docs/7.3/static/functions.html#FUNCTIONS-LOGICAL is missing some rows. The meaning is clear if you know that AND and OR is commutative but that is not written there either. The reason I found this at all is because in mysql AND is not commutative. In other words, in mysql SELECT (NULL AND FALSE); is not the same as SELECT (FALSE AND NULL); 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." I tried to look in the devel doc to see if it was the same there, but the devel part of the site is gone at the moment it seems like. It might be a good idea to add all combinations just to make it clear for people comming from mysql (not me, I just stumbled over it by mistake). -- /Dennis ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[DOCS] Incorrect REVOKE command in docs
Greetings, The 'REVOKE' command given in section 2.8.4 under Chapter 2. "Data Definition" is not correct. The command should be: REVOKE CREATE ON SCHEMA public FROM PUBLIC; Note that 'SCHEMA' is missing. This is at least on the web page: http://www.postgresql.org/docs/7.3/static/ddl-schemas.html under the 7.3 docs. If I should have sent this to -bugs let me know. Stephen pgp0.pgp Description: PGP signature
Re: [DOCS] Incorrect REVOKE command in docs
Already fixed in 7.4. Thanks. Stephen Frost writes: > Greetings, > > The 'REVOKE' command given in section 2.8.4 under Chapter 2. "Data > Definition" is not correct. The command should be: > > REVOKE CREATE ON SCHEMA public FROM PUBLIC; > > Note that 'SCHEMA' is missing. This is at least on the web page: > http://www.postgresql.org/docs/7.3/static/ddl-schemas.html > under the 7.3 docs. > > If I should have sent this to -bugs let me know. > > Stephen > -- Peter Eisentraut [EMAIL PROTECTED] ---(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] truth table missing values
Dennis Björklund writes: > The truth table in > > http://www.postgresql.org/docs/7.3/static/functions.html#FUNCTIONS-LOGICAL > > is missing some rows. The meaning is clear if you know that AND and OR is > commutative but that is not written there either. I've added a note that they are commutative. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
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
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [DOCS] Mysql -> Postgresql pitfalls
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 ---(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, 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 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
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] Mysql -> Postgresql pitfalls
> 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [DOCS] Mysql -> Postgresql pitfalls
> > 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [DOCS] Mysql -> Postgresql pitfalls
> > > 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] Mysql -> Postgresql pitfalls
> > > 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
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [DOCS] Mysql -> Postgresql pitfalls
> 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. signature.asc Description: This is a digitally signed message part
Re: [DOCS] Mysql -> Postgresql pitfalls
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
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[DOCS] man pages for v7.4 ... ?
Peter, I have the following man pages being copied in in my mk-beta script: %ls -ld /var/spool/ftp/pub/dev/doc/man.tar.gz -rw-r--r-- 1 petere wheel 146009 Nov 21 2002 /var/spool/ftp/pub/dev/doc/man.tar.gz Appears a bit old ... ? :) Just checked the mk-snapshot script, and its copying in the same files ... is there a newer version I need to grab, or a make target I should be using? ---(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
[DOCS] duplicated index entry found ...
Non-critical, but figured I'd mention it ... this was from the beta build this evening ... /usr/local/bin/perl /usr/local/share/sgml/docbook/dsssl/modular/bin/collateindex.pl -f -g -i 'bookindex' -o bookindex.sgml HTML.index Processing HTML.index... 568 entries loaded... duplicated index entry found, CHARACTER STRINGS LENGTH duplicated index entry found, LENGTH CHARACTER STRINGS duplicated index entry found, SUBSTRING duplicated index entry found, SUBSTRING 4 entries ignored... Done. Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: [EMAIL PROTECTED]|postgresql}.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
