Thanks a lot, Niphlod! I'll go ahead and file it.
On Monday, June 10, 2013 2:29:38 PM UTC-5, Niphlod wrote:
>
> 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.
