Re: [sqlalchemy] Re: SQLA pg upsert causes impl rollback, works thru CL query
Jonathan: Yeah. That seems likely. However, I feel "closer" to my database after removing zope.sqlalchemy from the mix. I guess I'm at a point where there is such a thing as too much abstraction. . ? BTW, I did give the nested tx a try, but as we both now know it did not work, and probably for the reasons you cited. That extra notification to the transaction manager would likely have done the trick. For me, right now, it is best to simply remove the zope stuff before the app gets too big. While I hate to go backward in time, I also like SQLA so much precisely because it gives me so much control. A fair trade-off to my way of thinking. Persistence matters, and that's why some people might read this. Thanks again. . . On Wednesday, June 28, 2017 at 3:10:28 PM UTC-6, Jonathan Vanasco wrote: > > > On Wednesday, June 28, 2017 at 4:28:32 PM UTC-4, Mike Bayer wrote: >> >> I'm not 100% sure that zope.sqlalchemy unconditionally emits COMMIT >> for the session that's associated. Though overall would need to see >> where you're getting request.session from and all that; if it's not >> associated with zope.sqlalchemy then you'd need to call >> session.commit() explicitly. >> > > rephrasing Mike's reply slightly... as I (think I) know what's wrong now... > > zope.sqlalchemy only knows that you did something with the session if you > use the ORM. you're using sqlalchemy core. > > you need to do this: > > > > from zope_sqlalchemy import mark_changed > mark_changed(session) > > > > That will let zop.sqlalchemy know that you did something in the session > and it will vote a COMMIT. otherwise it thinks you did nothing. > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: SQLA pg upsert causes impl rollback, works thru CL query
This turned out to be pleasantly painless. Posted here for the sake of "the
next guy."
First, I removed all of the import transaction statements.
Next, I changed the models.__init__:
def get_tm_session(session_factory, transaction_manager):
"""
Get a ``sqlalchemy.orm.Session`` instance backed by a transaction.
This function will hook the session to the transaction manager which
will take care of committing any changes.
- When using pyramid_tm it will automatically be committed or aborted
depending on whether an exception is raised.
- When using scripts you should wrap the session in a manager yourself.
For example::
import transaction
engine = get_engine(settings)
session_factory = get_session_factory(engine)
with transaction.manager:
dbsession = get_tm_session(session_factory,
transaction.manager)
"""
dbsession = session_factory()
#zope.sqlalchemy.register(
#dbsession, transaction_manager=transaction_manager)
return dbsession
Then the relevant code, so that the native SQLA session is back:
if request.params.get("event_type", None) == "ssm_source":
this_time = time.time()
REG = request.registry
b_interval = float(REG.settings.get('batch_interval', .25))
group, port = request.params.get("group", ":").split(":")
source = request.params.get("source", None)
##
## POSTGRES goes here, route and write for all
## subscribers in tile_sub
##
sess = request.dbsession
##upsert for tile updates
subsq =
sess.query(TileSub).filter(TileSub.groupip==group).filter(TileSub.portnum==port)
updl = []
updstable = TileUpd.__table__
updtime = datetime.datetime.now()
#sess.begin_nested()
for tsub in subsq.all():
instmt = UPSERT(updstable).values(
hostname=tsub.hostname,
groupip=group,
portnum=int(port),
sourceip=source
).on_conflict_do_update(
constraint = updstable.primary_key,
set_ = dict(sourceip=source, timecreated=updtime, timesent
= None)
)
sess.execute(instmt)
* sess.commit()*
It now works like a charm.
Many thanks for the prompt and thoughtful replies.
On Wednesday, June 28, 2017 at 1:16:52 PM UTC-6, Richard Rosenberg wrote:
>
>
> Hello:
>
> I've run into a problem with SQLA's implementation of postgresql's upsert.
> The equivalent statement works fine when run as a straight up query (thru
> pgadmin).
>
> The model(s) in question:
>
> class TileUpd(Base):
> __tablename__ = 'tile_upd'
> __table_args__ = TARGS
> hostname = Column(PgD.TEXT, ForeignKey('edgenode.hostname'),
> primary_key=True)
> groupip = Column(PgD.INET, primary_key=True)
> portnum = Column(PgD.INTEGER, primary_key=True)
> sourceip = Column(PgD.INET, nullable=False)
> timecreated = Column(PgD.TIMESTAMP, nullable=False,
> server_default=text('current_timestamp'))
> timesent = Column(PgD.TIMESTAMP, nullable=True)
>
> class TileSub(Base):
> __tablename__ = 'tile_sub'
> __table_args__ = TARGS
> groupip = Column(PgD.INET, primary_key=True)
> portnum = Column(PgD.INTEGER, primary_key=True)
> sourceip = Column(PgD.INET, nullable=False)
> hostname = Column(PgD.TEXT, ForeignKey('edgenode.hostname'),
> nullable=False)
> timecreated = Column(PgD.TIMESTAMP, nullable=False,
> server_default=text('current_timestamp'))
>
> This is a pyramid app, so I (so far) am using the canned transaction
> manager, though I prefer to control commits explicitly.
>
>
> from sqlalchemy.dialects.postgresql import insert as UPSERT
>
> #scaler change loader/endpoint - no json, uses POST
> @view_config(route_name='loader', renderer='json')
> def loader_view(request: pyramid.request):
> """Waits for scaled tile information from scaler cluster and loads
> changes
> into postgresql"""
> logger.debug("loader view")
> if request.params.get("event_type", None) == "ssm_source":
> this_time = time.time()
> REG = request.registry
> b_interval = float(REG.settings.get('batch_interval', .25))
> group, port = request.params.get("group", ":").split(":")
> source = request.params.get("source", None)
> ##
> ## POSTGRES goes here, route
Re: [sqlalchemy] Re: SQLA pg upsert causes impl rollback, works thru CL query
tly. > > On Wed, Jun 28, 2017 at 3:43 PM, Jonathan Vanasco <jona...@findmeon.com > > wrote: > > > >> > >> On Wednesday, June 28, 2017 at 3:16:52 PM UTC-4, Richard Rosenberg > wrote: > > > > > > On Wednesday, June 28, 2017 at 3:16:52 PM UTC-4, Richard Rosenberg > wrote: > >> > >> > >> I am absolutely puzzled, but it seems likely that pyramid_tm is in the > way > >> somehow. It always wants to do its own thing, and calling commit > explicitly > >> is something it seems to abhor. My next step is to wrap this in: > >> > >> with transaction.manager as tx: > >> > >> But this is really not what I want. I'm tempted to rip out all of the > >> zopish stuff and go with SQLA's session, but before I try that, I > thought it > >> might be worthwhile to get some further information. > >> > > > > For a quick-fix, i'd try to run this bit within a nested > > transaction/savepoint: > > > > http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html > > > > > > I doubt the problem is in `pyramid_tm`. that package just wraps the > request > > in some logic to hook into the transaction package; all the real work is > > done by `zope.sqlalchemy`. The issue is possibly linked to your version > of > > `zope.sqlalchemy` or `pyscopg2` (or other driver). I'd try to update > > those... but from what I see here, you could probably just dump this > into a > > nested transaction, which will limit the scope of the rollback. > > > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > 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 to sqlalchemy+...@googlegroups.com . > > To post to this group, send email to sqlal...@googlegroups.com > . > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: SQLA pg upsert causes impl rollback, works thru CL query
Jonathan: Thanks for your reply. After perusing the link you provided, I'll give begin_nested a try. And you're quite right, its probably not pyramid_tm so much as the zope.sqlalchemy. But truthfully, I don't know why it is happening. The prior bulk insert pattern using sess.add_all worked fine, but upsert is the right thing to do here. I'll give begin_nested a try and post back. Thanks again, Richard On Wednesday, June 28, 2017 at 1:43:22 PM UTC-6, Jonathan Vanasco wrote: > > > >> On Wednesday, June 28, 2017 at 3:16:52 PM UTC-4, Richard Rosenberg wrote: >> > > On Wednesday, June 28, 2017 at 3:16:52 PM UTC-4, Richard Rosenberg wrote: >> >> >> I am absolutely puzzled, but it seems likely that pyramid_tm is in the >> way somehow. It always wants to do its own thing, and calling commit >> explicitly is something it seems to abhor. My next step is to wrap this in: >> >> with transaction.manager as tx: >> >> But this is really not what I want. I'm tempted to rip out all of the >> zopish stuff and go with SQLA's session, but before I try that, I thought >> it might be worthwhile to get some further information. >> >> > For a quick-fix, i'd try to run this bit within a nested > transaction/savepoint: > > http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html > > > I doubt the problem is in `pyramid_tm`. that package just wraps the > request in some logic to hook into the transaction package; all the real > work is done by `zope.sqlalchemy`. The issue is possibly linked to your > version of `zope.sqlalchemy` or `pyscopg2` (or other driver). I'd try to > update those... but from what I see here, you could probably just dump this > into a nested transaction, which will limit the scope of the rollback. > > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: SQLA pg upsert causes impl rollback, works thru CL query
Oops. . .wrong log entries for the postgres log. They should look like this: 2017-06-28 12:02:39.547 MDT [4456] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:02:39.548 MDT [4456] richard@stemp LOG: unexpected EOF on client connection with an open transaction 2017-06-28 12:07:51.871 MDT [4756] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:07:51.874 MDT [4757] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:07:51.874 MDT [4755] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:07:51.877 MDT [4758] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:10:51.732 MDT [5014] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:10:51.734 MDT [5015] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:10:51.738 MDT [5012] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:10:51.745 MDT [5013] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:16:23.471 MDT [5515] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:16:23.476 MDT [5514] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:16:23.476 MDT [5513] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:16:23.478 MDT [5516] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:22:29.631 MDT [5902] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:22:29.634 MDT [5903] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:22:29.635 MDT [5904] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:22:29.636 MDT [5905] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:27:21.487 MDT [6255] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:27:21.491 MDT [6253] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:27:21.493 MDT [6256] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:27:21.499 MDT [6254] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:27:45.262 MDT [6339] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:27:45.262 MDT [6338] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:27:45.264 MDT [6340] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:27:45.269 MDT [6341] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:34:13.243 MDT [6701] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:34:13.245 MDT [6699] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:34:13.248 MDT [6700] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:34:13.252 MDT [6702] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:43:06.143 MDT [7177] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:43:06.145 MDT [7179] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:43:06.150 MDT [7178] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:43:06.150 MDT [7176] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:43:06.150 MDT [7176] richard@stemp LOG: unexpected EOF on client connection with an open transaction 2017-06-28 12:55:12.279 MDT [7843] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:55:12.285 MDT [7844] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:55:12.286 MDT [7841] richard@stemp LOG: could not receive data from client: Connection reset by peer 2017-06-28 12:55:12.288 MDT [7842] richard@stemp LOG: could not receive data from client: Connection reset by peer On Wednesday, June 28, 2017 at 1:16:52 PM UTC-6, Richard Rosenberg wrote: > > > Hello: > > I've run into a problem with SQLA's implementation of postgresql's upsert. > The equivalent statement works fine when run as a straight up query (thru > pgadmin). > > The model(s) in question: > > class TileUpd(Base)
[sqlalchemy] SQLA pg upsert causes impl rollback, works thru CL query
Hello:
I've run into a problem with SQLA's implementation of postgresql's upsert.
The equivalent statement works fine when run as a straight up query (thru
pgadmin).
The model(s) in question:
class TileUpd(Base):
__tablename__ = 'tile_upd'
__table_args__ = TARGS
hostname = Column(PgD.TEXT, ForeignKey('edgenode.hostname'),
primary_key=True)
groupip = Column(PgD.INET, primary_key=True)
portnum = Column(PgD.INTEGER, primary_key=True)
sourceip = Column(PgD.INET, nullable=False)
timecreated = Column(PgD.TIMESTAMP, nullable=False,
server_default=text('current_timestamp'))
timesent = Column(PgD.TIMESTAMP, nullable=True)
class TileSub(Base):
__tablename__ = 'tile_sub'
__table_args__ = TARGS
groupip = Column(PgD.INET, primary_key=True)
portnum = Column(PgD.INTEGER, primary_key=True)
sourceip = Column(PgD.INET, nullable=False)
hostname = Column(PgD.TEXT, ForeignKey('edgenode.hostname'),
nullable=False)
timecreated = Column(PgD.TIMESTAMP, nullable=False,
server_default=text('current_timestamp'))
This is a pyramid app, so I (so far) am using the canned transaction
manager, though I prefer to control commits explicitly.
from sqlalchemy.dialects.postgresql import insert as UPSERT
#scaler change loader/endpoint - no json, uses POST
@view_config(route_name='loader', renderer='json')
def loader_view(request: pyramid.request):
"""Waits for scaled tile information from scaler cluster and loads
changes
into postgresql"""
logger.debug("loader view")
if request.params.get("event_type", None) == "ssm_source":
this_time = time.time()
REG = request.registry
b_interval = float(REG.settings.get('batch_interval', .25))
group, port = request.params.get("group", ":").split(":")
source = request.params.get("source", None)
##
## POSTGRES goes here, route and write for all
## subscribers in tile_sub
##
sess = request.dbsession
##upsert for tile updates
subsq =
sess.query(TileSub).filter(TileSub.groupip==group).filter(TileSub.portnum==port)
updl = []
updstable = TileUpd.__table__
updtime = datetime.datetime.now()
for tsub in subsq.all():
instmt = UPSERT(updstable).values(
hostname=tsub.hostname,
groupip=group,
portnum=int(port),
sourceip=source
).on_conflict_do_update(
constraint = updstable.primary_key,
set_ = dict(sourceip=source, timecreated=updtime)
)
sess.execute(instmt)
transaction.commit()
The statements appear to be well formed, but SQLA logs show a rollback for
every call to execute:
2017-06-28 12:55:12,136 INFO
[sqlalchemy.engine.base.Engine:679][MainThread] BEGIN (implicit)
2017-06-28 12:55:12,137 INFO
[sqlalchemy.engine.base.Engine:1140][MainThread] SELECT tile_sub.groupip AS
tile_sub_groupip, tile_sub.portnum AS tile_sub_portnum, tile_sub.sourceip
AS tile_sub_sourceip, tile_sub.hostname AS tile_sub_hostname,
tile_sub.timecreated AS tile_sub_timecreated
FROM tile_sub
WHERE tile_sub.groupip = %(groupip_1)s AND tile_sub.portnum = %(portnum_1)s
2017-06-28 12:55:12,137 INFO
[sqlalchemy.engine.base.Engine:1143][MainThread] {'groupip_1':
'239.129.237.126', 'portnum_1': '55002'}
2017-06-28 12:55:12,139 INFO
[sqlalchemy.engine.base.Engine:1140][MainThread] INSERT INTO tile_upd
(hostname, groupip, portnum, sourceip) VALUES (%(hostname)s, %(groupip)s,
%(portnum)s, %(sourceip)s) ON CONFLICT ON CONSTRAINT pk_tile_upd DO UPDATE
SET sourceip = %(param_1)s, timecreated = %(param_2)s
2017-06-28 12:55:12,139 INFO
[sqlalchemy.engine.base.Engine:1143][MainThread] {'hostname':
'localhost:8080', 'groupip': '239.129.237.126', 'portnum': 55002,
'sourceip': '172.28.190.54', 'param_1': '172.28.190.54', 'param_2':
datetime.datetime(2017, 6, 28, 12, 55, 12, 135541)}
2017-06-28 12:55:12,140 INFO
[sqlalchemy.engine.base.Engine:699][MainThread] ROLLBACK
0.0.0.0 - - [28/Jun/2017 12:55:12] "POST /loader HTTP/1.1" 200 17
2017-06-28 12:55:12,148 DEBUG [stempqm.views.qmviews:42][MainThread] loader
view
2017-06-28 12:55:12,149 INFO
[sqlalchemy.engine.base.Engine:679][MainThread] BEGIN (implicit)
2017-06-28 12:55:12,150 INFO
[sqlalchemy.engine.base.Engine:1140][MainThread] SELECT tile_sub.groupip AS
tile_sub_groupip, tile_sub.portnum AS tile_sub_portnum, tile_sub.sourceip
AS tile_sub_sourceip, tile_sub.hostname AS tile_sub_hostname,
tile_sub.timecreated AS tile_sub_timecreated
FROM tile_sub
WHERE tile_sub.groupip = %(groupip_1)s AND tile_sub.portnum = %(portnum_1)s
2017-06-28 12:55:12,151 INFO
[sqlalchemy.engine.base.Engine:1143][MainThread] {'groupip_1':
'239.129.237.128', 'portnum_1': '55002'}
2017-06-28 12:55:12,153 INFO
[sqlalchemy] an example of concrete inheritance with polymorphic_union is needed
I think at this point, the docs are simply making it worse for me. Is there an example out there that is declarative and concise? This is a really simple scenario involving a single header table, and multiple (identical) detail tables, as in: headertable 1. id int 2. namekey varchar detail1 1. id integer 2. headerid integer, fk headertable.id 3. groupid integer 4. somevalue varchar And so on, ad nauseum, detail2. . .detail*N* * * Employees, engineers, and managers. . .Is.Not.Working for me. Is there something better out there. . ? I can make it AbstractConcreteBase or ConcreteBase, or whatever at this point, any direction in the way of best practice or gotchas is appreciated too. Thanks. . . -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/_i5k9TYUnEMJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
