Re: postgres table statistics

[Chandy G]

 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 fails at the driver
> level when records of large size (say 200mb each) flows through.
> this forces to reduce the fetch size (if were to operate at a fixed
> Xmx setting of client jvm).


Did you try the driver's "adaptive fetch" feature?


https://jdbc.postgresql.org/documentation/use/#connection-parameters



  

Re: Long running query causing XID limit breach

[yudhi s]

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 and thus we need
> to have appropriate monitoring in place to find this and get alerted
> beforehand. And also a monitoring to see how much WAL gets generated per
> hour/day to get an idea of the usage. I am not sure how to do it , but will
> check on this.
>


Not exactly related but just for our information, While going through the
"aurora postgres" database docs in regards to similar concepts which are
getting discussed here, I am finding some interesting stuff.

https://aws.amazon.com/blogs/database/manage-long-running-read-queries-on-amazon-aurora-postgresql-compatible-edition/




*Cancel the conflicting query on the reader node if the conflict lasts
longer than max_standby_streaming_delay (maximum 30 seconds). This is
different from Amazon RDS or self-managed PostgreSQL. With Amazon RDS or
self-managed PostgreSQL, the instance has its own physical copy of the
database, and you’re able to set the parameter max_standby_streaming_delay
as high as you want to prevent query cancellation.If the conflicting query
can’t cancel in time, or if multiple long-running queries are causing the
replication lag to go beyond 60 seconds, Aurora restarts the reader node to
ensure it’s not lagging far behind the primary node.*

So if i get it correct it means, even if hot_standby_feedback is set to OFF,
the constraints of max_standby_streaming_delay (30 seconds) and the
60-second replication lag limit applies. And thus Aurora may cancel
long-running queries or restart reader nodes to maintain synchronization
even if it just runs for >60seconds.  So it's really odd but does that mean
, by no way you can guarantee a query to run >60 seconds on read replica in
aurora postgres?

Re: DROP COLLATION vs pg_collation question

[Laurenz Albe]

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_oid - find a collation by possibly qualified name
   *
   * Note that this will only find collations that work with the current
   * database's encoding.
   */
  Oid
  get_collation_oid(List *collname, bool missing_ok)

Yours,
Laurenz Albe

TOAST Table / Dead Tuples / Free Pages

[Shenavai, Manuel]

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/toasttable 
size is growing (500MB).

Now I tried to find a way to get the DB size down again (it should be around 
10MB instead of 500MB). I don’t want to use VACUUM FULL due to the exclusive 
lock.

Is there any way to remove the dead tuples and free the pages?

Thanks in advance &
Best regards,
Manuel

Re: TOAST Table / Dead Tuples / Free Pages

[Kashif Zeeshan]

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
Kashif Zeeshan



On Thu, Jun 13, 2024 at 12:55 PM Shenavai, Manuel 
wrote:

> 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/toasttable size is growing (500MB).
>
>
>
> Now I tried to find a way to get the DB size down again (it should be
> around 10MB instead of 500MB). I don’t want to use VACUUM FULL due to the
> exclusive lock.
>
>
>
> Is there any way to remove the dead tuples and free the pages?
>
>
>
> Thanks in advance &
>
> Best regards,
>
> Manuel
>
>
>

Re: TOAST Table / Dead Tuples / Free Pages

[Muhammad Ikram]

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 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
> Kashif Zeeshan
>
>
>
> On Thu, Jun 13, 2024 at 12:55 PM Shenavai, Manuel 
> wrote:
>
>> 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/toasttable size is growing (500MB).
>>
>>
>>
>> Now I tried to find a way to get the DB size down again (it should be
>> around 10MB instead of 500MB). I don’t want to use VACUUM FULL due to the
>> exclusive lock.
>>
>>
>>
>> Is there any way to remove the dead tuples and free the pages?
>>
>>
>>
>> Thanks in advance &
>>
>> Best regards,
>>
>> Manuel
>>
>>
>>
>

-- 
Muhammad Ikram

Re: Question about UNIX socket connections and SSL

[Casey & Gina]

> 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 connection via UNIX 
socket from the application to haproxy, then an encrypted connection using SSL 
certificate authentication from haproxy to the database.  I spent some time 
attempting this.  But that doesn't seem to be possible since haproxy doesn't 
understand the postgres protocol.

-- 
Regards,
- Casey

Re: Questions on logical replication

[Koen De Groote]

> 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 gigantic tables as well. Disk IO
and Network traffic are a serious concern, increased CPU usage affecting
queries of the live system, as well as transaction wraparound.

Initial sync can be a serious concern, depending on the size of the table.

Here's a nice guide where people did a logical replication upgrade,
explaining why they did it this way:
https://knock.app/blog/zero-downtime-postgres-upgrades

On Wed, Jun 12, 2024 at 7:01 PM Justin  wrote:

>
>
> On Tue, Jun 11, 2024 at 5:43 PM Koen De Groote  wrote:
>
>> > If there are any errors during the replay of WAL such as missing
>> indexes for Replica Identities during an Update or Delete  this will cause
>> the main subscriber worker slot on the publisher to start backing up WAL
>> files
>>
>> And also if the connection breaks, from what I understand, is that
>> correct? Anything that stops the subscription, including disabling the
>> subscription, is that right?
>>
>
> Yes to all
>
>
>> > I suggest confirming all tables have replica identities or primary keys
>> before going any further.
>>
>> Yes, I am aware of this. I made me a small script that prints which
>> tables I have added to the publication and are done syncing, and which are
>> currently not being replicated.
>>
>
>
>>
>> > With PG 11 avoid REPLICA IDENTITY FULL as this causes full table scan
>> on the subscriber for PG 15 and earlier.
>>
>> I'm also aware of this. My plan is to create a publication with no
>> tables, and add them 1 by 1, refreshing the subscriber each time.
>>
>
> 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
>
>
>> I'm not planning on using "REPLICA IDENTITY FULL" anywhere.
>>
> Good
>

Re: UPDATE with multiple WHERE conditions

[Alvaro Herrera]

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 the 295 update commands and
> copy-paste them into psql or whatnot.

A closely related technique: if you have a query that generates the
UPDATE commands you need, you can run it under \gexec in psql, and
they'll be executed.

-- 
Álvaro Herrera   48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
Al principio era UNIX, y UNIX habló y dijo: "Hello world\n".
No dijo "Hello New Jersey\n", ni "Hello USA\n".

Re: Question about UNIX socket connections and SSL

[Daniel Gustafsson]

> 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%40coridan.postgresql.org#e151c884786c5ddf5bf49253964f841e

--
Daniel Gustafsson

Re: PG16.1 security breach?

[Joe Conway]

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 execute
all newly created procedures it is necessary to revoke that privilege for
every superuser in the system



That seems... excessive.


More to the point, it's wrong.  Superusers have every privilege there
is "ex officio"; we don't even bother to look at the catalog entries
when considering a privilege check for a superuser.  Revoking their
privileges will accomplish nothing, and it does nothing about the
actual source of the problem (the default grant to PUBLIC) either.

What I'd do if I didn't like this policy is some variant of

ALTER DEFAULT PRIVILEGES IN SCHEMA public
   REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;


In a past blog[1] I opined that this cleans up the default security 
posture fairly completely:


8<--
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE EXECUTE ON ALL ROUTINES IN SCHEMA public FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE EXECUTE ON ROUTINES FROM PUBLIC;

-- And/or possibly, more drastic options:
-- REVOKE USAGE ON SCHEMA public FROM PUBLIC;
-- DROP SCHEMA public CASCADE;

REVOKE TEMPORARY ON DATABASE  FROM PUBLIC;
REVOKE USAGE ON LANGUAGE sql, plpgsql FROM PUBLIC;
8<--


Repeat for each schema that you think might be publicly readable
(which is only public by default).


indeed


BTW, in PG 15 and up, the public schema is not writable by
default, which attacks basically the same problem from a different
direction.


also a good point


[1] 
https://www.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-2


--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Re: Definging columns for INSERT statements

[Rich Shepard]

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?

[David Barbour]

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 key in *import_file*.

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*



*ADD CONSTRAINT fk1_import_file FOREIGN KEY (import_job_oid)
REFERENCES idev.import_job (oid) MATCH SIMPLEON UPDATE NO ACTIONON
DELETE CASCADE;*

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"" *

There aren't any delete triggers for either table. Any idea why this isn't
working? Does cascade function differently in Postgres? Read the docs,
Googled the heck out of this and played all sorts of games with the tables.
I've also tried creating a before trigger on import_job, but can't seem to
get the right syntax for taking the oid from the psql delete picked up by
the trigger.

Here is one of my (many) attempts (have tried describing, setting, using
new.oid, old.oid, a bunch of stuff) and can't get this right either:

*CREATE OR REPLACE FUNCTION idev."td_import_job$import_job"() ** RETURNS
trigger ** LANGUAGE 'plpgsql' ** VOLATILE NOT LEAKPROOF **AS $BODY$ * *BEGIN
** RAISE NOTICE 'Value %', new.oid ** DELETE FROM idev.import_file ** WHERE
import_job_oid = new.oid; **RETURN OLD; **END; **$BODY$; * *delete from
idev.import_job where oid = 44949; * *NOTICE: Value  * *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"  *




-- 

*David A. Barbour*

*dbarb...@istation.com *

*(214) 292-4096*

Istation

8150 North Central Expressway, Suite 2000

Dallas, TX 75206

www.Istation.com 



CONFIDENTIALITY / PROPRIETARY NOTICE:

The information contained in this e-mail, including any attachment(s), is
confidential information that may be privileged and exempt from disclosure
under applicable law. If the reader of this message is not the intended
recipient, or if you received this message in error, then any direct or
indirect disclosure, distribution or copying of this message is strictly
prohibited. If you have received this message in error, please notify
Istation by calling 866-883-7323 immediately and by sending a return
e-mail; delete this message; and destroy all copies, including attachments.

Thank you.

Re: TOAST Table / Dead Tuples / Free Pages

[Adrian Klaver]

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.postgresql.org/docs/current/sql-cluster.html

"When a table is being clustered, an ACCESS EXCLUSIVE lock is acquired 
on it. This prevents any other database operations (both reads and 
writes) from operating on the table until the CLUSTER is finished."





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
Kashif Zeeshan



On Thu, Jun 13, 2024 at 12:55 PM Shenavai, Manuel 
mailto:manuel.shena...@sap.com>> wrote:


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/toasttable size is growing (500MB).

__ __

Now I tried to find a way to get the DB size down again (it should
be around 10MB instead of 500MB). I don’t want to use VACUUM FULL
due to the exclusive lock.

__ __

Is there any way to remove the dead tuples and free the pages?

__ __

Thanks in advance &

Best regards,

Manuel

__ __



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

Re: Syntax on BEFORE Trigger - Cascade?

[Tom Lane]

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"" *

I don't know what you're running there, but there is no such error
message in community Postgres.  Having said that, maybe what you
need is to *not* have any before trigger applied to the import_file
table.

regards, tom lane

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

[Adrian Klaver]

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/



Thanks. That was sorting it out.

In /var/log/leapp/leapp-report.txt I get »Packages not signed by Oracle 
found on the system« but that is of course expected.


If anyone interested, I did:

dnf --disablerepo=* -y install 
https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm 


Then the repo was disabled and gpgkey changed, add new gpgkey in the 
section:

/etc/yum.repos.d/pgdg-redhat-all.repo
[pgdg16]
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL

and then I could run »leapp preupgrade --oraclelinux« and fix the other 
errors.


--
𝕳𝖆𝖓𝖘 𝕾𝖈𝖍𝖔𝖚
☏ ➁➁ ➅➃ ➇⓪ ➁⓪


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

Re: Syntax on BEFORE Trigger - Cascade?

[David G. Johnston]

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*
>
>
>
> *ADD CONSTRAINT fk1_import_file FOREIGN KEY (import_job_oid)
> REFERENCES idev.import_job (oid) MATCH SIMPLEON UPDATE NO ACTIONON
> DELETE CASCADE;*
>

This, by itself, should work.  If it isn’t, please provide a self-contained
test case demonstrating that fact so it can be investigated/explained.

What version are you running?


> There aren't any delete triggers for either table. Any idea why this isn't
> working? Does cascade function differently in Postgres?
>
>
Nope (to both)


>
> I've also tried creating a before trigger on import_job,
>

Why?


>
> but can't seem to get the right syntax for taking the oid from the psql
> delete picked up by the trigger.
>

Your broken attempt to do this is likely what is causing the error.


>
> Here is one of my (many) attempts (have tried describing, setting, using
> new.oid, old.oid, a bunch of stuff) and can't get this right either:
>

Delete only populates OLD.

David J.

Re: Questions on logical replication

[Justin]

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 trying to accomplish
>
> Adding all tables at once means adding the gigantic tables as well. Disk
> IO and Network traffic are a serious concern, increased CPU usage affecting
> queries of the live system, as well as transaction wraparound.
>
> Initial sync can be a serious concern, depending on the size of the table.
>

The number of initial  sync workers can be controlled
via max_sync_workers_per_subscription
see https://www.postgresql.org/docs/current/logical-replication-config.html


if you want to do one table at a time just set sync workers to 1.

If bandwidth is a problem  either from the disk or network, direct the
network traffic from the subscriber through a proxy or firewall to throttle
the network speed.  Slowing the copy will cause the WAL to build up  on the
publisher

CPU load on the publisher is very low its actually hard to see it doing
anything as its just reading the disk, streaming it to the subscriber..

For large tables with lots of indexes for the copy to complete as fast as
possible to prevent WAL build up, drop indexes.  For me the WAL build up
has only been an issue when dealing with multi-TB sized tables when it
takes several days to copy the data  for one table.

One trick is to remove all the indexes during the initial sync except for
the primary key so the subscriber has less work to do.


> Here's a nice guide where people did a logical replication upgrade,
> explaining why they did it this way:
> https://knock.app/blog/zero-downtime-postgres-upgrades
>

The blog suggests overly complicated things.  only doing 100GB chunks of
data at one time.  Maybe  if the publisher was scarce on resources or the
table is multi-TB in size it requires days to weeks to copy...

If the publisher is so low on resources that Logical Replication is
problematic  one can create a binary replica, promote it and convert it to
logical replication skipping the initial sync.  Then upgrade that server.
There is a minor outage required to convert a binary replica to a logical
replica.  I've done it in under 30 seconds.




>
> On Wed, Jun 12, 2024 at 7:01 PM Justin  wrote:
>
>>
>>
>> On Tue, Jun 11, 2024 at 5:43 PM Koen De Groote  wrote:
>>
>>> > If there are any errors during the replay of WAL such as missing
>>> indexes for Replica Identities during an Update or Delete  this will cause
>>> the main subscriber worker slot on the publisher to start backing up WAL
>>> files
>>>
>>> And also if the connection breaks, from what I understand, is that
>>> correct? Anything that stops the subscription, including disabling the
>>> subscription, is that right?
>>>
>>
>> Yes to all
>>
>>
>>> > I suggest confirming all tables have replica identities or primary
>>> keys before going any further.
>>>
>>> Yes, I am aware of this. I made me a small script that prints which
>>> tables I have added to the publication and are done syncing, and which are
>>> currently not being replicated.
>>>
>>
>>
>>>
>>> > With PG 11 avoid REPLICA IDENTITY FULL as this causes full table scan
>>> on the subscriber for PG 15 and earlier.
>>>
>>> I'm also aware of this. My plan is to create a publication with no
>>> tables, and add them 1 by 1, refreshing the subscriber each time.
>>>
>>
>> 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
>>
>>
>>> I'm not planning on using "REPLICA IDENTITY FULL" anywhere.
>>>
>> Good
>>
>

Re: Reset sequence to current maximum value of rows

[Rich Shepard]

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 rows to the table yesterday.

Now that I'm aware of the DEFAULT option when inserting new rows I tried
to reset the sequence maximum number to max(company_nbr); the highest number
for the rows inserted yesterday. That's when I tried resetting the current
sequence number with the expectation that new rows would be numbered
sequentially higher than that value.

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.


What does your table definition look like?


 Table "public.companies"
Column| Type  | Collation | Nullable |  
Default

--+---+---+--+--
 company_nbr  | integer   |   | not null | 
nextval('companies_org_nbr_seq'::regclass)
 company_name | character varying(64) |   | not null | '??'::character 
varying
 url  | character varying(64) |   |  |
 email| character varying(64) |   |  |
 industry | character varying(24) |   | not null | 
'Other'::character varying
 status   | character varying(20) |   | not null | 
'Opportunity'::character varying
 comment  | text  |   |  |
 ea_nbr   | integer   |   |  | 0
 ea_amt   | numeric(10,2) |   |  | 0.00
Indexes:
"organizations_pkey" PRIMARY KEY, btree (company_nbr)
Foreign-key constraints:
"organizations_industry_fkey" FOREIGN KEY (industry) REFERENCES 
industrytypes(ind_name) ON UPDAT
E CASCADE ON DELETE RESTRICT
"organizations_status_fkey" FOREIGN KEY (status) REFERENCES 
statustypes(stat_name) ON UPDATE CAS
CADE ON DELETE RESTRICT
Referenced by:
TABLE "locations" CONSTRAINT "locations_org_nbr_fkey" FOREIGN KEY
(company_nbr) REFERENCES companies(company_nbr) ON UPDATE CASCADE ON DELETE
RESTRICT
TABLE "people" CONSTRAINT "people_org_nbr_fkey" FOREIGN KEY
(company_nbr) REFERENCES companies(c ompany_nbr) ON UPDATE CASCADE ON DELETE
RESTRICT

Rich

Re: Reset sequence to current maximum value of rows

[Ron Johnson]

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 expression from a stackexchange thread:
> SELECT setval('', , true);  -- next
> value will be max(PK) + 1
>
> Needing to add a new row to a table for a specific industry table (with 52
> rows) I set the PK as DEFAULT in the INSERT INTO expression. To my surprise
> and disappointment all 52 rows now have the company_name column as the
> newly
> inserted name. Feh! I need to restore all the correct names for each PK.
>

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.

What does your table definition look like?

Re: Reset sequence to current maximum value of rows

[Ron Johnson]

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: company_nbr) which has a sequence
> defined for it when I added about 50 rows to the table yesterday.
>
> Now that I'm aware of the DEFAULT option when inserting new rows I tried
> to reset the sequence maximum number to max(company_nbr); the highest
> number
> for the rows inserted yesterday. That's when I tried resetting the current
> sequence number with the expectation that new rows would be numbered
> sequentially higher than that value.
>
> Today I saw that I had missed one new company and entered it using DEFAULT
> for the company_nbr PK.


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.

When I looked at that table every company_name that
> I had added yesterday was changed to the one inserted today.
>

You'll have to show us what you did.

Re: Reset sequence to current maximum value of rows

[Adrian Klaver]

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.


You sure you did not actually do an UPDATE without a WHERE?




What does your table definition look like?


  Table "public.companies"
     Column    | Type  | Collation | Nullable 
|  Default


--+---+---+--+--
  company_nbr  | integer   |   | not null | 
nextval('companies_org_nbr_seq'::regclass)
  company_name | character varying(64) |   | not null | 
'??'::character varying

  url  | character varying(64) |   |  |
  email    | character varying(64) |   |  |
  industry | character varying(24) |   | not null | 
'Other'::character varying
  status   | character varying(20) |   | not null | 
'Opportunity'::character varying

  comment  | text  |   |  |
  ea_nbr   | integer   |   |  | 0
  ea_amt   | numeric(10,2) |   |  | 0.00
Indexes:
     "organizations_pkey" PRIMARY KEY, btree (company_nbr)
Foreign-key constraints:
     "organizations_industry_fkey" FOREIGN KEY (industry) REFERENCES 
industrytypes(ind_name) ON UPDAT

E CASCADE ON DELETE RESTRICT
     "organizations_status_fkey" FOREIGN KEY (status) REFERENCES 
statustypes(stat_name) ON UPDATE CAS

CADE ON DELETE RESTRICT
Referenced by:
     TABLE "locations" CONSTRAINT "locations_org_nbr_fkey" FOREIGN KEY
(company_nbr) REFERENCES companies(company_nbr) ON UPDATE CASCADE ON DELETE
RESTRICT
     TABLE "people" CONSTRAINT "people_org_nbr_fkey" FOREIGN KEY
(company_nbr) REFERENCES companies(c ompany_nbr) ON UPDATE CASCADE ON 
DELETE

RESTRICT

Rich




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

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

[Narek Galstyan]

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 exposed by my extension,
misspelled config-file entries that get executed before the extension is
loaded will not throw an error.

SET lantern.haha = 1; -- succeeds, since lantern extension is not loaded

SELECT ARRAY[1] <-> ARRAY[1]; -- uses a lantern API, so extension binary is
loaded
-- The line above does warn about removing the configuration parameter above
-- WARNING:  invalid configuration parameter name "lantern.haha", removing
it
-- DETAIL:  "lantern" is now a reserved prefix.

 SET lantern.haha = 1; -- now this throws an error
-- ERROR:  invalid configuration parameter name "lantern.haha"
-- DETAIL:  "lantern" is a reserved prefix.

I think, ideally, the last error should be thrown in the first SET
execution as well.

I'd expect GUC variables reserved by an extension to live more permanently
in Postgres catalogs (e.g., in pg_settings).
So, even when the extension binary is not loaded, Postgres would know which
prefixes are reserved and which GUC settings must be allowed (similar to
how Postgres knows in pg_extension which extensions are enabled, even when
the corresponding extension binary has not been loaded).

1. Would you consider the proposed behavior an improvement?

2. If so, do you have thoughts on how to implement it?

Thanks!
Narek Galstyan
--

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

[Tom Lane]

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 first use of an API exposed by my extension,
> misspelled config-file entries that get executed before the extension is
> loaded will not throw an error.

No, but a warning will be reported when the extension does get loaded.

This seems in line to me with the general behavior of
extension-defined GUCs: we cannot know anything about whether a value
stored in the config file is sane until we have loaded the extension
that defines the GUC's data type, allowed range, etc.

> I'd expect GUC variables reserved by an extension to live more permanently
> in Postgres catalogs (e.g., in pg_settings).

How would they get there?  What happens when the extension goes away?
How would such an approach emulate C-code-enforced restrictions,
that is checks made by a GUC check_hook?  What happens if different
databases in an installation have inconsistent catalog entries for
a GUC?  (You could eliminate such inconsistency by storing the data
in a shared catalog, perhaps, but that brings some other concerns.)

I don't really see the value for work expended here.

regards, tom lane

Re: Reset sequence to current maximum value of rows

[Rich Shepard]

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

[Rich Shepard]

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 value for column position 1.



We can't help you understand if you don't show a complete working example
and ask a question in relation to that example.  I suggest you start from
scratch, this time using scripts, so that your work is recorded and
replayable.


David,

INSERT into companies (company_nbr,company_name,industry,status) VALUES
(DEFAULT,'A new company name', 'Manufacturing',DEFAULT);

I always write scrips for SQL, R, GRASS, Python, bash. Above is a redacted
version of the single name I tried adding to the companies table.

Yesterday, before learning to use DEFAULT for the company_nbr PK I entered
all rows using company_nbr 2342-2391. This morning, after running the
single-line INSERT command company numbers from 2341-2392 all had 'A new
company name' as the company_name.

HTH,

Rich

Re: Reset sequence to current maximum value of rows

[Rich Shepard]

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.


Ron,

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?

I'm writing a script to enter all contact within these companies. I'll leave
out 'person_nbr' in the list of columns and DEFAULT at the beginning of the
VALUES () section.

I didn't pick this up in my readings.

Thank you,

Rich

Re: Reset sequence to current maximum value of rows

[David G. Johnston]

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 2342-2391. This morning, after running the
> single-line INSERT command company numbers from 2341-2392 all had 'A new
> company name' as the company_name.
>
>
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 J.

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

[Rich Shepard]

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 might be no way that what happened could happen, did happen.

You're welcome to your opinion about my memory yet my eyes saw the results
of the select statement.

Regardless, I updated all of yesterday's company insertions so they're now
all correct (again.) Found another one I missed then and successfully
inserted it without specifying the PK field or its DEFAULT value as I
learned from Ron's reply.

Regards,

Rich

Re: Reset sequence to current maximum value of rows

[Jeremy Smith]

> 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

[David G. Johnston]

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,industry,status) values
> (DEFAULT,'new company name','Industry','Opportunity')
>
> With DEFAULT as the company_nbr why can't the company_name not have a
> value?
>

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 value for column position 1.


> > If you use the default when inserting the next value in the sequence is
> > used.
>
> Yes, that's the theory. It didn't work for me and why it didn't is what I
> want to understand.
>
>
We can't help you understand if you don't show a complete working example
and ask a question in relation to that example.  I suggest you start from
scratch, this time using scripts, so that your work is recorded and
replayable.

David J.

Re: Reset sequence to current maximum value of rows

[David G. Johnston]

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 expression from a stackexchange thread:
> SELECT setval('', , true);  -- next
> value will be max(PK) + 1
>
> Needing to add a new row to a table for a specific industry table (with 52
> rows) I set the PK as DEFAULT in the INSERT INTO expression. To my surprise
> and disappointment all 52 rows now have the company_name column as the
> newly
> inserted name. Feh! I need to restore all the correct names for each PK.
>

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.


> There's an alternate expression in that SE thread that I didn't try:
> ALTER SEQUENCE  RESTART WITH ;
>

This is identical in action to the setval function call you performed.


> I want to avoid this same situation when resetting the second table's PK
> sequence number and would like to understand why the SELECT expression
> changed all column values


It didn't...


> rather than adding a new row with its attributes.
>

It wouldn't do this either...

And how to I reset sequences to ignore all current values


This doesn't make sense...

while adding the
> next higher value to the end when a new row is INSERTed.
>
>
If you use the default when inserting the next value in the sequence is
used.

David J.

Re: Reset sequence to current maximum value of rows

[Rich Shepard]

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 to the table.

Thanks,

Rich

Re: Reset sequence to current maximum value of rows

[Rich Shepard]

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','Opportunity')

With DEFAULT as the company_nbr why can't the company_name not have a value?


It didn't...


I changed the company_name for all company_nbr using the 'new company name'
for all rows with 'Industry' in that column.


And how to I reset sequences to ignore all current values
This doesn't make sense...


Then I cannot use the sequence for adding new rows in the table and I must
manually enter each PK number?


If you use the default when inserting the next value in the sequence is
used.


Yes, that's the theory. It didn't work for me and why it didn't is what I
want to understand.

Regards,

Rich

Reset sequence to current maximum value of rows

[Rich Shepard]

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);  -- next value 
will be max(PK) + 1

Needing to add a new row to a table for a specific industry table (with 52
rows) I set the PK as DEFAULT in the INSERT INTO expression. To my surprise
and disappointment all 52 rows now have the company_name column as the newly
inserted name. Feh! I need to restore all the correct names for each PK.

There's an alternate expression in that SE thread that I didn't try:
ALTER SEQUENCE  RESTART WITH ;

I want to avoid this same situation when resetting the second table's PK
sequence number and would like to understand why the SELECT expression
changed all column values rather than adding a new row with its attributes.
And how to I reset sequences to ignore all current values while adding the
next higher value to the end when a new row is INSERTed.

TIA,

Rich

Re: Reset sequence to current maximum value of rows

[Adrian Klaver]

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 utterly
immaterial how you specified the value for column position 1.



We can't help you understand if you don't show a complete working example
and ask a question in relation to that example.  I suggest you start from
scratch, this time using scripts, so that your work is recorded and
replayable.


David,

INSERT into companies (company_nbr,company_name,industry,status) VALUES
(DEFAULT,'A new company name', 'Manufacturing',DEFAULT);

I always write scrips for SQL, R, GRASS, Python, bash. Above is a redacted
version of the single name I tried adding to the companies table.

Yesterday, before learning to use DEFAULT for the company_nbr PK I entered
all rows using company_nbr 2342-2391. This morning, after running the


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?




single-line INSERT command company numbers from 2341-2392 all had 'A new
company name' as the company_name.

HTH,

Rich




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

Re: Reset sequence to current maximum value of rows

[Rich Shepard]

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?


Adrian,

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 single new company to the companies table change all company names in that
one industry to the new name, but I just discovered that it changed all rows
in that column to the new company name:

 company_nbr |  company_name 
-+-

   1 | Markowitz Herbold PC
   2 | Markowitz Herbold PC
   3 | Markowitz Herbold PC
   4 | Markowitz Herbold PC
   5 | Markowitz Herbold PC
   6 | Markowitz Herbold PC
   7 | Markowitz Herbold PC
   8 | Markowitz Herbold PC
   9 | Markowitz Herbold PC
  10 | Markowitz Herbold PC
  11 | Markowitz Herbold PC
  12 | Markowitz Herbold PC
  13 | Markowitz Herbold PC
  14 | Markowitz Herbold PC
  15 | Markowitz Herbold PC
  16 | Markowitz Herbold PC
  17 | Markowitz Herbold PC
  18 | Markowitz Herbold PC
  19 | Markowitz Herbold PC
  20 | Markowitz Herbold PC
  22 | Markowitz Herbold PC
  23 | Markowitz Herbold PC
--More--

So now I need to extract the companies table data from my 2024-06-10 backup
and use that to update the entire table. Sigh. There are 2101 rows in that
table and I must have forgotten to specify industry for that one new
addition. Not like me to do so, but it's the only explanation I have.

It might be quicker for me to restore the entire database from that backup
and then insert all new table rows since I have saved all the scripts.

Regards,

Rich

Re: Reset sequence to current maximum value of rows

[Adrian Klaver]

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 as the 
company FK back to companies?


Adrian,

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 single new company to the companies table change all company names in 
that
one industry to the new name, but I just discovered that it changed all 
rows

in that column to the new company name:


BEGIN;





Then either

ROLLBACK;
COMMIT;

depending on the result of check.




Regards,

Rich




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

Re: Reset sequence to current maximum value of rows

[David G. Johnston]

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 single new company to the companies table change all company names in
> that
> one industry to the new name, but I just discovered that it changed all
> rows
> in that column to the new company name:
>
>
At present the belief there is a bug in PostgreSQL is unsubstantiated.

I suggest you look internally for how an update command that caused the
resultant data could have been executed.  That is much more plausible, and
thus a better use of time, if you want to spend more time on this, than
trying to produce the observed behavior again using just insert and
setval(...) commands.

David J.

Configure autovacuum

[Shenavai, Manuel]

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);
alter table mytable set (autovacuum_vacuum_cost_limit  = 1);
alter table mytable set (autovacuum_vacuum_threshold  = 1);

I do a lot of updates on a single tuple and I would expect that the autovacuum 
would start basically after each update (due to autovacuum_vacuum_threshold=1). 
But the autovacuum is not running.

Is it possible to configure postgres to autovacuum very aggressively (i.e. 
after each update-statement)?

Thanks in advance &
Best regards,
Manuel