Re: [GENERAL] Effecient time ranges in 9.4/9.5?

Steven Lembark writes: > Trying to store open hours for storefront operations. > These are degenerate sets of > ( store + weekday + open time + close time ) > (i.e., candidate key == all fields). Ultimate goal is to compare hours > for service times (e.g., seating,

[GENERAL] Effecient time ranges in 9.4/9.5?

Trying to store open hours for storefront operations. These are degenerate sets of ( store + weekday + open time + close time ) (i.e., candidate key == all fields). Ultimate goal is to compare hours for service times (e.g., seating, pickup, delivery) to food prep times (e.g., breakast or

Re: [GENERAL] Sensitivity to drive failure?

Israel Brewster writes: > How sensitive is PostgreSQL to a failure that causes it to loose a single > tablespace, while the rest of the database cluster is still accessible? Will > it continue running, allowing access to databases that aren't in the missing > tablespace,

[GENERAL] Sensitivity to drive failure?

How sensitive is PostgreSQL to a failure that causes it to loose a single tablespace, while the rest of the database cluster is still accessible? Will it continue running, allowing access to databases that aren't in the missing tablespace, or will it crash (or similar)?I have one rather large

Re: [GENERAL] Serialization errors despite KEY SHARE/NO KEY UPDATE

On 10/02/2015 12:28 AM, Jim Nasby wrote: On 9/29/15 9:47 AM, Olivier Dony wrote: My understanding of the KEY SHARE/NO KEY UPDATE locks introduced in 9.3 was that they would avoid side-effects/blocking between transactions that are only linked via FK constraints, as long as the target PK was not

Re: [GENERAL] "global" & shared sequences

On 10/1/15 6:48 PM, Jonathan Vanasco wrote: 1. general performance at different stages of DB size. with 18 sequences, our keys/indexes are simply smaller than they'd be with 1 key. i wonder how this will impact lookups and joins. I'm not really following here... the size of an index is

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

pinker wrote: > I've tried to write audit trigger which fires only when data > changed, so I used "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause > as described in documentation. Should this clause be independent > from data type? because an error occurs when I'm trying to modify >

Re: [GENERAL] Serialization errors despite KEY SHARE/NO KEY UPDATE

On 10/2/15 11:44 AM, Olivier Dony wrote: On 10/02/2015 12:28 AM, Jim Nasby wrote: On 9/29/15 9:47 AM, Olivier Dony wrote: My understanding of the KEY SHARE/NO KEY UPDATE locks introduced in 9.3 was that they would avoid side-effects/blocking between transactions that are only linked via FK

Re: [GENERAL] Broken primary key after backup restore.

Michael Chau wrote: > For some reason, there were some bad wal log files in pg_xlog. I > believe that they got generated during the backup last Monday, > but I don't know why. I speculate that may be the > wal_keep_segments was not set high enough as I have changed

Re: [GENERAL] Postgresql 9.4 and ZFS?

On 10/1/15 8:04 PM, Joseph Kregloh wrote: In either case you are still "bottlenecked" by the speed of the write from RAM to the zpool. Now for a small database with not many writes a ZIL would be awesome. But on a write heavy database you will be acknowledging more writes because of the ZIL that

[GENERAL] aggregates, distinct, order by, and case - why won't this work

This...on 9.3 SELECT array_agg( distinct case when v % 2 = 0 then 'odd' else 'even' end order by case when v % 2 = 0 then 1 else 2 end ) FROM (VALUES (1), (2), (3)) val (v) I'm not particularly irked at this though I was hoping to fix a somewhat complex query of mine by simply adding a

Re: [GENERAL] postgresql doesn't start

On 10/02/2015 02:02 PM, Paolo De Michele wrote: hi there, I've a big problem with my postgresql installation I've postgresql 9.3 installed on docker; I start it via supervisord I've never had issues with postgresql and I don't touched nothing (no update, no changes) this is my configuration

[GENERAL] postgresql doesn't start

hi there, I've a big problem with my postgresql installation I've postgresql 9.3 installed on docker; I start it via supervisord I've never had issues with postgresql and I don't touched nothing (no update, no changes) this is my configuration there's a file called postgresql.conf in

Re: [GENERAL] "global" & shared sequences

Thanks for the reply. On Oct 2, 2015, at 3:26 PM, Jim Nasby wrote: > I'm not really following here... the size of an index is determined by the > number of tuples in it and the average width of each tuple. So as long as > you're using the same size of data type, 18 vs 1 sequence won't change

Re: [GENERAL] postgresql doesn't start

Paolo De Michele writes: > 2015-10-01 21:40:20 UTC FATAL: could not remove old lock file > "postmaster.pid": Permission denied Looks like something removed the postmaster's write permission on the data directory itself. regards, tom lane --

Re: [GENERAL] postgresql doesn't start

On 10/2/2015 2:02 PM, Paolo De Michele wrote: I've postgresql 9.3 installed on docker docker is a packaging system for linux containers, its not an operating system per se.what OS is your docker container running on? supervisord is a process/daemon manager, its not part of

Re: [GENERAL] postgresql doesn't start

On 10/2/2015 2:02 PM, Paolo De Michele wrote: exec su postgres -c "/usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main -c config_file=/etc/postgresql/9.3/main/postgresql.conf" until yesterday there were no problem right now I see this in the /var/log/supervisor's directory:

Re: [GENERAL] aggregates, distinct, order by, and case - why won't this work

On Fri, Oct 2, 2015 at 5:03 PM, Tom Lane wrote: > "David G. Johnston" writes: > > This...on 9.3 > > SELECT array_agg( > > distinct case when v % 2 = 0 then 'odd' else 'even' end > > order by case when v % 2 = 0 then 1 else 2 end > > ) > > FROM

Re: [GENERAL] aggregates, distinct, order by, and case - why won't this work

"David G. Johnston" writes: > This...on 9.3 > SELECT array_agg( > distinct case when v % 2 = 0 then 'odd' else 'even' end > order by case when v % 2 = 0 then 1 else 2 end > ) > FROM (VALUES (1), (2), (3)) val (v) The error message seems pretty clear to me: ERROR: in

Re: [GENERAL] "global" & shared sequences

On 10/2/15 4:08 PM, Jonathan Vanasco wrote: Using an even distribution as an example, the average width of the keys can increase by 2 places: Assuming you're using int4 or int8, then that doesn't matter. The only other possible issue I can think of would be it somehow throwing the planner

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

On Fri, Oct 2, 2015 at 3:03 PM, Kevin Grittner wrote: > pinker wrote: > > > I've tried to write audit trigger which fires only when data > > changed, so I used "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause > > as described in documentation. Should this clause

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

On Saturday, October 3, 2015, Dane Foster wrote: > > Hi, > > Would you please provide the link to the section in the documentation that > you are referring to because I'm new to PostgreSQL and I didn't know WHEN > could be used outside of CASE and EXCEPTION blocks. > >

Re: [GENERAL] postgresql doesn't start

​Are you using docker on centos ? I had problem with centos/docker/postgresql because container size was (maybe still is) limited to 20GB on that specific OS​. Maybe not related, but good to know 2015-10-03 0:03 GMT+02:00 John R Pierce : > On 10/2/2015 2:02 PM, Paolo De

Re: [GENERAL] Sensitivity to drive failure?

I think it would be really handy if temp_tablespaces were made resilient against everything going away in the event of a hard crash. -- Regards, Peter Geoghegan