Re: INSERT/SELECT and excessive foreign key checks

"Webb Sprague" <webb(dot)sprague(at)gmail(dot)com> writes:

> Is there a different potential hack for marking a table read-only,
> turning it on and off with a function()? In a hackish vein, use a
> trigger to enforce this, and maybe a rule that can do the
> optimization?

I think several people already have something like this in mind for the next
release for several different motivations. It is (as most things are in
Postgres) a bit trickier than it appears since even if you block subsequent
writes the table's contents still "change" from the point of view of clients
when their snapshots change.

What's needed is a two-phase command which first starts blocking writes to the
table then vacuums it waiting on each page until every tuple in the entire
table can be frozen. At that point the contents are truly static and the table
can be marked as such.

That would enable a number of optimizations:

. The table can be moved to a read-only medium.

. Index scans can be index-only scans

. The statistics could gather information such as min/max for each column and
the planner could trust this data. That would allow constraint exclusion to
kick in for partitions even if you're not querying on the partition key. It
also allows us to exclude the parent table of the inheritance tree.

. FK checks could rely on a share table lock instead of row locks and
aggressively cache which key values are found even across transactions.

But this all relies on a user-visible operation to switch the table from
read-write to read-only and back again. It cannot switch the behaviour
transparently because switching it back to read-write requires taking a lock
and notifying everyone to dump their old plans and caches.

Bruce's idea had the merit that it could be made transparent, but I think as a
result it has too few optimizations that would be safe to do.

As a DBA I don't think I would have been too upset at the idea that if I
manually marked a table read only it would enable various optimizations.
Especially if I was told I could mark it read write whenever I felt like, make
my changes and then set it back to read-only. It does have the "one more knob"
nature though.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com