Re: code question: storing INTO relation

"Michael Paesold" <mpaesold(at)gmx(dot)at> writes:

> If I understand the original proposal correctly, there is no risk of data loss
> except in a temporary file. The data would be copied into a new file (without
> wal-logging), but after that, the file would be fsynced and the resulting
> changes would indeed be logged to WAL.
>
> So if there is a crash during the copy, nothing valuable will be lost at all.
> If there is a crash after transaction commit of that transaction, nothing will
> be lost, again... the new file will have been fsynced already and everything
> else will be in WAL.

This is one of the reasons CREATE TABLE AS and SELECT ... INTO ... are _not_
necessarily the same. In the first case the table didn't exist at all prior to
the statement. Therefore there's no need to log any modifications to the
table. If there's a crash the initial creation of the table should be rolled
back and none of the data needs to be retained.

In Oracle CREATE TABLE AS is much faster than SELECT ... INTO ... for
basically this reason.

In Postgres CREATE TABLE AS is currently being treated as a synonym for SELECT
... INTO ... So I think this may be an awkward feature to add. Also, like
reindex the logging would still be necessary for online backups. So this may
be a dead-end direction in the long term.

--
greg