Re: pg_dump - how to force to show timestamps in client log

[Ron]

TBH, it's also been a while since I needed to monitor pg_dump, since we now 
use pgbackrest (which just so happens to have a progress meter...).


On 11/20/20 4:32 PM, Mark Johnson wrote:
This all sounds like a previous discussion on pg hackers about a progress 
meter for pg_dump.  Search the archives for that discussion.  Also, search 
the web for something like "pg_dump progress meter" and you'll get a few 
suggestions like pipe to pv, although that does not appear to work with 
all of the file formats supported by pg_dump.


What do you see in pg_stat_activity?  It's been a while since I tried to 
monitor a running pg_dump.


 Also, if you redirect the output to a file then doesn't the file's 
timestamp get updated each time something happens. (That's what I used to 
do with Oracle before they added timestamps).


On Fri, Nov 20, 2020 at 5:00 PM Ron > wrote:


On 11/20/20 3:39 PM, Adrian Klaver wrote:
> On 11/20/20 1:00 PM, Ron wrote:
>> On 11/20/20 2:56 PM, Adrian Klaver wrote:
>>> On 11/20/20 10:01 AM, Durumdara wrote:
 Hello!

 We need to log the pg_dump's state.
 What objects are in copy, and what are the starting and ending times.

 But when I try to redirect the output, the result doesn't have
timestamps.

 PG 11, on Windows.

 As I see the -v option isn't enough to see the starting times.

 For example:

 2020-11-19 12:00:01.084 Dump table content table1
 2020-11-19 12:03:12.932 Dump table content table2
 ...
 etc.
>>>
>>>
>>> If you are redirecting to a file it have the creation time that
you can
>>> use. Internally times don't really matter for the objects as the
dump is
>>> based on a snapshot. Said snapshot is based on visible
transactions not
>>> time. So for practical purposes they all occur at the same 'time'.
>>
>> It makes all the difference when monitoring the progress of a backup.
>>
>
> With -v you will get running list of objects dumped, just not the time.
> The time is only of value relative to the following. Progress will
only be
> measurable by determining what is left to run and the time for each
> object. Not sure that is feasible as you would have to pre-run the
dump to
> get information about the number of objects and an estimate of the data
> quantity involved and the effect of each on the other. I could see that
> estimate getting worse the bigger the data set(and hence the more you
> cared) got. Because at some point the load on the machine would
affect the
> output speed of the dump.

By knowing the sizes of the tables, and how long it takes to takes the
first
"some" tables, then one can forecast how long it takes to backup the
whole
database.

-- 
Angular momentum makes the world go 'round.





--
Angular momentum makes the world go 'round.

Re: pg_dump - how to force to show timestamps in client log

[Peter J. Holzer]

On 2020-11-20 19:01:47 +0100, Durumdara wrote:
> We need to log the pg_dump's state.
> What objects are in copy, and what are the starting and ending times.
> 
> But when I try to redirect the output, the result doesn't have timestamps.
> 
> PG 11, on Windows.
> 
> As I see the -v option isn't enough to see the starting times.
> 
> For example:
> 
> 2020-11-19 12:00:01.084 Dump table content table1
> 2020-11-19 12:03:12.932 Dump table content table2

You can pipe the output through a timestamping tool like this:

pg_dump --compress=5 -v -Fd -f "$name.$$" $db 2>&1 | ts > 
log/"$name".$(isodate).log

ts is available here: https://github.com/hjp/simple/tree/master/ts

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: two questions about toast

[Luca Ferrari]

On Sun, Nov 15, 2020 at 6:45 PM Adrian Klaver  wrote:
> Database --> Table --> TOAST table.

Thanks, I was not sure about.
>
> > 2) while executing a query against toasted values I got this debug
> > message that I don't know what is meaning
>
> This happened when you where doing the REINDEX DATABASE?


No, there was no indeed involved at that time.

Luca

INSERT Trigger to check for existing records

[Hagen Finley]

Hello,

I am definitely out over my skis here so I’ll apologize in advance 😉. 
Running version 12.5-1.pgdg20.04+1 on ubuntu. It’s essentially a 
personal database I use to ingest sales forecast spreadsheets from which 
I  create custom reports for my job function.


I pull a new forecast spreadsheet each Monday. 80% of the records are 
the same as the existing records from the week before.


Here’s what I (REALLY) want:

Trigger looks at three fields prior to new insert: Deal ID (numeric), 
revenue (numeric), stage(char)     Example: 19743576 22072.37    Commit 
- 90%


1. If the NEW dealid doesn't match any of the OLD dealids, insert the
   new row
2. if the NEW dealid, revenue and stage fields ALL match the OLD
   dealid, revenue and stage, skip (don't insert the NEW row)
3. If the NEW dealid matches an OLD dealid but either the NEW revenue
   OR the stage fields have changed (don't match OLD record) insert new
   row (I'll review both rows manually)


*Attempt 1: *Update chk field with 'same' if OLD revusd OR stage are 
different than the NEW revusd OR stage


CREATE TRIGGER chk4chg
BEFORE
    INSERT ON sfdc
    FOR EACH ROW
BEGIN
    UPDATE sfdc
    SET chk = 'same'
    WHERE ndealid = :NEW.ndealid
  AND revusd = :NEW.revusd
  AND stage = :NEW.stage
END chk4chg;

Remarkably, that works in that it will UPDATE the chk field with 'same'


|ndealid |revusd |stage |chk   |

|17713063|130|Propose - 60% |same  |

However, I must manually enter the parameters in dialogue box that 
(inexplicably) pops up when I run this command.



*Attempt 2:*

CREATE OR REPLACE FUNCTION update_insert() RETURNS trigger AS $$
BEGIN
    UPDATE sfdc
    SET chk = 'same'
    WHERE ndealid = OLD.ndealid;
    AND NEW.revusd = OLD.revusd
    AND NEW.stage = OLD.stage;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION Query returned successfully in 136 msec.

That's good news but the trigger doesn't actually update. It lacks 
BEFORE INSERT ON sfdc FOR EACH ROW so low prospect for success :-).



*Attempt 3: *A little more sophisticated executing Function from Trigger

CREATE TRIGGER smart_update_same BEFORE INSERT ON sfdc
FOR EACH ROW EXECUTE FUNCTION update_insert();

CREATE OR REPLACE FUNCTION update_insert() RETURNS trigger AS $$
BEGIN
    UPDATE sfdc
    SET sfdc.chk = 'same'
    WHERE NEW.ndealid = OLD.ndealid
  AND NEW.revusd = OLD.revusd
  AND NEW.stage = OLD.stage;
END;
$$ LANGUAGE plpgsql;

These 2 CREATEs return successfully but do not update the chk field on a 
successful INSERT:


sales=# select ndealid,revusd,stage,chk from sfdc where ndealid = 19743576;

  ndealid   |  revusd  | stage  | chk

 19743576 | 22072.37 | Commit - 90%   |
   19743576 | 22072.37 | Commit - 90%   |
   19743576 | 22072.37 | Commit - 90%   |

These 3 attempts won't give me what I REALLY want but I figure I could 
use the chk field to delete the new inserts I didn't need.


Am I anywhere close (same county) to the right code?

Hagen

Larimer County, CO

Re: INSERT Trigger to check for existing records

[David G. Johnston]

On Saturday, November 21, 2020, Hagen Finley  wrote:

>
> I pull a new forecast spreadsheet each Monday. 80% of the records are the
> same as the existing records from the week before.
>
> Here’s what I (REALLY) want:
>
> Trigger looks at three fields prior to new insert: Deal ID (numeric),
> revenue (numeric), stage(char) Example: 1974357622072.37Commit
> - 90%
>
>1. If the NEW dealid doesn't match any of the OLD dealids, insert the
>new row
>2. if the NEW dealid, revenue and stage fields ALL match the OLD
>dealid, revenue and stage, skip (don't insert the NEW row)
>3. If the NEW dealid matches an OLD dealid but either the NEW revenue
>OR the stage fields have changed (don't match OLD record) insert new row
>(I'll review both rows manually)
>
>
> Am I anywhere close (same county) to the right code?
>
>
>
IMO, don’t use triggers.  Load the data into a temporary, or unlogged
table, and then run commands to do what you want against the live tables.
Truncate/drop before doing that again the following week.

David J.

Re: INSERT Trigger to check for existing records

[Adrian Klaver]

On 11/21/20 8:00 AM, Hagen Finley wrote:

Hello,

I am definitely out over my skis here so I’ll apologize in advance 😉. 
Running version 12.5-1.pgdg20.04+1 on ubuntu. It’s essentially a 
personal database I use to ingest sales forecast spreadsheets from which 
I  create custom reports for my job function.


I pull a new forecast spreadsheet each Monday. 80% of the records are 
the same as the existing records from the week before.


Here’s what I (REALLY) want:

Trigger looks at three fields prior to new insert: Deal ID (numeric), 
revenue (numeric), stage(char)     Example: 19743576 22072.37    Commit 
- 90%


 1. If the NEW dealid doesn't match any of the OLD dealids, insert the
new row
 2. if the NEW dealid, revenue and stage fields ALL match the OLD
dealid, revenue and stage, skip (don't insert the NEW row)
 3. If the NEW dealid matches an OLD dealid but either the NEW revenue
OR the stage fields have changed (don't match OLD record) insert new
row (I'll review both rows manually)


*Attempt 1: *Update chk field with 'same' if OLD revusd OR stage are 
different than the NEW revusd OR stage


CREATE TRIGGER chk4chg
BEFORE
     INSERT ON sfdc
     FOR EACH ROW
BEGIN
     UPDATE sfdc
     SET chk = 'same'
     WHERE ndealid = :NEW.ndealid
   AND revusd = :NEW.revusd
   AND stage = :NEW.stage
END chk4chg;

Remarkably, that works in that it will UPDATE the chk field with 'same'


Not sure how.
More comments below.




|ndealid |revusd |stage |chk   |

|17713063|130|Propose - 60% |same  |

However, I must manually enter the parameters in dialogue box that 
(inexplicably) pops up when I run this command.


What client are you using?




*Attempt 2:*

CREATE OR REPLACE FUNCTION update_insert() RETURNS trigger AS $$
BEGIN
     UPDATE sfdc
     SET chk = 'same'
     WHERE ndealid = OLD.ndealid;
     AND NEW.revusd = OLD.revusd
     AND NEW.stage = OLD.stage;
     RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION Query returned successfully in 136 msec.

That's good news but the trigger doesn't actually update. It lacks 
BEFORE INSERT ON sfdc FOR EACH ROW so low prospect for success :-).



*Attempt 3: *A little more sophisticated executing Function from Trigger

CREATE TRIGGER smart_update_same BEFORE INSERT ON sfdc
FOR EACH ROW EXECUTE FUNCTION update_insert();

CREATE OR REPLACE FUNCTION update_insert() RETURNS trigger AS $$
BEGIN
     UPDATE sfdc
     SET sfdc.chk = 'same'
     WHERE NEW.ndealid = OLD.ndealid
   AND NEW.revusd = OLD.revusd
   AND NEW.stage = OLD.stage;
END;
$$ LANGUAGE plpgsql;

These 2 CREATEs return successfully but do not update the chk field on a 
successful INSERT:


sales=# select ndealid,revusd,stage,chk from sfdc where ndealid = 19743576;

   ndealid   |  revusd  | stage  | chk

  19743576 | 22072.37 | Commit - 90%   |
    19743576 | 22072.37 | Commit - 90%   |
    19743576 | 22072.37 | Commit - 90%   |

These 3 attempts won't give me what I REALLY want but I figure I could 
use the chk field to delete the new inserts I didn't need.


Am I anywhere close (same county) to the right code?


Don't do the UPDATE. Also I thought when the values matched you did
not want INSERT?

Instead:

IF NEW.ndealid = OLD.ndealid AND NEW.revusd = OLD.revusd
AND NEW.stage = OLD.stage THEN
RETURN NULL; --Will cancel INSERT
ELSE
RETURN NEW;

END IF;



Hagen

Larimer County, CO




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

Re: INSERT Trigger to check for existing records

[Adrian Klaver]

On 11/21/20 8:20 AM, Adrian Klaver wrote:

On 11/21/20 8:00 AM, Hagen Finley wrote:

Hello,




Instead:

IF NEW.ndealid = OLD.ndealid AND NEW.revusd = OLD.revusd
     AND NEW.stage = OLD.stage THEN
 RETURN NULL; --Will cancel INSERT
ELSE
     RETURN NEW;

END IF;


Well this is what happens when I answer BC(before coffee). The above 
will not work, if for no other reason then OLD does not exist in an 
INSERT. Will try to come up with something that is in the realm of 
possibility.






Hagen

Larimer County, CO







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

Re: INSERT Trigger to check for existing records

[Adrian Klaver]

On 11/21/20 8:47 AM, Adrian Klaver wrote:

On 11/21/20 8:20 AM, Adrian Klaver wrote:

On 11/21/20 8:00 AM, Hagen Finley wrote:

Hello,




Instead:

IF NEW.ndealid = OLD.ndealid AND NEW.revusd = OLD.revusd
 AND NEW.stage = OLD.stage THEN
 RETURN NULL; --Will cancel INSERT
ELSE
 RETURN NEW;

END IF;


Well this is what happens when I answer BC(before coffee). The above 
will not work, if for no other reason then OLD does not exist in an 
INSERT. Will try to come up with something that is in the realm of 
possibility.


Alright caffeine in the blood stream, so something that might actually work:

DECLARE
match_ct integer;
BEGIN

SELECT INTO
match_ct count(*)
FROM
sfdc
WHERE
ndealid = NEW.ndealid
AND
revusd = NEW.revusd
AND
   stage = NEW.stage;

   IF match_ct > 0 THEN
RETURN NULL; --Will cancel INSERT
   ELSE
RETURN NEW;
   END IF;

END;

Though I would also point you at David's solution. Given that you are 
only looking at ~20% of the records being different it would save you a 
lot of churning through INSERTs.








Hagen

Larimer County, CO










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

Re: INSERT Trigger to check for existing records

[Hagen Finley]

Thanks so much Adrian,

I like this approach but as you indicated it doesn't actually NULL the 
INSERT.


Could we UPDATE the existing record (per my fledgling chk UPDATE and 
then RETURN NULL? (More proof I don't know what I am talking about ;-).


Hagen


On 11/21/20 10:11 AM, Adrian Klaver wrote:

On 11/21/20 8:47 AM, Adrian Klaver wrote:

On 11/21/20 8:20 AM, Adrian Klaver wrote:

On 11/21/20 8:00 AM, Hagen Finley wrote:

Hello,




Instead:

IF NEW.ndealid = OLD.ndealid AND NEW.revusd = OLD.revusd
 AND NEW.stage = OLD.stage THEN
 RETURN NULL; --Will cancel INSERT
ELSE
 RETURN NEW;

END IF;


Well this is what happens when I answer BC(before coffee). The above 
will not work, if for no other reason then OLD does not exist in an 
INSERT. Will try to come up with something that is in the realm of 
possibility.


Alright caffeine in the blood stream, so something that might actually 
work:


DECLARE
    match_ct integer;
BEGIN

    SELECT INTO
match_ct count(*)
    FROM
    sfdc
    WHERE
    ndealid = NEW.ndealid
    AND
    revusd = NEW.revusd
    AND
   stage = NEW.stage;

   IF match_ct > 0 THEN
   RETURN NULL; --Will cancel INSERT
   ELSE
   RETURN NEW;
   END IF;

END;

Though I would also point you at David's solution. Given that you are 
only looking at ~20% of the records being different it would save you 
a lot of churning through INSERTs.








Hagen

Larimer County, CO

Re: INSERT Trigger to check for existing records

[Hagen Finley]

David,

That's an interesting idea. I WOULD like  to retain the OLD records that 
are the same and only INSERT new or changed records. Is there a way to 
compare the old and the new records without a trigger?


Hagen

On 11/21/20 9:15 AM, David G. Johnston wrote:



On Saturday, November 21, 2020, Hagen Finley > wrote:



I pull a new forecast spreadsheet each Monday. 80% of the records
are the same as the existing records from the week before.

Here’s what I (REALLY) want:

Trigger looks at three fields prior to new insert: Deal ID
(numeric), revenue (numeric), stage(char)     Example: 19743576   
22072.37    Commit - 90%

 1. If the NEW dealid doesn't match any of the OLD dealids, insert
the new row
 2. if the NEW dealid, revenue and stage fields ALL match the OLD
dealid, revenue and stage, skip (don't insert the NEW row)
 3. If the NEW dealid matches an OLD dealid but either the NEW
revenue OR the stage fields have changed (don't match OLD
record) insert new row (I'll review both rows manually)


Am I anywhere close (same county) to the right code?



IMO, don’t use triggers.  Load the data into a temporary, or unlogged 
table, and then run commands to do what you want against the live 
tables.  Truncate/drop before doing that again the following week.


David J.

Re: INSERT Trigger to check for existing records

[Adrian Klaver]

On 11/21/20 9:47 AM, Hagen Finley wrote:

Thanks so much Adrian,

I like this approach but as you indicated it doesn't actually NULL the 
INSERT.


It should cause the INSERT not to happen if a row exists with the same 
values for ndealid, revusd and stage. Are you seeing an INSERT for those 
conditions?




Could we UPDATE the existing record (per my fledgling chk UPDATE and 
then RETURN NULL? (More proof I don't know what I am talking about ;-).


The INSERT won't happen so I'm not sure what you want to check against?



Hagen


On 11/21/20 10:11 AM, Adrian Klaver wrote:

On 11/21/20 8:47 AM, Adrian Klaver wrote:

On 11/21/20 8:20 AM, Adrian Klaver wrote:

On 11/21/20 8:00 AM, Hagen Finley wrote:

Hello,




Instead:

IF NEW.ndealid = OLD.ndealid AND NEW.revusd = OLD.revusd
 AND NEW.stage = OLD.stage THEN
 RETURN NULL; --Will cancel INSERT
ELSE
 RETURN NEW;

END IF;


Well this is what happens when I answer BC(before coffee). The above 
will not work, if for no other reason then OLD does not exist in an 
INSERT. Will try to come up with something that is in the realm of 
possibility.


Alright caffeine in the blood stream, so something that might actually 
work:


DECLARE
    match_ct integer;
BEGIN

    SELECT INTO
match_ct count(*)
    FROM
    sfdc
    WHERE
    ndealid = NEW.ndealid
    AND
    revusd = NEW.revusd
    AND
   stage = NEW.stage;

   IF match_ct > 0 THEN
   RETURN NULL; --Will cancel INSERT
   ELSE
   RETURN NEW;
   END IF;

END;

Though I would also point you at David's solution. Given that you are 
only looking at ~20% of the records being different it would save you 
a lot of churning through INSERTs.








Hagen

Larimer County, CO
















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

Re: INSERT Trigger to check for existing records

[David G. Johnston]

On Saturday, November 21, 2020, Hagen Finley  wrote:

> David,
>
> That's an interesting idea. I WOULD like  to retain the OLD records that
> are the same and only INSERT new or changed records. Is there a way to
> compare the old and the new records without a trigger?
>

A where clause?

David J.

Re: INSERT Trigger to check for existing records

[David G. Johnston]

On Saturday, November 21, 2020, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Saturday, November 21, 2020, Hagen Finley  wrote:
>
>> David,
>>
>> That's an interesting idea. I WOULD like  to retain the OLD records that
>> are the same and only INSERT new or changed records. Is there a way to
>> compare the old and the new records without a trigger?
>>
>
> A where clause?
>
>
Did you get the part in the plan where there are two tables, existing and
new? You write queries that join the two tables together and use the where
clause in those queries to limit records.

David J.

RE: INSERT Trigger to check for existing records

[hagen]

Yes but it didn’t sink in but the two table join idea does make sense – I’ll 
give that a try. THANK YOU.

 

From: David G. Johnston  
Sent: Saturday, November 21, 2020 11:25 AM
To: Hagen Finley 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: INSERT Trigger to check for existing records

 

On Saturday, November 21, 2020, David G. Johnston mailto:david.g.johns...@gmail.com> > wrote:

On Saturday, November 21, 2020, Hagen Finley mailto:ha...@datasundae.com> > wrote:

David,

That's an interesting idea. I WOULD like  to retain the OLD records that are 
the same and only INSERT new or changed records. Is there a way to compare the 
old and the new records without a trigger?

 

A where clause?

 

 

Did you get the part in the plan where there are two tables, existing and new? 
You write queries that join the two tables together and use the where clause in 
those queries to limit records.

 

David J.