Don't prefix temporary tables

Oh right.

How is prefixing or not going to change anything? The temporary tables are "local" to the current session, so other sessions do not see the temporary table, so there is no name clash.

IIRC Drupal does not use persistant connections, so there is no way of different "sites" to use the same connection.

This also applies to mysql, AFAIK.

Ah well. Here's a patch.

Since we don't prefix temp tables we can't use {temp_table} syntax.

Here. Quoting the documentation and setting to ready to be commited:

Mysql:

From MySQL 3.23 on, you can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)

Postgresql:

If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.

and

In the standard, temporary tables are defined just once and automatically exist (starting with empty contents) in every session that needs them. PostgreSQL instead requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used. This allows different sessions to use the same temporary table name for different purposes, whereas the standard's approach constrains all instances of a given temporary table name to have the same table structure.

I'm affraid we can't prefix only for mysql and not for postgres.
After db_query_temporary() you need to use the created table, and then the code is the same for both databases, for example:

$result = db_query_temporary("SELECT i.type, i.sid, SUM(i.score * t.count) AS relevance, COUNT(*) AS ma
tches FROM {search_index} i INNER JOIN {search_total} t ON i.word = t.word $join1 WHERE $conditions GROUP
 BY i.type, i.sid HAVING matches >= %d", $arguments, 'temp_search_sids');
$normalize = db_result(db_query('SELECT MAX(relevance) FROM {temp_search_sids}'));

Just before sleep: there might be general solution for postgres (using search_path), I'll have to check tommorow.
Good night.

I can't find a good solution after all.
So I see two possibilities:
1. Do not prefix temporary tables (and thus "drop" people whose hosts enforce arbitrary prefix on all tables)
2. Do prefix temporary tables and disallow use of schema prefixes with postgresql (for more information about schemas in postgres see http://www.postgresql.org/docs/8.0/static/ddl-schemas.html).

Of course, as someone using postgresql, I'd prefer the first option, but it is possibile to run drupal on postgresql without using schemas (at least I didn't heard otherwise).
I don't know how many people use 4.6 with schemas, nor how many providers enforce arbitrary prefixes...