Forum OpenACS Development: Annoying "from statement missing" Notices

In a current installation of OpenACS I get a lot of errors related to the way the acs_object_id_seq is generated. Now that we require PostgreSQL 8, would it be possible to drop the view and just generated the sequence normally?

Apart from that, does anyone know how I can surpress these messages to show up in the Error log?

NOTICE: adding missing FROM-clause entry for table "acs_object_id_seq"
CONTEXT: SQL statement "SELECT acs_object_id_seq.nextval from dual"
PL/pgSQL function "content_revision__copy" line 23 at select into variables

And last but not least, should

select acs_object_id_seq.nextval into v_copy_id from dual;

not be

select into v_copy_id acs_object_id_seq.nextval from dual;

?

Collapse
Posted by Gustaf Neumann on
Well, i was annoyed by this as well for a while and devoted my weekend for the problem. The result is in CVS head and removes the "missing FROM-clause" messages in PostgreSQL (no changes for Oracle necessary).

The messages are mostly due to an old, deprecated syntax of pg, which pops up in connection with sequences (but not only, as well on the global constants of the content repository). While i was at it, i updated as well some of the sequence interfaces. For example db_nextval acs_object_id_seq is now more than twice as fast than before (before: 948 microseconds, now: 378 microseconds). I have tried to be as careful as possible and not to break existing code. The new version runs the regression test of the acs-kernel modules like before; i tested upgrade and fresh installs.

Actually, for most purposes, we could now get rid of the special postgres config flag

 add_missing_from = on
but since i have only tested the kernel-module regression tests and xotcl-core and xowiki, it can be that in some other acs packages there are still dependencies on that flag.

Since the change effects pretty basic functions of openacs (e.g. 00-database-procs.tcl, acs_object__new(), content_item__new(), ...) i have committed these changes in a single commit, such that one can more easily undo my changes in case of troubles.

http://xarg.net/tools/cvs/change-set-details?key=28379

Collapse
Posted by Torben Brosten on
Hi all,

I'm running pg9.0 with some non-core packages and am running into similar errors:

Database operation "0or1row" failed
(exception ERROR, "ERROR: missing FROM-clause entry for table "acs_object_id_seq"
LINE 2: select acs_object_id_seq.nextval

PG9.0 doesn't accept the add_missing_from config flag.

Also, the xarg.net link isn't working.

Where is there an example change?

I'd like to update the non-core packages with similar issues as I run-into them.

cheers,

Torben

Collapse
Posted by Torben Brosten on
I figured it out.

Replace with set var_name [db_nextval acs_object_id]

Thank you for reading..

Collapse
Posted by Gustaf Neumann on
If you want to address the problem from SQL, a typical approach is to use the "nextval" function instead of .nextval pseudo attribute. See for an example e.g.:

http://cvs.openacs.org/browse/OpenACS/openacs-4/packages/acs-tcl/tcl/security-procs-postgresql.xql?r1=1.10&r2=1.11

For some unknown reasons, fisheye is not able to resolve the changeset anymore. For more examples, how to deal with the problem, checkout on github

https://github.com/openacs/openacs-core/commit/b211f20ff219046f975b3fb834ef7c92d05346f6

and look for changes in

postgresql/acs-metadata-create.sql
postgresql/acs-objects-create.sql
etc.

Hope this helps
-gustaf neumann