Unix sockets

[reist]

Hi, Trying to set pgloader to use a secured configuration (only socket connections can log in to modify data) fails with different errors relating to INTO line parsing. Config example:

LOAD CSV
 FROM '/full/file/path'
 INTO postgresql://unix:/tmp/.s.PGSQL.5432/stats?a_table
;

This errors out with 'Could not parse subexpression "/" when parsing' Trying to add ':5432' (or even just a colon) after the path errors out with 'The value "/tmp/.s.PGSQL.5432" is not of type LIST.'

Same thing if I omit the filename itself. Am I missing something?

Thanks

[dimitri]

Well the parsing of this connection string is ambiguous because there's no clear marking of the end of the unix socket path. Adding the port separator (:) makes it unambiguous:

postgresql://unix:/tmp/.s.PGSQL.5432:/stats?a_table

[reist]

That makes sense. The problem is that even with the colon in I get an error. I can go around it by allowing a local net connection to also allow modifications, but it would be nicer if sockets would work.

Here's the beginning of the backtrace I get trying using the string you pasted.

An unhandled error condition has been signalled:
   The value "/tmp/.s.PGSQL.5432" is not of type LIST.

Date/time: 2014-02-21-08:59An unhandled error condition has been signalled:
                              The value "/tmp/.s.PGSQL.5432"
                              is not of type
                                LIST.

Backtrace for: #<SB-THREAD:THREAD "main thread" RUNNING {1007103413}>
0: (SB-C::IR1-CONVERT-COMBINATION-ARGS #<SB-C::LVAR 1 {10079C8223}> #<SB-C::CTRAN 2 {10079C81F3}> #<SB-C::CTRAN 3 {10079C6773}> #<SB-C::LVAR 4 {10079C67A3}> "/tmp/.s.PGSQL.5432") [tl,external]
1: (SB-C::IR1-CONVERT-COMBINATION #<SB-C::CTRAN 5 {1007912F23}> #<SB-C::CTRAN 3 {10079C6773}> #<SB-C::LVAR 4 {10079C67A3}> (:UNIX . "/tmp/.s.PGSQL.5432") #<SB-C::GLOBAL-VAR :%SOURCE-NAME :UNIX :TYPE #1=#<SB-KERNEL:BUILT-IN-CLASSOID FUNCTION (read-only)> :DEFINED-TYPE #1# :KIND :GLOBAL-FUNCTION {10079C8183}>)
2: (SB-C::IR1-CONVERT-COMBINATION-CHECKING-TYPE #<SB-C::CTRAN 5 {1007912F23}> #<SB-C::CTRAN 3 {10079C6773}> #<SB-C::LVAR 4 {10079C67A3}> (:UNIX . "/tmp/.s.PGSQL.5432") #<SB-C::GLOBAL-VAR :%SOURCE-NAME :UNIX :TYPE #1=#<SB-KERNEL:BUILT-IN-CLASSOID FUNCTION (read-only)> :DEFINED-TYPE #1# :KIND :GLOBAL-FUNCTION {10079C8183}>)
3: (SB-C::IR1-CONVERT #<SB-C::CTRAN 5 {1007912F23}> #<SB-C::CTRAN 3 {10079C6773}> #<SB-C::LVAR 4 {10079C67A3}> (:UNIX . "/tmp/.s.PGSQL.5432") NIL)
4: (SB-C::IR1-CONVERT-COMBINATION-ARGS #<SB-C::LVAR 6 {1007912F53}> #<SB-C::CTRAN 5 {1007912F23}> #<SB-C::CTRAN 7 {1007912B73}> #<SB-C::LVAR 8 {1007912BA3}> ((:UNIX . "/tmp/.s.PGSQL.5432")))

[dimitri]

Seems I was to quick in my answer and forgot the second part, which is as important as the first one. The expected unix: value is a path. You don't have to spell out the socket file name, because that's how it works with libpq based clients too. You need to give a port number.

Here's a limited test case that I just did to confirm all what we're saying:

PARSER> (with-monitor (:start-logger nil)
          (with-database-uri ("postgresql://unix:/tmp:54393/nba?team")
            (with-pgsql-transaction ()
              (pomo:query "select 1" :single))))
1
1

So you should actually use the connection string postgresql://unix:/tmp:5432/stats?a_table in your case. If you have some time to look at how to tweak the documentation around the issue, I would be glad to integrate your improvements (Doc Pull Requests are certainly accepted).

[reist]

Sorry, this is my first meeting with CLisp. I'd be happy to improve the documentations, but for now this just doesn't work for me.

After figuring out how, trying the above with my own connection string gives me: "No database connection selected." If I understand this right, the parse returns correctly:

(parse 'db-connection-uri "postgresql://unix:/tmp:5432/stats?a_table")
(:TYPE :POSTGRESQL :USER "reist" :PASSWORD NIL :HOST (:UNIX . "/tmp") :PORT 5432 :DBNAME "stats" :TABLE-NAME "a_table")

The problem is, running pgloader locally with the socket in /var/run/postgres and -d still gives the same error:

An unhandled error condition has been signalled:
   The value "/var/run/postgres" is not of type LIST.

Date/time: 2014-02-24-11:57An unhandled error condition has been signalled:
                              The value "/var/run/postgres" is not of type LIST.

Backtrace for: #<SB-THREAD:THREAD "main thread" RUNNING {10085EB563}>
0: (SB-C::IR1-CONVERT-COMBINATION-ARGS #<SB-C::LVAR 1 {1008EB1E23}> #<SB-C::CTRAN 2 {1008EB1DF3}> #<SB-C::CTRAN 3 {1008EB03C3}> #<SB-C::LVAR 4 {1008EB03F3}> "/var/run/postgres") [tl,external]
1: (SB-C::IR1-CONVERT-COMBINATION #<SB-C::CTRAN 5 {1008E02653}> #<SB-C::CTRAN 3 {1008EB03C3}> #<SB-C::LVAR 4 {1008EB03F3}> (:UNIX . "/var/run/postgres") #<SB-C::GLOBAL-VAR :%SOURCE-NAME :UNIX :TYPE #1=#<SB-KERNEL:BUILT-IN-CLASSOID FUNCTION (read-only)> :DEFINED-TYPE #1# :KIND :GLOBAL-FUNCTION {1008EB1D83}>)
2: (SB-C::IR1-CONVERT-COMBINATION-CHECKING-TYPE #<SB-C::CTRAN 5 {1008E02653}> #<SB-C::CTRAN 3 {1008EB03C3}> #<SB-C::LVAR 4 {1008EB03F3}> (:UNIX . "/var/run/postgres") #<SB-C::GLOBAL-VAR :%SOURCE-NAME :UNIX :TYPE #1=#<SB-KERNEL:BUILT-IN-CLASSOID FUNCTION (read-only)> :DEFINED-TYPE #1# :KIND :GLOBAL-FUNCTION {1008EB1D83}>)
3: (SB-C::IR1-CONVERT #<SB-C::CTRAN 5 {1008E02653}> #<SB-C::CTRAN 3 {1008EB03C3}> #<SB-C::LVAR 4 {1008EB03F3}> (:UNIX . "/var/run/postgres") NIL)
4: (SB-C::IR1-CONVERT-COMBINATION-ARGS #<SB-C::LVAR 6 {1008E02683}> #<SB-C::CTRAN 5 {1008E02653}> #<SB-C::CTRAN 7 {1008E022A3}> #<SB-C::LVAR 8 {1008E022D3}> ((:UNIX . "/var/run/postgres")))
...

[dimitri]

I need to see the command line you just used to produce the error message about /var/run/postgres, please.

[reist]

pgloader.exe -d test.load

Minimum example test.load:

LOAD CSV
 FROM '/tmp/data.tsv'
 INTO postgresql://unix:/var/run/postgres:5432/stats?a_table
 WITH fields terminated by '\t',
      fields optionally enclosed by '"'
 ;

[dimitri]

Thanks for your detailed test case, I could reproduce and fix the issue. Note that I had to change the unix socket path to /var/run/postgresql on my debian testing machine (running wheezy).

[reist]

Thank you! It works perfectly now.

[pandrewhk]

Hi guys! I got this error with pgloader 3.0.99 on debian:

STYLE-WARNING: Undefined alien: "SSLv2_client_method"
An unhandled error condition has been signalled:
   The value "/var/run/postgres" is not of type LIST.

;
; compilation unit aborted
;   caught 1 fatal ERROR condition

The command was:

load database
     from mysql://unix:/var/run/mysqld/mysqld.sock:/main
     into postgresql://unix:/var/run/postgres:5432/main

 WITH include drop, create tables, no truncate,
      create indexes, reset sequences, foreign keys;

[dimitri]

Thanks for that bug report, not only did I forget to implement local socket support for MySQL entirely, but also did I miss some cases where the PostgreSQL code path would fail with the same error too.

To use the fixed version of pgloader, you will need to recompile it now!

[pandrewhk]

Thank you so much!

[dimitri]

By the way, next time we have this conversation, you will be able to use pgloader --self-upgrade ... to test the patch without having a full development environment ;-)