Monthly Archives: July 2014

Timestamps in Vertica

I was recently trying to find long running queries in Vertica, as we had something that was killing our performance of our cluster, and I got bit by this little bit of behavior around getting the “current” time in Vertica.

For some context, I had an open tmux session on a development server with one of the windows setup to be an open connection to our Vertica database¹.

The part about having that database session open in a tmux session is important, because the database session had been open for at least a week at the point in time I was trying to do some checks to find the queries that had been running the longest.

What bit me was that current_timestamp, and now() functions are tied ‘to the start of the current transaction […] to allow a single transaction to have a consistent notion of the “current” time […]’.

The way this bit me, was I was seeing queries that were running for -7 days, because I was doing math on the timestamp against the current_timestamp.

someuser => select current_timestamp;
           ?column?           
-------------------------------
 2014-07-11 10:45:17.135025-05
(1 row)

someuser=> select now();
              now             
-------------------------------
 2014-07-11 10:45:17.135025-05
(1 row)

What I needed to be using was statement_timestamp(), which ‘returns the start time of the current statement’

someuser=> select statement_timestamp();
              now             
-------------------------------
 2014-07-18 14:23:36.492831-05
(1 row)

And that is how you get a more up to date timestamp in your Vertica queries if you are in a long running database session.

–Proctor

Since I have to help fill the role of a database administrator as part of being on the data team at work, and want quick access to be able to run some queries when things like the above happen, instead of hoping I can establish a connection. ↩

Granting Users Select Permission in Vertica

At work we use Vertica for one of our databases. As part of the data team, it is our job to help monitor and maintain the data in the database, as well as the health of the database as well. One of the things I have been doing is to create database users for the different services that are using the database.

This helps us when we have long running queries, high load on the database, issues with the number of connections, and it would also allow us to add constraints around all of those in the future on a per service basis based off of the different user accounts, not to mention restrict the permissions to tables that are needed and allow some of the experimental tables to exist without causing confusion by having people see those tables before they have been stabilized.

First we create a user for each service with a username and a password; the quotes around the username signify to Vertica that the username is case sensitive.

CREATE USER "UserName"  IDENTIFIED BY 'password';

We then grant SELECT only permissions to the tables that are specific to that user. We do SELECT only permissions as this is a data warehouse style database, and only a few select services have permissions to update the database, while everyone else should be free to read from the tables they need, or are stable.

grant select on <tablename> to "UserName";

We also have some helper functions that we give all of the users, and as I still usually can’t remember the functions and their argument types, I have to do a lookup of the different functions each time, because when granting permissions on Vertica functions, you need to specify the function name along with the arguments.

This query finds me all of the user defined functions in the database.

select * from user_functions;

Once I have the functions and arguments, I can then grant EXECUTE permissions on the functions, by providing the function name and the argument types.

grant execute on function <function name with argument types> to "UserName";

So to grant execute permissions to the function foo that has a number of overloads, it might look like.

grant execute on function foo(Float, Float) to "UserName";
grant execute on function foo(Float, Integer) to "UserName";
grant execute on function foo(Float, Numeric) to "UserName";
grant execute on function foo(Integer, Integer) to "UserName";
grant execute on function foo(Integer, Numeric) to "UserName";
grant execute on function foo(Numeric, Integer) to "UserName";
grant execute on function foo(Numeric, Numeric) to "UserName";

I hope this helps someone the next time they find themselves having to help play db admin/owner on a Vertica database.

–Proctor

Stop Writing Code

Code: n. A system of symbols, letters, or words given certain arbitrary meanings, used for transmitting messages requiring secrecy or brevity.
–American Heritage® Dictionary of the English Language

This is not what we should be writing.

I started thinking that we as an industry need a new term. The reason I say this is related to the Sapir-Whorf Hypothesis, which says that the language we use influences the way we experience the world around us. If we keep referring to what we write as code, we are going to keep writing software that ‘requires secrecy or brevity’, instead of writing software intended for other people to read. Ableson and Sussman nailed it back in 1985, in the preface to the first edition of Structure and Interpretation of Computer Programs, and I am sure they weren’t the first either.

First, we want to establish the idea that a computer language is not just a way of getting a computer to perform operations but rather that it is a novel formal medium for expressing ideas about methodology. Thus, programs must be written for people to read, and only incidentally for machines to execute.
–Structure and Interpretation of Computer Programs

Let’s take a moment and take a look at another term we use commonly.

program: Late Latin programma, public notice, from Greek programma, programmat-, from prographein, to write publicly : pro-, forth; see PRO-2 + graphein, to write; see gerbh- in Indo-European roots.
–American Heritage® Dictionary of the English Language

That is not bad, as it refers to writing publicly, so maybe, if we start thinking in that sense of the word, we would be okay; but odds are, it has deviated too far from that original meaning in common thought to try to rescue it and bring it back towards that original meaning. If we are going to come up with a better term, maybe we need to pick a term that expresses that we are telling the story of the system; XP talks about Metaphor of the system; and Eric Evans in Domain Driven Design talks about Ubiquitous Language; others talk about modeling the system; but the commonality is that we want what we write to expressive and suggestive. We have a word for the type of writing they describe.

poem: n. A verbal composition designed to convey experiences, ideas, or emotions in a vivid and imaginative way, characterized by the use of language chosen for its sound and suggestive power and by the use of literary techniques such as meter, metaphor, and rhyme.
[French poème, from Old French, from Latin poēma, from Greek poiēma, from poiein, to create; see kwei-2 in Indo-European roots.]
–American Heritage® Dictionary of the English Language

I am not saying that we should all start writing software poetry, but as an example of a word in our lexicon that may have a better fit. I suggest we think about what words would better describe the kind of software we wish we were reading, and then, see how the words we choose change the way we write software. What if we tried writing software poetry, software novellas, or software as the literature of the system, and see how that affects the way we write our software. Do we gain expressivity? Do we encourage others to read what we have written and get editorial reviews? Do we make sure to go back and read aloud what has been written to see if we stumble upon awkward phrasing? Do we take time to go back and try to rewrite what we have just written to make sure it is written well?

I have recently started reading On Writing Well by William Zinsser, and while he is talking about the word processor, I think it relates to writing software as well, since all too often we just move on and submit our writing as soon as we think it is working.

On one level the new torrent is good news. Any invention that reduces the fear of writing is up there with air-conditioning and the lightbulb. But, as always, there’s a catch. Nobody told all the new computer writers that the essence of writing is rewriting. Just because they’re writing fluently doesn’t mean they’re writing well.
–On Writing Well

–Proctor