Re: How to watch for schema changes

On 07/11/2018 08:46 PM, Igor Korot wrote: Hi, guys, On Mon, Jul 9, 2018 at 5:38 PM, Adrian Klaver wrote: On 07/09/2018 01:49 PM, Igor Korot wrote: Hi, Adrian On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver wrote: On 07/03/2018 11:15 AM, Igor Korot wrote: Adrian, On Tue, Jul 3, 2018 at

Re: How to watch for schema changes

Hi, guys, On Mon, Jul 9, 2018 at 5:38 PM, Adrian Klaver wrote: > On 07/09/2018 01:49 PM, Igor Korot wrote: >> >> Hi, Adrian >> >> On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver >> wrote: >>> >>> On 07/03/2018 11:15 AM, Igor Korot wrote: Adrian, On Tue, Jul 3, 2018 at 12:

Re: Open Source tool to deploy/promote PostgreSQL DDL

On Thu, Jul 12, 2018 at 5:18 AM, Adrian Klaver wrote: > On 07/11/2018 04:01 PM, Ron wrote: >> >> On 07/11/2018 04:10 PM, Christopher Browne wrote: >> [snip] >>> >>> ITIL surely does NOT specify the use of database rollback scripts as >>> THE SPECIFIED MECHANISM for a backout procedure. >>> >>> In

Re: Open Source tool to deploy/promote PostgreSQL DDL

On 07/11/2018 04:01 PM, Ron wrote: On 07/11/2018 04:10 PM, Christopher Browne wrote: [snip] ITIL surely does NOT specify the use of database rollback scripts as THE SPECIFIED MECHANISM for a backout procedure. In practice, we tend to take database snapshots using filesystem tools, as that repre

Re: Open Source tool to deploy/promote PostgreSQL DDL

On 07/11/2018 04:10 PM, Christopher Browne wrote: [snip] ITIL surely does NOT specify the use of database rollback scripts as THE SPECIFIED MECHANISM for a backout procedure. In practice, we tend to take database snapshots using filesystem tools, as that represents a backout procedure that will

Re: Open Source tool to deploy/promote PostgreSQL DDL

Gavin Flower writes: > 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 t

Re: sorting/comparing column values in non-alphanumeric sorting ways ?

On Wed, Jul 11, 2018 at 2:44 PM, David Gauthier wrote: > I want to load the 'highestver' column with the highest version of tcfg1-3. > > This won't work... > update tv set greatest = greatest(tcfg1,tcfg2,tcfg3) > ...because it thinks 1.0.9 is greater than 1.0.10 > > Is there a way to get this to w

Re: sorting/comparing column values in non-alphanumeric sorting ways ?

On Wednesday, July 11, 2018, David Gauthier wrote: > > This won't work... > update tv set greatest = greatest(tcfg1,tcfg2,tcfg3) > ...because it thinks 1.0.9 is greater than 1.0.10 > > Is there a way to get this to work right ? > > Haven't used it personally but this seems promising: https://pg

sorting/comparing column values in non-alphanumeric sorting ways ?

Hi: I have a table listing tools and tool versions for a number of different tool configurations. Something like this... create table tv (tool text, tcfg1 test, tcfg2 text, tcfg3 text, highestver text); insert into tv (tool,tcfg1mtcfg2,tcfg3) values ('tool_a','1.0.5b','1.0.10','1.0.9'); I want

Re: timestamp (military) at time zone without the suffix

On 07/11/2018 02:21 PM, David Gauthier wrote: Pleas reply to list also. Ccing list. Table columns have already been defined with timestamp datatype.  The on;y way I know of to fix this is to... 1) add a new column as timestamptz called 'tmp' (whatever) 2) update tmp with the value in the time

Re: timestamp (military) at time zone without the suffix

On 07/11/2018 01:34 PM, David Gauthier wrote: Thanks Everyone, they all work, but TL's seems to be the simplest... select current_timestamp(0) at time zone 'utc' I'm kinda stuck with the timestamp data type (vs timestamptz). Wondering if I can stick with that. The above is at little unclear.

Re: Open Source tool to deploy/promote PostgreSQL DDL

On Wed, 11 Jul 2018 at 16:37, Ron wrote: > > On 07/11/2018 03:21 PM, Christopher Browne wrote: > > I have built one that I call Mahout > > (https://github.com/cbbrowne/mahout) which has the merit of involving > > just two shell scripts, one of which is an auditing tool (pgcmp). > > > > It implemen

Re: How to tell which event was fired in Trigger function

On 7/11/2018 11:02 AM, David G. Johnston wrote: On Wed, Jul 11, 2018 at 10:54 AM, Igal @ Lucee.org >wrote: On 7/11/2018 10:38 AM, Adrian Klaver wrote: On 07/11/2018 10:36 AM, Igal @ Lucee.org wrote: How can I tell inside the trigger function if th

Re: timestamp (military) at time zone without the suffix

On Wednesday, July 11, 2018, David Gauthier wrote: > > I want to store the current UTC date/time in the DB. Does PG > unconditionally store something like UTC, then let the queries figure out > how they want to look at it (with "at time zone" and "to_char()" etc...) ? > Or do I have to intentiona

Re: Open Source tool to deploy/promote PostgreSQL DDL

On 07/11/2018 03:39 PM, Ravi Krishna wrote: Where I work, the requirement to have rollback scripts is part of the ITIL requirement for Changes to have a backout procedure. Liquibase provides that ability, but IMO rollback for RDBMS is always bit tricky. Certain DDL operations can take long t

Re: Open Source tool to deploy/promote PostgreSQL DDL

> > Where I work, the requirement to have rollback scripts is part of the ITIL > requirement for Changes to have a backout procedure. > Liquibase provides that ability, but IMO rollback for RDBMS is always bit tricky. Certain DDL operations can take long time if it involves a table rewrite.

Re: Open Source tool to deploy/promote PostgreSQL DDL

On 07/11/2018 03:21 PM, Christopher Browne wrote: I have built one that I call Mahout (https://github.com/cbbrowne/mahout) which has the merit of involving just two shell scripts, one of which is an auditing tool (pgcmp). It implements a "little language" to indicate dependencies between the SQL

Re: timestamp (military) at time zone without the suffix

Thanks Everyone, they all work, but TL's seems to be the simplest... select current_timestamp(0) at time zone 'utc' I'm kinda stuck with the timestamp data type (vs timestamptz). Wondering if I can stick with that. One last question... I want to store the current UTC date/time in the DB. Does

Re: Open Source tool to deploy/promote PostgreSQL DDL

I have built one that I call Mahout (https://github.com/cbbrowne/mahout) which has the merit of involving just two shell scripts, one of which is an auditing tool (pgcmp). It implements a "little language" to indicate dependencies between the SQL scripts that implement the DDL changes. The notabl

Re: timestamp (military) at time zone without the suffix

On Wednesday, July 11, 2018, David Gauthier wrote: > OK, the "to_char" gets rid of the timezone extension. But the times still > don't make sense. > > When I go to store this in a DB, I want to store the UTC time. How d I do > that ? > Use the data type that represents exactly that, timestampt

Re: timestamp (military) at time zone without the suffix

On 07/11/2018 12:59 PM, David Gauthier wrote: OK, the "to_char" gets rid of the timezone extension.  But the times still don't make sense. UTC should be 5 hours ahead, not behind.  It should be EST plus 5 hours (or 4 for DST), not minus.  That's why I said I expected 20:27 . When I go to st

Re: timestamp (military) at time zone without the suffix

OK, the "to_char" gets rid of the timezone extension. But the times still don't make sense. UTC should be 5 hours ahead, not behind. It should be EST plus 5 hours (or 4 for DST), not minus. That's why I said I expected 20:27 . When I go to store this in a DB, I want to store the UTC time. Ho

Re: timestamp (military) at time zone without the suffix

On 07/11/2018 12:36 PM, David Gauthier wrote: Hi: And I want to get rid of the -04 suffix. Is there a way to do this ? For the details see: https://www.postgresql.org/docs/10/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT Thanks ! -- Adrian Klaver adrian.kla...@aklaver

Re: Optimizing execution of expensive subqueries

Hi Try this way: SELECT tbl.field1, tbl.field2, tbl.field3, ..., b.Thingy1Sum, ... repeat for multiply thingies ... FROM tbl LATERAL JOIN ( SELECT anothertbl.UserId,SUM(Duration) as Thingy1Sum FROM anothertbl WHERE anothertbl.UserId = tbl.UserId AND anothertbl.ThingyId = 1 group by 1)

Re: timestamp (military) at time zone without the suffix

David Gauthier writes: > sqfdev=> select now()::timestamp(0) at time zone 'utc' ; > timezone > > 2018-07-11 11:27:12-04 > (1 row) You're doing it wrong: coercing to timestamp already involves a rotation to local time, and then "at time zone" says to interpret tha

Re: timestamp (military) at time zone without the suffix

On 07/11/2018 12:36 PM, David Gauthier wrote: select now()::timestamp(0) at time zone 'utc' ; Or: test=> select now(); now --- 2018-07-11 12:51:50.498416-07 (1 row) test=> select now()::timestamptz(0) at time zone 'utc' ; timezone -

Re: timestamp (military) at time zone without the suffix

On 07/11/2018 12:36 PM, David Gauthier wrote: Hi: I would like to get the utc timestamp, 24-hr clock (military time), without the time zone suffix. Below commands were run nearly at the same time... sqfdev=> select now()::timestamp(0)  ;          now -  2018-07-11 15:27

Re: timestamp (military) at time zone without the suffix

On Wednesday, July 11, 2018, David Gauthier wrote: > Hi: > > I would like to get the utc timestamp, 24-hr clock (military time), > without the time zone suffix. > > Below commands were run nearly at the same time... > > sqfdev=> select now()::timestamp(0) ; > now > -

timestamp (military) at time zone without the suffix

Hi: I would like to get the utc timestamp, 24-hr clock (military time), without the time zone suffix. Below commands were run nearly at the same time... sqfdev=> select now()::timestamp(0) ; now - 2018-07-11 15:27:12 (1 row) ...then immediately... sqfdev=> select

Re: How to tell which event was fired in Trigger function

On Wed, Jul 11, 2018 at 10:54 AM, Igal @ Lucee.org wrote: > On 7/11/2018 10:38 AM, Adrian Klaver wrote: > >> On 07/11/2018 10:36 AM, Igal @ Lucee.org wrote: >> >>> How can I tell inside the trigger function if the event was DELETE or >>> INSERT/UPDATE? >>> >> >> https://www.postgresql.org/docs/10

Re: How to tell which event was fired in Trigger function

On 7/11/2018 10:38 AM, Adrian Klaver wrote: On 07/11/2018 10:36 AM, Igal @ Lucee.org wrote: How can I tell inside the trigger function if the event was DELETE or INSERT/UPDATE? https://www.postgresql.org/docs/10/static/plpgsql-trigger.html This looks like it have all of the information that

Re: How to tell which event was fired in Trigger function

On 07/11/2018 10:36 AM, Igal @ Lucee.org wrote: Hi, I am writing a trigger function that is used after DELETE, INSERT, and UPDATE, like so:     CREATE TRIGGER tr_name AFTER DELETE OR INSERT OR UPDATE ... How can I tell inside the trigger function if the event was DELETE or INSERT/UPDATE?

How to tell which event was fired in Trigger function

Hi, I am writing a trigger function that is used after DELETE, INSERT, and UPDATE, like so:     CREATE TRIGGER tr_name AFTER DELETE OR INSERT OR UPDATE ... How can I tell inside the trigger function if the event was DELETE or INSERT/UPDATE? The table has a `NOT NULL id` column, so I am thi

Re: Using CTE vs temporary tables

One thing to note, if this is a query you would like to run on a replica, temp tables are a non-starter. I really wish that wasn't the case. I have quite a few analytical queries I had to optimize with temp tables and indexes, and I really wish I could run on my hot standby. I in most cases I can

Re: Using CTE vs temporary tables

On Wed, Jul 11, 2018 at 9:35 AM, Ravi Krishna wrote: > ​Does temp tables also suffer from optimization fence we see in CTE.​ > >> ​I suppose it depends on how they end up being referenced in the query. It is not possible for the auto-vacuum daemon to vacuum/analyze them so if you aren't doing th

Re: Using CTE vs temporary tables

​Does temp tables also suffer from optimization fence we see in CTE.​ >

Re: Using CTE vs temporary tables

On Wed, Jul 11, 2018 at 9:30 AM, hmidi slim wrote: > Hi, > I have a big query that used about 15 cte and its execution time is > acceptable. I'm trying to optimize my query because it contains about 150 > lines of code and becomes hard to understand it and add new filter or > condition easily. >

Using CTE vs temporary tables

Hi, I have a big query that used about 15 cte and its execution time is acceptable. I'm trying to optimize my query because it contains about 150 lines of code and becomes hard to understand it and add new filter or condition easily. I think to change some cte with temporary tables and using indexe

Optimizing execution of expensive subqueries

Hi pgsql-general! I'm currently looking at a query that is generally selecting a bunch of simple columns from a table, and also performing some subqueries to aggregate related data, and then sorting by one of the simple columns and paginating the result. eg. SELECT tbl.field1, tbl.field2, tbl.

RE: Extremely slow autovacuum:vacuum to prevent wraparound

Thanks a lot Steve, that really answers my question..  Many thanks, Rijo Roy  Sent from Yahoo Mail on Android On Wed, 11 Jul 2018 at 7:50 pm, Steven Winfield wrote: #yiv8600935552 #yiv8600935552 -- _filtered #yiv8600935552 {panose-1:2 2 6 9 4 2 5 8 3 4;} _filtered #yiv8600935552 {panose-

RE: Extremely slow autovacuum:vacuum to prevent wraparound

From: Rijo Roy [mailto:rjo_...@yahoo.com] Sent: 11 July 2018 15:14 To: Steven Winfield Cc: pgsql-gene...@postgresql.org Subject: RE: Extremely slow autovacuum:vacuum to prevent wraparound Hi Steve, Apologies, I misread it as 2 billion, it is as you said set as 200 million which is the default va

RE: Extremely slow autovacuum:vacuum to prevent wraparound

Hi Steve,  Apologies, I misread it as 2 billion, it is as you said set as 200 million which is the default value for the parameter autovacuum_freeze_max_age. I just wanted to confirm whether there would be any impact if I cancel or terminate the backend for the existing autovacuum :vacuum table_

Re: Extremely slow autovacuum:vacuum to prevent wraparound

> Setting it that high and disabling autovacuum isn’t just silly - it borders > on sabotage! LOL. My thoughts too. Perhaps some disgruntled employee's parting shot before quitting :-)

Re: Open Source tool to deploy/promote PostgreSQL DDL

On Tue, Jul 10, 2018 at 5: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 normally use scripts,

RE: Extremely slow autovacuum:vacuum to prevent wraparound

From: Rijo Roy [mailto:rjo_...@yahoo.com] Sent: 11 July 2018 14:30 To: srkris...@yahoo.com Cc: pgsql-gene...@postgresql.org Subject: Re: Extremely slow autovacuum:vacuum to prevent wraparound No, I have 3 sessions of autovacuum against 3 tables which is doing a Vacuum to prevent wraparound as i

Re: Extremely slow autovacuum:vacuum to prevent wraparound

On Wed, Jul 11, 2018 at 8:17 AM, Rijo Roy wrote: > +pgsql-general > > Sent from Yahoo Mail on Android > > > On Wed, 11 Ju

Re: Extremely slow autovacuum:vacuum to prevent wraparound

No, I have  3 sessions of autovacuum against 3 tables which is doing a Vacuum to prevent wraparound as it hit the limit of autovacuum_freeze_max_age of 2 billion. I also have a vaccumdb session which is invoked by me on these tables which is currently in paused state. So, I want to know whether

Re: Extremely slow autovacuum:vacuum to prevent wraparound

+pgsql-general Sent from Yahoo Mail on Android On Wed, 11 Jul 2018 at 5:43 pm, Rijo Roy wrote: Hi Ravi,  It was the application teams call to disable it fearing the alteration of execution plans and slowness as per their explanation. I have joined 2 days back and I have tried educating th

Extremely slow autovacuum:vacuum to prevent wraparound

Hi Experts,  I am running a PG10.4 in a RHEL 6.9. The tables stored in my database are mainly used for reads and very less writes happen. I have recently joined this environment and as soon as I logged into the postgresql servers, checked for dead tuples and relfrozenids.. And almost all have re

Re: Open Source tool to deploy/promote PostgreSQL DDL

On 10/07/18 23:13, 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 normally use scripts, but they want som

Re: Create event triger

On Tue, Jul 10, 2018 at 5:45 PM Adrian Klaver wrote: > select add_trigger('trg_test'); > > test=> \d trg_test > > Table "public.trg_test" > > > Column | Type| Collation | Nullable | Default > > > +---+---+--+- > >

Re: Open Source tool to deploy/promote PostgreSQL DDL

Hustler DBA schrieb am 11.07.2018 um 00:13: > 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 > someth