Re: postgres table statistics

Thanks Shammat! Seems to fit the bill, Will give it a try. On Wednesday, 12 June, 2024 at 07:44:27 am GMT-7, Shammat wrote: Chandy G schrieb am 12.06.2024 um 09:47: > Eventhough pg jdbc driver  provides a way to set fetch size to tune > the driver to achieve better throughput, the JVM

Re: Long running query causing XID limit breach

On Sat, Jun 8, 2024 at 2:51 PM sud wrote: > > Thank You so much Laurenz and Yudhi. > > Yes its RDS and as you mentioned there does exist a space limitation of > ~64TB but as Laurenz mentioned the only time the second standby may crash > would be probably because of the storage space saturation a

Re: DROP COLLATION vs pg_collation question

On Wed, 2024-06-12 at 21:13 +0200, Karsten Hilbert wrote: > > Note that with DROP COLLATION you can only remove collations > > that belong to the encoding of your current database. > > A-ha !  Can that bit be found anywhere in the docs ? src/backend/catalog/namespace.c: /* * get_collation_o

TOAST Table / Dead Tuples / Free Pages

Hi everyone, I created a simple scenario to understand the handling of TOASTs: There is an empty database with a single table and record. The single record gets updated multiple times with 10MB (bytea column). I can see that the table/

Re: TOAST Table / Dead Tuples / Free Pages

Hi You can use the CLUSTER command, which will physically reorder the table based on index, effectively reducing the size of the table without using VACUUM. CLUSTER your_table USING your_index; Or you can use the pg_repack extension as well. pg_repack -d your_database -t your_table Regards Kas

Re: TOAST Table / Dead Tuples / Free Pages

Hi Shenavai, Here are some more options.. VACUUM VACUUM FULL You may also reindex to reclaim space REINDEX TABLE REINDEX INDEX Regards, Muhammad Ikram Bitnine On Thu, Jun 13, 2024 at 1:09 PM Kashif Zeeshan wrote: > Hi > > You can use the CLUSTER command, which will physically reorde

Re: Question about UNIX socket connections and SSL

> On Jun 12, 2024, at 2:17 PM, Tom Lane wrote: > > (1) It'd add overhead without adding any security. Data going through > a UNIX socket will only pass through the local kernel, and if that's > compromised then it's game over anyway. That's true. My preference would be to have an unencrypted c

Re: Questions on logical replication

> Why? what benefit does this provide you?? Add all the tables when creating the publication and be done with it... I get this when trying to understand how this all works on test boxes, but for production NO idea what you're trying to accomplish Adding all tables at once means adding the giga

Re: UPDATE with multiple WHERE conditions

On 2024-Jun-12, David G. Johnston wrote: > On Wed, Jun 12, 2024 at 2:28 PM Rich Shepard > wrote: > > > I have a table with 3492 rows. I want to update a boolean column from > > 'false' to 'true' for 295 rows based on the value of another column. > > I'll often just use a spreadsheet to build th

Re: Question about UNIX socket connections and SSL

> On 12 Jun 2024, at 22:46, Casey & Gina wrote: > ..haproxy doesn't understand the postgres protocol. While not strictly that, there was a patch not too long ago for teaching postgres the PROXY protocol. https://www.postgresql.org/message-id/flat/165903873765.1168.11139166899805820567.pgcf%40co

Re: PG16.1 security breach?

On 6/12/24 18:56, Tom Lane wrote: Ron Johnson writes: On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston < david.g.johns...@gmail.com> wrote: I think my point is that a paragraph like the following may be a useful addition: If one wishes to remove the default privilege granted to public to exe

Re: Definging columns for INSERT statements

On Wed, 12 Jun 2024, Adrian Klaver wrote: The 'Examples' section at the bottom of this page: https://www.postgresql.org/docs/current/sql-insert.html is a useful resource. Adrian, That's good to know. Thank you. Regards, Rich

Syntax on BEFORE Trigger - Cascade?

Good Morning, We have a table - I'll call it *import_job* (which is the actual name) - that lists jobs to be executed. Each job has one or more child components listed in another table called *import_file*. The child table has a foreign key column called *import_job_oid* referencing the primary

Re: TOAST Table / Dead Tuples / Free Pages

On 6/13/24 01:08, Kashif Zeeshan wrote: Hi You can use the CLUSTER command, which will physically reorder the table based on index, effectively reducing the size of the table without using VACUUM. From OP: "I don’t want to use VACUUM FULL due to the exclusive lock." From here https://www.

Re: Syntax on BEFORE Trigger - Cascade?

David Barbour writes: > The files are appropriately deleted in Oracle, but Postgres is returning > the following: > *ERROR: Attempt to suppress referential action with before trigger. > CONTEXT: SQL statement "DELETE FROM ONLY "idev"."import_file" WHERE $1 > OPERATOR(pg_catalog.=) "import_job_oid"

Re: Oracle Linux 9 Detected RPMs with RSA/SHA1 signature

On 6/13/24 06:55, Hans Schou wrote: Reply to list also. Ccing list On Wed, Jun 12, 2024 at 4:34 PM Adrian Klaver > wrote: Take a look at: https://yum.postgresql.org/news/pgdg-rpm-repo-gpg-key-update/

Re: Syntax on BEFORE Trigger - Cascade?

On Thursday, June 13, 2024, David Barbour wrote: > > When a record in *import_job* is deleted, the child records (file > records) in *import_file* need to be deleted first. > > The constraint in both Oracle and Postgres is similar (Postgres version): > *ALTER TABLE IF EXISTS idev.import_file* > >

Re: Questions on logical replication

On Thu, Jun 13, 2024 at 6:01 AM Koen De Groote wrote: > > Why? what benefit does this provide you?? Add all the tables when > creating the publication and be done with it... I get this when trying to > understand how this all works on test boxes, but for production NO idea > what you're tryin

Re: Reset sequence to current maximum value of rows

On Thu, 13 Jun 2024, Ron Johnson wrote: If the table has a primary key, then the command *should* have failed with a duplicate key error as soon as the first dupe was discovered. Ron, I had manually set the PKs (column: company_nbr) which has a sequence defined for it when I added about 50 ro

Re: Reset sequence to current maximum value of rows

On Thu, Jun 13, 2024 at 1:20 PM Rich Shepard wrote: > Two tables have a sequence for the PK. Over time I manually entered the PK > numbers not being aware of applying DEFAULT to generate the next number. > > I just tried to set one table's PK sequence to the current max(PK) value > using this exp

Re: Reset sequence to current maximum value of rows

On Thu, Jun 13, 2024 at 2:38 PM Rich Shepard wrote: > On Thu, 13 Jun 2024, Ron Johnson wrote: > > > If the table has a primary key, then the command *should* have failed > with > > a duplicate key error as soon as the first dupe was discovered. > > Ron, > > I had manually set the PKs (column: com

Re: Reset sequence to current maximum value of rows

On 6/13/24 11:38, Rich Shepard wrote: On Thu, 13 Jun 2024, Ron Johnson wrote: Today I saw that I had missed one new company and entered it using DEFAULT for the company_nbr PK. When I looked at that table every company_name that I had added yesterday was changed to the one inserted today. Y

Reserving GUC prefixes from a non-preloaded DB extension is not always enforced

Hi all, I am an extension developer. I use `MarkGUCPrefixReserved` to reserve GUC prefixes, which my extension uses to help avoid accidentally misspelled config-file entries. However, since the reservation happens in `_PG_init()` and `_PG_init()` is not called until the first use of an API expose

Re: Reserving GUC prefixes from a non-preloaded DB extension is not always enforced

Narek Galstyan writes: > I am an extension developer. I use `MarkGUCPrefixReserved` to reserve GUC > prefixes, which my extension uses to help avoid accidentally misspelled > config-file entries. > However, since the reservation happens in `_PG_init()` and `_PG_init()` is > not called until the f

Re: Reset sequence to current maximum value of rows

On Thu, 13 Jun 2024, Adrian Klaver wrote: You sure you did not actually do an UPDATE without a WHERE? Adrian, Yep. There was no row to update as I was adding a new company. Regards, Rich

Re: Reset sequence to current maximum value of rows

On Thu, 13 Jun 2024, David G. Johnston wrote: Because you specified company_name in the column listing for the things you are inserting values for. So in column position 2 you must have a value than can be inserted into the company_name column. It is utterly immaterial how you specified the valu

Re: Reset sequence to current maximum value of rows

On Thu, 13 Jun 2024, Ron Johnson wrote: No need to do that. Just write: INSERT INTO public.companies (company_name, , industry, status) VALUES ('Berkshire Hathaway', 'Conglomerate', 'Mumble'); The next value of companies_org_nbr_seq will automatically be taken and inserted into the table.

Re: Reset sequence to current maximum value of rows

On Thu, Jun 13, 2024 at 12:57 PM Rich Shepard wrote: > INSERT into companies (company_nbr,company_name,industry,status) VALUES > (DEFAULT,'A new company name', 'Manufacturing',DEFAULT); > > Yesterday, before learning to use DEFAULT for the company_nbr PK I entered > all rows using company_nbr 234

Re: Reset sequence to current maximum value of rows [RESOLVED]

On Thu, 13 Jun 2024, David G. Johnston wrote: There is no way, in the absence of a user trigger, that the above insert command changed pre-existing rows. And if you cannot reproduce the behavior you claim to have seen I will continue to just assume you have faulty memory. David, While there m

Re: Reset sequence to current maximum value of rows

> Aha! So it's likely that by listing the PK column name in the list of > columns to be inserted was what caused the problem? No need to specify > DEFAULT for it? > There's no need to specify the column if it has a default value, but specifying it did not cause the issue that you saw.

Re: Reset sequence to current maximum value of rows

On Thu, Jun 13, 2024 at 11:24 AM Rich Shepard wrote: > On Thu, 13 Jun 2024, David G. Johnston wrote: > > > You need to show your work here. As your PK is a number it cannot have a > > company name as a value and so this doesn't make sense. > > insert into companies (company_nbr,company_name,indu

Re: Reset sequence to current maximum value of rows

On Thu, Jun 13, 2024 at 10:20 AM Rich Shepard wrote: > Two tables have a sequence for the PK. Over time I manually entered the PK > numbers not being aware of applying DEFAULT to generate the next number. > > I just tried to set one table's PK sequence to the current max(PK) value > using this ex

Re: Reset sequence to current maximum value of rows

On Thu, 13 Jun 2024, Jeremy Smith wrote: There's no need to specify the column if it has a default value, but specifying it did not cause the issue that you saw. Jeremy, I did not know this. While the reason for the issue shall remain unknown, it did happen and my update script restored order

Re: Reset sequence to current maximum value of rows

On Thu, 13 Jun 2024, David G. Johnston wrote: You need to show your work here. As your PK is a number it cannot have a company name as a value and so this doesn't make sense. David, insert into companies (company_nbr,company_name,industry,status) values (DEFAULT,'new company name','Industry'

Reset sequence to current maximum value of rows

Two tables have a sequence for the PK. Over time I manually entered the PK numbers not being aware of applying DEFAULT to generate the next number. I just tried to set one table's PK sequence to the current max(PK) value using this expression from a stackexchange thread: SELECT setval('', , true)

Re: Reset sequence to current maximum value of rows

On 6/13/24 12:57, Rich Shepard wrote: On Thu, 13 Jun 2024, David G. Johnston wrote: Because you specified company_name in the column listing for the things you are inserting values for. So in column position 2 you must have a value than can be inserted into the company_name column. It is utterl

Re: Reset sequence to current maximum value of rows

On Thu, 13 Jun 2024, Adrian Klaver wrote: Not with: Table "public.companies" [...] Indexes: "organizations_pkey" PRIMARY KEY, btree (company_nbr) That would throw duplicate key errors. Are you sure that you did not do this on the contacts table as the company FK back to companies

Re: Reset sequence to current maximum value of rows

On 6/13/24 15:13, Rich Shepard wrote: On Thu, 13 Jun 2024, Adrian Klaver wrote: Not with: Table "public.companies" [...] Indexes:    "organizations_pkey" PRIMARY KEY, btree (company_nbr) That would throw duplicate key errors. Are you sure that you did not do this on the contacts table a

Re: Reset sequence to current maximum value of rows

On Thu, Jun 13, 2024 at 3:13 PM Rich Shepard wrote: > Yes, I'm sure. Early yesterday I did get duplicate key errors. That's when > I > looked on stackexchange to learn how to reset the sequence's max value to > the value of the number of rows in the table. Not only did my attempt to > add > a sin

Configure autovacuum

Hi everyone, I would like to configure the autovacuum in a way that it runs very frequently (i.e. after each update-statement). I tried the following settings on my table: alter table mytable set (autovacuum_vacuum_scale_factor = 0.0); alter table mytable set (autovacuum_vacuum_cost_delay = 0.0