Thread: \d shows all my tables twice
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
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
> 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
> 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
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.
> 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
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
> 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