Re: isolation level serializable

On Mar 21, 3:00 pm, Chuck <skilover_nos..._at_bluebottle.com> wrote:
> xhos..._at_gmail.com wrote:
> > Laurenz Albe <inv..._at_spam.to.invalid> wrote:
> >> Chuck <skilover_nos..._at_bluebottle.com> wrote:
> >>> I have a stored procedure that copies a bunch of tables from one schema
> >>> to another using "execute immediate 'insert...' " and code that looks
> >>> something like this...
>
> >>> for i in (select table_name from ....)
> >>> loop
> >>> stmt := 'insert into s1.'||table_name;
> >>> stmt := stmt||' (select * from s2.'||table_name')';
> >>> execute immediate stmt;
> >>> end loop;
>
> >>> Some of the tables have FK relationships between them on both the
> >>> source and destination schemas. The procedure uses a similar loop early
> >>> on to disable all constraints on the destination tables, and another
> >>> later to enable them again after the inserts are done. The constraints
> >>> stay enabled the entire time on the source tables.
>
> >>> The problem is that I am getting FK constraint violations when I try to
> >>> enable the constraints on the destination tables. I thought I could
> >>> eliminate this by using a serializable isolation level for the
> >>> transaction. According to the manual, this will cause all queries to
> >>> look at a snapshot of the source tables from the same point in time, so
> >>> I execute this before the first insert.
>
> >>> commit;
> >>> set transaction isolation level serializable;
>
> > I don't like that. Each transaction should commit or rollback itself when
> > it is done. I don't think it should be committed implicitly by the start
> > of some other transaction.
>
> That's precisely what I am doing. The commit is only there because prior
> to bulk of the inserts is another insert into a log table. It just says
> "I'm at such and such point in the procedure". Prior to that was a bunch
> of truncates which do implicit comiits. If I don't commit at this point,
> the "set transaction" will fail because it must be the first command of
> the transaction.
>
> >>> Then I do another commit at the end of the last insert. There are no
> >>> commits, rollbacks, DDL, or any other SQL between the inserts, however
> >>> I still get FK violations at the end when I try enable the constraints.
> >>> Why?
>
> > To know why, it would help to know what. What is the problem that is
> > causing the violation?
>
> As stated in the OP, it is a foreign key constraint violation on one the
> destination tables. A child row has no parent.
>
> > Are you absolutely sure that the corresponding
> > constraint is in place and enabled and set to validate on the source table?
>
> Yes. Absolutely.

Is there some ordering necessary in the enabling of the constraints? I'm thinking you have a unique index that needs to be enabled before a foreign key? What are the exact errors?

jg

--
@home.com is bogus.
http://www.rotten.com/today/