Vertica Error 4115 and new tables

I’m in the process of creating data tables to be populated by our load system. This will be updated periodically, so using a MERGE statement makes sense. After creating the table and attempting to merge I receive the following error:

ERROR 4115:  No super projection found for table table_name

But isn’t a super projection created by default when a table is created? Checking the documentation confirms that yes a super projection should be created. So why am I getting this error?

This comes down to one of Vertica’s counter-intuitive optimizations. For this table I didn’t describe the super projection so I am relying on Vertica to create one for me (I may revisit the projections once I’ve made my proof of concept). So the information can be derived and the projection will be created if I INSERT into the table.

In fact, doing that resolves the issue. The call to INSERT creates the projection and the problem resolves itself. It would seem that Vertica does not think to create the projection at MERGE time despite the fact the INSERTs and SELECTs will work independently of one another.

EDIT: Yes, this does have the desired effect, but the preferred work around is to add an encoding type to any column. Even if you explicitly set it to auto this does the trick. This is slightly less convoluted, but I don’t see why this step should be needed at all.

One response to “Vertica Error 4115 and new tables”

  1. For creation of superprojections, u need to have atleast some data in the table. U can utlize a dummy row into the table. Once superprojection is formed, delete it and u can then use the merge statement.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: