change Oracle sequence on insert

[Ofir Herzas]

I have a table with the following column:

id = sa.Column(sa.Integer, sa.Sequence('id_seq'), primary_key=True, nullable=False)

Usually, I have no problems inserting data, but every time I insert rows with specific id, it causes problems with Oracle since the sequence is not modified accordingly.

For example, assuming that the table is new and the sequence starts at 1, if I insert a row specifying id=2, the sequence doesn't change which will cause the next insert to fail.

I do understand that Oracle does not support auto increment but what is the proper way of handling this under sqlalchemy? Do I need to manually change the sequence after such insert statement? can I bind to an event or use any other magic to make it work like other dialects? (choose max(id)+1)

[Michael Bayer]

the case where you have a sequence used for a table and at the same time you have the need to insert rows with specific identifiers as a normal matter of course (as opposed to when you need to do a bulk insert as part of database maintenance) is an unusual one.   Most database folks would ask why that’s the use case you have.    Surrogate primary keys are not supposed to be meaningful, you normally would just let the sequence handle creation of new values 100% of the time.    Because they increment atomically, you never have to worry about two primary  key identifiers conflicting.   If you’re working around that then you can’t be assured of integrity violations within concurrent scenarios.

Short answer yes if you are inserting values directly then you need to update the sequence manually, on oracle i think it might be ALTER SEQUENCE or something like that.   It’s not the kind of thing that would scale, though.

[Ofir Herzas]

Thanks Michael,

You of course is right and I rarely have to use this method.

Having said that, once in a while, if I need to migrate a version or do some maintenance, I do need that option.

How would I do that in sqlalchemy? Do I have to use raw SQL for that? Why would that not scale?

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/1hjzce5kg3Q/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Michael Bayer]

raw SQL and it wouldn’t scale because you probably cannot emit two such ALTER statements concurrently, and it probably doesn’t run very fast either.

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_2012.htm

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email tosqlalchemy+...@googlegroups.com.