You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@trafodion.apache.org by Carol Pearson <ca...@gmail.com> on 2016/02/03 00:04:51 UTC
Dropping Schema blocked if histograms exist?
I was trying to drop a schema after I updated statistics on the table. I'd
dropped all of the objects that I created in the schema (one table,
cjpjunk, because that's all I was testing...) but my drop schema failed:
>>drop schema test_sandbox_schema;
*** ERROR[1028] The schema must be empty. It contains at least one object
SB_HISTOGRAMS.
--- SQL operation failed with errors.
So I selected from the OBJECTS metadata table to see what's in the
test_sandbox_schema, and all I see are histograms tables, created when I
updated stats on good ol' cjpjunk:
>>select distinct object_name from "_MD_".objects where schema_name like
'TEST_SANDBOX_SCHEMA%';
OBJECT_NAME
-----------------------------------------------------------------------------------
SB_HISTOGRAMS
SB_HISTOGRAMS_PK
SB_HISTOGRAM_INTERVALS
SB_HISTOGRAM_INTERVALS_PK
__SCHEMA__
--- 5 row(s) selected.
Nothing but system-created tables, and the SB_HISTOGRAMS tables exist only
because I did an UPDATE STATISTICS command.
Now, I'm able to drop the schema with a drop schema cascade command:
>>drop schema test_sandbox_schema cascade;
--- SQL operation complete.
but why was cascade required? Shouldn't I be able to drop the schema
without cascade since I didn't physically create any of these objects, and
wouldn't generally need to be aware of them from a user perspective?
Thanks!
-Carol P.
---------------------------------------------------------------
Email: carol.pearson234@gmail.com
Twitter: @CarolP222
---------------------------------------------------------------
RE: Dropping Schema blocked if histograms exist?
Posted by Venkat Muthuswamy <ve...@esgyn.com>.
Hi Roberta,
Thanks for including this change along with the existing JIRA as it would
complete the scenario..
“Create the histogram tables when schema is created”…
“Drop the histogram tables when schema is dropped”…
Thanks
Venkat
*From:* Roberta Marton [mailto:roberta.marton@esgyn.com]
*Sent:* Tuesday, February 02, 2016 3:21 PM
*To:* user@trafodion.incubator.apache.org
*Subject:* RE: Dropping Schema blocked if histograms exist?
I have also noticed this behavior while working on a fix for JIRA
TRAFODION-1789. IMHO, we should drop the schema without requiring cascade
if only system created objects exist. In fact, I was planning to deliver
this behavioral change as part of the fix for TRAFODION-1789.
Roberta
*From:* Dave Birdsall [mailto:dave.birdsall@esgyn.com]
*Sent:* Tuesday, February 2, 2016 3:16 PM
*To:* user@trafodion.incubator.apache.org
*Subject:* RE: Dropping Schema blocked if histograms exist?
Hi,
I have run into this too. DROP SCHEMA treats the SB_HISTOGRAMS and
SB_HISTOGRAMS_INTERVALS as user tables. So when those exist, I either have
to explicitly drop them or use CASCADE on DROP SCHEMA.
I have no opinion on whether this is correct behavior or not.
Dave
*From:* Carol Pearson [mailto:carol.pearson234@gmail.com]
*Sent:* Tuesday, February 2, 2016 3:05 PM
*To:* user@trafodion.incubator.apache.org
*Subject:* Dropping Schema blocked if histograms exist?
I was trying to drop a schema after I updated statistics on the table. I'd
dropped all of the objects that I created in the schema (one table,
cjpjunk, because that's all I was testing...) but my drop schema failed:
>>drop schema test_sandbox_schema;
*** ERROR[1028] The schema must be empty. It contains at least one object
SB_HISTOGRAMS.
--- SQL operation failed with errors.
So I selected from the OBJECTS metadata table to see what's in the
test_sandbox_schema, and all I see are histograms tables, created when I
updated stats on good ol' cjpjunk:
>>select distinct object_name from "_MD_".objects where schema_name like
'TEST_SANDBOX_SCHEMA%';
OBJECT_NAME
-----------------------------------------------------------------------------------
SB_HISTOGRAMS
SB_HISTOGRAMS_PK
SB_HISTOGRAM_INTERVALS
SB_HISTOGRAM_INTERVALS_PK
__SCHEMA__
--- 5 row(s) selected.
Nothing but system-created tables, and the SB_HISTOGRAMS tables exist only
because I did an UPDATE STATISTICS command.
Now, I'm able to drop the schema with a drop schema cascade command:
>>drop schema test_sandbox_schema cascade;
--- SQL operation complete.
but why was cascade required? Shouldn't I be able to drop the schema
without cascade since I didn't physically create any of these objects, and
wouldn't generally need to be aware of them from a user perspective?
Thanks!
-Carol P.
---------------------------------------------------------------
Email: carol.pearson234@gmail.com
Twitter: @CarolP222
---------------------------------------------------------------
RE: Dropping Schema blocked if histograms exist?
Posted by Roberta Marton <ro...@esgyn.com>.
I have also noticed this behavior while working on a fix for JIRA
TRAFODION-1789. IMHO, we should drop the schema without requiring cascade
if only system created objects exist. In fact, I was planning to deliver
this behavioral change as part of the fix for TRAFODION-1789.
Roberta
*From:* Dave Birdsall [mailto:dave.birdsall@esgyn.com]
*Sent:* Tuesday, February 2, 2016 3:16 PM
*To:* user@trafodion.incubator.apache.org
*Subject:* RE: Dropping Schema blocked if histograms exist?
Hi,
I have run into this too. DROP SCHEMA treats the SB_HISTOGRAMS and
SB_HISTOGRAMS_INTERVALS as user tables. So when those exist, I either have
to explicitly drop them or use CASCADE on DROP SCHEMA.
I have no opinion on whether this is correct behavior or not.
Dave
*From:* Carol Pearson [mailto:carol.pearson234@gmail.com]
*Sent:* Tuesday, February 2, 2016 3:05 PM
*To:* user@trafodion.incubator.apache.org
*Subject:* Dropping Schema blocked if histograms exist?
I was trying to drop a schema after I updated statistics on the table. I'd
dropped all of the objects that I created in the schema (one table,
cjpjunk, because that's all I was testing...) but my drop schema failed:
>>drop schema test_sandbox_schema;
*** ERROR[1028] The schema must be empty. It contains at least one object
SB_HISTOGRAMS.
--- SQL operation failed with errors.
So I selected from the OBJECTS metadata table to see what's in the
test_sandbox_schema, and all I see are histograms tables, created when I
updated stats on good ol' cjpjunk:
>>select distinct object_name from "_MD_".objects where schema_name like
'TEST_SANDBOX_SCHEMA%';
OBJECT_NAME
-----------------------------------------------------------------------------------
SB_HISTOGRAMS
SB_HISTOGRAMS_PK
SB_HISTOGRAM_INTERVALS
SB_HISTOGRAM_INTERVALS_PK
__SCHEMA__
--- 5 row(s) selected.
Nothing but system-created tables, and the SB_HISTOGRAMS tables exist only
because I did an UPDATE STATISTICS command.
Now, I'm able to drop the schema with a drop schema cascade command:
>>drop schema test_sandbox_schema cascade;
--- SQL operation complete.
but why was cascade required? Shouldn't I be able to drop the schema
without cascade since I didn't physically create any of these objects, and
wouldn't generally need to be aware of them from a user perspective?
Thanks!
-Carol P.
---------------------------------------------------------------
Email: carol.pearson234@gmail.com
Twitter: @CarolP222
---------------------------------------------------------------
RE: Dropping Schema blocked if histograms exist?
Posted by Dave Birdsall <da...@esgyn.com>.
Hi,
I have run into this too. DROP SCHEMA treats the SB_HISTOGRAMS and
SB_HISTOGRAMS_INTERVALS as user tables. So when those exist, I either have
to explicitly drop them or use CASCADE on DROP SCHEMA.
I have no opinion on whether this is correct behavior or not.
Dave
*From:* Carol Pearson [mailto:carol.pearson234@gmail.com]
*Sent:* Tuesday, February 2, 2016 3:05 PM
*To:* user@trafodion.incubator.apache.org
*Subject:* Dropping Schema blocked if histograms exist?
I was trying to drop a schema after I updated statistics on the table. I'd
dropped all of the objects that I created in the schema (one table,
cjpjunk, because that's all I was testing...) but my drop schema failed:
>>drop schema test_sandbox_schema;
*** ERROR[1028] The schema must be empty. It contains at least one object
SB_HISTOGRAMS.
--- SQL operation failed with errors.
So I selected from the OBJECTS metadata table to see what's in the
test_sandbox_schema, and all I see are histograms tables, created when I
updated stats on good ol' cjpjunk:
>>select distinct object_name from "_MD_".objects where schema_name like
'TEST_SANDBOX_SCHEMA%';
OBJECT_NAME
-----------------------------------------------------------------------------------
SB_HISTOGRAMS
SB_HISTOGRAMS_PK
SB_HISTOGRAM_INTERVALS
SB_HISTOGRAM_INTERVALS_PK
__SCHEMA__
--- 5 row(s) selected.
Nothing but system-created tables, and the SB_HISTOGRAMS tables exist only
because I did an UPDATE STATISTICS command.
Now, I'm able to drop the schema with a drop schema cascade command:
>>drop schema test_sandbox_schema cascade;
--- SQL operation complete.
but why was cascade required? Shouldn't I be able to drop the schema
without cascade since I didn't physically create any of these objects, and
wouldn't generally need to be aware of them from a user perspective?
Thanks!
-Carol P.
---------------------------------------------------------------
Email: carol.pearson234@gmail.com
Twitter: @CarolP222
---------------------------------------------------------------