Re: [HACKERS] Adding connection id in the startup message

2017-06-21 Thread David G. Johnston 
On Wed, Jun 21, 2017 at 12:15 PM, Robert Haas  wrote:
> I think the problem is real,
> but I'm not sure that this is the best solution.  On the other hand,
> I'm also not entirely sure I understand the proposal yet.

Given the problems with changing the protocol it does seem like
generalizing away from "connection id" to "arbitrary payload" would be
useful.

Like, add a new "attachment map" area where any application-aware node
that encounters the message can attach data.  If some node further
downstream sends a response message the contents of the attachment map
would be sent back as-is.

The advantage of "connection id" is that the messages are still of
fixed size whereas the more flexible arrangement would necessitate
that message sizes vary.  In return middleware gets a place to store
session information that can be used more broadly than a simple
externally generated connection id.

David J.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Adding connection id in the startup message

2017-06-21 Thread Robert Haas 
On Thu, Jun 15, 2017 at 9:50 AM, Tom Lane  wrote:
> Can you give a concrete example where this would have
> helped above and beyond knowing, eg, the source and time of the connection
> attempt?

I can imagine that in really high-volume use cases (such as the OP
apparently has) the number of client connections might be so large
that identification by timestamp isn't useful, and the source IP will
be obscured after the first hop through a connection pooler or other
middleware.  If you've got 100 connections per second coming in,
matching things up by timestamp across different machines is going to
be tough.

But I agree with your other concerns.  I think the problem is real,
but I'm not sure that this is the best solution.  On the other hand,
I'm also not entirely sure I understand the proposal yet.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Adding connection id in the startup message

2017-06-21 Thread Robert Haas 
On Thu, Jun 15, 2017 at 3:11 AM, Satyanarayana Narlapuram
 wrote:
> The proposal is to tweak the connectivity wire protocol, and add a
> connection id (GUID) filed in the startup message. We can trace the
> connection using this GUID and investigate further on where the connection
> failed.

Wire protocol changes are scary, because they can result in a need to
update every client connector and every bit of middleware that speaks
PostgreSQL, not just the server.  If we thought this feature had
enough value to justify adding it, we could add it as an optional
feature so that existing clients don't break.  But if we added it and
only libpq and the server ended up supporting it, that would be a
disappointing outcome.

> Client adds a connection id in the startup message and send it to the server
> it is trying to connect to. Proxy logs the connection id information in its
> logs, and passes it to the server. Server logs the connection Id in the
> server log, and set it in the GUC variable (ConnectionId).

Are you imagining that this would be done by the client or by the
driver the client is using?  If the latter, it's transparent but maybe
useless, because the client won't even know that this ID got created.
If it's done by the client code proper, then it only works if not only
the server and every bit of middleware and every connector but also
every client application in the world is updated, which does not seem
like a thing that is likely to occur.

> This field can be an optional field driven by the connection parameters for
> PSql (-C or--include-clientId).

This makes it sound like you're imagining it being added by an
explicit action on the part of the client, which means we're in
update-every-PostgreSQL-application-in-the-world territory.

> P.S: I am looking for initial feedback on this idea and can provide more
> design details if needed.

I think you need to build a more compelling case for why this would be
useful, why application_name isn't the right answer, and how we avoid
forcing everybody in the world to worry about this new thing.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Adding connection id in the startup message

2017-06-15 Thread Tom Lane 
Satyanarayana Narlapuram  writes:
> As a cloud service, Azure Database for PostgreSQL uses a gateway proxy to 
> route connections to a node hosting the actual server. Potentially there 
> could be multiple hops (for example client, optional proxy at the client like 
> pgbouncer for connection pooling, Azure gateway proxy, backend server) in 
> between the client, and the server. For various reasons (client firewall 
> rules, network issues etc.), the connection can be dropped before it is fully 
> authenticated at one of these hops, and it becomes extremely difficult to say 
> where and why the connection is dropped.
> The proposal is to tweak the connectivity wire protocol, and add a connection 
> id (GUID) filed in the startup message. We can trace the connection using 
> this GUID and investigate further on where the connection failed.
> Client adds a connection id in the startup message and send it to the server 
> it is trying to connect to. Proxy logs the connection id information in its 
> logs, and passes it to the server. Server logs the connection Id in the 
> server log, and set it in the GUC variable (ConnectionId).

> When an attempt to connection to the server fails, the connection failed 
> message must include the connection id in the message. This Id can be used to 
> trace the connection end to end.
> Customers can provide this Id to the support team to investigate the 
> connectivity issues to the server, along with the server information.

This seems like a lot of added mechanism for not very much gain.
In particular, it wouldn't help at all unless the client side were
also on board with generating a connection UUID and making it visible
to the end user, and then you'd have to get proxy authors on board,
etc etc, so you have to sell the idea to a lot more people than just the
server hackers.  Can you give a concrete example where this would have
helped above and beyond knowing, eg, the source and time of the connection
attempt?

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Adding connection id in the startup message

2017-06-15 Thread Stephen Frost 
* Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote:
> On 6/15/17 03:11, Satyanarayana Narlapuram wrote:
> > Client adds a connection id in the startup message and send it to the
> > server it is trying to connect to. Proxy logs the connection id
> > information in its logs, and passes it to the server. Server logs the
> > connection Id in the server log, and set it in the GUC variable
> > (ConnectionId).
> 
> If you're using pgbouncer or similar, then it might reuse an already
> existing connection, so your connection ID wouldn't be passed on.

This tends to be what people (as pgbouncer does) use application_name
for.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Adding connection id in the startup message

2017-06-15 Thread Peter Eisentraut 
On 6/15/17 03:11, Satyanarayana Narlapuram wrote:
> Client adds a connection id in the startup message and send it to the
> server it is trying to connect to. Proxy logs the connection id
> information in its logs, and passes it to the server. Server logs the
> connection Id in the server log, and set it in the GUC variable
> (ConnectionId).

If you're using pgbouncer or similar, then it might reuse an already
existing connection, so your connection ID wouldn't be passed on.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers