I re-worked my test a little bit to use logging, and to switch table
definitions based on a command line argument.
I was able to trim down my schema to just the two tables in question,
although the behavior is the same regardless
of the related table, and attached is that schema along with some data
to make the test work.
Also I'm using SA 0.5.6, Postgres 8.4.1, psycopg2 2.0.12, python 2.5.4
on Debian Squeeze.
dgard...@cssun32 ~/assetdb/one-time:$ python eager_loadtest.py
using PGText
INFO:sqlalchemy.orm.strategies.LazyLoader:AssetRelation.Source lazy
loading clause asset.path = %(param_1)s
INFO:sqlalchemy.orm.strategies.LazyLoader:AssetRelation.Source will use
query.get() to optimize instance loads
INFO:sqlalchemy.orm.strategies.LazyLoader:Asset.Related lazy loading
clause %(param_1)s = relation.src_asset
INFO:sqlalchemy.orm.strategies.LazyLoader:Asset.Relatee lazy loading
clause %(param_1)s = relation.target_asset
INFO:sqlalchemy.orm.strategies.LazyLoader:AssetRelation.Target lazy
loading clause asset.path = %(param_1)s
INFO:sqlalchemy.orm.strategies.LazyLoader:AssetRelation.Target will use
query.get() to optimize instance loads
testshow/eps/201/s01/t01
testshow/chr/test/test
testshow/eps/201/s01/t01
dgard...@cssun32 ~/assetdb/one-time:$ python eager_loadtest.py text
using Text
INFO:sqlalchemy.orm.strategies.LazyLoader:AssetRelation.Source lazy
loading clause asset.path = %(param_1)s
INFO:sqlalchemy.orm.strategies.LazyLoader:Asset.Related lazy loading
clause %(param_1)s = relation.src_asset
INFO:sqlalchemy.orm.strategies.LazyLoader:Asset.Relatee lazy loading
clause %(param_1)s = relation.target_asset
INFO:sqlalchemy.orm.strategies.LazyLoader:AssetRelation.Target lazy
loading clause asset.path = %(param_1)s
testshow/eps/201/s01/t01
testshow/chr/test/test
testshow/eps/201/s01/t01
dgard...@cssun32 ~/assetdb/one-time:$ python eager_loadtest.py auto
using auto
INFO:sqlalchemy.orm.strategies.LazyLoader:AssetRelation.Source lazy
loading clause asset.path = %(param_1)s
INFO:sqlalchemy.orm.strategies.LazyLoader:AssetRelation.Source will use
query.get() to optimize instance loads
INFO:sqlalchemy.orm.strategies.LazyLoader:Asset.Related lazy loading
clause %(param_1)s = relation.src_asset
INFO:sqlalchemy.orm.strategies.LazyLoader:Asset.Relatee lazy loading
clause %(param_1)s = relation.target_asset
INFO:sqlalchemy.orm.strategies.LazyLoader:AssetRelation.Target lazy
loading clause asset.path = %(param_1)s
INFO:sqlalchemy.orm.strategies.LazyLoader:AssetRelation.Target will use
query.get() to optimize instance loads
testshow/eps/201/s01/t01
testshow/chr/test/test
testshow/eps/201/s01/t01
Michael Bayer wrote:
> On Sep 24, 2009, at 8:21 PM, David Gardner wrote:
>
>
>> Ran across something that I suspect might be a bug. If I define my
>> table like:
>>
>> asset_table = Table('asset', metadata,
>> Column('path', Text, primary_key=True,
>> server_default=FetchedValue(),
>> server_onupdate=FetchedValue()),
>> autoload=True)
>>
>> Then anytime I query for an asset and eagerload a related table the
>> backref on the related table isn't populated, causing a second query
>> to
>> the DB.
>> If instead I define that column of type PGText then the backrefs are
>> populated properly. I attached a test which is a simplified version of
>> my table mappings.
>>
>
> what does the logging output say if you turn on logging.INFO for the
> "sqlalchemy.orm" logger ? that would illustrate some things about the
> join conditions.
>
> For me to test this I'd have to build up some table names and guess
> what you have for those defaults....can you share your table
> definitions ?
>
>
>
>
>
>> Attached is a test of this behavior. The output when the column is
>> defined as Text or String looks like:
>> testshow/eps/201/s01/t01
>> testshow/chr/test/test
>> 2009-09-24 17:17:03,214 INFO sqlalchemy.engine.base.Engine.0x...1f10
>> SELECT asset.updated AS asset_updated, asset.name AS asset_name,
>> asset.type AS asset_type, asset.path AS asset_path, asset.parent AS
>> asset_parent, asset.is_file AS asset_is_file, asset.created_by AS
>> asset_created_by
>> FROM asset
>> WHERE asset.path = %(param_1)s
>> 2009-09-24 17:17:03,214 INFO sqlalchemy.engine.base.Engine.0x...1f10
>> {'param_1': 'testshow/eps/201/s01/t01'}
>> testshow/eps/201/s01/t01
>>
>>
>> When defined as PGText the output is:
>> testshow/eps/201/s01/t01
>> testshow/chr/test/test
>> testshow/eps/201/s01/t01
>>
>>
>> --
>> David Gardner
>> Pipeline Tools Programmer
>> Jim Henson Creature Shop
>> dgard...@creatureshop.com
>>
>>
>>
>> import sys
>> from sqlalchemy import *
>> from sqlalchemy.orm import *
>> from sqlalchemy.types import *
>> from sqlalchemy.databases.postgres import PGText
>>
>> DB_HOST = 'localhost'
>> DB_NAME = 'test_db'
>> DB_USER = 'testuser'
>> DB_PASS = 'testpass'
>> db_uri = 'postgres://%s:%...@%s/%s' % (DB_USER,DB_PASS,DB_HOST,DB_NAME)
>>
>> db = create_engine (db_uri)
>> metadata = MetaData(db)
>>
>> class Asset(object):
>> pass
>>
>> class AssetRelation(object):
>> pass
>>
>> #asset_table = Table('asset', metadata,autoload=True)
>>
>> #asset_table = Table('asset', metadata,
>> # Column('path', Text, primary_key=True,
>> # server_default=FetchedValue(),
>> # server_onupdate=FetchedValue()),
>> # autoload=True)
>>
>>
>> asset_table = Table('asset', metadata,
>> Column('path', PGText, primary_key=True,
>> server_default=FetchedValue(),
>> server_onupdate=FetchedValue()),
>> autoload=True)
>>
>> relation_table = Table('relation',metadata, autoload=True)
>>
>> asset_mapper = mapper(Asset, asset_table,
>> properties = {
>> 'Related' : relation(AssetRelation, backref='Source',
>> primaryjoin
>> =
>> asset_table.c.path
>> =
>> =
>> relation_table.c.src_asset
>> ,order_by=relation_table.c.target_asset,lazy=True)
>> })
>>
>> mapper(AssetRelation, relation_table, properties = {
>> 'Target' : relation(Asset, backref='Relatee',
>> primaryjoin=asset_table.c.path==relation_table.c.target_asset,
>> viewonly=True,lazy=False)
>> })
>>
>> session=create_session()
>> a=session.query(Asset).options(eagerload(Asset.Related)).get
>> ('testshow/eps/201/s01/t01')
>> db.echo=True
>> print a.path
>> r=a.Related[0]
>> print r.target_asset
>> b=r.Source
>> print b.path
>> session.close()
>>
>> sys.exit(0)
>>
>
>
> >
>
>
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--~--~---------~--~----~------------~-------~--~----~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
import sys
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.types import *
from sqlalchemy.databases.postgres import PGText
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.orm').setLevel(logging.INFO)
DB_HOST = 'localhost'
DB_NAME = 'test_db'
DB_USER = 'testuser'
DB_PASS = 'testpass'
db_uri = 'postgres://%s:%...@%s/%s' % (DB_USER,DB_PASS,DB_HOST,DB_NAME)
db = create_engine (db_uri)
metadata = MetaData(db)
class Asset(object):
pass
class AssetRelation(object):
pass
if len(sys.argv) < 2:
print "using PGText"
asset_table = Table('asset', metadata,
Column('path', PGText, primary_key=True,
server_default=FetchedValue(),
server_onupdate=FetchedValue()),
autoload=True)
elif sys.argv[1]=='text':
print "using Text"
asset_table = Table('asset', metadata,
Column('path', Text, primary_key=True,
server_default=FetchedValue(),
server_onupdate=FetchedValue()),
autoload=True)
else:
print "using auto"
asset_table = Table('asset', metadata,autoload=True)
relation_table = Table('relation',metadata, autoload=True)
asset_mapper = mapper(Asset, asset_table,
properties = {
'Related' : relation(AssetRelation, backref='Source', primaryjoin=asset_table.c.path==relation_table.c.src_asset,order_by=relation_table.c.target_asset,lazy=True)
})
mapper(AssetRelation, relation_table, properties = {
'Target' : relation(Asset, backref='Relatee', primaryjoin=asset_table.c.path==relation_table.c.target_asset, viewonly=True,lazy=False)
})
session=create_session()
a=session.query(Asset).options(eagerload(Asset.Related)).get('testshow/eps/201/s01/t01')
#db.echo=True
print a.path
r=a.Related[0]
print r.target_asset
b=r.Source
print b.path
session.close()
sys.exit(0)--
-- PostgreSQL database dump
--
-- Started on 2009-09-25 09:36:32 PDT
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
--
-- TOC entry 1797 (class 1262 OID 532157)
-- Name: test_db; Type: DATABASE; Schema: -; Owner: -
--
CREATE DATABASE test_db WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
\connect test_db
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
--
-- TOC entry 308 (class 2612 OID 532162)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: -
--
CREATE PROCEDURAL LANGUAGE plpgsql;
--
-- TOC entry 309 (class 2612 OID 532164)
-- Name: plpythonu; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: -
--
CREATE PROCEDURAL LANGUAGE plpythonu;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- TOC entry 1496 (class 1259 OID 532184)
-- Dependencies: 1775 1776 1777 1778 1779 3
-- Name: asset; Type: TABLE; Schema: public; Owner: -; Tablespace:
--
CREATE TABLE asset (
updated timestamp without time zone DEFAULT now() NOT NULL,
name text NOT NULL,
type character varying(50) NOT NULL,
path text NOT NULL,
parent text,
is_file text DEFAULT 'false'::text NOT NULL,
created_by character varying(255) DEFAULT 'www-data'::character varying,
CONSTRAINT name_not_blank CHECK ((btrim(name) <> ''::text)),
CONSTRAINT path_eq_parent_slash_name CHECK ((path = ((parent || '/'::text) || name)))
);
--
-- TOC entry 1497 (class 1259 OID 532375)
-- Dependencies: 3
-- Name: relation; Type: TABLE; Schema: public; Owner: -; Tablespace:
--
CREATE TABLE relation (
type character varying(255) NOT NULL,
src_asset text NOT NULL,
target_asset text NOT NULL
);
--
-- TOC entry 1781 (class 2606 OID 559809)
-- Dependencies: 1496 1496
-- Name: asset_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
--
ALTER TABLE ONLY asset
ADD CONSTRAINT asset_pkey PRIMARY KEY (path);
--
-- TOC entry 1789 (class 2606 OID 559868)
-- Dependencies: 1497 1497 1497 1497
-- Name: relation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
--
ALTER TABLE ONLY relation
ADD CONSTRAINT relation_pkey PRIMARY KEY (src_asset, target_asset, type);
--
-- TOC entry 1782 (class 1259 OID 560026)
-- Dependencies: 1496
-- Name: is_file_idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
--
CREATE INDEX is_file_idx ON asset USING btree (is_file);
--
-- TOC entry 1783 (class 1259 OID 560028)
-- Dependencies: 1496
-- Name: name_idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
--
CREATE INDEX name_idx ON asset USING btree (name text_pattern_ops);
--
-- TOC entry 1784 (class 1259 OID 560030)
-- Dependencies: 1496 1496
-- Name: name_type_idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
--
CREATE INDEX name_type_idx ON asset USING btree (name, type);
--
-- TOC entry 1785 (class 1259 OID 560039)
-- Dependencies: 1496
-- Name: parent_patternops_idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
--
CREATE INDEX parent_patternops_idx ON asset USING btree (parent text_pattern_ops);
--
-- TOC entry 1786 (class 1259 OID 560040)
-- Dependencies: 1496
-- Name: path_patternops_idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
--
CREATE INDEX path_patternops_idx ON asset USING btree (path text_pattern_ops);
--
-- TOC entry 1790 (class 1259 OID 560045)
-- Dependencies: 1497 1497
-- Name: src_asset_type; Type: INDEX; Schema: public; Owner: -; Tablespace:
--
CREATE INDEX src_asset_type ON relation USING btree (src_asset, type);
--
-- TOC entry 1791 (class 1259 OID 560054)
-- Dependencies: 1497 1497
-- Name: trg_asset_type; Type: INDEX; Schema: public; Owner: -; Tablespace:
--
CREATE INDEX trg_asset_type ON relation USING btree (target_asset, type);
--
-- TOC entry 1787 (class 1259 OID 560055)
-- Dependencies: 1496
-- Name: type_idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
--
CREATE INDEX type_idx ON asset USING btree (type);
--
-- TOC entry 1792 (class 2606 OID 560790)
-- Dependencies: 1780 1496 1496
-- Name: asset_parentfk; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY asset
ADD CONSTRAINT asset_parentfk FOREIGN KEY (parent) REFERENCES asset(path) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- TOC entry 1794 (class 2606 OID 560157)
-- Dependencies: 1780 1496 1497
-- Name: fk_relation_asset; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY relation
ADD CONSTRAINT fk_relation_asset FOREIGN KEY (src_asset) REFERENCES asset(path) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- TOC entry 1793 (class 2606 OID 560162)
-- Dependencies: 1780 1496 1497
-- Name: fk_relation_target_asset; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY relation
ADD CONSTRAINT fk_relation_target_asset FOREIGN KEY (target_asset) REFERENCES asset(path) ON UPDATE CASCADE ON DELETE CASCADE;
-- Completed on 2009-09-25 09:36:33 PDT
--
-- PostgreSQL database dump complete
--
--
-- PostgreSQL database dump
--
-- Started on 2009-09-25 10:01:43 PDT
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
INSERT INTO asset (updated, name, type, path, parent, is_file, created_by) VALUES ('2009-09-25 09:57:34.784734', 'testshow', 't', 'testshow', NULL, 'false', 'www-data');
INSERT INTO asset (updated, name, type, path, parent, is_file, created_by) VALUES ('2009-09-25 09:57:49.41621', 'eps', 't', 'testshow/eps', 'testshow', 'false', 'www-data');
INSERT INTO asset (updated, name, type, path, parent, is_file, created_by) VALUES ('2009-09-25 09:58:24.518829', '201', 't', 'testshow/eps/201', 'testshow/eps', 'false', 'www-data');
INSERT INTO asset (updated, name, type, path, parent, is_file, created_by) VALUES ('2009-09-25 09:58:58.783075', 's01', 't', 'testshow/eps/201/s01', 'testshow/eps/201', 'false', 'www-data');
INSERT INTO asset (updated, name, type, path, parent, is_file, created_by) VALUES ('2009-09-25 09:59:18.206156', 't01', 't', 'testshow/eps/201/s01/t01', 'testshow/eps/201/s01', 'false', 'www-data');
INSERT INTO asset (updated, name, type, path, parent, is_file, created_by) VALUES ('2009-09-25 09:59:36.069485', 'chr', 't', 'testshow/chr', 'testshow', 'false', 'www-data');
INSERT INTO asset (updated, name, type, path, parent, is_file, created_by) VALUES ('2009-09-25 09:59:53.348256', 'test', 't', 'testshow/chr/test', 'testshow/chr', 'false', 'www-data');
INSERT INTO asset (updated, name, type, path, parent, is_file, created_by) VALUES ('2009-09-25 10:00:15.95648', 'test', 't', 'testshow/chr/test/test', 'testshow/chr/test', 'false', 'www-data');
--
INSERT INTO relation (type, src_asset, target_asset) VALUES ('take_uses_chr', 'testshow/eps/201/s01/t01', 'testshow/chr/test/test');
-- Completed on 2009-09-25 10:01:43 PDT
--
-- PostgreSQL database dump complete
--
