Re: Postgresql13-devel fails to install on centos 7

Had to run this command: yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm yum install llvm5.0-devel And then it worked :) On Wed, Nov 18, 2020 at 1:26 PM Hemil Ruparel wrote: > I am trying to install postgres13-devel on a centos 7 server. But I am > gettin

vacuum vs vacuum full

Hi, We have a table of 3113GB, and we are planning to vacuum it in non business hours i.e. 12AM to 4AM, So my queries are: 1. What should be perform on the table Vacuum or Vacuum full ? 2. Do we need to perform Analyze also? 3. Will the operation be completed in the given time frame? how to check

Postgres on Kubernetes/VMware

Everyone,  I’ve run into an issue that’s got me stumped and I would be really grateful for any ideas. We’re deploying Postgres 11.8 on Kubernetes 1.17.9. The nodes are running RedHat EL 7.9 with the latest kernel for that distribution.  We create a Kubernetes pod that runs Postgres. It’s set

Re: Postgresql13-devel fails to install on centos 7

Hi, On Wed, 2020-11-18 at 13:45 +0530, Hemil Ruparel wrote: > Had to run this command: > >  yum install > https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm >  yum install llvm5.0-devel > > And then it worked :) Updated website to clarify that EPEL is needed for -devel RPMs

Re: pg_upgrade from 12 to 13 failes with plpython2

On Wed, Nov 18, 2020 at 8:11 AM Marcin Giedz wrote: > > but my question still remains the same - what causes pg_upgrade failure - are > functions the reason? what I did was to delete these 2 rows from > pg_pltemplate as I thought this may help: > > postgres=# delete from pg_pltemplate where tmpl

Re: vacuum vs vacuum full

On 11/18/20 2:33 AM, Atul Kumar wrote: Hi, We have a table of 3113GB, and we are planning to vacuum it in non business hours i.e. 12AM to 4AM, So my queries are: 1. What should be perform on the table Vacuum or Vacuum full ? The documentation *clearly states* the difference between VACUUM and

Re: vacuum vs vacuum full

Hi Atul, On Wed, Nov 18, 2020 at 9:33 AM Atul Kumar wrote: > Hi, > > We have a table of 3113GB, and we are planning to vacuum it in non > business hours i.e. 12AM to 4AM, So my queries are: > > 1. What should be perform on the table Vacuum or Vacuum full ? > Vacuum full will do a complete rewri

Re: vacuum vs vacuum full

On 11/18/20 3:41 AM, Olivier Gautherot wrote: Hi Atul, On Wed, Nov 18, 2020 at 9:33 AM Atul Kumar > wrote: Hi, We have a table of 3113GB, and we are planning to vacuum it in non business hours i.e. 12AM to 4AM, So my queries are: [snip] 3. Wil

Re: Postgresql13-devel fails to install on centos 7

Thanks a lot. I really hope it helps newbies like myself save a bunch of time On Wed 18 Nov, 2020, 2:50 PM Devrim Gündüz, wrote: > > Hi, > > On Wed, 2020-11-18 at 13:45 +0530, Hemil Ruparel wrote: > > Had to run this command: > > > > yum install > > https://dl.fedoraproject.org/pub/epel/epel-re

Re: vacuum vs vacuum full

Ron schrieb am 18.11.2020 um 10:44: > No matter how long it takes, this is an excellent argument for > partitioning Very Large Tables: many maintenance tasks are made > *much* easier. The problem is, you can't partition every table as long as Postgres does not support a primary key that is indepen

Re: vacuum vs vacuum full

On Wed, Nov 18, 2020 at 10:45 AM Ron wrote: > On 11/18/20 3:41 AM, Olivier Gautherot wrote: > > Hi Atul, > > On Wed, Nov 18, 2020 at 9:33 AM Atul Kumar wrote: > >> Hi, >> >> We have a table of 3113GB, and we are planning to vacuum it in non >> business hours i.e. 12AM to 4AM, So my queries are:

Re: pg_upgrade from 12 to 13 failes with plpython2

right, I had one function relaying on plpython2u so I changed it... but the again pg_upgrade claims error with python: cat loadable_libraries.txt could not load library "$libdir/plpython2": ERROR: could not access file "$libdir/plpython2": No such file or directory In database: argosrm In da

How to select values in a JSON type of column?

Hi! I have a column called targeting in a table called campaigns . The column looks like { "targets": [ { "audienceSegments": [ { "includes": [ { "consumer": "selection", "segments": { "allOf": [

No parallel plan on an union all subquery

Hi, I have a question about parallel plans. Here is my test case : select version(); version -- PostgreSQL

Re: pg_upgrade from 12 to 13 failes with plpython2

On Wed, 2020-11-18 at 11:05 +0100, Marcin Giedz wrote: > right, I had one function relaying on plpython2u so I changed it... but the > again pg_upgrade claims error with python: > > cat loadable_libraries.txt > could not load library "$libdir/plpython2": ERROR: could not access file > "$libdir

Re: vacuum vs vacuum full

On Wed, 2020-11-18 at 10:57 +0100, Thomas Kellerer wrote: > > No matter how long it takes, this is an excellent argument for > > partitioning Very Large Tables: many maintenance tasks are made > > *much* easier. > > The problem is, you can't partition every table as long as Postgres > does not sup

Re: autovacuum recommendations for Large tables

On Tue, 2020-11-17 at 22:17 +0100, Olivier Gautherot wrote: > On Tue, Nov 17, 2020 at 12:05 AM David G. Johnston > wrote: > > On Mon, Nov 16, 2020 at 3:57 PM Atul Kumar wrote: > > > I only have this one big table in the database of size 3113 GB with rows > > > 7661353111. > > > > > > Right Now

Re: Race condition with restore_command on streaming replica

Hi Brad, We did some research on this at EDB and here is a summary of the finding and suggested next steps. *Context:* *Given* a 3 node cluster M0, M1 and M2. *And *M0 is the primary with M1 and M2 as the replicas - we will call this *"T0" (Timeline 0).* *And* a *"switch over"* command is issued

Re: vacuum vs vacuum full

Hi Laurenz, > On 18. Nov, 2020, at 13:02, Laurenz Albe wrote: > > I personally hope that we will never have global indexes. > I am not looking forward to helping customers with the problems that > they create (long duration of ATTACH/DETACH PARTITION, index fragmentation). +1. Experience shows

Problem with compiling extensions with Postgres Version 13

Hello, I am not really sure if I am in the right mailing list, but I try it here first. I have written a postgres C extension as DLL which was used with Postgres 9.2 successfully. Now I am trying to upgrade to Postgres 13 but I ran into a compile problem. The function PG_GETARG_BYTEA_P causes

Re: vacuum vs vacuum full

> > Experience shows that global index in Oracle lead to problems when dropping a > partition. rebuilding an index, or other such nice administrative stuff, > often leading to unnecessarily long downtimes. > > I think Oracle fixed it later by allowing asynchronous update of global index afte

Re: How to select values in a JSON type of column?

On Wednesday, November 18, 2020, Snjezana Frketic < frketic.snjez...@gmail.com> wrote: > > > SELECT targeting#>'{targets,0,audienceSegments,0,includes,0, > segments,allOf,0,ids}'FROM campaigns; > > and that works, but, I don’t want to have a fixed path because positions > could change like 0 could

Re: How to select values in a JSON type of column?

I looked at it yesterday, but I couldn't figure it out because my JSON is more nested and I got lost going down the path. On Wed, 18 Nov 2020 at 15:40, David G. Johnston wrote: > > On Wednesday, November 18, 2020, Snjezana Frketic < > frketic.snjez...@gmail.com> wrote: > >> >> >> SELECT >> targ

Re: How to select values in a JSON type of column?

Snjezana Frketic schrieb am 18.11.2020 um 11:29: > I have a column called |targeting| in a table called |campaigns| . > [...] > and I need to select all the |ids| in |includes|. > Currently, I am doing it like this  > > SELECT  > |targeting#>'{targets,0,audienceSegments,0,includes,0,segments,allOf,

Re: vacuum vs vacuum full

Hi Ravi, > On 18. Nov, 2020, at 15:30, Ravi Krishna wrote: > > ALTER TABLE TABLE_NAME DROP PARTITION PARTITION_NAME UPDATE INDEXES; IIRC the statement is alter table drop partition update *GLOBAL* indexes; But we experienced big problems in the past which is why we changed all to local ind

Re: vacuum vs vacuum full

On Wed, Nov 18, 2020 at 1:33 AM Atul Kumar wrote: > Hi, > > We have a table of 3113GB, and we are planning to vacuum it in non > Just making sure that isn't a typo (repeated 1s)... business hours i.e. 12AM to 4AM, So my queries are: > > 1. What should be perform on the table Vacuum or Vacuum fu

Re: How to select values in a JSON type of column?

I actually have version 9.3.17 😬 On Wed, 18 Nov 2020 at 15:55, Thomas Kellerer wrote: > Snjezana Frketic schrieb am 18.11.2020 um 11:29: > > I have a column called |targeting| in a table called |campaigns| . > > [...] > > and I need to select all the |ids| in |includes|. > > Currently, I am doi

psql backward compatibility

Hello, When upgrading an older version of postgres, version 8.4 for example, to a newer version such as 9.6, does the data have to be migrated immediately? It looks like the recommended method is to dump the data, upgrade, initialize a new cluster, and then restore the dumped data into the newer

Re: create type with %type or %rowtype

On 11/17/20 11:34 PM, Post Gresql wrote: On 2020-11-18 04:37, David G. Johnston wrote: (resending to include the list) On Tue, Nov 17, 2020 at 3:12 PM Post Gresql > wrote: create type my_type as my_table%rowtype; This would be redundant with existing beha

Re: create type with %type or %rowtype

On Wed, Nov 18, 2020 at 12:34 AM Post Gresql wrote: > or even a complete table row as return type. > As mentioned, this is already possible. > create type my_type (a int, b my_table.my_column%type); > > The real reason: you will be sure you are using the same type everywhere. > And it is easier

Re: psql backward compatibility

> On Nov 18, 2020, at 08:05, Stephen Haddock wrote: > When upgrading an older version of postgres, version 8.4 for example, to a > newer version such as 9.6, does the data have to be migrated immediately? Yes. You cannot run binaries from a newer major version of PostgreSQL on a cluster tha

Re: psql backward compatibility

On Wed, Nov 18, 2020 at 9:05 AM Stephen Haddock wrote: > It appears that 9.6 is able to run against the older cluster (DB service > starts, queries work, etc) > If this is indeed what you've observed you've found a bug because a 9.6 service should not start at all if the data directory it is bei

Re: psql backward compatibility

On 11/18/20 8:05 AM, Stephen Haddock wrote: Hello, When upgrading an older version of postgres, version 8.4 for example, to a newer version such as 9.6, does the data have to be migrated immediately? It looks like the recommended method is to dump the data, upgrade, initialize a new cluster,

Re: psql backward compatibility

On 11/18/20 8:05 AM, Stephen Haddock wrote: Hello, When upgrading an older version of postgres, version 8.4 for example, to a newer version such as 9.6, does the data have to be migrated immediately? It looks like the recommended method is to dump the data, upgrade, initialize a new cluster,

Re: How to select values in a JSON type of column?

Unfortunately, I also can not update my version :) On Wed, 18 Nov 2020 at 17:00, Snjezana Frketic wrote: > I actually have version 9.3.17 😬 > > > On Wed, 18 Nov 2020 at 15:55, Thomas Kellerer wrote: > >> Snjezana Frketic schrieb am 18.11.2020 um 11:29: >> > I have a column called |targeting| in

Re: psql backward compatibility

Thanks for the quick responses! I'll double-check the configuration. Given your responses it is highly likely that the older version is still running the server and I'm simply running the client in 9.6. On Wed, Nov 18, 2020, 11:16 Adrian Klaver wrote: > On 11/18/20 8:05 AM, Stephen Haddock wrot

Re: psql backward compatibility

On Wed, Nov 18, 2020 at 9:16 AM Adrian Klaver wrote: > On 11/18/20 8:05 AM, Stephen Haddock wrote: > > Hello, > > > > When upgrading an older version of postgres, version 8.4 for example, to > > a newer version such as 9.6, does the data have to be migrated > immediately? > > > > It looks like th

Re: How to select values in a JSON type of column?

On Wed, Nov 18, 2020 at 9:23 AM Snjezana Frketic wrote: > Unfortunately, I also can not update my version :) > Then probably the answer to your original question is no :) There are possibly other ways to make something that works but if you aren't willing to upgrade off of a discontinued versio

Re: psql backward compatibility

On Wed, 2020-11-18 at 11:05 -0500, Stephen Haddock wrote: > When upgrading an older version of postgres, version 8.4 for example, to a > newer > version such as 9.6, does the data have to be migrated immediately? Since nobody mentioned that explicitly: do not upgrade to 9.6. If you upgrade, move

Re: psql backward compatibility

On Wed, Nov 18, 2020 at 9:30 AM Laurenz Albe wrote: > On Wed, 2020-11-18 at 11:05 -0500, Stephen Haddock wrote: > > When upgrading an older version of postgres, version 8.4 for example, to > a newer > > version such as 9.6, does the data have to be migrated immediately? > > Since nobody mentione

Re: vacuum vs vacuum full

On 11/18/20 6:02 AM, Laurenz Albe wrote: On Wed, 2020-11-18 at 10:57 +0100, Thomas Kellerer wrote: No matter how long it takes, this is an excellent argument for partitioning Very Large Tables: many maintenance tasks are made *much* easier. The problem is, you can't partition every table as lon

Re: How to select values in a JSON type of column?

Fair point. Appreciate your help nevertheless :) On Wed, 18 Nov 2020 at 17:30, David G. Johnston wrote: > On Wed, Nov 18, 2020 at 9:23 AM Snjezana Frketic < > frketic.snjez...@gmail.com> wrote: > >> Unfortunately, I also can not update my version :) >> > > Then probably the answer to your origi

Re: Problem with compiling extensions with Postgres Version 13

On Wed, 2020-11-18 at 14:46 +0100, Eric Svenson wrote: > I am not really sure if I am in the right mailing list, but I try it here > first. > > I have written a postgres C extension as DLL which was used with Postgres 9.2 > successfully. > > Now I am trying to upgrade to Postgres 13 but I ran i

Re: psql backward compatibility

On 11/18/20 10:13 AM, Adrian Klaver wrote: On 11/18/20 8:05 AM, Stephen Haddock wrote: Hello, When upgrading an older version of postgres, version 8.4 for example, to a newer version such as 9.6, does the data have to be migrated immediately? It looks like the recommended method is to dump t

Re: psql backward compatibility

On 11/18/20 10:30 AM, Laurenz Albe wrote: On Wed, 2020-11-18 at 11:05 -0500, Stephen Haddock wrote: When upgrading an older version of postgres, version 8.4 for example, to a newer version such as 9.6, does the data have to be migrated immediately? Since nobody mentioned that explicitly: do n

Re: How to select values in a JSON type of column?

Snjezana Frketic schrieb am 18.11.2020 um 17:00: I actually have version 9.3.17 😬 On Wed, 18 Nov 2020 at 15:55, Thomas Kellerer mailto:sham...@gmx.net>> wrote: Snjezana Frketic schrieb am 18.11.2020 um 11:29: > I have a column called |targeting| in a table called |campaigns| . >

Re: pg_upgrade from 12 to 13 failes with plpython2

Hi, not the case I believe : postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ---+---+--+-+-+--- argosrm | pgsql | UTF8 | pl_PL.UTF-8 | pl_PL.UTF-8 | postgres | pgsql | UTF8 | pl_PL.UTF-8

Re: pg_upgrade from 12 to 13 failes with plpython2

Marcin Giedz writes: > all DBs checked and no plpython(2u) is found except for plpython3u I think you also need to make sure you've dropped the plpythonu and plpython2u extensions in every database. regards, tom lane

Re: pg_upgrade from 12 to 13 failes with plpython2

so look at this: marcin@carbon2:~$ psql -U pgsql -h 192.168.89.64 postgres psql (12.4 (Ubuntu 12.4-1), server 12.5) Type "help" for help. postgres=# drop extension plpython; ERROR: extension "plpython" does not exist postgres=# drop extension plpythonu; ERROR: extension "plpythonu" does n

Re: pg_upgrade from 12 to 13 failes with plpython2

[ please don't top-post, it makes conversations unreadable ] Marcin Giedz writes: > so look at this: > postgres=# drop extension plpython; > ERROR: extension "plpython" does not exist > postgres=# drop extension plpythonu; > ERROR: extension "plpythonu" does not exist > postgres=# drop exten

Re: pg_upgrade from 12 to 13 failes with plpython2

[ please don't top-post, it makes conversations unreadable ] Marcin Giedz writes: > so look at this: > postgres=# drop extension plpython; > ERROR: extension "plpython" does not exist > postgres=# drop extension plpythonu; > ERROR: extension "plpythonu" does not exist > postgres=# drop ex

Re: pg_upgrade from 12 to 13 failes with plpython2

Marcin Giedz writes: > anyway got this from your query: > 16402 | plpython_call_handler | 11 | 10 | 13 | 1 | 0 | 0 | - | f | f | f | f > | f | v | u | 0 | 0 | 2280 | | | | | | | plpython_call_handler | > $libdir/plpython2 | | > 16403 | plpython_inline_handler | 11 | 10 | 13 | 1 | 0 | 0 | - |

Re: pg_upgrade from 12 to 13 failes with plpython2

> anyway got this from your query: > 16402 | plpython_call_handler | 11 | 10 | 13 | 1 | 0 | 0 | - | f | f | f | f > | f | v | u | 0 | 0 | 2280 | | | | | | | plpython_call_handler | > $libdir/plpython2 | | > 16403 | plpython_inline_handler | 11 | 10 | 13 | 1 | 0 | 0 | - | f | f | f | > t | f

Re: pg_upgrade from 12 to 13 failes with plpython2

On Wed, Nov 18, 2020 at 08:59:58PM +0100, Marcin Giedz wrote: > > > anyway got this from your query: > > > 16402 | plpython_call_handler | 11 | 10 | 13 | 1 | 0 | 0 | - | f | f | f | f > | f | v | u | 0 | 0 | 2280 | | | | | | | plpython_call_handler | $libdir/ > plpython2 | | > > 16403 | plpython_

Re: pg_upgrade from 12 to 13 failes with plpython2

Bruce Momjian writes: > I think one big problem is that when pg_upgrade fails in this way, users > are required to do some complex system catalog queries to diagnose the > cause. Is there a way to simplify this for them? Maybe pg_upgrade should print the actual function names, not just the probi

Re: pg_upgrade from 12 to 13 failes with plpython2

On Wed, Nov 18, 2020 at 03:25:56PM -0500, Tom Lane wrote: > Bruce Momjian writes: > > I think one big problem is that when pg_upgrade fails in this way, users > > are required to do some complex system catalog queries to diagnose the > > cause. Is there a way to simplify this for them? > > Maybe

Re: create type with %type or %rowtype

On 2020-11-18 17:07, Adrian Klaver wrote: \d cell_per    Foreign table "public.cell_per"   Column  |   Type    | Collation | Nullable | Default | FDW options --+---+---+--+-+-  category | character varyi

Re: pg_upgrade from 12 to 13 failes with plpython2

Bruce Momjian writes: > On Wed, Nov 18, 2020 at 03:25:56PM -0500, Tom Lane wrote: >> Maybe pg_upgrade should print the actual function names, not just the >> probin values. > It is done that way so we don't overwhelm them with lots of function > names, and they can focus on the library. I was th

Re: pg_upgrade from 12 to 13 failes with plpython2

Hi, On Wed, 2020-11-18 at 14:54 -0500, Tom Lane wrote: > Uh-huh, so there you have it.  These must be leftovers from some > pre-extension incarnation of plpython that was never cleaned up > properly.  I think this was broken when Marcin first dropped the language. We should just have dropped the

Re: psql backward compatibility

On 2020-Nov-18, Stephen Haddock wrote: > Hello, > > When upgrading an older version of postgres, version 8.4 for example, to a > newer version such as 9.6, does the data have to be migrated immediately? As others have said: yes. > It looks like the recommended method is to dump the data, upgrad

Re: create type with %type or %rowtype

On Wednesday, November 18, 2020, Post Gresql wrote: > > On 2020-11-18 17:07, Adrian Klaver wrote: > > > \d cell_per >Foreign table "public.cell_per" > Column | Type| Collation | Nullable | Default | FDW > options > --+---+--

Re: create type with %type or %rowtype

On 11/18/20 1:08 PM, Post Gresql wrote: On 2020-11-18 17:07, Adrian Klaver wrote: \d cell_per    Foreign table "public.cell_per"   Column  |   Type    | Collation | Nullable | Default | FDW options --+---+---+--+-+---

Re: pg_upgrade from 12 to 13 failes with plpython2

On Wed, Nov 18, 2020 at 10:06:17PM +, Devrim Gunduz wrote: > Hi, > > On Wed, 2020-11-18 at 14:54 -0500, Tom Lane wrote: > > Uh-huh, so there you have it.  These must be leftovers from some > > pre-extension incarnation of plpython that was never cleaned up > > properly.  > > I think this was

Re: pg_upgrade from 12 to 13 failes with plpython2

> On Nov 18, 2020, at 9:29 PM, Bruce Momjian wrote: > > On Wed, Nov 18, 2020 at 10:06:17PM +, Devrim Gunduz wrote: >> Hi, >> >> On Wed, 2020-11-18 at 14:54 -0500, Tom Lane wrote: >>> Uh-huh, so there you have it. These must be leftovers from some >>> pre-extension incarnation of plpython

Re: pg_upgrade from 12 to 13 failes with plpython2

On Wed, Nov 18, 2020 at 09:35:20PM -0700, Rob Sargent wrote: > > pg_upgrade does have some code to handle plpython call handlers in > > function.c:get_loadable_libraries(); > > > > * Systems that install plpython before 8.1 have > > * plpython_call_handler() defined in the "public"

CreateProcess call failed: A blocking operation was interrupted by a call to WSACancelBlockingCall

Hi, I'm not sure how to reach the cause and reproduce it. Any suggestion will be appreciated. PostgreSQL 11.7 postgresql-42.2.12.jar Windows Server 2016 Thanks, Kiyoshi 2020-11-13 21:11:51.535 JST [3652] LOG: CreateProcess call failed: A blocking operation was interrupted by a call to WSACancel

Re: pg_upgrade from 12 to 13 failes with plpython2

> On Nov 18, 2020, at 9:39 PM, Bruce Momjian wrote: > > On Wed, Nov 18, 2020 at 09:35:20PM -0700, Rob Sargent wrote: >>> pg_upgrade does have some code to handle plpython call handlers in >>> function.c:get_loadable_libraries(); >>> >>>* Systems that install plpython before 8.1 have

Re: pg_upgrade from 12 to 13 failes with plpython2

On Wed, Nov 18, 2020 at 10:57:00PM -0700, Rob Sargent wrote: > > It issues this message and fails: > > > >if (PQntuples(res) > 0) > >{ > >if (!found_public_plpython_handler) > >{ > >pg_log(PG_WARNING, > >

Re: create type with %type or %rowtype

Hi, > On 18. Nov, 2020, at 22:08, Post Gresql wrote: > > I might be stupid, but where in the document for create function does it say > that the return type can be a table? > > From the doc for version 13 > https://www.postgresql.org/docs/13/sql-createfunction.html > > "rettype > The return

Re: pg_upgrade from 12 to 13 failes with plpython2

Bruce Momjian writes: > It didn't trigger this message for him, and I am also wondering if it > should have. The extra functions in this case were in pg_catalog, not public, so there is exactly no part of that error message that is applicable. In any case, that seems an overly specific solution.

\COPY command and indexes in tables

Hello, We load large tables in some customer installation (some millions of rows) from file with: TRUNCATE TABLE tableName ; \COPY tableName FROM 'fileName' WITH ( DELIMITER '|' ) and got to know that the loading nearly stops (without any real CPU consumption) in the middle. The wild g

Re: \COPY command and indexes in tables

On Thu, 2020-11-19 at 08:38 +0100, Matthias Apitz wrote: > We load large tables in some customer installation (some millions of rows) > from file with: > > TRUNCATE TABLE tableName ; > \COPY tableName FROM 'fileName' WITH ( DELIMITER '|' ) > > and got to know that the loading nearly stops