Thread: 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?<br /><br /> because 1 on views didnt recreate after restoring from backup, we had a table instead of view. that
meansincomplete backup without even our knowledge (no warn,error), if its not a bug what other word would be better to
describethis?<br /><br /> and now the funny part:<br /> I deleted and created this view(and dependant views) from the
samedefinition it was stored in pg, and the problem just vanished....<br /><br /> this view now is properly stored by
pg_dump, and properly restored by psql.<br /><br /><br /><div class="moz-cite-prefix">W dniu 01.02.2013 16:06, Tom Lane
pisze:<br/></div><blockquote cite="mid:14030.1359731218@sss.pgh.pa.us" type="cite"><pre wrap="">Maciej Łopuszański <a
class="moz-txt-link-rfc2396E"href="mailto:lopuszanski@oleofarm.com"><lopuszanski@oleofarm.com></a> writes:
</pre><blockquote type="cite"><pre wrap="">is there a way to find exactly what object/table/column creates this
circular dependency?
</pre></blockquote><pre wrap="">
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.
</pre><blockquote type="cite"><pre wrap="">pg_dumb even in verbose mode does not warn/error of this situation.
</pre></blockquote><pre wrap="">
Why should it? This isn't a bug, this is merely an implementation
detail.
regards, tom lane
</pre></blockquote><br /><div class="moz-signature">-- <br /><span style="color: #004385; font-family: verdana;
font-size: 12px"> Maciej Łopuszański<br /> Programista<br /> mobile: +48 797 119 384<br /> e-mail: <a
class="moz-txt-link-abbreviated"href="mailto:lopuszanski@oleofarm.com">lopuszanski@oleofarm.com</a><br /></span><br
/><imgalt="" border="0" height="122" src="cid:part1.00080507.04080902@oleofarm.com" width="300" /><br /><span
style="color:#004385; font-family: verdana; font-size: 11px"> Oleofarm Sp. z o.o., ul. Fabryczna 16, 55-080
Pietrzykowice<br/> tel. +48 71 316 94 55, fax. +48 71 316 94 57, <a
href="http://www.oleofarm.com.pl/">www.oleofarm.com.pl</a><br/> NIP: 896-13-78-324, REGON: 933023467<br /><br /> Sąd
Rejonowydla Wrocławia Fabrycznej we Wrocławiu , VI Wydział Gospodarczy <br /> Krajowego Rejestru Sądowego pod numerem
KRS218272, kapitał zakładowy: 50 000 zł</span><br /><br /><span style="color: #666666; font-family: verdana; font-size:
10px"> Zawarte w niniejszej wiadomości lub załączonym dokumencie informacje są POUFNE<br /> i przeznaczone tylko
dlaoznaczonego adresata. Rozpowszechnianie, ujawnianie <br /> i kopiowanie tych informacji jest zabronione. Jeżeli
niniejszawiadomość trafiła <br /> do Państwa przez pomyłkę, bardzo prosimy o powiadomienie nas o tym fakcie <br /> i
odesłaniedokumentu lub natychmiastowe jego zniszczenie. <br /><br /> The information contained in the e-mail or
attacheddocument is CONFIDENTIAL <br /> and is intended only for the use of the individual to whom it is addressed. <br
/>If you are not the intended recipient or employee responsible to deliver to the <br /> intended recipient, you are
herebynotified that any dissemination, distribution <br /> or copying of the communication is strictly prohibited. If
youhave received <br /> the e-mail in error, please immediately notify us and return the original message <br /> to us
atthe address above or destroy it. </span></div>