<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">right now functions like startswith() and endswith() rely upon an in-python string concatenation of "%" with the given value.  this prevents the usage of bind parameters and other non-literal expressions.  a better approach would be to move the string concatenation into the database layer, so that instead of producing:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="where somecol LIKE &quot;%foo&quot;"><pre class="notranslate"><code class="notranslate">where somecol LIKE "%foo"
</code></pre></div>
<p dir="auto">we instead produce:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="where somecol LIKE '%' + 'foo'"><pre class="notranslate"><code class="notranslate">where somecol LIKE '%' + 'foo'
</code></pre></div>
<p dir="auto">but not every database supports "+" as a concatenation operator.  so a <code class="notranslate">ConcatenationClause</code> would be needed which each dialect can produce as it likes, such as oracle which would produce <code class="notranslate">CONCAT(x, y)</code> for example.</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/475/py">py</a></p>
<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">Trying out <code class="notranslate">sqlalchemy</code> for the first time, I notice that autoloading the schema from my PostgresQL 8.1 database takes forever: loading a single table takes more than a minute!</p>
<p dir="auto">With a little investigation, I found that the query executed is</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="SELECT table_constraints.constraint_name AS table_constraints_constraint_name, 
       table_constraints.constraint_type AS table_constraints_constraint_type, 
       table_constraints.table_name AS table_constraints_table_name, 
       key_column_usage.table_schema AS key_column_usage_table_schema, 
       key_column_usage.table_name AS key_column_usage_table_name, 
       key_column_usage.column_name AS key_column_usage_column_name, 
       key_column_usage.constraint_name AS key_column_usage_constraint_name, 
       constraint_column_usage.table_schema AS constraint_column_usage_table_schema, 
       constraint_column_usage.table_name AS constraint_column_usage_table_name, 
       constraint_column_usage.column_name AS constraint_column_usage_column_name, 
       constraint_column_usage.constraint_name AS constraint_column_usage_constraint_name
FROM information_schema.table_constraints, 
     information_schema.key_column_usage, 
     information_schema.constraint_column_usage
WHERE ((key_column_usage.constraint_name = constraint_column_usage.constraint_name AND 
        constraint_column_usage.constraint_name = table_constraints.constraint_name) AND 
       table_constraints.table_name = %(table_constraints_table_name)s) AND 
       table_constraints.table_schema = %(table_constraints_table_schema)s"><pre class="notranslate"><code class="notranslate">SELECT table_constraints.constraint_name AS table_constraints_constraint_name, 
       table_constraints.constraint_type AS table_constraints_constraint_type, 
       table_constraints.table_name AS table_constraints_table_name, 
       key_column_usage.table_schema AS key_column_usage_table_schema, 
       key_column_usage.table_name AS key_column_usage_table_name, 
       key_column_usage.column_name AS key_column_usage_column_name, 
       key_column_usage.constraint_name AS key_column_usage_constraint_name, 
       constraint_column_usage.table_schema AS constraint_column_usage_table_schema, 
       constraint_column_usage.table_name AS constraint_column_usage_table_name, 
       constraint_column_usage.column_name AS constraint_column_usage_column_name, 
       constraint_column_usage.constraint_name AS constraint_column_usage_constraint_name
FROM information_schema.table_constraints, 
     information_schema.key_column_usage, 
     information_schema.constraint_column_usage
WHERE ((key_column_usage.constraint_name = constraint_column_usage.constraint_name AND 
        constraint_column_usage.constraint_name = table_constraints.constraint_name) AND 
       table_constraints.table_name = %(table_constraints_table_name)s) AND 
       table_constraints.table_schema = %(table_constraints_table_schema)s
</code></pre></div>
<p dir="auto">Effectively, this query takes lot of time to execute (<code class="notranslate">PgAdmin3</code> says 85803 ms, and the server is a dual amd64), and the engine is not even able to "explain" it, giving the error</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="ERROR:  record type has not been registered"><pre class="notranslate"><code class="notranslate">ERROR:  record type has not been registered
</code></pre></div>
<p dir="auto">So, I managed to produce an equivalent query using JOINs instead, like the following:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="SELECT table_constraints.constraint_name AS table_constraints_constraint_name, ...
FROM information_schema.table_constraints 
  JOIN information_schema.constraint_column_usage 
    ON constraint_column_usage.constraint_name = table_constraints.constraint_name 
  JOIN information_schema.key_column_usage 
    ON key_column_usage.constraint_name = constraint_column_usage.constraint_name
WHERE table_constraints.table_name = %(table_constraints_table_name)s 
  AND table_constraints.table_schema = %(table_constraints_table_schema)s"><pre class="notranslate"><code class="notranslate">SELECT table_constraints.constraint_name AS table_constraints_constraint_name, ...
FROM information_schema.table_constraints 
  JOIN information_schema.constraint_column_usage 
    ON constraint_column_usage.constraint_name = table_constraints.constraint_name 
  JOIN information_schema.key_column_usage 
    ON key_column_usage.constraint_name = constraint_column_usage.constraint_name
WHERE table_constraints.table_name = %(table_constraints_table_name)s 
  AND table_constraints.table_schema = %(table_constraints_table_schema)s
</code></pre></div>
<p dir="auto">And this is <strong>fast</strong>: 356 ms!</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/60/test_alchemy.dump.bz2">test_alchemy.dump.bz2</a> | <a href="../wiki/imported_issue_attachments/60/queries.sql">queries.sql</a> | <a href="../wiki/imported_issue_attachments/60/sqla.diff">sqla.diff</a> | <a href="../wiki/imported_issue_attachments/60/constraints_by_table.diff">constraints_by_table.diff</a></p>