Re: [GENERAL] Re: Need for rebuilding index after many deletions?
With Postgres 6.5.2, if a table has undergone several row deletions, does it make sense/ is it needed to rebuild the index? If you've deleted a large fraction of the rows in the table, dropping and recreating the indexes would be worth doing, because VACUUM by itself won't reclaim unused space in an index. BTW, many people have found that drop indexes; VACUUM; rebuild indexes; is actually faster than letting VACUUM try to vacuum the indexes. One of the things on the to-do list is to redesign VACUUM so that it handles indexes this way internally. I could be wrong, but you also need the -a (analyze) option to vacuum to have it "re-think" it's lookup strategies, right? Doesn't have anything to do with index efficiency. If the table stats (column min/max values, etc) have changed a lot then it's worth doing "analyze" again to update pg_statistic. But in many cases you don't need to do "analyze" nearly as often as basic "vacuum". regards, tom lane
Re: [GENERAL] libperl.so
Yeah, openbsd ld/ld.so for example will bitch and moan when its asked to do this. (nonPIC code loaded as so). So this is to be used as last resort. On Sat, 5 Aug 2000, Lamar Owen wrote: Charles Tassell wrote: There is also a way to recompile a .a library into a shared library. Something like: ar x library.a ld -shared -o library.so *.o But a shared lib is _supposed_ to be compiled with position-independent code with -fPIC -- otherwise you are just asking for trouble to simply relink in this way. It may work for you (as it does for me with pl/perl, and for Karl DeBisschop), but it is highly unlikely it will work for everyone, and it is likely to cause performance issues even if it does work. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Re: [GENERAL] foreign keys
*** Bruce Momjian [EMAIL PROTECTED] [Saturday, 05.August.2000, 19:39 -0400]: Not to mentions fact that in a few places in docs it's shown as a method for copying table "SELECT... INTO" which does not "take" keys with it leading to database knwoledge loss. That is a good point. SELECT INTO doesn't support constraints. Unfortunately, I don't really know a way around that. The only solution is CREATE TABLE and then INSERT INTO ... SELECT. [.rs.] what about my other statement about third constraint not being transferred withh pg_dump -t table because it was "connected" to second database? Am I right? What is correct (mean: most simple) way of dupicating table with all FK ? Sorry, I don't know. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Table Design: Timestamp vs time/date
Dale Walker [EMAIL PROTECTED] writes: Having a 'timestamp' field 'CCYY-MM-DD HH:MM:SS.SS' or two separate fields one for time 'HH:MM:SS.SS' and one for Date 'CCYY-MM-DD'. Go for the timestamp. Otherwise you'll be cursing yourself the first time someone wants to know about "all logins between noon Tuesday and 3am Thursday", for example --- easy with timestamps, a pain in the neck without. Even when the range boundaries do coincide with midnight, there isn't likely to be any measurable performance advantage from using a date column instead of a timestamp column. Also, type timestamp is Postgres' best-supported date/time type, with a more complete set of available operations than any of the secondary date/time types. regards, tom lane
Re: [GENERAL] foreign keys
*** Bruce Momjian [EMAIL PROTECTED] [Saturday, 05.August.2000, 19:39 -0400]: Not to mentions fact that in a few places in docs it's shown as a method for copying table "SELECT... INTO" which does not "take" keys with it leading to database knwoledge loss. That is a good point. SELECT INTO doesn't support constraints. Unfortunately, I don't really know a way around that. The only solution is CREATE TABLE and then INSERT INTO ... SELECT. [.rs.] what about my other statement about third constraint not being transferred withh pg_dump -t table because it was "connected" to second database? Am I right? What is correct (mean: most simple) way of dupicating table with all FK ? -- radoslaw.stachowiak.http://alter.pl/
Re: [GENERAL] foreign keys
On Sun, 6 Aug 2000, Radoslaw Stachowiak wrote: *** Bruce Momjian [EMAIL PROTECTED] [Saturday, 05.August.2000, 19:39 -0400]: Not to mentions fact that in a few places in docs it's shown as a method for copying table "SELECT... INTO" which does not "take" keys with it leading to database knwoledge loss. That is a good point. SELECT INTO doesn't support constraints. Unfortunately, I don't really know a way around that. The only solution is CREATE TABLE and then INSERT INTO ... SELECT. [.rs.] what about my other statement about third constraint not being transferred withh pg_dump -t table because it was "connected" to second database? Am I right? Actually, you should only be seeing one constraint out on the referencing table and two out of the referenced one, but yes, fundamentally it only is dumping the constraint triggers for the table you are dumping at the moment. What is correct (mean: most simple) way of dupicating table with all FK ? Umm, possibly taking the dump of the table you want and a schema only dump of the referenced table and removing the bits you don't need. Or, turn the constraint triggers into alter table add constraint statements (although you'd then have to only get one alter table add constraint in case you were on the referenced table - and that could still get you in trouble depending on what precisely you're doing -- if the table was the referenced table of a fk constraint, would you necessarily want to alter the table that was referencing it?).
Re: [GENERAL] foreign keys
On Sat, 5 Aug 2000, Bruce Momjian wrote: Not to mentions fact that in a few places in docs it's shown as a method for copying table "SELECT... INTO" which does not "take" keys with it leading to database knwoledge loss. That is a good point. SELECT INTO doesn't support constraints. Unfortunately, I don't really know a way around that. The only solution is CREATE TABLE and then INSERT INTO ... SELECT. Argh, that's annoying. Is there a way to CREATE TABLE, getting the schema from another table? :o Ian
