Re: Limitations on PGSQL - Mailing list pgsql-general

IMHO Postgres' drawbacks are the following:

Severely limited access/grants system - postgres gives little or no control over anything beyond controlling access to
wholetables. -Yes you can create views but views have a couple of drawbacks too... This is especially a problem with
regardto functions (no trusted functions). 

Long connection time - if you are using the web you will have to use some sort of persistant scheme e.g. Apache::DBI
otherwiseyou will handle around 5 requests per sec on a decent computer. I wonder whether it would be possible for it
toeither reconnect, keeping the connection to a new database or user, or reuse it's kids - like Apache. 

No schema/tablespaces/cross-database access (- And it's listed on EXOTIC :()
- You can emulate some of these features yet it's not the same.

Error messages take a long time to get used to and generally figuring things out may take some time (at least for me)

If you create a function/trigger/view/rule etc. which accesses a table, and then you drop that table, and recreate it,
youmay have to recreate the function etc. 

It's advantages are:

Runs on practically any platform (I run OpenBSD so it matters).

Supports triggers, rules (statement level triggers), views and stored procedures!

fast - my queries - which may be quite complex at times, are generally fast, and if they are not I can always speed
themup with EXPLAIN, indexes, triggers creating derived tables and so on. 

Did I say stored procedures?

License - Do ANYTHING you want with it (more or less) not as communistic as the obiquitous GPL.

Price - Depending on your internet connection generally less than $0.02...

Great community - Does not mind answering questions and seems to forgive quickly as well.

Write Ahead logging, and many other functions I haven't really exploited yet.

Regards,

Aasmund




On Mon, 05 Nov 2001 11:33:48 +0100, Jean-Michel POURE <jm.poure@freesurf.fr> wrote:
> At 12:04 05/11/01 +0530, you wrote:
>
> Hello Balaji,
>
> There are no real limitations when using PostgreSQL smart programming
> features: views, triggers, rules, types and plpgsql server-side language.
>
> For example:
>
> 1) FAST READINGS: triggers can store display values instead of performing
> several LEFT JOINS or calling PL/pgSQL functions. Similarly, you can use
> triggers to perform complex initialization or maintain consistency when
> adding/modifying a record. Cron jobs and functions can perform queries and
> store results for instant results (ex: statistics tables).This makes your
> database very fast in complex readings (ex: web environment). This concept
> of storing values is the base of optimization.
> 2) SAFETY: postgreSQL is a real transactional system. When using a
> combination of views and rules, you can control data modification very
> neatly. Example: you can define a sub-select of a table and control the
> scope of queries. This is very important in a commercial environment when
> you data is valuable and must not be deleted or modified given a set of rules.
> 3) CODING: server-side coding is mainly performed in PL/pgSQL, a very easy
> and powerful server-side language.
>
> This is paradise if you are a programmer. IMHO, the only few drawbacks are:
>
> 1) TABLE DEFINITION: it is Impossible to delete a column or to
> promote/demote a column type. You have to drop the table and import old
> values into a new table. This makes life harder when working on large
> databases. You are always afraid of loosing your data. Even with backups,
> it is always 'heart breaking' to modify a table. You have to perform tests
> to ensure all data is there and safe.
>
> 2) VIEWS/TRIGGERS cannot be modified. You have to drop them and create them
> again. This makes programming a little bit tricky. Further more, if you
> create a view, let's say "SELECT table1.*, table2.* FROM table1 a LEFT JOIN
> table2 b on a.oid=b.oida", the resulting view displays all fields, hence
> making it harder for a non programmer to read view content.
>
> This is very little drawback compared to power and reliability of PostgreSQL.
>
> Best regards,
> Jean-Michel POURE
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46