Best options for tracking history of data?

[Guyren Howe]

I’m interested in tracking our data over time.

The basic requirement is for debugging and disaster recovery, but I’m passing 
familiar enough with the issue that I can imagine being able to set things up 
so I get that but much more.

I’ll need something that I can use on eg AWS, probably RDS, although I’ll 
listen to an argument for eg Timescale DB.

I want something easy to set up, rugged and with minimal changes to client 
code. My current needs are simple, but I’ll take “extra features basically for 
free” if I can.

I can’t find a good general discussion of options for this. If I missed one, 
I’ll take a link and offer a thank you.

Otherwise, this seems a useful topic to discuss.

Thanks in advance.

Re: ZFS filesystem - supported ?

[Ron]

On 10/26/21 7:55 PM, Mladen Gogala wrote:

On 10/26/21 20:50, Imre Samu wrote:

> Phoronix has some very useful benchmarks:
> 
https://www.phoronix.com/scan.php?page=news_item=Linux-5.14-File-Systems 


> Ext4 is much better than XFS with SQLite tests and almost equal with
> MariaDB test. PostgreSQL is a relational database (let's forget the
> object part for now) and the IO patterns will be similar to SQLite and
> MariaDB.

there is a link from the Phoronix page to the full OpenBenchmarking.org 
result file
and multiple PostgreSQL 13 pgbench results included: 
https://openbenchmarking.org/result/2108260-PTS-SSDS978300=D=postgres 


( XFS, F2FS, EXT4, BTRFS )

Regards,
 Imre


Wow! That is really interesting. Here is the gist of it:

XFS is the clear winner. It also answers the question about BTRFS. Thanks 
Imre!





XFS is 1.08% faster than ext4.  That's very close to being statistical noise.

--
Angular momentum makes the world go 'round.

Re: Need help understanding error message [RESOLVED]

[Ron]

On 10/26/21 1:17 PM, Rich Shepard wrote:

On Tue, 26 Oct 2021, Rob Sargent wrote:


Aren’t lines 3 and 6 duplicates?


Ah, shoot! The second was supposed to be 16 and that's how I saw it when I
scanned the list.


The second thing I deeply learned about computer programming: it's almost 
certainly my fault.


--
Angular momentum makes the world go 'round.

Re: Determining if a table really changed in a trigger

[Michael Lewis]

If you end up with no rows changing from an insert or delete, something
seems awry. Unless you mean 0 rows affected. Do after statement triggers
still execute? I suppose they very well might.

Would the statement even execute if no rows get updated and that is
prevented with before update? I would assume null is being returned rather
than old if the trigger finds the row to be identical.

Re: ZFS filesystem - supported ?

[Benedict Holland]

Honestly, I think if you are at the point I performance where you care
about a file system then you are in the upper 5% or higher. I am trying to
get my users to not do 100k individual inserts in django rather than 1
insert using sql. As for backups, I don't care how long they take. I ha e
separate computers that run backup and 15m interval Wal archives. Is it the
best? No. But this is a mailing list to get help with postgres and
specifically not a way to get production level support from a free service.
We try and get the message out that postgres is amazing. If your OS
defaults to ext4 then I assume linux experts did the tests to show that
ext4 is fine. If you have specific use cases for reported benchmarking then
that would be an interesting read but apart from that, this feels like such
a nuanced and detailed question that the op should pay for the postgres
support and test their systems and then report the results.

But that might just be me. Maybe the debate between zfs, xfs, ext4 or
others is a good discussion to have. I would love to hear about what bench
marks provide for a variety of use cases.

On Tue, Oct 26, 2021, 8:56 PM Mladen Gogala  wrote:

>
> On 10/26/21 20:50, Imre Samu wrote:
>
> > Phoronix has some very useful benchmarks:
> >
> https://www.phoronix.com/scan.php?page=news_item=Linux-5.14-File-Systems
> > Ext4 is much better than XFS with SQLite tests and almost equal with
> > MariaDB test. PostgreSQL is a relational database (let's forget the
> > object part for now) and the IO patterns will be similar to SQLite and
> > MariaDB.
>
> there is a link from the Phoronix page to the full OpenBenchmarking.org
> result file
> and multiple PostgreSQL 13 pgbench results included:
> https://openbenchmarking.org/result/2108260-PTS-SSDS978300=D=postgres
> ( XFS, F2FS, EXT4, BTRFS )
>
> Regards,
>  Imre
>
> Wow! That is really interesting. Here is the gist of it:
>
> XFS is the clear winner. It also answers the question about BTRFS. Thanks
> Imre!
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>

Re: ZFS filesystem - supported ?

[Mladen Gogala]

On 10/26/21 20:50, Imre Samu wrote:

> Phoronix has some very useful benchmarks:
> 
https://www.phoronix.com/scan.php?page=news_item=Linux-5.14-File-Systems 


> Ext4 is much better than XFS with SQLite tests and almost equal with
> MariaDB test. PostgreSQL is a relational database (let's forget the
> object part for now) and the IO patterns will be similar to SQLite and
> MariaDB.

there is a link from the Phoronix page to the full 
OpenBenchmarking.org result file
and multiple PostgreSQL 13 pgbench results included: 
https://openbenchmarking.org/result/2108260-PTS-SSDS978300=D=postgres 


( XFS, F2FS, EXT4, BTRFS )

Regards,
 Imre


Wow! That is really interesting. Here is the gist of it:

XFS is the clear winner. It also answers the question about BTRFS. 
Thanks Imre!



--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: ZFS filesystem - supported ?

[Imre Samu]

> Phoronix has some very useful benchmarks:
>
https://www.phoronix.com/scan.php?page=news_item=Linux-5.14-File-Systems
> Ext4 is much better than XFS with SQLite tests and almost equal with
> MariaDB test. PostgreSQL is a relational database (let's forget the
> object part for now) and the IO patterns will be similar to SQLite and
> MariaDB.

there is a link from the Phoronix page to the full OpenBenchmarking.org
result file
and multiple PostgreSQL 13 pgbench results included:
https://openbenchmarking.org/result/2108260-PTS-SSDS978300=D=postgres
( XFS, F2FS, EXT4, BTRFS )

Regards,
 Imre


Mladen Gogala  ezt írta (időpont: 2021. okt. 27.,
Sze, 1:42):

>
> On 10/26/21 05:35, Laura Smith wrote:
> > Curious, when it comes to "traditional" filesystems, why ext4 and not
> xfs ? AFAIK the legacy issues associated with xfs are long gone ?
>
> XFS is not being very actively developed any more. Ext4 is being
> actively developed and it has some features to help with SSD space
> allocation. Phoronix has some very useful benchmarks:
>
> https://www.phoronix.com/scan.php?page=news_item=Linux-5.14-File-Systems
>
> Ext4 is much better than XFS with SQLite tests and almost equal with
> MariaDB test. PostgreSQL is a relational database (let's forget the
> object part for now) and the IO patterns will be similar to SQLite and
> MariaDB. That benchmark is brand new, done on the kernel 5.14. Of
> course, the only guarantee is doing your own benchmark, with your own
> application.
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
> https://dbwhisperer.wordpress.com
>
>
>
>

Re: ZFS filesystem - supported ?

[Mladen Gogala]

On 10/26/21 20:12, E-BLOKOS wrote:
RedHat and Oracle are mostly maintaining XFS updates, and I didn't see 
anything saying it's not mainained actively,

especially when they offering many solutions with XFS as default


Oh, they are maintaining it, all right, but they're not developing it. 
XFS is still the file system for rotational disks with plates, reading 
heads, tracks and sectors, the type we were taught about in school. 
Allocation policy for SSD devices is completely different as are 
physical characteristics. Ext4 is being adjusted to ever more popular 
NVME devices. XFS is not. In the long run, my money is on Ext4 or its 
successors. Here is another useful benchmark:


https://www.percona.com/blog/2012/03/15/ext4-vs-xfs-on-ssd/

This one is a bit old, but it shows clear advantage for Ext4 in async 
mode. I maybe wrong. Neither of the two file systems has gained any new 
features since 2012. The future may lay in F2FS ("Flash Friendly File 
System") which is very new but has a ton of optimizations for SSD 
devices. Personally, I usually use XFS for my databases but I am testing 
Ext4 with Oracle 21c on Fedora. So far, I don't have any results to 
report. The difference is imperceptible. I am primarily an Oracle DBA 
and I am testing with Oracle. That doesn't necessarily have to be 
pertinent for Postgres.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: Determining if a table really changed in a trigger

[Mitar]

Hi!

On Wed, Oct 27, 2021 at 1:16 AM Mark Dilger
 wrote:
> If Mitar finds that suppress_redundant_updates_trigger is sufficient, that 
> may be a simpler solution.  Thanks for mentioning it.
>
> The suppress_redundant_updates_trigger uses memcmp on the old and new rows.  
> I don't know if memcmp will be sufficient in this case, since json can be 
> binary unequal and yet turn out to be equal once cast to jsonb.  I was using 
> the rule and casting the json column to jsonb before comparing for equality.

Very interesting, I didn't know about that trigger. Memcmp is OK for
my use case. This is why I am considering *= as well.

I am guessing that if I am already doing a row comparison on every
UPDATE before my AFTER trigger so that I do not run the trigger (the
rule-based approach suggested by Mark), it is probably better to do
the row comparison as a BEFORE trigger which prevents the UPDATE from
even happening. I already pay for the row comparison so at least I
could prevent the disk write as well. Do I understand that correctly?

So the only remaining question is how to prevent my statement trigger
from running if no rows end up being changed by INSERT/UPDATE/DELETE
without having to use REFERENCING.


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Re: ZFS filesystem - supported ?

[E-BLOKOS]

On 10/26/2021 4:42 PM, Mladen Gogala wrote:


On 10/26/21 05:35, Laura Smith wrote:
Curious, when it comes to "traditional" filesystems, why ext4 and not 
xfs ? AFAIK the legacy issues associated with xfs are long gone ?


XFS is not being very actively developed any more. Ext4 is being 
actively developed and it has some features to help with SSD space 
allocation. Phoronix has some very useful benchmarks:


https://www.phoronix.com/scan.php?page=news_item=Linux-5.14-File-Systems 



Ext4 is much better than XFS with SQLite tests and almost equal with 
MariaDB test. PostgreSQL is a relational database (let's forget the 
object part for now) and the IO patterns will be similar to SQLite and 
MariaDB. That benchmark is brand new, done on the kernel 5.14. Of 
course, the only guarantee is doing your own benchmark, with your own 
application.


RedHat and Oracle are mostly maintaining XFS updates, and I didn't see 
anything saying it's not mainained actively,

especially when they offering many solutions with XFS as default

--
E-BLOKOS

Re: Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND

[Michael Lewis]

>
>  Planning Time: 10.351 ms
>
>  Execution Time: 0.283 ms
>

 Nearly all of the time is in planning... What is your
default_statistics_target?

Re: ZFS filesystem - supported ?

[Mladen Gogala]

On 10/26/21 05:35, Laura Smith wrote:

Curious, when it comes to "traditional" filesystems, why ext4 and not xfs ? 
AFAIK the legacy issues associated with xfs are long gone ?


XFS is not being very actively developed any more. Ext4 is being 
actively developed and it has some features to help with SSD space 
allocation. Phoronix has some very useful benchmarks:


https://www.phoronix.com/scan.php?page=news_item=Linux-5.14-File-Systems

Ext4 is much better than XFS with SQLite tests and almost equal with 
MariaDB test. PostgreSQL is a relational database (let's forget the 
object part for now) and the IO patterns will be similar to SQLite and 
MariaDB. That benchmark is brand new, done on the kernel 5.14. Of 
course, the only guarantee is doing your own benchmark, with your own 
application.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: How to copy rows into same table efficiently

[Michael Lewis]

On Tue, Oct 26, 2021 at 1:07 AM Arun Suresh 
wrote:

> Current approach taken is to build a query like below:
> INSERT INTO mytable (col1, col2, col3, col4) SELECT col1, 'XYZ', col3,
> col4 FROM mytable WHERE col2 = 'ABCD'
>
> Is there a better way to do this?
> There could be other tables with foreign key reference, would a simple
> ordering of the copy based on table relationship suffice?
>

The other concern I would have is having a rolling view of the data in
default read committed mode. If you copy data from a primary table (with
other tables having fkey to that table coming later in the process), then
you may need to use some created_on < script_start_time, else when you try
to insert dependent rows where the record in the primary table did not
exist yet when you started your process and your rows in dependent table
want to reference that primary table row... error.

Or, use REPEATABLE READ, but WAL buildup may be a concern.

Re: Determining if a table really changed in a trigger

[Mark Dilger]

> On Oct 26, 2021, at 4:01 PM, Michael Lewis  wrote:
> 
> Does this perform differently from suppress_redundant_updates_trigger?
> 
> https://www.postgresql.org/docs/current/functions-trigger.html

If Mitar finds that suppress_redundant_updates_trigger is sufficient, that may 
be a simpler solution.  Thanks for mentioning it.

The suppress_redundant_updates_trigger uses memcmp on the old and new rows.  I 
don't know if memcmp will be sufficient in this case, since json can be binary 
unequal and yet turn out to be equal once cast to jsonb.  I was using the rule 
and casting the json column to jsonb before comparing for equality.
 
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Determining if a table really changed in a trigger

[Michael Lewis]

Does this perform differently from suppress_redundant_updates_trigger?

https://www.postgresql.org/docs/current/functions-trigger.html

Re: Determining if a table really changed in a trigger

[Mark Dilger]

> On Oct 26, 2021, at 3:39 PM, Mitar  wrote:
> 
> On Tue, Oct 26, 2021 at 10:55 PM Mark Dilger
>  wrote:
>> Note that there is a performance cost to storing the old rows using the 
>> REFERENCING clause of the trigger
> 
> Yea, by moving the trivial update check to a rule, I need REFERENCING
> only to see if there were any changes at all. This seems a bit
> excessive. Is there a way to check if any rows have been affected by
> an UPDATE inside a per statement trigger without using REFERENCING?

I felt the same way about it, but after glancing quickly through the code and 
docs nothing jumped out.  The information is clearly available, as it gets 
returned at the end of the UPDATE statement in the "UPDATE 0" OR "UPDATE 3", 
but I don't see how to access that from the trigger.  I might have to submit a 
patch for that if nobody else knows a way to get it.  (Hopefully somebody will 
respond with the answer...?)

> I also notice that you check if a table has any rows with:
> 
> SELECT true INTO have_rows FROM old_values LIMIT 1;
> IF have_rows THEN ...
> 
> Is this just a question of style or is this a better approach than my:
> 
> PERFORM * FROM old_values LIMIT 1;
> IF FOUND THEN ...

There is no reason to prefer my spelling of that over yours.  I didn't put much 
thought into it, but rather just wrote it the first way that occurred to me.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Determining if a table really changed in a trigger

[Mitar]

Hi!

On Tue, Oct 26, 2021 at 10:55 PM Mark Dilger
 wrote:
> The trigger "my_table_trig" in the example is a per row trigger, but it 
> exists only to demonstrate that the rule has filtered out the appropriate 
> rows.  You can use the rule "my_table_rule" as written and a per statement 
> trigger, as here:

Oh, very interesting. I thought that this is not possible because WHEN
condition on triggers does not have NEW and OLD. But this is a very
cool way to combine rules with triggers, where a rule can still
operate by row.

Thank you for sharing this!

> Note that there is a performance cost to storing the old rows using the 
> REFERENCING clause of the trigger

Yea, by moving the trivial update check to a rule, I need REFERENCING
only to see if there were any changes at all. This seems a bit
excessive. Is there a way to check if any rows have been affected by
an UPDATE inside a per statement trigger without using REFERENCING?

> Note that I used equality and inequality rather than IS DISTINCT FROM and IS 
> NOT DISTINCT FROM in the design, but you should think about how NULL values 
> (old, new, or both) will behave in the solution you choose.

I have just now tested the following rule:

CREATE RULE filter_trivial_updates AS ON UPDATE TO my_table WHERE NEW
*= OLD DO INSTEAD NOTHING;

and it looks like it works well. It sidesteps the issue around
equality operator for type json and also just compares nulls as just
another value (which I would like). Not sure how it is performance
wise in comparison with listing all columns and using the regular
equality operator.

I also notice that you check if a table has any rows with:

SELECT true INTO have_rows FROM old_values LIMIT 1;
IF have_rows THEN ...

Is this just a question of style or is this a better approach than my:

PERFORM * FROM old_values LIMIT 1;
IF FOUND THEN ...


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Re: ZFS filesystem - supported ?

[Lucas]

> On 27/10/2021, at 8:35 AM, Stephen Frost  wrote:
> 
> Greetings,
> 
> * Lucas (r...@sud0.nz) wrote:
>> On 26/10/2021, at 6:13 AM, Stephen Frost  wrote:
>>> * Mladen Gogala (gogala.mla...@gmail.com) wrote:
 On 10/23/21 23:12, Lucas wrote:
> This has proven to work very well for me. I had to restore a few backups
> already and it always worked. The bad part is that I need to stop the
> database before performing the Snapshot, for data integrity, so that means
> that I have a hot-standby server only for these snapshots.
> Lucas
 
 Actually, you don't need to stop the database. You need to execute
 pg_start_backup() before taking a snapshot and then pg_stop_backup() when
 the snapshot is done. You will need to recover the database when you finish
 the restore but you will not lose any data. I know that pg_begin_backup()
 and pg_stop_backup() are deprecated but since PostgreSQL doesn't have any
 API for storage or file system snapshots, that's the only thing that can
 help you use storage snapshots as backups. To my knowledge,the only 
 database
 that does have API for storage snapshots is DB2. The API is called 
 "Advanced
 Copy Services" or ACS. It's documented here:
 
 https://www.ibm.com/docs/en/db2/11.1?topic=recovery-db2-advanced-copy-services-acs
 
 For Postgres, the old begin/stop backup functions should be sufficient.
>>> 
>>> No, it's not- you must also be sure to archive any WAL that's generated
>>> between the pg_start_backup and pg_stop_backup and then to be sure and
>>> add into the snapshot the appropriate signal files or recovery.conf,
>>> depending on PG version, to indicate that you're restoring from a backup
>>> and make sure that the WAL is made available via restore_command.
>>> 
>>> Just doing stat/stop backup is *not* enough and you run the risk of
>>> having an invalid backup or corruption when you restore.
>>> 
>>> If the entire system is on a single volume then you could possibly just
>>> take a snapshot of it (without any start/stop backup stuff) but it's
>>> very risky to do that and then try to do PITR with it because we don't
>>> know where consistency is reached in such a case (we *must* play all the
>>> way through to the end of the WAL which existed at the time of the
>>> snapshot in order to reach consistency).
>>> 
>>> In the end though, really, it's much, much, much better to use a proper
>>> backup and archiving tool that's written specifically for PG than to try
>>> and roll your own, using snapshots or not.
>> 
>> When I create a snapshot, the script gets the latest WAL file applied from 
>> [1] and adds that information to the Snapshot Tags in AWS. I then use that 
>> information in the future when restoring the snapshot. The script will read 
>> the tag and it will download 50 WAL Files before that and all the WAL files 
>> after that required.
>> The WAL files are being backed up to S3.
>> 
>> I had to restore the database to a PITR state many times, and it always 
>> worked very well.
>> 
>> I also create slaves using the snapshot method. So, I don’t mind having to 
>> stop/start the Database for the snapshot process, as it’s proven to work 
>> fine for the last 5 years.
> 
> I have to say that the process used here isn't terribly clear to me (you
> cleanly shut down the database ... and also copy the WAL files?), so I
> don't really want to comment on if it's actually correct or not because
> I can't say one way or the other if it is or isn't.
> 
> I do want to again stress that I don't recommend writing your own tools
> for doing backup/restore/PITR and I would caution people against people
> trying to use this approach you've suggested.  Also, being able to tell
> when such a process *doesn't* work is non-trivial (look at how long it
> took us to discover the issues around fsync..), so saying that it seems
> to have worked for a long time for you isn't really enough to make me
> feel comfortable with it.
> 
> Thanks,
> 
> Stephen

I think it is worth mentioning that we’re using PG 9.2, so I don’t benefit from 
features like logical replication to help with backups and such.
(Yes, I know it’s old and that I should upgrade ASAP. We’re working on it)

The snapshots are done this way:
1. Grab the latest applied WAL File for further references, stores that in a 
variable in Bash
2. Stop the Postgres process
3. Check it is stopped
4. Start the Block level EBS Snapshot process
5. Applied tags to the snapshot, such as the WAL file from Step #1
6. Wait for snapshot to complete, querying the AWS API for that
7. Start PG
8. Check it is started
9. Check it is replicating from master

The entire process takes around 10 minutes to complete.

I copy the WAL files to S3 in case I ever need to restore an old snapshot (we 
keep snapshots for 30 days), which won’t have the WAL files in the volume 
itself. Also for safety reasons, after reading this 

Re: Determining if a table really changed in a trigger

[Mark Dilger]

> On Oct 26, 2021, at 1:34 PM, Mitar  wrote:
> 
> Hi!
> 
> On Tue, Oct 26, 2021 at 10:17 PM Mark Dilger
>  wrote:
>> I can't tell from your post if you want the trivial update to be performed, 
>> but if not, would it work to filter trivial updates as:
> 
> No, I want to skip trivial updates (those which have not changed
> anything). But my trigger is per statement, not per row. So I do not
> think your approach works there? So this is why I am then making a
> more complicated check inside the trigger itself.

The trigger "my_table_trig" in the example is a per row trigger, but it exists 
only to demonstrate that the rule has filtered out the appropriate rows.  You 
can use the rule "my_table_rule" as written and a per statement trigger, as 
here:

rules=# create table my_table (i integer, j json);
CREATE TABLE
rules=# insert into my_table
rules-#   select gs::integer, '{"key":1}'::json
rules-# from generate_series(1,3) gs;
INSERT 0 3
rules=# create function my_table_func () returns trigger as $$
rules$# declare
rules$#   have_rows boolean;
rules$# begin
rules$#   select true into have_rows from old_values limit 1;
rules$#   if have_rows then
rules$# raise warning 'rows have changed';
rules$#   else
rules$# raise warning 'no rows changed';
rules$#   end if;
rules$#   return null;
rules$# end
rules$# $$ language plpgsql;
CREATE FUNCTION
rules=# create trigger my_table_trig after update on my_table
rules-#   referencing old table as old_values
rules-#   for each statement
rules-#   execute function my_table_func();
CREATE TRIGGER
rules=# update my_table set j = '{"key":2}'::jsonb;
2021-10-26 13:51:58.139 PDT [34352] WARNING:  rows have changed
2021-10-26 13:51:58.139 PDT [34352] CONTEXT:  PL/pgSQL function my_table_func() 
line 7 at RAISE
WARNING:  rows have changed
UPDATE 3
rules=# create rule filter_trivial_updates as on update to my_table
rules-#   where new.i = old.i
rules-# and new.j::jsonb = old.j::jsonb
rules-#   do instead nothing;
CREATE RULE
rules=# update my_table set j = '{"key":2}'::jsonb;
2021-10-26 13:51:58.143 PDT [34352] WARNING:  no rows changed
2021-10-26 13:51:58.143 PDT [34352] CONTEXT:  PL/pgSQL function my_table_func() 
line 9 at RAISE
WARNING:  no rows changed
UPDATE 0
rules=# update my_table set j = '{"key":3}'::jsonb;
2021-10-26 13:51:58.143 PDT [34352] WARNING:  rows have changed
2021-10-26 13:51:58.143 PDT [34352] CONTEXT:  PL/pgSQL function my_table_func() 
line 7 at RAISE
WARNING:  rows have changed
UPDATE 3

Note that there is a performance cost to storing the old rows using the 
REFERENCING clause of the trigger, so you'll want to think about all your 
various options and decide between them.  I am not in a position to make 
performance recommendations for your schema.  However, if updates tend to be 
target at small sets of rows, and if the rule is used to further filter out 
trivial updates, this might be cheap.

Note that I used equality and inequality rather than IS DISTINCT FROM and IS 
NOT DISTINCT FROM in the design, but you should think about how NULL values 
(old, new, or both) will behave in the solution you choose.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Determining if a table really changed in a trigger

[Mitar]

Hi!

On Tue, Oct 26, 2021 at 10:17 PM Mark Dilger
 wrote:
> I can't tell from your post if you want the trivial update to be performed, 
> but if not, would it work to filter trivial updates as:

No, I want to skip trivial updates (those which have not changed
anything). But my trigger is per statement, not per row. So I do not
think your approach works there? So this is why I am then making a
more complicated check inside the trigger itself.


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Re: Determining if a table really changed in a trigger

[Mark Dilger]

> On Oct 26, 2021, at 12:05 AM, Mitar  wrote:
> 
> Hi!
> 
> I have a trigger like:
> 
> CREATE TRIGGER update_trigger AFTER UPDATE ON my_table REFERENCING NEW
> TABLE AS new_table OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE
> FUNCTION trigger_function;
> 
> I would like to test inside trigger_function if the table really
> changed. I have tried to do:
> 
> PERFORM * FROM ((TABLE old_table EXCEPT TABLE new_table) UNION ALL
> (TABLE new_table EXCEPT TABLE old_table)) AS differences LIMIT 1;
> IF FOUND THEN
>  ... changed ...
> END IF;
> 
> But this fails if the table contains a JSON field with the error:
> 
> could not identify an equality operator for type json
> 
> The table has an unique index column, if that helps.

I can't tell from your post if you want the trivial update to be performed, but 
if not, would it work to filter trivial updates as:

CREATE RULE filter_trivial_updates AS ON UPDATE TO my_table WHERE new.i = old.i 
AND new.j = old.j AND ... DO INSTEAD NOTHING;

You could replace the i, j, ... above with whichever columns you have, and 
specify the casts and equality operators you want for the json column (such as 
a cast to jsonb and equality.)

The advantage here, if you do it right, is that the trigger doesn't have to 
check whether the row has changed, because the trigger will only fire when a 
change has occurred.  You might try it and compare the performance against 
other solutions.  The general idea is shown here:

rules=# create table my_table (i integer, j json);
CREATE TABLE
rules=# insert into my_table
rules-#   select gs::integer, '{"key":1}'::json
rules-# from generate_series(1,3) gs;
INSERT 0 3
rules=# create function my_table_func () returns trigger as $$
rules$# begin
rules$#   raise warning '[old.i=%, old.j=%] => [new.i=%, new.j=%]',
rules$# old.i, old.j, new.i, new.j;
rules$#   return new;
rules$# end
rules$# $$ language plpgsql;
CREATE FUNCTION
rules=# create trigger my_table_trig before update on my_table
rules-#   for each row execute function my_table_func();
CREATE TRIGGER
rules=# update my_table set j = '{"key":2}'::jsonb;
WARNING:  [old.i=1, old.j={"key":1}] => [new.i=1, new.j={"key": 2}]
WARNING:  [old.i=2, old.j={"key":1}] => [new.i=2, new.j={"key": 2}]
WARNING:  [old.i=3, old.j={"key":1}] => [new.i=3, new.j={"key": 2}]
UPDATE 3
rules=# create rule filter_trivial_updates as on update to my_table
rules-#   where new.i = old.i
rules-# and new.j::jsonb = old.j::jsonb
rules-#   do instead nothing;
CREATE RULE
rules=# update my_table set j = '{"key":2}'::jsonb;
UPDATE 0
rules=# update my_table set j = '{"key":3}'::jsonb;
WARNING:  [old.i=1, old.j={"key": 2}] => [new.i=1, new.j={"key": 3}]
WARNING:  [old.i=2, old.j={"key": 2}] => [new.i=2, new.j={"key": 3}]
WARNING:  [old.i=3, old.j={"key": 2}] => [new.i=3, new.j={"key": 3}]
UPDATE 3

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: ZFS filesystem - supported ?

[Bruce Momjian]

On Mon, Oct 25, 2021 at 07:53:02PM +0200, Chris Travers wrote:
> On the whole ZFS on spinning disks is going to have some performance... rough
> corners.  And it is a lot harder to reason about a lot of things including
> capacity and performance when you are doing copy on write on both the db and 
> FS
> level, and have compression in the picture.  And there are other areas of
> complexity, such as how you handle partial page writes.
> 
> On the whole I think for small dbs it might perform well enough.  On large or
> high velocity dbs I think you will have more problems than expected. 
> 
> Having worked with PostgreSQL on ZFS I wouldn't generally recommend it as a
> general tool.

I know ZFS has a lot of features/options, and some of those can cause
corruption, so if you modify ZFS options, you need to be sure they don't
affect Postgres reliability.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

Re: [Major version upgrade] pg_upgrade fails despite passing check mode

[Bruce Momjian]

On Mon, Oct 25, 2021 at 07:52:13AM +0900, Abhishek Bhola wrote:
> Thanks, let me try that out.
> 
> But is there a way to know for sure if pg_upgrade will have any such problems?
> Other than FDW's owner, any other things I should check before upgrading other
> clusters?

No.  pg_upgrade's check mode checks as much as it reasonable can, but it
doesn't try restoring the database schema, for performance reasons, and
that is where the failure is happening.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

Re: ZFS filesystem - supported ?

[Stephen Frost]

Greetings,

* Lucas (r...@sud0.nz) wrote:
> On 26/10/2021, at 6:13 AM, Stephen Frost  wrote:
> > * Mladen Gogala (gogala.mla...@gmail.com) wrote:
> >> On 10/23/21 23:12, Lucas wrote:
> >>> This has proven to work very well for me. I had to restore a few backups
> >>> already and it always worked. The bad part is that I need to stop the
> >>> database before performing the Snapshot, for data integrity, so that means
> >>> that I have a hot-standby server only for these snapshots.
> >>> Lucas
> >> 
> >> Actually, you don't need to stop the database. You need to execute
> >> pg_start_backup() before taking a snapshot and then pg_stop_backup() when
> >> the snapshot is done. You will need to recover the database when you finish
> >> the restore but you will not lose any data. I know that pg_begin_backup()
> >> and pg_stop_backup() are deprecated but since PostgreSQL doesn't have any
> >> API for storage or file system snapshots, that's the only thing that can
> >> help you use storage snapshots as backups. To my knowledge,the only 
> >> database
> >> that does have API for storage snapshots is DB2. The API is called 
> >> "Advanced
> >> Copy Services" or ACS. It's documented here:
> >> 
> >> https://www.ibm.com/docs/en/db2/11.1?topic=recovery-db2-advanced-copy-services-acs
> >> 
> >> For Postgres, the old begin/stop backup functions should be sufficient.
> > 
> > No, it's not- you must also be sure to archive any WAL that's generated
> > between the pg_start_backup and pg_stop_backup and then to be sure and
> > add into the snapshot the appropriate signal files or recovery.conf,
> > depending on PG version, to indicate that you're restoring from a backup
> > and make sure that the WAL is made available via restore_command.
> > 
> > Just doing stat/stop backup is *not* enough and you run the risk of
> > having an invalid backup or corruption when you restore.
> > 
> > If the entire system is on a single volume then you could possibly just
> > take a snapshot of it (without any start/stop backup stuff) but it's
> > very risky to do that and then try to do PITR with it because we don't
> > know where consistency is reached in such a case (we *must* play all the
> > way through to the end of the WAL which existed at the time of the
> > snapshot in order to reach consistency).
> > 
> > In the end though, really, it's much, much, much better to use a proper
> > backup and archiving tool that's written specifically for PG than to try
> > and roll your own, using snapshots or not.
> 
> When I create a snapshot, the script gets the latest WAL file applied from 
> [1] and adds that information to the Snapshot Tags in AWS. I then use that 
> information in the future when restoring the snapshot. The script will read 
> the tag and it will download 50 WAL Files before that and all the WAL files 
> after that required.
> The WAL files are being backed up to S3.
> 
> I had to restore the database to a PITR state many times, and it always 
> worked very well.
> 
> I also create slaves using the snapshot method. So, I don’t mind having to 
> stop/start the Database for the snapshot process, as it’s proven to work fine 
> for the last 5 years.

I have to say that the process used here isn't terribly clear to me (you
cleanly shut down the database ... and also copy the WAL files?), so I
don't really want to comment on if it's actually correct or not because
I can't say one way or the other if it is or isn't.

I do want to again stress that I don't recommend writing your own tools
for doing backup/restore/PITR and I would caution people against people
trying to use this approach you've suggested.  Also, being able to tell
when such a process *doesn't* work is non-trivial (look at how long it
took us to discover the issues around fsync..), so saying that it seems
to have worked for a long time for you isn't really enough to make me
feel comfortable with it.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Determining if a table really changed in a trigger

[Mitar]

Hi!

Thank you everyone for your responses. I investigated them.

I have also found composite type operators [1]. There is no way to
tell the EXCEPT operator to use *= as its equality operator? *EXCEPT
would seem to be a useful operator to have. :-) I am not sure about
performance though. EXCEPT is generally fast, but probably because it
can use indices, not sure how fast *= is, given that it is comparing
binary representations. What is experience with this operator of
others?


Mitar

[1] 
https://www.postgresql.org/docs/current/functions-comparisons.html#COMPOSITE-TYPE-COMPARISON

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Re: Need help understanding error message [RESOLVED]

[Rich Shepard]

On Tue, 26 Oct 2021, Rob Sargent wrote:


Aren’t lines 3 and 6 duplicates?


Ah, shoot! The second was supposed to be 16 and that's how I saw it when I
scanned the list.

Thanks!

Rich

Re: Need help understanding error message

[Rich Shepard]

On Tue, 26 Oct 2021, Rob Sargent wrote:


Are you doing a batch of updates,


Yes,


... and including/generating a duplicate in there?


No.

Rich

Re: Need help understanding error message

[Rob Sargent]

> On Oct 26, 2021, at 11:06 AM, Rich Shepard  wrote:
> 
> On Tue, 26 Oct 2021, Ron wrote:
> 
>> Show us the actual UPDATE statement.
> 
> Ron,
> 
> insert into contacts 
> (person_nbr,contact_date,contact_type,notes,next_contact) values
> (1,'2021-10-26','Email','message 3','2012-11-16'),
> (4,'2021-10-26','Email','message 3','2012-11-16'),
> (6,'2021-10-26','Email','message 3','2012-11-16'),
> (3,'2021-10-26','Email','message 3','2012-11-16'),
> (6,'2021-10-26','Email','message 3','2012-11-16'),
> ...
> );
> 
Aren’t lines 3 and 6 duplicates?
 
> 

Re: Need help understanding error message

[Rich Shepard]

On Tue, 26 Oct 2021, Ron wrote:


Show us the actual UPDATE statement.


Ron,

insert into contacts (person_nbr,contact_date,contact_type,notes,next_contact) 
values
(1,'2021-10-26','Email','message 3','2012-11-16'),
(4,'2021-10-26','Email','message 3','2012-11-16'),
(6,'2021-10-26','Email','message 3','2012-11-16'),
(3,'2021-10-26','Email','message 3','2012-11-16'),
(6,'2021-10-26','Email','message 3','2012-11-16'),
...
);

Re: Need help understanding error message

[Rich Shepard]

On Tue, 26 Oct 2021, Rob Sargent wrote:


What do you get when you select * where person_nbr = 6 and contact_date =
‘2021-10-26’ and contact_type = ‘Email’ from activities; ?


Rob,

 person_nbr | contact_date | contact_type | notes | next_contact 
+--+--+---+--

(0 rows)

Rich

Re: Need help understanding error message

[Rob Sargent]

> On Oct 26, 2021, at 10:07 AM, Rob Sargent  wrote:
> 
> 
> 
>> 
> What do you get when you select * where person_nbr = 6 and contact_date = 
> ‘2021-10-26’ and contact_type = ‘Email’ from activities; ?
> 
> 
> 

Whoa.  Not sure why I put the “from” last but I’m sure you know it goes before 
the “where”.

Re: Need help understanding error message

[Ron]

On 10/26/21 11:58 AM, Rich Shepard wrote:

In a database table I have these rows:
# select * from contacts where person_nbr=6;
 person_nbr | contact_date | contact_type 
|  not
es   | next_contact 
+--+--+

-+--
  6 | 2018-04-05   | Phone    | Left message @ 9:39.
 |
  6 | 2019-05-14   | Phone    | He can call me if issues do 
come up. |

  6 | 2021-08-17   | Email    | Sent message 2.
 | 2021-10-06
  6 | 2021-07-23   | Email    | Sent message 1.
 | 2021-07-28
  6 | 2021-07-28   | Email    | Sent message 1.
 | 2021-08-16
(5 rows)

When I try to update this table with:
(6,'2021-10-26','Email','message 3','2012-11-16'),
psql reports this error:
psql:insert_into_contacts.sql:31: ERROR:  duplicate key value violates 
unique constraint "activities_pkey"
DETAIL:  Key (person_nbr, contact_date, contact_type)=(6, 2021-10-26, 
Email) already exists.


The PK for this table is:
PRIMARY KEY, btree (person_nbr, contact_date, contact_type)
so there should be no conflict as the contact_date in the update is not
already in the table.

I don't understand the error as it's not occurred before when updating this
table.

Insight needed,


Show us the actual UPDATE statement.

--
Angular momentum makes the world go 'round.

Re: Need help understanding error message

[Rob Sargent]

> On Oct 26, 2021, at 9:58 AM, Rich Shepard  wrote:
> 
> In a database table I have these rows:
> # select * from contacts where person_nbr=6;
> person_nbr | contact_date | contact_type |
>   not
> es   | next_contact 
> +--+--+-
> -+--
>  6 | 2018-04-05   | Phone| Left message @ 9:39.
> |
>  6 | 2019-05-14   | Phone| He can call me if issues do come 
> up. |
>  6 | 2021-08-17   | Email| Sent message 2.
> | 2021-10-06
>  6 | 2021-07-23   | Email| Sent message 1.
> | 2021-07-28
>  6 | 2021-07-28   | Email| Sent message 1.
> | 2021-08-16
> (5 rows)
> 
> When I try to update this table with:
> (6,'2021-10-26','Email','message 3','2012-11-16'),
> psql reports this error:
> psql:insert_into_contacts.sql:31: ERROR:  duplicate key value violates unique 
> constraint "activities_pkey"
> DETAIL:  Key (person_nbr, contact_date, contact_type)=(6, 2021-10-26, Email) 
> already exists.
> 
> The PK for this table is:
> PRIMARY KEY, btree (person_nbr, contact_date, contact_type)
> so there should be no conflict as the contact_date in the update is not
> already in the table.
> 
> I don't understand the error as it's not occurred before when updating this
> table.
> 
> Insight needed,
> 
> Rich
> 
> 
What do you get when you select * where person_nbr = 6 and contact_date = 
‘2021-10-26’ and contact_type = ‘Email’ from activities; ?

Need help understanding error message

[Rich Shepard]

In a database table I have these rows:
# select * from contacts where person_nbr=6;
 person_nbr | contact_date | contact_type | 
 not
es   | next_contact 
+--+--+-

-+--
  6 | 2018-04-05   | Phone| Left message @ 9:39.
 |
  6 | 2019-05-14   | Phone| He can call me if issues do come 
up. |
  6 | 2021-08-17   | Email| Sent message 2.
 | 2021-10-06
  6 | 2021-07-23   | Email| Sent message 1.
 | 2021-07-28
  6 | 2021-07-28   | Email| Sent message 1.
 | 2021-08-16
(5 rows)

When I try to update this table with:
(6,'2021-10-26','Email','message 3','2012-11-16'),
psql reports this error:
psql:insert_into_contacts.sql:31: ERROR:  duplicate key value violates unique constraint 
"activities_pkey"
DETAIL:  Key (person_nbr, contact_date, contact_type)=(6, 2021-10-26, Email) 
already exists.

The PK for this table is:
PRIMARY KEY, btree (person_nbr, contact_date, contact_type)
so there should be no conflict as the contact_date in the update is not
already in the table.

I don't understand the error as it's not occurred before when updating this
table.

Insight needed,

Rich

Re: Determining if a table really changed in a trigger

[Alban Hertroys]

> On 26 Oct 2021, at 16:16, Marcos Pegoraro  wrote:
> 
> 
>> Don’t use this approach with JSON (as opposed to JSONB) type fields though, 
>> a single extra space in the JSON structure would already lead to a 
>> difference, as would other formatting differences.
>> 
> I don´t think two equal values being converted to json will be different in 
> any way. If row_to_json of both are different, I suppose both record really 
> are different, no ? 

For row_to_json, as it’s the system that combines the fields in a row into a 
JSON structure and it probably would do that in the same way each time.

The OP however has a field of type JSON in their table, and that can contain 
the same information between the OLD and NEW fields formatted in a slightly 
different way.

For example:

=> with x as (
select '{ "x": 1, "y": 2 }'::json
union all
select '{ "y": 2, "x": 1 }'::json
)
select row(x.json)::text, md5(row(x.json)::text) from x;
row |   md5
+--
 ("{ ""x"": 1, ""y"": 2 }") | 84df40e8660dcf371d89dbf5d6a61c3d
 ("{ ""y"": 2, ""x"": 1 }") | abd6db88c2526be6ea97570aeec7e020
(2 rows)

Whereas:

=> with x as (
select '{ "x": 1, "y": 2 }'::jsonb
union all
select '{ "y": 2, "x": 1 }'::jsonb
)
select row(x.jsonb)::text, md5(row(x.jsonb)::text) from x;
   row|   md5
--+--
 ("{""x"": 1, ""y"": 2}") | d5a6dbdec7a5bfe0dc99e090db30322e
 ("{""x"": 1, ""y"": 2}") | d5a6dbdec7a5bfe0dc99e090db30322e
(2 rows)


Alban Hertroys
--
There is always an exception to always.

Re: Determining if a table really changed in a trigger

[Marcos Pegoraro]

>
>
> Don’t use this approach with JSON (as opposed to JSONB) type fields
> though, a single extra space in the JSON structure would already lead to a
> difference, as would other formatting differences.
>
> I don´t think two equal values being converted to json will be different
in any way. If row_to_json of both are different, I suppose both record
really are different, no ?

Re: Determining if a table really changed in a trigger

[Alban Hertroys]

> On 26 Oct 2021, at 9:05, Mitar  wrote:
> 
> Hi!
> 
> I have a trigger like:
> 
> CREATE TRIGGER update_trigger AFTER UPDATE ON my_table REFERENCING NEW
> TABLE AS new_table OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE
> FUNCTION trigger_function;
> 
> I would like to test inside trigger_function if the table really
> changed. I have tried to do:
> 
> PERFORM * FROM ((TABLE old_table EXCEPT TABLE new_table) UNION ALL
> (TABLE new_table EXCEPT TABLE old_table)) AS differences LIMIT 1;
> IF FOUND THEN
>  ... changed ...
> END IF;
> 
> But this fails if the table contains a JSON field with the error:
> 
> could not identify an equality operator for type json

Perhaps if you store an extra column containing a hash (for example MD5) of the 
row contents (minus the hash column, obviously)? You can put an index on the 
hash and match between OLD and NEW tables which ones changed.

When calculating the hash, you would have to specify the column names to 
exclude the hash itself, so something like this:

md5(row(col1, col2, col3)::text)

The row-to-text conversion already takes care of converting JSONB(!) to text.
Don’t use this approach with JSON (as opposed to JSONB) type fields though, a 
single extra space in the JSON structure would already lead to a difference, as 
would other formatting differences.

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: Determining if a table really changed in a trigger

[Miles Elam]

On Tue, Oct 26, 2021 at 6:36 AM Marcos Pegoraro  wrote:

>
>> Maybe converting new and old records to json and text
> PERFORM * FROM (select ID, row_to_json(O.*)::text Old_Values,
> row_to_json(N.*)::text New_Values from old_table o full outer join
> new_table N using(ID) where Old_Values is distinct from New_Values) as
> differences LIMIT 1;
>

I have done this, but with casting to jsonb, which already supports the
equality operator. Saved an extra cast to text. Also allows for easily
excluding a column or two before comparing.

I never noticed a performance problem, but I was using this technique to
see if a row had substantively changed, and if so, to cancel the write and
subsequent trigger invocations by returning NULL in the before-trigger.

The trade off of conversions to jsonb by not writing and performing
subsequent processing/writes due to later triggers was an obvious win for
me, but your mileage may vary depending on your use case.

Re: Determining if a table really changed in a trigger

[Marcos Pegoraro]

>
>
> PERFORM * FROM ((TABLE old_table EXCEPT TABLE new_table) UNION ALL
> (TABLE new_table EXCEPT TABLE old_table)) AS differences LIMIT 1;
> IF FOUND THEN
>   ... changed ...
> END IF;
>
> Maybe converting new and old records to json and text
PERFORM * FROM (select ID, row_to_json(O.*)::text Old_Values,
row_to_json(N.*)::text New_Values from old_table o full outer join
new_table N using(ID) where Old_Values is distinct from New_Values) as
differences LIMIT 1;

Re: How to copy rows into same table efficiently

[David G. Johnston]

On Tue, Oct 26, 2021 at 2:06 AM Ron  wrote:

> Anyway, for millions of rows, I might use COPY instead of INSERT
> (depending
> on how many millions, how many indices, how large the rows, how fast the
> machine, etc.
>
>
I don't imagine using COPY TO to write the data to a file and then COPY
FROM to import it is going to be an improvement over INSERT-SELECT.

Now, if you can perform the COPY TO on a replica and then only run the COPY
FROM on the primary that might be worth it.  Avoiding the I/O for the read
on the primary would be a big win.

David J.

Re: Determining if a table really changed in a trigger

[David G. Johnston]

On Tue, Oct 26, 2021 at 12:05 AM Mitar  wrote:

>
> But this fails if the table contains a JSON field with the error:
>
> could not identify an equality operator for type json
>

Thus it is not possible to use whole row comparisons.  You will need to
write the code to manually check equality on each column.  To check the
json column you will probably want to cast to jsonb (if it isn't already)
and then cast that to text and use a text equality check.  Since you are
doing a statement trigger that means writing "SELECT col1, col2, etc...".


> The table has an unique index column, if that helps.
>
>
That would be assumed since you need to be able to identify records whose
contents might otherwise be identical.

David J.

Re: ZFS filesystem - supported ?

[E-BLOKOS]

On 10/26/2021 2:35 AM, Laura Smith wrote:

Sent with ProtonMail Secure Email.

‐‐‐ Original Message ‐‐‐

On Tuesday, October 26th, 2021 at 01:18, Benedict Holland 
 wrote:


In my opinion, ext4 will solve any and all problems without a very deep 
understanding of file system architecture. In short, i would stick with ext4 
unless you have a good reason not to. Maybe there is one. I have done this a 
long time and never thought twice about which file system should support my 
servers.



Curious, when it comes to "traditional" filesystems, why ext4 and not xfs ? 
AFAIK the legacy issues associated with xfs are long gone ?



XFS is indeed for me  the most stable and performant for postgresql 
today. EXT4 was good too, but less performant.


--
E-BLOKOS

Re: plpython3 package installation problem

[Devrim Gündüz]

Hi,

On Tue, 2021-10-26 at 10:33 +0800, Yi Sun wrote:
> Hello,
> 
> As we need to use the plpython3u extension, we tried to install the
> plpython3 package but showed that we needed to install python3-libs,
> but python36-libs was already installed for patroni usage.
> 
> 1. Will installing python3-libs affect current python36-libs usage?
> 2. If we can do some configuration to let python36-libs work as
> python3-libs then no need to install   python3-libs? Thanks
> 
> # yum localinstall /tmp/postgresql11-plpython3-11.11-
> 1PGDG.rhel7.x86_64.rpm
> ...
>    Requires: python3-libs
>  You could try using --skip-broken to work around the problem
>  You could try running: rpm -Va --nofiles --nodigest
> 
> # yum search python3 | grep libs
> python34-libs.x86_64 : Python 3 runtime libraries
> python36-libs.x86_64 : Python runtime libraries
> shiboken-python36-libs.x86_64 : CPython bindings generator for C++
> libraries -
> # yum list python36-libs.x86_64
> Installed Packages
> python36-libs.x86_64
>  3.6.8-1.el7
> 

python3-libs is an alias for python36-libs. Please install that package
first.


Regards,


> Thank you

-- 
Devrim Gündüz
Open Source Solution Architect, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: ZFS filesystem - supported ?

[Laura Smith]

Sent with ProtonMail Secure Email.

‐‐‐ Original Message ‐‐‐

On Tuesday, October 26th, 2021 at 01:18, Benedict Holland 
 wrote:

> In my opinion, ext4 will solve any and all problems without a very deep 
> understanding of file system architecture. In short, i would stick with ext4 
> unless you have a good reason not to. Maybe there is one. I have done this a 
> long time and never thought twice about which file system should support my 
> servers.
>


Curious, when it comes to "traditional" filesystems, why ext4 and not xfs ? 
AFAIK the legacy issues associated with xfs are long gone ?

Re: ZFS filesystem - supported ?

[Bob Jolliffe]

We have some users of our software who have had a good experience with
postgresql on zfs/zol.  Two features which have proved useful are the
native encryption (less fiddly than luks) and compression.  Interestingly,
many of our users are stuck with quite old and slow disks.  Using
compression (even together with encryption) on the slow disks gives quite a
significant performance boost.  Trading cpu for disk bandwidth.  Also they
often dont have infinite access to more disk, so the storage efficiency is
welcomed.

We are interested in the snapshots but a little wary of potential data
integrity issues.

We have a disturbance in our database structure (usually nightly) where
large tables are dropped and recreated.  snapshots of a gradually
increasing size database probably work very well.  I think these massive
deletions probably make the snapshots quite heavy.  Also creating a
challenge for incremental backups, replication etc but that is another (not
quite unrelated) issue.

Regards
Bob

On Tue, 26 Oct 2021 at 01:18, Benedict Holland 
wrote:

> In my opinion, ext4 will solve any and all problems without a very deep
> understanding of file system architecture. In short, i would stick with
> ext4 unless you have a good reason not to. Maybe there is one. I have done
> this a long time and never thought twice about which file system should
> support my servers.
>
> On Mon, Oct 25, 2021, 6:01 PM Robert L Mathews 
> wrote:
>
>> On 10/25/21 1:40 PM, Mladen Gogala wrote:
>> > This is probably not the place
>> > to discuss the inner workings of snapshots, but it is worth knowing
>> that
>> > snapshots drastically increase the IO rate on the file system - for
>> > every snapshot. That's where the slowness comes from.
>>
>> I have recent anecdotal experience of this. I experiment with using
>> Btrfs for a 32 TB backup system that has five 8 TB spinning disks.
>> There's an average of 8 MBps of writes scattered around the disks, which
>> isn't super high, obviously.
>>
>> The results were vaguely acceptable until I created a snapshot of it, at
>> which point it became completely unusable. Even having one snapshot
>> present caused hundreds of btrfs-related kernel threads to thrash in the
>> "D" state almost constantly, and it never stopped doing that even when
>> left for many hours.
>>
>> I then experimented with adding a bcache layer on top of Btrfs to see if
>> it would help. I added a 2 TB SSD using bcache, partitioned as 1900 GB
>> read cache and 100 GB write cache. It made very little difference and
>> was still unusable as soon as a snapshot was taken.
>>
>> I did play with the various btrfs and bcache tuning knobs quite a bit
>> and couldn't improve it.
>>
>> Since that test was a failure, I then decided to try the same setup with
>> OpenZFS on a lark, with the same set of disks in a "raidz" array, with
>> the 2 TB SSD as an l2arc read cache (no write cache). It easily handles
>> the same load, even with 72 hourly snapshots present, with the default
>> settings. I'm actually quite impressed with it.
>>
>> I'm sure that the RAID, snapshots and copy-on-write reduce the maximum
>> performance considerably, compared to ext4. But on the other hand, it
>> did provide the performance I expected to be possible given the setup.
>> Btrfs *definitely* didn't; I was surprised at how badly it performed.
>>
>> --
>> Robert L Mathews, Tiger Technologies, http://www.tigertech.net/
>>
>>
>>

Re: How to copy rows into same table efficiently

[Ron]

On 10/26/21 1:04 AM, Arun Suresh wrote:


Dear PG experts,

We have a tenant discriminator column in our tables to enable storage of 
data from multiple

tenants. This column is also part of the composite primary key.
The customers may request creation of a tenant copy, which means if they 
currently have a
tenant id "ABCD", they would like to copy all data "where tenant_id = 
'ABCD'" to a new copy tenant "XYZ".

The copy must also be done on the same table.

Current approach taken is to build a query like below:
INSERT INTO mytable (col1, col2, col3, col4) SELECT col1, 'XYZ', col3, 
col4 FROM mytable WHERE col2 = 'ABCD'


Is there a better way to do this?
There could be other tables with foreign key reference, would a simple 
ordering of the copy based on table relationship suffice?


That would be my first thought.  Of course, FK dependencies quickly become a 
rat's nest, so beware.


Also if the table has millions of records, what are the things to take 
care of?


Take care of?

Anyway, for millions of rows, I might use COPY instead of INSERT (depending 
on how many millions, how many indices, how large the rows, how fast the 
machine, etc.


--
Angular momentum makes the world go 'round.

Re: Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND

[Vijaykumar Jain]

On Tue, 26 Oct 2021 at 11:39, Vivekk P  wrote:

> Hi Team,
>
> Please have a look on the below problem statement and suggest us if there
> are any ways to make the planner pick PARALLEL INDEX SCAN and PARALLEL
> APPEND
>
>
> 1. We have tried fine-tuning the below parameters with all possible values
> to get the expected results but got no luck,
>
> Parameter setting unit
> enable_parallel_append on
> enable_parallel_hash on
> force_parallel_mode off
> max_parallel_maintenance_workers 2
> max_parallel_workers 8
> max_parallel_workers_per_gather 2
> min_parallel_index_scan_size 64 8kB
> min_parallel_table_scan_size 1024 8kB
> parallel_leader_participation on
> parallel_setup_cost 1000
> parallel_tuple_cost 0.1
> effective_cache_size 4GB
> shared_buffers 128MB
> work_mem 4MB
>
>


I am pretty sure there will be questions on why you want to do that or why
you think this would solve any problem.

anyways,
This is just to force a parallel run, but do not do this in production.
The way we try this here, is to trick the optimizer by saying there is no
cost of making use of parallel setup, so this is always the best
path.(which is wrong, but..)

postgres=# select name,setting from pg_settings where name like '%para%';
   name| setting
---+-
 enable_parallel_append| on
 enable_parallel_hash  | on
 force_parallel_mode   | off
 log_parameter_max_length  | -1
 log_parameter_max_length_on_error | 0
 max_parallel_maintenance_workers  | 2
 max_parallel_workers  | 8
 max_parallel_workers_per_gather   | 2
 min_parallel_index_scan_size  | 64
 min_parallel_table_scan_size  | 1024
 parallel_leader_participation | on
 parallel_setup_cost   | 1000
 parallel_tuple_cost   | 0.1
 ssl_dh_params_file|
(14 rows)

postgres=# set force_parallel_mode to on;
SET
postgres=# set parallel_setup_cost to 0;
SET
postgres=# set parallel_tuple_cost to 0;
SET
postgres=# explain analyze select * from t where id > 0;
   QUERY PLAN

 Gather  (cost=0.00..3.76 rows=80 width=12) (actual time=2.900..5.996
rows=80 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Append  (cost=0.00..3.76 rows=34 width=12) (actual
time=0.002..0.009 rows=27 loops=3)
 ->  Parallel Seq Scan on t2 t_2  (cost=0.00..1.23 rows=18
width=12) (actual time=0.005..0.009 rows=31 loops=1)
   Filter: (id > 0)
 ->  Parallel Seq Scan on t1 t_1  (cost=0.00..1.21 rows=17
width=12) (actual time=0.004..0.006 rows=29 loops=1)
   Filter: (id > 0)
 ->  Parallel Seq Scan on t3 t_3  (cost=0.00..1.15 rows=12
width=12) (actual time=0.001..0.003 rows=20 loops=1)
   Filter: (id > 0)
 Planning Time: 0.568 ms
 Execution Time: 6.022 ms
(12 rows)

postgres=# set seq_page_cost to 10; --- since we do not want seq scan
but index scan
SET
postgres=# explain analyze select * from t where id > 0;
QUERY
PLAN
---
 Gather  (cost=0.14..37.65 rows=80 width=12) (actual time=0.232..5.326
rows=80 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Append  (cost=0.14..37.65 rows=34 width=12) (actual
time=0.007..0.020 rows=27 loops=3)
 ->  Parallel Index Only Scan using t2_ts_id_idx on t2 t_2
(cost=0.14..12.55 rows=18 width=12) (actual time=0.005..0.012 rows=31
loops=1)
   Index Cond: (id > 0)
   Heap Fetches: 31
 ->  Parallel Index Only Scan using t1_ts_id_idx on t1 t_1
(cost=0.14..12.53 rows=17 width=12) (actual time=0.007..0.013 rows=29
loops=1)
   Index Cond: (id > 0)
   Heap Fetches: 29
 ->  Parallel Index Only Scan using t3_ts_id_idx on t3 t_3
(cost=0.14..12.41 rows=12 width=12) (actual time=0.019..0.025 rows=20
loops=1)
   Index Cond: (id > 0)
   Heap Fetches: 20
 Planning Time: 0.095 ms
 Execution Time: 5.351 ms
(15 rows)


Again, do not do this in production. This is only for debugging purposes
using 0 cost.
You can try looking at pg_hint_plan (osdn.jp)
   if you want to force a
plan.
Also, be ready to answer, why do you want to do this or what makes you
think the parallel option will work magic.

also with TB sized dbs, pls ensure your disk io/latency etc are not a
problem.
maybe also bump memory and tune accordingly, to absorb disk io.

How to copy rows into same table efficiently

[Arun Suresh]

Dear PG experts,

We have a tenant discriminator column in our tables to enable storage of
data from multiple
tenants. This column is also part of the composite primary key.
The customers may request creation of a tenant copy, which means if they
currently have a
tenant id "ABCD", they would like to copy all data "where tenant_id =
'ABCD'" to a new copy tenant "XYZ".
The copy must also be done on the same table.

Current approach taken is to build a query like below:
INSERT INTO mytable (col1, col2, col3, col4) SELECT col1, 'XYZ', col3, col4
FROM mytable WHERE col2 = 'ABCD'

Is there a better way to do this?
There could be other tables with foreign key reference, would a simple
ordering of the copy based on table relationship suffice?
Also if the table has millions of records, what are the things to take care
of?


Regards,
Arun Suresh

Determining if a table really changed in a trigger

[Mitar]

Hi!

I have a trigger like:

CREATE TRIGGER update_trigger AFTER UPDATE ON my_table REFERENCING NEW
TABLE AS new_table OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE
FUNCTION trigger_function;

I would like to test inside trigger_function if the table really
changed. I have tried to do:

PERFORM * FROM ((TABLE old_table EXCEPT TABLE new_table) UNION ALL
(TABLE new_table EXCEPT TABLE old_table)) AS differences LIMIT 1;
IF FOUND THEN
  ... changed ...
END IF;

But this fails if the table contains a JSON field with the error:

could not identify an equality operator for type json

The table has an unique index column, if that helps.


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND

[Vivekk P]

Hi Team,

Please have a look on the below problem statement and suggest us if there
are any ways to make the planner pick PARALLEL INDEX SCAN and PARALLEL
APPEND

*Problem Statement :*

We have a partitioned table with a partition key column (crdt -->
timestamp). A SELECT query on this table that does not invoke the partition
key column undergoes INDEX SCAN on all the partitions and it is being
summed up in an APPEND node

Our requirement is to make the planner pick,

--PARALLEL INDEX SCAN instead of INDEX SCAN

--PARALLEL APPEND instead of APPEND


PostgreSQL version --> 13.4


*Table Structure :*


* Partitioned table "public.pay"* * Column | Type | Collation | Nullable |
Default*
---+--+---+--+-
id | bigint | | not null | pri | character varying(256) | | | prf |
character varying(128) | | | pi | character varying(256) | | | pas |
character varying(128) | | | s | payment_state | | not null | st | jsonb |
| not null | rct | character varying(32) | | | prf | jsonb | | | pa | jsonb
| | | always | jsonb | | | '{}'::jsonb pr | jsonb | | | pe | jsonb | | |
cda | jsonb | | | tr | jsonb | | | ar | jsonb | | | crq | jsonb | | | cr |
jsonb | | | prt | jsonb | | | rrq | jsonb | | | rtrt | jsonb | | | rrt |
jsonb | | | tc | character varying(32) | | | crdt | timestamp with time
zone | | not null | now() isfin | boolean | | | ifi | bigint | | | rid |
character varying(256) | | | pce | text | | | cce | text | | | pp | jsonb |
| | *Partition key: RANGE (crdt)* *Indexes:* "pay_pkey" PRIMARY KEY, btree
(id, crdt) "pay_businessid_storeid_crdt" btree ((pe ->>
'businessID'::text), (pe ->> 'storeID'::text), crdt) WHERE (pe ->>
'businessID'::text) IS NOT NULL AND (pe ->> 'storeID'::text) IS NOT NULL
"pay_crdt_index" btree (crdt) "pay_ifi_idx" btree (ifi) "pay_index_isfin"
btree (isfin) "pay_pi_pas_key" UNIQUE CONSTRAINT, btree (pi, pas, crdt)
"pay_pri_prf_key" UNIQUE CONSTRAINT, btree (pri, prf, crdt) "pay_rid_crdt"
btree (rid, crdt) WHERE rid IS NOT NULL AND crdt >= '2020-04-01
00:00:00+00'::timestamp with time zone "pay_tc_index" btree (tc, crdt)
"pay_user_id_pe_index" btree ((pe ->> 'userID'::text)) WHERE (pe ->>
'userID'::text) IS NOT NULL "pay_user_id_pr_index" btree ((pr ->>
'userID'::text)) WHERE (pr ->> 'userID'::text) IS NOT NULL *Triggers:*
pay_bucardo_delta AFTER INSERT OR DELETE OR UPDATE ON pay FOR EACH ROW
EXECUTE FUNCTION bucardo.delta_public_pay()
pay_bucardo_note_trunc_sync_payment_pay AFTER TRUNCATE ON pay FOR EACH
STATEMENT EXECUTE FUNCTION
bucardo.bucardo_note_truncation('sync_payment_pay')
pay_payment_completion_trigger_after_upsert AFTER INSERT OR UPDATE ON pay
FOR EACH ROW EXECUTE FUNCTION handle_payment_completion() *Triggers firing
always:* pay_trg_change_capture_pay AFTER INSERT OR UPDATE ON pay FOR EACH
ROW EXECUTE FUNCTION fun_change_capture_pay() Number of partitions: 4 (Use
\d+ to list them.)

*Partitions :*



p_p2021_09 FOR VALUES FROM ('2021-09-01 00:00:00+00') TO
('2021-10-01 00:00:00+00'),

p_p2021_10 FOR VALUES FROM ('2021-10-01 00:00:00+00') TO
('2021-11-01 00:00:00+00'),

p_p2021_11 FOR VALUES FROM ('2021-11-01 00:00:00+00') TO
('2021-12-01 00:00:00+00'),

p_default DEFAULT


*Table_size :*


*Name*

*Type*

*Size*

pay

partitioned table

0 bytes

p_default

table

8192 bytes

p_p2021_09

table

358 MB

p_p2021_10

table

370 MB

p_p2021_11

table

358 MB

*Note: *The table size will be in TB's in the actual scenario

*Query :*


SELECT id, pri, prf, pi, pas, s, st, a, rct, pr, pa, pr, pe, cda, crdt, tr,
ar, crq, cr, prt, tc, ifi, isfin, rrt, rrq, rtrt, rid, pce, cce, pp

FROM public.pay WHERE id = 3011852315482470422;


*Query Plan :*


pay=# EXPLAIN (ANALYZE,BUFFERS) SELECT id, pri, prf, pi, pas, s,

st, a, rct, pr, pa, pr, pe, cda, crdt,

tr, ar, crq, cr, prt, tc, ifi, isfin, rrt,

rrq, rtrt, rid, pce, cce, pp

FROM public.pay WHERE id = 3011852315482470422;

  QUERY
PLAN

--

 Append  (cost=0.29..33.09 rows=4 width=1931) (actual time=0.148..0.211
rows=0 loops=1)

   Buffers: shared hit=8

   ->  Index Scan using pay_p2021_09_pkey on pay_p2021_09 pay_1
(cost=0.29..8.30 rows=1 width=1931) (actual time=0.015..0.022 rows=0
loops=1)

 Index Cond: (id = '3011852315482470422'::bigint)

 Buffers: shared hit=2

   ->  Index Scan using pay_p2021_10_pkey on pay_p2021_10 pay_2
(cost=0.29..8.30 rows=1 width=1931) (actual time=0.012..0.019 rows=0
loops=1)

 Index Cond: (id = '3011852315482470422'::bigint)

 Buffers: shared hit=2

   ->  Index Scan using pay_p2021_11_pkey on pay_p2021_11 pay_3
(cost=0.29..8.30 rows=1 width=1931) (actual time=0.012..0.019 rows=0
loops=1)

 Index Cond: (id =