Re: error-free disabling of individual child partition tables

On Mon, May 22, 2006 at 02:25:09PM -0400, April Lorenzen wrote:
> It comes up repeatedly that I need to load fresh data into a new
> table, build indexes and vacuum - then drop the old table and
> substitute the new.

Why are you vacuuming a brand new table? Just run analyze.

> Postgresql partioning makes this all almost possible: the master
> automatically SELECTs from all the child tables - so it automatically
> recoginize the new ones, and no loss when the old ones are dropped.

How is that any better than:

BEGIN;
-- Don't do the drop right now, because it might take awhile
ALTER TABLE tablename RENAME TO delete_tablename;
ALTER TABLE temporary_tablename RENAME TO tablename;
COMMIT;
DROP delete_tablename;

> Just one thing would make it near perfect: if I could keep the master
> from being able to SELECT from the new child table while I'm COPYing
> data into the new child, building the index and vacuuming it - without
> sending an error back to the user who is querying the master table.
>
> I tried a CHECK constraint, a RULE - SELECT rule can't INSTEAD DO
> NOTHING - #postgresql channel people say there's nothing that can do
> this... revoking privs or changing the owner of the new child results
> in an error back to the user who queries the master table. Tried
> creating a dummy table with no records and making a rule for the child
> to INSTEAD select from the dummy table but that was not allowed
> because it would turn my non-empty child table into a view.
>
> Thank you,
>
> - April
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461