Re: TODO list comments

Hannu Krosing wrote:
> On K, 2005-08-24 at 21:58 -0400, Tom Lane wrote:
> > > * %Allow TRUNCATE ... CASCADE/RESTRICT
> >
> > Huh? What would that do?
>
> Maybe this was meant truncating of tables with dependent foreign keys ?
>
> AFAIR this was solved by allowing truncating several tables in one
> command even if they have FK relationships between themselves.

Yes, but I can imagine allowing a CASCADE behavior as well.

> > This is only partly done --- the 8.1 patch didn't cover all object types.
> >
> > > o %Disallow dropping of an inherited constraint
> > > ...
> > > o %Prevent child tables from altering constraints like CHECK that were
> > > inherited from the parent table
> >
> > These seem to be duplicates, or at least in need of merging.
>
> It should probably mention about weird inheritance behaviour of "CREATE
> CONSTRAINT ON ONLY tablename" - it is not propagated to existing child
> tables, but is inherited when creating new ones.

I am not sure on that one because the table does have the constraint at
the time the child is created. Comments?

> Also, I don't think this should be done at all, at least not before we
> have proper partitioned table support ready. I could live with it
> creating a warning about not being future-compatible.

Right, TODO item removed.

> > > o Handle references to temporary tables that are created, destroyed,
> > > then recreated during a session, and EXECUTE is not used
> > >
> > > This requires the cached PL/PgSQL byte code to be invalidated when
> > > an object referenced in the function is changed.
> >
> > This is redundant with the Dependency Checking item about regenerating
> > cached plans.
>
> Or maybe not completely, depending on how you do it.

Well, I beefed up the item:

* Track dependencies in function bodies and recompile/invalidate

This is particularly important for references to temporary tables
in PL/PgSQL because PL/PgSQL caches query plans. The only workaround
in PL/PgSQL is to use EXECUTE.

> If temp table itself is created inside the same pl/pgsql function, then
> there could still be a way to do the planning/optimising only once and
> then substitute temp table oids when running the function.
>
> The table structure in this case is quaranteed to be the same during
> each run of the function, it's just that the temp table and index oids
> should be treated as local variables.

Interesting approach but is it worth the added complexity? One issue
this does bring up is that functions themselves might invalidate their
own cached query plan by dropping a table and receating it. In those
cases, your solution would be the only valid one, or throw an error.

I added some more text:

* Track dependencies in function bodies and recompile/invalidate

This is particularly important for references to temporary tables
in PL/PgSQL because PL/PgSQL caches query plans. The only workaround
in PL/PgSQL is to use EXECUTE. One complexity is that a function
might itself drop and recreate dependent tables, causing it to
invalidate its own query plan.

> Done this way, it gives real benefits in terms of cached query plans,
> instead of just preventing newcomers from shooting themselves in foot by
> not using EXECUTE.
>
> > > * Improve speed with indexes
> > >
> > > For large table adjustements during vacuum, it is faster to reindex
> > > rather than update the index.
> >
> > This applies only to VACUUM FULL, so it probably needs to be reworded.
>
> In case we implement concurrent/non-blocking CREATE INDEX at some point,
> this might be a good idea for lazy VACUUM as well.

Perhaps.

> And it may make more sense to do CLUSTER instead of VACUUM FULL in at
> least some of these cases.

Cluster modifies the heap while reindex does not. This makes cluster a
much heavier operation.

> (btw. CLUSTER seems to be another function which my concurrent vacuuming
> patch should be extended to cover, at least on "client" side, like
> CREATE INDEX)

Not sure.

> > > * Auto-vacuum
> > >
> > > o %Suggest VACUUM FULL if a table is nearly empty
> >
> > It seems like a fairly bad idea for auto-vacuum to do a VACUUM FULL
> > ever, given the locking effects. And how is a background daemon going
> > to "suggest" anything? It could write to the postmaster log but it's
> > entirely likely the user would never notice.
>
> With current implementations of commands, doing (some equivalent of)
> CLUSTER here seems a better idea than VACUUM FULL, as it also un-bloats
> indexes. Not sure of of transactional behaviour though.

Not sure, CLUSTER is still heavier. That doesn't mean it shouldn't be
used, but the administrator should automatically consider CLUSTER in
place of VACUUM FULL for large updates.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073