Horton

Thread

  1. \d shows all my tables twice

    Karl DeBisschop <kdebisschop@range.infoplease.com> — 1999-12-10T17:02:46Z

    I'm using the postgresql-6.5.3-1 rpm from PostgreSQLs website on
redhat 6.0

Some time between yesterday and today postgres developed the habit of
listing all may table twice when I do \d or \dS from psql.  Right now
this is only annoying, but does this mean there is a system corruption
I need to fix?

I've destroyed all my databases other than template1 and vacuumed in
template1.  My next thought is to reinstall, but I'd rather not if I
don't have too.

-- 
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 Development
http://netsaintplug.sourceforge.net

  2. Re: [GENERAL] \d shows all my tables twice

    Bruce Momjian <pgman@candle.pha.pa.us> — 1999-12-10T17:15:13Z

    You have duplicate entries in pg_shadow/pg_user table.


> 
> I'm using the postgresql-6.5.3-1 rpm from PostgreSQLs website on
> redhat 6.0
> 
> Some time between yesterday and today postgres developed the habit of
> listing all may table twice when I do \d or \dS from psql.  Right now
> this is only annoying, but does this mean there is a system corruption
> I need to fix?
> 
> I've destroyed all my databases other than template1 and vacuumed in
> template1.  My next thought is to reinstall, but I'd rather not if I
> don't have too.
> 
> -- 
> 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 Development
> http://netsaintplug.sourceforge.net
> 
> ************
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  3. Re: [GENERAL] \d shows all my tables twice

    Karl DeBisschop <kdebisschop@range.infoplease.com> — 1999-12-10T17:25:06Z

    >   From: Bruce Momjian <pgman@candle.pha.pa.us>
>
>   You have duplicate entries in pg_shadow/pg_user table.
>
>   > 
>   > I'm using the postgresql-6.5.3-1 rpm from PostgreSQLs website on
>   > redhat 6.0
>   > 
>   > Some time between yesterday and today postgres developed the habit of
>   > listing all may table twice when I do \d or \dS from psql.  Right now
>   > this is only annoying, but does this mean there is a system corruption
>   > I need to fix?
>   > 
>   > I've destroyed all my databases other than template1 and vacuumed in
>   > template1.  My next thought is to reinstall, but I'd rather not if I
>   > don't have too.
>   > 
>   > -- 
>   > Karl DeBisschop <kdebisschop@alert.infoplease.com>

Dead on right.  Duplicates for postgres itself and the two admins.
Thanks.

By the way, you guys are great.  I really appreciate the work you do.
And I wanted to say the the RPM packaging was very well done in my
opinion - it installed like a dream, and the init script did a perfect
job of saving me the effort of manually initializing the DBMS.  Kudos
to all involved.

-- 
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

  4. Re: [GENERAL] \d shows all my tables twice

    Bruce Momjian <pgman@candle.pha.pa.us> — 1999-12-10T17:47:06Z

    > Dead on right.  Duplicates for postgres itself and the two admins.
> Thanks.
> 
> By the way, you guys are great.  I really appreciate the work you do.
> And I wanted to say the the RPM packaging was very well done in my
> opinion - it installed like a dream, and the init script did a perfect
> job of saving me the effort of manually initializing the DBMS.  Kudos
> to all involved.

Next release will not allow this problem to happen.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  5. Re: [GENERAL] \d shows all my tables twice

    Stephen Walton <swalton@galileo.csun.edu> — 1999-12-10T17:52:47Z

    I second Karl DeBisschop's comments below.  It took me hours to get
postgresql working on an HP/UX system, compared with minutes on Linux.
I'm using Linux as our main database server now.

By the way, I know about using pg_dump to backup the database and I do
that.  Is there a good way to maintain a second identical copy of the
database on another machine?  Will simply copying the dump over and
restoring it with psql do the trick?  Would I need to delete an old copy
of the same database first?  We have a somewhat slow Internet connection
to our Linux system's location and it would be nice to have an alternate
site with the same data.

--
Stephen Walton, Professor of Physics and Astronomy,
California State University, Northridge
stephen.walton@csun.edu

On Fri, 10 Dec 1999, Karl DeBisschop wrote:

> 
> By the way, you guys are great.  I really appreciate the work you do.
> And I wanted to say the the RPM packaging was very well done in my
> opinion - it installed like a dream, and the init script did a perfect
> job of saving me the effort of manually initializing the DBMS.  Kudos
> to all involved.

  6. Mirroring a DB (was Re: [GENERAL] \d shows all my tables twice)

    Karl DeBisschop <kdebisschop@range.infoplease.com> — 1999-12-10T18:09:18Z

    > By the way, I know about using pg_dump to backup the database and I do
> that.  Is there a good way to maintain a second identical copy of the
> database on another machine?  Will simply copying the dump over and
> restoring it with psql do the trick?  Would I need to delete an old copy
> of the same database first?  We have a somewhat slow Internet connection
> to our Linux system's location and it would be nice to have an alternate
> site with the same data.

We sometimes do:

 pg_dump -o -h <live> <table> | psql -h <mirror> <table>

(Note that you will probably want -z as well if pre-6.5)

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.

-- 
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

  7. Re: Mirroring a DB

    Peter Eisentraut <peter_e@gmx.net> — 1999-12-11T02:00:12Z

    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 :).

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

  8. Re: Mirroring a DB

    Karl DeBisschop <kdebisschop@range.infoplease.com> — 1999-12-11T14:41:41Z

    >   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

  9. Re: Mirroring a DB

    Peter Eisentraut <e99re41@docs.uu.se> — 1999-12-11T14:51:44Z

    Could the God of Rules please comment on this? It seems to be a deficiency
in the get_rule_def (sp?) backend function. Perhaps to play it safe all
attributes should be fully qualified, but that's probably not as easy as
it sounds.

On Sat, 11 Dec 1999, Karl DeBisschop wrote:

> 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;

[my highlightings]

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden