Search code examples
postgresqlplpgsqlpgadmin

Matching up line numbers from log file to function's code

I have an error I'm trying to debug in a trigger function. In the Postgres log, I have the following:

2012-08-16 21:41:23 PDT ERROR:  Unknown period: D
2012-08-16 21:41:23 PDT CONTEXT:  PL/pgSQL function "handle_promotion_update" 
    line 60  at assignment
SQL statement "UPDATE promotion SET some_column = foo + 1
    WHERE id = NEW.promotion_id"
PL/pgSQL function "handle_new_reward" line 94 at SQL statement

So, it seems like my problem probably starts at "line 60"

My question, is where do the line numbers start? If I go in pgAdmin and look at the function declaration it has "helper" (like delete function) at the top. So, I don't think it's from there. But, does it include the function declaration? Or is it just from the line with BEGIN?

Solution

  • Line numbers that you see in the PostgreSQL server logs start at the line with the opening quotes of the function body. You have to add the lines before that to arrive at the absolute line number in the pgAdmin SQL editor.

    Don't get confused: if you have errors while trying to create a function you get different line numbers: they refer to the executed script as a whole, while line numbers in runtime errors refer to the function body.