Thread: when does CREATE VIEW not create a view?
I am trying to create a view and have run across a, to me, bizarre
occurance. One CREATE VIEW statement creates the view fine; changing
the name of the view and repeating the same statement does not. This
has nothing to do with conflicting names as appropriate DROP commands
are issued first.
To be specific, here are the queries and the results:
-- precipitation_xxx_verify view created fine (see below)
drop view precipitation_xxx_verify; create view precipitation_xxx_verify as select p.id, p.verified,
w.name, w.country, w.state, w.county, p.date, p.precipitation / 2.54 as precipitation, -- 2.54 mm/inch
p.inserted_by, p.inserted_on, p.verified_by, p.verified_on from precipitation_data p, weather_stations w where
w.id= p.weather_station_id and verified != true;
-- precipitation_english_verify view is not created as a view (see below)
drop view precipitation_english_verify; -- XXX - fails because a view is not created (see below) drop
tableprecipitation_english_verify; -- XXX - why not a view? create view precipitation_english_verify as
selectp.id, p.verified, w.name, w.country, w.state, w.county, p.date, p.precipitation / 2.54 as
precipitation, -- 2.54 mm/inch p.inserted_by, p.inserted_on, p.verified_by, p.verified_on from
precipitation_datap, weather_stations w where w.id = p.weather_station_id and verified != true;
\d precipitation_xxx_verify \d precipitation_english_verify
View "precipitation_xxx_verify"Attribute | Type | Modifier ---------------+-----------+---------- id
| integer | verified | boolean | name | text | country | text |
state | text | county | text | date | timestamp | precipitation |
float8 | inserted_by | name | inserted_on | timestamp | verified_by | name |
verified_on | timestamp | View definition: SELECT p.id, p.verified, w.name, w.country, w.state, w.county, p.date,
(p.precipitation/ 2.54) AS precipitation, p.inserted_by, p.inserted_on, p.verified_by, p.verified_on FROM
precipitation_datap, weather_stations w WHERE ((w.id = p.weather_station_id) AND (p.verified <> 't'::bool));
View "precipitation_english_verify"Attribute | Type | Modifier ---------------+-----------+----------
id | integer | verified | boolean | name | text | country |
text | state | text | county | text | date | timestamp |
precipitation| float8 | inserted_by | name | inserted_on | timestamp | verified_by | name
| verified_on | timestamp | View definition: Not a view
It seems that the problem is with the word "english" as part of the
view name. Variants of the name that lack it (e.g., replacing xxx
above with eng, englih, etc.) seem to work fine, but variants that
include it (e.g., replacing xxx with english, eenglish, englishh)
suffer as above.
Is there something special involved in handling view names that would
preclude such names?
Any explanations for this behavior are welcome.
Thanks for your help.
Cheers,
Brook
It seems that the problem is with the word "english" as part of the view name. Variants of the name that lack it (e.g.,replacing xxx above with eng, englih, etc.) seem to work fine, but variants that include it (e.g., replacing xxxwith english, eenglish, englishh) suffer as above. The problem also seems to occur if xxx is replaced by british, imperial, and american. I haven't tried other location names, but there seems to be a trend. Cheers, Brook
Brook Milligan wrote: > > It seems that the problem is with the word "english" as part of the > view name. Variants of the name that lack it (e.g., replacing xxx > above with eng, englih, etc.) seem to work fine, but variants that > include it (e.g., replacing xxx with english, eenglish, englishh) > suffer as above. > > The problem also seems to occur if xxx is replaced by british, > imperial, and american. I haven't tried other location names, but > there seems to be a trend. This is probably wrong, but could it be the length of the name? Try replacing 'english' with some other seven letters e.g. precipitation_abdefgh_verify -- Mark Hollomon mhh@nortelnetworks.com ESN 451-9008 (302)454-9008
This is probably wrong, but could it be the length of the name? Try replacing 'english' with some other seven letters e.g. precipitation_abdefgh_verify Good guess, but I'm still confused. precipitation_abcdefgh_verify does not work; precipitation_abcdef_verify does. The latter is 27 characters. I thought identifiers could be 32 before truncation occurred (and for tables the name is just truncated anyway but otherwise unchanged). Does the backend add something to a view identifier to push it over 32 characters? Is that added as a prefix or a suffix? If the latter, perhaps it should be a prefix? Or is the problem with the select rule formed by CREATE VIEW? If the latter, should there be different truncation rules for view names than for table names so that the associated rule and table names have the appropriate relationship? Cheers, Brook
Brook, This smells like a identifier length limit problem to me. Let's see: precipitation_english_verify is 29 characters, default NAMEDATALEN is 32. Creating a view creates a table, and attaches a SELCT DO INSTEAD rule to it, named _RET<tablename>, so that tacks 4 characters on, giving us 29+4 = 33, bingo, rule doesn't get made. All your other attemps were longer, except for xxx. You'll find that replacing english with xxxxxxx won't work, either (and it's not the vchip). Sounds like a missing error check, or truncation, in the CREATE VIEW rule generation code. Ross On Tue, Aug 22, 2000 at 12:40:21PM -0600, Brook Milligan wrote: > I am trying to create a view and have run across a, to me, bizarre > occurance. One CREATE VIEW statement creates the view fine; changing > the name of the view and repeating the same statement does not. This > has nothing to do with conflicting names as appropriate DROP commands > are issued first. > -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
See my other reply about what gets added: the problem is the rewrite rule name, as you guessed. Here's a patch that silently truncates the generated rule name. Unlike tablename or generated sequence name truncation, there's no need in normal operation for the DBA to know the name of this rule, so I didn't put in a NOTICE about the truncation. I found every accurance of _RET in the source that refered to a view rule, and patched them to do the right thing. Ross On Tue, Aug 22, 2000 at 02:21:04PM -0600, Brook Milligan wrote: > > Does the backend add something to a view identifier to push it over 32 > characters? Is that added as a prefix or a suffix? If the latter, > perhaps it should be a prefix? Or is the problem with the select rule > formed by CREATE VIEW? If the latter, should there be different > truncation rules for view names than for table names so that the > associated rule and table names have the appropriate relationship? > > Cheers, > Brook -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Attachment
See my other reply about what gets added: the problem is the rewrite rule name, as you guessed. Here's a patch that silently truncates the generated rule name. THANKS!!! Once again, for all practical purposes _instant_ service from the mailing list. Very impressive! Cheers, Brook
On Tue, Aug 22, 2000 at 04:05:19PM -0500, Ross J. Reedstrom wrote: > > I found every accurance of _RET in the source that refered to a view rule, > and patched them to do the right thing. Sigh. 5 minutes after sending this, I find one last one, in pg_dump. Patch attached. Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Attachment
On Tue, Aug 22, 2000 at 03:20:36PM -0600, Brook Milligan wrote: > See my other reply about what gets added: the problem is the rewrite > rule name, as you guessed. > > Here's a patch that silently truncates the generated rule name. > > THANKS!!! Once again, for all practical purposes _instant_ service from > the mailing list. Very impressive! > Warning: these patches are against current source. Let me know if it doesn;t patch in for you. And be sure to get the extra bit, so pg_dump doesn't break. Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
> See my other reply about what gets added: the problem is the rewrite > rule name, as you guessed. > > Here's a patch that silently truncates the generated rule name. Unlike > tablename or generated sequence name truncation, there's no need in > normal operation for the DBA to know the name of this rule, so I didn't > put in a NOTICE about the truncation. > > I found every accurance of _RET in the source that refered to a view rule, > and patched them to do the right thing. Oh, the patch strikes me since it is not "multibyte aware." Are you going to put it into the CVS? If so, please let me know after you do it so that I could add the multibyte awareness to that. -- Tatsuo Ishii
On Wed, Aug 23, 2000 at 10:02:02AM +0900, Tatsuo Ishii wrote: > > See my other reply about what gets added: the problem is the rewrite > > rule name, as you guessed. > > > > Here's a patch that silently truncates the generated rule name. Unlike > > tablename or generated sequence name truncation, there's no need in > > normal operation for the DBA to know the name of this rule, so I didn't > > put in a NOTICE about the truncation. > > > > I found every accurance of _RET in the source that refered to a view rule, > > and patched them to do the right thing. > > Oh, the patch strikes me since it is not "multibyte aware." Are you > going to put it into the CVS? If so, please let me know after you do > it so that I could add the multibyte awareness to that. Well, I meant it to go into CVS, if noone objected. I consider your raising the multibyte issue sufficent objection to have it held off. No point patching and repatching. The problem is that I just chop it off at NAMEDATALEN, which might be in the middle of a multibyte character, correct? Ah, I see code in parser/scan.l that does the multibyte aware version of the chop. Should I just rewrite my patch with that code as a model? Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Brook Milligan wrote: > > See my other reply about what gets added: the problem is the rewrite > rule name, as you guessed. > > Here's a patch that silently truncates the generated rule name. > What are the consequences of changing the NAMEDATALEN and recompiling? Doesn't that seem like a better solution then to truncate the view name? -- You can hit reply if you want "malcontent" is a legit email.
On Thu, Aug 24, 2000 at 12:30:00AM -0600, Malcontent wrote: > Brook Milligan wrote: > > > > See my other reply about what gets added: the problem is the rewrite > > rule name, as you guessed. > > > > Here's a patch that silently truncates the generated rule name. > > > > What are the consequences of changing the NAMEDATALEN and recompiling? > Doesn't that seem like a better solution then to truncate the view name? Increasing NAMEDATALEN is a relatively common customization, but it does cost you efficency of storage in the system tables: all the identifiers take fixed NAMEDATALEN char fields, for speed of access. In this particular case, the view name is not getting truncated (that will already happen, if you try to create a view or table with a name longer than NAMEDATALEN). The problem is that creation of a view involves the backend creating a table with the supplied name, building an ON SELECT INSTEAD rule, whose (unique) name is created by prepending _RET to the supplied view name. Since this goes into a NAMEDATALEN field in a system table, it needs to be truncated. Current code fails by not creating the rule if the supplied name is within 4 characters of NAMEDATALEN, but leaving the underlying table around. Since end user code _never_ needs to manipulate the rule directly, truncating the name is not a problem. The patch I proposed has not been put in CVS, because I need to add multibyte support. Hmm, anyone got any spare round tuits? (I've got plenty of square ones...) Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
> Here's a patch that silently truncates the generated rule name. What are the consequences of changing the NAMEDATALEN and recompiling? Doesn't that seem like a better solution then totruncate the view name? All names are truncated. The bug arises from the fact that view names were being incorrectly truncated by not taking into account the extra characters added to enforce the automatic "on select" rule. The point is to make the truncation rules internally consistent. Cheers, Brook
> > Oh, the patch strikes me since it is not "multibyte aware." Are you > > going to put it into the CVS? If so, please let me know after you do > > it so that I could add the multibyte awareness to that. > > Well, I meant it to go into CVS, if noone objected. I consider your raising > the multibyte issue sufficent objection to have it held off. No point > patching and repatching. No problem for repatching I think, since we are in the development cycle anyway. > The problem is that I just chop it off at NAMEDATALEN, which might be > in the middle of a multibyte character, correct? Exactly. > Ah, I see code in parser/scan.l that does the multibyte aware version > of the chop. Should I just rewrite my patch with that code as a model? Please do so. If you need any help, please let me know. -- Tatsuo Ishii
On Tue, Aug 29, 2000 at 10:12:38AM +0900, t-ishii@sra.co.jp wrote: > > No problem for repatching I think, since we are in the development > cycle anyway. Oh well. > > > The problem is that I just chop it off at NAMEDATALEN, which might be > > in the middle of a multibyte character, correct? > > Exactly. Good. Understanding the problem is critical to fixing it. ;-) > > > Ah, I see code in parser/scan.l that does the multibyte aware version > > of the chop. Should I just rewrite my patch with that code as a model? > > Please do so. If you need any help, please let me know. O.K. I'm just about done with it. Since there are three places in the code that seem to know about how to make a rulename from a viewname, and one of them is a function named MakeRetrieveViewRuleName(), I've put the #ifdef MULTIBYTE in there, and called this function from the other places that need it. Only problem is in utils/adt/ruleutils.c There's code in there that constructs potential rule names that start with '_ret' as well as '_RET', in order to use an SPI query to find the rule associated with a view. This is the only occurance of the string '"_ret' in the codebase, and I can't find a way a rule might get that name, nor an example in either the 6.5.0 and 7.0.2 databases I've got here. Someone when to the trouble of writing the query that way, but I'm not convinced it's needed anymore. I'm guessing there was an extra tolower somewhere that doesn't happen anymore (Tom Lane tracked down a bunch of these when I whined about MultiCase tablenames breaking, nigh on a year ago) Should I trash it? Anyone have anything returned from SELECT rulename from pg_rewrite where rulename ~ '^_ret'; on any database with view defined? Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> Only problem is in utils/adt/ruleutils.c
> There's code in there that constructs potential rule names that start with
> '_ret' as well as '_RET', in order to use an SPI query to find the rule
> associated with a view. This is the only occurance of the string '"_ret'
> in the codebase, and I can't find a way a rule might get that name, nor an
> example in either the 6.5.0 and 7.0.2 databases I've got here.
Most likely it's dead code. I'd say simplify.
Mark Hollomon's question about adding a relisview column to pg_class
spurs another possibility: add a column to pg_class, but instead of
just a boolean, make it be 0 if not a view and the OID of the view rule
if it is. That'd get rid of the dependency on rule names altogether
for code that needs to find the associated rule.
regards, tom lane
On Tue, Aug 29, 2000 at 10:12:38AM +0900, t-ishii@sra.co.jp wrote: > > > Oh, the patch strikes me since it is not "multibyte aware." O.K. - Here's the multibyte aware version of my patch to fix the truncation of the rulename autogenerated during a CREATE VIEW. I've modified all the places in the backend that want to construct the rulename to use the MakeRetrieveViewRuleName(), where I put the #ifdef MULTIBYTE, so that's the only place that knows how to construct a view rulename. Except pg_dump, where I replicated the code, since it's a standalone binary. The only effect the enduser will see is that views with names len(name) > NAMEDATALEN-4 will fail to be created, if the derived rulename clases with an existing rule: i.e. the user is trying to create two views with long names whose first difference is past NAMEDATALEN-4 (but before NAMEDATALEN: that'll error out after the viewname truncation.) In no case will the user get left with a table without a view rule, as the current code does. > > Please do so. If you need any help, please let me know. > -- > Tatsuo Ishii I haven't tested the MULTIBYTE part. Could you give it a quick once over? Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Attachment
Applied. > On Tue, Aug 29, 2000 at 10:12:38AM +0900, t-ishii@sra.co.jp wrote: > > > > Oh, the patch strikes me since it is not "multibyte aware." > > O.K. - > Here's the multibyte aware version of my patch to fix the truncation > of the rulename autogenerated during a CREATE VIEW. I've modified all > the places in the backend that want to construct the rulename to use > the MakeRetrieveViewRuleName(), where I put the #ifdef MULTIBYTE, so > that's the only place that knows how to construct a view rulename. Except > pg_dump, where I replicated the code, since it's a standalone binary. > > The only effect the enduser will see is that views with names len(name) > > NAMEDATALEN-4 will fail to be created, if the derived rulename clases > with an existing rule: i.e. the user is trying to create two views with > long names whose first difference is past NAMEDATALEN-4 (but before > NAMEDATALEN: that'll error out after the viewname truncation.) In no > case will the user get left with a table without a view rule, as the > current code does. > > > > > Please do so. If you need any help, please let me know. > > -- > > Tatsuo Ishii > > I haven't tested the MULTIBYTE part. Could you give it a quick once over? > > Ross > -- > Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> > NSBRI Research Scientist/Programmer > Computer and Information Technology Institute > Rice University, 6100 S. Main St., Houston, TX 77005 [ Attachment, skipping... ] -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
I hate to say this, but this patch fails to apply on our current tree. Can you send me a version that applies? Thanks. > On Tue, Aug 29, 2000 at 10:12:38AM +0900, t-ishii@sra.co.jp wrote: > > > > Oh, the patch strikes me since it is not "multibyte aware." > > O.K. - > Here's the multibyte aware version of my patch to fix the truncation > of the rulename autogenerated during a CREATE VIEW. I've modified all > the places in the backend that want to construct the rulename to use > the MakeRetrieveViewRuleName(), where I put the #ifdef MULTIBYTE, so > that's the only place that knows how to construct a view rulename. Except > pg_dump, where I replicated the code, since it's a standalone binary. > > The only effect the enduser will see is that views with names len(name) > > NAMEDATALEN-4 will fail to be created, if the derived rulename clases > with an existing rule: i.e. the user is trying to create two views with > long names whose first difference is past NAMEDATALEN-4 (but before > NAMEDATALEN: that'll error out after the viewname truncation.) In no > case will the user get left with a table without a view rule, as the > current code does. > > > > > Please do so. If you need any help, please let me know. > > -- > > Tatsuo Ishii > > I haven't tested the MULTIBYTE part. Could you give it a quick once over? > > Ross > -- > Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> > NSBRI Research Scientist/Programmer > Computer and Information Technology Institute > Rice University, 6100 S. Main St., Houston, TX 77005 [ Attachment, skipping... ] -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
OK, the bad news is that this does not apply to the current development tree. Ross, can you make a more corrent one? Sorry. > On Tue, Aug 29, 2000 at 10:12:38AM +0900, t-ishii@sra.co.jp wrote: > > > > Oh, the patch strikes me since it is not "multibyte aware." > > O.K. - > Here's the multibyte aware version of my patch to fix the truncation > of the rulename autogenerated during a CREATE VIEW. I've modified all > the places in the backend that want to construct the rulename to use > the MakeRetrieveViewRuleName(), where I put the #ifdef MULTIBYTE, so > that's the only place that knows how to construct a view rulename. Except > pg_dump, where I replicated the code, since it's a standalone binary. > > The only effect the enduser will see is that views with names len(name) > > NAMEDATALEN-4 will fail to be created, if the derived rulename clases > with an existing rule: i.e. the user is trying to create two views with > long names whose first difference is past NAMEDATALEN-4 (but before > NAMEDATALEN: that'll error out after the viewname truncation.) In no > case will the user get left with a table without a view rule, as the > current code does. > > > > > Please do so. If you need any help, please let me know. > > -- > > Tatsuo Ishii > > I haven't tested the MULTIBYTE part. Could you give it a quick once over? > > Ross > -- > Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> > NSBRI Research Scientist/Programmer > Computer and Information Technology Institute > Rice University, 6100 S. Main St., Houston, TX 77005 [ Attachment, skipping... ] -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Mon, Oct 16, 2000 at 12:22:23PM -0400, Bruce Momjian wrote: > OK, the bad news is that this does not apply to the current development > tree. Ross, can you make a more corrent one? Sorry. I think it won't apply because it's already in there. There were also subsequent fixes to how pg_dump deals with views by Phil. Ross -- Open source code is like a natural resource, it's the result of providing food and sunshine to programmers, and then staying out of their way. [...] [It] is not going away because it has utility for both the developers and users independent of economic motivations. Jim Flynn, Sunnyvale, Calif.
On Mon, Oct 16, 2000 at 03:31:08PM -0500, Ross J. Reedstrom wrote: > On Mon, Oct 16, 2000 at 12:22:23PM -0400, Bruce Momjian wrote: > > OK, the bad news is that this does not apply to the current development > > tree. Ross, can you make a more corrent one? Sorry. > > I think it won't apply because it's already in there. There were also > subsequent fixes to how pg_dump deals with views by Phil. Err, I mean fixes by Philip to how pg_dump deals with views. AFAIK, there's no special cases in the code for views created by Philip. ;-> Ross -- Open source code is like a natural resource, it's the result of providing food and sunshine to programmers, and then staying out of their way. [...] [It] is not going away because it has utility for both the developers and users independent of economic motivations. Jim Flynn, Sunnyvale, Calif.