Postgres Pro
Downloads
Home   >   mailing lists

Thread: create snapshot?

create snapshot?

From
hubert depesz lubaczewski
Date:
hi, i was wondering if there is something in postgresql like "create snapshot".
i once saw ti on friend's database (dont know what it was), and i really like
it.
snapshot is something like materialized view.
it is a physically existing table, with only select's allowable, and update
snapshot command which recreates the table using defined at "create snapshot"
selects.
this is especially usefull for data warehouses, but not only, and could greatly
improve performance of database system.
importnant thing is that snapshots are not only for one session.
they are difned, created, and stay on storage after closing session - till some
other sessions does make "drop snapshot".

i'm not sure if i'm clear, but ... let's hope so.

any way to do it in pgsql?

depesz

--
hubert depesz lubaczewski
------------------------------------------------------------------------
     najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo,
      jest niesamowita wręcz łatwość unikania kontaktów z nim ...

Re: create snapshot?

From
hubert depesz lubaczewski
Date:
On Fri, Nov 24, 2000 at 11:23:41AM -0000, Alistair Hopkins wrote:
> assuming users are logging on as psqluser but the db is owned by psqlowner:
> create table mysnapshot as select * from myuser;
> grant select on mysnapshot to psqluser;

o.k. but updating it requires to drop table, and recreate using full select.
and i want it to work like:

update snapshot mysnapshot;
done;

depesz

--
hubert depesz lubaczewski
------------------------------------------------------------------------
     najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo,
      jest niesamowita wręcz łatwość unikania kontaktów z nim ...

Re: create snapshot?

From
Peter Eisentraut
Date:
hubert depesz lubaczewski writes:

> o.k. but updating it requires to drop table, and recreate using full select.
> and i want it to work like:
>
> update snapshot mysnapshot;
> done;

Write update rules or triggers on the snapshot table, and write a stored
procedure to encapsulate the "update snapshot" part.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

Re: create snapshot?

From
Tom Lane
Date:
hubert depesz lubaczewski <depesz@depesz.pl> writes:
> hi, i was wondering if there is something in postgresql like "create
> snapshot".

Seems like "SELECT INTO table" or "CREATE TABLE ... AS SELECT ..."
will do this just fine ...

            regards, tom lane