first order by then partition by x < a fixed value.

[jian he]

This question (https://stackoverflow.com/q/72975669/15603477) is fun.
DB fiddle link:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=36d685ad463831877ae70361be2cfa3b

account
size   idname 1001 John 2002 Mary 3003
Jane 4004 Anne1005 Mike 6006 Joanne

Then expected output: account group  size   idname 1   100
1 John 1   2002 Mary 1   3003 Jane 2
4004 Anne2   1005 Mike 3   6006 Joanne

Idea is fixed order by id then cumulative sum. if  <=600 then grouped
together using the same row_number.

But I imagine this kind question has been solved many times.
Current posted solution uses a recursive query, which is really hard for
me.
Is there any simple or more intuitive way to solve this kind of problem?
I can write some customized aggregate function to aggregate stops at 600.

I can get the following result, So what's the next step?

+--+++---+-+
| size | id |  name  | capped_at_600 | large_or_eq_600 |
+--+++---+-+
|  100 |  1 | John   |   100 | f   |
|  200 |  2 | Mary   |   300 | f   |
|  300 |  3 | Jane   |   600 | t   |
|  400 |  4 | Anne   |   400 | f   |
|  100 |  5 | Mike   |   500 | f   |
|  600 |  6 | Joanne |  1100 | t   |
+--+++---+-+



-- 
 I recommend David Deutsch's <>

  Jian

Re: first order by then partition by x < a fixed value.

[Frank Streitzig]

Am Thu, Jul 14, 2022 at 01:23:55PM +0530 schrieb jian he:
> This question (https://stackoverflow.com/q/72975669/15603477) is fun.
> DB fiddle link:
> https://dbfiddle.uk/?rdbms=postgres_14&fiddle=36d685ad463831877ae70361be2cfa3b
>
> account
> size   idname 1001 John 2002 Mary 3003
> Jane 4004 Anne1005 Mike 6006 Joanne
>
> Then expected output: account group  size   idname 1   100
> 1 John 1   2002 Mary 1   3003 Jane 2
> 4004 Anne2   1005 Mike 3   6006 Joanne
>
> Idea is fixed order by id then cumulative sum. if  <=600 then grouped
> together using the same row_number.
>
> But I imagine this kind question has been solved many times.
> Current posted solution uses a recursive query, which is really hard for
> me.
> Is there any simple or more intuitive way to solve this kind of problem?
> I can write some customized aggregate function to aggregate stops at 600.
>
> I can get the following result, So what's the next step?
>
> +--+++---+-+
> | size | id |  name  | capped_at_600 | large_or_eq_600 |
> +--+++---+-+
> |  100 |  1 | John   |   100 | f   |
> |  200 |  2 | Mary   |   300 | f   |
> |  300 |  3 | Jane   |   600 | t   |
> |  400 |  4 | Anne   |   400 | f   |
> |  100 |  5 | Mike   |   500 | f   |
> |  600 |  6 | Joanne |  1100 | t   |
> +--+++---+-+
>
>
>
> --
>  I recommend David Deutsch's <>
>
>   Jian

My proposal:

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=7daef32ae39b2ec7c38a83cf9e19d4ae

select  id, name, size
, sum(size) over (order by id) as size_total
, ((sum(size) over (order by id) - 1) / 600) + 1 as size_group
from account
order by id, name;


Best regards
Frank

Oracle to Postgress Migration

[DAVID ROTH]

Has anything been published on Oracle to Postgress migration.

I am finding plenty of information about schema migration but,
I think this is the easy part.
I have a a tremendous amount of SQL and PL/SQL code that needs to be 
translated.  I know Oracle "packages" will not translate.  DECODE is not 
standard and will need to be rewritten as CASE.  I have seen feature/function 
translation matrices to/from Oracle and other database but I can't seem to find 
one for Postgress.
Please point me in the right direction.
Dave

Re: Oracle to Postgress Migration

[Bruce Momjian]

On Thu, Jul 14, 2022 at 03:06:58PM -0400, DAVID ROTH wrote:
> Has anything been published on Oracle to Postgress migration.
> 
> I am finding plenty of information about schema migration but,
> I think this is the easy part.
> I have a a tremendous amount of SQL and PL/SQL code that needs to be 
> translated.  I know Oracle "packages" will not translate.  DECODE is not 
> standard and will need to be rewritten as CASE.  I have seen feature/function 
> translation matrices to/from Oracle and other database but I can't seem to 
> find one for Postgress.
> Please point me in the right direction.

I would start here:

https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

Re: PostgreSQL 14.4 ERROR: out of memory issues

[Aleš Zelený]

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.


> > 2022-07-02 14:48:47 CEST [4476]: [43-1] user=,db=,host=,app= LOG:  could
> > not fork worker process: Cannot allocate memory
> > terminate called after throwing an instance of 'std::bad_alloc'
> >   what():  std::bad_alloc
> >
> > DETAIL: parameters: $1 = '1', $2 = '1748010445', $3 = '0', $4 = '1000'
> > terminate
> > called after throwing an instance of 'std::bad_alloc' terminate called
> > after throwing an instance of 'std::bad_alloc' what(): what():
> > std::bad_allocstd::bad_alloc 2022-07-08 14:54:23 CEST [4476]: [49-1]
> > user=,db=,host=,app= LOG: background worker "parallel worker" (PID 25251)
> > was terminated by signal 6: Aborted
> > 2022-07-08 14:54:23 CEST [4476]: [51-1] user=,db=,host=,app= LOG:
> >  terminating any other active server processes
>
> Looks like something is going wrong in the memory handling of one of
> your C++ extensions here.  If you can isolate an issue using a query
> without any custom code, that would be a Postgres problem, but I think
> that you are missing a trick in it.
>

Here are extensions installed in the database served by the cluster:
prematch=# \dx
List of installed extensions
Name| Version |   Schema   |
 Description
+-++
 amcheck| 1.3 | public | functions for verifying
relation integrity
 dblink | 1.2 | public | connect to other PostgreSQL
databases from within a database
 file_fdw   | 1.0 | public | foreign-data wrapper for flat
file access
 hstore | 1.8 | public | data type for storing sets of
(key, value) pairs
 hypopg | 1.3.1   | public | Hypothetical indexes for
PostgreSQL
 pageinspect| 1.9 | public | inspect the contents of
database pages at a low level
 pg_buffercache | 1.3 | public | examine the shared buffer cache
 pg_stat_kcache | 2.2.0   | public | Kernel statistics gathering
 pg_stat_statements | 1.9 | public | track planning and execution
statistics of all SQL statements executed
 pgcrypto   | 1.3 | public | cryptographic functions
 pgstattuple| 1.5 | public | show tuple-level statistics
 plpgsql| 1.0 | pg_catalog | PL/pgSQL procedural language
 plpgsql_check  | 2.1 | public | extended check for plpgsql
functions
 postgres_fdw   | 1.1 | public | foreign-data wrapper for
remote PostgreSQL servers
 tablefunc  | 1.0 | public | functions that manipulate
whole tables, including crosstab
(15 rows)

When we started experiencing these issues, based on Prometheus
node-exporter statistics, I've begun collecting /proc//status for all
PG instance processes in an infinite loop.
>From this, I can extract some data. It looks like RssAnon memory is what I
can see growing from several hundred kB to 1-2GB over some period (depends
on workload, but usually 24 hours).
The sessi

Re: PostgreSQL 14.4 ERROR: out of memory issues

[Pavel Stehule]

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

>
> 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.
>
>
>> > 2022-07-02 14:48:47 CEST [4476]: [43-1] user=,db=,host=,app= LOG:  could
>> > not fork worker process: Cannot allocate memory
>> > terminate called after throwing an instance of 'std::bad_alloc'
>> >   what():  std::bad_alloc
>> >
>> > DETAIL: parameters: $1 = '1', $2 = '1748010445', $3 = '0', $4 = '1000'
>> > terminate
>> > called after throwing an instance of 'std::bad_alloc' terminate called
>> > after throwing an instance of 'std::bad_alloc' what(): what():
>> > std::bad_allocstd::bad_alloc 2022-07-08 14:54:23 CEST [4476]: [49-1]
>> > user=,db=,host=,app= LOG: background worker "parallel worker" (PID
>> 25251)
>> > was terminated by signal 6: Aborted
>> > 2022-07-08 14:54:23 CEST [4476]: [51-1] user=,db=,host=,app= LOG:
>> >  terminating any other active server processes
>>
>> Looks like something is going wrong in the memory handling of one of
>> your C++ extensions here.  If you can isolate an issue using a query
>> without any custom code, that would be a Postgres problem, but I think
>> that you are missing a trick in it.
>>
>
> Here are extensions installed in the database served by the cluster:
> prematch=# \dx
> List of installed extensions
> Name| Version |   Schema   |
>  Description
>
> +-++
>  amcheck| 1.3 | public | functions for verifying
> relation integrity
>  dblink | 1.2 | public | connect to other PostgreSQL
> databases from within a database
>  file_fdw   | 1.0 | public | foreign-data wrapper for flat
> file access
>  hstore | 1.8 | public | data type for storing sets of
> (key, value) pairs
>  hypopg | 1.3.1   | public | Hypothetical indexes for
> PostgreSQL
>  pageinspect| 1.9 | public | inspect the contents of
> database pages at a low level
>  pg_buffercache | 1.3 | public | examine the shared buffer
> cache
>  pg_stat_kcache | 2.2.0   | public | Kernel statistics gathering
>  pg_stat_statements | 1.9 | public | track planning and execution
> statistics of all SQL statements executed
>  pgcrypto   | 1.3 | public | cryptographic functions
>  pgstattuple| 1.5 | public | show tuple-level statistics
>  plpgsql| 1.0 | pg_catalog | PL/pgSQL procedural language
>  plpgsql_check  | 2.1 | public | extended check for plpgsql
> functions
>  postgres_fdw   | 1.1 | public | foreign-data wrapper for
> remote PostgreSQL servers
>  tablefunc  | 1.0 | public | functions that manipulate
> whole tables, including crosstab
> (15 rows)
>
> When we started experiencing these issues, based on Prometheus
> node-exporter statistics, I've begun collecting /proc//status for all
> PG instance processes

Re: Oracle to Postgress Migration

[DAVID ROTH]

Looks good.
Thanks

> On 07/14/2022 3:10 PM Bruce Momjian  wrote:
> 
>  
> On Thu, Jul 14, 2022 at 03:06:58PM -0400, DAVID ROTH wrote:
> > Has anything been published on Oracle to Postgress migration.
> > 
> > I am finding plenty of information about schema migration but,
> > I think this is the easy part.
> > I have a a tremendous amount of SQL and PL/SQL code that needs to be 
> > translated.  I know Oracle "packages" will not translate.  DECODE is not 
> > standard and will need to be rewritten as CASE.  I have seen 
> > feature/function translation matrices to/from Oracle and other database but 
> > I can't seem to find one for Postgress.
> > Please point me in the right direction.
> 
> I would start here:
> 
>   https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion
> 
> -- 
>   Bruce Momjian  https://momjian.us
>   EDB  https://enterprisedb.com
> 
>   Indecision is a decision.  Inaction is an action.  Mark Batterson

Re: PostgreSQL 14.4 ERROR: out of memory issues

[Tomas Vondra]

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.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: PostgreSQL 14.4 ERROR: out of memory issues

[Aleš Zelený]

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.
>

If the RssAnon memory is a good indicator, i can then determine the
backends and dump memory context.
It'll take me some time since I'm out of office for vacation, but I'll
manage that somewhat way.

Thanks for all to the hints!

Aleš

>
> regards
>
> --
> Tomas Vondra
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>