wg / epgsql Goto Github PK

It would be nice to see epgsql use rebar so that it can easily be integrated with rebar based projects (and rebar seems quite nice). Its not really a requirement I suppose just a wish.

I have a system which uses pgsql to query a database. The system is running 24/7, polling the database for changes. Unfortunately the database get's shut down from time to time, resulting in a timeout(timeout, State) status in pgsql_connection. The connection gets closed afterwards, which is OK I think, because after the database restart, the connection must be reestablished anyway.

I would like epgsql to handle this reconnection procedure internally, is this possoble? If not, how can I be notified of the broken connection to reestablish the connection by myself?

pgsql:equery(C,"select * from $1",["a_new_table_name"]).
{error,{error,error,<<"42601">>,
<<"syntax error at or near "$1"">>,
[{position,<<"15">>}]}}

but this is ok.

pgsql:equery(C,"select * from a_new_table_name;",[]).
{ok,[{column,<<"id">>,{unknown_oid,2950},16,-1,0},
{column,<<"name">>,text,-1,-1,1},
{column,<<"type">>,text,-1,-1,1},
{column,<<"default_value">>,text,-1,-1,1}],
[]}

I'm sometimes get the
{error,error,<<"26000">>, <<"unnamed prepared statement does not exist">>, []}
error -- why it can be ?!
just re-send that query again fixes it

array of float8 is not supported

Noticed that with_connection as well as BEGIN,COMMIT usage examples only used squery?

what's the best way to run equery with transaction ?

~B

The docs aren't clear on when I should use close(C, Statement) and sync(C). Am I supposed to do those two after every execution?

It would be great to add support for PostgreSQL v9.2

It has support for JSON data types, which is a huge upgrade from the original hstore data type.

Any plans for adding v9.2 compatibility?

Can you help me, i don't understand why after generate release? i get this error.
All work ok, when i develop and testing.
But when i generate release with rebar i always have this error.

Hi,
do you have .pgpass support. I am trying but cannot connect without giving the password for the postgresql connection.

Great library, it's been very helpful. But if I was to change anything I'd ask that the documentation about the timeout be a little clearer. In the readme it says the timeout option is for connections when in reality it is also used by the finite state machine to timeout queries.

As you probably already know, that means that using the default of 5 seconds will return an {error,timeout} on any query that takes more than 5 seconds.

Just for context:
I'm using this in conjunction with epgsql_pool and pounding the snot out of a >350,000,000 record table that I haven't bothered to partition yet so it manifested when I was doing ~500 inserts a second. After I looked through pgsql_connection.erl I saw that timeout was being passed to the state record as well. I then upped the timeout to 60,000 in the options I pass in and I stopped getting {error,timeout} returned from queries.

Sometimes, when database is slow and number of requests is big psql_connection is crushing because of message 'timeout' got in state 'timeout'. Is it a valid behaviour? I am using poolboy to store connections in gen_server and pgaql timeout is lesser than gen_servers timeout.

Is the UUID data type supported by epgsql? If so, how would I use it?

(see http://www.postgresql.org/docs/9.1/static/datatype-uuid.html for the datatype in question)

Regards,
-T.

The following query works:

pgsql:equery(C, "select name from servers where id = any ($1)", [[1, 2, 4]]).

whereas this one does not:

pgsql:equery(C, "select name from servers where id in ($1)", [[1, 2, 4]]).

The only difference is the use of id in ($1) vs. id = any ($1). The second version results in an error:

=ERROR REPORT==== 6-Aug-2013::10:04:58 ===
** State machine <0.1053.0> terminating
** Last message in was {'$gen_sync_event',
                           {<0.1051.0>,#Ref<0.0.0.199>},
                           {equery,
                               {statement,[],
                                   [{column,<<"name">>,varchar,-1,54,1}],
                                   [int4]},
                               [{int4,[1,2,4]}]}}
** When State == ready
**      Data  == {state,undefined,<0.1054.0>,5000,
                        [{<<"application_name">>,<<>>},
                         {<<"client_encoding">>,<<"UTF8">>},
                         {<<"DateStyle">>,<<"ISO, MDY">>},
                         {<<"integer_datetimes">>,<<"on">>},
                         {<<"IntervalStyle">>,<<"postgres">>},
                         {<<"is_superuser">>,<<"on">>},
                         {<<"server_encoding">>,<<"UTF8">>},
                         {<<"server_version">>,<<"9.2.3">>},
                         {<<"session_authorization">>,<<"postgres">>},
                         {<<"standard_conforming_strings">>,<<"on">>},
                         {<<"TimeZone">>,<<"US/Central">>}],
                        undefined,
                        {<0.1051.0>,#Ref<0.0.0.198>},
                        undefined,
                        {11102,347906346},
                        {statement,[],undefined,[int4]},
                        73}
** Reason for termination =
** {badarg,[{pgsql_binary,encode,2,[{file,"src/pgsql_binary.erl"},{line,14}]},
            {pgsql_connection,encode_parameter,1,
                              [{file,"src/pgsql_connection.erl"},{line,626}]},
            {pgsql_connection,encode_parameters,4,
                              [{file,"src/pgsql_connection.erl"},{line,618}]},
            {pgsql_connection,ready,3,
                              [{file,"src/pgsql_connection.erl"},{line,228}]},
            {gen_fsm,handle_msg,7,[{file,"gen_fsm.erl"},{line,494}]},
            {proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,239}]}]}
** exception exit: badarg
     in function  pgsql_binary:encode/2 (src/pgsql_binary.erl, line 14)
     in call from pgsql_connection:encode_parameter/1 (src/pgsql_connection.erl, line 626)
     in call from pgsql_connection:encode_parameters/4 (src/pgsql_connection.erl, line 618)
     in call from pgsql_connection:ready/3 (src/pgsql_connection.erl, line 228)
     in call from gen_fsm:handle_msg/7 (gen_fsm.erl, line 494)
     in call from proc_lib:init_p_do_apply/3 (proc_lib.erl, line 239)

Both queries work just fine if run manually from the command line, i.e.,

select name from servers where id in (1, 2, 3);

and

select name from servers where id = any (1, 2, 3);

both work fine.

What's the best way to overcome the following issue ?

         %% assume table1 & a connection C exists
         pgsql:squery(C, "create table table1 (id varchar);"). 

         %% this works 
         eg: pgsql:squery(C,"select id from table1; insert into table1 (id) values(1);") 

         %% but this fails 
         eg: pgsql:equery(C,"select id from table1; insert into table1 (id) values($1);",[1])
         %% with error
         {error,error,<<"42601">>,
          <<"cannot insert multiple commands into a prepared statement">>,
          []}.

~B

Hi there, I am trying to call stored procedure this way pgsql:equery(C, "select * from my_stored_procedure()"), and it is kind of doesn't work. Any ideas what could be the reason?

Couldn't see how to do this, is it possible?

When inserting into a table with an auto incrementing/SERIAL primary key

Thanks!

I ran into a crash today where the pgsql_connection is in the parsing state, and from the sasl logs I see that reply={error, timeout} in the state record. It crashes because it receives the message {$2, <<>>} and that is not a valid message while in the parsing state.

I found it interesting that when parsing times out it continues on in the parsing state:

parsing(timeout, State) ->
    #state{timeout = Timeout} = State,
    Reply = {error, timeout},
    send(State, $S, []),
    {next_state, parsing, State#state{reply = Reply}, Timeout};

Why does it not go to the timeout state like querying?

querying(timeout, State) ->
    #state{sock = Sock, timeout = Timeout, backend = {Pid, Key}} = State,
    pgsql_sock:cancel(Sock, Pid, Key),
    {next_state, timeout, State, Timeout};

I'm missing COPY command implementation. Is there any plan to add it?

Hi Will,

I'm using epgsql as part of Chicago Boss, and one thing that I miss from Rails is the ability to log SQL queries, to see exactly what is going on under the hood.

Since the actual queries are run in egpsql, I suppose that's the best place to fish out the actual SQL used.

I'm not quite sure how this might work... perhaps it's as simple as just adding it as an info_msg with the logger, and if people don't want to see it, they can silence those messages. Or a debug flag...

I have changes for this, attached.

Is there any build in functions?

Currently, epgsql will not allow the user to request a sync while the connection is in the "executing" state. When an error occurs in this state, then, the connection becomes wedged because the server requires a sync request to continue:

When an error is detected while processing any extended-query message, the backend issues ErrorResponse, then reads and discards messages until a Sync is reached, then issues ReadyForQuery and returns to normal message processing. (Source: http://www.postgresql.org/docs/8.4/interactive/protocol-flow.html)

The following commit creates a new "awaiting_sync" state and modifies the connection's state machine to transition into this state when an error is encountered in the "executing" state. In this new state, the user can request a sync but nothing else. Attempts to make other requests result in awaiting_sync errors, alerting the user to the server's expectations.

Commit: https://github.com/tmoertel/epgsql/commits/extended_query_limbo