Forum OpenACS Development: Handling Double Submits...

Posted by Alex Kroman on
So one of the more common errors I get on my installation of OpenACS is someone submitting a form and then a few minutes later clicking back a few times, editing their form and submitting it again. The error message they get is

ERROR:  duplicate key violates unique constraint "acs_objects_pk"

This is because OpenACS is trying to create a new object with an ID that has already been used.

Is there a way to get ad_form to run the edit_data declaration in this situation or at the very least make it display a nice message?

Here is my ad_form block:

ad_form -name add_edit \
    -cancel_url "opportunities" \
    -form {
    } -new_data {
    } -edit_data {
Posted by Jose Pablo Escobedo Del Cid on

I've had the very same problem before, and the only solution that have worked for me have been to add a "when not exists" in the insert statement. Like this:

insert into table (key_id)
(select (:key_id_var)
 where not exists
 (select 1 from table where key_id = :key_id))
or just
select create__new(:key_id)
where not exists (select 1 from table where key_id = :key_id)

I have tried other approaches, like generating the object_id in another page (a confirmation one, for instance), but there are allways some cases when it fails (if something can go wrong will go wrong ;o)

Of course a better approach would be to somehow improve the already existing API of the ad_form or the acs_objects to avoid this problem...

Posted by Alex Kroman on
Maybe the right answer is just ditching duplicate detection? Every application I've used on the Internet will create a duplicate if you go back and submit the 'create new item' form again....
Posted by Malte Sussdorff on
If you come up with a solution, I will volontarily add this to project manager and contacts. God, it is really boring to explain users to

a) Not use the backbotton when creating a form entry.
b) and if they do, hit reload to get the next object_id.

Talking about reload, has anyone managed to convince IE6 to keep the data entered in the RTE when you go back/forth or reload a page?

Posted by Dave Bauer on

No, checking if the item exists before trying to create it is the correct solution. I thought that ad_form was able to detect if a user clicked submit twice and process it accordingly. Apparently it is possible to use the back button and submit again and process the _new_data block again.

Hoepfully Don will read this and give us some advice!

Posted by Jade Rubick on
What I do is write PL/SQL that does this

insert into foo;
exception when dup_val_on_index
update foo;

Posted by Don Baccus on
The error message isn't coming from ad_form, it's coming from your code in -new_data.

So ... use db_transaction {} on_error { do something to give a smart error message }. Select to see if the object already exists, for instance. I greatly prefer doing this select statement when there's an error rather than doing so before the insert as was suggested above, because you only incur the cost of the extra db operation when you need it, i.e. when things fail.

You can also just cheat and assume the most likely cause of the error was a dupe. Give an error message saying "perhaps you used the back arrow to resubmit the form, causing a duplicate entry - here's the error the database gave us, though". Depends on how user-friendly you want to be.

There's really no way for ad_form to run the -edit_data code in this case. It uses its internal form vars to figure out what's going on.

Jade's suggest works on Oracle if updating not inserting is what you want to do. There's no equivalent in PG.

If you want to allow dupe entries, don't use the "key" feature of ad_form, and us on_submit rather than new_data and allocate a new object_id each time the form's processed.

In this case, of course, you'd need separate add/edit pages.

In some sense, using the ad_form "key" feature and its support of new_data and edit_data makes it easier to keep these pages uniform (because they're the same form) but at the expense of perhaps slightly more confusing error messages.

Posted by Alfred Werner on
Aren't we just discussing an upsert? Oracle has had a merge command since 9i. It's much faster since it is built into the oracle kernel and the optimizer is aware of it.
Posted by Pascal Byrne on
A simple client side fix would be to disable the submit button using a piece of Javascript attached to the onSubmit action.

This of course doesn't protect against the same page being open in two browser windows or legacy browsers and is no substitute for proper server-side validation.