Re: proposal: psql: show current user in prompt

čt 10. 8. 2023 v 14:05 odesílatel Jelte Fennema <postgres(at)jeltef(dot)nl> napsal:

> On Tue, 8 Aug 2023 at 07:20, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > The reason why I implemented separate flow is usage from psql and
> independence of transaction state. It is used for the \set command, that
> is non-transactional, not SQL. If I inject this message to some other flow,
> I lose this independence.
>
> I still don't understand the issue that you're trying to solve by
> introducing a new flow for handling the response. What do you mean
> with independence of the transaction state? That it is not rolled-back
> in a case like this?
>
> BEGIN;
> \set PROMPT '%N'
> ROLLBACK;
>

surely not.

\set is client side setting, and it is not transactional. Attention -
"\set" and "set" commands are absolutely different creatures.

> That seems more like a Postgres server concern, i.e. don't revert the
> change back on ROLLBACK. (I think your current server-side
> implementation already does that)
>

Postgres does it, but not on the client side. What I know, almost all
environments don't support transactions on the client side. Postgres is not
special in this direction.

>
> I guess one reason that I don't understand what you mean is that libpq
> doesn't really care about "transaction state" at all. It's really a
> wrapper around a socket with easy functions to send messages in the
> postgres protocol over it. So it cares about the "connection state",
> but not really about a "transaction state". (it does track the current
> connection state, but it doesn't actually use the value except when
> reporting the value when PQtransactionStatus is called by the user of
> libpq)
>
> > Without independence on transaction state and SQL, I can just implement
> some SQL function that sets reporting for any GUC, and it is more simple
> than extending protocol.
>
> I don't understand why this is not possible. As far as I can tell this
> should work fine for the usecase of psql. I still prefer the protocol
> message approach though, because that makes it possible for connection
> poolers to intercept the message and handle it accordingly. And I see
> some use cases for this reporting feature for PgBouncer as well.
>

Maybe we are talking about different features. Now, I have no idea how the
proposed feature can be useful for pgbouncer?

Sure If I implement a new flow, then pgbouncer cannot forward. But it is
not too hard to implement redirection of new flow to pgbouncer.

> However, I think this is probably the key thing that I don't
> understand about the problem you're describing: So, could you explain
> in some more detail why implementing a SQL function would not work for
> psql?
>

I try to get some consistency. psql setting and some features like
formatting doesn't depend on transactional state. It depends just on
connection. This is the reason why I don't want to inject dependency on
transaction state. Without this dependency, I don't need to check
transaction state, and I can execute prompt settings immediately. If I
implement this feature as transactional, then I need to wait to idle or to
make a new transaction (and this I have not under my control). I try to be
consistent with current psql behaviour. It Would be strange (can be very
messy) if I had a message like "cannot set a prompt, because you should do
ROLLBACK first"

When this feature should be implemented as an injected message, then I have
another problem. Which SQL command I have to send to the server, when the
user wants to set a prompt? And then I don't need to implement a new
message, and I can just implement the SQL function
pg_catalog.report_config(...).