Thread: Functions in CHECK constraint not getting dumped before tables.
Greetings! It is found that pg_dump does not dump function referred in CHECK constraint definations before dumping the table defination . As a result the tables do not get restored due to lack of defined functions. Is it something that will be worked upon in future ? regds mallah.
> It is found that pg_dump does not dump function referred in CHECK
> constraint definations before dumping the table defination . As a result
> the tables
> do not get restored due to lack of defined functions.
> Is it something that will be worked upon in future ?
This is fixed in CVS tip.
regards, tom lane
Tom Lane wrote:
Yes it did solve that problem.
another problem that i am facing is during pg_dump i get warning
like:
pg_dump: WARNING: owner of function "txtidx_in" appears to be invalid
pg_dump: WARNING: owner of function "txtidx_out" appears to be invalid
pg_dump: WARNING: owner of function "qtxt_in" appears to be invalid
pg_dump: WARNING: owner of function "qtxt_out" appears to be invalid
pg_dump: WARNING: owner of function "mqtxt_in" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_in" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_out" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_consistent" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_compress" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_decompress" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_penalty" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_picksplit" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_union" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_same" appears to be invalid
And in the dump there are lines like:
REVOKE ALL ON FUNCTION txtidx_in(opaque) FROM PUBLIC;
REVOKE ALL ON FUNCTION txtidx_in(opaque) FROM "";
SET SESSION AUTHORIZATION "103"; <---------------------------------
GRANT ALL ON FUNCTION txtidx_in(opaque) TO PUBLIC;
RESET SESSION AUTHORIZATION;
can you pls tell me how to get rid of the warnings and the invalid
id "103" in pg_proc i dont see any reference to SYSID 103
in the columns proowner or the acl columns.
Any other places where i should look?
Regds
Mallah.
It is found that pg_dump does not dump function referred in CHECK constraint definations before dumping the table defination . As a result the tables do not get restored due to lack of defined functions.Is it something that will be worked upon in future ?This is fixed in CVS tip.
Yes it did solve that problem.
another problem that i am facing is during pg_dump i get warning
like:
pg_dump: WARNING: owner of function "txtidx_in" appears to be invalid
pg_dump: WARNING: owner of function "txtidx_out" appears to be invalid
pg_dump: WARNING: owner of function "qtxt_in" appears to be invalid
pg_dump: WARNING: owner of function "qtxt_out" appears to be invalid
pg_dump: WARNING: owner of function "mqtxt_in" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_in" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_out" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_consistent" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_compress" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_decompress" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_penalty" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_picksplit" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_union" appears to be invalid
pg_dump: WARNING: owner of function "gtxtidx_same" appears to be invalid
And in the dump there are lines like:
REVOKE ALL ON FUNCTION txtidx_in(opaque) FROM PUBLIC;
REVOKE ALL ON FUNCTION txtidx_in(opaque) FROM "";
SET SESSION AUTHORIZATION "103"; <---------------------------------
GRANT ALL ON FUNCTION txtidx_in(opaque) TO PUBLIC;
RESET SESSION AUTHORIZATION;
can you pls tell me how to get rid of the warnings and the invalid
id "103" in pg_proc i dont see any reference to SYSID 103
in the columns proowner or the acl columns.
Any other places where i should look?
Regds
Mallah.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Rajesh Kumar Mallah wrote: > Tom Lane wrote: > >>>It is found that pg_dump does not dump function referred in CHECK >>>constraint definations before dumping the table defination . As a result >>>the tables >>>do not get restored due to lack of defined functions. >>> >>> >> >> >> >>>Is it something that will be worked upon in future ? >>> >>> >> >>This is fixed in CVS tip. >> >> > > Yes it did solve that problem. > another problem that i am facing is during pg_dump i get warning > like: This problem was also solved . The problem was that the corruption was in template1 and i was looking at a different database. i finally reset the template1 by using methods described in Adventures in PostgreSQL Episode 1: Restoring a Corrupted Template1 using Template0 , May 2002 Josh Berkus. Regds mallah. > > pg_dump: WARNING: owner of function "txtidx_in" appears to be invalid > pg_dump: WARNING: owner of function "txtidx_out" appears to be invalid > pg_dump: WARNING: owner of function "qtxt_in" appears to be invalid > pg_dump: WARNING: owner of function "qtxt_out" appears to be invalid > pg_dump: WARNING: owner of function "mqtxt_in" appears to be invalid > pg_dump: WARNING: owner of function "gtxtidx_in" appears to be invalid > pg_dump: WARNING: owner of function "gtxtidx_out" appears to be invalid > pg_dump: WARNING: owner of function "gtxtidx_consistent" appears to be > invalid > pg_dump: WARNING: owner of function "gtxtidx_compress" appears to be > invalid > pg_dump: WARNING: owner of function "gtxtidx_decompress" appears to be > invalid > pg_dump: WARNING: owner of function "gtxtidx_penalty" appears to be > invalid > pg_dump: WARNING: owner of function "gtxtidx_picksplit" appears to be > invalid > pg_dump: WARNING: owner of function "gtxtidx_union" appears to be invalid > pg_dump: WARNING: owner of function "gtxtidx_same" appears to be invalid > > And in the dump there are lines like: > > > REVOKE ALL ON FUNCTION txtidx_in(opaque) FROM PUBLIC; > REVOKE ALL ON FUNCTION txtidx_in(opaque) FROM ""; > *SET SESSION AUTHORIZATION "103"; <---------------------------------* > GRANT ALL ON FUNCTION txtidx_in(opaque) TO PUBLIC; > RESET SESSION AUTHORIZATION; > > > can you pls tell me how to get rid of the warnings and the invalid > id "103" in pg_proc i dont see any reference to SYSID 103 > in the columns proowner or the acl columns. > > Any other places where i should look? > > > Regds > Mallah. > > >> regards, tom lane >> >>---------------------------(end of broadcast)--------------------------- >>TIP 6: Have you searched our list archives? >> >> http://archives.postgresql.org >> >> >> >
Greetings! The only issue that i noticed now with pg_dump version 7.5dev is that create schemas are not dumped before user definations. which have their search_path set by ALTER USER command. eg ALTER USER arvind SET search_path TO 'erp'; ERROR: schema "erp" does not exist This is relatively harmless to me at least, but the point is to have pg_dump work without requiring any manual editing of the dumpfiles :) Regds Mallah. Rajesh Kumar Mallah wrote: > Rajesh Kumar Mallah wrote: > >> Tom Lane wrote: >> >>>> It is found that pg_dump does not dump function referred in CHECK >>>> constraint definations before dumping the table defination . As a >>>> result the tables >>>> do not get restored due to lack of defined functions. >>>> >>> >>> >>> >>> >>>> Is it something that will be worked upon in future ? >>>> >>> >>> >>> This is fixed in CVS tip. >>> >>> >> >> Yes it did solve that problem. >> another problem that i am facing is during pg_dump i get warning >> like: > > > > This problem was also solved . The problem was that the corruption > was in template1 and i was looking at a different database. > > i finally reset the template1 by using methods described in > > > Adventures in PostgreSQL > Episode 1: Restoring a Corrupted Template1 using Template0 , May 2002 > Josh Berkus. > > Regds > mallah. > >> >> pg_dump: WARNING: owner of function "txtidx_in" appears to be invalid >> pg_dump: WARNING: owner of function "txtidx_out" appears to be invalid >> pg_dump: WARNING: owner of function "qtxt_in" appears to be invalid >> pg_dump: WARNING: owner of function "qtxt_out" appears to be invalid >> pg_dump: WARNING: owner of function "mqtxt_in" appears to be invalid >> pg_dump: WARNING: owner of function "gtxtidx_in" appears to be invalid >> pg_dump: WARNING: owner of function "gtxtidx_out" appears to be invalid >> pg_dump: WARNING: owner of function "gtxtidx_consistent" appears to >> be invalid >> pg_dump: WARNING: owner of function "gtxtidx_compress" appears to be >> invalid >> pg_dump: WARNING: owner of function "gtxtidx_decompress" appears to >> be invalid >> pg_dump: WARNING: owner of function "gtxtidx_penalty" appears to be >> invalid >> pg_dump: WARNING: owner of function "gtxtidx_picksplit" appears to be >> invalid >> pg_dump: WARNING: owner of function "gtxtidx_union" appears to be >> invalid >> pg_dump: WARNING: owner of function "gtxtidx_same" appears to be invalid >> >> And in the dump there are lines like: >> >> >> REVOKE ALL ON FUNCTION txtidx_in(opaque) FROM PUBLIC; >> REVOKE ALL ON FUNCTION txtidx_in(opaque) FROM ""; >> *SET SESSION AUTHORIZATION "103"; <---------------------------------* >> GRANT ALL ON FUNCTION txtidx_in(opaque) TO PUBLIC; >> RESET SESSION AUTHORIZATION; >> >> >> can you pls tell me how to get rid of the warnings and the invalid >> id "103" in pg_proc i dont see any reference to SYSID 103 >> in the columns proowner or the acl columns. >> >> Any other places where i should look? >> >> >> Regds >> Mallah. >> >> >>> regards, tom lane >>> >>> ---------------------------(end of >>> broadcast)--------------------------- >>> TIP 6: Have you searched our list archives? >>> >>> http://archives.postgresql.org >>> >>> >>> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
Rajesh Kumar Mallah <mallah@trade-india.com> writes:
> The only issue that i noticed now with pg_dump version 7.5dev
> is that create schemas are not dumped before user definations.
> which have their search_path set by ALTER USER command.
"dev" how far back? I thought we fixed that a little while ago.
I get a notice not an error:
regression=# create user arvind;
CREATE USER
regression=# ALTER USER arvind SET search_path TO 'erp';
NOTICE: schema "erp" does not exist
ALTER USER
regression=#
regards, tom lane
yesterdays' lemme do a cvs update :) regds mallah. > Rajesh Kumar Mallah <mallah@trade-india.com> writes: >> The only issue that i noticed now with pg_dump version 7.5dev >> is that create schemas are not dumped before user definations. >> which have their search_path set by ALTER USER command. > > "dev" how far back? I thought we fixed that a little while ago. > I get a notice not an error: > > regression=# create user arvind; > CREATE USER > regression=# ALTER USER arvind SET search_path TO 'erp'; > NOTICE: schema "erp" does not exist > ALTER USER > regression=# > > regards, tom lane ----------------------------------------- Over 1,00,000 exporters are waiting for your order! Click below to get in touch with leading Indian exporters listed in the premier trade directory Exporters Yellow Pages. http://www.trade-india.com/dyn/gdh/eyp/
> Rajesh Kumar Mallah <mallah@trade-india.com> writes: >> The only issue that i noticed now with pg_dump version 7.5dev >> is that create schemas are not dumped before user definations. >> which have their search_path set by ALTER USER command. > > "dev" how far back? I thought we fixed that a little while ago. > I get a notice not an error: Actually my frontend is 7.5dev but the server is 7.4.1 so its fine i guess. Regds Mallah. > > regression=# create user arvind; > CREATE USER > regression=# ALTER USER arvind SET search_path TO 'erp'; > NOTICE: schema "erp" does not exist > ALTER USER > regression=# > > regards, tom lane ----------------------------------------- Over 1,00,000 exporters are waiting for your order! Click below to get in touch with leading Indian exporters listed in the premier trade directory Exporters Yellow Pages. http://www.trade-india.com/dyn/gdh/eyp/