[sqlalchemy] Re: Column to default to itself
On Apr 13, 1:47 pm, "Koen Bok" <[EMAIL PROTECTED]> wrote:
> request_table = Table('request', metadata,
> Column('id', Integer, primary_key=True),
> Column('number', Integer, unique=True, nullable=True,
> default=text('(SELECT coalesce(max(number), 0) + 1 FROM
> request)')))
>
> This seems to work well. But is this a good way to do this or can it
> cause complications?
This will start to throw duplicate key errors under heavier load.
There is a window of time between insert and commit when another
insert will get the same duplicate id. It might not be a problem in
your case, but I have had to deal with a similar problem when creating
sequentally hashed database table (for provable temporal ordering and
integrity) which will see very high loads. You'll either occasionally
get duplicate values in the database or you have to serialize all
inserts. The best that can be done, is try to occasionally get holes
and assume that usually transactions succeed and catch rollbacks. That
is a lot harder.
Ants
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Column to default to itself
request_table = Table('request', metadata,
Column('id', Integer, primary_key=True),
Column('number', Integer, unique=True, nullable=True,
default=text('(SELECT coalesce(max(number), 0) + 1 FROM
request)')))
This seems to work well. But is this a good way to do this or can it
cause complications?
On Apr 13, 11:23 am, "Koen Bok" <[EMAIL PROTECTED]> wrote:
> Ok, I'd rather handle it on the database level. Is that just a matter
> of creating a function and calling it on insert?
>
> Koen
>
> On Apr 13, 4:47 am, "Ants Aasma" <[EMAIL PROTECTED]> wrote:
>
> > On Apr 13, 2:47 am, Jorge Godoy <[EMAIL PROTECTED]> wrote:
>
> > > IF you insist on doing that at your code, make the column UNIQUE (or a
> > > PK...) and write something like this pseudocode:
>
> > > def save_data():
> > > def insert_data():
> > >try:
> > >unique_column_value = get_max_from_unique_column
> > >Class(unique_column_value + 1, 'other data')
> > >except YourDBExceptionForConstraintViolation:
> > >sleep(random.random())
> > >insert_data()
>
> > > The 'sleep(random.random())' is there to avoid constant clashes and to
> > > be "fair" to all connections that are inserting data on your table.
>
> > To get an uninterrupted number sequence you need to serialize your
> > inserts to that specific entity, for which you basically need locking.
> > The quoted approach is optimistic locking, where you hope that no one
> > tries to insert another row between when you use the
> > get_max_from_unique_column and do the database commit, but are ready
> > to retry if that expectation fails. Another way would be to use
> > pessimistic locking, by doing the get_max_from_unique_column query
> > with lockmode='update'. Then any other thread trying to insert another
> > row while you're busy inserting yours will have to wait.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Column to default to itself
On Apr 13, 12:23 pm, "Koen Bok" <[EMAIL PROTECTED]> wrote:
> Ok, I'd rather handle it on the database level. Is that just a matter
> of creating a function and calling it on insert?
You need a sequence that has locks and rolls back on rollback.
Simplest way is to use a table: CREATE TABLE sequences (name
varchar(100) not null primary key, value int not null default 0);
and a stored proc to get the next id, in postgresql that would be
CREATE FUNCTION next_id(varchar) RETURNS int AS $$
DECLARE id int;
BEGIN
UPDATE sequences SET value = value + 1 WHERE name = $1;
SELECT value INTO id FROM sequences WHERE name = $1;
RETURN id;
END
$$ LANGUAGE plpgsql;
Then say you have table bar, you initialize the sequence:
INSERT INTO sequences ('bar', 0);
And create the table:
CREATE TABLE bar (bar_id int NOT NULL DEFAULT next_id('bar') PRIMARY
KEY, ...);
or in SA:
Table('bar',
Column('id', Integer, primary_key=True,
default=func.next_id('bar')), ...
)
Ants
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Column to default to itself
Ok, I'd rather handle it on the database level. Is that just a matter of creating a function and calling it on insert? Koen On Apr 13, 4:47 am, "Ants Aasma" <[EMAIL PROTECTED]> wrote: > On Apr 13, 2:47 am, Jorge Godoy <[EMAIL PROTECTED]> wrote: > > > IF you insist on doing that at your code, make the column UNIQUE (or a > > PK...) and write something like this pseudocode: > > > def save_data(): > > def insert_data(): > >try: > >unique_column_value = get_max_from_unique_column > >Class(unique_column_value + 1, 'other data') > >except YourDBExceptionForConstraintViolation: > >sleep(random.random()) > >insert_data() > > > The 'sleep(random.random())' is there to avoid constant clashes and to > > be "fair" to all connections that are inserting data on your table. > > To get an uninterrupted number sequence you need to serialize your > inserts to that specific entity, for which you basically need locking. > The quoted approach is optimistic locking, where you hope that no one > tries to insert another row between when you use the > get_max_from_unique_column and do the database commit, but are ready > to retry if that expectation fails. Another way would be to use > pessimistic locking, by doing the get_max_from_unique_column query > with lockmode='update'. Then any other thread trying to insert another > row while you're busy inserting yours will have to wait. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Column to default to itself
On Apr 13, 2:47 am, Jorge Godoy <[EMAIL PROTECTED]> wrote: > IF you insist on doing that at your code, make the column UNIQUE (or a > PK...) and write something like this pseudocode: > > def save_data(): > def insert_data(): >try: >unique_column_value = get_max_from_unique_column >Class(unique_column_value + 1, 'other data') >except YourDBExceptionForConstraintViolation: >sleep(random.random()) >insert_data() > > The 'sleep(random.random())' is there to avoid constant clashes and to > be "fair" to all connections that are inserting data on your table. To get an uninterrupted number sequence you need to serialize your inserts to that specific entity, for which you basically need locking. The quoted approach is optimistic locking, where you hope that no one tries to insert another row between when you use the get_max_from_unique_column and do the database commit, but are ready to retry if that expectation fails. Another way would be to use pessimistic locking, by doing the get_max_from_unique_column query with lockmode='update'. Then any other thread trying to insert another row while you're busy inserting yours will have to wait. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Column to default to itself
"Koen Bok" <[EMAIL PROTECTED]> writes: > I need to have a uninterrupted number sequence in my table for > invoices. I was trying to do it like this, but I can't get it to work. > Can anyone give me a hint? Let your database do the job. It is always aware of all connections made to it, their contexts, their priorities, what transaction isolation level is being used, etc. It will be better on this task. IF you insist on doing that at your code, make the column UNIQUE (or a PK...) and write something like this pseudocode: def save_data(): def insert_data(): try: unique_column_value = get_max_from_unique_column Class(unique_column_value + 1, 'other data') except YourDBExceptionForConstraintViolation: sleep(random.random()) insert_data() The 'sleep(random.random())' is there to avoid constant clashes and to be "fair" to all connections that are inserting data on your table. -- Jorge Godoy <[EMAIL PROTECTED]> --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
