Re: named queries and the wire protocol - Mailing list pgsql-general

Hi,

> David Welton <davidnwelton@gmail.com> writes:
>> Specifically, I'm wondering how this code behaves in the case that the
>> Execute runs into trouble:
>
>> https://github.com/epgsql/epgsql/blob/0e84176be4b54fb712d1cc227a2b91c24b7a66ab/src/pgsql_sock.erl#L199
>
> I guess you mean this:

Yes

> command({equery, Statement, Parameters}, State) ->
>     #statement{name = StatementName, columns = Columns} = Statement,
>     Bin1 = pgsql_wire:encode_parameters(Parameters),
>     Bin2 = pgsql_wire:encode_formats(Columns),
>     send(State, ?BIND, ["", 0, StatementName, 0, Bin1, Bin2]),
>     send(State, ?EXECUTE, ["", 0, <<0:?int32>>]),
>     send(State, ?CLOSE, [?PREPARED_STATEMENT, StatementName, 0]),
>     send(State, ?SYNC, []),
>     {noreply, State};
>
> Bearing in mind that I don't know Erlang, so I'm just assuming that these
> commands work as they're apparently intended to ...

They're asynchronous, but yes, I think it's a safe assumption.

>> Does the Close still clean things up properly?

> If either the Bind or the Execute fails, the server will discard messages
> till Sync, so the Close is not executed.  But I'm not sure why you'd want
> this subroutine to destroy the prepared statement?  Which is what this
> code appears to be doing.  A Close on the unnamed portal created by the
> Bind would make sense there.  It's not really necessary, since the unnamed
> portal is recycled anyway when next used, but it's good style.  (Because
> it's not necessary, there's no need to worry about it not being executed
> if the Execute fails.)

> If you were using a named portal for execution, error recovery would
> become a more interesting topic, but with the unnamed portal you don't
> need to sweat it much.

This is code I inherited and am trying to clean up, so I'm not 100%
sure why it does what it does.  The general flow looks like this,
though:

equery(C, Sql, Parameters) ->
    Name = ["equery-", atom_to_list(node()), pid_to_list(self())],
%% ^^^^^^^^^^^^^^^^^^^ generated name ^^^^^^^^^^^^^^^^^^^^^^^^^^

    case parse(C, Name, Sql, []) of
        {ok, #statement{types = Types} = S} ->
            Typed_Parameters = lists:zip(Types, Parameters),
            gen_server:call(C, {equery, S, Typed_Parameters}, infinity);
        Error ->
            Error
    end.

And then the code above.  So it's generating a name itself and then
destroying it once the query is done.

Perhaps this behavior is not a good idea and using the unnamed portal
would be a better idea?

Thank you!

--
David N. Welton

http://www.dedasys.com/