CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Vitalii Ponomar
Vitalii Ponomar

Reputation: 10936

Sqlalchemy and PostgreSql: how to set primary key manually without conflicts in future?

I have such table:

class Tag(Base):
    __tablename__ = 'tag'

    id = Column(Integer, primary_key=True)
    name = Column(Unicode(50), nullable=False)

    def __init__(self, name):
        self.name = name

Supose table is empty. Than I insert some data in such way:

t = Tag('first tag')
t.id = 2
dbsession.add(t)
dbsession.commit()

And it's ok: I have one row in my postgresql table with id = 2. Next:

t = Tag('second tag')
dbsession.add(t)
dbsession.commit()

Than I have 2 rows: {id: 1, name: 'second tag'} and {id: 2, name: 'first_tag'}

Next (final step):

t = Tag('third tag')
dbsession.add(t)
dbsession.commit()

IntegrityError: (IntegrityError) duplicate key value violates unique constraint "tag_pkey"

(it wants to create row with id = 2, but it is engaged already with first tag)

Is it possible somehow to say to postgres: if such pkey is exist, try next until it will be available?

It is very usefull when using dumps.

Thanks in advance!

Upvotes: 3

Views: 4380

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324455

I'm not aware of any safe, efficient way to do what you want. You should really pick whether you want to define the keys yourself or use generated keys and stick to one strategy or the other.

If you don't mind terrible concurrency, you can LOCK TABLE thetable, do your work, setval the table's identifier sequence to the next free value after what you inserted, and commit to release the lock. However, that will still cause issues with apps that explicitly call nextval (like many ORMs) rather than letting the database define the value by omitting it from the ?INSERT column list or explicitly naming it as DEFAULT.

Otherwise you could have your code (or a PL/PgSQL helper function) do the insert in a retry loop that increments the key and tries again when it gets an integrity error. This strategy won't work if you need to do more than just the one insert per transaction. Furthermore in SERIALIZABLE isolation mode I don't think you can do it with PL/PgSQL, you have to use a client-side retry loop to handle serialization failures.

It's a terrible idea. Either use application defined keys consistently, or database-defined keys consistently. Don't mix the two.

Upvotes: 4

Related Questions