UUID or auto-increment

[Ashkar Dev]

Hi,
for web application is it needed to use UUID or auto-increment?

1- if two user inserts row at the same time, does it work?
2- dose the database give the same id for both users or execute one of them
first? ( I mean ID conflict not happens?)

Thanks.

SQL Question about like

[p...@gmx.de]

Hello,

my SQL question is, why psql doesn't return the record?

create table lll (text char(100));
insert into lll (text) values ('10% - Ersthelfer');

select * from lll where text like '10% - Ersthelfer';

Other databases (Maria, SQL Server, YARD) do this.
What can I do in pg, to get the result?

Thank you and best regards, Martin

Re: UUID or auto-increment

[Ravi Krishna]

Both can handle concurrent writes.  auto-increment is nothing but serial or 
sequence cols and they can handle unique concurrent request.  That is why 
sometimes you may have gaps.UUID is not only unique, but is also unique across 
space. You can have two different databases generate UUID at the same time and 
it will still be unique. So that will help if you are consolidating different 
databases into one big data mart and they can all can go to the same table 
without conflict. With Sequence or Serial that will be a problem.Finally UUID 
results in write amplication in wal logs.  Keep that in mind if your app does 
lot of writes.
  

Re: SQL Question about like

[Ron]

On 8/10/20 11:37 AM, p...@gmx.de wrote:

Hello,

my SQL question is, why psql doesn't return the record?

create table lll (text char(100));
insert into lll (text) values ('10% - Ersthelfer');

select * from lll where text like '10% - Ersthelfer';

Other databases (Maria, SQL Server, YARD) do this.
What can I do in pg, to get the result?


Add a wildcard character.  (Also, why use LIKE in an equality?)

test=# select * from lll where text like '10% - Ersthelfer';
 text
--
(0 rows)

test=# select * from lll where text like '10% - Ersthelfer%';
text
--
 10% - Ersthelfer
(1 row)

test=#
test=#
test=# select * from lll where text = '10% - Ersthelfer';
text
--
 10% - Ersthelfer
(1 row)


--
Angular momentum makes the world go 'round.

Re: UUID or auto-increment

[Michael Lewis]

UUID are also random and not correlated with time typically, so with a very
large table when accessing primarily recent data, hitting an index on a big
table will pull random pages into memory instead of primarily the end of
the index.

Re: UUID or auto-increment

[Ron]

On 8/10/20 11:38 AM, Ravi Krishna wrote:
[snip]
Finally UUID results in write amplication in wal logs.  Keep that in mind 
if your app does lot of writes.


Because UUID is 32 bytes, while SERIAL is 4 bytes?

--
Angular momentum makes the world go 'round.

Re: SQL Question about like

[Adrian Klaver]

On 8/10/20 9:37 AM, p...@gmx.de wrote:

Hello,

my SQL question is, why psql doesn't return the record?

create table lll (text char(100));
insert into lll (text) values ('10% - Ersthelfer');

select * from lll where text like '10% - Ersthelfer';

Other databases (Maria, SQL Server, YARD) do this.
What can I do in pg, to get the result?


A little documentation goes a long way:) :

https://www.postgresql.org/docs/12/functions-matching.html#FUNCTIONS-LIKE


Thank you and best regards, Martin






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

Re: UUID or auto-increment

[Stephen Frost]

Greeitngs,

* Ron (ronljohnso...@gmail.com) wrote:
> On 8/10/20 11:38 AM, Ravi Krishna wrote:
> >Finally UUID results in write amplication in wal logs.  Keep that in mind
> >if your app does lot of writes.
> 
> Because UUID is 32 bytes, while SERIAL is 4 bytes?

and because it's random and so will touch a lot more pages when you're
using it...

Avoid UUIDs if you can- map them to something more sensible internally
if you have to deal with them.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: UUID or auto-increment

[Adrian Klaver]

On 8/10/20 9:51 AM, Ron wrote:

On 8/10/20 11:38 AM, Ravi Krishna wrote:
[snip]
Finally UUID results in write amplication in wal logs.  Keep that in 
mind if your app does lot of writes.


Because UUID is 32 bytes, while SERIAL is 4 bytes?


You mean 32 digits for 128 bits?:

https://www.postgresql.org/docs/12/datatype-uuid.html

And there is BIGSERIAL which is 8 bytes.



--
Angular momentum makes the world go 'round.



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

Re: SQL Question about like

[Michael Nolan]

The problem is your field is fixed length text, change it to varchar(100)
or text and it works without the wildcard at the end.
--
Mike Nolan

Re: UUID or auto-increment

[Israel Brewster]

---
Israel Brewster
Software Engineer
Alaska Volcano Observatory 
Geophysical Institute - UAF 
2156 Koyukuk Drive 
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145

> On Aug 10, 2020, at 8:53 AM, Stephen Frost  wrote:
> 
> Greeitngs,
> 
> * Ron (ronljohnso...@gmail.com) wrote:
>> On 8/10/20 11:38 AM, Ravi Krishna wrote:
>>> Finally UUID results in write amplication in wal logs.  Keep that in mind
>>> if your app does lot of writes.
>> 
>> Because UUID is 32 bytes, while SERIAL is 4 bytes?
> 
> and because it's random and so will touch a lot more pages when you're
> using it...

I would point out, however, that using a V1 UUID rather than a V4 can help with 
this as it is sequential, not random (based on MAC address and timestamp + 
random). There is a trade off, of course, as with V1 if two writes occur on the 
same computer at the exact same millisecond, there is a very very small chance 
of generating conflicting UUID’s (see 
https://www.sohamkamani.com/blog/2016/10/05/uuid1-vs-uuid4/ 
). As there is 
still a random component, however, this seems quite unlikely.


> 
> Avoid UUIDs if you can- map them to something more sensible internally
> if you have to deal with them.
> 
> Thanks,
> 
> Stephen

Re: UUID or auto-increment

[Stephen Frost]

Greetings,

* Israel Brewster (ijbrews...@alaska.edu) wrote:
> > On Aug 10, 2020, at 8:53 AM, Stephen Frost  wrote:
> > * Ron (ronljohnso...@gmail.com) wrote:
> >> On 8/10/20 11:38 AM, Ravi Krishna wrote:
> >>> Finally UUID results in write amplication in wal logs.  Keep that in mind
> >>> if your app does lot of writes.
> >> 
> >> Because UUID is 32 bytes, while SERIAL is 4 bytes?
> > 
> > and because it's random and so will touch a lot more pages when you're
> > using it...
> 
> I would point out, however, that using a V1 UUID rather than a V4 can help 
> with this as it is sequential, not random (based on MAC address and timestamp 
> + random). There is a trade off, of course, as with V1 if two writes occur on 
> the same computer at the exact same millisecond, there is a very very small 
> chance of generating conflicting UUID’s (see 
> https://www.sohamkamani.com/blog/2016/10/05/uuid1-vs-uuid4/ 
> ). As there is 
> still a random component, however, this seems quite unlikely.

Sure, that helps, but it's still not great, and they're still much, much
larger than you'd ever need for an identifier inside of a given system,
so best to map it to something reasonable and avoid them as much as
possible.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: SQL Question about like

[Adrian Klaver]

On 8/10/20 10:01 AM, Michael Nolan wrote:
The problem is your field is fixed length text, change it to 
varchar(100) or text and it works without the wildcard at the end.


That assumes values don't get entered  with spaces:

create table lll (text varchar(100));
insert into lll (text) values ('10% - Ersthelfer');
insert into lll (text) values ('10% - Ersthelfer   ');

select * from lll where text like '10% - Ersthelfer';
   text
--
 10% - Ersthelfer
(1 row)

select * from lll where text like '10% - Ersthelfer%';
text
-
 10% - Ersthelfer
 10% - Ersthelfer

I have seen that enough times to default to using wildcard if I am 
really looking for LIKE not =.



--
Mike Nolan





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

Re: UUID or auto-increment

[Adam Brusselback]

>  I would point out, however, that using a V1 UUID rather than a V4 can
help with this as it is sequential, not random (based on MAC address and
timestamp + random)

I wanted to make this point, using sequential UUIDs helped me reduce write
amplification quite a bit with my application, I didn't use V1, instead I
used: https://pgxn.org/dist/sequential_uuids/

Reduces the pain caused by UUIDs a ton IMO.
-Adam

Re: SQL Question about like

[Michael Nolan]

I usually use something like trim(field) like 'pattern'.  Eliminates the
need for the wildcard at the end.  I find I use the ~ operator more than
'like' though.
--
Mike Nolan

On Mon, Aug 10, 2020 at 12:24 PM Adrian Klaver 
wrote:

> On 8/10/20 10:01 AM, Michael Nolan wrote:
> > The problem is your field is fixed length text, change it to
> > varchar(100) or text and it works without the wildcard at the end.
>
> That assumes values don't get entered  with spaces:
>
> create table lll (text varchar(100));
> insert into lll (text) values ('10% - Ersthelfer');
> insert into lll (text) values ('10% - Ersthelfer   ');
>
> select * from lll where text like '10% - Ersthelfer';
> text
> --
>   10% - Ersthelfer
> (1 row)
>
> select * from lll where text like '10% - Ersthelfer%';
>  text
> -
>   10% - Ersthelfer
>   10% - Ersthelfer
>
> I have seen that enough times to default to using wildcard if I am
> really looking for LIKE not =.
>
> > --
> > Mike Nolan
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: SQL Question about like

[Michael Nolan]

>
> Sorry about the top-posting, Firefox and I disagreed about whether I was
> done editing the previous message.
>
--
Mike Nolan

Certficates

[Shankar Bhaskaran]

Hi All,

This is a very basic question . i have to import the server.crt on the
client side java trustore to connect using jdbc to postgres server secured
by ssl.
But when i try psql from same machine , it shows the connection as
encrypted . How does psql import the server certificate?

 psql "postgresql://$POSTGRES_HOST:$PG_PORT/postgres" -U postgres
psql (9.6.18)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)
Type "help" for help.


Regards,
Shankar

Re: Certficates

[Martin Gainty]

cant you use keytool ?
assume server certificate is called server.crt
assume truststore file is called server.truststore.jks

cd %JRE_HOME%/lib/security
keytool -import -alias %ALIAS% -file server.crt -keystore server.truststore.jks

m.

From: Shankar Bhaskaran 
Sent: Monday, August 10, 2020 1:53 PM
To: pgsql-general@lists.postgresql.org 
Subject: Certficates

Hi All,

This is a very basic question . i have to import the server.crt on the client 
side java trustore to connect using jdbc to postgres server secured by ssl.
But when i try psql from same machine , it shows the connection as encrypted . 
How does psql import the server certificate?

 psql "postgresql://$POSTGRES_HOST:$PG_PORT/postgres" -U postgres
psql (9.6.18)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 
256, compression: off)
Type "help" for help.


Regards,
Shankar

Re: UUID or auto-increment

[Peter J. Holzer]

On 2020-08-10 09:10:00 -0800, Israel Brewster wrote:
> I would point out, however, that using a V1 UUID rather than a V4 can
> help with this as it is sequential, not random (based on MAC address
> and timestamp + random).

If I read the specs correctly, a V1 UUID will roll over every 429
seconds. I think that as far as index locality is concerned, this is
essentially random for most applications.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Certficates

[David G. Johnston]

On Mon, Aug 10, 2020 at 10:54 AM Shankar Bhaskaran 
wrote:

> How does psql import the server certificate?
>

See:

https://www.postgresql.org/docs/12/libpq-envars.html

Namely the "PGSSL*" prefixed environment variables.

It works by default because both the server and client are usually
installed from the same source and the same default certificate files are
provided to each.

David J.

Re: Certficates

[David G. Johnston]

The convention on these lists is to inline or bottom-post.

On Mon, Aug 10, 2020 at 11:11 AM Martin Gainty  wrote:

> cant you use keytool ?
>

That wasn't the question, the OP already indicated they can do this
successfully in JDBC.

David J.

Re: Certficates

[Tom Lane]

"David G. Johnston"  writes:
> On Mon, Aug 10, 2020 at 10:54 AM Shankar Bhaskaran 
> wrote:
>> How does psql import the server certificate?

> It works by default because both the server and client are usually
> installed from the same source and the same default certificate files are
> provided to each.

Actually I suspect the answer is "it works because the default behavior
is to just encrypt the connection, not to try to verify the server
certificate".  If you want it to fail when it doesn't recognize the server
cert, you need sslmode=verify-ca or sslosslmode=verify-full in your
connection string.  See sslmode here:

https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS

regards, tom lane

Re: UUID or auto-increment

[Israel Brewster]

> On Aug 10, 2020, at 12:06 PM, Peter J. Holzer  wrote:
> 
> On 2020-08-10 09:10:00 -0800, Israel Brewster wrote:
>> I would point out, however, that using a V1 UUID rather than a V4 can
>> help with this as it is sequential, not random (based on MAC address
>> and timestamp + random).
> 
> If I read the specs correctly, a V1 UUID will roll over every 429
> seconds. I think that as far as index locality is concerned, this is
> essentially random for most applications.

According to wikipedia, the time value in a V1 UUID is a 60-bit number, and 
will roll over "around 3400AD”, depending on the algorithm used, or 5236AD if 
the software treats the timestamp as unsigned. This timestamp is extended by a 
13 or 14-bit “uniqifying" clock sequence to handle cases of overlap, and then 
the 48bit MAC address (constant, so no rollover there) is appended. So perhaps 
that 13 or 14 bit “uniqifying” sequence will roll over every 429 seconds, 
however the timestamp *as a whole* won’t roll over for quite a while yet, 
thereby guaranteeing that the UUIDs will be sequential, not random (since, last 
I checked, time was sequential).

---
Israel Brewster
Software Engineer
Alaska Volcano Observatory 
Geophysical Institute - UAF 
2156 Koyukuk Drive 
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145

> 
>hp
> 
> -- 
>   _  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"

Re: UUID or auto-increment

[Rob Sargent]

On 8/10/20 10:53 AM, Stephen Frost wrote:

Greeitngs,

* Ron (ronljohnso...@gmail.com) wrote:

On 8/10/20 11:38 AM, Ravi Krishna wrote:

Finally UUID results in write amplication in wal logs.  Keep that in mind
if your app does lot of writes.


Because UUID is 32 bytes, while SERIAL is 4 bytes?


and because it's random and so will touch a lot more pages when you're
using it...

Avoid UUIDs if you can- map them to something more sensible internally
if you have to deal with them.

Thanks,

Stephen

I suspect the increased storage cost is more related to the size of the 
record than to the ratio of the data types.


What says two consecutively saved records ought to be stored on the same 
page or will likely be sought with the same search criterion.  Serial 
ids put a time order (loosely) on the data which may be completely 
artificial.

Re: UUID or auto-increment

[John W Higgins]

On Mon, Aug 10, 2020 at 1:45 PM Israel Brewster 
wrote:

>
>
> > On Aug 10, 2020, at 12:06 PM, Peter J. Holzer  wrote:
> >
> > On 2020-08-10 09:10:00 -0800, Israel Brewster wrote:
> >> I would point out, however, that using a V1 UUID rather than a V4 can
> >> help with this as it is sequential, not random (based on MAC address
> >> and timestamp + random).
> >
> > If I read the specs correctly, a V1 UUID will roll over every 429
> > seconds. I think that as far as index locality is concerned, this is
> > essentially random for most applications.
>
> According to wikipedia, the time value in a V1 UUID is a 60-bit number,
> and will roll over "around 3400AD”, depending on the algorithm used, or
> 5236AD if the software treats the timestamp as unsigned. This timestamp is
> extended by a 13 or 14-bit “uniqifying" clock sequence to handle cases of
> overlap, and then the 48bit MAC address (constant, so no rollover there) is
> appended. So perhaps that 13 or 14 bit “uniqifying” sequence will roll over
> every 429 seconds, however the timestamp *as a whole* won’t roll over for
> quite a while yet, thereby guaranteeing that the UUIDs will be sequential,
> not random (since, last I checked, time was sequential).
>
>
Except the time portion of a V1 UUID is not written high to low but rather
low then middle then high which means that the time portion is not
expressed in a sequential format and the left 8 chars of a V1 UUID
"rollover" every 429 seconds or so.

For example a V1 UUID right around now looks like

7db3f2ba-db4f-11ea-87d0-0242ac130003

Less than a second later

7db534cc-db4f-11ea-87d0-0242ac130003

So that looks sequential but in roughly 429 seconds it will look like

7db3f2ba-db4f-11ea-87d1-0242ac130003

More importantly in other roughly 300 seconds it would be something like

6ab3f2ba-db4f-11ea-87d2-0242ac130003

Note the move from 87d0 to 87d1 and 87d2 in the middle but the left 8 bytes
"rollover".

That's not quite sequential in terms of indexing.

John

serial + db key, or guid?

[Mark Phillips]

Given four instances of posgres, each with a database, each instance receiving 
new data, and desiring a data “merge” a la BDR or similar multiple database 
solutions, my team has been discussing the pros and cons of generating unique 
keys in each table.

1. create a unique “database” id for each database, add a column to all tables 
for this id value and combine that with a serial id
2. use guid from pg functions
3. create id ranges for each database, e.g. db1 gets 1 to 1M, db2 gets 1M+1 to 
2M, and so on

All get us to the finish line, but each has its drawbacks. 

Advice, cautionary tales, suggestions and such will be warmly received.

 - Mark

Re: serial + db key, or guid?

[Christophe Pettus]

> On Aug 10, 2020, at 15:19, Mark Phillips  wrote:
> Advice, cautionary tales, suggestions and such will be warmly received.

Here's one solution a company found for this; it seems to work very well:

https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c

--
-- Christophe Pettus
   x...@thebuild.com

Re: Keeping state in a foreign data wrapper

[Ian Barwick]

On 2020/08/04 19:21, Stelios Sfakianakis wrote:
> Thank you again, I have another question in order to make sure I have a clear 
understanding:
>
>
>> On 4 Aug 2020, at 11:24, Ian Lawrence Barwick  wrote:
>>
>> The hash table is specific to each running backend so will only be
>> accessed by that process.
>>
>> Pre-loading a shared library just gives the library an opportunity to
>> set up shared memory etc. You can always try adding one of the FDW
>> libraries to "shared_preload_libraries" and see what happens
>> (theoretically nothing).
>>
>
> My impression was that since each client (e.g. libpq) connection results in the 
creation of a Postgres process in the backend 
(https://www.postgresql.org/developer/backend/) then this  (mysql) "connection 
pool" hash table is not global per se and shared among the different client / users 
sessions.

Correct, the connections are specific to each individual backend.

> But that defeats the purpose, no?

The purpose is to cache connections within the session, to avoid the overhead
of reconnecting to the remote server each time a query for that server is issued
in that session.


Regards

Ian Barwick




--
Ian Barwick   https://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Keeping state in a foreign data wrapper

[Ian Barwick]

On 2020/08/04 19:21, Stelios Sfakianakis wrote:
> Thank you again, I have another question in order to make sure I have a clear 
understanding:
>
>
>> On 4 Aug 2020, at 11:24, Ian Lawrence Barwick  wrote:
>>
>> The hash table is specific to each running backend so will only be
>> accessed by that process.
>>
>> Pre-loading a shared library just gives the library an opportunity to
>> set up shared memory etc. You can always try adding one of the FDW
>> libraries to "shared_preload_libraries" and see what happens
>> (theoretically nothing).
>>
>
> My impression was that since each client (e.g. libpq) connection results in the 
creation of a Postgres process in the backend 
(https://www.postgresql.org/developer/backend/) then this  (mysql) "connection 
pool" hash table is not global per se and shared among the different client / users 
sessions.

Correct, the connections are specific to each individual backend.

> But that defeats the purpose, no?

The purpose is to cache connections within the session, to avoid the overhead
of reconnecting to the remote server each time a query for that server is issued
in that session.


Regards

Ian Barwick



--
Ian Barwick   https://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: ERROR: invalid memory alloc request size 18446744073709551613

[Flaris Roland Feller]

Hello all,

I’ve got successfully isolate and delete the corrupted rows at the table, but 
what would caused that? How to prevent it?

Thanks in advance,
Flaris.

Flaris R. Feller
48-999811781
flaris.fel...@gmail.com
http://linkedin.com/in/flarisfeller


> Em 22 de jun de 2020, à(s) 13:54, Tom Lane  escreveu:
> 
> Flaris Feller  writes:
>>> Em seg., 22 de jun. de 2020 às 12:33, Peter J. Holzer 
>>> escreveu:
>>> On 2020-06-22 11:13:33 -0300, Flaris Feller wrote:
 When using Postgres 9.6.15 on "CentOS Linux release 7.5.1804 (Core)" on
 Intel x86_64 I noticed "invalid memory alloc request size" error at 
 PostgreSQL
 logs.
> 
>>> That's 2**64 - 3. So probably the size of some object is (erroneously)
>>> computed as -3 bytes.
> 
> Yeah.  I don't recall the bit-level details offhand, but this is an
> extremely common symptom of a corrupted length word in a variable-length
> field.
> 
> The usual approach is to try to isolate which row or rows contains corrupt
> data and then delete it.  I'm not aware of any tools for doing that
> automatically, but you can usually home in on a bad row by a process of
> binary search, eg testing how many rows you can fetch without seeing the
> error.  Also "select * from mytab where ctid = '(m,n)'" is useful for
> probing individual rows, once you get close to the problem area.
> 
>regards, tom lane

Re: ERROR: invalid memory alloc request size 18446744073709551613

[Flaris Roland Feller]

Hello Rob,

You mean, an insert or update carrying this value? No, it doesn’t.

Thanks,
Flaris.

Flaris R. Feller
flaris.fel...@gmail.com
http://linkedin.com/in/flarisfeller


> Em 22 de jun de 2020, à(s) 11:28, Rob Sargent  
> escreveu:
> 
> 
> 
>> On Jun 22, 2020, at 8:13 AM, Flaris Feller  wrote:
>> 
>> Hello all,
>> 
>> When using Postgres 9.6.15 on "CentOS Linux release 7.5.1804 (Core)" on 
>> Intel x86_64 I noticed "invalid memory alloc request size" error at 
>> PostgreSQL logs.
>> This is the postgresq.log file's fragment of log where the error was found.
>> 
>> 2020-06-22 00:29:18 BRT [16987]: [1-1] db=bxs,user=postgres ERRO:  invalid 
>> memory alloc request size 18446744073709551613
>> 2020-06-22 00:29:18 BRT [16987]: [2-1] db=bxs,user=postgres COMANDO:  COPY 
>> public.cham_chamada
>> 
>> PostgreSQL was installed using official PGDG repository through yum.
>> This is the list of postgresql.conf settings used in the cluster.
>> 
>> listen_addresses = '*'
>> log_destination = 'stderr' 
>> logging_collector = on 
>> log_directory = 'pg_log' 
>> log_filename = 'postgresql-%a.log' 
>> log_truncate_on_rotation = on 
>> log_rotation_age = 1d 
>> log_rotation_size = 0 
>> autovacuum = off
>> datestyle = 'iso, mdy'
>> lc_messages = 'pt_BR'   
>> lc_monetary = 'pt_BR'   
>> lc_numeric = 'pt_BR'
>> lc_time = 'pt_BR'   
>> default_text_search_config = 'pg_catalog.portuguese'
>> max_connections=150
>> shared_buffers=2GB
>> effective_cache_size=4GB
>> work_mem=13981kB
>> maintenance_work_mem=256MB
>> log_min_duration_statement = 30
>> log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u '
>> log_lock_waits = on
>> timezone='America/Sao_paulo'
>> log_timezone = 'Brazil/East'
>> min_wal_size = 1GB
>> max_wal_size = 2GB
>> 
>> Looking at previous list's messages I've found this could be a data 
>> corruption issue and I've followed the recommended procedures.
>> But even removing the corrupted records, doing a vacuum full and re-indexing 
>> the table the problem keep going recurrently.
>> So I would like some guidance to find the root cause of the table corruption 
>> on the database.
>> Any help would be appreciated.
>> 
>> Thanks in advance,
>> Flaris Feller.
> 
> I there an application generating this value perhaps?
> 1101
> Looks a little like a signed/un-signed mismatch
> 
> 

Re: ERROR: invalid memory alloc request size 18446744073709551613

[Flaris Roland Feller]

Hello Peter,

In this case, It was a COPY for a table to out of the database, but the error 
occurs with other commands like a SELECT.

Thanks in advance,
Flaris.

Flaris R. Feller
flaris.fel...@gmail.com
http://linkedin.com/in/flarisfeller


> Em 22 de jun de 2020, à(s) 12:33, Peter J. Holzer  escreveu:
> 
> On 2020-06-22 11:13:33 -0300, Flaris Feller wrote:
>> When using Postgres 9.6.15 on "CentOS Linux release 7.5.1804 (Core)" on Intel
>> x86_64 I noticed "invalid memory alloc request size" error at PostgreSQL 
>> logs.
>> This is the postgresq.log file's fragment of log where the error was found.
>> 
>> 2020-06-22 00:29:18 BRT [16987]: [1-1] db=bxs,user=postgres ERRO:  invalid
>> memory alloc request size 18446744073709551613
> 
> That's 2**64 - 3. So probably the size of some object is (erroneously)
> computed as -3 bytes.
> 
>> 2020-06-22 00:29:18 BRT [16987]: [2-1] db=bxs,user=postgres COMANDO:  COPY
>> public.cham_chamada
> 
> Does this always happen in conjunction with a COPY command or sometimes
> with other commands, too? If the former, are you copying into the
> database or out of it?
> 
>hp
> 
> -- 
>   _  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"