Обсуждение: Re: Mirroring a DB
Could the God of Rules please comment on this? It seems to be a deficiency in the get_rule_def (sp?) backend function. Perhaps to play it safe all attributes should be fully qualified, but that's probably not as easy as it sounds. On Sat, 11 Dec 1999, Karl DeBisschop wrote: > to use a real world example, this is the output from pg_dump for a > view that we have: > > CREATE RULE "_RETelement_types" AS ON SELECT TO "element_types" DO > INSTEAD SELECT "ref", "fcat", "ecat", "oid" AS "ecat_oid", "ord", ^^^^^^ ^^^^^ > "emin", "emax", "rows" FROM "fcat", "ecat" WHERE "ref" = "fcat"; > > In fact, it needs to be modified before it will parse to: > > CREATE RULE "_RETelement_types" AS ON SELECT TO "element_types" DO > INSTEAD SELECT "ref", fcat.fcat, "ecat", ecat.oid AS "ecat_oid", ^^^^^^^^^ ^^^^^^^^ > "ord", "emin", "emax", "rows" FROM "fcat", "ecat" WHERE fcat.ref = > ecat.fcat; [my highlightings] -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Peter Eisentraut <e99re41@DoCS.UU.SE> writes:
> Could the God of Rules please comment on this? It seems to be a deficiency
> in the get_rule_def (sp?) backend function. Perhaps to play it safe all
> attributes should be fully qualified, but that's probably not as easy as
> it sounds.
I'm not the god of rules, but I have messed with that code. Current
sources will put table prefixes on every var in a rule if more than one
table appears in the rule's rangelist. I think this should be
sufficient, but it's hard to tell from this incomplete example;
are you actually complaining about some special case that arises when
a column has the same name as its table?
It would be nice to see the original view definition (plus enough table
definitions to let us create the rule without guessing).
regards, tom lane
> I'm not the god of rules, but I have messed with that code. Current > sources will put table prefixes on every var in a rule if more than one > table appears in the rule's rangelist. I think this should be > sufficient, but it's hard to tell from this incomplete example; > are you actually complaining about some special case that arises when > a column has the same name as its table? As far as I can see, the problem has nothing to do with whether the table has the same name as the column. The problem arises when the two tables each have attributes with the same name. So for instance when t1 has an attribute (say "foriegn_oid") that joins to oid in t2, the rule gets saved as just "oid" so when recreated, the parser can't determine which oid to join to. > It would be nice to see the original view definition (plus enough table > definitions to let us create the rule without guessing). Sorry, I really didn't think this was an unknown issue, otherwise I would have sent in a bug report with such details. I think the stuff below should cover it. If there's any more info that I can provide, just ask. Karl ============================================================================== create view element_types as select fcat.ref,fcat.fcat,ecat.ecat,ecat.oid as ecat_oid,ecat.ord,ecat.emin,ecat.emax,ecat.rowsfrom fcat,ecat where fcat.ref=ecat.fcat; ------------------------------------------------------------------------------ feature=> \d fcat Table = fcat +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | ref | int2 not null | 2 | | owner | int2 not null | 2 | | fcat | text not null | var | +----------------------------------+----------------------------------+-------+ feature=> \d ecat Table = ecat +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | fcat | int2 not null | 2 | | ord | int2 not null | 2 | | emin | int2 not null | 2 | | emax | int2 | 2 | | rows | int2 not null | 2 | | ecat | text not null | var | +----------------------------------+----------------------------------+-------+ Index: zecat_sf
> I'm not the god of rules, but I have messed with that code. Current > sources will put table prefixes on every var in a rule if more than one > table appears in the rule's rangelist. I think this should be > sufficient, but it's hard to tell from this incomplete example; > are you actually complaining about some special case that arises when > a column has the same name as its table? > > It would be nice to see the original view definition (plus enough table > definitions to let us create the rule without guessing). > > regards, tom lane I also looked back to double check versions. Unbeknownst to me, the source database is 6.5.1 - the destination is 6.5.3 Version 6.5.3 seem to behave as you said, so I'm guessing that this fix occurred relatively recently and I was just unaware it had been fixed. Karl
Karl DeBisschop <kdebisschop@range.infoplease.com> writes:
>> Current
>> sources will put table prefixes on every var in a rule if more than one
>> table appears in the rule's rangelist. I think this should be
>> sufficient, but it's hard to tell from this incomplete example;
> Version 6.5.3 seem to behave as you said, so I'm guessing that this
> fix occurred relatively recently and I was just unaware it had been
> fixed.
Actually, 6.5.3 just unconditionally prefixes all vars in a decompiled
rule, all the time. That was a quick-patch solution to the type of
problem you are complaining of. Current sources (6.6/7.0-to-be) try to
be smarter by only prefixing vars when there is possible ambiguity (ie,
more than one table in the rangelist). That's why I was concerned about
the details of your example --- I was wondering if this "improvement"
might fail under the right special case...
regards, tom lane