Re: correcting tablespaces inside data folder

Hi. On Fri, Jul 6, 2018 at 6:30 PM Duarte Carreira wrote: > > > On Fri, Jul 6, 2018 at 6:15 PM Magnus Hagander > wrote: > >> (please don't top-post. It makes it hard to follow discussions) >> >> On Fri, Jul 6, 2018 at 7:05 PM, Duarte Carreira >> wrote: >> >>> Magnus, >>> You mean changing the

Re: Create event triger

Łukasz Jarych schrieb am 09.07.2018 um 13:03: > i have small database and i am tracking changes using trigger: > > CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON t_trig > > FOR EACH ROW EXECUTE PROCEDURE change_trigger(); > > It is possible to create general trigger for all

Re: Split daterange into sub periods

Based on you example I updated it to get the results that I want: create table hmidi( id serial primary key, product_id integer, d date range) insert into hmidi(product_id, d) values(15, '[2018-11-01, 2018-11-01]'); insert into hmidi(product_id, d) values(15, '[2018-11-03, 2018-11-04]'); Then I

Re: Reporting bug on pgAdmin 4.3

On 07/09/2018 09:40 PM, a wrote: Hi I'm doing a normal query on pgAdmin, my server platform is win server 2008, my laptop is win10, both using pgsql 10. The results of the query shows 8488 rows are selected, which is correct. How did you determine this, in pgAdmin or somewhere else?

Re: Reconnecting a slave to a newly-promoted master

On Mon, Jul 09, 2018 at 05:58:53PM -0700, Shawn Mulloney wrote: > There are three PostgreSQL machines: A, B, and C. B and C are slaves off of > the master, A. A fails, and B is promoted to being the new master. Can C > just be pointed at A and have it "just work"? In your question I am pretty

Re: Create DDL trigger to catch which column was altered

It is no possible? Jacek pon., 9 lip 2018 o 13:38 Łukasz Jarych napisał(a): > Hi Guys, > > i am using sqls like below to track ddl changes: > > CREATE TABLE track_ddl >> ( >> event text, >> command text, >> ddl_time timestamptz, >> usr text >> ); >> CREATE OR REPLACE FUNCTION

Re: Create event triger

No possible? Jacek pon., 9 lip 2018 o 13:03 Łukasz Jarych napisał(a): > Hi, > > i have small database and i am tracking changes using trigger: > > CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON t_trig > > FOR EACH ROW EXECUTE PROCEDURE change_trigger(); > > It is possible to

Re: Create event triger

2018-07-10 10:56 GMT+02:00 Łukasz Jarych : > No possible? > > Nope, you need to set up the trigger on each table. Jacek > > pon., 9 lip 2018 o 13:03 Łukasz Jarych napisał(a): > >> Hi, >> >> i have small database and i am tracking changes using trigger: >> >> CREATE TRIGGER t BEFORE INSERT OR

Re: Create event triger

Thank you very much Guillaume. Do you know maybe any function to do it automatically? Best, Jacek wt., 10 lip 2018 o 11:25 Guillaume Lelarge napisał(a): > 2018-07-10 10:56 GMT+02:00 Łukasz Jarych : > >> No possible? >> >> > Nope, you need to set up the trigger on each table. > > Jacek >> >>

Re: Create event triger

Maybe yes, but for me when i am learning it is not... Best, Jacek wt., 10 lip 2018 o 11:29 Guillaume Lelarge napisał(a): > 2018-07-10 11:28 GMT+02:00 Łukasz Jarych : > >> Thank you very much Guillaume. >> >> Do you know maybe any function to do it automatically? >> >> > Nope, but it should

Re: Create event triger

2018-07-10 11:28 GMT+02:00 Łukasz Jarych : > Thank you very much Guillaume. > > Do you know maybe any function to do it automatically? > > Nope, but it should be easy to write a shell script or a DO script to do it. Best, > Jacek > > wt., 10 lip 2018 o 11:25 Guillaume Lelarge > napisał(a): >

Re: Disabling/Enabling index before bulk loading

Ravi Krishna schrieb am 10.07.2018 um 16:08: > > We recently did a test on COPY and found that on large tables (47 million > rows , 20GB of raw data) the > difference in COPY with 16 indexes and COPY without any index is 1:14. That > is, COPY is 14 times slower > when data is ingested with

Re: Create event triger

On 07/10/2018 02:30 AM, Łukasz Jarych wrote: Maybe yes, but for me when i am learning it is not... How do you do CREATE TABLE now, ad hoc in the client or via scripts? If via scripts you could create a template script for the trigger and then just fill in the table name as needed. A

Re: Reporting bug on pgAdmin 4.3

Hi, since I'm not with the test environment so there may be not very exact answers. 1, The data was set to load from oracle server at 0 am of each work day. There are 3 ways to determine the right results: a. data from oracle server; b. there is going to be a statement summary in query

Disabling/Enabling index before bulk loading

We recently did a test on COPY and found that on large tables (47 million rows , 20GB of raw data) the difference in COPY with 16 indexes and COPY without any index is 1:14. That is, COPY is 14 times slower when data is ingested with all indexes as opposed to COPY first without index and

Re: Refresh materialized view deadlocking?

Hi Xiao From https://www.postgresql.org/docs/current/static/sql-refreshmaterializedview.html For concurrently option " Even with this option only one REFRESH at a time may run against any one materialized view. " I would guess yes. Regards, Akshaya On Tue, 10 Jul 2018 at 23:24, Xiao Ba

Re: Disabling/Enabling index before bulk loading

> > Did you include the time to CREATE INDEX after the COPY or is the 1:14 only > for the COPY stage? Yes. Time taken to load 47 mil rows with all 16 indexes intact: 14+ hrs Time taken to load the same after dropping index and then loading and finally creating 16 indexes: 1 hr 40 min

Refresh materialized view deadlocking?

I have a materialized view that gets refreshed concurrently every 10 seconds (b/c it contains time sensitive stuff). At times it appears to get backed up so there are multiple refreshes queued and then gets stuck waiting for a lock? Does anybody know if this is expected behavior? Am I supposed to

Re: Disabling/Enabling index before bulk loading

On 07/10/2018 07:08 AM, Ravi Krishna wrote: We recently did a test on COPY and found that on large tables (47 million rows , 20GB of raw data) the difference in COPY with 16 indexes and COPY without any index is 1:14. That is, COPY is 14 times slower when data is ingested with all indexes as

max_standby_streaming_delay not enforced

I ran into an issue the other day on one of my database clusters where it appears a replica had a transaction open for almost 24 hours. This was causing stale data on the replica (as observed by queries against the db), as well as tuple bloat on the master (due to `hot_standby_feedback=on` and

Re: Disabling/Enabling index before bulk loading

On Tue, Jul 10, 2018 at 1:13 PM, Ravi Krishna wrote: > > > > Did you include the time to CREATE INDEX after the COPY or is the 1:14 > only for the COPY stage? > > Yes. > > Time taken to load 47 mil rows with all 16 indexes intact: 14+ hrs > Time taken to load the same after dropping index and

Re: Disabling/Enabling index before bulk loading

> > > https://fle.github.io/temporarily-disable-all-indexes-of-a-postgresql-table.html > > > This does not work in RDS. In order to update system catalog tables (pg_index), one needs privileges which is

Re: Disabling/Enabling index before bulk loading

On Tue, Jul 10, 2018 at 12:26 PM Ravi Krishna wrote: > > > > https://fle.github.io/temporarily-disable-all-indexes-of-a-postgresql-table.html > > > > > This does not work in RDS. In order to update system catalog tables > (pg_index), one needs privileges which is > denied in RDS. In RDS

Re: Open Source tool to deploy/promote PostgreSQL DDL

Thanks Adrian and Rich, I will propose sqitch to the client, but I think they want something with a GUI frontend. They want to deploy database changes, track which environments the change was deployed to, be able to rollback a change (with a rollback script), track when and if the change was

Re: Open Source tool to deploy/promote PostgreSQL DDL

On Tue, 10 Jul 2018, Hustler DBA wrote: A client of mine is looking for an open source tool to deploy and promote PostgreSQL DDL changes through database environments as part of SDLC. What tools (open source) does the community members use? I normally use scripts, but they want something open

Open Source tool to deploy/promote PostgreSQL DDL

Hi Community, A client of mine is looking for an open source tool to deploy and promote PostgreSQL DDL changes through database environments as part of SDLC. What tools (open source) does the community members use? I normally use scripts, but they want something open source. Thanks, Neil Barrett

Re: Disabling/Enabling index before bulk loading

Ravi Krishna writes: > We recently did a test on COPY and found that on large tables (47 million > rows , 20GB of raw data) the > difference in COPY with 16 indexes and COPY without any index is 1:14. That > is, COPY is 14 times slower > when data is ingested with all indexes as opposed to

Re: Open Source tool to deploy/promote PostgreSQL DDL

On 11/07/18 11:04, Hustler DBA wrote: Thanks Adrian and Rich, I will propose sqitch to the client, but I think they want something with a GUI frontend. They want to deploy database changes, track which environments the change was deployed to, be able to rollback a change (with a rollback

Re: Reporting bug on pgAdmin 4.3

On 07/10/2018 07:01 AM, a wrote: Hi, since I'm not with the test environment so there may be not very exact answers. 1, The data was set to load from oracle server at 0 am of each work day. There are 3 ways to determine the right results:     a. data from oracle server;     b. there is

Re: Disabling/Enabling index before bulk loading

> > I am very suspicious of why you need 16 indexes. Are you sure all those > indexes are actually being utilized? > Try executing the attached query, You may find find some are really not > needed. This is a DATAMART application and the indexes are to satisfy a large number of queries

Re: Open Source tool to deploy/promote PostgreSQL DDL

On 07/10/2018 03:13 PM, Hustler DBA wrote: Hi Community, A client of mine is looking for an open source tool to deploy and promote PostgreSQL DDL changes through database environments as part of SDLC. What tools (open source) does the community members use? I https://sqitch.org/ normally

Re: Disabling/Enabling index before bulk loading

> We recently did a test on COPY and found that on large tables (47 million rows , 20GB of raw data) the > difference in COPY with 16 indexes... *I am very suspicious of why you need 16 indexes. Are you sure all those indexes are actually being utilized?* *Try executing the attached query, You

Re: Open Source tool to deploy/promote PostgreSQL DDL

On 07/10/2018 05:04 PM, Hustler DBA wrote: Thanks Adrian and Rich, I will propose sqitch to the client, but I think they want something with a GUI frontend. They want to deploy database changes, track which environments the change was deployed to, be able to rollback a change (with a

Re: Create event triger

On 07/10/2018 02:30 AM, Łukasz Jarych wrote: Maybe yes, but for me when i am learning it is not... The example function. It is a minimal example but it should serve as a starting point.: CREATE OR REPLACE FUNCTION public.add_trigger(tbl_name character varying) RETURNS void LANGUAGE

Re: Reporting bug on pgAdmin 4.3

Hey, I fixed the problem by using web pgadmin also with a upgrade to 4.3.1. So I installed pgadmin 4.3 under the location of .\PostgreSQL\10\pgadmin, so it will start up a window instead of a web viewer. I deleted it and re-installed the 4.3.1 and started it with web browser, the problem