Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On 9/23/19 5:28 PM, Corey Taylor wrote: On Mon, Sep 23, 2019 at 7:23 PM Adrian Klaver > wrote: "Once restored, it is wise to run ANALYZE on each restored table so the optimizer has useful statistics; see Section 24.1.3 and Section 24.1.6 for more inf

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On Mon, Sep 23, 2019 at 7:23 PM Adrian Klaver wrote: > "Once restored, it is wise to run ANALYZE on each restored table so the > optimizer has useful statistics; see Section 24.1.3 and Section 24.1.6 > for more information." > > So is there some other step in the process that occurs after the res

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On 9/23/19 3:56 PM, Corey Taylor wrote: On Mon, Sep 23, 2019 at 5:51 PM Adrian Klaver > wrote: Usually what is seen here is the opposite, that tables are restored and ANALYZE is not run and performance on the subsequent queries is poor due to lac

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On Mon, Sep 23, 2019 at 5:51 PM Adrian Klaver wrote: > Usually what is seen here is the opposite, that tables are restored and > ANALYZE is not run and performance on the subsequent queries is poor due > to lack of current statistics. > > What is the restore process? > For these specific legacy

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On 9/23/19 3:43 PM, Corey Taylor wrote: On Mon, Sep 23, 2019 at 4:50 PM Adrian Klaver > wrote: Smaller hammer: https://www.postgresql.org/docs/11/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS "autovacuum_enabled, toast.autovacuum_enabled

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On Mon, Sep 23, 2019 at 4:50 PM Adrian Klaver wrote: > Smaller hammer: > > > https://www.postgresql.org/docs/11/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS > > "autovacuum_enabled, toast.autovacuum_enabled (boolean)" > > which can be done by ALTER TABLE also: > > https://www.postgresq

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On 9/23/19 2:34 PM, Corey Taylor wrote: On Mon, Sep 23, 2019 at 4:31 PM Adrian Klaver > wrote: Hmm, are there triggers on wss_entries that are UPDATEing/DELETEing entries elsewhere? No, that table is pretty much stand-alone.  What we're seeing here is

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On Mon, Sep 23, 2019 at 4:31 PM Adrian Klaver wrote: > Hmm, are there triggers on wss_entries that are UPDATEing/DELETEing > entries elsewhere? > No, that table is pretty much stand-alone. What we're seeing here is most likely caused by the initial copy of the legacy db tables. This happens wh

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On 9/23/19 1:58 PM, Corey Taylor wrote: On Mon, Sep 23, 2019 at 8:57 AM Tom Lane > wrote: Maybe check for waiting on a lock? It'd be useful to look in pg_stat_activity and/or top(1) while the initial query is running, to see if it seems to be eating CPU or

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On Mon, Sep 23, 2019 at 9:22 AM Adrian Klaver wrote: > Can we see the actual function/query? > > Also the schema of the table(s) involved? > Of course. This was added to pull some data from legacy db tables into something we could query while converting the process that populates the legacy db.

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On Mon, Sep 23, 2019 at 8:57 AM Tom Lane wrote: > Maybe check for waiting on a lock? > It'd be useful to look in pg_stat_activity and/or top(1) while the > initial query is running, to see if it seems to be eating CPU or > is blocked on some condition. > I think this will provide information tha

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On 9/23/19 12:07 AM, Corey Taylor wrote: First thing I'd look at is whether it's the same execution environment in both cases, eg same search_path. As far as I can tell, it's the same execution environment, same search_path and same user. I found after testing other situations, that

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

Corey Taylor writes: > I found after testing other situations, that the psql command would always > finish as expected after canceling the first query that ran too long. I > was able to reproduce this scenario with psql and pgadmin4 with various > combinations. Well, that's just weird. It's wel

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

> > First thing I'd look at is whether it's the same execution environment > in both cases, eg same search_path. > As far as I can tell, it's the same execution environment, same search_path and same user. I found after testing other situations, that the psql command would always finish as expect

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

Corey Taylor writes: > I am trying to figure out how to debug an issue where a function > 'import_wss' called through pgadmin4 will complete but not through psql. First thing I'd look at is whether it's the same execution environment in both cases, eg same search_path. You could try doing EXPLAI

postgres 9.6: insert into select finishes only in pgadmin not psql

If this is a common question or an article exists, please let me know. I couldn't find anything specific about it in stack overflow questions or postgres/psql documentation. This is in PostgreSQL 9.6. I am trying to figure out how to debug an issue where a function 'import_wss' called through pg