Forum OpenACS Q&A: Upgrade Problem from 5.4 to 5.5

exception ERROR, "FEHLER: NULL-Wert in Spalte »tree_sortkey« verletzt Not-Null-Constraint

Seems like

select content_item__new(:name,:parent_id,:item_id,:locale,:creation_date,:creation_user,:context_id,:creation_ip,:item_s..."

tries to enter null value in the tree_sortkey column of cr_items. Why?

I had the same error with acs_objects upgrading acs-kernel.

Any idea?

I created a dump of the 5.4 installation running on pg 8.1. I loaded the dump to pg 8.3. Then I upgraded from 5.4 to 5.5.

[11/Nov/2009:20:42:06][12527.1549695744][-main-] Error: Ns_PgExec: result status: 7 message: FEHLER: Datentyp text hat keine Standardoperatorklasse für Zugriffsmethode »gist«
HINT: Sie müssen für den Index eine Operatorklasse angeben oder eine Standardoperatorklasse für den Datentyp definieren.

[11/Nov/2009:20:42:06][12527.1549695744][-main-] Error: Error sourcing /www/dotlrn-2.5.0/packages/xowiki/tcl/xowiki-procs.tcl:
Database operation "dml" failed
(exception ERROR, "FEHLER: Datentyp text hat keine Standardoperatorklasse für Zugriffsmethode »gist«
HINT: Sie müssen für den Index eine Operatorklasse angeben oder eine Standardoperatorklasse für den Datentyp definieren.
")

while executing
"ns_pg_bind dml nsdb0 {create index xowiki_page_page_order_idx ON xowiki_page using gist (page_order)}"
("uplevel" body line 1)
invoked from within
"uplevel $ulevel [list ns_pg_bind $type $db $sql]"
("postgresql" arm line 2)
invoked from within
"switch $driverkey {
oracle {
return [uplevel $ulevel [list ns_ora $type $db $sql] $args]
}
..."
invoked from within
"db_exec dml $db $full_statement_name $sql"
("uplevel" body line 2)
invoked from within
"uplevel 1 $code_block "
invoked from within
"db_with_handle -dbn $dbn db {
db_exec dml $db $full_statement_name $sql
}"
(procedure "db_dml" line 100)
invoked from within
"db_dml [my qn create-index-$name] "create $uniquepart index $name ON $table $using ($col)""
(procedure "index" line 12)
::xo::db::require->index
invoked from within
"::xo::db::require index -table xowiki_page -col page_order -using [expr {[::xo::db::has_ltree] ? "gist" : ""}]"
(in namespace eval "::xowiki" script line 94)
invoked from within
"namespace eval ::xowiki {
#
# create classes for different kind of pages
#
::xo::db::CrClass create Page -superclass ::xo::db::CrItem \
..."
(file "/www/dotlrn-2.5.0/packages/xowiki/tcl/xowiki-procs.tcl" line 9)
invoked from within
"source $__file "

First:

[11/Nov/2009:20:31:54][6577.1125480784][-default:14-] Notice: <h3>Installing Kernel 5.5.1</h3>
[11/Nov/2009:20:31:55][6577.1125480784][-default:14-] Notice: reading /www/dotlrn-2.5.0/packages/acs-kernel/catalog/acs-kernel.en_US.ISO-8859-1.xml in ISO-8859-1
[11/Nov/2009:20:31:55][6577.1125480784][-default:14-] Notice: Loading messages in file /www/dotlrn-2.5.0/packages/acs-kernel/catalog/acs-kernel.en_US.ISO-8859-1.xml
[11/Nov/2009:20:31:55][6577.1125480784][-default:14-] Notice: reading /www/dotlrn-2.5.0/packages/acs-kernel/catalog/acs-kernel.de_DE.ISO-8859-1.xml in ISO-8859-1
[11/Nov/2009:20:31:55][6577.1125480784][-default:14-] Notice: Loading messages in file /www/dotlrn-2.5.0/packages/acs-kernel/catalog/acs-kernel.de_DE.ISO-8859-1.xml
[11/Nov/2009:20:31:56][6577.1125480784][-default:14-] Error: Ns_PgExec: result status: 7 message: FEHLER: NULL-Wert in Spalte »tree_sortkey« verletzt Not-Null-Constraint
CONTEXT: SQL-Anweisung »insert into acs_objects (object_id, object_type, title, package_id, context_id, creation_date, creation_user, creation_ip, security_inherit_p) values ( $1 , $2 , $3 , $4 , $5 , $6 , $7 , $8 , $9 )«
PL/pgSQL function "acs_object__new" line 39 at SQL statement
PL/pgSQL function "apm_package_version__new" line 25 at assignment

[11/Nov/2009:20:31:56][6577.1125480784][-default:14-] Error: apm_package_install: Error installing Kernel version 5.5.1: Database operation "0or1row" failed
(exception ERROR, "FEHLER: NULL-Wert in Spalte »tree_sortkey« verletzt Not-Null-Constraint
CONTEXT: SQL-Anweisung »insert into acs_objects (object_id, object_type, title, package_id, context_id, creation_date, creation_user, creation_ip, security_inherit_p) values ( $1 , $2 , $3 , $4 , $5 , $6 , $7 , $8 , $9 )«
PL/pgSQL function "acs_object__new" line 39 at SQL statement
PL/pgSQL function "apm_package_version__new" line 25 at assignment
")

Database operation "0or1row" failed
(exception ERROR, "FEHLER: NULL-Wert in Spalte »tree_sortkey« verletzt Not-Null-Constraint
CONTEXT: SQL-Anweisung »insert into acs_objects (object_id, object_type, title, package_id, context_id, creation_date, creation_user, creation_ip, security_inherit_p) values ( $1 , $2 , $3 , $4 , $5 , $6 , $7 , $8 , $9 )«
PL/pgSQL function "acs_object__new" line 39 at SQL statement
PL/pgSQL function "apm_package_version__new" line 25 at assignment
")

while executing
"ns_pg_bind 0or1row nsdb0 {
select apm_package_version__new(
:version_id,
:package_key,
:version_name,
:version_uri,
:summary,
:des..."
("uplevel" body line 1)
invoked from within
"uplevel $ulevel [list ns_pg_bind $type $db $sql]"
("postgresql" arm line 2)
invoked from within
"switch $driverkey {
oracle {
return [uplevel $ulevel [list ns_ora $type $db $sql] $args]
}
..."
invoked from within
"db_exec 0or1row $db $full_statement_name $sql"

And then:

[11/Nov/2009:20:34:17][6577.1123424592][-default:13-] Notice:

Installed Subsite, version 5.5.1.

Package enabled.
[11/Nov/2009:20:34:17][6577.1123424592][-default:13-] Notice: apm_invoke_callback_proc: invoking callback after-upgrade with command subsite::after_upgrade -from_version_name 5.4.2 -to_version_name 5.5.1
[11/Nov/2009:20:34:17][6577.1123424592][-default:13-] Error: POST http://33333/acs-admin/apm/packages-install-4?
referred by "http://33333/acs-admin/apm/packages-install-3";
no such array: apm_subsite_packages_list
while executing
"nsv_get apm_subsite_packages_list package_keys"
(procedure "subsite::package_keys" line 2)
invoked from within
"subsite::package_keys"

add_missing_from = ON or true, whatever PG expects, in postgresql.conf.

While you are there check the other compatibility flags to make sure they are set correctly.