PostgreSQL v7.2 Final Release

Excellent!! by nbvb · 2002-02-06 16:16 · Score: 0, Flamebait

Postgres is a great database.... really full-featured and feels more "robust" than mySQL..

I'd trust production data to postgres... not to mySQL though.

BTW, does anyone know if any of the original Ingres code made it into postgres? My understanding was that it was supposed to be the "next" Ingres...

Re:Excellent!! by dietz · 2002-02-06 16:24 · Score: 3, Informative

Ingres code did make it in to Postgres, but I don't know if any of it is in PostgreSQL. I'm sure there was a lot initially, but I seriously doubt there's any there anymore.

Keep in mind that when you abbreviate PostgreSQL to "Postgres", you're really talking about a seperate, older product.

I guess it doesn't really matter, since Postgres is long gone, but it still annoys me every time I see it.
Re:Excellent!! by Moosbert · 2002-02-06 17:19 · Score: 2, Informative

BTW, does anyone know if any of the original Ingres code made it into postgres? My understanding was that it was supposed to be the "next" Ingres...

INGRES and POSTGRES were two separate projects at Berkeley that shared no code. PostgreSQL is based on the latter.

See also the POSTGRES FAQ.
Re:Excellent!! by RMSIsAnIdiot · 2002-02-06 19:10 · Score: 0, Flamebait

Excuse me, but why is it whenever someone makes a negative comment (read: constructive criticism, you can use some) about OSS, they get labeled as "Trolls" that like to spread "FUD"? Will someone please answer this? Wow, he said that one piece of free software is better than another... that is so trollish (!)... you retard moderators. Everyone knows that mySQL is a piece of s**t, and I personally wouldn't trust it as far as I could throw the box that it's on (by coincidence, it's a 30 lb IBM XT). So there. -RMSIAI

--
Re:Excellent!! by aminorex · 2002-02-06 19:55 · Score: 1

> Postgres is a great database....

No argument there.

> ...and feels more "robust" than mySQL.

And centripetal force "feels" like it's pulling
you in the direction of your radius of rotation.
And strange quarks "taste" less filling that charm
quarks. This is not data, it's blathering
propaganda.

.

--
-I like my women like I like my tea: green-
Re:Excellent!! by Anonymous Coward · 2002-02-06 20:56 · Score: 0

as a regular troll on slashdot i am pissed that this legit post got modded as a troll. you faggot moderators.
Re:Excellent!! by FrostedChaos · 2002-02-06 22:14 · Score: 1

Um... how is saying Postgres is more reliable than mySQL "blathering propaganda"? If the parent really used both, and thought one felt more stable, who are you to argue? Just because he used the word "feel" instead of "analyze" or "examine" makes no difference here.

You use analogies from physics, but you would do well to remember that physics (and all science) involves observation as well as deduction. Sometimes, you have to draw inferences.

By the way, Data, the best benchmarks for a program are derived from simply using the program under normal conditions.

--
"Any connection between your reality and mine is purely coincidental." -Slashdot
Re:Excellent!! by RealBorg · 2002-02-06 22:41 · Score: 1

I wouldn't trust my data to a database that needs to be dumped and restored for a version upgrade.
I also wouldn't use a database that doesn't understand and refuses to restore it's own dumpfiles.
And finally I wouldn't use a database where I have to check every query using explain to make sure that the query optimizer does the right thing(tm).
If postgresql is that robust, why should one don't kill -9 the postmaster?!?
Re:Excellent!! by madprof · 2002-02-06 23:16 · Score: 1

A *little* troll-like perhaps?
Not every PostgreSQL upgrade requires a dump-restore.
Could you quote version numbers which don't have compatible dump files?
Re:Excellent!! by chriskl · 2002-02-06 23:28 · Score: 1

Hrm. I've had to dump/restore my old MySQL database enough times between versions - so I don't see how that's different to Postgres?

As for the query optimizer - why on earth do you trust your data to a database where you can't even optimize your queries.

At least postgres gives you partial indexes, functional indexes, histogram dispersion analysis of your columns, etc.

More power - but at a price: higher learning curve.

It's so worth it.

Chris
Re:Excellent!! by Anonymous Coward · 2002-02-07 01:59 · Score: 0

I would trust Access with my critical data before I trusted MySql.
Re:Excellent!! by Anonymous Coward · 2002-02-07 05:30 · Score: 0

yeah it seems pretty good, its pretty similar to oracle in setup it has triggers and stored procs. it even has something similar to pl/sql for stored procs.now it just needs an olap engine and some star schema optimizer and it will kick ass
Re:Excellent!! by Golias · 2002-02-07 05:51 · Score: 2

Keep in mind that when you abbreviate PostgreSQL to "Postgres", you're really talking about a seperate, older product.

I guess it doesn't really matter, since Postgres is long gone, but it still annoys me every time I see it
To call PostgreSQL a "seperate" product is not entirely acurate. The reason for the name change (first to Postgres95, and later to PostgreSQL), was because they dumped the PostQuel query language in favor of good ol' SQL. Under the hood, it's still the same DBMS (although somewhat enhanced and several versions more mature).
Go to PostgreSQL.org, and you will discover that in the opening pages of their tutorial is the following line:
The terms "Postgres" and "PostgreSQL" will be used interchangeably to refer to the software that accompanies this documentation.

So if you have a problem with people saying "postgres" instead of "postgreskew-ell", take it up with the folks writing the documentation.

--
Information wants to be anthropomorphized.
Re:Excellent!! by aminorex · 2002-02-07 06:59 · Score: 1

My point is simply that feelings are deceptive,
and not to be regarded as anything but
propaganda unless the source has some
credentials (conspicuously absent in this
case) establishing the expertise of their
testimony. Even if honest, and impartial,
the claim is purely subjective.

How about some hard data? Then I wouldn't care
if you were a dog. I could just evaluate your
data, and interpret it for my own purposes, even
experimentally verify your claims, for the love
of Popper.

Better yet, provide credentials or references
to credible sources, if you want to provide an
executive summary (very useful) rather than
raw data.

Finally, I would point out that the number of
MySQL installations probably outstrips the number
of PGSQL instalations by an order of magnitude.
Why? One suspects an evolutionary element at
work here. Now that's a factual claim,
accompanied by a falsifiable hypothesis. You
may not be able to evaluate it without some
work, but at least it's evaluable, not pure FUD.

--
-I like my women like I like my tea: green-
Re:Excellent!! by FrostedChaos · 2002-02-10 23:22 · Score: 1

My point is simply that feelings are deceptive, and not to be regarded as anything but propaganda unless the source has some credentials...

Having credentials does not necessarily make a source more or less impartial. But it might make a source more reliable under certain circumstances.

Better yet, provide credentials or references to credible sources, if you want to provide an executive summary (very useful) rather than raw data.

Your argument boils down to "I don't believe you." Couldn't you have said that in fewer words?

Finally, I would point out that the number of
MySQL installations probably outstrips the number
of PGSQL instalations by an order of magnitude.
Why? One suspects an evolutionary element at
work here.

The number of windows installations outstrips the number of linux installations. Do you suspect an evolutionary element at work there, too?

You criticize someone for offering their opinion about a piece of software, but then go on to make a highly subjective, completely unverifiable claim about "evolution." You can do better.

--
"Any connection between your reality and mine is purely coincidental." -Slashdot

Congrats to the PostgreSQL Development team! by thing12 · 2002-02-06 16:17 · Score: 3, Insightful

This is a huge step forward in making PostgreSQL ready for deployment in the enterprise. Eliminating the locking vacuum in favor of a separate statistics gathering process is clearly the best part of this release.

The only major hurdle left is replication built into the server.

Re:Congrats to the PostgreSQL Development team! by thing12 · 2002-02-06 16:22 · Score: 5, Interesting

Eliminating the locking vacuum...
I should probably clarify that - the full locking vacuum was separated out into two parts one which analyzes statistics and doesn't lock the tables and another which does what the old vacuum did by reordering data blocks to shrink the size on disk.... the bonus is that before you might run vacuum once a week or so because of the impact it has to a production system by doing a full lock on each table it vacuumed, now you can run it much more frequently as all it consumes is cpu time. Shrinking size on disk is nice, but it's the statistics that help the query planner turn SQL into faster queries.
Re:Congrats to the PostgreSQL Development team! by GooberToo · 2002-02-06 16:42 · Score: 4, Insightful

Always remember that table statistics are used for approximate best guesses for inputs to the query optimizer. It is not uncommon or unheard of to actually see somes types of queries run slower after table statistics have been updated. I've seen this on Oracle, Sybase and SQL Server. I doubt that this is an issue unique to those RDBMS since the conceptual implementations and basis for algorithms tend to all be more or less the same.
Re:Congrats to the PostgreSQL Development team! by nconway · 2002-02-06 16:55 · Score: 4, Interesting

I should probably clarify that - the full locking vacuum was separated out into two parts one which analyzes statistics and doesn't lock the tables and another which does what the old vacuum did by reordering data blocks to shrink the size on disk....

This is incorrect (I believe it describes the situation after 7.1: the VACUUM ANALYZE command only needed to lock the table exclusively when VACUUMing, only a read lock was needed for ANALYZE).

In 7.2, the ANALYZE command can now be used separately, as you say. However, there are other (more important) improvements: ANALYZE only takes a look at a statistical sampling of the rows in the table. This means that collecting statistics on even enormous tables is very fast. Furthermore, VACUUM has been made "lazy" by default: this means that it doesn't attempt to reclaim space as aggressively as before, but it no longer requires an exclusive lock on the database (instead, it cooperates with other DB clients). The old behavior is available as "VACUUM FULL", and it is suggested whenever you need to reclaim a lot of diskspace (e.g. you delete hundreds of thousands of rows of data and need the space).

you might run vacuum once a week or so

It was (and is) suggested that you run VACUUM once per day.

it's the statistics that help the query planner turn SQL into faster queries.

As far as I know, you only really need to update your planner stats when you change the statistical distribution of your data. Of course, running ANALYZE's reasonably often won't do any harm, and is a relatively cheap operation (performance-wise).
Re:Congrats to the PostgreSQL Development team! by thing12 · 2002-02-06 17:17 · Score: 4, Informative

I'm not disagreeing with anything you said, in fact you all but reiterated everything I said.
The 7.1 vacuum analyze required table locks. Doesn't matter which phase of it required locks - it required exclusive locks because it vacuumed. By breaking that into a separate commands the need for downtime is reduced drastically (down to the example which you point out - deleting thousands of rows at a time).
I know that you're recommended to run vacuum once per day, but I found that on a large database running on a fast server a daily vacuum took nearly 30 minutes to complete... that's 30 minutes of sequentially locked tables. Can't afford to do that every day - moving it to once a week may have degraded performance but it reduced the downtime window from 30 minutes per day to 1 hour per week.
I'm just happy that I don't have to bring a production server to its knees once a week (or for that matter once a day) just to do some table maintenance.
Re:Congrats to the PostgreSQL Development team! by nconway · 2002-02-06 17:30 · Score: 4, Informative

I'm not disagreeing with anything you said, in fact you all but reiterated everything I said.

No I didn't, read my post again.

The 7.1 vacuum analyze required table locks.

PostgreSQL has lots of different types of locks of varying granularities. Saying "table locks" doesn't mean a whole lot.

Doesn't matter which phase of it required locks

It does though -- in 7.1, splitting vacuum and analyze internally reduced the time that an exclusive lock needs to be held.

By breaking that into a separate commands the need for downtime is reduced drastically

This is where you're wrong. The reduction in downtime has nothing to do with allowing ANALYZE to be executed separately. It is entirely the result of the new vacuum code (which is "lazy", unlike a VACUUM FULL -- which does a 7.1-style VACUUM). In 7.2, running VACUUM (with or without ANALYZE) is fast, and doesn't require an exclusive lock -- so your database can continue serving clients while a VACUUM is executing. Whether you choose to run ANALYZE at the same time or separately is really irrelevant.

I'm just happy that I don't have to bring a production server to its knees once a week (or for that matter once a day) just to do some table maintenance.

On that, we agree ;-)
Re:Congrats to the PostgreSQL Development team! by thing12 · 2002-02-06 19:03 · Score: 2

No I didn't, read my post again.
I'll say that again, you reiterated everything I was thinking that I implied in my post... but true not everything I said.
This is where you're wrong. The reduction in downtime has nothing to do with allowing ANALYZE to be executed separately. It is entirely the result of the new vacuum code (which is "lazy", unlike a VACUUM FULL -- which does a 7.1-style VACUUM). In 7.2, running VACUUM (with or without ANALYZE) is fast, and doesn't require an exclusive lock -- so your database can continue serving clients while a VACUUM is executing. Whether you choose to run ANALYZE at the same time or separately is really irrelevant.
Yes, I misspoke there and left out lots of detail - when I run a vacuum I always run it with anaylyze since there's no way to analyze in 7.1 without vacuuming (at least that I'm aware of). Why would I want to run vacuum followed by vacuum analyze only to have the tables exclusively locked twice? I want to do both, clean up unused rows, and update stats.. so I always run vacuum analyze. What's worse is that the 7.1 vacuum can deadlock with other processes because it uses exclusive locks.
And yes, I know that in 7.1.x they changed the locking behavior so the vacuum analyze so it did not hold an exclusive lock during the portion where it was anlyzing the table. But that doesn't change the fact that it did hold an exclusive lock while vacuuming - which it had to do because the commands were joined together.
Effectively there were TWO good things that happened. Analyze was broken into a separate command - that alone would have been an improvement since it would have allowed for vacuums to be done distinctly from analyzes. Vacuum becoming lazy is a second good thing - and yes a very good thing it is. The short of all this is that now you can run vacuums and analyzes separately all day long if you want.
I'm not trying to get tha last word ;-) really! I think we both can agree that 7.2 is a big leap forward.
Re:Congrats to the PostgreSQL Development team! by JoeyLemur · 2002-02-06 22:35 · Score: 1

Actually, I would say that replication and some form of tablespace/datafile system are the only hurdles to the enterprise. The ability to add more space to the database on the fly is very handy and important in a 24/7 environment.
Re:Congrats to the PostgreSQL Development team! by bwt · 2002-02-07 02:57 · Score: 3, Informative

That is definately a risk. It is often possible to harm overall system performance by upgrading an RDBMS that includes optimizer improvements. Any changes to an optimizer will change execution plans. Hopefully most of them get better, but a few get worse, often dramatically worse. Finding the ones that get worse and tuning them is an important activity. Bad SQL plans are often the biggest impact tuning activity, so it is very important to understand what will happen to your specific application before you make changes that affect how your SQL-statements are implemented.

This is one area in which Oracle shows its power over the open source databases. (It's also a big oportunity because Oracle can be improved on). Oracle can actually tap into continuous statistics gathering on a per SQL level by using oracle's v$sqlarea dictionary view. If you need high-powered scrutiny on a particular activity, you can trace the session to logs and see the row statistics at every step of the exectution plan. Oracle has more optimizer hints, and has a facility to "pin" an execution plan, so that it won't be reevaluated if optimizer behavior changes. Oracle is working toward server-side SQL tuning, where you can ID bad SQL's and "intercept" them at runtime by adding hints on the server side. That will be an absolutely huge feature, since often SQL hits your system that you can't directly control but can predict.
Re:Congrats to the PostgreSQL Development team! by GooberToo · 2002-02-07 08:58 · Score: 2

First, I'd like to say thanks for the information on Oracle. That's a very interesting route that I'd not considered before. Very interesting indeed.

As for the optimizer issues associated with updated statistics, I guess I would like to expand on this slightly. Most people don't realize that many optimizers have hard and arbitrary thresholds for determining the best possible algorithmic data path. This is why it is very important to understand not only your data distribution but the nature of the delta of your data distribution. Also key is is the understanding of how multiple indexes will effect the optimizer's (especially if multiple index types are supported by the RDBMS in question) output. Common causes for negative query performance is slight distribution statistical changes which hit an arbitrary threshold causing another data path to be recommended by the optimizer. Thusly the significance of Oracle being able to pin query plans can be profound.

Because of these issues, my self and several other DBA's do not advocate blindly updating statistics simply for the sake of doing so. First of all, if your data distribution tends not to vary, this can result in nothing more than a waste of time. Furthermore, if your data distribution tends to vary only slightly, you'd better understand these trends as it's very possible timely and updated statistics may work against you depending on where you were within your trend's cycle.

While I knew about the pinned query plans on Oracle, I didn't know about the planned server tuning hints. Abilities (assuming I understood you correctly) like this can go a long way toward addressing the issues I've raised about query optimizers.

In short, know your database AND your data!

this is a great thing by Anonymous Coward · 2002-02-06 16:18 · Score: 0

I am glad that they have gotten this far I dont know what I would do if it werent for that damn chipmunk

Substantial improvements and it's only .1 higher by sawilson · 2002-02-06 16:19 · Score: 1

It's nice to see that they took a year, actually did a great deal of work making it better, and it's gone from 7.1 to 7.2. I always thought it was kinda lame when someone changes 3 characters in output and jumps from 1.3 to 3.0. Damn I need sleep.

--

The most important thing any republican needs to know.

Congrats PostgreSQL! by Anonymous Coward · 2002-02-06 16:19 · Score: 0

Excellent work!

Awsome.. by rtaylor · 2002-02-06 16:19 · Score: 2

These guys are here to kick ass and chew bubble gum -- but they're all out of gum.

On a serious note, I greatly welcome the 4 billion transaction limit being raised. Sure, it's 126 per second over the period of a year -- but it's one step closer to being the database for a major system.

Next step, master-master replication and clustering. Only wish I knew enough about either topic to help out. I'm doing lots of research on it though, maybe someday.

--
Rod Taylor

Re:Awsome.. by AtrN · 2002-02-06 16:41 · Score: 2

These guys are here to kick ass and chew bubble gum -- but they're all out of gum.
"They Live". What a film.
"We've got one who can see!"
(Kind of on topic actually, people are starting to get through the hype associated with some things and adopt stuff that works, like PostgreSQL).
Re:Awsome.. by Anonymous Coward · 2002-02-06 17:40 · Score: 0

"On a serious note, I greatly welcome the 4 billion transaction limit being raised. Sure, it's 126 per second over the period of a year -- but it's one step closer to being the database for a major system. "

I almost fell over my chair when I saw that, please tell me there has never been such a limit or that it isn't what is seems to be. 4 billion transactions are not that much in a production system.

Also, I see that their disk reclaiming have locked whole tables!!!
Re:Awsome.. by defile · 2002-02-07 02:50 · Score: 2

On a serious note, I greatly welcome the 4 billion transaction limit being raised. Sure, it's 126 per second over the period of a year -- but it's one step closer to being the database for a major system.

Jeez. I had no idea. If our MySQL server had this limit:

Uptime: 10277498 Threads: 7 Questions: 3678405287 Slow queries: 1584 Opens: 2299222 Flush tables: 1 Open tables: 256 Queries per second avg: 357.909

I'd be receiving a phone call in about a month from my client wondering why the site stopped working. ;)

And just to make this look less like flamebait: I use and appreciate both MySQL and PostgreSQL. Do get a life, kids.
Re:Awsome.. by snubber1 · 2002-02-07 03:15 · Score: 1

Uhh, I may be wrong on this one, but I do belive they are referring to *TRANSACTIONS*
Something MySql only supports on certian tables.
Saying that before 7.2 you could only call BEGIN 4 billion times...

--
I don't really mind double posts on //..
Re:Awsome.. by jslag · 2002-02-07 03:31 · Score: 1

If our MySQL server had this limit:
. . .Questions: 3678405287 . . .I'd be receiving a phone call in about a month from my client wondering why the site stopped working. ;)

Clarification - a 'Question' in mysqlese doesn't have to be an update / insert / delete, does it? I don't think SELECTs are counted as transactions under the pgsql limit.
Re:Awsome.. by King+Babar · 2002-02-07 04:13 · Score: 2

On a serious note, I greatly welcome the 4 billion transaction limit being raised. Sure, it's 126 per second over the period of a year -- but it's one step closer to being the database for a major system.
Jeez. I had no idea. If our MySQL server had this limit:
Uptime: 10277498 Threads: 7 Questions: 3678405287 Slow queries: 1584 Opens: 2299222 Flush tables: 1 Open tables: 256 Queries per second avg: 357.909
I'd be receiving a phone call in about a month from my client wondering why the site stopped working. ;)

As others have pointed out, there's a difference between "Queries" and "Transactions". Now, people have often facetiously accused MySQL people from not knowing what a Transaction is, but here we have some solid proof. :-)
Seriously, the previous PostgreSQL limitation probably did have some serious ramifications for bigger installations, so it's a good thing it's history now. When I bring PostgreSQL up in polite public these days, Oracle jockeys now feel the need to critique it more seriously than the "don't make me laugh" attitude of even a couple of years ago. Not because PostgreSQL is that likely to sink a real high-end RDBMS, but it will be an increasingly plausible contender for users who don't really need or use all of the cool (but expensive) features of Oracle or DB2.

--
Babar
Re:Awsome.. by Anonymous Coward · 2002-02-07 05:45 · Score: 0

Yes, except in Postgres every query which is not explicitely in a transaction block, automatically becomes a one-statement transaction. Nye-nah!
Re:Awsome.. by rtaylor · 2002-02-07 15:01 · Score: 2

4 billion transactions include updates, deletes and inserts. Not that its great, but any system thats doing that many changes in a second probably has enough money to spend on good hardware and needs a DB with excellent commercial support -- since the latter doesn't exist, Oracle or DB2 will due ;)

Selects aren't counted of course.

Yes, disk reclaiming locked whole tables. Mind you, until recently MySQL locked tables for every statement (including selects??), so did SyBase (but not for selects I don't think).

Locking a table for a minute once a day isn't nearly as bad as 150 times a second -- but of course Postgres does neither now.

--
Rod Taylor

Great. by Anonymous Coward · 2002-02-06 16:21 · Score: 0

Security
A new MD5 encryption option allows more secure storage and transfer of passwords. .

MD5 isn't encryption. Back to the drawing board guys.

Re:Great. by thesupraman · 2002-02-06 17:10 · Score: 2, Interesting

ok, so replace encryption with one way hashing. It's effectively the same thing here, it enhances the security of your passwords, a very very welcome step, and not something to be treated lightly.

One of the biggest problems I have with database servers are all the passwords that end up floating around semi-protected, this is a GREAT new feature!
Re:Great. by Anonymous Coward · 2002-02-06 18:08 · Score: 0

Ah, but you're wrong; any one-way hash can be converted at will to a block cipher. Back to the Schneier book for you, mister...
Re:Great. by dnelson · 2002-02-06 20:02 · Score: 1

Yeah, I think the wording here wasn't what quite right. I don't actually *know* for sure... but I'm guessing that maybe they're using MD5 to hash a 'password', much like unix passwords and check that.

highlights... by bob@dB.org · 2002-02-06 16:24 · Score: 5, Informative

from http://www.us.postgresql.org/news.html

Highlights of this release are as follows:

VACUUM: Vacuuming no longer locks tables, thus allowing normal user access during the vacuum. A new "VACUUM FULL" command does old-style vacuum by locking the table and shrinking the on-disk copy of the table.
Transactions: There is no longer a problem with installations that exceed four billion transactions.
OID's: OID's are now optional. Users can now create tables without OID's for cases where OID usage is excessive.
Optimizer: The system now computes histogram column statistics during "ANALYZE", allowing much better optimizer choices.
Security: A new MD5 encryption option allows more secure storage and transfer of passwords. A new Unix-domain socket authentication option is available on Linux and BSD systems.
Statistics: Administrators can use the new table access statistics module to get fine-grained information about table and index usage.
Internationalization: Program and library messages can now be displayed in several languages.

.. with many many more bug fixes, enhancements and performance related changes ...

--
Acts@core.mailboks.com Acrux@core.mailboks.com Adam@core.mailboks.com Adar@core.mailboks.com Ada@core.mailboks.com

Re:highlights... by Zeut · 2002-02-06 16:43 · Score: 5, Informative

One issue that is not mentioned in the release highlights is the marked improvement that is now available for SMP boxes. In some cases throughput has been increased by more than a factor of 2.
Re:highlights... by Metrollica · 2002-02-06 16:55 · Score: 1

There is also a new version of PostgreSQL AutoDoc v0.31 available that works with the new PostgreSQL.

--

--Metrollica
Re:highlights... by bob@dB.org · 2002-02-06 16:56 · Score: 1

One issue that is not mentioned in the release highlights is the marked improvement that is now available for SMP boxes. In some cases throughput has been increased by more than a factor of 2.
care to share some references to where we can read more about this? i just read the /pub/README.v72 document, and it had nothing on SMP.

--
Acts@core.mailboks.com Acrux@core.mailboks.com Adam@core.mailboks.com Adar@core.mailboks.com Ada@core.mailboks.com
Re:highlights... by Metrollica · 2002-02-06 17:00 · Score: 1

Right here... jackass!

Sorry, I couldn't help myself...

--

--Metrollica
Re:highlights... by bob@dB.org · 2002-02-14 06:52 · Score: 1

been using it for a week now, and it rocks!

--
Acts@core.mailboks.com Acrux@core.mailboks.com Adam@core.mailboks.com Adar@core.mailboks.com Ada@core.mailboks.com
Re:highlights... by bob@dB.org · 2002-02-21 08:39 · Score: 1

still rocking...

--
Acts@core.mailboks.com Acrux@core.mailboks.com Adam@core.mailboks.com Adar@core.mailboks.com Ada@core.mailboks.com
Re:highlights... by bob@dB.org · 2002-02-21 08:46 · Score: 2

and rocking...

--
Acts@core.mailboks.com Acrux@core.mailboks.com Adam@core.mailboks.com Adar@core.mailboks.com Ada@core.mailboks.com

I have only one feature request for PostgreSQL... by Trepalium · 2002-02-06 16:28 · Score: 3, Interesting

I really wish there was an embedable version of PostgreSQL... It's a very good database, but it's sometimes a real pain to write a program that ties together a SQL database with anything else, unless it's a local-use only program. I know MySQL added this feature in 4.x (but their transaction support is too new, IMO).

--
I used up all my sick days, so I'm calling in dead.

Obligatory MySQL flames by Gothmolly · 2002-02-06 16:30 · Score: 1, Redundant

Guys, lets keep the signal/noise ratio high for once?

--
I want to delete my account but Slashdot doesn't allow it.

Re:Obligatory MySQL flames by Anonymous Coward · 2002-02-06 16:47 · Score: 0

You're kidding, right? On slashdot? Most of the people that post here have nothing to do but flame things, people, pets, objects, or anything else. :)
Re:Obligatory MySQL flames by Anonymous Coward · 2002-02-06 16:53 · Score: 0

So, in order to facilitate increasing the signal/noise ratio, you add some noise?

Keep up the good work, then!

Re:Backup Copy In Case (Don't Mod This Please) by gmhowell · 2002-02-06 16:31 · Score: 0, Offtopic

Maybe you could have posted this a little sooner if you weren't an FP'ing little bitch.

(Moderators, go read this comment first, mod that down as offtopic, and THEN mod this as offtopic. Or better yet, find two quality posts and mod them up.)

--
Jesus was all right but his disciples were thick and ordinary. -John Lennon

some might disagree by Filthysock · 2002-02-06 16:32 · Score: 0, Flamebait

http://www.mysql.com/news/article-87.html

Re:some might disagree by dietz · 2002-02-06 16:45 · Score: 5, Interesting

That's a cheesy way to dispute his claims.

They're not use MySQL to store all their critical data. They're dumping all their data, presumably from some other more reliable database (Oracle, it sounds like), into mysql for quick web searches.

IOW, they're using mysql for what it does best: As a fast datastore for when data integrity isn't important (because they have all the data backed up in Oracle and could redump it to mysql at any time).

Admittedly, some of this post is conjecture, but you'd be crazy to suggest that the Census Bureau would trust all their critical data to mysql.
Re:some might disagree by GooberToo · 2002-02-06 16:49 · Score: 3, Informative

Don't take this the wrong way but that is seemingly exactly an ideal project for which MySQL was made for. Data access appears to be primarily read-only with none of it being critical. Worse case, if the database becomes corrupt, you simply restore the whole thing from back up. Since the data doesn't appear (based on description) to be changing rapidly (if at all or perhaps backend updates only), issue such as online back or high availability is likely not to be an issue in the least. If they were, they'd probably be using that Oracle license.
Re:some might disagree by Filthysock · 2002-02-06 16:52 · Score: 1

Ooops, apologies to you and GooberToo, i didn't read the article properly.
Re:some might disagree by aminorex · 2002-02-06 19:47 · Score: 0

I just have to point out that this post is pure
FUD, since the moderators have failed so miserably.
.

--
-I like my women like I like my tea: green-
Re:some might disagree by Anonymous Coward · 2002-02-06 23:38 · Score: 0

Explain to me how it's pure FUD? Though the article doesn't clearly state that the original data source WASN'T MySQL (it's a MySQL press release, after all!), it pretty clearly implies that their main data repository is Oracle.

Why else would they have an (extremely expensive) Oracle site license?

Seriously, I have no problem with using MySQL for what it's designed for, and this is a clear case where MySQL is a winner. It's noticibly faster than PostgreSQL (and probably Oracle, though I've never compared) and speed is the issue for these websites.

But if data integrity is your #1 goal (as it should be for the Census Bureau... a LOT of money went into getting that data), it's extremely unlikely that you're going to be using MySQL. If you're the Census Bureau, it's pretty unlikely that PostgreSQL would enter into consideration either.

I mean, all your love for MySQL aside, lets be realistic here.

Genome Project by Vardamir · 2002-02-06 16:38 · Score: 1

Will the human genome project be upgrading to this?

I like it when people use linux on non-x86 architectures.

Re:Hurt Me! by Anonymous Coward · 2002-02-06 16:49 · Score: 0

www.EricKrout.com

he has a vanity dot com for fuck's sake

Those Bastards! by pHDNgell · 2002-02-06 16:52 · Score: 1

I *just* finished my FreeBSD 4.5 upgrade on my database server last weekend. It was a big effort, as I've got multiple databases with tables ranging from a few to 7 million rows with referential integrity and average at least 1 insert every ten seconds every day.

Now I've gotta do it again!

--
-- The world is watching America, and America is watching TV.

Re:Those Bastards! by nconway · 2002-02-06 16:58 · Score: 1

average at least 1 insert every ten seconds every day.

You do realize that is a trivial workload, right? Maybe you meant "10 inserts per second"? (or 100x as much)
Re:Those Bastards! by Anonymous+DWord · 2002-02-06 19:05 · Score: 2

Aww, poor you. I just upgraded last night. Bah.

--
"If he thinks he can hide and run from the United States and our allies, he's sorely mistaken." Bush on bin Laden
Re:Those Bastards! by larien · 2002-02-06 21:43 · Score: 2

Er, why? Are you having problems with your existing systems that an upgrade might fix?
Remember that being on the bleeding edge is a risk, especially if it might put your data at risk.

Please mode this up! by GooberToo · 2002-02-06 16:53 · Score: 1

Someone please MOD this up!

Thanks for the info. I didn't know that.

What a karma whore... :P~

Why use PostgreSQL instead of MySQL?: ACID by Sivar · 2002-02-06 17:01 · Score: 5, Informative

PostgreSQL is an ACID compliant database. MySQL is not (unless that has changed recently--if so please let me know).
ACID (an acronymn for Atomicity Consistency Isolation Durability) is a 'keyword' that business professionals generally look for when evaluating databases. Frankly, non-ACID databases aren't taken very seriously, even if they are used by the likes of Yahoo and Slashdot (like MySQL is).
Here is a quick description of what it means to be ACID compliant:
1. Atomicity is an all-or-none proposition. Suppose you define a transaction that contains an UPDATE, an INSERT, and a DELETE statement. With atomicity, these statements are treated as a single unit, and thanks to consistency (the C in ACID) there are only two possible outcomes: either they all change the database or none of them do. This is important in situations like bank transactions where transferring money between accounts could result in disaster if the server were to go down after a DELETE statement but before the corresponding INSERT statement.

2. Consistency guarantees that a transaction never leaves your database in a half-finished state. If one part of the transaction fails, all of the pending changes are rolled back, leaving the database as it was before you initiated the transaction. For instance, when you delete a customer record, you should also delete all of that customer's records from associated tables (such as invoices and line items). A properly configured database wouldn't let you delete the customer record, if that meant leaving its invoices, and other associated records stranded.

3. Isolation keeps transactions separated from each other until they're finished. Transaction isolation is generally configurable in a variety of modes. For example, in one mode, a transaction blocks until the other transaction finishes. In a different mode, a transaction sees obsolete data (from the state the database was in before the previous transaction started). Suppose a user deletes a customer, and before the customer's invoices are deleted, a second user updates one of those invoices. In a blocking transaction scenario, the second user would have to wait for the first user's deletions to complete before issuing the update. The second user would then find out that the customer had been deleted, which is much better than losing changes without knowing about it.

4. Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination. Hence, even if the database server is unplugged in the middle of a transaction, it will return to a consistent state when it's restarted. The database handles this by storing uncommitted transactions in a transaction log. By virtue of consistency (explained above), a partially completed transaction won't be written to the database in the event of an abnormal termination. However, when the database is restarted after such a termination, it examines the transaction log for completed transactions that had not been committed, and applies them.

It is difficult to trust mission critical data to a database that does not guarantee that it will complete not screw up (short of a bug, of course), this such compliance--even when it is more political than technical--is very important.

--
Computer Science is no more about computers than astronomy is about telescopes. --E. W. Dijkstra

Re:Why use PostgreSQL instead of MySQL?: ACID by Anonymous Coward · 2002-02-06 17:13 · Score: 2, Insightful

All true. In fact, MySQL is neither relational nor a DBMS by any stretch of the imagination, if you really understand what these terms mean.

People who try to argue otherwise (including the developers of MySQL), need to go back to school to relearn their math and CS fundamentals. There's a bit of discussion of this at http://searchdatabase.techtarget.com/tip/1,289483, sid13_gci788645,00.html?FromTaxonomy=%2Fpr%2F28487 2
Re:Why use PostgreSQL instead of MySQL?: ACID by Pathwalker · 2002-02-06 17:20 · Score: 5, Funny

Here's an example of why an ACID database is useful that hits close to all of our hearts - Slashdot moderation:

You may have noticed that if several people try to whack a troll at the same time, they all expend one moderator point, even if only a fraction of those points were required to push that troll down into the dreaded depths of -1.

If an ACID complient database were used, and the two steps of whacking the troll, and deducting the moderator points were placed in the same transaction (with a check constraint on the score of the posts to prevent them from dropping below -1) then the later moderators who tried to whack the troll would not have their points deducted, as the transaction would rollback when the constraint on the score of the post was exceeded.

Alas, mysql is not ACID complient, and so this sensless waste of moderator points continues to this day...
Re:Why use PostgreSQL instead of MySQL?: ACID by Anonymous Coward · 2002-02-06 17:28 · Score: 1, Troll

Drop acid, not tables.
Re:Why use PostgreSQL instead of MySQL?: ACID by GoRK · 2002-02-06 17:49 · Score: 2

Actually, this is not the fault of the database. If you mod a post that is already +5 up or mod a -1 post down you will lose a mod point and the score will go unchanged (the moderation total values will increase though)

You can use transactions or locking with MySQL to keep consistancy on this particular issue as well; however, under either database, the use of a transaction to record a moderation is fairly frivilous and probably more of a waste of CPU time than moderation point -- which is why slash doesn't implement it.

For clarification, you can't use transactions on MyISAM tables [yet] but it's not like transactions in MySQL don't exist at all. Just use BDB, InnoDB, or Gemini table types and you get transaction support.

Anyway, I don't care which DB you prefer or use or promote or whatever. I have run into programming problems with both Postgres and MySQL alike.

So, even though you are quite incorrect (which your probably already knew), at least you got me to respond to your troll. :)

Let the whacking commence!

~GoRK
Re:Why use PostgreSQL instead of MySQL?: ACID by the+way · 2002-02-06 18:01 · Score: 2

MySQL is ACID compliant if you use the InnoDB table type. InnoDB comes with the most recent version of MySQL. It supports transactions and rollback, row level locking, and referential integrity. Slashdot uses InnoDB.
Re:Why use PostgreSQL instead of MySQL?: ACID by jrimmer · 2002-02-06 18:10 · Score: 3, Informative

MySQL is most definitely capable of supporting ACID functionality.
One of the nice features of MySQL is the capability of having pluggable persistence managers. An example of that is the default, MyISAM, which you are correct in saying does not support ACID. But with the release of MySQL-Max, which happened awhileago(tm), and MySQL v4 out of the box, support for 3 additional backends was added, BerkelyDB, Gemini, and InnoDB, all of which have complete ACID support. InnoDB also supports row level locking and even an initial implementation of foreign keys.

InnoDb is is in use here at Slashdot as well as a good deal of other sites demanding high-transaction throughput with full ACID support.

With the addition of foreign keys and stored procedures functionality, all of which are on the slate for the 4.x series, the reasons not to use MySQL are lessening every day.

Side note: Yeah, I know Gemini is the red-haired stepchild of the MySQL world. It's still a decent table manager.
Re:Why use PostgreSQL instead of MySQL?: ACID by Sivar · 2002-02-06 18:17 · Score: 1

Thankyou for this info, you and the previous post. I was not aware of that. Open source software moves so damn fast, it's all but impossible to keep up. Maybe that's why many people prefer the Windows world. They know that they'll have at least a month before worrying about the patch to fix the latest bug. :-)

--
Computer Science is no more about computers than astronomy is about telescopes. --E. W. Dijkstra
Re:Why use PostgreSQL instead of MySQL?: ACID by gregfortune · 2002-02-06 18:47 · Score: 1

Argh...

Let's first assume that the problem that you described does indeed exists. If an ACID complient database were used, and it were configured correctly, the coder of the slashdot site would be required to group the code for the two steps into the same place and send it as one single transaction.

If for some reason, the a database like mysql were used because maybe the project requirements demanded a screaming fast database, then the coder would be required to group his code in such a way that it did its own consistency checks in a proper manner and perhaps locked a row for write access if necessary.

Now, which of those is more work? I don't really know. Neither seem very difficult and I've built systems both ways. Remember, somewhere in code, someone has made the operation autonomous. The server doesn't really give a damn if it's done in application code, or in the database. In fact, the users of the site don't really care either...

So, don't go knocking it if you don't understand it...
Re:Why use PostgreSQL instead of MySQL?: ACID by rycamor · 2002-02-06 18:52 · Score: 2, Informative

Yes, you can move to InnoDB tables, but your performance goes South quickly, thus taking away one of the few reasons to use MySQL.

One of my clients had a Linux Apache/PHP/MySQL box that was being heavily pounded by 250+ web-based instant messaging users. (I know... using a database to handle instant messages :-( ). Anyway, I, in my simple logic, thought

1. The message tables are constantly subject to INSERT, SELECT, and DELETE queries.

2. MyISAM tables require a full lock for every INSERT or DELETE

3. InnoDB tables have row-level locking

4. Thus, I will install MySQL-Max, and use InnoDB tables for the messaging data, thus relieving PHP of the continuous connect...wait...reconnect scenario

Well, I read every note about configuring MySQL for performance, and I made the change to InnoDB, and promptly every single one of the 30 or 40 MySQL processes began taking up at least 60 MB RAM! The system slowed to a standstill, and I had to roll back to regular MyISAM tables 'real quick'. (the users on the system take about 10 seconds to start pestering the admin with trouble reports)

I know the real answer is to use shared memory or some such, but anyway, the performance benchmarks always show PostgreSQL ruling when you have mixed INSERTS and DELETES.
Re:Why use PostgreSQL instead of MySQL?: ACID by Pathwalker · 2002-02-06 19:11 · Score: 5, Informative

You Said:
Actually, this is not the fault of the database. If you mod a post that is already +5 up or mod a -1 post down you will lose a mod point and the score will go unchanged (the moderation total values will increase though)

I Reply:
I have a hard time believing that this behavior started out as a feature. I find it much more likely that it was initially a bug. This bug, being found useful was then elevated to the status of a "feature".
You are correct that it is not the fault of the database, but transaction and constraint support at the database level would have made it easy to prevent this problem from ever cropping up in the first place.

You Said:
the use of a transaction to record a moderation is fairly frivilous and probably more of a waste of CPU time than moderation point

I Reply:
For a system which recieves as much activity as Slashdot, and with a constant stream of friendly trolls looking for any crack in the system that they can use to share the sight of their favorite gaping asshole with the unwilling members of the rest of the population, if I were coding it, I would insist on inserting checks of basic constraints at different levels of the system. The database layer is your last line of defense against abuse of the system.

Secondly, these double checks are useful for finding errors in other levels of the system. Remember the problems that used to crop up from time to time with comments being moderated to -2 or to 6? If the value of the moderations was constrained in the database, not only would users not see this problem, but an error log generated (for the admins only) when a transaction is rolled back in a situaition where it is not expected would have helped isolate the fault very quickly.

The database level checks would also help against rogue activity of people in positions of (limited) trust. Worried about an editor editing one of their accounts to give themselves a huge number of modpoints? Cap the level in the database at 5; it would make it impossible for this nefarious subterfuge to take place.

As for the speed issue; if you are willing to sacrefice verification of correct operation for a small increase in speed, you have severly underspecified your hardware requirements.

Finally, I would like to include a small SQL fragment, showing some of the checks that I would feel are absolutely necessary for a web based discussion system that people are trying to subvert:

--First we create a table for a couple of users
CREATE TABLE "users" ( "uid" integer serial, "mod_points" integer default 0, "name" text not null, CONSTRAINT "user_mod_const" CHECK (((mod_points > -1) AND (mod_points < 6))) );--now a table for some postsCREATE TABLE "posts" ( "date" timestamp with time zone DEFAULT 'now()', "pid" serial, "parent" int4 "uid" int4, "mod" integer DEFAULT 1, "body" text not null, "section" integer, CONSTRAINT "mod_const" CHECK (((mod > -2) AND (mod < 6))), CONSTRAINT "user_key" FOREIGN KEY (uid) REFERENCES users(uid) on delete cascade on update cascade );-- the constraint to ensure parent is equal to zero, or another pid in the posts table is left to the reader.

--And now for a function to access them. (Remember - direct SQL is icky; run things through functions to ensure a consistant interface)
CREATE FUNCTION "mod_down" (integer,integer) RETURNS integer AS '
begin; update users set mod_points=mod_points-1 where uid=($1);
update posts set mod=mod-1 where pid=($2);
commit;
select mod from posts where pid=($2);
' LANGUAGE 'sql';

As you can see, this nicely serves as a check to ensure the restrictions I mentioned above. With it being so trivial to add the checks, I can't see any reason to not take this extra step to eliminate nasty surprises.
Re:Why use PostgreSQL instead of MySQL?: ACID by Anonymous Coward · 2002-02-06 19:46 · Score: 0

Direct SQL is icky? Not if you grok functional languages.
Re:Why use PostgreSQL instead of MySQL?: ACID by Pathwalker · 2002-02-06 19:57 · Score: 5, Insightful

Direct SQL is icky? Not if you grok functional languages.

I like SQL - I have no problems understanding SQL. I meant that statement in the sense that it feels more clean (to me) to call a stored procedures in the database from the outside, rather than sending the SQL statements to the database over and over.

It also serves as a handly layer of abstraction from your code, in the event that you want to make drastic changes to the structure of the underlying database.

Maybe I've just seen too much code with SQL commands scattered all over the place, doing the same thing in different fashions at different times, and using strange DB specific constructs at random.

I would prefer to keep the DB specific SQL extensions (if they must be used at all) in stored procedures in the database, and present a consistant interface of stored procedures to the external program. That way, you can support different databases, using the special features they each offer, by providing different database initialization files, and not require any changes to the main program.
Re:Why use PostgreSQL instead of MySQL?: ACID by Kraft · 2002-02-06 20:12 · Score: 2

One of my clients had a Linux Apache/PHP/MySQL box that was being heavily pounded by 250+ web-based instant messaging users. (I know... using a database to handle instant messages :-( ).

What's the real problem using a DB for instant messaging? What do you propose as an alternative?

--

-Kraft
Live and let live
Re:Why use PostgreSQL instead of MySQL?: ACID by Anonymous Coward · 2002-02-06 20:38 · Score: 0

>However, when the database is restarted after >such a termination, it examines the transaction >log for completed transactions that had not been >committed, and applies them.

One small nit to pick with this. After restart,
during the recovery process, transactions that
had not been committed are ROLLED BACK (!) *not*
committed. The database engine will "apply"
transactions that have already been committed,
but not yet written to the database (still in
the transaction log but not yet in the data
file.) Why would you want to "apply" an
uncommitted transaction ??!!?

jwp
Re:Why use PostgreSQL instead of MySQL?: ACID by CatherineCornelius · 2002-02-06 20:51 · Score: 1

Alas, mysql is not ACID complient, and so this sensless waste of moderator points continues to this day...
Deduct one point: troll. :)
Re:Why use PostgreSQL instead of MySQL?: ACID by jrimmer · 2002-02-06 21:09 · Score: 2, Informative

Sounds like your tuning was out of whack. You're not very specific about what you tuned (heck, if everyone was specific what fun would Slashdot be?) but I'd bet you had 2 wires crossed somewhere. We're running an system with, what would appear to be, a larger number of inserts and retrievals, for stock quotes and have had completely different results from your experience. We're very happy with the performance and find in a mixed insert/select situation InnoDB is quite a bit faster than MyISAM.

After your bad experience did you send an email to Mr. InnoDB, Heikki Tuuri? He's quite attentive and I'm sure would like to hear what happened with your application.

Not to mention when you're playing with databases or other vaguely unknown programmatic quantities, best not to test them in production. Perhaps if you had run the system in a test environment the lessened pressure would've enabled you to be better equipped to resolve whatever situation was occurring.
Re:Why use PostgreSQL instead of MySQL?: ACID by Anonymous Coward · 2002-02-06 23:17 · Score: 0

"InnoDb is is in use here at Slashdot "

Yes, but Slash has been around longer than InnoDB has. Which means they get the benefit of rowlocking, but unless they went and rewrote their code, they aren't using transaction locking. Not that it really matters for karma.
Re:Why use PostgreSQL instead of MySQL?: ACID by V.P. · 2002-02-06 23:21 · Score: 1

His use of terminology is inconsistent, but he's right.
You can have pieces of data that a commited transaction has changed, but are still in memory buffers and have not gone to disk yet. Then some idiot trips on the power cord.
Now, the transaction was commited, but the data on disk still reflect the old state of the database. That's why when the database comes back up, it has to replay the log, and make sure that the changes do make it to disk.
There's a beautiful (family of) algorithm(s) called ARIES that specifies how you do that properly.
It's tricky, especially when you want to ensure that the process will work, even if the server goes down while the database is trying to recover
Re:Why use PostgreSQL instead of MySQL?: ACID by Anonymous Coward · 2002-02-06 23:29 · Score: 0

I have to agree with the original poster.

You have made clever use of table contraints rather than a check in your mod_down function itself, but in this particular case if you're going to use constraints, the transaction part is still unnecessary overhead. Since the constraint will disallow the update on the posts table if it is out of range, you could choose to do the mod_points update based on the outcome. It would likely be an order of magnitude more efficient than using a real transaction, at least in this particular instance with slashcode.

You might as well make it a perl subroutine, or hell, just use the new perl procedural language inside MySQL itself.

For slash, remember that better==faster. Mod points aren't money. If you're worried about getting your grammy's inheritence cash, let's hope that happens inside of some transaction blocks. Slash is a billion hacks kludged together. It's full of bad design and junk whipped up in 10 minutes to make stuff work.

I find it difficult to present the argument that MySQL is shit because Slashcode is shit.

-=AnonyMoosE=-

I can hear my monitor coils ringing.
Re:Why use PostgreSQL instead of MySQL?: ACID by j1mmy · 2002-02-07 01:27 · Score: 1

Most IM software is actually P2P. Messages don't actually pass through a server. I think the problem here isn't using a DB for IM, it's delivering IM over http. That's got to be hell on the server.
Re:Why use PostgreSQL instead of MySQL?: ACID by jallen02 · 2002-02-07 01:45 · Score: 1

It is horribly ineffecient, I have written a chat system using a database for the message storage interface.

I used a hidden frame that contained some JavaScript. The script updates every so often, say 15 seconds. Every 15 seconds the hidden frame, acting as a crude socket, updates and queries the database to see if there are any new messages to display for a particular user. It grabs any new messages and rewrites layer/frame for that user.

Thats a query every 15 seconds for a user, and EVERY message sent generates a database write. Every user coming or going generates yet another write.

Granted with a high number of users there are still probably going to be more reads than writes, it is still quite write and read intensive. 10 users generate 10 queries every 15 seconds, or 40 queries a minute, PLUS any writes they do which just add to the mess.

This is not even a realtime chat system, and its a pain making it work with HTTP. Its a real-time (every 15 seconds)tm system.

Could you imagine writing an instant messaging system with even half a percent of AIMs daily message load? You are talking tens of thousands of reads and writes per second. It quickly becomes very ineffecient and silly.

Jeremy
Re:Why use PostgreSQL instead of MySQL?: ACID by Anonymous Coward · 2002-02-07 03:17 · Score: 0

I understand your concerns about consistancy and business rules enforced at the DBMS level versus trusting the application layer solely (DBAs never do and never should trust app. programmers).
However, there is a trade-off: stored proceedures can tie you inexplicably to one DBMS backend forever. This is fine as long as there is a business/strategic decision to marry your DBMS vendor, but divorces can/will be nasty, messy and lossy. I've seen it happen more than once.
That's why I prefer an API contained in an external-but-physically-close application wrapper (and further application access through this wrapper only) on those projects where we want to have the ability to swap PostgreSQL and Oracle (for instance).
I've seen ENTIRE apps written in PL/SQL...and I've seen those same projects fail when application logic (not just the CRUDs) had to change for business reasons and the enormity of the task was unbearable (mentally and financially). I could name names, but the industry I'm familiar with where this has occured is very tight and unforgiving...
Re:Why use PostgreSQL instead of MySQL?: ACID by arkanes · 2002-02-07 03:27 · Score: 2

I completely agree. Pretty much my entire job revolves around sticking Intranet front ends on databases of various types, in Cold Fusion no less, and when I look back over mode I code, I see some of the dirtiest, hacky solutions I can imagine. I can't use any stored procs because they don't allow me access to the database, so I'm stuck with hacking stuff up on my end.
I'd like to rant for a moment: The MS Jet SQl engine sucks, and is the bane of me existence. Thank you.
Re:Why use PostgreSQL instead of MySQL?: ACID by Anonymous Coward · 2002-02-07 05:49 · Score: 0

This behaviour is by design. STFU.
Re:Why use PostgreSQL instead of MySQL?: ACID by Anonymous Coward · 2002-02-07 05:52 · Score: 0

and when I look back over mode I code

Uhm, English please?
Re:Why use PostgreSQL instead of MySQL?: ACID by Anonymous Coward · 2002-02-07 06:08 · Score: 0

2. MyISAM tables require a full lock for every INSERT or DELETE
Do not.
promptly every single one of the 30 or 40 MySQL processes began taking up at least 60 MB RAM
Hmm, I've got almost 90 processes taking up 181MB each! On a 512MB box! Could it be!?!? Threads? OMG! They're not processes, they're threads! I hope you understand how thread memory usage shows up on ps and top. Load is less than 2 (on a dual machine, ie, real load < 1). The only reason your machine collapsed is because it probably only had 64mb to begin with.
Re:Why use PostgreSQL instead of MySQL?: ACID by thing12 · 2002-02-07 09:07 · Score: 2

You might as well make it a perl subroutine, or hell, just use the new perl procedural language inside MySQL itself.
PostgreSQL has a much better perl based stored procedure language than mySQL has. Unlike the mySQL hack, the Pg implementation is a complete procedure language -- you can even use the normal DBI syntax to run queries against the database.
Re:Why use PostgreSQL instead of MySQL?: ACID by bovinewasteproduct · 2002-02-07 16:08 · Score: 1

Ok, one question. Can you do online backups yet with InnoDB or do you still have to shutdown the system?

This one was a show stopper along with no sub-selects...

BWP
Re:Why use PostgreSQL instead of MySQL?: ACID by the+way · 2002-02-07 16:18 · Score: 2

Ok, one question. Can you do online backups yet with InnoDB or do you still have to shutdown the system?You can buy an incremental hot backup module from InnoDB for EUR250/year, or use MySQL replication to keep a backup constantly up to date (you can then stop replication and take down the slave DB to do a binary backup as required).

Online Backups/High Availability by murphj · 2002-02-06 17:01 · Score: 3, Insightful

I didn't notice anything about online backups, point-in-time recovery, or standby databases. Is any of this possible on PostgreSQL yet? How about clustering/parallel server. Seems like these are important features to become an Oracle/SQL Server replacement.

--
SONY. Because caucasians are just too damn tall.

Re:Online Backups/High Availability by jslag · 2002-02-06 17:10 · Score: 1

...Seems like these are important features to become an Oracle/SQL Server replacement.

Yeah, and Postgresql doesn't require you to use a GUI to install it & administer it yet, either. Maybe it'll be more like the Big Boys when version 8.0 rolls around?
Re:Online Backups/High Availability by pthisis · 2002-02-06 18:18 · Score: 5, Informative

I didn't notice anything about online backups, point-in-time recovery, or standby database

Online backups: yes, for quite some time

point-in-time recovery: postgres uses WAL undo/redo logging, but I'm not sure what the state of rollback tools is at the moment.

Standby database: Assuming you mean Master/Slave replication, this is one of the major features planned for 7.3; 7.x has added a lot of the infrastructure needed for replication, and by 7.4 they hope to have multimaster replication (ie a fully distributed database).

SONY. Because caucasians are just too damn tall.

Crazy People. Hysterical movie.

Sumner

--
rage, rage against the dying of the light
Re:Online Backups/High Availability by aminorex · 2002-02-06 19:45 · Score: 2, Interesting

MySQL has master-slave replication.
In fact, you can make a trees of replication
(good for high transaction volumes with massive
redundancy) or even daisy-chain replication into
a ring (giving you master-....-master).

It's too bad that it takes about 2 years to
update the public perception when a product
transitions from toy to tool.
.

--
-I like my women like I like my tea: green-
Re:Online Backups/High Availability by TheFuzzy · 2002-02-06 19:46 · Score: 1

We've had online backups since version 6.5. In fact, backing up and restoring Postgres is easier than any other well-known DB except MySQL. Certainly it's about 5x faster than MS SQL Server.

Point-in-time recovery, standby databases, and replication are all related on the TODO list. Sadly, I think that these major features will wait until a commercial entity decides they're worth paying for. Unless you wanna volunteer?

As for clustering/parallel processing, may I point out that MS SQL Server doesn't have that, either? This is an Oracle/DB2-grade feature you're talking about, and there are still some reasons that Ellison and the boys get to charge $25,000 for their product.

-Josh
PostgreSQL Volunteer

P.S. How about we drop the Postgres vs. MySQL flamewars every time someone posts a database article? Frankly, as a PostgreSQL applications developer, it's a non-issue. MySQL is a good database for some things. PostgreSQL is a good database for most-but-not-all things. It's not a popularity contest, for crissakes! Grow up.
Re:Online Backups/High Availability by madprof · 2002-02-06 23:06 · Score: 1

> P.S. How about we drop the Postgres vs. MySQL
> flamewars every time someone posts a database
> article?

That wouldn't be as much fun. Sadly.
Hopefully one of the replication projects will mature into something to rival a commercial package. But that is a lot to hope for.
Re:Online Backups/High Availability by Peter+Harris · 2002-02-07 00:51 · Score: 1

Hey, you know what would be fun instead? A PostgreSQL / MS SQL Server flamewar, but with benchmarks.

--

-- What do you need?
-- Gnus. Lots of Gnus.
Re:Online Backups/High Availability by GooberToo · 2002-02-07 02:58 · Score: 2

Hi Josh.

I someone wanted to become active in contributing to this project, who would we need to contact? Is there a primary contact for each sub-project?
Re:Online Backups/High Availability by mesocyclone · 2002-02-07 03:59 · Score: 2

Point in time recovery is not yet available AFAIK. This is one critical feature that distinguishes commercial databases from Postgresql.

My company is using Postgresql in a traditional OLTP context, and we would very much like to have the point in time recovery. At some point it could make the difference between us continuing in the open source mode or switching to (ugh) Oracle!

I do understand that PIT recovery is being worked on. I wish I had time to help!

--
The only good weather is bad weather.
Re:Online Backups/High Availability by madprof · 2002-02-11 10:22 · Score: 1

Yes, then we could pull the wool over each other's eyes with an increased sense of superiority and twice the derision!
Damn, that's an inspired idea... :-)
Can I nominate Quake 3 performance whilst running large UPDATEs as a performance indicator please?
Re:Online Backups/High Availability by Peter+Harris · 2002-02-12 00:24 · Score: 1

Not geeky and pointless enough! How about implementing the quake engine in SQL? Although I think Postgres might have a tiny advantage there since it has a lot of built-in geometric types. :-)

--

-- What do you need?
-- Gnus. Lots of Gnus.

eecpg? by coyote-san · 2002-02-06 17:01 · Score: 2

What is it you're having problems with?

I've used ecpg (ESQL/C, similar to Oracle's Pro*C) since I started using postgresql years ago, and have had no problems at all accessing the data from C or C++ code.

If you're using java then this is even more of a no-brainer - just used the JDBC binding.

--
For every complex problem there is an answer that is clear, simple, and wrong. -- H L Mencken

Re:eecpg? by Trepalium · 2002-02-06 17:27 · Score: 1

I mean accessing a pgsql database features without a backend pgsql database server. Unless I'm completely misunderstanding, ecpg is simply a pre-processor to make using SQL queries to a backend SQL database easier. I'm talking about something closer to Sleepycat's Berkeley DB or the new features of MySQL 4.0, where the application actually calls the library (which is just the stripped down version of the server) to create the database, create the tables, and so on.
There are good reasons for putting data in a conventional server-based SQL database, but there's also good reasons not to. There are a number of cases where another application (and even the database server itself) has no business accessing or potentially modifying the data. It's for things like this that I would like to have such an embeded version. For example, if I'm making a mail server, or something, perhaps bad things can happen if the data is not updated in a particular way, I don't want just any old app making changes, obviously. It also helps reduce namespace conflicts between programs, and so on.

--
I used up all my sick days, so I'm calling in dead.
Re:eecpg? by Wdomburg · 2002-02-07 01:16 · Score: 2

Our of curiosity, is there some reason why you'd want PostgreSQL specifically for this task?

There are a wide variety of embedded database toolkits that implement RDBMS functionality without the requirement of running a server product.

The eldest example would likely be the classic DBM file, with is descendents NDBM and GDBM, though these are fairly simplictic.

Your next step up would likely be BDB, which, like DBM, uses its own API, but provides a much more richer set of functionality. The concurrent access, transaction, and recovery options are exceptional.

And finally, if you're looking specifically for a SQL interface, the SQLite package provides a fairly complete SQL92 implementation in its toolkit, including automic commit and rollback.
It also has a command-line "monitor" much like the pgsql or mysql commands for administration.

I'm not necessarily dismissing the concept of in-process access to PostgreSQL tables, but given the choices available already available for embedded databases, and the unique requirements in writing one (e.g. locking without a controlling process), I don't quite what the motivation would be.
Re:eecpg? by DrSkwid · 2002-02-07 02:23 · Score: 2

python has Gadfly, an in memory sql server

--
There are places where the networks are not touching,and there are places where they are-Boeing's Lori Gunter

Re:I like SQL by huddles · 2002-02-06 17:02 · Score: 0, Troll

Um, shouldn't the table alias go *before* the column? 'tard...

Postgres vs. MySQL by Merlin_ · 2002-02-06 17:02 · Score: 1, Funny

... and they're off!!!

--

Remembering your name in the morning is already a good start...

Re:Postgres vs. MySQL by Golias · 2002-02-07 06:06 · Score: 1

Comparing Postgres to MySQL is less like comparing vi to emacs, and more like comparing a full-sized John Deere tractor to a garden rake. Only a fool would plow a 200-acre farm with a rake, likewise you will regret it if you drive farming equipment through the wife's flower bed.
Postgres is rock solid, but notoriously slow. MySQL is wicked fast, but doesn't scale well.
Which should people use? Both. They are free, so it's not like you can only have one. Use PostgreSQL for your heavy lifting when you can't afford the Oracle license, use MySQL when speed is all that counts. Wasn't that easy? Now let's all get along.

--
Information wants to be anthropomorphized.
Re:Postgres vs. MySQL by tzanger · 2002-02-07 07:13 · Score: 2

Postgres is rock solid, but notoriously slow. MySQL is wicked fast, but doesn't scale well.

Oh puh-lease. That old "postgres is slow" arguement is ancient. While mysql may be faster for simple insert/selects, that's where it stops, and compared to today's postgres it's not that much faster. Update your FUD, please.
Re:Postgres vs. MySQL by Anonymous Coward · 2002-02-07 09:30 · Score: 0

I wish I could moderate this post, I'd rate it Funny.

But I do appreciate, the message is meant to be serious - just the stuff about the garden rake and plough up your wife's flower bed.

ROTFL.
Re:Postgres vs. MySQL by Golias · 2002-02-08 04:13 · Score: 2

compared to today's postgres it's not that much faster
So, you agree that postgres is slower, but it's FUD when I say it's slower. Whatever, dude.

--
Information wants to be anthropomorphized.
Re:Postgres vs. MySQL by tzanger · 2002-02-08 05:43 · Score: 2
So, you agree that postgres is slower, but it's FUD when I say it's slower. Whatever, dude.

Read your comment again:
- Postgres is rock solid, but notoriously slow.
"Nortoriously slow" is where I have the problem. It's not notoriously slow. MySQL is marginally faster than PostgreSQL on simple insert/select statements, but that's it. So yes, you are spreading fud, dude.
Re:Postgres vs. MySQL by Golias · 2002-02-08 06:30 · Score: 2

1. The acronym "FUD" does not mean "saying bad things about a program". FUD stands for "Fear, Uncertainty and Doubt". Spreading FUD would be saying something like "you better not have your company depend on Java, because Sun might be out of business in a few months." It's only FUD if you are trying to make people AFRAID of using a product. If you think that scaring people away from postgres was the main thrust of my post, you need to read it again. To say postgres is slower than MySQL is not spreading FUD, it's stating a fact. A fact that I am well aware of, because I use PostgreSQL 7.1 every damned day, both at work, and in my own home projects.
2. The word "notorious" does not mean extreme. It means "well noted". Since even you, who is obviously a passionate defender of all things postgres, acknowledge that MySQL is at least slightly faster, my statement was essentially correct.
Learn what words mean before you start shooting your mouth off.

--
Information wants to be anthropomorphized.
Re:Postgres vs. MySQL by tzanger · 2002-02-09 04:53 · Score: 2

<sigh>

The acronym "FUD" does not mean "saying bad things about a program". FUD stands for "Fear, Uncertainty and Doubt".

Almost correct. Taken purely as an acronym, that is what FUD means. However the definition has grown to include tactics including half-truths and downright wrong information. Your statements could have been taken as FUD as you were saying that PostgreSQL is notoriously slow. That's just like "Linux is is useless compared to Windows on the desktop," or "MySQL doesn't support transactions." These things may have been true in the past but are not now, so they are considered statements of FUD. Back to my particular statment: you're casting doubt over the performance of the system.

The word "notorious" does not mean extreme. It means "well noted".

That is exactly correct. Your well-noted observation that PostgreSQL is slow is dated and unsubstantiated. Even older versions of PostgreSQL fare much better than MySQL when you start doing more than one update to a table at a time or involve anything but trivial inserts and selects. Earlier versions also had synchronous disk access defaulted on which hurt performance signficantly. Both of these facts are well-noted as well, but you did not bring them up, which to me also helps the FUD claim: you're only telling half the story.

I'm not really a passionate defender of all things Postgres; I just hate seeing the same old tired and dated arguments brought up again and again as they simply don't apply anymore.

I know what the words mean; don't try to cast uncertainty and doubt over my English proficiency, please.
Re:Postgres vs. MySQL by Golias · 2002-02-09 21:12 · Score: 2

You said yourself that postgres is, in fact, slower than MySQL. There's a lot of good reasons why it's slower. An enterprise-level database needs certain features (which MySQL lacks) which will always cause a performance hit.
Even had you not conceded that point (which makes everything I said *fact*, rather than *FUD*), I don't really feel like I need you to tell me how spiffy postgres is, because, like I said, I use postgres all the time, and have compared its speed to enough other systems to know how well it performs.
What astonishes me most of all about all your whining about "FUD" was that, when I posted my original message, I actually thought it would be the MySQL fans that would be more likey to get pissed off at me (for calling MySQL a rake to potgres's tractor). The fact that you took that post, which basically said that both DBMS's have their uses, as a "FUD" attack on postgres leads me to the only possible conclusion, that any conversation with you must be like walking on egg shells, because you are too fucking touchy. Take a few breaths, re-read my original post, and you just might realize that it was posted by somebody with no agenda against your favorite program.

--
Information wants to be anthropomorphized.
Re:Postgres vs. MySQL by Golias · 2002-02-09 21:26 · Score: 1

Oh, by the way, thanks for posting your resume on your website. I now know for a fact that I have a shitload more RDBMS experience than you do, and no longer feel compelled to justify my observations to you. Have a nice day.

--
Information wants to be anthropomorphized.
Re:Postgres vs. MySQL by tzanger · 2002-02-10 07:09 · Score: 2

You said yourself that postgres is, in fact, slower than MySQL. There's a lot of good reasons why it's slower. An enterprise-level database needs certain features (which MySQL lacks) which will always cause a performance hit.

You're quite correct. However, and as I'm sure you're aware, PostgreSQL is oftentimes faster than MySQL when you're doing anything other than simple inserts and selects. If you'll read my original post again you'll see that I was upset when you didn't justify the "slower" than MySQL; you just blanket-statement said that it was slower, which is often taken as "always."

What I don't understand is why you think I'm the one walking on eggshells, or if I'm trying to convert you to PostgreSQL. All I was doing was calling you to task on your statement. You say you've got all this database experience so I'm sure that we're just arguing over semantics here. (by the way, I do thank you for keeping the argument civilized, it's rare on /.) MySQL is faster than PostgreSQL for the simple stuff. We both agree on that. PostgresSQL is taken more seriously than MySQL because of the features; I think we both agree with this too. And finally, PostgreSQL is faster than MySQL when you are doing any "real" database work (more than just simple inserts and selects on simple tables); I think we both agree on this as well.

As far as my resume goes: so what? I have enough relevant experience to discuss these matters and it seems that we're just arguing over nothing anyway; does a 30-year vertran have anything more important to say than a 3-year programmer? We're not talking about something where vast differences in experience make much difference, if at all.

Re:Backup Copy In Case (Don't Mod This Please) by dimator · 2002-02-06 17:04 · Score: 0, Flamebait

Judging from this post, and your attempted FP, I can conclude that you are a dumb fuck.

--
python -c "x='python -c %sx=%s; print x%%(chr(34),repr(x),chr(34))%s'; print x%(chr(34),repr(x),chr(34))"

Re:More on the Slashdot Janitors moderating by Anonymous Coward · 2002-02-06 17:05 · Score: 0

Any post which actually mentions Slashdot also gets modded down pretty fast, too. See the comment right below yours for reference.

Re:I like SQL by Anonymous Coward · 2002-02-06 17:05 · Score: 0

I like books.

[What I don't like are shitty IBM NetVista's]

Re:I like SQL by Anonymous Coward · 2002-02-06 17:09 · Score: 0

What kind of shitty SQL are you writing? You sound like a fucking cumdumpster. Is that what you are?!! ARE YOU A CUMDUMPSTER?!! IS CUM DUMPED INSIDE OF YOU?!! ANSWER ME MOTHERFUCKER!!

IBM NetVista's fucking suck balls.

Re:Substantial improvements and it's only .1 highe by abe+ferlman · 2002-02-06 17:10 · Score: 2

Interesting that their numbering is keeping pace with Redhat's numbering schema. Probably just a coincedence, but iirc Redhat is selling a version of PostGreSQL as the Redhat Database or somesuch.

It would be pretty funny if they started mimicking Microsoft instead: PostGreSQLXP, PostGreSQL98, PostgreSQL2000AdvancedServer, etc. But I digress.

--
microsoftword.mp3 - it doesn't care that they're not words...

Ain't that the truth by Anonymous Coward · 2002-02-06 17:10 · Score: 0

If CmdrTaco of Slashdot wants people to take his site more seriously and cut down on trolling, he should stop pissing off the people who are doing the trolling on Slashdot and abusing CmdrTaco.

The time is now 12:09:30 EST

Prunciation? by Anonymous Coward · 2002-02-06 17:12 · Score: 0

How does one prnounce Postgres or however it's spelt?

Re:Prunciation? by Anonymous Coward · 2002-02-06 17:14 · Score: 0

It's pronounced 'postgres'
Re:Prunciation? by Anonymous Coward · 2002-02-06 17:23 · Score: 0

There is a WAV/MP3 right on their main page... here is a link for those who are too lazy to look.
Re:Prunciation? by King+of+the+World · 2002-02-06 17:25 · Score: 0

Er, it's on the front page of their site.

--
--Giving to trolls for the benefit of us all

Re:Hey! by Anonymous Coward · 2002-02-06 17:16 · Score: 0

Maybe his skin is leathery?

Long time mysql user, postgresql newbie by gid · 2002-02-06 17:21 · Score: 2, Interesting

I've been using mysql for close to three years now, maybe around 6 months back I tried out postgresql, I switched a site or two over to using it. I did some lame inaccurtate benchmarking of a "typic page", pgsql was twice as slow at the time using v7.1, untweaked, but mysql was also untweaked.

Anyway's let me tell you, pgsql's user permissions still make my head swim, it's a nightmare. I mean, ok there's like how many different ways to authenticate a user, plain text password, crypted password, now md5, ident, local ident, kerberos, etc etc. Seriously, what's the "preferred" way to add a normal, non super user, only has select, insert, update, and delete access to a given database that can connect from the local machine, and remotely. Is this even possible? (yes I know you have to give it the -i option for network access)

I guess another kind of oddity about the pgsql is that out of the box, it only does ident type local socket authetication, no tcp/ip. You have to add all these weird rules to a config file to enable these mysterious, "accounts with passwords". Documentation inside the actual config files makes me dead btw.

I've looked forever, but I've yet to find a "mysql to postgresql" quick start guide. I know, rtfm, and figure it out, trust me I have, there's a boat load postgresql docs all split over 10 different manuals it seems.

Also, would it be darn nice to include a start/stop script that reads only config files and can be linked from /etc/rc2.d/ etc. pg_ctl doesn't exactly cut it. I wrote one myself that's basically a wrapper for pg_ctl, but it's a major hack, I can clean it up and post it if anyone's intersted.

Overall a good db, it definitely takes way longer than mysql to set up and understand, but the added features make it worthwhile. Even if you don't use the triggers, out the box transactions, and sub query support right away, you'll be glad when do finally want to use them to help you out with data integrity. Sorry, I'm rambling, I have no real point, this is more of a "this is my experience with this thing post". Maybe it will be of some use to someone, and hopefully I'll get an intelligent post or two setting me straight. :)

Re:Long time mysql user, postgresql newbie by Moosbert · 2002-02-06 17:37 · Score: 5, Informative

Anyway's let me tell you, pgsql's user permissions still make my head swim, it's a nightmare. I mean, ok there's like how many different ways to authenticate a user, plain text password, crypted password, now md5, ident, local ident, kerberos, etc etc.

Options are somtimes considered to be a good thing.

Seriously, what's the "preferred" way to add a normal, non super user, only has select, insert, update, and delete access to a given database that can connect from the local machine, and remotely. Is this even possible?

Add something like this to your pg_hba.conf:

local sameuser trust

host sameuser 127.0.0.1 255.255.255.255 trust

I guess another kind of oddity about the pgsql is that out of the box, it only does ident type local socket authetication, no tcp/ip.

We like the default setup to be reasonably secure.

I've looked forever, but I've yet to find a "mysql to postgresql" quick start guide.

try here

Also, would it be darn nice to include a start/stop script that reads only config files and can be linked from /etc/rc2.d/ etc.

It's in contrib/start-scripts. Or you might as well download the RPMs.
Re:Long time mysql user, postgresql newbie by aminorex · 2002-02-06 19:50 · Score: 1

> Options are somtimes considered to be a good thing.

But sometimes they are *NOT* a good thing.
Specifically, when it comes to providing umpteen
different access mechanisms (read "remote exploit
opportunities").
.

--
-I like my women like I like my tea: green-
Re:Long time mysql user, postgresql newbie by Aapje · 2002-02-07 00:44 · Score: 1

Options are sometimes considered to be a good thing.

You are clearly not a minimalist. Many see the advantages of having one clear way to do something, instead of many options, all with their advantages, disadvantages and different abilities. A single, consistent design gives you clean and logical interfaces, which are fairly easy to use. Especially for authentication it may be better to have a slighty less capable interface, instead of an interface that people can't comprehend and thus misuse.

Of course you may sometimes need something more potent, this should be achieved by extending the basic design ('advanced options') or by adding a separate more complex interface. This should be a superset of the simpler interface. Users can thus be pointed to the simple interface by default and can migrate to the more complex one if their application becomes more complex.

--

The Drowned and the Saved - Primo Levi
Re:Long time mysql user, postgresql newbie by Anonymous Coward · 2002-02-07 06:18 · Score: 0

try here [techdocs.postgresql.org]
OMG, where'd you find that link!?!?! It's not anywhere on postgres' site, believe me, I've looked all over the place (including Google) for any USEFUL docs on configuring PG. The default config is just useless. We've got a dynamic site using PG, and with just 2 concurrent web users the db collapsed (on a dual p3-600 512mb scsi). 2 users, looking at a single page! It's trivial to get Mysql to use all available system memory to cache the hell out of the db (or lock it down to 200mb just to be on the safe side), why is it such a pain in the ass to get PG to use more than 4mb? Even once you figure out to work some voodoo on the shared memory settings, the performance still sucks total ass! When real users hit the site the load on the machine went over 100. WTF?!?
Re:Long time mysql user, postgresql newbie by slamb · 2002-02-07 08:33 · Score: 3, Informative

Seriously, what's the "preferred" way to add a normal, non super user, only has select, insert, update, and delete access to a given database that can connect from the local machine, and remotely. Is this even possible?

Add something like this to your pg_hba.conf:
local sameuser trust host sameuser 127.0.0.1 255.255.255.255 trust

That's not authentication! "trust" just allows logins, period. Try "psql -U postgres" as anyone on that machine. You'll instantly be logged in as the superuser.
Something like this works fairly well on Postgresql 7.1:
host all 127.0.0.1 255.255.255.255 ident sameuser host all 0.0.0.0 0.0.0.0 password
Then enable TCP/IP connections ("tcpip_socket = true" in postgresql.conf)
Very important: make sure your ident server is trustworthy. Many ident servers have an option to allow a user to fake identification. Turn it off.
Also, the config I posted there will let any user connect to any database. That's the simplest, but not the most secure. The "sameuser" in the database field won't be enough to let the superuser connect to databases. You might add a seperate line for that with an ident map containing only postgres (the file would have only the words "postgres postgres" in it, on one line). And then "all" in the database field with that map. I.e., "host all 127.0.0.1 255.255.255.255 ident postgres"
For remote connections, just make sure they have a password in the database:
create user slamb with password '12345'; alter user bob with password 'newpassword';
There's no authentication method here specified for UNIX domain sockets, so they just don't work. You'll need to set the PGHOSTNAME="localhost" environmental variable for stuff to authenticate correctly. I did this because pgsql 7.1 did not support ident on UNIX domain sockets. pgsql 7.2 now does, on certain platforms. (Just replace "host <db> <ip> <netmask> ident <map>" with "local <db> ident <map>")
pgsql 7.2 adds pam support. If your UNIX and PostgreSQL usernames correspond, it should work.
pgsql 7.2 also adds support of encrypted passwords. There's an option for storing password encrypted in the database and an option for challenge-based encryption. I think these methods are incompatible - good challenge-based encryption requires the password be stored in plaintext on the server.
There has been Kerberos auth for some time. I'm trying to switch over to this now, as I'm setting up Kerberos on my network. It's a more complicated system to set up correctly, though. Get something else working first.
Official docs are here
Re:Long time mysql user, postgresql newbie by gid · 2002-02-07 08:48 · Score: 1

Thanks for the response, you cleared some things up for me, but I'm still kind of shakey on the users.

Now looking at the documentation in pg_hba.conf (heh):
# DBNAME can be:
# o the name of a PostgreSQL database
# o "all" to indicate all databases
# o "sameuser" to allow access only to databases with the same
# name as the connecting user

The user will have access to the database with the same name as the username, but that user has TOTAL access to that database, right? Is there way I can ONLY give a user SELECT, INSERT, UPDATE, and maybe DELETE. (such as a web user)

It seems to me there must be a way, I just haven't found one yet...

After reading and playing a bit more, it looks like the md5 password method is the preffered password method, although at first glance it looks like when I add md5 password lines to my pg_hba.conf, I had a weird annomally.

I installed webpg (new phpmyadmin verion) and normal users could log in with passwords, but if you typed in the password in wrong, the user was still allowed in! Assumingly because the web server is really a local user can therefore can be trusted by the default install.

Anyway, so I take out the trust lines from the my pg_hba.conf but now the postgres user can't log in because it doesn't have a password.... *sigh* These kinds of things are the problems I see with multiple log in methods, since the default login method doesn't use passwords, it's not suitable for remote access.

I'll continue to play... I'm probably still missunderstanding something though.

Re:Substantial improvements and it's only .1 highe by thing12 · 2002-02-06 17:21 · Score: 2, Funny

Actually before they called it PostgreSQL they called it Postgres95. :-)

Re:Substantial improvements and it's only .1 highe by Moosbert · 2002-02-06 17:24 · Score: 1

Actually, the truth is that Red Hat has been planning this database thing for a long time and they have made sure that by the time they announced it their version number would match up with PostgreSQL's.

Re:I have only one feature request for PostgreSQL. by jcoy42 · 2002-02-06 17:31 · Score: 3, Insightful

I only have one real gripe about PostgreSQL- I hate the upgrade path.

Having to dump the database to disk and re-import is a bad thing IMO. Having to add a switch to keep integrety constraints is a very odd thing for a database (shouldn't the default be to *keep* integrety constraints?)

A separate program to preserve LOBs I can rationalize (it's a lot of generally unneeded overhead since few people use LOBs).

It would really be nice if someone would write some wrapper programs to check for foreign keys and LOBs, then wrap the pg_dumpall & pg_dump commands with the appropriate options into one set of programs.

--
Never trust an atom. They make up everything.

while that's true by Trepidity · 2002-02-06 17:33 · Score: 2

I find it a bit odd to claim non-ACID aren't taken seriously while in the same sentence mentioning that Yahoo uses a non-ACID database system. Obviously somebody there took it seriously enough to roll out...

--
10 PRINT CHR$(205.5+RND(1)); : GOTO 10

Re:while that's true by Sivar · 2002-02-06 18:14 · Score: 2, Interesting

Fair enough.

However, Yahoo is not a typical business. As the Yahoo article says, "Finance managed its database needs through homegrown flat files and Berkeley DB databases. But those solutions proved to be inflexible and not scalable enough for their needs..."
Homebrew flat files will be wooped by any decent DB server, especially a blazing fast one like MySQL, any day of the week. Yahoo also tends to embrace open source and new technologies more readily. Most of their servers, for example, run FreeBSD and have since the beginning.
This is a slightly bad example considering that FreeBSD is unquestionably a product that you can trust your mission critical data to, but it illustrates to a degree the type of company that Yahoo is.

I, myself, would trust MySQL with anything that didn't matter to someone else. In the end--it's politics, and "nobody ever got fired for using an ACID compliant database." ;-)

--
Computer Science is no more about computers than astronomy is about telescopes. --E. W. Dijkstra
Re:while that's true by Anonymous Coward · 2002-02-07 05:57 · Score: 0

Hmm, that's funny, over 10000 users trust all their email to MySQL every day over here, and have done so for over 2 years, without a single problem. Maybe you should get some better hardware and a database design course.

Why do Slashdot moderators suck?: Crack by Anonymous Coward · 2002-02-06 17:54 · Score: 0

The title pretty much says it all.

Whew, that was close by flacco · 2002-02-06 17:59 · Score: 4, Funny

I'm sure I'm not the only slash-dotter who was on the verge of exceeding the 4 billion transaction limit on their pgsql-based Anime fan fiction submission website.

--
pr0n - keeping monitor glass spotless since 1981.

not a postgres guru by Anonymous Coward · 2002-02-06 18:00 · Score: 0

i started using postgres back when mysql was free for personal use, but putting it on a production box cost some serious bucks. setting up postgres was about like doing apache/php. the docs were good, and following the directions got everything running. then i wanted to use an app that required mysql... i never did get it to work, and ended up writing my own app for postgres. btw, get bruce momjian's postgres book, it works for newbies and seasoned database hackers.

as for the benchmark times - lightweight use, mysql wins. heavy-duty hammer-the-database apps, postgres wins. it scales a lot better and keeps on chugging long after mysql crashes and dies. if you don't need the extra goodies and your app won't strain the db, stick with what you know. if things are going to get real busy, or if you need the added features, go with postgres, it'll be worth the learning curve.

Re:Hurt Me! by Anonymous Coward · 2002-02-06 18:05 · Score: 0

It's good advertisement for my site. I get hundreds and hundreds of hits per day solely because of my .sig.

Go figure.

Re:Backup Copy In Case (Don't Mod This Please) by megyhazy · 2002-02-06 18:11 · Score: 0, Offtopic

i was reading that 'java sucks' link. i think your difficulty is that you have no idea what you are doing and/or do not understand oop at all. you were using a reference to a string, you were comparing memory locations. why did you expect the result to return true? and no, it is not different in C/C++, if you had used a reference to a String object in that language it would have done the same thing.

Look, assface: by Anonymous Coward · 2002-02-06 18:11 · Score: 0

This is _evidence_ that _these folks_ _actually give a shit_ about the product they're releasing. This is _a good thing_. Fuckwit.

The O'Reilly book explains this excellently by Anonymous Coward · 2002-02-06 18:14 · Score: 1, Interesting

And you can even read it online. I recommend spending the money to buy it hardcopy. This book is *excellent*.

Like you, I couldn't figure out how to configure users. This book explains it so it makes sense. I was even able to make SSL authentication work! I can't recommend it highly enough.

Here is a link to the online copy of Practical PostgreSQL. Enjoy!

Updateable ResultSets in JDBC Driver by coughlin · 2002-02-06 18:22 · Score: 1

Something that still hasn't made it into the PostgreSQL jdbc driver is updateable ResultSets. This would be an extremely useful and timesaving feature, and I am kind of suprised that it hasn't been implemented. Does anyone know what the status of this is?

Re:Updateable ResultSets in JDBC Driver by Twisted+Mind · 2002-02-06 22:34 · Score: 1

Maybe you already do that, but (with another JDBC driver that doesn't support that) I use PreparedStatements as a substititute for that

I create a the PreparedStatements for all my updates on the start of my application, which consist of something like "UPDATE WHERE =?"

--
(-% TwistedMind %-)
Re:Updateable ResultSets in JDBC Driver by Anonymous Coward · 2002-02-06 23:27 · Score: 0

The difference is that your prepared statement could end sending lots of seperate updates back to the DB, where an updatable recordset would send all of the updates back in one batch.

Re:Hurt Me! by Anonymous Coward · 2002-02-06 18:28 · Score: 0

and he's gay...

http://www.EricKrout.com/modules.php?op=modload& na me=Downloads&file=index&req=viewdownload&cid=6

7.3 wishlist? by mlinksva · 2002-02-06 18:48 · Score: 1

The TODO list says "A dash (-) marks changes that will appear in the upcoming 7.3 release" but I don't see any dashes.

Re:7.3 wishlist? by Moosbert · 2002-02-06 18:57 · Score: 1

Well, we've just released 7.2 so you can't expect that anything is already done for 7.3.
Re:7.3 wishlist? by mlinksva · 2002-02-06 20:04 · Score: 1

Of course I don't expect anything is done for 7.3! I was just looking for a wishlist/roadmap of things that might get done for 7.3.
Re:7.3 wishlist? by Moosbert · 2002-02-07 04:03 · Score: 1

Fair enough. But note that the dash in the TODO list marks changes that will appear in the next release, not changes that might appear, should appear, or are hoped to appear.

If you want to know what's cooking, browse the mailing list archives of the developers' lists.

Replication? by msaavedra · 2002-02-06 18:49 · Score: 2

Congratulations to the pgsql developers on the new release. It looks like the only showstopper now for heavy duty use is lack of replication. I know that there are developers working on this. Does anyone know what sort of progress is being made?

--
"Any fool can make a rule, and any fool will mind it."
--Henry David Thoreau

Re:Funny? by Bodrius · 2002-02-06 18:53 · Score: 1, Offtopic

+4:Funny?

Isn't that stretching a bit the definition of "funny"? The comment is valid enough, even if its anecdotical and not the most serious, mission-critical effect.

Maybe "amusing" would be a better label, or maybe "ironic". Maybe we should have those moderation options. After all, "Insightful" and "Interesting" are not orthogonal qualities either, are they?.

--
Freedom is the freedom to say 2+2=4, everything else follows...

Postgres load balancing? by icejai · 2002-02-06 18:53 · Score: 1

Hope this isn't too offtopic.

Does postgres have any plans for database replication?
Like... Master-master (oracle), master-slave setups? (mysql)

Re:Postgres load balancing? by Anonymous Coward · 2002-02-06 21:41 · Score: 0

Jups. There are already replication patches, but 7.3 should go support this and 7.4 should also support multiple masters.

PostgreSQL Books by LarryRiedel · 2002-02-06 19:35 · Score: 3, Informative

There are a few decent books about PostgreSQL out there now. It is so much nicer than a few years ago.

Practical PostgreSQL. I think this one just came out as a bound book. I just got it a couple days ago and it is pretty good. It is also online.

Postgresql : Developer's Handbook. I (as a developer) like this one best of all that are out now.

PostgreSQL Essential Reference. This one is pretty good, but I would not say it is essential. :-)

Beginning Databases with PostgreSQL. This is one of those Wrox books which is about 10000 pages, including 80% of what I want to know and 2000% of what I don't.

There other others, but I think they are weaker. I was disappointed with the one just called PostgreSQL.

The all-important question: upgrading. by StrawberryFrog · 2002-02-06 19:36 · Score: 2

We have a live system running on postgres 7.1 Now what about upgrading?

When should we do it? Should we wait a couple of weeks for any possible patch/point release, or is this one granite-rock-solid?

Why should we do it? The current DB is working just fine. Are there any compelling new features?

How do we do it? Are the database file binary compatible? Do we need to export and reimport data? Are the RPMs of postgres 7.2 out?

--

My Karma: ran over your Dogma
StrawberryFrog

Re:The all-important question: upgrading. by dietz · 2002-02-07 00:30 · Score: 1

All information about imporant changes you will need to make to your application are documented at the top of the README (under the "Migration" section).

There are potentially some changes you will need to make depending on which of the database features you are using.

To upgrade you will need to pg_dump your database and reload it with 'psql -f'. Again, check the readme.
Re:The all-important question: upgrading. by dark_panda · 2002-02-07 05:53 · Score: 2

I've been using a 7.2 beta on a production website for a while now. I know it's not generally considered kosher to run a beta of a new release on a production website, but it isn't exactly a high-traffic site, and it's been running without a hitch since I started it up a month ago. I'd estimate it does about a few thousand transactions a day or so. (Not true begin-commit-rollback-transactions, just updates/inserts and selects.)

A few good features...

- vacuum doesn't need to lock things up all the time, which is definitely nice.

- the speed increase is noticible, especially on a dual P3 SMP.

- there have been a few changes to a number of the datatypes, like the increased resolution on timestamps (now recorded with milliseconds), changes to char and varchar types (they now reject strings longer than their limits -- i.e. a varchar(30) won't accept 31 characters by cutting off the excess).

- you can supply passwords via md5 hashes rather than plaintext or using crypt, which is definitely good, especially for connections made over the wire. You can also supply the passwords via UNIX domain sockets now, which for some reason you couldn't before. (You had to start postmaster with -i and connect via an INET port for that sort of authenication. Ew.)

The databases are not binary compatible, so upgrading from 7.1 to 7.2 will require a pg_dump-from-7.1-initdb-and-dump-into-7.2. Life is rough.

I've been working with it quite a bit and I haven't had any real problems throughout beta testing and the RCs. The "production" site will probably be upgraded later today, or tomorrow.

I have no idea about RPMs, since I always compile from source, but check out freshrpms or rpmfind.net or something.

J

So when should we expect... by doorbot.com · 2002-02-06 19:58 · Score: 1, Offtopic

Postgres XP
Postgres .NET
Postgres X
Postgres ]|[
Postgres Championship
Postgres G5
Postgres Ti

Re:So when should we expect... by Smoking · 2002-02-06 21:28 · Score: 3, Interesting

Postgres Ti:
Done...

I've got 7.1 running on my Titanium Powerbook...
there are really nice MacOS X packages of Postgres at Marc Liyanage's home page

I also take this occasion to thank him for the nice MacOS X packages he's put together...

Quentin
Re:So when should we expect... by liyanage · 2002-02-07 06:39 · Score: 1

You're welcome :-)

A new Mac OS X package of version 7.2 is now online.
Re:So when should we expect... by Smoking · 2002-02-07 09:01 · Score: 1

Maaaaan

I didn't even get the time to ask for it!

thanx...

Quentin
Re:So when should we expect... by Anonymous Coward · 2002-02-08 03:05 · Score: 0

new Mac OS X package of version 7.2 is now online

Marc, you rock! Not only do you build the app for us and write great instructions, but you even host 5MB downloads for free. Thanks so much.

Test it out first by TheLink · 2002-02-06 20:00 · Score: 2

There are some potential issues - the formatting and behaviour of some outputs and functions have been changed slightly.

Your applications might break and need modification.

So test it out first - non live.

Even if there are no known issues you should test it out first, you might be unluckily the first one to know you know :).

--

Too many replies beneath your current threshold

Windows version? by pointwood · 2002-02-06 21:13 · Score: 3

Yes, I know it should be avoided, but I'll ask anyway since sometimes you have no other choice :(

I'm currently using MySQL on a Win2k server and it actually runs pretty okay and is very easy to install. What about PostgreSQL? Last I looked at it - it was a lot more difficult to get running on Windows. Has that changed or are PostgreSQL still more or less *nix only?

Re:Windows version? by Pengo · 2002-02-06 21:30 · Score: 2

if what your using works, why would you want to change it? And yes, pgsql works under windows using Cygwin.
Re:Windows version? by sc00ch · 2002-02-06 22:21 · Score: 2, Insightful

Maybe he wants to learn about pgsql and alternatives to mysql? That would be healthy!

I myself with no experience of cygwin found it a pain in the ass to get postgres working on windows. There was no quick and easy howto on the postgres site at the time, im still unsure if a quick step by step guide exists today.

I'm sure this puts a lot of people off from initially trying out postgres. Mysql makes it a lot easier to check out on windows, simply run the installation program and its up and running.

Re: plication by Smoking · 2002-02-06 21:14 · Score: 3, Informative

I've recently set up a master-master replication environnement on Oracle 9i and I did some research to check if it was possible with postgres.

In fact there are many solutions available (check techdocs.postgresql.org for a list...)

The most advanced guys on the subject seem to be the swiss engineering school in Zürich. Here is a list of their publications.
They seem to have developped a replication scheme (Postgres-R) where they have better than linear performance improvement when they add new masters...Quite impressive

Quentin

OK, smartypants... by aquarian · 2002-02-06 21:15 · Score: 1

...tell us exactly how MySQL fails the ACID test, and what that means in the real world.

Re:I have only one feature request for PostgreSQL. by aquarian · 2002-02-06 21:18 · Score: 1

A Windows version! We all can't run 'nix all the time! I'm hacking and praying with MySQL for Windows clients, unless they're willing to pop for Oracle...

Re:I have only one feature request for PostgreSQL. by Pengo · 2002-02-06 21:19 · Score: 2

Ask and you recieve.

Cygwin has a port of Postgres that runs fine. Performance isn't as good as the Linux version, but not bad.

Give it a shot, it comes with the distro, you don't even have to compile it.

Proper metadata by seasunset · 2002-02-06 21:37 · Score: 1

I have been using PostgreSQL for five years and last week I have stumbled on a show stopper:

I am doing a tool that relies heavily on metadata, namely I need to get foreign key info (via JDBC).

PgSQL 7.1 does foreign keys as triggers, and has no info in the metadata tables regarding foreign keys.

I have tried also other OS databases, MySQL and HSQL, but it was even worse.

Does anyone know if this version improves on metadata?

Also the PgSQL JDBC driver is incomplete (it has to be has at least it can't implement proper metadata retrieval), but the answers from the driver for non implemented methods are pretty lame (returning nulls instead of throwing exceptions). Has this changed? I think I will volunteer for this as the changes are easy.

Re:Proper metadata by rmello · 2002-02-07 04:05 · Score: 1

The 7.2 JDBC driver is much better in that regard, and it is backwards compatible with PG 7.1, 7.0 and I think 6.5.
Re:Proper metadata by rmello · 2002-02-07 04:07 · Score: 1

Sorry, forgot the URL: http://jdbc.postgresql.org/

Re:A message from Metrollica: I LIKE BIG BUTTS! by Anonymous Coward · 2002-02-06 22:20 · Score: 0

I have carefully read your post, but I still don't understand what it has to say about PostgreSQL version 7.2, final release. Please clarify.

Re:Substantial improvements and it's only .1 highe by T-Punkt · 2002-02-06 22:24 · Score: 1

> Actually before they called it PostgreSQL they called it Postgres95. :-)

[-1 Troll]

Wow, we see some uninformed moderator here in action, who seems not to believe that the name really was Postgres95 for a brief time and too lazy to check it out.

Abstract from http://www.ca.postgresql.org/docs/devhistory.html
1977-1985 Ingres
1986-1994 Postgres
1994-1995 Postgres95
1996-today PostgreSQL

Re:I have only one feature request for PostgreSQL. by Anonymous Coward · 2002-02-06 22:28 · Score: 0

I don't think it's what you mean, but I've successfully run postgres on a linux-reflashed Compaq iPAQ H3760... :-)

(Unfortunately, the company still went with Wince + SQL Server CE)

Embedded DB/merge replication by Anonymous Coward · 2002-02-06 22:33 · Score: 0

Improtant : growth area - merge replication, suitable for disconnected operation, with a cut-down DB for PDAs/tablet pads. This is important for asset/stock management applications, which lots of businesses are now implementing. Basically, you give a guy in the field a PDA, he records asset data, and it's sent back to a central data repository every so often.

This is a major growth area. Oracle has 9i Lite, Microsoft has SQL Server CE, Open Source has - not much...

Re:I have only one feature request for PostgreSQL. by chriskl · 2002-02-06 23:34 · Score: 3, Informative

I'm a postgres developer and I really have no idea what you mean here!

Postgres always keeps its integrity constraints, including when you dump and restore. It's done this, as far as I am aware, since at least 7.0.

LOBs are no longer a problem, since 7.1 supported unlimited row length with binary or ascii data - just use 'bytea' or 'text' fields...

Chris

Re:I have only one feature request for PostgreSQL. by iomud · 2002-02-06 23:58 · Score: 2

My only feature request would be replication facilities.

PostgreSQL Documentation by Confuse+Ed · 2002-02-07 00:26 · Score: 2, Informative

PostgreSQL also comes with some great documentation (probably the best I've yet seen with any software I've used (the only contender I can think of offhand might be the Devpac assembler on the AtariST many years ago))
(This is also of course online, e.g. the 7.1 version is up at http://www.ca.postgresql.org/users-lounge/docs/7.1 /postgres/ , or interactivly the interactive version http://www.postgresql.org/idocs/)

So how do we encourage other projects to develop such documentation, which I assume is very time consuming and difficult, as well as being for very little reward (How often do we see posts appreciating new software features compared to those acknowleding the associated documentation?)

Re:I have only one feature request for PostgreSQL. by sql*kitten · 2002-02-07 00:52 · Score: 2

. Having to add a switch to keep integrety constraints is a very odd thing for a database (shouldn't the default be to *keep* integrety constraints?)

If you are certain that there are no constraint violations, because you've just exported from one database, then disabling them would make importing into a new database much faster. It's common practice, you just re-enable them before you make the new database available for transactions.

Re:I have only one feature request for PostgreSQL. by jcoy42 · 2002-02-07 03:11 · Score: 1

I'm a postgres developer and I really have no idea what you mean here

Specifically, I am refering to OIDs.

If an OID is like a ROWID in oracle, then I recant this point whole heartedly.

I would still like a more direct upgrade path tho- I really don't like the idea of doing a full dump to disk to reload to the new database.

--
Never trust an atom. They make up everything.

Thanks for the notice! by alexhmit01 · 2002-02-07 03:40 · Score: 3, Interesting

We run PostgreSQL on a dual-processor Linux box to feed our OpenBSD web servers. We got a HUGE speed gain from the OpenBSD -> Linux change (even when we ran it on a slower machine while testing it), and any SMP gains will be helpful.

When we did OpenBSD we had to be VERY careful not to do more queries than necessary (including some complicated joins and then having PHP parse the results). With Linux as the database server, I feel that I can throw hardware at it (including moving to Solaris if need be) and optimize the queries a bit less to abstract the programming.

SMP improvement is important, as the next step up for us is a Quad-Xeon processor, then Sun Hardware. (PostgreSQL seemed to run best on Linux and Solaris from the old website)...

It's such a shame that they never figured out the PostgreSQL support model. I would have happily paid for some support, but it always seemed easier to get the OpenBSD port or the Redhat RPM than pay for their CDs. They never included much beyond installation support. I knew how to install it, having some support (not the mailling list) for some of my optimization questions would have saved days and been worth a support contract.

Alex

Question by technomancerX · 2002-02-07 03:46 · Score: 2

One feature I find really useful (I do lots of web programming) in MySQL that I couldn't find an equivalent of in Postgres is FULLTEXT indexes... The only thing I could find was a hack in the contrib repository...

Is there any plan to add this or some equivalent? Is it already there and I just missed it? (it's one of the reason I haven't done versions of my open source releases for Postgres)

--
.technomancer

Re:Question by Moosbert · 2002-02-07 04:08 · Score: 1

One feature I find really useful (I do lots of web programming) in MySQL that I couldn't find an equivalent of in Postgres is FULLTEXT indexes... The only thing I could find was a hack in the contrib repository...

Please explain why you think the solution in the contrib repository is a "hack".

And alternative solution, which is thought of by many as more sophisticated, is OpenFTS.
Re:Question by Anonymous Coward · 2002-02-07 06:29 · Score: 0

Because OpenFTS a specialty product. For search engines and like applications. I just want a simple db, with a table or two with some text fields that I may want searchable sometime. I don't want a friggin search engine. It's overkill, it's not suitable, I don't need it or want it.

As for the contrib patch, it's a hack, always has been and always will be until it's rolled into the distro. By definition.
Re:Question by chriskl · 2002-02-07 13:59 · Score: 1

Hrm. The contrib/fulltextindex in postgres is designed exactly to do what you want - simple indexing of some text fields.

Read the README.fti.

I have submitted improvements to it for 7.2 that let it index multiple fields per table, with better docs. We use it at work all over the place.

Chris

moderator point example not so good by brlewis · 2002-02-07 04:04 · Score: 2

MySQL doesn't have begin/commit, but I believe individual updates are atomic. You could do an update like this:

update posts set mod=mod-1 where pid=($2) and mod >= -1;

Then look at the row count and only deduct a moderator point if there's at least one row updated.

I agree that begin/commit is important; it's just that you can do without it in this instance so long as an individual update is atomic.

Re:moderator point example not so good by Anonymous Coward · 2002-02-07 05:55 · Score: 0

Don't you mean:
update posts set mod=mod-1 where pid=($2) and mod > -1;
Re:moderator point example not so good by brlewis · 2002-02-07 07:08 · Score: 1

Yes, that's what I meant. I don't do C anymore, but I still get bit by off-by-one errors. :-)

You can always check this comparison. by Secure42 · 2002-02-07 04:05 · Score: 1

Thank you for the informations it was really usefull.
You can always check this comparison for more information:
http://www.geocities.com/mailsoftware42/db/

Re:Question: OpenFTS for fulltext by drowsy · 2002-02-07 04:08 · Score: 1

Check out OpenFTS

openfts.sourceforge.net

Do'h! OpenFTS for fulltext by drowsy · 2002-02-07 04:10 · Score: 1

That's the last time i fail to preview!

openfts.sourceforge.net

Minor annoyance with the release (thus far) by denisb · 2002-02-07 04:30 · Score: 1

PostgreSQL is excellent, I use it, administer it, and generally have a Good Feeling (tm) towards it.
However, at our site uptime is king. We initially installed 7.1.2 and used it until 7.1.3 came along, easy upgrade indeed.

Along the way, much of what is fixed / improved in 7.2.0 has been the source of some annoyances.

I would now love to upgrade and run 7.2.0. However reading through the INSTALL, I quickly read "Only upgrading from pre 7.2 requires an initdb". I read the whole procedure and yes it is doable to perform an upgrade through a dump & restore.

What I am a little annoyed about though is that the 7.2.0 release does not include a better solution for upgrades from 7.1.X.

I understand there has to be fundamental changes to the structure to facilitate these improvements, however what I am looking for is a less downtime demanding solution / set of scripts, to actually perform the upgrade..

Oh well, I'll probably just have to hold my breath a little longer, and wait till 7.2.2 for the upgrade ;-)

- d9s

--
life+universe+everything=42

Re:Minor annoyance with the release (thus far) by bmomjian · 2002-02-07 06:48 · Score: 1

There is a /contrib/pg_upgrade in 7.2 that I wrote. It needs more testing to be bullet-proof but feel free to give it a try.
Re:Minor annoyance with the release (thus far) by rtaylor · 2002-02-07 15:26 · Score: 2

Easy way to approach this is to install the 7.2 installation in a seperate path from the 7.1 installation.

If done right (ie --prefix=/usr/local/pgsql72) you can easily compile, install and have everything ready to go.

Initdb to the /usr/local/pgsql72/data location, then fire up 7.2 on a seperate port (5433 rather than 5432 -- may wish to move the socket too). Setup & test our security aspects.

Turn down the webserver to the 7.1 system. Close any applications.

Last but not least:

/usr/local/pgsql72/bin/pg_dumpall -h localhost | /usr/local/pgsql72/bin/psql -h localhost -p 5432

Of course, this takes some time and requires double the disk space. While this is running upgrade any changes to your software that you wish to apply (datatype changes, features being outdated, etc.).

As soon as it's done, shot down both the 71 and 72 databases. Reset the port and socket to the default location for 72 and re-fire them back up. Alternately you can have your application look in a different spot for a connection.

Fire back up the webserver, or re-enable connections to the system. A 4GB database using the above method with a relatively simple structure (100 tables, 50 or so functions, etc) took about 10 minutes of actual downtime.

Of course, several test runs should go before hand. I heavily suggest you enable md5 style passwords at the same time and update the users password storage mechanism -- ODBC doesn't support these yet that I know of.

--
Rod Taylor
Re:Minor annoyance with the release (thus far) by denisb · 2002-02-07 23:41 · Score: 1

Thanks for the tips, also thanks to Bruce for reminding me to check out the contrib section ..
I probably will postpone the update for another .1 or .2 anyway, just to be on the extremely safe side.

-d9s

--
life+universe+everything=42

Drop column? by Yoda2 · 2002-02-07 04:31 · Score: 1

Anyone know if you can drop a column in 7.2 without creating a new table with (n-1) columns and renaming?

Re:Drop column? by Lazy+Jones · 2002-02-07 05:35 · Score: 2

No, you can't ... I guess it's not on their priority list ;-)

--
"I love my job, but I hate talking to people like you" (Freddie Mercury)

Re:I have only one feature request for PostgreSQL. by Sloppy · 2002-02-07 05:00 · Score: 2

Eek, overload. Client = application that connects to database. Client = person that is paying you to do stuff. Hmm..

If you're talking about Windows applications that talk to the database, PostgreSQL already has what you need. Just run PostgreSQL on the Unix box and get the ODBC driver (yes, there is one) for the Windows client machines, and you're all set. It works.

If you're talking about Windows people, well, think of this as an infiltration opportunity. Just sell 'em a dedicated database server machine, and they don't need to know what OS it's running. :-)

--
As copyright owner of this comment, I authorize everyone to defeat any technological measure which limits access to it.

Wish List by bwt · 2002-02-07 05:10 · Score: 3, Interesting

Here's my Postgres wish list:

1. Point in time recovery
2. Reconstruct SQL from write ahead log
3. Function based indexes with SQL rewrite
4. Materialized views with SQL rewrite
5. Analogue to Oracle's v$sqlarea
6. Wait statistics
7. Tablespaces
8. Inline views (from clause subselects)
9. Parallel query capability
10. Partioned tables
11. Bitmap indexes
12. IO monitoring (read/write per object)
13. Dynamic sort and hash area allocation
14. Detailed SQL tracing (rows per plan step)
15. Multiplexed WAL writes
16. SQL optimizer hints

Re:Thanks for the notice! - support note by stompro · 2002-02-07 05:15 · Score: 1

As I think others have noted, Redhat is suppling a db product that is named redhatdb, but is basically PostgreSQL. They describe it as

Utilizing the award-winning Red Hat Linux operating system, Red Hat Database is the cost-effective, fully supported, scalable, data management solution for mid-sized businesses and enterprise workgroups and departments. Powered by the robust and proven relational database management system, PostgreSQL, Red Hat Database is the intelligent alternative to overly complex and costly enterprise systems.

So there are options for support. And RedHat does have a team working on Postgresql, Last I heard is that they are working on adding db replication.(but my info is probably out of date)

Josh

Ohh lord, I'm defending Redhat...

I noticed Redhat's by alexhmit01 · 2002-02-07 05:22 · Score: 2

I was referring to Great Bridge? One of the core teams tried to start a PostgreSQL business and failled.

I know about redhatdb. For $2500, I wasn't really sure what I got. It may become to solution in the future however.

Our Red Hat 6.2 boxes would get rooted if we did the install before lunch and came back to fix them after lunch.

Now they sit inside a nice safe OpenBSD network. Red Hat Database looks promising, it's just sad that Redhat stole their business because they couldn't get their shit together.

Alex

PostgreSQL issues by augustz · 2002-02-07 05:56 · Score: 2

Things on my wishlist/irklist are:

Alter table improvements. Changing schemas now with triggers etc is a nightmare. How are others doing this?

I can't figure out how to avoid "" around "every" "single" "identifier" which is annoying.

Continued increased speed and performance. We can do database backed sessions in a mysql damn fast, and it has been tricky to get postgresql to perform as well.

Reclaim space without forcing table locks. The fact is I'm always amazed folks can even lock their tables for an hour. How do you run 24x7 systems with that. The new VAC is a great step.

Improve the website. It is ugly and hard to navigate. Just copy php.net/mysql.com or some other reasonable devloper portal.

It runs under Cygwin!!! by bartwol · 2002-02-07 15:20 · Score: 2, Informative

I haven't tested it under load, but I've installed and successfully tested PostgreSQL 7.1 under Cygwin on Windows 2000.
<bart

Sorta Like "MkExt2IntoReiserFS" by Christopher+B.+Brown · 2002-02-07 16:04 · Score: 2

It sure would be nice to be able to do major hackery with the data formats, and have an easy in-place upgrade; unfortunately, this comes with two big costs:

It takes a lot of work to do this, which will seem pointless to many when "dump/re-import" is already there and works;
More importantly, debugging this, across umpteen platforms, possible variations on how folks compiled it, and moves between minor version numbers that may differ more or less than you'd expect is really a lot of work.

The unavailability of a "Ext2-to-ReiserFS" translator, or Ext2-to-JFS or Ext2-to-XFS occurs for much the same reason, albeit with the further challenge that the "upgrade" would somewhat more resemble a "MySQL-format-to-PostgreSQL-format" conversion :-).

--
If you're not part of the solution, you're part of the precipitate.

You are correct in referring to Great Bridge by jocknerd · 2002-02-13 02:39 · Score: 0

I worked there. We were in constant disagreement with management all the time. Their business plan changed monthly. Here's some of the things that went on:

1. They didn't want to be a 'software' company. Only support. Yet, our focus in the office was always about getting the next 'packaged release' out the door.

2. We only were to provide support for PostgreSQL. Not for converting the customer's existing system over to it. Biggest mistake I thought we made. The only way this could work was for a new company without a previous database system.

3. Red Hat didn't steal our business. We actually had no business until July of last year. In fact we didn't have a VP of Sales until last February and it took him a few months to get his staff together. July was the first month we sold anything substantial and August was much better. We were on the verge of making something special when the investors got cold feet because of the stock market. (So much for their word on "We're not in this for the shortterm. We have a long-term stake in this company.")

I still get a sick feeling over this. This was my 'dream' job. A high-tech company in my hometown. I still miss the place.

Such a fucking shame... by alexhmit01 · 2002-02-13 03:44 · Score: 2

I couldn't figure out what your support policy was. There were no support contracts or anything else. It seemed like our only option was to pay $500 for installation help. It was rediculous.

Look, I'm a small guy, but I was having pain in the ass optimization problems getting OpenBSD and PostgreSQL to run at a reasonable speed. However, I could get it running. I am 100% certain that if I called support with the $500 CD, I'd be told that I was SOL.

I can't believe there wasn't a migration program. You're right, that was the biggest mistake. A better solution would be if you could cater to dot-coms that did a 2-3 year Oracle license that was coming to the end and needed cash. They could choose to pay for more Oracle or choose to pay your conversion fee. That would get you somewhere.

I can sympathize with management not knowing how to make things fly. However, if you won't charge for software updates (can't on BSD code without making it non-BSD) then worrying primarily about the next software release is silly.

Such a shame. PostgreSQL is a great product, we power all our clients on it. I would have been more comfortable with some support arrangement, but nobody seemed terribly interested in selling one to me. We've gone so long without one, I would only need one as an insurance policy of sorts, not really for the kinds of help I would like.

Tis a shame, better (and bound) documentation and other niceties would have been nice.

Well, look on the bright side, a good job at a company with hot tech is a good resume line as long as the tech stays hot. I leveraged a summer job at Citrix into the training to get my MCSE (this was 4 years ago) and numerous jobs as an NT Admin whereever a Citrix server was involved.

The ACID factor and Relations by fm6 · 2002-02-20 13:19 · Score: 2

It occurs to me that an anti-MySQL rant is not terribly ontopic. I won't say "Karma Whoring", but that might be the POV of the moderators that gave you three Overrateds and a Troll. Or maybe the Slashdot editors are justed peeved at your implied criticism of their design choices...

Anyway, you do have a good point and you've neatly summarized one requirement of a "real" database. I would just caution not to base all comparisons between two DBMSs on ACID support -- especially if one of them is MySQL. After all, there are a lot of specific things that distinguish a serious DBMS from a toy.

Not that MySQL has a lot of them. The only one that comes to mind is the SQL interpreter. A lot more is missing, and a lot of it is stuff most programmers will miss a lot more than transactional integrity.

The biggest omission, to my mind, is support for relational queries.

"BZZZT!" I hear you saying. "What do you mean MySQL doesn't support relational queries? You can do a join with select/where! And..."

Well, you can specify a join in MySQL. (Just as you can if your "database" is a simple collection of text files.) But joins aren't very useful if you can't do nontrivial things with them. And you can't, because MySQL takes a terrible performance hit if your query involves more than one index. That's why Slashdot now numbers posts sequentially. Discouraging the first-posters was merely a side effect.

Slashdot Mirror

PostgreSQL v7.2 Final Release

258 comments