Thread: BUG #4817: Dump of 8.3 hstore not restorable to 8.4 (RECHECK)
The following bug has been logged online: Bug reference: 4817 Logged by: David Blewett Email address: david@dawninglight.net PostgreSQL version: 8.4beta2 Operating system: Gentoo Linux Description: Dump of 8.3 hstore not restorable to 8.4 (RECHECK) Details: In testing the beta to see if my planner issues might be resolved, I ran into a situation where the 8.4 pg_dump created a dump for the hstore contrib module which would not restore against 8.4. Here is the problem SQL: -- -- Name: gin_hstore_ops; Type: OPERATOR CLASS; Schema: public; Owner: postgres -- CREATE OPERATOR CLASS gin_hstore_ops DEFAULT FOR TYPE hstore USING gin AS STORAGE text , OPERATOR 7 @>(hstore,hstore) RECHECK , OPERATOR 9 ?(hstore,text) , FUNCTION 1 bttextcmp(text,text) , FUNCTION 2 gin_extract_hstore(internal,internal) , FUNCTION 3 gin_extract_hstore_query(internal,internal,smallint) , FUNCTION 4 gin_consistent_hstore(internal,smallint,internal); -- -- Name: gist_hstore_ops; Type: OPERATOR CLASS; Schema: public; Owner: postgres -- CREATE OPERATOR CLASS gist_hstore_ops DEFAULT FOR TYPE hstore USING gist AS STORAGE ghstore , OPERATOR 7 @>(hstore,hstore) RECHECK , OPERATOR 9 ?(hstore,text) RECHECK , OPERATOR 13 @(hstore,hstore) RECHECK , FUNCTION 1 ghstore_consistent(internal,internal,integer) , FUNCTION 2 ghstore_union(internal,internal) , FUNCTION 3 ghstore_compress(internal) , FUNCTION 4 ghstore_decompress(internal) , FUNCTION 5 ghstore_penalty(internal,internal,internal) , FUNCTION 6 ghstore_picksplit(internal,internal) , FUNCTION 7 ghstore_same(internal,internal,internal); This generates the following errors: ERROR: RECHECK is no longer supported at character 131 ERROR: RECHECK is no longer supported at character 136 I worked around this by installing the 8.4 version of hstore and ignoring those particular errors. However, why would pg_dump generate syntax that isn't supported any longer? David Blewett
On Wed, May 20, 2009 at 8:48 AM, David Blewett <david@dawninglight.net>wrote: > > In testing the beta to see if my planner issues might be resolved, I ran > into a situation where the 8.4 pg_dump created a dump for the hstore > contrib > module which would not restore against 8.4. It looks like this relates back to the "Remove lossy-operator RECHECK flag?" discussion [1] last April. That seemed to end with this decision: > For the moment I have it doing #1, but it strikes me that that is only > useful if 8.4 getsto release without having made any backwards-incompatible > changes in pg_dump output, which is probably not better than a fifty-fifty > bet. > Has there been any backwards-incompatible changes since then? David Blewett 1. http://archives.postgresql.org/message-id/19212.1208122532@sss.pgh.pa.us
David Blewett <david@dawninglight.net> writes: > It looks like this relates back to the "Remove lossy-operator RECHECK flag?" > discussion [1] last April. That seemed to end with this decision: >> For the moment I have it doing #1, but it strikes me that that is only >> useful if 8.4 getsto release without having made any backwards-incompatible >> changes in pg_dump output, which is probably not better than a fifty-fifty >> bet. > Has there been any backwards-incompatible changes since then? Just to answer the question, I can't find any. There have been plenty of feature additions, and if you dump from an 8.4 database that's using any of those features then the dump wouldn't reload into 8.3. But using current pg_dump to dump from an 8.3 database will produce a dump that loads back into 8.3, AFAICS (and some simple testing with the regression database supports that). If we remove the RECHECK printout then this will stop being true --- and worse, it will break silently and in a way that will only cause you to get occasional wrong answers from your queries. Even though we discourage people from using later pg_dumps to reload into older servers, I'm not sure that I want us to loose that kind of foot-gun on the world. So there's still no good answer here. regards, tom lane
I wrote: > If we remove the RECHECK printout then this will stop being true --- > and worse, it will break silently and in a way that will only cause > you to get occasional wrong answers from your queries. But actually, that only says it's a bad idea to drop RECHECK from the pg_dump output. It doesn't say that an 8.4 server has to throw error when it sees RECHECK. The way things are set up now, ignoring it seems fairly failsafe. The default assumption in 8.4 is that operators are lossy, so the worst case consequence is that you waste cycles on unnecessary rechecks. You don't get wrong answers, even if you neglected to update your opclass support code. Off to make a proposal to pghackers ... regards, tom lane