Re: [GENERAL] Query with error - DOW FROM timestamp
Yes, thank you.
-- Original Message --
From: "Albe Laurenz"
To: "Victor Sterpu" ; "PostgreSQL General"
Sent: 5/23/2014 12:51:30 PM
Subject: RE: [GENERAL] Query with error - DOW FROM timestamp
Victor Sterpu wrote:
When I run the query from down I receive an error.
How can I write this query to receive the day ot the week.
SELECT EXTRACT(DOW FROM timestamp TO_TIMESTAMP('14-10-2011',
'DD-MM-'));
Maybe you mean
SELECT EXTRACT(DOW FROM TO_TIMESTAMP('14-10-2011', 'DD-MM-'));
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trouble running nested select - Join query
Hi, I have been working with Postgresxc 1.1 on a cluster, each cluster compute node having 48GB RAM. I have instantiated the coordinator on one of the compute nodes and have 4 datanodes (2+2) running on two other compute nodes. I have been trying to run the following query which is a select on two large, same size tables - miami_2d(head int, tail int) and miami_directednetwork(head int, tail int) (both of them have 105400516 rows). miami_2d has been replicated on all datanodes and miami_directednetwork has been hash distributed on the head column across all 4 datanodes. I have indexes on both the tables. The query is: copy (select * from (select s1.head as h1,s1.tail as t1 ,s2.tail as neighbor from miami_2d s1, miami_directednetwork s2 where s1.tail=s2.head and s2.tail not in (select tail from miami_2d where head=s1.head)) as O where O.h1!=O.neighbor) to '/tmp/tmp.txt' Basically, the two tables are just a copy of each other having 'tail' as neighbor of 'head'. I am trying to find the distance-2 or 2 hop neighbor of the head column. For example: miami_directednetwork head | tail 400 | 336 400 | 209 400 | 487 336 | 400 336 | 209 336 | 500 487 | 400 487 | 391 miami_2d head | tail 400 | 336 400 | 209 400 | 487 336 | 400 336 | 209 336 | 500 487 | 400 487 | 391 Result: head | tail | neighbor 400 | 336 | 500 400 | 209 | 391 The issue is that every time I run the above query, it either gets killed by the system or it just keeps running for hours( I kill it after I see it running for more than a day). I am using the following settings in the postgers.conf file: For the datanodes: effective_cache_size=10480MB shared_buffers=3072MB work_mem=10480MB For the coordinator: effective_cache_size=1048MB shared_buffers=512MB work_mem=1024MB My question is: Is there anything wrong with the way I have framed the query or perhaps something else wrong? Any help is appreciated! Regards, Rushi
Re: [GENERAL] Shared memory changes in 9.4?
Maciek Sakrejda writes: > I've been trying to take the 9.4 beta for a spin, but seem to have run into > a shared memory issue on startup: > FATAL: could not open shared memory segment "/PostgreSQL.1804289383": > Permission denied > We're running Postgres inside LXC, which I suspect is causing problems > here, but 9.0 through 9.3 run just fine in the same configuration. Any idea > what may have changed? I suspect the solution here is tweaking some LXC > setting, but I was hoping someone can point me in the right direction. This message is coming out of the new "dynamic shared memory" code. I'm not real sure why that's being invoked at startup; perhaps Robert Haas can elucidate. Anyway the short answer is that this sounds like a portability hazard in the new code in src/backend/storage/ipc/dsm_impl.c. Perhaps you can look into that and identify what's up. 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] Shared memory changes in 9.4?
Hi, I've been trying to take the 9.4 beta for a spin, but seem to have run into a shared memory issue on startup: FATAL: could not open shared memory segment "/PostgreSQL.1804289383": Permission denied We're running Postgres inside LXC, which I suspect is causing problems here, but 9.0 through 9.3 run just fine in the same configuration. Any idea what may have changed? I suspect the solution here is tweaking some LXC setting, but I was hoping someone can point me in the right direction. Thanks, Maciek Sakrejda Heroku Postgres
