"Webb Sprague" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
