Skip to main content

LOCK command is under-documented, fails on empty table

  • September 5, 2014
  • 8 replies
  • 26 views

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?

8 replies

eli_revach
Forum|alt.badge.img+2
  • Participating Frequently
  • September 5, 2014
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
Forum|alt.badge.img
  • Participating Frequently
  • September 5, 2014
Hi, I will log a doc bug for this as this is not listed as you mentioned.
Thanks

Theory
  • Author
  • New Participant
  • September 5, 2014
How do I do that?

wwong2
Forum|alt.badge.img
  • Participating Frequently
  • September 5, 2014
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
Forum|alt.badge.img+2
  • Participating Frequently
  • September 5, 2014
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
  • Author
  • New Participant
  • September 5, 2014
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
  • Author
  • New Participant
  • September 5, 2014
No, I was asking about creating a default projection. Thanks for submitting the request that LOCK be documented, much appreciated!

Theory
  • Author
  • New Participant
  • September 5, 2014
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?