Forum OpenACS Development: Overview of PostgreSQL's limitations
- max. row width
- max. key/index width
- max. size of varchar
1. max number of args is 16. This used to be only 8 for pg versions before 7.0. A lot of the pl/sql functions in acs 4.0 have a large number of input arguements, but so far the largest number of input args that I have encountered is 16.
2. if any of the args are null, the function short-circuits and returns a null.
3. an pl/sql function is an implicit transaction. no transaction control is allowed inside of a pl/sql function. Either the whole function completes correctly, and is committed, or the whole thing is rolled back.
4. pg doesn't support packages, so we'll need a naming convention to avoid namespace conflicts. Also pg truncates names greater than a certain length, so it might be difficult to do something simple like substitute a '_' for a '.' when porting the package specific function calls.
Biggest function name is 30 characters long.
5. PG has no procedures. It only has functions that can only return one thing (AFAIK - hopefully I am wrong).
6. Postgres supports: PL/PgSQL (imitates PL/SQL but has many less features), PL/Tcl (nice to work with strings and arrays) and PL/Perl (never did anything with it).
Maybe you could tell us the same things about Interbase (in another thread). I am sure there are people other than me that are also curious.
BY constructs. Somewhere in openACS code I spotted a solution
which uses two (or three?) Pl/PgSql functions emulating LEVEL
and ROWNUM, or something like that.
Is this preferred way? Or should we create index table for
each parent/child structure which should be updated on
INSERT/DELETE/UPDATE to master table? Since PostgreSQL does
not suffer from "mutating" syndrome like Oracle, will it be
possible just to write few triggers that could fire
recursively and maintain the index (parent-child mapping)
table? Or something else?
Sebastian> Thanks for all answers so far. I'm just sweating over Sebastian> CONNECT BY constructs. Somewhere in openACS code I Sebastian> spotted a solution which uses two (or three?) Pl/PgSql Sebastian> functions emulating LEVEL and ROWNUM, or something like Sebastian> that. Is this preferred way?We've done some connect by and oracle pseudo-column emulation with recursive functions for pg. See the intranet, homepage and file-storage modules for examples. This is not optimal, but it could be improved by caching the order by field in a table column. Other alternatives have also been suggested. See this previous discussion
simulates Oracle's "connect by" query. It works similar to db_foreach.
Please let me know what you think. If this is a move in the right
direction, we could replace all calls of db_foreach that use a
"connect by" clause with the corresponding call of "db_dfs".
Then, we could provide two versions of db_dfs: one for Postgres
that simulates the "connect by" with a recursive algorithm and
one for Oracle that uses "connect by" as before.