Skip site navigation (1) Skip section navigation (2)

Re: TODO list comments

From: Hannu Krosing <hannu(at)skype(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>,pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: TODO list comments
Date: 2005-08-25 07:12:50
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On K, 2005-08-24 at 21:58 -0400, Tom Lane wrote:
> 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.

> 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.

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.

> > 	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. 

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.

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.

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

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

> > * 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.

Hannu Krosing <hannu(at)skype(dot)net>

In response to


pgsql-hackers by date

Next:From: Magnus HaganderDate: 2005-08-25 07:58:21
Subject: Re: Stuff running slooow
Previous:From: Michael GlaesemannDate: 2005-08-25 06:50:43
Subject: Re: TODO list comments

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group