Alter the column data type of the large data volume table.

Hi all, I have a table with 1.6 billion records. The data type of the primary key column is incorrectly used as integer. I need to replace the type of the column with bigint. Is there any ideas for this? Solutions that have been tried: Adding temporary columns was too time-consuming, so I gave up

Re: pg_dump of partitioned table not working.

On 12/2/20 6:14 PM, Ron wrote: On 12/2/20 5:50 PM, Tom Lane wrote: I wrote: What you need here is something like "pg_dump -t measurement*" to indicate that you want measurement's child tables too, but AFAIR pg_dump has no such feature.  Maybe we should add it. Or actually: that syntax does do

Re: pg_dump of partitioned table not working.

On 12/2/20 6:54 PM, Adrian Klaver wrote: On 12/2/20 4:38 PM, Ron wrote: On 12/2/20 6:21 PM, Adrian Klaver wrote: On 12/2/20 4:13 PM, Ron wrote: On 12/2/20 6:08 PM, David G. Johnston wrote: On Wed, Dec 2, 2020 at 5:06 PM Ron > wrote: That you were comparing app

Re: pg_dump of partitioned table not working.

On 12/2/20 4:38 PM, Ron wrote: On 12/2/20 6:21 PM, Adrian Klaver wrote: On 12/2/20 4:13 PM, Ron wrote: On 12/2/20 6:08 PM, David G. Johnston wrote: On Wed, Dec 2, 2020 at 5:06 PM Ron > wrote: That you were comparing apples and oranges - specifically that the d

Re: pg_dump of partitioned table not working.

On Wed, Dec 2, 2020 at 5:38 PM Ron wrote: > Shame on me for assuming, based on the explicit pg_dump command in the > example. > > This is what you wrote: > > (Specifying the whole file name because multiple versions are installed.) The path of the executable doesn't generally make a difference h

Re: pg_dump of partitioned table not working.

On 12/2/20 6:21 PM, Adrian Klaver wrote: On 12/2/20 4:13 PM, Ron wrote: On 12/2/20 6:08 PM, David G. Johnston wrote: On Wed, Dec 2, 2020 at 5:06 PM Ron > wrote: That you were comparing apples and oranges - specifically that the database you were dumping was emp

Re: pg_dump of partitioned table not working.

On 12/2/20 6:21 PM, David G. Johnston wrote: On Wed, Dec 2, 2020 at 5:14 PM Ron > wrote: > It does seem like there might be reason to have a switch along > the lines of "--include-child-tables". That would be great, but won't help me in v12. I'd pr

Re: pg_dump of partitioned table not working.

On Wed, Dec 2, 2020 at 5:14 PM Ron wrote: > > > It does seem like there might be reason to have a switch along > > the lines of "--include-child-tables". > > That would be great, but won't help me in v12. > > I'd probably just relocate the table to a separate schema and require that all partition

Re: pg_dump of partitioned table not working.

On 12/2/20 4:13 PM, Ron wrote: On 12/2/20 6:08 PM, David G. Johnston wrote: On Wed, Dec 2, 2020 at 5:06 PM Ron > wrote: That you were comparing apples and oranges - specifically that the database you were dumping was empty but the one you were checking was not.

Re: pg_dump of partitioned table not working.

On 12/2/20 5:50 PM, Tom Lane wrote: I wrote: What you need here is something like "pg_dump -t measurement*" to indicate that you want measurement's child tables too, but AFAIR pg_dump has no such feature. Maybe we should add it. Or actually: that syntax does do something, but it selects tables

Re: pg_dump of partitioned table not working.

On 12/2/20 6:08 PM, David G. Johnston wrote: On Wed, Dec 2, 2020 at 5:06 PM Ron > wrote: On 12/2/20 5:35 PM, David G. Johnston wrote: On Wednesday, December 2, 2020, Ron mailto:ronljohnso...@gmail.com>> wrote: What am I missing? (Specify

Re: pg_dump of partitioned table not working.

On 12/2/20 5:49 PM, David G. Johnston wrote: On Wednesday, December 2, 2020, Tom Lane > wrote: What you need here is something like "pg_dump -t measurement*" to indicate that you want measurement's child tables too, but AFAIR pg_dump has no such feature.

Re: pg_dump of partitioned table not working.

On Wed, Dec 2, 2020 at 5:06 PM Ron wrote: > On 12/2/20 5:35 PM, David G. Johnston wrote: > > On Wednesday, December 2, 2020, Ron wrote: > >> What am I missing? >> >> (Specifying the whole file name because multiple versions are installed.) >> >> $ /usr/lib/postgresql/12/bin/pg_dump --version >>

Re: pg_dump of partitioned table not working.

On 12/2/20 5:35 PM, David G. Johnston wrote: On Wednesday, December 2, 2020, Ron > wrote: What am I missing? (Specifying the whole file name because multiple versions are installed.) $ /usr/lib/postgresql/12/bin/pg_dump --version pg_dump (Postgre

Re: pg_dump of partitioned table not working.

On 12/2/20 5:42 PM, David G. Johnston wrote: On Wednesday, December 2, 2020, Ron > wrote: What am I missing? postgres=# \d+ measurement    Partitioned table "public.measurement" $ /usr/lib/postgresql/12/bin/pg_dump -p5433

Re: "Server versions prior to 8.0 do not support savepoints" when using JDBC ...

On 02/12/2020 23:46, Dave Cramer wrote: On Sun, 29 Nov 2020 at 14:09, Martin Goodson wrote: On 29/11/2020 18:46, David G. Johnston wrote: On Sun, Nov 29, 2020 at 11:06 AM Martin Goodson Hello. I wonder if anyone can assist with this? Some of my developers are reporting that they are gettin

Re: pg_dump of partitioned table not working.

I wrote: > What you need here is something like "pg_dump -t measurement*" > to indicate that you want measurement's child tables too, but > AFAIR pg_dump has no such feature. Maybe we should add it. Or actually: that syntax does do something, but it selects tables by pattern matching not hierarch

Re: pg_dump of partitioned table not working.

On Wednesday, December 2, 2020, Tom Lane wrote: > > > What you need here is something like "pg_dump -t measurement*" > to indicate that you want measurement's child tables too, but > AFAIR pg_dump has no such feature. -t accepts a pattern in pg_dump. But that requires the user to adhere to a n

Re: "Server versions prior to 8.0 do not support savepoints" when using JDBC ...

On Sun, 29 Nov 2020 at 14:09, Martin Goodson wrote: > On 29/11/2020 18:46, David G. Johnston wrote: > > On Sun, Nov 29, 2020 at 11:06 AM Martin Goodson > > > wrote: > > > >> Hello. > >> > >> I wonder if anyone can assist with this? > >> > >> Some of my developers are reporting that they are gett

Re: pg_dump of partitioned table not working.

On Wednesday, December 2, 2020, Ron wrote: > What am I missing? > > postgres=# \d+ measurement >Partitioned table "public.measurement" > > > $ /usr/lib/postgresql/12/bin/pg_dump -p5433 -d postgres -t measurement > --data-only > I’m unsure whether to expect a dump of o

Re: pg_dump of partitioned table not working.

Ron writes: > What am I missing? There's no data in a partitioned table per se, so the result is not surprising. What you need here is something like "pg_dump -t measurement*" to indicate that you want measurement's child tables too, but AFAIR pg_dump has no such feature. Maybe we should add it

Re: pg_dump of partitioned table not working.

On Wednesday, December 2, 2020, Ron wrote: > What am I missing? > > (Specifying the whole file name because multiple versions are installed.) > > $ /usr/lib/postgresql/12/bin/pg_dump --version > pg_dump (PostgreSQL) 12.5 (Ubuntu 12.5-1.pgdg18.04+1) > > postgres=# \d+ measurement > > psql? on (de

pg_dump of partitioned table not working.

What am I missing? (Specifying the whole file name because multiple versions are installed.) $ /usr/lib/postgresql/12/bin/pg_dump --version pg_dump (PostgreSQL) 12.5 (Ubuntu 12.5-1.pgdg18.04+1) postgres=# \d+ measurement    Partitioned table "public.measurement"   Column

Re: Is the Halloween problem an issue in Postgres

On 12/2/20 4:23 PM, raf wrote: On Wed, Dec 02, 2020 at 02:08:41PM -0800, Adrian Klaver wrote: On 12/2/20 2:02 PM, Thomas Kellerer wrote: guy...@icloud.com schrieb am 02.12.2020 um 21:27: The Halloween problem is that it is a challenge for the database if you’re updating a field that is also

Re: Is the Halloween problem an issue in Postgres

On Wed, Dec 02, 2020 at 02:08:41PM -0800, Adrian Klaver wrote: > On 12/2/20 2:02 PM, Thomas Kellerer wrote: > > guy...@icloud.com schrieb am 02.12.2020 um 21:27: > > > The Halloween problem is that it is a challenge for the database if > > > you’re updating a field that is also in the WHERE clau

Re: Is the Halloween problem an issue in Postgres

On Wed, Dec 02, 2020 at 11:02:07PM +0100, Thomas Kellerer wrote: > guy...@icloud.com schrieb am 02.12.2020 um 21:27: > > The Halloween problem is that it is a challenge for the database if > > you’re updating a field that is also in the WHERE clause of the same > > query. > > > > I just saw a p

Re: Is the Halloween problem an issue in Postgres

On 12/2/20 2:02 PM, Thomas Kellerer wrote: guy...@icloud.com schrieb am 02.12.2020 um 21:27: The Halloween problem is that it is a challenge for the database if you’re updating a field that is also in the WHERE clause of the same query. I just saw a presentation from someone about how in SQL Se

Re: Is the Halloween problem an issue in Postgres

guy...@icloud.com schrieb am 02.12.2020 um 21:27: The Halloween problem is that it is a challenge for the database if you’re updating a field that is also in the WHERE clause of the same query. I just saw a presentation from someone about how in SQL Server he recommended writing changes to a tem

Re: Is the Halloween problem an issue in Postgres

guy...@icloud.com writes: > The Halloween problem is that it is a challenge for the database if you’re > updating a field that is also in the WHERE clause of the same query. > I just saw a presentation from someone about how in SQL Server he recommended > writing changes to a temp table and then

Is the Halloween problem an issue in Postgres

The Halloween problem is that it is a challenge for the database if you’re updating a field that is also in the WHERE clause of the same query. I just saw a presentation from someone about how in SQL Server he recommended writing changes to a temp table and then writing them to the table as bein

Re: concurrent re-partitioning of declarative partitioned tables

On Wed, 2 Dec 2020 at 15:59, Michael Lewis wrote: > You can not have overlapping partitions that are both attached. > Not directly, no. That's why I'm considering the _partition_channel hack. Why do you want to merge partitions that you are "done with" instead of > just leaving them partitioned

Re: concurrent re-partitioning of declarative partitioned tables

On Wed, 2 Dec 2020 at 16:07, David G. Johnston wrote: > On Mon, Nov 30, 2020 at 8:36 AM Nick Cleaton wrote: > >> I want to set up a large table on postgresql 12.4, using declarative >> partitioning to partition by record creation date. I'd like to have recent >> records in small partitions but o

Re: concurrent re-partitioning of declarative partitioned tables

On Mon, Nov 30, 2020 at 8:36 AM Nick Cleaton wrote: > I want to set up a large table on postgresql 12.4, using declarative > partitioning to partition by record creation date. I'd like to have recent > records in small partitions but old records in a few larger partitions, so > I want merges. The

Re: concurrent re-partitioning of declarative partitioned tables

You can not have overlapping partitions that are both attached. Why do you want to merge partitions that you are "done with" instead of just leaving them partitioned by day? Why are you partitioning at all? Are you confident that you need partitions for performance & that the trade-offs are worth

Re: error on connecting port 5432

On 12/1/20 11:02 PM, Atul Kumar wrote: Thanks a lot Tom, I appended the -h /tmp and it worked. I need just one more help from you. Could you tell me that why & how that socket file existed in /tmp directory. What is the practice to make sure that this file (.s.PGSQL.5432) should be inside /var

Re: Calling Procedure from another procedure in Postgres

Muthukumar.GK schrieb am 02.12.2020 um 11:20: > I need to call the procedure(not function) and insert the records > into a temporary table from another procedure in postgres. When > executing the procedure 'Sampleproc2',I got some below syntax error. > Kindly let me know whether postgres supports

Re: Calling Procedure from another procedure in Postgres

st 2. 12. 2020 v 11:20 odesílatel Muthukumar.GK napsal: > Hi team, > > I need to call the procedure(not function) and insert the records into a > temporary table from another procedure in postgres. When executing the > procedure 'Sampleproc2',I got some below syntax error. Kindly let me know > w

Re: Calling Procedure from another procedure in Postgres

insert into is not valid On Wed, Dec 2, 2020 at 3:50 PM Muthukumar.GK wrote: > Hi team, > > I need to call the procedure(not function) and insert the records into a > temporary table from another procedure in postgres. When executing the > procedure 'Sampleproc2',I got some below syntax error.

Calling Procedure from another procedure in Postgres

Hi team, I need to call the procedure(not function) and insert the records into a temporary table from another procedure in postgres. When executing the procedure 'Sampleproc2',I got some below syntax error. Kindly let me know whether postgres supports this functionality or any other way of call

pg_cron question

Hi all Is there a way to run pg_cron job only once? Or can I get somehow inside the cronjob the ID of a running job to unschedule? Thanks Levi