> one immediate flaw I see in your application is that you are using
> implicit execution of SQL constructs without them having any
> connection to the ongoing transaction, such as:
>
> f_table.delete(f_table.c.user_id==theone.id).execute()
> f_table.insert().execute(user_id=theone.id, path='/', ls=ls)
I've replaced with this :
session.execute(delete(f_table), {'user_id': theone.id})
session.execute(insert(f_table), {'user_id':
theone.id,
'path':
'/',
'ls': ls})
session.commit()
> the easiest solution would be to just turn on the "threadlocal=True"
> flag on your engine, which means that all connection requests in the
> same thread use the same connection and transaction.
db = create_engine("postgres://...", strategy="threadlocal")
> So a step like that should help, but the design of your application
> still seems to require random deletes and updates of data in different
> threads, and youre also using some really esoteric features such as
> SAVEPOINT transactions (i.e. via the begin_nested()). If you're using
> all that and having trouble figuring out how to arrange concurrency
> properly, its possible that you'd need to simplify your application a
> bit.
But how to determine if record is exists and update if it does or
insert instead without executing select, which would be very slow ?
I'm using nested begin_nested() here to avoid rollback of whole
transaction.
> If its really just a multithreaded daemon I'd might even try
> using normal threading.Lock objects to limit access to critical
> mutable structures.
I'll try, but now cannot get rid of this error, which happens every
time:
Exception in thread Thread-3:
Traceback (most recent call last):
File "threading.py", line 442, in __bootstrap
self.run()
File "./camper.py", line 126, in run
walk(session, theone, root)
File "./camper.py", line 85, in walk
stuff =
session.query(Path).select_from(f_table.join(u_table)).filter(User.c.id==theone.id).filter(Path.c.path==relpath).first()
File "/usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py",
line 627, in first
ret = list(self[0:1])
File "/usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py",
line 656, in __iter__
return self._execute_and_instances(context)
File "/usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py",
line 659, in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self.mapper,
instance=self._refresh_instance)
File "/usr/lib/python2.4/site-packages/sqlalchemy/orm/session.py",
line 528, in execute
return self.__connection(engine,
close_with_result=True).execute(clause, params or {})
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py",
line 796, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py",
line 847, in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) > 1), distilled_params=params)
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py",
line 859, in _execute_compiled
self.__execute_raw(context)
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py",
line 871, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py",
line 887, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
ProgrammingError: (ProgrammingError) current transaction is aborted,
commands ignored until end of transaction block
'SELECT fs_file.id AS fs_file_id, fs_file.user_id AS fs_file_user_id,
fs_file.path AS fs_file_path, fs_file.ls AS fs_file_ls \nFROM fs_file
JOIN auth_user ON auth_user.id = fs_file.user_id \nWHERE auth_user.id
= %(auth_user_id)s AND fs_file.path = %(fs_file_path)s ORDER BY
fs_file.id \n LIMIT 1 OFFSET 0' {'fs_file_path': '/', 'auth_user_id':
1}
the context :
try:
session.execute(insert(f_table), {'user_id': theone.id,
'path': relpath,
'ls': ls})
except IntegrityError:
stuff = session.query(...) #line 85
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
