Forum OpenACS Development: Error copying survey from one subsite to another

1: Error copying survey from one subsite to another

Posted by Esti Alvarez on 02/07/05 05:01 PM

Hi!

I want to copy a survey (complex-suvey v0.6) from one subsite to another using the survey_copy proc provided by the package, -new_package_id being the package_id of the target survey package, and I get the following error message:

ERROR:  null value in column "tree_sortkey" violates not-null constraint
CONTEXT:  PL/pgSQL function "acs_object__new" line 24 at SQL statement
PL/pgSQL function "survey_question__new" line 26 at assignmentSQL:
        SELECT survey_question__new (
            NULL,
            '8692',
            '1',
            'first question',
            'shorttext',
            't',
            't',
            'textbox',
            'small',
            'beside',
            'f',
            NULL,
            NULL,
            NULL,
            '448',
            '8692',
            NULL,
            NULL,
            NULL,
            NULL,
            NULL
        );

As you see, the problem rises when it tries to copy the questions, because when creating the corresponding object, it tries to set a null tree_sortkey.

I've been digging into the code and have found that the place to set the value for this tree_sortkey is the trigger acs_objects_insert_tr. The piece of code that fails to retrieve a correct value for tree_sortkey is this:

  SELECT tree_sortkey, tree_increment_key(max_child_sortkey)
    INTO v_parent_sk, v_max_child_sortkey
  FROM acs_objects
  WHERE object_id = new.context_id
  FOR UPDATE;

where new.context_id is the section_id of the new survey,
and returns null for both v_parent_sk, v_max_child_sortkey.

If I comment in survey_copy the lines involving question copying, the survey and it's sections are copied without problem, and the above select returns not null values. I mean, it seems the problem is that it starts copying the question before the trigger has set the correct tree_sortkey for the parent section (I know, it sounds very inconsistent).

Does anybody know what can be going on?

Note: using the same proc (suvey_copy) to copy surveys within the same survey instance (passing no -new_package_id) works fine, don't know if this helps in the diagnose.

Thanks in advance

2: Re: Error copying survey from one subsite to another (response to 1)

Posted by Jeff Davis on 02/07/05 05:12 PM

It is probably the order in which the objects are being copied. tree_sortkey has a not null constraint which means it can't be
null if the new.context_id acs_object already exists.
(that or the new.context_id is not set properly and is null or something).

I am pretty sure the acs_objects_insert_tr function is correct.

3: I was nesting transactions (response to 2)

Posted by Esti Alvarez on 02/07/05 10:44 PM

After 2 days trying anything you can think of, I've found what I was doing wrong: as I wanted to copy a bunch of surveys from one place to another, I had enclosed the function calls in a db_transaction creating another level of nested transactions with those ongoing inside the functions themselves. Removing the db_transaction, the problem dissapeared.

I hope this can save someone else's time.

Thanks Jeff! I was also sure the trigger definition was ok 😊

4: Re: Error copying survey from one subsite to another (response to 1)

Posted by Deds Castillo on 02/08/05 02:34 AM

It's better if you extract the id's into a list and do a foreach so you can still preserve the transaction. (if it's really needed). Another thing to look at is increasing the stacksize in the config file. We've been hit by this a couple of times where I thought it was the nested transactions but it was actually a small stack.

5: Re: Re: Error copying survey from one subsite to another (response to 4)

Posted by Esti Alvarez on 02/08/05 09:29 AM

It's better if you extract the id's into a list and do a foreach so you can still preserve the transaction

I was already doing it like this because otherwise I got a "Ran out of db pools" error message, and still didn't work.

About the stack size, you mean the one in the ns/thread section in config.tcl, right? I'm setting different values and doesn't work neither, but it's a very interesting comment, I'll have a closer look at it. Thank you!