Forum OpenACS Q&A: load-data-model errors

I'm getting a bunch of errors when I use the load-data-model file:
ERROR: DefineQueryRewrite: rule plan string too big.
ERROR: Tuple is too big: size 11588
ERROR: ProcedureCreate: procedure plpgsql_call_handler already exists with same arguments
ERROR: parser: parse error at or near "interval"
What am I missing? load-geo-data worked fine...

Looks like that didn't solve all of the errors -- I can't log in as system (invalid password, changeme) and I can't register as a new user: Error: Ns_PgExec: result status: 7 message: ERROR:  Cannot insert a duplicate key into unique index users_preferences_pkey

What now?

"I found the problem. The current version of ACS/pg requires Postgresql 7.0; this was causing the problems. The documentation should probably be updated. "

The documentation needs update, yes, but the necessary updates that I know of, are really minor. The documentation was updated for beta 2 and here is what it says:

"This is the last ACS/pg release in which PG 6.5 will be supported. Our next releases will use the more advanced PG 7 features that are critical to ACS/pg (such as lztext which will allow bigger comments, static pages, etc.) Upgrading from PG
6.5 to PG 7 is not too hard although you'll need to pg_dump and restore your database plus an initdb. So consider yourself warned ! " (Section 5.1)

The following line is the one that needs update:

"edit the file load-data-model.sql. Comment the line i postgres65.sql if you are running PG 7 (comment with " --" not " #" )." (Section 5.4)

I didn't know, but the line mentioned above is now commented out, so you should uncomment if you are running PostgreSQL 6.5. I apologize for that.

Try this:
<p>
psql universe<br>
select user_id from users_preferences;<br>
<p>
and tell us what you get, OK?
<p>
The error is telling you that you already have a user_preferences record with a user_id set to "3", which is weird.  "system" and "anonymous" are the two initial users.
<p>
One possibility is that during the fold-in/re-port of 3.2 two copies of the insert ended up in the script...I've not downloaded 3.2 yet (I've set aside Friday to do that) so don't have the source available to peek at right now.

This is very strange, I just cleared out my acspg and downloaded the latest CVS tree, and had no problem creating myself as a user...

I'm not using the very latest PG beta, though...I think there are others who are, though.

Anyone else have an idea as to what's happening, here?

Check your users_preferences tables and see what's in there. There shouldn't be anything.

Are you sure the datamodel loaded right ? Did you dump the output of the loading to a file and checked it for errors ? That's pretty much the only way to check for errors, for as when you are loading it it goes by too fast.

Make sure all your datamodel was loaded right. If some trigger or plpgsql function was not created, then you'll have problems.

Did you try adding yourself after you added the user as superuser ? You mean you logged in as system and added a user through the admin pages right ?

If you're open to extreme measures and have a full-time 'net connection and are willing to open up...I'm willing to telnet in and take a look (tomorrow).

E-mail me privately, of course, if you're open to this.  I'm extremely  curious.

OK, I've played around with Shalon's system.  He's uncovered a bug which I think is probably present in the ACS/Oracle version, too.

In his .ini file he uncommented the line:

;RequiredUserTable=users_preferences

In spam.sql, there's a trigger function on inserts into "users".  In the Oracle version, it tries to first update users_preferences() and if that fails, inserts into users_preferences.  I didn't see how to do  that easily in PL/pgSQL so I just did the insert, after checking the scripts that make a new user.  They always insert into users before touching extra tables.

When Shalon uncommented the .ini line, that caused user-new-2.tcl to do a "insert into users_preferences(user_id) values($user_id)", but the trigger mentioned above had already done an insert, thus the duplicate key error.

It sure looks to me like the Oracle version does stuff in the same order, because the script's hardwired (and I checked the current Oracle 3.2 sources and they're the same).  In other words, the genuine PL/SQL version's check to see if the entry exists in order to avoid a dupe fails, because it is called first, with the script doing its insert afterwards.

The key - "don't do that, Shalon!" :)  For now, at least - I'll enter a ticket on this.  It may be that the offending line wants to be removed altogether from the .ini/.tcl files to avoid confusing folks.

Oops, sorry, I didn't get the same error, here is a sample of the errors I got:

[19/Apr/2000:06:45:15 -0700] Error: Ns_PgExec: result status: 7 message: ERROR: Cannot insert a duplicate key into unique index sec_browser_properties_pkey [18/Apr/2000:08:49:29 -0700] Error: Ns_PgExec: result status: 7 message: ERROR: Cannot insert a duplicate key into unique index referer_log_pkey [19/Apr/2000:03:30:04 -0700] Error: Ns_PgExec: result status: 7 message: ERROR: Relation 'user_preferences' does not exist

Yes, I am going to update my site to beta 3 this weekend. No real point into troubleshooting this version. In addition, I don't even know if this affected the operation of the website.