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 assignment
SQL:

        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

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

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!