PostgreSQL's Max Identifier Length in Amazon RDS

[Atul Kumar]

Hi,

Could someone help me in telling :  how to increase Max Identifier Length
in amazon RDS.running instance on postgres 10 ?

I couldn't find the blog related to it so far, So kindly suggest.




Regards,
Atul

Re: PostgreSQL's Max Identifier Length in Amazon RDS

[Michael Lewis]

I believe that would not be possible. One quick read-
https://til.hashrocket.com/posts/8f87c65a0a-postgresqls-max-identifier-length-is-63-bytes

With it being RDS, changing source and recompiling is not an option.

Re: PostgreSQL's Max Identifier Length in Amazon RDS

[Michael Lewis]

Also-
https://www.postgresql.org/docs/10/runtime-config-preset.html

"The following “parameters” are read-only, and are determined when
PostgreSQL is compiled or when it is installed."

>

Re: Strange results when casting string to double

[Peter Eisentraut]

On 16.02.22 14:27, Carsten Klein wrote:
AFAIK, this conversion is done by internal function float8in, which, 
when called directly, yields the same results:


SELECT float8in('1.56');

--> 1.55   (wrong!)   on one server, and
--> 1.56   (correct!) on all other servers.


float8in() really just calls the operating system's strtod() function. 
I would test that one directly with a small C program.


It's also possible that different compiler options lead to different 
optimizations.

Re: Max_connection

[Daulat]

On Fri, Feb 18, 2022 at 2:39 PM Daulat  wrote:

> As I know, each PostgreSQL connection consumes RAM for managing the
> connection or the client using it. The more connections, the more RAM.
> I have a machine having 124 GB RAM and 16 CPU with SSD storage.
> Currently, 1200 connections are set but this value is low according to my
> application. hence, I am looking to know the criteria/thumb rule of
> deciding max_connection limit in postgres.
>
> I feel, we need to increase the shared buffer too while increasing the
> connections. I don't have idea still, we need to increase kernel.shmmax
> also?
>
>
> Thanks
>
>
> On Thu, Feb 17, 2022 at 10:47 PM Daulat  wrote:
>
>> Hello,
>>
>> Any one can suggest the criteria/thumb rule of deciding max_connection
>> limit in postgres.
>>
>> Thanks
>>
>>
>>

Re: 2 phase commit with FDW

[Mladen Gogala]

On 2/17/22 13:10, Mladen Gogala wrote:


Hi!

I am getting the following error when trying to PREPARE transaction 
which updates both local and foreign table:


2/17/22 12:48:00:657 EST] 0128 RegisteredRes E   WTRN0046E: An 
attempt by the transaction manager to call prepare on a transactional 
resource has resulted in an error. The error code was XAER_RMFAIL. The 
exception stack trace follows: org.postgresql.xa.PGXAException: Error 
preparing transaction. prepare xid={XidImpl: formatId(57415344), 
gtrid_length(36), bqual_length(54),

data(017f08cc3aea0001232c3afce8abf609478378ca6ac9d4bddbfea3e4efa55cf3017f08cc3aea0001232c3afce8abf609478378ca6ac9d4bddbfea3e4efa55cf300010002)}
    at 
org.postgresql.xa.PGXAConnection.prepare(PGXAConnection.java:365)


There are no errors is all the tables are local. It seems that 
postgres_fdw is incompatible with the XA protocol. Is there a way 
around it? If not, we will need yet another code change and another 
data source.


Regards

Is there anybody on this list who can tell me whether foreign tables can 
participate in 2-phase commit or not? My version is 14.2 on Oracle Linux 
8.5.


Regards


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

pg_upgrade from Postgresql-12 to Postgresql-13 fails with "Creating dump of database schemas postgres *failure*"

[Yoong S. Chow]

Hi I hope I send this to the right mailing list and able to get some help.


I was trying to perform `pg_upgrade` from Postgresql-12 to Postgresql-13
(after being advised that upgrading from Postgresql-12 to Postgresql-14 is
biting off much more than I could chew... ).

The `pg_upgrade --check` went through OK. But the actual `pg_upgrade`
always get error at the step of *Creating dump of database schemas* for
`postgres` database.

```

/usr/lib/postgresql/13/bin/pg_upgrade
--old-datadir=/bnas/pgdata/postgresql/12/main
--new-datadir=/bnas/pgdata/postgresql/13/main
--old-bindir=/usr/lib/postgresql/12/bin
--new-bindir=/usr/lib/postgresql/13/bin --old-options '-c
config_file=/etc/postgresql/12/main/postgresql.conf' --new-options '-c
config_file=/etc/postgresql/13/main/postgresql.conf' --check

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 system-defined composite types in user tables ok

Checking for reg* data types in user tables ok

Checking for contrib/isn with bigint-passing mismatch ok

Checking for presence of required libraries ok

Checking database user is the install user ok

Checking for prepared transactions ok

Checking for new cluster tablespace directories ok


*Clusters are compatible*

postgres@ual:~$ /usr/lib/postgresql/13/bin/pg_upgrade
--old-datadir=/bnas/pgdata/postgresql/12/main
--new-datadir=/bnas/pgdata/postgresql/13/main
--old-bindir=/usr/lib/postgresql/12/bin
--new-bindir=/usr/lib/postgresql/13/bin --old-options '-c
config_file=/etc/postgresql/12/main/postgresql.conf' --new-options '-c
config_file=/etc/postgresql/13/main/postgresql.conf'

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 system-defined composite types in user tables ok

Checking for reg* data types in user tables ok

Checking for contrib/isn with bigint-passing mismatch ok

Creating dump of global objects ok

Creating dump of database schemas

postgres

*failure*


Consult the last few lines of "pg_upgrade_dump_13427.log" for

the probable cause of the failure.

Failure, exiting

```

Here are the logs: pg_upgrade_server.log ,
pg_upgrade_dump_13427.log  and
tail_postgresql-12-2022-02-16_190344.log .

Additional information:

   1.

   An observation on `pg_upgrade --check`. I could not run actual
   `pg_upgrade` directly after the `--check` operation as I will always get
   some `server not shut down properly` error.
   2.

   I have PostGIS and a couple other audit/monitoring extensions installed
   on Postgresql-12. I have installed the similar Ubuntu PostGIS packages for
   Postgresql-13. Do I need to run `CREATE EXTENSIONS xxx` before performing
   the `pg_upgrade`?

Have been stuck with this for over a week now, and sincerely hope someone
could help me. TIA.

chow

Re: Strange results when casting string to double

[Carsten Klein]

On 18.02.2022 13:28, Peter Eisentraut wrote:

float8in() really just calls the operating system's strtod() function. I 
would test that one directly with a small C program.


It's also possible that different compiler options lead to different 
optimizations.


That's what I did. Here's my small C program: (nicht lachen *g*)

#include 
#include 

int main(int argc, char* argv[]) {

/* default string to convert */
char buf[10] = "1.56\0";

/* choose and print string to convert */
char* sval = argc > 1 ? argv[1] : buf;
printf("string value: %s\n", sval);

/* convert and print */
char* ptr;
double dval = strtod(sval, &ptr);
printf("double value: %.20f\n", dval);

return 0;
}

It works correctly on all these servers. Here's its output:

string value: 1.56
double value: 1.56005329

I didn't test different compiler options. However, PostgreSQL was always 
installed from official Ubuntu 14.04 repositories (getting the binaries, 
not the source packages), so all binaries should have been compiled with 
the same options.


Carsten

UUID type question

[Laura Smith]

I'm *sure* I've seen this discussed on here before - infact I'm worried it 
might even have been me who asked the question !

But I've searched the archives without luck, so here goes :

Is there anything inherently "special" about the UUID type ? i.e. if I store a 
UUID in a text is it "worse" ?  (I'm speaking here about performance & storage, 
obviously, not the fact that uuid enforces the formatting).

Thanks !

Laura

Re: UUID type question

[Dominique Devienne]

On Fri, Feb 18, 2022 at 3:06 PM Laura Smith
 wrote:
> Is there anything inherently "special" about the UUID type ? i.e. if I store 
> a UUID in a text is it "worse" ?

uuid is stored as 16 binary bytes.
Store it as text, and that's 36 chars (assuming UTF-8, double-that in
UTF-16, if that's possible in PG).
Or encode it as base-62 instead of hexa (base-16, with dashes), to use
only 22 chars as text (in UTF-8 or ASCII)

Re: UUID type question

[Dominique Devienne]

On Fri, Feb 18, 2022 at 3:24 PM Dominique Devienne  wrote:
> On Fri, Feb 18, 2022 at 3:06 PM Laura Smith
>  wrote:
> > Is there anything inherently "special" about the UUID type ? i.e. if I 
> > store a UUID in a text is it "worse" ?
>
> uuid is stored as 16 binary bytes.
> Store it as text, and that's 36 chars (assuming UTF-8, double-that in
> UTF-16, if that's possible in PG).
> Or encode it as base-62 instead of hexa (base-16, with dashes), to use
> only 22 chars as text (in UTF-8 or ASCII)

You might also be interested in KSUID, e.g. https://github.com/segmentio/ksuid

Re: UUID type question

[Laura Smith]

Sent with ProtonMail Secure Email.

--- Original Message ---

On Friday, February 18th, 2022 at 14:25, Dominique Devienne 
 wrote:

>
> You might also be interested in KSUID, e.g. https://github.com/segmentio/ksuid

I love KSUID. I use it in all new projects. ;-)

Re: Strange results when casting string to double

[Tom Lane]

Carsten Klein  writes:
> On 18.02.2022 13:28, Peter Eisentraut wrote:
>> float8in() really just calls the operating system's strtod() function. I 
>> would test that one directly with a small C program.

> That's what I did. Here's my small C program: (nicht lachen *g*)

Yeah, you said that upthread, which makes the whole thing pretty
baffling.  One possible explanation is that your small program got linked
against a different version of libc than what the Postgres backend is
using ("ldd" would help you check that, but given the age of the Postgres
installation, this seems plausible).  Beyond that it's hard to think
of any explanation other than hardware fault or corrupted executable.

regards, tom lane

Re: 2 phase commit with FDW

[Pavel Stehule]

Hi

pá 18. 2. 2022 v 14:24 odesílatel Mladen Gogala 
napsal:

> On 2/17/22 13:10, Mladen Gogala wrote:
>
> Hi!
>
> I am getting the following error when trying to PREPARE transaction which
> updates both local and foreign table:
>
> 2/17/22
>  12:48:00:657 EST] 0128 RegisteredRes E   WTRN0046E: An attempt by
> the transaction manager to call prepare on a transactional resource
>  has resulted in an error. The error code was XAER_RMFAIL. The exception
>  stack trace follows: org.postgresql.xa.PGXAException: Error preparing
> transaction. prepare xid={XidImpl: formatId(57415344), gtrid_length(36),
>  bqual_length(54),
>
> data(017f08cc3aea0001232c3afce8abf609478378ca6ac9d4bddbfea3e4efa55cf3017f08cc3aea0001232c3afce8abf609478378ca6ac9d4bddbfea3e4efa55cf300010002)}
>
> at org.postgresql.xa.PGXAConnection.prepare(PGXAConnection.java:365)
>
> There are no errors is all the tables are local. It seems that
> postgres_fdw is incompatible with the XA protocol. Is there a way around
> it? If not, we will need yet another code change and another data source.
>
> Regards
>
> Is there anybody on this list who can tell me whether foreign tables can
> participate in 2-phase commit or not? My version is 14.2 on Oracle Linux
> 8.5.
>

 I don't watch this topic, but it looks so FDW doesn't support 2PC yet

https://www.postgresql.fastware.com/blog/postgresql-14-and-beyond

Regards

Pavel

Regards
>
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>

Re: Strange results when casting string to double

[Carsten Klein]

On 18.02.2022 16:32, Tom Lane wrote:

Yeah, you said that upthread, which makes the whole thing pretty
baffling.  One possible explanation is that your small program got linked
against a different version of libc than what the Postgres backend is
using ("ldd" would help you check that, but given the age of the Postgres
installation, this seems plausible).  Beyond that it's hard to think
of any explanation other than hardware fault or corrupted executable.


Tom,

both PostgreSQL and my C program are linked to the same libc.so.6. Same 
path, same MD5 sum. Since libc is a Shared Object (so), both processes 
should really run the identical code. Am I missing something? I've 
written and compiled the small C program on the same old Ubuntu OS.


So, you're not aware of any ways this behavior could be achieved from 
within PostgreSQL? Something like a custom cast (actually, there is 
none) or something that could intercept string to double conversion? 
That would be something to look at closer. The question is: how would 
you implement such an evil database wide text to double conversion (just 
to kid users) if you had to?


PostgreSQL is up for more than 480 days on that server. I'm thinking of 
giving a restart of the database a try. However, there's a long running 
import taking place, so this will not happen before mid or end of next week.


Regards, Carsten

Re: Strange results when casting string to double

[Tom Lane]

Carsten Klein  writes:
> So, you're not aware of any ways this behavior could be achieved from 
> within PostgreSQL? Something like a custom cast (actually, there is 
> none) or something that could intercept string to double conversion? 

Well, you asserted that these installations are all alike ... but
sure, it's conceivable that somebody could've replaced the default
numeric -> float8 cast (i.e., numeric_float8()) with some other code
that does it a bit inaccurately.  There's only a pg_cast entry
connecting that function to those types.  Have you tried stepping
through things with a debugger, to see if numeric_float8 and float8in
are actually reached on the problematic system?

> That would be something to look at closer. The question is: how would 
> you implement such an evil database wide text to double conversion (just 
> to kid users) if you had to?

At the SQL level, your example does not involve text -> double.
The literal 1.56 is of type numeric.

regards, tom lane

Re: Strange results when casting string to double

[Peter J. Holzer]

On 2022-02-18 18:07:35 +0100, Carsten Klein wrote:
> both PostgreSQL and my C program are linked to the same libc.so.6. Same
> path, same MD5 sum. Since libc is a Shared Object (so), both processes
> should really run the identical code. Am I missing something? I've written
> and compiled the small C program on the same old Ubuntu OS.
[...]
> PostgreSQL is up for more than 480 days on that server. I'm thinking of
> giving a restart of the database a try. However, there's a long running
> import taking place, so this will not happen before mid or end of next week.

If there has been a glibc update (or a postgresql update) in those 480
days (Ubuntu 14.04 is out of its normal maintenance period but ESM is
still available) the running processes may well run different code than
a newly started program. So it could be a bug which has since been
fixed.

Another idea: It could be the case that something (maybe a bug in
postgres, maybe an extension, maybe even a random bit flip in memory)
changed the FP rounding mode within the postgres process, which would
affect all FP computations until the rounding mode is reset. That would
have to have happened in the master to affect the worker processes as
consistently as you are seeing. I don't know if it is even possible for
a non-standard rounding mode to persist for any length of time, but if
it is it would certainly account for weird rounding errors.

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: Strange results when casting string to double

[Tom Lane]

"Peter J. Holzer"  writes:
> Another idea: It could be the case that something (maybe a bug in
> postgres, maybe an extension, maybe even a random bit flip in memory)
> changed the FP rounding mode within the postgres process, which would
> affect all FP computations until the rounding mode is reset. That would
> have to have happened in the master to affect the worker processes as
> consistently as you are seeing. I don't know if it is even possible for
> a non-standard rounding mode to persist for any length of time, but if
> it is it would certainly account for weird rounding errors.

Hmm, that is a pretty interesting theory.  On a RHEL8 box, I find
that fesetround(FE_DOWNWARD) causes strtod("1.56", NULL) to return

  1.55983124

rather than the usual

  1.56005329

which seems to square with Carsten's symptom.

Postgres itself contains no fesetround calls, but if you want
to believe a random bit flip changed that mode, maybe that'd
account for it.  It'd certainly be interesting to find out
whether the problem persists after a postmaster restart.

[ wanders away wondering if the troublesome machine has ECC
memory ... ]

regards, tom lane

Re: pg_upgrade from Postgresql-12 to Postgresql-13 fails with "Creating dump of database schemas postgres *failure*"

[Laurenz Albe]

On Fri, 2022-02-18 at 13:17 +0800, Yoong S. Chow wrote:
> I was trying to perform `pg_upgrade` from Postgresql-12 to Postgresql-13
> (after being advised that upgrading from Postgresql-12 to Postgresql-14
> is biting off much more than I could chew... ).

That was bad advice.

> The `pg_upgrade --check` went through OK. But the actual `pg_upgrade`
> always get error at the step of Creating dump of database schemas for
> `postgres` database.

Buried in your logs I find

  free(): invalid pointer

Perhaps you have found a PostgreSQL bug.  Then it would be gread if you
could provide a reproducer.

But perhaps there is an extension installed that causes that error?

Does a plain pg_dumpall succeed?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: pg_upgrade from Postgresql-12 to Postgresql-13 fails with "Creating dump of database schemas postgres *failure*"

[Jeff Janes]

On Fri, Feb 18, 2022 at 8:35 AM Yoong S. Chow  wrote:

>
> Here are the logs: pg_upgrade_server.log ,
> pg_upgrade_dump_13427.log  and
> tail_postgresql-12-2022-02-16_190344.log .
>

>From that last log file we have:


   1. 2022-02-16 19:04:35 +08 [303982]: [4957-1] session=620cda42.4a36e,user
   =postgres,db=postgis_raster,app=pg_dump,client=[local] | LOG:
disconnection: session time: 0:00:01.101 user=postgres
database=postgis_raster
   host=[local]
   2. free(): invalid pointer


and then a little later:


   1. 2022-02-16 19:04:35 +08 [303791]: [7-1] session=620cda0f.4a2af,user=,
   db=,app=,client= | LOG:  server process (PID 303982) was terminated by
   signal 6: Aborted


Do you get any core files for 303982?  Do you have core dumps enabled?
What do you get if you run a manual pg_dump -s on db postgis_raster?

Cheers,

Jeff

>

Re: alter function/procedure depends on extension

[Bryn Llewellyn]

> david.g.johns...@gmail.com wrote:
> 
> It's not a member though; there's a different syntax for that (ALTER 
> EXTENSION name ADD member_object).  The differences are a bit subtle, but for 
> example making the function an extension member would change how pg_dump 
> treats it.

I read, and re-read, the six replies from Tom and David that came in yesterday 
(17-Feb) after my most recent turn in this thread. Here’s what I’ve concluded:

(1) Tom has ruled that there are no implementation bugs in this space. This 
means that all the functionality that the tests that I have done (and 
presumably could do) reflect the intended behavior.

(2) The reasons for deciding on at least some of this behavior are lost in the 
mists of time. But anyway, no implementation changes will be made.

(3) I, for one, found it very hard to build an overall, predictive, mental 
model from the documentation. But, then, you’ve seen (somewhat to my 
embarrassment) that I often have such difficulties. I’m grateful therefore, 
especially in these work-from-home days, for the answers that my questions to 
this list have received.

(4) The clue to the whole thing is the semantics of the LoV for "pg_depend. 
deptype" (see below) — but you all kept this a secret from me!
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — 

Here’s some detailed observations and responses to what Tom and David wrote:

> David: « An extension neither depends on nor is dependent upon its members. »

This must be a typo: "depends on" and "is dependent upon" mean the same. I’m 
guessing that this was meant: « An extension neither depends on nor is 
*depended* upon by its members. » If so, then it’s wrong. Yes: an extension 
doesn’t depend on its members. This query (which runs without error) shows that 
an extension depends upon only the schema nominated by "create extension… with 
schema…".

select nspname from
pg_namespace
where oid = (
  select refobjid
  from pg_catalog.pg_depend
  where objid = (
select oid
from pg_catalog.pg_extension
where extname::text = $1));

But no: the objects do depend upon the extension that creates them, as this 
query shows:

prepare dependents_on_extension(text) as
select
  p.proname::textas dependent_name,
  'subprogram'::text as dependent_type,
  e.extname::textas parent_name,
  'extension'::text  as parent_type,
  d.deptype::textas dependecy_type
from
  pg_catalog.pg_proc p
  inner join
  pg_catalog.pg_depend d
  on p.oid = d.objid
  inner join
  pg_catalog.pg_extension e
  on d.refobjid = e.oid
where e.extname::text = $1;

I tried it for the "cube" extension and it listed out the same function names 
that "\df the_schema.*" lists (where "the_schema" is the provided argument for 
the "with schema" clause of "create extension".

Moreover, it showed that the "dependency_type" is 'e' for the objects that 
creating the extension brings. And the section "52.18. pg_depend" says this 
about that value:

«
DEPENDENCY_EXTENSION (e) — The dependent object is a member of the extension 
that is the referenced object (see pg_extension). The dependent object can be 
dropped only via DROP EXTENSION on the referenced object. Functionally this 
dependency type acts the same as an INTERNAL dependency, but it's kept separate 
for clarity and to simplify pg_dump.
»

and it says this about "internal":

«
DEPENDENCY_INTERNAL (i) — The dependent object was created as part of creation 
of the referenced object, and is really just a part of its internal 
implementation. A direct DROP of the dependent object will be disallowed 
outright (we'll tell the user to issue a DROP against the referenced object, 
instead). A DROP of the referenced object will result in automatically dropping 
the dependent object whether CASCADE is specified or not…
»

Seems to me that the account of the "create extension" DDL would benefit from 
words to this effect and an x-ref to the account of "pg_depend"

In my tests, I installed the "cube" extension in schema "cube". Then I did this:

alter function cube.cube_cmp(cube.cube, cube.cube)depends on extension cube;

And then I repeated the query that I showed above. Now the results included 
these two rows:

   dependent_name   | dependent_type | parent_name | parent_type | 
dependecy_type 
++-+-+
 cube_cmp   | subprogram | cube| extension   | e
 cube_cmp   | subprogram | cube| extension   | x

Here's what the doc says about the new result:

«
DEPENDENCY_AUTO_EXTENSION (x) — The dependent object is not a member of the 
extension that is the referenced object (and so it should not be ignored by 
pg_dump), but it cannot function without the extension and should be 
auto-dropped if the extension is. The dependent object may be dropped on its 
own as well. Functionally this dependency type acts the same as an AUTO 
dependency, but it's kept separate for clarity and to simplify pg_du

Re: alter function/procedure depends on extension

[David G. Johnston]

On Fri, Feb 18, 2022 at 6:16 PM Bryn Llewellyn  wrote:

>
> (4) The clue to the whole thing is the semantics of the LoV for
> "pg_depend. deptype" (see below) — but you all kept this a secret from me!
>

I didn't even think to look at the system catalogs for guidance in this
kind of thing.  The catalogs are not what I consider end-user facing
documentation; I'd want the core documentation (SQL Command Reference and
exposition chapters for different features) to be sufficient for someone to
understand how these things work.  Now, I would probably have been a bit
less certain of myself had I gone and looked at the catalogs early on.
Seeing the specification for DEPENDENCY_AUTO (a) would have reset my
internal consistency trigger.


> David wrote:
>
> This must be a typo: "depends on" and "is dependent upon" mean the same.
> I’m guessing that this was meant: « An extension neither depends on nor is
> *depended* upon by its members. » If so, then it’s wrong. Yes: an extension
> doesn’t depend on its members. This query (which runs without error) shows
> that an extension depends upon only the schema nominated by
> "create extension… with schema…".
>

> But no: the objects do depend upon the extension that creates them, as
> this query shows:
>
>
Yeah, I seem to have had a brain fade there.
David J.