Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> what would be the actual use cases of any of these features?
> Let's collect some, so we can think of ways to make them work.
The two things which leap to mind for me are:
(1) All the \d commands in psql should be implemented in SPs so
that they are available from any client, through calling one SP
equivalent to one \d command. The \d commands would be changed to
call the SPs for releases recent enough to support this. Eventually
psql would be free of worrying about which release contained which
columns in which system tables, because it would just be passing the
parameters in and displaying whatever results came back.
I have used products which implemented something like this, and
found it quite useful.
(2) In certain types of loads -- in particular converting data from
old systems into the database for a new system -- you need to load
several tables in parallel, with queries among the tables which are
being loaded. The ability to batch many DML statements into one
transaction is important, to avoid excessive COMMIT overhead and
related disk output; however, the ability to ANALYZE tables
periodically is equally important, to prevent each access to an
initially-empty table from being done as a table scan after it has
millions of rows. VACUUM might become equally important if there
are counts or totals being accumulated in some tables, or status
columns are being updated, as rows are added to other tables.
I've often had to do something like this during conversions. This
could be handled in an external program (I've often done it in
Java), but performance might be better if a stored procedure in
PostgreSQL was able to keep SQL/MED streams of data open while
committing and performing this maintenance every so many rows.
-Kevin