Page 1 of 1
createtype(A)
Posted: Thu Feb 27, 2014 7:48 pm
by dbmsuser7
Anyone know what this one ("A") signifies?
Re: createtype(A)
Posted: Thu Feb 27, 2014 7:50 pm
by NorbertKrupa
Can you provide some context around what you're doing or where you're seeing this?
Re: createtype(A)
Posted: Thu Feb 27, 2014 8:33 pm
by JimKnicely
Anyone know what this one ("A") signifies?
You must be referring to the projection type. And I believe "A" signifies "A"utomatic projection creation...
Example:
If you use CREATE TABLE AS SELECT to create a table from the results of a query, the table is created first and a projection is created immediately after, using some of the properties of the underlying SELECT query.
Code: Select all
dbadmin=> create table public.jim (col1 int);
CREATE TABLE
dbadmin=> create table public.jim2 as select * from jim;
CREATE TABLE
dbadmin=> select export_objects('', 'public.jim2');
export_objects
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE public.jim2
(
col1 int
);
CREATE PROJECTION public.jim2 /*+createtype(A)*/
(
col1
)
AS
SELECT jim2.col1
FROM public.jim2
ORDER BY jim2.col1
SEGMENTED BY hash(jim2.col1) ALL NODES KSAFE 1;
SELECT MARK_DESIGN_KSAFE(1);
(1 row)
Check the "Automatic Projection Creation" section in the SQL Guide for more details!
https://my.vertica.com/docs/7.0.x/PDF/H ... Manual.pdf
Re: createtype(A)
Posted: Thu Feb 27, 2014 8:43 pm
by NorbertKrupa
I think it would be valuable to put together a list of createtypes for future reference. Thoughts?
Re: createtype(A)
Posted: Thu Feb 27, 2014 8:48 pm
by JimKnicely
I think this is a complete list:
- "A" is for automatic projection creation
"D" is for Database Designer
"L" if creation was delayed meaning the table was created, but waiting for data to be inserted
"P" is the projection was created with a projection clause
At least it is in my DB:
Code: Select all
dbadmin=> select distinct createtype from vs_projections order by 1;
createtype
------------
A
D
L
P
(4 rows)
Re: createtype(A)
Posted: Thu Feb 27, 2014 10:14 pm
by dbmsuser7
norbertk wrote,
Can you provide some context around what you're doing or where you're seeing this?
Right; it was in a
statement.
I'm in the middle of migrating our user schemas from a 6-node version 6 cluster to a 4-node version 7 cluster.
I wrote a python script which first dumps out the table definitions using EXPORT_OBJECTS(). After "importing" the definitions on the new cluster by running the SQL output by EXPORT_OBJECTS(), I copy the data over using COPY FROM VERTICA.
For my own schema, it was fine. But when I tested it on another user, the script choked because there were projections that actually listed nodes (instead of using ALL NODES), and coming from a 6-node cluster, there were references to nodes 5 and 6 which failed on the new 4-node cluster.
Anyway, it was while trying to figure out those projection definitions that I became puzzled by "createtype(A)".
Re: createtype(A)
Posted: Mon Mar 03, 2014 8:27 pm
by NorbertKrupa
Just to add more to this. It's recommended to not use create types when writing manual projections as the type is utilized in storing the projection.