Re: Memory issues with PostgreSQL 15

[Ron Johnson]

On Thu, Jul 25, 2024 at 6:59 AM Christian Schröder <
christian.schroe...@wsd.com> wrote:

> Hi all,
> I started this discussion in May and was then dragged into other topics,
> so I could never follow up. Sorry for that!
> Since then, the problem has resurfaced from time to time. Right now, we
> seem to have issues again, which gives me the opportunity to follow up on
> your various suggestions.
>
> The current error messages are similar to what we have seen before:
>
> <2024-07-25 12:27:38 CEST - > LOG:  could not fork autovacuum worker
> process: Cannot allocate memory
> <2024-07-25 12:27:38 CEST - mailprocessor> ERROR:  could not resize shared
> memory segment "/PostgreSQL.1226901392" to 189280 bytes: No space left on
> device
>

What's mailprocessor?  Maybe it's using some tmpfs device.

>
>

Re: Issue while creating index dynamically

[Ron Johnson]

On Tue, Jul 23, 2024 at 4:10 PM veem v  wrote:

> Hi,
> It's postgres version 15.4. We have a requirement to create an index on a
> big partition table and want to do it online. And to make the script run in
> an automated way on any day , through our ci/cd pipeline we were trying to
> write it as below inside a begin/end block. I.e. create index using "ON
> ONLY" option and then create index on each partition using 'CONCURRENTLY"
> key word and then attach the index partitions to the main index, something
> as below.
>
> But we are getting an error while executing saying it cant be executed in
> transaction block with "CONCURRENTLY". So I want to understand , is there
> any alternate way to get away with this?
>
>   EXECUTE format('CREATE INDEX %I ON ONLY %I (%I);', index_name,
> table_name, column_name);
>
>   FOR partition_name IN
> SELECT inhrelid::regclass::text
> FROM pg_inherits
> WHERE inhparent = table_name::regclass
> LOOP
> partition_index_name := partition_name || '_' || index_name ||
> '_idx';
>
> EXECUTE format('
> CREATE INDEX CONCURRENTLY %I ON %I (%I);',
> partition_index_name, partition_name, column_name);
>
> EXECUTE format('
> ALTER INDEX %I ATTACH PARTITION %I;', index_name,
> partition_index_name);
> END LOOP;
>
> 
> ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block
> CONTEXT:  SQL statement "
> CREATE INDEX CONCURRENTLY partitioned_table_0_index1_idx ON
> partitioned_table_0 (id);"
> PL/pgSQL function inline_code_block line 20 at EXECUTE
>

I'd write that in bash, not in a DO block.

Re: Re. Select with where condition times out

[Ron Johnson]

(Because VACUUM FULL rewrites the table, an implicit REINDEX occurs.)

I don't see mention of analyzing the database.

Also, VACUUM FULL probably doesn't do what you think it does.

On Sat, Jul 20, 2024 at 7:44 AM sivapostg...@yahoo.com <
sivapostg...@yahoo.com> wrote:

> Executed
> VACUUM FULL VERBOSE
> followed by
> REINDEX DATABASE dbname;
>
> It didn't increase the performance, still time out happened.  VACUUM
> didn't find any dead rows in that particular table.
>
> Yes, the actual query and conditions were not given in my first comment.
> Actually where condition is not on the date field alone and the query with
> current date is only a sample.
>
> What I did,
> 1.  Took backup (pg_dump) of the database from the server it's running.
>  [ Server config. Xeon Silver 4208, Windows Server 2019 Standard ].
> 2.  Restored in another desktop system, installing PG 11 afresh.
> 3.  Performance was excellent.  Within milliseconds I got the result.
> Application was run from the desktop.
> 4.  Restored the database in the same server, as another database.
> Improved performance but doesn't match the performance of the desktop.
> Application run from the server itself.
>
> Now server got two databases with exactly the same data.   Old one takes
> more than 15 minutes; newer one takes few seconds.  Application run from
> the server and also from clients.  In both conditions, the result is same.
>
> What else I need to do to correct this issue?
>
> I can easily replace the old database with the backup.  Is that only
> option?
>
> Happiness Always
> BKR Sivaprakash
>
> On Thursday, 18 July, 2024 at 05:23:39 pm IST, Francisco Olarte <
> fola...@peoplecall.com> wrote:
>
>
> On Thu, 18 Jul 2024 at 11:38, sivapostg...@yahoo.com
>  wrote:
> > Hello,
> > PG V11
> >
> > Select count(*) from table1
> > Returns 10456432
> >
> > Select field1, field2 from table1 where field3> '2024-07-18 12:00:00'
> > Times out
>
> How do you send the query / how does it time out? Is that the real
> query? Is table a table or a view? What does explain say?
>
>
> > Any possible way(s) to do this?
>
>
> If your client is timing out, increase timeout, if imposible you can
> try fetching in batches, but more detail would be needed.
>
> Suggestions to improve total time had already being given, try to
> decrease bloat if you have it, but AFAIK timeouts are configurable, so
> it may just be you have a too low timeout.
>
> If it had been working, is field3 indexed? How is the table modified?
>
> Because with a configured timeout, whit an unindexed table ( forcing a
> table scan ) the query may be working for years before you hit the bad
> spot. Also, the query includes todays date, so I doubt it has been
> used for years, probably "a similar one has been used for years", and
> probably that is not your real table ( or you have a naming problem ).
> Without giving real info, people cannot give you real solutions.
>
> Francisco Olarte.
>
>

Re: Semantic cache capability for Postgresql db

[Ron Johnson]

What does "python client will support the pgprewarm cache usage" mean?

Read the pgprewarm docs.

On Wed, Jul 17, 2024 at 11:10 AM pradeep t  wrote:

> Thanks, I'll check it out.
> Also is there any python client for the connection to pgprewarm?
> Is the existing Postgresql python client will support the pgprewarm cache
> usage also?
>
> On Tue, Jul 16, 2024 at 9:01 PM Kashif Zeeshan 
> wrote:
>
>> Hi Pradeep
>>
>> pgprewarm Extension is available for Postgres
>>
>> https://www.postgresql.org/docs/current/pgprewarm.html
>>
>>
>>
>> On Tue, Jul 16, 2024 at 7:05 PM pradeep t 
>> wrote:
>>
>>> Hi,
>>>
>>> Shall I use the Postgresql database for a* semantic cache *like the
>>> Redis semantic cache?
>>> Are we currently supporting such usage?
>>>
>>> --
>>> Thanks and regards
>>> Pradeep.T
>>>
>>
>
> --
> Thanks and regards
> Pradeep.T
>

Re: PostgreSQL Active-Active Clustering

[Ron Johnson]

On Mon, Jul 15, 2024 at 5:54 PM Christoph Moench-Tegeder 
wrote:

> ## Ron Johnson (ronljohnso...@gmail.com):
>
> > This "lack of products" puzzles me, because DEC was doing this with VAX
> > (then Alpha and Itanium) clusters 40 years ago via a Distributed Lock
> > Manager integrated deep into VMS.  Their Rdb and (CODASYL) DBMS products
>
> Tech and trade-offs have changed over the last 40 years :)
> These days you can so many cores in one package, while "more than one
> processor" was quite a feat in the 80ies ("A dual processor VAX 11/780",
> 1982 https://dl.acm.org/doi/10./800048.801738; also the 11/782 and
> 11/784), and you get so much RAM and storage (even fast storage, if
> you keep it local) with that package. Response Latency really jumps
> if you have to communicate with anything outside your box.
> While latency matters, the number of problems where you absolutely
> need that distributed lock manager has not really grown that much,
> I think.


 Customers still want High Availability, and VMS Clusters were great for
HA.

Re: PostgreSQL Active-Active Clustering

[Ron Johnson]

On Mon, Jul 15, 2024 at 3:28 PM Christophe Pettus  wrote:

>
> > On Jul 15, 2024, at 12:06, Sarkar, Subhadeep 
> wrote:
> >
>
[snip]

> >   • In the Community edition of PostgreSQL is it possible to setup  a
> cluster where all the nodes are able to concurrently read-write the
> underlying database image using NATIVE features (i.e. without using any
> extensions or external components or usage of Kubernetes/Dockers).
>
[snip]

> No product, either commercial or open-source, provides the last one
> (read-write shared storage), although there are commercial products that
> provide for a shared-storage model single-writer, multiple-reader model
> (for example, Amazon Aurora).
>

This "lack of products" puzzles me, because DEC was doing this with VAX
(then Alpha and Itanium) clusters 40 years ago via a Distributed Lock
Manager integrated deep into VMS.  Their Rdb and (CODASYL) DBMS products
used those functions extensively.

(In the late 1990s, they sold the DLM code to Oracle, which is where RAC
comes from.)

It was shared-disk, multiple-writer, because the DLM allowed for locking at
the row level.  Thus, a half dozen cluster nodes could hold write locks on
different rows on the same data page.

Re: How does this FK constraint error happen?

[Ron Johnson]

On Mon, Jul 15, 2024 at 12:47 PM Adrian Klaver 
wrote:

> On 7/15/24 09:21, Ron Johnson wrote:
> > On Mon, Jul 15, 2024 at 11:37 AM Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> wrote:
> >
>
>
> > I don't think it is entirely coincidental that 1210 is the only shown
> > user_id with a modified_on value that is in proximity to the delete
> > error.
> >
> >
> > I don't think so either.
> >
> > My suspicion is that actions are not happening in the exact order
> > you think they are.
> >
> >
> > modified_on is CURRENT_TIMESTAMP or NOW() or somesuch.  I'm not sure,
> > because I'm not privy to the code.
> >
> > But I'm printing the system time in bash before every statement.
>
> That is why I wrote 'Time travel?'.
>
> I suspect the modified_on time in the table is not accurately
> representing when the row is modified.
>

That JBDC code is pretty slow...


>
> >
> > I would think that combining DELETE FROM
> > rel_group_user; and DELETE FROM public.access_user; in a single
> > transaction would be a good start to fixing this.
> >
> >
> > That is in fact what I'm working on now.  There are 26 tables, and they
> > must be done in a specific order when deleting, and the reverse while
> > inserting.
> >
> > postgres_fdw would make this easier...
>
> It can't be installed?
>

Less bureaucratic overhead to write a script.

Re: How does this FK constraint error happen?

[Ron Johnson]

On Mon, Jul 15, 2024 at 11:37 AM Adrian Klaver 
wrote:

> On 7/15/24 08:18, Ron Johnson wrote:
> > On Mon, Jul 15, 2024 at 11:06 AM Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> wrote:
> >
> > On 7/15/24 07:53, Ron Johnson wrote:
> >  > On Mon, Jul 15, 2024 at 10:35 AM Peter J. Holzer
>
>
> >  > TAPd=# select * from rel_group_user
> >  > where user_id between 1100 and 1300
> >  > order by user_id;
> >  >   user_id | group_id | modified_by |   modified_on
> >  > -+--+-+-
> >  >  1133 |2 |1133 | 2024-07-15 08:43:35.669
> >  >  1142 |2 |1142 | 2024-07-15 09:05:58.451
> >  >  1147 |2 |1147 | 2024-07-15 09:30:37.169
> >  >  1158 |2 |1158 | 2024-07-15 09:36:45.142
> >  >  1197 |2 |1197 | 2024-07-15 09:52:58.477
> >  >  1210 |2 |1210 | 2024-07-15 02:42:09.355
> > <<<<<<<<<<<<<
> >
> > Time travel?
> >
> >
> > 
> >
> >
> > 2024-07-15 02:41:15 Deleting from
> > FISPTAPPGS401DA/TAPd.public.access_user
> > DELETE FROM public.access_user;
> >
> > Or do the cron jobs take that long to execute?
> >
> >
> > The deletes from 26*3 tables (the same 26 tables in three children) took
> > from 02:40:02 to 02:41:47.
> > Then a bunch of COPY statements run (pg_dump from the federation master,
> > then COPY to the federation children).  Must be done in a specific order.
>
> I don't think it is entirely coincidental that 1210 is the only shown
> user_id with a modified_on value that is in proximity to the delete
> error.


I don't think so either.


> My suspicion is that actions are not happening in the exact order
> you think they are.


modified_on is CURRENT_TIMESTAMP or NOW() or somesuch.  I'm not sure,
because I'm not privy to the code.

But I'm printing the system time in bash before every statement.


> I would think that combining DELETE FROM
> rel_group_user; and DELETE FROM public.access_user; in a single
> transaction would be a good start to fixing this.
>

That is in fact what I'm working on now.  There are 26 tables, and they
must be done in a specific order when deleting, and the reverse while
inserting.

postgres_fdw would make this easier...


> >
> > How is modified_on created?
> >
> >
> > It's updated by the application.
>
> At what point in the process?
>
> >
> >  >  1229 |2 |1229 | 2024-07-15 08:33:48.443
> >  >  1242 |2 |1242 | 2024-07-15 10:29:51.176
> >  >  1260 |2 |1260 | 2024-07-15 07:36:21.182
> >  >  1283 |2 |1283 | 2024-07-15 09:48:25.214
> >  >  1288 |2 |1288 | 2024-07-15 08:10:33.609
> >  > (11 rows)
> >  >
> >  > TAPd=# select user_id, login_id, created_on, modified_on
> >  > TAPd-# from public.access_user
> >  > TAPd-# where user_id = 1210;
> >  >   user_id |  login_id  |   created_on|
> modified_on
> >  >
> >
>  -++-+-
> >  >  1210 | JORIEUSER3 | 2023-10-20 11:54:24.562 | 2024-07-15
> > 02:42:09.355
> >  > (1 row)
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: How does this FK constraint error happen?

[Ron Johnson]

On Mon, Jul 15, 2024 at 11:06 AM Adrian Klaver 
wrote:

> On 7/15/24 07:53, Ron Johnson wrote:
> > On Mon, Jul 15, 2024 at 10:35 AM Peter J. Holzer  > <mailto:hjp-pg...@hjp.at>> wrote:
> > [snip]
> >
> >
> > Is it possible that some other process created an entry in
> > rel_group_user between these two queries?
> >
> > That was, in fact, the problem.  At just the wrong time to impact one of
> > the child databases (TAPd), but not the other two (TAPb and TAPc).
> >
> > TAPd=# select * from rel_group_user
> > where user_id between 1100 and 1300
> > order by user_id;
> >   user_id | group_id | modified_by |   modified_on
> > -+--+-+-
> >  1133 |2 |1133 | 2024-07-15 08:43:35.669
> >  1142 |2 |1142 | 2024-07-15 09:05:58.451
> >  1147 |2 |1147 | 2024-07-15 09:30:37.169
> >  1158 |2 |1158 | 2024-07-15 09:36:45.142
> >  1197 |2 |1197 | 2024-07-15 09:52:58.477
> >  1210 |2 |1210 | 2024-07-15 02:42:09.355
> <<<<<<<<<<<<<
>
> Time travel?
>




>
> 2024-07-15 02:41:15 Deleting from FISPTAPPGS401DA/TAPd.public.access_user
> DELETE FROM public.access_user;
>
> Or do the cron jobs take that long to execute?
>

The deletes from 26*3 tables (the same 26 tables in three children) took
from 02:40:02 to 02:41:47.

Then a bunch of COPY statements run (pg_dump from the federation master,
then COPY to the federation children).  Must be done in a specific order.

How is modified_on created?
>

It's updated by the application.


> >  1229 |2 |1229 | 2024-07-15 08:33:48.443
> >  1242 |2 |1242 | 2024-07-15 10:29:51.176
> >  1260 |2 |1260 | 2024-07-15 07:36:21.182
> >  1283 |2 |1283 | 2024-07-15 09:48:25.214
> >  1288 |2 |1288 | 2024-07-15 08:10:33.609
> > (11 rows)
> >
> > TAPd=# select user_id, login_id, created_on, modified_on
> > TAPd-# from public.access_user
> > TAPd-# where user_id = 1210;
> >   user_id |  login_id  |   created_on|   modified_on
> >
> -++-+-
> >  1210 | JORIEUSER3 | 2023-10-20 11:54:24.562 | 2024-07-15
> 02:42:09.355
> > (1 row)
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: How does this FK constraint error happen?

[Ron Johnson]

On Mon, Jul 15, 2024 at 10:35 AM Peter J. Holzer  wrote:
[snip]

>
> Is it possible that some other process created an entry in
> rel_group_user between these two queries?


That was, in fact, the problem.  At just the wrong time to impact one of
the child databases (TAPd), but not the other two (TAPb and TAPc).

TAPd=# select * from rel_group_user
where user_id between 1100 and 1300
order by user_id;
 user_id | group_id | modified_by |   modified_on
-+--+-+-
1133 |2 |1133 | 2024-07-15 08:43:35.669
1142 |2 |1142 | 2024-07-15 09:05:58.451
1147 |2 |1147 | 2024-07-15 09:30:37.169
1158 |2 |1158 | 2024-07-15 09:36:45.142
1197 |2 |1197 | 2024-07-15 09:52:58.477
1210 |2 |1210 | 2024-07-15 02:42:09.355  <
1229 |2 |1229 | 2024-07-15 08:33:48.443
1242 |2 |1242 | 2024-07-15 10:29:51.176
1260 |2 |1260 | 2024-07-15 07:36:21.182
1283 |2 |1283 | 2024-07-15 09:48:25.214
1288 |2 |1288 | 2024-07-15 08:10:33.609
(11 rows)

TAPd=# select user_id, login_id, created_on, modified_on
TAPd-# from public.access_user
TAPd-# where user_id = 1210;
 user_id |  login_id  |   created_on|   modified_on
-++-+-
1210 | JORIEUSER3 | 2023-10-20 11:54:24.562 | 2024-07-15 02:42:09.355
(1 row)

How does this FK constraint error happen?

[Ron Johnson]

PG 14.12

The job does DELETE FROM rel_group_user; (no WHERE clause!!) then does DELETE
FROM public.access_user; (also no WHERE clause), but the
public.access_user statement
fails on FK constraint error on rel_group_user (which was just recently
emptied).

Each statement is in a different transaction, since they are executed via
separate psql statements.  Thus, no apparent MVCC visibility weirdness.

My first thought, of course, was that there are *two* rel_group_user tables.
Alas, no, there's just one.  See below for grep statement.

Excerpts from the cron job log file:
[snip]
2024-07-15 02:40:04 Deleting from FISPTAPPGS401DA/TAPd.rel_group_user
DELETE FROM rel_group_user;
DELETE 42747
[snip]
2024-07-15 02:41:15 Deleting from FISPTAPPGS401DA/TAPd.public.access_user
DELETE FROM public.access_user;
ERROR:  update or delete on table "access_user" violates foreign key
constraint "fk_rel_group_user_1" on table "rel_group_user"
DETAIL:  Key (user_id)=(1210) is still referenced from table
"rel_group_user".
ERROR: deleting FISPTAPPGS401DA/TAPd.public.access_user
[snip]

$ psql --host=FISPTAPPGS401DA TAPd -Xc "\dt *.*" | grep " rel_group_user "
 public | rel_group_user| table
  | TAP

Here are the table definitions (if relevant):
TAPd=# \d public.access_user
   Table
"public.access_user"
   Column   |Type | Collation |
Nullable |   Default
+-+---+--+--
 user_id| integer |   | not
null | nextval('access_user_user_id_seq'::regclass)
 login_id   | character varying(255)  |   | not
null |
[snip]
 Indexes:
"pk_access_user" PRIMARY KEY, btree (user_id)
"idx_user_login_id" UNIQUE, btree (login_id)
Foreign-key constraints:
"fk_access_user_home_domain" FOREIGN KEY (home_domain_id) REFERENCES
access_domain(domain_id)
"fk_user_userdesktop" FOREIGN KEY (user_desktop_id) REFERENCES
user_desktop(user_desktop_id)
Referenced by:
[snip]
TABLE "rel_group_user" CONSTRAINT "fk_rel_group_user_1" FOREIGN KEY
(user_id) REFERENCES access_user(user_id)
[snip]

TAPd=# \d rel_group_user
   Table "public.rel_group_user"
   Column|Type | Collation | Nullable | Default
-+-+---+--+-
 user_id | integer |   | not null |
 group_id| integer |   | not null |
 modified_by | integer |   |  |
 modified_on | timestamp without time zone |   | not null |
Indexes:
"idx_rel_group_user" UNIQUE, btree (user_id, group_id)
"idx_rel_group_user_groupid" btree (group_id)
"idx_rel_group_user_userid" btree (user_id)
Foreign-key constraints:
"fk_rel_group_user_1" FOREIGN KEY (user_id) REFERENCES
access_user(user_id)
"fk_rel_group_user_2" FOREIGN KEY (group_id) REFERENCES
access_group(group_id)

Re: Dropping column from big table

[Ron Johnson]

On Thu, Jul 11, 2024 at 3:41 AM sud  wrote:

>
>
> On Thu, 11 Jul, 2024, 12:46 pm Ron Johnson, 
> wrote:
>
>> On Wed, Jul 10, 2024 at 11:28 PM sud  wrote:
>>
>>>
>>>
>>>
>>> Thank you so much. When you said *"you can execute one of the forms of
>>> ALTER TABLE that performs a rewrite*
>>> *of the whole table."* Does it mean that post "alter table drop column"
>>> the vacuum is going to run longer as it will try to clean up all the rows
>>> and recreate the new rows? But then how can this be avoidable or made
>>> better without impacting the system performance
>>>
>>
>> "Impact" is a non-specific word.  "How much impact" depends on how many
>> autovacuum workers you've set it to use, and how many threads you set in
>> vacuumdb.
>>
>>
>>> and blocking others?
>>>
>>
>> VACUUM never blocks.
>>
>> Anyway, DROP is the easy part; it's ADD COLUMN that can take a lot of
>> time (depending on whether or not you populate the column with a default
>> value).
>>
>> I'd detach all the partitions from the parent table, and then add the new
>> column to the not-children in multiple threads, add the column to the
>> parent and then reattach all of the children.  That's the fastest method,
>> though takes some time to set up.
>>
>
>
> Thank you so much.
>
> Dropping will take it's own time for post vacuum however as you rightly
> said, it won't be blocking which should be fine.
>
> In regards to add column, Detaching all partitions then adding column  to
> the individual partition in multiple sessions and then reattaching looks to
> be a really awesome idea to make it faster.
>

Do both the DROP and ADD in the same "set".  Possibly in the same statement
(which would be fastest if it works), and alternatively on the same command
line.  Examples:
psql --host=foo.example.com somedb -c "ALTER TABLE bar_p85 DROP COLUMN
splat, ADD COLUMN barf BIGINT;"
psql --host=foo.example.com somedb -c "ALTER TABLE bar_p85 DROP splat;" -c
ALTER TABLE bar_p85 ADD COLUMN barf BIGINT;"

My syntax is probably wrong, but you get the idea.

However one doubt, Will it create issue if there already exists foreign key
> on this partition table or say it's the parent to other child
> partition/nonpartition tables?
>

(Note that detached children have FK constraints.)

It'll certainly create an "issue" if the column you're dropping is part of
the foreign key. 

It'll also cause a problem if the table you're dropping from or adding to
is the "target" of the FK, since the source can't check the being-altered
table during the ALTER TABLE statement.

Bottom line: you can optimize for:
1. minimized wall time by doing it in multiple transactions (which
*might* bodge
your application; we don't know it, so can't say for sure), OR
2. assured consistency (one transaction where you just ALTER the parent,
and have it ripple down to the children); it'll take much longer, though.

One other issue: *if* adding the new column requires a rewrite, "ALTER
parent" *might*  (but I've never tried it) temporarily use an extra 2TB of
disk space in that single transaction.  Doing the ALTERs child by child
minimizes that, since each child's ALTER is it's own transaction.

Whatever you do... test test test.

>

Re: Dropping column from big table

[Ron Johnson]

On Wed, Jul 10, 2024 at 11:28 PM sud  wrote:

>
> On Thu, Jul 11, 2024 at 2:52 AM Adrian Klaver 
> wrote:
>
>>
>> https://www.postgresql.org/docs/current/sql-altertable.html
>>
>> "The DROP COLUMN form does not physically remove the column, but simply
>> makes it invisible to SQL operations. Subsequent insert and update
>> operations in the table will store a null value for the column. Thus,
>> dropping a column is quick but it will not immediately reduce the
>> on-disk size of your table, as the space occupied by the dropped column
>> is not reclaimed. The space will be reclaimed over time as existing rows
>> are updated.
>>
>> To force immediate reclamation of space occupied by a dropped column,
>> you can execute one of the forms of ALTER TABLE that performs a rewrite
>> of the whole table. This results in reconstructing each row with the
>> dropped column replaced by a null value.
>> "
>>
>>
> Thank you so much. When you said *"you can execute one of the forms of
> ALTER TABLE that performs a rewrite*
> *of the whole table."* Does it mean that post "alter table drop column"
> the vacuum is going to run longer as it will try to clean up all the rows
> and recreate the new rows? But then how can this be avoidable or made
> better without impacting the system performance
>

"Impact" is a non-specific word.  "How much impact" depends on how many
autovacuum workers you've set it to use, and how many threads you set in
vacuumdb.


> and blocking others?
>

VACUUM never blocks.

Anyway, DROP is the easy part; it's ADD COLUMN that can take a lot of time
(depending on whether or not you populate the column with a default value).

I'd detach all the partitions from the parent table, and then add the new
column to the not-children in multiple threads, add the column to the
parent and then reattach all of the children.  That's the fastest method,
though takes some time to set up.

Re: can stored procedures with computational sql queries improve API performance?

[Ron Johnson]

On Tue, Jul 9, 2024 at 8:58 PM Krishnakant Mane  wrote:

> Hello.
>
> I have a straight forward question, but I am just trying to analyze the
> specifics.
>
> So I have a set of queries depending on each other in a sequence to
> compute some results for generating financial report.
>
> It involves summing up some amounts from tuns or of rows and also on
> certain conditions it categorizes the amounts into types (aka Debit
> Balance, Credit balance etc).
>
> There are at least 6 queries in this sequence and apart from 4 input
> parameters. these queries never change.
>
> So will I get any performance benefit by having them in a stored
> procedure rather than sending the queries from my Python based API?


One problem is that the query planner reverts to a generic query plan if
you execute the same query over and over in a loop in the SP.

That bit us once.  A big SP that had been running "normally" for months
suddenly went from about 20 minutes to six hours.  The solution (given by
someone on this list a couple of years ago) was to add "set plan_cache_mode
= force_custom_plan;" above the call.

That way, the query plan was updated every time.  Performance dropped to
about 8 minutes IIRC.

Re: Load a csv or a avro?

[Ron Johnson]

On Sat, Jul 6, 2024 at 4:10 PM sud  wrote:

> On Fri, Jul 5, 2024 at 8:24 PM Adrian Klaver 
> wrote:
>
>> On 7/5/24 02:08, sud wrote:
>> > Hello all,
>> >
>> > Its postgres database. We have option of getting files in csv and/or in
>> > avro format messages from another system to load it into our postgres
>> > database. The volume will be 300million messages per day across many
>> > files in batches.
>>
>> Are dumping the entire contents of each file or are you pulling a
>> portion of the data out?
>>
>>
>>
> Yes, all the fields in the file have to be loaded to the columns in the
> tables in postgres.
>

But you didn't say *which* columns or *which* tables.

If one row of CSV input must be split into multiple tables, then it might
be pretty slow.


> But how will that matter here for deciding if we should ask the data in
> .csv or .avro format from the outside system to load into the postgres
> database in row and column format? Again my understanding was that
> irrespective of anything , the .csv file load will always faster as because
> the data is already stored in row and column format as compared to the
> .avro file in which the parser has to perform additional job to make it row
> and column format or map it to the columns of the database table. Is my
> understanding correct here?
>

Yes and no.  It all depends on how well each input row maps to a Postgresql
table.

Bottom line: you want an absolute answer, but we can't give you an absolute
answer, since we don't know what the input data looks like, and we don't
know what the Postgresql tables look like.

An AVRO file *might* be faster to input than CSV, or it might be horribly
slower.

And you might incompetently program a CSV importer so that it's horribly
slow.

We can't give absolute answers without knowing more details than the
ambiguous generalities in your emails.

Re: Load a csv or a avro?

[Ron Johnson]

On Fri, Jul 5, 2024 at 5:08 AM sud  wrote:

> Hello all,
>
> Its postgres database. We have option of getting files in csv and/or in
> avro format messages from another system to load it into our postgres
> database. The volume will be 300million messages per day across many files
> in batches.
>
> My question was, which format should we chose in regards to faster data
> loading performance ?
>

What application will be loading the data?   If psql, then go with CSV;
COPY is *really* efficient.

If the PG tables are already mapped to the avro format, then maybe avro
will be faster.

> and if any other aspects to it also should be considered apart from just
> loading performance?
>

If all the data comes in at night, drop as many indices as possible before
loading.

Load each file in as few DB connections as possible: the most efficient
binary format won't do you any good if you open and close a connection for
each and every row.

Re: Design strategy for table with many attributes

[Ron Johnson]

On Thu, Jul 4, 2024 at 3:38 PM Lok P  wrote:

> Hello,
> In one of the applications we are getting transactions in messages/events
> format and also in files and then they are getting parsed and stored into
> the relational database. The number of attributes/columns each transaction
> has is ~900+. Logically they are part of one single transaction
>

Nothing out of the ordinary.


> and should be stored in one table as one single row.
>

Says who?


> There will be ~500million such transactions each day coming into the
> system. And there will be approx ~10K peak write TPS and 5K read TPS in
> target state. This system has a postgres database as a "source of truth" or
> OLTP store. And then data moves to snowflakes for the olap store.
>
> Initially when the system was designed the number of attributes per
> transaction was <100 but slowly the business wants to keep/persist other
> attributes too in the current system and the number of columns keep growing.
>
> However, as worked with some database systems , we get few suggestions
> from DBA's to not have many columns in a single table. For example in
> oracle they say not to go beyond ~255 columns as then row chaining and row
> migration type of things are going to hunt us. Also we are afraid
> concurrent DMLS on the table may cause this as a contention point. So I
> wanted to understand , in such a situation what would be the best design
> approach we should use irrespective of databases? Or say, what is the
> maximum number of columns per table we should restrict? Should we break the
> single transaction into multiple tables like one main table and other
> addenda tables with the same primary key to join and fetch the results
> wherever necessary?
>

You need database normalization.  It's a big topic.  Here's a good simple
explanation:
https://popsql.com/blog/normalization-in-sql

Re: Alignment check

[Ron Johnson]

On Thu, Jun 27, 2024 at 1:26 PM Marthin Laubscher 
wrote:
[snip]

> So when you're done trolling me and my choices,


Adrian didn't start this "conversation".


> feel free to comment on the actual question.
>

YB says they are almost finished updating their system to the PG 15 (not
sure which point release) codebase; it could already be in beta.

Maybe your extension will work on the new version.

Re: A way to optimize sql about the last temporary-related row

[Ron Johnson]

On Thu, Jun 27, 2024 at 11:33 AM aghart...@gmail.com 
wrote:

> Hi,
>
> You are right. Too quickly copy-paste on my part :-)
>
> I take this opportunity to add a NOT insignificant detail.
>
> Before executing the select query I clear the cache:
>
> systemctl stop postgresql-16 && sync && echo 3 > /proc/sys/vm/drop_caches
> &&  systemctl start postgresql-16
>
> I need to get a performance result even if data is not in cache.
>

In addition to David's suggestion, consider the pg_prewarm extension:
https://www.postgresql.org/docs/current/pgprewarm.html

Re: A way to optimize sql about the last temporary-related row

[Ron Johnson]

On Thu, Jun 27, 2024 at 11:20 AM aghart...@gmail.com 
wrote:
[snip]

> -- insert 4M records
> insert into test_table(pk_id) select generate_series(1,400,1);
>
> -- now set some random data, distribuited between specific ranges (as in
> my production table)
> update test_table set
> datetime_field_1 = timestamp '2000-01-01 00:00:00' + random() *
> (timestamp '2024-05-31 23:59:59' - timestamp '2000-01-01 00:00:00'),
> integer_field_1 = floor(random() * (6-1+1) + 1)::int,
> integer_field_2 = floor(random() * (20-1+1) + 1)::int;
>
>
> -- indexes
> CREATE INDEX idx_test_table_integer_field_1 ON test_table(integer_field_1);
> CREATE INDEX xtest_table_datetime_field_1 ON test_table(datetime_field_1
> desc);
> CREATE INDEX idx_test_table_integer_field_2 ON test_table(integer_field_2);
>
>
Off-topic: save some resources by vacuuming before creating indices.

Re: Autovacuum, dead tuples and bloat

[Ron Johnson]

On Wed, Jun 26, 2024 at 3:03 AM Shenavai, Manuel 
wrote:

> Thanks for the suggestions.
> I checked pg_locks  shows and pg_stat_activity but I could not find a LOCK
> or an transaction on this (at this point in time).
>
> I assume that this problem may relate to long running transactions which
> write a lot of data. Is there already something in place that would help me
> to:
> 1) identify long running transactions
>

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
https://www.postgresql.org/docs/current/pgstatstatements.html


> 2) get an idea of the data-volume a single transaction writes?
>
> I tested the log_statement='mod' but this writes too much data (including
> all payloads). I rather would like to get a summary entry of each
> transaction like:
> "Tx 4752 run for 1hour and 1GB data was written."
>
> Is there something like this already available in postgres?
>

*Maybe* you can interpolate that by seeing how much wal activity is written
during the transaction, but I'm dubious.


>
> Best regards,
> Manuel
>
> -Original Message-
> From: Adrian Klaver 
> Sent: 22 June 2024 23:17
> To: Shenavai, Manuel ; Achilleas Mantzios <
> a.mantz...@cloud.gatewaynet.com>; pgsql-general@lists.postgresql.org
> Subject: Re: Autovacuum, dead tuples and bloat
>
> On 6/22/24 13:13, Shenavai, Manuel wrote:
> > Thanks for the suggestion. This is what I found:
> >
> > - pg_locks  shows only one entry for my DB (I filtered by db oid). The
> entry is related to the relation "pg_locks" (AccessShareLock).
>
> Which would be the SELECT you did on pg_locks.
>
> > - pg_stat_activity shows ~30 connections (since the DB is in use, this
> is expected)
>
> The question then is, are any of those 30 connections holding a
> transaction open that needs to see the data in the affected table and is
> keeping autovacuum from recycling the tuples?
>
> You might need to look at the Postgres logs to determine the above.
> Logging connections/disconnections helps as well at least 'mod' statements.
>
> See:
>
> https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
>
> for more information.
>
> >
> > Is there anything specific I should further look into in these tables?
> >
> > Regarding my last post: Did we see a problem in the logs I provided in
> my previous post? We have seen that there are 819294 n_live_tup in the
> toast-table. Do we know how much space these tuple use?  Do we know how
> much space one tuple use?
>
> You will want to read:
>
> https://www.postgresql.org/docs/current/storage-toast.html
>
> Also:
>
> https://www.postgresql.org/docs/current/functions-admin.html
>
> 9.27.7. Database Object Management Functions
>
> There are functions there that show table sizes among other things.
>
> >
> > Best regards,
> > Manuel
> >
> > -Original Message-
> > From: Adrian Klaver 
> > Sent: 21 June 2024 22:39
> > To: Shenavai, Manuel ; Achilleas Mantzios <
> a.mantz...@cloud.gatewaynet.com>; pgsql-general@lists.postgresql.org
> > Subject: Re: Autovacuum, dead tuples and bloat
> >
> > On 6/21/24 12:31, Shenavai, Manuel wrote:
> >> Hi,
> >>
> >> Thanks for the suggestions. I found the following details to our
> >> autovacuum (see below). The related toast-table of my table shows some
> >> logs related the vacuum. This toast seems to consume all the data
> >> (27544451 pages * 8kb ≈ 210GB )
> >
> > Those tuples(pages) are still live per the pg_stat entry in your second
> > post:
> >
> > "n_dead_tup": 12,
> > "n_live_tup": 819294
> >
> > So they are needed.
> >
> > Now the question is why are they needed?
> >
> > 1) All transactions that touch that table are done and that is the data
> > that is left.
> >
> > 2) There are open transactions that still need to 'see' that data and
> > autovacuum cannot remove them yet. Take a look at:
> >
> > pg_stat_activity:
> >
> >
> https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
> >
> > and
> >
> > pg_locks
> >
> > https://www.postgresql.org/docs/current/view-pg-locks.html
> >
> > to see if there is a process holding that data open.
> >
> >>
> >> Any thoughts on this?
> >>
> >> Best regards,
> >> Manuel
> >>
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Replication After manual Failover

[Ron Johnson]

How were you replicating?

What was the status of the replication?

On Tue, Jun 25, 2024 at 1:20 PM Yongye Serkfem  wrote:

> Hello Engineer,
> Below is the error message I am getting after failing over to the standby
> and reconfiguring the former master as the new standby. Any help will be
> appreciated.
> [image: image.png]
>

Re: Replication After manual Failover

[Ron Johnson]

On Tue, Jun 25, 2024 at 2:39 PM Muhammad Ikram  wrote:

> Hi,
>
> Please reinitialize using pg_basebackup
>

Might not be possible during a switchover.


> or use pg_rewind
>
>
>
> Muhammad Ikram
> Bitnine Global
>
>
> On Tue, 25 Jun 2024 at 22:20, Yongye Serkfem  wrote:
>
>> Hello Engineer,
>> Below is the error message I am getting after failing over to the standby
>> and reconfiguring the former master as the new standby. Any help will be
>> appreciated.
>>
>> [snip]

Re: Issue with pgstattuple on Sequences in PostgreSQL

[Ron Johnson]

On Mon, Jun 24, 2024 at 7:09 AM Ayush Vatsa 
wrote:
[snip]

> How can one use pgstattuple on sequences?
>
Out of curiosity... *why*?

Re: Execute permission to function

[Ron Johnson]

On Mon, Jun 24, 2024 at 6:29 AM arun chirappurath 
wrote:

> Hi all
>
> I am using rds postgres 14. I have created few users and added them to
> pg_readall_data and pg_write_alldata groups
>
>
> They are able to read all data and do update in tables
>
> However they can't execute functions
>

https://www.postgresql.org/docs/14/sql-grant.html


> and not able to script out objects from pg_admin
>

What error message(s)?

Re: Re: How to use createdb command with newly created user?

[Ron Johnson]

Better to run now, and save yourself hassle in the future:
ALTER ROLE "Baba" RENAME TO baba;

Also, use a .pgpass file:
https://www.postgresql.org/docs/14/libpq-pgpass.html

On Sun, Jun 23, 2024 at 3:22 PM 毛毛  wrote:

>
>
> Thank you! You are right!
>
> After putting quotes around the username, it works!
>
>
>
> 在 2024-06-24 02:47:44，"David G. Johnston"  写道：
>
>
>
> On Sun, Jun 23, 2024, 11:43 毛毛  wrote:
>
>> Hi,
>>
>> I tried to create a user with CREATEDB permission.
>> Then I wanted to run command line tool `createdb` with this newly created
>> user.
>>
>> So I ran SQL first to create a user:
>>
>> ```
>> CREATE USER Baba WITH PASSWORD 'xxx' CREATEDB;
>> ```
>>
>> Then I run the following command on PowerShell on Windows 10:
>>
>> ```
>>  createdb -U Baba -W test_db
>> ```
>>
>> But no mater how I tried, the password always failed.
>>
>> If I specify the user as postgres, the defaut user, everything works fine.
>>
>> ```
>> createdb -U postgres -W test_db_1
>> ```
>>
>> Do you have any suggestions?
>>
>
>
> You named the user "baba" all lower-case but your createdb command uses
> Baba and in the OS the case-folding of identifiers does not happen.  Baba
> != baba  is your issue.
>
> David J.
>
>

Re: Password complexity/history - credcheck?

[Ron Johnson]

On Sat, Jun 22, 2024 at 7:28 PM Martin Goodson 
wrote:

> Hello.
>
> Recently our security team have wanted to apply password complexity
> checks akin to Oracle's profile mechanism to PostgreSQL, checking that a
> password hasn't been used in x months


There would have to be a pg_catalog table which stores login history.


> etc, has minimum length, x special
> characters and x numeric characters, mixed case etc.
>

Is that an after-the-fact scanner (with all the problems Tom mentioned), or
is it a client-side "check while you're typing in the *new* password"
scanner?

Re: pg_dump restores as expected on some machines and reports duplicate keys on others

[Ron Johnson]

On Sat, Jun 22, 2024 at 1:02 PM Shaheed Haque 
wrote:

> Hi,
>
> I am using Postgres 14 on AWS RDS and am seeing the output of pg_dump be
> restored as expected by pg_restore on some database instances, and fail
> with reports of duplicate keys on other database instances:
>
>- My deployments are always a pair, one "logic VM" for Django etc and
>one "RDS instance". The psql client runs on the logic VM. The Postgres
>version is the same in all cases; psql reports:
>
>
>- psql (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1), server 14.9)
>
>
>- The pg_restore is done using the same script in both cases.
>- In the failing cases, there are always the same 26 errors (listed in
>detail below), but in summary, 3 distinct "child" tables complain of a
>duplicate id=1, id=2 and id=3 respectively.
>- These "child" tables are FK-related via some intermediate table to a
>top level table. They form a polymorphic set. There are other similar child
>tables which do not appear to be affected:
>   - polymorphicmodel
>  - companybankdetail
> - companybankdetailde
> - companybankdetailgb  <<< 1 duplicate, id=2
> - companybankdetailus
> - companypostaldetail
> - companypostaldetailde
> - companypostaldetailgb  <<< 1 duplicate, id=1
> - companypostaldetailus
>  - companytaxdetail
> - companytaxdetailde
> - companytaxdetailgb  <<< 1 duplicate, id=3
> - companytaxdetailus
>  - ...
>  - several other hierarchies, all error free
>  - ...
>   - I've looked at the dumped .dat files but they contain no
>duplicates.
>- The one difference I can think of between deployment pairs which
>work ok, and those which fail is that the logic VM (i.e. where the psql
>client script runs) is the use of a standard AWS ubuntu image for the OK
>case, versus a custom AWS image for the failing case.
>   - The custom image is a saved snapshot of one created using the
>   standard image.
>
> Why should the use of one type of VM image versus another cause pg_restore
> to hallucinate the duplicate records?
>
> Encls: 26 errors as mentioned...
>
> 
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 5635; 1262 53894 DATABASE foo postgres
> pg_restore: error: could not execute query: ERROR:  database "foo" already
> exists
> Command was: CREATE DATABASE foo WITH TEMPLATE = template0 ENCODING =
> 'UTF8' LOCALE = 'en_US.UTF-8';
>

Check *all* of the client and server encodings.

99.99% of the time, that's the problem when the same dump file fails to
restore on different servers.

Re: Autovacuum, dead tuples and bloat

[Ron Johnson]

On Thu, Jun 20, 2024 at 12:47 PM Shenavai, Manuel 
wrote:

> Hi everyone,
>
>
>
> we can see in our database, that the DB is 200GB of size, with 99% bloat.
> After vacuum full the DB decreases to 2GB.
>
> DB total size: 200GB
>
> DB bloat: 198 GB
>
> DB non-bloat: 2GB
>
>
>
> We further see, that during bulk updates (i.e. a long running
> transaction), the DB is still growing, i.e. the size of the DB growth by
> +20GB after the bulk updates.
>
>
>
> My assumption is, that after an autovacuum, the 99% bloat should be
> available for usage again. But the DB size would stay at 200GB. In our
> case, I would only expect a growth of the DB, if the bulk-updates exceed
> the current DB size (i.e. 220 GB).
>
>
That's also my understanding of how vacuum works.

Note: I disable autovacuum before bulk modifications, manually VACUUM
ANALYZE and then reenable autovacuum.  That way, autovacuum doesn't jump in
the middle of what I'm doing.

 How could I verify my assumption?
>
>
>
> I think of two possibilities:
>
>1. My assumption is wrong and for some reason the dead tuples are not
>cleaned so that the space cannot be reused
>2. The bulk-update indeed exceeds the current DB size. (Then the
>growth is expected).
>
>
>
> Can you help me to verify these assumptions? Are there any statistics
> available that could help me with my verification?
>

I've got a weekly process that deletes all records older than N days from a
set of tables.
db=# ALTER TABLE t1 SET (autovacuum_enabled = off);
db=# ALTER TABLE t2 SET (autovacuum_enabled = off);
db=# ALTER TABLE t3 SET (autovacuum_enabled = off);
db=# DELETE FROM t1 WHERE created_on < (CURRENT_TIMESTAMP - INTERVAL '90
DAY');
db=# DELETE FROM t2 WHERE created_on < (CURRENT_TIMESTAMP - INTERVAL '90
DAY');
db=# DELETE FROM t3 WHERE created_on < (CURRENT_TIMESTAMP - INTERVAL '90
DAY');
$ vacuumdb --jobs=3 -t t1 -t t2 -t t3
db=# ALTER TABLE t1 SET (autovacuum_enabled = on);
db=# ALTER TABLE t2 SET (autovacuum_enabled = on);
db=# ALTER TABLE t3 SET (autovacuum_enabled = on);

pgstattuple shows that that free percentage stays pretty constant.  That
seems to be what you're asking about.

Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.

[Ron Johnson]

On Thu, Jun 20, 2024 at 3:23 AM Dmitry O Litvintsev 
wrote:

> Hello,
>
> I am in the process of migrating DB to Alma9 host. The databse
> is rather large - few TBs.
>
> I have run pg_basebackup on Alma9 host and established replication from
> production to it. The idea is to quickly switch from master to this new
> host during downtime.
>
> Establishing replication went fine. Source postgresql version is 15.6,
> destination is 15.7
>
> When I psql into replica I get:
>
> WARNING:  database "xxx" has a collation version mismatch
> DETAIL:  The database was created using collation version 2.17, but the
> operating system provides version 2.34.
> HINT:  Rebuild all objects in this database that use the default collation
> and run ALTER DATABASE xxx REFRESH COLLATION VERSION, or build PostgreSQL
> with the right library version.
>
> Looking up the issue the solution seems to be
>
>   REINDEX database xxx
>   ALTER DATABASE xxx REFRESH COLLATION VERSION
>
> But this defeats the whole idea of having short downtime because REINDEX
> will take forever.
>
> What is this "or build PostgreSQL with the right library version"?
> Is this about 15.7 vs 15.6 or is it about different glibc version between
> RH7 and Alma9?
>
> Is there a better way to handle it? I cannot afford long downtime.


You "only" need to REINDEX indices with TEXT (including CHAR and VARCHAR)
columns. That may be most of your indices, or very few.

I use this view and query to find such indices:

create or replace view dba.all_indices_types as
select tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname as
table_name
, ndcl.relname as index_name
, array_agg(ty.typname order by att.attnum) as index_types
from pg_class ndcl
inner join pg_index nd
on (ndcl.oid = nd.indexrelid and ndcl.relkind = 'i')
inner join pg_class tbcl
on (nd.indrelid = tbcl.oid and tbcl.relkind = 'r')
inner join pg_attribute att
on att.attrelid = nd.indexrelid
inner join pg_type ty
on att.atttypid = ty.oid
where tbcl.relnamespace::regnamespace::text != 'pg_catalog'
group by tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname
, ndcl.relname
order by 1, 2;

select *
from dba.all_indices_types
where index_types && '{"text","varchar","char"}';

Re: Transaction issue

[Ron Johnson]

On Wed, Jun 19, 2024 at 5:39 PM Rich Shepard 
wrote:

> On Wed, 19 Jun 2024, Ron Johnson wrote:
>
> > The problem is that you don't know where it's failing.
>
> Ron,
>
> True that. There's no specificity to why the transaction didn't complete.
>
> > I suggest you run "\echo all"  before running "\i
> > insert-law-offices-addr.sql".  That way, you'll see which line it barfs
> on.
>
> Good point. I'll do that. In the meantime, commenting out (or removing) the
> BEGIN; command inserts all rows without error.
>
>
In addition, manually run the "BEGIN;" before the "\i insert-blarge.sql"
command.

That way, insert-blarge.sql just inserts.  My reasoning: since you control
the ROLLBACK, you should also control the BEGIN.

Re: Transaction issue

[Ron Johnson]

On Wed, Jun 19, 2024 at 4:54 PM Rich Shepard 
wrote:

> On Wed, 19 Jun 2024, Adrian Klaver wrote:
>
> > Looks to me you have a left over unresolved transaction in your psql
> session.
> > The easiest solution if that is the case is to exit the session and
> start a
> > new session to run the script.
>
> Adrian, et al.:
>
> That's what I've done. This time I commented out the BEGIN; line:
> bustrac=# \i insert-law-offices-addr.sql
> INSERT 0 66
>
> There are no errors in the file but when I tried running it as a
> transaction
> it failed.
>
> I have not before used transactions when inserting or updating tables; I'm
> surprised that starting the transaction from the psql command line chokes
> the attempt.
>

It doesn't.  The rest of us have successfully done it before.  You're Doing
*Something* Wrong.  Don't know what, but you're doing it. Happens to *ALL
OF US*, and is why "\echo all" and "psql -a" were developed.

So we can see WTH we screwed up, and then fix it.

Re: Transaction issue

[Ron Johnson]

On Wed, Jun 19, 2024 at 4:33 PM Rich Shepard 
wrote:

> On Wed, 19 Jun 2024, Alban Hertroys wrote:
>
> > The error prior to those statements is what you need to look at. That’s
> > what’s causing the transaction to fail.
>
> Alban/Adrian,
>
> > I get the impression that you’re executing shell scripts that run the
> psql
> > command-line utility.
>
> That's what I do as I've not taken the time to finish the GUI with TKinter.
>
> Here's the latest example:
> bustrac=# \i insert-law-offices-addr.sql
> psql:insert-law-offices-addr.sql:1: ERROR:  current transaction is
> aborted, commands ignored until end of transaction block
> psql:insert-law-offices-addr.sql:69: ERROR:  current transaction is
> aborted, commands ignored until end of transaction block
>
> Line 1 is the BEGIN; statement; line 69 is the last row of data to be
> inserted.
>

The problem is that you don't know where it's failing.

I suggest you run "\echo all"  before running "\i
insert-law-offices-addr.sql".  That way, you'll see which line it barfs on.

Re: Proper format for pg_dump file date

[Ron Johnson]

On Wed, Jun 19, 2024 at 1:14 PM Rich Shepard 
wrote:

> On Wed, 19 Jun 2024, David G. Johnston wrote:
>
> > If you are doing a custom format dump using .sql as the extension is
> > objectively wrong. it is correct if you are doing an sql dump, but those
> > are generally best avoided.
>
> David,
>
> I use the default text format so restoration is by psql.
>

Perfectly valid for small databases, and copying tables from one instance
to another (though in that case, consider postgres_fdw).

Re: Proper format for pg_dump file date

[Ron Johnson]

On Wed, Jun 19, 2024 at 11:55 AM Rich Shepard 
wrote:

> Is the correct date format for pg_dump
> -$(date +%Y-%m-%d).sql
> or
> --MM-DD.sql
>

"Click select" stops at dash in some ssh clients, but not in others.
That's what drives *my* decision making.

(Also, "%F" is equivalent to "%Y-%m-%d".)

Re: Manual Failover

[Ron Johnson]

On Wed, Jun 19, 2024 at 11:03 AM Yongye Serkfem  wrote:

> Hello Engineers,
> I am facing an issue with the manual failover of the standby to the master
> role. I was able to promote the standby and got it out of recovery mode.
> How do I direct applications to point to the standby which has assumed the
> role of the current master,
>

A typical solution is a separate service which manages a virtual IP that
points to whichever DB server you choose.


> and what should I do after completing maintenance on the previous master
> to assume its original role as master?
>

pg_basebackup is guaranteed to get previous-master in sync with
now-active.  pg_rewind might also work.

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

[Ron Johnson]

On Tue, Jun 18, 2024 at 2:37 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tuesday, June 18, 2024, Ron Johnson  wrote:
>
>> On Tue, Jun 18, 2024 at 1:57 PM David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>> On Tuesday, June 18, 2024, Ron Johnson  wrote:
>>>
>>>>
>>>> But I stand by returning OUT params and records at the same time.
>>>>
>>>
>>> You mean you dislike adding the optional returns clause when output
>>> parameters exist?
>>>
>>
>> Correct.  It breaks the distinction between function and procedure.
>>
>
> How so?
>
> The two distinctions are functions can produce sets while procedures get
> transaction control.
>
> They both can produce a single multi-column output record.  The presence
> or absence of the optional return clause on a function definition doesn’t
> change that fact.
>

"A function returns a value*, but a procedure does not."

*In the case of SQL, "value" might be a set.

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

[Ron Johnson]

On Tue, Jun 18, 2024 at 1:57 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tuesday, June 18, 2024, Ron Johnson  wrote:
>
>>
>> But I stand by returning OUT params and records at the same time.
>>
>
> You mean you dislike adding the optional returns clause when output
> parameters exist?
>

Correct.  It breaks the distinction between function and procedure.


> Because the out parameters and the “record” represent the exact same thing.
>

What's the purpose?  Legacy of not having procedures?

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

[Ron Johnson]

On Tue, Jun 18, 2024 at 1:16 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tue, Jun 18, 2024 at 10:07 AM Ron Johnson 
> wrote:
>
>> fun2 puzzles me.  Why would you return parameters AND *a single record* 
>> (unless
>> it's an error status).
>>
>
> You mis-understand what 2 is doing.  You should go re-read the docs for
> create function again.  Especially the description of rettype.
>

That's true.  I've even used "RETURNS SETOF record" before.

But I stand by returning OUT params and records at the same time.

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

[Ron Johnson]

fun1 returns a table set just like any other SELECT statement.
fun2 puzzles me.  Why would you return parameters AND *a single record* (unless
it's an error status).
fun3 just returns two parameters.  Why isn't it a procedure?

fun2, returning parameters AND a function value, would have made my Comp
Sci professors very, very angry.  Definitely Bad Practice.

You choose which to use based on how much data you want to return.

On Tue, Jun 18, 2024 at 12:50 PM Ayush Vatsa 
wrote:

> Hi PostgreSQL community,
> Recently I am exploring extensions in postgres and got a little confused
> regarding the function definition present in SQL file. For example consider
> below three functions:
>
> CREATE FUNCTION fun1(integer)
> RETURNS TABLE(
> col1 integer,
> col2 text
> )
> AS 'MODULE_PATHNAME', 'fun1'
> LANGUAGE C;
>
> CREATE FUNCTION fun2(
> IN  input integer,
> OUT col1 integer,
> OUT col2 text
> )
> RETURNS SETOF record
> AS 'MODULE_PATHNAME', 'fun2'
> LANGUAGE C;
>
> CREATE FUNCTION fun3(
> IN  input integer,
> OUT col1 integer,
> OUT col2 text
> )
> AS 'MODULE_PATHNAME', 'fun3'
> LANGUAGE C;
>
> 1/ I wanted to know what's the difference between the above three
> definitions.
> As per my understanding, "fun1" and "fun2" look the same, taking one
> integer and returning two columns with multiple rows.
> Whereas "fun3" is used when we are returning only one row, but my doubt
> for "fun3" is that, Can the above definition (used for fun1 and fun2) cover
> both single and multiple row scenarios.
>
> 2/ How does someone decide which type of definition is to be used?
>
> Regards
> Ayush Vatsa
>

Re: Monitoring logical replication

[Ron Johnson]

On Tue, Jun 18, 2024 at 5:03 AM Shaheed Haque 
wrote:

> Hi all,
>
> Is there an "official" pairing of LSN values on the publication and
> subscription sides that should be used to track the delta between the two
> systems? I ask because Google is full of different pairs being used. I
> tried to identify the highest level interface points exposed, i.e. what is
> documented on
> https://www.postgresql.org/docs/current/replication-origins.html, the
> pg_stat_subscription table, the pg_stat_publication table and the
> pg_current_wal_lsn() function on the publisher, but these seem to be barely
> used.
>

The attached scripts (whose guts I took from a Stack Exchange post) might
be a good starting point.  It certainly works for physical replication!


> P.S. On a related note, I see a (stalled?) discussion on providing LSN ->
> timestamp conversion
> ,
> I'd just like to say that something like that would be very useful.
>

Out of curiosity, how does that work?  Is an instance's initial LSN really
based on Epoch?
#!/bin/bash

readonly Prime=foo1.example.com
readonly Replica=foo2.example.com

wal_location_to_64bits()
{
local id="${1%%/*}"
local offset="${1##*/}"
echo $((0xFF00 * 0x$id + 0x$offset))
}

declare Prime_SSE Prime_LSN
declare Second_SSE Second_Recv_LSN Second_Repl_LSN

IFS=$'\t' read -r Prime_LSN \
<<<$(psql --host=$Prime -XAt -F$'\t' \
-c "select pg_current_wal_lsn();")
IFS=$'\t' read -r Second_Timestamp Second_Recv_LSN Second_Repl_LSN \
<<<$(psql --host=$Replica -XAt -F$'\t' \
-c "select pg_last_wal_receive_lsn()
, pg_last_wal_replay_lsn();")

Prime_SSE=$(date +"%s.%N" -d "$Prime_Timestamp")
Second_SSE=$(date +"%s.%N" -d "$Second_Timestamp")
declare Query_Lag=$(echo "$Second_SSE - $Prime_SSE" | bc -l)

printf "Query Lag: %f\n" $Query_Lag

echo "LSN:"
printf "  Prime:= %s\n" $Prime_LSN
printf "  Replica Received: = %s\n" $Second_Recv_LSN
printf "  Replica Replayed: = %s\n" $Second_Repl_LSN

declare -i Prime_Bytes=$(wal_location_to_64bits $Prime_LSN)
declare -i Second_Recv_Bytes=$(wal_location_to_64bits $Second_Recv_LSN)
declare -i Second_Repl_Bytes=$(wal_location_to_64bits $Second_Repl_LSN)

echo "Backlog Bytes:"
printf "  Received = %'18d\n" $(echo "($Prime_Bytes - $Second_Recv_Bytes)" | bc -l)
printf "  Replayed = %'18d\n" $(echo "($Prime_Bytes - $Second_Repl_Bytes)" | bc -l)

Re: WAL settings for larger imports

[Ron Johnson]

On Mon, Jun 17, 2024 at 9:46 AM Benoit, Eric  wrote:

> What is a good data import configuration for only one server for
> WRITE-AHEAD LOG settings postgresql.conf where no one is connecting to the
> database accept me as a superuser.
>

How worried are you about data loss / database corruption?

Is streaming or logical replication enabled?

Re: pgstattuple - can it/does it provide stats by partition?

[Ron Johnson]

On Sat, Jun 15, 2024 at 9:47 AM Dragam  wrote:

> Hi all,
>
> As the title says - does (and/or can) pgstattuple provide a breakdown by
> partition?
> 
>

Partitions are tables, created via CREATE TABLE.  I know from experience
that it works on inheritance tables; what happens when you try
pgstattuple() on a small test partition?

Re: Configure autovacuum

[Ron Johnson]

On Fri, Jun 14, 2024 at 2:20 AM Shenavai, Manuel 
wrote:

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

HOT is probably what you're looking for:
https://www.postgresql.org/docs/14/storage-hot.html

Presuming that the field you're updating is not indexed, and the table can
be exclusively locked for as long as it takes to rewrite it: give the table
"more space to work" in each page:
ALTER TABLE foo SET (fillfactor = 30);
VACUUM FULL foo;

Then you don't need to VACUUM s frequently.

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

[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: UPDATE with multiple WHERE conditions

[Ron Johnson]

On Wed, Jun 12, 2024 at 5: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.
>
> Is there a way to access a file with those condition values? If not, should
> I create a temporary table with one column containing those values, or do I
> write a psql script with 295 lines, one for each row to be updated?
>

A plain UPDATE might work.

UPDATE to_be_updated a
SET bool_col = true
FROM other_table b
WHERE a.pk = b.pk
  AND b.field3 = mumble;

(You can join them, right?)

Re: PG16.1 security breach?

[Ron Johnson]

On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Jun 10, 2024 at 2:21 AM Laurenz Albe 
> wrote:
>
>> > How is it that the default privilege granted to public doesn’t seem to
>> care who the object creator
>> > is yet when revoking the grant one supposedly can only do so within the
>> scope of a single role?
>>
>> I don't understand what you wrote.  ALTER DEFAULT PRIVILEGES also only
>> applies to objects
>> created by a single role when you grant default privileges.
>>
>>
> 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.  You can revoke other privs from public (can't
you?), so why seemingly only do procedures/functions have this difficulty.

Re: postgres table statistics

[Ron Johnson]

On Wed, Jun 12, 2024 at 3:48 AM Chandy G  wrote:

> Hi,
>   We have postgres 13.9 running with tables thats got billions of records
> of varying sizes. 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).
>
> It get a bit trickier when 100s of such tables exists with varying records
> sizes. trying to see if the fetch size can be set dynamically based on the
> row count and the record size distribution for a table. Unfortunately,
> trying to get this data by a query run against each table (for row size:
> max(length(t::text))) seem to be  quite time consuming too.
>

Maybe create your own table with three columns:
table_name (PK; taken from pg_class.relname)
average_rec_size (taken from sum(pg_stat.avg_width))
max_rec_size (calculated yourself)

Periodically refresh it.  (How periodic depends on how often the average
and max change substantively.)

Does postgres maintain metadata about tables for the following.
> 1. row count
>

https://www.postgresql.org/docs/13/catalog-pg-class.html

pg_class.reltuples.  This is an estimate, so make sure your tables are
regularly analyzed.


> 2. max row size.
>

https://www.postgresql.org/docs/13/view-pg-stats.html

pg_stats.avg_width


> or is there some other pg metadata that can help get this data quicker.
>
> TIA.
>

Re: Does trigger only accept functions?

[Ron Johnson]

On Tue, Jun 11, 2024 at 2:53 PM veem v  wrote:

>
> On Tue, 11 Jun 2024 at 17:03, hubert depesz lubaczewski 
> wrote:
>
>> On Tue, Jun 11, 2024 at 12:47:14AM +0530, veem v wrote:
>> > to be called from ~50 triggers? or any other better approach exists to
>> > handle this?
>>
>> pgaudit extension?
>>
>> Or just write all the changes to single table?
>>
>> Or use dynamic queries that will build the insert based on the name of
>> table the event happened on?
>>
>> Or pass arguments?
>>
>> Best regards,
>>
>> depesz
>>
>>
> Thank you so much. I hope you mean something as below when you say making
> it dynamic. Because we have the audit tables having more number of columns
> as compared to the source table and for a few the column name is a bit
> different.
>
> -- Trigger for deletes
> CREATE TRIGGER before_delete
> BEFORE DELETE ON source_table
> FOR EACH ROW EXECUTE FUNCTION log_deletes();
>
>
> -- Trigger for source_table1
> CREATE TRIGGER before_delete_source_table1
> BEFORE DELETE ON source_table1
> FOR EACH ROW EXECUTE FUNCTION log_deletes();
>
> -- Trigger for source_table2
> CREATE TRIGGER before_delete_source_table2
> BEFORE DELETE ON source_table2
> FOR EACH ROW EXECUTE FUNCTION log_deletes();
>
>
> CREATE OR REPLACE FUNCTION log_deletes()
> RETURNS TRIGGER AS $$
> BEGIN
> IF TG_TABLE_NAME = 'source_table1' THEN
> INSERT INTO delete_audit1 ( col1, col2, col3)
> VALUES (OLD.col1, OLD.col2, OLD.col3);
> ELSIF TG_TABLE_NAME = 'source_table2' THEN
> INSERT INTO delete_audit2 ( col4, col5, col6)
> VALUES (OLD.col4, OLD.col5, OLD.col6);
> -- Add more conditions for other tables
>

Dear god, no.

Since all the functions are going to be similar, I'd write a shell script
to generate all the triggers, one per relevant.  If you're going to record
every field, then save effort, and don't bother enumerating them.  You'll
need to dig into the PG catalog's guts to list columns in the correct
order, but Google and Stack Exchange makes that easy enough.

(And, of course, that single trigger would be SLOW.)

This is essentially what we did 25 years ago to "logically replicate" data
from our OLTP system to the OLAP system.  There were two log tables for
every table to be replicated: foo_LOG1 and foo_LOG2.  The trigger wrote to
foo_LOG1 on even days, and foo_LOG2 on odd days.  It even added a
current_timestamp column, and action_code ("I" for insert, "D" for delete,
and "U" for update).

At around 01:00, a batch job copied out all of "yesterday's" log data
(there were 80-90 tables), and then truncated the table.

Re: Multiple tables row insertions from single psql input file

[Ron Johnson]

On Mon, Jun 10, 2024 at 5:16 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Jun 10, 2024 at 12:43 PM Ron Johnson 
>> wrote:
>
>
>> Most useful to you will be some number of "ALTER TABLE  DISABLE
>> TRIGGER ALL;" statements near the beginning of the file, and their "ALTER
>> TABLE ... ENABLE TRIGGER ALL;" counterparts near the end of the file.
>>
>>
> Have you just not heard of deferred constraints or is there some reason
> besides deferring constraints that you'd want to use alter table in
> transactional production code?
>

I mentioned bulk loading of data.  Occasionally that's useful, even in a
prod database.

Re: Multiple tables row insertions from single psql input file

[Ron Johnson]

On Mon, Jun 10, 2024 at 4:06 PM Rich Shepard 
wrote:

> On Mon, 10 Jun 2024, Ron Johnson wrote:
>
> > With enough clever scripting you can create a .sql file that does almost
> > anything.
>
> Ron,
>
> My projects don't all use SQL so I'm far from a clever scripter. :-)
>

No one is born a scripter, much less a clever scripter.


> > Most useful to you will be some number of "ALTER TABLE  DISABLE
> > TRIGGER ALL;" statements near the beginning of the file, and their "ALTER
> > TABLE ... ENABLE TRIGGER ALL;" counterparts near the end of the file.
>
> Doesn't alter table primarily apply to existing row values for specific
> columns rather than inserting new rows and their column values?
>

I don't think so.  For example, pg_dump has an option to add those
DISABLE/ENABLE TRIGGER  statements.  It makes bulk loading of records much
simpler.

Re: Multiple tables row insertions from single psql input file

[Ron Johnson]

On Mon, Jun 10, 2024 at 2:50 PM Rich Shepard 
wrote:

> My business tracking database has three main tables: company, location,
> contact. The company and contact primary keys are sequences.
>
> I've been adding new rows using INSERT INTO files separately for each table
> after manually finding the last PK for the company and contact tables. The
> location table has the company PK as a FK; the contact table has both
> company PK and location PK as foreign keys.
>
> Now I will use next_val 'PK' to assign the value for each new table row.
>
> My question is whether I can create new rows for all three tables in the
> same sql source file. Since the location and contact tables require
> sequence
> numbers from the company and location tables is there a way to specify,
> e.g., current_val 'tablename PK' for the related tables? Or, do I still
> need
> to enter all new companies before their locations and contact?
>

With enough clever scripting you can create a .sql file that does almost
anything.

Most useful to you will be some number of "ALTER TABLE  DISABLE
TRIGGER ALL;" statements near the beginning of the file, and their "ALTER
TABLE ... ENABLE TRIGGER ALL;" counterparts near the end of the file.

Re: Escaping single quotes with backslash seems not to work

[Ron Johnson]

On Mon, Jun 10, 2024 at 11:42 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Jun 10, 2024 at 8:19 AM Ron Johnson 
> wrote:
>
>>
>> "set standard_encoding_strings = on" is at the top, and there's no other
>> reference to it.
>>
>>
> Well, if they are not using E-strings for escapes then you have the answer
> why v14 is broken.  Does it really matter why v9.6 apparently worked even
> though it should not have if that setting was also set to on?
>

It matters that *something broke* either between PG 9.6 and 14 *OR* the old
JDBC driver and the new JDBC driver, because the client end users are
HOPPING MAD.

(Don't ask why it wasn't caught in testing; that's beyond my control.)

Re: Escaping single quotes with backslash seems not to work

[Ron Johnson]

On Mon, Jun 10, 2024 at 11:08 AM Tom Lane  wrote:

> Ron Johnson  writes:
> > On Mon, Jun 10, 2024 at 10:56 AM David G. Johnston <
> > david.g.johns...@gmail.com> wrote:
> >> As the caution on that page says the default for standard conforming
> >> strings changed in 9.1. But maybe your 9.6 had the old value configured
> but
> >> when you upgraded to 14 you decided to go with the new default.
>
> > That was the first thing I checked... It's the same on both the 9.6 and
> 14
> > systems:.
>
> Did you check that as the user that runs the Java app (I sure hope
> it's not the superuser you evidently used here), in the DB the Java
> app uses?  I'm wondering about per-user or per-DB settings of
> standard_conforming_strings.
>

It's a remote Java app which runs as a non-superuser.  I don't know what
it's doing.

I ran "pg_dumpuser -g" on the old systems, and applied the sql to the
corresponding new servers.

"set standard_encoding_strings = on" is at the top, and there's no other
reference to it.

Re: Escaping single quotes with backslash seems not to work

[Ron Johnson]

On Mon, Jun 10, 2024 at 10:56 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Monday, June 10, 2024, Ron Johnson  wrote:
>
>> On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>> On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson 
>>> wrote:
>>>
>>>> PG 9.6 and PG 14
>>>>
>>>>
>>>> https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
>>>>
>>>> [quote]
>>>> Any other character following a backslash is taken literally. Thus, to
>>>> include a backslash character, write two backslashes (\\). Also, a
>>>> single quote can be included in an escape string by writing \', in
>>>> addition to the normal way of ''.
>>>> [/quote]
>>>>
>>>>
>>> The link you provided goes to the wrong subsection.  The following
>>> subsection, which discusses, String Constants With C-Style Escapes,
>>> requires that you write the literal as E'abc\'def'
>>>
>>> Note the E prefix on the literal, which is the thing that enables
>>> considering backslash as an escape.
>>>
>>
>> This hasn't changed from 9.6, has it?
>>
>> A Java app that uses backslash escapes broke this morning on fields with
>> single quotes, after the weekend migration from PG 9.6.24 to 14.12, and I
>> don't know why.  I'm not a Java programmer, though.
>>
>>
> As the caution on that page says the default for standard conforming
> strings changed in 9.1. But maybe your 9.6 had the old value configured but
> when you upgraded to 14 you decided to go with the new default.
>

That was the first thing I checked... It's the same on both the 9.6 and 14
systems:.

TAP=# show standard_conforming_strings;
 standard_conforming_strings
-
 on
(1 row)

TAP=#
TAP=# show backslash_quote;
 backslash_quote
-
 safe_encoding
(1 row)

Re: Escaping single quotes with backslash seems not to work

[Ron Johnson]

On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson 
> wrote:
>
>> PG 9.6 and PG 14
>>
>>
>> https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
>>
>> [quote]
>> Any other character following a backslash is taken literally. Thus, to
>> include a backslash character, write two backslashes (\\). Also, a
>> single quote can be included in an escape string by writing \', in
>> addition to the normal way of ''.
>> [/quote]
>>
>>
> The link you provided goes to the wrong subsection.  The following
> subsection, which discusses, String Constants With C-Style Escapes,
> requires that you write the literal as E'abc\'def'
>
> Note the E prefix on the literal, which is the thing that enables
> considering backslash as an escape.
>

This hasn't changed from 9.6, has it?

A Java app that uses backslash escapes broke this morning on fields with
single quotes, after the weekend migration from PG 9.6.24 to 14.12, and I
don't know why.  I'm not a Java programmer, though.

Escaping single quotes with backslash seems not to work

[Ron Johnson]

PG 9.6 and PG 14

https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

[quote]
Any other character following a backslash is taken literally. Thus, to
include a backslash character, write two backslashes (\\). Also, a single
quote can be included in an escape string by writing \', in addition to the
normal way of ''.
[/quote]

But it doesn't seem to work.  Obviously there's some misconfiguration or ,
but I don't see what I did wrong.

TAP=# insert into foo (name, description) values ('XYZ_Name ', '''XYZ ''');
INSERT 0 1

TAP=# insert into foo (name, description) values ('XYZ_Name ', '\'XYZ ');
TAP'#
TAP'# ');
ERROR:  syntax error at or near "XYZ"
LINE 1: ...into foo (name, description) values ('XYZ_Name ', '\'XYZ ');

TAP=# show standard_conforming_strings;
 standard_conforming_strings
-
 on
(1 row)

TAP=#
TAP=# show backslash_quote;
 backslash_quote
-
 safe_encoding
(1 row)

Re: Question on pg_cron

[Ron Johnson]

On Sat, Jun 8, 2024 at 5:31 AM yudhi s  wrote:

> Hello All,
>
> We have around 10 different partition tables for which the partition
> maintenance is done using pg_partman extension. These tables have foreign
> key dependency between them.  We just called partman.run_maintanance_proc()
> through pg_cron without any parameters and it was working fine. So we can
> see only one entry in the cron.job table. And it runs daily once.
>
> It was all working fine and we were seeing the historical partition being
> dropped and new partitions being created without any issue. But suddenly we
> started seeing, its getting failed with error "ERROR: can not drop
> schema1.tab1_part_p2023_12_01 because other objects depend on it"
>

Have you changed version lately of PG, pg_cron or pg_partman?  Or maybe
what pg_cron or pg_partman depends on?

Re: Poor performance after restoring database from snapshot on AWS RDS

[Ron Johnson]

On Fri, Jun 7, 2024 at 4:36 AM Sam Kidman  wrote:

> > This is due to the way that RDS restores snapshots.
>
> Thanks, I never would have guessed. Would vacuum analyze be sufficient
> to defeat the lazy loading or would we need to do something more
> specific to our application? (for example. select(*) on some commonly
> used tables)
>

https://www.postgresql.org/docs/14/pgprewarm.html

pg_prewarm is probably what you want.  Don't know if RDS Postgresql
supports it or not, though.


>
> I think vacuum full would certainly defeat the lazy loading since it
> would copy all of the table data, but that may take a very long time
> to run. I think vacuum analyze only scans a subset of rows but I might
> be wrong about that.
>
> Best, Sam
>
> On Wed, Jun 5, 2024 at 10:09 PM Jeremy Smith 
> wrote:
> >
> > On Wed, Jun 5, 2024 at 4:23 AM Sam Kidman  wrote:
> >
> > > We get very poor performance in the staging environment after this
> > > restore takes place - after some usage it seems to get better perhaps
> > > because of caching.
> > >
> >
> > This is due to the way that RDS restores snapshots.
> >
> > From the docs (
> https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RestoreFromSnapshot.html
> ):
> >
> > You can use the restored DB instance as soon as its status is
> > available. The DB instance continues to load data in the background.
> > This is known as lazy loading.
> >
> > If you access data that hasn't been loaded yet, the DB instance
> > immediately downloads the requested data from Amazon S3, and then
> > continues loading the rest of the data in the background.
> >
> >
> >
> >   -Jeremy
>
>
>

Re: PG 14 pg_basebackup accepts --compress=server-zst option

[Ron Johnson]

On Fri, Jun 7, 2024 at 12:32 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thursday, June 6, 2024, Kashif Zeeshan  wrote:
>
>> Hi
>>
>> On Fri, Jun 7, 2024 at 6:54 AM Ron Johnson 
>> wrote:
>>
>>>
>>> https://www.postgresql.org/docs/14/app-pgbasebackup.html doesn't
>>> mention "--compress=[{client|server}-]method".  That first appears in the
>>> v15 docs.
>>>
>>> And yet pg_basebackup doesn't complain about an invalid option.
>>> (Technically, this is a bug; I first noticed it a week after copying a
>>> script from a PG 15 server to five PG 14 servers, and running it quite a
>>> few times without fail.)
>>>
>>
> Seems a bit suspect, but as your script doesn’t mention tar the option
> itself is apparently ignored, I guess silently.
>

Does this mean that "--compress=server-zst" is only relevant with
--format=tar?


> Assuming this isn’t an actual regression in behavior in a patch-released
> older version
>

My apologies for not mentioning the version: 14.12-1PGDG-rhel8.


> I don’t see us adding an error message at this point.
>

Me neither.  It just seemed odd.

PG 14 pg_basebackup accepts --compress=server-zst option

[Ron Johnson]

https://www.postgresql.org/docs/14/app-pgbasebackup.html doesn't mention
"--compress=[{client|server}-]method".  That first appears in the v15 docs.

And yet pg_basebackup doesn't complain about an invalid option.
(Technically, this is a bug; I first noticed it a week after copying a
script from a PG 15 server to five PG 14 servers, and running it quite a
few times without fail.)

$ pg_basebackup \
> --pgdata=$PGDATA \
> --dbname=service=basebackup \
> --verbose --progress \
> --checkpoint=fast \
> --write-recovery-conf \
> --wal-method=stream \
> --create-slot --slot=pgstandby1 \
> --compress=server-zst ; echo $?
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 256/BC28 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created replication slot "pgstandby1"
42567083/42567083 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 256/BC000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
0

Re: Logical replication type- WAL recovery fails and changes the size of wal segment in archivedir

[Ron Johnson]

On Wed, Jun 5, 2024 at 6:26 AM Laurenz Albe 
wrote:

> On Wed, 2024-06-05 at 06:36 +, Meera Nair wrote:
> > 2024-06-05 11:41:32.369 IST [54369] LOG:  restored log file
> "00050001006A" from archive
> > 2024-06-05 11:41:33.112 IST [54369] LOG:  restored log file
> "00050001006B" from archive
> > cp: cannot stat ‘/home/pgsql/wmaster/00050001006C’: No such
> file or directory
> > 2024-06-05 11:41:33.167 IST [54369] LOG:  redo done at 1/6B000100
> > 2024-06-05 11:41:33.172 IST [54369] FATAL:  archive file
> "00050001006B" has wrong size: 0 instead of 16777216
> > 2024-06-05 11:41:33.173 IST [54367] LOG:  startup process (PID 54369)
> exited with exit code 1
> > 2024-06-05 11:41:33.173 IST [54367] LOG:  terminating any other active
> server processes
> > 2024-06-05 11:41:33.174 IST [54375] FATAL:  archive command was
> terminated by signal 3: Quit
> > 2024-06-05 11:41:33.174 IST [54375] DETAIL:  The failed archive command
> was: cp pg_wal/00050001006B
> /home/pgsql/wmaster/00050001006B
> > 2024-06-05 11:41:33.175 IST [54367] LOG:  archiver process (PID 54375)
> exited with exit code 1
> > 2024-06-05 11:41:33.177 IST [54367] LOG:  database system is shut down
> >
> > Here ‘/home/pgsql/wmaster’ is my archivedir (the folder where WAL
> segments are restored from)
> >
> > Before attempting start, size of
> > 00050001006B file was 16 MB.
> > After failing to detect 00050001006C, there is a FATAL error
> saying wrong size for 00050001006B
> > Now the size of 00050001006B is observed as 2 MB. Size of
> all other WAL segments remain 16 MB.
> >
> > -rw--- 1 postgres postgres  2359296 Jun  5 11:34
> 00050001006B
>
> That looks like you have "archive_mode = always", and "archive_command"
> writes
> back to the archive.  Don't do that.
>

In fact, don't write your own PITR backup process.  Use something like
PgBackRest or BarMan.

Re: Can't Remote connection by IpV6

[Ron Johnson]

On Thu, Jun 6, 2024 at 11:03 AM Adrian Klaver 
wrote:

> On 6/6/24 07:46, Marcelo Marloch wrote:
> > Hi everyone, is it possible to remote connect through IpV6? IpV4 works
> > fine but I cant connect through V6
> >
> > postgresql.conf is to listen all address and pg_hba.conf is set with
> > host all all :: md5 i've tried ::/0 and ::0/0 but had no success
>
> Is the firewall open for IPv6 connections to the Postgres port?
>

netcat (comes with nmap) is great for this.  There's a Windows client, too.

Re: Purpose of pg_dump tar archive format?

[Ron Johnson]

On Tue, Jun 4, 2024 at 3:47 PM Gavin Roy  wrote:

>
> On Tue, Jun 4, 2024 at 3:15 PM Ron Johnson 
> wrote:
>
>>
>> But why tar instead of custom? That was part of my original question.
>>
>
> I've found it pretty useful for programmatically accessing data in a dump
> for large databases outside of the normal pg_dump/pg_restore workflow. You
> don't have to seek through one large binary file to get to the data section
> to get at the data.
>

Interesting.  Please explain, though, since a big tarball _is_ "one large
binary file" that you have to sequentially scan.  (I don't know the
internal structure of custom format files, and whether they have file
pointers to each table.)

Is it because you need individual .dat "COPY" files for something other
than loading into PG tables (since pg_restore --table= does that, too),
and directory format archives can be inconvenient?

Re: Purpose of pg_dump tar archive format?

[Ron Johnson]

On Tue, Jun 4, 2024 at 2:55 PM Rob Sargent  wrote:

>
>
> On 6/4/24 11:40, Shaheed Haque wrote:
> >
> > We use it. I bet lots of others do too.
> >
> >
>
> Of course.  There are lots of small, real, useful databases in the wild.
>

But why tar instead of custom? That was part of my original question.

Re: Purpose of pg_dump tar archive format?

[Ron Johnson]

On Tue, Jun 4, 2024 at 10:43 AM Adrian Klaver 
wrote:

> On 6/4/24 05:13, Ron Johnson wrote:
> > It doesn't support compression nor restore reordering like the custom
> > format, so I'm having trouble seeing why it still exists (at least
> > without a doc warning that it's obsolete).
>
> pg_dump -d test -U postgres -Ft  | gzip --stdout > test.tgz
>

Who's got meaningful databases that small anymore?

And if you've got meaningfully sized databases, open port 5432 and move
them using pg_dump.

Purpose of pg_dump tar archive format?

[Ron Johnson]

It doesn't support compression nor restore reordering like the custom
format, so I'm having trouble seeing why it still exists (at least without
a doc warning that it's obsolete).

Re: Postgresql 16.3 Out Of Memory

[Ron Johnson]

On Mon, Jun 3, 2024 at 9:12 AM Greg Sabino Mullane 
wrote:

> On Mon, Jun 3, 2024 at 6:19 AM Radu Radutiu  wrote:
>
>> Do you have any idea how to further debug the problem?
>>
>
> Putting aside the issue of non-reclaimed memory for now, can you show us
> the actual query? The explain analyze you provided shows it doing an awful
> lot of joins and then returning 14+ million rows to the client. Surely the
> client does not need that many rows?
>

And the query cost is really high.  "Did you ANALYZE the instance after
conversion?" was my first question.

Re: ERROR: found xmin from before relfrozenxid; MultiXactid does no longer exist -- apparent wraparound

[Ron Johnson]

On Fri, May 31, 2024 at 1:25 PM Alanoly Andrews  wrote:

> Yes, and I know that upgrading the Postgres version is the stock answer
> for situations like this. The upgrade is in the works.
>

*Patching *was the solution.  It takes *five minutes*.
Here's how I did it (since our RHEL systems are blocked from the Internet,
and I had to manually d/l the relevant RPMs):
$ sudo -iu postgres pg_ctl stop -wt -mfast
$ sudo yum install PG96.24_RHEL6/*rpm
$ sudo -iu postgres pg_ctl start -wt

You'll have a bit of effort finding the PG10 repository, since it's EOL,
but it can be found.

Re: PG 12.2 ERROR: cannot freeze committed xmax

[Ron Johnson]

On Thu, May 23, 2024 at 9:41 AM bruno da silva  wrote:

> Hello,
> I have a deployment with PG 12.2 reporting ERROR: cannot freeze committed
> xmax
> using Red Hat Enterprise Linux 8.9.
>
> What is the recommended to find any bug fixes that the version 12.2 had
> that could have caused this error.
>

https://www.postgresql.org/docs/release/

You're missing *four years* of bug fixes.

Could this error be caused by OS/Hardware related issues?
>

 Four years of bug fixes is more likely the answer.

Re: search_path and SET ROLE

[Ron Johnson]

On Wed, May 22, 2024 at 2:02 PM Isaac Morland 
wrote:

> On Wed, 22 May 2024 at 13:48, Ron Johnson  wrote:
>
> As a superuser administrator, I need to be able to see ALL tables in ALL
>> schemas when running "\dt", not just the ones in "$user" and public.  And I
>> need it to act consistently across all the systems.
>>
>
> \dt *.*
>

Also shows information_schema, pg_catalog, and pg_toast.  I can adjust to
that, though.


> But I am skeptical how often you really want this in a real database with
> more than a few tables. Surely \dn+ followed by \dt [schemaname].* for a
> few strategically chosen [schemaname] would be more useful?
>

More than you'd think.  I'm always looking up the definition of this table
or that table (mostly for indices and keys), and I never remember which
schema they're in.

Re: search_path wildcard?

[Ron Johnson]

On Wed, May 22, 2024 at 1:58 PM Tom Lane  wrote:

> Ron Johnson  writes:
> > That would be a helpful feature for administrators, when there are
> multiple
> > schemas in multiple databases, on multiple servers: superusers get ALTER
> > ROLE foo SET SEARCH_PATH  = '*'; and they're done with it.
>
> ... and they're pwned within five minutes by any user with the wits
> to create a trojan-horse function or operator.  Generally speaking,
> you want admins to run with a minimal search path not a maximal one.
>

Missing tables when running "\t" is a bigger hassle.

Re: search_path wildcard?

[Ron Johnson]

On Wed, May 22, 2024 at 12:53 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, May 22, 2024, 10:36 Ron Johnson  wrote:
>
>> This doesn't work, and I've found nothing similar:
>> ALTER ROLE foo SET SEARCH_PATH  = '*';
>>
>
> Correct, you cannot do that.
>

That would be a helpful feature for administrators, when there are multiple
schemas in multiple databases, on multiple servers: superusers get ALTER
ROLE foo SET SEARCH_PATH  = '*'; and they're done with it.

Re: search_path and SET ROLE

[Ron Johnson]

On Wed, May 22, 2024 at 1:10 PM Tom Lane  wrote:

> Ron Johnson  writes:
> > It seems that the search_path of the role that you SET ROLE to does not
> > become the new search_path.
>
> It does for me:
>
> regression=# create role r1;
> CREATE ROLE
> regression=# create schema r1 authorization r1;
> CREATE SCHEMA
> regression=# select current_schemas(true), current_user;
>current_schemas   | current_user
> -+--
>  {pg_catalog,public} | postgres
> (1 row)
>
> regression=# set role r1;
> SET
> regression=> select current_schemas(true), current_user;
> current_schemas | current_user
> +--
>  {pg_catalog,r1,public} | r1
> (1 row)
>
> regression=> show search_path ;
>search_path
> -
>  "$user", public
> (1 row)
>
> The fine manual says that $user tracks the result of
> CURRENT_USER, and at least in this example it's doing that.
> (I hasten to add that I would not swear there are no
> bugs in this area.)
>
> > Am I missing something, or is that PG's behavior?
>
> I bet what you missed is granting (at least) USAGE on the
> schema to that role.  PG will silently ignore unreadable
> schemas when computing the effective search path.
>

There are multiple schemata in (sometimes) multiple databases on (many)
multiple servers.

As a superuser administrator, I need to be able to see ALL tables in ALL
schemas when running "\dt", not just the ones in "$user" and public.  And I
need it to act consistently across all the systems.

(Heck, none of our schemas are named the same as roles.)

This would be useful for account maintenance:

CREATE ROLE dbagrp SUPERUSER INHERIT NOLOGIN;
ALTER ROLE dbagrp SET search_path = public, dba, sch1, sch2, sch3, sch4;
CREATE USER joe IN GROUP dbagrp INHERIT PASSWORD = 'linenoise';

Then, as user joe:
SHOW search_path;
   search_path
-
 "$user", public
(1 row)
SET ROLE dbagrp RELOAD SESSION; -- note the new clause
SHOW search_path;
   search_path
---
public , dba, sch1, sch2, sch3, sch4
(1 row)

When a new DBA comes on board, add him/her to dbagrp, and they
automagically have everything  that dbagrp has.
Now, each dba must individually be given a search_path.  If you forget, or
forget to add some schemas, etc, mistakes ger made and time is wasted.

search_path wildcard?

[Ron Johnson]

This doesn't work, and I've found nothing similar:
ALTER ROLE foo SET SEARCH_PATH  = '*';

Is there a single SQL statement which will generate a search path based
on information_schema.schemata, or do I have to write an anonymous DO
procedure?
SELECT schema_name FROM information_schema.schemata WHERE schema_name !=
'information_schema' AND schema_name NOT LIKE 'pg_%';

search_path and SET ROLE

[Ron Johnson]

PG 9.6.24 (Soon, I swear!)

It seems that the search_path of the role that you SET ROLE to does not
become the new search_path.

Am I missing something, or is that PG's behavior?

AS USER postgres


$ psql -h 10.143.170.52 -Xac "CREATE ROLE dbagrp SUPERUSER INHERIT NOLOGIN;"
CREATE ROLE dbagrp SUPERUSER INHERIT NOLOGIN;
CREATE ROLE

$ psql -h 10.143.170.52 -Xac "CREATE USER rjohnson IN GROUP dbagrp INHERIT;"
CREATE USER rjohnson IN GROUP dbagrp INHERIT;
CREATE ROLE

[postgres@FISPMONDB001 ~]$ psql -h 10.143.170.52 -Xac "CREATE USER
\"11026270\" IN GROUP dbagrp INHERIT PASSWORD '${NewPass}' VALID UNTIL
'2024-06-30 23:59:59';"
CREATE USER "11026270" IN GROUP dbagrp INHERIT PASSWORD 'linenoise' VALID
UNTIL '2024-06-30 23:59:59';
CREATE ROLE

$ psql -h 10.143.170.52 -Xac "ALTER ROLE dbagrp set search_path = dbagrp,
public, dba, cds, tms;"
ALTER ROLE dbagrp set search_path = dbagrp, public, dba, cds, tms;
ALTER ROLE

AS USER rjohnson


[rjohnson@fpslbxcdsdbppg1 ~]$ psql -dCDSLBXW
psql (9.6.24)
Type "help" for help.

CDSLBXW=> SET ROLE dbagrp;
SET
CDSLBXW=#
CDSLBXW=# SHOW SEARCH_PATH;
   search_path
-
 "$user", public
(1 row)


Back to user postgres
=

$ psql -h 10.143.170.52 -Xac "ALTER ROLE rjohnson set search_path = dbagrp,
public, dba, cds, tms;"
ALTER ROLE rjohnson set search_path = dbagrp, public, dba, cds, tms;
ALTER ROLE

Back to user rjohnson
=

[rjohnson@fpslbxcdsdbppg1 ~]$ psql -dCDSLBXW
psql (9.6.24)
Type "help" for help.

CDSLBXW=>
CDSLBXW=> SET ROLE dbagrp;
SET

CDSLBXW=# SHOW SEARCH_PATH;
  search_path
---
 dbagrp, public, dba, cds, tms
(1 row)

Re: pg_dump and not MVCC-safe commands

[Ron Johnson]

On Mon, May 20, 2024 at 11:54 AM Christophe Pettus  wrote:

>
>
> > On May 20, 2024, at 08:49, PetSerAl  wrote:
> > Basically, you need application cooperation to make
> > consistent live database backup.
>
> If it is critical that you have a completely consistent backup as of a
> particular point in time, and you are not concerned about restoring to a
> different processor architecture, pg_basebackup is a superior solution to
> pg_dump.
>

Single-threaded, and thus dreadfully slow.  I'll stick with PgBackRest.

Re: Unnecessary buffer usage with multicolumn index, row comparison, and equility constraint

[Ron Johnson]

On Fri, May 10, 2024 at 11:28 PM WU Yan <4wu...@gmail.com> wrote:

> Hi everyone, first time here. Please kindly let me know if this is not the
> right place to ask.
>
> I notice a simple query can read a lot of buffer blocks in a meaningless
> way, when
> 1. there is an index scan on a multicolumn index
> 2. there is row constructor comparison in the Index Cond
> 3. there is also an equality constraint on the leftmost column of the
> multicolumn index
>
>
> ## How to reproduce
>
> I initially noticed it on AWS Aurora RDS, but it can be reproduced in
> docker container as well.
> ```bash
> docker run --name test-postgres -e POSTGRES_PASSWORD=mysecretpassword -d
> -p 5432:5432 postgres:16.3
> ```
>
> Create a table with a multicolumn index. Populate 12 million rows with
> random integers.
> ```sql
> CREATE TABLE t(a int, b int);
> CREATE INDEX my_idx ON t USING BTREE (a, b);
>
> INSERT INTO t(a, b)
> SELECT
> (random() * 123456)::int AS a,
> (random() * 123456)::int AS b
> FROM
> generate_series(1, 12345678);
>
> ANALYZE t;
> ```
>
> Simple query that uses the multicolumn index.
> ```
> postgres=# explain (analyze, buffers) select * from t where row(a, b) >
> row(123450, 123450) and a = 0 order by a, b;
>

Out of curiosity, why "where row(a, b) > row(123450, 123450)" instead of "where
a > 123450 and b > 123450"?

Forcing INTERVAL days display, even if the interval is less than one day

[Ron Johnson]

PG 9.6.24, if relevant.  (Hopefully we're migrating next month.)

Displaying how long ago a date was is easy, but interval casts "helpfully"
suppress "X days ago" if the interval is less than one day ago.

How do I make it display "days ago", even when days ago is zero?
Explicitly casting "day to second" didn't work.

CDSLBXW=# with
tables as
(
select schemaname||'.'||relname as table_name
 , greatest(last_vacuum, last_autovacuum) as latest_vacuum
from pg_stat_user_tables
)
select table_name, latest_vacuum,
   date_trunc('second', (current_timestamp - latest_vacuum))::interval
day to second as vacuumed_ago
from tables
order by latest_vacuum desc
limit 30;
   table_name   | latest_vacuum |
 vacuumed_ago
+---+-
 cds.x937_file  | 2024-05-07 10:53:38.971431-04 | 00:01:45
 cds.lockbox_end_of_day | 2024-05-07 10:53:38.758813-04 | 00:01:45
 dba.index_bloat_2stg   | 2024-05-07 10:49:09.196655-04 | 00:06:15
 dba.index_bloat_1stg   | 2024-05-07 10:49:03.153449-04 | 00:06:21
 dba.table_bloat_2stg   | 2024-05-07 10:48:56.681218-04 | 00:06:28
 dba.table_bloat_1stg   | 2024-05-07 10:48:50.233984-04 | 00:06:34
 cds.x937_cash_letter   | 2024-05-07 10:45:38.763453-04 | 00:09:45
 tms.batch  | 2024-05-07 10:37:50.758763-04 | 00:17:33
 cds.cdslockbox | 2024-05-07 10:35:38.625663-04 | 00:19:46
 tms.item_mapping   | 2024-05-07 10:29:09.16413-04  | 00:26:15
 public.job | 2024-05-07 10:03:38.270296-04 | 00:51:46
 cds.mail_out_address   | 2024-05-07 09:55:38.269805-04 | 00:59:46
 cds.rebatching_rule| 2024-05-07 09:38:38.062069-04 | 01:16:46
 cds.cds_job_history| 2024-05-07 09:16:40.071253-04 | 01:38:44
 tms.document   | 2024-05-07 08:01:15.545398-04 | 02:54:09
 cds.cdsdocument| 2024-05-07 08:00:13.793372-04 | 02:55:10
 cds.all_day_event_trigger  | 2024-05-07 07:54:38.202722-04 | 03:00:46
 public.job_history | 2024-05-07 01:45:25.265417-04 | 09:09:59
 tms.chk_image  | 2024-05-06 15:39:12.708045-04 | 19:16:12
 tms.transaction| 2024-05-06 15:38:32.878078-04 | 19:16:51
 tms.payment| 2024-05-06 14:10:17.76129-04  | 20:45:06
 public.schedule| 2024-05-05 00:00:49.160792-04 | 2 days
10:54:35
 tms.gl_ticket_image| 2024-05-04 23:55:05.632414-04 | 2 days
11:00:19
 tms.alerted_watchlist  | 2024-05-04 23:55:05.62597-04  | 2 days
11:00:19
 cds.balancing_record_imagerps  | 2024-05-04 23:55:05.625671-04 | 2 days
11:00:19
 cds.balancing_record_publisher | 2024-05-04 23:55:05.618346-04 | 2 days
11:00:19
 tms.credit_card| 2024-05-04 23:55:05.617497-04 | 2 days
11:00:19
 tms.chk_original_image | 2024-05-04 23:55:05.607952-04 | 2 days
11:00:19
 cds.billing_volume_header  | 2024-05-04 23:55:05.60093-04  | 2 days
11:00:19
 cds.balancing_publisher_batch  | 2024-05-04 23:55:05.590679-04 | 2 days
11:00:19
(30 rows)

Re: Ora2pg Delta Migration: Oracle to PostgreSQL

[Ron Johnson]

On Thu, May 2, 2024 at 8:28 PM Amit Sharma  wrote:

> Hello,
>
> Has anyone tried delta/incremental data migration for Oracle to PostgreSQL
> using Ora2pg? Or what are the best options to run delta migration for
> Oracle to PostgreSQL?
>

What do the ora2pg docs say about whether or not that feature is
implemented?  (It wasn't when I last used it in 2022.)

Re: Prevent users from executing pg_dump against tables

[Ron Johnson]

On Thu, May 2, 2024 at 1:47 AM RAJAMOHAN  wrote:

> Hello all,
>
> In our production db infrastructure, we have one read_only role which has
> read privileges against all tables in schema A.
>
> We are planning to grant this role to some developers for viewing the
> data, but also I want to limit the users from executing statements like
> copy or using pg_dump. Main reason being I don't want the data to be copied
> from the database to their local machines.
>
> I tried by implementing triggers, but was not able to figure out a way to
> restrict the pg_dump and allow only select statements.
>

> Is there a way to implement this? Please advise.
> 
>

If you can query a table, then you can save the query contents to your
local context.  That's a fundamental law of nature, since you gave them
read privs.

For example:
psql --host=SomeEC2Node $DB -Xc "SELECT * FROM read_only_table;" >
read_only_table.txt

That even works on Windows.

Re: Linked directory or explicit reference

[Ron Johnson]

On Thu, May 2, 2024 at 12:50 AM Senor Cervesa 
wrote:
[snip]

>  I'm not sure what would trigger "directory not empty".


The lost+found directory.

Re: Posgresql 14 and CarbonBlack on RHEL8?

[Ron Johnson]

On Tue, Apr 30, 2024 at 10:07 PM Tom Lane  wrote:

> Ron Johnson  writes:
> > When running stress tests on the systems (in prod, during the maintenance
> > window), 171K events/second are generated on the RHEL8 servers, and CB
> > needs (according to top(1)) 325% of CPU to handle that, and still
> dropping
> > 92% of them.
> > The RHEL6 system doesn't bat an eye at running the exact same test (36
> cron
> > jobs running psql executing SELECT statements).
>
> Is JIT enabled on the newer system?  If so try turning it off, or else
> raise the associated cost settings.  We've seen lots of reports of
> workloads where, by default, the planner is too aggressive about
> applying JIT.
>

A puzzling suggestion.  Why should it impact AV software?

At one point, I disabled JIT to test its impact on PG, performance was a
bit of a wash (some queries were a bit faster, some were a bit slower), but
I didn't monitor CB.

Just now, I did ALTER SYSTEM SET jit='off'; and re-ran the stress test.  No
impact to CarbonBlack.

Posgresql 14 and CarbonBlack on RHEL8?

[Ron Johnson]

(CarbonBlack is cross-platform AV software sold by VMware.)

Currently we're running PG 9.6.24 on RHEL 6.10 with CB (version unknown to
me) in production, and testing PG 14.11 on RHEL 8.9 with CB 2.15.2
(hopefully going into production next month).

Both old and new VMs are 32 CPU with 128GB RAM.
Nothing but PG, CB and itsm software runs on these systems.

When running stress tests on the systems (in prod, during the maintenance
window), 171K events/second are generated on the RHEL8 servers, and CB
needs (according to top(1)) 325% of CPU to handle that, and still dropping
92% of them.
The RHEL6 system doesn't bat an eye at running the exact same test (36 cron
jobs running psql executing SELECT statements).

The small RHEL8/PG14 non-prod systems show similar load when lots of SELECT
statements run.

Has anyone else seen this?  If so, how did you resolve it?

Re: Linked directory or explicit reference

[Ron Johnson]

On Tue, Apr 30, 2024 at 7:00 PM Senor Cervesa 
wrote:

> Hi All;
>
> When doing an initial install of PostgreSQL on RHEL 7 or 8 derived OS via
> rpm, what are pros, cons and recommendations of these 2 procedures for
> utilizing a second disk?
>
> Secondary SSD or RAID mounted at /disk2.
>
> Option #1
>
>1. install the rpm which creates basic user and home
>2. Create symlink /var/lib/pgsql/15/data --> /disk2/data
>3. initdb with no special options
>
> Or Option #2
>
>1. install the rpm which creates basic user and home
>2. initdb with --pgdata=/disk2/data
>Probably using included 'postgresql-12-setup' script
>
> I also link /var/lib/pgsql/data  --> ../15/data so automation can
> reference postgresql.conf without knowing version (legacy stuff).
>

In my experience,The PgBackRest restore feature does not like symlinks.


> The install is automated with a bash script which handles several options
> including whether there is a second disk for DB. Scripting the install with
> or without the second disk is straight forward but I'm concerned with
> either scenario causing unforeseen differences.
>
> I don't think there's a benefit to using tablespace here but I have no
> experience with it. The systemd service is configured with a dependency on
> the disk mount so I don't think there are different risks for starting
> postgres with missing data directory.
>
> I've run postgres in both scenarios and not had any issues. I'm interested
> in comments from others on their experience using these or other options.
>
Is the mount point just "/disk2" when using "--pgdata=/disk2/data"?  I've
gotten "directory not empty" errors when the mount point is
"/Database/x.y/data".

Re: best migration solution

[Ron Johnson]

On Thu, Apr 25, 2024 at 3:55 AM Zwettler Markus (OIZ) <
markus.zwett...@zuerich.ch> wrote:

> we have to migrate from hosted PG12 to containerized PG16 on private
> cloud.
>
>
>
> some of the installed PG12 extensions are not offered on the containerized
> PG16, eg. PostGIS related extensions like pg_routing and ogr_fdw.
>
> some of these extensions are not needed anymore. some of these extensions
> were installed in their own schema.
>
> we also need to change the database names and most role names due to
> external requirements.
>
>
>
>
>
> I came up with this solution.
>
>
>
> dump all roles with pg_dumpall.
>
> edit this dumpfile and
>
>- exclude roles not needed
>- change required role names
>
>
>
> dump all required databases with pg_dump
>
>- in plain text
>- exclude all schemas not needed
>
> edit this dump file and
>
>- exclude any "create extension" command for not existing extensions
>- change all required role names on permissions and ownerships
>
>
>
>
>
> any missings?
> any better solutions?
>

How big of a database?

Editing a giant SQL file in vim is painful.

I'd do this, which is conceptually similar to your plan:
* pg_dump -Fd
* pg_restore --list
* Edit the generated list
* pg_restore --use-list=edited_list

 I wonder whether a plain text dump could lead to conversion problems or
> something similar?
>

Maybe, if the collations are different between the source and destination.

Re: Backup_Long Running

[Ron Johnson]

PgBackRest is in the PGDG repositories (RHEL & Debian).

The documentation is thorough, and discoverable via Google,

On Wed, Apr 24, 2024 at 8:30 AM jaya kumar  wrote:

> Thanks for your update. Can you have any link or document to configure L0
> & L1 backup using pgbackrest tool. Also share the pgbackrest installation
> method.
>
> On Wed, Apr 24, 2024 at 2:50 PM Vijaykumar Jain <
> vijaykumarjain.git...@gmail.com> wrote:
>
>>
>> On Wed, Apr 24, 2024, 12:33 PM jaya kumar  wrote:
>>
>>> Hi Team,
>>>
>>>
>>>
>>> Production database Backup is running very long hours. Any option to
>>> reduce backup time? Kindly advise me.
>>>
>>>
>>>
>>> DB size: 793 GB
>>>
>>>
>>>
>>> We are taking pg_basebackup backup.
>>>
>>>
>> do you see network saturation, io saturation ?
>> generally faster hardware i.e striped and or nvme disks along with a
>> robust network link and capacity should help get the backup done quickly.
>> where are you taking the backup from? is the server busy doing other work
>> or it is a dedicated machine for backups ?
>> basically monitor for resource saturation, if all looks good, we could
>> take basebackup of a 10tb db in 8 hours, and in another case on a slow
>> remote storage, backup of 2tb took 1 day.
>>
>> now, pgbackrest can speedup backup processes by spawning more workers for
>> archiving and stuff. we have taken backup on nvme disks striped of 28tb in
>> 3 hours, bare metals servers with powerful cpu.
>>
>> so , it's hardware  else switch to pgbackrest which can take
>> incremental/differential/full backups.
>> there are other tools too, I used only these two.
>>
>>>
>>>
>
> --
> Thanks & Regards,
> Jayakumar.S
> +91-9840864439.
>

Re: CLUSTER vs. VACUUM FULL

[Ron Johnson]

On Mon, Apr 22, 2024 at 5:56 PM Adrian Klaver 
wrote:

> On 4/22/24 14:35, Ron Johnson wrote:
>
> >  >
> >  > On Stack Exchange, I've got a question on how to determine when
> > to run
> >  > CLUSTER.  It ties in strongly with this thread..
> >  >
> >
> > And the link is?
>
> It should have been the initial question of this thread and it explains
> what you are really after.
>

It was already a long email.

Re: issue with reading hostname

[Ron Johnson]

There's no hostname in a socket directory.

Do you mean :"change the socket name from /tmp or /var/run/postgresql to
something else?"

If so, then
https://www.postgresql.org/docs/16/runtime-config-connection.html mentions
sockets.

On Mon, Apr 22, 2024 at 5:37 PM Atul Kumar  wrote:

> Can we edit the socket to change the hostname in it ?
>
> Regards.
>
> On Tue, Apr 23, 2024 at 2:41 AM Ron Johnson 
> wrote:
>
>> On Mon, Apr 22, 2024 at 4:14 PM Atul Kumar  wrote:
>>
>>> Hi,
>>>
>>> I have postgresql  version 15 running on centos7.
>>>
>>> I have below query that reads hostname from /tmp directory:
>>>
>>> psql postgres -A -t -p 5432 -h /tmp/ -c 'SELECT pg_is_in_recovery();'
>>>
>>
>> If you installed from the PGDG repository (possibly also the CENTOS
>> repos, but I'm not sure), then the domain socket also lives in :
>> /var/run/postgresql
>>
>> * I find that more expressive than /tmp.
>> * No need to specify the host when using sockets.
>> * Using a socket name makes parameterizing the hostname easier in scripts.
>>
>>
>>

Re: CLUSTER vs. VACUUM FULL

[Ron Johnson]

On Mon, Apr 22, 2024 at 5:03 PM Adrian Klaver 
wrote:

> On 4/22/24 13:59, Ron Johnson wrote:
> > On Mon, Apr 22, 2024 at 4:21 PM Adrian Klaver  > <mailto:adrian.kla...@aklaver.com>> wrote:
> > [snip]
> >
> > Which gets us back to your comment upstream:
> >
> > "What the VACUUM docs "should" do, it seems, is suggest CLUSTER on
> the
> > PK, if the PK is a sequence (whether that be an actual sequence, or a
> > timestamp or something else that grows monotonically)."
> >
> > This is a case specific to you and this particular circumstance, not
> a
> > general rule for VACUUM FULL. If for no other reason then it might
> make
> > more sense for the application that the CLUSTER be done on some other
> > index then the PK.
> >
> >
> > On Stack Exchange, I've got a question on how to determine when to run
> > CLUSTER.  It ties in strongly with this thread..
> >
>
> And the link is?
>

Sorry.  Got distracted by the answer.

https://dba.stackexchange.com/questions/338870/when-to-rerun-cluster

Re: issue with reading hostname

[Ron Johnson]

On Mon, Apr 22, 2024 at 4:14 PM Atul Kumar  wrote:

> Hi,
>
> I have postgresql  version 15 running on centos7.
>
> I have below query that reads hostname from /tmp directory:
>
> psql postgres -A -t -p 5432 -h /tmp/ -c 'SELECT pg_is_in_recovery();'
>

If you installed from the PGDG repository (possibly also the CENTOS repos,
but I'm not sure), then the domain socket also lives in :
/var/run/postgresql

* I find that more expressive than /tmp.
* No need to specify the host when using sockets.
* Using a socket name makes parameterizing the hostname easier in scripts.

Re: CLUSTER vs. VACUUM FULL

[Ron Johnson]

On Mon, Apr 22, 2024 at 4:21 PM Adrian Klaver 
wrote:
[snip]

> Which gets us back to your comment upstream:
>
> "What the VACUUM docs "should" do, it seems, is suggest CLUSTER on the
> PK, if the PK is a sequence (whether that be an actual sequence, or a
> timestamp or something else that grows monotonically)."
>
> This is a case specific to you and this particular circumstance, not a
> general rule for VACUUM FULL. If for no other reason then it might make
> more sense for the application that the CLUSTER be done on some other
> index then the PK.
>

On Stack Exchange, I've got a question on how to determine when to run
CLUSTER.  It ties in strongly with this thread..

Re: CLUSTER vs. VACUUM FULL

[Ron Johnson]

On Mon, Apr 22, 2024 at 3:14 PM Adrian Klaver 
wrote:

>
>
> On 4/22/24 11:45 AM, Ron Johnson wrote:
> > On Mon, Apr 22, 2024 at 12:29 PM David G. Johnston
> > mailto:david.g.johns...@gmail.com>> wrote:
> >
> >
> >
> > On Mon, Apr 22, 2024, 08:37 Ron Johnson  > <mailto:ronljohnso...@gmail.com>> wrote:
> >
> > On Mon, Apr 22, 2024 at 10:25 AM Tom Lane  > <mailto:t...@sss.pgh.pa.us>> wrote:
> >
> > Marcos Pegoraro  > <mailto:mar...@f10.com.br>> writes:
> >  > But wouldn't it be good that VACUUM FULL uses that index
> > defined by
> >  > Cluster, if it exists ?
> >
> > No ... what would be the difference then?
> >
> > What the VACUUM docs "should" do, it seems, is suggest CLUSTER
> > on the PK, if the PK is a sequence (whether that be an actual
> > sequence, or a timestamp or something else that grows
> > monotonically).
> >
> > That's because the data is already roughly in PK order.
> >
> >
> > If things are bad enough to require a vacuum full that doesn't seem
> > like a good assumption.
> >
> >
> > Sure it does.
> >
> > For example, I just deleted the oldest half of the records in 30
> > tables.  Tables who's CREATED_ON timestamp value strongly correlates to
> > the synthetic PK sequence values.
> >
> > Thus, the remaining records were still mostly in PK order.  CLUSTERs on
> > the PK values would have taken just about as much time as the VACUUM
> > FULL statements which I /did/ run.
>
> 1) If they are already in enough of a PK order that the CLUSTER time vs
> VACUUM FULL time would not be material as there is not much or any
> sorting to do then what does the CLUSTER gain you?


Not much.  Now they're just "slightly more ordered" instead of "slightly
less ordered" for little if any extra effort.


> 2) What evidence is there that the records where still in PK order just
> because you deleted based on CREATED_ON? I understand the correlation
> between CREATED_ON and the PK just not sure why that would necessarily
> translate to an on disk order by PK?
>

1. Records are appended to tables in INSERT order, and INSERT order is
highly correlated to synthetic PK, by the nature of sequences.
2. My original email showed that CLUSTER took just as long as VACUUM FULL.
That means not many records had to be sorted, because... the on-disk order
was strongly correlated to PK and CREATED_ON.

Will that happen *every time* in *every circumstance* in *every database*?
No, and I never said it would.  But it does in *my *database in *this *
application.

Re: CLUSTER vs. VACUUM FULL

[Ron Johnson]

On Mon, Apr 22, 2024 at 12:29 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

>
>
> On Mon, Apr 22, 2024, 08:37 Ron Johnson  wrote:
>
>> On Mon, Apr 22, 2024 at 10:25 AM Tom Lane  wrote:
>>
>>> Marcos Pegoraro  writes:
>>> > But wouldn't it be good that VACUUM FULL uses that index defined by
>>> > Cluster, if it exists ?
>>>
>>> No ... what would be the difference then?
>>>
>>
>> What the VACUUM docs "should" do, it seems, is suggest CLUSTER on the PK,
>> if the PK is a sequence (whether that be an actual sequence, or a timestamp
>> or something else that grows monotonically).
>>
>> That's because the data is already roughly in PK order.
>>
>
> If things are bad enough to require a vacuum full that doesn't seem like a
> good assumption.
>

Sure it does.

For example, I just deleted the oldest half of the records in 30 tables.
Tables who's CREATED_ON timestamp value strongly correlates to the
synthetic PK sequence values.

Thus, the remaining records were still mostly in PK order.  CLUSTERs on the
PK values would have taken just about as much time as the VACUUM FULL
statements which I *did* run.

Re: CLUSTER vs. VACUUM FULL

[Ron Johnson]

On Mon, Apr 22, 2024 at 10:25 AM Tom Lane  wrote:

> Marcos Pegoraro  writes:
> > But wouldn't it be good that VACUUM FULL uses that index defined by
> > Cluster, if it exists ?
>
> No ... what would be the difference then?
>

What the VACUUM docs "should" do, it seems, is suggest CLUSTER on the PK,
if the PK is a sequence (whether that be an actual sequence, or a timestamp
or something else that grows monotonically).

That's because the data is already roughly in PK order.

Re: CLUSTER vs. VACUUM FULL

[Ron Johnson]

On Sun, Apr 21, 2024 at 9:35 PM David Rowley  wrote:

> On Mon, 22 Apr 2024 at 12:16, Ron Johnson  wrote:
> >
> > On Sun, Apr 21, 2024 at 6:45 PM Tom Lane  wrote:
> >>
> >> Ron Johnson  writes:
> >> > Why is VACUUM FULL recommended for compressing a table, when CLUSTER
> does
> >> > the same thing (similarly doubling disk space), and apparently runs
> just as
> >> > fast?
> >>
> >> CLUSTER makes the additional effort to sort the data per the ordering
> >> of the specified index.  I'm surprised that's not noticeable in your
> >> test case.
> >
> > Clustering on a completely different index  was also 44 seconds.
>
> Both VACUUM FULL and CLUSTER go through a very similar code path. Both
> use cluster_rel().  VACUUM FULL just won't make use of an existing
> index to provide presorted input or perform a sort, whereas CLUSTER
> will attempt to choose the cheapest out of these two to get sorted
> results.
>
> If the timing for each is similar, it just means that using an index
> scan or sorting isn't very expensive compared to the other work that's
> being done.  Both CLUSTER and VACUUM FULL require reading every heap
> page and writing out new pages into a new heap and maintaining  all
> indexes on the new heap. That's quite an effort.
>

My original CLUSTER command didn't have to change the order of the data
very much, thus, the sort didn't have to do much work.

CLUSTER on a different index was indeed much slower than VACUUM FULL.

Re: CLUSTER vs. VACUUM FULL

[Ron Johnson]

On Sun, Apr 21, 2024 at 6:45 PM Tom Lane  wrote:

> Ron Johnson  writes:
> > Why is VACUUM FULL recommended for compressing a table, when CLUSTER does
> > the same thing (similarly doubling disk space), and apparently runs just
> as
> > fast?
>
> CLUSTER makes the additional effort to sort the data per the ordering
> of the specified index.  I'm surprised that's not noticeable in your
> test case.
>

Clustering on a completely different index  was also 44 seconds.

Re: CLUSTER vs. VACUUM FULL

[Ron Johnson]

On Sun, Apr 21, 2024 at 6:45 PM Tom Lane  wrote:

> Ron Johnson  writes:
> > Why is VACUUM FULL recommended for compressing a table, when CLUSTER does
> > the same thing (similarly doubling disk space), and apparently runs just
> as
> > fast?
>
> CLUSTER makes the additional effort to sort the data per the ordering
> of the specified index.  I'm surprised that's not noticeable in your
> test case.
>

It's in a freshly restored database.  However, regular deletions of old
records, and normal vacuums would have led me to expect that the "fresh"
public.log would have been in relatively random order.

CLUSTER vs. VACUUM FULL

[Ron Johnson]

PG 14.11 on RHEL8

Why is VACUUM FULL recommended for compressing a table, when CLUSTER does
the same thing (similarly doubling disk space), and apparently runs just as
fast?

My tests:
Table: CDSLBXW.public.log

Time 1  Time 2  Time 3
secssecssecs
VACUUM FULL 44.239.342.3
CLUSTER 41.738.941.3

CDSLBXW=# \d public.log
 Table "public.log"
 Column  |Type | Collation | Nullable |
  Default
-+-+---+--+-
 log_id  | bigint  |   | not null |
nextval('log_log_id_seq'::regclass)
 level   | numeric(10,0)   |   |  |
 source  | character varying(255)  |   |  |
 username| character varying(255)  |   |  |
 user_login_id   | character varying(255)  |   |  |
 user_ip_address | character varying(255)  |   |  |
 computer| character varying(255)  |   |  |
 search_tag  | character varying(4000) |   |  |
 log_group_id| integer |   |  |
 created_on  | timestamp without time zone |   | not null |
 created_by  | integer |   |  |
 xml_detail  | bytea   |   |  |
Indexes:
"pk_log" PRIMARY KEY, btree (log_id)
"idx_log_attr_source" btree (source)
"idx_log_level" btree (level)
"idx_log_search_tag" btree (search_tag)

CDSLBXW=# SELECT COUNT(*) FROM public.log;
  count
-
 6774664
(1 row)

CDSLBXW=# \timing
CDSLBXW=# vacuum (full) public.log;
VACUUM
Time: 44190.799 ms (00:44.191)
CDSLBXW=#
CDSLBXW=# vacuum (full) public.log;
VACUUM
Time: 39316.824 ms (00:39.317)
CDSLBXW=# vacuum (full) public.log;
VACUUM
Time: 42336.268 ms (00:42.336)

CDSLBXW=# CLUSTER public.log USING pk_log;
CLUSTER
Time: 41722.335 ms (00:41.722)
CDSLBXW=#
CDSLBXW=# CLUSTER public.log USING pk_log;
CLUSTER
Time: 38915.128 ms (00:38.915)
CDSLBXW=#
CDSLBXW=# CLUSTER public.log USING pk_log;
CLUSTER
Time: 41342.651 ms (00:41.343)