
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

Fri, 10 Dec 1999 12:02:46 -0500
Karl DeBisschop <kdebisschop@range.infoplease.com>
\d shows all my tables twice

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

Fri, 10 Dec 1999 12:15:13 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [GENERAL] \d shows all my tables twice

>   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

Fri, 10 Dec 1999 12:25:06 -0500
Karl DeBisschop <kdebisschop@range.infoplease.com>
Re: [GENERAL] \d shows all my tables twice
> 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

Fri, 10 Dec 1999 12:47:06 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [GENERAL] \d shows all my tables twice
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.


Fri, 10 Dec 1999 09:52:47 -0800 (PST)
Stephen Walton <swalton@galileo.csun.edu>
Re: [GENERAL] \d shows all my tables twice

> 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

Fri, 10 Dec 1999 13:09:18 -0500
Karl DeBisschop <kdebisschop@range.infoplease.com>
Mirroring a DB (was Re: [GENERAL] \d shows all my tables twice)
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



Sat, 11 Dec 1999 03:00:12 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
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

Sat, 11 Dec 1999 09:41:41 -0500
Karl DeBisschop <kdebisschop@range.infoplease.com>
Re: Mirroring a DB
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


Sat, 11 Dec 1999 15:51:44 +0100 (MET)
Peter Eisentraut <e99re41@DoCS.UU.SE>
Re: Mirroring a DB
Peter Eisentraut <e99re41@DoCS.UU.SE> writes:
> 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.

I'm not the god of rules, but I have messed with that code.  Current
sources will put table prefixes on every var in a rule if more than one
table appears in the rule's rangelist.  I think this should be
sufficient, but it's hard to tell from this incomplete example;
are you actually complaining about some special case that arises when
a column has the same name as its table?

It would be nice to see the original view definition (plus enough table
definitions to let us create the rule without guessing).

			regards, tom lane

Sat, 11 Dec 1999 13:22:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: Mirroring a DB 

>   I'm not the god of rules, but I have messed with that code.  Current
>   sources will put table prefixes on every var in a rule if more than one
>   table appears in the rule's rangelist.  I think this should be
>   sufficient, but it's hard to tell from this incomplete example;
>   are you actually complaining about some special case that arises when
>   a column has the same name as its table?

As far as I can see, the problem has nothing to do with whether the
table has the same name as the column.  The problem arises when the
two tables each have attributes with the same name.  So for instance
when t1 has an attribute (say "foriegn_oid") that joins to oid in t2,
the rule gets saved as just "oid" so when recreated, the parser can't
determine which oid to join to.

>   It would be nice to see the original view definition (plus enough table
>   definitions to let us create the rule without guessing).

Sorry, I really didn't think this was an unknown issue, otherwise I
would have sent in a bug report with such details. I think the stuff
below should cover it.  If there's any more info that I can provide,
just ask.

Karl

==============================================================================

create view element_types as select fcat.ref,fcat.fcat,ecat.ecat,ecat.oid as ecat_oid,ecat.ord,ecat.emin,ecat.emax,ecat.rows from fcat,ecat where fcat.ref=ecat.fcat;

------------------------------------------------------------------------------

feature=> \d fcat 
Table    = fcat
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| ref                              | int2 not null                    |     2 |
| owner                            | int2 not null                    |     2 |
| fcat                             | text not null                    |   var |
+----------------------------------+----------------------------------+-------+
feature=> \d ecat
Table    = ecat
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| fcat                             | int2 not null                    |     2 |
| ord                              | int2 not null                    |     2 |
| emin                             | int2 not null                    |     2 |
| emax                             | int2                             |     2 |
| rows                             | int2 not null                    |     2 |
| ecat                             | text not null                    |   var |
+----------------------------------+----------------------------------+-------+
Index:    zecat_sf


Sat, 11 Dec 1999 16:32:28 -0500
Karl DeBisschop <kdebisschop@range.infoplease.com>
Re: [HACKERS] Re: Mirroring a DB

>   I'm not the god of rules, but I have messed with that code.  Current
>   sources will put table prefixes on every var in a rule if more than one
>   table appears in the rule's rangelist.  I think this should be
>   sufficient, but it's hard to tell from this incomplete example;
>   are you actually complaining about some special case that arises when
>   a column has the same name as its table?
>
>   It would be nice to see the original view definition (plus enough table
>   definitions to let us create the rule without guessing).
>
>			   regards, tom lane

I also looked back to double check versions.  Unbeknownst to me, the
source database is 6.5.1 - the destination is 6.5.3

Version 6.5.3 seem to behave as you said, so I'm guessing that this
fix occurred relatively recently and I was just unaware it had been
fixed.

Karl


Sat, 11 Dec 1999 16:45:44 -0500
Karl DeBisschop <kdebisschop@range.infoplease.com>
Re: [HACKERS] Re: Mirroring a DB
Karl DeBisschop <kdebisschop@range.infoplease.com> writes:
>> Current
>> sources will put table prefixes on every var in a rule if more than one
>> table appears in the rule's rangelist.  I think this should be
>> sufficient, but it's hard to tell from this incomplete example;

> Version 6.5.3 seem to behave as you said, so I'm guessing that this
> fix occurred relatively recently and I was just unaware it had been
> fixed.

Actually, 6.5.3 just unconditionally prefixes all vars in a decompiled
rule, all the time.  That was a quick-patch solution to the type of
problem you are complaining of.  Current sources (6.6/7.0-to-be) try to
be smarter by only prefixing vars when there is possible ambiguity (ie,
more than one table in the rangelist).  That's why I was concerned about
the details of your example --- I was wondering if this "improvement"
might fail under the right special case...

			regards, tom lane

Sun, 12 Dec 1999 01:03:12 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: Mirroring a DB 