when does CREATE VIEW not create 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.

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

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

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

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

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

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 to truncate 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

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