pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction to total reset a schema
6+ messages / 4 participants
[nested] [flat]
* Function to total reset a schema
@ 2011-05-28 14:16 Surfing <onlinesurfing@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Surfing @ 2011-05-28 14:16 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I need to write a function that totally empty a schema.
So I have written a TRUNCATE statement for each table and set to 0 each
sequence.
Btw, it could be good to execute a vacuum statement on each table, but
from within the function this is not allowed.
Is there a way to obtain the same result in another way (without using
the vacuum)?
Thanks
^ permalink raw reply [nested|flat] 6+ messages in thread
* Function to total reset a schema
@ 2011-05-29 07:38 Surfing <onlinesurfing@gmail.com>
2011-05-29 07:49 ` Re: Function to total reset a schema Scott Marlowe <scott.marlowe@gmail.com>
2011-05-29 07:54 ` Re: Function to total reset a schema Thomas Kellerer <spam_eater@gmx.net>
2011-05-30 14:45 ` Re: Function to total reset a schema Rob Sargent <robjsargent@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Surfing @ 2011-05-29 07:38 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I need to write a function that totally empty a schema.
So I have written a TRUNCATE statement for each table and set to 0 each
sequence.
Btw, it could be good to execute a vacuum statement on each table, but
from within the function this is not allowed.
Is there a way to obtain the same result in another way (without using
the vacuum)?
Thanks
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Function to total reset a schema
2011-05-29 07:38 Function to total reset a schema Surfing <onlinesurfing@gmail.com>
@ 2011-05-29 07:49 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Scott Marlowe @ 2011-05-29 07:49 UTC (permalink / raw)
To: Surfing <onlinesurfing@gmail.com>; +Cc: pgsql-sql
On Sun, May 29, 2011 at 1:38 AM, Surfing <onlinesurfing@gmail.com> wrote:
> Hi all,
> I need to write a function that totally empty a schema.
>
> So I have written a TRUNCATE statement for each table and set to 0 each
> sequence.
> Btw, it could be good to execute a vacuum statement on each table, but from
> within the function this is not allowed.
You shouldn't need to vacuum truncated tables, as they're basically at
a lower level just wiped out and replaced by a new empty file / table.
Same goes for the indexes I believe.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Function to total reset a schema
2011-05-29 07:38 Function to total reset a schema Surfing <onlinesurfing@gmail.com>
@ 2011-05-29 07:54 ` Thomas Kellerer <spam_eater@gmx.net>
2011-05-29 08:03 ` Re: Function to total reset a schema Surfing <onlinesurfing@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Thomas Kellerer @ 2011-05-29 07:54 UTC (permalink / raw)
To: pgsql-sql
Surfing wrote on 29.05.2011 09:38:
> Hi all,
> I need to write a function that totally empty a schema.
>
> So I have written a TRUNCATE statement for each table and set to 0 each sequence.
> Btw, it could be good to execute a vacuum statement on each table, but from within the function this is not allowed.
>
> Is there a way to obtain the same result in another way (without using the vacuum)?
>
If you are only using a single schema in your database, you could create an empty database with all your tables that you then use as the template database when creating a new one:
Then each time you want to reset your schema (=database) you do a
drop database real_database;
create database real_database template template_database;
Then you don't need to adjust your "reset" script if your database changes (you only maintain the template database using your SQL scripts)
Thomas
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Function to total reset a schema
2011-05-29 07:38 Function to total reset a schema Surfing <onlinesurfing@gmail.com>
2011-05-29 07:54 ` Re: Function to total reset a schema Thomas Kellerer <spam_eater@gmx.net>
@ 2011-05-29 08:03 ` Surfing <onlinesurfing@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Surfing @ 2011-05-29 08:03 UTC (permalink / raw)
To: pgsql-sql
I have two schemas, so I can't do that ...
@ Scott. You are totally right ... I have just read this on the
documentation:
"TRUNCATE quickly removes all rows from a set of tables. It has the same
effect as an unqualified
DELETE on each table, but since it does not actually scan the tables it
is faster. Furthermore, it reclaims
disk space immediately, rather than requiring a subsequent VACUUM
operation. This is most useful on
large tables."
So the VACUUM should not really be necessary..thanks.
Il 29/05/2011 09:54, Thomas Kellerer ha scritto:
> Surfing wrote on 29.05.2011 09:38:
>> Hi all,
>> I need to write a function that totally empty a schema.
>>
>> So I have written a TRUNCATE statement for each table and set to 0
>> each sequence.
>> Btw, it could be good to execute a vacuum statement on each table,
>> but from within the function this is not allowed.
>>
>> Is there a way to obtain the same result in another way (without
>> using the vacuum)?
>>
>
> If you are only using a single schema in your database, you could
> create an empty database with all your tables that you then use as the
> template database when creating a new one:
>
> Then each time you want to reset your schema (=database) you do a
>
> drop database real_database;
> create database real_database template template_database;
>
> Then you don't need to adjust your "reset" script if your database
> changes (you only maintain the template database using your SQL scripts)
>
> Thomas
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Function to total reset a schema
2011-05-29 07:38 Function to total reset a schema Surfing <onlinesurfing@gmail.com>
@ 2011-05-30 14:45 ` Rob Sargent <robjsargent@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Rob Sargent @ 2011-05-30 14:45 UTC (permalink / raw)
To: Surfing <onlinesurfing@gmail.com>; +Cc: pgsql-sql
I would hope you have readily at hand the ddl for the schema in
question. Then it's simply a matter of drop schema <name> cascade and
re-run you ddl scripts.
Surfing wrote:
> Hi all,
> I need to write a function that totally empty a schema.
>
> So I have written a TRUNCATE statement for each table and set to 0
> each sequence.
> Btw, it could be good to execute a vacuum statement on each table, but
> from within the function this is not allowed.
>
> Is there a way to obtain the same result in another way (without using
> the vacuum)?
>
> Thanks
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-05-30 14:45 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-28 14:16 Function to total reset a schema Surfing <onlinesurfing@gmail.com>
2011-05-29 07:38 Function to total reset a schema Surfing <onlinesurfing@gmail.com>
2011-05-29 07:49 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-05-29 07:54 ` Thomas Kellerer <spam_eater@gmx.net>
2011-05-29 08:03 ` Surfing <onlinesurfing@gmail.com>
2011-05-30 14:45 ` Rob Sargent <robjsargent@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox