Hash partitioning, what function is used to compute the hash?

[Dennis Ryan]

Regarding hash partitioning, what is the function/algorithm that is used to 
compute the hash for the partition key?  I need to write a query like

“SELECT unknown_partition_hash_function(id) AS hash_value, COUNT(id) AS 
number_of_records
FROM existing_table
GROUP BY 1”


Sent from Mail for Windows 10

Re: Hash partitioning, what function is used to compute the hash?

[Michael Lewis]

The documentation shows it is just a modulus operation. If you partition on
object_key % 3 then you will create three partitions for remainder values
0-2 for instance.

Afaik, hash partition doesn't have real world expected use cases just yet.
List or range is probably what you want to use.

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

[Tom Lane]

Matthias Apitz  writes:
> I launched a shell script to get some more information in this case:

ok

> The process with the PID in question is 'postmaster':

Yeah, this should actually be a newly-forked postmaster child process,
but it won't have done anything yet to change its ps-visible command
line.

> postmaste 14622 postgres9u IPv4 131903453   0t0   TCP 
> srap32dxr1.dev.oclc.org:5432->srap32dxr1.dev.oclc.org:44836 (ESTABLISHED)

OK, so it's gotten a TCP not Unix-socket connection; that's already
going to narrow things a little bit.

> Interestingly, the other side of this connection of the port 5432 is not
> visible, i.e. a 'lsof -P | grep 5432' does not show it in this moment.

Permissions problem maybe?  I'm not sure that lsof will tell you much
about non-postgres-owned processes, unless you run it as root.

regards, tom lane

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

[Matthias Apitz]

El día Montag, Mai 11, 2020 a las 09:50:12 -0400, Tom Lane escribió:

> 
> > postmaste 14622 postgres9u IPv4 131903453   0t0   TCP 
> > srap32dxr1.dev.oclc.org:5432->srap32dxr1.dev.oclc.org:44836 (ESTABLISHED)
> 
> OK, so it's gotten a TCP not Unix-socket connection; that's already
> going to narrow things a little bit.
> 
> > Interestingly, the other side of this connection of the port 5432 is not
> > visible, i.e. a 'lsof -P | grep 5432' does not show it in this moment.
> 
> Permissions problem maybe?  I'm not sure that lsof will tell you much
> about non-postgres-owned processes, unless you run it as root.

Ah, yes. As this is just a dev host only, I set 'chmod 4755 /usr/bin/lsof'
and now I see, the connecting client is one of our Java tools and I can
look at the problem from this side.

matthias


-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: Hash partitioning, what function is used to compute the hash?

[Laurenz Albe]

On Mon, 2020-05-11 at 04:33 +, Dennis Ryan wrote:
> Regarding hash partitioning, what is the function/algorithm that is used to 
> compute the hash for the partition key?  I need to write a query like
> 
> “SELECT unknown_partition_hash_function(id) AS hash_value, COUNT(id) AS 
> number_of_records
> FROM existing_table
> GROUP BY 1”

To find the function that PostgreSQL uses to hash a data type,
try something like

SELECT amp.amproc,
   amp.amproclefttype::regtype
FROM pg_amproc AS amp
   JOIN pg_opfamily AS opf ON amp.amprocfamily = opf.oid
   JOIN pg_am ON opf.opfmethod = pg_am.oid
WHERE pg_am.amname = 'hash'
  AND amp.amprocnum = 1;

Yours,
Laurenz Albe
-- 
+43-670-6056265
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com

Enforcing uniqueness on [real estate/postal] addresses

[Peter Devoy]

Hi list

I need to store addresses for properties (as in real estate) so in my
naivety I created a unique constraint like this:

ALTER TABLE properties
ADD CONSTRAINT is_unique_address
UNIQUE (
description, --e.g. Land north of Foo Cottage
address_identifier_general,
street,
postcode
);

Of course, if any of the fields are NULL (which they often are) I end
up with duplicates.

One solution may be to add NOT NULL constraints and use empty strings
instead of NULL values but, until asking around today, I thought this was
generally considered bad practice.

Please can anyone recommend a way of approaching this? Perhaps empty strings
are pragmatic in this situation?

Kind regards


Peter

Re: Enforcing uniqueness on [real estate/postal] addresses

[Philip Semanchuk]

> On May 11, 2020, at 12:55 PM, Peter Devoy  wrote:
> 
> Hi list
> 
> I need to store addresses for properties (as in real estate) so in my
> naivety I created a unique constraint like this:
> 
> ALTER TABLE properties
>ADD CONSTRAINT is_unique_address
>UNIQUE (
>description, --e.g. Land north of Foo Cottage
>address_identifier_general,
>street,
>postcode
>);
> 
> Of course, if any of the fields are NULL (which they often are) I end
> up with duplicates.
> 
> One solution may be to add NOT NULL constraints and use empty strings
> instead of NULL values but, until asking around today, I thought this was
> generally considered bad practice.
> 
> Please can anyone recommend a way of approaching this? Perhaps empty strings
> are pragmatic in this situation?

Hi Peter,
I wouldn’t use empty strings in place of NULL. It’s possible to define a 
partial unique index that has more or less the same effect as a constraint. 
Have you looked into them? 

Cheers
Philip

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

[Matthias Apitz]

El día Montag, Mai 11, 2020 a las 06:24:52 +0200, Matthias Apitz escribió:

> Ah, yes. As this is just a dev host only, I set 'chmod 4755 /usr/bin/lsof'
> and now I see, the connecting client is one of our Java tools and I can
> look at the problem from this side.
> 

With the knowledge which Java process it is causing, I'm able to simple
reproduce the message and with knowing the 4-tuple of the TCP connection to
identify the exchange in a capture with TCPDUMP sniffing on localhost
port 5432.

Below is the exchange. The visible strings are: 'sisis' (the PG user),
'sisis123' (its password in PG, a dummy password used for testing) and
'srap32dxr1' the hostname. The server is terminating the connection with
a F-pkg:

19:54:02.940205 IP 10.23.33.19.48438 > 10.23.33.19.5432: Flags [S], seq 
3950072774, win 43690, options [mss 65495,sackOK,TS val 3334863612 ecr 
0,nop,wscale 7], length 0
0x:  4500 003c e98b 4000 4006 fadc 0a17 2113  E..<..@.@.!.
0x0010:  0a17 2113 bd36 1538 eb71 53c6    ..!..6.8.qS.
0x0020:  a002  5682  0204 ffd7 0402 080a  V...
0x0030:  c6c5 fafc   0103 0307
19:54:02.940217 IP 10.23.33.19.5432 > 10.23.33.19.48438: Flags [S.], seq 
749639996, ack 3950072775, win 43690, options [mss 65495,sackOK,TS val 
3334863612 ecr 3334863612,nop,wscale 7], length 0
0x:  4500 003c  4000 4006 e468 0a17 2113  E..<..@.@..h..!.
0x0010:  0a17 2113 1538 bd36 2cae 993c eb71 53c7  ..!..8.6,..<.qS.
0x0020:  a012  5682  0204 ffd7 0402 080a  V...
0x0030:  c6c5 fafc c6c5 fafc 0103 0307
19:54:02.940226 IP 10.23.33.19.48438 > 10.23.33.19.5432: Flags [.], ack 1, win 
342, options [nop,nop,TS val 3334863612 ecr 3334863612], length 0
0x:  4500 0034 e98c 4000 4006 fae3 0a17 2113  E..4..@.@.!.
0x0010:  0a17 2113 bd36 1538 eb71 53c7 2cae 993d  ..!..6.8.qS.,..=
0x0020:  8010 0156 567a  0101 080a c6c5 fafc  ...VVz..
0x0030:  c6c5 fafc
19:54:02.948877 IP 10.23.33.19.48438 > 10.23.33.19.5432: Flags [P.], seq 1:513, 
ack 1, win 342, options [nop,nop,TS val 3334863621 ecr 3334863612], length 512
0x:  4500 0234 e98d 4000 4006 f8e2 0a17 2113  E..4..@.@.!.
0x0010:  0a17 2113 bd36 1538 eb71 53c7 2cae 993d  ..!..6.8.qS.,..=
0x0020:  8018 0156 587a  0101 080a c6c5 fb05  ...VXz..
0x0030:  c6c5 fafc 0200 0200   7372 6170  srap
0x0040:  3332 6478 7231       32dxr1..
0x0050:       0a73 6973 6973  ...sisis
0x0060:           
0x0070:      0005 7369 7369 7331  ..sisis1
0x0080:  3233         23..
0x0090:      0800     
0x00a0:           
0x00b0:     0001 0200 0604 0801   
0x00c0:   0002        
0x00d0:           
0x00e0:           
0x00f0:   ..
19:54:02.948886 IP 10.23.33.19.5432 > 10.23.33.19.48438: Flags [.], ack 513, 
win 350, options [nop,nop,TS val 3334863621 ecr 3334863621], length 0
0x:  4500 0034 6004 4000 4006 846c 0a17 2113  E..4`.@.@..l..!.
0x0010:  0a17 2113 1538 bd36 2cae 993d eb71 55c7  ..!..8.6,..=.qU.
0x0020:  8010 015e 567a  0101 080a c6c5 fb05  ...^Vz..
0x0030:  c6c5 fb05
19:54:02.948970 IP 10.23.33.19.48438 > 10.23.33.19.5432: Flags [P.], seq 
513:612, ack 1, win 342, options [nop,nop,TS val 3334863621 ecr 3334863621], 
length 99
0x:  4500 0097 e98e 4000 4006 fa7e 0a17 2113  E.@.@..~..!.
0x0010:  0a17 2113 bd36 1538 eb71 55c7 2cae 993d  ..!..6.8.qU.,..=
0x0020:  8018 0156 56dd  0101 080a c6c5 fb05  ...VV...
0x0030:  c6c5 fb05 0201 0063      ...c
0x0040:   0600   0800     
0x0050:           
0x0060:           
0x0070:  0135 3132  0003   e200 1801  .512
0x0080:  0c07 cdff 85ee ef65 7fff  d602 0800  ...e
0x0090:  0680 0648  00...H...
19:54:02.948974 IP 10.23.33.19.5432 > 10.23.33.19.48438: Flags [.], ack 612, 
win 350, options [nop,nop,TS val 3334863621 ecr 3334863621], length 0
0x:  4500 0034 6005 4000 4006 846b 0a

Re: Enforcing uniqueness on [real estate/postal] addresses

[Adrian Klaver]

On 5/11/20 9:55 AM, Peter Devoy wrote:

Hi list

I need to store addresses for properties (as in real estate) so in my
naivety I created a unique constraint like this:

ALTER TABLE properties
 ADD CONSTRAINT is_unique_address
 UNIQUE (
 description, --e.g. Land north of Foo Cottage
 address_identifier_general,
 street,
 postcode
 );

Of course, if any of the fields are NULL (which they often are) I end
up with duplicates.

One solution may be to add NOT NULL constraints and use empty strings
instead of NULL values but, until asking around today, I thought this was
generally considered bad practice.

Please can anyone recommend a way of approaching this? Perhaps empty strings
are pragmatic in this situation?


How about?:

create table properties (description varchar, address_identifier_general 
varchar, street varchar, postcode varchar);


CREATE UNIQUE INDEX is_unique_address ON properties 
((coalesce(description, '')),

(coalesce(address_identifier_general, '')),
(coalesce(street, '')),
(coalesce(postcode, ''))
);


insert into properties (description, street, postcode) values ('test', 
'anywhere', '1234');

INSERT 0 1

insert into properties (description, street, postcode) values ('test', 
'anywhere', '1234');

ERROR:  duplicate key value violates unique constraint "is_unique_address"
DETAIL:  Key (COALESCE(description, ''::character varying), 
COALESCE(address_identifier_general, ''::character varying), 
COALESCE(street, ''::character varying), COALESCE(postcode, 
''::character varying))=(test, , anywhere, 1234) already exists.




Kind regards


Peter





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

Re: Enforcing uniqueness on [real estate/postal] addresses

[David G. Johnston]

On Mon, May 11, 2020 at 9:56 AM Peter Devoy  wrote:

> I need to store addresses for properties (as in real estate) so in my
> naivety I created a unique constraint like this:
>
> ALTER TABLE properties
> ADD CONSTRAINT is_unique_address
> UNIQUE (
> description, --e.g. Land north of Foo Cottage
> address_identifier_general,
> street,
> postcode
> );
>
>

> Please can anyone recommend a way of approaching this? Perhaps empty
> strings
> are pragmatic in this situation?
>

Don't add a unique table constraint (aside from an artificial primary key).

Then, if you can define a problem where you feel having a unique table
constraint over the field is the correct solution - especially given the
fact that you can have missing data in the relevant fields - you should
post the problem and take suggestions on ways to solve it.

David J.

Re: Enforcing uniqueness on [real estate/postal] addresses

[Paul Jungwirth]

On 5/11/20 9:55 AM, Peter Devoy wrote:

Of course, if any of the fields are NULL (which they often are) I end
up with duplicates.

One solution may be to add NOT NULL constraints and use empty strings
instead of NULL values but, until asking around today, I thought this was
generally considered bad practice.


If you don't want to store empty strings (which I agree is a little 
yucky), you could replace NULLs with an empty string *only when checking 
for uniqueness*. To do this, first replace your unique constraint with a 
unique index, which gives you some additional features (e.g. indexing 
expressions, indexing only part of the table with a WHERE clause, 
building it concurrently, etc.). In this case we only care about 
indexing expressions.


So you can say:

CREATE UNIQUE INDEX is_unique_address ON properties (
  COALESCE(description, ''),
  COALESCE(address_identifier_general, ''),
  COALESCE(street, ''),
  COALESCE(postcode, ''));

Another approach, which I don't think is really a serious suggestion but 
is sort of interesting to think about: you could define an operator, say 
===, that does the same thing as `IS NOT DISTINCT FROM`. Then you could 
create an exclusion constraint using that operator on all four columns. 
I've never tried that before but it seems like it would work.


Maybe that's too much effort for something like this. I just think it's 
interesting because it feels like a use case for exclusion constraints 
that goes in the "opposite direction" of how they are usually used: 
instead of being less restrictive than =, it is more restrictive.


Regards,

--
Paul  ~{:-)
p...@illuminatedcomputing.com

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

[Tom Lane]

Matthias Apitz  writes:
> Below is the exchange. The visible strings are: 'sisis' (the PG user),
> 'sisis123' (its password in PG, a dummy password used for testing) and
> 'srap32dxr1' the hostname. The server is terminating the connection with
> a F-pkg:

[ squint... ]  That looks nothing like a Postgres-protocol exchange
to me.  If it weren't for the runs of zeroes, I'd wonder if the
connection had SSL encryption turned on.  Perhaps you captured the
wrong session?

regards, tom lane

Re: Enforcing uniqueness on [real estate/postal] addresses

[Peter Devoy]

Hello everyone

Thank you all for your suggestions.  I had neither heard of partial
unique indexes nor
considered using COALESCE so I will explore both of these as options
and update the
thread with how it goes.

>Then, if you can define a problem where you feel having a unique table 
>constraint over
>the field is the correct solution [...] you should post the problem and take 
>suggestions on
>ways to solve it.
I am not sure if I have such a problem :)  I tried to simplify my
example for the sake of
brevity but if I cannot get the above to work I will post the full problem.

Kind regards


Peter

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

[Matthias Apitz]

El día Montag, Mai 11, 2020 a las 02:41:29 -0400, Tom Lane escribió:

> Matthias Apitz  writes:
> > Below is the exchange. The visible strings are: 'sisis' (the PG user),
> > 'sisis123' (its password in PG, a dummy password used for testing) and
> > 'srap32dxr1' the hostname. The server is terminating the connection with
> > a F-pkg:
> 
> [ squint... ]  That looks nothing like a Postgres-protocol exchange
> to me.  If it weren't for the runs of zeroes, I'd wonder if the
> connection had SSL encryption turned on.  Perhaps you captured the
> wrong session?

I don't think so. It is exactly the problematic connection started by
the Java process against the port 5432; look the SYN pkg:

19:54:02.940205 IP 10.23.33.19.48438 > 10.23.33.19.5432: Flags [S], seq 
3950072774, win 43690,
options [mss 65495,sackOK,TS val 3334863612 ecr 0,nop,wscale 7], length 0

As you say, that this is not any Postgres-protocol exchange, I will
check the configuration of this part of our software. It can be some
kind of misconfiguration, some part is talking TCP/IP to the wrong server.

Thanks for the hint.

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Is there a significant difference in Memory settings between 9.5 and 12

[Tory M Blue]

Upgraded from 9.5 to 12 and 12 would not start with the current configured
Shared Buffers.

Same hardware, same config file.

Which tells me something has changed, are there new default settings in the
12 postgresql.conf file that are not being called out in my 9.5 config file
that could be the cause or?

9.5
shared_buffers 10GB

12
shared_buffers 5GB

12 will not start at 10GB, even though it's the same hardware, same config
file, same physical box, same everything, just version 12 vs 9.5

So this tells me that maybe i'm missing a new memory setting in 12 , that
is not being overwritten in my local config file (I run an include and my
own settings), this worked fine in 9.5 but I'm guessing there is something
considerably different between 9.5 and 12 (I'm just not seeing it). Anyone
with insight into what memory settings may have been added in 10/11/12 that
are significantly different than 9.5?

Thanks
Tory

CentOS 7
Postgresql 9.5.x
Postgresql 12.2

Re: Is there a significant difference in Memory settings between 9.5 and 12

[Tom Lane]

Tory M Blue  writes:
> 12 will not start at 10GB, even though it's the same hardware, same config
> file, same physical box, same everything, just version 12 vs 9.5

For me, using all-default settings (in particular, shared_buffers =
128MB), the shared memory block is about 141.6MB using 9.5 and 142.1MB
using 12.  So there's half a meg or so of additional data in v12, but
certainly not gigabytes worth.

Are you trying to start both postmasters concurrently?  Maybe you're
hitting some kernel limit on the total amount of shared memory in the
system.

regards, tom lane

Re: Is there a significant difference in Memory settings between 9.5 and 12

[Tory M Blue]

On Mon, May 11, 2020 at 1:36 PM Tom Lane  wrote:

> Tory M Blue  writes:
> > 12 will not start at 10GB, even though it's the same hardware, same
> config
> > file, same physical box, same everything, just version 12 vs 9.5
>
> For me, using all-default settings (in particular, shared_buffers =
> 128MB), the shared memory block is about 141.6MB using 9.5 and 142.1MB
> using 12.  So there's half a meg or so of additional data in v12, but
> certainly not gigabytes worth.
>
> Are you trying to start both postmasters concurrently?  Maybe you're
> hitting some kernel limit on the total amount of shared memory in the
> system.
>
> regards, tom lane
>

Hey Tom

Nope, just a single one that is why i'm flummoxed :) I've even rebooted,
but I can't start Postgres 12 with my current setting of 10GB, I can start
9.5 with 10GB configured.   I've tried, shutting down 9.5 and rebooting so
nothing is running and attempting to start 12 and nada, it won't unless I
drop the Shared Buffers down to 5GB (half)..  But these are dedicated
postgresql servers. And in fact my latest migrations, don't even have 9.5
binaries anymore and 12 will not start with my 9.5 configuration of 10GB
buffers. So something feels really different.

It's very possible that there are new defaults , new memory settings that
I'm not finding in the default postgresql 12 .conf file, and my include is
not overwriting it. But really I just can't fathom what that could be..
Buffers, work mem, effective cache, what would they have added?

I am going to pull the settings from postgres itself and compare 12 and 9.5
to see if there is something glaring.

Thanks! :)
Tory

Re: Is there a significant difference in Memory settings between 9.5 and 12

[Tory M Blue]

On Mon, May 11, 2020 at 1:36 PM Tom Lane  wrote:

> Tory M Blue  writes:
> > 12 will not start at 10GB, even though it's the same hardware, same
> config
> > file, same physical box, same everything, just version 12 vs 9.5
>
> For me, using all-default settings (in particular, shared_buffers =
> 128MB), the shared memory block is about 141.6MB using 9.5 and 142.1MB
> using 12.  So there's half a meg or so of additional data in v12, but
> certainly not gigabytes worth.
>
> Are you trying to start both postmasters concurrently?  Maybe you're
> hitting some kernel limit on the total amount of shared memory in the
> system.
>
> regards, tom lane
>

Okay the one difference I see in settings is this little gem in 12..

shared_memory_type mmap

Otherwise i'm not seeing a ton of other settings not common between them,.

This is the only major difference I'm seeing, as it's really not an option
in 9.5. Appears 9.5 was using

shared_memory_type (enum)

Specifies the shared memory implementation that the server should use for
the main shared memory region that holds PostgreSQL's shared buffers and
other shared data. Possible values are mmap (for anonymous shared memory
allocated using mmap), sysv (for System V shared memory allocated via shmget)
and windows (for Windows shared memory). Not all values are supported on
all platforms; the first supported option is the default for that platform.
The use of the sysv option, which is not the default on any platform, is
generally discouraged because it typically requires non-default kernel
settings to allow for large allocations (see Section 18.4.1
).

Re: Hash partitioning, what function is used to compute the hash?

[Alvaro Herrera]

On 2020-May-11, Michael Lewis wrote:

> Afaik, hash partition doesn't have real world expected use cases just yet.

I don't think I agree with this assertion.

While I understand that there might be things still to do in this area
(as everywhere else), it should certainly have its uses already.  If you
have a wish-list for hash partitioning to become usable for you, would
you please list the features you wish it'd have?

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Is there a significant difference in Memory settings between 9.5 and 12

[Adrian Klaver]

On 5/11/20 1:42 PM, Tory M Blue wrote:



On Mon, May 11, 2020 at 1:36 PM Tom Lane > wrote:


Tory M Blue mailto:tmb...@gmail.com>> writes:
 > 12 will not start at 10GB, even though it's the same hardware,
same config
 > file, same physical box, same everything, just version 12 vs 9.5

For me, using all-default settings (in particular, shared_buffers =
128MB), the shared memory block is about 141.6MB using 9.5 and 142.1MB
using 12.  So there's half a meg or so of additional data in v12, but
certainly not gigabytes worth.

Are you trying to start both postmasters concurrently?  Maybe you're
hitting some kernel limit on the total amount of shared memory in the
system.

                         regards, tom lane


Hey Tom

Nope, just a single one that is why i'm flummoxed :) I've even rebooted, 
but I can't start Postgres 12 with my current setting of 10GB, I can 


So what is the error output from console, Postgres log and/or system log?

start 9.5 with 10GB configured.   I've tried, shutting down 9.5 and 
rebooting so nothing is running and attempting to start 12 and nada, it 
won't unless I drop the Shared Buffers down to 5GB (half)..  But these 
are dedicated postgresql servers. And in fact my latest migrations, 
don't even have 9.5 binaries anymore and 12 will not start with my 9.5 
configuration of 10GB buffers. So something feels really different.


It's very possible that there are new defaults , new memory settings 
that I'm not finding in the default postgresql 12 .conf file, and my 
include is not overwriting it. But really I just can't fathom what that 
could be.. Buffers, work mem, effective cache, what would they have added?


I am going to pull the settings from postgres itself and compare 12 and 
9.5 to see if there is something glaring.


Thanks! :)
Tory



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

Re: Is there a significant difference in Memory settings between 9.5 and 12

[Tom Lane]

Tory M Blue  writes:
> Okay the one difference I see in settings is this little gem in 12..
> shared_memory_type mmap

Well, v12 is just exposing a switch for something that was hard-wired
before.  But now I wonder if your 9.5 installation could've been compiled
to force it to use SysV shmem instead of POSIX.  It would be pretty
unusual to have a system where the SysV shmem limits were higher than
the POSIX limits --- usually it's the other way 'round.  But this'd
explain why you're seeing a difference.

Does v12 start with the higher shared_buffers setting if you
set shared_memory_type = sysv?

regards, tom lane

Re: Upgrade Process Says "The database server was not shut down cleanly" but it was

[TalGloz]

Well I tried your steps, both Postgres 10 and 12 are in perfect running
conditions and were shut down using the "systemctl" command. When I execute: 

/usr/pgsql-12/bin/pg_upgrade --old-bindir=/usr/pgsql-10/bin/
--new-bindir=/usr/pgsql-12/bin/ --old-datadir=/var/lib/pgsql/10/data
--new-datadir=/var/lib/pgsql/12/data --old-options '-c
config_file=/var/lib/pgsql/10/data/postgresql.conf' --new-options '-c
config_file=/var/lib/pgsql/12/data/postgresql.conf' --check

I get:

Performing Consistency Checks
-
Checking cluster versions   ok
Checking database user is the install user  ok
Checking database connection settings ok
Checking for prepared transactions  ok
Checking for reg* data types in user tables   ok
Checking for contrib/isn with bigint-passing mismatch  ok
Checking for tables WITH OIDSok
Checking for invalid "sql_identifier" user columns ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions  ok

*Clusters are compatible*

But if I execute the same command right after that without the "--check"
flag I get:

Performing Consistency Checks
-
Checking cluster versions   ok

The target cluster was not shut down cleanly.
Failure, exiting


Both of the servers were still shut down. If I start form the beginning with
a fresh Prostgres 12 installation and execute the upgrade command right away
without the "--check" flag I get the problem described in my original post.

Best regards,
TalGlo



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Upgrade Process Says "The database server was not shut down cleanly" but it was

[Adrian Klaver]

On 5/11/20 2:45 PM, TalGloz wrote:

Well I tried your steps, both Postgres 10 and 12 are in perfect running
conditions and were shut down using the "systemctl" command. When I execute:

/usr/pgsql-12/bin/pg_upgrade --old-bindir=/usr/pgsql-10/bin/
--new-bindir=/usr/pgsql-12/bin/ --old-datadir=/var/lib/pgsql/10/data
--new-datadir=/var/lib/pgsql/12/data --old-options '-c
config_file=/var/lib/pgsql/10/data/postgresql.conf' --new-options '-c
config_file=/var/lib/pgsql/12/data/postgresql.conf' --check


The --*-options  are redundant as you have already pointed pg_upgrade at 
them with --*-datadir. Not sure it has anything to do with this, but you 
might want to take them out just to be sure.


Have you run:

ps ax| grep post

before and between steps to make sure there is not a Postgres instance 
running?




I get:

Performing Consistency Checks
-
Checking cluster versions   ok
Checking database user is the install user  ok
Checking database connection settings ok
Checking for prepared transactions  ok
Checking for reg* data types in user tables   ok
Checking for contrib/isn with bigint-passing mismatch  ok
Checking for tables WITH OIDSok
Checking for invalid "sql_identifier" user columns ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions  ok

*Clusters are compatible*

But if I execute the same command right after that without the "--check"
flag I get:

Performing Consistency Checks
-
Checking cluster versions   ok

The target cluster was not shut down cleanly.
Failure, exiting


Both of the servers were still shut down. If I start form the beginning with
a fresh Prostgres 12 installation and execute the upgrade command right away
without the "--check" flag I get the problem described in my original post.

Best regards,
TalGlo



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html





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

Firing an event trigger for ADD COLUMN?

[Michel Pelletier]

I'm writing a script that "reverses" ddl operations in an "up" script by
capturing event triggers and generating a "down" script for reverting.
I've got it working great for tables, indexes, etc, but it seems ADD COLUMN
doesn't sent an event, here's the code i'm using, pasted straight from the
documentation:

https://gist.github.com/michelp/c6daa1c123c4743be4f8b08fad5d5396

And here's the results I get, I get an event for dropping a column, but not
adding one:

postgres=# create table foo (bar int);
NOTICE:  CREATE TABLE created object: table public public.foo
CREATE TABLE
postgres=# alter table foo add column baz int;
NOTICE:  ALTER TABLE created object: table public public.foo
ALTER TABLE
postgres=# alter table foo drop column bar;
NOTICE:  ALTER TABLE dropped object: table column public.
public.foo.bar
NOTICE:  ALTER TABLE created object: table public public.foo
ALTER TABLE
postgres=#

Is this asymmetry a bug?  I realize these event trigger functions are
typically written in C, but I'd like to keep this idea contained to a
plpgsql function and cover as many reversible changes as I can.

Thank you,

-Michel

Re: Firing an event trigger for ADD COLUMN?

[Tanveer Munavar]

> Is this asymmetry a bug?  I realize these event trigger functions are
typically written in C, but I'd like to keep this idea contained to a
plpgsql function and cover as many reversible changes as I can.

This is the expected behavior since pg_event_trigger_ddl_commands function
does not return object_name unlike the pg_event_trigger_dropped_objects.

https://www.postgresql.org/docs/9.6/functions-event-triggers.html

Logging the statement through log_statement=ddl or higher is the only way I
see to capture the details of alter statement.

On Tue, May 12, 2020 at 4:57 AM Michel Pelletier 
wrote:

> I'm writing a script that "reverses" ddl operations in an "up" script by
> capturing event triggers and generating a "down" script for reverting.
> I've got it working great for tables, indexes, etc, but it seems ADD COLUMN
> doesn't sent an event, here's the code i'm using, pasted straight from the
> documentation:
>
> https://gist.github.com/michelp/c6daa1c123c4743be4f8b08fad5d5396
>
> And here's the results I get, I get an event for dropping a column, but
> not adding one:
>
> postgres=# create table foo (bar int);
> NOTICE:  CREATE TABLE created object: table public public.foo
> CREATE TABLE
> postgres=# alter table foo add column baz int;
> NOTICE:  ALTER TABLE created object: table public public.foo
> ALTER TABLE
> postgres=# alter table foo drop column bar;
> NOTICE:  ALTER TABLE dropped object: table column public.
> public.foo.bar
> NOTICE:  ALTER TABLE created object: table public public.foo
> ALTER TABLE
> postgres=#
>
> Is this asymmetry a bug?  I realize these event trigger functions are
> typically written in C, but I'd like to keep this idea contained to a
> plpgsql function and cover as many reversible changes as I can.
>
> Thank you,
>
> -Michel
>


-- 
Thanks,
Mohamed Tanveer
988-061-3565
www.linkedin.com/in/tanveermunavar

Re: Firing an event trigger for ADD COLUMN?

[Alvaro Herrera]

On 2020-May-11, Michel Pelletier wrote:

> Is this asymmetry a bug?  I realize these event trigger functions are
> typically written in C, but I'd like to keep this idea contained to a
> plpgsql function and cover as many reversible changes as I can.

Sadly, you cannot obtain all the necessary details using only plpgsql.
The only way is to compile a C extension.  There's a very rough,
incomplete skeleton to show how to do this in
src/test/modules/test_ddl_deparse.  A much more complete approach, which
was not completed, was used to produce JSON output from the C
structures -- see
https://www.postgresql.org/message-id/20150409161419.gc4...@alvh.no-ip.org

Keep in mind ALTER TABLE is a very sophisticated; you can do things like
ALTER TABLE one ADD COLUMN two INT, DROP COLUMN three, ADD COLUMN four text;
(i.e. do several things in one command) so if you were to use a
simplistic approach, you might end up missing things.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Firing an event trigger for ADD COLUMN?

[Alvaro Herrera]

On 2020-May-11, Alvaro Herrera wrote:

> https://www.postgresql.org/message-id/20150409161419.gc4...@alvh.no-ip.org

(Please don't expect this code to compile anymore.)

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Enforcing uniqueness on [real estate/postal] addresses

[Tim Cross]

Peter Devoy  writes:

> Hi list
>
> I need to store addresses for properties (as in real estate) so in my
> naivety I created a unique constraint like this:
>
> ALTER TABLE properties
> ADD CONSTRAINT is_unique_address
> UNIQUE (
> description, --e.g. Land north of Foo Cottage
> address_identifier_general,
> street,
> postcode
> );
>
> Of course, if any of the fields are NULL (which they often are) I end
> up with duplicates.
>
> One solution may be to add NOT NULL constraints and use empty strings
> instead of NULL values but, until asking around today, I thought this was
> generally considered bad practice.
>
> Please can anyone recommend a way of approaching this? Perhaps empty strings
> are pragmatic in this situation?
>
> Kind regards
>
>

Hi Peter,

Personally, I don't like the idea of using empty strings just to avoid
having nulls. This is probably a personal preference, but for me null
and '' are quite different. A null indicates an unknown - we don't know
what the value is. An empty string i.e. '' means there is no value (i.e.
we know it has no value). The difference is quite subtle and may not
seem relevant. It may not be or it may be or it may become relevant in
the future. General rule of thumb for me is that my model should reflect
the known information and should always avoid any data transformation or
mapping which reduces the known information.

I would step back a bit and think about why/what constraint you really
need and what needs to be unique. The first field which jumps out for me
is description. Is this really a unique value? Would it be possible to
have two properties with the same description? Does it matter if two
properties have the same description? Does the description really affect
property uniqueness. If two records have the same street, postcode and
general_property_identifier, but different descriptions, are they really
two different records? Will description change over time? As description
is a fairly subjective value, I would be tempted to not include it in
your unique constraint at all. In fact, I would probably keep
description in a separate table as it may be reasonable to have multiple
descriptions for a property. If you want just a single description, then
you can leave it in this table. I would not put a unique or not null
constraint on it.

This would leave you with address_identifier_general, street and
postcode. None of those will be unique by themselves. You will only get
uniqueness when you combine all 3. Can any of them be null? I would
suspect not, so I would define them with not null constraints. I would
then probably add a composite unique index using all 3 values to enforce
uniqueness. Depending on your application needs, I would probably add a
unique property_id field to the table as well (which would be the value
I would used to link records in other tables, such as a
property_description table).

Of course, there are other constraints you could consider - post code
probably has a set format which you might want to enforce or perhaps you
can access a complete listing of valid postcodes and import that into
your system as a postcode table. In that case, your postcode field might
be better defined as a foreign key constraint into the postcode table. 

When defining your constraints, it is important to consider what
information is known at the point of initial data entry. Is there a need
to enter partial data (for example, you might know the street and
postcode, but not the general_property_identifier. Is it expected or
reasonable to allow entry of this sort of partial data? If so, how will
that work with your uniqueness constraints? (it may be quite reasonable
to require all 3 fields be known). The point is, you need to know how
the system will be used and what the expectations of the users are.
Maybe there is a legitimate business case to allow partial data entry,
in which case, you may need a different approach or a way to identify
partial/incomplete records etc. 

-- 
Tim Cross

Re: Upgrade Process Says "The database server was not shut down cleanly" but it was

[Adrian Klaver]

On 5/11/20 2:45 PM, TalGloz wrote:

Well I tried your steps, both Postgres 10 and 12 are in perfect running
conditions and were shut down using the "systemctl" command. When I execute:

/usr/pgsql-12/bin/pg_upgrade --old-bindir=/usr/pgsql-10/bin/
--new-bindir=/usr/pgsql-12/bin/ --old-datadir=/var/lib/pgsql/10/data
--new-datadir=/var/lib/pgsql/12/data --old-options '-c
config_file=/var/lib/pgsql/10/data/postgresql.conf' --new-options '-c
config_file=/var/lib/pgsql/12/data/postgresql.conf' --check

I get:

Performing Consistency Checks
-
Checking cluster versions   ok
Checking database user is the install user  ok
Checking database connection settings ok
Checking for prepared transactions  ok
Checking for reg* data types in user tables   ok
Checking for contrib/isn with bigint-passing mismatch  ok
Checking for tables WITH OIDSok
Checking for invalid "sql_identifier" user columns ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions  ok

*Clusters are compatible*

But if I execute the same command right after that without the "--check"
flag I get:

Performing Consistency Checks
-
Checking cluster versions   ok

The target cluster was not shut down cleanly.
Failure, exiting


Both of the servers were still shut down. If I start form the beginning with
a fresh Prostgres 12 installation and execute the upgrade command right away
without the "--check" flag I get the problem described in my original post.


How are you installing Postgres?

Are the 10 and 12 packages coming from the same location?



Best regards,
TalGlo



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html





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

Re: Is there a significant difference in Memory settings between 9.5 and 12

[Tory M Blue]

On Mon, May 11, 2020 at 2:08 PM Tory M Blue  wrote:

>
>
> On Mon, May 11, 2020 at 1:36 PM Tom Lane  wrote:
>
>> Tory M Blue  writes:
>> > 12 will not start at 10GB, even though it's the same hardware, same
>> config
>> > file, same physical box, same everything, just version 12 vs 9.5
>>
>> For me, using all-default settings (in particular, shared_buffers =
>> 128MB), the shared memory block is about 141.6MB using 9.5 and 142.1MB
>> using 12.  So there's half a meg or so of additional data in v12, but
>> certainly not gigabytes worth.
>>
>> Are you trying to start both postmasters concurrently?  Maybe you're
>> hitting some kernel limit on the total amount of shared memory in the
>> system.
>>
>> regards, tom lane
>>
>
> Okay the one difference I see in settings is this little gem in 12..
>
> shared_memory_type mmap
>
> Otherwise i'm not seeing a ton of other settings not common between them,.
>
> This is the only major difference I'm seeing, as it's really not an option
> in 9.5. Appears 9.5 was using
>
> shared_memory_type (enum)
>
> Specifies the shared memory implementation that the server should use for
> the main shared memory region that holds PostgreSQL's shared buffers and
> other shared data. Possible values are mmap (for anonymous shared memory
> allocated using mmap), sysv (for System V shared memory allocated via
> shmget) and windows (for Windows shared memory). Not all values are
> supported on all platforms; the first supported option is the default for
> that platform. The use of the sysv option, which is not the default on
> any platform, is generally discouraged because it typically requires
> non-default kernel settings to allow for large allocations (see
> Section 18.4.1
> ).
>
>

That didn't help.

ay 11 19:46:13 qdb03.prod.ca. postmaster[31048]: < 2020-05-11 19:46:13.026
PDT  >FATAL:  could not create shared memory segment: Cannot allocate memory
May 11 19:46:13 qdb03.prod.ca postmaster[31048]: < 2020-05-11 19:46:13.026
PDT  >DETAIL:  Failed system call was shmget(key=5432001, size=11026235392,
03600).
May 11 19:46:13 qdb03.prod.ca postmaster[31048]: < 2020-05-11 19:46:13.026
PDT  >HINT:  This error usually means that PostgreSQL's request for a
shared memory segm
May 11 19:46:13 qdb03.prod.ca postmaster[31048]: The PostgreSQL
documentation contains more information about shared memory configuration.

Attempted to change ;
#shared_memory_type = 'sysv'

It took the change but didn't help. So 10GB of shared_buffers in 12 is
still a no go. I'm down to 5GB and it works, but this is the same hardware,
the same exact 9.5 configuration. So I'm missing something. WE have not had
to mess with kernel memory settings since 9.4, so this is an odd one.

I'll keep digging, but i'm hesitant to do my multiple TB db's with half of
their shared buffer configs, until I understand what 12 is doing
differently than 9.5

Thanks again for the ideas

Tory

Re: Is there a significant difference in Memory settings between 9.5 and 12

[David G. Johnston]

On Monday, May 11, 2020, Tory M Blue  wrote:

> I'll keep digging, but i'm hesitant to do my multiple TB db's with half of
>>> their shared buffer configs, until I understand what 12 is doing
>>> differently than 9.5
>>
>>
Maybe run your test suite on 9.6, 10, and 11 to see if it is indeed new to
12 or at least appears on other versions?

David J.

Re: Firing an event trigger for ADD COLUMN?

[Michel Pelletier]

On Mon, May 11, 2020 at 5:23 PM Alvaro Herrera 
wrote:

> On 2020-May-11, Michel Pelletier wrote:
>
> > Is this asymmetry a bug?  I realize these event trigger functions are
> > typically written in C, but I'd like to keep this idea contained to a
> > plpgsql function and cover as many reversible changes as I can.
>
> Sadly, you cannot obtain all the necessary details using only plpgsql.
> The only way is to compile a C extension.  There's a very rough,
> incomplete skeleton to show how to do this in
> src/test/modules/test_ddl_deparse.  A much more complete approach, which
> was not completed, was used to produce JSON output from the C
> structures -- see
> https://www.postgresql.org/message-id/20150409161419.gc4...@alvh.no-ip.org


Ah thank you, test_ddl_deparse gives me a good idea what's going on now.
Very interesting thread, thanks for your work on this.

Keep in mind ALTER TABLE is a very sophisticated; you can do things like
> ALTER TABLE one ADD COLUMN two INT, DROP COLUMN three, ADD COLUMN four
> text;
> (i.e. do several things in one command) so if you were to use a
> simplistic approach, you might end up missing things.
>

This is a support feature for some users so they are encouraged to write
down scripts.  The idea being the tool would spit one out and they'll be
advised to use it as a starting point for the actual post-hoc down script,
so catching CREATE TABLE/INDEX is a good start.  As this ddl deparsing
feature evolves in the future, I can add more complex cases.

Thanks again!

-Michel


> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: Is there a significant difference in Memory settings between 9.5 and 12

[Thomas Munro]

On Tue, May 12, 2020 at 2:52 PM Tory M Blue  wrote:
> It took the change but didn't help. So 10GB of shared_buffers in 12 is still 
> a no go. I'm down to 5GB and it works, but this is the same hardware, the 
> same exact 9.5 configuration. So I'm missing something. WE have not had to 
> mess with kernel memory settings since 9.4, so this is an odd one.
>
> I'll keep digging, but i'm hesitant to do my multiple TB db's with half of 
> their shared buffer configs, until I understand what 12 is doing differently 
> than 9.5

Which exact version of 9.5.x are you coming from?  What's the exact
error message on 12 (you showed the shared_memory_type=sysv error, but
with the default  value (mmap) how does it look)?  What's your
huge_pages setting?

Can you reproduce the problem with a freshly created test cluster?  As
a regular user, assuming regular RHEL packaging, something like
/usr/pgsql-12/bin/initdb -D test_pgdata, and then
/usr/pgsql-12/bin/postgres -D test_pgdata -c shared_buffers=10GB (then
^C to stop it).  If that fails to start in the same way, it'd be
interesting to see the output of the second command with strace in
front of it, in the part where it allocates shared memory.  And
perhaps it'd be interesting to see the same output with
/usr/pgsql-9.5/bin/XXX (if you still have the packages).  For example,
on my random dev laptop that looks like:

openat(AT_FDCWD, "/proc/meminfo", O_RDONLY) = 6
fstat(6, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
read(6, "MemTotal:   16178852 kB\nMemF"..., 1024) = 1024
read(6, ":903168 kB\nShmemHugePages:  "..., 1024) = 311
close(6)= 0
mmap(NULL, 11016339456, PROT_READ|PROT_WRITE,
MAP_SHARED|MAP_ANONYMOUS|MAP_HUGETLB, -1, 0) = -1 ENOMEM (Cannot
allocate memory)
mmap(NULL, 11016003584, PROT_READ|PROT_WRITE,
MAP_SHARED|MAP_ANONYMOUS, -1, 0) = 0x7ff74e579000
shmget(0x52e2c1, 56, IPC_CREAT|IPC_EXCL|0600) = 3244038
shmat(3244038, NULL, 0) = 0x7ff9df5ad000

The output is about the same on REL9_5_STABLE and REL_12_STABLE for
me, only slightly different sizes.  If that doesn't fail in the same
way on your system with 12, perhaps there are some more settings from
your real clusters required to make it fail.  You could add them one
by one with -c foo=bar or in the throw away
test_pgdata/postgresql.conf, and perhaps that process might shed some
light?

I was going to ask if it might be a preloaded extension that is asking
for gobs of extra memory in 12, but we can see from your "Failed
system call was shmget(key=5432001, size=11026235392, 03600)" that
it's in the same ballpark as my total above for shared_buffers=10GB.

Re: Is there a significant difference in Memory settings between 9.5 and 12

[Tory M Blue]

On Mon, May 11, 2020 at 7:57 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Monday, May 11, 2020, Tory M Blue  wrote:
>
>> I'll keep digging, but i'm hesitant to do my multiple TB db's with half
 of their shared buffer configs, until I understand what 12 is doing
 differently than 9.5
>>>
>>>
> Maybe run your test suite on 9.6, 10, and 11 to see if it is indeed new to
> 12 or at least appears on other versions?
>
> David J.
>

That may be the next step in the lab, but was hoping someone knew of a
significant difference.

Thanks
Tory

Re: Is there a significant difference in Memory settings between 9.5 and 12

[Tom Lane]

Tory M Blue  writes:
> That may be the next step in the lab, but was hoping someone knew of a
> significant difference.

I think we've made it perfectly clear that we don't.  There's something
odd about your situation.

regards, tom lane

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

[Matthias Apitz]

El día Montag, Mai 11, 2020 a las 02:41:29 -0400, Tom Lane escribió:

> Matthias Apitz  writes:
> > Below is the exchange. The visible strings are: 'sisis' (the PG user),
> > 'sisis123' (its password in PG, a dummy password used for testing) and
> > 'srap32dxr1' the hostname. The server is terminating the connection with
> > a F-pkg:
> 
> [ squint... ]  That looks nothing like a Postgres-protocol exchange
> to me.  If it weren't for the runs of zeroes, I'd wonder if the
> connection had SSL encryption turned on.  Perhaps you captured the
> wrong session?

It turned out, that the messages:

2020-05-12 05:33:06.828 CEST [6271] LOG:  invalid length 33554940 of startup 
packet
2020-05-12 05:33:08.305 CEST [6291] LOG:  invalid length 33554940 of startup 
packet
2020-05-12 05:34:07.495 CEST [6596] LOG:  invalid length 33554940 of startup 
packet
2020-05-12 05:34:09.710 CEST [6927] LOG:  invalid length 33554940 of startup 
packet

have been caused by a misconfiguration of the component in question: the
application used the Sybase JDBC driver to talk to the PostgreSQL server
:-(

This part of the thread is hereby solved.

Remaining is still:

2020-04-30 15:14:03.367 CEST [3843] LOG:  bogus received message length: 
1650422894
2020-04-30 15:57:48.926 CEST [10380] LOG:  bogus received message length: 
1650422894
2020-04-30 15:58:31.851 CEST [10380] LOG:  incomplete message from client
2020-04-30 15:58:53.578 CEST [10439] LOG:  bogus received message length: 
1650422894
2020-04-30 16:00:13.500 CEST [10439] LOG:  incomplete message from client

matthias


-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: Is there a significant difference in Memory settings between 9.5 and 12

[Tory M Blue]

On Mon, May 11, 2020 at 9:57 PM Tom Lane  wrote:

> Tory M Blue  writes:
> > That may be the next step in the lab, but was hoping someone knew of a
> > significant difference.
>
> I think we've made it perfectly clear that we don't.  There's something
> odd about your situation.
>
> regards, tom lane
>

totally, and i'll try to provide some more data tomorrow.

Thanks everyone.

Tory

Re: Is there a significant difference in Memory settings between 9.5 and 12

[Tory M Blue]

On Mon, May 11, 2020 at 9:01 PM Thomas Munro  wrote:

> On Tue, May 12, 2020 at 2:52 PM Tory M Blue  wrote:
> > It took the change but didn't help. So 10GB of shared_buffers in 12 is
> still a no go. I'm down to 5GB and it works, but this is the same hardware,
> the same exact 9.5 configuration. So I'm missing something. WE have not had
> to mess with kernel memory settings since 9.4, so this is an odd one.
> >
> > I'll keep digging, but i'm hesitant to do my multiple TB db's with half
> of their shared buffer configs, until I understand what 12 is doing
> differently than 9.5
>
> Which exact version of 9.5.x are you coming from?  What's the exact
> error message on 12 (you showed the shared_memory_type=sysv error, but
> with the default  value (mmap) how does it look)?  What's your
> huge_pages setting?
>

9.5-20
postgresql95-9.5.20-2PGDG.rhel7.x86_64
postgresql95-contrib-9.5.20-2PGDG.rhel7.x86_64
postgresql95-libs-9.5.20-2PGDG.rhel7.x86_64
postgresql95-server-9.5.20-2PGDG.rhel7.x86_64

I don't use huge_pages

And this error is actually from the default mmap

May 08 12:33:58 qdb01.prod.ca postmaster[8790]: < 2020-05-08 12:33:58.324
PDT  >HINT:  This error usually means that PostgreSQL's request for a
shared memory segment exceeded available memory, swap space, or huge pages.
To reduce the request size (currently 11026235392 bytes), reduce
PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or
max_connections.

The above error is with 12 trying to start with shared_buffers = 10GB...

9.5 starts fine with the same configuration file.   That kind of started me
down this path.

And just to repeat. Same exact hardware, same kernel, nothing more than
installing the latest postgres12, copying my config files from 9.5 to 12
and running the pg_upgrade.

9.5 has been running for years with the same configuration file, so
something changed somewhere along the line that is preventing 12 to start
with the same config file.  And the allocation error is with either the
sysv or mman on 12. (will start with 5GB allocated, but not 10GB, on a 15GB
box (dedicated postgres server).


> Can you reproduce the problem with a freshly created test cluster?  As
> a regular user, assuming regular RHEL packaging, something like
> /usr/pgsql-12/bin/initdb -D test_pgdata, and then
> /usr/pgsql-12/bin/postgres -D test_pgdata -c shared_buffers=10GB (then
> ^C to stop it).  If that fails to start in the same way, it'd be
> interesting to see the output of the second command with strace in
> front of it, in the part where it allocates shared memory.  And
> perhaps it'd be interesting to see the same output with
> /usr/pgsql-9.5/bin/XXX (if you still have the packages).  For example,
> on my random dev laptop that looks like:
>
> openat(AT_FDCWD, "/proc/meminfo", O_RDONLY) = 6
> fstat(6, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
> read(6, "MemTotal:   16178852 kB\nMemF"..., 1024) = 1024
> read(6, ":903168 kB\nShmemHugePages:  "..., 1024) = 311
> close(6)= 0
> mmap(NULL, 11016339456, PROT_READ|PROT_WRITE,
> MAP_SHARED|MAP_ANONYMOUS|MAP_HUGETLB, -1, 0) = -1 ENOMEM (Cannot
> allocate memory)
> mmap(NULL, 11016003584, PROT_READ|PROT_WRITE,
> MAP_SHARED|MAP_ANONYMOUS, -1, 0) = 0x7ff74e579000
> shmget(0x52e2c1, 56, IPC_CREAT|IPC_EXCL|0600) = 3244038
> shmat(3244038, NULL, 0) = 0x7ff9df5ad000
>
> The output is about the same on REL9_5_STABLE and REL_12_STABLE for
> me, only slightly different sizes.  If that doesn't fail in the same
> way on your system with 12, perhaps there are some more settings from
> your real clusters required to make it fail.  You could add them one
> by one with -c foo=bar or in the throw away
> test_pgdata/postgresql.conf, and perhaps that process might shed some
> light?
>
> I was going to ask if it might be a preloaded extension that is asking
> for gobs of extra memory in 12, but we can see from your "Failed
> system call was shmget(key=5432001, size=11026235392, 03600)" that
> it's in the same ballpark as my total above for shared_buffers=10GB.
>

Be more than happy to test this out. I'll see what I can pull tomorrow and
provide some dataz :)   I know it's not ideal to use the same config file,
I know that various things are added or changed (usually added) but the
defaults are typically safe. But after sometime dialing in the settings for
our use case, I've just kind of kept moving them forward.

But  let me do some more testing tomorrow (since I'm trying to get to the
bottom of this, before I attempt my big DB upgrades).  So I'll spend some
time testing and see if I can't get similar "failures/challenges"? and go
from there.

Appreciate the ideas!

Tory

Re: Is there a significant difference in Memory settings between 9.5 and 12.

[David G. Johnston]

On Monday, May 11, 2020, Tory M Blue  wrote:
>
>
> And just to repeat. Same exact hardware, same kernel, nothing more than
> installing the latest postgres12, copying my config files from 9.5 to 12
> and running the pg_upgrade.
>

You’ll want to remove the pg_upgrade from the equation and try v12

David J.

Re: Is there a significant difference in Memory settings between 9.5 and 12

[David G. Johnston]

Repost, edited subject by mistake...

On Monday, May 11, 2020, Tory M Blue  wrote:
>
> And just to repeat. Same exact hardware, same kernel, nothing more than
> installing the latest postgres12, copying my config files from 9.5 to 12
> and running the pg_upgrade.
>

You’ll want to remove the pg_upgrade from the equation and try v12

David J.

Re: Is there a significant difference in Memory settings between 9.5 and 12

[David G. Johnston]

On Monday, May 11, 2020, David G. Johnston 
wrote:

> Repost, edited subject by mistake...
>
> On Monday, May 11, 2020, Tory M Blue  wrote:
>>
>> And just to repeat. Same exact hardware, same kernel, nothing more than
>> installing the latest postgres12, copying my config files from 9.5 to 12
>> and running the pg_upgrade.
>>
>
> You’ll want to remove the pg_upgrade from the equation and try v12
>

Sorry...if you copied the config to v12 before the upgrade and the upgrade
worked that suggests that v12 booted up at some point with the
configuration, no?  Does pg_upgrade do something special?

David J.