Forum OpenACS Q&A: Re: PG 7.2->7.3 upgrade gotcha?

Posted by Andrew Piskorski on
Ah, important points about views, etc. in Postgres. I wasn't aware of those limitations.

As far as an OpenACS-wide file naming and organization convention, ok, here's one concrete proposal:

  • Wherever possible and feasible, database objects must be defined with "create or replace" rather than "create". See below [TODO] for info on Oracle and PostgreSQL limitations.

  • All procedural code that lives inside the database must be defined in its own files separate from any DDL ("create table", etc.) statements. These files must be named either "*-pb.sql" (for Oracle package bodies, functions, and procedures; for PostgreSQL functions and procedures) or "*-ph.sql" (for Oracle package headers).

  • Oracle package headers may be included with the package bodies in the "*-pb.sql" file, but often should be in their own "*-ph.sql" file (developer's judgment). As PostgreSQL pl/pgsql does not support packages, there should be no Postgres "*-ph.sql" files.

  • Triggers should be in their own file named "*-tr.sql".

  • Views should be in their own file named "*-vw.sql".

If anyone has counter-suggestions of comments, please chime in. :)

E.g., perhaps "*-fn.sql" should also be allowed in addition to "*-pb.sql" for Oracle and PostgreSQL functions and procedures. In that case, I think you'd add:

  • Stand-alone Oracle functions and procedures, not part of any PL/SQL package, may be in a "*-pb.sql" file, but usually should be in a "*-fn.sql" file instead.

  • PostgreSQL functions designed to emulate or be compatible with Oracle package body functions should preferably be in a "*-pb.sql" file rather than a "*-fn.sql" file.