Improvements to PostgreSQL

On Sun, 25 Jul 2004, Bruce Momjian wrote:

I am forwarding this to hackers on behalf of some people who want to
improve PostgreSQL in India.

I suggest you read the developers FAQ on the developers page:

http://developers.postgresql.org

then subscribe to hackers to discuss feature additions. Thanks.

As Bruce suggests, subscribe and consider posting the list of feature
differences so that others can see what the diffences are.

Thanks,

Gavin

I reckon they could have a crack at implementing SQL2003 recursive
queries (eg. WITH syntax).

Or, SQL2003 ROLLUP and CUBE queries.

But that's just what I want :)

Chris

Bruce Momjian wrote:

Hi,

I am already a member of hackers group and I get all
the messages posted to the group. In fact I get 2
copies of each!! I think I subscribed twice. But I am
unable to send messages to the group.

Here are the feature list which we are planning to
implement. This list is obtained by comparing the
features of Oracle 8i and Postgresql 7.4 . We know
that a lot more work has to be done in finding the
exact features and the way to implement them. Anyway
this is our initial list :

Our basic idea is to divide the task of migrating from
Oracle to postgresql in 3 parts
1. Automation of migration from Oracle to postgresql
:- there is already the tool ora2pg - but we came to
know that a lot of things have to be improved to
totally automate it.

2. Core functionality add-ons to postgresql :-
-Table joins
-Java stored procedures
-Backup and recovery (incremental backup/restore)
-Query optimization
* Indexing techniques
* Star query optimization
- Data Types
* BLBL
* Images
* Long
Some of the above features are already present, but
may not be complete(as compared to Oracle).

3. Application compatibility
-Oracle Compatible JDBC driver
-PL/SQL Compatibility
* Named parameter
* Parameterized Cursors
* Output Paramters
-Query compatibility
* Oracle DML/DDL Compatibility
* Behavioral differences like null handling
* Nested Queries
* Manual Optimization Hints

I know that this list is not totally explainative, but
we will provide you further details as we investigate
deeper in this regard.

All your sugestions are welcome. Please help us to
implement these features.
Our aim is to make postgresql enterprise level.

Thanks,
Suresh

--- Bruce Momjian <pgman@candle.pha.pa.us> wrote: > I
am forwarding this to hackers on behalf of some

people who want to
improve PostgreSQL in India.

I suggest you read the developers FAQ on the
developers page:

http://developers.postgresql.org

then subscribe to hackers to discuss feature
additions. Thanks.

---------------------------------------------------------------------------

Suresh Tri wrote:

Hi all,

We are a bunch(about 10)of enthusiastic software
engineers from Bangalore who strongly believe in
opensource.
We are currently looking at the prospect of

improving

the PostgreSQL to the enterprise level. At first

we

would like to take PostgreSQL atleast to the level

of

Oracle 7i or 8i. We already have the difflist of
features differences between Oracle 8i and the

current

PostgreSQL(7.4). But we are finding it hard to
prioritize the features.
What do you think are the important features that

are

present in Oracle 8i but absent in the current
PostgreSQL ehich the enterprises look at. It would

be

of the great help if you can tell us about these
featues and also the way in which we can

contribute to

the PostgreSQL development.

-- 
Bruce Momjian                        | 
http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610)
359-1001
+  If your life is a hard drive,     |  13 Roberts
Road
+  Christ can be your backup.        |  Newtown
Square, Pennsylvania 19073

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the
unregister command
(send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)

___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun! http://www.allnewmessenger.com

Suresh Tri wrote:

2. Core functionality add-ons to postgresql :-
-Table joins

Already done.

-Java stored procedures

Already done.

-Backup and recovery (incremental backup/restore)

Will appear in next release.

-Query optimization
* Indexing techniques
* Star query optimization

Good luck with that. :-)

- Data Types
* BLBL
* Images
* Long

These are probably redundant with bytea.

3. Application compatibility
-Oracle Compatible JDBC driver

JDBC drivers should be compatible with the JDBC standard, not with some
vendor.

-Query compatibility
* Oracle DML/DDL Compatibility

Like what?

* Behavioral differences like null handling

Not gonna happen.

* Nested Queries

Do you mean recursive queries?

* Manual Optimization Hints

These are probably not going to be compatible with Oracle anyway.

All your sugestions are welcome. Please help us to
implement these features.
Our aim is to make postgresql enterprise level.

PostgreSQL is already enterprise-level.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Suresh Tri wrote:
<snip>

All your sugestions are welcome. Please help us to
implement these features.
Our aim is to make postgresql enterprise level.

Hi Suresh,

From reading your post, I feel your team is approaching the goal of
making PostgreSQL "Enterprise Level" in a non-optimal way.

With the "soon to be released" version 7.5 of PostgreSQL, the core
database engine itself is already very good. This is not the area
needing to be worked upon for the next level of Enterprise Functionality.

Your team will likely have a lot more effect if they concentrate on what
Enterprises really need that PostgreSQL is missing:

+ An SNMP agent to report on PostgreSQL's status and allows remote
control of the PostgreSQL daemon. From an Oracle perspective, this
would be the equivalent of "Oracle Intelligent Agents", part of the core
features of the Oracle Enterprise Manager (OEM).

+ Tools to allow control of PostgreSQL databases from one central
place. Again, the same as OEM.

    + Starting and stopping the database
    + Managing Users
    + Backup and Recovery
    + Alerts and submitting jobs
    + etc

Oracle does this by having a centralised information repository that
a management GUI connects too, and having Oracle Intelligent Agents
running on each server the database software is on. These Oracle
Intelligent Agents keep the centralised repository aware of the status
of the Oracle server software, perform actions on the Oracle servers as
directed by the centralised repository (jobs running on there,
instructions by the GUI, etc), and more.

There's more to what the OEM GUI does, but that's a good start.

+ Something else that would be useful is a GUI tool to automatically
setup PostgreSQL replication. The PostgreSQL "Slony-I" project would be
the first one to look at, and probably equivalent to something like
Oracle's Data Guard. They use the different approach, but the end
result is having a master and standby databases.

Hope this is helpful.

Regards and best wishes,

Justin Clift

Thanks,
Suresh

<snip>

Thanks Peter for your reply,

we will investigate further into your comments. We
understand that postgresql is already
enterprise-level. But as our observation many of the
propriety databse users in the enterprise level are
reluctant to switch to postgresql. We wanted to to
bridge the gap.

Thanks once again,
Suresh

--- Peter Eisentraut <peter_e@gmx.net> wrote: > Suresh
Tri wrote:

2. Core functionality add-ons to postgresql :-
-Table joins

Already done.

-Java stored procedures

Already done.

-Backup and recovery (incremental backup/restore)

Will appear in next release.

-Query optimization
* Indexing techniques
* Star query optimization

Good luck with that. :-)

- Data Types
* BLBL
* Images
* Long

These are probably redundant with bytea.

3. Application compatibility
-Oracle Compatible JDBC driver

JDBC drivers should be compatible with the JDBC
standard, not with some
vendor.

-Query compatibility
* Oracle DML/DDL Compatibility

Like what?

* Behavioral differences like null handling

Not gonna happen.

* Nested Queries

Do you mean recursive queries?

* Manual Optimization Hints

These are probably not going to be compatible with
Oracle anyway.

All your sugestions are welcome. Please help us to
implement these features.
Our aim is to make postgresql enterprise level.

PostgreSQL is already enterprise-level.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the
unregister command
(send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)

___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun! http://www.allnewmessenger.com

On Mon, Jul 26, 2004 at 06:13:30PM +1000, Justin Clift wrote:

+ An SNMP agent to report on PostgreSQL's status and allows remote
control of the PostgreSQL daemon. From an Oracle perspective, this
would be the equivalent of "Oracle Intelligent Agents", part of the core
features of the Oracle Enterprise Manager (OEM).

Is there more to "remote control" than setting GUC variables? Tell me more!

Cheers,

Patrick

Patrick Welche wrote:
<snip>

Is there more to "remote control" than setting GUC variables? Tell me more!

Sure:

  + starting/restarting/stopping the database server software
  + the normal DBA type work - creating/altering/dropping databases, 
users, functions, languages, permissions (etc)
  + Remote backup and recovery
  + Submitting jobs to run remotely on the server.  i.e. reindexing or 
vacuuming scripts

Remote Monitoring:
+ Alerts for specified events. i.e. The database server is getting
near to capacity in it's filesystem(s), or there have been too many
invalid PG authorisation attempts, or there are connections getting
rejected because the max_connections parameter isn't high enough

Groups
+ Defining arbitrary groups of servers for the above to speed things
up when working with many servers

Roles
+ Having multiple administrators with different permissions (role
based is generally good), all communicating through the centralised info
repository so things don't get out of sync

(possibly)
+ loading additional PG packages. i.e. rolling out "oid2name" or
"pgbench" (or other PG utils) to servers. Could be viewed as something
that should be done with the OS packaging mechanism(s) instead.

Any of the PG GUI's (I generally use pgAdmin) could likely be extended
to do all of this in a nice, user friendly way.

As an aside, SNMP is important in enterprise settings as it allows PG to
be plugged into the monitoring capabilities of enterprise management
frameworks. i.e. Concord's eHealth, and probably Tivoli, OpenView, etc

Hope that's useful.

Regards and best wishes,

Justin Clift