Hi,
As part of MDL-39725, I'm investigating the use of temporary tables and then how to interact with them from a statistics point of view. PostgreSQL does not keep statistics on temporary tables automatically and it can impact performance when indexes are used. Backup/Restore with backup_ids_temp is an example.
I've found 3 different ways temp tables are used/created.
1. in backup with create_temp_table_from_realtable(), Chat discussions indicated that this was an older method and can be replaced with the standard database create_temp_table.
2. In lib/statslib.php stats_temp_table_create() we use install.xml templates in some cases to create the temporary tables from existing tables. Some real tables, some templates. In (1 backup/restore) there is a note about using install.xml as it uses 10M of RAM for that purpose. Further to that, there is some option to include other files, but I don't completely understand that code segment.
3. In "auth/ldap/auth.php" the xmldb_table object is used to create an object on the fly.
Now as I'm newly exposed to this, (3) makes the most sense to me as temporary tables are used in specific locations for a specific purpose. (2) seems strange as it's a mix of template tables in the database and copies of real tables. This feels dangerous to copy real table structures for temporary purposes rather than just create a temporary table. So I'm looking for input on unifying the approach on temporary tables;
In my view (3) that creates the temp table in the code on the fly is the clearest to understand. It removes them from install.xml and the database making both cleaner to understand. The options are (2) and/or (3) as (1) has already been stated as being a non-current method.
Opinions/Objections to altering these to use inline temp tables?
Is there any other pearls of wisdom I do not know from reading the code and my limited experience?
Thanks
