Re: Restrict connection from pgadmin.

[Tom Lane]

Pawan Sharma  writes:
> Yes I will show the pgadmin in stat_activity but how  can block that..
> I think I need to create a job to terminate the pgadmin connections and
> schedule it for  every 5 min and so that I will check any new connections
> from pgadmin.

I think onlookers are still completely mystified as to why you consider
this a useful activity.

pgadmin is nothing but a GUI.  Whatever can be done through it can be
done equally well through psql, or any other client software.  So if
you're looking for security against unwanted SQL commands, you're going
about it the wrong way (better to look at preventing logins of privileged
accounts, and/or use of SQL permissions to limit what can be done).
If your objective is something else, you haven't explained what that is.

regards, tom lane

Re: Restrict connection from pgadmin.

[Pawan Sharma]

Yes I will show the pgadmin in stat_activity but how  can block that..

I think I need to create a job to terminate the pgadmin connections and
schedule it for  every 5 min and so that I will check any new connections
from pgadmin.

On Mon, 3 Feb 2020, 11:13 pm Adrian Klaver, 
wrote:

> On 2/3/20 9:15 AM, Ravi Krishna wrote:
> >>
> >> Not that I know of. pgAdmin is just another client so you just have the
> methods available here:
> >>
> >> https://www.postgresql.org/docs/12/auth-pg-hba-conf.html
> >>
> >
> > will the col application_name from pg_stat_activity list pgadmin as
> pgadmin. if yes, then the closest one can achieve
>
> Make a connection from pgAdmin and see.
>
> > is to have a daemon process wake up every 1 min or so and terminate any
> pgadmin app.
>
> I can do a lot of damage in a minute.
>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Should I reinstall over current installation?

[Adrian Klaver]

On 2/3/20 3:16 PM, Chris Charley wrote:

I tried items you suggested (1-5), but could find no helpful info.


Well in that case I would try a reinstall.



Thanks for your help and going the extra mile!

On Mon, Feb 3, 2020 at 4:23 PM Adrian Klaver > wrote:


On 2/3/20 1:11 PM, Chris Charley wrote:
 > Hi
 >
 > The link you provided
 >

(https://www.howtogeek.com/123646/htg-explains-what-the-windows-event-viewer-is-and-how-you-can-use-it/)
 >
 > Points me to Event Viewer but I'm kinda lost there trying to find
 > anything pertaining to postgreql.

I cranked up a Windows 7 instance, which should be close enough. What I
found:

1) Open Event Viewer

2) Click on Windows Logs

3) Click on Application

4) The center section will show logs. The application name is under
Source.

5) If you want to search, then Ctrl+f and enter Postgres

 >
 > On Mon, Feb 3, 2020 at 4:02 PM Adrian Klaver
mailto:adrian.kla...@aklaver.com>
 > >> wrote:
 >
 >     On 2/3/20 12:54 PM, Chris Charley wrote:
 >      > /What does the Windows system log show when you do this?/
 >      >
 >      > I don't know where to find this log (for Windows 10)
 >      >
 >
 >     I'm not a Windows user, what I can do is point you at:
 >
 >

https://www.howtogeek.com/123646/htg-explains-what-the-windows-event-viewer-is-and-how-you-can-use-it/
 >
 >
 >     --
 >     Adrian Klaver
 > adrian.kla...@aklaver.com 
>
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Postgres Crashing

[Tom Lane]

Adrian Klaver  writes:
> Please reply to list also.

> On 2/3/20 2:18 PM, Doug Roberts wrote:
>> Here is what the reset recirc function is doing.
>> ...
>> UPDATE containers
>> ...

> So how did containers_reset_recirc() come to clash with 
> containers_add_update()?

If this is PG 12.0 or 12.1, a likely theory is that this is an
EvalPlanQual bug (which'd be triggered during concurrent updates
of the same row in the table, so that squares with the observation
that locking the table prevents it).  The known bugs in that area
require either before-row-update triggers on the table, or
child tables (either partitioning or traditional inheritance).
So I wonder what the schema of table "containers" looks like.

Or you could have hit some new bug ... but there's not enough
info here to diagnose.

regards, tom lane

Re: Should I reinstall over current installation?

[Chris Charley]

I tried items you suggested (1-5), but could find no helpful info.

Thanks for your help and going the extra mile!

On Mon, Feb 3, 2020 at 4:23 PM Adrian Klaver 
wrote:

> On 2/3/20 1:11 PM, Chris Charley wrote:
> > Hi
> >
> > The link you provided
> > (
> https://www.howtogeek.com/123646/htg-explains-what-the-windows-event-viewer-is-and-how-you-can-use-it/
> )
> >
> > Points me to Event Viewer but I'm kinda lost there trying to find
> > anything pertaining to postgreql.
>
> I cranked up a Windows 7 instance, which should be close enough. What I
> found:
>
> 1) Open Event Viewer
>
> 2) Click on Windows Logs
>
> 3) Click on Application
>
> 4) The center section will show logs. The application name is under Source.
>
> 5) If you want to search, then Ctrl+f and enter Postgres
>
> >
> > On Mon, Feb 3, 2020 at 4:02 PM Adrian Klaver  > > wrote:
> >
> > On 2/3/20 12:54 PM, Chris Charley wrote:
> >  > /What does the Windows system log show when you do this?/
> >  >
> >  > I don't know where to find this log (for Windows 10)
> >  >
> >
> > I'm not a Windows user, what I can do is point you at:
> >
> >
> https://www.howtogeek.com/123646/htg-explains-what-the-windows-event-viewer-is-and-how-you-can-use-it/
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Postgres Crashing

[Adrian Klaver]

On 2/3/20 2:18 PM, Doug Roberts wrote:
Please reply to list also.
Ccing list.

Adrian,

Here is what the reset recirc function is doing.

CREATE OR REPLACE FUNCTION containers_reset_recirc
(
     in_uid INTEGER
)
RETURNS INTEGER
AS $BODY$
     DECLARE regex VARCHAR(50);
BEGIN
     SELECT concat(',*', in_uid, '=\d+,*') INTO regex;

     LOCK TABLE containers IN SHARE ROW EXCLUSIVE MODE;

     UPDATE containers
         SET recirculation_count =
             case
                 when substring(recirculation_count, regex) like ',%,' then
                     regexp_replace(recirculation_count, regex, ',')
                 else
                     regexp_replace(recirculation_count, regex, '')
                 end;

     RETURN in_uid;
END;

Containers add/update is basically updating a specific container using 
the values that were passed to the function.


So how did containers_reset_recirc() come to clash with 
containers_add_update()?




UPDATE containers
     SET type_uid = COALESCE(declared_type_uid, type_uid),
         carton_type_uid = COALESCE(declared_carton_type_uid, 
carton_type_uid),

         status_uid = COALESCE(declared_status_uid, status_uid),
         order_uid = COALESCE(in_order_uid, order_uid),
         wave_uid = COALESCE(in_wave_uid, wave_uid),
         length = COALESCE(in_length, carton_length, length),
         width = COALESCE(in_width, carton_width, width),
         height = COALESCE(in_height, carton_height, height),
         weight = COALESCE(in_weight, weight),
         weight_minimum = COALESCE(in_weight_minimum, weight_minimum),
         weight_maximum = COALESCE(in_weight_maximum, weight_maximum),
         weight_expected = COALESCE(in_weight_expected, weight_expected),
         first_seen_decision_point_id = 
COALESCE(first_seen_decision_point_id, in_last_seen_decision_point_id),
         first_seen_datetime = COALESCE(first_seen_datetime, 
last_seen_date_time),
         last_seen_decision_point_id = 
COALESCE(in_last_seen_decision_point_id, last_seen_decision_point_id),
         last_seen_datetime = COALESCE(last_seen_date_time, 
last_seen_datetime),
         recirculation_count = COALESCE(in_recirculation_count, 
recirculation_count),

         project_flags = COALESCE(in_project_flags, project_flags),
         passed_weight_check = COALESCE(in_passed_weight_check, 
passed_weight_check)

     WHERE uid = in_uid

Thanks,

Doug

On Mon, Feb 3, 2020 at 4:49 PM Adrian Klaver > wrote:


On 2/3/20 1:43 PM, Doug Roberts wrote:
 > Hello,
 >
 > I'm having an issue where a process in Postgres is crashing and
cause
 > the server to go into recovery mode.
 >
 > I'm getting the following errors in the log.
 >
 > 2020-02-03 14:12:57.473 EST [11992] [0]WARNING:  57P02: terminating
 > connection because of crash of another server process
 > 2020-02-03 14:12:57.473 EST [11992] [0]DETAIL:  The postmaster has
 > commanded this server process to roll back the current
transaction and
 > exit, because another server process exited abnormally and possibly
 > corrupted shared memory.
 > 2020-02-03 14:12:57.473 EST [11992] [0]HINT:  In a moment you
should be
 > able to reconnect to the database and repeat your command.
 > 2020-02-03 14:12:57.473 EST [11992] [0]CONTEXT:  while locking tuple
 > (4101,2) in relation "containers"
 > SQL statement "UPDATE containers
 >             SET type_uid = COALESCE(declared_type_uid, type_uid),
 >                 carton_type_uid = COALESCE(declared_carton_type_uid,
 > carton_type_uid),
 >                 status_uid = COALESCE(declared_status_uid,
status_uid),
 >                 order_uid = COALESCE(in_order_uid, order_uid),
 >                 wave_uid = COALESCE(in_wave_uid, wave_uid),
 >                 length = COALESCE(in_length, carton_length, length),
 >                 width = COALESCE(in_width, carton_width, width),
 >                 height = COALESCE(in_height, carton_height, height),
 >                 weight = COALESCE(in_weight, weight),
 >                 weight_minimum = COALESCE(in_weight_minimum,
 > weight_minimum),
 >                 weight_maximum = COALESCE(in_weight_maximum,
 > weight_maximum),
 >                 weight_expected = COALESCE(in_weight_expected,
 > weight_expected),
 >                 first_seen_decision_point_id =
 > COALESCE(first_seen_decision_point_id,
in_last_seen_decision_point_id),
 >                 first_seen_datetime = COALESCE(first_seen_datetime,
 > last_seen_date_time),
 >                 last_seen_decision_point_id =
 > COALESCE(in_last_seen_decision_point_id,
last_seen_decision_point_id),
 >                 last_seen_datetime = COALESCE(last_seen_date_time,
 > last_seen_datetime),
 >                 recirculation_count =
COALESCE(in_recirculation_count,
 > 

Re: Postgres Crashing

[Tom Lane]

Doug Roberts  writes:
> I'm having an issue where a process in Postgres is crashing and cause the
> server to go into recovery mode.

Can you reduce this to a self-contained test case for others to try?

If not, you'll have to do the initial investigation yourself.
A stack trace from the crash would be pretty helpful:

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

We could also use all the standard details suggested in

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

most notably, exactly which PG version is this?

> I'm getting the following errors in the log.

Unfortunately, this is pretty useless, since you only quoted the part
of the log after the problem was detected.

regards, tom lane

Re: Postgres Crashing

[Adrian Klaver]

On 2/3/20 1:43 PM, Doug Roberts wrote:

Hello,

I'm having an issue where a process in Postgres is crashing and cause 
the server to go into recovery mode.


I'm getting the following errors in the log.

2020-02-03 14:12:57.473 EST [11992] [0]WARNING:  57P02: terminating 
connection because of crash of another server process
2020-02-03 14:12:57.473 EST [11992] [0]DETAIL:  The postmaster has 
commanded this server process to roll back the current transaction and 
exit, because another server process exited abnormally and possibly 
corrupted shared memory.
2020-02-03 14:12:57.473 EST [11992] [0]HINT:  In a moment you should be 
able to reconnect to the database and repeat your command.
2020-02-03 14:12:57.473 EST [11992] [0]CONTEXT:  while locking tuple 
(4101,2) in relation "containers"

SQL statement "UPDATE containers
            SET type_uid = COALESCE(declared_type_uid, type_uid),
                carton_type_uid = COALESCE(declared_carton_type_uid, 
carton_type_uid),

                status_uid = COALESCE(declared_status_uid, status_uid),
                order_uid = COALESCE(in_order_uid, order_uid),
                wave_uid = COALESCE(in_wave_uid, wave_uid),
                length = COALESCE(in_length, carton_length, length),
                width = COALESCE(in_width, carton_width, width),
                height = COALESCE(in_height, carton_height, height),
                weight = COALESCE(in_weight, weight),
                weight_minimum = COALESCE(in_weight_minimum, 
weight_minimum),
                weight_maximum = COALESCE(in_weight_maximum, 
weight_maximum),
                weight_expected = COALESCE(in_weight_expected, 
weight_expected),
                first_seen_decision_point_id = 
COALESCE(first_seen_decision_point_id, in_last_seen_decision_point_id),
                first_seen_datetime = COALESCE(first_seen_datetime, 
last_seen_date_time),
                last_seen_decision_point_id = 
COALESCE(in_last_seen_decision_point_id, last_seen_decision_point_id),
                last_seen_datetime = COALESCE(last_seen_date_time, 
last_seen_datetime),
                recirculation_count = COALESCE(in_recirculation_count, 
recirculation_count),

                project_flags = COALESCE(in_project_flags, project_flags),
                passed_weight_check = COALESCE(in_passed_weight_check, 
passed_weight_check)

            WHERE uid = in_uid"
PL/pgSQL function 
containers_add_update(integer,integer,integer,integer,integer,integer,double 
precision,double precision,double precision,double precision,double 
precision,double precision,double precision,integer,timestamp without 
time zone,character varying,bigint,boolean) line 60 at SQL statement




This happened when I was using a function to remove part of a comma 
delimited string while updating a row. The update could potentially 
touch every row in the table. The issue above occurred when a different 
update function was being executed on the same table.


The full content of containers_add_update() would be helpful as well as 
the content of the other function. If that is not possible some idea of 
the order in which they where run as well as where the LOCK TABLE below 
was inserted?




If I use the following lock this issue seems to be resolved. However, 
I'm not sure why the above issue occurred.


LOCK TABLE containers IN SHARE ROW EXCLUSIVE MODE;

Does anyone have any ideas?

Thanks,

Doug



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

Postgres Crashing

[Doug Roberts]

Hello,

I'm having an issue where a process in Postgres is crashing and cause the
server to go into recovery mode.

I'm getting the following errors in the log.

2020-02-03 14:12:57.473 EST [11992] [0]WARNING:  57P02: terminating
connection because of crash of another server process
2020-02-03 14:12:57.473 EST [11992] [0]DETAIL:  The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2020-02-03 14:12:57.473 EST [11992] [0]HINT:  In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-03 14:12:57.473 EST [11992] [0]CONTEXT:  while locking tuple
(4101,2) in relation "containers"
SQL statement "UPDATE containers
   SET type_uid = COALESCE(declared_type_uid, type_uid),
   carton_type_uid = COALESCE(declared_carton_type_uid,
carton_type_uid),
   status_uid = COALESCE(declared_status_uid, status_uid),
   order_uid = COALESCE(in_order_uid, order_uid),
   wave_uid = COALESCE(in_wave_uid, wave_uid),
   length = COALESCE(in_length, carton_length, length),
   width = COALESCE(in_width, carton_width, width),
   height = COALESCE(in_height, carton_height, height),
   weight = COALESCE(in_weight, weight),
   weight_minimum = COALESCE(in_weight_minimum, weight_minimum),
   weight_maximum = COALESCE(in_weight_maximum, weight_maximum),
   weight_expected = COALESCE(in_weight_expected,
weight_expected),
   first_seen_decision_point_id =
COALESCE(first_seen_decision_point_id, in_last_seen_decision_point_id),
   first_seen_datetime = COALESCE(first_seen_datetime,
last_seen_date_time),
   last_seen_decision_point_id =
COALESCE(in_last_seen_decision_point_id, last_seen_decision_point_id),
   last_seen_datetime = COALESCE(last_seen_date_time,
last_seen_datetime),
   recirculation_count = COALESCE(in_recirculation_count,
recirculation_count),
   project_flags = COALESCE(in_project_flags, project_flags),
   passed_weight_check = COALESCE(in_passed_weight_check,
passed_weight_check)
   WHERE uid = in_uid"
PL/pgSQL function
containers_add_update(integer,integer,integer,integer,integer,integer,double
precision,double precision,double precision,double precision,double
precision,double precision,double precision,integer,timestamp without time
zone,character varying,bigint,boolean) line 60 at SQL statement
2020-02-03 14:12:57.473 EST [11992] [0]LOCATION:  quickdie, postgres.c:2717
2020-02-03 14:12:57.473 EST [12260] [0]WARNING:  57P02: terminating
connection because of crash of another server process
2020-02-03 14:12:57.473 EST [12260] [0]DETAIL:  The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2020-02-03 14:12:57.473 EST [12260] [0]HINT:  In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-03 14:12:57.473 EST [12260] [0]LOCATION:  quickdie, postgres.c:2717
2020-02-03 14:12:57.476 EST [24552] [0]WARNING:  57P02: terminating
connection because of crash of another server process
2020-02-03 14:12:57.476 EST [24552] [0]DETAIL:  The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2020-02-03 14:12:57.476 EST [24552] [0]HINT:  In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-03 14:12:57.476 EST [24552] [0]LOCATION:  quickdie, postgres.c:2717
2020-02-03 14:12:57.479 EST [23844] [0]WARNING:  57P02: terminating
connection because of crash of another server process
2020-02-03 14:12:57.479 EST [23844] [0]DETAIL:  The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2020-02-03 14:12:57.479 EST [23844] [0]HINT:  In a moment you should be
able to reconnect to the database and repeat your command.
2020-02-03 14:12:57.479 EST [23844] [0]LOCATION:  quickdie, postgres.c:2717
2020-02-03 14:12:57.586 EST [25992] [0]FATAL:  57P03: the database system
is in recovery mode
2020-02-03 14:12:57.586 EST [25992] [0]LOCATION:  ProcessStartupPacket,
postmaster.c:2275
2020-02-03 14:12:57.587 EST [19428] [0]FATAL:  57P03: the database system
is in recovery mode
2020-02-03 14:12:57.587 EST [19428] [0]LOCATION:  ProcessStartupPacket,
postmaster.c:2275
2020-02-03 14:12:57.627 EST [24968] [0]LOG:  0: all server processes
terminated; reinitializing
2020-02-03 14:12:57.627 EST [24968] [0]LOCATION:  PostmasterStateMachine,
postmaster.c:3912
2020-02-03 14:12:57.697 EST [16620] [0]LOG:  0: database system 

Re: Should I reinstall over current installation?

[Adrian Klaver]

On 2/3/20 1:11 PM, Chris Charley wrote:

Hi

The link you provided 
(https://www.howtogeek.com/123646/htg-explains-what-the-windows-event-viewer-is-and-how-you-can-use-it/)


Points me to Event Viewer but I'm kinda lost there trying to find 
anything pertaining to postgreql.


I cranked up a Windows 7 instance, which should be close enough. What I 
found:


1) Open Event Viewer

2) Click on Windows Logs

3) Click on Application

4) The center section will show logs. The application name is under Source.

5) If you want to search, then Ctrl+f and enter Postgres



On Mon, Feb 3, 2020 at 4:02 PM Adrian Klaver > wrote:


On 2/3/20 12:54 PM, Chris Charley wrote:
 > /What does the Windows system log show when you do this?/
 >
 > I don't know where to find this log (for Windows 10)
 >

I'm not a Windows user, what I can do is point you at:


https://www.howtogeek.com/123646/htg-explains-what-the-windows-event-viewer-is-and-how-you-can-use-it/


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Should I reinstall over current installation?

[Chris Charley]

Hi

The link you provided (
https://www.howtogeek.com/123646/htg-explains-what-the-windows-event-viewer-is-and-how-you-can-use-it/
)

Points me to Event Viewer but I'm kinda lost there trying to find anything
pertaining to postgreql.

On Mon, Feb 3, 2020 at 4:02 PM Adrian Klaver 
wrote:

> On 2/3/20 12:54 PM, Chris Charley wrote:
> > /What does the Windows system log show when you do this?/
> >
> > I don't know where to find this log (for Windows 10)
> >
>
> I'm not a Windows user, what I can do is point you at:
>
>
> https://www.howtogeek.com/123646/htg-explains-what-the-windows-event-viewer-is-and-how-you-can-use-it/
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Should I reinstall over current installation?

[Adrian Klaver]

On 2/3/20 12:54 PM, Chris Charley wrote:

/What does the Windows system log show when you do this?/

I don't know where to find this log (for Windows 10)



I'm not a Windows user, what I can do is point you at:

https://www.howtogeek.com/123646/htg-explains-what-the-windows-event-viewer-is-and-how-you-can-use-it/


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

Re: Should I reinstall over current installation?

[Chris Charley]

*What does the Windows system log show when you do this?*

I don't know where to find this log (for Windows 10)

On Mon, Feb 3, 2020 at 3:47 PM Adrian Klaver 
wrote:

> On 2/3/20 12:44 PM, Chris Charley wrote:
> Please reply to list also.
> Ccing list.
> > Adrian
> >
> > /Does Postgres still exist on the system?
> >
> > If it does have you started the service?/
> >
> > PostgreSQL-x64-12 is list as 'stopped' in the task manger services tab.
> > When I try to 'start', it doesn't change to 'running'.
>
> What does the Windows system log show when you do this?
>
>
> >
> >
> > On Mon, Feb 3, 2020 at 3:26 PM Adrian Klaver  > > wrote:
> >
> > On 2/3/20 12:20 PM, Chris Charley wrote:
> >  > I just got my Windows 10 computer in Nov 2019 and installed
> > PostgreSQL
> >  > Jan this year.
> >  >
> >  > I had a problem with Windows update and when I contacted
> > Microsoft, they
> >  > installed a fresh operating system. After that, I was unable to
> run
> >  > postgres from pgAdmin 4 or from a perl program that accessed the
> > database.
> >  >
> >  > The message I got was:
> >  >
> >  > could not connect to server: Connection refused
> > (0x274D/10061) Is
> >  > the server running on host "localhost" (127.0.0.1) and accepting
> > TCP/IP
> >  > connections on port 5432? at pg_01.pl 
> >  line 8.
> >  > **//___^
> >
> > Does Postgres still exist on the system?
> >
> > If it does have you started the service?
> >
> >  > I  was considering 2 options:
> >  >
> >  > !) Reinstall postgres over the current without removing the
> current
> >  > postgres first.
> >  > 2) uninstall the current postgres and then install postgres again.
> >  >
> >  > The tables I created in my database are not many and if I lose
> them
> >  > doing a reinstall, I could recreate my database and the tables. I
> > was
> >  > considering option (1) as I was wondering if that choice would
> > keep the
> >  > database and tables I created this January.
> >  >
> >  > (The database and contained tables are from 'Practical SQL' by
> > Anthony
> >  > DeBarros.)
> >  >
> >  > A second question is: When I try to edit anything in pgAdmin,
> > sometimes
> >  > it won't let me edit the code. I then refresh the database in the
> > left
> >  > pane and then it allows me to edit the SQL in the query pane.
> >  >
> >  > Thank you for any help you may provide.
> >  >
> >  > Chris
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Should I reinstall over current installation?

[Adrian Klaver]

On 2/3/20 12:44 PM, Chris Charley wrote:
Please reply to list also.
Ccing list.

Adrian

/Does Postgres still exist on the system?

If it does have you started the service?/

PostgreSQL-x64-12 is list as 'stopped' in the task manger services tab. 
When I try to 'start', it doesn't change to 'running'.


What does the Windows system log show when you do this?





On Mon, Feb 3, 2020 at 3:26 PM Adrian Klaver > wrote:


On 2/3/20 12:20 PM, Chris Charley wrote:
 > I just got my Windows 10 computer in Nov 2019 and installed
PostgreSQL
 > Jan this year.
 >
 > I had a problem with Windows update and when I contacted
Microsoft, they
 > installed a fresh operating system. After that, I was unable to run
 > postgres from pgAdmin 4 or from a perl program that accessed the
database.
 >
 > The message I got was:
 >
 > could not connect to server: Connection refused
(0x274D/10061) Is
 > the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP
 > connections on port 5432? at pg_01.pl 
 line 8.
 > **//___^

Does Postgres still exist on the system?

If it does have you started the service?

 > I  was considering 2 options:
 >
 > !) Reinstall postgres over the current without removing the current
 > postgres first.
 > 2) uninstall the current postgres and then install postgres again.
 >
 > The tables I created in my database are not many and if I lose them
 > doing a reinstall, I could recreate my database and the tables. I
was
 > considering option (1) as I was wondering if that choice would
keep the
 > database and tables I created this January.
 >
 > (The database and contained tables are from 'Practical SQL' by
Anthony
 > DeBarros.)
 >
 > A second question is: When I try to edit anything in pgAdmin,
sometimes
 > it won't let me edit the code. I then refresh the database in the
left
 > pane and then it allows me to edit the SQL in the query pane.
 >
 > Thank you for any help you may provide.
 >
 > Chris


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Should I reinstall over current installation?

[Adrian Klaver]

On 2/3/20 12:20 PM, Chris Charley wrote:
I just got my Windows 10 computer in Nov 2019 and installed PostgreSQL 
Jan this year.


I had a problem with Windows update and when I contacted Microsoft, they 
installed a fresh operating system. After that, I was unable to run 
postgres from pgAdmin 4 or from a perl program that accessed the database.


The message I got was:

could not connect to server: Connection refused (0x274D/10061) Is 
the server running on host "localhost" (127.0.0.1) and accepting TCP/IP 
connections on port 5432? at pg_01.pl  line 8.

**//___^


Does Postgres still exist on the system?

If it does have you started the service?


I  was considering 2 options:

!) Reinstall postgres over the current without removing the current 
postgres first.

2) uninstall the current postgres and then install postgres again.

The tables I created in my database are not many and if I lose them 
doing a reinstall, I could recreate my database and the tables. I was 
considering option (1) as I was wondering if that choice would keep the 
database and tables I created this January.


(The database and contained tables are from 'Practical SQL' by Anthony 
DeBarros.)


A second question is: When I try to edit anything in pgAdmin, sometimes 
it won't let me edit the code. I then refresh the database in the left 
pane and then it allows me to edit the SQL in the query pane.


Thank you for any help you may provide.

Chris



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

Should I reinstall over current installation?

[Chris Charley]

I just got my Windows 10 computer in Nov 2019 and installed PostgreSQL Jan
this year.

I had a problem with Windows update and when I contacted Microsoft, they
installed a fresh operating system. After that, I was unable to run
postgres from pgAdmin 4 or from a perl program that accessed the database.

The message I got was:

could not connect to server: Connection refused (0x274D/10061) Is the
server running on host "localhost" (127.0.0.1) and accepting TCP/IP
connections on port 5432? at pg_01.pl line 8.

I  was considering 2 options:

!) Reinstall postgres over the current without removing the current
postgres first.
2) uninstall the current postgres and then install postgres again.

The tables I created in my database are not many and if I lose them doing a
reinstall, I could recreate my database and the tables. I was considering
option (1) as I was wondering if that choice would keep the database and
tables I created this January.

(The database and contained tables are from 'Practical SQL' by Anthony
DeBarros.)

A second question is: When I try to edit anything in pgAdmin, sometimes it
won't let me edit the code. I then refresh the database in the left pane
and then it allows me to edit the SQL in the query pane.

Thank you for any help you may provide.

Chris

Re: performance of loading CSV data with COPY is 50 times faster than Perl::DBI

[Ron]

On 2/3/20 12:42 PM, Ravi Krishna wrote:

"already suffering from a complex of coding in an unreadable language"

hearsay and conjecture


I was somewhat facetious. But what finished perl was the reputation it earned 
that it is a write only
language.  I don't think that reputation it earned is without merit.

IMO the early adopters of perl  were sysadmins and other non programmers who 
didn't do a stellar
job in writing a highly readable code.  To make it worse, perl folks take it as 
a badge of honor to
write their code in most obfuscated manner.  They even get some recognition for 
that.


No need for an /Obfuscated Perl/ contest, because it's already obfuscated...

--
Angular momentum makes the world go 'round.

Re: Slow Query, many tmpfiles

[Tom Lane]

basti  writes:
> What I have found is this:

> postgres=# SELECT temp_files AS "Temporary files"
>  , temp_bytes AS "Size of temporary files"
> FROM   pg_stat_database db;
>  Temporary files | Size of temporary files
> -+-
>0 |   0
>0 |   0
>0 |   0
>0 |   0
>13542 |   5313912899891
> (5 rows)

> postgres@dolly:~/9.6/main/base/pgsql_tmp$ ls -la
> total 20
> drwx-- 2 postgres postgres 16384 Feb  3 02:25 .
> drwx-- 8 postgres postgres  4096 Jul 26  2017 ..

> Where does the 13542 tmp files are come from?
> How can I delete that?
> Where are they stored?

Those are temp files that have been created during queries since you last
reset the statistics counters.  By the time they're counted in this view,
they've already been deleted, so there's nothing for you to do here
maintenance-wise.  But if the numbers seem to be going up a lot over time,
that might suggest that you look for the queries causing it.

regards, tom lane

Re: performance of loading CSV data with COPY is 50 times faster than Perl::DBI

[Ravi Krishna]

> 
> "already suffering from a complex of coding in an unreadable language"
> 
> hearsay and conjecture
> 

I was somewhat facetious. But what finished perl was the reputation it earned 
that it is a write only
language.  I don't think that reputation it earned is without merit.

IMO the early adopters of perl  were sysadmins and other non programmers who 
didn't do a stellar
job in writing a highly readable code.  To make it worse, perl folks take it as 
a badge of honor to
write their code in most obfuscated manner.  They even get some recognition for 
that.

Re: performance of loading CSV data with COPY is 50 times faster than Perl::DBI

[bret_stern]

"already suffering from a complex of coding in an unreadable language"

hearsay and conjecture

On 2/3/2020 8:57 AM, Ravi Krishna wrote:

already
suffering from a complex of coding in an unreadable language

Re: performance of loading CSV data with COPY is 50 times faster than Perl::DBI

[Matthias Apitz]

El día lunes, febrero 03, 2020 a las 10:01:04a. m. -0600, Steven Lembark 
escribió:

> On Fri, 31 Jan 2020 19:24:41 +0100
> Matthias Apitz  wrote:
> 
> > Hello,
> > 
> > Since ages, we transfer data between different DBS (Informix, Sybase,
> > Oracle, and now PostgreSQL) with our own written tool, based on
> > Perl::DBI which produces a CSV like export in a common way, i.e. an
> > export of Oracle can be loaded into Sybase and vice versa. Export and
> > Import is done row by row, for some tables millions of rows.
> > 
> > We produced a special version of the tool to export the rows into a
> > format which understands the PostgreSQL's COPY command and got to know
> > that the import into PostgreSQL of the same data with COPY is 50 times
> > faster than with Perl::DBI, 2.5 minutes ./. 140 minutes for around 6
> > million rows into an empty table without indexes.
> > 
> > How can COPY do this so fast?
> 
> DBI is a wonderful tool, but not intended for bulk transfer. It
> is useful for post-processing queries that extract specific
> data in ways that SQL cannot readily handle. 
> 
> One big slowdown is the cycle of pull-a-row, push-a-row involves 
> signiicant latency due to database connections. That limits the 
> throughput.

I should have mentioned this: the export is done on Linux to file and
the import with that tool is read from such files.

matthias


-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: Restrict connection from pgadmin.

[Adrian Klaver]

On 2/3/20 9:15 AM, Ravi Krishna wrote:


Not that I know of. pgAdmin is just another client so you just have the methods 
available here:

https://www.postgresql.org/docs/12/auth-pg-hba-conf.html



will the col application_name from pg_stat_activity list pgadmin as pgadmin. if 
yes, then the closest one can achieve


Make a connection from pgAdmin and see.


is to have a daemon process wake up every 1 min or so and terminate any pgadmin 
app.


I can do a lot of damage in a minute.




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

Re: Restrict connection from pgadmin.

[Ravi Krishna]

> 
> Not that I know of. pgAdmin is just another client so you just have the 
> methods available here:
> 
> https://www.postgresql.org/docs/12/auth-pg-hba-conf.html
> 

will the col application_name from pg_stat_activity list pgadmin as pgadmin. if 
yes, then the closest one can achieve
is to have a daemon process wake up every 1 min or so and terminate any pgadmin 
app.

Re: Restrict connection from pgadmin.

[David G. Johnston]

On Sun, Feb 2, 2020 at 11:48 PM Pawan Sharma  wrote:

> Hi All,
>
> Is there any way to restrict the connection only from pgadmin???  The same
> user can connect through application but can't from pgadmin.. Or any logon
> triggers..???
>

Why just pgAdmin?  Why not psql?  How do you expect to know the difference?
Its all the same protocol going back-and-forth no matter what kind of user
interface you provide to the user.

You can make it difficult for the user to gain direct access from their
machine while allowing a local application to do so, but I'm doubtful it
can be made hacker-proof.  Specifically the user can be assumed to be
allowed to attempt a connection but if they lack valid credentials they
will be unable to get in.  If you need connection-level security you want
to host the application on a separate machine that does have access to
PostgreSQL and only provide user interface access to the application to the
user.  As they are never truly on the machine that has access to the
database they will be unable to establish their own connection.

You can log and monitor logon attempts but there is no server level
triggers.  Again, the client sends along all of the validation information
and so can make up values for any parameters you might wish to check so
that they look like, i.e., your application as far as the authentication
code is concerned.

David J.

Re: Restrict connection from pgadmin.

[Adrian Klaver]

On 2/2/20 10:47 PM, Pawan Sharma wrote:

Hi All,

Is there any way to restrict the connection only from pgadmin???  The 
same user can connect through application but can't from pgadmin.. Or 
any logon triggers..???




Not that I know of. pgAdmin is just another client so you just have the 
methods available here:


https://www.postgresql.org/docs/12/auth-pg-hba-conf.html

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

Re: performance of loading CSV data with COPY is 50 times faster than Perl::DBI

[Ravi Krishna]

> 
> Depending on who wrote the code, they may have extracted the rows
> as hashrefs rather than arrays; that can be a 10x slowdown right
> there. [I have no idea why so many people are so addicted to storing
> rows in hashes, but it is always a significant slowdown; and 
> array slices are no more complicated than hash slices!]


I have not done perl code for a while now, but most perl coders, already
suffering from a complex of coding in an unreadable language do not
prefer to make it worse by using array which is position dependent
and hence reading it can be a nightmare when large number of cols are
selected.

Also isn't array_ref even better than array, since it avoids copying the data
to your local array in the code.

Re: How to avoid UPDATE on same data in table ?

[Condor]

On 03-02-2020 10:29, Andrei Zhidenkov wrote:

Have you tried to use built-in suppress_redundant_updates_trigger[1]
for this?

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



No,
solution point me Adrian Klaver it's working for me. I look at page, but 
this probably is build in function and can't be changed.
In my case I need to change lastchange column to know which row is 
changed and when.


Regards,
HS

Re: performance of loading CSV data with COPY is 50 times faster than Perl::DBI

[Steven Lembark]

> 2. postgres has not implicit plan cache, so every INSERT planned
> again and again

Good point: If you are doing something in a loop you probably
want to use prepare_cached() to get the $sth, for both extract
and insert.


-- 
Steven Lembark3646 Flora Place
Workhorse ComputingSt. Louis, MO 63110
lemb...@wrkhors.com+1 888 359 3508

Re: performance of loading CSV data with COPY is 50 times faster than Perl::DBI

[Steven Lembark]

On Fri, 31 Jan 2020 19:24:41 +0100
Matthias Apitz  wrote:

> Hello,
> 
> Since ages, we transfer data between different DBS (Informix, Sybase,
> Oracle, and now PostgreSQL) with our own written tool, based on
> Perl::DBI which produces a CSV like export in a common way, i.e. an
> export of Oracle can be loaded into Sybase and vice versa. Export and
> Import is done row by row, for some tables millions of rows.
> 
> We produced a special version of the tool to export the rows into a
> format which understands the PostgreSQL's COPY command and got to know
> that the import into PostgreSQL of the same data with COPY is 50 times
> faster than with Perl::DBI, 2.5 minutes ./. 140 minutes for around 6
> million rows into an empty table without indexes.
> 
> How can COPY do this so fast?

DBI is a wonderful tool, but not intended for bulk transfer. It
is useful for post-processing queries that extract specific
data in ways that SQL cannot readily handle. 

One big slowdown is the cycle of pull-a-row, push-a-row involves 
signiicant latency due to database connections. That limits the 
throughput.

Depending on who wrote the code, they may have extracted the rows
as hashrefs rather than arrays; that can be a 10x slowdown right
there. [I have no idea why so many people are so addicted to storing
rows in hashes, but it is always a significant slowdown; and 
array slices are no more complicated than hash slices!]

Where DBI is really nice is managing the copy: generating a 
useful basename, determining the path, deciding whether to zip
the output, etc. Using metadata to determine which of the tables
to back up and where to put the result, all of the automation 
you'd want to get flexible backups is nice in DBI. Bulk copies, 
probably not.

-- 
Steven Lembark3646 Flora Place
Workhorse ComputingSt. Louis, MO 63110
lemb...@wrkhors.com+1 888 359 3508

Slow Query, many tmpfiles

[basti]

Hello, some simple query are very slow since a few days, no significant
changes are done the last days. I look for disk-io, swap, looked tables,
all seems to be good.

What I have found is this:

postgres=# SELECT temp_files AS "Temporary files"
 , temp_bytes AS "Size of temporary files"
FROM   pg_stat_database db;
 Temporary files | Size of temporary files
-+-
   0 |   0
   0 |   0
   0 |   0
   0 |   0
   13542 |   5313912899891
(5 rows)

postgres@dolly:~/9.6/main/base/pgsql_tmp$ ls -la
total 20
drwx-- 2 postgres postgres 16384 Feb  3 02:25 .
drwx-- 8 postgres postgres  4096 Jul 26  2017 ..

Where does the 13542 tmp files are come from?
How can I delete that?
Where are they stored?

Re: How to avoid UPDATE on same data in table ?

[Andrei Zhidenkov]

Have you tried to use built-in suppress_redundant_updates_trigger[1] for this?

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


> On 3. Feb 2020, at 09:27, Condor  wrote:
> 
> On 02-02-2020 23:06, Adrian Klaver wrote:
>> On 2/2/20 1:24 AM, Condor wrote:
>>> Hello,
>>> I'm using PostgreSQL 12.1 and trying to avoid update on table when data is 
>>> the same. I read somewhere if UPDATE is with the same data SQL server on 
>>> system level does not do update on table but don't know if that is true or 
>>> not. If that is not true I do:
>>> First I create a function that should update data:
>>> CREATE OR REPLACE FUNCTION log_last_chaged() RETURNS TRIGGER
>>> LANGUAGE plpgsql
>>> AS $$
>>> BEGIN
>>> UPDATE status_table SET status0 = NEW.status0, lastchage = 
>>> CURRENT_TIMESTAMP WHERE rowid = OLD.rowid;
>>> RETURN NEW;
>>> END
>>> $$;
>>> then create table:
>>> CREATE TABLE status_table (
>>> rowid INTEGER,
>>> status0 INTEGER,
>>> lastchage TIMESTAMP(0) WITHOUT TIME ZONE
>>> );
>>> attach trigger:
>> Why the DROP TRIGGER on card_sync_tbl?
>> More below.
>>> DROP TRIGGER last_changes ON card_sync_tbl;
>>> CREATE TRIGGER last_changes
>>>   BEFORE UPDATE ON status_table
>>>   FOR EACH ROW
>>>   WHEN (OLD.* IS DISTINCT FROM NEW.*)
>>>   EXECUTE FUNCTION log_last_chaged();
>>> insert first data:
>>> INSERT INTO status_table (rowid, status0) VALUES (11, 1);
>>> INSERT INTO status_table (rowid, status0) VALUES (12, 2);
>>> and check do everything work fine:
>>> UPDATE status_table SET status0 = 1 WHERE rowid = 11;
>>> UPDATE status_table SET status0 = 4 WHERE rowid = 12;
>>> I receive something on rowid 12 that probably is error:
>>> SQL statement "UPDATE status_table SET status0 = NEW.status0, lastchage = 
>>> CURRENT_TIMESTAMP WHERE rowid = OLD.rowid"
>>> PL/pgSQL function log_last_chaged() line 3 at SQL statement
>>> After quick look on duckduckgo I change the function to this:
>>> CREATE OR REPLACE FUNCTION log_last_chaged() RETURNS TRIGGER
>>> LANGUAGE plpgsql
>>> AS $$
>>> BEGIN
>>> NEW.lastchage := CURRENT_TIMESTAMP;
>>> RETURN NEW;
>>> END
>>> $$;
>>> and everything seems work now, but that break the idea update not to hit 
>>> table if data is the same.
>> Some changes based on:
>> https://www.postgresql.org/docs/12/sql-createtrigger.html
>> "In a BEFORE trigger, the WHEN condition is evaluated just before the
>> function is or would be executed, so using WHEN is not materially
>> different from testing the same condition at the beginning of the
>> trigger function. Note in particular that the NEW row seen by the
>> condition is the current value, as possibly modified by earlier
>> triggers. "
>> https://www.postgresql.org/docs/12/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER
>> "Row-level triggers fired BEFORE can return null to signal the trigger
>> manager to skip the rest of the operation for this row (i.e.,
>> subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does
>> not occur for this row)."
>> CREATE OR REPLACE FUNCTION public.log_last_chaged()
>> RETURNS trigger
>> LANGUAGE plpgsql
>> AS $function$
>> BEGIN
>>IF OLD.* IS DISTINCT FROM NEW.* THEN
>>RAISE NOTICE 'UPDATE';
>>NEW.lastchage := CURRENT_TIMESTAMP;
>>RETURN NEW;
>>ELSE
>>RETURN NULL;
>>END IF;
>> END
>> $function$
>> CREATE TRIGGER last_changes
>>  BEFORE UPDATE ON status_table
>>  FOR EACH ROW
>>  EXECUTE FUNCTION log_last_chaged();
>> test=> INSERT INTO status_table (rowid, status0) VALUES (11, 1);
>> INSERT 0 1
>> test=> INSERT INTO status_table (rowid, status0) VALUES (12, 2);
>> INSERT 0 1
>> test=> select ctid, * from status_table ;
>> ctid  | rowid | status0 | lastchage
>> ---+---+-+---
>> (0,1) |11 |   1 |
>> (0,2) |12 |   2 |
>> (2 rows)
>> test=> UPDATE status_table SET status0 = 1 WHERE rowid = 11;
>> UPDATE 0
>> test=> select ctid, * from status_table ;
>> ctid  | rowid | status0 | lastchage
>> ---+---+-+---
>> (0,1) |11 |   1 |
>> (0,2) |12 |   2 |
>> (2 rows)
>> NOTE: UPDATE 0 and no change in ctid
>> test=> UPDATE status_table SET status0 = 4 WHERE rowid = 12;
>> NOTICE:  UPDATE
>> UPDATE 1
>> test=> select ctid, * from status_table ;
>> ctid  | rowid | status0 |  lastchage
>> ---+---+-+-
>> (0,1) |11 |   1 |
>> (0,3) |12 |   4 | 02/02/2020 13:03:21
>> (2 rows)
>> NOTE: UPDATE 1 and ctid change.
>>> Any body can help with some hint ? Also I want to know why my first 
>>> function does not work, probably loop is happened if trigger does not stop 
>>> update to be sent to table on rowid 12 or syntax error.
>>> Regards,
>>> HS
> 
> Thank you for detailed explanation.
> Have a good day.
> 
> Regards,
> HS
> 
> 

Re: How to avoid UPDATE on same data in table ?

[Condor]

On 02-02-2020 23:06, Adrian Klaver wrote:

On 2/2/20 1:24 AM, Condor wrote:


Hello,

I'm using PostgreSQL 12.1 and trying to avoid update on table when 
data is the same. I read somewhere if UPDATE is with the same data SQL 
server on system level does not do update on table but don't know if 
that is true or not. If that is not true I do:


First I create a function that should update data:
CREATE OR REPLACE FUNCTION log_last_chaged() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
     UPDATE status_table SET status0 = NEW.status0, lastchage = 
CURRENT_TIMESTAMP WHERE rowid = OLD.rowid;

     RETURN NEW;
END
$$;

then create table:
CREATE TABLE status_table (
     rowid INTEGER,
     status0 INTEGER,
     lastchage TIMESTAMP(0) WITHOUT TIME ZONE
);

attach trigger:


Why the DROP TRIGGER on card_sync_tbl?

More below.


DROP TRIGGER last_changes ON card_sync_tbl;
CREATE TRIGGER last_changes
   BEFORE UPDATE ON status_table
   FOR EACH ROW
   WHEN (OLD.* IS DISTINCT FROM NEW.*)
   EXECUTE FUNCTION log_last_chaged();

insert first data:
INSERT INTO status_table (rowid, status0) VALUES (11, 1);
INSERT INTO status_table (rowid, status0) VALUES (12, 2);

and check do everything work fine:
UPDATE status_table SET status0 = 1 WHERE rowid = 11;
UPDATE status_table SET status0 = 4 WHERE rowid = 12;

I receive something on rowid 12 that probably is error:
SQL statement "UPDATE status_table SET status0 = NEW.status0, 
lastchage = CURRENT_TIMESTAMP WHERE rowid = OLD.rowid"

PL/pgSQL function log_last_chaged() line 3 at SQL statement

After quick look on duckduckgo I change the function to this:
CREATE OR REPLACE FUNCTION log_last_chaged() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
     NEW.lastchage := CURRENT_TIMESTAMP;
     RETURN NEW;
END
$$;

and everything seems work now, but that break the idea update not to 
hit table if data is the same.


Some changes based on:

https://www.postgresql.org/docs/12/sql-createtrigger.html
"In a BEFORE trigger, the WHEN condition is evaluated just before the
function is or would be executed, so using WHEN is not materially
different from testing the same condition at the beginning of the
trigger function. Note in particular that the NEW row seen by the
condition is the current value, as possibly modified by earlier
triggers. "

https://www.postgresql.org/docs/12/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER

"Row-level triggers fired BEFORE can return null to signal the trigger
manager to skip the rest of the operation for this row (i.e.,
subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does
not occur for this row)."

CREATE OR REPLACE FUNCTION public.log_last_chaged()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
IF OLD.* IS DISTINCT FROM NEW.* THEN
RAISE NOTICE 'UPDATE';
NEW.lastchage := CURRENT_TIMESTAMP;
RETURN NEW;
ELSE
RETURN NULL;
END IF;
END
$function$


CREATE TRIGGER last_changes
  BEFORE UPDATE ON status_table
  FOR EACH ROW
  EXECUTE FUNCTION log_last_chaged();

test=> INSERT INTO status_table (rowid, status0) VALUES (11, 1);
INSERT 0 1
test=> INSERT INTO status_table (rowid, status0) VALUES (12, 2);
INSERT 0 1
test=> select ctid, * from status_table ;
 ctid  | rowid | status0 | lastchage
---+---+-+---
 (0,1) |11 |   1 |
 (0,2) |12 |   2 |
(2 rows)

test=> UPDATE status_table SET status0 = 1 WHERE rowid = 11;
UPDATE 0
test=> select ctid, * from status_table ;
 ctid  | rowid | status0 | lastchage
---+---+-+---
 (0,1) |11 |   1 |
 (0,2) |12 |   2 |
(2 rows)

NOTE: UPDATE 0 and no change in ctid

test=> UPDATE status_table SET status0 = 4 WHERE rowid = 12;
NOTICE:  UPDATE
UPDATE 1
test=> select ctid, * from status_table ;
 ctid  | rowid | status0 |  lastchage
---+---+-+-
 (0,1) |11 |   1 |
 (0,3) |12 |   4 | 02/02/2020 13:03:21
(2 rows)

NOTE: UPDATE 1 and ctid change.

Any body can help with some hint ? Also I want to know why my first 
function does not work, probably loop is happened if trigger does not 
stop update to be sent to table on rowid 12 or syntax error.


Regards,
HS




Thank you for detailed explanation.
Have a good day.

Regards,
HS