Re: [SQL] VACUUM FULL vs dump & restore
Hello Ilya, you have to check for yourself which method is faster - just test it with a stopwatch;-) You have to take care, because when you make VACUUM FULL, then it vacuums also the system tables, etc. of postgres. I'm not sure if this is the same way VACUUM goes through all objects, but I'd make a customized vacuum, which finds out first every object which should be vacuumed by: select relname frompg_class You can filter out not wanted objects through the query or when processing the "VACUUM FULL [object]" or only "VACUUM [object]. In this way I can decide for myself what I want to vacuum, and what I will do by dump-truncate-restore. In many cases a normal VACUUM was even faster then the primitive dump-truncate-restore process. The bottlneck on a VACUUM is as I saw from my experience on tables with long strings inside and an amount of hundreds of millions. Regards, Aldor Ilya A. Kovalenko wrote: > Greetings, > > What advantages I lose, when using dump-truncate-restore (table > or whole DB) instead of performing VACUUM FULL ? > In both cases I have no access to data, but first is much faster > (by subjective estimate). > > Thank you, > > Ilya A. Kovalenko (mailto:[EMAIL PROTECTED]) > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] VACUUM FULL vs dump & restore
Another way how to do it with having access to the data in the same time
is to create a new table, named a little bit differently and do an:
insert into [table]2
select *
from[table];
Then switch to the second table.
Then you have to do on the first table the TRUNCATE and DROP.
For getting out which table is the actual one you can create a table
which holds the originate table name and the actual table name. When
using plpgsql you can check the table name before building the queries
and then build them with EXECUTE.
Be aware that you cannot do:
SELECT col1, col2
FROMgettablename('[table]');
Also be aware to switch back when you do the process again, so you dump
the data from the [table]2 to [table].
For my experience this way was faster then dump-truncate-restore on the
table.
Regards,
Aldor
Ilya A. Kovalenko wrote:
> Greetings,
>
> What advantages I lose, when using dump-truncate-restore (table
> or whole DB) instead of performing VACUUM FULL ?
> In both cases I have no access to data, but first is much faster
> (by subjective estimate).
>
> Thank you,
>
> Ilya A. Kovalenko (mailto:[EMAIL PROTECTED])
>
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org
>
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] stored procs in postgresql
Hi, I have been having some trouble with plsql stored procs in postgres in that I can make a table name a variable in the stored proc. Is there some special way to make this happen that I am unaware of? For example, I want to do something like: stored_proc(integer,varchar) SELECT table_name.id FROM table_name $2 WHERE table_name.id=$1 but I get an error about the $2 argument being no good. Does anyone know how I can deal with this? Thanks, Cere ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] stored procs in postgresql
[EMAIL PROTECTED] writes: > I have been having some trouble with plsql stored procs in postgres in > that I can > make a table name a variable in the stored proc. Is there some > special way to make this happen that I am unaware of? > > For example, I want to do something like: > > stored_proc(integer,varchar) > > SELECT table_name.id > FROM table_name $2 >WHERE table_name.id=$1 > > but I get an error about the $2 argument being no good. > > Does anyone know how I can deal with this? To do this sort of thing, you need to build up the query as a string, and EXECUTE it. Thus... query := 'select t.id from ' || $2 || ' t where t.id = ' || $1 || ';'; The other vital problem is that the select is in bad form. The actual name of the table needs to come BEFORE the alias, not after. The following would represent more nearly legitimate SQL... SELECT table_name.id FROM $2 table_name WHERE table_name.id=$1 -- let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];; http://cbbrowne.com/info/spiritual.html And me, with this terrible pain in all the diodes down my left side... -- Marvin the Paranoid Android ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] redundancy in CHECK CONSTRAINTs
I have the following table: CREATE TABLE gyuktnine ( id SERIAL, intsystem INTEGER NOT NULL REFERENCES yuksystems(id) CONSTRAINT int_cannot_equal_ext CHECK (intsystem != extsystem), extsystem INTEGER NOT NULL REFERENCES yuksystems(id) CONSTRAINT ext_cannot_equal_int CHECK (extsystem != intsystem), PRIMARY KEY (intsystem, extsystem) ); the intsystem and extsystem fields both have a check constraint on them which preventing any one record from having values in which they are equal. There is also a primary key. Is this redundant? Do only one of them really need this constraint? Or does it not really matter. I'm concerned about using constraints like this and have redundant checks built in slowing down my db. Ferindo ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] redundancy in CHECK CONSTRAINTs
Ferindo Middleton Jr <[EMAIL PROTECTED]> writes: > I have the following table: > CREATE TABLE gyuktnine ( > id SERIAL, > intsystem INTEGER NOT NULL REFERENCES yuksystems(id) CONSTRAINT > int_cannot_equal_ext >CHECK (intsystem != extsystem), > extsystem INTEGER NOT NULL REFERENCES yuksystems(id) CONSTRAINT > ext_cannot_equal_int > CHECK (extsystem != intsystem), > PRIMARY KEY (intsystem, extsystem) > ); > Is this redundant? Yes. I think it's poor style too: a constraint referencing multiple columns should be written as a table constraint not a column constraint. That is, you ought to write CREATE TABLE gyuktnine ( idSERIAL, intsystem INTEGER NOT NULL REFERENCES yuksystems(id), extsystem INTEGER NOT NULL REFERENCES yuksystems(id), PRIMARY KEY (intsystem, extsystem), CONSTRAINT int_cannot_equal_ext CHECK (intsystem != extsystem) ); At least in the earlier versions of the SQL standard, it was actually illegal for a column constraint to reference any other columns. I'm not sure if that's still true in the latest spec. Postgres treats column constraints and table constraints alike, but other SQL databases are likely to be pickier. BTW, is there any actual need for the "id" column here, seeing that you have a natural primary key? regards, tom lane ---(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
Re: [SQL] stored procs in postgresql
thanks, I didn't even know about the string concatination function for this. Unfortunately, it was of no help. Specificly I am trying this, with the following error: SQL error: ERROR: syntax error at or near "' || $2 || '" at character 110 In statement: CREATE FUNCTION "getcensusbound" (geometry, character) RETURNS character AS 'SELECT bound_table.name FROM '' || $2 || '' bound_table WHERE bound_table.the_geom::bytea != ''null''::bytea AND contains(geometryn(bound_table.the_geom, 1), $1) = true;' LANGUAGE "sql" RETURNS NULL ON NULL INPUT -Cere On 9/24/05, Chris Browne <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] writes: > > I have been having some trouble with plsql stored procs in postgres in > > that I can > > make a table name a variable in the stored proc. Is there some > > special way to make this happen that I am unaware of? > > > > For example, I want to do something like: > > > > stored_proc(integer,varchar) > > > > SELECT table_name.id > > FROM table_name $2 > >WHERE table_name.id=$1 > > > > but I get an error about the $2 argument being no good. > > > > Does anyone know how I can deal with this? > > To do this sort of thing, you need to build up the query as a string, > and EXECUTE it. > > Thus... >query := 'select t.id from ' || $2 || ' t where t.id = ' || $1 || ';'; > > The other vital problem is that the select is in bad form. The actual > name of the table needs to come BEFORE the alias, not after. > > The following would represent more nearly legitimate SQL... > > SELECT table_name.id > FROM $2 table_name > WHERE table_name.id=$1 > -- > let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];; > http://cbbrowne.com/info/spiritual.html > And me, with this terrible pain in all the diodes down my left side... > -- Marvin the Paranoid Android > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > -- Cere Davis [EMAIL PROTECTED] --- GPG Key: http://staff.washington.edu/cere/pubkey.asc GPG fingerprint (ID# 73FCA9E6) : F5C7 627B ECBE C735 117B 2278 9A95 4C88 73FC A9E6 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] redundancy in CHECK CONSTRAINTs
Thank you for your advice, Tom. I've re-done the table in my db using the schema you describe below. The is a need for the id field. Other tables in my applications use it to refer to any one intsystem/extsystem relationship and be able to provide users with one simple number to use to refer to them. Thank you. Ferindo Tom Lane wrote: Ferindo Middleton Jr <[EMAIL PROTECTED]> writes: I have the following table: CREATE TABLE gyuktnine ( id SERIAL, intsystem INTEGER NOT NULL REFERENCES yuksystems(id) CONSTRAINT int_cannot_equal_ext CHECK (intsystem != extsystem), extsystem INTEGER NOT NULL REFERENCES yuksystems(id) CONSTRAINT ext_cannot_equal_int CHECK (extsystem != intsystem), PRIMARY KEY (intsystem, extsystem) ); Is this redundant? Yes. I think it's poor style too: a constraint referencing multiple columns should be written as a table constraint not a column constraint. That is, you ought to write CREATE TABLE gyuktnine ( idSERIAL, intsystem INTEGER NOT NULL REFERENCES yuksystems(id), extsystem INTEGER NOT NULL REFERENCES yuksystems(id), PRIMARY KEY (intsystem, extsystem), CONSTRAINT int_cannot_equal_ext CHECK (intsystem != extsystem) ); At least in the earlier versions of the SQL standard, it was actually illegal for a column constraint to reference any other columns. I'm not sure if that's still true in the latest spec. Postgres treats column constraints and table constraints alike, but other SQL databases are likely to be pickier. BTW, is there any actual need for the "id" column here, seeing that you have a natural primary key? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
