Postgres listens on random port

[Kal]

Hi

I have a scenario in which postgres listens on a random port different from
the one which was specified in pg_ctl start command.

This occurs in windows. When postgres is started on port 5460, the logs
denote that postgres is started on 5460. But the database actually listens
on some random port like 20011

When the same postgres is started as a service,it listens on the intended
port.

I have tried with both normal and admin users.

Any suggestions on this behaviour will be helpful.


Regards
Kal

Re: Postgres listens on random port

[Saul Perdomo]

" But the database actually listens on some random port like 20011"

How exactly did you determine this?

(If you used, say, netstat, can you share its output with us?)

On Tue, Nov 5, 2024 at 8:46 AM Kal  wrote:

> Hi
>
> I have a scenario in which postgres listens on a random port different
> from the one which was specified in pg_ctl start command.
>
> This occurs in windows. When postgres is started on port 5460, the logs
> denote that postgres is started on 5460. But the database actually listens
> on some random port like 20011
>
> When the same postgres is started as a service,it listens on the intended
> port.
>
> I have tried with both normal and admin users.
>
> Any suggestions on this behaviour will be helpful.
>
>
> Regards
> Kal
>

Re: Postgres listens on random port

[Ron Johnson]

On Tue, Nov 5, 2024 at 9:22 AM Kal  wrote:

>
> On Tue, 5 Nov 2024 at 7:42 PM, Greg Sabino Mullane 
> wrote:
>
>> Start by seeing where the port is actually being set by running this:
>>
>> select setting, source, sourcefile, sourceline from pg_settings where
>> name = 'port';
>> Cheers,
>> Greg
>>
>> Hi Greg,
>
> The query output states the source as command line. Event the port value
> output from show port is same as given in pg_ctl command.
>
> But the database is listening on some other random port.
>

*Client* applications use a random port on *their* host:  In this case,
10.176.242.216 is the client, and 10.109.165.4, and 10.176.252.201 are the
DB servers:

$ netstat -an | grep :5432
tcp0  0 10.176.242.216:3708610.109.165.4:5432
ESTABLISHED
tcp0  0 10.176.242.216:4325610.176.252.201:5432
TIME_WAIT
tcp0  0 10.176.242.216:3617210.143.170.55:5432
 TIME_WAIT
tcp0  0 10.176.242.216:3614210.143.170.55:5432
 TIME_WAIT
tcp0  0 10.176.242.216:3615610.143.170.55:5432
 TIME_WAIT
tcp0  0 10.176.242.216:3617410.143.170.55:5432
 TIME_WAIT
tcp0  0 10.176.242.216:3899810.109.165.4:5432
TIME_WAIT
tcp0  0 10.176.242.216:3618610.143.170.55:5432
 TIME_WAIT
tcp0  0 10.176.242.216:3618810.143.170.55:5432
 ESTABLISHED
tcp   19  0 10.176.242.216:3619610.143.170.55:5432
 ESTABLISHED
tcp   19  0 10.176.242.216:3620810.143.170.55:5432
 ESTABLISHED
tcp0  0 10.176.242.216:3621010.143.170.55:5432
 ESTABLISHED

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 crustacean!

Re: Postgres 12.21?

[David G. Johnston]

On Tue, Nov 5, 2024 at 8:21 AM Mark Hill  wrote:

> Will there be a minor version update to Postgres 12 this Thursday,
> 11/7/24, going to version 12.21?
>

Yes.

https://www.postgresql.org/support/versioning/

 David J.

Re: Postgres listens on random port

[Adrian Klaver]

On 11/5/24 06:22, Kal wrote:



On Tue, 5 Nov 2024 at 7:42 PM, Greg Sabino Mullane > wrote:


Start by seeing where the port is actually being set by running this:

select setting, source, sourcefile, sourceline from pg_settings
where name = 'port';
Cheers,
Greg

Hi Greg,

The query output states the source as command line. Event the port value 
output from show port is same as given in pg_ctl command.


How did you connect to the database to run the query if the port is random?

In other words what client and connection settings did you use to 
connect to the server and run the query?




But the database is listening on some other random port.


Regards
Kal


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Postgres listens on random port

[Greg Sabino Mullane]

Start by seeing where the port is actually being set by running this:

select setting, source, sourcefile, sourceline from pg_settings where name
= 'port';
Cheers,
Greg

Re: Postgres listens on random port

[Kal]

On Tue, 5 Nov 2024 at 7:42 PM, Greg Sabino Mullane 
wrote:

> Start by seeing where the port is actually being set by running this:
>
> select setting, source, sourcefile, sourceline from pg_settings where name
> = 'port';
> Cheers,
> Greg
>
> Hi Greg,

The query output states the source as command line. Event the port value
output from show port is same as given in pg_ctl command.

But the database is listening on some other random port.


Regards
Kal

Re: Postgres listens on random port

[Kal]

Hi

The netstat command did not reveal any entries on the intended port (say
5460). The 5460 port was vacant, no processes were using it.
 I had to crawl through the postgres processes on task manager to find the
listening port.


Could this be due interference from other services? But the port 5460
remains unused.


Regards,
Kal

On Tue, 5 Nov 2024 at 7:57 PM, Saul Perdomo  wrote:

> " But the database actually listens on some random port like 20011"
>
> How exactly did you determine this?
>
> (If you used, say, netstat, can you share its output with us?)
>
> On Tue, Nov 5, 2024 at 8:46 AM Kal  wrote:
>
>> Hi
>>
>> I have a scenario in which postgres listens on a random port different
>> from the one which was specified in pg_ctl start command.
>>
>> This occurs in windows. When postgres is started on port 5460, the logs
>> denote that postgres is started on 5460. But the database actually listens
>> on some random port like 20011
>>
>> When the same postgres is started as a service,it listens on the intended
>> port.
>>
>> I have tried with both normal and admin users.
>>
>> Any suggestions on this behaviour will be helpful.
>>
>>
>> Regards
>> Kal
>>
>

Re: Postgres listens on random port

[Erik Wienhold]

On 2024-11-05 15:22 +0100, Kal wrote:
> On Tue, 5 Nov 2024 at 7:42 PM, Greg Sabino Mullane 
> wrote:
> 
> > Start by seeing where the port is actually being set by running this:
> >
> > select setting, source, sourcefile, sourceline from pg_settings where name
> > = 'port';
> > Cheers,
> > Greg
> >
> > Hi Greg,
> 
> The query output states the source as command line. Event the port value
> output from show port is same as given in pg_ctl command.
> 
> But the database is listening on some other random port.

Please provide more details:

1) The complete pg_ctl command that you've executed.

2) Which Postgres version?

3) Which OS?

-- 
Erik

Postgres 12.21?

[Mark Hill]

Will there be a minor version update to Postgres 12 this Thursday, 11/7/24, 
going to version 12.21?

Thanks, Mark

Re: Postgres listens on random port

[Saul Perdomo]

Agreed - it sounds like OP found a *client *process and mistook it for a
server process, which is why I wanted to know where exactly he saw this.

On Tue, Nov 5, 2024 at 9:41 AM Ron Johnson  wrote:

> On Tue, Nov 5, 2024 at 9:22 AM Kal  wrote:
>
>>
>> On Tue, 5 Nov 2024 at 7:42 PM, Greg Sabino Mullane 
>> wrote:
>>
>>> Start by seeing where the port is actually being set by running this:
>>>
>>> select setting, source, sourcefile, sourceline from pg_settings where
>>> name = 'port';
>>> Cheers,
>>> Greg
>>>
>>> Hi Greg,
>>
>> The query output states the source as command line. Event the port value
>> output from show port is same as given in pg_ctl command.
>>
>> But the database is listening on some other random port.
>>
>
> *Client* applications use a random port on *their* host:  In this case,
> 10.176.242.216 is the client, and 10.109.165.4, and 10.176.252.201 are
> the DB servers:
>
> $ netstat -an | grep :5432
> tcp0  0 10.176.242.216:3708610.109.165.4:5432
> ESTABLISHED
> tcp0  0 10.176.242.216:4325610.176.252.201:5432
> TIME_WAIT
> tcp0  0 10.176.242.216:3617210.143.170.55:5432
>  TIME_WAIT
> tcp0  0 10.176.242.216:3614210.143.170.55:5432
>  TIME_WAIT
> tcp0  0 10.176.242.216:3615610.143.170.55:5432
>  TIME_WAIT
> tcp0  0 10.176.242.216:3617410.143.170.55:5432
>  TIME_WAIT
> tcp0  0 10.176.242.216:3899810.109.165.4:5432
> TIME_WAIT
> tcp0  0 10.176.242.216:3618610.143.170.55:5432
>  TIME_WAIT
> tcp0  0 10.176.242.216:3618810.143.170.55:5432
>  ESTABLISHED
> tcp   19  0 10.176.242.216:3619610.143.170.55:5432
>  ESTABLISHED
> tcp   19  0 10.176.242.216:3620810.143.170.55:5432
>  ESTABLISHED
> tcp0  0 10.176.242.216:3621010.143.170.55:5432
>  ESTABLISHED
>
> --
> Death to , and butter sauce.
> Don't boil me, I'm still alive.
>  crustacean!
>

Switching to NOINHERIT user triggers #XX000 error

[Dominique Devienne]

Hi. Still working on porting our stuff to v16+, with the ROLE changes.

I'm facing a new issue. The same code and unit test works in v14,
when the LOGIN user running the test is INHERIT or NOINHERIT.

On v16 and v17 though, it works with INHERIT, but with NOINHERIT, I get:

GRANT "...:USER" TO "ddevienne" #XX000: ERROR:  no possible grantors

Any clue to what this means, so I can start looking in the right place?

In all 3 (or 6) cases, the code is the same, the client-side is
running libpq 17,
it's only the server that changes. The LOGIN user is the same, with the same
pg_roles attributes, with again the variable being INHERIT or not. --DD

Re: Switching to NOINHERIT user triggers #XX000 error

[Dominique Devienne]

On Tue, Nov 5, 2024 at 6:42 PM Tom Lane  wrote:
> Dominique Devienne  writes:
> > On v16 and v17 though, it works with INHERIT, but with NOINHERIT, I get:
> > GRANT "...:USER" TO "ddevienne" #XX000: ERROR:  no possible grantors
> > Any clue to what this means, so I can start looking in the right place?
>
> \errverbose in psql would help you locate what part of the code
> is throwing that.  (Or if triggering it from psql is not convenient,
> set log_error_verbosity = verbose and find the info in the postmaster
> log.)
>
> This is probably a bug, at least to the extent that we don't like
> XX000 errors to be easily reachable, so please let us know what
> you find out.

Hi Tom.

Finding where the error is thrown is easy, it's when I'm doing that grant.
But the code creates two larguish schemas before that and several ROLEs,
so it's not easy to share.

I trapped the error in the code, and here's the details

ERROR:  XX000: no possible grantors
LOCATION:  check_role_grantor, user.c:2229

I used PQresultVerboseErrorMessage().
Tell me if you need some other error field
This is against a 17.0 server, for the line number above.

If I shared (privately) a libpq client-side trace with you Tom, would
that help you?

This particular test, I could redesign to work-around this issue probably.
But this is not the only tests that fails with a NOINHERIT user on v16+,
and I haven't tracked down all the reasons yet...

PS: I can't easily get server-side logs

Re: Switching to NOINHERIT user triggers #XX000 error

[Tom Lane]

Dominique Devienne  writes:
> On Tue, Nov 5, 2024 at 6:42 PM Tom Lane  wrote:
>> This is probably a bug, at least to the extent that we don't like
>> XX000 errors to be easily reachable, so please let us know what
>> you find out.

> Finding where the error is thrown is easy, it's when I'm doing that grant.
> But the code creates two larguish schemas before that and several ROLEs,
> so it's not easy to share.

Really, we need a self-contained example case to do anything
about this.  Maybe you could anonymize the object names in
your test?

regards, tom lane

Re: Switching to NOINHERIT user triggers #XX000 error

[Tom Lane]

Dominique Devienne  writes:
> On v16 and v17 though, it works with INHERIT, but with NOINHERIT, I get:
> GRANT "...:USER" TO "ddevienne" #XX000: ERROR:  no possible grantors
> Any clue to what this means, so I can start looking in the right place?

\errverbose in psql would help you locate what part of the code
is throwing that.  (Or if triggering it from psql is not convenient,
set log_error_verbosity = verbose and find the info in the postmaster
log.)

This is probably a bug, at least to the extent that we don't like
XX000 errors to be easily reachable, so please let us know what
you find out.

regards, tom lane

Re: Switching to NOINHERIT user triggers #XX000 error

[Dominique Devienne]

On Tue, Nov 5, 2024 at 7:25 PM Tom Lane  wrote:
> Dominique Devienne  writes:
> > On Tue, Nov 5, 2024 at 6:42 PM Tom Lane  wrote:
> >> This is probably a bug, at least to the extent that we don't like
> >> XX000 errors to be easily reachable, so please let us know what
> >> you find out.
>
> > Finding where the error is thrown is easy, it's when I'm doing that grant.
> > But the code creates two larguish schemas before that and several ROLEs,
> > so it's not easy to share.
>
> Really, we need a self-contained example case to do anything
> about this.  Maybe you could anonymize the object names in
> your test?

I understand, thanks. I might be able to do that. We'll see. --DD

Re: adsrc

[Ron Johnson]

On Tue, Nov 5, 2024 at 4:45 PM Matt Zagrabelny  wrote:

> Greetings Pg folk,
>
> I've got a legacy app connecting to a legacy Pg database (9.4).
>
> I'd like to upgrade the database to Pg 15, but when I connect and perform
> some tasks in the app, I get:
>
> ERROR:  column d.adsrc does not exist at character 331
>
> Some searching online yields that adsrc was removed in Pg 12.
>

1. What is the application?

2. An upgraded version of the application (or at least the db driver)
probably won't reference it.

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: adsrc

[David G. Johnston]

On Tue, Nov 5, 2024 at 2:45 PM Matt Zagrabelny  wrote:

> ERROR:  column d.adsrc does not exist at character 331
>
> Some searching online yields that adsrc was removed in Pg 12.
>
> Is there a way to recreate it?
>
>
That is a base catalog table.  Its removal means the data it stored is no
longer being stored.  Even if you somehow got the column reference error to
go away whatever the application is doing with that is going to be
surprised to be handed an empty string.

You could always checkout the source for v15, un-revert the commit removing
that field, compile, and see what happens.  But if you want to use a
community build the application needs to be changed along with upgrades to
the database.

David J.

Re: adsrc

[Matt Zagrabelny]

On Tue, Nov 5, 2024 at 4:44 PM Ron Johnson  wrote:

> On Tue, Nov 5, 2024 at 4:45 PM Matt Zagrabelny  wrote:
>
>> Greetings Pg folk,
>>
>> I've got a legacy app connecting to a legacy Pg database (9.4).
>>
>> I'd like to upgrade the database to Pg 15, but when I connect and perform
>> some tasks in the app, I get:
>>
>> ERROR:  column d.adsrc does not exist at character 331
>>
>> Some searching online yields that adsrc was removed in Pg 12.
>>
>
> 1. What is the application?
>

Netdot - an IPAM from the 2000's.


>
> 2. An upgraded version of the application (or at least the db driver)
> probably won't reference it.
>

Yes. Looks like the DBD::Pg module is what needs the upgrade.

-m

adsrc

[Matt Zagrabelny]

Greetings Pg folk,

I've got a legacy app connecting to a legacy Pg database (9.4).

I'd like to upgrade the database to Pg 15, but when I connect and perform
some tasks in the app, I get:

ERROR:  column d.adsrc does not exist at character 331

Some searching online yields that adsrc was removed in Pg 12.

Is there a way to recreate it?

Thanks for any help or answers.

Cheers,

-m

Re: Why not do distinct before SetOp

[Tom Lane]

David Rowley  writes:
> On Tue, 5 Nov 2024 at 04:18, Tom Lane  wrote:
>> A different idea that occurred to me while looking at this is:
>> why have we got all this machinery to add and check a flag
>> column, rather than arranging things so that the two input
>> relations are "outer" and "inner" children of the SetOp?

> I've no idea why it's not like that. The current design is quite
> strange and feels dated. It might be worth making that change as even
> if we gave joins better support for IS NOT DISTINCT FROM and made
> INTERSECT use INNER JOIN instead and EXCEPT use anti join, we'd still
> need nodeSetOp.c for INTERSECT ALL and EXCEPT ALL.

Yeah.  We'd still need it, and besides which it seems like a fairly
small project, unlike the other thing which could take multiple
years to get to an acceptable state.

Of course, I might be overestimating the performance benefit we'd get.
But I'm tempted to give it a try.

regards, tom lane

Re: Why not do distinct before SetOp

[David Rowley]

On Wed, 6 Nov 2024 at 12:09, Tom Lane  wrote:
> Of course, I might be overestimating the performance benefit we'd get.
> But I'm tempted to give it a try.

I'm glad. I'm curious to see if you're right about the projection
overhead of the flags. If you're right, it seems like a not too
difficult optimisation to get in.

I like the idea as it also gets rid of the flag cruft from
prepunion.c, which saves having to add flags conditionally if
INTERCEPT/EXCEPT were ever made to use joins.

David

Re: Why not do distinct before SetOp

[David Rowley]

On Tue, 5 Nov 2024 at 04:18, Tom Lane  wrote:
> A different idea that occurred to me while looking at this is:
> why have we got all this machinery to add and check a flag
> column, rather than arranging things so that the two input
> relations are "outer" and "inner" children of the SetOp?

I've no idea why it's not like that. The current design is quite
strange and feels dated. It might be worth making that change as even
if we gave joins better support for IS NOT DISTINCT FROM and made
INTERSECT use INNER JOIN instead and EXCEPT use anti join, we'd still
need nodeSetOp.c for INTERSECT ALL and EXCEPT ALL.

> It's possible some of the performance difference reported here
> is due to having to pass more tuples through the SubqueryScan
> node (with its projection to add the flag) and Append node,
> but we could remove those steps entirely.

Seems plausible.

> > If we did want to improve this area, I think the first thing we'd want
> > to do is use standard join types rather than HashSetOp Intersect to
> > implement INTERSECT (without ALL).  To do that efficiently, we'd need
> > to do a bit more work on the standard join types to have them
> > efficiently support IS NOT DISTINCT FROM clauses as the join keys.
>
> Maybe.  It'd be a big project, but we do get complaints every so
> often about IS NOT DISTINCT FROM predicates not being efficient,
> so the benefits would be wider than just INTERSECT.

Yeah, I agree. I think that's step 1 towards making INTERSECT (without
ALL) and EXCEPT (without ALL) better and it would probably make a few
other people happy who use IS NOT DISTINCT FROM in their join
conditions.

David

Re: adsrc

[Christoph Moench-Tegeder]

## Matt Zagrabelny (mzagr...@d.umn.edu):

> I'd like to upgrade the database to Pg 15, but when I connect and perform
> some tasks in the app, I get:
> 
> ERROR:  column d.adsrc does not exist at character 331

That pg_attrdef.adsrc was already marked as "historical, and is best
not used" in 9.4: https://www.postgresql.org/docs/9.4/catalog-pg-attrdef.html
(In fact, that notice was added in 8.0).
The official way to get the human-readable representation of the
column's default value is "pg_get_expr(d.adbin, d.adrelid)" (use that
instead of d.adsrc) - that way the fix is rather straight-forward.
Documentation on pg_get_expr() is in this table:
https://www.postgresql.org/docs/15/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE

Regards,
Christoph

-- 
Spare Space