I see the problem.  Your 7.0.3 dump contains several instances of this
pattern:

CREATE TABLE "users_alertable" (
	"user_id" int4,
	"email" character varying(100),
	"first_names" character varying(100),
	"last_name" character varying(100),
	"password" character varying(30)
);

...

CREATE FUNCTION "user_vacations_kludge" (int4 ) RETURNS int4 AS '
begin
 return count(*) 
   from user_vacations v, users u
   where u.user_id = $1 and v.user_id = u.user_id
   and current_timestamp between v.start_date and v.end_date;
end;' LANGUAGE 'plpgsql';

...

CREATE RULE "_RETusers_alertable" AS ON SELECT TO users_alertable DO INSTEAD SELECT u.user_id, u.email, u.first_names, u.last_name, u."password" FROM users u WHERE (((((u.on_vacation_until ISNULL) OR (u.on_vacation_until < "timestamp"('now'::text))) AND (u.user_state = 'authorized'::"varchar")) AND ((u.email_bouncing_p ISNULL) OR (u.email_bouncing_p = 'f'::bpchar))) AND (user_vacations_kludge(u.user_id) = 0));

Although this works fine, 7.1 folds the table + rule down into a single
CREATE VIEW, which comes before the CREATE FUNCTION because that's what
the OID ordering suggests will work.  Ugh.

A possible kluge answer is to make pg_dump's OID-ordering of views
depend on the OID of the view rule rather than the view relation.
I am not sure if that would break any cases that work now, however.

			regards, tom lane

Fri, 13 Apr 2001 17:34:51 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump ordering problem (rc4) 
At 17:34 13/04/01 -0400, Tom Lane wrote:
>
>A possible kluge answer is to make pg_dump's OID-ordering of views
>depend on the OID of the view rule rather than the view relation.
>I am not sure if that would break any cases that work now, however.
>

This seems good to me; it should be based on the 'oid of the view', and
AFAICT, the rule OID should be it. Should I do this?


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Sat, 14 Apr 2001 10:36:50 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump ordering problem (rc4) 
> At 17:34 13/04/01 -0400, Tom Lane wrote:
> >
> >A possible kluge answer is to make pg_dump's OID-ordering of views
> >depend on the OID of the view rule rather than the view relation.
> >I am not sure if that would break any cases that work now, however.
> >
> 
> This seems good to me; it should be based on the 'oid of the view', and
> AFAICT, the rule OID should be it. Should I do this?

The view oid is certainly better than the base relation oid.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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, 13 Apr 2001 21:15:21 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dump ordering problem (rc4)
At 21:15 13/04/01 -0400, Bruce Momjian wrote:
>> At 17:34 13/04/01 -0400, Tom Lane wrote:
>> >
>> >A possible kluge answer is to make pg_dump's OID-ordering of views
>> >depend on the OID of the view rule rather than the view relation.
>> >I am not sure if that would break any cases that work now, however.
>> >
>> 
>> This seems good to me; it should be based on the 'oid of the view', and
>> AFAICT, the rule OID should be it. Should I do this?
>
>The view oid is certainly better than the base relation oid.
>

Since I'm in pg_dump at the moment, I'll make the change...



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Sat, 14 Apr 2001 11:43:02 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump ordering problem (rc4)
>>> >
>>> >A possible kluge answer is to make pg_dump's OID-ordering of views
>>> >depend on the OID of the view rule rather than the view relation.
>>> >I am not sure if that would break any cases that work now, however.
>>> >
>>> 
>>> This seems good to me; it should be based on the 'oid of the view', and
>>> AFAICT, the rule OID should be it. Should I do this?
>>
>>The view oid is certainly better than the base relation oid.
>>
>
>Since I'm in pg_dump at the moment, I'll make the change...
>

Having now looked at pg_dump more closely, I'm not at all sure I want to
make the change directly in pg_dump. The reason is that I am trying to move
version-specific stuff from pg_dump, and I currently get a view definition
by doing 'select pg_getviewdef(<name>)' (rather than looking up the rule etc).

Would people mind me adding a 'pg_getviewoid(<name>)' for pg_dump's use?
(especially since 7.1 now seems to be out...)





----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Sat, 14 Apr 2001 17:30:25 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump ordering problem (rc4)
Philip Warner <pjw@rhyme.com.au> writes:
> Having now looked at pg_dump more closely, I'm not at all sure I want to
> make the change directly in pg_dump. The reason is that I am trying to move
> version-specific stuff from pg_dump, and I currently get a view definition
> by doing 'select pg_getviewdef(<name>)' (rather than looking up the rule etc).

> Would people mind me adding a 'pg_getviewoid(<name>)' for pg_dump's use?

While that would be a clean solution, it would mean that the problem
will remain until 7.2, because you don't get to assume another initdb
until 7.2.  I don't think we want to wait that long; I want to see a fix
of some kind in 7.1.1.

A possible compromise is to do a direct lookup of the OID in 7.1.*
with plans to replace it with some backend-side solution in 7.2 and
beyond.

			regards, tom lane

Sat, 14 Apr 2001 03:53:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump ordering problem (rc4) 
At 03:53 14/04/01 -0400, Tom Lane wrote:
>
>> Would people mind me adding a 'pg_getviewoid(<name>)' for pg_dump's use?
>
>While that would be a clean solution, it would mean that the problem
>will remain until 7.2, because you don't get to assume another initdb
>until 7.2.  I don't think we want to wait that long; I want to see a fix
>of some kind in 7.1.1.
>
>A possible compromise is to do a direct lookup of the OID in 7.1.*
>with plans to replace it with some backend-side solution in 7.2 and
>beyond.
>

I don't suppose we can change the pg_views view without an initdb?

If so, a better solution would be to add a 'view_oid' column. Otherwise,
I'll have to kludge pg_dump.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Sat, 14 Apr 2001 18:29:30 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump ordering problem (rc4) 
At 18:29 14/04/01 +1000, Philip Warner wrote:
>
>I don't suppose we can change the pg_views view without an initdb?
>

Having now looked at the source, I realize that initdb is where this view
is defined. However, is there any reason that we can not change this
definition when upgrading to 7.1.1? 

The embargo on initdb is, I think, primarily related to avoiding
export/import of databases - is that right? If so, then doing
non-destructive changes to things like system views does not seem too evil
(in this case an update of a row of pg_rewrite and the addition of an attr
to pg_views, I think).

Am I missing something here? ISTM that the more higher level definitions we
have (eg. functions returning multiple rows, DEFINITION SCHEMAs etc), the
more we may need to allow changes to be made of things that are
*customarily* defined in initdb.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Sat, 14 Apr 2001 21:15:28 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump ordering problem (rc4) 
Philip Warner <pjw@rhyme.com.au> writes:
> I don't suppose we can change the pg_views view without an initdb?

No, not very readily.

Even assuming that we were willing to require dbadmins to run a script
during upgrade (which I wouldn't want to do unless forced to it), it's
not that easy to fix all the occurrences of a system view.  Remember
there will be a separate copy in each database, including template0
which you can't even get to.  On top of which, DROP VIEW/CREATE VIEW
wouldn't work because the view would then have the wrong OID, and would
look like a user-created object to pg_dump.  You'd have to manually
manipulate tuples in pg_class, pg_attribute, pg_rewrite, etc.

Kluging pg_dump is a *lot* cleaner.

I agree with the idea of adding the rule OID as a new column of pg_views
for 7.2, however.

			regards, tom lane

Sat, 14 Apr 2001 12:50:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump ordering problem (rc4) 