Re: String comparison fails for some cases after migration

Jayadevan M writes: > We moved our PostgreSQL database from one hosting provider to another using > pgbackrest. In the new environment, some comparison operations were > failing. The issue was fixed by running an update. But I am trying to find > out what would have happened. Did the underlyin

Re: String comparison fails for some cases after migration

Hi, On Wed, Oct 27, 2021 at 2:12 PM Jayadevan M wrote: > > We moved our PostgreSQL database from one hosting provider to another using > pgbackrest. In the new environment, some comparison operations were > failing. The issue was fixed by running an update. But I am trying to find > out wha

String comparison fails for some cases after migration

Hello all, We moved our PostgreSQL database from one hosting provider to another using pgbackrest. In the new environment, some comparison operations were failing. The issue was fixed by running an update. But I am trying to find out what would have happened. select * from accounts where email

Best options for tracking history of data?

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, alt

Re: ZFS filesystem - supported ?

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&px=Linux-5.14-File-Systems > Ext4 is much

Re: Need help understanding error message [RESOLVED]

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

Re: Determining if a table really changed in a trigger

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

Re: ZFS filesystem - supported ?

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

Re: ZFS filesystem - supported ?

On 10/26/21 20:50, Imre Samu wrote: > Phoronix has some very useful benchmarks: > https://www.phoronix.com/scan.php?page=news_item&px=Linux-5.14-File-Systems > Ext4 is much better than XFS with SQLite tests and almo

Re: ZFS filesystem - supported ?

> Phoronix has some very useful benchmarks: > https://www.phoronix.com/scan.php?page=news_item&px=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 pa

Re: ZFS filesystem - supported ?

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

Re: Determining if a table really changed in a trigger

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 w

Re: ZFS filesystem - supported ?

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 activel

Re: Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND

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

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

Re: How to copy rows into same table efficiently

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 for

Re: Determining if a table really changed in a trigger

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

Re: Determining if a table really changed in a trigger

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

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

Re: Determining if a table really changed in a trigger

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,

Re: ZFS filesystem - supported ?

> 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

Re: Determining if a table really changed in a trigger

> 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 (th

Re: Determining if a table really changed in a trigger

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 state

Re: Determining if a table really changed in a trigger

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

Re: ZFS filesystem - supported ?

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

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

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 mo

Re: ZFS filesystem - supported ?

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

Re: Determining if a table really changed in a trigger

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

Re: Need help understanding error message [RESOLVED]

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

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

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

Re: Need help understanding error message

> On Oct 26, 2021, at 11:05 AM, Rich Shepard wrote: > > 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, > > 'Email'; > person_nbr | contact_date | c

Re: Need help understanding error message

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','messa

Re: Need help understanding error message

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

Re: Need help understanding error message

> 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

Re: Need help understanding error message

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_conta

Re: Need help understanding error message

> 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

Need help understanding error message

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

Re: Determining if a table really changed in a trigger

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

Re: Determining if a table really changed in a trigger

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

Re: Determining if a table really changed in a trigger

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

Re: Determining if a table really changed in a trigger

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 distin

Re: Determining if a table really changed in a trigger

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

Re: How to copy rows into same table efficiently

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

Re: Determining if a table really changed in a trigger

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 eac

Re: ZFS filesystem - supported ?

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

Re: plpython3 package installation problem

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 python

Re: ZFS filesystem - supported ?

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

Re: ZFS filesystem - supported ?

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

Re: How to copy rows into same table efficiently

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 curren

Re: Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND

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

How to copy rows into same table efficiently

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 t

Determining if a table really changed in a trigger

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