SQL/PSM for 8.2 will offer ANSI/ISO, MySQL, and DB2 Compatibility

Hi All,

I have nothing bad to say about MySQL. :-) Certainly they are a
marketing machine and they do have that spiffy ability to run fast
read-only stuff when there's no transaction control on the DB.

On a serious note, I think it's extremely important for Postgres to
maintain it's crown as the most ANSI compliant commercial quality DBMS
in the world. (MySQL 5.0 will be in production some day and they are
adding a first generation of SQL/PSM support).

This is the primary reason why EnterpriseDB is interested in sponsoring
a community SQL/PSM project for PG 8.2 that will provide ANSI/ISO
compatible triggers n stored procs that are:

- part of the PG core,
- efficiently implemented,
- have full interactive debugging support built in from day 1.

--Luss

PS Stored Procs came late to IBM's UDB/DB2 product line and they
therefore use SQL/PSM triggers and stored procs. Perhaps the code name
for the SQL/PSM project can be "UpsulMonk".

> -----Original Message-----
> From: pgsql-advocacy-owner(at)postgresql(dot)org [mailto:pgsql-advocacy-
> owner(at)postgresql(dot)org] On Behalf Of Chris Travers
> Sent: Tuesday, July 26, 2005 3:27 PM
> To: Jim C. Nasby; PostgreSQL advocacy
> Subject: Re: [pgsql-advocacy] [HACKERS] Enticing interns to PostgreSQL
>
> Jim C. Nasby wrote:
>
> >But the problem is that grassroots OSS movements change the market
once
> >they get large enough. 10, or even 5 years ago it was impossible to
get
> >linux into big business, for many of the reasons you mentioned. But
> >that's changed, even though *BSD was technically superior. It changed
> >because there was a virtual army of linux users who wanted very badly
to
> >be able to use linux at work. MySQL has more 'foot-soldiers' than
> >PostgreSQL does, even if a lot of them are alienated.
> >
> >
> I don't want to get into a BSD v. Linux flamewar. But I think that
the
> most important thing that Linux did better than BSD was
> community-building. Apache did an excellent job of community-building
> as well.
>
> MySQL is currently sort of a de-facto standard. However, I think we
> have a more involved community here with PostgreSQL. We may have
fewer
> footsoldiers, but our footsoldiers are better, more technically able,
> and form a larger core community than you have with MySQL.
>
> As evidence, MySQL used to argue that PostgreSQL's rate of development
> was too fast, and that their slower rate was better because it meant
> things got done right. Whatever.....
>
> Finally, I would say that I have avoided MySQL since PostgreSQL 7.0
came
> out. Like many people, I found MySQL easier to use before that point.
> But with advances in usability, PostgreSQL has become an extremely
easy
> to use and powerful product. I am able to get so much more done
faster
> with PostgreSQL, and my queries run faster. For example:
>
> SELECT ar1.invnumber AS invoice1, ar2.invnumber AS invoice2,
> ar1.amount, ar1.paid, ar1.duedate
> FROM ar ar1, ar ar2
> WHERE ar1.invnumber::numeric > (ar2.invnumber::numeric - 3::numeric)
> AND ar1.invnumber::numeric < (ar2.invnumber::numeric + 3::numeric) AND
> ar1.amount = ar2.amount
> AND ar1.invnumber <> ar2.invnumber AND ar1.till::text = ar2.till::text
> AND ar1.duedate = ar2.duedate AND ar1.employee_id = ar2.employee_id
AND
> NOT (ar1.id IN ( SELECT i1.trans_id
> FROM invoice i1
> WHERE i1.trans_id = ar1.id AND NOT (i1.parts_id IN ( SELECT
> i2.parts_id
> FROM invoice i2
> WHERE i2.trans_id = ar2.id AND i2.parts_id =
> i1.parts_id AND i1.qty = i2.qty)))) AND NOT (ar1.id IN ( SELECT
> ac1.trans_id
> FROM acc_trans ac1
> WHERE ac1.source IS NOT NULL AND ac1.trans_id = ar1.id AND
NOT
> (ac1.amount IN ( SELECT ac2.amount
> FROM acc_trans ac2
> WHERE ar2.id = ac2.trans_id AND ac2.source =
> ac1.source AND ac2.source IS NOT NULL AND ac1.amount = ac2.amount))))
> AND ar1.amount <> 0::double precision
> ORDER BY ar1.invnumber::numeric;
>
> ran in a reasonable amount of time. Running the same query in MySQL
> would have been much slower.
>
> >Second (and probably more important), we need to make it easier for
> >people to migrate to PostgreSQL from MySQL. There's a sizeable number
of
> >people who would like to migrate things off of MySQL if it wasn't so
> >difficult, and hard to do incrementally. Adding support for some
MySQL
> >features (such as enum and tinyint), making it easy for PostgreSQL
> >databases to talk to MySQL databases (perhaps via dblink), and
providing
> >methods to connect to PostgreSQL without having to tear out big
chunks
> >of un-abstracted code are some things that would help here.
> >
> >
> How hard would it be to automatically create enum_ tables in the back
> ground to emulate MySQL's enum type? Sort of like we do with SERIAL
> datatypes... Part of the problem is that MySQL's enum type is so
> braindead from a database design perspective that most of us would not
> be interested in using it. Emulating an int foreign key for another
> created table might make it ok, though.
>
> Also, why not simply allow tinyint to be the same as int(2)?
>
> Personally, I agree that we should pursue MySQL compatibility along
with
> compatibility for the large "real" RDBMS. Simply because it means a
> larger, more diverse community.
>
> Best Wishes,
> Chris Travers
> Metatron Technology Consulting
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings