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

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 th

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

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

Slow Query, many tmpfiles

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

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

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 c

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

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

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

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 func

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

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

Re: Restrict connection from pgadmin.

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 metho

Re: Restrict connection from pgadmin.

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

Re: Restrict connection from pgadmin.

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

Re: Restrict connection from pgadmin.

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 clos

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

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,

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

"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

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

Re: Slow Query, many tmpfiles

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 |

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

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 wit

Should I reinstall over current installation?

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 databa

Re: Should I reinstall over current installation?

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 o

Re: Should I reinstall over current installation?

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

Re: Should I reinstall over current installation?

*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

Re: Should I reinstall over current installation?

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

Re: Should I reinstall over current installation?

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

Re: Should I reinstall over current installation?

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 Windo

Postgres Crashing

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

Re: Postgres Crashing

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 cra

Re: Postgres Crashing

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 woul

Re: Postgres Crashing

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

Re: Should I reinstall over current installation?

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

Re: Postgres Crashing

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

Re: Should I reinstall over current installation?

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:

Re: Restrict connection from pgadmin.

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

Re: Restrict connection from pgadmin.

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 completel