wg / epgsql Goto Github PK
View Code? Open in Web Editor NEWErlang PostgreSQL client
License: BSD 3-Clause "New" or "Revised" License
Erlang PostgreSQL client
License: BSD 3-Clause "New" or "Revised" License
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 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?
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 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?
Is there any build in functions?
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
I'm missing COPY command implementation. Is there any plan to add it?
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};
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?
I have changes for this, attached.
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'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
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.
array of float8 is not supported
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.
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.
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
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...
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?
Hi,
do you have .pgpass support. I am trying but cannot connect without giving the password for the postgresql connection.
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
==> epgsql (compile)
compile: warnings being treated as errors
compile: warnings being treated as errors
Compiled src/pgsql.erl
src/pgsql_sock.erl:68: variable 'State2' exported from 'case' (line 59)
src/pgsql_idatetime.erl:47: variable 'J4' exported from 'case' (line 42)
src/pgsql_sock.erl:69: variable 'Opts3' exported from 'case' (line 44)
src/pgsql_sock.erl:156: variable 'Channel' exported from 'case' (line 152)
src/pgsql_sock.erl:156: variable 'Payload' exported from 'case' (line 152)
src/pgsql_idatetime.erl:61: variable 'Y2' exported from 'case' (line 53)
src/pgsql_idatetime.erl:64: variable 'M2' exported from 'case' (line 53)
make: *** [compile] Fehler 1
# erl -v
Erlang R16B02 (erts-5.10.3) [source] [64-bit] [smp:2:2] [async-threads:10] [hipe] [kernel-poll:false]
Eshell V5.10.3 (abort with ^G)
Couldn't see how to do this, is it possible?
When inserting into a table with an auto incrementing/SERIAL primary key
Thanks!
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.