Re: Mirroring a DB

>   From: Peter Eisentraut <peter_e@gmx.net>
>
>   On 1999-12-10, Karl DeBisschop mentioned:
>
>   >  pg_dump -o -h <live> <table> | psql -h <mirror> <table>
>   >
>   > This generally works, but has a habit recreating the views as actual
>   > tables.  Often you can live with this, and there may be a simple way
>   > to prevent it.  I just haven't found one yet.
>
>   I view *is* a table, with a ON SELECT rule on it. So writing
>
>   CREATE TABLE foo ( ... );
>   CREATE RULE _RETfoo AS ON SELECT DO INSTEAD SELECT your_stuff_here;
>
>   is equivalent to
>
>   CREATE VIEW foo AS SELECT your_stuff_here;
>
>   Perhaps it would be nicer if the dump contained the second version, but
>   you're not supposed to read these dumps (in case you didn't know :).

I was in fact aware of everything that you mentioned here.  The only
point I was trying make, albeit not clearly, is that when executing
the above pipe, the create rule provided by pg_dump is often ambiguous.

to use a real world example, this is the output from pg_dump for a
view that we have:

CREATE RULE "_RETelement_types" AS ON SELECT TO "element_types" DO INSTEAD SELECT "ref", "fcat", "ecat", "oid" AS
"ecat_oid","ord", "emin", "emax", "rows" FROM "fcat", "ecat" WHERE "ref" = "fcat"; 

In fact, it needs to be modified before it will parse to:

CREATE RULE "_RETelement_types" AS ON SELECT TO "element_types" DO INSTEAD SELECT "ref", fcat.fcat, "ecat", ecat.oid AS
"ecat_oid","ord", "emin", "emax", "rows" FROM "fcat", "ecat" WHERE fcat.ref = ecat.fcat; 

Since the rules come at the end of the pg_dump, the transfer mostly
works.  But I would not depend on it.

Now I'm not sure if this is a bug, since I think there are choices of
attribute names that will make the rule parse.  But it might be a bug, and
certainly the questioner should be aware that there are common
database structures for which the above command can fail to correctly
create the views.

Please forgive the sloppiness of my nomenclature if the this was not
clear before.  I had just assumed that this was a known issue, and
that a caution was justified.

--
Karl DeBisschop <kdebisschop@alert.infoplease.com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com  - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper

Netsaint Plugins Development
http://netsaintplug.sourceforge.net