Weird behavior during CREATE EXTENSION

This behavior had be quite baffled...

> ~(at)decina(dot)local/29760# create extension "trunklet-format" CASCADE;
> NOTICE: installing required extension "trunklet"
> NOTICE: installing required extension "variant"
> CREATE EXTENSION
> ~(at)decina(dot)local/29760# create extension "pgxntool-test";
> ERROR: syntax error at or near "-"
> LINE 1: create extension "pgxntool-test";
> ^
> ~(at)decina(dot)local/29760# select * from pg_available_extensions where name ~'-';
> name | default_version | installed_version | comment
> -----------------+-----------------+-------------------+-------------------------------------------------
> pgxntool-test | 0.1.0 | |
> trunklet-format | 0.1.1 | 0.1.1 | A format()-based template language for trunklet
> (2 rows)

Eventually, I realized the problem was the first line of the extension
file itself:

CREATE FUNCTION pgxntool-test(

wrapping that in "s fixed the issue. (The reason that still doesn't line
up with the ^ above is because the ^ is accounting for "LINE 1: ".)

This makes debugging extensions quite tedious. Part of the explanation
is in the comment for execute_sql_string():

> /*
> * Execute given SQL string.
> *
> * filename is used only to report errors.
> *
> * Note: it's tempting to just use SPI to execute the string, but that does
> * not work very well. The really serious problem is that SPI will parse,
> * analyze, and plan the whole string before executing any of it; of course
> * this fails if there are any plannable statements referring to objects
> * created earlier in the script. A lesser annoyance is that SPI insists
> * on printing the whole string as errcontext in case of any error, and that
> * could be very long.
> */

I can think of 4 ways to fix this:

1) Have psql parse the script into separate commands for us.
2) Pull enough of psql's parsing into the backend code to be able to do #1
3) Add *file* line numbers to the output of pg_parse_query()
4) Have ereport spit out the context you'd normally get from SPI if it
sees that it was called from somewhere underneath execute_sql_string().

My preference would actually be #1, because that would make it easy for
any tool that wanted to to get access to that. Jon Erdman actually
looked into a similar alternative in the past and it was only a few
lines of code. Basically, when the "parse file" option is chosen don't
even attempt to connect to a database, just parse things, execute \
commands and print the results instead of sending them via libpq. That
wouldn't work directly here because we want to split commands apart, but
it wouldn't be hard to have psql spit out a special command separator
line and then look for that. psql would have to ignore \quit in this
mode though, but I think that's fine.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com