Folks,

I need someone to prepare a standard response for me to send out to inquiries 
on this topic.  I get them a lot.

What I'd like is a factual, non-perjorative list of the things which 
PostgreSQL and the PostgreSQL project have that MySQL does not, with a little 
bit of explanation by each.   Where links can be provided, please do so.  
Examples:

PROCEDURES:  Postgres supports stored procedures (as functions) allowing 
programming in the database for the many tasks which are far more efficient, 
consistent, and secure done there.   Procedures may be written in any of nine 
different languages, currently, with two more in development.  MySQL does not 
support procedures at all.

TRANSACTIONS:  blah, blah, blah.   MySQL has just begun offering transactions 
this year, and their solution is largely untested, slow, and suffers from 
complications with the many different "table types".  PostgreSQL's MVCC 
transaction support, on the other hand, has been in use in production in 
numerous environments for over six years.

Can anyone do this?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Wed, 20 Aug 2003 08:39:28 -0700
Josh Berkus <josh@agliodbs.com>
Need concrete "Why Postgres not MySQL" bullet list
In article <200308200839.28230.josh@agliodbs.com>,
Josh Berkus <josh@agliodbs.com> writes:

> PROCEDURES:  Postgres supports stored procedures (as functions) allowing 
> programming in the database for the many tasks which are far more efficient, 
> consistent, and secure done there.   Procedures may be written in any of nine 
> different languages, currently, with two more in development.  MySQL does not 
> support procedures at all.

 From the MySQL manual:
   * With UDF (user-defined functions) one can extend MySQL Server with
     both normal SQL functions and aggregates, but this is not yet as
     easy or as flexible as in PostgreSQL.

> TRANSACTIONS:  blah, blah, blah.   MySQL has just begun offering transactions 
> this year, and their solution is largely untested, slow...

InnoDB transactions in MySQL are pretty robust and fast.  However,
this affects only INSERT/UPDATE/DELETE - not CREATE TABLE etc.

> and suffers from 
> complications with the many different "table types".

True.  Transactions break unless all tables used are InnoDB.


20 Aug 2003 19:20:27 +0200
Harald Fuchs <nospam@sap.com>
Re: Need concrete "Why Postgres not MySQL" bullet list
On Wed, 2003-08-20 at 13:20, Harald Fuchs wrote:
> > and suffers from 
> > complications with the many different "table types".
> 
> True.  Transactions break unless all tables used are InnoDB.
> 

Actually the problem is worse than transactions breaking, the problem is
that they don't break and just silently fail you in some circumstances. 

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


20 Aug 2003 14:57:10 -0400
Robert Treat <xzilla@users.sourceforge.net>
Re: Need concrete "Why Postgres not MySQL" bullet
On Wednesday 20 August 2003 17:39, Josh Berkus wrote:
> Folks,
>
> I need someone to prepare a standard response for me to send out to
> inquiries on this topic.  I get them a lot.
>
> What I'd like is a factual, non-perjorative list of the things which 
> PostgreSQL and the PostgreSQL project have that MySQL does not,

Oh dear, this excludes my usual one-item "things to think about
when considering what database to use":

* PROPER USAGE OF NULL

mysql> select * from ai_test where id is null;
+----+-------+
| id | txt   |
+----+-------+
|  1 | hello |
+----+-------+
1 row in set (0.00 sec)

;-). I digress. Off the top of my head, in no particular order:

* VIEWS
VIEWs are an important element of efficient professional database design,
pushing repetitive, often duplicated queries into the backend, 
increasing the flexibility of an application and reducing the risk of errors. 
They also enable the encapsulation of table structure, which may change
with application development, behind a consistent interface.

- MySQL has no support for views, but promises them in a future version,
for which no target production date exists:
http://www.mysql.com/doc/en/ANSI_diff_Views.html

* TRIGGERS
Triggers enable predefined actions to be carried out before
or after inserts, deletes and updates are performed on tables.
This enables automation of many tasks in the database, for
example logging specific events such as changes to a table
holding accounting data, or checking and modifying a column
prior to insertion or update.

- MySQL does not support triggers. Implementation is planned:
http://www.mysql.com/doc/en/ANSI_diff_Triggers.html

* SEQUENCES
Often it is useful or necessary to generate a unique number
for a row of data, for example for providing a primary key
ID. Many databases including PostgreSQL provide sequences - 
functions for automatically generating unique numbers 
in predefined increments.

- MySQL provides only a very primitive sequence generator, AUTO_INCREMENT,
whose behaviour is difficult to modify. It is not possible to explicitly
set the current value of a specific AUTO_INCREMENT sequence or an
incrementation other than 1.
- AUTO_INCREMENT is implemented as SELECT MAX(col) FROM table
(see: http://www.mysql.com/doc/en/InnoDB_auto-increment_column.html )
which makes concurrent transactions prone to deadlocks
- The use of the NULL value to provoke insertion of the next sequence value
means it is impossible to have a sequence on a NULLable column in MySQL.
- there can be only one AUTO_INCREMENT column per table

* RULES, TYPES, DOMAINS

* PROCEDURES
in a variety of languages...

* DDL
- Data definition language (table creation statements etc.) in MySQL
are not transaction based and cannot be rolled back.
- Some parts of a table definition, although syntactically corrent,
maybe ignored without warning. This applies particularly to syntax
such as 
CREATE TABLE my_table (
 id INT NOT NULL PRIMARY KEY,
 fkey_id INT NOT NULL REFERENCES other_table(id)
) TYPE=InnoDB

See:
http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html

- MySQL may, at its discretion, silently alter column specifications. See:
  http://www.mysql.com/doc/en/Silent_column_changes.html
- MySQL only permits constants as default values not functions or expressions.
See: http://www.mysql.com/doc/en/CREATE_TABLE.html
This makes it impossible to provide default values like this:
CREATE TABLE deftest (
  id INT, 
  date_at_creation TEXT DEFAULT 'The date is '|| to_char('dd-mm-yyyy',  
                                                          now())
);

* GENUINE REFERENTIAL INTEGRITY
An essential part of the relational model is the ability to
create foreign keys, which define relationships between tables.
Naturally only values which exist in the referenced table can
be used in a foreign key column.
PostgreSQL has provided integrated foreign key support
since (find out when).

- In MySQL foreign keys are an "optional extra" and are only
available when the InnoDB table type is specified. 
- Not all MySQL server installations are configured to provide
  InnoDB support, e.g. ISP-run servers.
- despite the implementation of foreign keys with InnoDB tables,
MySQL does not provide robust referential integrity. 
Under certain circumstances MySQL will insert into or permit the
presence of values in a foreign key which are not present in the table
referred to. For example, if a foreign key column can contain null
values, and the column's definition is changed to NOT NULL,
MySQL will insert 0 (zero) into the previously NULL columns,
even if the column referred to in a different table does not contain
0.
- No ON UPDATE ... support

* RIGOROUS FAILURE TESTING
When developing a database, and in day-to-day operation, it
is essential that erroneous statements fail with a specific
warning to prevent violations of data integrity.

- in many cases where a statement would be expected to
fail, MySQL will silently insert default values.
For example, when (mistakenly) inserting an integer
1 into an ENUM field, MySQL will insert a string
containing '0' rather than raise an error.


* DICTIONARY BASED FULL-TEXT INDEXING
Although SQL provides several methods of searching for patterns
in text fields (LIKE, regexes), these do not provide sufficient functionality
for more complex searches, e.g. as used by search engines.

- MySQL provides a builtin index type FULLTEXT, which allows keyword
searches. In contrast to PostgreSQL's tsearch2 functionality, this
does not provide for advanced natural language based searches using
features such as dictionary lookups and stemming.

* LICENSING
- MySQL is available as "open source", but depending on useage
not always with an open source license. MySQL's licencing
conditions are subject to change.
- PostgreSQL is available under a BSD-style licence to all
users whether commercial or private.


I can take the above and any further additions and flesh it out
into a more readable list (if noone else wants to). I have a
project at the moment which will probably be marketed as
a MySQL-compatible application ("it's what the users have"),
though I am prototyping it in PostgreSQL, and I'm collecting
a lot of interesting insights...

Ian Barwick
barwick@gmx.net





Thu, 21 Aug 2003 00:22:28 +0200
Ian Barwick <barwick@gmx.net>
Re: Need concrete "Why Postgres not MySQL" bullet list
Ian,

do you use any specific tools to prototype applications designed for 
PostgreSQL?

Anastasios

> I can take the above and any further additions and flesh it out
> into a more readable list (if noone else wants to). I have a
> project at the moment which will probably be marketed as
> a MySQL-compatible application ("it's what the users have"),
> though I am prototyping it in PostgreSQL, and I'm collecting
> a lot of interesting insights...
> 
> Ian Barwick
> barwick@gmx.net


Thu, 21 Aug 2003 00:44:42 +0200
Anastasios Hatzis <ahatzis@ithcorp.com>
Re: Need concrete "Why Postgres not MySQL" bullet
> Oh dear, this excludes my usual one-item "things to think about
> when considering what database to use":
> 
> * PROPER USAGE OF NULL
> 
> mysql> select * from ai_test where id is null;
> +----+-------+
> | id | txt   |
> +----+-------+
> |  1 | hello |
> +----+-------+
> 1 row in set (0.00 sec)
> 
> ;-). I digress. Off the top of my head, in no particular order:

You're not trying hard enough:

mysql> create table test3 (a date);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test3 values (-1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test3 values ('1996-02-31');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test3 values ('1996-67-31');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test3;
+------------+
| a          |
+------------+
| 0000-00-00 |
| 1996-02-31 |
| 0000-00-00 |
+------------+
3 rows in set (0.00 sec)

I find that even funnier...

Chris


Thu, 21 Aug 2003 09:42:45 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Need concrete "Why Postgres not MySQL" bullet list
On 21 Aug 2003 at 0:22, Ian Barwick wrote:
> * DDL
> - Data definition language (table creation statements etc.) in MySQL
> are not transaction based and cannot be rolled back.

Just wondering, what other databases has transactable DDLs? oracle seems to 
have autonomous transactions which is arthogonal.

If we are going to compare it, we are going to need it against other databases 
as well.

Personally I find transactable DDL's a big plus of postgresql. It allows real 
funcky application design at times..:-)

Bye
 Shridhar

--
drug, n:	A substance that, injected into a rat, produces a scientific paper.


Thu, 21 Aug 2003 14:45:03 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: Need concrete "Why Postgres not MySQL" bullet list
Shridhar Daithankar wrote:

>On 21 Aug 2003 at 0:22, Ian Barwick wrote:
>  
>
>>* DDL
>>- Data definition language (table creation statements etc.) in MySQL
>>are not transaction based and cannot be rolled back.
>>    
>>
>
>Just wondering, what other databases has transactable DDLs? oracle seems to 
>have autonomous transactions which is arthogonal.
>
M$ SQL2000 has (and previous versions had too, I believe)

Regards,
Andreas



Thu, 21 Aug 2003 12:30:58 +0200
Andreas Pflug <pgadmin@pse-consulting.de>
Re: [HACKERS] Need concrete "Why Postgres not MySQL"
On Thu, 21 Aug 2003 14:45:03 +0530, "Shridhar Daithankar"
<shridhar_daithankar@persistent.co.in> wrote:
>Just wondering, what other databases has transactable DDLs?

Firebird.

Servus
 Manfred

Thu, 21 Aug 2003 15:05:52 +0200
Manfred Koizar <mkoi-pg@aon.at>
Re: [HACKERS] Need concrete "Why Postgres not MySQL" bullet list
Andreas Pflug <pgadmin@pse-consulting.de> writes:

> Shridhar Daithankar wrote:
> 
> >On 21 Aug 2003 at 0:22, Ian Barwick wrote:
> >
> >>* DDL
> >>- Data definition language (table creation statements etc.) in MySQL
> >>are not transaction based and cannot be rolled back.
> >
> > Just wondering, what other databases has transactable DDLs? oracle seems to
> > have autonomous transactions which is arthogonal.
> >
> M$ SQL2000 has (and previous versions had too, I believe)

In Oracle DDL (including truncate!) was special and wasn't in a transaction.
I always just assumed that was just the way it had to be.

-- 
greg


21 Aug 2003 09:21:01 -0400
Greg Stark <gsstark@mit.edu>
Re: [pgsql-advocacy] Need concrete "Why Postgres not MySQL"
On 21 Aug 2003 at 9:21, Greg Stark wrote:

> Andreas Pflug <pgadmin@pse-consulting.de> writes:
> 
> > Shridhar Daithankar wrote:
> > 
> > >On 21 Aug 2003 at 0:22, Ian Barwick wrote:
> > >
> > >>* DDL
> > >>- Data definition language (table creation statements etc.) in MySQL
> > >>are not transaction based and cannot be rolled back.
> > >
> > > Just wondering, what other databases has transactable DDLs? oracle seems to
> > > have autonomous transactions which is arthogonal.
> > >
> > M$ SQL2000 has (and previous versions had too, I believe)

Any pointers to documentation?

Bye
 Shridhar

--
divorce, n:	A change of wife.


Thu, 21 Aug 2003 19:48:28 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: [pgsql-advocacy] Need concrete "Why Postgres not MySQL"
On 21 Aug 2003 at 9:21, Greg Stark wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
> > Shridhar Daithankar wrote:
> > > Just wondering, what other databases has transactable DDLs? oracle seems to
> > > have autonomous transactions which is arthogonal.
> > >
> > M$ SQL2000 has (and previous versions had too, I believe)
> 
> In Oracle DDL (including truncate!) was special and wasn't in a transaction.
> I always just assumed that was just the way it had to be.

That is the autonomous transaction. Right now I am looking at 
interbase/firebird documentation. It looks like it has automous DDL transaction 
as well..

Bye
 Shridhar

--
Collaboration, n.:	A literary partnership based on the false assumption that 
the	other fellow can spell.


Thu, 21 Aug 2003 19:48:29 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: [pgsql-advocacy] Need concrete "Why Postgres not MySQL"
Shridhar Daithankar wrote:

>On 21 Aug 2003 at 9:21, Greg Stark wrote:
>
>  
>
>>Andreas Pflug <pgadmin@pse-consulting.de> writes:
>>
>>    
>>
>>>Shridhar Daithankar wrote:
>>>
>>>      
>>>
>>>>On 21 Aug 2003 at 0:22, Ian Barwick wrote:
>>>>
>>>>        
>>>>
>>>>>* DDL
>>>>>- Data definition language (table creation statements etc.) in MySQL
>>>>>are not transaction based and cannot be rolled back.
>>>>>          
>>>>>
>>>>Just wondering, what other databases has transactable DDLs? oracle seems to
>>>>have autonomous transactions which is arthogonal.
>>>>
>>>>        
>>>>
>>>M$ SQL2000 has (and previous versions had too, I believe)
>>>      
>>>
>
>Any pointers to documentation?
>  
>
No, just looked at the doc and didn't find anything, it's assumed 
implicitely at some locations though.
DDL statement will create a Schema Modification lock (Sch-M), i.e. DDL 
statements pending in a transaction will lock a table exclusively. After 
commit or rollback, schema modification is committed or undone as 
expected, I verified this.

Regards,
Andreas



Thu, 21 Aug 2003 18:04:56 +0200
Andreas Pflug <pgadmin@pse-consulting.de>
Re: [pgsql-advocacy] Need concrete "Why Postgres not MySQL"
On 20 Aug 2003, Harald Fuchs wrote:

> In article <200308200839.28230.josh@agliodbs.com>,
> Josh Berkus <josh@agliodbs.com> writes:
> 
> > PROCEDURES:  Postgres supports stored procedures (as functions) allowing 
> > programming in the database for the many tasks which are far more efficient, 
> > consistent, and secure done there.   Procedures may be written in any of nine 
> > different languages, currently, with two more in development.  MySQL does not 
> > support procedures at all.
> 
> >From the MySQL manual:
>    * With UDF (user-defined functions) one can extend MySQL Server with
>      both normal SQL functions and aggregates, but this is not yet as
>      easy or as flexible as in PostgreSQL.
> 
> > TRANSACTIONS:  blah, blah, blah.   MySQL has just begun offering transactions 
> > this year, and their solution is largely untested, slow...
> 
> InnoDB transactions in MySQL are pretty robust and fast.  However,
> this affects only INSERT/UPDATE/DELETE - not CREATE TABLE etc.

Well, I wouldn't say that they're that robust.  Try this:

create innodb table 
begin transaction;
insert 1,000,000 rows;
rollback;

wait for years for the rollback to finish.

 From the MySQL manual:

http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#Innodb_tuning

QUOTE

8:  # Beware of big rollbacks of mass inserts: InnoDB uses the insert 
buffer to save disk I/O in inserts, but in a corresponding rollback no 
such mechanism is used. A disk-bound rollback can take 30 times the time 
of the corresponding insert. Killing the database process will not help 
because the rollback will start again at the database startup. The only 
way to get rid of a runaway rollback is to increase the buffer pool so 
that the rollback becomes CPU-bound and runs fast, or delete the whole 
InnoDB database.

ENDQUOTE

It's obvious that innodb transactions aren't meant to handle large data 
sets and rollback well, and the compromise here, like in all of MySQL, 
tends towards "hoping for the best" and benchmarking that particular 
aspect.

> > and suffers from 
> > complications with the many different "table types".
> 
> True.  Transactions break unless all tables used are InnoDB.

And, more importantly, they break silently, or you find out too late (i.e. 
oh by the way, some of those rows couldn't be rolled back...)


Thu, 21 Aug 2003 11:57:55 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: Need concrete "Why Postgres not MySQL" bullet
Ian's list is excellent.

I would like to add to the point about pl languages.

Use the right tool for the job:  
Procedure may be written in tcl, plpgsql, perl,
python, R (statistics) so you can use the right tool
for the job at hand as well as leverage your
existing knowledge of a given scripting language.

It would help to solidify our specific examples
of failures if the version and platform of mysql
is included.  Clarifications with postgresql
functionality working correctly may be appropriate
in some cases (also include version & platform).

At oscon, the mysql guys were fighting "old" rumors.
As they glob on functionality to try to catch up
to 1990 technology, they will assert that "we have
transactions now!", etc.  

By including the version and platform *and* sticking
only to the facts, we can forstall a few flames.

elein

On Wed, Aug 20, 2003 at 08:39:28AM -0700, Josh Berkus wrote:
> Folks,
> 
> I need someone to prepare a standard response for me to send out to inquiries 
> on this topic.  I get them a lot.
> 
> What I'd like is a factual, non-perjorative list of the things which 
> PostgreSQL and the PostgreSQL project have that MySQL does not, with a little 
> bit of explanation by each.   Where links can be provided, please do so.  
> Examples:
> 
> PROCEDURES:  Postgres supports stored procedures (as functions) allowing 
> programming in the database for the many tasks which are far more efficient, 
> consistent, and secure done there.   Procedures may be written in any of nine 
> different languages, currently, with two more in development.  MySQL does not 
> support procedures at all.
> 
> TRANSACTIONS:  blah, blah, blah.   MySQL has just begun offering transactions 
> this year, and their solution is largely untested, slow, and suffers from 
> complications with the many different "table types".  PostgreSQL's MVCC 
> transaction support, on the other hand, has been in use in production in 
> numerous environments for over six years.
> 
> Can anyone do this?
> 
> -- 
> Josh Berkus
> Aglio Database Solutions
> San Francisco
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
> 

Thu, 21 Aug 2003 11:16:48 -0700
elein <elein@varlena.com>
Re: Need concrete "Why Postgres not MySQL" bullet list
On Thu, 21 Aug 2003 15:05:52 +0200, I wrote:
>>Just wondering, what other databases has transactable DDLs?
>
>Firebird.

Stop!  I withdraw that statement.  I must have mis-read some feature
list :-(

Tests with InterBase 6 showed that you can change metadata within a
transaction, but when you ROLLBACK, metadata changes persist.

Servus
 Manfred

Thu, 21 Aug 2003 21:30:27 +0200
Manfred Koizar <mkoi-pg@aon.at>
Re: [GENERAL] [HACKERS] Need concrete "Why Postgres not MySQL" bullet
	list
On Thursday 21 August 2003 21:30, Manfred Koizar wrote:
> On Thu, 21 Aug 2003 15:05:52 +0200, I wrote:
> >>Just wondering, what other databases has transactable DDLs?
> >
> >Firebird.
>
> Stop!  I withdraw that statement.  I must have mis-read some feature
> list :-(
>
> Tests with InterBase 6 showed that you can change metadata within a
> transaction, but when you ROLLBACK, metadata changes persist.

Aha. I was just about to ask about that, because I was experimenting
with a 1.5 beta version without success. Doesn't seem to work there
(though as I have little experience and virtually no docs I might
be missing something).


Ian Barwick
barwick@gmx.net

Thu, 21 Aug 2003 22:19:57 +0200
Ian Barwick <barwick@gmx.net>
Re: [GENERAL] [HACKERS] Need concrete "Why Postgres not MySQL" bullet
	list
On Thursday 21 August 2003 11:15, Shridhar Daithankar wrote:
> On 21 Aug 2003 at 0:22, Ian Barwick wrote:
> > * DDL
> > - Data definition language (table creation statements etc.) in MySQL
> > are not transaction based and cannot be rolled back.
>
> Just wondering, what other databases has transactable DDLs? oracle seems to
> have autonomous transactions which is arthogonal.

DB2 8.1 seems to support transaction-capable DDL. At least, a rollback
following a CREATE TABLE causes the table to disappear. Haven't gone
into it in any depth.


Ian Barwick
barwick@gmx.net




Thu, 21 Aug 2003 22:28:52 +0200
Ian Barwick <barwick@gmx.net>
Re: [GENERAL] Need concrete "Why Postgres not MySQL" bullet list
On Thursday 21 August 2003 00:44, Anastasios Hatzis wrote:
> Ian,
>
> do you use any specific tools to prototype applications designed for
> PostgreSQL?

 'Fraid not. Apart from a pen and a piece of paper ;-).

I find Postgres very "ergonomical" to develop with, particularly
in the earlier stages of an application: transaction-capable
DDL makes design changes easy to test without risk of 
messing up the database; strong referential integrity 
helps develop robust applications (having the app fail
on the slightest hint of bad data makes bugs in the app
much easier to find)  and psql with its tab-completion
and readline support has gone a long way to saving
me from carpal tunnel syndrome.


Ian Barwick
barwick@gmx.net



Thu, 21 Aug 2003 22:43:40 +0200
Ian Barwick <barwick@gmx.net>
Re: Need concrete "Why Postgres not MySQL" bullet
On Thursday 21 August 2003 20:16, elein wrote:
> Ian's list is excellent.
>
> I would like to add to the point about pl languages.
>
> Use the right tool for the job:
> Procedure may be written in tcl, plpgsql, perl,
> python, R (statistics) so you can use the right tool
> for the job at hand as well as leverage your
> existing knowledge of a given scripting language.

Good point. I will try and get a working draft
together by the start of next week.

> It would help to solidify our specific examples
> of failures if the version and platform of mysql
> is included. 

Yup. I have compiled a long list of behavioural
oddities for my own reference; I've researched
many of these in the MySQL documentation (to make
sure I'm not seeing things), and an astounding number
are actually documented and show no signs of
going away, even in the 4.x series. I will install a 4.1
alpha (?) version just to be on the safe side, as this is
being touted as "the will-MySQL-make-it-into-the
enterprise-league version" (i.e. the version with sub-selects).

> Clarifications with postgresql
> functionality working correctly may be appropriate
> in some cases (also include version & platform).

Here we need to emphasize the progress made in 7.3 / 7.4,
I get the impression a lot of people still see Postgres as
the "database without the DROP COLUMN functionality"...


Ian Barwick
barwick@gmx.net


Thu, 21 Aug 2003 23:40:49 +0200
Ian Barwick <barwick@gmx.net>
Re: Need concrete "Why Postgres not MySQL" bullet list
On Thu, 2003-08-21 at 14:30, Manfred Koizar wrote:
> On Thu, 21 Aug 2003 15:05:52 +0200, I wrote:
> >>Just wondering, what other databases has transactable DDLs?
> >
> >Firebird.

Rdb/VMS, but your pockets had better be deep...

-- 
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"they love our milk and honey, but preach about another way of living"
Merle Haggard, "The Fighting Side Of Me"


Thu, 21 Aug 2003 19:10:41 -0500
Ron Johnson <ron.l.johnson@cox.net>
Re: [HACKERS] [pgsql-advocacy] Need concrete "Why

Hi all!

Can someone explain me why is it usefull if the table created in
transaction disapears on rollback?
Anyway the progress db supports it, at least the version 9.
The other question: why is mysql enemy? Isn`t it just another RDBMS?

Thanks,
Laszlo

On Thu, 21 Aug 2003, Ian Barwick wrote:

> On Thursday 21 August 2003 11:15, Shridhar Daithankar wrote:
> > On 21 Aug 2003 at 0:22, Ian Barwick wrote:
> > > * DDL
> > > - Data definition language (table creation statements etc.) in MySQL
> > > are not transaction based and cannot be rolled back.
> >
> > Just wondering, what other databases has transactable DDLs? oracle seems to
> > have autonomous transactions which is arthogonal.
> 
> DB2 8.1 seems to support transaction-capable DDL. At least, a rollback
> following a CREATE TABLE causes the table to disappear. Haven't gone
> into it in any depth.
> 
> 
> Ian Barwick
> barwick@gmx.net
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 


Fri, 22 Aug 2003 08:30:24 +0200 (CEST)
Hornyak Laszlo <kocka@tigrasoft.hu>
Re: [GENERAL] Need concrete "Why Postgres not MySQL"
> Can someone explain me why is it usefull if the table created in
> transaction disapears on rollback?

Because then you can write big scripts like we do at work to install a new
feature in the live database.  Such a script might create two new tables,
add columns to existing tables, drop and recreate some foreign keys, etc.
What you do is to test it you go 'begin;' and then execute the script.  If
it all worked, commit, else rollback and fix the script.  Repeat until you
get it right.

Chris


Fri, 22 Aug 2003 14:40:32 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [GENERAL] Need concrete "Why Postgres not MySQL"
On 21 Aug 2003 at 21:30, Manfred Koizar wrote:

> On Thu, 21 Aug 2003 15:05:52 +0200, I wrote:
> >>Just wondering, what other databases has transactable DDLs?
> >
> >Firebird.
> 
> Stop!  I withdraw that statement.  I must have mis-read some feature
> list :-(
> 
> Tests with InterBase 6 showed that you can change metadata within a
> transaction, but when you ROLLBACK, metadata changes persist.

Well, isql documentation mentions that DDLs don't go to database unless you 
commit and autoddl parameter defaults to true.

Looks like there definition of transactable does not include a rollback case. 
Oops!

BTW any comments on storing an entire database in single file? I don't trust 
any file system for performance and data integrity if I have single 100GB file. 
I would rather have multiple of them..

Bye
 Shridhar

--
Moore's Constant:	Everybody sets out to do something, and everybody	does 
something, but no one does what he sets out to do.


Fri, 22 Aug 2003 12:19:25 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: [GENERAL] [HACKERS] Need concrete "Why Postgres not MySQL" bullet
	list
On 22 Aug 2003 at 8:30, Hornyak Laszlo wrote:
> Can someone explain me why is it usefull if the table created in
> transaction disapears on rollback?

Imagine you are trying to duplicate a table but succeed halfway only?

More importantly all catalog changes are transaction safe in postgresql. Not 
only tables, but indexes, views, functions, triggers, rules and schemas are 
transaction safe as well. IMO that's a big feature list..

> Anyway the progress db supports it, at least the version 9.
> The other question: why is mysql enemy? Isn`t it just another RDBMS?

First of all it's not RDBMS. Any product that exposes details of underlying 
storage mechanism can not qualify as RDBMS. Innodb only has transactions.. 
Wow..

Secondly it's not enemy. At the most competitor if you in business of selling 
postgresql and an overhyped product overall..

Just my opinion..

Bye
 Shridhar

--
Canada Bill Jones's Motto:	It's morally wrong to allow suckers to keep their 
money.Canada Bill Jones's Supplement:	A Smith and Wesson beats four aces.


Fri, 22 Aug 2003 12:24:11 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: [GENERAL] Need concrete "Why Postgres not MySQL"
> On Friday 22 August 2003 13:59, Andrew Dunstan wrote:
>> Shridhar Daithankar wrote:
>> >BTW any comments on storing an entire database in single file? I
>> >don't
>> > trust any file system for performance and data integrity if I have
>> > single 100GB file. I would rather have multiple of them..
>>
>> I don't see why not. Entire file systems are stored within a single
>> file sometimes. Examples: vmware, and IIRC UserMode Linux.
>
> Well, half the day that I have spent on interbase documnetation, I
> didn't see  any WAL type logs. If transactions directly go to database
> and entire  database is file, I seriously doubt about performance and
> recovery.
>

I am not saying I would do it that way, merely that I could see it
working. I agree about logs, though. I could see it working as 2 files,
one for the base db and one for the log.

> UML and VMware are emulators. You don't want to use them in production
> right?
>

I know companies using VMware extensively. It makes some sense in a multi-
platform environment. If it had lots of corruption problems people
wouldn't use it. (Personally I prefer to use VNC in such an environment).

(Interesting as this is it's probably OT for hackers, though).

cheers

andrew



Fri, 22 Aug 2003 03:02:17 -0500 (EST)
"Andrew Dunstan" <andrew@dunslane.net>
Re: [GENERAL] [pgsql-advocacy] Need concrete 'Why Postgres
On Friday 22 August 2003 07:40, Christopher Kings-Lynne wrote:
> > Can someone explain me why is it usefull if the table created in
> > transaction disapears on rollback?
>
> Because then you can write big scripts like we do at work to install a new
> feature in the live database.  Such a script might create two new tables,
> add columns to existing tables, drop and recreate some foreign keys, etc.
> What you do is to test it you go 'begin;' and then execute the script.  If
> it all worked, commit, else rollback and fix the script.  Repeat until you
> get it right.

Exactly - I'm currently finalising a schema in conjunction with a client who's 
working with some telecoms control equipment. Being able to modify tables and 
copy-forward the data inside a transaction is very useful.

-- 
  Richard Huxton
  Archonet Ltd

Fri, 22 Aug 2003 09:29:32 +0100
Richard Huxton <dev@archonet.com>
Re: [GENERAL] Need concrete "Why Postgres not MySQL"


Shridhar Daithankar wrote:

>BTW any comments on storing an entire database in single file? I don't trust 
>any file system for performance and data integrity if I have single 100GB file. 
>I would rather have multiple of them..
>

I don't see why not. Entire file systems are stored within a single file 
sometimes. Examples: vmware, and IIRC UserMode Linux.

cheers

andrew


Fri, 22 Aug 2003 04:29:57 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: [GENERAL] [pgsql-advocacy] Need concrete "Why Postgres
On Friday 22 August 2003 13:59, Andrew Dunstan wrote:
> Shridhar Daithankar wrote:
> >BTW any comments on storing an entire database in single file? I don't
> > trust any file system for performance and data integrity if I have single
> > 100GB file. I would rather have multiple of them..
>
> I don't see why not. Entire file systems are stored within a single file
> sometimes. Examples: vmware, and IIRC UserMode Linux.

Well, half the day that I have spent on interbase documnetation, I didn't see 
any WAL type logs. If transactions directly go to database and entire 
database is file, I seriously doubt about performance and recovery.

UML and VMware are emulators. You don't want to use them in production right?

I would really love if UML allowed access to filesystems. A Jail type feature 
including access control right in memory. That would really rock but it's a 
different story..

 Shridhar


Fri, 22 Aug 2003 14:06:48 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: [GENERAL] [pgsql-advocacy] Need concrete "Why Postgres
In article <Pine.LNX.4.21.0308220805290.11798-100000@tiger.tigrasoft.hu>,
Hornyak Laszlo <kocka@tigrasoft.hu> writes:

> Hi all!

> Can someone explain me why is it usefull if the table created in
> transaction disapears on rollback?
> Anyway the progress db supports it, at least the version 9.
> The other question: why is mysql enemy? Isn`t it just another RDBMS?

Your second question is answered by someone in a recent posting on the
MySQL mailing list:

> As an Oracle DBA (I'm one myself), InnoDB will give you "close to Oracle"
> features.

> As an FYI, we also spent alot of time looking @ Postres and SAPDB. Postgres
> is a great database engine, and would be very adequate if it didn't have one
> significant missing feature - there is no replication or standby support
> unless you buy an expensive licence (which brings the cost close to that of
> Oracle); we need the high-availability of clusters and replication. Both
> Postgres and MySQL have great support via their mailing lists, but once in a
> while, the people on the Postgres mailing list decide to kick MySQL around a
> bit; I think they have an inferiority complex.

:-)


22 Aug 2003 11:59:00 +0200
Harald Fuchs <nospam@sap.com>
Re: [GENERAL] Need concrete "Why Postgres not MySQL"
Hornyak Laszlo wrote:

> Hi all!
> 
> Can someone explain me why is it usefull if the table created in
> transaction disapears on rollback?
> Anyway the progress db supports it, at least the version 9.
> The other question: why is mysql enemy? Isn`t it just another RDBMS?

Go to http://www.mysql.com, type "postgresql" into the search field (top 
right corner) and read through some of the links. Especially stuff like 
the subsections of

     http://www.mysql.com/doc/en/Compare_PostgreSQL.html

Then go to http://www.postgresql.org and try to find similar FUD. You 
will rather find something like this:

     http://developer.postgresql.org/~petere/comparison.html

This exercise will tell you who considers whom "enemy" and why some of 
us just "dislike" MySQL and their understanding of "fair".


Jan

> 
> Thanks,
> Laszlo
> 
> On Thu, 21 Aug 2003, Ian Barwick wrote:
> 
>> On Thursday 21 August 2003 11:15, Shridhar Daithankar wrote:
>> > On 21 Aug 2003 at 0:22, Ian Barwick wrote:
>> > > * DDL
>> > > - Data definition language (table creation statements etc.) in MySQL
>> > > are not transaction based and cannot be rolled back.
>> >
>> > Just wondering, what other databases has transactable DDLs? oracle seems to
>> > have autonomous transactions which is arthogonal.
>> 
>> DB2 8.1 seems to support transaction-capable DDL. At least, a rollback
>> following a CREATE TABLE causes the table to disappear. Haven't gone
>> into it in any depth.
>> 
>> 
>> Ian Barwick
>> barwick@gmx.net
>> 
>> 
>> 
>> 
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Fri, 22 Aug 2003 10:25:57 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: [GENERAL] Need concrete "Why Postgres not MySQL"
Guys,

> >BTW any comments on storing an entire database in single file? I don't
> > trust any file system for performance and data integrity if I have single
> > 100GB file. I would rather have multiple of them..
>
> I don't see why not. Entire file systems are stored within a single file
> sometimes. Examples: vmware, and IIRC UserMode Linux.

Several database systems use a "single file" for data storage.   The problem 
with this is that it's not really a single file .... it's a proprietary file 
system on top of the host file system.   This sort of design makes a couple 
assumptions:

1) That the database is better than the host filesystem/OS and storage system 
at regulating its use of, and storage of, data files;
2) that your data file will not exceed the maximum file size for the host OS.

Both of these assumptions are, IMHO, based on antiquated data (or on Windows).  
Modern *nix filesystems and RAID are very, very efficient at file access and 
only a database with the development budget of Oracle could hope to keep up.  
Additionally, databases larger than 2GB are becoming increasingly common.

Single-file databases also introduce a number of problems:

1) The database file is extremely vulnerable to corruption, and if corruption 
occurs it is usually not localized but destroys the entire database due to 
corruption of the internal file structure.  Recovery of raw data out of a 
damaged single-file database inevitably requires specialized tools if it is 
possible at all.
2) Often DBAs are prevented from using normal file operations on the database 
files to maintain their systems.  For example, try moving a partition on an 
MS SQL Server installation.  Go on, I double-dog dare you.
3) Due to the necessity of maintaining not only data and metadata, but a file 
partitioning structure as well, maintenance on single-file databases is often 
more time-consuming but at the same time more crucial (to prevent #1).

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Fri, 22 Aug 2003 09:07:52 -0700
Josh Berkus <josh@agliodbs.com>
Re: Single-file DBs WAS: Need concrete "Why Postgres
Some well known database that is very popular amongst people who care 
more for their data than for license fees uses few very big files that 
are statically allocated (if using files instead of raw devices).

Sure does Oracle internally maintain some sort of filesystem. But this 
is more due to other reasons.

If a filesystem contains only very few big files (and nothing else) and 
these files do not grow or shrink during normal operation and are really 
fully allocated in the block tables, then said filesystems metadata does 
not change and that means that the filesystem will never ever be corrupt 
from the OS's point of view (except due to hardware failure). Plus, an 
FSCK on a filesystem with very few huge files is fast, really *fast*. So 
in the case of an OS crash, your system is up in no time again, no 
matter how big your database is.

 From there the DB itself maintains it's own metadata and has control 
with it's WAL and other mechanisms over what needs to be redone, undone 
and turned around to get back into a consistent state.


Jan

Josh Berkus wrote:

> Guys,
> 
>> >BTW any comments on storing an entire database in single file? I don't
>> > trust any file system for performance and data integrity if I have single
>> > 100GB file. I would rather have multiple of them..
>>
>> I don't see why not. Entire file systems are stored within a single file
>> sometimes. Examples: vmware, and IIRC UserMode Linux.
> 
> Several database systems use a "single file" for data storage.   The problem 
> with this is that it's not really a single file .... it's a proprietary file 
> system on top of the host file system.   This sort of design makes a couple 
> assumptions:
> 
> 1) That the database is better than the host filesystem/OS and storage system 
> at regulating its use of, and storage of, data files;
> 2) that your data file will not exceed the maximum file size for the host OS.
> 
> Both of these assumptions are, IMHO, based on antiquated data (or on Windows).  
> Modern *nix filesystems and RAID are very, very efficient at file access and 
> only a database with the development budget of Oracle could hope to keep up.  
> Additionally, databases larger than 2GB are becoming increasingly common.
> 
> Single-file databases also introduce a number of problems:
> 
> 1) The database file is extremely vulnerable to corruption, and if corruption 
> occurs it is usually not localized but destroys the entire database due to 
> corruption of the internal file structure.  Recovery of raw data out of a 
> damaged single-file database inevitably requires specialized tools if it is 
> possible at all.
> 2) Often DBAs are prevented from using normal file operations on the database 
> files to maintain their systems.  For example, try moving a partition on an 
> MS SQL Server installation.  Go on, I double-dog dare you.
> 3) Due to the necessity of maintaining not only data and metadata, but a file 
> partitioning structure as well, maintenance on single-file databases is often 
> more time-consuming but at the same time more crucial (to prevent #1).
> 


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Fri, 22 Aug 2003 12:49:33 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Single-file DBs WAS: Need concrete "Why Postgres

On Friday, August 22, 2003, at 12:07 PM, Josh Berkus wrote:

>
> Single-file databases also introduce a number of problems:
>
> 1) The database file is extremely vulnerable to corruption, and if 
> corruption
> occurs it is usually not localized but destroys the entire database 
> due to
> corruption of the internal file structure.  Recovery of raw data out 
> of a
> damaged single-file database inevitably requires specialized tools if 
> it is
> possible at all.
>
<snip>

Having fallen victim to Oracle crapping in its own nest and doing this 
exact thing, and having to drop some stupid amount of $$ to Oracle for 
them to use their "specialized tool" to try to recover data (which they 
really didn't do much of), I concur with this statement.

Boy, was that a lousy experience.

> -- 
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to 
> majordomo@postgresql.org
>
--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com


Fri, 22 Aug 2003 15:48:45 -0400
Andrew Rawnsley <ronz@ravensfield.com>
Re: Single-file DBs WAS: Need concrete "Why Postgres
Jan,

> If a filesystem contains only very few big files (and nothing else) and 
> these files do not grow or shrink during normal operation and are really 
> fully allocated in the block tables, then said filesystems metadata does 
> not change and that means that the filesystem will never ever be corrupt 
> from the OS's point of view (except due to hardware failure). Plus, an 
> FSCK on a filesystem with very few huge files is fast, really *fast*. So 
> in the case of an OS crash, your system is up in no time again, no 
> matter how big your database is.

I'm not talking about problems with the host filesystem.  I'm talking about 
problems with the data file itself.   From my perspective, the length of time 
it takes to do an FSCK is inconsequential, because I do one maybe once every 
two years.  

It does you little good, though, to have the host OS reporting that the files 
are OK, when the database won't run.

>  From there the DB itself maintains it's own metadata and has control 
> with it's WAL and other mechanisms over what needs to be redone, undone 
> and turned around to get back into a consistent state.

Yes, but you've just added a significant amount to the work the DB system 
needs to do in recovery.   PostgreSQL just needs to check for, and recover 
from, issues with LSN headers and transactions.   Single-file DBs, like SQL 
Server, need to also check and audit the internal file partitioning.

In my experience (a lot of MS SQL, more MS Access than I want to talk about, 
and a little Oracle) corruption failures on single-file databases are more 
frequent than databases which depend on the host OS, and such failures are 
much more severe when the occur.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Fri, 22 Aug 2003 13:05:10 -0700
Josh Berkus <josh@agliodbs.com>
Re: Single-file DBs WAS: Need concrete "Why Postgres
Josh Berkus wrote:

> Jan,
>
> In my experience (a lot of MS SQL, more MS Access than I want to talk about, 
> and a little Oracle) corruption failures on single-file databases are more 
> frequent than databases which depend on the host OS, and such failures are 
> much more severe when the occur.
>

Vadim seemed to think differently:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=00030722102200.00601%40lorc.wgcr.org&rnum=9&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3DVadim%2Bsingle%2Bfile%2Bpostgres

In addition to Jan's points, using a single pre-allocated file also
reduces file descriptor consumption, although I don't know what the
costs are regarding maintaining the LRU of file descriptors, the price
of opens and closes, the price of having a high upper limit of file
descriptors, etc.

Just because Oracle and MS do something doesn't necessary make it
wrong. :-)


Mike Mascari
mascarm@mascari.com









Fri, 22 Aug 2003 16:29:49 -0400
Mike Mascari <mascarm@mascari.com>
Re: Single-file DBs WAS: Need concrete "Why Postgres
Mike,
> Vadim seemed to think differently:
> 
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=00030722102200.00601%40lorc.wgcr.org&rnum=9&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3DVadim%2Bsingle%2Bfile%2Bpostgres

Bad link.  This gives me a post by Lamar Owen talking about usng OIDs to name 
files.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Fri, 22 Aug 2003 15:42:19 -0700
Josh Berkus <josh@agliodbs.com>
Re: Single-file DBs WAS: Need concrete "Why Postgres
On Friday 22 August 2003 18:42, Josh Berkus wrote:
> Bad link.  This gives me a post by Lamar Owen talking about usng OIDs to
> name files.

I think he may be referring to the last paragraph.  Vadim had said that the 
tablenames would go to OIDs.  They have always been individual files.  Been a 
long time since I wrote that e-mail....
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute

Fri, 22 Aug 2003 22:48:55 -0400
Lamar Owen <lowen@pari.edu>
Re: Single-file DBs WAS: Need concrete "Why Postgres

Is anyone seriously suggesting that postgres should support either raw
devices or use some sort of virtual file system? If not, this whole
discussion is way off topic. And if they are my response would be that it
would at best be a serious waste of time - there is far more important
work to do.

cheers

andrew



Sat, 23 Aug 2003 05:33:35 -0500 (EST)
"Andrew Dunstan" <andrew@dunslane.net>
Re: Single-file DBs WAS: Need concrete 'Why Postgres


<off>
Yep, I know, but that is not a reason. :)
</off>

Laci

On Fri, 22 Aug 2003, Jan Wieck wrote:

> Hornyak Laszlo wrote:
> 
> > Hi all!
> > 
> > Can someone explain me why is it usefull if the table created in
> > transaction disapears on rollback?
> > Anyway the progress db supports it, at least the version 9.
> > The other question: why is mysql enemy? Isn`t it just another RDBMS?
> 
> Go to http://www.mysql.com, type "postgresql" into the search field (top 
> right corner) and read through some of the links. Especially stuff like 
> the subsections of
> 
>      http://www.mysql.com/doc/en/Compare_PostgreSQL.html
> 
> Then go to http://www.postgresql.org and try to find similar FUD. You 
> will rather find something like this:
> 
>      http://developer.postgresql.org/~petere/comparison.html
> 
> This exercise will tell you who considers whom "enemy" and why some of 
> us just "dislike" MySQL and their understanding of "fair".
> 
> 
> Jan
> 
> > 
> > Thanks,
> > Laszlo
> > 
> > On Thu, 21 Aug 2003, Ian Barwick wrote:
> > 
> >> On Thursday 21 August 2003 11:15, Shridhar Daithankar wrote:
> >> > On 21 Aug 2003 at 0:22, Ian Barwick wrote:
> >> > > * DDL
> >> > > - Data definition language (table creation statements etc.) in MySQL
> >> > > are not transaction based and cannot be rolled back.
> >> >
> >> > Just wondering, what other databases has transactable DDLs? oracle seems to
> >> > have autonomous transactions which is arthogonal.
> >> 
> >> DB2 8.1 seems to support transaction-capable DDL. At least, a rollback
> >> following a CREATE TABLE causes the table to disappear. Haven't gone
> >> into it in any depth.
> >> 
> >> 
> >> Ian Barwick
> >> barwick@gmx.net
> >> 
> >> 
> >> 
> >> 
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 2: you can get off all lists at once with the unregister command
> >>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >> 
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 
> 
> -- 
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #================================================== JanWieck@Yahoo.com #
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 


Sat, 23 Aug 2003 12:57:36 +0200 (CEST)
Hornyak Laszlo <kocka@tigrasoft.hu>
Re: [GENERAL] Need concrete "Why Postgres not MySQL"
On Fri, 22 Aug 2003, Mike Mascari wrote:

> In addition to Jan's points, using a single pre-allocated file also
> reduces file descriptor consumption, although I don't know what the
> costs are regarding maintaining the LRU of file descriptors, the price
> of opens and closes, the price of having a high upper limit of file
> descriptors, etc.
>

another thought I had - I wonder how much fragmentation affects postgres
(and others).  On Informix when you use "cooked files" it needs to
preallocate all the space.  I figured part of it was to 1. make the file
seem more like a device 2. reduce fragmentation 3. guarantee that 2GB
"chunk" can never have an out of space issue.

On a dedicated pg box there is probably not much fragmentation (and thanks
to today's modern filesystems, it isn'ttoo big of a deal in any case)..
but it would still be interesting to have numbers..

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



Sat, 23 Aug 2003 09:31:27 -0400 (EDT)
Jeff <threshar@torgo.978.org>
Re: Single-file DBs WAS: Need concrete "Why Postgres
There is an interesting report (in English) about the use of free 
Open-Source software in companies and public institutions in Germany, UK 
and Sweden. (As Germany is one of the most important and largest markets 
for Open-Source software)

This FLOSS report covers to some extend specific figures about 
Open-Source DBMS, reasons why organizations have decided for OS DBMS, 
market shares of OSS DBMS, etc. - PostgreSQL and MySQL are mentioned too 
(see chapter 5.1.2 OSS used for databases, Page 37+).

The report has been made by Berlecon Research (http://www.berlecon.de)
in 2002, financed by the European Commission. Maybe you already know
this report, but for them who do not, I provide this report as PDF 
temporarily at following URL:

http://www.go-gen.com/tmp/FLOSS1-Usage_of_Free_Open_Source.pdf

Hopefully, these information may be useful for better understanding the 
needs of our customers, or the promotion of PostgreSQL.

Have a nice W/E
Anastasios


Sat, 23 Aug 2003 17:02:29 +0200
Anastasios Hatzis <ahatzis@ithcorp.com>
Surveys & Studies: FLOSS - Usage of Free/Libre Open Source Software
	(PostgreSQL, etc.)

This makes sense to me. I sense a TODO item :-)

(My dim and possibly incorrect memory of administering Ingres around 10
years ago was that it supported both raw devices and file system based
databases. We opted for a file system base, for reasons others have
mentioned here, but I seem to recall we used a raw device for the
transaction log for performance reasons. But, as the saying goes, that was
a long time ago, and in another country.)

andrew


Tom wrote
> "Andrew Dunstan" <andrew@dunslane.net> writes:
>> Is anyone seriously suggesting that postgres should support either raw
>> devices or use some sort of virtual file system? If not, this whole
>> discussion is way off topic.
>
> I have zero interest in actually doing it.  However, it'd be nice if
> the existing "storage manager" API were clean enough that our response
> to this type of question could be "sure, go implement it, and when
> you're done let us know what performance improvement you see".  We've
> allowed the smgr API to degenerate over the years.  CREATE/DROP
> DATABASE both bypass it, and the support for alternate database
> locations messes up the API pretty thoroughly (not that there's
> anything clean about that feature at all), and I think there are some
> other issues with specific commands bypassing the smgr abstractions.
>
> I think it would be reasonable to fix this as part of the "tablespaces"
> work that people keep wanting to do.
>
> 			regards, tom lane




Sun, 24 Aug 2003 14:15:25 -0500 (EST)
"Andrew Dunstan" <andrew@dunslane.net>
Re: Single-file DBs WAS: Need concrete 'Why Postgres
"Andrew Dunstan" <andrew@dunslane.net> writes:
> Is anyone seriously suggesting that postgres should support either raw
> devices or use some sort of virtual file system? If not, this whole
> discussion is way off topic.

I have zero interest in actually doing it.  However, it'd be nice if the
existing "storage manager" API were clean enough that our response to
this type of question could be "sure, go implement it, and when you're
done let us know what performance improvement you see".  We've allowed
the smgr API to degenerate over the years.  CREATE/DROP DATABASE both
bypass it, and the support for alternate database locations messes up
the API pretty thoroughly (not that there's anything clean about that
feature at all), and I think there are some other issues with specific
commands bypassing the smgr abstractions.

I think it would be reasonable to fix this as part of the "tablespaces"
work that people keep wanting to do.

			regards, tom lane

Sun, 24 Aug 2003 18:39:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Single-file DBs WAS: Need concrete 'Why Postgres 