Question 224865 in PostgreSQL Question posted in
The official documentation can be found here.

How can a PostgreSQL client determine what its own TCP port number is?

If running e.g. psql as a client, one can use a query like

SELECT * FROM pg_stat_activity WHERE application_name = 'psql';

to determine the local port by inspecting the client_port column in the returned result.

How can an arbitrary client program, with access to the C-style API etc., determine the local port number it is using to connect to the server’s port (5432 by default)?

This information has to be available somewhere locally since apart from anything else it’s known by the OS (Linux in my case). I’d have expected there to be some relatively-simple way to go use the low-level connection handle to get a sockaddr, hence to get the local port number in sin_port… however I’m struggling with the practicalities and would appreciate any hints.

What I’m actually trying to do is monitor the client connection state without touching the Postgres API, so that I can get advance warning if the connection has been broken by e.g. a timeout due to a laptop going to sleep state. If I knew the local port number, then I could use something like the identd (port 113) facility on the server, since this is stateless it should be unaffected by connection interruption.

This was prompted by somebody elsewhere complaining that long-running connections were interrupted by his ISP changing his domestic IP address. Leaving aside all considerations of the wisdom of running something like a Postgres connection over the Internet, I considered it to be a problem worth investigating since I’ve long been unhappy about the practice of some RAD environments (e.g. Delphi and Lazarus) keeping connections open for an indefinite period.

Updated: I was hoping to be able to keep this inside the program, and definitely without invoking a sequence of external utilities; however while investigating how best to phrase this I think I’ve spotted the reason my earlier attempts failed. I’m using FPC/Lazarus which is an open-source clone of Delphi (which while decidedly "untrendy" remain competent development environments, what in the 1990s we’d have called "a 4GL"). It turns out that their definition of the (equivalent of the) pg_conn struct is grossly out of date, I need to hack a fixed equivalent together so that I can get at the laddr field and/or investigate interfacing with libpq directly.

Thanks for the responses so far (and thanks in particular for not just telling me that I’m trying to do something pointless or undesirable), but please leave this one with me, I’ll report back once I know more (but it won’t be today).

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The issue which spurred the question is that an application program written with the assistance of a RAD such as Delphi or Lazarus is likely to keep a PostgreSQL connection open for an extended period. If something happens which interrupts the session, e.g. the server-side process times out or the client's IP address is changed by an ISP, the application might still believe it has a good connection but will run into trouble when an attempt is made to commit a transaction. This could potentially inconvenience a user by forcing him to reenter work, the situation is particularly acute when DB-aware grid components are used.

    I believe that the HA community might have a related problem, in that a failover will cause the state of any current transaction to be lost.

    While not foolproof, I believe that being able to warn the user of an application program that the connection has been interrupted before he attempts to enter work would be valuable.

    Information describing the current connection is held in the pg_conn struct. However this has to be regarded as opaque and private to libpq, in some versions there are laddr/raddr fields which are standard sockaddr struct while in others they are a private format (and are stored at different offsets). As such, any attempt by application-level tools to publish an equivalent to the pg_conn struct (e.g. as a Pascal-style record) cannot be trusted.

    Fortunately, the public libpq functions provide enough functionality to be able to derive the client-side port number. As a Pascal fragment:

    
var
  handle2: pointer;
  sockaddr: TSockAddr;
  sz: integer;

begin
  if not Connected then
    exit(-1);

  handle2 := GetHandle;
  if handle2 = nil then
    exit(-1);

  handle2 := GetTransactionHandle(TSQLHandle(handle2));
  if handle2 = nil then
    exit(-1);

  sz := SizeOf(TSockAddr);
  fillChar(sockaddr, sz, 0);
  if fpgetsockname(PQSocket(handle2), @sockaddr, @sz) < 0 then
    exit(-1);
  exit(ntohs(sockaddr.sin_port))
end;

    The returned value is the client-side port number. Knowing the server-side port number (typically 5432), it is possible to use e.g. the identd (port 113) protocol to ask the server whether the connection is still healthy, and to at least warn the user of imminent problems if it is not.


  2. 0

    You could use the socket statistics utility to filter and display established TCP connections where the source port matches the PostgreSQL port.

    ss -o state established '( sport = :postgresql )'

    OR

    ss -t -a 'sport = :5432' | awk '$1 == "ESTAB" {print}'

    To check all the instances on that particular port.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search