I'd go for filing a bug on web2py's issues and just removing the constraint
on your archive tables. nothing should break.
I think that the code creating the archive tables is just the one used to
create the original one, so the archive table gets created with the same
constraint as the original, and of course it goes into exception if it
tries to archive a record that doesn't have the "link" to a valid auth_user
row.
Right now the only thing that gets modified from the original table is the
unique constraint. We should add in the code a similar exception for
reference fields, although keeping a "row version" with a "non-existant row
reference" kinda breaks the deal if you don't archive also the "referenced"
table.
However, smart people should take that into account in their own code, so I
think it can be a "safe thing to do".
On Monday, June 10, 2013 5:56:09 PM UTC+2, Lamps902 wrote:
>
> I've asked this one before, but didn't get an answer; perhaps I was being
> too vague, so I'll give it another shot with more detail:
>
> I've got a primary postgresql database (db1), and I've set up a separate
> postgresql database (db1_archive) used for archiving several of the tables
> from db1. This functionality is implemented in a module with the following:
> class db_archives():
>
> def __init__(self, request, response, db1_archive, db1, auth):
>
> ...
>
> auth.enable_record_versioning(
> archive_db = db1_archive,
> tables = [db1.auth_user, db1.table1, db1.table2, ...]
> archive_names='archive_%(tablename)s'
> )
>
>
> When something is deleted from the relevant tables in db1, it should
> automatically be archived in db1_archive. However, upon deletion, the
> following psycopg2.IntegrityError is produced instead:
>
> insert or update on table "archive_table1" violates foreign key
> constraint "archive_table1_created_by_fkey" DETAIL: Key (created_by)=(9) is
> > not present in table "auth_user"
>
> I guess what's happening is that the archive DB is not finding a user with
> an ID of 9. The 'created_by' field has the following constraint:
>
> FOREIGN KEY (created_by) REFERENCES auth_user(id) ON DELETE CASCADE
>
> I'd like to remove all such constraints from the archive db (will doing so
> have any potentially dangerous effects?). I think this can be done with
> web2py's *[database_name].executesql() *function. However, I've tried a
> few things such as db1_archive.executesql('SET FOREIGN_KEY_CHECKS=0;'), and
> only ended up with error messages. Can anyone advise me on some SQL or
> web2py codes I could try to fix the situation? Thanks.
>
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
