More than one Cluster on single server (single instance)

[Daulat]

Hello Team,

We are planning to create multiple clusters on a single server (single
instance) with PostgreSQL V.10 to run multiple applications.

I don't know if it is a good idea to use a single machine to run n clusters
but we are looking to minimize the cost of servers and other resources.

Please share your thoughts on this approach?

How can we create multiple clusters under a single postgres version?

How can we identify/allocate the resources to parameters like
shared_buffers, effective_cache_size, work_mem etc in case of multiple
clusters?

Thanks

Re: More than one Cluster on single server (single instance)

[Guillaume Lelarge]

Hi,

Le lun. 18 juil. 2022 à 09:55, Daulat  a écrit :

>
> Hello Team,
>
> We are planning to create multiple clusters on a single server (single
> instance) with PostgreSQL V.10 to run multiple applications.
>
>
Planning to create a database cluster on a release 10 sounds already like a
bad idea, as release 10 will be maintained till november. After November
2022, no more bug fixes for this release. You really should use a more
recent release.

I don't know if it is a good idea to use a single machine to run n clusters
> but we are looking to minimize the cost of servers and other resources.
>
> Please share your thoughts on this approach?
>
>
It would make things easier for you to use a single cluster, and as many
databases as you want in this cluster.


> How can we create multiple clusters under a single postgres version?
>
>
Mostly depends on your operating system, and on how you installed
PostgreSQL in the first place. The usual answer would be to run initdb for
each cluster.

How can we identify/allocate the resources to parameters like
> shared_buffers, effective_cache_size, work_mem etc in case of multiple
> clusters?
>
>
That's the hard part if you have many clusters. You will have to determine
by yourself how much memory each cluster will need. If you use only one
cluster (and many databases in it), you would use the usual formula to set
these parameters.

Regards.


-- 
Guillaume.

Re: More than one Cluster on single server (single instance)

[Mladen Gogala]

On 7/18/22 03:55, Daulat wrote:

Please share your thoughts on this approach?

How can we create multiple clusters under a single postgres version?

You can use Docker images.


How can we identify/allocate the resources to parameters like 
shared_buffers, effective_cache_size, work_mem etc in case of multiple 
clusters?


Thanks


Each container can be adjusted to have its own memory and parameter 
file. Personally, I don't like that approach. I think that separating 
data into separate databases is enough separation even for the most 
fervent separatists (pun with the name of political movements is purely 
accidental).


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

Re: More than one Cluster on single server (single instance)

[Ron]

On 7/18/22 02:55, Daulat wrote:


Hello Team,

We are planning to create multiple clusters on a single server (single 
instance) with PostgreSQL V.10 to run multiple applications.


Version (9,6, 10, 111, 12...) does not matter.

I don't know if it is a good idea to use a single machine to run n 
clusters but we are looking to minimize the cost of servers and other 
resources.


That depends on your circumstances.


Please share your thoughts on this approach?


It allows you to do PITR backups without doing all-or-nothing restores.


How can we create multiple clusters under a single postgres version?


Separate $PGDATA directories, and separate postgresql.conf files (with each 
config file specifying a different port number: 5432, 5433, 5434, etc)




How can we identify/allocate the resources to parameters like 
shared_buffers, effective_cache_size, work_mem etc in case of multiple 
clusters?


Very trickily.

--
Angular momentum makes the world go 'round.

Re: More than one Cluster on single server (single instance)

[Ron]

On 7/18/22 04:46, Guillaume Lelarge wrote:

Hi,

[snip]
It would make things easier for you to use a single cluster, and as many 
databases as you want in this cluster.


It would make *PITR* restoration *of a single database* (or small set of 
databases) impossible (since such backups -- and therefore restores -- are 
always binary "whole cluster").


--
Angular momentum makes the world go 'round.

Re: PostgreSQL 14.4 ERROR: out of memory issues

[Aleš Zelený]

Hello,




čt 14. 7. 2022 v 23:31 odesílatel Aleš Zelený 
napsal:

>
>
> Dne čt 14. 7. 2022 23:11 uživatel Tomas Vondra <
> tomas.von...@enterprisedb.com> napsal:
>
>>
>>
>> On 7/14/22 21:25, Aleš Zelený wrote:
>> >
>> > st 13. 7. 2022 v 2:20 odesílatel Michael Paquier > > > napsal:
>> >
>> > On Mon, Jul 11, 2022 at 10:50:23AM +0200, Aleš Zelený wrote:
>> > > So far, it has happened three times (during a single week) from
>> > the 14.3 ->
>> > > 14.4 upgrade, before 14.4 we haven't suffered from such an issue.
>> > >
>> > > Questions:
>> > > 1)  Can we safely downgrade from 14.4 to 14.3 by shutting down the
>> > instance
>> > > and reinstalling 14.3 PG packages (to prove, that the issue
>> > disappear)?
>> > > 2) What is the best way to diagnose what is the root cause?
>> >
>> > Hmm.  14.4 has nothing in its release notes that would point to a
>> > change in the vacuum or autovacuum's code paths:
>> > https://www.postgresql.org/docs/14/release-14-4.html#id-1.11.6.5.4
>> > > >
>> >
>> > There is nothing specific after a look at the changes as of, and I
>> am
>> > not grabbing anything that would imply a change in memory context
>> > handling either:
>> > `git log --stat REL_14_3..REL_14_4`
>> > `git diff REL_14_3..REL_14_4 -- *.c`
>> >
>> > Saying that, you should be able to downgrade safely as there are no
>> > changes in WAL format or such that would break things.  Saying that,
>> > the corruption issue caused by CONCURRENTLY is something you'd still
>> > have to face.
>> >
>> >
>> > Thanks, good to know that, we can use it for a test case, since we
>> > already hit the CONCURRENTLY bug on 14.3.
>> >
>> > > 2022-07-02 14:48:07 CEST [3930]: [3-1] user=,db=,host=,app=
>> > ERROR:  out of
>> > > memory
>> > > 2022-07-02 14:48:07 CEST [3930]: [4-1] user=,db=,host=,app=
>> > DETAIL:  Failed
>> > > on request of size 152094068 in memory context
>> > "TopTransactionContext".
>> > > 2022-07-02 14:48:07 CEST [3930]: [5-1] user=,db=,host=,app=
>> CONTEXT:
>> > >  automatic vacuum of table "prematch.replication.tab_queue_tmp"
>> >
>> > This is the interesting part.  Do you happen to use logical
>> > replication in a custom C++ plugin?
>> >
>> >
>> > We are using logical replication to other instances (pg_output) and
>> > decoderbufs
>> > https://github.com/debezium/postgres-decoderbufs
>> >  for other
>> applications.
>> >
>>
>> This is probably just a red herring - std:bad_alloc is what the process
>> that runs into the overcommit limit gets. But the real issue (e.g.
>> memory leak) is likely somewhere else - different part of the code,
>> different process ...
>>
>> > ...
>> >
>> > Checking the RssAnon from proc/pid/status I've found some points where
>> > RssAnon memory usage grew very steep for a minute, but no "suspicious"
>> > queries/arguments were found in the instance logfile.
>> >
>> > Any hint, on how to get the root cause would be appreciated since so far
>> > I've failed to isolate the issue reproducible testcase.
>> > At least I hope that looking for the RssAnon process memory is an
>> > appropriate metric, if not, let me know and I'll try to update the
>> > monitoring to get the root cause.
>> >
>> > I can imagine a workaround with client application regular reconnect...,
>> > but u to 14.3 it works, so I'd like to fix the issue either on our
>> > application side or at PG side if it is a PG problem.
>> >
>>
>> I think it's be interesting to get memory context stats from the
>> processes consuming a lot of memory. If you know which processes are
>> suspect (and it seems you know, bacause if a reconnect helps it's the
>> backend handling the connection), you can attach a debugger and do
>>
>>$ gdb -p $PID
>>call MemoryContextStats(TopMemoryContext)
>>
>> which will log info about memory contexts, just like autovacuum.
>> Hopefully that tells us memory context is bloated, and that might point
>> us to particular part of the code.
>>
>
after some time, I've found a process consuming over 1GB of memory"
-bash-4.2$ grep RssAnon /proc/*/status | sort -nk2 | tail
/proc/12156/status:RssAnon:  902984 kB
/proc/8303/status:RssAnon:  912848 kB
/proc/31187/status:RssAnon:  920424 kB
/proc/16469/status:RssAnon:  922864 kB
/proc/13769/status:RssAnon:  925824 kB
/proc/15449/status:RssAnon:  932452 kB
/proc/26144/status:RssAnon:  936892 kB
/proc/22570/status:RssAnon:  937920 kB
/proc/2910/status:RssAnon: 1039124 kB
/proc/17048/status:RssAnon: 1053952 kB

Here are memory contexts for PID 17048:

TopMemoryContext: 422592 total in 14 blocks; 42536 free (169 chunks);
380056 used
  pgstat TabStatusArray lookup hash table: 16384 total in 2 blocks; 7520
free (3 chunks); 8864 used
  PL/pgSQL function: 16384 total in 2 blocks; 5832 free 

Re: PostgreSQL 14.4 ERROR: out of memory issues

[Tom Lane]

=?UTF-8?B?QWxlxaEgWmVsZW7DvQ==?=  writes:
> after some time, I've found a process consuming over 1GB of memory"
> -bash-4.2$ grep RssAnon /proc/*/status | sort -nk2 | tail
> /proc/17048/status:RssAnon: 1053952 kB

> Here are memory contexts for PID 17048:

> TopMemoryContext: 422592 total in 14 blocks; 42536 free (169 chunks);
> 380056 used
...
> Grand total: 14312808 bytes in 4752 blocks; 3920880 free (1043 chunks);
> 10391928 used

OK, so PG's normal memory consumption is only ~14MB.  Where'd the
rest of it go?

> -bash-4.2$ pmap 17048
...
> 02d93000 838092K rw---   [ anon ]
> 7fd999777000 180232K rw---   [ anon ]
> 7fd9a8d75000  32772K rw---   [ anon ]
...
Probably the 838M chunk is shared memory?  Is that within hailing
distance of your shared_buffers setting?
...
> 7fd9b0551000 10827040K rw-s- zero (deleted)
...
And here we have the culprit, evidently ... but what the dickens
is it?  I can't think of any mechanism within Postgres that would
create such an allocation.

regards, tom lane

postgis

[Marc Millas]

Hi,

I would like to install postgis 3.04 on a debian 11.

digging into various web sites, I didnt found the name of that packet.

can someone help ?

thanks

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

Re: postgis

[Adrian Klaver]

On 7/18/22 10:08, Marc Millas wrote:

Hi,

I would like to install postgis 3.04 on a debian 11.

digging into various web sites, I didnt found the name of that packet.

can someone help ?


Where did you install Postgres from, the Debian or the Postgres 
community repos?




thanks

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 




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

Re: postgis

[Adrian Klaver]

On 7/18/22 10:08, Marc Millas wrote:

Hi,

I would like to install postgis 3.04 on a debian 11.

digging into various web sites, I didnt found the name of that packet.

can someone help ?


Should have added to previous response, what version of Postgres?


thanks

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 




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

Re: PostgreSQL 14.4 ERROR: out of memory issues

[Aleš Zelený]

po 18. 7. 2022 v 16:25 odesílatel Tom Lane  napsal:

> =?UTF-8?B?QWxlxaEgWmVsZW7DvQ==?=  writes:
> > after some time, I've found a process consuming over 1GB of memory"
> > -bash-4.2$ grep RssAnon /proc/*/status | sort -nk2 | tail
> > /proc/17048/status:RssAnon: 1053952 kB
>
> > Here are memory contexts for PID 17048:
>
> > TopMemoryContext: 422592 total in 14 blocks; 42536 free (169 chunks);
> > 380056 used
> ...
> > Grand total: 14312808 bytes in 4752 blocks; 3920880 free (1043 chunks);
> > 10391928 used
>
> OK, so PG's normal memory consumption is only ~14MB.  Where'd the
> rest of it go?
>
> > -bash-4.2$ pmap 17048
> ...
> > 02d93000 838092K rw---   [ anon ]
> > 7fd999777000 180232K rw---   [ anon ]
> > 7fd9a8d75000  32772K rw---   [ anon ]
>

and a few hours later it even grew:

-bash-4.2$ cat 20220718_200230.pmap.17048.log | grep anon
00db3000200K rw---   [ anon ]
02d15000504K rw---   [ anon ]
02d93000 934476K rw---   [ anon ]
7fd989776000 311304K rw---   [ anon ]
7fd9a8d75000  32772K rw---   [ anon ]
7fd9acb65000 20K rw---   [ anon ]
7fd9affc1000372K rw---   [ anon ]

>From previous observation I know, that the process RssAnon memory grew over
time, sometimes there are some steps. Still, generally, the growth is
linear until the process finishes or we run out of memory, and the cluster
is reinitialized by the postmaster.
No such behavior on older versions until 14.3 (including).

...
> Probably the 838M chunk is shared memory?  Is that within hailing
> distance of your shared_buffers setting?
>

postgres=# show shared_buffers ;
 shared_buffers

 10GB
(1 row)

...
> > 7fd9b0551000 10827040K rw-s- zero (deleted)
>

These 10GB matches 10GB configured as shared buffers.


> ...
> And here we have the culprit, evidently ... but what the dickens
> is it?  I can't think of any mechanism within Postgres that would
> create such an allocation.
>
> regards, tom lane
>

Kind regards
Ales Zeleny

Re: postgis

[Marc Millas]

Hi,
postgres 12.
I may use whatever repo.

I install postgres from postgresql.org...



Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Mon, Jul 18, 2022 at 7:15 PM Adrian Klaver 
wrote:

> On 7/18/22 10:08, Marc Millas wrote:
> > Hi,
> >
> > I would like to install postgis 3.04 on a debian 11.
> >
> > digging into various web sites, I didnt found the name of that packet.
> >
> > can someone help ?
>
> Should have added to previous response, what version of Postgres?
> >
> > thanks
> >
> > Marc MILLAS
> > Senior Architect
> > +33607850334
> > www.mokadb.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: PostgreSQL 14.4 ERROR: out of memory issues

[Pavel Stehule]

po 18. 7. 2022 v 20:26 odesílatel Aleš Zelený 
napsal:

>
> po 18. 7. 2022 v 16:25 odesílatel Tom Lane  napsal:
>
>> =?UTF-8?B?QWxlxaEgWmVsZW7DvQ==?=  writes:
>> > after some time, I've found a process consuming over 1GB of memory"
>> > -bash-4.2$ grep RssAnon /proc/*/status | sort -nk2 | tail
>> > /proc/17048/status:RssAnon: 1053952 kB
>>
>> > Here are memory contexts for PID 17048:
>>
>> > TopMemoryContext: 422592 total in 14 blocks; 42536 free (169 chunks);
>> > 380056 used
>> ...
>> > Grand total: 14312808 bytes in 4752 blocks; 3920880 free (1043 chunks);
>> > 10391928 used
>>
>> OK, so PG's normal memory consumption is only ~14MB.  Where'd the
>> rest of it go?
>>
>> > -bash-4.2$ pmap 17048
>> ...
>> > 02d93000 838092K rw---   [ anon ]
>> > 7fd999777000 180232K rw---   [ anon ]
>> > 7fd9a8d75000  32772K rw---   [ anon ]
>>
>
> and a few hours later it even grew:
>
> -bash-4.2$ cat 20220718_200230.pmap.17048.log | grep anon
> 00db3000200K rw---   [ anon ]
> 02d15000504K rw---   [ anon ]
> 02d93000 934476K rw---   [ anon ]
> 7fd989776000 311304K rw---   [ anon ]
> 7fd9a8d75000  32772K rw---   [ anon ]
> 7fd9acb65000 20K rw---   [ anon ]
> 7fd9affc1000372K rw---   [ anon ]
>
> From previous observation I know, that the process RssAnon memory grew
> over time, sometimes there are some steps. Still, generally, the growth is
> linear until the process finishes or we run out of memory, and the cluster
> is reinitialized by the postmaster.
> No such behavior on older versions until 14.3 (including).
>
> ...
>> Probably the 838M chunk is shared memory?  Is that within hailing
>> distance of your shared_buffers setting?
>>
>
> postgres=# show shared_buffers ;
>  shared_buffers
> 
>  10GB
> (1 row)
>
> ...
>> > 7fd9b0551000 10827040K rw-s- zero (deleted)
>>
>
> These 10GB matches 10GB configured as shared buffers.
>
>
>> ...
>> And here we have the culprit, evidently ... but what the dickens
>> is it?  I can't think of any mechanism within Postgres that would
>> create such an allocation.
>>
>
I checked code, and there is lot of usage of malloc function.

static void *
GenerationAlloc(MemoryContext context, Size size)
{
<-->GenerationContext *set = (GenerationContext *) context;
<-->GenerationBlock *block;
<-->GenerationChunk *chunk;
<-->Size<--><-->chunk_size = MAXALIGN(size);
<-->Size<--><-->required_size = chunk_size + Generation_CHUNKHDRSZ;

<-->/* is it an over-sized chunk? if yes, allocate special block */
<-->if (chunk_size > set->allocChunkLimit)
<-->{
<--><-->Size<--><-->blksize = required_size + Generation_BLOCKHDRSZ;

<--><-->block = (GenerationBlock *) malloc(blksize);
<--><-->if (block == NULL)
<--><--><-->return NULL;

Cannot be some memory lost in this allocation?

Regards

Pavel



>
>> regards, tom lane
>>
>
> Kind regards
> Ales Zeleny
>

Re: PostgreSQL 14.4 ERROR: out of memory issues

[Tom Lane]

=?UTF-8?B?QWxlxaEgWmVsZW7DvQ==?=  writes:
> postgres=# show shared_buffers ;
>  shared_buffers
> 
>  10GB
> (1 row)

Oh!  In that case, there is absolutely nothing to see here.
This chunk:

> 7fd9b0551000 10827040K rw-s- zero (deleted)

must be your shared-memory region, and it's accounting for just
about all of the process's memory space.  There is no reason
to suspect a leak.

I think you've been fooled by the fact that "top" and some other
tools frequently don't report a shared-memory page as part of a
process's usage until that process touches that particular page.
Thus, the reported memory usage of a Postgres backend will grow
over time as it randomly happens to touch different buffers within
the shared buffer arena.  That does not constitute a leak, but
it does make such tools next door to useless for detecting
actual leaks :-(.  You can only believe there's a leak if the
reported usage doesn't level off after reaching the vicinity of
your shared memory size.

So back to why you're getting these out-of-memory failures:
we still don't have much info about that.  I wonder whether
there is something watching the total usage reported by "top"
and taking action based on that entirely-misleading number.
The kernel itself should be aware that there's no extra memory
pressure from N backends all using the same shared memory
segment, but it can be hard for outside tools to know that.

At this point I suspect that PG 14.3 vs 14.4 is a red herring,
and what you should be looking into is what else you updated
at about the same time, particularly in the line of container
management tools or the like (if you use any).

regards, tom lane

pg_receivewal/xlog to ship wal to cloud

[neslişah demirci]

Hi all,

Wondering if anyone has any experience of using pg_receivewal/xlog to ship
wal files to GCP/S3?

Could you please share ideas or issues you had?

Thanks.

Re: pg_receivewal/xlog to ship wal to cloud

[neslişah demirci]

Or downloading from cloud experience?

neslişah demirci , 18 Tem 2022 Pzt, 15:55
tarihinde şunu yazdı:

> Hi all,
>
> Wondering if anyone has any experience of using pg_receivewal/xlog to ship
> wal files to GCP/S3?
>
> Could you please share ideas or issues you had?
>
> Thanks.
>


-- 
Best,
Neslisah

Re: pg_receivewal/xlog to ship wal to cloud

[Alan Hodgson]

On Mon, 2022-07-18 at 15:55 -0400, neslişah demirci wrote:
> Hi all, 
> 
> Wondering if anyone has any experience of using pg_receivewal/xlog
> to ship wal files to GCP/S3?
> 

I use archive_command to send WAL to S3. It works fine. I do gzip
them before uploading, as they are usually pretty compressible.

I use a lifecycle rule on the bucket to automatically delete ones
older than I might need.

It's not the fastest process in the world, and replay on our
reporting server does occasionally fall behind, but I think that's
true of WAL replay in general as a serialized process.

Re: postgis

[Adrian Klaver]

On 7/18/22 11:48, Marc Millas wrote:

Hi,
postgres 12.
I may use whatever repo.

I install postgres from postgresql.org...


You pretty much need to install from the same repo for PostGIS. 
Otherwise you will most likely run into compatibility issues.


From here:

https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgis/

I only see 3.0.1 and 3.0.3 as options.

A more complete answer will need to come from one of the maintainers.





Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 






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

Re: equivalent thing of mtr in mysql

[merryok]

I've read the doc, and it doesn't help too much.
Finally I've found START_CRIT_SECTION and END_CRIT_SECTION. It's like mtr.start(), mtr.commit() in mysql. May I ask why many places are wrapped into START_CRIT_SECTION/END_CRIT_SECTION during a single dml operation ?

And if Assert(CritSectionCount > 0) isn't satisfied (CritSectionCount need't be protected ?), PG server will panic and exit ? If so, what's the probability of that ?





发自我的小米手机在 Adrian Klaver ，2022年7月16日 23:30写道：On 7/15/22 18:31, merryok wrote:
> I'm sorry, first time to post a thread. mtr is short for mini-transaction.
> About mtr in mysql, we can refer to 
> https://dev.mysql.com/doc/refman/8.0/en/glossary.html 
>  or
> https://dev.mysql.com/blog-archive/mysql-8-0-new-lock-free-scalable-wal-design/ 
>  
> 
> 
> 
> So an insert in mysql,  wrapped in a user transaction, may result in 
> multiple mini-transactions, one is responsible for writing redo for 
> undo, the other one for writing redo for clustered index/secondary 
> index, and so on. Each mtr may modify multiple pages,  whose redo logs 
> are guaranteed by mtr to be written into disk or none of them are written.
> 

Read this:

https://www.postgresql.org/docs/current/wal.html

and see if it answers your questions.


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

Getting the table ID

[Igor Korot]

Hi, guys,

In the database theory each table is identified as "schema_name"."table_name".

When I tried to look at how to get the table id inside the PostgreSQL,
I saw that I needed to look at the pg_class table.

SELECT oid FROM pg_class WHERE relname = "table_name";

However that query will give a non-unique table id (see the first sentence).

So how do I get the table id based on the "schema_name.table_name"?

There is a pg_namespace table - is this where the schema should come from?
If yes - how?
Looking at that table I don't see any unique fields...
Or is this something that is hidden?

In fact I'm trying to run following query:

SELECT c.oid FROM pg_class c, pg_namespace nc WHERE nc.oid =
c.relnamespace AND c.relname = ? AND nc.nspname = ?;

from my ODBC based program, but it returns 0 rows on SQLFetch.

I know PostgreSQL does not use '?' for query parameters
but I thought that since its an ODBC everything should work.

Nevertheless, all bindings were successful, but now rows are returned.

Is this query correct?

Thank you.

Re: Getting the table ID

[David G. Johnston]

On Mon, Jul 18, 2022 at 8:11 PM Igor Korot  wrote:

>
> There is a pg_namespace table - is this where the schema should come from?
> If yes - how?
> Looking at that table I don't see any unique fields...
> Or is this something that is hidden?
>

Catalogs don't have all of the same metadata that user-space tables have.

"namespace" is the historical and internally used label for what we present
to users as "schema"

>
> In fact I'm trying to run following query:
>
> SELECT c.oid FROM pg_class c, pg_namespace nc WHERE nc.oid =
> c.relnamespace AND c.relname = ? AND nc.nspname = ?;
>

Then either relname or nspname caused the records to filter out because the
join part of that where clause is correct.

I suggest you experiment using psql and literals so you can provide both
actual queries and results more easily.  The whole self-contained script
thing is very helpful.

You can also learn quite a bit by echoing queries in psql then using the
various description metacommands that query these same catalogs - thus
echoing the queries psql itself uses to answer this same question.

David J.

Re: equivalent thing of mtr in mysql

[Christophe Pettus]

> On Jul 18, 2022, at 20:03, merryok  wrote:
> 
> I've read the doc, and it doesn't help too much.
> Finally I've found START_CRIT_SECTION and END_CRIT_SECTION. It's like 
> mtr.start(), mtr.commit() in mysql. May I ask why many places are wrapped 
> into START_CRIT_SECTION/END_CRIT_SECTION during a single dml operation ?

A PostgreSQL critical section is not the equivalent of MySQL InnoDB 
mini-transaction.

A critical section in PostgreSQL is a section of code that needs to run without 
interruption to avoid corruption of internal in-memory data structures.

PostgreSQL doesn't have a direct equivalent of a MySQL mini-transaction.  When 
WAL information is created by a statement, it's stored in the WAL buffers, and 
then flushed to disk by the WAL writer (to a first approximation).  There's no 
special operation that groups pages together for atomic writes; that's done by 
the underlying file system flush operation.

> And if Assert(CritSectionCount > 0) isn't satisfied (CritSectionCount need't 
> be protected ?), PG server will panic and exit ? If so, what's the 
> probability of that ?

If it occurs, it indicates a bug in PostgreSQL.  It is *extremely* infrequent 
(as in, you can go years without seeing one; I can't remember the last time I 
did).

Migrating from Oracle - Implicit Casting Issue

[Karthik K L V]

Hi Team,

We are migrating from Oracle 12c to Aurora Postgres 13 and running into
implicit casting issues.

Oracle is able to implicitly cast the bind value of prepared statements
executed from the application to appropriate type - String -> Number,
String -> Date, Number -> String etc. when there is a mismatch b/w java
data type and the column Datatype.

For example: If the Datatype of a Column is defined as Number and the
application sends the bind value as a String (with single quotes in the
query) - Oracle DB is able to implicitly cast to Number and execute the
query and return the results.

The same is not true with Postgres and we are getting below exception

*org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint
= character varying*
*Hint: No operator matches the given name and argument types. You might
need to add explicit type casts..*

We found a Postgres Driver property - stringtype=unspecified which appears
to solve this problem and have the following questions.
https://jdbc.postgresql.org/documentation/83/connect.html

Could you please let us know the following?

Q1) Will configuring this stringtype property introduce overhead on
Postgres leading to Performance issues
Q2)Does setting this attribute have any other implications on the data in
the DB.
Q3)Is there any plan to deprecate / stop supporting this attribute in
future Aurora Postgres releases.


-- 
Karthik klv

Migrating from Oracle - Implicit Casting Issue

[David G. Johnston]

On Monday, July 18, 2022, Karthik K L V  wrote:

> Hi Team,
>
> We are migrating from Oracle 12c to Aurora Postgres 13 and running into
> implicit casting issues.
>
> Oracle is able to implicitly cast the bind value of prepared statements
> executed from the application to appropriate type - String -> Number,
> String -> Date, Number -> String etc. when there is a mismatch b/w java
> data type and the column Datatype.
>
> For example: If the Datatype of a Column is defined as Number and the
> application sends the bind value as a String (with single quotes in the
> query) - Oracle DB is able to implicitly cast to Number and execute the
> query and return the results.
>
> The same is not true with Postgres and we are getting below exception
>
> *org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint
> = character varying*
> *Hint: No operator matches the given name and argument types. You might
> need to add explicit type casts..*
>
> We found a Postgres Driver property - stringtype=unspecified which appears
> to solve this problem and have the following questions.
> https://jdbc.postgresql.org/documentation/83/connect.html
>
> Could you please let us know the following?
>
> Q1) Will configuring this stringtype property introduce overhead on
> Postgres leading to Performance issues
> Q2)Does setting this attribute have any other implications on the data in
> the DB.
> Q3)Is there any plan to deprecate / stop supporting this attribute in
> future Aurora Postgres releases.
>
>
That setting is not recognized by the server in any way, it is a driver
concern only.  IIUC it makes the Java Driver behave in a way consistent
with the expectations of the server since by leaving the supplied type info
undeclared the server can use its own logic.  If it works for you I say use
it, it will be less problematic than methodically fixing your queries up
front.  Though if there are some that show to be bottlenecks getting the
type info correct may prove to make a difference in some situations.

David J.

Re: Proposed Translations of Updated Code of Conduct Policy

[Stefan Fercot]

Hi,

There's a typo in the french translation. "consécutis" should be "consécutifs".

And per consistency, I'd suggest to not mix "mandat" and "termes" to speak 
about the same thing. I believe it would be better to replace "(3) termes 
consécutis" by "(3) mandats consécutifs".

--
Kind Regards,

Stefan FERCOT
EDB: [https://www.enterprisedb.com](https://www.enterprisedb.com/)

--- Original Message ---
Le samedi 16 juillet 2022 à 7:24 PM, Lucie Šimečková 
 a écrit :

> Hello all,
>
> Following the recent update of the English version of the Code of Conduct 
> that clarified the term limits of CoC committee members, the PostgreSQL 
> Community Code of Conduct Committee has solicited translations of the updated 
> CoC.
>
> The English version of the Policy is at:
>
> https://www.postgresql.org/about/policies/coc/
>
> The following translations were contributed by:
>
> - German translation provided by Andreas Scherbaum
> - French translation provided by Guillaume Lelarge
> - Hebrew translation provided by Michael Goldberg
> - Italian translation provided by Federico Campoli and reviewed by Tommaso 
> Bartoli
> - Russian translation provided by Alexander Lakhin
> - Chinese translation provided by Bo Peng
> - Japanese translation provided by Tatsuo Ishii
>
> The proposed translations are attached as plain text and PDF files to this 
> message.
>
> If you have any comments or suggestions for the proposed translations, please 
> bring them to our attention no later than 11:59 PM UTC on Sunday, July 24, 
> 2022. What time is that in my time zone? 
> https://www.timeanddate.com/worldclock/converter.html?iso=20220724T23&p1=1440
>
> Thank you!
>
> Lucie Šimečková
>
> PostgreSQL Community Code of Conduct Committee Member

Re: Proposed Translations of Updated Code of Conduct Policy

[Guillaume Lelarge]

Hi,

Le mar. 19 juil. 2022 à 08:49, Stefan Fercot 
a écrit :

> Hi,
>
> There's a typo in the french translation. "consécutis" should be "
> consécutifs".
>
> And per consistency, I'd suggest to not mix "mandat" and "termes" to speak
> about the same thing. I believe it would be better to replace "(3) termes
> consécutis" by "(3) mandats consécutifs".
>
>
Agreed on both.


> --
> Kind Regards,
> Stefan FERCOT
> EDB: https://www.enterprisedb.com
>
> --- Original Message ---
> Le samedi 16 juillet 2022 à 7:24 PM, Lucie Šimečková <
> luciesimeck...@outlook.com> a écrit :
>
> Hello all,
>
>
>
> Following the recent update of the English version of the Code of Conduct
> that clarified the term limits of CoC committee members, the PostgreSQL
> Community Code of Conduct Committee has solicited translations of the
> updated CoC.
>
>
>
> The English version of the Policy is at:
>
> https://www.postgresql.org/about/policies/coc/
>
> The following translations were contributed by:
>
>
>
>- German translation provided by Andreas Scherbaum
>- French translation provided by Guillaume Lelarge
>- Hebrew translation provided by Michael Goldberg
>- Italian translation provided by Federico Campoli and reviewed by
>Tommaso Bartoli
>- Russian translation provided by Alexander Lakhin
>- Chinese translation provided by Bo Peng
>- Japanese translation provided by Tatsuo Ishii
>
>
>
> The proposed translations are attached as plain text and PDF files to this
> message.
>
> If you have any comments or suggestions for the proposed translations,
> please bring them to our attention no later than 11:59 PM UTC on Sunday, July
> 24, 2022. What time is that in my time zone?
> https://www.timeanddate.com/worldclock/converter.html?iso=20220724T23&p1=1440
>
> Thank you!
>
> Lucie Šimečková
>
> PostgreSQL Community Code of Conduct Committee Member
>
>
>
>
>

-- 
Guillaume.