Обсуждение: BUG #7842: pg_dump scripts view to table

The following bug has been logged on the website:

Bug reference:      7842
Logged by:          Maciej =C5=81opusza=C5=84ski
Email address:      lopuszanski@oleofarm.com
PostgreSQL version: 9.1.7
Operating system:   ubuntu 12.04
Description:        =


hello,
1. after using pg_dump to dump WHOLE database to file, 1 of views 'turned'
into a table.

so there is no 'create or replace VIEW XXXX .......' with definition, but
instead:
its scripted as 'create TABLE XXXX......' and definition.(in file that
pg_dump created)

2. but after using pg_dump to dump only this 1 problematic 'view'  (with -t
option) it stores it correcly as 'create or replace VIEW XXXX .......'

it looks like pg_dump has problem with dependancies of this view.....

what other information I should supply to solve this mystery of pg_dump?

lopuszanski@oleofarm.com writes:
> 1. after using pg_dump to dump WHOLE database to file, 1 of views 'turned'
> into a table.

> so there is no 'create or replace VIEW XXXX .......' with definition, but
> instead:
> its scripted as 'create TABLE XXXX......' and definition.(in file that
> pg_dump created)

There's probably a CREATE RULE command further down that turns it into a
view again.

> 2. but after using pg_dump to dump only this 1 problematic 'view'  (with -t
> option) it stores it correcly as 'create or replace VIEW XXXX .......'

That's not terribly surprising.  The separate table and rule entries
would only be used when it's necessary to work around a circular
dependency with some other object.

> it looks like pg_dump has problem with dependancies of this view.....

I see no reason to think there's a bug here.  If you still think it's
a bug, you need to provide enough information for someone else to
reproduce the case.
http://www.postgresql.org/docs/9.1/static/bug-reporting.html

            regards, tom lane

is there a way to find exactly what object/table/column creates this
circular dependency?

pg_dumb even in verbose mode does not warn/error of this situation. I
managed to find this by testing copy of production db(this should be
reported by pg_dump). also there is nothing fancy about this 'view' of mine.

in db it looks like this:
t1, t2, t3.......t10
create view1 from t1-t10

create view2 from view1 (only agregation with group by).

pg_dump creates table from view1.

best regards,
maciej lopuszanski


W dniu 31.01.2013 20:27, Tom Lane pisze:
> lopuszanski@oleofarm.com writes:
>> 1. after using pg_dump to dump WHOLE database to file, 1 of views 'turned'
>> into a table.
>> so there is no 'create or replace VIEW XXXX .......' with definition, but
>> instead:
>> its scripted as 'create TABLE XXXX......' and definition.(in file that
>> pg_dump created)
> There's probably a CREATE RULE command further down that turns it into a
> view again.
>
>> 2. but after using pg_dump to dump only this 1 problematic 'view'  (with -t
>> option) it stores it correcly as 'create or replace VIEW XXXX .......'
> That's not terribly surprising.  The separate table and rule entries
> would only be used when it's necessary to work around a circular
> dependency with some other object.
>
>> it looks like pg_dump has problem with dependancies of this view.....
> I see no reason to think there's a bug here.  If you still think it's
> a bug, you need to provide enough information for someone else to
> reproduce the case.
> http://www.postgresql.org/docs/9.1/static/bug-reporting.html
>
>             regards, tom lane

Maciej Łopuszański <lopuszanski@oleofarm.com> writes:
> is there a way to find exactly what object/table/column creates this
> circular dependency?

Look into pg_depend for entries referencing the view or the view's
rowtype.  (Offhand I'd bet it's the latter.)  So the refobjid would
be 'viewname'::regclass or 'viewname'::regtype.

> pg_dumb even in verbose mode does not warn/error of this situation.

Why should it?  This isn't a bug, this is merely an implementation
detail.
        regards, tom lane

why?

because 1 on views didnt recreate after restoring from backup, we had a table instead of view. that means incomplete backup without even our knowledge (no warn,error), if its not a bug what other word would be better to describe this?

and now the funny part:
I deleted and created this view(and dependant views) from the same definition it was stored in pg, and the problem just vanished....

this view now is properly stored by pg_dump,  and properly restored by psql.

Maciej Łopuszański <lopuszanski@oleofarm.com> writes:

is there a way to find exactly what object/table/column creates this 
circular dependency?

Look into pg_depend for entries referencing the view or the view's
rowtype.  (Offhand I'd bet it's the latter.)  So the refobjid would
be 'viewname'::regclass or 'viewname'::regtype.

pg_dumb even in verbose mode does not warn/error of this situation.

Why should it?  This isn't a bug, this is merely an implementation
detail.
		regards, tom lane