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

[Tom Lane]

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, pickup, delivery) to food prep times
> (e.g., breakast or lunch menu).

> I'd like to store them as:

> ( store + weekday + timerange )

> to simplify exclusion constraints and joins for overlapping food prep
> and service times. Lacking a built-in "timetzrange", I'm stuck defining
> the type. 

Why do you think it needs to be "timetzrange" and not "timerange"?
Most stores I know of close at, say, 5PM local time, not 5PM during
standard time and some other time during daylight-savings.

> The examples in [1] & [2] don't include a working subtype_diff (just a 
> reference to "float8mi" without defining it).

That example works fine, as you'd soon find if you tried
copying-and-pasting it into psql.  float8mi() is a built-in function,
namely the one underlying the "float8 - float8" operator.  The example is
oversimplified a little bit, in that that subtraction operator yields
float8, which just happens to be the required result type of a
subtype_diff function.  For most types, you'd need a subtraction operator
and then a conversion to float8, wrapped up as a single function.

The problem you'll have in defining timetzrange is that you first need to
invent a "timetz - timetz" operator, which doesn't exist as a builtin
function because the behavior seems not well-defined.  What would you do
with the timezone fields?

If you went with "timerange" then the required subtraction operator
does already exist, and you just need a wrapper function to cast the
result to float8, probably with extract(epoch ...).

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Effecient time ranges in 9.4/9.5?

[Steven Lembark]

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 lunch menu).


I'd like to store them as:

( store + weekday + timerange )

to simplify exclusion constraints and joins for overlapping food prep
and service times. Lacking a built-in "timetzrange", I'm stuck defining
the type. 

I think a working subtype_diff to effeciently support exclusion 
constraints on ( store with =, + weekday with =, hours with &&).

In particular, a working subtype_diff, assuming that the hours are all
in the range of  .. 2400 (i.e., no cross-day intervals).

The examples in [1] & [2] don't include a working subtype_diff (just a 
reference to "float8mi" without defining it). At the least a working 
time -> float8 operator might be nothing more than a cast but I don't 
see how to do it offhand.

There are several cases I've found of people wanting to create a 
working time range, without any specifics of how (e.g., [3]). I can
see where the built-in would have issues ([4], [5]) but using time 
ranges with dates as templates to produce timestamp-ranges makes life 
s much easier with scheduling.

The 9.4 doc's describe the subtype_diff as necessary for effective 
gist indexing. Then again, the builtins for time may be sufficient 
to just define subtype = timetz and be done with it... I cannot find
any references either way.

It's not that hard to handle differences mod-24hrs:

diff = ( upper - lower + 24 % 24 );

if upper < lower the +24 corrects the sign; if upper > lower the % 24
keeps the result in range. I'm just not entirely 

Q: Is the subtype_diff really useful for indexing if the subtype is 
   timetz?

Q: If so, where is an example to an effecient diff for the times?

thanks

[1] 
[2] 
[3] 
[4] 

[5] 


-- 
Steven Lembark   3646 Flora Pl
Workhorse Computing St Louis, MO 63110
lemb...@wrkhors.com+1 888 359 3508


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Sensitivity to drive failure?

[Tom Lane]

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, or will it crash (or similar)?

If the tablespace just disappears mid-run, things will not be good; any
dirty buffers for those tables that are hanging around in shared buffers
cannot be written out, so checkpoints cannot complete, so WAL will
accumulate indefinitely (meaning that crash recovery time will grow,
among other bad effects).  It's not going to be any better than losing
some random subset of files that aren't separated by tablespace.

I don't recall the details for sure, but I think you could manually
recover from such a scenario by dropping all the affected tables.  But
the tablespace mechanism, per se, doesn't help you in this.  It's not
designed to be a robustness aid.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Sensitivity to drive failure?

[Israel Brewster]

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 database in my cluster containing non-critical statistical data. Stuff that's nice to have, but if I were to loose it, I probably won't even bother trying to restore from backup - I'd just start over collecting it again. As it takes up a lot of space, I am planning to move it off of my main (raid 10) drive, and on to an external. As this data is non-critical, I was thinking of having that external just be a simple single drive, but that leads to my question - should that one drive fail, that portion of the database will obviously disappear. Will Postgress continue to function, serving up the rest of the database that is on the RAID and simply throwing errors whenever something tries to access the missing database? Or will loosing that drive with the one database bring down the entire cluster? If the latter, then I obviously need to think about doing a RAID for that extra drive as well. Thanks.
---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD

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

[Olivier Dony]

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 touched. Isn't it the case here?


Not quite. Any unique index that isn't partial and isn't a functional
index can satisfy a foreign key. That means that if you change a field
that is in ANY unique index that update becomes a FOR KEY UPDATE.


Interesting, do you know if that is mentioned in the documentation somewhere? 
(I couldn't find it)




-- Setup tables
CREATE TABLE users ( id serial PRIMARY KEY,
name varchar,
date timestamp );
CREATE TABLE orders ( id serial PRIMARY KEY,
 name varchar,
 user_id int REFERENCES users (id) );
INSERT INTO users (id, name) VALUES (1, 'foo');
INSERT INTO orders (id, name) VALUES (1, 'order 1');


-- Run 2 concurrent transactions: T1 and T2
   T1T2
|-|--|
 BEGIN ISOLATION LEVEL
   REPEATABLE READ;

 UPDATE orders
 SET name = 'order of foo',
 user_id = 1
 WHERE id = 1;

   BEGIN ISOLATION LEVEL
 REPEATABLE READ;

   UPDATE users
   SET date = now()
   WHERE id = 1;

   COMMIT;

 UPDATE orders
 SET name = 'order of foo (2)',
 user_id = 1
 WHERE id = 1;

T1 fails with:
ERROR:  could not serialize access due to concurrent update
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."users" x WHERE
"id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"


This isn't a locking failure, it's a serialization failure. I'm not sure
why it's happening though... is there an index on date?


I don't think so. I can reproduce the problem with the queries quoted above, 
and the only index that seems to be present is the PK (sorry for the wrapping):


9=# \d users
   Table "public.users"
 Column |Type | Modifiers 


+-+
 id | integer | not null default 
nextval('users_id_seq'::regclass)

 name   | character varying   |
 date   | timestamp without time zone |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "orders" CONSTRAINT "orders_user_id_fkey" FOREIGN KEY (user_id) 
REFERENCES users(id)


9=# \d orders
   Table "public.orders"
 Column  |   Type|  Modifiers 


-+---+-
 id  | integer   | not null default 
nextval('orders_id_seq'::regclass)
 name| character varying |
 user_id | integer   |
Indexes:
"orders_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"orders_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)


--
Olivier


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] "global" & shared sequences

[Jim Nasby]

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 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 the size of your indexes.



2. managing this sequence when next scaling the db (which would probably have 
to be sharding, unless others have a suggestion)


Sequences are designed to be extremely fast to assign. If you ever did 
find a single sequence being a bottleneck, you could always start 
caching values in each backend. I think it'd be hard (if not impossible) 
to turn a single global sequence into a real bottleneck.


If you start sharding you'll need to either create a composite ID where 
part of the ID is a shard identifier (say, the top 8 bits), or assign 
IDs in ranges that are assigned to each shard. There's work being done 
right now to make #2 a bit easier. Probably better would be if you could 
shard based on something like object or customer; that way you only have 
to look up which shard the customer lives in.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Kevin Grittner]

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
> row with point data type:
> ERROR: could not identify an equality operator for type point

> CREATE TRIGGER trigger_update_test
> AFTER UPDATE
> ON test1
> FOR EACH ROW
> WHEN ((old.* IS DISTINCT FROM new.*))
> EXECUTE PROCEDURE test_update();

Since you seem to be on 9.4, how about this?:

CREATE TRIGGER trigger_update_test
AFTER UPDATE
ON test1
FOR EACH ROW
WHEN ((old *<> new))
EXECUTE PROCEDURE test_update();

http://www.postgresql.org/docs/9.4/static/functions-comparisons.html#COMPOSITE-TYPE-COMPARISON

Which says (in part):

| To support matching of rows which include elements without a
| default B-tree operator class, the following operators are
| defined for composite type comparison: *=, *<>, *<, *<=, *>, and
| *>=. These operators compare the internal binary representation
| of the two rows. Two rows might have a different binary
| representation even though comparisons of the two rows with the
| equality operator is true. The ordering of rows under these
| comparison operators is deterministic but not otherwise
| meaningful. These operators are used internally for materialized
| views and might be useful for other specialized purposes such as
| replication but are not intended to be generally useful for
| writing queries.

It seems to me that auditing would be an appropriate use, because
it would show whether there was any change in the stored value, not
just whether the old and new values were equal in a btree ordering
comparison.  For example, if a citext column were changed from 'a'
to 'A', it would compare as equal with its type's "=" operator, but
the row would show as changed anyway, if you use "*=" or "*<>".

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Jim Nasby]

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 constraints, as long as the target PK was
not touched. Isn't it the case here?


Not quite. Any unique index that isn't partial and isn't a functional
index can satisfy a foreign key. That means that if you change a field
that is in ANY unique index that update becomes a FOR KEY UPDATE.


Interesting, do you know if that is mentioned in the documentation
somewhere? (I couldn't find it)


http://www.postgresql.org/docs/9.4/static/ddl-constraints.html#DDL-CONSTRAINTS-FK 
does say this:


"A foreign key must reference columns that either are a primary key or 
form a unique constraint."


So you can kind of infer it. It could probably be more explicitly 
mentioned somewhere though. Care to suggest a doc change?



-- Setup tables
CREATE TABLE users ( id serial PRIMARY KEY,
name varchar,
date timestamp );
CREATE TABLE orders ( id serial PRIMARY KEY,
 name varchar,
 user_id int REFERENCES users (id) );
INSERT INTO users (id, name) VALUES (1, 'foo');
INSERT INTO orders (id, name) VALUES (1, 'order 1');


-- Run 2 concurrent transactions: T1 and T2
   T1T2
|-|--|
 BEGIN ISOLATION LEVEL
   REPEATABLE READ;

 UPDATE orders
 SET name = 'order of foo',
 user_id = 1
 WHERE id = 1;

   BEGIN ISOLATION LEVEL
 REPEATABLE READ;

   UPDATE users
   SET date = now()
   WHERE id = 1;

   COMMIT;

 UPDATE orders
 SET name = 'order of foo (2)',
 user_id = 1
 WHERE id = 1;

T1 fails with:
ERROR:  could not serialize access due to concurrent update
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."users" x WHERE
"id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"


This isn't a locking failure, it's a serialization failure. I'm not sure
why it's happening though... is there an index on date?


I don't think so. I can reproduce the problem with the queries quoted
above, and the only index that seems to be present is the PK (sorry for
the wrapping):


I'm not sure. Perhaps Kevin Grittner (author of serializable patch) can 
shed some light.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Kevin Grittner]

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 it
> recently.
>
> Luckily, I have archived the wal log files. And by comparing
> between the two directories, I did see those bad wal log files in
> pg_xlog directory only.

The instructions for making a backup explicitly say to exclude or
delete the files in the pg_xlog directory and use those from the
archive.  That's because tar (or whatever you are using to copy the
files) may copy a WAL file before some change made during the
backup is written to it.  What you were doing is not supported and
likely to appear to work sometimes (possibly even without hidden
corruption), and fail to create a backup that will even start on
other attempts.

You might find this blog post helpful:

http://tbeitr.blogspot.com/2015/07/deleting-backuplabel-on-restore-will.html

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgresql 9.4 and ZFS?

[Jim Nasby]

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 what you are
physically able to write from RAM to zpool, thereby degrading performance.


Unless ZFS lies about fsync, you'll have to wait for writes to go 
somewhere, either the main pool or the ZIL. Because the ZIL is 
effectively write-only, having a ZIL could significantly reduce fsync 
latency because it's a) only writing and b) writing sequentially.


Essentially, it's the same idea as having a separate pg_xlog partition 
(except that depending on your setup there may well be pg_xlog read 
activity as well).

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[David G. Johnston]

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 "DISTINCT" to the array_agg that I
am building from derived (using CASE) data.

I am curious to the reason for the limitation, particularly as it would
relate to this specific instance.

Any givers?

Thank!

David J.

Re: [GENERAL] postgresql doesn't start

[Adrian Klaver]

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
there's a file called postgresql.conf in /etc/supervisor/conf.d/
cat command:

[program:postgres]
command=/opt/postgresql.sh
autostart=true
autorestart=true
stopsignal=QUIT


If it where me I would change the above to:

[program:postgres]
command=/opt/postgresql.sh
autostart=true
stopsignal=TERM



lookt at /opt/postgresql.sh

#!/bin/sh

# This script is run by Supervisor to start PostgreSQL 9.3 in foreground
mode

if [ -d /var/run/postgresql ]; then
 chmod 2775 /var/run/postgresql
else
 install -d -m 2775 -o postgres -g postgres /var/run/postgresql
fi

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:

2015-10-01 21:40:18 UTC HINT:  The file seems accidentally left over,
but it could not be removed. Please remove the file by hand and try again.
2015-10-01 21:40:20 UTC FATAL:  could not remove old lock file
"postmaster.pid": Permission denied

if I remove this file with sudo permissions when I re-run the process
I've the same error and I don't understand why. I tried the same
configuration (postgresql installation and configuration) in another new
docker installation and works fine.

what's the problem?
someone help me?
I've very important databases and I dont' know how to recover it

please let me know, thanks in advance





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] postgresql doesn't start

[Paolo De Michele]

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 /etc/supervisor/conf.d/
cat command:

[program:postgres]
command=/opt/postgresql.sh
autostart=true
autorestart=true
stopsignal=QUIT

lookt at /opt/postgresql.sh

#!/bin/sh

# This script is run by Supervisor to start PostgreSQL 9.3 in foreground
mode

if [ -d /var/run/postgresql ]; then
chmod 2775 /var/run/postgresql
else
install -d -m 2775 -o postgres -g postgres /var/run/postgresql
fi

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:

2015-10-01 21:40:18 UTC HINT:  The file seems accidentally left over, but
it could not be removed. Please remove the file by hand and try again.
2015-10-01 21:40:20 UTC FATAL:  could not remove old lock file
"postmaster.pid": Permission denied

if I remove this file with sudo permissions when I re-run the process I've
the same error and I don't understand why. I tried the same configuration
(postgresql installation and configuration) in another new docker
installation and works fine.

what's the problem?
someone help me?
I've very important databases and I dont' know how to recover it

please let me know, thanks in advance

Re: [GENERAL] "global" & shared sequences

[Jonathan Vanasco]

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 the 
> size of your indexes.

I'm pretty much concerned with exactly that -- the general distribution of 
numbers, which affects the average size/length of each key.

Using an even distribution as an example, the average width of the keys can 
increase by 2 places:

Since we have ~18 object types, the primary keys in each might range from 1 to  
9,999,999
Using a shared sequence, the keys for the same dataset would range from  1 to 
189,999,999

Each table is highly related, and may fkey onto 2-4 other tables... So i'm a 
bit wary of this change.  But if it works for others... I'm fine with that!


> Sequences are designed to be extremely fast to assign. If you ever did find a 
> single sequence being a bottleneck, you could always start caching values in 
> each backend. I think it'd be hard (if not impossible) to turn a single 
> global sequence into a real bottleneck.

I don't think so either, but everything I've read has been theoretical -- so I 
was hoping that someone here can give the "yeah, no issue!" from experience.
The closest production stuff I found was  via  the BDR plugin (only relevant 
thing that came up during search) and there seemed to be anecdotal accounts of 
issues with sequences becoming bottlenecks -- but that was from their code that 
pre-generated allowable sequence ids on each node.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgresql doesn't start

[Tom Lane]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgresql doesn't start

[John R Pierce]

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 'postgresql', 
although apparently your docker container is using it to run postgresql.


your questions all seem related to the specific packaging of this 
particular docker container, you might see who created this container 
configuration, and ask them these questions as there's a lot going on 
there abstracting things.



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgresql doesn't start

[John R Pierce]

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:

2015-10-01 21:40:18 UTC HINT:  The file seems accidentally left over, 
but it could not be removed. Please remove the file by hand and try again.
2015-10-01 21:40:20 UTC FATAL:  could not remove old lock file 
"postmaster.pid": Permission denied



try...
ls -la /var/lib/postgresql/9.3/main

The directory . should be owned by the postgres user, and it should have 
700, 750, or 770 permissions.   all the files in it should also be owned 
by postgres.


also look and see if postgres logged anything in its own system log 
files (/var/log/postgresql/9.3  or whatever).




--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[David G. Johnston]

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 (VALUES (1), (2), (3)) val (v)
>
> The error message seems pretty clear to me:
>
> ERROR:  in an aggregate with DISTINCT, ORDER BY expressions must appear in
> argument list
>
> This is exactly the same as the complaint you'd get with a SELECT-level
> DISTINCT, eg
>
> regression=# create table ttt(a int, b int);
> CREATE TABLE
> regression=# select distinct a from ttt order by b;
> ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select
> list
> LINE 1: select distinct a from ttt order by b;
> ^
>
> and the reason is the same too: the value of b is not necessarily unique
> within any one group of rows with the same value of a, so it's not
> well-defined what output order this is asking for.
>
> In the example you give, it's possible for a human to see that the two
> case expressions give values that must correlate perfectly.  But PG
> doesn't try to do that kind of analysis.  It just insists that an ORDER
> BY expression be one of the ones being DISTINCT'd on.
>
>
​Thanks.

It definitely makes simple situations a bit more complicated but I can see
how it needs to be that way to handle the generalized case.

I guess I'm looking for something that basically performs a sort, a map,
and then unique but one that simply leaves the first instance of any values
while removing subsequent ones even if non-adjacent.

imagine sorted input with a map function classifying each number - indeed
this is not a great example...

EVEN, ODD, ODD, IMAGINARY, ODD, EVEN, INFINITY => EVEN, ODD, IMAGINARY,
INFINITY

Put differently I'm trying to perform set-operations while using an
array...I should explore this more and see if I can make a set (sub-query)
work...
DISTINCT ON may be useful
​.

David J.
​

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

[Tom Lane]

"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 an aggregate with DISTINCT, ORDER BY expressions must appear in 
argument list

This is exactly the same as the complaint you'd get with a SELECT-level
DISTINCT, eg

regression=# create table ttt(a int, b int);
CREATE TABLE
regression=# select distinct a from ttt order by b;
ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: select distinct a from ttt order by b;
^

and the reason is the same too: the value of b is not necessarily unique
within any one group of rows with the same value of a, so it's not
well-defined what output order this is asking for.

In the example you give, it's possible for a human to see that the two
case expressions give values that must correlate perfectly.  But PG
doesn't try to do that kind of analysis.  It just insists that an ORDER
BY expression be one of the ones being DISTINCT'd on.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] "global" & shared sequences

[Jim Nasby]

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 stats off, but I think the odds of that are very small.



>Sequences are designed to be extremely fast to assign. If you ever did find a 
single sequence being a bottleneck, you could always start caching values in each 
backend. I think it'd be hard (if not impossible) to turn a single global sequence 
into a real bottleneck.

I don't think so either, but everything I've read has been theoretical -- so I was hoping 
that someone here can give the "yeah, no issue!" from experience.The 
closest production stuff I found was  via  the BDR plugin (only relevant thing that came 
up during search) and there seemed to be anecdotal accounts of issues with sequences 
becoming bottlenecks -- but that was from their code that pre-generated allowable 
sequence ids on each node.


You could always run a custom pg_bench that runs a PREPAREd SELECT 
nextval() and compare that to a prepared SELECT currval(). You might 
notice a difference at higher client counts with no caching, but I doubt 
you'd see that much difference with caching turned on.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Dane Foster]

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 be independent
> > from data type? because an error occurs when I'm trying to modify
> > row with point data type:
> > ERROR: could not identify an equality operator for type point
>
> > CREATE TRIGGER trigger_update_test
> > AFTER UPDATE
> > ON test1
> > FOR EACH ROW
> > WHEN ((old.* IS DISTINCT FROM new.*))
> > EXECUTE PROCEDURE test_update();
>
> Since you seem to be on 9.4, how about this?:
>
> CREATE TRIGGER trigger_update_test
> AFTER UPDATE
> ON test1
> FOR EACH ROW
> WHEN ((old *<> new))
> EXECUTE PROCEDURE test_update();
>
>
> http://www.postgresql.org/docs/9.4/static/functions-comparisons.html#COMPOSITE-TYPE-COMPARISON
>
> Which says (in part):
>
> | To support matching of rows which include elements without a
> | default B-tree operator class, the following operators are
> | defined for composite type comparison: *=, *<>, *<, *<=, *>, and
> | *>=. These operators compare the internal binary representation
> | of the two rows. Two rows might have a different binary
> | representation even though comparisons of the two rows with the
> | equality operator is true. The ordering of rows under these
> | comparison operators is deterministic but not otherwise
> | meaningful. These operators are used internally for materialized
> | views and might be useful for other specialized purposes such as
> | replication but are not intended to be generally useful for
> | writing queries.
>
> It seems to me that auditing would be an appropriate use, because
> it would show whether there was any change in the stored value, not
> just whether the old and new values were equal in a btree ordering
> comparison.  For example, if a citext column were changed from 'a'
> to 'A', it would compare as equal with its type's "=" operator, but
> the row would show as changed anyway, if you use "*=" or "*<>".
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
​
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.

Thanks.

Dane
​

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

[David G. Johnston]

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.
>
>
 http://www.postgresql.org/docs/9.4/static/sql-createtrigger.html

David J.

Re: [GENERAL] postgresql doesn't start

[Nicolas Paris]

​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 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:
>>
>> 2015-10-01 21:40:18 UTC HINT:  The file seems accidentally left over, but
>> it could not be removed. Please remove the file by hand and try again.
>> 2015-10-01 21:40:20 UTC FATAL:  could not remove old lock file
>> "postmaster.pid": Permission denied
>>
>
>
> try...
> ls -la /var/lib/postgresql/9.3/main
>
> The directory . should be owned by the postgres user, and it should have
> 700, 750, or 770 permissions.   all the files in it should also be owned by
> postgres.
>
> also look and see if postgres logged anything in its own system log files
> (/var/log/postgresql/9.3  or whatever).
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Sensitivity to drive failure?

[Peter Geoghegan]

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