LOCK command is under-documented, fails on empty table

Theory

The LOCK command, mentioned in this blog post and this GS post, does not appear in the documentation. I would expect it to look something like the PostgreSQL documentation, including the discussion of lock types. Would love to see this updated.

It's especially important since I've found that calling LOCK on a newly created, empty table hangs. Nothing happens. It's not until there is at least one row that LOCK returns. Is there a way to LOCK an empty table? And if not, how do I tell if a table is empty so I don't try to lock it?

eli_revach

Hi To fix the locking issue you mention, you should explicity create the super projection after the table is being created than you will be able to lock with no issues. Thanks

wwong2

Hi, I will log a doc bug for this as this is not listed as you mentioned.
Thanks

Theory

How do I do that?

wwong2

Hi, if you are asking about how to log a doc bug, I have already submitted one for you.  In general, if you have a bug you want to log, you would need to open a support case and support will log one for you.

Regards

eli_revach

Its simple ddl like creare table that you should execute after the create table. You can simply generate such ddl from existing table with data using the select export_objects('','same table you have') .

Theory

Thanks. Is there no way to get Vertica to automatically create the default projection other than by inserting data? I tried calling REFRESH() to no avail. It looks like I can INSERT a row in a transaction and roll it back, though that feels kind of gross.

Theory

No, I was asking about creating a default projection. Thanks for submitting the request that LOCK be documented, much appreciated!

Theory

Hrm. Looks like if I just put ENCODING AUTO on a single column expression in the CREATE TABLE statement, it immediately creates the default projection. The only difference I can see from the default projection created when data is first inserted is in the comment when I export it: It says "/*+basename(changes),createtype(P)*/ " instead of "/*+basename(changes),createtype(L)*/ ". Is there any other difference I need to worry about?