Re: [GENERAL] PG vs MySQL
Hi david and PGSQL lovers, I think that PGSQL devellopers and users must not be upset by all the ongoing comparisons between MySQL and PostgreSQL. This is the direct illustration of the postgresql success, it gives me the feelings that its like a shameful desire of MySQL users to come to a more "professional" DB. It is a "demonstration" of the technical scale in DBMS Access/MySQL/PostgreSQL (IMHO). Like for every domain in the common life; if you want to make things better, you need to do it "harder", but at the same time, depending of your needs, before trying to do things better (migrating from MySQL to PostgreSQL) you want to check if the investment is worthwhile. 'cause the last 10% oftently costs 90% of the effort. So, don't turn the back to people trying to understand the diff. and simil. between the DBMS, or the pro's and con's. Explain (as you aleady does) that the 2 systems do not fullfill the same needs and does not requires the same skill, like the diff. between Access and MySQL. Perhaps there is a page on the web which explain that? I didn't found it, but may be somebody know one! The probleme is also linked, as can be seen from the amount of mail on general-psql list, to the fact that MySQL is available natively on Window (and very easy to install there), while PostgreSQL not yet, and a lot of people (me included) are waiting this. These 2 points explain the wealth of mail on general list and I really understand that PostgreSQL devellopers, programmers and exclusive users could be upset by mail avalanche on this subject. So, apart creating a mailing list for native window users, it could be of some interest to create a "MySQL migration" list to clear general list. I must admit that I am incline to unsuscribe, although it is a nice chanel to stay informed and continue to learn from other users experiences, but too much information kills information. All the best to the postgresql team, Pierre PS: I hope that my english is understable, and that I didn't hurt anybody with misunderstandings or mistakes. David Garamond wrote: Alex wrote: MySQL is still the default database offered by any web hosting company and if Postgres wants to become the designated db engine for these services or become the worlds no.1 open source db then i think lots of things need to be done. Take for example the admin interface (MySQL Administrator) for MySQL which is done very professionally or the ease of setting up Replication. Postgres still is quite far behind there and for normal users that know MySQL best the transition is probably a too big step and risk. But then again, real admin uses CLI :-) doing this only 24hours per day ;-) Trust me, administering PG is not at all harder than MySQL, Apache, Bind, Qmail, FB, etc. The only extra thing I need to do compared to MySQL or FB is that I need to run VACUUM from time to time, but that's so easy to do and autovacuum might be the way of the future anyway. As to usage, PG is also very easy and convenient to use. Want some proofs? 1) PG's command line client is *much better* than MySQL's. Common basic users love window interface, and even more experienced ones like them because that are (WI) more "self explanatory", give you an easy way to use all the possibilities of commands, without having to remember all the tiny tricks hiden everywhere in corners. 2) There are lots of webhosting provider offering PostgreSQL. 3) API/binding to virtually any language/environment (I don't think I've seen .NET Data Provider or Parrot binding for MySQL); 4) LIMIT clause (with nicer syntax), autoincrementing column, easy BLOB/TEXT, full-text search, replication, etc.? You got it. But of course, if you're looking for other MySQL "conveniences" such as silently chopping your string, silently converting your column data type, allowing entering invalid values in your ENUM column, allowing invalid dates, allowing breaking FK integrity, etc. then PG does not have those. But I find them scary anyway :-) These are among the technical advantages of PostgreSQL, and I experienced somes, introducing weird data in my tables ;-o -- Pierre -- DIDELON :@: pdidelon_at_cea.frPhone : 33 (0)1 69 08 58 89 CEA SACLAY - Service d'Astrophysique 91191 Gif-Sur-Yvette Cedex -- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PG vs MySQL
On Sun, 28 Mar 2004, Mike Nolan wrote: > I'm not much of an expert in MySQL, but on my ISP 'show databases' only > shows MY databases. Right, show databases == \l, I believe ... but, how is security on the table(s) that 'show databases' dealt with ... can you access those directly, by passing the "security" on 'show databases'? > I also wonder how well the pg_hba.conf method will scale. What happens > if there are hundreds of client databases or thousands of entries in > pg_hba.conf? I'd be more concerned with how any server would scale to having thousands of databases running on it myself ... but, right now, my largest server is running ~165 databases, servicing 4 physical servers, and the server itself is still highly responsive. Note that the server that that database is running on runs two other instances of PostgreSQL (7.2 and 7.4), 2 of MySQL (4.0 and 4.1), one of Firebird and about 25 other "virtual servers" ... loadavg is generally <2 ... Only time I've ever really had a problem with it was when we ran Mnogosearch for the archives ... major dog on resources, since it wasn't really designed for anything by MySQL ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PG vs MySQL
On Sun, 28 Mar 2004, Mike Nolan wrote: > > > Perhaps, but it isn't obvious which directory has which database. I'm not > > > not sure which system catalogs provide that information, something that > > > wasn't obvious from the online docs, either. > > > > SELECT oid FROM pg_database WHERE datname = ''; > > Thanks. That should be easier to find in the documentation, perhaps it > should be mentioned in the docs for the pg_database system catalog. > > From an ISP's or DBA's point of view, it would be preferable if there was > a way to determine which directory held which database without having > to actually log into the database. I can envision circumstances under > which postmaster might not be running when that information is needed. Two valid points ... I've brought them up onto hackers to see about getting both rectified ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] PG vs MySQL
Alex wrote: MySQL is still the default database offered by any web hosting company and if Postgres wants to become the designated db engine for these services or become the worlds no.1 open source db then i think lots of things need to be done. Take for example the admin interface (MySQL Administrator) for MySQL which is done very professionally or the ease of setting up Replication. Postgres still is quite far behind there and for normal users that know MySQL best the transition is probably a too big step and risk. How easy is it to setup a reliable replication with failover, switchover and especially switchback? I have never done that in MySQL, but you seem to know quite a bit about it. If I have one master and 3 slaves, and I need to shutdown the master for maintenance purposes, what do I have to do to switch over to one of the slaves, let the other two slaves replicate against that new master, and when I'm done and the original master could take over again, what's the procedure to get it back into the master role? Keep in mind that our databases are quite a few gigabytes in size and that longer interruption of accessibility is not acceptable (that's what we do that replication stuff for in the first place). If you don't know the answers to that, I assume it isn't that easy as people try to make believe. And in case the answer is "that is not possible but ...", then you better think again what you want that replication setup for. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Contrib question
Of course I meant that is contained in "vacuumlo" --> stupid spell checker auto-corrected it. -Original Message- From: Dann Corbit Sent: Monday, March 29, 2004 2:24 PM To: [EMAIL PROTECTED] Subject: Contrib question Is there any functionality contained in vacuum that is not contained in pg_autovacuum? Suggestion: Modify the base schema for tables and add a timestamp column for the last vacuum operation, and a timestamp for the last vacuum analyze. Finally, a timestamp for the last time that the data was modified via insert update or delete would be handy (but I realize that this timestamp might be problematic). With that data, a crude autovacuum could be generated periodically whether or not statistics were enabled. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Contrib question
Title: Message Is there any functionality contained in vacuum that is not contained in pg_autovacuum? Suggestion: Modify the base schema for tables and add a timestamp column for the last vacuum operation, and a timestamp for the last vacuum analyze. Finally, a timestamp for the last time that the data was modified via insert update or delete would be handy (but I realize that this timestamp might be problematic). With that data, a crude autovacuum could be generated periodically whether or not statistics were enabled.
Re: Interval constant syntax, was Re: [GENERAL] Interval & check
On Mon, 29 Mar 2004, Karl O. Pinc wrote:
>
> On 2004.03.29 14:44 Bruno Wolff III wrote:
> >
> > In postgres you shouldn't have to explicitly cast the constant to an
> > interval as long as there isn't one than one >= operator that could
> > be applied (depending on the eventaul type of the constant). I would
> > really be surprized if this were to happen for >= and an interval
> > operand
> > on one side or the other.
> >
> > It won't work with two unknown constants, if that was what you tested.
> > Try just casting on one side.
>
> This is my plpgsql code
>
> PERFORM MATUREDATES.sname FROM MATUREDATES
> WHERE NEW.sname = MATUREDATES.sname
>AND ( NEW.birth > MATUREDATES.Matured - ''3 years''
> OR NEW.birth > MATUREDATES.Matured - ''7 years'');
>IF FOUND THEN
>
> And this was my scratch psql test:
>
> => select CAST('1/1/2004' AS date) - '3 years';
> ERROR: Bad date external representation '3 years'
I think that's because (date - date) is the prefered interpretation.
The best way to specify an interval literal is probably
INTERVAL '3 years'
which is close to the SQL specification of an interval literal.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PG vs MySQL
On Mon, 29 Mar 2004, Tom Lane wrote: > "scott.marlowe" <[EMAIL PROTECTED]> writes: > > And while we're at it, maybe we should have a setting somewhere should > > someone execute the famous "update pg_shadow set usesuper = false" that > > someone did a while back to be able to force an account to be a superuser > > account. > > We already have an adequate solution for that one: shut down the > postmaster and run a standalone backend. You are always superuser in > a standalone backend, so you can create a new superuser or just reverse > the UPDATE command. Ahhh. Good point. Any chance of having the same behaviour for pg_hba as a table? I.e. you accidentally remove all connectability and you could restore it to a pg_hba table? Does that even make sense? I'm not sure. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PG vs MySQL
On Mon, 29 Mar 2004, Tom Lane wrote: > It might be possible to do something with a flat file as an intermediary > between the postmaster and the tables that are the master data. We > already do this for pg_shadow passwords, and I've been thinking of > proposing that we add a flat file for the database name -> OID mapping > so we could get rid of the horrid hack that is GetRawDatabaseInfo(). > Per-database flat files would be a bit messy though. The ability to manipulate pg_hba via interfaces stuck as phpPgAdmin and PgAdmin would definitely be a plus, even if it just results in something like pg_shadow ... in an ISP environment, pg_hba is about the only 'hassle' that I think really exists ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PG vs MySQL
On Mon, 29 Mar 2004, Mike Nolan wrote: > I know of an ISP who has a large number of customers (in excess of 400) > running similar small (probably under 100MB each) MySQL databases. Since > I know each customer has access only to his own data, I assume it is > implemented using a different database for each customer. Whether or not > it is on one or several machines is a detail I'm not sure of. Note that we are actually talking about two different things here ... I have a server with 165 databases running on it for clients ... no client can access another clients database, as access is restricted to a user created specifically for the client that owns the database, as well as the IP that they will be connecting from ... Now, that doesn't preclude clients from seeing the names of another clients database using \l, but unless there is gross mis-management of the pg_hba.conf, seeing the names of other databases doesn't give other clients any benefits ... > Without knowing much about how pg uses the pg_hba.conf file, I don't > know what problems porting that ISP to pg might raise, I only cite it as > an example of an extreme case that might not have been anticipated and > thus possibly an inherent limit in the pg_hba.conf method. To be honest, I can't see much in the way of issues with migrating the above scenario from MySQL -> PostgreSQL ... other then the obvious migration of table structures and data, but there are more then enough scripts out here for mysql2pg conversion ... > I think it is possible to discuss MySQL features in comparison to pg > without getting into an Annie Oakley/Frank Butler-style argument here. Agreed, else I wouldn't have jump'd in ... you've brought up a couple of points that I've raised on the -hackers list ... but, I don't consider them to be 'high priority' issues, mainly because there is no security reasons to adopt a 'hide it all' policy similar to MySQL ... In fact, I'd almost go to the extent of saying that MySQL model of hiding things would result in a slightly more lazy admin maintaining the server, since they would be relying more on MySQL to provide security for them, instead of them providing it themselves ... we (PgSQL) tend to be more aware of our servers *because* we have to think about whether we've setup the security properly ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] PG vs MySQL
Which HA abilities does PG lack? CSN I sincerely hope not. PostgreSQL is THE free database that can reach the production quality of the major databases (Oracle, DB2). The only remaining feature it lacks out of the box is replication and some HA abilities the big ones have. And for that it's really free as in beer and speech. Not a fake type of hybrid license like MySQL. __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PG vs MySQL
> Now, that doesn't preclude clients from seeing the names of another > clients database using \l, but unless there is gross mis-management of the > pg_hba.conf, seeing the names of other databases doesn't give other > clients any benefits ... That rather depends upon what those clients are doing, doesn't it? I can see benefits from being able to completely isolate one client/database from another, even to the point of not giving them any hints that they're sharing the same database server. (Depending on how fanatical I am about it, there are other solutions, such as separate instances or completely separate physical systems, but those present a different set of administrative issues.) It may be more of a marketing issue than a technical one. If we want increased commercial acceptance, that may be one of the higher priority features from an ISP's (or his clients') point of view, if not from ours. -- Mike Nolan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] PG vs MySQL
On Mon, 29 Mar 2004, Mike Nolan wrote: > > Now, that doesn't preclude clients from seeing the names of another > > clients database using \l, but unless there is gross mis-management of the > > pg_hba.conf, seeing the names of other databases doesn't give other > > clients any benefits ... > > That rather depends upon what those clients are doing, doesn't it? I don't know, does it? I can't think of any circumstance that this would give a client any advantage ... can you? > It may be more of a marketing issue than a technical one. If we want > increased commercial acceptance, that may be one of the higher priority > features from an ISP's (or his clients') point of view, if not from > ours. Coming from the ISP side, I can't say that, in the 8 or so years I've been providing PostgreSQL, I've ever had anyone voice a concern about being able to see other clients databases ... most of my clients don't even see the database, as they are using such thinks as OpenACS (they plug in the connection details, and OpenACS loads the schema/data for them) or phpPgAdmin (which has an option to hide databases not owned by them) ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Contrib question
On Monday 29 March 2004 05:24 pm, Dann Corbit wrote: > Of course I meant that is contained in "vacuumlo" --> stupid spell I have never worked with large objects in postgresql and I have no idea what is different with vacuumlo. Suggestions or thoughts anyone? Matthew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PG vs MySQL
On Mar 29, 2004, at 7:44 PM, Marc G. Fournier wrote: On Mon, 29 Mar 2004, Mike Nolan wrote: Now, that doesn't preclude clients from seeing the names of another clients database using \l, but unless there is gross mis-management of the pg_hba.conf, seeing the names of other databases doesn't give other clients any benefits ... That rather depends upon what those clients are doing, doesn't it? I don't know, does it? I can't think of any circumstance that this would give a client any advantage ... can you? Depends what you name the databases, honestly. Never underestimate the power of the belief in confidentiality (paranoia to the rest of us) inherent in certain industries - actual advantage gained means nothing compared to the perception thereof. I do much work in the financial and environmental industries - you can't spit without offending a lawyer (particularly in the environmental space. The lawyers are the only ones who make any money...). Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: Interval constant syntax, was Re: [GENERAL] Interval & check clause
On Mon, Mar 29, 2004 at 15:28:59 -0600,
"Karl O. Pinc" <[EMAIL PROTECTED]> wrote:
>
> And this was my scratch psql test:
>
> => select CAST('1/1/2004' AS date) - '3 years';
> ERROR: Bad date external representation '3 years'
>
> I am using 7.3 so maybe this has been fixed. Or it's just the
> operator, some require casts and some don't and you just have to find
> out the hard way. :(
This is different in that you are using - instead of >= . Date - Date
will get picked because that is the only - operator with a left operand
of type date. You are trying to use timestamp - interval (with date
getting promoted to timestamp).
---(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: Interval constant syntax, was Re: [GENERAL] Interval & check clause
Bruno Wolff III <[EMAIL PROTECTED]> writes: > This is different in that you are using - instead of >= . Date - Date > will get picked because that is the only - operator with a left operand > of type date. Even if it weren't the only one, there is a preferential case involved: given "known_type operator unknown_literal", the parser will preferentially assume that the unknown_literal is the same type as the other operand, if there is an available operator that takes that type on both sides. For details see http://www.postgresql.org/docs/7.4/static/typeconv-oper.html (rule 2a is my point here). Note that in *no* case will the contents of the string literal have any a-priori effect on the parser's decision about what the literal's type is. I believe this is a good policy in general --- doing otherwise would render the behavior way too unpredictable, since often the contents of the literal are not under the control of the SQL query author. But this does mean that just writing '2 days' is not going to be enough to make the system think it is an interval constant. There must be some cue to the type outside the quotes, whether an explicit cast or an implicit match to another operand. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
